MySQL 知识点总结

MySQL 基本介绍

MySQL 是一种关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品，由于其体积小、速度快、成本低、开源等优点，使其在 Web 开发中尤其受欢迎。

MySQL 客户端连接

1	`mysql -u [用户名] -p`

示例：

以 root 用户登陆：

1	`mysql -u root -p`

MySQL Command 介绍

MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言。

SQL 语句分类

最常见的 SQL 语句包括以下 4 大类：

DDL（Data Definition Language）
- 用途：对数据库对象（数据库、表、列、索引等）进行创建、删除、修改等。
- 常用关键字：create、drop、alter
DML（Data Manipulation Language）
- 用途：用于添加、修改、删除和查询数据库记录，并检查数据完整性。
- 常用关键字：insert、update、delete
DQL（Data Query Language）
- 用途：用于查询数据。
- 常用关键字：select
DCL（Data Control Language）
- 用途：用于管理用户权限。

DDL 语句

数据库操作

查询所有数据库：
1
show databases;

创建数据库：

1
2
3

create database [数据库名称];
create database if not exists [数据库名称];
create database [数据库名称] default charset [字符集];

删除数据库：

1 2	`drop database [数据库名称]; drop database if exists [数据库名称];`

使用数据库：
1
use [数据库名称];
查询当前数据库：
1
select database();

表操作

查询当前数据库所有表：
1
show tables;
查询表结构：
1
desc [表名];
查询指定表的创建语句：
1
show create table [表名];

创建表：

create table [表名](
    字段1   字段1类型   [约束]  [comment],
    字段2   字段2类型   [约束]  [comment],
    # ...
    字段n   字段n类型   [约束]  [comment]
)comment 表注释;

例如：

# 创建如下员工表
create table demo(
    id      int           comment '编号',
    name    varchar(50)   comment '姓名',
    age     int           comment '年龄',
    gender  varchar(1)    comment '性别'
)comment '员工';

修改表名：

1	`alter table [表名] rename to [新表名];`

删除表：
1
drop table (if exists) [表名];
删除指定表，并重新创建该表（即删除表中所有行）：
1
truncate table [表名];

字段操作

添加字段：

1	`alter table [表名] add [字段名] [类型(长度)] [约束] [comment];`

例如：

1 2	`alter table staff add gender varchar(10) default 'undefined' not null comment '性别';`

如上，给 staff 表格添加 gender 字段

修改字段数据类型

1	`alter table [表名] modify [字段名] [新数据类型];`

修改字段名和字段数据类型：

1	`alter table [表名] change [旧字段名] [新字段名] [类型(长度)] [约束] [comment];`

例如：

1 2	`alter table staff change id s_id int not null comment '员工 ID';`

删除字段：
1
alter table [表名] drop [字段名];

补充

MySQL 表格字段数据类型

基本数据类型：

类型	大小	描述
`tinyint`	1 byte	整数
`smallint`	2 bytes	整数
`mediumint`	3 bytes	整数
`int` 或者 `integer`	4 bytes	整数
`bigint`	8 bytes	整数
`float`	4 bytes	单精度浮点数
`double`	8 bytes	双精度浮点数
`decimal`	依赖于精度和标度的值	小数值

字符串类型：

类型	大小	描述
`char`	0 ~ 255 bytes	定长字符串
`varchar`	0 ~ 65535 bytes	变长字符串
`tinyblob`	0 ~ 255 bytes	不超过 255 个字符的二进制数据
`tinytext`	0 ~ 255 bytes	短文本字符串
`blob`	0 ~ 65535 bytes	二进制形式的长文本数据
`text`	0 ~ 65535 bytes	长文本数据

日期类型：

类型	大小	格式	说明
`date`	3 bytes	`YYYY-MM-DD`	日期值
`time`	3 bytes	`HH:MM:SS`	持续时间值
`year`	1 bytes	`YYYY`	年份值
`datetime`	8 bytes	`YYYY-MM-DD HH:MM:SS`	混合日期和时间值
`timestamp`	4 bytes	`YYYY-MM-DD HH:MM:SS`	混合日期和时间值，时间戳

枚举类型：

关键词：enum

枚举范围需要在创建表格时通过枚举的方式指定，最多允许有 65535 个成员。

例如：
1
2
3
4
5
create table stu( id int primary key auto_increment, name varchar(20) not null, gender enum('男', '女') not null );
其中 gender 字段只能是「男」或者「女」

MySQL 约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据。

分类：

