GitHub Viewer - FazBrowse

--- title: SQL语法基础知识总结 category: 数据库 tag: - 数据库基础 - SQL --- > 本文整理完善自下面这两份资料： > > - [SQL 语法速成手册](https://juejin.cn/post/6844903790571700231) > - [MySQL 超全教程](https://www.begtut.com/mysql/mysql-tutorial.html) ## 基本概念 ### 数据库术语 - `数据库（database）` - 保存有组织的数据的容器（通常是一个文件或一组文件）。 - `数据表（table）` - 某种特定类型数据的结构化清单。 - `模式（schema）` - 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。数据库和表都有模式。 - `列（column）` - 表中的一个字段。所有表都是由一个或多个列组成的。 - `行（row）` - 表中的一个记录。 - `主键（primary key）` - 一列（或一组列），其值能够唯一标识表中每一行。 ### SQL 语法 SQL（Structured Query Language)，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。 #### SQL 语法结构 ![](https://img-blog.csdnimg.cn/img_convert/812dc1e042d44b6a2d4331d31a25ddc6.png) SQL 语法结构包括： - **`子句`** - 是语句和查询的组成成分。（在某些情况下，这些都是可选的。） - **`表达式`** - 可以产生任何标量值，或由列和行的数据库表 - **`谓词`** - 给需要评估的 SQL 三值逻辑（3VL）（true/false/unknown）或布尔真值指定条件，并限制语句和查询的效果，或改变程序流程。 - **`查询`** - 基于特定条件检索数据。这是 SQL 的一个重要组成部分。 - **`语句`** - 可以持久地影响纲要和数据，也可以控制数据库事务、程序流程、连接、会话或诊断。 #### SQL 语法要点 - **SQL 语句不区分大小写**，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置。例如：`SELECT` 与 `select` 、`Select` 是相同的。 - **多条 SQL 语句必须以分号（`;`）分隔**。 - 处理 SQL 语句时，**所有空格都被忽略**。 SQL 语句可以写成一行，也可以分写为多行。 ```sql -- 一行 SQL 语句 UPDATE user SET username='robot', password='robot' WHERE username = 'root'; -- 多行 SQL 语句 UPDATE user SET username='robot', password='robot' WHERE username = 'root'; ``` SQL 支持三种注释： ```sql ## 注释1 -- 注释2 /* 注释3 */ ``` ### SQL 分类 #### 数据定义语言（DDL）数据定义语言（Data Definition Language，DDL）是 SQL 语言集中负责数据结构定义与数据库对象定义的语言。 DDL 的主要功能是**定义数据库对象**。 DDL 的核心指令是 `CREATE`、`ALTER`、`DROP`。 #### 数据操纵语言（DML）数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句。 DML 的主要功能是 **访问数据**，因此其语法都是以**读写数据库**为主。 DML 的核心指令是 `INSERT`、`UPDATE`、`DELETE`、`SELECT`。这四个指令合称 CRUD(Create, Read, Update, Delete)，即增删改查。 #### 事务控制语言（TCL）事务控制语言 (Transaction Control Language, TCL) 用于**管理数据库中的事务**。这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。 TCL 的核心指令是 `COMMIT`、`ROLLBACK`。 #### 数据控制语言（DCL）数据控制语言 (Data Control Language, DCL) 是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。 DCL 的核心指令是 `GRANT`、`REVOKE`。 DCL 以**控制用户的访问权限**为主，因此其指令作法并不复杂，可利用 DCL 控制的权限有：`CONNECT`、`SELECT`、`INSERT`、`UPDATE`、`DELETE`、`EXECUTE`、`USAGE`、`REFERENCES`。根据不同的 DBMS 以及不同的安全性实体，其支持的权限控制也有所不同。 **我们先来介绍 DML 语句用法。 DML 的主要功能是读写数据库实现增删改查。** ## 增删改查增删改查，又称为 CRUD，数据库基本操作中的基本操作。 ### 插入数据 `INSERT INTO` 语句用于向表中插入新记录。 **插入完整的行** ```sql # 插入一行 INSERT INTO user VALUES (10, 'root', 'root', 'xxxx@163.com'); # 插入多行 INSERT INTO user VALUES (10, 'root', 'root', 'xxxx@163.com'), (12, 'user1', 'user1', 'xxxx@163.com'), (18, 'user2', 'user2', 'xxxx@163.com'); ``` **插入行的一部分** ```sql INSERT INTO user(username, password, email) VALUES ('admin', 'admin', 'xxxx@163.com'); ``` **插入查询出来的数据** ```sql INSERT INTO user(username) SELECT name FROM account; ``` ### 更新数据 `UPDATE` 语句用于更新表中的记录。 ```sql UPDATE user SET username='robot', password='robot' WHERE username = 'root'; ``` ### 删除数据 - `DELETE` 语句用于删除表中的记录。 - `TRUNCATE TABLE` 可以清空表，也就是删除所有行。 **删除表中的指定数据** ```sql DELETE FROM user WHERE username = 'robot'; ``` **清空表中的数据** ```sql TRUNCATE TABLE user; ``` ### 查询数据 `SELECT` 语句用于从数据库中查询数据。 `DISTINCT` 用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同。 `LIMIT` 限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。 - `ASC` ：升序（默认） - `DESC` ：降序 **查询单列** ```sql SELECT prod_name FROM products; ``` **查询多列** ```sql SELECT prod_id, prod_name, prod_price FROM products; ``` **查询所有列** ```css ELECT * FROM products; ``` **查询不同的值** ```sql SELECT DISTINCT vend_id FROM products; ``` **限制查询结果** ```sql -- 返回前 5 行 SELECT * FROM mytable LIMIT 5; SELECT * FROM mytable LIMIT 0, 5; -- 返回第 3 ~ 5 行 SELECT * FROM mytable LIMIT 2, 3; ``` ## 排序 `order by` 用于对结果集按照一个列或者多个列进行排序。默认按照升序对记录进行排序，如果需要按照降序对记录进行排序，可以使用 `desc` 关键字。 `order by` 对多列排序的时候，先排序的列放前面，后排序的列放后面。并且，不同的列可以有不同的排序规则。 ```sql SELECT * FROM products ORDER BY prod_price DESC, prod_name ASC; ``` ## 分组 **`group by`** ： - `group by` 子句将记录分组到汇总行中。 - `group by` 为每个组返回一个记录。 - `group by` 通常还涉及聚合`count`，`max`，`sum`，`avg` 等。 - `group by` 可以按一列或多列进行分组。 - `group by` 按分组字段进行排序后，`order by` 可以以汇总字段来进行排序。 **分组** ```sql SELECT cust_name, COUNT(cust_address) AS addr_num FROM Customers GROUP BY cust_name; ``` **分组后排序** ```sql SELECT cust_name, COUNT(cust_address) AS addr_num FROM Customers GROUP BY cust_name ORDER BY cust_name DESC; ``` **`having`**： - `having` 用于对汇总的 `group by` 结果进行过滤。 - `having` 一般都是和 `group by` 连用。 - `where` 和 `having` 可以在相同的查询中。 **使用 WHERE 和 HAVING 过滤数据** ```sql SELECT cust_name, COUNT(*) AS num FROM Customers WHERE cust_email IS NOT NULL GROUP BY cust_name HAVING COUNT(*) >= 1; ``` **`having` vs `where`** ： - `where`：过滤过滤指定的行，后面不能加聚合函数（分组函数）。`where` 在`group by` 前。 - `having`：过滤分组，一般都是和 `group by` 连用，不能单独使用。`having` 在 `group by` 之后。 ## 子查询子查询是嵌套在较大查询中的 SQL 查询，也称内部查询或内部选择，包含子查询的语句也称为外部查询或外部选择。简单来说，子查询就是指将一个 `select` 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。子查询可以嵌入 `SELECT`、`INSERT`、`UPDATE` 和 `DELETE` 语句中，也可以和 `=`、``、`IN`、`BETWEEN`、`EXISTS` 等运算符一起使用。子查询常用在 `WHERE` 子句和 `FROM` 子句后边： - 当用于 `WHERE` 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 `WHERE` 子句查询条件的值。 - 当用于 `FROM` 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 `FROM` 后面是表的规则。这种做法能够实现多表联合查询。 > 注意：MYSQL 数据库从 4.1 版本才开始支持子查询，早期版本是不支持的。用于 `WHERE` 子句的子查询的基本语法如下： ```sql select column_name [, column_name ] from table1 [, table2 ] where column_name operator (select column_name [, column_name ] from table1 [, table2 ] [where]) ``` - 子查询需要放在括号`( )`内。 - `operator` 表示用于 where 子句的运算符。用于 `FROM` 子句的子查询的基本语法如下： ```sql select column_name [, column_name ] from (select column_name [, column_name ] from table1 [, table2 ] [where]) as temp_table_name where condition ``` 用于 `FROM` 的子查询返回的结果相当于一张临时表，所以需要使用 AS 关键字为该临时表起一个名字。 **子查询的子查询** ```sql 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 = 'RGAN01')); ``` 内部查询首先在其父查询之前执行，以便可以将内部查询的结果传递给外部查询。执行过程可以参考下图： ![](https://img-blog.csdnimg.cn/img_convert/898b370018bb52505e78ad6963d28aa1.png) ### WHERE - `WHERE` 子句用于过滤记录，即缩小访问数据的范围。 - `WHERE` 后跟一个返回 `true` 或 `false` 的条件。 - `WHERE` 可以与 `SELECT`，`UPDATE` 和 `DELETE` 一起使用。 - 可以在 `WHERE` 子句中使用的操作符。 | 运算符 | 描述 | | ------- | ------------------------------------------------------ | | = | 等于 | | | 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 != | | > | 大于 | | < | 小于 | | >= | 大于等于 | | 注意：在 MySQL 中，分号 `;` 是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL 可以开始执行了。因此，解释器遇到触发器执行动作中的分号后就开始执行，然后会报错，因为没有找到和 BEGIN 匹配的 END。 > > 这时就会用到 `DELIMITER` 命令（DELIMITER 是定界符，分隔符的意思）。它是一条命令，不需要语句结束标识，语法为：`DELIMITER new_delemiter`。`new_delemiter` 可以设为 1 个或多个长度的符号，默认的是分号 `;`，我们可以把它修改为其他符号，如 `$` - `DELIMITER $` 。在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了 `$`，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。在 MySQL 5.7.2 版之前，可以为每个表定义最多六个触发器。 - `BEFORE INSERT` - 在将数据插入表格之前激活。 - `AFTER INSERT` - 将数据插入表格后激活。 - `BEFORE UPDATE` - 在更新表中的数据之前激活。 - `AFTER UPDATE` - 更新表中的数据后激活。 - `BEFORE DELETE` - 在从表中删除数据之前激活。 - `AFTER DELETE` - 从表中删除数据后激活。但是，从 MySQL 版本 5.7.2+开始，可以为同一触发事件和操作时间定义多个触发器。 **`NEW` 和 `OLD`** ： - MySQL 中定义了 `NEW` 和 `OLD` 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。 - 在 `INSERT` 型触发器中，`NEW` 用来表示将要（`BEFORE`）或已经（`AFTER`）插入的新数据； - 在 `UPDATE` 型触发器中，`OLD` 用来表示将要或已经被修改的原数据，`NEW` 用来表示将要或已经修改为的新数据； - 在 `DELETE` 型触发器中，`OLD` 用来表示将要或已经被删除的原数据； - 使用方法： `NEW.columnName` （columnName 为相应数据表某一列名） ### 创建触发器 > 提示：为了理解触发器的要点，有必要先了解一下创建触发器的指令。 `CREATE TRIGGER` 指令用于创建触发器。语法： ```sql CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN trigger_statements END; ``` 说明： - `trigger_name` ：触发器名 - `trigger_time` : 触发器的触发时机。取值为 `BEFORE` 或 `AFTER`。 - `trigger_event` : 触发器的监听事件。取值为 `INSERT`、`UPDATE` 或 `DELETE`。 - `table_name` : 触发器的监听目标。指定在哪张表上建立触发器。 - `FOR EACH ROW`: 行级监视，Mysql 固定写法，其他 DBMS 不同。 - `trigger_statements`: 触发器执行动作。是一条或多条 SQL 语句的列表，列表内的每条语句都必须用分号 `;` 来结尾。当触发器的触发条件满足时，将会执行 `BEGIN` 和 `END` 之间的触发器执行动作。示例： ```sql DELIMITER $ CREATE TRIGGER `trigger_insert_user` AFTER INSERT ON `user` FOR EACH ROW BEGIN INSERT INTO `user_history`(user_id, operate_type, operate_time) VALUES (NEW.id, 'add a user', now()); END $ DELIMITER ; ``` ### 查看触发器 ```sql SHOW TRIGGERS; ``` ### 删除触发器 ```sql DROP TRIGGER IF EXISTS trigger_insert_user; ``` ## 文章推荐 - [后端程序员必备：SQL高性能优化指南！35+条优化建议立马GET!](https://mp.weixin.qq.com/s/I-ZT3zGTNBZ6egS7T09jyQ) - [后端程序员必备：书写高质量SQL的30条建议](https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247486461&idx=1&sn=60a22279196d084cc398936fe3b37772&chksm=cea24436f9d5cd20a4fa0e907590f3e700d7378b3f608d7b33bb52cfb96f503b7ccb65a1deed&token=1987003517&lang=zh_CN#rd)