sql 减法_第四关：从零学SQL：复杂查询

一、 视图

1、 视图是什么？

数据库----------------------->连接-------------------------->客户端

表中存放的实际数据---->视图中存放的是SQL查询语句----->使用视图时，会运行视图里的 SQL查询语句创建出一张临时表

如何创建视图？

Create view 视图名称（<视图列名1>,<视图列名2>,……）
As
<select 查询语句>：

-- 创建视图create VIEW
create view 按性别汇总(性别,人数)
as
select 性别,count(*)
from student
group by 性别;

2、 如何用视图？

-- 视图里查询
select 性别,人数
from 按性别汇总;----->在from字句中使用视图名称代替表名称

如何删除视图？

3、 视图有什么用？

为什么使用视图，什么情况下使用视图

如果有些SQL语句保存成视图，就不用总是去写一遍SQL了，特别是在进行汇总以及复杂的查询条件下，导致这个SQL查询语句非常庞大的时候使用视图可以帮助我们提高效率，使用视图的第二个好处是，视图中的数据他会随着原表的变化自动更新，可以保证数据的最新状态，这是因为视图里面他存放的不是数据，而是说他存放的是SQL查询语句，每次查的时候呢会从原表里面去取数据，所以原表数据更新的时候，他查出来的结果也会自动更新，使用视图第三个好处是视图不需要保存数据，可以节省存放数据的空间

4、 视图的注意事项

1、避免在视图的基础上再去创建视图，因为多重视图会降低SQL的性能和效率，
2、不能往视图里面插入数据，不然会报错

二、 子查询

1、 什么是子查询

子查询类似一次性视图

-- 什么是子查询
Select 性别,人数
From (
Select性别,count(*) as 人数--1、在from字句中直接写定义视图的SQL查询语句
From student
Grope by 性别
)as 按性别汇总;-->2

先运行子查询，再运行外部的查询语句。

2、 如何使用子查询

In（子查询）
Any （子查询）
All （子查询）

案例：in

找出每个课程里 成绩最低的 学号

--找出每个课程里  成绩最低的  学号
Select 课程号,min(成绩)
From score
Group by 课程号--没有子查询的情况下只能找到学对应的课程号找不到对应的学号

错误案例

-- 错误案例：找出每个课程里  成绩最低的  学号
select 课程号,min(成绩),学号
from score
group by 课程号;

案例： 含有子查询的查询步骤

--第一步：查找出每门课程的最低成绩有哪些值

--第一步：查找出每门课程的最低成绩有哪些值
Select 课程号,min(成绩)
From score
Group by 课程号

--第二步：最终SQL

--第二步：最终SQL
Select 课程号,学号,成绩
From score as x
Where 成绩 in(select min(成绩)-- 子查询
              from score as y
              where y.课程号=x.课程号
              group by 课程号)

any，all如何跟子查询一起使用

…… any(子查询)
……all（子查询）使用方法：必须和比较运算符一起使用

以下是经常用到的一些运算符：其中any，all必须和比较运算符一起使用

算术运算符:

+ 含义： 加法运算
- 含义 ： 减法运算
* 含义 ：乘法运算
/ 含义 ： 除法运算

比较运算符 :

= 含义：相等
< > 含义：不等于
> 含义：大于
>= 含义：大于等于
< 含义：小于
<= 含义：小于等于

逻辑运算符:

Not 含义：否定某一条件
And 含义：并且
Between 含义： 范围查找
Or 含义：或者
In 含义： 等同于or

any（子查询）案例：any（子查询）与some（子查询）相同

Select 列名1
From 表名1
Where 列名1>any(子查询)

案例：哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？

第一步：课程0002的全部成绩，比如是（10,30）
第二步：某个学生的成绩 大于 任意一个第一步里的成绩，就符合条件

-- 哪些学生的成绩比课程0002的全部成绩里的任意一个高呢？
Select 学号,成绩
From score
Where 成绩>any(---------任意一个高 any（子查询）
Select 成绩
From score
Where 课程号='0002');  ------1、查找出课程0002的全部成绩

具体步骤

1） 查找出课程0002的全部成绩

Select 成绩
From score
Where 课程号=‘0002’

2） 比任意一个高any（子查询）

Select学号，成绩
From score
Where 成绩>any(子查询)

all（子查询）案例

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

--哪些学生的成绩比课程002的全部成绩里的都高呢？
Select 学号,成绩
From score
Where 成绩>all(
Select 成绩
From score
Where 课程号='0002');-----子查询

3、 子查询有什么用

子查询使用的频率相对较低
视图使用的频率相对较高

4、 注意事项

注意事项1: a>3*all(b) 是错误的
a/3>all(b) 是正确的
注意事项2: select…from(子查询（子查询）)层层嵌套 避免使用,因为很难看懂
注意事项3 :select…from… 子查询 as 子查询名称是可以省略的

5、SQL运行顺序：

1、 先运行子查询

2、 每个查询语句里运行顺序

三、 标量子查询

1、 什么是标量子查询

案例：大于平均成绩学生的学号和成绩

错误案例

--错误案例
Select 学号,成绩
From score
Where 成绩>avg(成绩);

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

-- 大于平均成绩学生的学号和成绩
Select 学号,成绩
From score
Where 成绩>(
Select avg(成绩)
From score   -- ---标量子查询 avg（成绩）81.125
);   

同

-- 大于平均成绩（81.125）学生的学号和成绩
Select 学号,成绩
From score
Where 成绩>81.125

案例：成绩介于：差生（成绩<=60）的平均成绩 优等生（成绩>80）的平均成绩之间

