mysql复杂查询

1.分组查询

先分组，然后对每一组的数据进行操作

涉及到两个字句：group by & having

关键字顺序：

select ... 
-- 后续执行顺序
from  ... 
where ...
group by ... 
having ...
order by ... ;

从某张表查询数据，经过where筛选信息，对筛选出的信息进行分组，分组后使用having继续筛选，select查询出结果后进行排序输出

1. group by

例：每个岗位工资总和

SELECT JOB,sum(SAL) FROM EMP GROUP BY JOB;
-- 先从EMP表查询数据，根据job字段进行分组，然后对每一组数据进行求和

在一条SQL语句中，如有group by 语句，select 后只能跟参与分组的字段以及分组函数，其他无关的不加

例：每个部门的最高薪资

SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO;

例：每个部门，不同工作岗位的最高薪资

技巧：两个字段联合成一个字段看，即两个字段联合分组

SELECT DEPTNO,JOB,MAX(SAL) FROM EMP GROUP BY DEPTNO,JOB ORDER BY DEPTNO;

2. having

可以用于对分组后的数据进一步过滤

不能单独使用，也不能代替where

having 必须喝group by联合使用

例：找出每个部门最高薪资，显示大鱼3000的

1.找出每个部门最高薪资 2.设定条件大于3000

SELECT DEPTNO,MAX(SAL) FROM EMP WHERE SAL >= 3000 GROUP BY DEPTNO; -- where 效率更高
SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO HAVING max(sal) >= 3000; -- having

例：找出每个部门平均薪资，显出高于2500的

1.找出平均薪资 2.显示平均薪资高于2500

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2500;

例：找出每个岗位的平均薪资，要求显示大于1500的，除了manage外，按照平均薪资降序排列

SELECT 
	JOB,AVG(SAL) 
from 
	emp 
WHERE 
	job <> \'MANAGER\' 
GROUP BY 
	JOB 
HAVING 
	AVG(SAL)>1500 
ORDER BY 
	AVG(SAL) DESC;

2.连接查询 : 跨表查询，多张表联合查询数据

表的连接方式主要有以下几点：

1内连接

等值连接

非等值连接

自连接

2外连接

左外连接(左连接)

右外连接(右连接)

3全连接(了解)

*笛卡尔积现象

两张表连接查询，但无条件限制的时候，最终查询结果条数是两张表条数相乘结果

表的连接次数越多，效率越低

避免笛卡尔积现象：使用条件约束结果，减少连接次数，提高效率

例：查询每个员工所在部门名称（EMP，DEPT）

SELECT DNAME,ENAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- 更高效的方法如下
SELECT DEPT.DNAME,EMP.ENAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- 表取别名可提高查询效率
SELECT d.DNAME,e.ENAME FROM EMP e ,DEPT d WHERE e.DEPTNO = d.DEPTNO; -- SQL92语法

查询匹配次数并没有比无条件时候少

1. 内连接

（两张表连接没有主次关系。平等的。）

特点：内连接的特点：完成能够匹配上这个条件的数据查询出来。

1.1 内连接之等值连接（条件为等量关系）

例：查询每个员工所在部门名称，显示员工名和部门名？

emp表和dept表进行连接，条件：emp.deptno=dept.deptno

SELECT e.ENAME,d.DNAME FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO; -- SQL92-- sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。
SELECT e.ENAME,d.DNAME FROM EMP e (INNER) JOIN DEPT d ON e.DEPTNO=d.DEPTNO; -- SQL99-- sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加whereSELECT e.ENAME,d.DNAME FROM EMP e INNER JOIN DEPT d ON e.DEPTNO=d.DEPTNO; -- 内连接inner可以省略

SQL99语法：
select
…
from
a
join
b
on
a和b的连接条件
where
筛选条件

1.2 内连接之非等值连接(条件并非等量关系)

例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

SELECT e.ENAME,e.SAL,s.GRADE from EMP e JOIN SALGRADE s ON e.SAL BETWEEN s.LOSAL AND s.HISAL;

1.3 内连接之自连接

例：查询员工上级领导，要求显示员工名字和领导名字

一张表当成两张表处理，通过不同的别名当成两张表去进行连接查询

SELECT a.ENAME,b.ENAME FROM EMP a JOIN EMP b ON a.MGR=b.EMPNO -- on后筛选：员工上级领导的编号等于领导自己的编号

