Data Query Language 3.0 复杂查询
SQL

3.复杂查询

md的抽风大小写变化可无视

3.1 分组查询

需要分组（拆分）地处理获取数据时使用

3.1.1 Group by介绍

语法

select 分组函数，分组列
from 表
group by 分组列表

举例说明

#每个工种的最高工资
select max(salary),job_id
from employees
group by job_id

#每个位置(location_id)的部门个数
select count(*),location_id
from departments
group by location_id

3.1.2 添加分组前的筛选

添加筛选条件where进行更加复杂的筛选
where在group by之前
原始表作为数据源

#查询邮箱中包含a字符的每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id

#查询每个领导手下有奖金的员工的最高工资
select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id

这里相当于只把符合where的行拿过来统计

3.1.2 添加分组后的筛选

利用最后加上having句段对于分组后的函数进行条件筛选
分组后的结果集作为数据源

#显示部门员工数大于2的情况
select count(*),department_id
from employees
group by department_id
having count(*)>2;

更加复杂的查询举例

#查询每个工种有奖金的员工的最高工资大于12000的工种编号和平均工资
#首先查询每个工种有奖金的最高工资
select max(salary), job_id, avg(salary)
from employees
where commission_pct is not null
group by job_id
#根据之前结果进一步筛选
having max(salary)>12000
#查询领导编号大于102的领导手下员工的最低工资大于5555的领导编号以及最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5555

3.1.3 group by接函数

#按照员工姓名长度分组查询员工个数,筛选出个数大于7的
select count(*) 个数,length(last_name) 长度
from employees
group by 长度 #别名阔以直接替换变量
having 个数>7

3.1.4 多字段分组

均符合条件选取，即并查（

#查询每个部门每个工种的员工平均工资
select avg(salary),department_id,job_id
from employees
group by department_id,job_id #随便掉顺序

3.1.5 排序

最后加上order by句段即可

3.2 连接查询

  • 多表连接起来查询，简称连接查询（跨表查询）
  • 连起来：from接两个及以上的表，这时候按照运行顺序（先运行from），会将两个表合并先，即：
  • 直接查询阔以，就是会出现“笛卡尔积”

3.2.1 内连接（sql92标准）

内连接查询操作只列出与连接条件匹配的数据行：匹配就列出，所以存在一个表的一行与另一个表中多行都匹配，于是都列出的情况

3.2.1.1 等值连接

（接上文）所以要加连接条件才能连接查询：where + 表名1.列名1=表名2.列名2，最简单的连接条件

等值连接的连接条件为取交集部分

别名：可以但不要混用

#查询员工名、工种号、工种名
select last_name,e.job_id,job_title#加前置解决字段ambiguous问题
from employees e, jobs j#直接在此起别名，别名和原名称无法混用
where e.job_id=j.job_id

where可以加and进一步筛选、进一步连接（三表）

#查询员工名、部门名、和所在城市
select last_name,department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and city like 's%'#一路加and都行

与分组查询搭配

#查询每个城市的部门个数
select count(*) 数量, city
from departments d,location l
group by city
#这样会导致直接笛卡尔积了，每个城市都27个部门
select count(*) 数量, city
from departments d,locations l
where d.location_id=l.location_id
group by city
#加上连接条件就有意义了

having、order都是可以继续添加的~

3.2.1.2 非等值连接

where的=换成别的

#查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND grade_level='A'

3.2.1.3 自连接

自己连接自己，即赢两次
哦不连两次
务必别名区分避免报错

#查询员工名和领导名（员工名——领导id——领导名）
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manager_id=m.employee_id

3.2.1 内连接（sql99标准）

inner join

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

如果查询表1.*，那么结果不会包含表2的内容，即使已经内连接了
等值连接

#查询员工名、部门名
SELECT last_name, department_name
FROM employees e
INNER JOIN departments d #起别名
ON e.department_id=d.department_id

三表连接，添加inner join即可
非等值连接

#查询工资级别
select salary,grade_level
from employees e
inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal

自连接

#查询员工名和领导名（员工名——领导id——领导名）
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e
JOIN employees m
ON e.manager_id=m.employee_id

3.2.2 外连接(sql99标准新增)

  • 外连接查询的结果为主表中的所有记录，作用就是把匹配的显示出来，没匹配的显示null

  • 注意，存在这种情况，主表一条记录匹配从表多条记录，这多条记录均会显示出来
    file

  • left join左边是主表

  • right join右边是主表

3.2.2.1 左外连接

