sql 子查询返回的值不止一个_mysql Day4 复杂查询

--definition-how-what-P.S.

--视图（存放需频繁用于不同表格的sql查询语句），使用视图时sql查询语句会创建一条随客户端关闭而自动消失的临时表
--1.创建：视图-新建，视图-刷新；运行查询，刷新
create VIEW 性别汇总(性别,人数)
AS
select 性别,人数 from student--view中列与select查询结果中列的顺序必须一一对应，但名称可不同
--2.使用：
select 性别,人数 from view 性别汇总--即对应view代替表名，即可将这个视图应用于需要性别汇总的其他表格，且原表数据更新时视图也会自动更新
--3.视图-选中对应视图-删除视图
--4.视图中不能插入子视图（效率）、数据（报错）

--子查询（查询条件需从表中获取）
  --嵌套查询，在from子句中直接定义视图，一步到位而不是先写视图再调用视图
  select 性别,人数 FROM
  (select 性别,count(distinct 学号) as 人数 from student group by 性别) as 性别汇总 --子查询结果为临时表‘性别汇总’，在外部select运行结束后临时表自动消失
  --顺序：先运行子查询得到临时表，将子查询结果作为外部查询的范围
  --子查询可放入from中作为简单对象，也可与where联合in/any/all使用构建复杂子查询，在in/any/all()内放入子查询
--eg1.每门课最低成绩及对于最低成绩获得者
  select 学号,课程号,成绩 from score where 成绩 in (select min(成绩) from score group by 课程号)
  --注意1.外部select要from score 2.select 课程号,min(成绩),学号 from score group by 课程号 中汇总时根据课程得出每科对应min，无法识别学号处理，只能随机分配
  --思路即先得出每门最低，再看成绩表中（因为汇总结果中没有学号信息）最低成绩而不是课程/课程+成绩（因为每一课程最低成绩只会有一个数，而可能有多个学号，且不需要课程号只需最低成绩值）对应学号
  --in是all的简便写法
--any=someall必须与比较运算符一起使用
--eg2.哪些学生成绩比课程0002中全部成绩中任意一个高
  select 学号,课程号,成绩 from score where 成绩 > any(select 成绩 from score where 课程号 = 0002)
--eg3.哪些学生成绩比课程0002中全部成绩中每一个高
  select 学号,课程号,成绩 from score where 成绩 > all(select 成绩 from score where 课程号 = 0002)
  select 学号,课程号,成绩 from score where 成绩 > (select max(成绩) from score where 课程号 = 0002)
--注意：
  --1.all()输出的是多行数据，不可用于处理单一变量的算术运算符 2.子查询尽量只用一层 3.使用from后插入子查询尽量命名，增强可读性

--标量子查询
--eg1.找出成绩高于平均成绩的学生
select 学号,课程号,成绩 from score
where 成绩 > (select avg(成绩) from score)
--eg2.找出成绩位于差等生（<=60）和优等生（>80）平均之间的学生学号
select 学号,课程号,成绩 from score where 成绩 in (select 成绩 where 60<成绩<=80)
select 学号,课程号,成绩 from score 
where 成绩 between
(select avg(成绩) from score where 成绩 <= 60)
and (select avg(成绩) from score where 成绩 >80)--之间的查找范围-between，这里要加avg()，平均成绩只能从表格中查出-标量子查询
--标量子查询不只可以用于where语句中，可独立作为变量放在select列名来输出多列结果，这是由于每行都是单独完整一行
select 学号,课程号,成绩,(select avg(成绩) from score) as 总平均成绩 from score
--但注意一行select中不能用可能输出多行结果的标量子查询，如select avg(成绩) from score group by 课程号

--关联子查询（通过‘关联条件’在每组内部分别进行比较得出查询结果）
select 课程号,学号,分数,该科平均分 from score where 成绩 > (select avg(成绩) from score group by 课程号)
--如何找出在各科中分别都高于该科平均分的学生
select 课程号,学号,成绩,该科平均分 from score as A where 成绩 > 
(select avg(成绩) as 该课平均分 from score as B where A.课程号 = B.课程号 group by 课程号)
--注意子查询后的select要与外部where后变量对应
--先执行子查询，由于B作为关联条件的一部分仅在子查询中有效，子查询结果为临时表，B能看到A，而A看不到B，所以A中不可添加列‘该课平均分’；再根据where来判断
--注意关联条件只在子查询里有效

--需求大白话-数学解题分析思路-翻译为sql子句
--检查错误，先查子查询是否错误，再查外部的查询语句

--各种函数
--参数-不同名称函数进行处理-返回值
--算术、字符串、日期           

待熟练：各类函数，尽量做到不要谷歌