MySQL数据库基础教程（DDL、DML、DQL…）

MySQL数据库基础教程：DDL、DML、DQL…

---

1 概述

MySQL下载地址：https://dev.mysql.com/downloads/mysql/）
在终端连接MySQL服务器：mysql -u用户名 -p[密码] [-h数据库服务器IP地址 -P端口号]
或可在DataGrip或Idea（集成了DataGrip）的图形化界面中连接、操作MySQL数据库，如下图所示

数据库（DataBase，DB）：存储和管理数据的仓库。

数据库管理系统（DataBase Management System，DBMS）：操纵和管理数据库的大型软件。

关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。使用表存储数据，格式统一，便于维护；使用SQL语言操作，标准统一，使用方便，可用于复杂查询。

SQL（Structured Query Language，操作关系型数据库的编程语言）定义了一套操作关系型数据库的统一标准。

• SQL语句可以单行或多行书写，以分号结尾
• SQL语句可以使用空格/缩进来增强语句的可读性
• MySQL数据库的SQL语句不区分大小写
• 注释：单行注释（-- 注释内容 或＃注释内容）、多行注释（/* 注释内容 */）

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

2 数据库设计——DDL

2.1 数据库操作

1. 查询数据库

/* 查询所有数据库 */
show databases;
/* 查询当前数据库 */
select database();

2. 使用数据库

use 数据库名;

3. 创建数据库

create database [if not exists] 数据库名;

4. 删除数据库

drop database [if exists] 数据库名;

方括号[]中内容表示可选；所有的database(s)可替换成schema(s)

2.2 表操作

设置create_time/update_time：用now()函数获取当前时间

1. 创建表结构

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

2. 查询

/* 查询当前数据库所有表 */
show tables;
/* 查询表结构 */
desc 表名;
/* 查询建表语句 */
show create table 表名;

3. 修改

/* 添加字段 */
alter table 表名 add 字段名 字段类型(长度) [comment 注释] [约束];
/* 修改字段类型 */
alter table 表名 modify 字段名 新字段类型(长度);
/* 修改字段名和字段类型 */
alter table 表名 change 旧字段名 新字段名 字段类型(长度) [comment 注释] [约束];
/* 删除字段 */
alter table 表名 drop column 字段名;
/* 修改表名 */
rename table 表名 to 新表名;

4. 删除表结构

drop table [if exists] 表名;

2.2.1 约束

作用于表中字段上的规则，用于限制存储在表中的数据，保证数据库中数据的正确性、有效性和完整性。

约束	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key（auto_increment 自增）
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default + 默认值
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

2.2.2 数据类型

MySQL中的数据类型主要分为数值类型、字符串类型、日期时间类型，字符串和日期型数据应包含在引号中。

数值类型	大小	有符号范围signed	无符号范围unsigned	描述
tinyint	1	$(-128,127)$	$(10,255)$	小整数值
smallint	2	$(-32768,32767)$	$(0,65535)$	大整数值
mediumint	3	$(-8388608,8388607)$	$(0,16777215)$	大整数值
int	4	$(-2147483648,2147483647)$	$(0,4294967295)$	大整数值
bigint	8	$(-2^{63},2^{63}-1)$	$(0,2^{64}-1)$	极大整数值
float	4	$(-3.402823466\times 10^{38},3.402823466351\times 10^{38})$	$0$ 和 $(1.175494351\times 10^{-38},3.402823466\times 10^{38})$	单精度浮点数值，float(n, m)：整个数字长度为n，小数位为m
double	8	$(-1.7976931348623157\times 10^{308},1.7976931348623157\times 10^{308})$	$0$ 和 $(2.2250738585072014\times 10^{-308},1.7976931348623157\times 10^{308})$	双精度浮点数值，double(n, m)：整个数字长度为n，小数位为m
decimal				小数值（精度更高），decimal(n, m)：整个数字长度为n，小数位为m

字符串类型	大小	描述
char	0~255 bytes	定长字符串，char(n)：最多只能存n个字符，始终占用n个字符的空间，空间换时间
varchar	0~65535 bytes	变长字符串，varchar(n)：最多只能存n个字符，不足n个时按实际长度存储，时间换空间
tinyblob	0~255 bytes	不超过255个字符的二进制数据
tinytext	0~255 bytes	短文本字符串
blob	0~65535 bytes	二进制形式的长文本数据
text	0~65535 bytes	长文本数据
mediumblob	0~16 777 215 bytes	二进制形式的中等长度文本数据
mediumtext	0~16 777 215 bytes	中等长度文本数据
longblob	0~4 294 967 295 bytes	二进制形式的极大文本数据
longtext	0~294 967 295 bytes	极大文本数据

日期类型	大小(byte)	范围	格式	描述
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	混合日期和时间值，时间戳

3 数据库操作——DML

1. 添加数据