#没有男友记录的女生情况
select b.name
from beauty b
left outer join boys bo
on b.boyfriend_id=bo.id
where bo.id is null #筛选id匹配为null的

3.2.2.1 右外连接

#查询哪个部门没有员工
select d.*,e.employee_id
from employees e
right outer join departments d
on d.department_id=e.department_id
where e.employee_id is null

3.2.2.1 全外连接(mysql中不支持)

3.2.2 交叉连接

cross join
形成笛卡尔积

3.3 子查询

出现在其它语句中的select语句，称为子查询
子查询一般放在小括号内

select first_name
from employees
where department_id IN
(
    select department_id
    from departments
    where location_id=1700
)

3.3.1 select后

select只支持标量子查询

#每个部门的员工个数
#原始方法:null不好表示
SELECT d.department_id,COUNT(*)
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
GROUP BY d.department_id
#子查询
SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id=d.department_id #加上筛选：刚好等于“当前”departmentid
) 数量
FROM departments d

3.3.2 from后

#查询每个部门的平均工资的工资等级
#查询每个部门平均工资
select avg(salary),department_id
from employees
group by department_id 
#连接结果与job_grade
select ag_dep.*,g.grade_level
from (
select avg(salary) ag,department_id
from employees
group by department_id 
) ag_dep #这个表必须起别名以查找
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal

3.3.3 where、having后

标量子查询
搭配单行操作符使用

#谁的工资比abel高
select *
from employees
where salary>(
    select salary
    from employees
    where last_name='Abel'
)

可以用and添加条件

#查询job_id与141号员工一样，salary比143多的员工姓名、job_id与工资
SELECT last_name, job_id, salary
FROM employees
WHERE job_id=(
    SELECT job_id
    FROM employees
    WHERE employee_id =141
)
AND salary>(
    SELECT salary
    FROM employees
    WHERE employee_id =143
)

where语句中不能直接添加分组函数，只能用子查询表达

列子查询（多行子查询）
搭配多行操作符使用（in、any、some、all）

  • in
#返回location_id是1500或1700的部门中的所有员工姓名
select last_name
from employees
where department_id in(
    SELECT DISTINCT department_id#去重好习惯
    FROM departments
    WHERE location_id IN(1500,1700)
)
  • any：任意一个
#返回其它部门中比job_id为IT_PROG部门任意工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary
from employees
where salary<any(
    select distinct salary
    from employees
    where job_id='IT_PROG'
)
and job_id<>'IT_PROG'
  • all：所有

行子查询

#查询员工编号最小且工资最高的员工信息
select *
from employees
where (employee_id,salary)=(
    select min(employee_id),max(salary)
    from employees
)/

3.3.4 exist后(相关子查询)

实际为判断是否存在的布尔查询

exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

select exists(select employee_id from employees)

在where后使用，表示筛选出true的条件，过滤掉false的条件

  • 子查询语句返回结果不为空，说明where条件成立就会执行主sql语句
  • 如果为空就表示where条件不成立，sql语句就不会执行
#查询没有女朋友的男生信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
    SELECT boyfriend_id
    FROM beauty
)
#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM beauty b
    WHERE bo.id=b.boyfriend_id#这里的筛选条件的作用是什么
)

3.4 分页查询

s1mple

分页查询经常用于实际的web应用，有的时候你并不需要将所有符合条件的列显示出来（例如搜索）

  • 用法：
select 查询列表
from 表
join 表2
where 筛选条件
group by 分组字段
having 分组后筛选
order by
limit offset,size #起始索引与条目数

offset：要显示的条目起始索引（从零开始）（零的话可以省略）
size：要显示的条目数

#前五条员工信息
SELECT * FROM employees LIMIT 0,5
SELECT * FROM employees LIMIT 5
#第十一条至第二十五条
SELECT * FROM employees LIMIT 10,15
#有奖金的员工信息，且工资较高的前十名显示
select *
from employees
where commission_pct is not null
order by salary desc
limit 0,10

3.5 联合查询

联合查询，将多条查询语句（各自独立）的结果合并为一个结果
来自网络：每一条select语句获取的字段数必须严格一致(但是字段类型无关)
实例

#部门编号大于90，或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90
#联合查询
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90

应用
查询的结果来自多个表，只要字段数一致，就可以单纯的拼接，无视字段名不一致这样的情况

常见错误

  • 不打逗号
  • 聚类函数的使用
  • 派生表格没取别名
  • 对于一个刚刚生成的聚类函数，不能在同一句查询中的条件里马上使用之
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																				
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