SQL 语句说明

数据库和数据表

数据库是通过某种方式来组织和存储数据的集合，数据库中的数据存放时以表问单位，表就是一个结构化的文件，关系型数据库中，表由字段（列）和记录（行）组成。

• 表名：表名用于唯一标识某个数据库中的一张表，一般通过 完全限定名 这种方式标识。例如：MySQL中唯一标识一张表 “DBNAME.TBNAME”
• 字段：一个数据表由一个或多个字段组成，每个字段由字段名、数据类型和字段属性组成。数据类型限制了该字段可以存储的数据。
• 记录：记录就是表中的一条数据。

表中常见的数据类型：

• 数值类：用于存储各种整数、小数
• 字符类：用于存储文本字符串，通常使用引号包裹
• 时间类：用于存储日期和时间数据
• 布尔类：用于表示真假值，通常为 TRUE 或 FALSE
• 二进制类：用于存储图片、音频等二进制数据

表中常见的字段属性：

• PRIMARY KEY（主键）：唯一标识每一行记录，不能为空、不能重复。每张表只能设置一个主键。
• FOREIGN KEY（外键）：用于建立与其他表的关系，外键的值必须来自另一张表的主键或唯一键。
• UNIQUE（唯一约束）：要求该字段的值在表中不能重复，但可以为 NULL。
• AUTO_INCREMENT（自增）：数值字段自动增长（MySQL 专有），常用于主键。
• DEFAULT（默认值）：如果插入数据时没有指定该字段的值，就使用默认值。
• INDEX（索引）：提高查询效率，对字段建立索引。

什么是 SQL 语言

SQL （Structured Query Language）结构化查询语言是由 ISO（国际标准化组织） 和 ANSI（美国国家标准协会） 共同定义的标准化数据库查询语言，通过SQL可以和数据库进行交互的语言。

SQL 语言的特点：

• 关键字不区分大小写：例如SELECT 和 select 效果一样
• 语句以分号结束：一条完整的 SQL 语句通常以 ; 结尾
• 支持单双引号：具体的作用在不通的DBMS中不通，MySQL中默认单双引号都是表示字符常量。
• 支持不同的注释方式：单行注释（两个减号+空格），多行注释（/* 注释内容 */）

SQL 语言的分类：

正因为 SQL 是一种标准，所以基本上所有的DBMS都支持SQL的标准格式，但是不通的DBMS会根据自身情况进行扩展，从而更方便对数据库的管理，这种扩展后的SQL仅该厂家支持，不通用。

标准 SQL ：

扩展 SQL：

