526互联

MySQL DQL语句

发布时间 2023-11-17 18:15:14作者: 背对背依靠

查询数据 SELECT

DQL是数据库查询语言，主要就是通过SELECT关键字来从数据库中查询指定规则的数据。

语法格式：

SELECT
    需要查询的列名字
FROM
    表名称
WHRER
    查询条件

说明：

1、如果需要查询所有的列，SELECT的参数指定为 * ，如果查询的列名有多个，使用逗号隔开

2、如果没有使用 where 指定查询条件，默认就是查询表中所有数据的信息

1、排序规则 ORDER BY

默认情况下，使用select查询出来的结果是按照它在表中出现的顺序显示，如果需要将查询出来的数据进行排序，可以使用 ORDER BY 关键字来指定。 ORDER BY 指定的参数就是排序的依据。

例如：

SELECT
    Name
FROM
    Employees
ORDER BY
    Salary;

ORDER BY特点：

1、ORDER BY关键字只能位于一条SQL语句的最后面，它的后面不能在有SQL关键字

2、使用 ORDER BY 指定的列名来进行排序时，即使这个列在最终的查询结果中没有显示出来也可以

3、如果指定多个列名进行排序时，要用逗号进行隔开

4、ORDER BY的默认排序规则事升序（Ascending即ASC），如果想要以降序（Descending即DESC）排序，需要在 ORDER BY 子句后明确指定 DESC 关键字

2、指定过滤条件 WHERE

如果需要查询符合特定条件的行，就需要使用WHRER关键字来指定过滤条件。where支持很多操作符号，最常用的就是 = 号了，还有其它一些操作符可以使用。

单个值检查：

=   >    <>   >=   IS NULL

IS NULL  # 判断是否为NULL值

# <> 表示的是不等于

多值检查：

BETWEEN：在指定的两个值之间

where说明：

1、whrer 指定的参数就是进行过滤的条件，列名 操作符 值 ， 如果值是字符串的话需要用引号括起来，数值不用加引号。

2、如果过滤条件有多个，可以用OR或者AND关键字来进行连接，AND的话输出的是条件都满足的行会输出，OR的话是满足其一即可。

3、还有一个类西域OR的关键字IN，用于指定一个范围检查某列的值是否在这个范围内。使用IN操作符时，小括号用于封装一系列的值，以便进行比较操作

4、还可以指定通配符进行过滤，这个时候需要使用like关键字，常用的通配符有两个。%表示任何字符出现任意次。下划线（\_）表示它只匹配单个字符数

例如：

SELECT * FROM Students where id IN (1,2,3,4,5)

3、五个聚合函数

聚合函数是通过执行一系列值上的计算，并返回单个值的函数。所以聚合函数的特点就是最终结果返回一个值。

标准的SQL提供了5个聚合函数：

（1）count（）；输出满足特定条件的行数字，如果参数为 * 表示通过真个数据表有多少行。

mysql> select count(*) from tb_locationinfo_20231112;
+----------+
| count(*) |
+----------+
|      111 |
+----------+
1 row in set (0.19 sec)

# 传递的参数是一个列名字，则该列中有值（即非 NULL 值）的行会被计数。
mysql> select count(card_id) from tb_locationinfo_20231112;
+----------------+
| count(card_id) |
+----------------+
|            111 |
+----------------+
1 row in set (0.00 sec)

（2）MAX（）：计算指定列中的最大值。传递的参数是一个列名字

mysql> select MAX(card_id) from tb_locationinfo_20231112;
+--------------+
| MAX(card_id) |
+--------------+
|       313556 |
+--------------+
1 row in set (0.00 sec)

（3）MIN（）：计算指定列中的最小值。传递的参数是一个列名字

（4）SUM（）：计算指定列的总和

mysql> select SUM(card_id) from tb_locationinfo_20231112;
+--------------+
| SUM(card_id) |
+--------------+
|      2432755 |
+--------------+
1 row in set (0.00 sec)

说明：

SUM 函数设计用于对数值数据（如整数、浮点数等）进行操作，而对于字符、字符串或其他非数值数据类型，它不知道如何进行合适的求和操作。所以对非数值列（如字符或文本列）进行求和时，这通常会导致错误或异常。

（5）AVG（）：计算某列数值的平均值，和SUM一样是对数值类型数据进行操作。

mysql> select AVG(card_id) from tb_locationinfo_20231112;
+--------------+
| AVG(card_id) |
+--------------+
|   21916.7117 |
+--------------+
1 row in set (0.02 sec)

4、指定别名 AS

