SQL查询总览

查询

对表的操作—顺序

计算列

• 查询重点(顺序)：理顺SQL语句执行顺序。到底先执行哪个后执行哪个？先执行from…后执行select…先执行from是把整个表的每一条数据拉出来，然后用select选择判断到底对哪个值进行输出，如果是字段就把字段列输出；如果是字段列有别名，就把别名输出；如果是多个字段，就多个字段列输出；如果是常量值，就把无意义的值输出，但可看出表内一共有多少行

-- select选择
-- * 表示所有
-- from emp：从emp中查询
select * from emp;

select empno,ename from emp;

select ename,sal from emp;

select ename,sal * 12 as "年薪" from emp; 
			-- as可以省略，记住“年薪”不要写成'年薪'，也不要写成 年薪 
			
select 5111111 from emp;
			-- 输出的行数是emp表的行数，每行只有一个字段，值是5
			
select ename,sal * 12 as "年薪", sal as "月薪",job from emp; 
			--  逗号分隔（输出规则：emp -> ename -> sal -> sal*12 -> sal -> 月薪 -> job）

distinct

[不允许重复的]-- distinct用法

select deptno from emp;
		-- 访问emp,找到deptno,找到一个输出一个
select distinct deptno from emp;
		-- 过滤重复值
		
select distinct comm from emp;
		-- 有空值
		-- distinct也可以过滤掉重复的null
		
select distinct comm,deptno from emp;
		-- 整体过滤（过滤comm,deptno同时重复的,不单过滤）
		
-- select deptno,distinct comm from emp; 
		-- erro 逻辑冲突

between

-- between (比特翁)
-- 查找工资在1500到3000之间（包括1500和3000）的所有员工信息
select *from emp
	where sal >= 1500 and sal <= 3000			-- 挨个遍历
	
-- 等价于
select *from emp
	where sal between 1500 and  3000

-- 查找工资在小于等于1500或大于等于3000之间的所有员工信息
select *from emp
	where sal <= 1500 or sal >= 3000
	
-- 等价于
select *from emp
	where sal not between 1500 and  3000

in [属于若干个孤立的值]

-- in
select *from emp where sal in (1500,3000,5000);
-- 等价于
select *from emp
	where sal = 1500 or sal = 3000 or sal = 5000

select *from emp where sal not in (1500,3000,5000);
		-- 找sal既不是1500，也不是3000，也不是5000
-- 等价于
select *from emp
	where sal <> 1500 and sal <> 3000 and sal <> 5000
	-- 数据库中不等于有两种表示： ！=  <>(推荐使用)
	-- 对“或”取反是“且”，反之也成立

top

-- top用法
-- 分页查熊也会用到top,比如一页显示10个，显示若干页（网页搜索）
select * from emp;

select top 2 * from emp;

select top 15 percent * from emp;
		-- 前15%，小数点上取整

test : 把工资在1500 到3000之间（包括）的工资最高的前四个人降序输出

-- 执行顺序 from -> where -> order by -> top 4*
select top 4*
	from emp
	where sal between 1500 and 3000 
	order by sal desc		-- 降序输出

null

靠!

出毛病了！一点执行就弹出该窗口

奥，这玩意其实就是显示方式，原来是网格，现在是文件。只需要点查询按钮–>将结果保存

-- null
-- 输出奖金非空的人
select *from emp where comm <> null;	-- 不行
-- 为啥？
-- 因为null 不能参与 <> 、!= 运算

-- 1、null可以参与is、not is运算
-- 0和null 不一样
select * from emp where comm is null;

select * from emp where comm is not null;

-- 输出每个员工的姓名、年薪（包含奖金）
select *from emp;
select ename, sal * 12+comm as "年薪" from emp;	-- 一个不是值的东西和一个值做数学运算，最后还不是一个值
-- 2、null不能参与任何数学运算,应该怎么做？
-- 自己试试（用in,不大行）

select *from emp 
	 select sal * 12+comm as "年薪" from emp where comm is not null;

select ename, sal * 12+comm as "年薪" from emp;	-- 一个不是值的东西和一个值做数学运算，最后还不是一个值
-- 2、null不能参与任何数学运算,应该怎么做？

-- isnull (comm,0) : 如果comm是null，就返回0，否则返回具体的值
select ename ,sal * 12 + ISNULL (comm,0) as "年薪" from emp;	-- 这样就行了

order by

-- order by 【以某个字段排序】
select *from emp order by sal;		-- 默认是升序排序
select *from emp order by deptno,sal;	-- order by deptno,sal：按组合排序吗？NO，是先按deptno排，如果deptno相同了，再排sal（优先级问题）

