条件查询
条件查询就是在查询时给出WHERE子句
- 在WHERE子句中可以使用如下运算符及关键字:
- =、!=、<>、<、<=、>、>=
- BETWEEN…AND; IN(set); IS NULL
AND; OR; NOT;
(1)查询学号为S_1001,S_1002,S_1003的记录
SELECT * FROM STUDENT WHERE SID IN ('S_1001','S_1002','S_1003');
-- 或者
SELECT * FROM STUDENT WHERE SID = 'S1001' AND SID = 'S_1002' AND SID = 'S_1003';
(2)查询学号不是S_1001,S_1002,S_1003的记录
SELECT * FROM STUDENT WHERE SID NOT IN ('S_1001','S_1002','S_1003');
(3)查询年龄为null的记录
SELECT * FROM STUDENT WHERE AGE IS NULL;
(4) 查询年龄在20到40之间的学生记录 [包含临界值的]
SELECT * FROM STUDENT WHERE AGE BETWEEN 20 AND 40;
模糊查询
当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。
语法: 列名 like '表达式’
表达式必须是字符串
通配符:
(下划线): 任意一个字符
%: 任意0~n个字符,‘张%’
(4)查询姓名中第2个字母为“i”的学生记录
SELECT * FROM STUDENT WHERE SNAME LIKE '_i%';
(5)查询姓名中包含“a”字母的学生记录
SELECT * FROM STUDENT WHERE SNAME LIKE '%a%';
字段控制
(1)去除重复记录
去除重复记录(两行或两行以上记录中系列的上的数据都相同)。
假如:emp表中sal字段就存在相同的多个记录。当只查询emp表的sal字段时,那么会出现重复记录,那么想去除重复记录,需要使用 DISTINCT:
SELECT DISTINCT SAL FROM EMP;
(2)查看雇员的月薪(sal)与佣金(comm)之和 因为sal和comm两列的类型都是数值类型,所以可以做加运算。如果sal或comm中有一个字段不是数值类型,那么会出错。
比如comm列有很多记录的值为NULL,因为任何东西与NULL相加结果还是NULL,所以结算结果可能会出 现NULL。下面使用了把NULL转换成数值0的函数IFNULL:
SELECT SAL + IFNULL(COMM, 0) FROM EMP;
(3)给列名添加别名:
在上面查询中出现列名是:sal+IFNULL(comm,0),这很不美观,现在我们给这一列给出一个别名,为total:
SELECT SAL + IFNULL(COMM, 0) AS TOTAL FROM EMP;
排序
语法: order by 列名 ASC/DESC(默认:ASC)
(1) 查询所有学生记录,按年龄升序排序
SELECT *
FROM stu
ORDER BY age ASC;
(2) 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序
多列排序:当前面的列的值相同的时候,才会按照后面的列值进行排序
SELECT * FROM emp
ORDER BY sal DESC,empno ASC;
上面这个例子先按照sal降序排序,当有多个数据的sal相同时,按照empno升序排序。
聚合函数
之前的sal + comm
是横向的两列相加,而聚合函数则是纵向的多个数据相加 或者 取平均等等
- COUNT(列名):统计指定列不为NULL的记录行数;如果为Null不计入数量
- MAX(列名):计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算; MIN(列名):计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
- SUM(列名):计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
- AVG(列名):计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0
(1)查询有佣金(没有佣金 comm = NULL)的人数,以及有领导(没有领导的话mgr = NULL)的人数:
SELECT COUNT(COMM),COUNT(MGR) FROM EMP;
(2)SUM和AVG,当需要纵向求和时使用sum()函数。
- 查询所有雇员月薪和:
SELECT COUNT(COMM),COUNT(MGR) FROM EMP;
- 查询所有雇员月薪和,以及所有雇员佣金和:
SELECT SUM(sal),SUM(comm) FROM EMP;
- 查询所有雇员月薪+佣金和:
SELECT SUM(IFNULL(comm,0) + sal) FROM EMP;
- 统计所有员工平均工资:
SELECT AVG(sal) FROM EMP;
(3) MAX和MIN
- 查询最高工资和最低工资:
SELECT MAX(SAL), MIN(SAL) FROM EMP;
分组查询
当需要分组查询时需要使用GROUP BY子句,例如查询每个部门的工资和:这说明要使用部分来分组。
注意:如果查询语句中有分组操作,则select后面能添加的只能是聚合函数和被分组的列名 (因为如果选择其他的列名 就从逻辑上不合理,比如看每个部门的工资和,如果还添加上员工工号empno就不合理了,因为员工是隶属于每个部门的)
- 查询每个部门的部门编号和每个部门的工资和:
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;
- 查询每个部门的部门编号以及每个部门的人数:
SELECT deptno,COUNT(*) AS TOTAL FROM emp GROUP BY deptno;
- 查询每个部门的部门编号以及每个部门员工工资大于1500的人数:
SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;
HAVING子句
- 查询工资总和大于9000的部门编号以及相应的部门工资和:
应该先计算 工资总和 按照各部门
SELECT deptno, SUM(SAL) FROM EMP GROUP BY deptno;
但是还不够,要筛选出工资总和大于9000的部门
SELECT deptno, SUM(SAL) FROM EMP GROUP BY deptno HAVING SUM(SAL) > 9000;
注:HAVING与WHERE的区别:
做好分析:哪些是分组前,哪些是分组后
- HAVING是在分组后对数据进行过滤,WHERE是在分组前对数据进行过滤
- HAVING后面可以使用分组函数(统计函数),HAVING不能单独出现
- WHERE后面不可以使用分组函数。
- WHERE是对分组前记录的条件(也就是每条记录进行筛选),如果某行记录没有满足WHERE子句的条件,那么这行记录不会参加分组;
- 而HAVING是对分组后数据的约束。
补充: 多列分组
- 统计出stu表中每个班级的男女生各多少人
SELECT gradename,gender,COUNT(*) FROM stu GROUP BY gradename,gender
- 先根据班级划分:GROUP BY gradename
- 分完组之后,男女生对应于不同班级,然后再根据性别划分:GROUP BY gender
LIMIT
限定展示信息:应用百度搜索的页码池
LIMIT 0,10: 从0下标开始,一共查询10行
第一个参数是下标值,第二个参数是查询数量
⚠️注意,起始行 从0开始,即第一行开始!
2.查询10行记录,起始行从3开始
SELECT * FROM EMP LIMIT 3, 10;
分页查询
如果一页记录为10条,希望查看第3页记录应该怎么查呢?
- 第一页记录起始行为0,一共查询10行: limit 0,10
- 第二页记录起始行为10,一共查询10行: limit 10,10
LIMIT (pageIndex - 1) * pageSize, pageSize
## pageSize :每页记录数量
## pageIndex: 页码值
总结:
SELECT 要查询的列名称
FROM 表名称
WHERE 限定条件 /*分组前的行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果排序*/
LIMIT offset_start, row_count /*结果限定*/