mysql练习_MySQL练习——4

复杂查询

原先建立的数据集情况：

student（学号，姓名，出生日期，性别）

score（学号，课程号，成绩）

course（课程号，课程名称，教师号）

teacher（教师号，教师姓名）

• 视图

1.在数据库中有很多表，表中存放了实际数据，视图中存放的是SQL查询语句，当通过客户端连接到数据库开始使用视图时，会运行视图中的查询语句，从表中查找数据导出到临时表中（临时表表示当数据库与客户端断开连接时，临时表消失）。

2.创建视图

create view 视图名称（视图列名1，视图列名2，...）

as

select 查询语句

create view 按性别汇总(性别,人数) 
as
select 性别,count(*)
from student
group by 性别

注意：视图的列名与查询语句中选择的表的列名需一一对应。

3.界面操作：运行查询语句后，右键视图点击刷新就能看到出现新的视图

4.使用及作用

select 性别,人数
from 按性别汇总;

删除视图，选中视图右键删除。

作用：频繁使用某语句，可定义好视图直接使用即可。表中数据更新，视图数据同步更新。不需多余的存储位置，节省数据的储存空间

5.注意事项

避免在视图中创建视图，多重视图会降低SQL的运行性能和效率。不能往视图中插入数据，会报错。

运行结果展示：

• 子查询

1.子查询：在from查询字句中直接写定义视图的SQL查询语句。

字句as后为子查询的名称，是一次性的，不保存，语句运行完便消失。运行顺序：子查询→外部查询语句。

2.使用：

select 

子查询除了像上面一样放到from字句中，还可以放到where字句中，与in、any、all一起使用。

IN: 找出每个课程中成绩最低的学号。（下例为关联子查询）

select * 
from score A
where 成绩 in (
              select min(成绩) 
              from score B
              where B.课程号=A.课程号
              group by 课程号)
order by 课程号,学号;

ANY: 哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？(SOME相同)

select *
from score
where 成绩 > any(
                 select 成绩 
                 from score 
                 where 课程号='0002');

ALL: 哪些学生的成绩比课程0002的全部成绩里的都高呢？

select *
from score
where 成绩 > all(
                 select 成绩 
                 from score 
                 where 课程号='0002');

注意，这里的大于any和all换种说法可理解为，大于某组别中最小值和大于某组别中最大值。

但all、any返回结果不是一个数值而是一组集合。

3.作用：偶尔使用的语句，不像视图那样经常用。

4.注意事项：

a>3*all(b) ×

a/3>all(b) √

避免使用多层嵌套子查询，因为子查询层数有限制，层数越多性能越差。

as 子查询名称 可以省略，但最好标注方便查看。

运行结果展示：

• 标量子查询

1.标量子查询：只返回一行一列查询结果（单一值）的子查询。

大于平均成绩的学生学号和成绩

select 学号,成绩
from score 
where 成绩 > (
             select avg(成绩)
             from score);

查找成绩介于差生（成绩不及格)的平均值和优等生（成绩大于80)的平均值之间学生的学号和成绩

select 学号,成绩
from score
where 成绩 between (select avg(成绩) from score where 成绩<=60) 
               and (select avg(成绩) from score where 成绩>80);

标量子查询可放在select子句中

select 学号,成绩,(select avg(成绩) from score) as 平均成绩
from score;

2.作用：用于偶尔使用的语句中，因为返回的是单一的语句，所以可以和运算符连用实现复杂查询条件。

3.注意事项：不能返回多行数据（下例错误）

select 学号,成绩,(select avg(成绩) from score group by 课程号) as 平均成绩 from score;

运行结果展示：

• 关联子查询

1.关联子查询：

查找出每个课程中大于对应课程平均成绩的学生

select * from score A 
where 成绩 >(select avg(成绩) from score B where B.课程号 = A.课程号 group by 课程号)

注意：外表别名可在子查询中识别出，但子查询中别名仅在子查询中有效。

运行结果展示：

• 如何用SQL解决业务问题

哪些学生的成绩比课程0002的全部成绩里的任意一个高

全部0002的成绩（子查询）→大于任意

select * from score
where 成绩 > any (select 成绩 from score where 课程号='0002');

查找报错原因，可逐一排查

可先选中子查询语句，点击运行已选中的，看是否报错。

运行结果展示：

• 各种函数

1.汇总函数

count（列名）求某列的行数

sum（列名）求某列的数据和

avg（列名）求某列数据的平均值

min（列名）最小值

max（列名）最大值

2.算数函数

round（数值，保留小数的位数（可为负））对数据四舍五入

abs（数值）求绝对值

mod（数值）求余数

3.字符串函数

length（字符串）求字符串长度

lower（字符串）全部转换为小写

upper（字符串）全部转换为大写

concat（字符串1，字符串2）字符串拼接

replace（字符串，被替换的字符串，用什么字符串替换）替换

substring（字符串，截取起始位置，截取长度）截取

4.日期函数

current_date 当前日期 2019-09-30

current_time 当前时间 22:19:12

current_timestamp 当前日期和时间 2019-09-30 22:19:12

year（日期）、month（日期）、day（日期） 获取日期的年份、月份、天份 2019、09、30

dayname（日期）日期对应星期几 星期一

select * from student
where year(出生日期)='1990';

运行结果展示：

SQLZOO练习题 链接：

寓言：Exercise of SQLZOO​zhuanlan.zhihu.com