约束	关键字	描述
非空约束	`not null`	字段不能为 `NULL`
唯一约束	`unique`	字段数据都是唯一的，不重复的
主键约束	`primary key`	主键是一行数据的唯一表示，要求非空唯一
默认约束	`default`	保存数据时，如果未指定该字段的值，则采用默认值
检查约束	`check`	保证字段满足某一个条件
外键约束	`foreign key`	用来建立两张表的数据之间的联系，保证数据的一致性和完整性

案例：

根据以下需求完成表的创建：

字段名	字段含义	字段类型	约束条件	约束关键词
`id`	ID 唯一标识	`int`	主键，并且自动增长	`primary key, auto_increment`
`name`	姓名	`varchar(10)`	非空唯一	`not null, unique`
`age`	年龄	`int`	大于 0，不大于 120	`check`
`status`	状态	`char(1)`	默认为 1	`default`
`gender`	性别	`char(1)`	无

create table stu(
    id      int             primary key auto_increment          comment 'ID',
    name    varchar(10)     not null unique                     comment '姓名',
    age     int             check ( age > 0 and age <= 120 )    comment '年龄',
    status  char(1)         default '1'                         comment '状态',
    gender  char(1)                                             comment '性别'
)comment '学生';

外键约束

让多个表的数据产生连接，子表的外键是父表的一个候选键，如下图：

外键示意图

管理外键关联：

创建表时添加外键关联：

1	`[外键名称] foreign key [外键字段名] references [主表(主表列名)] [指定行为];`

创建表后添加外键关系：

1	`alter table [表名] add constraint [外键名称] foreign key [外键字段名] references [主表(主表列名)] [指定行为];`

删除外键关联：

1	`alter table [表名] drop foreign key [外键名称];`

外键删除和更新行为：

行为	说明
`no action`	当父表中删除或更新对应记录时，首先检查是否有对应外键，如果有则不允许删除或更新（默认）
`restrict`	当父表中删除或更新对应记录时，首先检查是否有对应外键，如果有则不允许删除或更新（与 `no action` 一致）
`cascade`	当父表中删除或更新对应记录时，首先检查是否有对应外键，如果有则也删除或者更新外键在子表中的记录
`set null`	当父表中删除或更新对应记录时，首先检查是否有对应外键，如果有则设置子表的外键值为 `NULL`
`set default`	当父表中删除或更新对应记录时，首先检查是否有对应外键，如果有则设置子表的外键值为默认值（Innodb 不支持）

案例：

# 给 staff 表的 dept_id 属性添加关联 dept 表的 id 属性的外键关系，并且将更新行为和删除行为设置为级联
alter table staff
    add constraint fk_staff_dept_id foreign key (dept_id) references dept(id)
    on update cascade on delete cascade;

DML 语句

添加数据：

给指定字段添加数据：

1	`insert into [表名(字段名1, 字段名2, ...)] values(值1, 值2, ...);`

给全部字段添加数据：

1	`insert into [表名] values(值1, 值2, ...);`

批量添加：

1	`insert into [表名] values(值1, 值2, ...), (值1, 值2, ...), ...;`

示例：

1
2
3

insert into staff (s_id, name, age, gender)
values (1002, 'Tom', 24, '男'),
       (1003, 'Frank', 25, '男');

修改数据：

1	`update [表名] set 字段名1 = 值1，字段名2 = 值2, ... [where 条件];`

删除数据：
1
delete from [表名] [where 条件];

DQL 语句

语法：

select
		[字段列表]
from
		[表名列表]
where
		[条件列表]
group by
		[分组字段列表]
having
		[分组后条件列表]
order by
		[排序字段列表]
limit
		[分页参数];

select 之后添加 distinct 可以消除重复元组

查询多个字段：

select [字段1, 字段2, ...] from [表名];

select * from [表名]; # 查询所有字段（不推荐）

select [字段] as [别名] from [表名];

条件查询：

1	`select [字段列表] from [表名] where [条件列表];`

条件：

比较运算符	功能
`>`	大于
`>=`	大于等于
`<`	小于
`<=`	小于等于
`=`	等于
`<>` 或者 `!=`	不等于
`between ... and ...`	在某个范围之内（含最小、最大值）
`in(...)`	在 `in` 之后的列表中的值
`like 占位符`	模糊匹配，`_` 匹配单个字符，`%` 匹配任意个字符
`is null`	是 `NULL`

逻辑运算符	功能
`and` 或 `&&`	并且
`or` 或 `\|\|`	或者
`not` 或 `!`	非

几个案例：

