六、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