====班级表
mysql> insert into class values
-> (1, '三年二班'),
-> (2, '三年一班'),
-> (3, '三年三班'),
-> (4, '三年六班');
Query OK, 4 rows affected (0.00 sec)
mysql> select * from class;
+-----+--------------+
| cid | caption |
+-----+--------------+
| 1 | 三年二班 |
| 2 | 三年一班 |
| 3 | 三年三班 |
| 4 | 三年六班 |
+-----+--------------+
4 rows in set (0.00 sec)
====学生表
mysql> insert into student values
-> (1, '巴托', 'male', 1),
-> (2, '拉花', 'male', 2),
-> (3, '尔康', 'male', 3),
-> (4, '小太妹', 'female', 4),
-> (5, '大静', 'female', 4);
mysql> insert into student values
-> (6, '赏金', 'female', 1),
-> (7, '隆多', 'male', 2),
-> (8, '薇恩', 'female', 3),
-> (9, '小萝莉', 'female', 4),
-> (10, '寒冰', 'female',1);
Query OK, 5 rows affected (0.00 sec)
mysql> insert into student values
-> (11, '剑圣', 'male', 1),
-> (12, '洛克', 'male', 3),
-> (13, '瑞文', 'male', 3),
-> (14, '皇子', 'male', 4),
-> (15, '凯莎', 'female', 2);
Query OK, 5 rows affected (0.00 sec)
mysql> select * from student;
+-----+-----------+--------+----------+
| sid | sname | gender | class_id |
+-----+-----------+--------+----------+
| 1 | 巴托 | male | 1 |
| 2 | 拉花 | male | 2 |
| 3 | 尔康 | male | 3 |
| 4 | 小太妹 | female | 4 |
| 5 | 大静 | female | 4 |
| 6 | 赏金 | female | 1 |
| 7 | 隆多 | male | 2 |
| 8 | 薇恩 | female | 3 |
| 9 | 小萝莉 | female | 4 |
| 10 | 寒冰 | female | 1 |
| 11 | 剑圣 | male | 1 |
| 12 | 洛克 | male | 3 |
| 13 | 瑞文 | male | 3 |
| 14 | 皇子 | male | 4 |
| 15 | 凯莎 | female | 2 |
+-----+-----------+--------+----------+
====教师表
mysql> insert into teacher values
-> (2, '李某'),
-> (3, '梁某'),
-> (4, '陈某');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into teacher values
-> (5, '刘某'),
-> (6, '唐某');
Query OK, 2 rows affected (0.02 sec)
mysql> select * from teacher;
+-----+--------+
| tid | tname |
+-----+--------+
| 1 | 陈某 |
| 2 | 李某 |
| 3 | 梁某 |
| 4 | 陈某 |
| 5 | 刘某 |
| 6 | 唐某 |
+-----+--------+
6 rows in set (0.00 sec)
====课程表
mysql> insert into course values
-> (1, '生物', 1),
-> (2, '物理', 2),
-> (3, '体育', 3),
-> (4, '美术', 2),
-> (5, '体育', 4);
Query OK, 5 rows affected (0.01 sec)
mysql> select * from course;
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
| 5 | 体育 | 4 |
+-----+--------+------------+
5 rows in set (0.00 sec)
====成绩表
mysql> insert into score values
-> (1, 1, 1, 10),
-> (2, 1, 2, 9),
-> (3, 1, 4, 66),
-> (4, 2, 1, 8),
-> (5, 2, 3, 68),
-> (6, 2, 4, 88),
-> (7, 3, 2, 8),
-> (8, 3, 3, 55),
-> (9, 3, 4, 66),
-> (10, 4, 1, 6),
-> (11, 4, 3, 89),
-> (12, 4, 4, 96),
-> (13, 5, 1, 33),
-> (14, 5, 2, 34),
-> (15, 5, 4, 34);
Query OK, 15 rows affected (0.00 sec)
mysql> insert into score values
-> (16, 6, 1, 10),
-> (17, 6, 2, 9),
-> (18, 6, 4, 66),
-> (19, 7, 1, 8),
-> (20, 7, 3, 68),
-> (21, 7, 4, 88),
-> (22, 7, 2, 8);
Query OK, 7 rows affected (0.00 sec)
mysql> select * from score;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
| 1 | 1 | 1 | 10 |
| 2 | 1 | 2 | 9 |
| 3 | 1 | 4 | 66 |
| 5 | 2 | 3 | 68 |
| 6 | 2 | 4 | 88 |
| 7 | 3 | 2 | 8 |
| 8 | 3 | 3 | 55 |
| 9 | 3 | 4 | 66 |
| 10 | 4 | 1 | 6 |
| 11 | 4 | 3 | 89 |
| 12 | 4 | 4 | 96 |
| 13 | 5 | 1 | 33 |
| 14 | 5 | 2 | 34 |
| 15 | 5 | 4 | 34 |
| 16 | 6 | 1 | 10 |
| 17 | 6 | 2 | 9 |
| 18 | 6 | 4 | 66 |
| 19 | 7 | 1 | 8 |
| 20 | 7 | 3 | 68 |
| 21 | 7 | 4 | 88 |
| 22 | 7 | 2 | 8 |
+-----+------------+-----------+--------+
21 rows in set (0.00 sec)
- 查询所有大于60分的学生的姓名和学号 (DISTINCT: 去重)
mysql> select distinct student.sid, sname
-> from student left join score on
-> student.sid = score.student_id where
-> number > 60;
+-----+-----------+
| sid | sname |
+-----+-----------+
| 1 | 巴托 |
| 2 | 拉花 |
| 3 | 尔康 |
| 4 | 小太妹 |
| 6 | 赏金 |
| 7 | 隆多 |
+-----+-----------+
2.查询每个老师教授的课程数量 和 老师信息
mysql> select tid, tname, count(tid)
-> from teacher right join course on
-> tid = teacher_id
-> group by tid;
+------+--------+------------+
| tid | tname | count(tid) |
+------+--------+------------+
| 1 | 陈某 | 1 |
| 2 | 李某 | 2 |
| 3 | 梁某 | 1 |
| 4 | 陈某 | 1 |
+------+--------+------------+
4 rows in set (0.00 sec)
3.查询学生的信息以及学生所在的班级信息
mysql> select *
-> from student left join class on
-> class_id = cid;
+-----+-----------+--------+----------+------+--------------+
| sid | sname | gender | class_id | cid | caption |
+-----+-----------+--------+----------+------+--------------+
| 1 | 巴托 | male | 1 | 1 | 三年二班 |
| 6 | 赏金 | female | 1 | 1 | 三年二班 |
| 10 | 寒冰 | female | 1 | 1 | 三年二班 |
| 11 | 剑圣 | male | 1 | 1 | 三年二班 |
| 2 | 拉花 | male | 2 | 2 | 三年一班 |
| 7 | 隆多 | male | 2 | 2 | 三年一班 |
| 15 | 凯莎 | female | 2 | 2 | 三年一班 |
| 3 | 尔康 | male | 3 | 3 | 三年三班 |
| 8 | 薇恩 | female | 3 | 3 | 三年三班 |
| 12 | 洛克 | male | 3 | 3 | 三年三班 |
| 13 | 瑞文 | male | 3 | 3 | 三年三班 |
| 4 | 小太妹 | female | 4 | 4 | 三年六班 |
| 5 | 大静 | female | 4 | 4 | 三年六班 |
| 9 | 小萝莉 | female | 4 | 4 | 三年六班 |
| 14 | 皇子 | male | 4 | 4 | 三年六班 |
+-----+-----------+--------+----------+------+--------------+
15 rows in set (0.00 sec)
4、学生中男生的个数和女生的个数
mysql> select gender, count(gender)
-> from student
-> group by gender;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| male | 8 |
| female | 7 |
+--------+---------------+
2 rows in set (0.00 sec)
5.获取所有学习'生物'的学生的学号和成绩；姓名
mysql> select student.sid, sname, number
-> from score
-> left join student on
-> score.student_id = student.sid
-> left join course on
-> score.course_id = course.cid where
-> course.cname = '生物';
+------+-----------+--------+
| sid | sname | number |
+------+-----------+--------+
| 1 | 巴托 | 10 |
| 4 | 小太妹 | 6 |
| 5 | 大静 | 33 |
| 6 | 赏金 | 10 |
| 7 | 隆多 | 8 |
+------+-----------+--------+
5 rows in set (0.00 sec)
6.查询平均成绩大于60分的同学的学号和平均成绩；
mysql> select student.sid, sname, number
-> from score
-> left join student on
-> score.student_id = student.sid
-> left join course on
-> score.course_id = course.cid where
-> course.cname = '生物';
+------+-----------+--------+
| sid | sname | number |
+------+-----------+--------+
| 1 | 巴托 | 10 |
| 4 | 小太妹 | 6 |
| 5 | 大静 | 33 |
| 6 | 赏金 | 10 |
| 7 | 隆多 | 8 |
+------+-----------+--------+
5 rows in set (0.00 sec)
7.查询姓“李”的老师的个数；
mysql> select count(tname) '姓李的老师的个数'
-> from teacher
-> where tname like '李%';
+--------------------------+
| 姓李的老师的个数 |
+--------------------------+
| 1 |
+--------------------------+
1 row in set (0.00 sec)
8.查询课程成绩小于60分的同学的学号、姓名；
mysql> select distinct student.sid, sname
-> from student left join score on
-> student.sid = score.student_id where
-> number < 60;
+-----+-----------+
| sid | sname |
+-----+-----------+
| 1 | 巴托 |
| 3 | 尔康 |
| 4 | 小太妹 |
| 5 | 大静 |
| 6 | 赏金 |
| 7 | 隆多 |
+-----+-----------+
6 rows in set (0.00 sec)
9.删除学习“叶平”老师课的SC表记录
mysql> delete from score
-> where course_id =(
-> select cid
-> from teacher
-> left join course on
-> teacher.tid = course.teacher_id
-> where tname = '皇子');
Query OK, 0 rows affected (0.00 sec)
10.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
mysql> select course_id '课程ID', max(number) '最高分' , min(number) '最低分'
-> from score
-> group by course_id;
+----------+-----------+-----------+
| 课程ID | 最高分 | 最低分 |
+----------+-----------+-----------+
| 1 | 33 | 6 |
| 2 | 34 | 8 |
| 3 | 89 | 55 |
| 4 | 96 | 34 |
+----------+-----------+-----------+
4 rows in set (0.00 sec)
11.查询每门课程被选修的学生数
mysql> select cname, count(cname)
-> from score left join course on
-> course.cid = score.course_id
-> group by cname;
+--------+--------------+
| cname | count(cname) |
+--------+--------------+
| 体育 | 4 |
| 物理 | 5 |
| 生物 | 5 |
| 美术 | 7 |
+--------+--------------+
4 rows in set (0.00 sec)
12.查询姓“张”的学生名单；
mysql> select *
-> from student where
-> sname like '皇%';
+-----+--------+--------+----------+
| sid | sname | gender | class_id |
+-----+--------+--------+----------+
| 14 | 皇子 | male | 4 |
+-----+--------+--------+----------+
1 row in set (0.00 sec)
13.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
mysql> select course_id, avg(number)
-> from score
-> group by course_id
-> order by avg(number),course_id desc;
+-----------+-------------+
| course_id | avg(number) |
+-----------+-------------+
| 1 | 13.4000 |
| 2 | 13.6000 |
| 3 | 70.0000 |
| 4 | 72.0000 |
+-----------+-------------+
4 rows in set (0.00 sec)
14.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
mysql> select student.sid, student.sname, avg(number)
-> from student
-> left join score on
-> student.sid = score.student_id
-> group by student.sid
-> having avg(number) >60;
+-----+-----------+-------------+
| sid | sname | avg(number) |
+-----+-----------+-------------+
| 2 | 拉花 | 78.0000 |
| 4 | 小太妹 | 63.6667 |
+-----+-----------+-------------+
15.查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；
mysql> select student.sid, student.sname
-> from student
-> left join score on
-> student.sid = score.student_id
-> where
-> course_id = 3 and
-> number > 80;
+-----+-----------+
| sid | sname |
+-----+-----------+
| 4 | 小太妹 |
+-----+-----------+
1 row in set (0.00 sec)
16.查询各个课程及相应的选修人数
mysql> select cname, count(cname)
-> from score left join course on
-> course.cid = score.course_id
-> group by cname;
+--------+--------------+
| cname | count(cname) |
+--------+--------------+
| 体育 | 4 |
| 物理 | 5 |
| 生物 | 5 |
| 美术 | 7 |
+--------+--------------+
4 rows in set (0.00 sec)
17..查询“4”课程分数小于60，按分数降序排列的同学学号
mysql> select student.sid from
-> student left join score on
-> student.sid = score.student_id
-> where course_id = 4 and
-> number < 60
-> order by number desc;
+-----+
| sid |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)
18.删除学号为“2”的同学的“1”课程的成绩
mysql> delete from score
-> where course_id = 1
-> and student_id = 2;
Query OK, 0 rows affected (0.00 sec)