【MySQL】4、查询练习

一、准备数据

1、新建数据库selectTest

mysql> create database selectTest;
mysql> use selectTest;

2、新建学生表student

--创建学生表
mysql> create table student(
    -> s_no varchar(20) primary key comment'学生学号',
    -> s_name varchar(20) not null comment'学生姓名',
    -> s_sex varchar(10) not null comment'学生性别',
    -> s_birthday datetime comment'学生生日',
    -> s_class varchar(20) comment'学生所在班级'
    -> );
mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| s_no       | varchar(20) | NO   | PRI | NULL    |       |
| s_name     | varchar(20) | NO   |     | NULL    |       |
| s_sex      | varchar(10) | NO   |     | NULL    |       |
| s_birthday | datetime    | YES  |     | NULL    |       |
| s_class    | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

3、课程表

--创建课程表
mysql> create table course(
    -> c_no varchar(20) primary key comment'课程号',
    -> c_name varchar(20) not null comment'课程名称',
    -> t_no varchar(20) not null comment'教师编号-外键关联teacher',
    -> foreign key(t_no) reference teacher(t_no)
    -> );
//这时报错，因为没有创建教师表
//创建教师表后，再查看
mysql> desc course;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_no   | varchar(20) | NO   | PRI | NULL    |       |
| c_name | varchar(20) | NO   |     | NULL    |       |
| t_no   | varchar(20) | NO   | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

4、教师表

mysql> create table teacher(
    -> t_no varchar(20) primary key comment'教师编号',
    -> t_name varchar(20) not null comment'教师姓名',
    -> t_sex varchar(20) not null comment'教师性别',
    -> t_birthday datetime comment'教师生日',
    -> t_rof varchar(20) not null comment'教师职称',
    -> t_depart varchar(20) not null comment'教师所在部门'
    -> );
mysql> desc teacher;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_no       | varchar(20) | NO   | PRI | NULL    |       |
| t_name     | varchar(20) | NO   |     | NULL    |       |
| t_sex      | varchar(20) | NO   |     | NULL    |       |
| t_birthday | datetime    | YES  |     | NULL    |       |
| t_rof      | varchar(20) | NO   |     | NULL    |       |
| t_depart   | varchar(20) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

5、成绩表

mysql> create table score(
    -> s_no varchar(20) not null comment'成绩表编号-依赖于学生学号s_no',
    -> c_no varchar(20) not null comment'课程号-依赖于课程表的c_no',
    -> sc_degree decimal,
    -> foreign key(s_no) references student(s_no),
    -> foreign key(c_no) references course(c_no),
    -> primary key(s_no,c_no) 
    //复合主键，一个学生号对应多个课程号，而某一个学生的某一个课程号就不能
    //有重复
    //，只能有一个成绩
    //而之前只要学生表只要学生的编号定了，其他的字段都是定了的，随之变化。
    -> );
mysql> desc score;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| s_no      | varchar(20)   | NO   | PRI | NULL    |       |
| c_no      | varchar(20)   | NO   | PRI | NULL    |       |
| sc_degree | decimal(10,0) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

5、成绩表

mysql> create table score(
    -> s_no varchar(20) not null comment'成绩表编号-依赖于学生学号s_no',
    -> c_no varchar(20) not null comment'课程号-依赖于课程表的c_no',
    -> sc_degree decimal,
    -> foreign key(s_no) references student(s_no),
    -> foreign key(c_no) references course(c_no),
    -> primary key(s_no,c_no) 
    //复合主键，一个学生号对应多个课程号，而某一个学生的某一个课程号就不能
    //有重复
    //，只能有一个成绩
    //而之前只要学生表只要学生的编号定了，其他的字段都是定了的，随之变化。
    -> );
mysql> desc score;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| s_no      | varchar(20)   | NO   | PRI | NULL    |       |
| c_no      | varchar(20)   | NO   | PRI | NULL    |       |
| sc_degree | decimal(10,0) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

6、添加数据

--学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

--教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

