MySQL基础操作 - 查询 - 《数据分析_学习笔记》

student表中的sname、ssex和sclass列
student表的所有记录
teacher表中所有不重复的系名
teacher表中，每个系有多少个老师
成绩在60到80之间的所有记录
成绩为85，86，88的记录
student表中，95031班级或性别为女的同学记录
student表中，95033班和95031班全体学生的记录
student表中，所有不姓王的同学记录
以sclass降序查询所有学生记录
以cno升序，degree降序查询score表的所有记录
以班号和年龄从大到小查询student表的全部记录
95031班的学生人数
学生表中，最大和最小sbirthday 日期值
存在85分以上成绩的课程编号
子查询，查询score表中最高分的学号和课程号
3-105号课程的平均分
score表中，至少有5名学生选修并且以3开头的课程的平均分
score表中，最低分大于70分，最高分小于90分的sno
所有学生的sname、cno和degree列
所有学生的sname、cname和degree列
“张旭”教师任课的学生成绩
同一个系中不同职称的教师的tname和pro
“计算机系”与“电子工程系”不同职称的教师的tname和pro
“计算机系”教师所教课程的成绩

student表中的sname、ssex和sclass列

涉及表：student
涉及字段：sname、ssex、sclass

mysql> select sname,ssex,sclass
    -> from student;
+-------+------+--------+
| sname | ssex | sclass |
+-------+------+--------+
| 李军  | 男   | 95033  |
| 陆君  | 男   | 95031  |
| 匡明  | 男   | 95031  |
| 王丽  | 女   | 95033  |
| 曾华  | 男   | 95033  |
| 王芳  | 女   | 95031  |
+-------+------+--------+
6 rows in set (0.00 sec)

student表的所有记录

涉及表：student
涉及字段：所有字段

mysql> select * from student;
+-----+-------+------+---------------------+--------+
| sno | sname | ssex | sbirthday           | sclass |
+-----+-------+------+---------------------+--------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033  |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031  |
| 105 | 匡明  | 男   | 1997-10-02 00:00:00 | 95031  |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033  |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033  |
| 109 | 王芳  | 女   | 1975-02-10 00:00:00 | 95031  |
+-----+-------+------+---------------------+--------+
6 rows in set (0.00 sec)

teacher表中所有不重复的系名

涉及表：teacher
涉及字段：depart
关键字：distinct

mysql> select distinct depart from teacher;
+------------+
| depart     |
+------------+
| 计算机系   |
| 电子工程系 |
+------------+
2 rows in set (0.00 sec)

teacher表中，每个系有多少个老师

使用 group by 按系名分组
求每组老师的数量：因为在教师表中每一行代表一名老师，所以每组老师的数量相当于每组的行数

mysql> select depart,count(*)
    -> from teacher
    -> group by depart;
+------------+----------+
| depart     | count(*) |
+------------+----------+
| 计算机系   |        2 |
| 电子工程系 |        2 |
+------------+----------+
2 rows in set (0.00 sec)

成绩在60到80之间的所有记录

查询score表中的指定记录
这些记录满足：degree在60和80之间

mysql> select * from score
    -> where degree between 60 and 80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |   64.0 |
| 105 | 3-245 |   75.0 |
| 107 | 6-166 |   79.0 |
| 108 | 3-105 |   78.0 |
| 109 | 3-105 |   76.0 |
| 109 | 3-245 |   68.0 |
+-----+-------+--------+
6 rows in set (0.00 sec)

成绩为85，86，88的记录

查询score表中的指定记录
这些记录满足：degree为85，86，88

方法一：使用关键字 in 方法二：使用关键字 or

mysql> select * from score
    -> where degree in (85,86,88);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 6-166 |   85.0 |
| 103 | 3-245 |   86.0 |
| 105 | 3-105 |   88.0 |
+-----+-------+--------+
3 rows in set (0.00 sec)
mysql> select * from score
    -> where degree=85 or degree=86 or degree=88;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 6-166 |   85.0 |
| 103 | 3-245 |   86.0 |
| 105 | 3-105 |   88.0 |
+-----+-------+--------+
3 rows in set (0.00 sec)

