6、表加强查询

六、mysql表加强查询

在前面我们讲过mysq|表的基本查询，但是都是对一张表进行的查询，这在实际的软件开发中，还远远的不够。下面我们讲解的过程中，将使用前面创建三张表(emp,dept,salgrade)为大家演示如何进行多表查询。

模糊与排序查询

-- 查询加强
-- ■ 使用where子句
-- 表说明：表中含有姓名、日期等信息
-- 	?如何查找1992.1.1后入职的员工
-- 在mysql中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp
	WHERE hiredate > '1992-01-01'
	
-- ■ 如何使用like操作符(模糊)
-- 	%: 表示以首字母S开头的ename
SELECT ename, sal FROM emp
	WHERE ename LIKE 'S%'
-- 	表示以首字母S结尾的ename
SELECT ename, sal FROM emp
	WHERE ename LIKE '%S'
-- 	表示第三个字母为O的ename，前面添加两个下划线
SELECT ename, sal FROM emp
	WHERE ename LIKE '__O%'

-- ■ 如何显示没有上级的雇员的情况，mysql中判断相等用=，但是判断是否为NULL，只能用IS NULL；
SELECT * FROM emp
	WHERE mgr IS NULL;
-- ■ 查询表结构 
DESC emp 

-- 使用order by子句 ASC升序(默认排序) DESC降序
--   按照sal的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp
	ORDER BY sal 
--   按照deptno升序而sal的工资降序排列 , 显示雇员信息，即先满足deptno排序，若有相同deptno再按照sal排序
SELECT * FROM emp
	ORDER BY deptno ASC , sal DESC;

正则查询

正则这里就不多讲了，学到这里，前面都遇到过的

-- 以字母a开头
SELECT * FROM employees WHERE first_name REGEXP '^a';
-- $结尾
SELECT * FROM employees WHERE first_name REGEXP 'a$';
-- b中间是任意字符c b与c之间得有一个任意字符，
SELECT * FROM employees WHERE first_name REGEXP 'b.c';
-- +b必须得出现到n次
SELECT * FROM employees WHERE first_name REGEXP 'b+c';
-- * 必须得0到n次
SELECT * FROM employees WHERE first_name REGEXP 'b*c';
SELECT * FROM employees WHERE first_name REGEXP 'b{2,}c';
-- {2,5} 2到5次
SELECT * FROM employees WHERE first_name REGEXP 'b{2,4}c';

伪列

假的列，如果想对伪列左限制，请使用子查询

row_number() 按顺序排 1 2 3 4

rank() 若出现相同的 则按照1 1 3 3 5 这类排

dense_rank() 若出现相同的 则按照1 1 2 2 3

SELECT * FROM emp; -- 还是按照上面用过的表进行演示

select e.* ,DENSE_RANK() over (order by sal desc) rownum from emp e ;  -- 会按照（order by sal desc）排序好，然后在信息最后加上一列排名列

select * from (select e.* ,DENSE_RANK() over (order by sal desc) rownum from emp e ) a where a.rownum<=3;  -- 找到前三名

分页查询

-- 分页查询
-- 按雇员的id号升序取出， 每页显示3条记录，请分别显示 第1页，第2页，第3页
-- 第1页
SELECT * FROM emp 
	ORDER BY empno 
	LIMIT 0, 3;
-- 第2页
SELECT * FROM emp 
	ORDER BY empno 
	LIMIT 3, 3;
-- 第3页
SELECT * FROM emp 
	ORDER BY empno 
	LIMIT 6, 3;
# 其实就是从第几条开始显示，显示几条

-- 推导一个公式 
/*
SELECT * FROM emp
	ORDER BY empno 
	LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数
*/

增强group by 的使用

-- 增强group by 的使用

-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), AVG(sal), job
	FROM emp 
	GROUP BY job; 
	
-- (2) 显示雇员总数，以及获得补助的雇员数。
SELECT COUNT(*), COUNT(comm)  -- 图12
	FROM emp 

--  老师的扩展要求：统计没有获得补助的雇员数
-- IF(comm IS NULL, 1, NULL) 如果comm是null就返回1，否则就返回nul，类似三目运算符
-- 而count(列)：统计不为null的个数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL)) -- 图13
	FROM emp 

SELECT COUNT(*), COUNT(*) - COUNT(comm)
	FROM emp 

-- (3) 显示管理者的总人数。 对mgr去重，后统计
SELECT COUNT(DISTINCT mgr) -- 图14
	FROM emp; 

-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) -- 图15
	FROM emp;


-- 应用案例：请统计各个部门      group by deptno
-- 的平均工资                   avg，
-- 并且是大于1000的             having，
-- 并且按照平均工资从高到低排序， order by
-- 取出前两行记录               limit 0, 2

SELECT deptno, AVG(sal) AS avg_sal  -- 图16
	FROM emp
	GROUP BY deptno
	HAVING  avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0,2 
	-- 如果select语句同时包含有group by ,having , limit，order by那么他们的顺序是group by，having , order by,limit