SQL进阶-优快云博客

去除重复记录

select distinct job from emp;//distinct关键字，去除重复记录
+-----------+
|    job    |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

错误例子

select ename,distinct job from emp;
以上的sql语句是错误的
注意：distinct 只能出现在所有字段的最前面!!!

案例1

select deptno,job from emp order by deptno;
+---------+-----------+
| deptno  |    job    |
+---------+-----------+
|      10 | CLERK     |
|      10 | PRESIDENT |
|      10 | MANAGER   |
|      20 | ANALYST   |
|      20 | CLERK     |
|      20 | ANALYST   |
|      20 | MANAGER   |
|      20 | CLERK     |
|      30 | MANAGER   |
|      30 | SALESMAN  |
|      30 | SALESMAN  |
|      30 | SALESMAN  |
|      30 | CLERK     |
|      30 | SALESMAN  |
+---------+-----------+

select distinct deptno,job from emp;//deptno和job联合起来去重
+---------+-----------+
| deptno  |    job    |
+---------+-----------+
|      20 | CLERK     |
|      30 | SALESMAN  |
|      20 | MANAGER   |
|      30 | MANAGER   |
|      10 | MANAGER   |
|      10 | PRESIDENT |
|      30 | CLERK     |
|      10 | CLERK     |
+---------+-----------+

案例2：统计岗位的数量

select count(distinct job) from emp;

+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

连接查询

概述：在实际开发中，大部分情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。一般一个业务都会对应多张表，比如：学生和班级，起码两张表。

stuno	stuname	classno	classname
1	张三	1	XX学院XX年级1班
2	李四	1	XX学院XX年级1班
…	…	…	…

学生和班级信息存储到一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。

连接查询的分类

根据语法出现的年代来划分的话，包括：SQL92、SQL99（比较新的语法）

根据表的连接方式来划分，包括：内连接、外连接、全连接（很少用）

内连接又分为：等值连接、非等值连接、自连接

外连接又分为：左外连接（左连接）、右外连接（右连接）

笛卡尔积现象

笛卡尔积现象：又叫笛卡尔乘积现象

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

select ename.deptno from emp;
EMP表（有14个人）
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| ...    |    ... |
+--------+--------+

select * from dept;
DEPT表
+--------+------------+----------+
| DEOINO |    DNAME   |   LOC    |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

select ename,dname from emp,dept;//这里没有条件限制，会造成有56（14 * 4）条数据。
+-------+------------+
| ename |    dname   | 
+-------+------------+
| SMITH | ACCOUNTING |
| SMITH | PERSEARCH  |
| SMITH | SALES      |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | PERSEARCH  |
| ...   | ...        | 
+-------+------------+
笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

关于表的别名：

select e.ename,d.dname from emp e,dept d;

表的别名的好处：
	1.执行效率高
	2.可读性好

如何避免笛卡尔积现象

加条件进行过滤

思考：避免笛卡尔积现象会减少记录的匹配次数吗？

不会！！！次数还是56次，只不过显示的是有效记录（匹配次数没有减少，效率没有提高）

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

select
	e.ename,d.dname
from
	emp e,dept d;
where
	e.deptno = d.deptno;//SQL92，以后不用

内连接之等值连接

最大特点是：条件是等量关系

案例：查询每个员工的部门名称，要求显示员工名和部门名

SQL92：（太老了，不用了）
select
	e.ename,d.dname
from
	emp e,dept d;
where
	e.deptno = d.deptno;
	
SQL99:（常用的）
select
	e.ename,d.dname
from
	emp e
inner join//inner可以省略
	dept d
on
	e.deptno = d.deptno;

语法：
...
	A
join
	B
on
	连接条件
where
	...

SQL99语法结构更清晰一些，表的连接条件和后来的where条件分离了。

内连接之非等值连接

最大特点是：连接条件中的关系是非等量关系

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

select ename,sal from emp;
+-------+---------+
| ename |   sal   |
+-------+---------+
| SMITH |  800.00 |
| ALLEN | 1600.00 |
| WARD  | 1250.00 |
| ...   | ...     |
+-------+---------+

select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

select
	e.ename,e.sal,s.grade
from
	emp e
inner join
	salgrade s
on
	e.sal between s.losal and s.hisal;
//结果：
+-------+---------+-------+
| ename |   sal   | grade |
+-------+---------+-------+
| SMITH |  800.00 |     1 |
| ALLEN | 1600.00 |     3 |
| WARD  | 1250.00 |     2 |
| ...   | ...     |   ... |
+-------+---------+-------+

