MYSQL数据查询相关操作

2401_83322621

已于 2024-09-13 13:52:40 修改

阅读量1k

点赞数 19

文章标签： mysql 数据库

于 2024-09-07 14:36:48 首次发布

本文链接：https://blog.csdn.net/2401_83322621/article/details/141904580

版权

DQL查询语句，语法结构如下:

select 字段列表 from 表名列表 where 条件列表 grouo by 分组字段列表 having 分组后的条件列表 limit 分页参数

一.基础查询:

1.选择所有的数据:

select * from 表名
*号代表查询所有字段

2.查询多个字段:
select 字段1,字段2,字段3,......from 表名; 多个字段之间用英文逗号分割

3.字段设置别名:

select 字段1 [as 别名1], 字段2 [as 别名] ......from 表名;

select 字段1 [ 别名1], 字段2 [ 别名] ......from 表名;as可以省略

select workaddress from emp;

select workaddress as '工作地址' from emp;#‘workaddress’这一字段名称被设置为'工作地址'

4.去除重复记录(使用distinct):

select distinct 字段列表 from 表名;

5.条件查询(使用where):

运算符	功能
between.....and..	在某个范围之内(包含最小，最大值)
in(....)	在in之后的列表中的值,多选一
like占位符	模糊匹配( '_' 匹配单个字符,'%'匹配任意个字符)
is null	判断是否是空值

逻辑运算符	功能
and 或 &&	并且(多个条件同时成立)
or 或 \|\|	或者(多个条件任意一个成立)
not 或 !	非,不是

(1).查询年龄等于 88 的员工:

select * from emp where age = 88

查询没有身份证号的员工信息：

select * from emp where idcard is null;

(2).查询姓名为两个字的员工信息 __, :

slect * from emp where name like '__';

(3)查询身份证号最后一位是X的员工信息：

select * from emp where idcard like '%x'; %表示任意字符

select * from emp where idcard like '________________x';

二.聚合函数与查询:

(将一列数据作为一个整体，进行纵向计算，注意nulll值是不参与所有聚合函数计算)：
语法:select 聚合函数(字段列表) from 表名;

聚合函数的参考可参考此文章:https://blog.csdn.net/Sihang_Xie/article/details/125772825

三.分组查询:

(1)语法:

select 字段列表 from 表名 [where 条件] groupby by 分组字段名 [having 分组后过滤条件]
where与having区别:
(1).执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
(2).判断条件不同：where不能对聚合函数进行判断，而having可以。
注意事项:
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB

(2)创建分组：

1.对数据进行分组，一般有两种使用场景：
⑴单独使用GROUP BY关键字，
⑵将GROUP BY关键字与聚合函数一起使用(常用)
2.对于GROUP BY子句的使用，需要注意以下几点：
⑴GROUP BY子句可以包含任意数目的列，使其可以对分组进行嵌套，为数据分组提供更加细致的控制
⑵GROUP BY子句列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数。若在SELECT语句中使用表达式，则必须在GROUP BY子句中指定相同的表达式
3.若用于分组的列中包含有NULL值，则NULL将作为一个单独的分组返回；若该列中存在多个NULL值，则将这些NULL值所在的行分为一组

四.GROUP BY单独使用:

注意:GROUP BY 时，MySQL 默认会选取每个分组中的一行记录，具体选择哪一行是不确定的，通常是表中的第一行

单独使用 GROUP BY关键字时，查询结果会只显示每个分组的第一条记录:

mysql> select * from emp;
+------+--------+--------+--------+------+--------------------+-------------+------------+
| id   | workno | name   | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+--------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏   | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭   | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍   | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶   | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|   16 | 00016  | 周芷若 | 女     |   18 | NULL               | 北京        | 2012-06-01 |
+------+--------+--------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)

mysql> select * from emp group by gender;
+------+--------+------+--------+------+--------------------+-------------+------------+
| id   | workno | name | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏 | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    6 | 00006  | 杨逍 | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
+------+--------+------+--------+------+--------------------+-------------+------------+
2 rows in set (0.00 sec)

