Javaweb08——MySQL（3）

MySQL note2

#查询各个部门的名称及其部门下职员的名称
SELECT dept.DNAME,emp.ENAME FROM dept JOIN emp ON dept.DEPTNO=emp.DEPTNO
#查询部门20的名称及其部门下职位名称与人数
SELECT dept.DNAME,emp.JOB,COUNT(*) FROM dept JOIN emp ON dept.DEPTNO=emp.DEPTNO WHERE dept.DEPTNO=20 GROUP BY emp.JOB
#查询各个部门名称及部门下最高工资 并按部门编号升序排列
SELECT dept.DEPTNO,dept.DNAME,MAX(SAL) FROM dept JOIN emp ON dept.DEPTNO=emp.DEPTNO GROUP BY dept.DEPTNO,dept.DNAME ORDER BY dept.DEPTNO ASC
#查询各个部门名称以及clerk人员的人数
SELECT dept.DNAME,COUNT(*) FROM dept JOIN emp ON dept.DEPTNO=emp.DEPTNO WHERE emp.JOB='clerk' GROUP BY dept.DNAME
#查询各个部门及其部门下的人数 仅显示5人以上的部门 根据部门人数进行降序
SELECT dept.DNAME,COUNT(*) FROM dept JOIN emp ON dept.DEPTNO=emp.DEPTNO GROUP BY dept.DNAME HAVING COUNT(*)>=5 ORDER BY COUNT(*) DESC #此处不是调用聚合函数 是select生成临时表字段名  ORDER BY使用排序的字段只能来自select生成的临时表
#查询所有员工的姓名 工资 工资等级
SELECT emp.ENAME,emp.SAL,salgrade.grade
FROM salgrade JOIN emp
ON emp.SAL BETWEEN salgrade.losal AND salgrade.hisal
#查询部门20的名称及其部门下职位名称与人数
SELECT dept.DNAME,emp.JOB,COUNT(*)
FROM dept JOIN emp
ON dept.DEPTNO=emp.DEPTNO
WHERE dept.DEPTNO=20
GROUP BY emp.JOB
#查询各个部门名称及其部门下最高工资
SELECT dept.DEPTNO,dept.DNAME,MAX(SAL)
FROM dept JOIN emp
ON dept.DEPTNO=emp.DEPTNO
GROUP BY dept.DEPTNO,dept.DNAME  #一定要注意这里 除了count其他select都需要先group by
ORDER BY 1 ASC
#查询各个部门名称下clerk的人数
SELECT dept.DNAME,COUNT(*)
FROM dept JOIN emp
ON dept.DEPTNO=emp.DEPTNO
WHERE emp.JOB='clerk'
GROUP BY dept.DNAME
ORDER BY 2 DESC
#查询各个部门的名称及其部门下的人数【无论部门是否有人都要展示出来（部门40没有人）】 使用外链接！
SELECT dept.DEPTNO,COUNT(emp.EMPNO)
FROM dept LEFT JOIN emp
ON dept.DEPTNO=emp.DEPTNO
GROUP BY dept.DEPTNO
ORDER BY 1 ASC
#将部门名称 部门名称 职员编号 职员名称展示出来
SELECT DEPTNO,DNAME FROM dept
UNION
SELECT EMPNO,ENAME FROM emp
#工资高于平均工资的职员信息以及公司的平均工资      子查询
SELECT *,(SELECT avg(SAL) FROM emp)
FROM emp
WHERE sal>(SELECT avg(SAL) FROM emp)
#子查询生成临时表交给FROM定位
SELECT t1.DEPTNO,t1.DNAME
FROM(
	SELECT dept.DEPTNO,dept.DNAME,emp.ENAME,emp.JOB,emp.SAL
	FROM dept LEFT JOIN emp
	ON dept.DEPTNO=emp.DEPTNO
	)AS t1
#查询部门平均工资高于公司平均工资的部门名称
SELECT DEPTNO,AVG(SAL)
FROM emp
GROUP BY DEPTNO
HAVING AVG(SAL)>(SELECT AVG(SAL) FROM emp)

相关笔记：

/*
一 表文件关系
	MySQL被称为关系型数据库 通过表文件数据关系描述现实生活中的【隶属关系】
	隶属关系角色划分：
		1一方：拥有资源的一方（部门）    一方表dept
		2多方：依附于一方的资源（职员）		多方表emp
二 表文件字段分类
	1主键字段 数据行的身份证 用于确认数据行身份 不能null不能重复
	2非主键字段 描述主键编号
	3外键字段 只存在于多方表 描述多方数据与一方数据之间依赖关系 来自一方数据主键值
三 多表查询
	本质：将多张表中的数据行进行合并到同一个临时表中 然后使用查询命令进行定位分组排序等操作
	关键点：如何将多张表中数据进行合并
	方案：
		1连接查询合并方案
			①确保两张表间存在隶属关系 才可以使用连接查询合并方案
			②将两张表数据行沿水平方向进行拼接
		2联合查询合并方案
			①不需要两张表间存在隶属关系
			②将两张表中数据行沿垂直方向进行堆砌
四 连接查询合并方案 JOIN
	1数据来源  dept.dname  emp.ename
	2确认表之间的关系  隶属关系
	3确认表之间的角色划分  dept是一方表 emp是多方表
	JOIN两边的表先后顺序无影响
	拼接即做笛卡尔积
	使用ON 连接过滤查询方案 定位合法数据行 滤掉脏数据
五 连接查询过滤方案
	前提：JOIN生成的临时表必有脏数据
	分类：内连接过滤方案 外连接过滤方案
六 外链接过滤方案
	角色：
		1需要被帮助的表 2不需要被帮助的表
	原理：
		需要被帮助的表中某行数据在另一张表中匹配不成功 仍然将其拿出来作为独立数据行存入新临时表
	格式
		FROM 需要被帮助表（有多余行的表）LEFT JOIN 不需要被帮助的表（无多余行的表） ON 条件
		右外连接同理
	注意一下：
		COUNT(*)统计的是数据行行数 不管是否有null值都会被统计在内
		COUNT(字段)统计不为null的数据行数
七 联合查询合并方案
	1要求参与合并的两个临时表字段结构一致 即列个数相同，类型顺序相同
	2将两个临时表中数据行垂直方向堆砌到一个临时表中
	3生成临时表字段是第一个表的字段
	4UNION执行时自动的将两个临时表中相同数据行进行合并 类似于Java的Set集合
八 子查询
	查询时无法从当前临时表中得到需要的数据 MySQL允许嵌套
	在哪些场景使用？
		SELECT FROM WHERE HAVING
		GROUP BY 和 ORDER BY不能用子查询！
		
*/