本篇笔记中默认的规则：

代码中出现[]的部分为可写可不写的

概述

数据库相关概念

关系型数据库

概念：建立在关系，模型基础上，由多张相互连接的二维表组成的数据库

RDBMS 术语

• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • 联合主键:关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。对于联合主键，允许一列有重复，只要不是所有主键列都重复即可
• 外键：可以把数据与另一张表关联起来,，这种列称为外键。外键用于关联两个表。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
• 表的每一行称为记录（Record），记录是一个逻辑意义上的数据；
• 表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

前置知识

MySQL 数据类型

数值类型

标准 SQL 数值数据类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

注：double的使用规则：

1
2

#字段名 DOUBLE(总体位数,小数部分位数);如:
score DOUBLE(4,1);#小数一位，整数部分最高3位的浮点数

日期和时间类型

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

字符串类型

注意：**char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。**

CHAR 和 VARCHAR 类型类似，它们在存储和检索数据时有一些区别。

1. 存储方式：CHAR类型以固定长度存储数据，而VARCHAR类型以可变长度存储数据。例如，如果定义一个CHAR(10)列，不论实际存储的字符数是多少，它都会占用10个字符的存储空间。而VARCHAR(10)列只会占用实际存储的字符数所需的空间。
2. 最大长度：CHAR类型具有固定的最大长度，而VARCHAR类型具有可变的最大长度。例如，如果定义一个CHAR(10)列，它可以存储最多10个字符。而VARCHAR(10)列也可以存储最多10个字符，但它可以根据实际存储的字符数动态调整长度。
3. 尾部空格：对于CHAR类型，存储时会在字符串末尾填充空格，以达到指定的固定长度。而VARCHAR类型不会填充空格，它只会存储实际的字符数据。
4. 性能：char的性能比varchar更高，无需判断字符串大小

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。有时候我们需要存储的数据是二进制形式的，例如图像、音频或加密数据等。在这种情况下，我们可以使用BINARY和VARBINARY类型来保存二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象（如图片、音频、视频等等），可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

SQL

SQL通用语句

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

SQL语句分类

DDL

数据库操作

查询

查询所有数据库

1

SHOW DATABASES;

查询当前数据库

1

SELECT DATABASE();

创建

1

CREATE DATABASE [IF NOT EXSITS] 数据库名 [DEFAULT CHARSET 字符集（如utf-8、gbk等）] [COLLATE 排序规则]；

注：[IF NOT EXSITS]表明如果数据库不存在则创建，存在则不创建

删除

1

DROP DATABASE [IF EXISTS] 数据库名；

使用

1

USE 数据库名；

表操作

查询

查询当前数据库的所有表

1

SHOW TABLES;

查询表结构

1

DESC 表名;

查询指定表的建表语句

1

SHOW CREATE TABLE 表名;

创建

1
2
3
4
5
6
7
8

CREATE TABLE 表名(
	字段1 字段1类型 约束条件 [COMMENT 字段1注释],
	字段2 字段2类型 约束条件 [COMMENT 字段2注释],
	字段3 字段3类型 约束条件 [COMMENT 字段3注释],
	.....
	字段n 字段n类型 约束条件 [COMMENT 字段n注释]

)[COMMENT 表注释];

注：

• 最后一个字段不要加,逗号
• 注释需要用' '

修改

添加字段

1

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

修改字段

1

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型

1

ALTER TABLE 表名 CHANGE 旧字段名 类型(长度) [COMMENT 注释] [注释];

删除字段

1

ALTER TABLE 表名 DROP 字段名;

修改表名

1

ALTER TABLE 旧表名 RENAME TO 新表名

删除

删除表

1

DROP TABLE [IF EXISTS] 表名;

删除指定表，并重新创建该表

1

TRUNCATE TABLE 表名;

注：重新创建后的表其内置的数据已被删除

DML

添加数据

给指定字段添加数据

1

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

给全部字段添加数据

1

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

批量添加数据(添加多行数据)

1

INSERT INTO 表名 （字段1,字段2，...） VALUES（值1，值2,....）,（值1，值2,....）,（值1，值2,....）;

1

INSERT INTO 表名 VALUES （值1，值2,....）,（值1，值2,....）,（值1，值2,....）;

注意：

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内
• char类型的需要用' '单引号进行括号起来

修改数据

1

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

注：不加where条件会使所以的字段更改为对应的值

删除数据

1

DELETE FROM 表名 [WHERE 条件];

注：

• 如果不加入条件将删除整张表的数据
• DELETE语句不能删除某个字段的值（可以用UPDATE）

DQL

语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

基本查询

1.查询多个字段

1

SELECT 字段1，字段2，字段3，...FROM 表名;

1

SELEC * FROM 表名;# *代表查询所有

2.设置别名

1

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

注：AS可省略，直接添加上别名

3.去除重复数据

1

SELECT DISTINCT 字段列表 FROM 表名;

条件查询(WHERE)

语法

1

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

条件

聚合函数(count,max,min,avg,sum)

作用：将一列函数作为一个整体，进行纵向计算

常见聚合函数

语法

1

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

注：

• null值不参与所有聚合函数运算
• count会统计字段列表中，含有最多数目的字段

分组查询(GROUP BY)

语法

1

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤的条件]

注：

• where先筛选符合的字段，having筛选分组后的字段。因此where不能对聚合函数进行判断，而having可以。
• 执行顺序：where > 聚合函数 > having
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他的字段无意义
• 依照分组字段进行分组时，非分组字段只显示一次出现的值
• 此处使用聚合函数，获得的是分组后使用聚合函数的结果

排序查询(ORDER BY)

语法

1

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2; 

排序方式

• ASC:升序（默认值）
• DESC:降序

注：如果是多字段排序，当第一个字段相同时，才根据第二个字段进行排序

分页查询(LIMIT)

语法

1

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注：

• 起始索引从0开始，起始索引＝（查询页码-1）*每页显示记录数。
• 分页查询是数据库的方，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit +数目。

DQL顺序

编辑顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

执行顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

DCL

管理用户

1.查询用户

1
2

USE mysql;
SELECT * FROM user;

2.创建用户

1

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

3.修改用户密码

1

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

4.删除用户

1

DROP USER '用户名'@'主机名';

注：主机名可以使用%通配

权限控制

常用权限

1.查询权限

1

SHOW GRANTS FOR '用户名'@'主机名';

2.授予权限

1

GRANTS 权限列表 ON 数据库.表名 TO '用户名'@'主机名';

3.撤销权限

1

REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';

注：

多个权限之间用,进行分割

授权时，数据库名和表名可以使用*进行通配，代表所有。

函数

字符串函数

数值函数

日期函数

流程函数

注：CASE后可跟多个WHEN;

约束

概述：作用于表中字段上的规则，用于限制储存在表中的数据

目的:保证数据库中数据的正确、有效性和完整性

分类

注:

• 约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束
• 一个字段可以使用多个约束条件

外键约束