MySQL学习笔记----查询练习

查询练习

一：查询表准备

学生表（Student）：学号、姓名、性别、出生年月日、班级
课程表（Course）：课程号、课程名称、教师编号
成绩表（Score）　：学号、课程号、成绩
教师表（Teacher）：教师编号、教师性别、教师性别、出生年月日、职称、所在部门

创建一个数据库selecttest
mysql> create database selecttest;
Query OK, 1 row affected (0.00 sec)

mysql> use selecttest;
Database changed
mysql> show tables;
Empty set (0.00 sec)

创建学生表 学生编号进项主键约束
mysql> create table student(
    ->     snumber varchar(20) primary key,
    ->    sname varchar(20) not null,
    ->     ssex varchar(20) not null,
    ->     sbirthday datetime,
    ->     class varchar(20)
    ->     );
Query OK, 0 rows affected (0.03 sec)

创建老师表，老师编号主键约束
mysql> create table teacher(
    ->    tnumber varchar(20) primary key,
    ->    tname varchar(20) not null,
    ->   tsex varchar(20) not null,
    ->     tbirthday datetime,
    ->    prof varchar(20) not null,
    ->    depart varchar(20) not null
    ->    );
Query OK, 0 rows affected (0.02 sec)

创建课程表，tnumber和老师表中tnumber一样，使用外键约束
mysql> create table course(
    ->    cnumber varchar(20) primary key,
    ->     cname varchar(20) not null,
    ->    tnumber varchar(20) not null,
    ->    foreign key(tnumber) references teacher(tnumber));
Query OK, 0 rows affected (0.03 sec)

创建成绩表，snumber和cnumber分别进行外键约束，并且进行联合主键约束
mysql> create table score(
    ->     snumber varchar(20) not null,
    ->    cnumber varchar(20) not null,
    ->     degree decimal,
    ->      foreign key(snumber) references student(snumber),
    ->     foreign key(cnumber) references course(cnumber),
    ->     primary key(snumber,cnumber)
    ->    );
Query OK, 0 rows affected (0.02 sec)

看一下表
mysql> show tables;
+----------------------+
| Tables_in_selecttest |
+----------------------+
| course               |
| score                |
| student              |
| teacher              |
+----------------------+
4 rows in set (0.00 sec)

疯一般的插入数据啊，有点秃

mysql> select * from student;
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 105     | 关羽   | 男   | 1787-03-08 00:00:00 | 三班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 107     | 司马懿 | 男   | 4443-09-08 00:00:00 | 三班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
+---------+--------+------+---------------------+-------+
9 rows in set (0.00 sec)

mysql> select * from teacher;
+---------+--------+------+---------------------+--------+--------+
| tnumber | tname  | tsex | tbirthday           | prof   | depart |
+---------+--------+------+---------------------+--------+--------+
| 401     | 苏轼   | 男   | 1873-02-11 00:00:00 | 讲师   | 信通系 |
| 402     | 王维   | 男   | 1376-11-30 00:00:00 | 教授   | 语文系 |
| 403     | 李白   | 男   | 1073-04-04 00:00:00 | 副教授 | 数学系 |
| 404     | 李商隐 | 男   | 1890-12-11 00:00:00 | 主任   | 管理系 |
| 405     | 杜甫   | 男   | 1062-06-09 00:00:00 | 讲师   | 信通系 |
+---------+--------+------+---------------------+--------+--------+
5 rows in set (0.00 sec)

mysql> select * from course;
+---------+------------+---------+
| cnumber | cname      | tnumber |
+---------+------------+---------+
| 801     | 计算机理论 | 404     |
| 802     | 通信原理   | 403     |
| 803     | FPGA       | 405     |
| 804     | 高数       | 401     |
+---------+------------+---------+
4 rows in set (0.00 sec)

mysql> desc score;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| snumber | varchar(20)   | NO   | PRI | NULL    |       |
| cnumber | varchar(20)   | NO   | PRI | NULL    |       |
| degree  | decimal(10,0) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)