select *from emp order by deptno desc,sal;-- 有两种可能，一种是前面降序后面也降序，另一种是前面降序，后面还是按默认的升序
-- 结果：对后面的没有影响
-- 结论：order by a desc,b,c,d; desc只对a有影响

select *from emp order by deptno,sal desc;	
---- 也不会
-- 先按deptno升序，如果相同，再按sal降序
-- 还是讲究优先级问题

-- 如果不指定排序标准，默认升序（asc）,默认可以不写
-- 为某一个字段指定排序，都不会影响其他字段的排序，并且优先级还是先来后到

模糊查询

-- 模糊查询
-- 搜索关键字
-- 格式：
	-- select 字段的集合（某个或几个字段） from 表名 where 某个字段的名字 like 匹配的条件
	-- 匹配的条件通常有通配符：
	
-- 1、% ：任意0个或多个字符
select * from emp 
	where ename like '%S%'		-- 只要含有S就输出
select * from emp 
	where ename like 'A%'			-- 首字符只要有A就输出
select * from emp 
	where ename like '%A'			-- 尾字符只要有A就输出
		
-- 2、 _【下划线，不是减号】
select * from emp where ename like '_A%';  -- 第二个字母是A就输出
-- _A:表示第二个字母是A且到字母A后，该单词就结束了

-- 3、[a-f]
-- a到f中的任意单个字符，只能是a,b,c,d,e,f
select * from emp where ename like '_[A-F]%'; -- 把第二个字母是A-K的输出

-- 4、[a,f]
-- a或f
select * from emp where ename like '_[A,f]%'

-- 5、[^a-c]	^：(异或)取反，不是a也不是f
select * from emp where ename like '_[^A-F]%';
-- 把ename中第二个字母不是A，B，C,D,E,F的输出
	--注意：
	-- 1、匹配的条件必须得用单引号,双引号表示对象名称，单引号表示字符串 
	-- 3、通配符和要查找的字符冲突问题（比如我要查找%、_）
select * from emp where ename like '%\%%' escape '\';		-- escape：把\当成转义字符看待,所以在\后再添加%，就代表是一个普通的%
															-- 当然这个'\',可以换成任何字符，比如’m‘ : escape 'm'，一般用\
															-- '%\%%'：第一个%表示任意0个或多个字符；\后第一个%就是个普通的%,最后一个%表示后面任意0个或多个字符

聚合函数

-- 聚合函数的分类
-- max()
-- min()
-- avg()
-- count()	求个数
select count(*) from emp;	-- 返回emp表所有记录的个数
select count(deptno) from emp;   -- 返回值是14，说明deptno中重复的记录也被当做有效记录

select count(distinct deptno) from emp;	-- 返回字段不重复个数且非空的记录
select COUNT(comm) from emp;		-- 返回值是4,null值不计数

-- 总结
-- 返回字段非空值的个数，重复记录也会被当做有效记录

-- 判断如下语句是否正确
select max (sal) "最高",MIN (sal) "最低",COUNT(*) "人数" from emp;    -- 正确
select MAX (sal) ,LOWER(ename) from emp;			-- 错误（单行函数与多行函数不能通用）

group by

• 我想看看部门的编号、部门的人数、部门的平均工资，看看哪个高

  格式：

 group by  字段组合

​ 功能：

​ 把表中的记录按照字段分成不同组。

​ 实例：

-- group by 分组
use scott
select deptno from emp;

-- 输出每个部门的编号和该部门的平均工资
select deptno,avg(sal) as "部门平均工资" from emp			-- 3、deptno,avg(sal)这俩是不是必须具有相关性	--必然是
	group by deptno;										
	
select deptno,avg(sal) as "部门平均工资",ename from emp		-- 错误：加上ename不行
	group by deptno;										-- 已经分好组了，1、依据什么分的组2、直接过滤重复的？--内部实现好了
	-- 总结：
	-- group by deptno之后，select 中只能出现分组后的整体信息，不能出现组内的详细信息


select deptno,job 
	from emp
	group by deptno,job; -- 先按deptno分组，遇到相同的再看job，最后得到一个小组


select deptno,job,AVG(sal) 
	from emp
	group by deptno,job	-- 部门是deptno的，从事job的人，这些人的平均工资是avg(sal)
	order by deptno;
	
select deptno,job,AVG(sal) "平均工资",COUNT(*) as "个数",SUM(sal) as "总和"		-- COUNT(*)是整体信息
	from emp												
	group by deptno,job	
	order by deptno desc;

