SQL基础

编写函数毕竟是为了把较大的概念拆分为另一抽象层上的一系列步骤，函数中的语句应在同一抽象层上。——《Clean Code》

SQL

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 注释：
   • 单行注释：--注释内容或 #注释内容(MySQL特有)
   • 多行注释：/* 注释内容 */

SQL分类

分类	全称	说明
DDL	Data Definition Langualage	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录。
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限。

DDL语句

数据库操作

• 查询
  查询所有数据库：

  SHOW DATABASES;

  查询当前数据库：

  SELECT DATABASE();

• 创建数据库：

  CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]; 

  字符集常见选项：

  • utf8：它支持大部分多语言字符，单每个字符最多使用3个字节。这意味着它不能完整的支持一些复杂的字符，如Emoji。
  • utf8mb4：这是MySQL官方推荐的、功能最全的UTF-8字符集。mb4代表”most bytes 4”，即每个字符最多可以占用4个字节。这使得它能够完整支持所有Unicode字符，包括Emoji、复杂的汉字、特殊符号。
  • gbk：用于简体中文，每个汉字占用两个字节。它只支持简体中文，不支持其他语言的字符。
  • latin1：这是MySQL的默认字符集。它只包含西欧语言字符，不支持中文。
  • ascii：最基本的字符集，只包含英文字母、数字和一些符号。

  排序规则：排序规则是与特定字符集相关联的一组规则，它定义了如何比较和排序字符集中的字符串。同一个字符集可以有多种排序规则。以 utf8mb4为例：

  • utf8mb4_general_ci：这是 utf8mb4字符集的一个通用排序规则。ci表示不区分大小写。
  • utf8mb4_unicode_ci：它基于Unicode标准进行排序。不区分大小写。
  • utf8mb4_bin：直接按照字符的二进制值进行比较，因此是区分大小写的。

• 删除：

  DROP DATABASE[IF EXISTS] 数据库名;

• 使用：

  USE 数据库名;

表操作

查询

• 查询当前数据库所有表：

  SHOW TABLES;

• 查询表结构：

  DESC 表名;

• 查询指定表的建表语句

  SHOW CREATE TABLE 表名;

创建

• DDL-表操作-创建

  CREATE TABLE 表名(
  	字段1 字段1类型 [COMMENT "字段1注释"],
  	字段2 字段2类型 [COMMENT "字段2注释"],
  	...
  	字段n 字段n类型 [COMMENT "字段n注释"]
  )[COMMENT "表注释"];

修改

• DDL-表操作-添加字段

  ALTER TABLE 表名 ADD 字段名 类型 [COMMENT 注释] [约束];

• DDL-表操作-修改数据类型

  ALTER TABLE 表名 MODIFY 字段名 新数据类型;

• DDL-表操作-修改字段名和字段类型

  ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 [COMMENT注释] [约束];

• DDL-表操作-删除字段

  ALTER TABLE 表名 DROP 字段名;

• DDL-表操作-修改表名

  ALTER TABLE 表名 RENAME TO 新表名;

删除

• DDL-表操作-删除

  DROP TABLE [IF EXISTS] 表名;

• DDL-表操作-删除指定表，并重新创建该表(清除表中的数据)

  TRUNCATE TABLE 表名

数据类型

数值类型

类型	大小 byte	SIGNED范围	UNSIGNED范围	描述
TINYINT	1	-128~127	0~255	小整数值
SMALLINT	2	-32768~32767	0~65535	整数值
MEDIUMINT	3	-8388608~8388607	0~16777215	整数值
INT	4	-2147483648~2147483647	0~4294967295	大整数值
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615	极大整数值
FLOAT	4	-3.402823466E+38~3.402823466E+38	0~3.402823466E+38	单精度浮点数值
DOUBLE	8	-1.7976931348623157E+308~1.7976931348623157E+308	0~1.7976931348623157E+308	双精度浮点数值
DECIMAL(M,D)	依赖M,D	依赖于M和D的值	依赖于M和D的值	小数值(精确定点数)

关于显示宽度：

• 对于整数类型：括号中的M为显示宽度，表示客户端希望显示的最少字符数。只有在结合 ZEROFILL属性时才有实际意义，否则仅为显示提示。

• 对于浮点数类型：M是数据的总位数(精度)，D是小数点后的位数(标度)。当整数部分长度大于M-D时会插入失败，小数部分长度大于D显示时会被截断。

