MySQL(四)

去除重复记录

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 |
+---------------------+

连接查询

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

stunostunameclassnoclassname
1张三1XX学院XX年级1班
2李四1XX学院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. 左外连接(左连接):表示左边的这张表是主表
  2. 右外连接(右连接):表示右边的这张表是主表

案例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

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK79021980-12-17800.00NULL20
7499ALLENSALESMAN76981981-02-201600.00300.0030
7521WARDSALESMAN76981981-02-221250.00500.0030
7566JONESMANAGER78391981-04-022975.00NULL20
7654MARTINSALESMAN76981981-09-281250.001400.0030
7698BLAKEMANAGER78391981-05-012850.00NULL30
7782CLARKMANAGER78391981-09-092450.00NULL10
7788SCOTTANALYST75661987-04-193000.00NULL20
7839KINGPRESIDENTNULL1981-11-175000.00NULL10
7844TURNERSALESMAN76981981-09-081500.000.0030
7876ADAMSCLERK77881987-05-231100.00NULL20
7900JAMESCLERK76981981-12-03950.00NULL30
7902FORDANALYST75661981-12-033000.00NULL20
7934MILLERCLERK77821982-01-231200.00NULL10

部门表:DEPT

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCEDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
select
	d.*
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	e.empno is null;

结果:

DEPTNODNAMELOC
40OPERATIONSBOSTON

3张表连接查询

案例1

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

员工表:EMP e

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK79021980-12-17800.00NULL20
7499ALLENSALESMAN76981981-02-201600.00300.0030
7521WARDSALESMAN76981981-02-221250.00500.0030
7566JONESMANAGER78391981-04-022975.00NULL20
7654MARTINSALESMAN76981981-09-281250.001400.0030
7698BLAKEMANAGER78391981-05-012850.00NULL30
7782CLARKMANAGER78391981-09-092450.00NULL10
7788SCOTTANALYST75661987-04-193000.00NULL20
7839KINGPRESIDENTNULL1981-11-175000.00NULL10
7844TURNERSALESMAN76981981-09-081500.000.0030
7876ADAMSCLERK77881987-05-231100.00NULL20
7900JAMESCLERK76981981-12-03950.00NULL30
7902FORDANALYST75661981-12-033000.00NULL20
7934MILLERCLERK77821982-01-231200.00NULL10

部门表:DEPT d

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCEDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

工资表:SALGRADE s

GRADELOSALHISAL
17001200
212011400
314012000
420013000
530019999
...
	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;

结果:

员工dnamegrade领导
SMITHRESEARCE1FORD
ALLENSALES3BLAKE
WARDSALES2BLAKE
JONESRESEARCE4KING
MARTINSALES2BLAKE
BLAKESALES4KING
CLARKACCOUNTING4KING
SCOTTRESEARCE4JONES
KINGACCOUNTING5NULL
TURNERSALES3BLAKE
ADAMSRESEARCE1SCOTT
JAMESSALES1BLAKE
FORDRESEARCE4JONES
MILLERACCOUNTING2CLARK

子查询

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;
enamesaldeptnograde
SMITH800.00201
ALLEN1600.00303
WARD1250.00302
JONES2975.00204
MARTIN1250.00302
BLAKE2850.00304
CLARK2450.00104
SCOTT3000.00204
KING5000.00105
TURNER1500.00303
ADAMS1100.00201
JAMES950.00301
FORD3000.00204
MILLER1300.00102
第二步:基于以上结果继续按照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;
deptnoavg(s.grade)
103.6667
202.8000
302.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');
enamejob
ALLENSALESMAN
WARDSALESMAN
JONESMANAGER
MARTINSALESMAN
BLAKEMANAGER
CLARKMANAGER
TURNERSALESMAN
第三种:union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
//两个结果相加
enamejob
JONESMANAGER
BLAKEMANAGER
CLARKMANAGER
ALLENSALESMAN
WARDSALESMAN
MARTINSALESMAN
TURNERSALESMAN

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

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个 
enamesal
JONES2975.00
BLAKE2850.00
CLARK2450.00
ALLEN1600.00
TURNER1500.00
MILLER1300.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
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值