条件查询

条件查询

distinct:去除重复行

-- 查询部门地址
SELECT loc FROM dept
-- 查询去重后的部门地址
SELECT DISTINCT loc FROM dept

**where**查询满足条件的信息

- 查询deptno等于1的部门记录
SELECT * FROM dept WHERE deptno = 1
-- 查询地址在一区的部门记录
SELECT * FROM dept WHERE loc = '一区'
-- 查询地址在二区的部门名称
SELECT dname FROM dept WHERE loc = '二区'
-- 根据名称查询部门记录
SELECT * FROM dept WHERE dname = 'research'
-- 根据名称地址查询部门记录
SELECT * FROM dept WHERE dname = 'research' AND loc  = '二区'
SELECT * FROM dept WHERE dname = 'research' OR loc  = '二区'
-- 查询工资大于8000的员工信息
SELECT * FROM emp WHERE sal > 8000
-- 查询工资是8000或者3000的员工信息
SELECT * FROM emp WHERE sal = 8000 or sal = 3000
SELECT * FROM emp WHERE sal IN(8000,3000)

**LIKE**：模糊查询

-- 查询名字里包含a的员工信息
SELECT * FROM emp WHERE ename LIKE 'a%' OR ename LIKE '%a%' OR ename LIKE '%a'
select * from emp where ename like 'l%' --以l开头的
select * from emp where ename like '%a' --以a结束的
select * from emp where ename like '%a%' --中间包含a的
select * from emp where ename like 'l\_\_' --l后面有两个字符的 _代表一个字符位置

**null**

select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的

BETWEEN AND：BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。

-- 查询sal在3000-10000内的员工信息
SELECT * FROM emp WHERE sal >= 3000 AND sal <= 10000
SELECT * FROM emp WHERE sal BETWEEN 3000 AND 10000
-- 查询2015年到2019年入职的员工信息
SELECT * FROM emp WHERE YEAR(hiredate) >= 2015 AND YEAR(hiredate) =< 2019
SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN 2015 AND 2019

LIMIT：分页

SELECT * FROM emp LIMIT 3 #只查前三条
SELECT * FROM emp LIMIT 1,3 #从第二条开始查循3条
SELECT * FROM emp LIMIT 0,3 #从第1条开始查循3条

order by：排序

SELECT * FROM emp ORDER BY sal DESC #降序
SELECT * FROM emp ORDER BY sal ASC #升序
SELECT * FROM emp ORDER BY sal #默认为ASC，升序
SELECT * FROM emp ORDER BY hiredate
SELECT * FROM emp ORDER BY ename #按字典排序a-z升序
SELECT * FROM emp ORDER BY job #按utf-8码表中汉字对应的的数字进行排序

统计案例

员工统计

-- 统计2019年入职的员工
SELECT * FROM emp WHERE YEAR(hiredate) = 2019
SELECT * FROM emp WHERE hiredate > '2019-1-1' AND hiredate < '2019-12-31'
-- 统计2017年以前入职的员工
SELECT * FROM emp WHERE YEAR(hiredate) < 2017
-- 统计2015-2017年入职的员工
SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN 2015 AND 2017
-- 统计员工入职了几年
SELECT YEAR(NOW())-YEAR(hiredate) AS year FROM emp ORDER BY year

年薪统计

-- 统计员工的年薪
SELECT sal,comm,(sal+comm)*12 FROM emp #有空字段，进行加法运算会变空需要先判断
SELECT sal,comm,(sal+IFNULL(comm,0))*12 AS 年薪 FROM emp #AS起别名,可省略

聚合函数

普通查询不可以和聚合查询一起使用

Max:求最大值

-- 求最高薪
SELECT sal FROM emp ORDER BY sal DESC LIMIT 1;#按工资降序排列第一个
SELECT *,MAX(sal) FROM emp

Min：求最小值

-- 求最低薪
SELECT sal FROM emp ORDER BY sal LIMIT 1;#按工资升序排列第一个
SELECT MIN(sal) FROM emp
-- 求附加工资最低值
SELECT *,IFNULL(comm,0) FROM emp ORDER BY comm LIMIT 1;
SELECT *,MIN(IFNULL(comm,0)) FROM emp

SUM:求和

-- 求工资总和
SELECT sum(sal) FROM emp

avg：求平均值

-- 求平均工资
SELECT AVG(sal) FROM emp

count：统计行数，即记录数

-- 求总个数
SELECT count(sal) FROM emp #不统计null--低效
SELECT count(comm) FROM emp #不统计null--低效按照字段名统计个数,如果字段值是null将不做统计
SELECT count(*) FROM emp #都统计--低效
SELECT count(1) FROM emp #都统计--高效
-- 统计工资>8000的人数
SELECT count(1) FROM emp WHERE sal > 8000
-- 统计2019年入职的员工
SELECT COUNT(1) FROM emp WHERE YEAR(hiredate) = 2019

分组查询

GROUP BY：分组

group by可以进行分组查询，GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。查询结果中同时存在聚合列与非聚合列必须分组，通常使用非聚合列分组。

-- 统计每个部门的平均薪资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno #按照部门编号分组
-- 统计每个岗位的最高薪资
SELECT job,MAX(sal) FROM emp GROUP BY job #按照岗位编号分组
-- 统计每年的入职人数
SELECT YEAR(hiredate) AS 年份,COUNT(1) AS 人数 FROM emp GROUP BY YEAR(hiredate)

HAVING：将分组后的结果进行过滤

WHERE 关键字无法与合计函数一起使用，HAVING效果与WHERE相同。

-- 统计每个部门平均薪资大于10000的部门
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 10000
-- 统计每个岗位最高薪资且薪资大于8000的人
SELECT job,MAX(sal) FROM emp GROUP BY job HAVING MAX(sal) > 8000 ORDER BY MAX(sal)
# SELECT job,MAX(sal) FROM emp WHERE MAX(sal)  > 8000 #过滤后再查询更高效，但不能使用聚合函数
-- 统计每年的入职人数，只要人数>1的记录
SELECT YEAR(hiredate) 年份,COUNT(1) 人数 FROM emp GROUP BY YEAR(hiredate) HAVING COUNT(1) > 1
-- 统计2017年以后的入职人数
SELECT YEAR(hiredate),COUNT(1) FROM emp
WHERE YEAR(hiredate) > 2017 #高效
GROUP BY YEAR(hiredate) 
-- HAVING YEAR(hiredate) > 2017 #使用的过滤条件必须为查到确定的结果