• 浮点数类型存储的都是近似值，而不是精确值。在要求高精度时，应该使用 DECIMAL。

• INT(M)、FLOAT(M, D)、DOUBLE(M, D) 等写法以及 ZEROFILL 都是 MySQL 特有的语法扩展，不属于 SQL 标准。从 MySQL 8.0.17 开始它们已被标记为弃用（deprecated），在使用时会产生警告，未来可能被移除。使用时建议直接使用 INT、FLOAT、DOUBLE 。

  

  经过实测，这几个功能在：8.0.43MySQL中任然可以生效。

DECIMAL(M,D)类型详细说明：(这个是SQL语言标准，为定点数可以精确表示范围内的数值)

• M的取值范围：1~65，默认值为10
• D的取值范围：0~30，且不能大于M，默认值为0
• 例如：DECIMAL(5,2) 可以存储 -999.99 到 999.99

修饰符（放在类型后面修饰）：

• UNSIGNED：表示为无符号数，不能存储负数
• ZEROFILL：用0填充，当数值少于指定位数时，会在左边补0，同时隐含UNSIGNED属性(MySQL独有，且已经准备弃用)
• AUTO_INCREMENT：自动递增，通常用于主键字段

字符串类型

类型	大小	描述
CHAR(M)	0-255 bytes	定长字符串
VARCHAR(M)	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过255个字节的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65535 bytes	二进制形式的长文本数据
TEXT	0-65535 bytes	长文本数据
MEDIUMBLOB	0-16777215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16777215 bytes	中等长度文本数据
LONGBLOB	0-4294967295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4294967295 bytes	极大文本数据

M：表示存储的最大字符数。

• char类型为定长字符串，存取性能较高。空余部分用空格填充。
• varchar为变长字符串，存取性能较差，但节省空间。
• BLOB类型存储二进制数据，如音频视频等文件，但是文件太大导致存储效率不高，通常使用文件系统代替。数据库只存储文件路径或URL。

日期类型

类型	大小	范围	格式	描述
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901 至 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

DML语句

DML是数据操作语言，用来对数据库中表的数据记录进行增删改操作。

添加数据

1. 给指定字段添加数据

   INSERT INTO 表名 (字段1, 字段2, ...) VALUES (值1,值2,...);

2. 给全部字段添加数据

   INSERT INTO 表名 VALUES (值1, 值2, ...);

3. 批量添加数据

   INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), ...;

   INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), ...;

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期类型数据应该包含在引导中。
• 插入的数据大小，应该在字段的规定范围内。

修改数据

UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [WHERE 条件];

• 如果没有条件，则会修改整个表的数据。

删除数据

DELETE FROM 表名 [WHERE 条件];

• 无条件删除整个表
• DELETE语句不能删除某一个字段的值，只能一次删除一行。

DQL语句

DQL是数据查询语言，用来查询数据库中表的记录。

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

基本查询

1. 查询多个字段

   SELECT 字段1 [AS "别名1"], 字段2 [AS "别名2"], 字段3 [AS "别名3"], ... FROM 表名;

   SELECT * FROM 表名;

• AS关键字可省略。

2. 去除重复记录

   SELECT DISTINCT 字段列表 FROM 表名;

条件查询

1. 语法

   SELECT 字段列表 FROM 表名 WHERE 条件;

SQL 比较运算符对照表

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN ... AND ...	在某个范围之内（含最小、最大值）
IN (...)	在 IN 之后的列表中的值，多选一
LIKE “占位符”	模糊匹配（_ 匹配单个字符，% 匹配任意字符）
IS NULL	是 NULL

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或 `
NOT 或 !	非，不是

聚合查询

将一列数据作为一个整体，进行纵向计算。

SELECT 聚合函数(字段列表) FROM 表名;

• null值不参与所有聚合函数运算。

常见聚合函数

函数	描述	示例用法
COUNT()	统计行数（可选字段或 *）	SELECT COUNT(*) FROM users;
SUM()	计算数值字段的总和	SELECT SUM(salary) FROM staff;
AVG()	计算数值字段的平均值	SELECT AVG(score) FROM exams;
MAX()	获取字段中的最大值	SELECT MAX(age) FROM people;
MIN()	获取字段中的最小值	SELECT MIN(price) FROM goods;