--添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

--添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');

INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

mysql> select * from student;
+------+-----------+-------+---------------------+---------+
| s_no | s_name    | s_sex | s_birthday          | s_class |
+------+-----------+-------+---------------------+---------+
| 101  | 曾华      | 男    | 1977-09-01 00:00:00 | 95033   |
| 102  | 匡明      | 男    | 1975-10-02 00:00:00 | 95031   |
| 103  | 王丽      | 女    | 1976-01-23 00:00:00 | 95033   |
| 104  | 李军      | 男    | 1976-02-20 00:00:00 | 95033   |
| 105  | 王芳      | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军      | 男    | 1974-06-03 00:00:00 | 95031   |
| 107  | 王尼玛    | 男    | 1976-02-20 00:00:00 | 95033   |
| 108  | 张全蛋    | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱    | 男    | 1974-06-03 00:00:00 | 95031   |
+------+-----------+-------+---------------------+---------+

mysql> select * from teacher;
+------+--------+-------+---------------------+-----------+-----------------+
| t_no | t_name | t_sex | t_birthday          | t_rof     | t_depart        |
+------+--------+-------+---------------------+-----------+-----------------+
| 804  | 李诚   | 男    | 1958-12-02 00:00:00 | 副教授    | 计算机系        |
| 825  | 王萍   | 女    | 1972-05-05 00:00:00 | 助教      | 计算机系        |
| 831  | 刘冰   | 女    | 1977-08-14 00:00:00 | 助教      | 电子工程系      |
| 856  | 张旭   | 男    | 1969-03-12 00:00:00 | 讲师      | 电子工程系      |
+------+--------+-------+---------------------+-----------+-----------------+

mysql> select * from course;
+-------+-----------------+------+
| c_no  | c_name          | t_no |
+-------+-----------------+------+
| 3-105 | 计算机导论      | 825  |
| 3-245 | 操作系统        | 804  |
| 6-166 | 数字电路        | 856  |
| 9-888 | 高等数学        | 831  |
+-------+-----------------+------+

mysql> select * from score;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-105 |        92 |
| 103  | 3-245 |        86 |
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
| 105  | 3-245 |        75 |
| 105  | 6-166 |        79 |
| 109  | 3-105 |        76 |
| 109  | 3-245 |        68 |
| 109  | 6-166 |        81 |
+------+-------+-----------+

二、查询练习

1、查询student中的所有记录

select * from student;

2、查询student中的name，sex，class列

mysql> select s_name,s_sex,s_class from student;
+-----------+-------+---------+
| s_name    | s_sex | s_class |
+-----------+-------+---------+
| 曾华      | 男    | 95033   |
| 匡明      | 男    | 95031   |
| 王丽      | 女    | 95033   |
| 李军      | 男    | 95033   |
| 王芳      | 女    | 95031   |
| 陆军      | 男    | 95031   |
| 王尼玛    | 男    | 95033   |
| 张全蛋    | 男    | 95031   |
| 赵铁柱    | 男    | 95031   |
+-----------+-------+---------+

3、查询教师所有的单位-即不重复的depart列

dintinct排除重复。
mysql> select distinct t_depart from teacher;
+-----------------+
| t_depart        |
+-----------------+
| 计算机系        |
| 电子工程系      |
+-----------------+

4、查询score表中成绩在60-80之间的所有记录

查询区间between ... and ...，左右边界是包含的。
mysql> select * from score where sc_degree between 60 and 80;
//也可以写成select * from score where degree > 60 and degree < 80;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 105  | 3-245 |        75 |
| 105  | 6-166 |        79 |
| 109  | 3-105 |        76 |
| 109  | 3-245 |        68 |
+------+-------+-----------+

5、查询score表中成绩为85，86，88的记录

表示或者关系的查询in()
mysql> select * from score where sc_degree in(85,86,88);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-245 |        86 |
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
+------+-------+-----------+

6、查询student表中"95031"班或性别为“女”的同学记录