内连接之自连接

最大的特点是：一张表看做是两张表。自己连接自己。

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名

select empno,ename,mgr from emp;
emp a 员工表
+-------+-------+------+
| empno | ename |  mgr |
+-------+-------+------+
|  7369 | SMITH | 7902 |
|  7499 | ALLEN | 7698 |
|  7521 | WARD  | 7698 |
|  ...  | ...   | ...  |
|  7698 | BLAKE | 7839 |
|  7902 | FORD  | 7566 |
+-------+-------+------+

emp b 领导表
+-------+-------+------+
|  ...  | ...   | ...  |
|  7698 | BLAKE | 7839 |
|  7902 | FORD  | 7566 |
+-------+-------+------+
（一张表看成两张表）
员工的领导编号 = 领导的员工编号

select
	a.ename as '员工名',b.ename as '领导名'
from
	emp a
inner join
	emp b
on
	a.mgr = b.empno;
//结果：
+-------+--------+
| 员工名 | 领导名 |
+-------+--------+
| SMITH | FORD   |
| ALLEN | BLAKE  |
| WARD  | BLAKE  |
| ...   | ...    |
+-------+--------+
//KING是老板，没有上级，没有查询出来

外连接

内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来。AB两张表没有主副之分，是平等的。

外连接：假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类

左外连接（左连接）：表示左边的这张表是主表
右外连接（右连接）：表示右边的这张表是主表

案例1

找出每个员工的上级领导（所有员工必须全部查询出来）

emp a 员工表
+-------+-------+------+
| empno | ename |  mgr |
+-------+-------+------+
|  7369 | SMITH | 7902 |
|  7499 | ALLEN | 7698 |
|  7521 | WARD  | 7698 |
|  ...  | ...   | ...  |
|  7698 | BLAKE | 7839 |
|  7839 | KING  | NULL |//老板，没有上级
|  7902 | FORD  | 7566 |
+-------+-------+------+

emp b 领导表
+-------+-------+------+
|  ...  | ...   | ...  |
|  7698 | BLAKE | 7839 |
|  7839 | KING  | NULL |
|  7902 | FORD  | 7566 |
+-------+-------+------+

//内连接
select
	a.ename '员工',b.ename '领导'
from
	emp a
join
	emp b
on
	a.mgr = b.empno;

//外连接:(左外连接，左连接)
select
	a.ename '员工',b.ename '领导'
from
	emp a
left outer join //outer 可以省略
	emp b
on
	a.mgr = b.empno;

//外连接:(右外连接，右连接)
select
	a.ename '员工',b.ename '领导'
from
	emp a
right join
	emp b
on
	a.mgr = b.empno;

//结果：
+-------+--------+
| 员工名 | 领导名 |
+-------+--------+
| SMITH | FORD   |
| ALLEN | BLAKE  |
| WARD  | BLAKE  |
| ...   | ...    |
| BLAKE | KING   |
| KING  | NULL   |
| ...   | ...    |
+-------+--------+

外连接最重要的特点：主表的数据无条件的全部查询出来。

案例2

找出哪个部门没有员工

员工表:EMP

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-09-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-05-23	1100.00	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950.00	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000.00	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1200.00	NULL	10

部门表：DEPT

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCE	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

select
	d.*
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	e.empno is null;

结果：

DEPTNO	DNAME	LOC
40	OPERATIONS	BOSTON

3张表连接查询

案例1

找出每一个员工的部门名称以及工资等级

员工表：EMP e

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800.00	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02	2975.00	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850.00	NULL	30
7782	CLARK	MANAGER	7839	1981-09-09	2450.00	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000.00	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000.00	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-05-23	1100.00	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950.00	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000.00	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1200.00	NULL	10

部门表：DEPT d

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCE	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

工资表：SALGRADE s

GRADE	LOSAL	HISAL
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

...
	A
join
	B
join
	C
on
	...
注意：以上表示A表和B表先进行表连接，连接之后A表继续和C表进行表连接。

select
	e.ename.d.dname,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;

案例2

找出每一个员工的部门名称、工资等级以及上级领导

select
	e.ename '员工',d.dname,s.grade,e1.ename '领导'
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp e1
on
	e.mgr = e1.empno;

结果：

