[SQL]数据查询(二)

[SQL]数据查询(二)
我们继续上篇文章所讲的数据查询

👉数据查询(一)

字符匹配

匹配固定字符串
- 找出工号为76543的教师的详细情况
```
select * from instructor
where ID = '76543'
```
```
select * from instructor
where ID = '76543';
```
通配符(重要!!)
- %(百分号)代表任意长度(长度可以为0)的字符串
- - 例如:a%b表示以a开头,以b结尾的任意长度的字符串.如acb,addgb,ab等都慢则该匹配串
- _(下划线)代表任意单个字符
- - 例如:a_b表示以a开头,以b结尾的长度为3的任意字符串.如acb,afb等都满足该匹配串.注意!!!长度!!!
- 当用户要查询的字符串本身就含有%或者_时,就要使用ESCAPE'<换码字符>'短语对通配符进行转义
- - like 'ab\%cd|' escape'\' #(匹配所有以"ab%cd"开头的字符串)
  - like 'ab\\cd%' escape '\' #(匹配所有以"ab\cd"开头的字符串)
匹配含有通配符的字符串
- 找出课程名以"Intro"为开头的课程的详细信息
```
select * from course 
where title like 'Intro%';
```
- 找出课程名字中包含子串"Biology"的课程的详细信息
```
select * from course
where title like '%Biology%';
```
- SQL标准中,字符串上的相等运算大小写敏感
- MySQL在匹配字符串时并不区分大小写
空值(NULL)
- 空值表示"不知道","不确定"
- 涉及空值的运算
- - 涉及控制的算术运算的结果为空,如:5+null 返回null
  - 涉及空值的比较运算的结果为unknown，如： 5 < null ； null <>null ； null = null
  - 涉及空值的逻辑运算
- - OR:
    (unknown or true) = true,
    (unknown or false) = unknown
    (unknown or unknown) = unknown
    AND:
    (true and unknown) = unknown
    (false and unknown) = false
    (unknown and unknown) = unknown
    NOT:
    (not unknown) = unknown
- 如果where子句对一个元组计算出false或unknown,那么该元组不能被加入到结果集中.
- 使用IS NULL 和IS NOT NULL 测试空值
- - 例如:找出instructor关系中salary为空值的所有教师
```
select name 
from instructor
where salary is null;
```
没有不是空值的

ORDER BY子句
- ORDER BY 子句
- - 可以按一个或者多个属性列排序
  - 升序:ASC
  - 降序:DESC
  - 默认是升序
- 当排序列含空值的时候
- - ASC:排序列为空值的元组最先显示
  - DESC:排序列为空值的元组最后显示
- 按字母顺序列出在Physics系的所有老师
```
select name from instructor
where dept_name = 'Physics'
order by name;
```
- 将Instructor 关系按照salary降序,salary相同时按照name升序排列
```
select * from instructor
order by salary desc, name asc;
```
聚集函数
- 聚集函数是以值的一个集合作为输入,返回单个值的函数
- 五个聚集函数
- - 平均值:AVG
  - 最小值:MIN
  - 最大值:MAX
  - 总和:SUM
  - 计数:COUNT
- DISTINCT短语:在计算时要取消指定列中的重复值
- ALL短语:不取消重复值
- ALL为缺省值
- 找出"Comp. Sci."系教师的平均工资
```
select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp. Sci.';
```
- 找出“Comp. Sci. ”系教师的最高工资和最低工资
```
select max(salary) as salary_max,
	min(salary) as salary_min
from instructor
where dept_name = 'Comp. Sci.'
```
- 找出在2018年春季学期讲授课程的教师总数
```
select count(distinct ID)
from teaches
where semester = 'Spring' and year = 2018;
```
- 计算teaches关系中元组的个数
```
select count(*) 
from teaches;
```
聚集函数处理空值的原则
- 假设instructor关系中有些元组在salary上取空值,考虑计算所有工资总额的查询
```
select sum(salary) 
from instructor;
```
- 上述的查询中sum运算符是自动忽略NULL的值
- 如果所有元组上salary都取空值,则查询结果为NULL
- 除了count(*)外的所有聚集函数都忽略输入集合中的空值
- 如果集合中只有空值呢?
- - count运算返回0
  - 其余聚集函数的运算返回NULL
