去除重复记录
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
}