66 lines
1.2 KiB
SQL
66 lines
1.2 KiB
SQL
create table t_student(
|
|
s_id int PRIMARY KEY auto_increment,
|
|
s_name VARCHAR(30) ,
|
|
sex VARCHAR(2)
|
|
);
|
|
|
|
create table t_course(
|
|
c_id int PRIMARY KEY auto_increment,
|
|
c_name VARCHAR(30)
|
|
);
|
|
|
|
create table stu_cou(
|
|
sc_id int PRIMARY KEY auto_increment,
|
|
c_id int,
|
|
s_id int
|
|
);
|
|
|
|
INSERT INTO t_student(s_name,sex)VALUES
|
|
('张三','男'), ('李四','男'), ('王五','男'), ('赵柳','女'),
|
|
('田七','男'), ('王霸','男'), ('南丰','男'), ('梅梅','女'),
|
|
('小气','女'), ('诺手','女'), ('阿萨德','男'), ('阿斯顿','男');
|
|
|
|
INSERT into t_course(c_name) VALUES
|
|
('语文'),('计算机'),('英语'),('数学');
|
|
|
|
|
|
INSERT into stu_cou(s_id,c_id) VALUES
|
|
(1,1),(1,3),(1,2),(2,1),(2,2),(2,4),(3,2),(3,1),(3,3),(4,1),
|
|
(4,2),(4,4),(5,1),(5,2),(6,1),(6,3),(7,3),(7,2),(8,1),(9,2),
|
|
(10,4),(9,1),(10,2),(11,4),(12,1),(12,3);
|
|
|
|
|
|
#男女生各多少
|
|
SELECT
|
|
sex,
|
|
COUNT(*)
|
|
FROM
|
|
t_student
|
|
GROUP BY
|
|
sex;
|
|
|
|
|
|
|
|
#男女生分别占总人数的比例
|
|
SELECT
|
|
sex,
|
|
CONCAT( ROUND( COUNT(*) / ( SELECT COUNT(*) FROM t_student )* 100, 2 ), '%' )
|
|
FROM
|
|
t_student
|
|
GROUP BY
|
|
sex;
|
|
|
|
|
|
#没门课程下的学生人数
|
|
SELECT
|
|
c.c_name,
|
|
count(*)
|
|
FROM
|
|
t_course c
|
|
LEFT JOIN stu_cou sc ON sc.c_id = c.c_id
|
|
LEFT JOIN t_student s ON sc.s_id = s.s_id
|
|
GROUP BY
|
|
c_name
|
|
|
|
|