• 不同数据库系统有各自的“SQL 方言”，语法细节和特性可能不同。例如：MySQL中就提供了一个名为AUTO_INCREMENT`（自增）的属性来用于实现对主键的自增。

数据操作语言（DML）

1、SELECT 单表查询

如果需要从数据库中查询指定的数据（记录）信息，需要使用 DML 中的人 SELECT 语句进行操作。在标准的SQL语句中，关键字是不区分大小写的。

SELECT 语法格式：

SELECT 字段列表
	FROM 数据表名
	[WHERE 条件]
	[GROUP BY 分组字段]
	[HAVING 分组后筛选条件]
	[ORDER BY 排序字段 [ASC|DESC]]
	[LIMIT 限制数量]  --（MySQL 专属）

字段列表：指定输出满足查询条件的数据时，数据那些列，多个列名之间必须以逗号分隔

指定显示的字段：

字段列表控制需要将符合条件数据的哪些字段进行输出，如果指定了多个字段，多个列名之间必须以逗号分隔，如果需要输出所有字段信息，使用 * 表示。

例如：

select user,host from mysql.user

通过 WHERE 对原始数据进行筛选

语法格式

WHERE 条件表达式;

通过 WHERE 关键字就可以指定检索数据时的查询条件，WHERE 支持以下操作符：

比较操作符：

• 比较规则：是根据 数据库字符集规则 进行比较。例如：MySQL使用 utf8mb4_general_ci 字符集时，无论是中文、英文、标点符号，都是根据Unicode 编码值进行比较，处理英文字母时会将大小写视为相同的字符来处理。

逻辑操作符

范围操作符

集合操作符

通配符匹配操作符：

空值判断操作符：

通过 聚合函数 获取汇总信息

聚合函数作用：是SQL中专门用于对一组数据进行汇总、计算的函数，使用的时候一般配合GROUP BY 使用，用来统计、求和、取平均、查最大最小值等。

聚合函数位置：在 SELECT 字段列表中。用来作为查询结果的一部分显示出来

常见聚合函数：

例如：通过某个数据表中一共有多少行

SELECT count(*) FROM position_ehcommon.tb_track_history 

通过 ORDER BY 对结果排序

说明：同时使用 ORDER BY 和 WHERE 时，应该让 ORDER BY 位于WHERE 之后，否则会报错。

语法格式：

ORDER BY 需要参与排序的字段  ASC|DESC

1. 参与排序的字段，可以指定一个或多个，多个字段之间使用逗号分隔
2. 每个字段后面都可以单独指定是升序还是降序

排序规则：

排序规则由数据库的 字符排序集 决定。例如：MySQL使用的字符排序集是utf8mb4_general_ci时，进行排序的时候是根据字符的Unicode编码值比较大小，且字母比较时认为大小写字母一样。

排序方式：

通过 GROUP BY 对数据进行分组

通过SQL 中的 GROUP BY 可以对查询到的数据进行分组，一般都是和 聚合函数 进行结合使用。因为GROUP BY 是“按某个字段分组”，每组只保留一行结果；这一行通常需要展示这个组的汇总信息，汇总信息需要通过聚合函数来计算。

GROUP BY 语法格式：

GROUP BY 分组字段;	

例如：统计每个部门的人数

SELECT department, COUNT(*) AS 部门人数
		FROM employees
		GROUP BY department;

通过 HAVING 对分组结果进行筛选

HAVING 使用场景：是 SQL 中专门用于筛选分组后的结果的子句，所以一般是和GROUP BY 一起使用的。

HAVING 语法格式：

HAVING 聚合条件;

和 WHERE 的区别:

说明：对聚合函数的结果（如 COUNT()、AVG()、SUM()）进行筛选，必须使用 HAVING，不能使用 WHERE。

例如：对平均工资进行筛选

-- 正确格式
SELECT department, AVG(salary) AS 平均工资
	FROM employees
	GROUP BY department
	HAVING AVG(salary) > 8000;
	
-- 错误情况
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000    -- 报错！
GROUP BY department;

2、SELECT 多表查询

SQL 的 JOIN 功能：

当需要查询的数据信息分散存储在多张表里面时，如果需要一次性查到需要的数据。就需要使用SQL提供的JOIN功能。JOIN就是通过某张关系让两个或多个表拼接起来返回一个结果集，然后在这个结果集中筛选所需的数据。

表之间的桥梁：

• 外键和主键之间的关系；
• 两个表中“意义相同”的字段

JOIN 的类型：

JION 语法格式：

SELECT 字段列表  FROM 表1 -- 查询时字段一般写 完全限定格式（如 表名.字段名）
	INNER|LEFT|RIGHT JOIN 表2  ON 连接条件

例如：

3、INSERT 插入数据

通过 DML 中的 INSERT 可以实现在某张表中插入一条或多条数据。

语法格式：

-- 指定字段名的插入
INSERT INTO 表名 (字段1, 字段2, ...)
	VALUES (值1, 值2, ...);
-- 不指定字段名的插入
INSERT INTO 表名
	VALUES (值1, 值2, ...);

• 指定字段名插入数据时：会将数据一一对应插入到对应字段，指定的字段会被赋值，没被指定的字段如果定义了 DEFAULT 默认值，就使用默认值，如果字段允许为 NULL，则自动填充为 NULL，否则就报错。
• 不指定字段插入数据时：数据会按顺序插入到表结构定义中从上到下的字段(不推荐)

4、UPDATE 更新数据

通过DML中的 UPDATE 可以实现更改某站表中的制定数据。

语法格式：

UPDATE 表名 -- 指定要修改哪张表的数据；
	SET 字段1 = 值1, 字段2 = 值2, ... -- 列出要修改的字段和值；
	WHERE 条件; -- 限制修改范围

注意：使用 UPDATE 时一定要使用 WHERE 来限定范围！否则会将当前表中所有记录的字段都更改了。而且涉及数据修改前，先备份。

例如：

UPDATE tb_device_info
	SET face_terminate_id_out=''  -- 该表所有记录的face_terminate_id_out字段都会为空

5、DELETE 删除数据

通过 DML 中的 DELETE 可以删除某张数据表中的制定数据。

语法格式：

DELETE FROM 表名
	WHERE 条件;

注意：如果不加 WHERE，会删除整张表中的所有数据！，操作前先备份表数据，防止意外。

DELETE 和 TRUNCATE 删除所有区别：

• DELETE：仅仅是将指定表当前的数据全部清空，如果设置了主键自增，也不会被重置为从0重新开始，文件系统空间占用不会释放。
• TRUNCATE ：是在文件系统中将整张表删除了然后重建表结构，所以主键自增也会重置从0开始。会释放文件系统空间占用。

数据定义语言（DDL）

1、CREAT TABLE

语法格式：

CREATE TABLE 表名
	(
	字段1 数据类型 属性,
	字段2 数据类型 属性,
	字段3 数据类型 属性,
	);

只复制某张表的表结构：

说明：这种方式会将表的完整结构信息，包括属性信息这些一起复制过来。

CREATE TABLE 表名 LIKE 原表名;	

复制某张表的数据和基本结构：

说明：这种方式不会复制原表的索引、主键、外键、默认值、约束等结构信息，就只复制了表的数据和结构

CREATE TABLE 新表名 AS
	SELECT * FROM 原表名;

复制某张表的完整结构和数据：

-- 第一步：复制结构（包含字段类型、默认值、主键、索引等）
CREATE TABLE 新表名 LIKE 原表名;

-- 第一步：复制结构（包含字段类型、默认值、主键、索引等）
CREATE TABLE 新表名 LIKE 原表名;

2、ALTER TABLE

ALTER TABLE 用于对已有的表进行结构性修改。

新增字段语法格式：

ALTER TABLE 表名 ADD 字段名 数据类型 属性;

删除字段语法格式：

ALTER TABLE 表名 DROP 字段名;

修改字段语法格式：

• MODIFY ：只能修改字段的类型，属性。并且一定要写全字段的所有属性，因为会以前的都会被覆盖掉。
• CHANGE：用于修改字段的名称，但是需要指定原名才行。

3、DROP TABLE

DROP TABLE 用于删除某张表。

语法格式：

DROP TABLE TB_NAME

说明：如果是重要数据，删除表之前要备份，否则悔之晚矣。

事务控制语言（TCL）

TCL 是事务控制语言（Transaction Control Language），用于实现对数据库事务的控制。

说明：事务只会对DML操作生效，如果是DDL这种对数据库结构进行修改的，不支持事务。

TCL 中的事务控制语句：

例如：MySQL中的事务使用

-- 关闭自动提交（MySQL 默认自动提交，每条语句自动生效）
SET autocommit = 0;

-- 开始事务
START TRANSACTION;

-- 第一步：A 扣钱
UPDATE accounts SET balance = balance - 100 WHERE name = 'A';

-- 第二步：B 加钱
UPDATE accounts SET balance = balance + 100 WHERE name = 'B';

-- 提交事务（两步都成功才执行）
COMMIT;

-- 如果 所有语句都执行成功，COMMIT 才会生效，生效后没法进行回滚
-- 如果 存在某些语句执行失败，COMMIT 时会提示失败，需要手动执行ROLLBACK才会自动回滚。