-- NULL也可以成一组
select comm ,COUNT(*)from emp
	group by comm;

​ 注意：

group by a,b,c的用法：

​		--先按a分组，a相同再按b分组，b相同再按c分组

​		--最终统计的是最小分组的信息
--使用 group by 之后，select中只能出现分组后的整体信息，不能出现组内的详细信息
--只有经过 group by分组的整体信息，才能被select

having

先分组再过滤,对分组之后的信息进行过滤

-- having
-- 先分组再过滤,对分组之后的信息进行过滤

-- 输出部门平均工资大于2000的部门的部门编号	部门的平均工资
-- 首先你得把部门分类，再找到平均工资大于2000的部门

select deptno,avg(sal) as "平均工资" from emp
	group by deptno
	having AVG(sal) > 2000		-- 对分组后的信息再过滤

select deptno,avg(sal) as "平均工资" from emp
	group by deptno
	having deptno > 20		-- 对分组后的信息再过滤
	
select deptno,avg(sal) as "平均工资" from emp
	group by deptno
	having COUNT(*) > 3		-- 对分组后，有组内个数>3的组吗？ 不对！
							-- 它是统计总个数吧

-- having 与 where
-- 把姓名不包含A的所有员工按部门编号分组；
-- 思路 ：先把带A的排除，然后分组，然后求平均工资，过滤两次：1、不包含A   2、平均工资大于2000
-- 统计输出部门平均工资大于2000的部门的部门编号	部门的平均工资
select  deptno,AVG(sal) "平均工资"
	from emp
	where ename not like '%A%'
	group by deptno
	having avg(sal) > 2000

​

-- 把工资>2000所有员工按部门编号分组；
-- 统计输出部门平均工资大于3000的部门的部门编号	部门的平均工资
select deptno ,avg(sal) as "平均工资"
	from emp
	where sal > 2000			-- 对原始记录过滤
	group by deptno
	having AVG(sal) > 3000		-- 对分组后的记录过滤
	

– 所有参数的顺序是不允许变化的

总结：

• having子句用来对分组后数据进行过滤，常与group by 连用
• 如果没有使用group by，但使用了having,则意味着having把所有记录当做一组来进行过滤

连接查询

定义：

分类：

​ 内连接

– 连接查询
– 定义
– 将两个表或两个以上的表以一定的连接条件连接起来，从中检索满足条件的数据

– 内连接（重难点）
– 让你把员工姓名和部门名称输出

-- 1、select ... from A表 ,B表的用法
-- emp 14行8列，dept 5行3列（笛卡尔积）
-- 结果70行，11列（行数是乘积，列数是之和）
-- A表的每条记录和B表的第条记录拼接
-- A表的每条记录和B表的每条记录连接，B表的每条记录和A表的每条记录连接，
select *from emp,dept

-- 2、select ... from A,B where ... 的用法
-- 产生的笛卡尔积用where中的条件进行过滤
select * 
	from emp,dept
	where EMPNO = 7369
		-- 输出5行

-- A 撞 B 昂
-- 3、select ... from A join B on ... 的用法
-- from emp join dept on 用法一般会给表起别名，别名的话用"",不用也行
-- 输出11列70行
select E.ename as "员工姓名",D.dname as "部门名称"	-- select得加个具体的名字
	from emp E
	join dept D   -- join表连接
	on 1 = 1	  -- on 是连接条件，on不能省，有join就有on

-- 输出11列14行
select E.ename as "员工姓名",D.dname as "部门名称"	-- select得加个具体的名字
	from emp E
	join dept D   -- join表连接
	on E.deptno = D.deptno	  -- on 是连接条件，on不能省，有join就有on

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pHj3y5Bu-1641436524648)(…/image_t/image-20211206194543148-16389635964811.png)]

-- 4、select ... from A,B where ...
-- 与
--	  select ... from A join B on ...
select E.ename,D.dname
	from emp E,dept D
	where E.deptno = D.deptno
-- 等价于
select E.ename,D.dname
	from emp E join dept D
	on E.deptno = D.deptno

 5、select ... from A,B where ...   SQL 92	
	-- 等价于
	select ... from A join B on ... SQL 99
	-- 推荐使用99

– 把工资大于2000的员工的姓名和部门和工资的等级输出
– SQL99标准

如何把三个表连接在一起？

-- 1、首先emp表的deptno和dept的deptno连接，成一张表了，那如何用**这个表**去连接**第三个表**？