student表中，95031班级或性别为女的同学记录

方法一：使用关键字 or

查询student表中的指定记录
这些记录满足：sclass为’95031’，或ssex为’女’

方法二：使用关键字 union

查询sclass为’95031’的记录
查询ssex为’女’的记录

mysql> select * from student
    -> where sclass=95031 or ssex='女';
+-----+-------+------+---------------------+--------+
| sno | sname | ssex | sbirthday           | sclass |
+-----+-------+------+---------------------+--------+
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031  |
| 105 | 匡明  | 男   | 1997-10-02 00:00:00 | 95031  |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033  |
| 109 | 王芳  | 女   | 1975-02-10 00:00:00 | 95031  |
+-----+-------+------+---------------------+--------+
4 rows in set (0.00 sec)
mysql> select * from student where sclass=95031
    -> union
    -> select * from student where ssex='女';
+-----+-------+------+---------------------+--------+
| sno | sname | ssex | sbirthday           | sclass |
+-----+-------+------+---------------------+--------+
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031  |
| 105 | 匡明  | 男   | 1997-10-02 00:00:00 | 95031  |
| 109 | 王芳  | 女   | 1975-02-10 00:00:00 | 95031  |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033  |
+-----+-------+------+---------------------+--------+
4 rows in set (0.00 sec)

student表中，95033班和95031班全体学生的记录

查询student表中的指定记录
这些记录满足：sclass为’95033’，或sclass为’95031’

方法一：使用关键字 in 方法二：使用关键字 or

mysql> select * from student
    -> where sclass=95033 or sclass=95031;
+-----+-------+------+---------------------+--------+
| sno | sname | ssex | sbirthday           | sclass |
+-----+-------+------+---------------------+--------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033  |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031  |
| 105 | 匡明  | 男   | 1997-10-02 00:00:00 | 95031  |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033  |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033  |
| 109 | 王芳  | 女   | 1975-02-10 00:00:00 | 95031  |
+-----+-------+------+---------------------+--------+
6 rows in set (0.00 sec)
mysql> select * from student
    -> where sclass in (95033,95031);
+-----+-------+------+---------------------+--------+
| sno | sname | ssex | sbirthday           | sclass |
+-----+-------+------+---------------------+--------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033  |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031  |
| 105 | 匡明  | 男   | 1997-10-02 00:00:00 | 95031  |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033  |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033  |
| 109 | 王芳  | 女   | 1975-02-10 00:00:00 | 95031  |
+-----+-------+------+---------------------+--------+
6 rows in set (0.00 sec)

student表中，所有不姓王的同学记录

查询student表中的指定记录
这些记录满足：不姓王

mysql> select * from student
    -> where sname not like '王%';
+-----+-------+------+---------------------+--------+
| sno | sname | ssex | sbirthday           | sclass |
+-----+-------+------+---------------------+--------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033  |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031  |
| 105 | 匡明  | 男   | 1997-10-02 00:00:00 | 95031  |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033  |
+-----+-------+------+---------------------+--------+
4 rows in set (0.00 sec)

以sclass降序查询所有学生记录

查询student表的所有记录
使用 order by 子句对查询结果降序排序

mysql> select * from student
    -> order by sclass desc;
+-----+-------+------+---------------------+--------+
| sno | sname | ssex | sbirthday           | sclass |
+-----+-------+------+---------------------+--------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033  |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033  |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033  |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031  |
| 105 | 匡明  | 男   | 1997-10-02 00:00:00 | 95031  |
| 109 | 王芳  | 女   | 1975-02-10 00:00:00 | 95031  |
+-----+-------+------+---------------------+--------+
6 rows in set (0.00 sec)

以cno升序，degree降序查询score表的所有记录

查询成绩表的所有记录
使用 order by 子句，对查询结果按cno和degree排序

mysql> select * from score
    -> order by cno,degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 107 | 3-105 |   91.0 |
