【编程语言】SQL语言

WangYun · 2023 年8 月 22 日 07:09

0. 常见数据类型

数字类型
- TINYINT：0～255或-128～127，1字节
- SMALLINT：0～65535或-32768～32767，2字节
- MEDIUMINT：0～16777251或-8388608～8388607，3字节
- INT：0～4294967295或-2147683648～2147683647，4字节
- BIGINT：8字节
- FLOAT：单精度浮点值
- DOUBLE：双精度浮点值
- BOOLEAN：布尔值
字符串类型
- CHAR：1～255个字符，固定长度字符串
- VARCHAR：最多不超过255字符，长度可变
- TEXT：最大长度为64K的变长文本
- TINYTEXT：与TEXT相同，但最大长度为255字节
- MEDIUMTEXT：与TEXT相同，但最大长度为16K
- LONGTEXT：与TEXT相同，但最大长度为4GB
日期和时间类型
- DATE：日期，格式为YYYY-MM-DD
- TIME：时间，格式为HH:MM:SS
- DATETIME：日期和时间，格式为YYYY-MM-DD HH:MM:SS
- TIMESTAMP：时间标签，功能和DATETIME相同，但范围较小
- YEAR：年份可指定两位数字和四位数字的格式

1. DDL数据库操作

1.1. 数据库相关

创建数据库

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] 数据库名 [CHARACTER SET 字符集];
- IF NOT EXISTS: 创建前先判断，不存在是才进行创建
- CHARACTER SET=字符集: 用于指定数据库的字符集
- 建议使用小写字符定义数据库名
查看数据库信息
- 列出当前用户权限范围内所能查看到的所有数据库名 SHOW DATABASES;
- 指定我们要使用的数据库(必须指定才能对数据库进行操作) USE 数据库名;
- 查看数据库定义信息 SHOW CREATE DATABASE 数据库名;
修改数据库

ALTER DATABASE [数据库名] [CHARACTER SET 字符集];
- 如果我们不指定旧数据库名，以为着修改当前使用的数据库，即 USE 指定的数据库
删除数据库

DROP DATABASE [IF EXISTS] 数据库名;
- IF EXISTS：删除前判断数据库是否存在，可以避免删除不存在的数据库而产生异常

1.2. 数据表相关

创建数据表
```
CREATE TABLE 表名 (
	列名1 数据类型 [NOT NULL | NULL] [DEFAULT 默认值] [PRIMARY KEY] [AUTO_INCREMENT] [注释],
	列名2 列属性
);
```
- NOT NULL | NULL：该列是否允许是空值，该属性默认是NULL
- DEFAULT：设置默认值
- AUTO_INCREMENT：设置自动编号
- PRIMARY KEY：设置为主键
复制数据表

CREATE TABLE 数据库表名 {LIKE 源数数据表名 | (LIKE 源数据库表名)}
- LIKE：指定待复制的源数据表，注意这只会复制表结构，不会复制数据
查看表信息
- 查看数据库中所有表名 SHOW TABLES;
- 查看表结构 DESCRIBE 表名; 或 DESC 表名;
- 查看表中某列结构信息 DESCRIBE 表名列名; 或 DESC 表名列名;
修改数据表
- 添加新列 ALTER TABLE 表名 ADD 列名列属性;
- 修改列属性 ALTER TABLE 表名 MODIFY 列名列属性;
- 修改列名 ALERT TABLE 表名 CHANGE 旧列名新列名列属性;
- 删除列 ALERT TABLE 表名 DROP 列名;
- 修改表名 ALERT TABLE 旧表名 RENAME AS 新表名; 或 RENAME TABLE 旧表名 TO 新表名
删除数据表

DROP TABLE [IF EXISTS] 数据表名;
- IF EXISTS：删除前判断数据表是否存在，可以避免删除不存在的表而产生异常

2. DML表数据操作

2.1. 插入表数据

INSERT INTO 数据表名 [(列名1, 列名2...)] VALUES (值1, 值2...), (值1，值2...);

当指定列名时，值和列需要一一对应
当为指定列名时，值需要包含所有属性，并且不可以随意更改属性顺序

2.2. 修改表数据

UPDATE 数据表名 SET 列名1=值1[, 列名2=值2...] [WHERE 条件表达式];

假如我们没有通过WHERE进行筛选的话，整列的值将全都被修改

2.3. 删除表数据

DELETE FROM 数据表名 [WHERE 条件表达式];

要注意，如果没有指定WHERE会删除表内所有数据

如果我们的本意是要直接删除表中所有数据可以使用 TRUNCATE TABLE 数据表名;

DELETE 在删除时相当于有多少条数据执行多少次DELETE
TRUNCATE 在做删除操作时相当于直接删掉整张表之后重新创建一张空表
假设我们的表中有自增长字段