2. 外连接

2.1 右外连接(右连接)RIGHT

例：查询每个员工所在部门名称，显示员工名和所有部门名

select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno; -- outer可以省略

right表示将join关键字右边的这张表看成主表，主要是为了将右边dept这张表的数据全部查询出来，捎带着关联查询左边的表emp。
在外连接当中，两张表连接，产生了主次关系。

任何一个右连接都有左连接的写法。

2.2 左外连接(左连接)LEFT

select e.ename,d.dname from dept d left OUTER join emp e on e.deptno = d.deptno; -- outer可以省略

任何一个左连接都有右连接的写法。

*外连接查询结果条数一定大于等于内连接的查询结果条数

例：查询员工上级领导，要求显示员工名字和领导名字

SELECT e.ENAME,m.ENAME FROM EMP e LEFT JOIN EMP m ON e.MGR=m.EMPNO

3. 全连接union /union all

-- union会自动将完全重复的数据去除掉
(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB );
-- union all会保留那些重复的数据
(select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB );

注意事项

1.通过union连接的SQL它们分别单独取出的列数必须相同；
2.不要求合并的表列名称相同时，以第一个sql 表列名为准；
3.使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；
4.被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；(select id,name from A order by id) union all (select id,name from B order by id); //没有排序效果(select id,name from A ) union all (select id,name from B ) order by id; //有排序效果

4. 多张表连接查询

语法：

select 	... 
from 	a
join 	b
on 	a和b的连接条件
join 	c
on 	a和c的连接条件
right join	d-- 内连接和外连接可以混合使用
on 	a和d的连接条件

例：找出每个员工的部门名称和工资等级，显示员工名，部门名，薪资，薪资等级

SELECT 	
e.ENAME,d.DNAME,e.SAL,s.GRADE 
FROM 	
EMP e 
JOIN 	
DEPT d 
ON 	
e.DEPTNO=d.DEPTNO 
JOIN 	
SALGRADE s 
ON 	
e.SAL 
BETWEEN 
s.LOSAL AND s.HISAL

例：找出每个员工的部门名称和工资等级以及其上级领导，显示员工名，领导名，部门名，薪资，薪资等级

SELECT 	e.ENAME,d.DNAME,e.SAL,s.GRADE ,l.ENAME
FROM 	
EMP e 
JOIN 	
DEPT d 
ON 	
e.DEPTNO=d.DEPTNO 
JOIN 	
SALGRADE s 
ON 	
e.SAL 
BETWEEN 
s.LOSAL AND s.HISALLEFT 
JOIN -- 用内连接会缺失一条king的数据，要用外连接	
EMP l 
ON	
e.MGR=l.EMPNO

3.子查询：select语句中嵌套select语句，被嵌套的select语句称为子查询。

子查询可以出现在select，from，where后

3.1 where中的子查询

例：找出比最低工资高的员工姓名和薪资

先查询最低工资，然后比较

SELECT ename,SAL FROM EMP WHERE SAL>(SELECT MIN(SAL) FROM EMP)

3.2 from中的子查询

from后面的子查询结果可以作为一个临时表

例：找出每个岗位平均薪资的薪资等级

第一步：找出每个岗位的平均工资（按照岗位分组求平均值）

第二步：把以上的查询结果就当做一张真实存在的表并取别名来方便调用，其中job和av_sal就是表中可以查询的字段

SELECT	
a.JOB,	a.avg_sal,	sg.GRADE 
FROM	
( 	SELECT 
	job, AVG( SAL ) avg_sal 
	FROM 
	EMP 
	GROUP BY 
	JOB 
) a
JOIN 	
SALGRADE sg 
ON 	
a.avg_sal 
BETWEEN 
sg.LOSAL AND sg.HISAL

3.3 select中的子查询

例：找出每个员工的部门名称，要求显示员工名，部门名？

select
e.ename,e.deptno,(	
	select 
	d.dname 
	from 
	dept d 
	where 
	e.deptno = d.deptno
) as dname
from
emp e;

4、union合并查询结果集

例：查询工作岗位是MANAGER和SALESMAN的员工？

SELECT ENAME,JOB FROM EMP WHERE job = \'MANAGER\' or job = \'SALESMAN\';SELECT ENAME,JOB FROM EMP WHERE job IN (\'MANAGER\',\'SALESMAN\');-- union
SELECT ENAME,JOB FROM EMP WHERE job = \'MANAGER\'unionSELECT ENAME,JOB FROM EMP WHERE job = \'SALESMAN\';