- 对于更加复杂的SQL语句,空值的影响会更加难以捉摸
GROUP BT子句
- 细化聚集函数的作用对象
- - 未对查询结果分组,聚集函数将作用于整个查询结果.
  - 对查询结果分组后,聚集函数将分别作用于每个组
  - 按照指定的一列或者多列值分组,值相等的为一组
- 找出每个系教师的平均工资
```
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;
```
- 限制分组的条件:利用having子句
Having子句

找出教师平均工资超过42000美元的系
```
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary)>42000;
```
Having 短语和WHERE子句的区别
1. 作用对象不同
2. WHERE子句作用于基本表或者试图,从中选择满足条件的元组
3. Having短语作用于分组,从中选择满足条件的分组
- 保证出现在select语句中但没有被聚集的属性只能是出现在group by 子句中的那些属性
```
select dept_name, ID, avg (salary)
from instructor
group by dept_name;
```
连接查询
- 当查询同时涉及两个以上的表时，称为连接查询，连接查
  询可实现从多个表中提取数据。
连接查询的三种方法
- 方法1：表之间满足一定的条件的元组进行连接，此时FROM子句中指明进行连接的表名， WHERE子句指明连接的列名及其连接条件；
- 方法2：利用关键字JOIN进行连接，并用ON表明连接条件；
- 方法3：利用关键字JOIN进行连接，并用USING指定连接的属性列表
- 找出所有教师的姓名,所在院系以及院系所在建筑的名称
```
select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name=
department.dept_name;
```
- 找出所有教师的姓名，所在院系以及院系所在建筑的名称
```
select name, T.dept_name, building
from instructor as T, department as D
where T.dept_name= D.dept_name;
```
- 找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系某一个教师的工资要高
```
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = ’Biology’;
```
利用join关键字连接
- 当将JOIN关键字放于FROM子句中时，应有关键字ON与之相对应，以表明连接的条件。
```
语句格式：
SELECT {*|<表达式>,…,<表达式>}
FROM <表名> JOIN <表名> ON <条件表达式>
```
- 找出所有教师的姓名，所在院系以及院系所在建筑的名称
```
select name, instructor.dept_name, building
from instructor, department
where instructor.dept_name= department.dept_name;
select name, instructor.dept_name, building
from instructor join department
on instructor.dept_name= department.dept_name;
```
- 将instructor关系与teaches关系做等值连接
这是两个表的内容
- 等价的两种形式
```
select *
from instructor, teaches
where instructor.ID= teaches.ID;
select *
from instructor join teaches
on instructor.ID= teaches.ID;
```
自然连接
```
select * from instructor natural join teaches
```
- 找出每个系在2018年春季学期讲授课程的教师人数
```
select dept_name, count(distinct ID) as instr_count
from instructor natural join teaches
where semester = ‘Spring’ and year = 2018
group by dept_name;
```
外连接

在结果中创建带空值的元组，以此来保留在连接中丢失的
那些元组。
- 考虑查询“找出所有未授课教师的ID、姓名、所在系别”
左外连接
- 给出所有教师的个人信息及其授课信息，如果有教师不授
  课，其授课信息用NULL表示
```
select *
from instructor natural left outer join
teaches;
```
- 找出所有未授课教师的ID、姓名、所在系别
```
select ID, name, dept_name
from instructor natural left outer join teaches
where course_id is null;
```
考虑查询“找出没有选修任何课程的学生
```
select *
from takes natural right outer join student;
```
- 找出没有选修任何课程的学生
```
select ID
from takes natural right outer join student
where course_id is null;
```
ON条件与外连接
```
考虑查询：
select *
from takes right outer join student on takes.ID= student.ID;
等同于： select *
from takes natural right outer join student;
```
只不过第一个查询中属性ID在结果中出现两次

USING条件连接
- USING指定连接的属性名列表
```
select *
from takes right outer join student using (ID);
```
多表连接

假设:
instructor(ID, name, dept_name, salary)
teaches(ID, course_id, sec_id, semester, year)
course(course_id, title, dept_name, credits)
考虑查询：列出教师的名字以及他们所讲授课程的名称
select name, title
from instructor natural join teaches natural
join course;

