一、 视图
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)