| 105 | 3-105 |   88.0 |
| 108 | 3-105 |   78.0 |
| 109 | 3-105 |   76.0 |
| 101 | 3-105 |   64.0 |
| 103 | 3-245 |   86.0 |
| 105 | 3-245 |   75.0 |
| 109 | 3-245 |   68.0 |
| 101 | 6-166 |   85.0 |
| 108 | 6-166 |   81.0 |
| 107 | 6-166 |   79.0 |
+-----+-------+--------+
12 rows in set (0.00 sec)

以班号和年龄从大到小查询student表的全部记录

查询学生表的所有记录
使用 order by 子句，对查询结果按班号和年龄降序排序
按年龄降序，等价于按出生日期升序

mysql> select * from student
    -> order by sclass desc,sbirthday;
+-----+-------+------+---------------------+--------+
| sno | sname | ssex | sbirthday           | sclass |
+-----+-------+------+---------------------+--------+
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033  |
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033  |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033  |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031  |
| 109 | 王芳  | 女   | 1975-02-10 00:00:00 | 95031  |
| 105 | 匡明  | 男   | 1997-10-02 00:00:00 | 95031  |
+-----+-------+------+---------------------+--------+
6 rows in set (0.00 sec)

95031班的学生人数

筛选符合条件的学生
求学生人数

mysql> select count(*) from student
    -> where sclass=95031;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

学生表中，最大和最小sbirthday 日期值

查询：最大日期、最小日期
select 最大日期,最小日期 from 学生表
分别求最大日期和最小日期
最大值：max(sbirthday)
最小值：min(sbirthday)

mysql> select max(sbirthday),min(sbirthday) from student;
+---------------------+---------------------+
| max(sbirthday)      | min(sbirthday)      |
+---------------------+---------------------+
| 1997-10-02 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

存在85分以上成绩的课程编号

题意：查询某个课程的编号

将成绩表按照课程编号cno分组
group by cno
分组后再筛选，符合条件：组内最高成绩大于85
having max(degree)>85
获得符合条件的组的课程编号cno
select cno from score

mysql> select distinct(cno) from score
    -> where degree>85;
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.00 sec)
mysql> select cno from score
    -> group by cno
    -> having max(degree)>85;
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.00 sec)

子查询，查询score表中最高分的学号和课程号

题意：查询某个学生的学号和课程号

查询score表中的最高分
查询这个最高分所对应的学号和课程号

mysql> select sno,cno from score
    -> where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)
mysql> select sno,cno from score
    -> having max(degree);
+-----+-------+
| sno | cno   |
+-----+-------+
| 101 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)

错误写法：

mysql> select sno,cno from score
    -> where degree=max(degree);
ERROR 1111 (HY000): Invalid use of group function

聚合函数不能用在条件语句中

3-105号课程的平均分

题意：查询某个课程的平均分

筛选符合条件的行
使用avg()函数，查询符合条件的行的平均分

mysql> select avg(degree) from score
    -> where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
|    81.50000 |
+-------------+
1 row in set (0.00 sec)

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

题意：查询某些课程的平均分

使用group by语句，按照课程编号分组
使用having子句筛选符合条件的组
使用聚合函数avg()对符合条件的组求平均值

mysql> select avg(degree) from score
    -> group by cno
    -> having count(*)>5 and cno like '3%';
+-------------+
| avg(degree) |
+-------------+
|    81.50000 |
+-------------+
1 row in set (0.00 sec)

score表中，最低分大于70分，最高分小于90分的sno

查询某些学生的学号

按学号对学生分组
使用having子句筛选符合条件的组
查询符合条件的组所对应的学号

mysql> select sno from score
    -> group by sno
    -> having max(degree)<90 and min(degree)>70;
+-----+
| sno |
+-----+
| 105 |
| 108 |
+-----+
2 rows in set (0.00 sec)

所有学生的sname、cno和degree列

方法一：涉及两张表，使用连接查询

确定数据来源：student、score
使用inner join连接student和score
student inner join score
使用on关键字指定连接条件
on student.sno=score.sno
查询所需字段
seelct student.sname,score.cno,score.degree

mysql> select student.sname,score.cno,score.degree
    -> from student inner join score
    -> on student.sno=score.sno;
