数据库基础

# 数据库基础

# 环境变量

在用户里面添加 MYSQL_HOME = MYSQL 的安装目录

在系统变量 Path 里面添加 MYSQL 的安装目录 /bin

即可在 cmd 里面输入

mysql -uroot -p

如想连其他主机 mysql 则使用

# mysql -h IP -P 3306 -uroot -p 密码
mysql -h 127.0.0.1 -P 3306 -uroot -p 123456

1
2

# 命令符指令

show databases; 查看数据库
use 库的名字；进入指定的库
show tables; 查看指定库的表
show tables from 库的名字；查看某个库的表，不进入库
select database (); 查看当前在那个库
create table 表的名字 (表结构); 在当前库创建新的表，命令符里面都是以分号作结束字段
desc 表的名字；查看表的结构 (describe 描述)
selct * from 表的名字；查看表的数据
insert into 表的名字 values (); 在表中插入新的数据，括号里面填入要插入的数据
set names gbk; 如果插入失败可能是编码问题，如果改 gbk 不行尝试改成 utf8
update 表的名字 set 列的名字 =‘XXXX’ where (相当于 if) 其中一项列的名字 (判断条件);
delete from 表的名字 where 列的名字 =“XXXX” 删除指定列
alter table 表的名字 add column 列的名字数据类型在表中新建类别列
drop table 表的名字删除指定表的
exit; 退出
#注释
–- 注释
/* 注释 */ #注释不能嵌套

# SQL 语句类别

SQL 基本可以用在所有关系型数据库中，除了某些特有的指令

DDL (Data Definition Language)：数据定义语言，这类语句定义了不同的数据库、表、索引、视图等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构；如常见指令有:create/drop/alter
DML(Data Manipulation language)：数据操作语言，用于添加、删除、更新和查询数据库记录，并检查数据完整性；如 Insert/updata/delete/select 等
DCL (Data Control Language)：数据控制语言，主要用于定义数据库、表、字段、用户的访问权限和安全级别；主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等
TCL (Transaction Control Language)：事务控制语言，TCL 事务控制语言主要用来事务管理，如果需要在一个事务中包含多条 SQL 语句，那么需要开启事务和结束事务；主要的语句关键字包括 COMMIT 提交、 ROLLBACK 回滚、 SAVEPOINT 保存点等。
DQL(Data Query Language)：数据查询语言，DQL 数据查询语言主要用来查询记录（数据），执行 SELECT 操作。数据库执行 DQL 语句不会对数据进行改变，而是让数据库发送结果集给客户端。如 select

# SQL 语言的基本规范

SQL 语言可以单行或多行书写。为了提高可读性，各子句可分行写，必要时使用缩进；
每条命令以 “;” 或 “ \g” 或 “ \G” 结尾；
关键字不能被缩写也不能分行；
必须保证所有的（）、单引号、双引号是成对结束的；
必须使用英文状态下的半角输入方式；
字符串型和日期时间类型的数据可以使用单引号（’’）表示；
列的别名，尽量使用双引号（””），而且不建议省略 as 。

# SQL 大小写规范

MySQL 在 windows 环境下是大小写不敏感的，而在在 Linux 环境是大小写敏感的
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名 (或字段名) 、列的别名 ( 字段的别名 ) 是忽略大小写的
数据库名、表名、字段名称、变量名称、视图名称、索引名称、存储名称、触发器名称等都用小写
SQL 关键字、函数、数据类型等都用大写

# MySQL 数据库对象命名规范