mysql> select * from score;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 101     | 801     |     49 |
| 102     | 801     |     89 |
| 102     | 802     |     93 |
| 103     | 803     |     60 |
| 104     | 804     |     99 |
| 106     | 804     |     88 |
| 109     | 804     |     78 |
+---------+---------+--------+
7 rows in set (0.00 sec)

二：MySQL查询练习

1.查询表中所有记录

mysql> select * from student;
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 105     | 关羽   | 男   | 1787-03-08 00:00:00 | 三班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 107     | 司马懿 | 男   | 4443-09-08 00:00:00 | 三班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
+---------+--------+------+---------------------+-------+
9 rows in set (0.02 sec)

2.查询指定字段，查列（逗号隔开）

mysql> select snumber,sname,class from student;
+---------+--------+-------+
| snumber | sname  | class |
+---------+--------+-------+
| 101     | 张飞   | 一班  |
| 102     | 曹操   | 一班  |
| 103     | 孙权   | 二班  |
| 104     | 刘备   | 一班  |
| 105     | 关羽   | 三班  |
| 106     | 孙尚香 | 一班  |
| 107     | 司马懿 | 三班  |
| 108     | 诸葛亮 | 一班  |
| 109     | 孙策   | 二班  |
+---------+--------+-------+
9 rows in set (0.00 sec)

3.查询不重复列，就是排重

• distinct 去重

mysql> select distinct depart from teacher;
+--------+
| depart |
+--------+
| 信通系 |
| 语文系 |
| 数学系 |
| 管理系 |
+--------+
4 rows in set (0.00 sec)

可以看到两个通信系的字段已经被去重称为一个了
mysql> select * from teacher;
+---------+--------+------+---------------------+--------+--------+
| tnumber | tname  | tsex | tbirthday           | prof   | depart |
+---------+--------+------+---------------------+--------+--------+
| 401     | 苏轼   | 男   | 1873-02-11 00:00:00 | 讲师   | 信通系 |
| 402     | 王维   | 男   | 1376-11-30 00:00:00 | 教授   | 语文系 |
| 403     | 李白   | 男   | 1073-04-04 00:00:00 | 副教授 | 数学系 |
| 404     | 李商隐 | 男   | 1890-12-11 00:00:00 | 主任   | 管理系 |
| 405     | 杜甫   | 男   | 1062-06-09 00:00:00 | 讲师   | 信通系 |
+---------+--------+------+---------------------+--------+--------+
5 rows in set (0.00 sec)

4.查询score成绩在60-80之间

查询区间：

1. between…and…（包括端点值）
2. 直接使用运算符比较 < = >

mysql> select * from score;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 101     | 801     |     49 |
| 102     | 801     |     89 |
| 102     | 802     |     93 |
| 103     | 803     |     60 |
| 104     | 804     |     99 |
| 106     | 804     |     88 |
| 109     | 804     |     78 |
+---------+---------+--------+
7 rows in set (0.00 sec)

将成绩在60-80的字段全部输出（包括60,80）
使用between....and....
mysql> select * from score where degree between 60 and 80;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 103     | 803     |     60 |
| 109     | 804     |     78 |
+---------+---------+--------+
2 rows in set (0.01 sec)

使用运算符比较，可以看到结果是一样的
mysql> select * from score where degree >= 60 and degree <80;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 103     | 803     |     60 |
| 109     | 804     |     78 |
+---------+---------+--------+
2 rows in set (0.00 sec)

5.查出成绩是60,99 ，100或者78的字段（同一个字段查询，或的关系）

• 表示或关系查询 用关键词 in
• in（ ， ，）：表示取出被查询列中属于in（）里的值的范围内
• 当没有（）中值时，不输出，如下代码中的100

mysql> select * from score where degree in (60,78,99,100);
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 103     | 803     |     60 |
| 104     | 804     |     99 |
| 109     | 804     |     78 |
+---------+---------+--------+
3 rows in set (0.00 sec)

6.查student 表中 班级为二班，或者学生性别为女的记录

（与5不同在于是不同字段查询）**

• or关键字 表示或者

可以看到班级为二班 或者 性别为女的信息均被输出
mysql> select * from student where class = '二班' or ssex = '女';
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
+---------+--------+------+---------------------+-------+
3 rows in set (0.00 sec)