-- 用emp表的sal连接第三个表的lowsal;再用sal连接第三个表的highsal。

-- SQL99标准

-- 三个表连接，350行14列
select *
	from emp E join dept 
	on 1 = 1		-- 两张表合成一张表了
	join SALGRADE S
	on 1 = 1		-- 三张表合成一张表了
	
-- SQL99标准
select E.ename,D.dname,S.GRADE
	from emp E 
	join dept D 
	on E.deptno = D.deptno		-- 两张表合成一张表了(为啥二表合体的东西也没个名字，就能和新表join)
	join SALGRADE S
	on E.sal >= S.LOSAL and E.sal <= S.HISAL		-- 三张表合成一张表了
	where E.sal > 2000
	
-- SQL92标准
select *
	from emp E,dept D,SALGRADE S
	where E.deptno = D.deptno and E.sal >= S.LOSAL and E.sal <= S.HISAL and E.sal > 2000

第一题：

-- 求出员工的姓名、部门编号、薪水和薪水等级

select E.ename "姓名",E.deptno "部门编号",E.sal "薪水",S.GRADE "薪水等级"
	from emp E 
	join SALGRADE S
	on sal >= S.LOSAL and sal <= S.HISAL

第二题：

-- 查找出每个部门的编号、该部门所有员工的平均工资、平均工资的等级
-- 先连接工资表，这时候会出现5列，我只需要输出1/2/3列

-- 每个部门的平均工资
select T.部门编号 "部门编号",T.平均工资 "平均工资",S.GRADE "工资等级"
	from SALGRADE S join(		-- 虚表要用()
		select deptno "部门编号", AVG(sal) "平均工资"
		from emp E
		group by deptno
	) T
	on T.平均工资 between S.LOSAL and S.HISAL
	

-- select E.deptno "部门编号",AVG(E.sal) "平均工资",S.GRADE "平均工资等级"
-- from emp E
-- join SALGRADE S
-- on AVG(E.sal) > S.LOSAL and AVG(E.sal) < S.HISAL

第三题：

-- 三、查找出每个部门的编号、部门名称、该部门所有员工的平均工资、平均工资的等级
select T.部门编号 "部门编号",D."dname",T.平均工资 "平均工资",S.GRADE "工资等级"
	from SALGRADE S join(		-- 虚表要用()
		select deptno "部门编号", AVG(sal) "平均工资"
		from emp E
		group by deptno
	) T on T.平均工资 between S.LOSAL and S.HISAL
	join dept D 
	on D.deptno = T.部门编号

第四题：

-- 四、输出emp表所有领导的信息
select *
	from emp 
	where EMPNO in (select mgr from emp)
	
-- 输出emp表所有非领导的信息
select *
	from emp
	where EMPNO not in (select mgr from emp)
-- in 与null的组合带来的问题

select *
	from emp
	where EMPNO not in (
	select mgr from emp where mgr is not null)	

第五题：

-- 求出平均薪水最高的部门的编号和部门的平均工资

select top 1 D.deptno "部门编号",T.avg_sal "平均工资"
	from dept D join(
	select AVG(sal) "avg_sal",E.deptno "部门编号"
		from emp E
		group by deptno
		) T on D.deptno = T.部门编号
	
-- 等价于
select top 1 AVG(sal) "avg_sal",E.deptno "部门编号"
		from emp E
		group by deptno
		order by avg(sal) desc

查询顺序

​

select top ...
	from A
	inner join B
	on ....
	join C
	on ....
	where .....
	group by ...
	having .....
	order by ...

• on 中既可以写连接条件，也可以写过滤条件，但不推荐，应该分开写，on中只写连接条件，where中写过滤条件。

  -- 把工资大于1500的所有的员工按部门分组   把部门的平均工资大于2000的最高的前2个，按升序输出部门编号，部门名称，部门平均工资等级
  
  select T.*,S.GRADE,D.dname
  	from dept D
  	join (
  		select top 2 E.deptno,AVG(E.sal) avg_sal
  		from emp E
  		join dept D
  		on E.deptno = D.deptno
  		join SALGRADE S 
  		on E.sal between S.LOSAL and S.HISAL
  		where E.sal > 1500
  		-- 问：按某个表的某个字段分组后，已连接的其他表的字段都无法使用？加上D.dname,E.sal就能用了	
  		group by E.deptno	-- 按照部门分组了，没有dename
  		having AVG(E.sal) > 2000
  		order by AVG(E.sal) desc
  	) T 
  	on D.deptno = T.deptno
  	join SALGRADE S
  	on T.avg_sal between S.LOSAL and S.HISAL
  

