mysql用any查询_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 |

+-----+-------+--------+