7.以class字段降序查询student表中记录

mysql> select * from student order by snumber（字段） desc（降序）;

• order by 排序
• ascend 升序 asc（不写时默认升序）
• descend 降序 desc

首先当我们通过snumber进行排序时，可以看到结果是正确的
mysql> select * from student order by snumber;
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 105     | 关羽   | 男   | 1787-03-08 00:00:00 | 三班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 107     | 司马懿 | 男   | 4443-09-08 00:00:00 | 三班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
+---------+--------+------+---------------------+-------+
9 rows in set (0.00 sec)

但是当我们直接用这种方式以中文字段进行排序时，就会出问题。

此时默认是升序排序
mysql> select * from student order by class;
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
| 105     | 关羽   | 男   | 1787-03-08 00:00:00 | 三班  |
| 107     | 司马懿 | 男   | 4443-09-08 00:00:00 | 三班  |
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
+---------+--------+------+---------------------+-------+
9 rows in set (0.00 sec)

我们希望的是按字母表升序进行排序，但是此时一班，二班，三班（Y,R,S）的这个顺序明显不对。

原因： utf字符集是外国人弄的，不是按照汉语拼音的顺序排列的，因此，我们需要将要排序的字段把编码设定为GBK或者BG2312再进行排序。
解决方法： mysql中对字符的转码支持的不错，convert()函数，直接搞定。

mysql> select * from student order by convert(class using gbk);
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
| 105     | 关羽   | 男   | 1787-03-08 00:00:00 | 三班  |
| 107     | 司马懿 | 男   | 4443-09-08 00:00:00 | 三班  |
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
+---------+--------+------+---------------------+-------+
9 rows in set (0.00 sec)

可以看到此时班级排序（R,S,Y）满足字母表升序排列。
再用人名排序看一下，比较明显

未使用convert方法进行转码时：
mysql> select * from student order by sname desc;
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 107     | 司马懿 | 男   | 4443-09-08 00:00:00 | 三班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 105     | 关羽   | 男   | 1787-03-08 00:00:00 | 三班  |
+---------+--------+------+---------------------+-------+
9 rows in set (0.00 sec)


使用convert方法进行转码，按字母表降序排列
mysql> select * from student order by convert (sname using gbk) desc;
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
| 107     | 司马懿 | 男   | 4443-09-08 00:00:00 | 三班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 105     | 关羽   | 男   | 1787-03-08 00:00:00 | 三班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
+---------+--------+------+---------------------+-------+
9 rows in set (0.00 sec)

可参考：https://www.cnblogs.com/ME-WE/p/12433588.html

8.以cnumber升序，degree降序排列查询sore表中的记录

select * from score order by cnumber asc,degree desc;
（进行多重排序时使用逗号隔开）

• cnumber升序排列，若cnumber相同则按degree降序排列

mysql> select * from score order by cnumber asc,degree desc;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 102     | 801     |     89 |
| 101     | 801     |     49 |
| 102     | 802     |     93 |
| 103     | 803     |     60 |
| 104     | 804     |     99 |
| 106     | 804     |     88 |
| 109     | 804     |     78 |
+---------+---------+--------+
7 rows in set (0.01 sec)

9.查询一班的学生人数

方法：count（） 统计

mysql> select count(*) from student where class = '一班';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

看一眼，确实5个
mysql> select * from student where class = '一班';
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
+---------+--------+------+---------------------+-------+
5 rows in set (0.00 sec)

10.成绩表中最高分的学生学号以及课程号

两种方法：
方法1：子查询
先看一下两条sql语句查询，先找到最高分，再根据最高分找到其对应的学生号和课程号

mysql> select snumber,max(degree) from score;
+---------+-------------+
| snumber | max(degree) |
+---------+-------------+
| 101     |          99 |
+---------+-------------+
1 row in set (0.00 sec)

mysql> select snumber,cnumber from score where degree = '99';
+---------+---------+
| snumber | cnumber |
+---------+---------+
| 104     | 804     |
+---------+---------+
1 row in set (0.00 sec)

可以将两条sql语句合并在一起，进行子查询。