从中我们可以看到对 gender这个字段分组时，只显示了每个分组的第一条记录.

注：
(1)由于是单独使用的GROUP BY关键字，因此只会返回每个分组的第一条记录
(2)这样的的结果展示没有多大意义，所以group by 通常与聚合函数一起使用

group by与where一起使用对数据过滤后再进行分组：

mysql> select * from emp;
+------+--------+--------+--------+------+--------------------+-------------+------------+
| id   | workno | name   | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+--------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏   | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭   | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍   | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶   | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|   16 | 00016  | 周芷若 | 女     |   18 | NULL               | 北京        | 2012-06-01 |
+------+--------+--------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)

mysql> select * from emp where age>=28 group by gender;
+------+--------+------+--------+------+--------------------+-------------+------------+
| id   | workno | name | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+------+--------+------+--------------------+-------------+------------+
|    6 | 00006  | 杨逍 | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
+------+--------+------+--------+------+--------------------+-------------+------------+
1 row in set (0.00 sec)

注：
(1).先筛选表中"age>=28"的数据(查询的部分列)，再对符合条件的数据里找"gender"字段值进行分组
(2).只展示了分组的第一条数据

说明：
前面两个例子中：单独使用GROUP BY关键字只显示每个分组的一条记录
⑴这说明：GROUP BY关键字单独使用时，只能查询出每个分组的一条记录
⑵这样做的意义不大。因此，一般在使用聚合函数时才使用GROUP BY关键字

五.GROUP BY与聚合函数：

(1)GROUP BY关键字通常与集合函数一起使用。集合函数包括COUNT()函数、SUM()函数、 AVG()函数、MAX()函数和MIN()函数
(2)如果GROUP BY不与聚合函数一起使用，那么查询结果就是字段取值的分组情况
(3)字段中取值相同的记录为一组，但是只显示该组的第一条记录(跟前面GROUP BY单独使用一样)

mysql> select * from emp;
+------+--------+--------+--------+------+--------------------+-------------+------------+
| id   | workno | name   | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+--------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏   | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭   | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍   | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶   | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|   16 | 00016  | 周芷若 | 女     |   18 | NULL               | 北京        | 2012-06-01 |
+------+--------+--------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)


mysql> select gender,count(*) from emp group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 女     |        3 |
| 男     |        2 |
+--------+----------+
2 rows in set (0.01 sec)

注：
1、上面例子表示：查询表中所有的数据，按照"gender"字段值进行分组，然后计算每组的记录条数

GROUP BY与GROUP_CONCAT函数：

GROUP BY关键字可以和GROUP_CONCAT()函数一起使用。GROUP_CONCAT()函数会把每个分组的字段值都显示出来

mysql> select * from emp;
+------+--------+--------+--------+------+--------------------+-------------+------------+
| id   | workno | name   | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+--------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏   | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭   | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍   | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶   | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|   16 | 00016  | 周芷若 | 女     |   18 | NULL               | 北京        | 2012-06-01 |
+------+--------+--------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)


mysql> select gender,count(*),group_concat(name) from emp group by gender;
+--------+----------+--------------------+
| gender | count(*) | group_concat(name) |
+--------+----------+--------------------+
| 女     |        3 | 赵敏,小昭,周芷若   |
| 男     |        2 | 杨逍,范瑶          |
+--------+----------+--------------------+
2 rows in set (0.00 sec)

mysql>

注：可以看出:
(1).对字段 'gender'分组后，使用聚合函数count计算每个组的个数，然后再使用函数 group_concat展示出每个组所有的人的姓名name。

多字段分组:

1.使用GROUP BY可以对多个字段进行分组，GROUP BY关键字后面跟需要分组的字段
2.MYSQL根据多字段的值来进行层次分组，分组层次从左到右
⑴即先按第一个字段分组，然后在第一个字段值相同的记录中，再根据第二个字段的值进行分组...一次类推

