第二天

1、关于查询结果集的去重

注意：原表数据不会被修改，只是查询结果去重。
去重需要使用一个关键字：distinct

mysql> select distinct job from emp;

distinct只能出现在所有字段的最前方。

// 这样编写是错误的，语法错误。

mysql> select ename,distinct job from emp;

// distinct出现在job,deptno两个字段之前，表示两个字段联合起来去重。

mysql> select distinct job,deptno from emp;

统计一下工作岗位的数量？

	select count(distinct job) from emp;
	
	+---------------------+
	| count(distinct job) |
	+---------------------+
	|                   5 |
	+---------------------+

2、连接查询

2.1、什么是连接查询？

从一张表中单独查询，称为单表查询。

emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。

这种跨表查询，多张表联合起来查询数据，被称为连接查询。

2.2、连接查询的分类？

根据语法的年代分类：
	SQL92：1992年的时候出现的语法
	SQL99：1999年的时候出现的语法
	我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子)

根据表连接的方式分类：
	内连接：
		等值连接
		非等值连接
		自连接

	外连接：
		左外连接（左连接）
		右外连接（右连接）

	全连接（不讲）

2.3、当两张表进行连接查询时，没有任何条件的限制会发生什么现象？（笛卡尔积现象）

案例：查询每个员工所在部门名称？

两张表连接没有任何条件限制：
	select ename,dname from emp, dept;
	+--------+------------+
	| ename  | dname      |
	+--------+------------+
	| SMITH  | ACCOUNTING |
	| SMITH  | RESEARCH   |
	| SMITH  | SALES      |
	| SMITH  | OPERATIONS |
	| ALLEN  | ACCOUNTING |
	| ALLEN  | RESEARCH   |
	| ALLEN  | SALES      |
	| ALLEN  | OPERATIONS |
	...
	56 rows in set (0.00 sec)
	14 * 4 = 56

	当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是
	两张表条数的乘积，这种现象被称为：笛卡尔积现象。（笛卡尔发现的，这是
	一个数学现象。）

关于表的别名：（把上面56条记录的语句别名修饰）

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

表的别名有什么好处？

第一：执行效率高；
第二：可读性好。

2.4、怎么避免笛卡尔积现象？(加筛选条件)

连接时加条件，满足这个条件的记录被筛选出来！
select 
	e.ename,d.dname 
from 
	emp, dept
where
	e.deptno =d.deptno;

思考：最终查询的结果条数是14条，但是匹配的过程中，匹配的次数减少了吗？

还是56次，只不过进行了四选一。次数没有减少（只是显示出需要的）。

注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的
连接次数。

2.5、内连接之等值连接。(条件是等量关系)

案例：查询每个员工所在部门名称，显示员工名和部门名？
emp e和dept d表进行连接。条件是：e.deptno = d.deptno

SQL92语法：
select 
	e.ename,d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。

SQL99语法：

	select 
		...
	from
		a
	join
		b
	on
		a和b的连接条件
	where
		筛选条件

sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

SQL99语法：
select 
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;

//inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）

select 
	e.ename,d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno; // 条件是等量关系，所以被称为等值连接。

2.6、内连接之非等值连接（连接条件中的关系时非等量关系- - - 范围）

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

select 
	e.ename, e.sal, s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。

2.7、内连接之自连接（一张表看做两张表，自己连自己）

案例：查询员工的上级领导，要求显示员工名和对应的领导名？
（下面的写法是内连接- - - 输出不包含KING）

select 
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
join
	emp b
on
	a.mgr = b.empno; //员工的领导编号 = 领导的员工编号

2.8、外连接（主、副之分）

什么是外连接，和内连接的区别？

内连接：

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

外连接：（主表数据无条件的全部查询出来）

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

外连接的分类？

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

（左连接有右连接的写法，右连接有左连接的写法）

案例：查询员工的上级领导，要求显示员工名和对应的领导名？
（要包含KING- - - 故使用外连接 [主查员工，副查上级领导，即使没有上级领导，也要输出KING]）

select
	a.ename '员工',b.ename '领导'
from
	emp a
left outer join
	emp b
on
	a.mgr = b.empno;

left代表：（right同理）
 将join关键字左边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询右边的表。

outer可以省略，可读性高

思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数？
  正确。

案例：查询哪个部门没有员工？

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

2.9、三张表，四张表怎么连接？