数据库、表名不得超过 30 个字符，变量名的长度限制为 29 ；
命名只能包含 A–Z ， a–z ， 0–9 及’ _’ ，共 63 个字符，不能出现其他字符，不建议使用中文；
数据库名、表名、字段名等对象名中间不要包含空格；
同一个 MySQL 软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名；
必须保证字段没有和保留字、数据库系统或常用方法冲突。如果一定要使用，则必须在 SQL 语句中使用 ` （着重号）引起来；
保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。

# 数据库管理

数据库系统（ Database System ，简称简称 DBS u 是指在计算机应用系统中引入数据库后的系统构成。主要由硬件、 OS 、数据库、数据库管理系统、应用系统和数据库用户组成，简称用户可以通过 DBMS 或应用程序操作数据库。

首先由客户端应用程序发起一个数据请求，简称经过 DBMS 把客户端应用程序发来的请求命令转换，简称转换成一个底层数据库能够识别的底层指令，简称然后交给底层数据库，简称数据库经过相应的处理并把查询结果返还给 DBMS ，简称然后 DBMS 将处理结果返回给客户端应用程序。

DBS 与普通软件系统的最大区别在于：普通软件是自己管理数据及数据安全，简称而 DBS 是由 DBMS 来管理数据及其安全性。

一个完整的数据存储需要以下 4 步即可

从系统架构的层次上看，简称 MySQL 数据库系统从大到小依次是：数据库服务器、数据库、数据表、数据表的行与列；

# 创建数据库

创建数据库是在系统磁盘上划分一块区域用于数据的存储和管理。创建数据库时，简称可以指定一些选项

字符集 (Character Set)：gbk、utf8mb4
排序规则 (Collation)：gbk_chinese_ci、utf8mb4_0900_ai_ci

#MySQL支持的所有字符集和排序规则等信息
SHOW CHARACTER SET; 
#查看MySQL默认字符集
show variables like "character_set%";

1
2
3
4

创建数据库有以下三种方式

-- 创建数据库  
create database 数据库名;
-- 创建时判断是否存在
create database if not exists 数据库名;
-- 指定字符集
create database 数据库名 character set utf8;
-- 指定字符集并设置排序规则
CREATE DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则;

1
2
3
4
5
6
7
8

# 查看数据库

在 MySQL 数据库中存在系统数据库和自定义数据库，简称系统数据库是指 MySQL 安装完成后，简称自动创建的 information_schema 、 mysql 、 performance_schema 、 sakila 、 sys 以及 world 六个数据库，简称其中 MySQL 自带创建的数据库就是系统数据库，简称而自定义数据库是由用户自定义创建的数据库。有些老版本的 MySQL 数据库会有一个 test 数据库，简称这是一个测试数据库。有了 sakila 和 world 替代 test 在新版本中也就没有这个数据库了。

information_schema ：主要保存关于 MySQL 服务器维护的所有其他数据库的信息，简称如数据库名称、数据库的表名称、字段名称、表栏的数据类型、访问权限与系统使用的文件夹等；
mysql ：主要存储数据库的用户、权限设置、关键字等 MySQL 本身需要使用的控制和管理信息；
performance_schema ：主要用于收集 MySQL 数据库服务器性能参数，简称且数据库里表的存储引擎均为 PERFORMANCE_SCHEMA ，简称而用户是不能创建存储引擎为 PERFORMANCE_SCHEMA 的表的。
sakila ：这个数据库最初由 MySQLAB 文档团队的前成员 Mike Hillyer 开发，简称旨在提供可用于书籍、教程、文章、样本等示例的标准模式。 sakila 示例数据库还用于突出 MySQL 的最新功能，简称如视图、存储过程和触发器；
sys ：通过视图的形式把 information_schema 、 performance_schema 结合起来，简称查询出更加容易理解的数据存储过程，简称可以执行一些性能方面的配置，简称也可以得到得到一些性能诊断报告内容，简称帮助系统管理员和开发人员监控 MySQL 的技术性能；
world ：是 MySOL 提供的示例数据库，简称包括 3 个数据表，简称分别是 city 、 country 、 countrylanguage 等内容。

-- 查看多个数据库
show databases;
-- 查看指定数据库的创建信息
show create database 数据库名;
-- 查看当前正在使用的数据库
select DATABASE();
-- 查看指定库下的所有表
show tables from 数据库名;
-- 使用数据库
use 数据库名;

1
2
3
4
5
6
7
8
9
10

# 修改数据库

MySQL8.0 版本的数据库默认使用的字符集是 utf8mb4 ，简称一般都不会去更改字符集，简称因为 utf8mb4 支持中文输入。在 MySQL 5.5.3 之后就增加了 utf8mb4 的编码，简称 mb4 即 most bytes 4 。简单说 utf8mb4 是 utf8 的超集并完全兼容 utf8 ，简称能够用四个字节存储更多的字符。如果数据库主要支持中文且数据量很大，简称性能要求也比较高，简称那么就可以选择 gbk 。因为相对于 utf8mb4 而言，简称 gbk 占用的空间较小，简称每个汉字只占 2 个字节。相反，简称如果数据库主要处理英文，简称则建议采用 utf8mb4 。因为 utf8mb4 对中文采用 3 个字节，简称对英文采用 1 个字节，简称 gbk 对中英文都采用 2 个字节。

-- 修改数据库字符
alter database 数据库名 character set utf8;

1
2

# 删除数据库

在 MySQL 使用 SQL 语句中删除指定的数据库有以下两种方式如下

-- 删除指定数据库
drop database 数据库名;
-- 删除时判断是否存在
drop database if exists 数据库名;

1
2
3
4

# 数据类型

数据类型用于在系统中限制或允许该列中存储的数据。在 MySQL 中，数据类型主要根据数据值的内容、大小、精度来选择，为字段选择合适的数据类型对数据库的优化具有重要作用；反之，则可能会严重影响应用程序的功能和性能。MySQL 支持多种数据类型，主要分为 3 种：数值、日期与时间、字符串类型。其中，数值类型包括整数、浮点数和定点数类型：字符串类型包括文本字符串、二进制字符串类型。MySQL 数据类型下表所示。

类型名称	数据类型
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)、BIGINT
浮点数类型	FLOAT、DOUBLE
定点数类型	DECIMAL
日期与时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET
二进制字符串类型	BINARY、VARBINARY、BLOB、BIT

# 整数类型

整数类型分为 TINYINT、SMALLINT、MEDIUMINT、INT (INTEGER) 和 BIGINT 这 5 个类型。

整数类型	说明	字节数	有符号数值取值范围	无符号数值取值范围
TINYINT	非常小的整数	1	-128~127	0~255
SMALLINT	小整数	2	-32768~32767	0~65535
MEDIUMINT	中等大小的整数	3	-8388608~8388607	0~16777215
INT(INTEGER)	普通大小的整数	4	-2147483648~2147473647	0-4294967295
BIGINT	非常大的整数	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

TINYINT: 一般用于枚举数据，如系统设定取值范围很小且固定的场景。
SMALLINT: 一般用于较小范围的统计数据，如统计工厂的固定资产库存数量等。
MEDIUMINT: 一般用于较大整数的计算，如车站每日的客流量等。
INT (INTEGER): 取值范围足够大，一般情况下不用考虑超限问题，用得最多，如商品编号。
BIGINT: 一般只有当处理特别巨大的整数时才会用到，如 “双十一” 的电商交易量，大型门户网站点击量、证券公司衍生产品持仓等。

# 浮点数类型

在实际开发中，很多情况下需要存储的数据是有小数数值的，就要使用到浮点数类型，MySQL 中的浮点数类型主要有两种，分别单精度浮点数 FLOAT 和双精度浮点数 DOUBLE。

浮点数类型可以用 (M,D) 来表示，其中 M 称为精度，表示整数的位数；D 称为标度，表示小数的位数。MySQL 浮点数类型下表所示。

浮点数类型	说明	字节数	有符号数值取值范围	无符号数值取值范圆
FLOAT(M,D)	单精度浮点数	4	$- 3.402823466 E + 38 \sim 1.1754943511 E - 38$	0 和 $1.1754943511E-38\sim 3.402823466E+38$4
DOUBLE(M,D)	双精度浮点数	8	$- 1.7976931348623157 E + 308 \sim 2.2250738585072014 E - 308$	0 和 $- 2.2250738585072014 E - 308 \sim 1.7976931348623157 E + 308$

FLOAT 和 DOUBLE 浮点数类型的区别：FLOAT 占用字节数少，取值范围小；DOUBLE 占用字节数多，取值范固也大。
当浮点数类型不指定数据精度时，系统会默认按照实际计算机硬件和操作系统决定精度；若指定精度超出浮点数类型的数据精度，系统则会自动四舍五入，且正常显示。

# 定点数类型

当项目对精确度要求较高时，则可以使用定点数类型。MySQL 中只有 DECIMAL 一种定点数类型，定点数也可以用 (M,D) 来表示，其中 M 称为精度，表示数据的总位数；D 称为标度，表示数据的小数部分的位数。MySQL 定点数类型下表所示。

定点数类型	字节数	无符号数值取值范固
DECIMAL(M,D),DEC	M+2	有效范围内由 M 和 D 决定

定点数类型是以字符串存储的。
当定点数类型不指定 M 和 D 时，系统则默认为 DECIMAL (10,0)。
若数据的精度超出了定点数类型的精度范围，系统也会进行四舍五入操作，但会有警告。在实际场景当中，该如何进行选择浮点数和定点数类型？MySQL 浮点数和定点数类型的不同场景选择如下。
- 浮点数类型适用于取值范围大，且可容忍微小误差的科学计算场景，如计算化学、分子建模、流体动力学等。
- 定点数类型适用于对精度要求极高的场景，如涉及金额计算的场景。

# 日期与时间类型

日期与时间类型	说明	字节数	日期格式	数值取值范围
YEAR	年	1	YYYY 或 YY	1901~2155
TIME	时间	3	HH:MM:SS	-838:59:59~838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01~9999-12-03
DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00UTC~2038-01-19 03:14:07UTC

# YEAR 类型

YEAR 类型有两种存储格式，分别是以 4 位字符串或数字格式表示和以两位字符串格式

当以 4 位字符串或数字格式表示时，格式为 YYYY, 取值范围为 1901~2155。
当以两位字符串格式表示 YEAR 类型时，表示下表所示。

YY 取值表示范围

169 2001~2069

70~99 1970~1999

日期 / 字符串 "0"（整数 0 或 00） 2000(0000)

YY 取值	表示范围
169	2001~2069
70~99	1970~1999
日期 / 字符串 "0"（整数 0 或 00）	2000(0000)

# DATE 类型

DATE 类型用于表示仅需要日期信息的值，没有时间部分，格式为 YYYY-MM-DD, 其中 YYYY 表示年，MM 表示月，DD 表示日。

若以 YYYYMMDD 格式表示，则会被转换为 YYYY-MM-DD 格式。
使用 CURRENT_DATE () 或 NOW () 函数，会获取当前系统的日期。

# TIME 类型

TIME 类型用于表示只需要时间信息的值，没有日期部分，格式为 HH:MM:SS, 其中 HH 表示小时，MM 表示分钟，SS 表示秒。

如果使用带有 D 的字符串，如 DHH:MM:SS、DHH:MM 等格式，当插入字段时，D (表示天) 会被转换为小时，计算方法为 DX24+HH。
当使用带有冒号并且不带 D 的字特串表示时间时，如 12:34:56，表示当天的时间；不带有冒号的字符串或数字，如 "123456" 或 123456，格式为 "HMMSS" 或 HMMSS, 将被自动转换为 HH:MM:SS 格式进行存储。如果插入一个不合法的字符串或数字，如 12:34:56PM 或 1234567, 则会将其自动转换为 00:00:00 进行存储。因为在 MySQL 中，时间类型的数据是用 HH:MM:SS 格式进行存储和比较的，如果插入的数据不符合这个格式，MySQL 会将其自动转换为 HH:MM:SS 格式，如果无法转换，则会被视为 00:00:00。
使用 CURRENT_TIME () 或 NOW () 函数，会插入当前系统的日期。

# DATETIME 类型

DATETIME 类型在格式上是 DATE 类型和 TIME 类型的结合，是在所有类型中存储空间最大的，格式为 YYYY-MM-DDHH:MM:SS 或 YYYYMMDDHHMMSS, 其中 YYYY 表示年，前 1 个 MM 表示月，DD 表示日，HH 表示小时，后 1 个 MM 表示分钟，SS 表示秒。

插入 DATETIME 类型的字段时，两位数的年份规则符台 YEAR 类型的规则。
与 DATE 类型的存储格式类似，以 YYYYMMDDHHMMSS 格式插入 DATETIME 类型的字段时，会被转换为 YYYY-MM-DDHH:MM:SS 格式，
使用 CURRENT_TIMESTAMP () 或 NOW () 函数，可以向 DATETIME 类型的字段插入当前系统的日期和时间。

# TIMESTAMP 类型

TIMESTAMP 类型与 DATETIME 类型的格式相同，也可以表示日期和时间。但与 DATETIME 类型不同的是，TIMESTAMP 类型是以 UTC (世界标准时间) 格式进行存储的，存储时对当前时区进行转换，查询时再转换回当前时区，也就是在不同地区查询时会显示不同时间。

当插入 TIMESTAMP 类型的字段时，两位数值的年份同样符合 YEAR 类型的规则条件。
TIMESTAMP 类型表示的时间范围要小很多，在插入字段时，不要超出范围，否则 MySQL 会抛出错误。
使用 CURRENT_TIMESTAMP () 或 NOW () 函数，可以向 TIMESTAMP 类型的字段插入当前系统的日期和时间。
如果不赋值或赋值为 NULL，则使用当前系统时间自动赋值

DATETIME 类型占 8 字节，TIMESTAMP 类型占 4 字节，若要求存储范围较大，建议使用 DATETIME 类型。DATETIME 类型反映的是插入时当地的时区，不会因为访问用户时区不同显示的结果发生变化；而 TIMESTAMP 类型反映的是访间用户的时区，不同时区的用户访问会显示不同的结果。使用 DATETIME 和 TIMESTAMP 类型比较大小或计算日期时，TIMESTAMP 类型会更快、更方便。

# 文本字符串类型

MySQL 支持的字符串类型包括文本字符串类型和二进制字符申类型，主要用来存储字符串数据，以及存储图片和声音的二进制数据。MySQL 中的文本字符申类型主要包括 CHAR、VARCHAR、TINYTEXT、TEXT,MEDIUMTEXT、LONGTEXT、ENUM、SET 等。 MySQL 字符串数据类型下表所示，其中 M 表示为其指定的长度。

数据类型	说明	长度范围	字节数
CHAR(M)	固定长度非二进制字符串	0≤M≤255	M
VARCHAR(M)	可变长非二进制字符串	0≤M≤65535	M+1
TINYTEXT	小文本，可变长度	0≤L≤255	L+2
TEXT	文本，可变长度	0≤L≤65535	L+2
MEDIUMTEXT	中等文本，可变长度	0≤L≤16777215	L+3
LONGTEXT	大文本，可变长度	0≤L≤4294967295 (相当于 4GB)	L+4
ENUM	枚举类型，只能有一个枚举字符	0≤L≤65535	1 或 2
SET	串类型一个设置，字符串对象可以有 0 个或多个 SET 成员	0≤L≤64	1,2,3,4 或 8

# CHAR 类型与 VARCHAR 类型

在 MySQL 中，CHAR (M) 类型一般需要先定义字符串长度 M, 若没有指定 M, 则表示长度默认是 1 个字符；而 VARCHAR (M) 类型在定义时必须指定长度 M, 否则会报错。

当检索到 CHAR 类型的数据时，CHAR 类型字段尾部的空格将被删除。
VARCHAR 类型在保存和检索字段数据时，字段尾部的空格仍会保留。

CHAR 类型与 VARCHAR 类型如何进行选择？

当存储的信息较短，速度要求高时，可以使用 CHAR 类型实现，如班级号 (01,02，…)：反之，则选择 VARCHAR 类型实现。
当需要固定长度时，使用 CHAR 类型会更合适，而 VARCHAR 类型可变长的特性就消失，而且还会占多一个长度信息。由于 CHAR 类型平均占用的空间大于 VARCHAR 类型，所以除了简短并且固定长度的情况，其他考虑使用 VARCHAR 类型。
在 InnoDB 存储引擎中，建议使用 VARCHAR 类型。因为对于 InnoDB 数据表，内部的行存储格式并没有区分固定长度和可变长度列，而且主要影响性能的因素是数据行使用的存储总量，由于 VARCHAR 类型是按实际长度进行存储的，这样节省空间，磁盘 I/O 和数据存储总量性能比较好。

# TEXT 类型

当在 TEXT 类型字段中保存或查询数据时，与 VARCHAR 类型相同，不会删除数据尾部的空格。

什么场景使用 TEXT ？

当数据列保存非二进制字符串时，如文章内容、评论等。
在实际开发当中，实际存储长度不确定时，不建议使用 TEXT 类型字段作主键。
当字符数大于 5000 时，建议使用 TEXT 类型，并且新建一个表进行存储，避免影响索引查询效率。

# ENUM 类型

ENUM 类型又叫作枚举类型，它的取值范围需要在创建表时通过枚举方式进行指定，在设置字段值时，ENUM 类型只允许从成员中选取单个值，不能一次选取多个值，其所需要的存储空间由定义 ENUM 类型时指定的成员个数决定。ENUM 类型下表所示，其中 L 表示实际成员个数。

成员个数范围	字节数	成员个数范围	字节数
1≤L≤255	1	256≤L≤65535	2

在定义字段时，若 ENUM 类型字段声明为 NULL, 插入 NULL 为有效值，默认值为 NULL; 若 ENUM 类型字段声明为 NOT NULL, 插入 NULL 为无效值，默认值为 ENUM 类型成员的第 1 个成员。

# SET 类型

SET 类型与 ENUM 类型十分相似，也是一个字符串对象。与 ENUM 类型不同的是，SET 类型一次可以选取多个成员，而 ENUM 类型则只能选取一个。当一个字符串设置字段值时，SET 类型可以取成员个数范围内的 0 个或多个值。SET 类型包含的成员个数和存储空间都不同，具体如下所示，其中 L 表示实际成员个数。

成员个数范围	字节数	成员个数范围	字节数
1≤L≤8	1	25≤L≤32	4
9≤L≤16	2	33≤L≤64	8
17≤L≤24	3

# 二进制字符串类型

在 MySQL 中，二进制字符串类型主要用于存储二进制数据，如图片、音频和视频等。MySQL 支持的二进制字符串类型主要包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，具体如下表所示，其中 M 和 L 都表示值的长度。

数据类型	值的长度	字节数
BIT(M)	1≤M≤64	约为 (M+7)/8
BINARY(M)	M(0≤M≤255)	M
VARBINARY(M)	M(0≤M≤65535)	M+1
TINYBLOB	0≤L≤255	L+1
BLOB	0≤L≤65535(64KB)	L+2
MEDIUMBLOB	0≤L≤16777215(16MB)	L+3
LONGBLOB	0≤L≤4294967295(4GB)	L+4

# BIT 类型

BIT 类型又称作位字段类型，主要存储二进制值，类似 010110。若没有指定长度 M, 默认为 1 位，表示只能存储 1 位二进制值。若分配的值的长度小于 M 位，则在值的左侧用 0 填充，

# BINARY 类型和 VARBINARY 类型

BINARY 类型与 VARBINARY 类型主要用于存储二进制字符串。BINARY (M) 存储固定长度的二进制字符串，如果未指定长度 M, 表示只能存储 1 字节。若存储字段不足 M 字节，将在右侧填充 / 0 以补齐指定长度；反之，超出的部分则会被截断。VARBINARY (M) 存储可变长度的二进制字符串，必须指定 M, 否则会报错。

# BLOB 类型

在 MySQL 中，BLOB 类型包括 4 种类型，分别为 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 类型。BLOB 类型是一个二进制的对象，主要用于存储可变数量的数据，如图片、音频和视频等。

什么时候选择 BLOB 类型？

BLOB 类型存储的是二进制字符串，而 TEXT 类型存储的是非二进制字符串。
BLOB 类型的数据是以字节序列的形式存储的，因此在进行排序和比较时，会基于这些字节的数值进行操作。TEXT 类型的数据则是以字符序列的形式存储的，所以在排序和比较时，会根据字符集规则对这些字符进行操作。
在实际工作中，往往不会在 MySQL 数据库中使用 BLOB 类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到 MySQL 中。

# 表管理

# 创建表

在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的完整性。

数据完整性是指数据的精确性和可靠性，是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的

create tables [if not exists] 表名称(
	字段1 数据类型 [列级别约束条件] [默认值],
    字段2 数据类型 [列级别约束条件] [默认值],
    [表级别约束]
);

1
2
3
4
5

# 非空约束

非空约束 (Not Null Constraint) 是指数据表中某列的内容不允许为空，可以使用 NOTNULL 来表示。如果使用了非空约束，用户在添加数据时没有指定值，数据库系统会报错。

字段名 数据类型 NOT NULL

MySQL 默认所有类型的值都可以是 NULL.
只能某个列单独限定非空，不能组合非空。
空字符串 (') 不等于 NULL,0 也不等于 NULL。

# 主键约束

在 MySQL 中创建数据表时，可以给数据表指定主键，主键又称为主码，是数据表中一列或多列的组合。主键约束 (Primary Key Constraint). 是使用最频繁的约束，主键约束既不能重复，也不能为空，主键能够唯一地标识数据表中的一条记录，可以结合外键定义不同数据表之间的关系，并且加快数据库查询的速度。可以使用 PRIMARY KEY 表示主键，简称 PK。

-- 在定义列的同时指定主键
字段名 数据类型 PRIMARY KEY
-- 在定义完所有列后指定多个主键
[Constraint 约束名] PRIMARY KEY [字段1,字段2,...,字段n]

1
2
3
4

# 外键约束

外键约束 (Foreign Key Constraint), 用于在两个数据表的数据之间建立连接，可以是一列或多列。只要是数据表设计，一定要有外键关系，外键是作用在两个数据表上的约束，限定某个表的某个字段的引用完整性。一个表的外键可以为空，也可以不为空，当外键不为空时，则每个外键的值必须等于另一个表主键的某个值，一个表的外键可以不是本表的主键，但其对应另一个表的主键。在一个表中定义了外键之后，不允许删除另外一个表中具有关联关系的行数据。

外键是作用在两个表中，对于两个具有关联关系的表，又分为主表和从表。

主表（父表）：两个表具有关联关系时，关联字段中主键所在的表为主表。
从表（子表）：两个表具有关联关系时，关联字段中外键所在的表为从表。

[Constraint 外键名] foreign key 字段1[,字段2，字段3] REFERENCES 主表 (主表列名)

# 唯一约束

唯一约束（ Unique Constraint ）和是指数据表中某一个列的数据不允许重复。唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是，唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。

字段名 数据类型 UNIQUE

唯一性约束允许列值为空

# 自增主键

在数据库应用中，需要在每次插入记录时，系统能自动生成字段的主键值。可以通过为表主键添加 “AUTO_INCREMENT” 关键字来实现。当主键定义为自增长后，这个主键的值就不再需要用户输入数据，而由数据库根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

字段名 数据类型 AUTO_INCREMENT

在默认情况下，自增列的初始值为 1 ，每新增一条记录，字段值自动加 1
一个表中只能有一个字段使用自增列约束，且该字段必须为主键或主键的一部分
自增列约束只能是整数类型，例如 TINYINT 、 SMALLINT 、 INT 、 BIGINT 等

# 默认值约束

默认约束（ Default Constraint ）和是给某个字段 / 某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

字段名 数据类型 DEFAULT 默认值

默认值：表示一个具体的值也可以是通过表达式得到的一个值，但必须与该字段的数据类型相匹配
一个表可以有很多的默认值约束，在创建表时为列添加默认值

# 检查约束

检查约束（ Check Constraint ）和是指在进行数据更新前设置一些过滤条件，满足此条件的数据可以实现更新，可以使用 “CHECK” 关键字可以定义检查约束。用于检验输入值，拒绝接受不满足条件的值，减少无效数据的输入。

CHECK(检查约束的条件)

检查约束都是逐个进行过滤，如果在一个数据表上进行了过多的检查约束，在进行数据更新的时候会严重影响程序的性能

# 约束

PRIMARY KEY : 主键约束（Primay Key Coustraint）唯一性，非空性；
PRIMARY KEY AUTO_INCREMENT ：主键自增每增加一条记录，主键会自动以相同的步长进行增长
UNIQUE : 唯一约束（Unique Counstraint）唯一性，可以空，但只能有一个；
NOT NULL : 非空约束（Not Null Counstraint）设置非空约束，该字段不能为空。
DEFAULT : 默认约束 (Default Counstraint) 该数据的默认值；
FOREIGN KEY : 外键约束 (Foreign Key Counstraint) 需要建立两表间的关系；

-- 主键约束 默认包含非空和唯一  一张表只有一个主键
-- 创建  
create table 表名 (列名 数据类型 primary key)
-- 删除pk
alter table 表名 drop primary key;
-- 建表后添加主键
alter table 表名 modify 列名 数据类型 primary key;

-- 自增约束   必须要配合其他约束一起使用 如主键
-- 创建
create table 表名(列名 数据类型 primary key auto_increment);
-- 删除
alter table 表名 modify 列名 数据类型
-- 建表后添加
alter table 表名 modify 列名 数据类型 auto_increment;

-- 唯一约束  不重复
-- 创建
create table 表名(列名 数据类型 unique);
-- 删除
alter table 表名 drop index 列名;
-- 建表后添加
alter table 表名 modify 列名 数据类型 unique;

-- 非空约束  不允许为NULL
-- 创建
create table 表名(列名 数据类型 not null);
-- 删除
alter table 表名 modify 列名 数据类型;
-- 建表后添加
alter table 表名 modify 列名 数据类型 not null;

-- 外键约束 表与表之间有关联性  保证数据的准确性
-- 创建
create table 表名(列名 数据类型 约束) constraint 外键名 foreign key (本表外键列名) references 主表名(主表主键列名);
-- 删除
alter table 表名 drop foreign key 外键名;
-- 建表后添加
alter table 表名 add constraint 外键名 foreign key (本表外键列名) references 主表名(主表主键列名);

-- 外键级联更新 当主表的主键更新时 从表的外键也更新
alter table 表名 add constraint 外键名 foreign key (本表外键列名) references 主表名(主表主键列名) on update cascade;
-- 外键级联删除 当主表的主键删除时 从表的外键也删除
alter table 表名 add constraint 外键名 foreign key (本表外键列名) references 主表名(主表主键列名) on delete cascade;
-- 级联同时更新和删除
alter table 表名 add constraint 外键名 foreign key (本表外键列名) references 主表名(主表主键列名) on update cascade on delete cascade;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

# 查看表

-- 查看数据表结构
describe 表名称;
desc 表名称;
-- 查看建表语句
show create table 表名称;
-- 查询表字符集
show table status from 库名 like '表名';

1
2
3
4
5
6
7

# 修改表

在实际开发当中，还需要根据实际情况来对数据表进行修改，当需要修改数据表的名称，则可以执行 “ALTER TABLE” 语句来实现表名称的修改，具体语法格式如下

-- 重命名表 其中 to 表示为可选参数，使用与否不影响执行结果
alter table 旧表名称 rename [to] 新表名称;
/* 
 添加字段
 FIRST 是可选参数，其作用是将新添加的字段设置为表的第一个字段
 AFTER 是可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面
*/
alter table 表名称 add [column] 新字段名称 字段类型 [FIRST|AFTER 已存在的字段名];
-- 修改字段类型
alter table 表名称 modify 字段名 字段类型;
-- 添加主键约束
alter table 表名称 add constraint 约束名 primary key [字段1，字段2...];
-- 添加外键约束
alter table 表名称 add constraint 约束名 foreign key [字段1，字段2...] references [字段1，字段2...];
-- 添加唯一约束
alter table 表名称 add constraint 约束名 unique(字段名);
-- 添加自增性约束
alter table 表名称 change 字段名 数据类型 unsigned auto_increment ;
-- 添加默认约束
alter table 表名 add constraint 约束名 default 默认值 for 字段名
-- 修改字段名称与类型
alter table 表名称 change 旧字段名 新字段名 新数据类型;
/*
 修改字段排列位置
 FIRST ：其作用是将字段 1 修改为数据表中的第一个字段
 AFTER ：其作用是将字段 1 插到字段 2 的后面
*/
alter table 表名称 modify 字段名1 数据类型 FIRST|AFTER 字段名2;
-- 删除字段
alter table 表名称 drop 字段名;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

# 删除表

删除没有被关联的表就是删除与其他数据表没有关联的表，使用 “DROP TABLE” 语句删除，可以一次删除一个或多个没有被其他表关联的数据表

drop table [if exists] 数据表1[，数据表2...];

数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败，原因是之间删除将破坏表的完整性。如果必须要删除，可以先直接删除与它关联的子表，再删除父表，只是这样就同时删除了两个数据表中的数据，或者将关联表的外键约束取消，再删除父表；

-- 删除表外键约束
alter table 表名称 drop foreign key 外键约束名;
-- 删除表某字段
alter table 表名称 drop 字段名称;

1
2
3
4

# 查询

SELECT { * | < 字段 1, 字段 2...>}
FROM { < 表 1>,< 表 2>...| 视图 }
[WHERE 查询条件 ]
[GROUP BY grouping_columns]
[ORDER BY sorting_columns]
[HAVING secondary_constraint]
[LIMIT count];

1
2
3
4
5
6
7

{* | < 字段 1, 字段 2...> } 包含星号通配符和字段列表。星号通配符表示指定所有字段。如果使用字段名称查询，需要注意多个字段间使用逗号隔开。
{< 表 1>,< 表 2>...| 视图 } 包含数据表和视图。引用多个数据表时，表之间需要用逗号隔开，视图同理。
[WHERE 查询条件] 表示对查询的字段内容增加限制条件，对所查询的字段内容进一步筛选。
[GROUP BY grouping columns] ： grouping columns 意为所指定字段。 GROUP BY 意为对指定的字段进行分组。
[ORDER BY sorting columns] ： sorting columns 意为所指定字段。 ORDER BY 意为对指定的字段进行排序。
[HAVING secondary constraint] ： secondary constraint 意为次要约束。 HAVING 意为查询时满足的第二条件。
[LIMIT count] 意为限定输出的查询结果

-- 查询所有字段
select * from 表名;
-- 查询指定字段
select 字段名[,字段名2...] from 表名;
-- 去除结果中的重复行
select distinct 字段名 from 表名;
-- 判断null
select ifnull(列名,替换的值) from 表名;
-- 别名   AS 关键字为可选项，可以省略不写
select 字段名 [as] 别名 from 表名;

1
2
3
4
5
6
7
8
9
10

# 运算符

# 算术运算符

# 加法和减法

一个整数类型的值对整数（浮点数）进行加法和减法操作，运算结果还是一个整数（浮点数）；
在 MySQL 可以直接 “SELECT 1” ，不需要加 “ FROM 表名” 就可以执行，但在 Oracle 中就需要满足 “SELECT...FROM...” 的结构， Oracle （ SQL 语句需要以分号结尾）不能直接执行 “SELECT 1;” 则需要引入虚拟表（ dual ）来满足结构。

# 乘法和除法

一个数除以整数后，不管是否能除尽，结果都是浮点数，并且结果都保留到小数点后 4 位；
一个数除以 1 后都变成浮点数，数值与原数相等
0 不能作为除数，返回值为 NULL

# 模

求模运算符由百分号（ % ）表示，执行常规的除法运算，返回除法运算得到余数。在 MySQL 中 “%” 和 “ MOD” 作用相同，返回值一样

# 比较运算符

等号两边的值都是整数，则 MySQL 会按照整数来比较两个值的大小
等号两边的值一个是整数，另一个是字符串，则 MySQL 会将字符串转化为数字进行比较
等号两边的值都为字符串，则 MySQL 会按照字符串进行比较
等号两边的值、字符串或表达式中有一个为 NULL ，则比较结果为 NULL

# 安全等于运算符

“<=>” 运算符即使操作的值是 NULL 也可以正确比较。

当两个操作数均为 NULL 时，则返回值为 1 ，而不为 NULL ；当一个操作数为 NULL 时，则返回值为 0 ，而不为 NULL

# 不等于运算符

判断两边的数字、字符串或者表达式的值是否不相等，若不相等则返回 1 ，相等则返回 0

不等于运算符不能判断 NULL 值，若两边的值有任意一个为 NULL ，或两边都为 NULL ，则结果为 NULL

# 小于和大于

如果小于（大于），则返回值为 1 ；否则返回值为 0

不能用于判断空值 NULL，表达式中有一个值为 NULL ，比较结果为 NULL ，则返回值为 NULL

# 非符号类型比较运算符

# 模糊匹配运算符

LIKE 运算符可以使用以下两个通配符 “%” 和 “ _”

“%” ：可以匹配任意多个 ( 可以包含 0 个 ) 字符
“_” ：只能匹配任意一个字符

# 逻辑运算符

# 位运算符

# 运算符的优先级

数字编号越大，优先级越高，优先级高的运算符先进行计算

# where 子句

如果要从众多记录中查询出指定的记录，需要设定查询的条件。设定查询条件应用的是 WHERE 子句。通过它可以实现很多复杂的条件查询。在使用 WHERE 子句时，需要使用一些比较运算符来确定查询的条件

# 集合函数

MySQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。 MySQL 包含了大量并且丰富的函数，在查询中最常见的则是聚合函数。聚合函数最大的特点是它们根据一组数据求出一个值。聚合函数的结果值只根据选定行中非 NULL 的值进行计算， NULL 值被忽略。

# count 函数

COUNT () 函数用于对除了 “ *” 以外的任何参数，返回所选择集合中非 NULL 值的行的数目；对于参数 “*” ，返回所选择集合中所有行的数目，包含 NULL 值的行，没有 WHERE 子句的 COUNT (*) 是经过内部优化的，能够快速地返回表中所有记录总数。

SELECT COUNT(*) FROM 表名;
SELECT COUNT(字段名) FROM 表名;

1
2

count (*) 与 count (1) 区别

当 count () 参数不为列名，为 * 号或数字的时候。COUNT 函数的意思是将括号中的值分配给每一行，然后计算被分配的次数，因此 *、1、2、3 等值都会做相同的操作。

-- 下面sql等价
select count(*) from xxx;
select count(1) from xxx;
select count(2) from xxx;
select count(2000) from xxx;

1
2
3
4
5

# sum 函数

SUM () 函数可以求出表中某个数值类型字段取值的总和

SELECT SUM(字段名) FROM 表名;

# avg 函数

AVG () 函数可以求出表中某个数值类型字段取值的平均值

SELECT AVG(字段名) FROM 表名;

# min、max 函数

MAX () 、 MIN () 函数可以求出表中某个数值类型字段取值的最大值和最小值

SELECT MIN(字段名),MAX(字段名) FROM 表名;

# 排序查询

使用 ORDER BY 关键字能够对查询的结果进行升序或降序排列。在默认情况下， ORDER BY 按升序排列输出结果

SELECT * FROM 表名 ORDER BY 字段名 [ASC|DESC];

ASC 升序默认值如果是多个条件只有当判断值一样时才会执行第二个条件排序
DESC 降序
对含有 NULL 值的列排序时，升序排列会将其放在最前面，反之放在最后

# 分组查询

通过 GROUP BY 子句可以将数据划分到不同的组中，实现对记录进行分组查询。在查询的列需要包含在分组的列中，目的是使查询到的数据没有矛盾。

SELECT 字段|聚合函数 FROM 表名 GROUP BY 字段[,字段2...];

分组前过滤使用 where
分组后过滤使用 having
如果 SELECT 后有聚合函数，则该函数可以不存在于 GROUP BY 子句。常见的聚合函数有 COUNT 、 MAX 、 MIN 等

GROUP_CONCAT() 函数使用方式与聚合函数一致，主要用于分组显示，其作用是将分组中的值连接成一个字符串，并将该字符串返回，所以要结合 GROUP BY 使用

SELECT GROUP_CONCAT(字段名) FROM 表名 GROUP BY 字段名;

# 分页查询

查询数据时，可能会查询出很多记录，但是用户可能实际只需要其中一部分记录，所以 MySQL 提供了 LIMIT 关键字限制查询结果的数量

SELECT * FROM 表名 LIMIT [ 位置偏移量, ] 行数

[位置偏移量] 为可选项，表示 MySQL 从哪一行记录开始显示。如果不指定位置偏移量，则默认从表的第一行记录开始显示，第一条记录的位置偏移量是 0 ，第二条记录的位置偏移量是 1 ，以此类推。当前页 = (当前页 - 1 ) * 每页个数
行数表示返回记录行的数量

# 插入数据

向数据表中添加数据，可以使用 INSERT 语句来完成。

INSERT [INTO] 数据表 [( 字段 1, 字段 2...)] VALUES ( 值 1, 值 2...); --  INTO 关键字在 MySQL 中可以省略。如不指定指定则为全部字段
INSERT INTO 数据表 SET 字段 1 = 值 1, 字段 2 = 值 2......;
-- 插入指定的select语句结果
INSERT [INTO] 数据表 [( 字段 1, 字段 2...)] SELECT 语句;

1
2
3
4

插入值的数据类型和对应开的数据类型一定要匹配，如果类型不同，将无法插入。使用 INSERT...VALUES 语句插入数据有两种情况，第一种情况为表的所有字段均插入数据，第二种情况为表的指定字段插入数据。

# 更新数据

在 MySQL 中使用 UPDATE 语句可以更新表中的记录

UPDATE [IGNORE] 数据表
SET 字段 1= 值 1[, 字段 2= 值2...]
[WHERE 条件表达式 ]
[ORDER BY...]
[LIMIT 行数 ];

1
2
3
4
5

[IGNORE] 在 MySQL 中，通过 UPDATE 语句更新表中多行数据时，如果出现错误，那么整个 UPDATE 语句操作都会被取消，错误发生前更新的所有行将被恢复到它们原来的值。因此，为了在发生错误时也要继续进行更新，可以在 UPDATE 语句中使用 IGNORE 关键字。

# 删除数据

在 MySQL 中，可以使用 DELETE 或者 TRUNCATE TABLE 语句删除表中的一行或者多行数据

DELETE [QUICK] [IGNORE] FROM 数据表
[WHERE 条件表达式 ]
[ORDER BY...]
[LIMIT 行数 ];

1
2
3
4

[QUICK] 为可选项，用于加快部分种类的删除操作速度
[IGNORE] 在 MySQL 中，通过 DELETE 语句删除表中多行数据时，如果出现错误，那么整个 DELETE 语句操作都会被取消，错误发生前更新的所有行将被恢复到它们原来的值。

如果要删除表中所有行，可以通过 TRUNCATE TABLE 语句实现

TRUNCATE [TABLE] 数据表名;

对于参与了索引和视图的表，不能使用 TRUNCATE TABLE 语句来删除数据，而应该使用 DELETE 语句。

编辑

上次更新: 2025/01/01, 10:09:39

← 数据库简介多表查询→

数据库基础

# 数据库基础

# 环境变量

# 命令符指令

# SQL 语句类别

# SQL 语言的基本规范

# SQL 大小写规范

# MySQL 数据库对象命名规范

# 数据库管理

# 创建数据库

# 查看数据库

# 修改数据库

# 删除数据库

# 数据类型

# 整数类型

# 浮点数类型

# 定点数类型

# 日期与时间类型

# YEAR 类型

# DATE 类型

# TIME 类型

# DATETIME 类型

# TIMESTAMP 类型

# 文本字符串类型

# CHAR 类型 与 VARCHAR 类型

# TEXT 类型

# ENUM 类型

# SET 类型

# 二进制字符串类型

# BIT 类型

# BINARY 类型 和 VARBINARY 类型

# BLOB 类型

# 表管理

# 创建表

# 非空约束

# 主键约束

# 外键约束

# 唯一约束

# 自增主键

# 默认值约束

# 检查约束

# 约束

# 查看表

# 修改表

# 删除表

# 查询

# 运算符

# 算术运算符

# 加法和减法

# 乘法和除法

# 模

# 比较运算符

# 安全等于运算符

# 不等于运算符

# 小于和大于

# 非符号类型比较运算符

# 模糊匹配运算符

# 逻辑运算符

# 位运算符

# 运算符的优先级

# where 子句

# 集合函数

# count 函数

# sum 函数

# avg 函数

# min、max 函数

# 排序查询

# 分组查询

# 分页查询

# 插入数据

# 更新数据

# 删除数据

# CHAR 类型与 VARCHAR 类型

# BINARY 类型和 VARBINARY 类型