可以看到结果是一样的
mysql> select snumber,cnumber from score where degree = (select max(degree) from score);
+---------+---------+
| snumber | cnumber |
+---------+---------+
| 104     | 804     |
+---------+---------+
1 row in set (0.01 sec)

方法二：先进行排序，在查找最首，或最末的记录
limit（0,1）：表示从0开始查找1条
limit：首字母为起始位置，第二个字母为查找条数

但是一般不使用排序法，因为当最高分有两条时，使用这样的排序法只会输出一条的最高分

mysql> select snumber,degree from score order by degree desc limit 0,1;
+---------+--------+
| snumber | degree |
+---------+--------+
| 104     |     99 |
+---------+--------+
1 row in set (0.00 sec)

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

求平均值方法：avg（）

查询所有课的平均分
mysql> select cnumber,avg(degree) from score ;
+---------+-------------+
| cnumber | avg(degree) |
+---------+-------------+
| 801     |     79.4286 |
+---------+-------------+
1 row in set (0.00 sec)

查询课程号为803,804的平均分
mysql> select cnumber,avg(degree) from score where cnumber = '804';
+---------+-------------+
| cnumber | avg(degree) |
+---------+-------------+
| 804     |     88.3333 |
+---------+-------------+
1 row in set (0.01 sec)

mysql> select cnumber,avg(degree) from score where cnumber = '803';
+---------+-------------+
| cnumber | avg(degree) |
+---------+-------------+
| 803     |     60.0000 |
+---------+-------------+
1 row in set (0.00 sec)

我们这里要进行每门课的平均成绩查询，语句可以一条条写，但是如何在一条sql语句中查询每门课的平均成绩？

分组查询：group by
先根据group by后面的字段cnumber进行分组，再按组算出avg（degree），最后输出cnumber，avg的记录

mysql> select cnumber ,avg(degree) from score group by cnumber;
+---------+-------------+
| cnumber | avg(degree) |
+---------+-------------+
| 801     |     69.0000 |
| 802     |     93.0000 |
| 803     |     60.0000 |
| 804     |     88.3333 |
+---------+-------------+
4 rows in set (0.00 sec)

12.查询score表中至少有 两名学生选修 并以 8开头 的课程平均成绩

平均成绩：avg（）
分组查询;group by
至少有：having count（cnumber）>=
以8开头：like ‘3%’；

• 模糊匹配关键词：like
• 3%：以3开头，%是通配符

条件较多一个个看，先看一下score表

mysql> select * from score;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 101     | 801     |     49 |
| 102     | 801     |     89 |
| 102     | 802     |     93 |
| 103     | 803     |     60 |
| 104     | 804     |     99 |
| 106     | 804     |     88 |
| 109     | 804     |     78 |
+---------+---------+--------+
7 rows in set (0.00 sec)

根据cnumber进行分组

mysql> select * from score group by cnumber;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 101     | 801     |     49 |
| 102     | 802     |     93 |
| 103     | 803     |     60 |
| 104     | 804     |     99 |
+---------+---------+--------+
4 rows in set (0.00 sec)

加上限制条件 至少有两名同学选修，也就是cnumber >=2
使用having关键词配合count（）求和方法使用

mysql> select cnumber from score group by cnumber having count(cnumber) >= 2;
+---------+
| cnumber |
+---------+
| 801     |
| 804     |
+---------+
2 rows in set (0.00 sec)

再加上以8开头的修饰，使用模糊匹配关键词：like
8%：以8开头，%是通配符

mysql> select cnumber from score group by cnumber having count(cnumber) >= 2 and cnumber like '8%';
+---------+
| cnumber |
+---------+
| 801     |
| 804     |
+---------+
2 rows in set (0.00 sec)

最后加上平均分

mysql> select cnumber,avg(degree) from score group by cnumber having count(cnumber) >= 2 and cnumber like '8%';
+---------+-------------+
| cnumber | avg(degree) |
+---------+-------------+
| 801     |     69.0000 |
| 804     |     88.3333 |
+---------+-------------+
2 rows in set (0.00 sec)

很完美~

