记录 MySQL的学习过程（8）复杂查询 别名

union union all

多个结果集合并查询的功能

这2条语句的效果是一样的

select * from City where CountryCode='CHN' or CountryCode='USA'
select * from City where CountryCode='CHN' union all select * from City where CountryCode='USA'

union 和 unionall 的区别是，当查询的结果有重复的列，unionall不去重，union 去重复

多表连接查询

a.查询世界上小于100人的城市，所在地国家名，国土面积，城市名，人口数
因为数据分散在几张表中，所以需要多表查询

如果使用单表查询则

select * from City where population < 100

select * from Country where code ='PCN'

使用多表查询的话，则需要先找到2个表之间的关联列

city表中的CountryCode 和 country表中的Code 为关联列

书写列时，则使用表名.列名去加以区分

最后使用join on 连接2个表

select Country.name,Country.SurfaceArea,City.name,City.Population from City join Country on City.CountryCode = Country.code where City.population < 100;

+----------+-------------+-----------+------------+
| name | SurfaceArea | name | Population |
+----------+-------------+-----------+------------+
| Pitcairn | 49.00 | Adamstown | 42 |
+----------+-------------+-----------+------------+
1 row in set (0.00 sec)

 

如果涉及到过滤 分组 排序等条件 按照顺序写在on的后面

多张表

A
JOIN B
ON A.x=B.y
JOIN C
ON B.m=C.n

有点类似于 a表和b表通过某一列关联以后形成一张表，然后再找到和c表关联的列，最后形成一张大表

性能问题，要把数据量小的表放在join的左边（因为mysql会把左边的表不走索引去和右面的表去关联），这样会提高性能，当然，如果join右边的表很大还没有索引，依然会变得很慢

什么是驱动表，就是join最左边的那张表，又称为父表，其余的表则为子表，这里要注意的是，子表的关联列要是主键或者唯一键，至少建立一个索引，这样会提高性能。

练习题

school的表关系如下：

student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno： 教师编号
tname：教师名字

course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号

score ：成绩表
sno： 学号
cno： 课程编号
score：成绩

 

1.查询zhang3学习了几门课

select student.sname,count(sc.cno) from student join sc on student.sno = sc.sno where student.sname='zhang3';
+--------+---------------+
| sname | count(sc.cno) |
+--------+---------------+
| zhang3 | 2 |
+--------+---------------+

注意：如果有多个不同id的张三，为了严谨起见，可以在最后加上group by 加以区分

2.查询zhang3,学习的课程名称有哪些?

select student.sname,sc.cno,course.cname from student join sc on student.sno=sc.sno join course on sc.cno = course.cno where student.sname ='zhang3';
+--------+------+--------+
| sname | cno | cname |
+--------+------+--------+
| zhang3 | 1001 | linux |
| zhang3 | 1002 | python |
+--------+------+--------+

3.查询oldguo老师教的学生名以及学生个数

select student.sno,student.sname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where tname='oldguo';
+-----+---------+
| sno | sname |
+-----+---------+
| 2 | zhang4 |
| 3 | li4 |
| 4 | wang5 |
| 5 | zh4 |
| 6 | zhao4 |
| 7 | ma6 |
| 9 | oldgirl |
| 10 | oldp |
+-----+---------+

上面的是统计出都是哪些学生，下面通过group_concat把他们拼到一起

select teacher.tname,GROUP_CONCAT(student.sname),count(student.sno) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where tname='oldguo';
+--------+---------------------------------------------+--------------------+
| tname | GROUP_CONCAT(student.sname) | count(student.sno) |
+--------+---------------------------------------------+--------------------+
| oldguo | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp | 8 |
+--------+---------------------------------------------+--------------------+

为了精确起见，可以在句尾加上 group by teacher.tname='oldguo'来区分多个叫oldguo的老师

4.查询oldguo所教课程的平均分数

select teacher.tname,avg(sc.score) from sc join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where tname='oldguo';
+--------+---------------+
| tname | avg(sc.score) |
+--------+---------------+
| oldguo | 76.7500 |
+--------+---------------+

本例中oldguo只教了一门 mysql，如果他还教别的课程，那么则需在语句的最后面加上group by sc.cno 来根据不同的课程名称来分组

5.每位老师所教课程的平均分,并按平均分排序（由大到小）

select teacher.tname,course.cname,avg(sc.score) from sc join course on sc.cno=course.cno join teacher on course.tno=teacher.tno group by course.cname order by avg(sc.score) desc ;
+--------+--------+---------------+
| tname | cname | avg(sc.score) |
+--------+--------+---------------+
| oldboy | linux | 80.6667 |
| oldguo | mysql | 76.7500 |
| hesw | python | 70.0000 |
+--------+--------+---------------+