or表示或者
mysql> select * from score where sc_degree in(85,86,88);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-245 |        86 |
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
+------+-------+-----------+

7、以class降序查询student表的所有记录

升序asc，降序desc，不写默认升序。
mysql> select * from score where sc_degree in(85,86,88);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-245 |        86 |
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
+------+-------+-----------+
升序select * from student order by s_class asc;，其中asc不写默认升序。

8、以c_no升序、sc_degree降序查询score表中的所有记录

mysql> select * from score where sc_degree in(85,86,88);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-245 |        86 |
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
+------+-------+-----------+

9、查询95031班的人数

mysql> select * from score where sc_degree in(85,86,88);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-245 |        86 |
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
+------+-------+-----------+

10、查询score中最高分的学生学号和课程号（子查询或者排序）

mysql> select * from score where sc_degree in(85,86,88);
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-245 |        86 |
| 103  | 6-166 |        85 |
| 105  | 3-105 |        88 |
+------+-------+-----------+

最高分有多个时会有问题，可以写成SELECT c_no, s_no FROM score ORDER BY sc_degree DESC LIMIT 0,1;。其中limit x,y表示从第x条数据开始，查询y条。
并且如果只有select max(sc_degree) from score时，是可以展示多个相同的最高分的。

11、查询每门课的平均成绩

avg函数算平均值，group by表示分组
mysql> select c_no,avg(sc_degree) from score group by c_no; //先按照c_no进行分组，再进行计算平均值
+-------+----------------+
| c_no  | avg(sc_degree) |
+-------+----------------+
| 3-105 |        85.3333 |  //各个班的各自的平均成绩
| 3-245 |        76.3333 |
| 6-166 |        81.6667 |
+-------+----------------+
某一班的平均成绩select c_no,avg(sc_degree) from score where c_no='3-105';

12、查询score表中至少有2名学生选修的，并且以3开头的课程的平均分

groupby后面加条件，不用where用having

//select c_no from score group by cno //到这里输出只有三行，只是分好组
mysql> select c_no,avg(sc_degree),count(*) from score 
	-> group by c_no //先是分组
    -> having count(c_no)>=2 and c_no like '3%'; //分组后面带条件，不用where而是having
+-------+----------------+----------+
| c_no  | avg(sc_degree) | count(*) |
+-------+----------------+----------+
| 3-105 |        85.3333 |        3 |
| 3-245 |        76.3333 |        3 |
+-------+----------------+----------+

13、查询分数大于70小于90的s_no列

mysql> select s_no,sc_degree from score
    -> where sc_degree between 70 and 90;  //其实这么写70和90包含在里面
//mysql> select s_no,sc_degree from score   
    //-> where sc_degree>70 and sc_degree<90;  //这么写也可以
+------+-----------+
| s_no | sc_degree |
+------+-----------+
| 103  |        86 |
| 103  |        85 |
| 105  |        88 |
| 105  |        75 |
| 105  |        79 |
| 109  |        76 |
| 109  |        81 |
+------+-----------+

//显示对应的人
mysql> select s_name,c_name,sc_degree from score,student,course
    -> where score.s_no = student.s_no AND score.c_no = course.c_no //关联表的连接，多表联合查询
    -> AND sc_degree between 71 and ;
+-----------+-----------------+-----------+
| s_name    | c_name          | sc_degree |
+-----------+-----------------+-----------+
| 王丽      | 操作系统        |        86 |
| 王丽      | 数字电路        |        85 |
| 王芳      | 计算机导论      |        88 |
| 王芳      | 操作系统        |        75 |
| 王芳      | 数字电路        |        79 |
| 赵铁柱    | 计算机导论      |        76 |
| 赵铁柱    | 数字电路        |        81 |
+-----------+-----------------+-----------+

14、查询所有学生的s_name,c_no,sc_degree列

mysql> select s_name,c_no,sc_degree from student,score 
	-> where student.s_no = score.s_no;
