SQL 语句 - YHT's Note Repo

%% # 纲要 > 主干纲要、Hint/线索/路标 # Q&A #### 已明确 #### 待明确 > 当下仍存有的疑惑 **❓ 有什么问题？** # Buffer ## 闪念 > sudden idea 1. 从哪些方面去组织笔记？ 2. SQL 语法可能很细，笔记只**体现纲要 & 重点**，但要给出详细语法说明的参考信息，例如书籍的附录或者官方文档等。 ## 候选资料 > Read it later %% # 关于 SQL >❓什么是 SQL？ **SQL（Structured Query Language，结构化查询语言）** 是用于**管理和操作==关系型数据库==的标准化语言**。 # MySQL 大小写敏感性 | | SQL 命令（关键字） | 数据库名 & 表名 | 表别名 | 列名（字段名） | 变量名 | | --------- | ----------- | --------- | --- | ------- | --- | | Linux 下 | ❌ | ✔️ | ✔️ | ❌ | ✔️ | | Windows 下 | ❌ | ❌ | ❌ | ❌ | ✔️ | - ❌ 表示**大小写不敏感**；✔️表示**严格==区分大小写==** > [!NOTE] **Linux 下大小写敏感性** > > ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-7C094ED1251AB48A86111E5BCC6B36AA.png|468]] > > Linux 下，"**数据库名和表名**" 区分大小写的原因是 "Linux 的文件系统中对文件名本身是大小写敏感的"。 > # MySQL 系统信息相关命令 ```sql STATUS; -- 查看mysql当前相关信息（版本号, 当前数据库, 连接ID, 连接方式, 端口等等） SELECT version(); -- 查看mysql版本号 ``` # SQL 语句基本说明 - **子句**：SQL 语句由子句构成。一个子句通常**由一个关键字和所提供的数据**组成。 - **结束标识符**：sql 命令以 `;` 或 `\g` 或 `\G` 标识结束； - `DELIMITER <char>` 命令可指定 "**结束标识符**"，常用于定义 "存储程序" 的场景（执行语句以 `;` 结尾，因此需要更改为结束标识符） - **放弃命令**：sql 命令以`\c` 结尾时，表示 **==放弃本次操作==**。 - **字符串表示**：**字符串需要使用单引号或双引号**，数据库名、表名、列名不用，**除非包含特殊字符**。 - **注释格式**：三种 - 单行注释：使用 `#` 或 `--` 起始（`#` 是 MySQL 特有扩展） - 多行注释：`/* ... */` # 表格字段约束（Contraint）约束：对**列中存储的数据**进行**限制或者追加条件规则**的功能。 #### MySQL 支持的约束 - `DEFAULT <value>` ：指定默认值 - `NULL`：允许 NULL 值（**默认设置**） - `NOT NULL`：要求该字段**非空**，必须有值 - `UNIQUE` 或 `UNIQUE KEY`：声明**该字段的==有效值不可重复==**（允许值为 `NULL` 且重复）——**插入数据时 MySQL 会进行校验** - `CHECK`： - `PRIMARY KEY`：作为**主键**（一张表**最多只能有一个主键**，**主键值不能重复**）——**插入数据时 MySQL 会进行校验 - `CONSTRAINT [外键名称] FOREIGN KEY (列1, ...) REFERENCES 父表名(父列1, 父列2)`：声明**字段为==外键==**——**插入数据时 MySQL 会进行校验** - `AUTO_INCREMENT`：表示**若在插入数据时若未指定该字段值或显式设置为 `NULL` 或`0` 时，则==自增==取值** - `COMMENT <'strng'>`：**列的备注说明** - `ZEROFILE`：对于 **`UNSIGNED`整数类型列**，当**实际位数小于==数据类型指定的最大位数==时，当补上前缀 0**。 > [!NOTE] `DISTINCT` 作用于 `SELECT` 中的 "所有列"，而非单个列。 > [!note] 关于主键 > > - 主键和具有`UNIQUE` 属性的列会==自动建立索引== > - 主键天然具有 `NOT NULL` 属性 > [!NOTE] 关于 `AUTO_INCREMENT` > > 1. 每张表中**只允许有一个 `AUTO_INCREMENT`列**。 > 2. `AUTO_INCREMENT` 列**必须建立索引**。 > 3. `AUTO_INCREMENT` 列**不能再通过 `DEFAULT` 指定默认值**。 > 4. 一般拥有 `AUTO_INCREMENT` 属性的列都是作为主键的属性，来自动生成唯一标识一条记录的主键值。 > 5. 对 `AUTO_INCREMENT` 列通过 `INSERT` 插入**有效的指定值**（未使用过的）后，**后续的自动增量**将 **==基于这一值==** 自增。 > > > # 数据定义语句 ## 数据库管理 #### 增删查改数据库 ```sql # 查看所有数据库 SHOW DATABASES; # 创建数据库 CREATE DATABASES [IF NOT EXISTS] 数据库名; # 使用/切换当前访问的数据库 USE 数据库名; # 删除数据库 DROP DATABASE [IF EXISTS] 数据库名; ``` ## 表格管理 #### 创建表格 ```sql # 创建表格 CREATE TABLE [IF NOT EXITS] 表名 ( 列名1 数据类型 [列的属性], 列名2 数据类型 [列的属性], ... ) [table_option] [partition_option]; # 创建表格示例 CREATE TABLE IF NOT EXISTS first_table ( number INT PRIMARY KEY, # 学号作为主键 name VARCHAR(5), sex ENUM('男', '女'), id_number CHAR(18), department VARCHAR(30), major VARCHAR(30), enrollment_time DATE ) COMMENT '学生基本信息表'; # 表的备注说明 # 创建表格示例 CREATE TABLE IF NOT EXISTS student_score ( number INT, subject VARCHAR(30), score TINYINT, PRIMARY KEY (number, subject) # 设置两个字段值作为主键 CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number) # 声明外键: 该表中的number字段作为外键, 关联自`student_info`表中的number列。 ); ``` #### 删除表格 ```sql # 删除表格 DROP TABLE [IF EXISTS] 表1, 表2, ... 表n; # 清空表格 (实际过程是删除表格后再创建一张同名表, 而不是逐行删除表中数据) TRUNCATE TABLE; ``` #### 查看表格信息 ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-00731A59E441F52B1AB2C0BBC7D45563.png|554]] ```sql # 查看当前数据库中的所有表格 SHOW TABLES; # 查看指定数据库中的所有表格 SHOW TABLES FROM 数据库名; # 查看表结构: 各个字段及类型(下列语句等价) # 可为`表名`指示其所属数据库——`[数据库名.]表名` DESC 表名; DESCRIBE 表名; EXPLAIN 表名; SHOW COLUMNS FROM 表名; SHOW FIElDS FROM 表名; # 查看完整的表格创建语句 SHOW CREATE TABLE 表名; # 建议使用\G结尾, 更易于查看: `SHOW CREATE TABLE 表名\G` ``` #### 修改表格信息 > [!NOTE] `ALTER TABLE 表名` 之后可以跟多个对于该表的操作，以逗号 `,` 分隔，例如： > > ```sql > ALTER TABLE first_table DROP COLUMN third_column, DROP COLUMN fourth_column, DROP COLUMN fifth_column; > ``` > > **修改表名** & **转移表所属数据库**： ```sql # 修改表名, 也可转移表所属的数据库 (下列两语句等价) # 方式一: ALTER TABLE 旧表名 RENAME TO 新表名; # 方式二: RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2, ...; # 支持同时重命名多个表. # 转移表所属的数据库 ALTER TABLE 旧表名 RENAME TO 新数据库.新表名; ``` **更改表格所用的存储引擎**： ```sql # 更改已有表的存储引擎 ALTER TABLE 表名 ENGINE = InnoDB; ``` #### 字段修改 ###### **新增列** `ADD COLUMN` ```sql -- 新增列 ALTER TABLE 表名 ADD COLUMN 列名数据类型 [列属性]; -- 新增列(添加为第一列) ALTER TABLE 表名 ADD COLUMN 列名数据类型 [列属性] FIRST; -- 新增列(添加到指定列之后) ALTER TABLE 表名 ADD COLUMN 列名数据类型 [列属性] AFTER 指定列名; -- 示例: ALTER TABLE vendors ADD vend_phone CHAR(20); ``` ###### **删除列** (`DROP COLUMN`) ```sql -- 删除列 ALTER TABLE 表名 DROP COLUMN 列名; -- 示例: 删除列 ALTER TABLE Vendors DROP COLUMN vend_phone; ``` ###### **修改列信息** (`MODIFY` 或 `CHANGE`) ```sql # 修改字段信息 # 方式一: ALTER TABLE 表名 MODIFY 列名新数据类型 [新属性]; # 方式二: ALTER TABLE 表名 CHANGE 旧列名新列名新数据类型 [新属性]; ``` > [!caution] 修改字段类型时，要求修改后的数据类型必须兼容原类型现有数据！否则 MySQL 会报错。 ###### 增加外键 ```sql -- 向Vendors表中添加外键, 以`Vendors.order_num`作为外键, 键值引用`orders.order_num`. ALTER TABLE Vendors ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num); ``` ## 视图管理 #### 创建视图 - `CREATE VIEW 视图名[(列名1, 列名2, ...)] AS 查询语句` ```sql -- 创建视图: 为指定查询语句创建一个视图 -- 示例一: 为男生成绩创建一个视图 CREATE VIEW male_student_view AS SELECT s1.number, s1.name, s2.score, s2.subject FROM student_info s1 LEFT JOIN student_score s2 ON s1.number = s2.number WHERE s1.sex = '男'; -- 示例二: 可指定视图的自定义列名 CREATE VIEW student_info_view(no, n, m) AS SELECT number, name, major FROM student_info; ``` #### 删除视图 ```sql -- 删除视图 DROP VIEW 视图名； ``` #### 查看视图 ```sql -- 查看所有表&视图（视图作为虚拟表, 会和真实表一并显示） SHOW TABLES; -- 查看视图的定义 SHOW CREATE VIEW 视图名; ``` #### 修改已有视图（修改其对应的查询语句） - `ALTER VIEW` 或 `CREATE OR REPLACE VIEW ... AS 查询语句` ```sql -- 修改既有视图对应的查询语句 ALTER VIEW view_name AS SELECT ... FROM ... ... -- 修改既有视图对应的查询语句 CREATE OR REPLACE VIEW view_name AS SELECT ... FROM ... ... ``` #### 更新视图（操作数据） **满足特定条件的视图**是可更新的，即**可执行 `INSERT`, `DELETE`, `UPDATE` 语句**，实质上会转换为对 "**底层真实表**" 中数据的**增、删、改**操作。 > [!caution] 可更新的视图 > > 仅基于单表、无复杂聚合的视图是可更新的。生成视图时使用了下列语句的视图均不可更新： > > - 聚集函数（`AVG`、`MAX`、`SUM`、`COUNT` 等） > - `DISTINCT` > - `GROUP BY` 与 `HAVING` > - `UNION` 或 `UNION ALL` > - 某些子查询 > - 某些连接查询 > - .....等等 > # 数据操纵语句 #### 插入数据 > [!NOTE] 对于指定了默认值的列，可在 `VALUES` 子句中的括号内使用 `DEFAULT` 关键字，代表该列使用默认值。 > - `INSERT INTO ... VALUES ...`：**存在重复值时报错** ```sql -- 向表中插入数据 (指定要插入数据的列并给出对应值, 未显式指定的列取空值NULL) INSERT INTO 表名(列1, 列2, ...) VALUES (列1值, 列2值, ...), (...), ...; # 向表中插入数据(将查询结果作为值插入) INSERT INTO 表名(列1, 列2, ...) SELECT 列11, 列22, ... -- 检索出的列数量要对齐被插入表中指定的列 FROM ... ... ``` - `INSERT IGNORE INTO ... VALUES ...`：**存在重复值时==忽略==，跳过** ```sql -- 向表中插入数据, `IGNORE`表示示若重复数据值已存在则忽略 INSERT IGNORE INTO 表名(列1, 列2, ...) VALUES (列1值, 列2值)... ``` - `INSERT INTO ... VALUES ... ON DUPLICATE KEY UPDATE ...`：**==更新==重复值**（主键或 `UNIQUE` 列重复时，更新其他指定列的值） ```sql -- 向表中插入数据, 若对于主键或unique列存在中复制, 则更新既有记录中的某些指定列的值 -- 示例一: 对于要插入的数据`(1, '哇哈哈')`，若表中已存在`first_column`的列值为`1`的记录（因为`first_column`列具有`UNIQUE`约束），则将该记录的`second_column`列更新为`'雪碧'`. INSERT INTO first_table (first_column, second_column) VALUES(1, '娃哈哈') ON DUPLICATE KEY UPDATE second_column = '雪碧'. -- 插入时, 若主键重复, 则更新已有记录的`second_column` 为新值. INSERT INTO first_table (first_column, second_column) VALUES(2, '红牛'), (3, '橙汁') ON DUPILICATE KEY UPDATE second_column = VALUES(second_colum); ``` #### 删除数据 - `DELETE FROM 表名 [WHERE 条件]`： **删除符合指定条件的数据**，若**未指定 `WHERE` 子句则表明删除所有数据** ```sql -- 删除所有数据 DELETE FROM first_table -- 删除符合指定条件的数据 DELETE FROM firt_table WHERE first_column > 4; -- 删除指定列值最大的那条记录 DELETE FROM first_table ORDER BY first_column DESC -- 对`first_column`列降序排列, 取最大的一项值. LIMIT 1; ``` #### 更新数据 - `UPDATE [IGNORE] 表名 SET 列1=值1, 列2=值2, ... [WHERE 布尔表达式]` - `IGNORE` : 在更新多行数据时，**忽略出错的行**，而继续执行剩余更新 ```sql -- 更新数据: 将表中为`first_column`为NULL的记录进行更新 UPDATE first_table SET first_column = 5, second_column = '东鹏特饮' WHERE first_column IS NULL; -- 更新数据: 将表中为`first_column`值最大的记录进行更新 UPDATE first_table SET second_column = '椰汁' ORDER BY first_column DESC LIMIT 1; ``` # 数据查询语句 ### 语句关键字概览 - `SELECT`：指定**检索/查询的列** - `DISTINCT`：**去重** - `AS 别名`：**列别名** - `FROM`：**指定表格** - `WHERE`：指定**过滤条件** - **比较条件**：`=`、`<>` 或 `!=`、`<` , `>` , `<=` , `>=` - **范围区间**： - `BETWEEN a AND b`：目标值位于区间 `[a, b]` - `NOT BETWEEN a AND b`：目标值位于区间 `(, a)` 或 `(b, )` - **匹配列表项**： - `IN (v1, v2, ...)`：若匹配指定列表中**任一项**则成功 - `NOT INT(v1, v2, ...)`：要求**不匹配指定列表中所有项** - **匹配 `NULL` 值**： - `IS NULL`：为空 - `IS NOT NULL`：非空 - **逻辑操作符**：`AND` 、`OR`，用于 "**组合多个过滤条件**" - **通配符**： - 语句： - `a LIKE b`： - `a NOT LIKE b`： - 通配符： - `%` ：代表任意**任意字符出现任意次数**（可为空） - `_` ：代表任意**一个字符** - **正则表达式匹配**： - `REGEXP <正则表达式>` - `GROUP BY <列名>`：**分组** - `[HAVING <条件>]`：**作用于分组结果的过滤条件** - `ORDER BY <列名> ASC|DESC`：**排序**（默认`ASC`，从上到下升序排列） - `LIMIT <[起始行,] 限制条数>` ：**限制查询结果条数** ##### 执行顺序 > [!NOTE] 执行顺序——先过滤（`WHERE`），再分组（`GROUP BY`），最后再排序（`ORDER BY`） ```sql SELECT [DISTINCT] 列名1 [AS 别名], ... [FROM 表名] [[INNER]|[LEFT|RIGHT [OUTER]]|[CROSS] JOIN 表名] [ON 连接条件] [WHERE 过滤条件] [GROUP BY 列名] [HAVING 分组过滤条件] [ORDER BY 列名] [LIMIT <起始行, 限制条数>] ; ``` ##### 示例 ```sql SELECT * FROM student_score WHERE (score > 95 OR score < 55) AND subject = '论萨达姆的战争准备'; ``` ## 过滤 #### 空值检查 > [!caution] 值为`NULL` 的记录不参与匹配 > > `NULL` 表示 "空值"，**==不参与匹配比较==**，因此在 `WHERE` 子句中： > - `<>` 或 `!=` 筛选出的结果集中**不包含值为 `NULL` 的行**。 > - **`LIKE` 与通配符** 也不能匹配到 `NULL`。 > > 如果要包含值为 `NULL` 的行，必须**在过滤条件中显式指明 `or col IS NULL`**，例如： `WHERE col != XXX or col IS NULL` 。 > #### 通配符匹配 **`LIKE` 关键字**作为谓词，**搭配通配符 `%` 或者 `_`** 进行模糊匹配。 #### 正则表达式匹配 > 详细说明参见 [^1]（P55） `REGEXP [BINARY] <正则表达式>` 进行**正则表达式匹配**。 - `[BINARY]` 指示 "**正则表达式匹配时==严格区分==大小写**"（默认不区分） ```sql SELECT prod_name FROM products WHERE prod_name REGEXP '.000'; -- 正则表达式匹配, 例如1000和2000都能匹配上. ``` > [!NOTE] 测试正则表达式 > > ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-16E3D5BCE4B90598609FE67B32A795B5.png|592]] > > ## 排序 SQL 中，`ORDER BY` 子句用于将查询结果**按指定列的顺序排列**（默认==`ASC`==，从上到下**升序**）。 > [!NOTE] `ASC` 与 `DESC` 只作用于 "**其前面的列**" > > 多列排序时，如果**要在每个列上均降序排列**，需**对每个列指定 `DESC` 关键字**。 > [!caution] NULL 值不能被应用比较运算符，因此不参与排序，其总是排列在排序结果集的开头或末尾，汇总显示。 ```sql SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...; -- 支持按多个列排序 ``` 示例：`ORDER BY` 搭配 `LIMIT`，可找出一个列中的最大 or 最小值 ```sql -- 找出分数最高的学生 SELECT number, score FROM student_score ORDER BY score DESC LIMIT 1; ``` # 子查询子查询是嵌套在另一条 SQL 查询中的查询，其**可位于 `SELECT`、`FROM`、`WHERE`、`HAVING` 子句**中。根据子查询的结果，可分为： | | 说明 | | ----------------------------------- | ----------------------------------------------------------- | | **标量子查询**（Scalar Subquery） | 子查询返回**单个值**，常用于 `SELECT` 或 `WHERE` 子句中。 | | **列子查询**（Column Subquery） | 子查询返回**一列多行**（**一组值**），常用于`WHERE` 子句搭配 `IN`、`ANY`、`ALL` 操作符 | | **行子查询**（Row Subquery） | 子查询返回**一行多列**，常**用于与其他行的比较，必须与主查询中的多列做匹配**。 | | **表子查询**（Table Subquery） | 子查询返回**一张完整的表**（多行多列），通常**用于 `FROM` 语句中**，作为临时表 | | **EXISTS 子查询** | 子查询用于**检测某个条件是否成立**，子查询**结果集非空**时，`EXISTS`表达式为 `TRUE` | | **NOT EXISTS 子查询** | 子查询用于**检测某个条件是否不成立**，子查询**结果集为空**时，`EXISTS`表达式为 `TRUE` | | **不相干子查询**（Non-Correlated Subquery） | 子查询与外层查询**没有关联关系**，**子查询独立运行并产出结果**，外层查询再利用子查询的结果 | | **相关子查询**（Correlated Subquery） | 子查询中需要引用**主查询中的值**，**主查询每处理一行记录，子查询都会被执行一次** | ### 标量子查询 ```sql -- 标量子查询: 子查询返回一个值 -- 示例一: 查找姓名为'YYJ'的学生成绩 -- 内层子查询: 从`student_info`表中根据姓名找到学号, 返回学号. -- 外层查询: 根据学号, 在`student_score`表中找到其所有成绩分数. SELECT * FROM student_score WHERE number = ( SELECT number FROM student_info WHERE name = 'YYJ' ); -- 示例二: 查找student_score表中, '母猪的产后护理'这门课的课程成绩大于平均分的记录 -- 内层子查询: 返回这门课的平均成绩 -- 外层查询: 找出score大于平均成绩的记录 SELECT * FROM student_score WHERE subject = '母猪的产后护理' and score > ( SELECT AVG(SCORE) as avg_score FROM student_score WHERE subject = '母猪的产后护理' ); ``` > [!NOTE] 标量子查询可位于 `SELECT` 子句中，充当一列值示例： ```sql -- 相关子查询: 子查询返回一个统计值(顾客的订单总数), 作为SELECT输出结果集的一列. SELECT cust_name, cust_state, ( SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id ) AS order_cnt FROM customers ORDER BY cust_name; ``` ### 列子查询 ```sql -- 列子查询: 查询结果返回一列多行的一组值. -- 示例一: 查询专业为'计算机科学与工程'的所有学生的成绩 -- 内层子查询: 从`student_info`表中找出所有指定专业学生的学号 -- 外层查询: 根据学生学号, 在`student_score`表中找到其所有成绩分数. SELECT * FROM student_score WHERE number in ( SELECT number FROM student_info WHERE major = '计算机科学与工程' ); -- 示例二: 查询工资大于"指定部门最低工资"的员工姓名. -- 内层子查询: 返回指定部门的员工工资列表 -- 外层子查询: 返回工资大于 "子查询列表中任一值" 的员工姓名 SELECT name FROM employees WHERE salary > ANY ( SELECT DISTINCT salary FROM employees WHERE department_id = 10; ); -- 示例三: 多层嵌套子查询 SELECT cust_name, cust_contact FROM customers WHERE cust_id IN ( SELECT cust_id FROM orders WHERE order_num IN ( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' ) ); ``` ### 行子查询 ```sql -- 行子查询: 查询结果返回一行多列 -- 内层子查询: 从`employees` 表中找出指定部门中的最高薪资 -- 外层查询: 根据返回的薪资和部门ID, 定位到这个人, 返回其姓名和薪资 SELECT name, salary FROM employees WHERE (salary, department_id) = ( SELECT MAX(salary), department_id FROM employees WHERE department_id = 10 ); ``` ### 表子查询 ```sql -- 表子查询: 子查询返回一张完整的表(多行多列) SELECT * FROM ( SELECT name, salary FROM employees WHERE department_id = 10 ) AS temp_table; ``` ### EXISTS 子查询、NOT EXISTS 子查询 ```sql -- EXISTS 子查询: 子查询用于检测某个条件是否成立. -- 内层子查询: 为外层查询中的每个部门id, 检查其是否同时存在于`employees`表中. -- 外层查询: 返回同时存在于`employees` 与 `department` 两表中的部门的名字. SELECT name FROM department d WHERE EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id -- 这里是"相关子查询", 子查询语句中引用了主查询中的值, 主查询每检查一行记录, 子查询都会执行一次. ); ``` ### 相关子查询 ```sql -- 相关子查询: 子查询中需要引用"主查询中的值", 主查询每处理一行记录，子查询都会被执行一次. -- 示例一: 从`student_info`表中, 找出在`student_score`表中存在成绩记录的学生的信息. SELECT number, name, id_number, major FROM student_info si WHERE EXISTS ( SELECT * FROM student_score ss WHERE ss.number = si.number -- 引用主查询中的学生id, 在student_score中查询 ); ``` ### 不相关子查询 ```sql -- 不相关子查询: 子查询独立执行 SELECT * FROM student_score WHERE number = ( SELECT number FROM student_info WHERE name = '杜琦玉' ); ``` # 连接（联结）查询 SQL 中，连接（JOIN）用于**将多个表中的数据根据某种条件组合/拼接在一起**。 SQL 支持的连接包括以下类型： - **==内连接==**（Inner Join）：只返回两个表中**满足连接条件的==交集==**，即**同时存在于两张表中的数据**。如果某行在一张表中没有匹配，则不会出现在结果中。 - **==外连接==**（Outer Join）：分为三种： - **左外连接**（Left Join）：**以左侧表为"==驱动表=="，返回==左表==中的所有记录**，对于在右表中无匹配项的，则右表的列将填充为 `NULL` 值。 - **右外连接**（Right Join）：**以右侧表为"==驱动表=="，返回==右表==中的所有记录**，对于在左表中无匹配项的，则左表的列将填充为 `NULL` 值。 - **全外连接**（Full Join）：**返回两个表中==所有的记录==**，包括在连接条件中**没有匹配**的记录。对于没有匹配的记录，**未匹配的表中的字段会填充 `NULL` 值**。 - **==交叉连接==**（Cross Join）：返回**两个表的==笛卡尔积==**（全连接），即**每一条左表的记录都与右表的所有记录组合**，用于**生成所有组合**的情况。 - **自连接**（Self Join）：**将==同一张表==进行连接**，通常用于查找表中数据之间的关系 - **自然连接**（Natural Join）：自动**根据两个表中==具有相同名字的列==进行连接**，而不需要显式指定连接条件（进行连接的列）。 > [!NOTE] MySQL 中，`JOIN` 关键字等价于 `INNER JOIN` ### 内连接 ```sql -- 内连接使用示例: 查询"已被分配了所属部门"的员工姓名及其所在部门名 -- 对于未分配部门的员工(e.department_id为NULL), 或者部门id无效的员工(e.department_id不在d表中), 将被过滤. SELECT e.name, d.department_name FROM employees e [INNER] JOIN departments d ON e.department_id = d.department_id; -- 内连接的另一种等价写法, `FROM`中给出多个表, 使用`WHERE`子句进行连接过滤 SELECT e.name, d.department_name FROM employees e, department_name d WHERE e.department_id = d.department_id ``` > [!NOTE] 内连接可省略 `JOIN ... ON ...` 关键字，而改用 `WHERE` 子句实现。示例二 ```sql -- 查询每个顾客的订单总数: -- 将顾客信息表与订单表, 按顾客ID拼接, 再按顾客id分组, 对分组结果应用聚合函数 SELECT c.cust_name, c_cust_id, COUNT(o.order_num) AS num_ord FROM customers AS c INNER JOIN orders AS o ON c.cust_id = o.cust_id GROUP BY c.cust_id ``` ### 外连接左外连接： ```sql -- 左外连接示例: 返回 `employees` 表中的所有员工，若某个员工没有所属的部门，则员工的 `department_name` 将显示为 `NULL` SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id ``` 右外连接： ```sql -- 右外连接示例: 返回 `departments` 表中的所有部门，若某个部门没有员工, 则部门的 `employees.name` 将显示为 `NULL`。 SELECT e.name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id ``` 全外连接： ```sql -- 返回所有员工和所有部门，若某个员工没有部门, 或某个部门没有员工, 则对应字段将显示为 `NULL` SELECT e.name d.deparment_name FROM employees e FULL [OUTER] JOIN ``` ### 交叉连接 > [!NOTE] **内连接 `[INNER] JOIN` 在未通过 `ON` 指定连接条件时，即等价于 `CROSS JOIN`** ```sql -- 交叉连接的几种等价写法 SELECT * FROM t1 CROSS JOIN t2; SELECT * FROM t1 [INNER] JOIN t2; SELECT * FROM t1, t2; ``` ### 自连接示例一： ```sql -- 自连接示例一: 返回每个员工的直接上级. SELECT e1.name as Employee, e2.name as Manager FROM employee e1 JOIN employee e2 ON e1.manager_id = e2.employee_id; -- 自连接示例二: 找出`DTNTR`的商品供应商所生产的所有产品 SELECT p1.prod_id, p1.prod_name FROM products AS p1 INNER JOIN products AS p2 ON p1.vend_id = p2.vend_id WHERE p2.prod_id = 'DTNTR'; -- 使用where语句的等价写法: SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'; ``` ### 连接与子查询的等价使用有的查询需求既可以使用连接查询解决，也可以使用子查询解决： ```sql -- 查找'计算机科学与工程'专业学生的考试成绩 -- 使用子查询 (注: 返回结果中, 不包含"缺考"的同学, 即`student_score`中无成绩的同学) SELECT * FROM student_score WHERE number IN ( SELECT number FROM student_info WHERE major = '计算机科学与工程' ) -- 使用连接: SELECT ss.*, FROM student_score ss RIGHT JOIN student_info si # 使用右外连接, 使结果中包含缺考的同学, 其分数将显示为NULL. ON ss.number = si.number WHERE si.major = '计算机科学与工程'; ``` # 分组查询分组查询（`GROUP BY`）用于将查询结果**按一个或多个列的值**进行分组。 - **`SELECT` 语句中的每个列，除聚合函数语句外，都必须==同时在 `GROUP BY` 子句中给出==**。 - 如果分组列具有 `NULL` 值，则 **所有`NULL` 值将作为一个==独立分组==** 返回。 ### 聚合函数聚合函数与分组查询结合使用时，将**分别==应用于每个分组内==** 进行统计。对于 "**多列分组**" 的情况，聚合函数作用于 "**最后一列**" 的分组结果。示例： ```sql -- 统计每个部门的平均薪资 SELECT departmend_id, AVG(salary) FROM employees GROUP BY department_id; -- 统计各个部门中, 每个岗位的薪酬总支出 SELECT department_id, job_title, SUM(salary) FROM employees GROUP BY department_id, job_title; ``` ### 过滤分组结果——HAVING 子句 **`HAVING`** 是用于**对==分组结果==进行过滤的子句**，类似于 `WHERE`，但 `HAVING` **作用于分组之后**。示例： ```sql -- 找出至少具有2笔订单的所有顾客 SELECT cust_id, COUNT(*) AS order_cnt FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2; ``` ###### WHERE 与 HAVING > [!NOTE] `WHERE` 与 `HAVING` 的区别 > > - `WHERE` 子句是对 "**==原始记录条目==**" 进行过滤，作用于分组前； > - `HAVING` 子句是对 "**==分组结果==**" 进行过滤，作用于分组后的记录 （用法：`GROUP BY ... HAVING ...` ）示例一： ```sql -- 过滤掉低于 60 分的记录后, 再统计平均分 SELECT subject, AVG(score) FROM student_score WHERE score > 60; -- GROUP BY subject; -- 查询平均分大于 70 分的课程 SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING AVG(score) > 70; -- 过滤掉平均分<=70 的分组记录 ``` 示例二： ```sql -- 列出具有2个及以上的, 价格在10元及以上商品的供应商 SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 -- 筛选出价格在10元及以上的商品 GROUP BY vend_id -- 按供应商分组 HAVING COUNT(*) >= 2; -- 筛选出, 具有2个及以上的10元以上商品的供应商. ``` # 组合查询 - `UNION [ALL]`：将两个或多个 `SELECT` 查询的结果集进行组合，并返回所有查询的结果——**两个结果集的==并集==**。 - `UNION` 会**去除重复记录**，`UNION ALL` 会**包含所有重复记录**。 - `INTERSECT`：返回两个 `SELECT` 语句中都存在的共同记录，即**两个结果集的==交集==**。 - `INTERSECT` **仅在某些 SQL 实现中（如 PostgreSQL、Oracle）被支持**，但 **MySQL 不直接支持** - `EXCEPT` 或 `MINUS`：**返回第一个 `SELECT` 查询中有但第二个 `SELECT` 查询中没有的记录**，即**两个结果集的==差集==**。 - `EXCEPT` 通常在 PostgreSQL 中使用，`MINUS` 常见于 Oracle。 > [!NOTE] "组合查询" 使用注意 > > - 组合查询要求**每个 `SELECT` 语句必须==具有相同的列、表达式或聚集函数==（顺序不一定相同），且==各个列的数据类型必须兼容==**。 > - **最后的结果集展示的 "列名" 是==第一个查询中给定的列名==**。 > - 组合查询中 **`ORDER BY` 子句只能放置在==最后一条 `SELECT` 语句之后==**，表示 **==对整个结果集排序==**（不支持仅对部分结果排序） # 表达式表达式可以用在两个地方： - 放在 **`SELECT` 之后的==查询列表==中** - 放在 **`WHERE` 之后作为==搜索条件==** ### CASE 表达式 ![[_attachment/02-开发笔记/08-数据库/SQL 练习.assets/IMG-SQL 练习-C54B6C25DEECD86908D24520AE433D7F.png|603]] ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-1973EBA6CBAE1F82403D27531FEA7CB7.png|516]] # MySQL 函数 ## 聚合函数 **聚合函数/聚集函数**（Aggregation function）用于获取 "**统计**" 结果。 MySQL 提供了一系列聚合函数[^1]（P76），最主要的 5 个： ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-8294530DC0F948CE179F46CB13D4E07F.png|602]] > [!NOTE] 聚合函数对 `NULL` 值的处理 > > - `AVG`, `MAX` , `MIN`, `SUM` 都会**忽略值为 `NULL` 行**； > - `COUNT(*)`：统计 **"行"数目**，**==计入 `NULL`==** > - `COUNT(列名)`：统计指**定列中具有 "有效值" 的行数目**，**忽略该列值为 `NULL` 的行** > > > > [!NOTE] 聚合函数中可应用 `DISTINCT` 关键字，表示**统计时不计入重复行** > > `DISTINCT` 实际上只对 `AVG` 和 `COUNT` 有意义。 > > ```mysql > -- 统计平均值时, 去掉`DISTINCT`列 > SELECT AVG(DISTINCT prod_price) AS avg_price > FROM products > WHERE vend_id = 1003; > ``` > > ## 数值处理函数 ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-88E24CE8CCA98559045A7775ED9D26EB.png|647]] ## 文本处理函数 ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-793F234DD88069E7F93948F87BC17F73.png|736]] ## 日期和时间处理函数 ### 函数说明 ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-09F7A5841FD434D1F444905E667ECA59.png|618]] 使用示例： ```sql -- 查询指定日期的订单记录 SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01'; -- 查询指定月份内的所有订单(方式一) SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'; -- 查询指定月份内的所有订单(方式二) SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9; ``` ### 支持的时间单位 ```sql # 令指定时间`2019-08-16 17:10:43` 增加2分钟 SELECT DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 MINUTE); ``` ### 日期时间格式符 ![[_attachment/02-开发笔记/08-数据库/SQL 语句.assets/IMG-SQL 语句-862255DC03B83998D5FFECA3A271EE80.png|417]] ```sql -- 显示: 'Sep 19 2024 09:07 PM' SELECT DATE_FORMAT(NOW(), '%b %d %Y %h:%i %p'); ``` ### 系统函数 **系统函数**：返回 DBMS 正使用的特殊信息（例如返回用户登录信息，检查版本细节） ```sql SELECT version(); -- `version()`即为系统函数 ``` # MySQL 变量 MySQL 中可以定义变量，支持多种变量类型，可用于不同上下文： - **用户定义变量**（User-Defined Variables）：使用 `@` 作为前缀，变量值**可用于 SQL 语句中**。 - **局部变量**（Local Variables）：在**存储过程或函数**中定义和使用，在 `BEGIN` 和 `END` 块中**使用 `DECLARE` 关键字定义** - **系统变量**（System Variables）：**用于==配置 MySQL 服务器的参数和状态信息==** - **全局系统变量**：影响整个服务器，所有会话都会受到影响。 - **会话系统变量**：仅影响当前会话 #### 用户定义变量使用 `@` 作为前缀，存储在**会话级别**（同一个数据库连接期间） ```sql -- 定义用户变量 SET @var_name = value; SET @var_name = @other_var; SET @var_name = (SELECT ... ); -- 将查询语句的结果(单个值)赋给变量 (SELECT ...) INTO @var_name; -- 将查询语句的结果(单个值)赋给变量 (SELECT col1, col2 ...) INTO @var1, @var2; -- `INTO`支持将单行多列的查询结果赋给多个变量 -- 查看变量值 SELECT @var_name; ``` > [!caution] MySQL 中没有直接查看 "所有用户定义变量" 的命令，只能通过 `SELECT` 指明需要查看的变量值. #### 局部变量「**局部变量**」在**存储过程或存储函数**中使用，在 `BEGIN` 和 `END` 块中**使用 ==`DECLARE` 关键字==定义**，只在**存储过程、函数或触发器**的执行期间有效。 ```sql -- "局部变量" 仅在存储过程或存储函数中使用 CREATE PROCEDURE example_procedure() BEGIN DECLARE counter INT DEFAULT 0; -- 声明局部变量 `counter` SET counter = counter + 1; SELECT counter; END; ``` #### 系统变量「**系统变量**」**用于==配置 MySQL 服务器的参数和状态信息==**。包括两类： - **全局系统变量**：影响整个服务器，所有会话都会受到影响。 - **会话系统变量**：仅影响当前会话 ```sql -- 查看所有系统变量 SHOW VARIABLES; -- 查看指定系统变量(模糊匹配) SHOW VARIABLES LIKE ...; -- 设置会话级系统变量 SET SESSION var_name = value; -- 示例: SET SESSION sql_mode = 'STRICT_TRANS_TABLES'; -- 设置全局系统变量 SET GLOBAL var_name = value; -- 示例: SET GLOBAL max_connections = 1000; ``` # FAQ # 参考资料 - [MySQL :: MySQL 5.7 Reference Manual :: 13 SQL Statements](https://dev.mysql.com/doc/refman/5.7/en/sql-statements.html) # Footnotes [^1]: 《**MySQL 必知必会**》