13.查询成绩大于70，小于90的列

between …and…是包含端点的，相当于>=,<=

mysql> select * from score where degree between 70 and 90;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 102     | 801     |     89 |
| 106     | 804     |     88 |
| 109     | 804     |     78 |
+---------+---------+--------+
3 rows in set (0.03 sec)

mysql> select * from score where degree > 70 and degree < 90;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 102     | 801     |     89 |
| 106     | 804     |     88 |
| 109     | 804     |     78 |
+---------+---------+--------+
3 rows in set (0.00 sec)

14.查询所有学生的sname，cnumber，和degree列

涉及到多表查询，sname是student表的，cnumber和degree都是score表中的

先看一下各表中都有啥
mysql> select * from student;
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 105     | 关羽   | 男   | 1787-03-08 00:00:00 | 三班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 107     | 司马懿 | 男   | 4443-09-08 00:00:00 | 三班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
+---------+--------+------+---------------------+-------+
9 rows in set (0.01 sec)

mysql> select * from score;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 101     | 801     |     49 |
| 102     | 801     |     89 |
| 102     | 802     |     93 |
| 103     | 803     |     60 |
| 104     | 804     |     99 |
| 106     | 804     |     88 |
| 109     | 804     |     78 |
+---------+---------+--------+
7 rows in set (0.00 sec)

可以发现两表中的snumber相同，所以可以通过snumber进行两个表的联系，通过：
where student.snumber=score.snumber;可以将两个表的字段连接到一起，相同字段相等进行两表的联系

mysql> select sname,cnumber,degree from student,score where student.snumber=score.snumber;
+--------+---------+--------+
| sname  | cnumber | degree |
+--------+---------+--------+
| 张飞   | 801     |     49 |
| 曹操   | 801     |     89 |
| 曹操   | 802     |     93 |
| 孙权   | 803     |     60 |
| 刘备   | 804     |     99 |
| 孙尚香 | 804     |     88 |
| 孙策   | 804     |     78 |
+--------+---------+--------+
7 rows in set (0.01 sec)

15.查询所有学生的snumber，cname，degree

与14题相差不多，先看一下表

mysql> select * from course;
+---------+------------+---------+
| cnumber | cname      | tnumber |
+---------+------------+---------+
| 801     | 计算机理论 | 404     |
| 802     | 通信原理   | 403     |
| 803     | FPGA       | 405     |
| 804     | 高数       | 401     |
+---------+------------+---------+
4 rows in set (0.01 sec)

可以看到snumber在学生表和成绩表中都有，我们为了简单方便此处选择成绩表中的
cname在课程表中
degree在成绩表中
通过cnumber将课程表和成绩表联合查询
course.cnumber = score.cnumber;

mysql> select snumber,cname,degree from score,course where course.cnumber = score.cnumber;
+---------+------------+--------+
| snumber | cname      | degree |
+---------+------------+--------+
| 101     | 计算机理论 |     49 |
| 102     | 计算机理论 |     89 |
| 102     | 通信原理   |     93 |
| 103     | FPGA       |     60 |
| 104     | 高数       |     99 |
| 106     | 高数       |     88 |
| 109     | 高数       |     78 |
+---------+------------+--------+
7 rows in set (0.00 sec)

16 查询所有学生的sname，cname，degree。（三表查询）

通过多个共同字段进行连接
where student.snumber = score.snumber and score.cnumber = course.cnumber;
这里用and连接，不是逗号不是逗号不是逗号！

mysql> select sname,cname,degree from student,course,score where student.snumber = score.snumber and score.cnumber = course.cnumber;
+--------+------------+--------+
| sname  | cname      | degree |
+--------+------------+--------+
| 张飞   | 计算机理论 |     49 |
| 曹操   | 计算机理论 |     89 |
| 曹操   | 通信原理   |     93 |
| 孙权   | FPGA       |     60 |
| 刘备   | 高数       |     99 |
| 孙尚香 | 高数       |     88 |
| 孙策   | 高数       |     78 |
+--------+------------+--------+
7 rows in set (0.00 sec)

17.查询一班所有学生的每门课的成绩的平均分（子查询）