外连接

https://www.cnblogs.com/zccfrancis/p/14669178.html

-- 不仅返回满足连接条件的部分，还返回部分不满足连接条件的元素
-- 内连接：如果前面的后面的不匹配，则不输出
-- 左外连接：以左表为中心，如果左边的某个记录在右边找不到匹配的行，也会有一行输出，输出的东西是空，左边输出左表第一行内容，右边全部输出NULL
-- 左表只要有信息，肯定输出，如果对应的右表没信息，输出空
select * 
	from emp E
	left join dept D
	on E.deptno = D.deptno

完全连接

-- 完全连接
-- 输出的结果包含三部分：
-- 1.两个表中匹配的所有行记录
-- 2.左表中那些在右表中找不到匹配的行的记录，这些记录的右边全为空
-- 3.右表中那些在左表中找不到匹配的行的记录，这些记录的左边全为空

​ 交叉连接

生成没有过滤的笛卡尔积。

​ 自连接

– 自连接（自交）
– 定义：一张表自己和自己连接起来查询数据
– 例子：不准用聚合函数 求薪水最高的员工的信息

– 能用聚合函数
select *
from emp
where sal = (select max(sal) from emp)

– 自连接

-- 自连接（自交）
	-- 定义：一张表自己和自己连接起来查询数据
	-- 例子：不准用聚合函数  求薪水最高的员工的信息

-- 能用聚合函数
select *
	from emp 
	where sal = (select max(sal) from emp)
-- 自连接
select * 
	from emp 
		where empno not in 
		(
			select distinct E1.EMPNO 
			from emp E1
			inner join emp E2	
			on E1.sal < E2.sal	-- 把E1所有的元素依次与E2进行连接，最后只剩下最高的没连接								  （因为E2没有比它大的）
		)

SQL例题 ：https://www.cnblogs.com/Leo-Heng/p/14633254.html

​ 联合

-- 用联合
-- 表和表之间的数据以纵向的方式连接
-- 以前讲的都是横向的
select E1.ename "员工姓名",E1.sal "工资", E2.ename "上司姓名"
	from emp E1
	join emp E2
	on E1.mgr = E2.EMPNO
union
select ename,sal,'已是最大boss' from emp where mgr is NULL
-- 注意：若干个slect 语句要联合成功的话，必须满足的两个条件
	-- 1、这若干个select子句输出的字段数必须是相等的
	-- 2、这若干个select子句输出字段的类型至少是兼容的

分页查询

-- 分页查询
-- 输出工资最高的前三个员工的所有信息
-- 工资从高到低排序，输出工资第4-6的员工信息
-- 工资从高到低排序，输出工资第7-9的员工信息
--  ...
select top 3 * from emp order by sal;	-- 先执行order by，后执行top

-- 工资从高到低排序，输出工资第4-6的员工信息
select top 3 *
	from emp
	where EMPNO not in (select top 3 empno from emp order by sal desc)
	order by sal desc
	
-- 工资从高到低排序，输出工资第7-9的员工信息
select top 3 *
	from emp
	where EMPNO not in (select top 6 empno from emp order by sal desc)
	order by sal desc
	
-- 总结
-- 假设每页显示n条记录，当前要显示的是第n页
select top n *
	from A
	where A_id not in (select top (n - 1) * A_id from emp)

ame,sal,‘已是最大boss’ from emp where mgr is NULL
– 注意：若干个slect 语句要联合成功的话，必须满足的两个条件
– 1、这若干个select子句输出的字段数必须是相等的
– 2、这若干个select子句输出字段的类型至少是兼容的

**分页查询**

```sql
-- 分页查询
-- 输出工资最高的前三个员工的所有信息
-- 工资从高到低排序，输出工资第4-6的员工信息
-- 工资从高到低排序，输出工资第7-9的员工信息
--  ...
select top 3 * from emp order by sal;	-- 先执行order by，后执行top

-- 工资从高到低排序，输出工资第4-6的员工信息
select top 3 *
	from emp
	where EMPNO not in (select top 3 empno from emp order by sal desc)
	order by sal desc
	
-- 工资从高到低排序，输出工资第7-9的员工信息
select top 3 *
	from emp
	where EMPNO not in (select top 6 empno from emp order by sal desc)
	order by sal desc
	
-- 总结
-- 假设每页显示n条记录，当前要显示的是第n页
select top n *
	from A
	where A_id not in (select top (n - 1) * A_id from emp)