【SQL 语言】 DML
【SQL 语言】 DML
Metadata
title: 【SQL 语言】 DML
date: 2022-12-20 15:41
tags:
- 行动阶段/完成
- 主题场景/数据存储
- 笔记空间/KnowladgeSpace/ProgramSpace/BasicsSpace
- 细化主题/数据存储/SQL_语言
categories:
- 数据存储
keywords:
- 数据存储
description: 数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句，通常是数据库专用编程语言之中的一个子集，例如在信息软件产业通行标准的SQL语言中，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入(意指新增或创建)、更新(修改)与删除(销毁)。在使用数据库的系统开发过程中，其中应用程序必然会使用的指令；而加上 SQL的SELECT语句，欧美地区的开发人员把这四种指令，以“CRUD”(分别为 Create, Read, Update, Delete英文四前缀字母缩略的术语)来称呼；而亚洲地区使用汉语的开发人员，或可能以四个汉字：增 查 改 删 来略称。
简介
数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句，通常是数据库专用编程语言之中的一个子集，例如在信息软件产业通行标准的SQL语言中，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入(意指新增或创建)、更新(修改)与删除(销毁)。在使用数据库的系统开发过程中，其中应用程序必然会使用的指令；而加上 SQL的SELECT语句，欧美地区的开发人员把这四种指令，以“CRUD”(分别为 Create, Read, Update, Delete英文四前缀字母缩略的术语)来称呼；而亚洲地区使用汉语的开发人员，或可能以四个汉字：增 查 改 删来略称。
语法结构
DML 的主要功能即是访问数据，因此其语法都是以读取与写入数据库为主，除了INSERT以外，其他指令都可能需搭配WHERE指令来过滤数据范围，或是不加WHERE指令来访问全部的数据。
SELECT
主条目：SELECT
SELECT是SQL数据操纵语言(DML)中用于查询表格内字段数据的指令，可搭配条件限制的子句(如where)或排列顺序的子句(如order)来获取查询结果。
SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
DISTINCT
相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。
SELECT DISTINCT col1, col2
FROM mytable;
LIMIT
限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。
INSERT
主条目：INSERT
INSERT 是将数据插入到数据库对象中的指令，可以插入数据的数据库对象有数据表以及可更新查看表两种。
普通插入
INSERT INTO mytable(col1, col2)
VALUES(val1, val2);
插入检索出来的数据
INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;
将一个表的内容插入到一个新表
CREATE TABLE newtable AS
SELECT * FROM mytable;
UPDATE
主条目：UPDATE
UPDATE 指令是依给定条件，将匹配条件的数据表中的数据更新为新的数值。
UPDATE mytable
SET col = val
WHERE id = 1;
DELETE
主条目：DELETE
DELETE 指令为自数据库对象中删除数据的指令。
DELETE FROM mytable
WHERE id = 1;
TRUNCATE TABLE 可以清空表，也就是删除所有行。
TRUNCATE TABLE mytable;
DQL
排序
- ASC : 升序(默认)
- DESC : 降序
可以按多个列进行排序，并且为每个列指定不同的排序方式:
SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;
过滤
不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| < | 小于 |
| > | 大于 |
| <> != | 不等于 |
| <= !> | 小于等于 |
| >= !< | 大于等于 |
| BETWEEN | 在两个值之间 |
| IS NULL | 为 NULL 值 |
应该注意到，NULL 与 0、空字符串都不同。
AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。
IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
NOT 操作符用于否定一个条件。
通配符
通配符也是用在过滤语句中，但它只能用于文本字段。
% 匹配 >=0 个任意字符；_ 匹配 ==1 个任意字符；[ ] 可以匹配集合内的字符，例如[ab]将匹配字符 a 或者 b。脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。
使用 Like 来进行通配符匹配。
SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本
不要滥用通配符，通配符位于开头处匹配会非常慢。
计算字段
在数据库服务器上完成数据的转换和格式化的工作往往比客户端上快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。
计算字段通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。
SELECT col1 * col2 AS alias
FROM mytable;
CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;
函数
各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。
汇总
| 函 数 | 说 明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
AVG() 会忽略 NULL 行。
使用 DISTINCT 可以让汇总函数值汇总不同的值。
SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;
文本处理
| 函数 | 说明 |
|---|---|
| LEFT() | 左边的字符 |
| RIGHT() | 右边的字符 |
| LOWER() | 转换为小写字符 |
| UPPER() | 转换为大写字符 |
| LTRIM() | 去除左边的空格 |
| RTRIM() | 去除右边的空格 |
| LENGTH() | 长度 |
| SOUNDEX() | 转换为语音值 |
其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。
SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')
日期和时间处理
- 日期格式: YYYY-MM-DD
- 时间格式: HH:MM:SS
| 函 数 | 说 明 |
|---|---|
| AddDate() | 增加一个日期(天、周等) |
| AddTime() | 增加一个时间(时、分等) |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期之差 |
| Date_Add() | 高度灵活的日期运算函数 |
| Date_Format() | 返回一个格式化的日期或时间串 |
| Day() | 返回一个日期的天数部分 |
| DayOfWeek() | 对于一个日期，返回对应的星期几 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Month() | 返回一个日期的月份部分 |
| Now() | 返回当前日期和时间 |
| Second() | 返回一个时间的秒部分 |
| Time() | 返回一个日期时间的时间部分 |
| Year() | 返回一个日期的年份部分 |
数值处理
| 函数 | 说明 |
|---|---|
| SIN() | 正弦 |
| COS() | 余弦 |
| TAN() | 正切 |
| ABS() | 绝对值 |
| SQRT() | 平方根 |
| MOD() | 余数 |
| EXP() | 指数 |
| PI() | 圆周率 |
| RAND() | 随机数 |
分组
分组就是把具有相同的数据值的行放在同一组中。
可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。
指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;
WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;
分组规定:
- GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
- 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
- NULL 的行会单独分为一组；
- 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。
子查询
子查询中只能返回一个字段的数据。
可以将子查询的结果作为 WHRER 语句的过滤条件:
SELECT cust_name, (SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id)
AS orders_num
FROM Customers
ORDER BY cust_name;
连接
连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。
连接可以替换子查询，并且比子查询的效率一般会更快。
可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。
内连接
内连接又称等值连接，使用 INNER JOIN 关键字。
SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;
可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。
SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;
在没有条件语句的情况下返回笛卡尔积。
自连接
自连接可以看成内连接的一种，只是连接的表是自身而已。
一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。
子查询版本
SELECT name
FROM employee
WHERE department = (
SELECT department
FROM employee
WHERE name = "Jim");
自连接版本
SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
AND e2.name = "Jim";
自然连接
自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。
内连接和自然连接的区别: 内连接提供连接的列，而自然连接自动连接所有同名列。
SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;
外连接
外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。
检索所有顾客的订单信息，包括还没有订单信息的顾客。
组合查询
使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。
每个查询必须包含相同的列、表达式和聚集函数。
默认会去除相同行，如果需要保留相同行，使用 UNION ALL。
只能包含一个 ORDER BY 子句，并且必须位于语句的最后。
SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;
wechat
alipay