+-------+-------+--------+
| sname | cno   | degree |
+-------+-------+--------+
| 李军  | 3-105 |   64.0 |
| 李军  | 6-166 |   85.0 |
| 陆君  | 3-105 |   92.0 |
| 陆君  | 3-245 |   86.0 |
| 匡明  | 3-105 |   88.0 |
| 匡明  | 3-245 |   75.0 |
| 王丽  | 3-105 |   91.0 |
| 王丽  | 6-166 |   79.0 |
| 曾华  | 3-105 |   78.0 |
| 曾华  | 6-166 |   81.0 |
| 王芳  | 3-105 |   76.0 |
| 王芳  | 3-245 |   68.0 |
+-------+-------+--------+
12 rows in set (0.00 sec)

方法二：使用where

mysql> select student.sname,score.cno,score.degree
    -> from student,score
    -> where student.sno=score.sno;
+-------+-------+--------+
| sname | cno   | degree |
+-------+-------+--------+
| 李军  | 3-105 |   64.0 |
| 李军  | 6-166 |   85.0 |
| 陆君  | 3-105 |   92.0 |
| 陆君  | 3-245 |   86.0 |
| 匡明  | 3-105 |   88.0 |
| 匡明  | 3-245 |   75.0 |
| 王丽  | 3-105 |   91.0 |
| 王丽  | 6-166 |   79.0 |
| 曾华  | 3-105 |   78.0 |
| 曾华  | 6-166 |   81.0 |
| 王芳  | 3-105 |   76.0 |
| 王芳  | 3-245 |   68.0 |
+-------+-------+--------+
12 rows in set (0.00 sec)

所有学生的sname、cname和degree列

方法一：涉及三张表，使用连接查询

确定内连接的表：student st、score sc、course c
使用inner join连接st、sc和c
student st inner join score sc inner join course c
使用on关键字指定连接条件
on st.sno=sc.sno and c.cno=sc.cno
查询所需字段
seelct st.sname,c.cname,sc.degree

mysql> select st.sname,c.cname,sc.degree
    -> from student st inner join score sc inner join course c
    -> on st.sno=sc.sno and c.cno=sc.cno;
+-------+------------+--------+
| sname | cname      | degree |
+-------+------------+--------+
| 李军  | 计算机导论 |   64.0 |
| 李军  | 数据电路   |   85.0 |
| 陆君  | 计算机导论 |   92.0 |
| 陆君  | 操作系统   |   86.0 |
| 匡明  | 计算机导论 |   88.0 |
| 匡明  | 操作系统   |   75.0 |
| 王丽  | 计算机导论 |   91.0 |
| 王丽  | 数据电路   |   79.0 |
| 曾华  | 计算机导论 |   78.0 |
| 曾华  | 数据电路   |   81.0 |
| 王芳  | 计算机导论 |   76.0 |
| 王芳  | 操作系统   |   68.0 |
+-------+------------+--------+
12 rows in set (0.00 sec)

方法二：使用where

确定内连接的表：student st、score sc、course c
使用where子句筛选符合条件的行
在where子句中指定连接条件
where st.sno=sc.sno and c.cno=sc.cno
查询所需字段
seelct st.sname,c.cname,sc.degree

mysql> select st.sname,c.cname,sc.degree
    -> from student st,course c,score sc
    -> where st.sno=sc.sno and c.cno=sc.cno;
+-------+------------+--------+
| sname | cname      | degree |
+-------+------------+--------+
| 李军  | 计算机导论 |   64.0 |
| 李军  | 数据电路   |   85.0 |
| 陆君  | 计算机导论 |   92.0 |
| 陆君  | 操作系统   |   86.0 |
| 匡明  | 计算机导论 |   88.0 |
| 匡明  | 操作系统   |   75.0 |
| 王丽  | 计算机导论 |   91.0 |
| 王丽  | 数据电路   |   79.0 |
| 曾华  | 计算机导论 |   78.0 |
| 曾华  | 数据电路   |   81.0 |
| 王芳  | 计算机导论 |   76.0 |
| 王芳  | 操作系统   |   68.0 |
+-------+------------+--------+
12 rows in set (0.00 sec)