第一步：先找谁是一班的，知道snumber

mysql> select * from student where class = '一班';
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
+---------+--------+------+---------------------+-------+
5 rows in set (0.00 sec)

第二步：通过snumber分组找cnumber，再求平均分。

这里是错误的，因为没有对课程进行分组查找，题目要求的是找出每门课的平均分，这里变成了所有课的平均分，所以我们要通过课程编号进行分组查找
mysql> select cnumber,avg(degree) from score where snumber in (select snumber from student where class = '一班');
+---------+-------------+
| cnumber | avg(degree) |
+---------+-------------+
| 801     |     83.6000 |
+---------+-------------+
1 row in set (0.00 sec)


正确：
```sql
mysql> select cnumber,avg(degree) from score where snumber in (select snumber from student where class = '一班') group by cnumber;
+---------+-------------+
| cnumber | avg(degree) |
+---------+-------------+
| 801     |     69.0000 |
| 802     |     93.0000 |
| 804     |     93.5000 |
+---------+-------------+
3 rows in set (0.00 sec)

18.查选修804这门课的学生中，该课成绩高于106号同学该课成绩的人的记录；

第一步：先找106号同学的804号成绩是多少
第二步：查找其他人的信息，对于成绩的限制是高于106的成绩，对于课程号的限制是课程号为804
嵌套成为一句sql语句

第一步：106号同学的804号成绩
mysql> select degree from score where cnumber = '804' and snumber = '106';
+--------+
| degree |
+--------+
|     88 |
+--------+
1 row in set (0.00 sec)


第二步：查找其他人的信息，对于成绩的限制是高于106的成绩，对于课程号的限制是课程号为804
```sql
mysql> select * from score where degree > (select degree from score where cnumber = '804' and snumber = '106') and cnumber = '804';
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 104     | 804     |     99 |
+---------+---------+--------+
1 row in set (0.00 sec)

19、查询所有课程的成绩高于804课程中成绩高于106号同学成绩的同学记录

与18相同，只是不对课程进行限制，只需要把18语句中对于课程限制的部分删掉即可

106同学的804成绩是88分，可以看到下面结果正确
mysql> select * from score where degree > (select degree from score where cnumber = '804' and snumber = '106') ;
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 102     | 801     |     89 |
| 102     | 802     |     93 |
| 104     | 804     |     99 |
+---------+---------+--------+
3 rows in set (0.00 sec)

20.查询学号为109、101 的同学同年出生的所有学生的snumber、sname和sbirthday

看一眼student表：

mysql> select * from student;
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 105     | 关羽   | 男   | 1787-03-08 00:00:00 | 三班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 107     | 司马懿 | 男   | 4443-09-08 00:00:00 | 三班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
+---------+--------+------+---------------------+-------+
9 rows in set (0.00 sec)

第一步：先找到101,109同学的出生年份
使用函数：year（）查找 日期里面的年份

mysql> select year(sbirthday) from student where snumber in (101,109);
+-----------------+
| year(sbirthday) |
+-----------------+
|            1604 |
|             235 |
+-----------------+
2 rows in set (0.00 sec)

第二步：查找出生年份为1604,235的人的信息
注意这里要用in，因为年份是两个值，如果是一个的话可以用where，但是要习惯用in

mysql> select snumber,sname,sbirthday from student where year(sbirthday) in(1604,0235);
+---------+-------+---------------------+
| snumber | sname | sbirthday           |
+---------+-------+---------------------+
| 101     | 张飞  | 1604-04-16 00:00:00 |
| 109     | 孙策  | 0235-07-08 00:00:00 |
+---------+-------+---------------------+
2 rows in set (0.00 sec)

拼接sql语句，通过子查询嵌套，可以看到结果是一样的。

mysql> select snumber,sname,sbirthday from student where year(sbirthday) in(select year(sbirthday) from student where snumber in (101,109));
+---------+-------+---------------------+
| snumber | sname | sbirthday           |
+---------+-------+---------------------+
| 101     | 张飞  | 1604-04-16 00:00:00 |
| 109     | 孙策  | 0235-07-08 00:00:00 |
+---------+-------+---------------------+
2 rows in set (0.01 sec)