mysql> select * from emp;
+------+--------+--------+--------+------+--------------------+-------------+------------+
| id   | workno | name   | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+--------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏   | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭   | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍   | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶   | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|   16 | 00016  | 周芷若 | 女     |   18 | NULL               | 北京        | 2012-06-01 |
+------+--------+--------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)


mysql>  select *,group_concat(name) from emp group by gender,age;
+------+--------+------+--------+------+--------------------+-------------+------------+--------------------+
| id   | workno | name | gender | age  | idcard             | workaddress | entrydate  | group_concat(name) |
+------+--------+------+--------+------+--------------------+-------------+------------+--------------------+
|    5 | 00005  | 小昭 | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 | 小昭               |
|    4 | 00004  | 赵敏 | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 | 赵敏,周芷若        |
|    6 | 00006  | 杨逍 | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 | 杨逍               |
|    7 | 00007  | 范瑶 | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 | 范瑶               |
+------+--------+------+--------+------+--------------------+-------------+------------+--------------------+
4 rows in set (0.00 sec)

注:
(1).先对字段gender进行分组,然后再对字段age分组
(2).分组了之后我们需要用groupby_concat()函数显示出人员的分组的名单,可以看到赵敏与周芷诺在一个组.

六.使用HAVING过滤分组(对分组之后的结果进行过滤)：

1.GROUP BY可以和HAVING一起限定显示记录所需满足的条件：只有满足条件的分组才会被显示

2.HAVING关键字是对分组结果进行过滤。WHERE关键字是对表数据进行过滤
⑴两者同时存在时：肯定是先计算WHERE，WHERE排除的记录肯定是不会出现在分组内的

mysql> select * from emp;
+------+--------+--------+--------+------+--------------------+-------------+------------+
| id   | workno | name   | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+--------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏   | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭   | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍   | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶   | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|   16 | 00016  | 周芷若 | 女     |   18 | NULL               | 广州        | 2012-06-01 |
+------+--------+--------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)



mysql> select *,group_concat(name),group_concat(age) from emp group by gender having age>18;
+------+--------+------+--------+------+--------------------+-------------+------------+--------------------+-------------------+
| id   | workno | name | gender | age  | idcard             | workaddress | entrydate  | group_concat(name) | group_concat(age) |
+------+--------+------+--------+------+--------------------+-------------+------------+--------------------+-------------------+
|    6 | 00006  | 杨逍 | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 | 杨逍,范瑶          | 28,40             |
+------+--------+------+--------+------+--------------------+-------------+------------+--------------------+-------------------+
1 row in set (0.00 sec)

注：
从上面可以看出先对字段 gender 分组，分组之后使用having对分组的结果进行过滤
选出age大于18的数据

(7)GROUP BY与WITH ROLLUP:

WITH POLLUP关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量

mysql>  select * from emp;
+------+--------+--------+--------+------+--------------------+-------------+------------+
| id   | workno | name   | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+--------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏   | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭   | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍   | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶   | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|   16 | 00016  | 周芷若 | 女     |   18 | NULL               | 广州        | 2012-06-01 |
+------+--------+--------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)

mysql> select name,age,count(*) from emp group by age;
+------+------+----------+
| name | age  | count(*) |
+------+------+----------+
| 赵敏 |   18 |        2 |
| 小昭 |   16 |        1 |
| 杨逍 |   28 |        1 |
| 范瑶 |   40 |        1 |
+------+------+----------+
4 rows in set (0.00 sec)

mysql> select name,age,count(*) from emp group by age with rollup;
+------+------+----------+
| name | age  | count(*) |
+------+------+----------+
| 小昭 |   16 |        1 |
| 赵敏 |   18 |        2 |
| 杨逍 |   28 |        1 |
| 范瑶 |   40 |        1 |
| 范瑶 | NULL |        5 |
+------+------+----------+
5 rows in set (0.01 sec)