“张旭”教师任课的学生成绩

teacher.tno ⇔ course.tno ⇒ course.cno ⇔ score.cno ⇒ score.degree
查询“张旭”教师任课的学生成绩.png

mysql> select sc.degree
    -> from teacher t inner join course c inner join score sc
    -> on t.tno=c.tno and c.cno=sc.cno
    -> where t.tname='张旭';
+--------+
| degree |
+--------+
|   85.0 |
|   79.0 |
|   81.0 |
+--------+

同一个系中不同职称的教师的tname和pro

同一张表中两两对比，使用自连接：

查询某些教师的姓名和职称
这些教师要满足一些条件：系名相同，职称不同

mysql> select distinct tname, pro from teacher where depart in ('计算机系', '电子工程系');
+-------+--------+
| tname | pro    |
+-------+--------+
| 李诚  | 副教授 |
| 王萍  | 助教   |
| 刘冰  | 助教   |
| 张旭  | 讲师   |
+-------+--------+
4 rows in set (0.00 sec)

错误写法：

mysql> select a.tname,b.pro
    -> from teacher a,teacher b
    -> where a.depart=b.depart and a.pro<>b.pro;
+-------+--------+
| tname | pro    |
+-------+--------+
| 王萍  | 副教授 |
| 李诚  | 助教   |
| 张旭  | 助教   |
| 刘冰  | 讲师   |
+-------+--------+
4 rows in set (0.01 sec)

“计算机系”与“电子工程系”不同职称的教师的tname和pro

mysql> SELECT tname,pro FROM teacher
    -> WHERE pro NOT IN 
    -> (
    -> SELECT pro FROM teacher AS temp0
    -> WHERE depart = '计算机系'
    -> AND EXISTS (SELECT pro FROM teacher AS temp1
    -> WHERE depart = '电子工程系' AND temp1.pro = temp0.pro) 
    -> );
+-------+--------+
| tname | pro    |
+-------+--------+
| 李诚  | 副教授 |
| 张旭  | 讲师   |
+-------+--------+
2 rows in set (0.00 sec)
mysql> select tname,pro from Teacher 
    -> where Depart in ('计算机系','电子工程系') and Pro not in 
    -> (select pro from Teacher where Depart='电子工程系' and Pro in 
    -> (select Pro from Teacher where Depart='计算机系'));
+-------+--------+
| tname | pro    |
+-------+--------+
| 李诚  | 副教授 |
| 张旭  | 讲师   |
+-------+--------+
2 rows in set (0.00 sec)

“计算机系”教师所教课程的成绩

方法一：连接查询

mysql> select sc.degree
    -> from teacher t inner join course c inner join score sc
    -> on t.tno=c.tno and c.cno=sc.cno
    -> where t.depart='计算机系';
+--------+
| degree |
+--------+
|   86.0 |
|   75.0 |
|   68.0 |
|   64.0 |
|   92.0 |
|   88.0 |
|   91.0 |
|   78.0 |
|   76.0 |
+--------+
9 rows in set (0.00 sec)

方法二：子查询（嵌套查询）

在teacher中，根据系名“计算机系”找出对应的教师编号tno
select tno from teacher
where depart=’计算机系’
在course中，根据教师编号tno查找这些教师对应的课程编号cno
select cno from course
where tno=(子查询1)
在score中，根据cno查找课程对应的成绩degree
select degree from score
where cno in (子查询2)

mysql> select degree from score where cno =
    -> (select cno from course where tno=(select tno from teacher where depart='计算机系'));
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select degree from score where cno in
    -> (select cno from course where tno in (select tno from teacher where depart='计算机系'));
+--------+
| degree |
+--------+
|   86.0 |
|   75.0 |
|   68.0 |
|   64.0 |
|   92.0 |
|   88.0 |
|   91.0 |
|   78.0 |
|   76.0 |
+--------+
9 rows in set (0.00 sec)

单行子查询可以使用比较运算符，但是多行子查询不能使用。