/* 指定字段添加数据 */
insert into 表名 (字段名1, 字段名2) values (值1, 值2);	-- 指定的字段与值一一对应
insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);	-- 批量添加数据
/* 全部字段添加数据 */
insert into 表名 values (值1, 值2, ...);
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);	-- 批量添加数据

2. 修改/更新数据

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

3. 删除操作（无法删除某一字段的值，可以使用UPDATE将字段的值置为NULL）

delete from 表名 [where 条件];

4 数据库查询——DQL

4.1 基本查询

/* 查询字段 */
select 字段1, 字段2, 字段3 from 表名;
select * from 表名;	-- 查询所有字段
/* 设置别名 */
select 字段1 [[as] 别名1], 字段2 [[as] 别名2] from 表名;	-- 可以省略关键字as；别名有空格则必须带引号
/* 去重查询 */
select distinct 字段列表 from 表名;

通配符(*)不直观，影响效率，实际开发中尽量少用

4.2 条件查询

select 字段列表 from 表名 where 条件列表;

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
!=或<>	不等于
between ... and ...	在某个范围之内（闭区间）
in (...)	属于in之后的列表中的值之一
like 占位符	模糊匹配：_匹配1个字符，%匹配任意个字符（可以0个）。 例：name like '王__'表示匹配name为王某某（两个下划线）
is null	为null

逻辑运算符	功能
and或&&	并且（多个条件同时成立）
or或||	或者（多个条件任意一个成立）
not或!	非，不是

流程控制：

• if函数：if(条件表达式, true取值, false取值)
• case：(case 表达式 when 值1 then 结果1 [when 值2 then 结果2 ... ] [else result] end)

4.3 分组查询

聚合函数：将一列数据作为一个整体，进行纵向计算，如用select 聚合函数(字段列表) from 表名;可直接查询运算后的结果。null值不参与所有聚合函数运算。

常用的聚合函数	功能	语法说明
count	统计数量	count(字段)：统计该字段非null值数量 count(*)：统计所有行的数量，包括含null值的行（推荐） count(常量)：统计所有行的数量，包括含null值的行，效果同上
max	最大值	max(字段)
min	最小值	min(字段)
avg	平均值	avg(字段)，返回浮点数
sum	求和	sum(字段)

分组查询的字段列表一般为分组字段 聚合函数，执行顺序：where > 聚合函数 > having 。

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

-- 例1：根据性别分组，统计男女员工的数量
select gender, count(*) from tb_emp group by gender;
-- 例2：先查询入职时间在'2015-01-01'以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位
select job, count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;

where与having区别

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

4.4 排序查询

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

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件] order by 字段1 排序方式1, 字段2 排序方式2 ... ;

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

4.5 分页查询

起始索引从0开始，计算式：起始索引$=($页码$-1)\times$每页展示记录数

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

-- 例：从起始索引0开始查询员工数据，每页展示5条记录。
-- 1. 查询第1页员工数据（查询第1页时可省略起始索引0）
select * from tb_emp limit 5;
-- 2. 查询第2页员工数据
select * from tb_emp limit 5, 5;
-- 3. 查询第3页员工数据
select * from tb_emp limit 10, 5;

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

5 多表设计

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构。由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多（多对一）
• 多对多
• 一对一

5.1 一对多

一对多关系中，"一"和"多"的双方常分别称为父表(主表)和子表（参考数据结构中的树）。

案例：部门与员工的关系。

-- 员工
create table tb_emp (
	id int unsigned primary key auto_increment comment 'ID',
	...
	entrydate date comment '入职时间',
	dept_id int unsigned comment '归属的部门ID',		-- 关联的部门ID
	create_time datetime not null comment '创建时间',
	update_time datetime not null comment '修改时间'
) comment '员工表';

-- 部门
create table tb_dept (
	id int unsigned primary key auto_increment comment 'ID',
	name varchar(10) not null unique comment '部门名称',
	create_time datetime not null comment '创建时间',
	update_time datetime not null comment '修改时间'
) comment '部门表';

部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题。原因在于目前上述的两张表，在数据库层面并未建立关联，所以无法保证数据的一致性和完整性。

实现表与表之间关联的方法：外键

1. 物理外键：使用外键约束foreign key定义外键关联另外一张表。在多的一方添加外键，关联另外一方的主键。
   • 缺点
     • 影响增、删、改的效率（需要检查外键关系）
     • 仅用于单节点数据库，不适用与分布式、集群场景
     • 容易引发数据库的死锁问题，消耗性能