为了提高可读性和简化查询语句，可以使用 AS 关键子来指定别名，可以为列、表或者聚合函数指定别名。

5、数据分组 GROUP BY

一般使用聚合函数的时候，都需要姜输出结果进行分组。要实现分组功能就需要使用 GROUP BY 关键字了，使用这个关键字的时候需要指定分组的列。

GROUP BY有一个特点，就是在输出的列中，除了是聚合函数外，只有GROUP BY 关键字指定的列才可以直接出现在 SELECT 语句的列列表中。

错误示范：

group by 关键字指定的分组依据是 power 列，但是select 却列出了所有列。这是不对的

select * from tb_card group by power ;

正确示范：

select power, count(*) from tb_card group by power;

单独使用数据分组：

主要就是用来去重的吧，将指定字符的重复数据只输出一次。效果和使用 DISTINCT 关键字的效果相同

例如：

mysql> select ip  from tb_log  group by ip;
+----------------+
| ip             |
+----------------+
| 192.168.14.99  |
| 192.168.14.159 |
| 192.168.12.170 |
| 192.168.13.79  |
| 192.168.15.177 |
| 192.168.12.177 |
| 192.168.5.202  |
| 192.168.13.74  |
| 192.168.13.84  |
| 192.168.13.153 |
| 192.168.13.166 |
| 192.168.17.1   |
| 192.168.14.181 |
+----------------+
13 rows in set (0.00 sec)

# 使用 distinct关键字也能去重
mysql> select DISTINCT ip  from tb_log  group by ip;

结合聚合函数使用：

分组结合聚合函数使用就可以实现一些更高级的数据分析功能了。

例如：

mysql> select ip, count(*)  from tb_log  group by ip;
+----------------+----------+
| ip             | count(*) |
+----------------+----------+
| 192.168.14.99  |       20 |
| 192.168.14.159 |       27 |
| 192.168.12.170 |        2 |
| 192.168.13.79  |        1 |
| 192.168.15.177 |        1 |
| 192.168.12.177 |        1 |
| 192.168.5.202  |        1 |
| 192.168.13.74  |        5 |
| 192.168.13.84  |      116 |
| 192.168.13.153 |        3 |
| 192.168.13.166 |        1 |
| 192.168.17.1   |        1 |
| 192.168.14.181 |       10 |
+----------------+----------+
13 rows in set (0.00 sec)

当 COUNT(*) 与 GROUP BY 一起使用时，查询的过程是这样的：

1、首先，SQL 会根据 GROUP BY 子句中指定的列对数据进行分组。意味着所有具有相同 ip 值的行会被归为同一组。

2、接着，对于每个这样的分组，COUNT(*) 聚合函数会计算该分组内的行数。

3、最后，查询的结果将列出每个唯一的 ip 地址及其在表中出现的行数。

所以，如果没有指定分组，count(*)表示获取整个表的行数，如果指定了分组，表示的是每个分组中的行数。

所以由此我们可知，当聚合函数（如 SUM, AVG, MAX, MIN, COUNT 等）与 GROUP BY 子句一起使用时，聚合函数的操作范围变为 GROUP BY 指定的每个分组，而不是整个表。这意味着聚合函数将对每个分组中的数据进行独立的计算。

6、条件过滤 HAVING

在某些时候，使用分组结合聚合函数将数据输出后，可能还需要对输出的内容进行一定的过滤。

例如：

mysql> select ip, count(*) as count   from tb_log  group by ip;
+----------------+-------+
| ip             | count |
+----------------+-------+
| 192.168.14.99  |    20 |
| 192.168.14.159 |    27 |
| 192.168.12.170 |     2 |
| 192.168.13.79  |     1 |
| 192.168.15.177 |     1 |
| 192.168.12.177 |     1 |
| 192.168.5.202  |     1 |
| 192.168.13.74  |     8 |
| 192.168.13.84  |   116 |
| 192.168.13.153 |     3 |
| 192.168.13.166 |     1 |
| 192.168.17.1   |     1 |
| 192.168.14.181 |    10 |
+----------------+-------+
13 rows in set (0.00 sec)

如果需要过滤数量大于10的ip地址，使用where指定过滤条件的话会提示异常。

mysql> select ip, count(*) as count   from tb_log  group by ip where count > 10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where count > 10' at line 1

因为 WHERE 的条件必须针对原始数据表中实际存在的列。而且根据SQL语法规定，where只能过滤分组之前的数据，如果要过滤分组之后的数据，SQL 给了一个having关键字，这个关键字指定的选项不一定在表中真实存在，例如count就是聚合函数count(*)的别名。

所以正确的用法是：