使用 DELETE 删除某写数据之后再添加数据，自增长的数据不会重新开始计数
使用 TRUNCATE 删除后，因为是重新创建的表，因此当再添加数据，自增长会重新开始计数。但将从1开始计数，最初设置的自增长起始值将会失效

3. DQL表查询操作

完整语句: SELECT [DISTINCT] 字段|聚合函数 FROM 表名 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT];

3.1. 单表查询

SELECT * FROM 表名;

字段查询

SELECT 字段1(列名)[, 字段2...] FROM 表名;
字段别名
- 表别名 SELECT 字段1(列名)[, 字段2...] FROM 表名表别名;
- 字段别名 SELECT 字段1 AS 别名1[, 字段2 AS 别名2...] FROM 表名;
去重

SELECT DISTINCT {字段1(列名)[, 字段2...] | *} FROM 表名;

假设我们查找的范围是多列的情况，那么每个字段都相同的两个数据才称之为重复数据
运算查询

我们可以在查询的过程中进行一些简单运算 SELECT 字段1 运算表达式[, 字段2 运算表达式...] FROM 表名;
- 例如: SELECT salary AS ‘原始薪资’, salary+1000 AS ‘浮动后薪资’ FROM salaries;

3.2. 条件查询

SELECT 字段 FROM 表名 WHERE 条件表达式;

条件表达式支持的运算符(常用)
- AND &&: 逻辑与
- OR ||: 逻辑或
- NOT: 取反，逻辑非
- <: 小于
- >: 大于
- <=: 小于等于
- >=: 大于等于
- =: 等于
- <> !=: 不等于
- ...BETWEEN...AND...: 范围限定
- 字段 IN 常量列表: 子集限定
- IS NULL IS NOT NULL: 是否为空
- ... LIKE '通配符字段': 模糊查询（大小写不敏感）
  - %: 匹配任意多个字符
  - _: 匹配一个字符

当我们判断一个字符串大于小于等于时，本质上是判断字符串的ASCII码的大小，因此在做日期判断时需要考虑清楚。

3.3. 排序

SELECT 字段 FROM 表名 [WHERE] ORDER BY 字段1 [ASC|DESC][, 字段2 [ASC|DESC]...];

ASC：升序（默认）
DESC：降序

当我们指定多个字段排序时意味着我们在进行组合排序
此时会先对第一个字段排序，如果第一个字段相同在通过第二个字段排序

3.4. 聚合函数

聚合函数是做纵向(列)运算的方法函数

SELECT 聚合函数(字段) FROM 表名 [WHERE] [ORDER BY];

COUNT(): 统计指定列不为NULL的记录行数
MAX(): 计算指定列的最大值
MIN(): 计算指定列的最小值
SUM(): 计算指定列的数值和
AVG(): 计算指定列的平均值

3.5. 分组查询

SELECT 分组列|聚合函数 [AS] FROM 表名 [WHERE] GROUP BY 分组列 [HAVING 条件];

HAVING：对分组结果进行再次过滤

GROUP BY 后面的分组列一定要出现在SELECT之后的查询列当中
WHERE 是用于分组前的数据筛选
HAVING 是用于分组后的数据筛选

3.6. LIMIT关键字

写法1: SELECT 字段 FROM 表名 LIMIT [开始的行数(默认为0),] 查询记录的条数;

写法2: SELECT 字段 FROM 表名 LIMIT 查询记录条数 OFFSET 开始的行数;

仅仅在MySQL中才有此方法！！！！！

4. SQL约束

4.1. 主键约束

主键不可为空，且值唯一
一张表只允许有一个主键
通常情况下我们不会遇到删除表中主键的情况，若遇到可使用 ALTER TABLE 表名 DROP PRIMARY KEY;

4.1.1 PRIMARY KEY 创建带有主键的表

CREATE TABLE 表名 (
	字段1 字段属性 PRIMARY KEY,
	字段2 字段属性
	...
);

4.1.2 为没有主键的表设置主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段);

当我们为没有主键的表中设置主键时有一下几点需要注意

将要被设置为主键的字段中不可以存在空值数据(非法数据), 所以应尽量避遍在建表之后才设置主键
我们可以使用 ALTER TABLE 表名 MODIFY 方法去设置主键，但这需要保持列属性的完整

4.1.3 AUTO_INCREMEN 创建主键自增的表

CREATE TABLE 表名 (
	字段1 字段属性 PRIMARY KEY AUTO_INCREMENT,
	字段2 字段属性
	...
) [AUTO_INCREMENT=n];

AUTO_INCREMENT=n：默认自增是从1开始的，我们可以利用 AUTO_INCREMENT=n 的方式设置自增起始值为n

4.2. 非空约束

CREATE TABLE 表名 (
	字段1 字段属性 NOT NULL,
	字段2 字段属性 NOT NULL
	...
)

4.3. 唯一约束

CREATE TABLE 表名 (
	字段1 字段属性 UNIQUE,
	字段2 字段属性 UNIQUE
	...
)