-- 创建表时指定外键
create table 表明 (
	字段名 数据类型,
	...
	[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);

-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

3. 逻辑外键：在业务层逻辑中，解决外键关联。可以避免物理外键的问题。

5.2 一对一

一对一关系多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率。可以视为只有一个子表的特殊的一对多关系。

案例：用户与身份证信息的关系。
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）。

5.3 多对多

案例：学生与课程的关系，一个学生可以选修多门课程，一门课程也可以供多个学生选择。
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

6 多表查询

6.1 概述

多表查询：从多张表中查询数据，分为连接查询（内连接、外连接）、子查询。

select * from tb_emp, tb_dept;	-- 该种方式使得两张表的数据两两组合，产生了大量无效数据

笛卡尔积：在数学中，两个集合（$A$ 集合和 $B$ 集合）的所有组合情况。

多表查询的目的就是消除笛卡尔积产生的无效数据，可以根据所设外键指定条件进行查询。指定字段时用表名.字段名格式。

select * from tb_emp, tb_dept where tb_emp.dept_id = tb.dept.id;

6.2 内连接

内连接：相当于查询上图中 $A$、$B$ 交集部分数据

/* 隐式内连接 */
select 字段列表 from 表1, 表2 where 条件 ... ;
/* 显式内连接 */
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ... ;

-- 例：查询员工的姓名及所属部门的名称（注：某些员工不属于任何部门，也某些部门无员工）
-- 1. 隐式内连接
select tb_emp.name, tb_dept.name from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id;
select e.name, d.name from tb_emp e, tb_dept d where e.dept_id = d.id;	-- 可先给表起别名，简化书写
-- 2. 显示内连接
select tb_emp.name, tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb.dept.id

6.3 外连接

外连接根据上图中集合位置可分为以下两类

• 左外连接：查询左表所有数据（包括两张表交集部分数据）
• 右外连接：查询右表所有数据（包括两张表交集部分数据）

/* 左外连接（更常用） */
select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ... ;	-- 表1为左表
/* 右外连接（可改写为左外连接） */
select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ... ;	-- 表2为右表

-- 例1：查询员工表所有员工的姓名及所属部门的名称
select e.name, d.name from tb_emp e left join tb_dept d on e.dept_id = d.id;	-- 左外连接
-- 例2：查询部门表所有部门的名称及对应员工的名称
select e.name, d.name from tb_emp e right join tb_dept d on e.dept_id = d.id;	-- 右外连接

6.4 子查询

子查询/嵌套查询：SQL语句中嵌套的select语句，外部语句可为insert、update、delete、select（最常见）。根据子查询返回的结果可分为如下几类

1. 标量子查询：子查询返回的结果为单个值（数字、字符串、日期等），是最简单的子查询。常用的操作符有=、!=、>、>=、<、<=。

-- 例1：查询部门表中教研部所有员工信息：1. 查询教研部的部门ID；2. 查询该ID的部门下的员工信息。
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
-- 例2：查询司马彰入职后的员工信息：1. 查询司马彰的入职时间；2. 查询在司马彰入职后的员工信息
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '司马彰');

2. 列子查询：子查询返回的结果为一列（可以是多行）。常用的操作符有in、not in等

-- 例：查询教研部和咨询部的所有员工信息：1. 查询教研部和咨询部的ID；2. 查询这些ID的部门下的员工信息
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

3. 行子查询：子查询返回的结果为一行（可以是多列）。常用的操作符有=、!=、in、not in等

-- 例：查询与yohane的入职日期与职位都相同的员工信息：1. 查询yohane的入职日期与职位；2. 查询入职日期和职位与她都相同的员工信息
select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = 'yohane');
-- 优化写法

4. 表子查询：子查询返回的结果为多行多列，常作为临时表使用。常用的操作符有in等

-- 例：查询入职日期是'2024-05-01'之后的员工信息，及其部门名称
select e.*, d.name from (select * from tb_emp where entrydate > '2024-05-01') e, tb_dept d where e.dept_id = d.id;	-- e.*表示表e的所有信息

7 事务

事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

• 事务的控制

  /* 开启事务 */
  start transaction;
  begin;
  /* 提交事务 */
  commit;
  /* 回滚事务 */
  rollback;
  

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

8 数据库优化——索引

索引（index）是帮助数据库高效获取数据的数据结构。

• 优点
  1. 提高数据查询的效率，降低效据库的I0成本。
  2. 通过索引列对数据进行排序，降低致据排序的成本，降低CPU消耗。
• 缺点
  1. 索引会占用存储空间。
  2. 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree案引、Full-Text索引等。我们平常所说的案引，若无特别指明都是指默认的B+Tree结构组织的索引：

• 每一个结点可以存储多个key（有n个key，就有n个指针）
• 所有的数据都存储在叶子结点，非叶子结点仅用于索引数据
• 叶子结点形成了一条双向链表，便于数据的排序及区间范围查询

使用普通的二叉搜索树或红黑树作为索引结构的缺点：大数据量情况下，层级深，检索速度慢。

索引的常用操作如下

/* 为表的字段创建索引 */
create [unique] index 索引名 on 表名 (字段名, ...);	-- 加上unique表示唯一索引
/* 查看索引 */
show index from 表名;
/* 删除索引 */
drop index 索引名 on 表名;

• 主键字段在建表时，会自动创建主键索引。
• 添加唯一约束时，数据库实际上会添加唯一索引。