MySQL中的高级查询

这篇具有很好参考价值的文章主要介绍了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 sal=2500 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 sal=2500 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中的内置函数的讲解。点个关注不迷路。文章来源地址https://www.toymoban.com/news/detail-836569.html

到了这里,关于MySQL中的高级查询的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL数据库高级操作

    if not exists:表示检测要创建的表是否已存在,如果不存在就继续创建 int(4) zerofill:表示若数值不满4位数,则前面用“0”填充,例0001 auto_increment:表示此字段为自增长字段,即每条记录自动递增1,默认从1开始递增;自增长字段数据不可以重复;自增长字段必须是主键;如添

    2024年02月09日
    浏览(68)
  • MySQL数据库 --- 高级篇

    1.1.1、连接层 最上层是一些客户端和链接服务,包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上

    2024年02月08日
    浏览(41)
  • 【MySQL数据库】MySQL 高级SQL 语句一

    ) % :百分号表示零个、一个或多个字符 _ :下划线表示单个字符 ‘A_Z’:所有以 ‘A’ 起头,另一个任何值的字符,且以 ‘Z’ 为结尾的字符串。例如,‘ABZ’ 和 ‘A2Z’ 都符合这一个模式,而 ‘AKKZ’ 并不符合 (因为在 A 和 Z 之间有两个字符,而不是一个字符)。 ‘ABC%’

    2024年02月09日
    浏览(66)
  • MySQL数据库管理高级语句

    复制表及内容     克隆表 获取数据表的表结构、索引等信息    清空表,删除表内的所有数据       删除的特点: 创建临时表 临时表创建成功之后,使用SHOWTABLES命令是看不到创建的临时表的, 临时表会在连接退出后被销毁。 如果在退出连接之前,也可以可执行增删改查

    2024年02月11日
    浏览(47)
  • 数据库应用:MySQL高级语句(一)

    目录 一、理论 1.常用查询 2.函数 3.进阶查询 二、实验 1.普通查询 2.函数 3.进阶查询 三、问题 1.MySQL || 运算符不生效 四、总结 常用查询包括:增、删、改、查; 对 MySQL 数据库的查询,除了基本的查询外,有时候需要对查询的结果集进行处理。 (1)selelct select,显示表格中

    2024年02月17日
    浏览(70)
  • MySQL数据库——MySQL WHERE:条件查询数据

    在 MySQL 中,如果需要有条件的从数据表中查询数据,可以使用 WHERE 来指定查询条件。 使用 WHERE 的语法格式如下: WHERE 查询条件 查询条件可以是: 带比较运算符和逻辑运算符的查询条件 带 BETWEEN AND 的查询条件 带 IS NULL 的查询条件 带 IN

    2024年02月03日
    浏览(43)
  • 数据库应用:MySQL数据库SQL高级语句与操作

    目录 一、理论 1.克隆表与清空表 2.SQL高级语句 3.SQL函数 4.SQL高级操作 5.MySQL中6种常见的约束 二、实验  1.克隆表与清空表 2.SQL高级语句 3.SQL函数 4.SQL高级操作 5.主键表和外键表  三、总结 克隆表:将数据表的数据记录生成到新的表中。 (1)克隆表 ① 先创建再导入 ② 创建

    2024年02月13日
    浏览(60)
  • MySQL数据库实验三 MySQL查询

    一、实验项目: MySQL查询。 二、实验目的 掌握MySQL的查询操作。 三、实验内容 (一): 1、查询lineitem表中商品编号(productid)和单价(unitprice),要求消除重复行。 2、计算lineitem表中每条记录的商品金额。 3、显示orders表单笔高于200元的客户号(userid)、成交金额(total

    2024年02月11日
    浏览(43)
  • 【数据库】MySQL 高级(进阶) SQL 语句

    location表格创建 store_info表格创建 显示表格中一个或数个字段的所有数据记录 不显示重复的数据记录 按照条件进行查询 在已知的字段数据取值范围内取值 另外还有not in命令,用法一致,表示显示不在指定范围内的字段的值。 在两个字段数据值之间取值,包含两边字段的数据

    2024年02月09日
    浏览(120)
  • linux高级管理——访问MYSQL数据库

    一、认识数据库系统:  MySQL数据库系统也是一个典型的C/S(客户端/服务器)架构的应用,要访问MySQL数据库需要使用专门的客户端软件。在Linux系统中,最简单、易用的MySQL客户端软件是其自带的mysql命令工具。 1.登录到MySQL服务器 经过安装后的初始化过程,MySQL 数据库的默

    2024年02月04日
    浏览(33)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包