条件查询

distinct:去除重复行

  1. -- 查询部门地址
  2. SELECT loc FROM dept
  3. -- 查询去重后的部门地址
  4. SELECT DISTINCT loc FROM dept

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

  1. - 查询deptno等于1的部门记录
  2. SELECT * FROM dept WHERE deptno = 1
  3. -- 查询地址在一区的部门记录
  4. SELECT * FROM dept WHERE loc = '一区'
  5. -- 查询地址在二区的部门名称
  6. SELECT dname FROM dept WHERE loc = '二区'
  7. -- 根据名称查询部门记录
  8. SELECT * FROM dept WHERE dname = 'research'
  9. -- 根据名称地址查询部门记录
  10. SELECT * FROM dept WHERE dname = 'research' AND loc = '二区'
  11. SELECT * FROM dept WHERE dname = 'research' OR loc = '二区'
  12. -- 查询工资大于8000的员工信息
  13. SELECT * FROM emp WHERE sal > 8000
  14. -- 查询工资是8000或者3000的员工信息
  15. SELECT * FROM emp WHERE sal = 8000 or sal = 3000
  16. SELECT * FROM emp WHERE sal IN(8000,3000)

**LIKE**:模糊查询

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

**null**

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

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

  1. -- 查询sal3000-10000内的员工信息
  2. SELECT * FROM emp WHERE sal >= 3000 AND sal <= 10000
  3. SELECT * FROM emp WHERE sal BETWEEN 3000 AND 10000
  4. -- 查询2015年到2019年入职的员工信息
  5. SELECT * FROM emp WHERE YEAR(hiredate) >= 2015 AND YEAR(hiredate) =< 2019
  6. SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN 2015 AND 2019

LIMIT:分页

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

order by:排序

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

统计案例

员工统计

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

年薪统计

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

聚合函数

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

Max:求最大值

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

Min:求最小值

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

SUM:求和

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

avg:求平均值

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

count:统计行数,即记录数

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

分组查询

GROUP BY:分组

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

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

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

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

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