MySQL数据库学习之子查询

第四章

第一节 嵌套在where语句后

		子查询是select嵌套语句
		案例：找出薪水比公司平均薪水高的员工，要求显示员工名和薪水
		第一步 找出公司平均薪水：
		mysql> select avg(sal)from emp;
		+-------------+
		| avg(sal)    |
		+-------------+
		| 3741.666667 |
		+-------------+
		第二步 找出薪水比公司平均薪水高的员工：
		mysql> select ename,sal from emp where sal>(select avg(sal)from emp);
		+--------+---------+
		| ename  | sal     |
		+--------+---------+
		| Jack   | 4000.00 |
		| White  | 4500.00 |
		| cathy  | 4000.00 |
		| jenny  | 5500.00 |
		| Andrew | 6000.00 |
		+--------+---------+

第二节 嵌套在from语句后(相当于创建一张临时表)

		案例：找出每个部门的平均薪水，要要求显示平均薪水的薪水等级
		第一步：找出每个部门的平均薪水：
		mysql> select dname,avg(sal) from emp e join dept d on e.deptno = d.deptno group by d.deptno;
		+----------------+-------------+
		| dname          | avg(sal)    |
		+----------------+-------------+
		| sales          | 4166.666667 |
		| administration | 3600.000000 |
		| planning       | 5000.000000 |
		| project        | 3500.000000 |
		| Secretarys     | 2800.000000 |
		| technical      | 2700.000000 |
		| sales_2        | 3100.000000 |
		+----------------+-------------+
		第二步：显示每个部门平均薪水的薪水等级
		select 
			t.*,s.sgrade 
		from 
			(select dname,avg(sal) avgsal from emp e join dept d on e.deptno = d.deptno group by d.deptno) t
		join 
			salgrade s
		on 
			t.avgsal between s.lowsal and s.highsal
		+----------------+-------------+--------+
		| dname          | avgsal      | sgrade |
		+----------------+-------------+--------+
		| sales          | 4166.666667 |      4 |
		| administration | 3600.000000 |      3 |
		| planning       | 5000.000000 |      5 |
		| project        | 3500.000000 |      2 |
		| Secretarys     | 2800.000000 |      1 |
		| technical      | 2700.000000 |      1 |
		| sales_2        | 3100.000000 |      2 |
		+----------------+-------------+--------+

第五章

第一节 union关键字(字段个数相同)

	mysql> select ename,job from emp where job='salesman';
	+-------+----------+
	| ename | job      |
	+-------+----------+
	| Jack  | salesman |
	| jenny | salesman |
	| ford  | salesman |
	| Andy  | salesman |
	+-------+----------+
	mysql> select ename,job from emp where job='manager';
	+--------+---------+
	| ename  | job     |
	+--------+---------+
	| White  | Manager |
	| Andrew | Manager |
	+--------+---------+

	mysql> select ename,job from emp where job='salesman'
    -> union
    -> select ename,job from emp where job='manager';
	+--------+----------+
	| ename  | job      |
	+--------+----------+
	| Jack   | salesman |
	| jenny  | salesman |
	| ford   | salesman |
	| Andy   | salesman |
	| White  | Manager  |
	| Andrew | Manager  |
	+--------+----------+

第二节 limit关键字(limit 起始下标 长度)

	mysql> select empno,ename from emp limit 2,5;
	+-------+--------+
	| empno | ename  |
	+-------+--------+
	|     3 | cathy  |
	|     4 | merry  |
	|     5 | lucy   |
	|     6 | Hellen |
	|     8 | jenny  |
	+-------+--------+