语法：
	select 
		...
	from
		a
	join
		b
	on
		a和b的连接条件
	join
		c
	on
		a和c的连接条件
	right join
		d
	on
		a和d的连接条件
	
	一条SQL中内连接和外连接可以混合。都可以出现！

案例：找出每一个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级？

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

案例：找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级？

select 
	e.ename,e.sal,d.dname,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.hisal
left join
	emp l
on
	e.mgr = l.empno;

3、子查询

3.1、什么是子查询？
  select语句中嵌套select语句，被嵌套的select语句称为子查询。

3.2、子查询都可以出现在哪里呢？

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

3.3、where子句中的子查询

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

	select 
		ename,sal
	from
		emp 
	where
		sal > min(sal);

ERROR 1111 (HY000): Invalid use of group function
where子句中不能直接使用分组函数。

实现思路：

	第一步：查询最低工资是多少
		select min(sal) from emp;
		+----------+
		| min(sal) |
		+----------+
		|   800.00 |
		+----------+
	第二步：找出>800的
		select ename,sal from emp where sal > 800;
	
	第三步：合并
		select ename,sal from emp where sal > (select min(sal) from emp);

3.4、from（跟的是表）子句中的子查询

  注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

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

第一步：找出每个岗位的平均工资（按照岗位分组求平均值）
	select job,avg(sal) from emp group by job;
	+-----------+-------------+
	| job       | avgsal      |
	+-----------+-------------+
	| ANALYST   | 3000.000000 |
	| CLERK     | 1037.500000 |
	| MANAGER   | 2758.333333 |
	| PRESIDENT | 5000.000000 |
	| SALESMAN  | 1400.000000 |
	+-----------+-------------+t表

第二步：克服心理障碍，把以上的查询结果就当做一张真实存在的表t
	mysql> select * from salgrade; s表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
t表和s表进行表连接，条件：t表avg(sal) between s.losal and s.hisal;
	
	select 
		t.*, s.grade
	from
		(select job,avg(sal) as avgsal from emp group by job) t
	join
		salgrade s
	on
		t.avgsal between s.losal and s.hisal;
	
	+-----------+-------------+-------+
	| job       | avgsal      | grade |
	+-----------+-------------+-------+
	| CLERK     | 1037.500000 |     1 |
	| SALESMAN  | 1400.000000 |     2 |
	| ANALYST   | 3000.000000 |     4 |
	| MANAGER   | 2758.333333 |     4 |
	| PRESIDENT | 5000.000000 |     5 |
	+-----------+-------------+-------+

3.5、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');

第三种
	select ename,job from emp where job = 'MANAGER'
	union
	select ename,job from emp where job = 'SALESMAN';

union的效率要高一些。对于表连接来说，每连接一次新表，
则匹配的次数满足笛卡尔积，成倍的翻。。。
但是union可以减少匹配的次数。在减少匹配次数的情况下，
还可以完成两个结果集的拼接。

a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是：1000

a 连接 b一个结果：10 * 10 --> 100次
a 连接 c一个结果：10 * 10 --> 100次
使用union的话是：100次 + 100次 = 200次。（union把乘法变成了加法运算）

union在使用的时候有注意事项吗？

//错误的：union在进行结果集合并的时候，要求两个结果集的列数相同。

select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job = 'SALESMAN';

5、limit（非常重要，分页查询靠它mysql特有）

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

5.2、limit怎么用呢？(startIndex是起始下标，length是长度)

完整用法：limit startIndex, length
	startIndex是起始下标，length是长度。
	起始下标从0开始。

**缺省用法：limit 5; 这是取前5.**

案例：按照薪资降序，取出排名在前5名的员工？
select 
	ename,sal
from
	emp
order by 
	sal desc
limit 5; //取前5


select 
	ename,sal
from
	emp
order by 
	sal desc
limit 0,5;

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

5.3、注意：mysql当中limit在order by之后执行（limit是sql语句最后执行的一个环节）！！！！！！

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

5.4、取出工资排名在[3-5]名的员工？

select 
	ename,sal
from
	emp
order by
	sal desc
limit
	2, 3;

2表示起始位置从下标2开始，就是第三条记录。
3表示长度。

+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

5.5、分页

每页显示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

public static void main(String[] args){
	// 用户提交过来一个页码，以及每页显示的记录条数
	int pageNo = 5; //第5页
	int pageSize = 10; //每页显示10条

	int startIndex = (pageNo - 1) * pageSize;
	String sql = "select ...limit " + startIndex + ", " + pageSize;
}

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