注：
通过GROUP BY分组之后，在显示结果的最后面新添加了一行，该行每列的值正好是上面所有值之和

GROUP BY与ORDER BY:

某些情况下需要对分组进行排序:
语法:SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

⑴一般情况下ORDER BY是用来对查询结果进行排序的。当其与GROUP BY一起使用时，可以对分组结果进行排序
(2).排序方式:默认时升序(ASC),降序是:DESC
需要注意：当使用ROLLUP时，就不能同时使用ORDER BY子句进行结果排序了ROLLUP和 ORDER BY是互相排斥的。
如果是升序, 可以不指定排序方式ASC ;
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;
例7：

mysql> select * from emp;
+------+--------+--------+--------+------+--------------------+-------------+------------+
| id   | workno | name   | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+--------+--------+------+--------------------+-------------+------------+
|    4 | 00004  | 赵敏   | 女     |   18 | 123456757123845670 | 北京        | 2009-12-01 |
|    5 | 00005  | 小昭   | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍   | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
|    7 | 00007  | 范瑶   | 男     |   40 | 123456789212345670 | 北京        | 2005-05-01 |
|   16 | 00016  | 周芷若 | 女     |   18 | NULL               | 广州        | 2012-06-01 |
+------+--------+--------+--------+------+--------------------+-------------+------------+
5 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM emp
    -> WHERE age = (SELECT MIN(age) FROM emp AS e2 WHERE e2.gender = emp.gender)
    -> ORDER BY gender;
+------+--------+------+--------+------+--------------------+-------------+------------+
| id   | workno | name | gender | age  | idcard             | workaddress | entrydate  |
+------+--------+------+--------+------+--------------------+-------------+------------+
|    5 | 00005  | 小昭 | 女     |   16 | 123456769012345678 | 上海        | 2007-07-01 |
|    6 | 00006  | 杨逍 | 男     |   28 | 12345678931234567X | 北京        | 2006-01-01 |
+------+--------+------+--------+------+--------------------+-------------+------------+
2 rows in set (0.01 sec)

从中我们可以看出我们对字段gender分组之后，然后对age进行排序，获得出来的结果.

七.分页查询:

语法:SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
注意事项:
起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10

A. 查询第 1 页员工数据 , 每页展示 10 条记录
select * from temp limit 0,10;
select * from temp limit 10;
B.查询第 2 页员工数据 , 每页展示 10 条记录 --------> ( 页码 -1)* 页展示记录数
select * from temp 10,10;

九.DQL语句的执行顺序：

验证：

1.查询年龄大于 15 的员工姓名、年龄，并根据年龄进行升序排序

select name , age from emp where age > 15 order by age asc;

2.在查询时，我们给emp表起一个别名 e，然后在select 及 where中使用该别名。
select e.name , e.age from emp e where e.age > 15 order by age asc;执行上述SQL语句后，我们看到依然可以正常的查询到结果，此时就说明： from 先执行, 然后 where 和 select 执行。那 where 和 select 到底哪个先执行呢?

3.此时，此时我们可以给 select 后面的字段起别名，然后在 where 中使用这个别名，然后看看是否可以执行成功。

select e.name ename , e.age eage from emp e where eage > 15 order by age asc;
执行上述SQL报错:

由此我们可以得出结论 : from 先执行，然后执行 where ，再执行 select 。

4.接下来，我们再执行如下SQL语句，查看执行效果：
select e.name ename , e.age eage from emp e where e.age > 15 order by eage asc;
结果执行成功。那么也就验证了 : order by 是在 select 语句之后执行的。

5.综上所述，我们可以看到 DQL 语句的执行顺序为： from ... where ... group by ...

having ... select ... order by ... limit ...

更详细的用法请参考:https://blog.csdn.net/qq_39314932/article/details/109708498

MySQL高级查询语句——超详细，一篇就够了-CSDN博客