select name, s_id from staff where age >= 25;
# 查找年龄不小于 25 的员工姓名和 ID

select name, s_id from staff where age between 20 and 30;
# 查找年龄在 20 和 30 岁之间的员工姓名和年龄

select name, s_id from staff where gender is null;
# 查找 gender 为 null 的员工姓名和 ID

select name, s_id from staff where age >= 25 and gender = '女';
# 查找年龄不小于 25 的女员工姓名和 ID

select name from staff like '李%';
# 查找姓李的员工

聚合函数

聚合函数是指将一列数据作为一个整体，进行纵向计算。

函数功能

count() 统计数量

max() 最大值

min() 最小值

avg() 平均值

sum() 求和

注：NULL 值不参与除 count() 之外的聚合函数的运算

语法：
1
select 聚合函数(字段列表) from [表名];
几个案例：
1
2
3
4
5
select count(s_id) from staff; # s_id 数量，NULL 值不参与聚合函数计算 select max(age) from staff; # 查找员工年龄最大值

函数	功能
`count()`	统计数量
`max()`	最大值
`min()`	最小值
`avg()`	平均值
`sum()`	求和

分组查询

语法：

1	`select [字段列表] from [表名] where [条件列表] group by [分组字段名] having [分组后过滤条件];`

‼️ where 和 having 的区别：

执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤。
判断条件不同，where 不能对聚合函数进行判断，而 having 可以。

几个案例：

1 2	`select gender, count(*) as numebr from staff group by gender; # 查询男性员工和女性员工的数量`

结果：

1
2

select address, count(*) as address_count from emp where age <= 35 group by address having address_count >= 5;
# 查询不同地区年龄小于等于 35 岁的员工且人数不小于 5 的地区及其员工数量

‼️ 执行顺序：

where
聚合函数
having

排序查询

语法：

1
2

select [字段列表] from [表名] order by [字段1] [排序方案1], [字段2] [排序方案2] ...;
# 多个字段时先看前面的字段，若前面的字段值相同，则看后面的字段规则

排序方案：

asc ：升序排序
desc ：降序排序

几个案例：

1 2	`select name, s_id from staff order by age asc; # 将员工姓名和 ID 按照年龄升序排列`

分页查询

关键词：limit

语法：

1	`select [字段列表] from [表名] limit [起始索引], [查询记录数];`

注：起始索引从 0 开始，若索引为 0，则 0 可以省略

几个案例：

1 2	`select * from emp limit 0, 10; # 展示从索引 0 开始的 10 条记录`

‼️ 多表查询

直接使用 select 语句：

1	`select [字段列表] from [表1], [表2];`

以上语句会直接得到表1 和表2 的笛卡尔积的投影字段。

自然连接：

1	`select [字段列表] from [表1], [表2] where [表1.key] = [表2.key];`

key 在这里为主表的外键

JOIN 语句

cross join ：笛卡尔积

1	`select [字段列表] from [表1] cross join [表2];`

CROSS JOIN 示例

on 关键字：在 join 语句后添加 on [条件列表] 可以筛选特定条件的元组
内连接
- natural join ：自然连接
  1
  select [字段列表] from [表1] natural join [表2];
- inner join ：内连接，join 不加修饰的默认连接，显示左表和右表符合条件的记录
  1
  select [字段列表] from [表1] (inner) join [表2] on [条件列表];
  上图使用 where 语句的等价连接方式叫做隐式内连接。
外连接
- left join ：左连接，即保留左表所有元组，右表无法匹配的属性用 NULL 取代
  1
  select [字段列表] from [表1] left join [表2] on [条件列表];
- right join ：右连接，即保留右表所有元组，左表无法匹配的属性用 NULL 取代
  1
  select [字段列表] from [表1] right join [表2] on [条件列表];
- full join ：全连接，即保留两张表所有元组，无法匹配的属性用 NULL 取代
  1
  select [字段列表] from [表1] full join [表2] on [条件列表];

‼️ 子查询

在 SQL 语句中嵌套 select 语句，称为嵌套查询，又称子查询。

标量子查询：子查询的结果为单个值
列子查询：子查询的结果为一列

常用操作符：in、not in、any、some、all
行子查询：子查询的结果为一行

常用操作符：=、!=、in、not in、exists、not exists

exists (select ...) 用来判断是否有后面的 select 语句是否有返回值。若至少返回一行，则返回 true ，否则返回 false
表子查询：子查询的结果为多行多列

常用操作符：in

根据子查询的位置，又可以分为：

where 之后
from 之后
select 之后