列出教师的名字以及他们所讲授课程的名称
select name, title
from (instructor natural join teaches) join course
using (course_id);

内连接和外连接
SQL把常规连接称作内连接，用inner join表示。
关键词inner是可选的，当join子句中没有使用outer前缀时，
默认的连接类型是inner join。
select *
from student join takes using (ID);
等价于：
select *
from student inner join takes using (ID);

连接类型和连接条件

连接类型

Inner join

Left outer join

Right outer join

Full outer join

集合查询

SQL 作用在关系上的union， intersect和except 运算

分别对应于数学集合论中的∪ ， ∩ 和 −。

假设关系c1:在2017年秋季学期开设的所有课程的集合

select course_id from section

where semester = ’Fall’ and year= 2017;

关系c2:在2018年春季学期开设的所有课程的集合：

select course_id from section

where semester = ’Spring’ and year= 2018;

并运算

找出在2017年秋季开设，或者在2018年春季开设或两个学
期都开设的所有课程
```
(select course_id
from section
where semester = ’Fall’ and year= 2017)
union
(select course_id
from section
where semester = ’Spring’ and year= 2018);
```
与select不同， union运算自动去除重复

如何保留重复元组？
```
(select course_id
from section
where semester = ’Fall’ and year= 2017)
union all
(select course_id
from section
where semester = ’Spring’ and year= 2018);
```
结果中的重复元组数等于在c1和c2中出现的重复元组数的
和

交运算intersect
```
找出在2017年秋季和2018年春季都开设的课程的集合：
(select course_id
from section
where semester = ’Fall’ and year= 2017)
intersect
(select course_id
from section
where semester = ’Spring’ and year= 2018);
```
保留重复加all

差运算

找出在2017年秋季学期开设但不在2018年春季学期开设的
所有课程：
(select course_id
from section
where semester = ’Fall’ and year= 2017)
except
(select course_id
from section
where semester = ’Spring’ and year= 2018);

结果中的重复元组数等于c1中出现的重复元组数减去c2中
出现的重复元组数（前提是此差为正）。

MySQL中的集合查询

SQL Server数据库中支持关键字:union,intersect和except

MySQL中只支持union关键字

那我们如何在MySQL中实现intersect和except呢?

嵌套查询

一个 SELECT-FROM-WHERE语句称为一个查询块

将一个查询块嵌套在另外一个查询块的WHERE子句或FROM子句中的查询称为嵌套查询

子查询是嵌套在另一个查询中的SELECT-FROM-WHERE表达式

查询：找出在2017年秋季学期开课的教师姓名
```
SELECT name /*外层查询/父查询*/
FROM instructor
WHERE ID IN
(SELECT ID /*内层查询/子查询*/
FROM teaches
WHERE semester = ’Fall’ and year= 2017 )；
```
嵌套查询求解方法

不相关子查询:子查询的查询条件不依赖于父查询
- 由里向外逐层处理
- 即每个子查询在上一级查询处理之前求解,子查询的结果用于建立其夫查询的查找条件
相关子查询:子查询的查询条件依赖于父查询
- 首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处理内层查询,若WHERE子句返回值为真,则取此元组放入结果表.
- 然后再取外层表的下一个元组
- 重复这一过程,知道外层表全部检查完为止
子查询的位置

WHERE子句

通常用于集合的成员资格,集合的比较以及集合的基数进行检查

FROM子句

任何SELECT-FROM-WHERE表达式返回的结果都是关系,因而可以被插入到另一个SELECT-FROM-WHERE中任何关系可以出现的地方

SELECT,WHERE和HAVING子句

标量子查询返回包含单个属性的单个元组

集合成员资格
- SQL允许测试元组在关系中的成员资格
- - 集合是由select子句产生的一组值构成的
  - 连接词in测试元组是否是集合中的成员
  - 连接词not in则测试元组是否不是集合中的成员
- 考虑查询:找出在2017年球季和2018年春季同时开设的所有课程的集合
- - 交运算
  - 自然连接
  - in连接词
