wordpress移动到回收站时发生错误,房地产手机网站模板,介休做网站,wordpress 功能开发通过条件查询可以查询到符合条件的数据#xff0c;但如同要实现对字段的值进行计算、根据一个或多个字段对查询结果进行分组等操作时#xff0c;就需要使用更高级的查询#xff0c;MySQL提供了聚合函数、分组查询、排序查询、限量查询、内置函数以实现更复杂的查询需求。接下…通过条件查询可以查询到符合条件的数据但如同要实现对字段的值进行计算、根据一个或多个字段对查询结果进行分组等操作时就需要使用更高级的查询MySQL提供了聚合函数、分组查询、排序查询、限量查询、内置函数以实现更复杂的查询需求。接下来将针对这些高级查询的知识进行讲解。
1.聚合函数
在实际开发中经常需要做一些数据统计操作例如统计某个字段的最大值、最小值、平均值等。像这样对一组值执行计算并将计算后的值返回的操作称为聚合操作聚合操作一般通过聚合函数实现。使用聚合函数实现查询的基本语法格式如下。
SELECT [字段名1字段名2···字段名n] 聚合函数 FROM 数据表名
MySQL中常用的聚合函数如下 COUNT(e) 返回查询的记录总数参数e可以是字段名或* SUM(e) 返回e字段中值的总和 AVG(e) 返回e字段中值的平均值 MAX(e) 返回e字段中的最大值 MIN(e) 返回e字段中的最小值
上面的聚合函数都是MySQL中内置的函数使用者根据函数的语法格式直接调用即可。
接下来通过一些例子学习聚合函数在数据统计中的使用。
为了方面演示我把之前的员工表删了又创建了一个员工表并插入了一些数据
mysql CREATE TABLE emp(- empno INT PRIMARY KEY,- ename VARCHAR(16),- job VARCHAR(16),- sal INT,- bon INT- );
Query OK, 0 rows affected (0.01 sec)mysql DESC emp;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| empno | int | NO | PRI | NULL | |
| ename | varchar(16) | YES | | NULL | |
| job | varchar(16) | YES | | NULL | |
| sal | int | YES | | NULL | |
| bon | int | YES | | NULL | |
-----------------------------------------------
5 rows in set (0.00 sec)
表格介绍
empno员工编号
ename 员工姓名
job职位
sal工资
bon奖金
插入语句
mysql INSERT INTO emp VALUES- (9880,张三,销售,3000,200),- (9885,李四,保洁,2500,100),- (9775,王五,销售,3500,500),- (9900,孙七,销售,2500,200),- (9990,周八,经理,7000,1000)- (9770,吴九,保洁,2500,null),- (9888,郑十,销售,3500,null);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
表中的具体内容如下
mysql SELECT*FROM emp;
-----------------------------------
| empno | ename | job | sal | bon |
-----------------------------------
| 9770 | 吴九 | 保洁 | 2500 | NULL |
| 9775 | 王五 | 销售 | 3500 | 500 |
| 9880 | 张三 | 销售 | 3000 | 200 |
| 9885 | 李四 | 保洁 | 2500 | 100 |
| 9888 | 郑十 | 销售 | 3500 | NULL |
| 9900 | 孙七 | 销售 | 2500 | 200 |
| 9990 | 周八 | 经理 | 7000 | 1000 |
-----------------------------------
7 rows in set (0.00 sec)
1.COUNT()函数
COUNT()函数用于检索数据表行中的值的计数COUNT(*)可以统计数据表中记录的总条数即数据表中有多少行记录。例如想要使用SQL语句查询员工表中有多少个员工的记录。在查询时可以使用COUNT()函数进行统计具体SQL语句及执行结果如下。
mysql SELECT COUNT(*) FROM emp;
----------
| COUNT(*) |
----------
| 7 |
----------
1 row in set (0.00 sec)
由上述执行结果可以得出数据表emp中有7条记录也就是说员工表中有7个员工的记录。
COUNT()函数中的参数除可以使用*号还可以使用字段的名称。两者不同的是使用COUNT(*)统计结果时相当于统计数据表的行数不会忽略字段中值为NULL的行如果使用COUNT(字段)统计那么字段值为NULL的记录不会被统计。例如想要使用SQL语句查询员工表中奖金不为NULL的员工个数具体SQL语句及执行结果如下。
mysql SELECT COUNT(bon) FROM emp;
------------
| COUNT(bon) |
------------
| 5 |
------------
1 row in set (0.00 sec)
由上述执行结果可以得出数据表emp中奖金不为NULL的员工有5个。
2.SUN()函数
如果字段中存放的是数值型数据需要统计该字段中所有值的总数可以使用SUM()函数。SUM()函数会对指定字段中的值进行累加并且在数据累加时忽略字段中的NULL值。
例如想要使用SQL语句查询员工表中员工奖金的总和。可以在查询时使用SUM()函数进行统计具体SQL语句及执行结果如下。
mysql SELECT SUM(bon) FROM emp;
----------
| SUM(bon) |
----------
| 2000 |
----------
1 row in set (0.00 sec)
上述SELECT语句使用SUM()函数对COMM字段中的值进行求和统计执行结果中显示员工奖金总和为2000元。
3.AVG()函数
如果字段中存放的是数值型数据需要统计该字段中所有值的平均值可以使用AVG()函数。AVG()函数会计算指定字段值的平均值并且计算时会忽略字段中的NULL值。
例如想要使用SQL语句查询员工表中员工的平均奖金。查询时可以使用AVG()函数进行统计具体SQL语句及执行结果如下。
mysql SELECT AVG(bon) FROM emp;
----------
| AVG(bon) |
----------
| 400.0000 |
----------
1 row in set (0.00 sec)
上述SELECT语句使用AVG()函数计算bon字段的平均值。由执行结果可以得出bon字段的平均值为400.0000。AVG()函数在计算时会忽略bon字段中的NULL值即只对非NULL的数值进行累加然后将累加和除以非NULL的行数计算出平均值。
上面已经设置了是五个人有奖金有两个是没有奖金的奖金是空值因此那两个人也不会计算入内。
如果想要统计所有员工的平均奖金即奖金平均到所有员工身上可以借助IFNULL()函数。
IFNULL(v1,v2)
上述格式表示如果v1的值不为NULL则返回v1的值否则返回v2。例如想要使用SQL语句查询所有员工的平均奖金。查询时可以调用AVG()函数和IFNULLO函数进行统计先调用IFNULL()函数将bon字段中所有的NULL值转换为0再调用AVG()函数统计平均值具体SQL语句及执行结果如下。
mysql SELECT AVG(IFNULL(BON,0)) FROM emp;
--------------------
| AVG(IFNULL(BON,0)) |
--------------------
| 285.7143 |
--------------------
1 row in set (0.00 sec)
上述SELECT语句在执行AVG()函数之前调用IFNULL()函数对bon字段中的值进行判断如果是NULL值就转换成0返回由执行结果并结合数据表中的数据可以得出本次统计的平均奖金是所有员工的平均奖金。
4.MAX()函数
MAX(函数用于计算指定字段中的最大值如果字段的值是数值类型则比较的是值的大小。例如想要使用SQL语句查询员工表中最高的工资。查询时可以使用MAX()函数进行计算具体SQL语句及执行结果如下。
mysql SELECT MAX(sal) FROM emp;
----------
| MAX(sal) |
----------
| 7000 |
----------
1 row in set (0.00 sec)
上述SELECT语句使用MAX()函数获取了sal字段中最大的数值。
5.MIN()函数
MIN()函数用于计算指定字段中的最小值如果字段的值是数值类型则比较的是值的大小。例如想要使用SQL语句查询员工表中最低的工资。查询时可以使用MIN()函数进行计算具体SQL语句及执行结果如下。
mysql SELECT MIN(sal) FROM emp;
----------
| MIN(sal) |
----------
| 2500 |
----------
1 row in set (0.00 sec)
在上述代码中使用MIN()函数获取了sal字段中最小的数值。
分组查询
在对数据表中的数据进行统计时有时需要按照一定的类别作统计。例如财务在统计每个部门的工资总数时属于同一个部门的所有员工就是一个分组。在MySQL中可以使用GROUP BY根据指定的字段结果集进行分组如果某些记录的指定字段具有相同的价值那么分组后被合并为一条数据。使用GROUP BY分组查询的语法如下
1.单独使用GROUP BY 分组
单独使用GROUP BY进行分组时将根据指定的字段合并数据行。例如我们想要使用SQL语句查询员工表的职位有有哪几种具体SQL语句及执行结果如下
mysql SELECT job FROM emp GROUP BY job;
--------
| job |
--------
| 保洁 |
| 销售 |
| 经理 |
--------
3 rows in set (0.00 sec)
在上述SELECT语句中使用GROUP BY根据job字段中的值对数据表中的记录进行分组从执行结果来看员工的职位一共有三种。
2.GROUP BY和聚合函数一起使用
如果分组查询时要进行统计汇总此时需要将GROUP BY和聚合函数一起使用。例如统计员工表各部门的薪资总和或平均薪资可以使用GROUP BY和聚合函数AVG()、SUM()进行统计具体SQL语句及执行结果如下:
mysql SELECT job,AVG(sal),SUM(sal) FROM emp GROUP BY job;
-----------------------------
| job | AVG(sal) | SUM(sal) |
-----------------------------
| 保洁 | 2500.0000 | 5000 |
| 销售 | 3125.0000 | 12500 |
| 经理 | 7000.0000 | 7000 |
-----------------------------
3 rows in set (0.01 sec)
在上述SELECT语句中使用GROUP BY根据job字段中的值对数据表的记录进行分组值相同的为一组并计算出各个职位的总工资和平均工资。
3.GROUP BY和HAVING关键字一起使用
通常情况下GROUP BY和HAVING关键字一起使用用于对分组后的结果进行条件过滤。例如假如我们想要使用SQL语句查询员工表中的平均工资小于3000的部门编号及这些部门的平均工资。查询时可以使用GROUP BY和HAVING进行统计具体SQL语句及执行结果如下
mysql SELECT empno,AVG(sal) FROM emp GROUP BY empno HAVING AVG(sal)3000;
------------------
| empno | AVG(sal) |
------------------
| 9770 | 2500.0000 |
| 9885 | 2500.0000 |
| 9900 | 2500.0000 |
------------------
3 rows in set (0.00 sec)
在上述SELECT语句中使用GROUP BY根据empno字段中的值对数据表的记录进行分组并且使用HAVING筛选平均工资小于3000的数据最终返回了平均工资小于3000的部门编号及平均工资。
在MySQL中HAVING 子句用于在 GROUP BY 子句后对聚合结果进行过滤。它通常与聚合函数如 SUM(), COUNT(), AVG(), MAX(), MIN() 等一起使用以筛选满足特定条件的分组。
与 WHERE 子句不同WHERE 子句在聚合之前对单个记录进行过滤而 HAVING 子句在聚合之后对分组进行过滤。这一点不要弄错哦。
排序查询
对数据表的数据进行查询时可能查询出来的数据是无序的或者其排列顺序不是用户期望的。如果想要对查询结果按指定的方式排序例如对员工信息按姓名顺序排列等可以使用ORDER BY对查询结果进行排序。查询语句中使用ORDER BY的基本语法格式如下。
SELECT*|{字段名1字段名2,···} FROM 表名 ORDER BY 字段名1 [ASC | DESC], 字段名2 [ASC | DESC]......
在上面的语法格式中ORDER BY后指定的字段名1、字段名2等是对查询结果排序的依据即按照哪一个字段进行排序。参数ASC表示按照升序进行排序DESC表示按照降序进行排序。
使用ORDER BY对查询结果进行排序时如果不指定排序方式默认按照ASC方式进行排序。例如技术人员想要使用SQL语句查询员工表中职位为销售的员工信息查询出的结果根据员工工资升序排列具体SQL语句及执行结果如下
mysql SELECT * FROM emp WHERE job销售 ORDER BY sal;
-----------------------------------
| empno | ename | job | sal | bon |
-----------------------------------
| 9900 | 孙七 | 销售 | 2500 | 200 |
| 9880 | 张三 | 销售 | 3000 | 200 |
| 9775 | 王五 | 销售 | 3500 | 500 |
| 9888 | 郑十 | 销售 | 3500 | NULL |
-----------------------------------
4 rows in set (0.00 sec)
上述SELECT语句使用ORDER BY对job字段值为销售的所有记录按照工资从低到高进行排序即sal字段的值按升序排序。因为没有设置怎么排序所以默认ASC升序排序。
要注意的是如果有字段中的值为NULL那么NULL会被当做最小值进行排序下面按照奖金对销售员工进行排序
mysql SELECT * FROM emp WHERE job销售 ORDER BY bon;
-----------------------------------
| empno | ename | job | sal | bon |
-----------------------------------
| 9888 | 郑十 | 销售 | 3500 | NULL |
| 9880 | 张三 | 销售 | 3000 | 200 |
| 9900 | 孙七 | 销售 | 2500 | 200 |
| 9775 | 王五 | 销售 | 3500 | 500 |
-----------------------------------
4 rows in set (0.00 sec)
上述SELECT语句查询职位为销售的员工信息并且根据员工奖金值进行升序排序。从执行结果可以看出奖金值为NULL的员工信息排在第一位说明排序时NULL被当作最小值。
ORDER BY可以对多个字段的值进行排序并且每个排序字段可以有不同的排序顺序。例如技术人员想要使用SQL语句查询员工表中工资为2500的员工所有记录查询出的记录先按职位的升序排序再按员工编号降序排序具体SQL语b 句及执行结果如下。
mysql SELECT * FROM emp WHERE sal2500 ORDER BY job,empno DESC;
-----------------------------------
| empno | ename | job | sal | bon |
-----------------------------------
| 9885 | 李四 | 保洁 | 2500 | 100 |
| 9770 | 吴九 | 保洁 | 2500 | NULL |
| 9900 | 孙七 | 销售 | 2500 | 200 |
-----------------------------------
3 rows in set (0.00 sec)
在上述SELECT语句中查询sal字段工资为2500的所有记录先将这些记录按照job字段的值升序排序如果job字段的值相同则按照empno字段的值进行降序排序。如果排序字段的值是字符串类型则会按字符串中字符的ASCII码值进行排序。
上面的例子job字段后面没有设置排序因此默认是升序然后又给empno字段设置了降序大家不要迷了啊。
mysql SELECT * FROM emp WHERE sal2500 ORDER BY bon DESC,empno DESC;
-----------------------------------
| empno | ename | job | sal | bon |
-----------------------------------
| 9900 | 孙七 | 销售 | 2500 | 200 |
| 9885 | 李四 | 保洁 | 2500 | 100 |
| 9770 | 吴九 | 保洁 | 2500 | NULL |
-----------------------------------
3 rows in set (0.00 sec)
这个是两个字段都设置了排序并且都是降序。
限量查询
查询数据时SELECT语句可能会返回很多条记录而用户需要的记录可能只是其中的一条或几条。中的一条或几条。例如在员工管理系统中希望每一页默认展示10条员工信息并且可以通过下拉框更改每页展示的员工信息数。MySQL中提供了一个关键字LIMIT可以指定查询结果从哪一条记录开始以及一共查询多少条信息。在SELECT语句中使用LIMIT的基本语法格式如下。
SELECT 字段名1字段名2,..… FROM 数据表名 LIMIT [OFFSET] 记录数
在上面的语法格式中LIMIT后面可以跟2个参数。第一个参数OFFSET为可选值表示偏移量如果偏移量为0则从查询结果的第一条记录开始偏移量为1则从查询结果的第二条记录开始以此类推。如果不指定OFFSET的值其默认值为0。第二个参数“记录数”表示返回查询记录的条数。
例如技术人员想要使用SQL语句查询员工表中工资最高的前3名的员工信息查询时可以使用LIMIT进行限量具体SQL语句及执行结果如下。
mysql SELECT * FROM emp ORDER BY sal DESC LIMIT 3;
-----------------------------------
| empno | ename | job | sal | bon |
-----------------------------------
| 9990 | 周八 | 经理 | 7000 | 1000 |
| 9775 | 王五 | 销售 | 3500 | 500 |
| 9888 | 郑十 | 销售 | 3500 | NULL |
-----------------------------------
3 rows in set (0.00 sec)
在上述SELECT语句中首先使用ORDER BY根据字段sal的值对数据表中的记录进行降序排序接着使用LIMIT指定返回第1~3条记录。
除了指定查询记录数LIMIT还可以通过指定OFFSET的值指定查询的偏移量也就是查询时跳过几条记录。
例如我们要使用SQL语句查询员工表中工资第二名到第五名的员工信息。具体SQL语句及执行结果如下
mysql SELECT * FROM emp ORDER BY sal DESC LIMIT 1,4;
-----------------------------------
| empno | ename | job | sal | bon |
-----------------------------------
| 9775 | 王五 | 销售 | 3500 | 500 |
| 9888 | 郑十 | 销售 | 3500 | NULL |
| 9880 | 张三 | 销售 | 3000 | 200 |
| 9770 | 吴九 | 保洁 | 2500 | NULL |
-----------------------------------
4 rows in set (0.00 sec)
在上述SELECT语句中先使用ORDER BY根据字段sal的值对数据表中的记录进行降序排序然后指定返回记录的偏移量为1查询记录的条数为4.执行结果跳过了排序后的第一条员工信息返回工资前2~5名的员工信息。
这里和Python的索引有点像可以联系理解一下下标就像偏移量都是从0开始。
下篇文章是MySQL中的内置函数的讲解。点个关注不迷路。