+-----------+-------+-----------+
| s_name    | c_no  | sc_degree |
+-----------+-------+-----------+
| 王丽      | 3-105 |        92 |
| 王丽      | 3-245 |        86 |
| 王丽      | 6-166 |        85 |
| 王芳      | 3-105 |        88 |
| 王芳      | 3-245 |        75 |
| 王芳      | 6-166 |        79 |
| 赵铁柱    | 3-105 |        76 |
| 赵铁柱    | 3-245 |        68 |
| 赵铁柱    | 6-166 |        81 |
+-----------+-------+-----------+

15、查询所有学生的s_no，c_name，sc_degree列

mysql> select s_no,c_name,sc_degree from course,score //多表关联，c_name字段来自于course表
    -> where course.c_no = score.c_no;
+------+-----------------+-----------+
| s_no | c_name          | sc_degree |
+------+-----------------+-----------+
| 103  | 计算机导论      |        92 |
| 105  | 计算机导论      |        88 |
| 109  | 计算机导论      |        76 |
| 103  | 操作系统        |        86 |
| 105  | 操作系统        |        75 |
| 109  | 操作系统        |        68 |
| 103  | 数字电路        |        85 |
| 105  | 数字电路        |        79 |
| 109  | 数字电路        |        81 |
+------+-----------------+-----------+

16、查询所有学生s_name，c_name，sc_degree列

mysql> select s_no,c_name,sc_degree from course,score //多表关联，c_name字段来自于course表
    -> where course.c_no = score.c_no;
+------+-----------------+-----------+
| s_no | c_name          | sc_degree |
+------+-----------------+-----------+
| 103  | 计算机导论      |        92 |
| 105  | 计算机导论      |        88 |
| 109  | 计算机导论      |        76 |
| 103  | 操作系统        |        86 |
| 105  | 操作系统        |        75 |
| 109  | 操作系统        |        68 |
| 103  | 数字电路        |        85 |
| 105  | 数字电路        |        79 |
| 109  | 数字电路        |        81 |
+------+-----------------+-----------+

17、查询95031班学生每门课的平均分

mysql> select s_no,c_name,sc_degree from course,score //多表关联，c_name字段来自于course表
    -> where course.c_no = score.c_no;
+------+-----------------+-----------+
| s_no | c_name          | sc_degree |
+------+-----------------+-----------+
| 103  | 计算机导论      |        92 |
| 105  | 计算机导论      |        88 |
| 109  | 计算机导论      |        76 |
| 103  | 操作系统        |        86 |
| 105  | 操作系统        |        75 |
| 109  | 操作系统        |        68 |
| 103  | 数字电路        |        85 |
| 105  | 数字电路        |        79 |
| 109  | 数字电路        |        81 |
+------+-----------------+-----------+

18、查询选修3-105课程的成绩高于109号学生3-105成绩 的所有同学记录

mysql> select s_no,c_name,sc_degree from course,score //多表关联，c_name字段来自于course表
    -> where course.c_no = score.c_no;
+------+-----------------+-----------+
| s_no | c_name          | sc_degree |
+------+-----------------+-----------+
| 103  | 计算机导论      |        92 |
| 105  | 计算机导论      |        88 |
| 109  | 计算机导论      |        76 |
| 103  | 操作系统        |        86 |
| 105  | 操作系统        |        75 |
| 109  | 操作系统        |        68 |
| 103  | 数字电路        |        85 |
| 105  | 数字电路        |        79 |
| 109  | 数字电路        |        81 |
+------+-----------------+-----------+

19、查询成绩高于学号为109，课程号为3-105的成绩的所有记录

mysql> select s_no,c_name,sc_degree from course,score //多表关联，c_name字段来自于course表
    -> where course.c_no = score.c_no;
+------+-----------------+-----------+
| s_no | c_name          | sc_degree |
+------+-----------------+-----------+
| 103  | 计算机导论      |        92 |
| 105  | 计算机导论      |        88 |
| 109  | 计算机导论      |        76 |
| 103  | 操作系统        |        86 |
| 105  | 操作系统        |        75 |
| 109  | 操作系统        |        68 |
| 103  | 数字电路        |        85 |
| 105  | 数字电路        |        79 |
| 109  | 数字电路        |        81 |
+------+-----------------+-----------+

