71 lines
2.5 KiB
SQL
71 lines
2.5 KiB
SQL
DROP TABLE IF EXISTS `stu_cou`;
|
||
CREATE TABLE `stu_cou` (
|
||
`sc_id` int(11) NOT NULL AUTO_INCREMENT,
|
||
`c_id` int(11) NULL DEFAULT NULL,
|
||
`s_id` int(11) NULL DEFAULT NULL,
|
||
PRIMARY KEY (`sc_id`) USING BTREE
|
||
)
|
||
|
||
DROP TABLE IF EXISTS `t_course`;
|
||
CREATE TABLE `t_course` (
|
||
`c_id` int(11) NOT NULL AUTO_INCREMENT,
|
||
`c_name` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL,
|
||
PRIMARY KEY (`c_id`) USING BTREE
|
||
)
|
||
|
||
DROP TABLE IF EXISTS `t_student`;
|
||
CREATE TABLE `t_student` (
|
||
`s_id` int(11) NOT NULL AUTO_INCREMENT,
|
||
`s_name` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL,
|
||
`sex` varchar(255) CHARACTER SET utf8 NULL DEFAULT NULL,
|
||
PRIMARY KEY (`s_id`) USING BTREE
|
||
)
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- 插入数据
|
||
|
||
INSERT INTO `t_student` VALUES (1, '陈詩涵', '女');
|
||
INSERT INTO `t_student` VALUES (2, '高秀英', '女');
|
||
INSERT INTO `t_student` VALUES (3, '周子异', '男');
|
||
INSERT INTO `t_student` VALUES (4, '钱云熙', '男');
|
||
INSERT INTO `t_student` VALUES (5, '史致远', '男');
|
||
INSERT INTO `t_student` VALUES (6, '蔡詩涵', '女');
|
||
INSERT INTO `t_student` VALUES (7, '马睿', '男');
|
||
INSERT INTO `t_student` VALUES (8, '黄嘉伦', '男');
|
||
INSERT INTO `t_student` VALUES (9, '刘杰宏', '男');
|
||
INSERT INTO `t_student` VALUES (10, '常子韬', '男');
|
||
|
||
|
||
INSERT INTO `t_course` VALUES (1, '语文');
|
||
INSERT INTO `t_course` VALUES (2, '数学');
|
||
INSERT INTO `t_course` VALUES (3, '英语');
|
||
|
||
|
||
INSERT INTO `stu_cou` VALUES (1, 2, 10);
|
||
INSERT INTO `stu_cou` VALUES (2, 1, 8);
|
||
INSERT INTO `stu_cou` VALUES (3, 2, 1);
|
||
INSERT INTO `stu_cou` VALUES (4, 2, 3);
|
||
INSERT INTO `stu_cou` VALUES (5, 3, 3);
|
||
INSERT INTO `stu_cou` VALUES (6, 2, 10);
|
||
INSERT INTO `stu_cou` VALUES (7, 2, 1);
|
||
INSERT INTO `stu_cou` VALUES (8, 3, 4);
|
||
INSERT INTO `stu_cou` VALUES (9, 2, 5);
|
||
INSERT INTO `stu_cou` VALUES (10, 2, 7);
|
||
INSERT INTO `stu_cou` VALUES (11, 2, 10);
|
||
INSERT INTO `stu_cou` VALUES (12, 3, 7);
|
||
INSERT INTO `stu_cou` VALUES (13, 3, 5);
|
||
INSERT INTO `stu_cou` VALUES (14, 3, 5);
|
||
INSERT INTO `stu_cou` VALUES (15, 2, 10);
|
||
|
||
-- 3.查询男生和女生各多少人。（15分）
|
||
SELECT sex, COUNT(*) AS num FROM t_student GROUP BY sex;
|
||
|
||
-- 4. 查询男生和女生分别占总人数的比例。（20分）
|
||
SELECT sex, COUNT(*) FROM t_student GROUP BY sex;
|
||
|
||
-- 5.查询每门课程下的学生总人数。（15分）
|
||
SELECT c.c_name, COUNT(sc.s_id) AS student_count FROM
|
||
t_course c LEFT JOIN stu_cou sc
|
||
ON c.c_id = sc.c_id GROUP BY c.c_id, c.c_name;
|