一张表当中可以存在多个唯一约束字段，且值可以为空

4.4. 默认值约束

CREATE TABLE 表名 (
	字段1 字段属性 DEFAULT xxx,
	字段2 字段属性 DEFAULT xxx
	...
)

4.5. 枚举约束

CREATE TABLE 表名 (
  字段1 字段属性 ENUM(选项1, 选项2, 选项3),
  字段2 字段属性 ENUM(选项1, 选项2, 选项3)
);

枚举约束可以为字段提供可选选项，例如性别我们可指定选项为 ENUM('男', '女')

即便我们提供的可选项内没有NULL，在添加数据时该字段依旧可以为NULL

4.6. 检查约束

CREATE TABLE 表名 (
  字段1 字段属性 CHECK (条件表达式),
  字段2 字段属性 CHECK (条件表达式)
);

限制字段允许输入值的范围, 例如年龄我们可以 age INT CHECK (age >= 18 AND age <= 65);

4.7. 外键约束

首先我们要明确两表之间的主从关系，被约束的表是从表
- 例如员工表和部门表，员工表中设置了外键约束为部门id。此时的员工表便为从表，部门表即为主表。
建立外键约束时从表数据类型与主表主键要保持一致
一张表内可以有多个外键

在不同的联系类型中，我们对与表中外键的使用原则略有不同

一对多：多通常被作为从表，一为主表。例如部门和员工，部门为主表，员工为从表
多对多：通常我们将两张表的主键作为外键从而创建一张中间表
一对一：放在单表中即可，无需拆分

4.7.1 创建一张带有外键约束的表

CREATE TABLE 表名 (
	字段1 字段属性,
	CONSTRAINT 自定义的外键约束的名字 FOREIGN KEY (字段1) REFERENCES 主表(主键字段) 
);

4.7.2 向已有表中添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 自定义的外键约束的名字 FOREIGN KEY (外键字段) REFERENCES 主表(主键字段)

与建表后添加主键一样，当我们表中已经有非法数据时(将要被设为的外键字段中存在主表主键字段中不存在的值)会添加外键失败
所以我们应该尽量避免在建表后期去以此方式增加外键

4.7.3 删除外键约束

ALERT TABLE 表名 DROP FOREIGN KEY 外键约束名称

4.7.4 级联删除

通常情况下当我们删除一个主表数据之前需要将从表中相关数据先删掉

例如当我们想要删除部门1，我们需要在员工表中将部门号为1的员工先删去

这样做的效率是低下的，我们希望可以实现删除部门之后，对应部门的员工自动被删除。这便是级联删除的作用

我们在创建外键时可以对字段设置级联删除

CREATE TABLE 表名 (
	字段1 字段属性,
	CONSTRAINT 自定义的外键约束的名字 FOREIGN KEY (字段1) REFERENCES 主表(主键字段) ON DELETE CASCADE
);

5. 高级查询方式

5.1. 多表查询

SELECT 字段 FROM 表1, 表2;

这样的查找方式所展示的结果是完整的笛卡尔积组成的表，我们通常需要从中获取到真正有效的数据，我们的做法可以是 SELECT * FROM 表1, 表2 WHERE 条件表达式;

5.2. 内连接查询

内连接只显示符合匹配条件的结果

隐式内连接：SELECT 字段 FROM 表1, 表2 WHERE 连接条件;

显示内连接：SELECT 字段 FROM 表1 INNER JOIN 表2 ON 连接条件;

其实我们在5.1当中提出的做法便是隐式内连接的方式

5.3. 外连接查询

查询多个表中相关联的行，有时候需要包含没有关联的行中数据，即返回结果集合中不仅包含符合连接条件的行，还包括左表(左连接)、右表(右连接)中所有的数据行

左连接：SELECT 字段 FROM 左表 LEFT JOIN 右表 ON 连接条件;
右连接：SELECT 字段 FROM 左表 RIGHT JOIN 右表 ON 连接条件;

5.4. 子查询

FROM子查询：将子查询的结果作为父查询的表来使用 SELECT 字段 FROM (子查询语句) AS 别名;
条件表达式子查询：将子查询的结果作为条件表达式的一部分来使用 SELECT 字段 FROM 表名 WHERE 含有子查询语句的条件表达式;
WITH AS：如果整句查询语句中，某个子查询会被多个父查询引用时可以使用此方法将子查询的结果作为一张临时的表并命名，从而简化语句 WITH 别名 AS (子查询语句) SELECT语句;

子查询语句一定要被()包在内部
FROM子查询一定要为子查询语句建立别名

6. 视图

视图是一种虚拟的表，他并不会在你的存储空间复制一份数据，而是对原数据的一种引用。可理解为一种存储起来的SQL语句

创建视图：CREATE VIEW 视图名 AS (SELECT查询语句)

7. 附录

开源数据集: https://github.com/datacharmer/test_db.git