为了严谨起见，可以在句尾加上group by teacher.tname 来区分一个老师教多门课的这种特殊情况

 

6.查询oldguo所教的不及格的学生姓名

select student.sname,course.cname,sc.score,teacher.tname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where teacher.tname='oldguo' and sc.score<60;
+-------+-------+-------+--------+
| sname | cname | score | tname |
+-------+-------+-------+--------+
| li4 | mysql | 40 | oldguo |
| zh4 | mysql | 40 | oldguo |
+-------+-------+-------+--------+

 

7.查询所有老师所教学生不及格的信息

select teacher.tname,group_concat(student.sname,sc.score) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where sc.score<60 group by teacher.tname;
+--------+--------------------------------------+
| tname | group_concat(student.sname,sc.score) |
+--------+--------------------------------------+
| hesw | zhang359 |
| oldguo | li440,zh440 |
+--------+--------------------------------------+

为了让显示效果更好，可以使用concat函数加以美化

select teacher.tname,group_concat(concat(student.sname,":",sc.score)) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where sc.score<60 group by teacher.tname;
+--------+--------------------------------------------------+
| tname | group_concat(concat(student.sname,":",sc.score)) |
+--------+--------------------------------------------------+
| hesw | zhang3:59 |
| oldguo | li4:40,zh4:40 |
+--------+--------------------------------------------------+

另外的一种写法

select student.sname,course.cname,sc.score,teacher.tname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where sc.score<60;
+--------+--------+-------+--------+
| sname | cname | score | tname |
+--------+--------+-------+--------+
| zhang3 | python | 59 | hesw |
| li4 | mysql | 40 | oldguo |
| zh4 | mysql | 40 | oldguo |
+--------+--------+-------+--------+

 

别名应用

表别名

使用别名可以大大提高工作效率，比如刚才的这一段语句如果使用别名可以写成

使用前

select student.sname,course.cname,sc.score,teacher.tname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where sc.score<60;

使用后

select s.sname,c.cname,sc.score,t.tname from student as s join sc on s.sno=sc.sno join course as c on sc.cno=c.cno join teacher as t on c.tno=t.tno where sc.score<60;

列别名

列别名是给一些列起别名，如刚才的例子

select student.sname as xueshengxingming,course.cname as kechengmingcheng ,sc.score,teacher.tname as laoshimingzi from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where sc.score<60;
+------------------+------------------+-------+--------------+
| xueshengxingming | kechengmingcheng | score | laoshimingzi |
+------------------+------------------+-------+--------------+
| zhang3 | python | 59 | hesw |
| li4 | mysql | 40 | oldguo |
| zh4 | mysql | 40 | oldguo |
+------------------+------------------+-------+--------------+

 

2者区别为 列别名只能在having order by 中可以被调用，而表别名则可以全局被调用

另外 as 关键字可以省略，但是为了看起来规范化，还是保留as比较好

 

补充一下 left join right join

现在我们在数据库中的student还有sc 这2张表中各新增了一条数据，而且这2条数据没有任何关联

   

此时我们还是用join 来查询是没有结果的，因为join只有都匹配才有数据

如果此时我们使用left join 来看看是什么结果

select student.sname,sc.score from student left join sc on student.sno=sc.sno;
+---------+-------+
| sname | score |
+---------+-------+
....
| oldgirl | 80 |
| oldp | 96 |
| maria | NULL |
+---------+-------+
18 rows in set (0.00 sec)

也就是说 left join 会返回所有的行，即使右面的表里没有匹配的行（用NULL显示）

再来看看 right join 会返回什么结果

select student.sname,sc.score from student right join sc on student.sno=sc.sno;
+---------+-------+
| sname | score |
+---------+-------+
...
| oldp | 96 |
| NULL | 90 |
+---------+-------+
18 rows in set (0.00 sec)

结果是会返回所有的行，即使左面的表里没有匹配的行（用NULL显示）

注意 mysq中没有full join 但是使用union配合left/right join的方法也可以实现这个功能

select student.sname,sc.score from student left join sc on student.sno=sc.sno union select student.sname,sc.score from student right join sc on student.sno=sc.sno;
+---------+-------+
| sname | score |
+---------+-------+
| zhang3 | 80 |
| zhang3 | 59 |
| zhang4 | 90 |
| zhang4 | 100 |
| li4 | 99 |
| li4 | 40 |
| wang5 | 79 |
| wang5 | 61 |
| wang5 | 99 |
| zh4 | 40 |
| zhao4 | 89 |
| zhao4 | 77 |
| ma6 | 67 |
| ma6 | 82 |
| oldboy | 70 |
| oldgirl | 80 |
| oldp | 96 |
| maria | NULL |
| NULL | 90 |
+---------+-------+