mysql查询学习笔记【五】【any，all，复制表查询】

一.其中任意一个

查询课程‘3-105’成绩比课程‘3-245’的任意一条成绩高的成绩记录
其实这个题用min也可以做，但是这里重点学习任意一个。
任意一个：any
所以就是：

select cno,sno,degree from score where cno=‘3-105’
and degree >
any(select degree from score where cno=‘3-245’)
order by degree desc;

查询结果：

mysql> select cno,sno,degree from score
    -> where cno='3-105'
    -> and degree >
    -> any(select degree from score where cno='3-245')
    -> order by degree desc;
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 103 |     92 |
| 3-105 | 102 |     91 |
| 3-105 | 101 |     90 |
| 3-105 | 104 |     89 |
| 3-105 | 105 |     88 |
| 3-105 | 109 |     76 |
+-------+-----+--------+

二.all 所有

查询课程‘3-105’成绩比课程‘3-245’的所有成绩高的成绩记录
其实和上一题很想，这个题用max也可以写的
我们只需要把查询出来的‘3-245’的成绩筛选条件改为所有
也就是all(select degree from score where cno=‘3-245’)
全部的语句：

select cno,sno,degree from score
where cno=‘3-105’
and degree >
all(select degree from score where cno=‘3-245’);

查询结果：

mysql> select cno,sno,degree from score
    -> where cno='3-105'
    -> and degree >
    -> all(select degree from score where cno='3-245');
+-------+-----+--------+
| cno   | sno | degree |
+-------+-----+--------+
| 3-105 | 101 |     90 |
| 3-105 | 102 |     91 |
| 3-105 | 103 |     92 |
| 3-105 | 104 |     89 |
| 3-105 | 105 |     88 |
+-------+-----+--------+

三. as别名和union连接

查询所有教师和学生的名字，性别和生日
首先我们可以在教师表中查询所有老师的名字性别和生日：

ysql> select tname,tsex,tbirthday from teacher;
+-------+------+---------------------+
| tname | tsex | tbirthday           |
+-------+------+---------------------+
| 李诚  | 男   | 1958-12-02 00:00:00 |
| 王萍  | 女   | 1972-05-05 00:00:00 |
| 刘冰  | 女   | 1977-08-14 00:00:00 |
| 张旭  | 男   | 1969-03-12 00:00:00 |
+-------+------+---------------------+

然后我们可以再学生表中查询所有学生的名字，性别和生日：

mysql> select sname,ssex,sbirthday from students;
+--------+------+---------------------+
| sname  | ssex | sbirthday           |
+--------+------+---------------------+
| 曾华   | 男   | 1977-09-01 00:00:00 |
| 匡明   | 男   | 1975-10-02 00:00:00 |
| 王丽   | 女   | 1976-01-23 00:00:00 |
| 李军   | 男   | 1975-02-10 00:00:00 |
| 王芳   | 女   | 1974-06-03 00:00:00 |
| 陆君   | 男   | 1974-06-03 00:00:00 |
| 王尼玛 | 男   | 1976-02-20 00:00:00 |
| 张全蛋 | 男   | 1975-02-10 00:00:00 |
| 赵铁柱 | 男   | 1974-06-03 00:00:00 |
+--------+------+---------------------+

现在我们有了学生和教师两张表，我们要怎么把这两张表连接起来呢？
如果要连接两个查询结果，需要用到union关键字：

select tname,tsex,tbirthday from teacher
union
select sname,ssex,sbirthday from students;

看一下结果:

mysql> select tname,tsex,tbirthday from teacher
    -> union
    -> select sname,ssex,sbirthday from students;
+--------+------+---------------------+
| tname  | tsex | tbirthday           |
+--------+------+---------------------+
| 李诚   | 男   | 1958-12-02 00:00:00 |
| 王萍   | 女   | 1972-05-05 00:00:00 |
| 刘冰   | 女   | 1977-08-14 00:00:00 |
| 张旭   | 男   | 1969-03-12 00:00:00 |
| 曾华   | 男   | 1977-09-01 00:00:00 |
| 匡明   | 男   | 1975-10-02 00:00:00 |
| 王丽   | 女   | 1976-01-23 00:00:00 |
| 李军   | 男   | 1975-02-10 00:00:00 |
| 王芳   | 女   | 1974-06-03 00:00:00 |
| 陆君   | 男   | 1974-06-03 00:00:00 |
| 王尼玛 | 男   | 1976-02-20 00:00:00 |
| 张全蛋 | 男   | 1975-02-10 00:00:00 |
| 赵铁柱 | 男   | 1974-06-03 00:00:00 |
+--------+------+---------------------+

但是我们可以看到，在每一列的第一行也就是字段名的地方写的是tname，tsex，tbirthday但是我们的表中还有学生啊，这个时候我们把要显示的字段名字给取一个别名，用as关键字：

select tname as name,tsex as sex,tbirthday as birthday from teacher
union
select sname,ssex,sbirthday from students;

第二个查询就不需要再写了，看一下查询结果：

mysql> select tname as name,tsex as sex,tbirthday as birthday from teacher
    -> union
    -> select sname,ssex,sbirthday from students;
+--------+-----+---------------------+
| name   | sex | birthday            |
+--------+-----+---------------------+
| 李诚   | 男  | 1958-12-02 00:00:00 |
| 王萍   | 女  | 1972-05-05 00:00:00 |
| 刘冰   | 女  | 1977-08-14 00:00:00 |
| 张旭   | 男  | 1969-03-12 00:00:00 |
| 曾华   | 男  | 1977-09-01 00:00:00 |
| 匡明   | 男  | 1975-10-02 00:00:00 |
| 王丽   | 女  | 1976-01-23 00:00:00 |
| 李军   | 男  | 1975-02-10 00:00:00 |
| 王芳   | 女  | 1974-06-03 00:00:00 |
| 陆君   | 男  | 1974-06-03 00:00:00 |
| 王尼玛 | 男  | 1976-02-20 00:00:00 |
| 张全蛋 | 男  | 1975-02-10 00:00:00 |
| 赵铁柱 | 男  | 1974-06-03 00:00:00 |
+--------+-----+---------------------+

四. 复制表做查询

查询每门课程中比该课程平均分低的成绩记录
那我们先查询一下课程表看一下：

mysql> select * from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

如我我们把这个表复制一下，我们分别叫这两个表为A表和B表：

   		A表 								B表
+-----+-------+--------+			+-----+-------+--------+
| sno | cno   | degree |			| sno | cno   | degree |
+-----+-------+--------+			+-----+-------+--------+
| 101 | 3-105 |     90 |			| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |			| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |			| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |			| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |			| 103 | 6-166 |     85 |
| 104 | 3-105 |     89 |			| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |			| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |			| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |			| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |			| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |			| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |			| 109 | 6-166 |     81 |
+-----+-------+--------+			+-----+-------+--------+

那么我们拿‘第一条记录| 101 | 3-105 | 90 |为例：
我们只需要算出B表中‘3-105’的平均分，看看第一条记录的degree是不是低于这个平均分的就可以了。
那么其他的记录呢？
我们做一下推广就是，拿A表的记录x，然后在B表中计算和记录x相同课程的课程平均分和记录x中的degree相比较就可以了，那接下来就是写sql语句了：

select * from score A where degree < (select avg(degree) from score B where A.cno=B.cno);

可能比较难理解这个写法，要稍微多看两遍。
看一下查询结果：

mysql> select * from score A where degree < (select avg(degree) from score B where A.cno=B.cno);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+