DCL 语句

DCL（Data Control Language）用来管理数据库用户控制数据库的访问权限。

管理用户：

查询用户：
1
2
use mysql; select * from user;

创建用户：

1	`create user ['用户名'@'主机名'] identified by ['密码'];`

示例：

# 创建用户 Jack，只能在当前主机 localhost 访问，密码 123456
create user 'Jack'@'localhost' identified by '123456';

# 创建用户 Tina，可以在任意主机访问该数据库，密码 123456
create user 'Tina'@'%' identified by '123456';

修改用户密码：

1	`alter user ['用户名'@'主机名'] identified with mysql_native_password by ['新密码'];`

删除用户：
1
drop user ['用户名'@'主机名'];

权限控制：

权限	说明
`all, all privileges`	所有权限
`select`	查询数据
`insert`	插入数据
`update`	修改数据
`delete`	删除数据
`alter`	修改表
`drop`	删除数据库 / 表 / 视图
`create`	创建数据库 / 表

查询权限：

1	`show grants for ['用户名'@'主机名'];`

授予权限：

1	`grant [权限列表] on [数据库名.表名] to ['用户名'@'主机名'];`

撤销权限：
1
revoke [权限列表] on [数据库名.表名] to ['用户名'@'主机名'];
1. 多个权限之间，采取逗号分隔
2. 授权时，数据库和表名可以使用 * 进行通配

MySQL 函数

函数是指一段可以直接被另一段程序调用的程序或代码。

字符串函数

函数	功能
`length(s)`	返回字符串的长度
`concat(s1, s2, ...)`	字符串拼接
`lower(str)`	将字符串全部小写
`upper(str)`	将字符串全部大写
`lpad(str, n, pad)`	左填充
`rpad(str, n, pad)`	右填充
`trim(str)`	去掉字符串头部和尾部的空格
`substring(str, start, len)`	返回字符串从 `start` 位置起的 `len` 个长度的子字符串

数据函数

函数	说明
`ceil(x)`	向上取整
`floor(x)`	向下取整
`mod(x, y)`	返回 `x mod y` 的值
`rand()`	返回 0 ~ 1 之间的随机数
`round(x, y)`	求参数 `x` 的四舍五入的值，保留 `y` 位小数

日期函数

函数	说明
`curdate()`	返回当前日期
`curtime()`	返回当前时间
`now()`	返回当前日期和时间
`year(date)`	获取指定 `date` 的年份
`month(date)`	获取指定 `date` 的月份
`day(date)`	获取指定 `date` 的日期
`date_add(date, interval expr type)`	返回一个日期 / 时间值加上一个时间间隔 `expr` 后的时间值
`datediff(date1, date2)`	返回结束时间 `date1` 和起始时间 `date2` 之间的天数
`date_format(date, format)`	`date_format(date, format)` ：用于以不同的格式显示日期 / 时间数据。`date` 参数是合法的日期，`format` 规定日期/时间的输出格式。例如，将 `2023-03-14` 转化为 `2023-03` （即年 - 月）输出，则为： `date_format(target_date, '%Y-%m')`

流程函数

流程函数也是很常用的一类函数，可以在 SQL 语句中实现条件筛选，从而提高语句效率。

函数	功能
`if(value, t, f)`	`value` 为 `true` 返回 `t` ，否则返回 `f`
`ifnull(value1, value2)`	如果 `value1` 不为空，返回 `value1` ，否则返回 `value2`
`case when [val1] then [res1] ... else [default] end`	如果 `val1` 为 `true` ，返回 `res1` ，...，`default` 为默认值
`case [expr] when [val1] then [res1] ... else [default] end`	同上，若 `expr` 的值等于 `val1` ，则返回 `res1`， ...，`default` 为默认值

MySQL 事务操作

事务（Transaction）是一组操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务 4 大特性（ACID）：

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

事务操作

查看 / 设置事务提交方式

1 2	`select @@autocommit; set @@autocommit = 0; # 将事务设置为手动提交`

开启事务

1	`start transaction; # 我们也可以不更改 @@autocommit 参数`

提交事务

1	`commit; # 设置为手动提交后必须要手动提交才能更新数据`

回滚事务
1
rollback; # 回滚 / 撤销事务

数据库

#SQL #MySQL

MySQL 知识点总结

https://goer17.github.io/2023/04/10/MySQL 知识点总结/

作者

Captain_Lee

发布于

2023年4月10日

许可协议

如何在程序中计算定积分？—— Simpson 公式讲解上一篇

素数筛下一篇