union可以减少表的连接次数，即减少笛卡尔积现象，一般是完成两个结果的拼接

union一般是对两个结果总数进行相加得到最终结果集

注意事项：

	-- 错误的：union在进行结果集合并的时候，要求两个结果集的列数相同。	select ename,job from emp where job = \'MANAGER\'	union	select ename from emp where job = \'SALESMAN\';	-- MYSQL可以，oracle语法严格 ，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。	select ename,job from emp where job = \'MANAGER\'	union	select ename,sal from emp where job = \'SALESMAN\';

5.limit

5.1limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。

5.2用法

例：薪资降序，取前五名员工

SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 5;

完整语法：limit startindex,length

startIndex是起始下标，length是长度。
起始下标从0开始(可不写，缺省用法：limit 5; — 取前五)。

5.3 mysql中limit是在order by 后执行

例：工资排名3到5的员工信息

SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 2,3-- 2表示下标为2的元素开始，也就是第三个元素

例：工资排名[5-9]的员工信息

SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 4,5;

5.4分页

每页显示3条记录
第1页：limit 0,3 [0 1 2]
第2页：limit 3,3 [3 4 5]
第3页：limit 6,3 [6 7 8]
第4页：limit 9,3 [9 10 11]

每页显示pageSize条记录
第pageNo页：limit (pageNo – 1) * pageSize , pageSize

def pagecut(pageno,pagesize):    
	startindex = (pageno-1)*pagesize    
	sql = "SELECT ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT %d,%d;" %(startindex,pagesize)    
	return sql

记公式：
limit (pageNo-1)*pageSize , pageSize

语句执行顺序大总结

select 
	...
from
	...
join
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...

本文链接：https://www.cnblogs.com/fancyhome/p/14988216.html

mysql复杂查询

1.分组查询

1. group by

例：每个岗位工资总和

例：每个部门的最高薪资

例：每个部门，不同工作岗位的最高薪资

2. having

例：找出每个部门最高薪资，显示大鱼3000的

例：找出每个部门平均薪资，显出高于2500的

例：找出每个岗位的平均薪资，要求显示大于1500的，除了manage外，按照平均薪资降序排列

2.连接查询 : 跨表查询，多张表联合查询数据

表的连接方式主要有以下几点：

1内连接

2外连接

3全连接(了解)

*笛卡尔积现象

避免笛卡尔积现象：使用条件约束结果，减少连接次数，提高效率

例：查询每个员工所在部门名称（EMP，DEPT）

1. 内连接

1.1 内连接之等值连接（条件为等量关系）

例：查询每个员工所在部门名称，显示员工名和部门名？

1.2 内连接之非等值连接(条件并非等量关系)

例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

1.3 内连接之自连接

例：查询员工上级领导，要求显示员工名字和领导名字

2. 外连接

2.1 右外连接(右连接)RIGHT

2.2 左外连接(左连接)LEFT

*外连接查询结果条数一定大于等于内连接的查询结果条数

例：查询员工上级领导，要求显示员工名字和领导名字

3. 全连接union /union all

注意事项

4. 多张表连接查询

例：找出每个员工的部门名称和工资等级，显示员工名，部门名，薪资，薪资等级

例：找出每个员工的部门名称和工资等级以及其上级领导，显示员工名，领导名，部门名，薪资，薪资等级

3.子查询：select语句中嵌套select语句，被嵌套的select语句称为子查询。

子查询可以出现在select，from，where后

3.1 where中的子查询

例：找出比最低工资高的员工姓名和薪资

3.2 from中的子查询

例：找出每个岗位平均薪资的薪资等级

3.3 select中的子查询

例：找出每个员工的部门名称，要求显示员工名，部门名？

4、union合并查询结果集

例：查询工作岗位是MANAGER和SALESMAN的员工？

5.limit

5.1limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。

5.2用法

例：薪资降序，取前五名员工

完整语法：limit startindex,length

5.3 mysql中limit是在order by 后执行

例：工资排名3到5的员工信息

例：工资排名[5-9]的员工信息

5.4分页

语句执行顺序大总结

mysql复杂查询的更多相关文章

随机推荐

热门专题

目录导航