21.查询’李商隐’老师所任课程的学生成绩

根据tname找到tnumber
mysql> select tnumber from teacher where tname = '李商隐';
+---------+
| tnumber |
+---------+
| 404     |
+---------+
1 row in set (0.00 sec)


根据tnumber找到cnumber
mysql> select cnumber from course where tnumber = '404';
+---------+
| cnumber |
+---------+
| 801     |
+---------+
1 row in set (0.00 sec)
根据cnumber找到所有该课学生信息
mysql> select * from score where cnumber= '801';
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 101     | 801     |     49 |
| 102     | 801     |     89 |
+---------+---------+--------+

上三条语句拼接成一条sql语句进行套娃，可以看到结果是一样的
mysql> select * from score where cnumber = (select cnumber from course where tnumber =(select tnumber from teacher where tname='李商隐'));
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 101     | 801     |     49 |
| 102     | 801     |     89 |
+---------+---------+--------+
2 rows in set (0.00 sec)

22.查询某门课程被选的学生超过2人的任课老师姓名

和21类似，嵌套

mysql> select cnumber from score group by cnumber having count(*)>2;
+---------+
| cnumber |
+---------+
| 804     |
+---------+
1 row in set (0.00 sec)

mysql> select cnumber from score group by cnumber having count(cnumber) >2;
+---------+
| cnumber |
+---------+
| 804     |
+---------+
1 row in set (0.00 sec)

mysql> select tnumber from course where cnumber = '804';
+---------+
| tnumber |
+---------+
| 401     |
+---------+
1 row in set (0.00 sec)

mysql> select tname from teacher where tnumber = '401';
+-------+
| tname |
+-------+
| 苏轼  |
+-------+
1 row in set (0.00 sec)

一条sql语句：
mysql> select tname from teacher where tnumber = (select tnumber from course where cnumber =(select cnumber from score group by cnumber having count(cnumber) >2));
+-------+
| tname |
+-------+
| 苏轼  |
+-------+
1 row in set (0.00 sec)

23.查询一班二班全体学生记录

用in进行多种匹配查询

mysql> select * from student where class in ('一班','二班');
+---------+--------+------+---------------------+-------+
| snumber | sname  | ssex | sbirthday           | class |
+---------+--------+------+---------------------+-------+
| 101     | 张飞   | 男   | 1604-04-16 00:00:00 | 一班  |
| 102     | 曹操   | 男   | 1397-12-08 00:00:00 | 一班  |
| 103     | 孙权   | 男   | 1237-04-08 00:00:00 | 二班  |
| 104     | 刘备   | 男   | 1587-07-08 00:00:00 | 一班  |
| 106     | 孙尚香 | 女   | 1234-11-08 00:00:00 | 一班  |
| 108     | 诸葛亮 | 男   | 0890-08-08 00:00:00 | 一班  |
| 109     | 孙策   | 男   | 0235-07-08 00:00:00 | 二班  |
+---------+--------+------+---------------------+-------+
7 rows in set (0.00 sec)

24 .查询存在85分以上成绩的课程号

很简单这个，因为要查的数据都在一个表里（score）

当然也可以使用>=
mysql> select cnumber from score where degree >=85;
+---------+
| cnumber |
+---------+
| 801     |
| 802     |
| 804     |
| 804     |
+---------+
4 rows in set (0.00 sec)

25.查询信通系教师所教课程的成绩表

条件嵌套，参考21,22

mysql> select * from score where cnumber in (select cnumber from course where tnumber in (select tnumber from teacher where depart='信通系'));
+---------+---------+--------+
| snumber | cnumber | degree |
+---------+---------+--------+
| 104     | 804     |     99 |
| 106     | 804     |     88 |
| 109     | 804     |     78 |
| 103     | 803     |     60 |
+---------+---------+--------+
4 rows in set (0.00 sec)

复杂的查询就一步步写，然后将步1的结果当做下一步的条件进行嵌套，依次进行查询

在使用多表查询时，两表之间的相互关联，不一定与创建表时的外键有关，不一定需要主外键关系，字段匹配就OK