1. 找出2018年春季开设的所有课程
```
(select course_id
from section
where semester = 'Spring' and year = 2018);
```
1. 从子查询形成的课程集合中找出那些在2017年秋季开设的课程
```
select distinct course_id from section
where semester = 'Fall' and year = 2017 
and course_id in 
(select course_id
from section
where semester = 'Spring' and year = 2018);
```
找出在2017年秋季学期开设但不在2018年春季学期
开设的所有课程
```
select distinct course_id from section
where semester = ’Fall’ and year= 2017 and
course_id not in
(select course_id from section
where semester = ’Spring’ and year= 2018);
```
IN和NOT IN用于枚举集合
- 找出名字既不是"Mozart"也不是"Einstein"的教师姓名
```
select distinct name from instructor
where name not in ('Mozart','Einstein');
```
IN 和 NOT IN 用于多属性关系
- 找出学生总数,他们选修了ID为"10101"的教师所讲授的课程端
```
select count(distinct ID) from takes
where (course_id, sec_id, semester, year) in
(select course_id, sec_id, semester, year
from teaches where teaches.ID = 10101);
```
集合的比较
- 考虑查询 "找出满足下面条件的所有教师的姓名,他们的工资至少比Biology系某一个教师的工资要高"
```
select distinct T.name
from instructor as T, //这里创建两个关系,一个是所有的,一个是生物系的老师
instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';
```
```
select name from instructor
where salary > all (select salary
from instructor where dept_name = ’Biology’);
```
找出平均工资最高的系
```
select dept_name from instructor
group by dept_name
having avg (salary) >= all (select avg(salary)
from instructor group by dept_name);
```
集合比较

> SOME 大于子查询结果中的某个值

> ALL 大于子查询结果中的所有值

< SOME 小于子查询结果中的某个值

< ALL 小于子查询结果中的所有值

>= SOME 大于等于子查询结果中的某个值

>= ALL 大于等于子查询结果中的所有值

<= SOME 小于等于子查询结果中的某个值

<= ALL 小于等于子查询结果中的所有值

= SOME 等于子查询结果中的某个值

=ALL 等于子查询结果中的所有值（通常没有实际意义）

!=（或<>） SOME 不等于子查询结果中的某个值

!=（或<>） ALL 不等于子查询结果中的任何一个值

SOME ,ALL谓词与聚集函数,IN谓词的等价转换关系

=

<>或!=

<

<=

>

>=

SOME

IN

--

<=MAX

>MIN

>= MIN

ALL

--

NOT IN

<= MIN

>MAX

>= MAX

空关系测试
- 测试一个子查询的结果中是否存在元组
- EXISTS结构
- - 带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值"true"或逻辑假值"false"
  - 子查询非空时返回真值
  - 由EXISTS引出的子查询,其目标列表达式通常都用*,因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义
- NOT EXISTS结构
- - 若子查询结果非空,则外层的WHERE子句返回价值
  - 若子查询结果为空,则外层的WHERE子句返回真值
EXISTS

找出在2017年秋季学期和2018年春季学期同时开设的所有课程
```
select course_id from section as S
where semester = 'Fall' and year= 2017 and
exists (select * from section as T
where semester = 'Spring' and year= 2018 and
S.course_id= T.course_id);
```
- NOT EXISTS结构测试子查询结果是否不存在元组
- 使用NOT EXISTS结构模拟集合包含操作
- - "关系A包含关系B”写成“NOT EXISTS （B except A）”
- 考虑查询:"找出选修了Biology系开设的所有课程的学生"
```
select distinct S.ID, S.name
from student as S
where not exists ((select course_id
from course
where dept_name = 'Biology')
except
/*上面是在生物系的课程id,下面是
  全部课程都学的课程id*/
(select T.course_id
from takes as T
where S.ID = T.ID));
```
- UNIQUE用于测试一个子查询的结果中是否存在重复元组
- 如果作为参数的子查询结果中没有重复的元组， UNIQUE将返回true值；
- 如果作为参数的子查询结果中存在重复的元组， NOT UNIQUE将返回true值。
“找出所有在2017年最多开设一次的课程”
```
select T.course_id
from course as T
where 1 >= (select count(R.course_id)
from section as R
where T.course_id= R.course_id and
R.year = 2017);
```
UNIQUE结构

“找出所有在2017年最多开设一次的课程”
```
select T.course_id
from course as T
where unique (select R.course_id
from section as R
where T.course_id= R.course_id and
R.year = 2017);
```
NOT UNIQUE结构