mysql> SELECT ip, COUNT(*) AS count FROM tb_log GROUP BY ip HAVING count  > 10;
+----------------+-------+
| ip             | count |
+----------------+-------+
| 192.168.14.99  |    20 |
| 192.168.14.159 |    27 |
| 192.168.13.84  |   116 |
+----------------+-------+
3 rows in set (0.00 sec)

7、子查询

所谓的子查询就是将一个select语句嵌套在另外一个语句（SELECT、INSERT、UPDATE 或 DELETE）里面。

子查询出现位置：
子查询可以出现在多个地方，包括 SELECT 子句（作为列的一部分）、FROM 子句（作为数据源）、WHERE 子句（作为条件的一部分）等。

例如：

select 子查询语句 FROM tb_name

select 列名 FROM 子查询语句

select 列名 FROM tb_name where 子查询语句

8、联结表 join

联结表就是表中表之间的共同字段来将这些表进行结合，从而可以从多个表中查询相关数据。

内联结 iner join

内联结又叫做等值联结，是基于两个表中指定列的值相等来联结数据，只有当这两个表中的指定列值匹配时，相关的记录才会出现在查询结果中。

语法格式：

select 需要显示的列名
    FROM table1,table2
WHERE
    联结条件

例如：

SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;

这种方式是通过 FROM 来指定联结的表，通过 WHERE指定联结两个表的条件。这种SQL风格早期的时候使用很多，但是存在一个问题就是在处理更多的表和更复杂的操作时候，不好维护。所以后面搞了个SQL语法叫作显式内联结。

显式内联结语法：

select
    列名
FROM
    A_table inner join  B_table
ON
    联结条件

这种通过 inner join来列出进行联结的两个表，通过 ON来指定联结条件，在处理多表联结和复杂查询时更加清晰，易于维护

例如：联结两个表

SELECT
    vend_name, prod_name, prod_price
FROM
    Vendors
INNER JOIN
    Products
ON
    Vendors.vend_id = Products.vend_id;

使用场景： 需要从两个或更多相关表中结合，输出对应信息

外联结 out join

和内联结不同的是，外联结会会包含没有匹配项的行根据包含未匹配行的表的不同，又分为左联结（left join），右联结（right join）和全联结（full jion）。

  • 左联结：返回左表（LEFT JOIN 左侧的表）的所有记录，以及右表中与左表匹配的记录。如果右表中没有匹配项，则相应的部分会显示为 NULL

  • 右联结：返回右表（RIGHT JOIN 右侧的表）的所有记录，以及左表中与右表匹配的记录。如果左表中没有匹配项，则相应的部分会显示为 NULL

  • 全联结：返回左表和右表中的所有记录。如果一侧的表中没有匹配项，那么另一侧的相应部分将显示为 NULL

语法格式：

和内联结不同的是，使用隐式方法来实现外联结是不大可能实现的，官方只提供了显示的SQL语法规则。

# 左联结
SELECT 列名
    A LEFT JOIN B
    ON 联结条件

# 右联结
SELECT 列名
    A RIGHT JOIN B
    ON 联结条件

# 全联结
SELECT 列名
    A FULL JOIN B
    ON 联结条件

例如：

SELECT
    tb_person.name as '姓名',
    tb_branch.branch_name as '班组',
    FROM_UNIXTIME(down_time) as '进入时间',
    FROM_UNIXTIME(up_time ) as '离开时间',
    CONVERT(stay_time/3600,DECIMAL(4, 1)) as '进洞时长'
FROM
    tb_up_down_pit_record
LEFT JOIN
    tb_person ON tb_up_down_pit_record.uuid =tb_person.uuid
LEFT JOIN
    tb_branch ON tb_person.branch_id =tb_person.branch_id
ORDER BY `update_time` DESC

9、联合查询 UNION

UNION 是一个操作符，作用就是将多个SELECT语句的输出组合在一起，并且如果两个不同的 SELECT 语句产生了一些相同的行，UNION 会确保这些重复的行在最终结果集中只出现一次。

基本语法：

SELECT XXXX
UNION
SELECT XXXX;

UNION操作符特点：

1、如果不想把重复的行消除，使用 UNION ALL，它不会检查重复项

2、要使用 UNION 操作，需要满足以下两个条件：即SELECT 语句必须拥有相同数量的列和对应的列需要具有兼容的数据类型。

使用场景：

1、从不同的表中合并结构相似的数据

2、在某些情况下，UNION 可以作为连接（JOIN）多个表的一个更简单或更直观的替代方法，尤其是当涉及的表结构不便于直接连接时