当遇到一些复杂的需求时,例如对查询结果进行排序,分组和分页等,MySQL 数据库有着更高级的查询方法以处理更加复杂的需求。
一,排序查询
在查询完成后,结果集中的数据是按默认顺序排序的。为了方便用户自定义结果集中数据的顺序,MySQL 提供了 ORDER BY 对于查询结果进行排序,具体语法如下:
SELECT 字段名1, 字段名2, ....FROM 表名 ORDER BY 字段名1 (ASC/DESC), 字段名2(ASC/DESC)...;
在以上语法中,字段名1,字段名2等表示需要查询的字段名称,ORDER BY 关键字后的字段名表示指定排序的字段,ASC 和 DESC 参数是可选的,其中 ASC 代表按升序排序,DESC 代表按降序排序,如果不写该参数,则默认按升序排序。
查询所有学生记录,按年龄升序排序。
SELECT * FROM stu ORDER BY age ASC;
注意:如果不写 ASC 也是升序排序,使用默认排序方式。
接着查询所有学生记录,按 sid 降序排序。
SELECT * FROM stu ORDER BY sid DESC;
查询所有员工信息,按员工月薪降序排序,如果月薪相同,按员工编号升序排序。
SELECT * FROM emp ORDER BY sal DESC,empno ASC;
二,聚合函数
在查询出数据之后,可能需要对数据进行统计,例如获得工资的总和,年龄最大值,奖金最小值等,MySQL 提供了一系列函数实现数据统计,也称为聚合函数。
常用聚合函数:
函数名称 | 作用 |
COUNT() | 返回某列的行数 |
SUM() | 返回某列的和 |
AVG() | 返回某列的平均值 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
1,COUNT() 函数
COUNT() 函数的语法格式如下:
SELECT COUNT(*|1|列名) FROM 表名;
在上述语法格式中,COUNT()
函数有3个可选参数,其中 COUNT(*)
是返回行数,包含 NULL ;COUNT(列名)
是返回特定列的值具有的行数,不包含 NULL ;还有一种是 COUNT(1)
,它与 COUNT(*)
返回的结果是一样的,如果数据表没有主键,则 COUNT(1)
的执行效率会高一些。
查询员工表中的记录数。
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;
从执行结果上看,COUNT(*) 与 COUNT(1) 的查询结果一致,他们只是在某些情况下执行效率不同。
另外,查询出的结果集列名显示为 COUNT(1) ,不是很直观,这时可以为列名起别名,只需要在 COUNT(1) 还没加上“ AS 别名 ”即可。
SELECT COUNT(1) AS totle FROM emp;
注意:在取别名时 AS 是可以省略不写的,效果一样。
接着查询员工表中有奖金的人数。
SELECT COUNT(comm) AS totle FROM emp;
员工表中,comm 字段为奖金,除去值为 NULL 的其他记录共4条。
查询员工表中月薪大于 2500 元的人数,查询结果的列名指定为 total。
SELECT COUNT(*) AS total FROM emp WHERE sal>2500;
查询员工表中有奖金的人数和领导的人数。
SELECT COUNT(comm),COUNT(mgr) FROM emp;
查询员工表中月薪与奖金之和大于 2500 元的人数。
SELECT COUNT(*) AS total FROM emp WHERE sal+IFNULL(comm,0)>2500;
注意:因为有些员工的奖金为 NULL 当数字类型与 NULL 相加时结果为0。所以 IFNULL() 函数可以解决这个问题,该函数可以判断字段是否为 NULL 如果是,则将 NULL 替换为0。
2,SUM() 函数
SUM() 函数用于计算指定列的数值和,如果指定列的类型不是数值类型,那么计算结果为0,具体语法如下:
SELECT SUM(字段名) FROM 表名;
查询员工表中所有员工的月薪和。
SELECT SUM(sal) FROM emp;
查询员工表中所有员工的月薪和以及所有员工的奖金和。
SELECT SUM(sal),SUM(comm) FROM emp;
查询员工表所有员工的月薪和奖金的和,查询出的列名指定为 totle。
SELECT SUM(sal+IFNULL(comm,0)) AS totle FROM emp;
注意:奖金中有 NULL 值的存在,因此需要用到 IFNULL() 函数进行判断。
3,AVG() 函数
AVG() 函数用于计算指定列的平均值,如果指定列的类型不是数值类型,那么计算的结果为0,具体语法格式如下:
SELECT AVG(字段名) FROM 表名;
查询员工表中所有员工的平均月薪。
SELECT AVG(sal) FROM emp;
4,MAX() 函数
MAX() 函数用于计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算,具体语法格式如下:
SELECT MAX(字段名) FROM 表名;
查询员工表中员工的最高月薪。
SELECT MAX(sal) FROM emp;
5,MIN() 函数
MIN() 函数用于计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算,具体语法格式如下:
SELECT MIN(字段名) FROM 表名;
查询员工表的最低月薪。
SELECT MIN(sal) FROM emp;
三,分组查询
在查询数据时,有时需要按照一定的类别进行统计,例如查询每个部门的人数,查询每个部门的薪资总和等,在 MySQL 中可以使用 GROUP BY 关键字进行分组查询,语法格式如下:
SELECT 字段名1, 字段名2, ....FROM 表名 GROUP BY 字段名1, 字段名2, ....;
在以上语法格式中,GROUP BY 后面的字段名是对查询结果分组的依据。
查询学生表中的学生信息,按照性别字段分组。
SELECT * FROM stu GROUP BY gender;
按照 gender 字段分组后的记录是3条,查询结果是按照 gender 字段不同的值进行分组,并没有太多实际意义,GROUP BY 通常与聚合函数一起使用。
查询员工表中每个部门的部门编号和每个部门的工资和。
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
查询员工表中每个部门的部门编号以及每个部门的人数。
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
查询员工表中每个部门的部门编号以及每个部门工资大于1500元的人数。
SELECT deptno,COUNT(*) FROM emp
-> WHERE sal>1500
-> GROUP BY deptno;
四,HAVING 子句
对于一些更复杂的分组查询,在查询完成后还需要进行数据过滤。MySQL 提供了 HAVING 子句,用于分组后对数据进行过滤,在它后面可以使用聚合函数,而 WHERE 子句是在分组前对数据进行过滤,在它后面不可以使用聚合函数。HAVING 子句语法格式如下:
SELECT 字段名1, 字段名2, ....FROM 表名 GROUP BY 字段名1, 字段名2, ....[HAVING 条件表达式];
HAVING 子句是可选的。
查询员工表中工资总和大于9000元的部门编号以及工资和。
SELECT deptno,SUM(sal) FROM emp
-> GROUP BY deptno
-> HAVING SUM(sal)>9000;
五,LIMIT 分页
在查询数据时一般会返回几条,几十条甚至更多的数据,但用户可能只需要其中某几条,而且这种查询方式明显会影响程序的性能,为了解决这一问题,MySQL 提供了 LIMIT 关键字用于限制查询结果的数量,也可以通俗的理解为分页,既满足了用户需求,又不影响系统性能。LIMIT 的语法格式如下:
SELECT 字段名1, 字段名2, .....FROM 表名 LIMIT m,n;
LIMIT 后面可以跟两个参数,第一个 m 是可选的,代表起始索引,若不指定,则默认为0,代表第一条记录;第二个参数 n 是必选的,代表从第 m+1 条记录开始取 n 条记录。
查询学生表中前五条记录。
SELECT * FROM stu LIMIT 0,5;
当从0开始查询时,0也可以省略不写。
查询学生表中从第3条开始的记录,总共查询5条记录。
SELECT * FROM stu LIMIT 2,5;
LIMIT 后面的第一个参数指定为2,代表从第3条记录开始查询,第二个参数指定的5,代表查询5条记录。