20、查询学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday

mysql> select s_no,c_name,sc_degree from course,score //多表关联，c_name字段来自于course表
    -> where course.c_no = score.c_no;
+------+-----------------+-----------+
| s_no | c_name          | sc_degree |
+------+-----------------+-----------+
| 103  | 计算机导论      |        92 |
| 105  | 计算机导论      |        88 |
| 109  | 计算机导论      |        76 |
| 103  | 操作系统        |        86 |
| 105  | 操作系统        |        75 |
| 109  | 操作系统        |        68 |
| 103  | 数字电路        |        85 |
| 105  | 数字电路        |        79 |
| 109  | 数字电路        |        81 |
+------+-----------------+-----------+

21、查询 张旭 教师任课的学生信息

mysql> select t_no from teacher where t_name = '张旭';

mysql> select c_no from course where t_no = ( select t_no from teacher where t_name ='张旭');
+-------+
| c_no  |
+-------+
| 6-166 |
+-------+
1 row in set (0.01 sec)

mysql> select * from score where c_no = (select c_no from course where t_no = (select t_no from teacher where t_name = '张旭'));
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 6-166 | 85        |
| 105  | 6-166 | 79        |
| 109  | 6-166 | 81        |
+------+-------+-----------+
3 rows in set (0.02 sec)

22、查询选修某课程的同学人数多余2人的课程

 select c_no from score group by c_no having count(*)>2;
+-------+
| c_no  |
+-------+
| 3-105 |
| 3-245 |
| 6-166 |
+-------+

23、查询95033和95031班全体学生的记录

mysql> select * from student where s_class in ('95031','95033');
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday          | s_class |
+------+--------+-------+---------------------+---------+
| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |
| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |
| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |
| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |
| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |
| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |
| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |
| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |
| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |
+------+--------+-------+---------------------+---------+
9 rows in set (0.03 sec)

24、查询存在有85分以上成绩的课程c_no

mysql> select c_no,sc_degree from score where sc_degree >85; 
+-------+-----------+
| c_no  | sc_degree |
+-------+-----------+
| 3-105 | 92        |
| 3-245 | 86        |
| 3-105 | 88        |
+-------+-----------+
3 rows in set (0.05 sec)

25、查询出计算机系教师所教课程的成绩表

mysql> select * from teacher where t_depart = '计算机系';
+------+--------+-------+---------------------+--------+----------+
| t_no | t_name | t_sex | t_birthday          | t_rof  | t_depart |
+------+--------+-------+---------------------+--------+----------+
| 804  | 李诚   | 男    | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825  | 王萍   | 女    | 1972-05-05 00:00:00 | 助教   | 计算机系 |
+------+--------+-------+---------------------+--------+----------+
2 rows in set (0.08 sec)

mysql> select * from course where t_no in (select t_no from teacher where t_depart = '计算机系');
+-------+------------+------+
| c_no  | c_name     | t_no |
+-------+------------+------+
| 3-245 | 操作系统   | 804  |
| 3-105 | 计算机导论 | 825  |
+-------+------------+------+

26、查询 计算机系 与 电子工程系 不同职称的教师的t_name 和 t_rof

mysql> select t_rof from teacher where t_depart = '电子工程系';
+-------+
| t_rof |
+-------+
| 助教  |
| 讲师  |
+-------+
mysql> select * from teacher where t_depart = '计算机系' and t_rof not in(select t_rof from teacher where t_depart = '电子工程系');
+------+--------+-------+---------------------+--------+----------+
| t_no | t_name | t_sex | t_birthday          | t_rof  | t_depart |
+------+--------+-------+---------------------+--------+----------+
| 804  | 李诚   | 男    | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
+------+--------+-------+---------------------+--------+----------+