找出所有在2017年最少开设两次的课程”
```
select T.course_id
from course as T
where not unique (select R.course_id
from section as R
where T.course_id= R.course_id and
R.year = 2017);
```
FROM子句中的子查询

考虑查询 "找出系平均工资超过42000美元的那些系中教师的平均工资"

方法1 使用having子句
```
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;
```
方法2 在FROM子句中使用
```
select dept_name, avg_salary
from 
(select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name) as dept_avg
where avg_salary > 42000;
```
- MySQL要求对每一个子查询所得到的关系都给一个名字，即使该名字从不被使用。
- Oracle允许对子查询结果关系命名（省略关键字as），但是不允许对关系中的属性重命名。
- 找出在所有系中工资总额最大的系
```
select dept_name from (select dept_name,sum(salary) as sum_salary  from instructor
                       group by dept_name) as total_dept1
where sum_salary = (select max(sum_salary) from
    (select dept_name,sum(salary) as sum_salary  from instructor
     group by dept_name) as total_dept);
```
WITH子句
- WITH子句提供定义临时关系的方法
- 定义只对包含WITH子句的查询有效
- 考虑查询 "具有最大预算值的系"
```
with max_budget (value) as
(select max(budget)
from department)
select department
from department, max_budget
where department.budget = max_budget.value;
```
```
with dept_total (dept_name, value) as
(select dept_name, sum(salary)
from instructor group by dept_name),
dept_total_avg(value) as
(select avg(value) from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value;
```
标量子查询

SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组。

这样的子查询叫做标量子查询
- 考虑查询:"列出所有的系以及他们拥有的教师数量"
```
select dept_name,
(select count(*)
from instructor
where department.dept_name =
instructor.dept_name)
as num_instructors
from department;
```
数据更新

插入数据

让Music系每个修满144学分的学生成为Music系的教师，其工资为18000美元
```
insert into instructor
select ID, name, dept_name, 18000
from student
where dept_name = 'Music' and tot_cred > 144;
```
对工资低于平均数的教师涨5%的工资
```
update instructor
set salary = salary * 0.5
where salary < (select avg(salary) from instructor);
```
删除在Watson大楼工作的教师元组
```
delete from instructor 
where dept_name 
in (
  select dept_name
  from department
  where building = 'Watson'
);
```
删除工资低于大学平均工资的教书记录
```
delete from instructor
where salary < (select avg(salary) from instructor);
```
相关阅读:
现代检测技术课程实验编程：波特图分析仪原理仿真：一阶检测系统编程仿真
 流媒体技术基础-摄像头接口与标准
 JavaScript模块导入与编程
 FFmpeg关键函数介绍
 【kali-信息收集】（1.6）服务的指纹识别：Nmap、Amap
Spring Data【Spring Data 介绍、Spring Data JPA 、Spring Data JDB】(一)-全面详解（学习总结---从入门到深化）
Redis 缓存预热、缓存雪崩、缓存击穿、缓存穿透业务实践
 [附源码]Java计算机毕业设计SSM扶贫产品展销平台小程序
 jnpf3.6私有化部署
 Codeforces Round #804 (Div. 2)-C. The Third Problem
原文地址：https://blog.csdn.net/qq_63511424/article/details/127724903

	=	<>或!=	<	<=	>	>=
SOME	IN	--		<=MAX	>MIN	>= MIN
ALL	--	NOT IN		<= MIN	>MAX	>= MAX

字符匹配

ORDER BY子句

聚集函数

GROUP BT子句

连接查询

自然连接

外连接

左外连接

ON条件与外连接

USING条件连接

多表连接

连接类型和连接条件

集合查询

并运算

如何保留重复元组？

交运算intersect

差运算

MySQL中的集合查询

嵌套查询

嵌套查询求解方法

子查询的位置

WHERE子句

FROM子句

SELECT,WHERE和HAVING子句

集合成员资格

IN和NOT IN用于枚举集合

IN 和 NOT IN 用于多属性关系

集合的比较

集合比较

SOME ,ALL谓词与聚集函数,IN谓词的等价转换关系

空关系测试

EXISTS

UNIQUE结构

NOT UNIQUE结构

FROM子句中的子查询

WITH子句

标量子查询

数据更新

插入数据