-- 成绩介于：差生（成绩<=60）的平均成绩 与优等生（成绩>80）的平均成绩之间
Select 学号,成绩
From score
Where 成绩 between-- 等同于Between 60 And 84.14
(select avg(成绩)
From score
Where 成绩<=60)and
(select avg(成绩)
From score
Where 成绩>=80);

2、 如何使用标量子查询

-- 如何使用标量子查询
Select 学号,成绩,(select avg(成绩)
From score)as 平均成绩
From score;

3、 标量子查询有什么用

偶尔用车：打车（子查询）

多行（普通子查询）
单一值（标量子查询）in，any，all，between

频繁使用：私家车（视图）

4、 注意事项

标量子查询不能返回多行结果，如果返回多行结果就不能算是标量子查询，只能算作是普通子查询

-- 标量子查询错误示范
Select 学号,成绩(select avg(成绩)
From score
Group by 课程号
) as 平均成绩
  From score;

四、 关联子查询

1、 什么是关联子查询，如何使用关联子查询

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

--查找出每门课程的平均成绩
Select avg(成绩)
From score
Group by 课程号;

关联子查询

-- 查找出每个课程中大于对应课程平均成绩的学生
Select 学号,课程号,成绩
From score as s1
Where 成绩>(select avg(成绩)
From score as s2
Where s1.课程号=s2.课程号
Group by 课程号
);

子查询里的关联条件

Where s1.课程号=s2.课程号

为什么外部的表别名s1 可以在子查询里识别出来呢？

-- 为什么外部的表别名s1 可以在子查询里识别出来呢？
Select 学号，课程号，成绩
From score as s1 --s1在全部查询里都有效
Where 成绩>(select avg(成绩)
From score as s2    -- --s2仅在子查询里有效
Where s1.课程号=s2.课程号
Group by 课程号);

2、 关联子查询有什么用

什么时候使用子查询呢?

在每个组里进行比较的时候，使用关联子查询

1）偶尔使用：打车

1. 多行子查询
2. 单一值（标量子查询）in，any，all，between
3. 在每个组里比较（关联子查询）关联条件

2）频繁使用：私家车(视图)

五、 如何用SQL解决业务问题

1、 翻译成大白话

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

第一步：课程0002的全部成绩，比如是（10,30）
第二步：某个学生的成绩大于任意一个第一步里的成绩，就符合条件

2、 写出分析思路

3、 写出对应的SQL子句

第一步：查找出课程0002的全部成绩

-- 查找出课程0002的全部成绩
Select 成绩
From score
Where 课程号='0002';

第二步：某个学生的成绩大于任意一个第一步里的成绩，就符合条件,比任意一个高any（子查询）

-- 某个学生的成绩大于任意一个第一步里的成绩
Select 学号,成绩
From score
Where 成绩>any(------2、任意一个高 any（子查询）
Select 成绩
From score
Where 课程号='0002');-------1、查找出课程0002的全部成绩

4、如何看懂SQL报错信息？

-- 如何看懂SQL报错信息？
Select 学号,成绩
 from score
  where 成绩>any(
select 成绩
from score
where 课程号=‘0002’);-----先对子查询进行逐一排查

六、 各种函数

1、汇总函数

Count（列名） 求某列的行数，count（*）：全部列的行数
Sum（列名） 对某列数据求和，只能对数值类型的列计算
Avg（列名） 求某列数据的平均值，只能对数值类型的列计算
Max（列名） 求某列数据的最大值
Min（列名） 求某列数据的最小值

2、算数函数

3、字符串函数

4、日期函数

案例：--查找1990年出生的学生名单

--查找1990年出生的学生名单
Select 学号,姓名
From student
Where year(出生日期)=1990;

七、练习题

/*1、列出每個國家的名字 name，當中人口 population 是高於俄羅斯'Russia'的人口。

world(name, continent, area, population, gdp)*/
SELECT name 
FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Russia')

/*2、列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。

人均GDP*/
select name
from world
where continent='Europe' and gdp/population>(select gdp/population
                                               from world
                                                where name='United Kingdom' )

/*3、在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，
列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序*/
select name,continent
from world
where continent in (select continent
                      from world 
                      where name in('Argentina','Australia'))
order by name

/* 4、哪一個國家的人口比加拿大Canada的多，
但比波蘭Poland的少?列出國家名字name和人口population 。*/
select name,population
from world
where population>(select population from world where name='Canada') and population<(select population from world where name='Poland')

/* 5、Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。

顯示歐洲的國家名稱name和每個國家的人口population。
以德國的人口的百分比作人口顯示。*/
select name,concat(round(population/(select population from world where name='Germany')*100,0),'%')
from world
where continent='Europe'

/* 6、哪些國家的GDP比Europe歐洲的全部國家都要高呢?
 [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)*/
select name
from world
where gdp>all(select gdp from world where continent='Europe' and gdp>0 )

/* 7、在每一個州中找出最大面積的國家，列出洲份 continent,
 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)*/
SELECT continent, name, area FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0
         group by continent)

/* 8、列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。
(即每洲只有列一國)*/
select continent, name 
from world as x
where name <= all
(select name 
from world as y
where y.continent=x.continent
group by continent);

/* 9、找出洲份，當中全部國家都有少於或等於 25000000 人口.
 在這些洲份中，列出國家名字name，continent 洲份和population人口。*/
select  name,continent,population
from world 
where continent not in (select continent
                          from world
                          where population>25000000)

/* 10、有些國家的人口是同洲份的所有其他國的3倍或以上。
列出 國家名字name 和 洲份 continent。*/
select name,continent
from world as x
where population/3>=all(select population
                         from world as y
                         where y.continent=x.continent and y.name<>x.name
                          group by continent)