mysql复杂查询

本文主要是介绍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 	ajoin 	bon 	a和b的连接条件join 	con 	a和c的连接条件right join  -- 内连接和外连接可以混合使用	don 	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.ENAMEFROM 	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 ) aJOIN 	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');-- unionSELECT 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		...

这篇关于mysql复杂查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！