员工	dname	grade	领导
SMITH	RESEARCE	1	FORD
ALLEN	SALES	3	BLAKE
WARD	SALES	2	BLAKE
JONES	RESEARCE	4	KING
MARTIN	SALES	2	BLAKE
BLAKE	SALES	4	KING
CLARK	ACCOUNTING	4	KING
SCOTT	RESEARCE	4	JONES
KING	ACCOUNTING	5	NULL
TURNER	SALES	3	BLAKE
ADAMS	RESEARCE	1	SCOTT
JAMES	SALES	1	BLAKE
FORD	RESEARCE	4	JONES
MILLER	ACCOUNTING	2	CLARK

子查询

select语句当中嵌套select语句，被嵌套的select语句是子查询。

select
	..(select).
from
	..(select).
where
	..(select).

where语句中使用子查询

案例：找出高于平均薪资的员工信息

第一步：找出平均薪资
select avg(sal) from emp;

第二步：where过滤
select * from emp where sal > 2073.214286;

第一步、第二步合并：
select * from emp where sal > (select avg(sal) from emp);

from后面嵌套子查询

案例

找出每个部门平均薪资的薪资等级

第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno |    avgsal   |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal
select 
	t.*,s..gread
from
	(select deptno,avg(sal) as avgsal from emp group by deptno) t //这个查询结果是一张表	
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno |    avgsal   | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |   3   |
|     10 | 2916.666667 |   4   |
|     20 | 2175.000000 |   4   |
+--------+-------------+-------+

案例

找出每个部门平均的薪资等级

第一步：找出每个员工的薪水等级
select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

ename	sal	deptno	grade
SMITH	800.00	20	1
ALLEN	1600.00	30	3
WARD	1250.00	30	2
JONES	2975.00	20	4
MARTIN	1250.00	30	2
BLAKE	2850.00	30	4
CLARK	2450.00	10	4
SCOTT	3000.00	20	4
KING	5000.00	10	5
TURNER	1500.00	30	3
ADAMS	1100.00	20	1
JAMES	950.00	30	1
FORD	3000.00	20	4
MILLER	1300.00	10	2

第二步：基于以上结果继续按照deptno分组，求grade平均值
select 
	e.depptno,avg(s.grade)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
 	e.deptno;

deptno	avg(s.grade)
10	3.6667
20	2.8000
30	2.5000

select后面嵌套子查询

案例

找出每个员工所在部门的名称，要求显示员工名和部门名

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

union用法

可以将查询结果集相加

案例

找出工作岗位是SALESMAN和MANAGER的员工

第一种：
select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
第二种：
select ename,job from emp where job in('SALESMAN'.'MANAGER');

ename	job
ALLEN	SALESMAN
WARD	SALESMAN
JONES	MANAGER
MARTIN	SALESMAN
BLAKE	MANAGER
CLARK	MANAGER
TURNER	SALESMAN

第三种：union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
//两个结果相加

ename	job
JONES	MANAGER
BLAKE	MANAGER
CLARK	MANAGER
ALLEN	SALESMAN
WARD	SALESMAN
MARTIN	SALESMAN
TURNER	SALESMAN

两张不相干的表中的数据拼接在一起显示

select ename from emp
union
select dname from dept;

错误写法

select ename,sal from emp
union
select dname from dept;
ERROR 1222 （21000）：The use SELECT statement have a different number of coulums

limit（重点中的重点，以后分页查询全靠它了）

limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）

limit的作用：取结果集中的部分数据

语法机制

limit startIndex,length

startIndex表示起始位置
length表示取几个

案例1

取出工资前5名的员工（思路：降序取前5个）

工资降序：
select ename,sal from emp order by sal desc;

取前5个：
select ename,sal from emp order by sal desc limit 0,5;

select ename,sal from emp order by sal desc limit 5;//跟上一条结果一样

limit是SQL语句最后执行的一个环节

select			5
	...
from			1
	...
where			2
	...
group by		3
	...
having			4
	...
order by		6
	...
limit			7
	...

案例2

找出工资排名在第4到第9名的员工

select ename,sal from emp order by sal desc limit 3,6;//4到9是6个

ename	sal
JONES	2975.00
BLAKE	2850.00
CLARK	2450.00
ALLEN	1600.00
TURNER	1500.00
MILLER	1300.00

通用的标准分页SQL

每页显示3条记录

第1页：0,3

第2页：3,3

第3页：6,3

第4页：9,3

第5页：12,3

每页显示pageSize条记录

第pageNO页：(pageNO - 1) * pageSize，pageSize

pageSize是每页显示多少条记录

pageNo是显示第几页

//java代码
{
	int pageNo = 2;//页码是2
    int pageSize = 10;//每页显示10条
    
    limit (pageNo - 1) * pageSize,pageSize
}