MySQL

基础知识

数据库相关概念

名称	全称	简称
数据库	存储数据的仓库，数据是有组织的进行存储	DataBase(DB)
数据库管理系统	操作和管理数据库的软件(如：MySQL)	DataBase Management System(DBMS)
SQL	操作关系型数据库的变成语言，一种统一标准	Structured Query Lanquage (SQL)

SQL语句的分类

• DDL(数据定义语言 Definition定义):对表的增删改
• DML(数据操作语言 Manipulation操纵):增删改
• DQL(数据查询语言 Query查询):查询
• DCL(数据控制语言 Control):grant授权、revoke撤销权限
• TCL(事务控制语言):commit提交事务，rollback回滚事物

通用语法规则

1. 可以多行，以分号结尾。
2. 可以使用空格/缩进
3. 不区分大小写，关键字建议使用大写。
4. 注释方式: 单行注释: --注释内容 #注释内容(MySQL特有) 多行注释: / * 注释内容 * /

数据类型

DDL

连接数据库

1. 在开始菜单找到MySQL 8.0 Command Line Client，并打开
2. 在cmd窗口输入

mysql [-h 127.0.0.1] [-P 3306] -u root -p yourpassword

对库操作

注意:中括号内的语句可以省略

1. 查询所有数据库

SHOW DATABASES;

2. 查询当前数据库

SELECT DATABASE();

3. 创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

4. 删除数据库

DROP DATABASE [IF EXISTS] database_name;

5. 使用/进入数据库

USE database_name;

对表操作

1. 查询表

SHOW TABLES;

2. 查询表结构

DESC table_name;

3. 查询建表语句

SHOW CREATE TABLE table_name;

4. 创建表

CREATE TABLE table_name(
    字段1  数据类型1[COMMENT 字段1注释],
    字段2  数据类型2[COMMENT 字段2注释],
    ...
    字段n  数据类型n[COMMENT 字段n注释]
);[表注释]

5. 修改表

   • 添加字段

   ALTER TABLE table_name ADD 字段名 数据类型;
   

   • 修改数据类型

   ALTER TABLE table_name MODIFY 字段名 新字段名;
   

   • 修改列名和数据类型

   ALTER TABLE table_name CHANGE 旧字段名 新字段名 新数据类型;
   

   • 删除一列

   ALTER TABLE table_name DROP 列名;
   

   • 修改表名

   ALTER TABLE table_name RENAME TO 新表名;
   

6. 删除表

DROP TABLE [IF EXISTS] table_name;

删除表，并重新创建该表

TRUNCATE TABLE table_name;

DML

添加数据

1. 给指定字段添加数据

INSERT INTO table_name (字段名1，字段名2,...) VALUES (值1,值2,..);

2. 添加一行数据

INSERT INTO table_name VALUES (值1,值2,.….);

3. 批量向某字段添加数据

INSERT INTO table_name (字段名1，字段名2,...) VALUES (值1,值2,...),(值1，值2,..….);

4. 批量全部字段添加数据

INSERT INTO table_name VALUES (值1,值2,...) , (值1,值2,...);

注意：

• 插入数据时，字段顺序要与值的顺序一致
• 字符串和日期型数据应该包含在引号中

修改数据

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

删除数据

DELETE FROM table_name [WHERE条件];

注意: 如果没有添加条件，数据会全部删除

DQL

基本查询

SELECT 字段列表 FROM 表名;

• 给返回的字段起一个别名

SELECT 字段1 [AS] '别名1' FROM 表名;

• 去除重复记录

SELECT DISTINCT 字段列表 FROM table_name;

注意

• DISTINCT只能出现在所有字段的正前方
• 当列名有多个的时，所有字段完全一样的才去重复

实例:

--统计岗位数量
SELECT count(DISTINCT job) FROM emp;

条件查询

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

在了解条件查询之前首先我们要知道mysql中一些基本的运算符

运算符分类	运算符	含义
算数运算符	+、-、*、/、%	与Java含义一致
比较运算符	=、>、<、>=、<=、<>或!=	<>也是不等于
比较运算符	IS NULL 、IS NOT NULL、IN、BETWEEN AND	为空、不为空、在条件中、处于条件范围中
逻辑运算符	AND或&&、OR或ll、NOT或!	与、或、非

模糊查询： LIKE + 占位符

占位符	含义
_(下划线)	匹配任意单个字符
%	匹配任意多个字符

示例：

• 查询emp中年龄在15岁到20岁之间的

SELECT * FROM emp WHERE age BETWEEN 15 AND 20;

• 查询emp中姓张的人员信息

--要求找出姓张的即可，直接使用占位符%
SELECT * FROM emp WHERE name LIKE '张%';

聚合函数

将一列数据作为一个整体，进行纵向计算。

函数名	含义
COUNT	统计数量
MAX	最大值
MIN	最小值
AVG	平均值
SUM	求和

语法：

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

NULL 不参与所有的聚合函数运算

案例： 统计emp员工表中员工的数量

SELECT COUNT(*) FROM emp;

分组查询

语法：

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

WHERE与HAVING的区别

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

案例 查询emp年龄小于45的员工，并根据地址分组，获取员工数量大于等于3的地址

--我们拆开解析，首先先查询年龄小于45的员工的数量
SELECT COUNT(*) FROM emp WHERE age < 45;
--然后我们根据地址的不同对齐分组
SELECT COUNT(*) FROM emp WHERE age < 45 GROUP BY address;
--最后我们筛选员工数大于等于3的地址
SELECT COUNT(*) FROM emp WHERE age < 45 GROUP BY address HAVING COUNT(*) >= 3;

执行顺序：where > 聚合函数 > having 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

语法:

SELECT 字段列表 FROM table_name ORDER BY 字段1 排序方式1，字段2 排序方式2;

排序方式:

• ASC 升序(默认方式)
• DESC 降序

注意:多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

语法:

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

注意:

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

DCL

DCL英文全称是Data ControlLanguage(数据控制语言)，用来管理数据库用户、控制数据库的访问 权限。 这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员)使用。

管理用户

1. 查询用户

USE mysql;
SELECT * FROM user;

2. 创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
# 如果要在任何主机都可以访问到数据库则使用'用户名'@'%'

3. 修改用户密码

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

4. 删除用户

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

权限控制

权限类型	具体权限	权限说明
所有权限	ALL, ALL PRIVILEGES	拥有所有权限
查询权限	SELECT	查询数据
插入权限	INSERT	插入数据
修改权限	UPDATE	修改数据
删除权限	DELETE	删除数据
表结构修改权限	ALTER	修改表
删除对象权限	DROP	删除数据库/表/视图
创建对象权限	CREATE	创建数据库/表

1. 查询权限

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

2. 授予权限

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

3. 撤销权限

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

多表查询

首先了解改章的表结构

CREATE TABLE student (  
    id INT PRIMARY KEY,  
    name VARCHAR(50),  
    class_id INT,  
    -- 其他字段...  
    FOREIGN KEY (class_id) REFERENCES class(id)  
);
CREATE TABLE class (  
    id INT PRIMARY KEY,  
    name VARCHAR(50),  
    -- 其他字段...  
);

表与表的关系

1. 一对多
   • 一个班级有多个学生
   • 一个学生对应一个班级
2. 多对多
   • 一个学生可以选多门课
   • 一门课程可以被多个学生选择
3. 一对一
   • 学生与学生详细信息表
   • 多用与单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在另一张表中

例如：我们在查询学生和其所在班级两张表时如果直接使用

SELECT * FROM student , class;

我们会发现，返回的结果数是student中条数乘以class中的条数。 这种现象我们称之为笛卡尔积。 因此我们需要消除无用数据，并保留有效数据。

查询方式的分类

多表查询分为：

• 连接查询 查询A、B两个表的交集部分数据，叫做内连接。 查询A的全部数据，并查询出A、B的交集部分数据，叫做左外连接 同理还有B的右外连接 若表与自身的连接查询，叫做自连接。

注意：自连接由于都是这一个表，所有我们需要给它起别名

• 子查询

内连接

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件...;

案例：使用隐式内连接的查询学生名称和其班级名称

SELECT student.name,class.name FROM student , class WHERE student.class_id = class.id;

显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

同样的案例我们使用显式内连接的方式

SELECT student.name,class.name FROM student INNER JOIN class ON student.class_id = class.id;

外连接

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

案例：查询所有学生及其所在班级的名称，即使某些学生没有分配到班级

SELECT student.name FROM student LEFT OUTER JOIN class ON student.class_id = class.id;

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON条件...;

• 注意： 在MySQL中，实际上并不直接支持右外连接RIGHT OUTER JOIN。但是，你可以通过调整查询中的表顺序并使用左外连接LEFT OUTER JOIN来模拟右外连接的效果。

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

案例：查找哪些学生在同一个班级

SELECT 
	s1.name AS student1, 
	s2.name AS student2 
FROM student s1 
JOIN student s2 
ON s1.class_id = s2.class_id
WHERE s1.id < s2.id;--避免重复和自身匹配

联合查询（UNION查询）

概念： 允许将两个或更多的SELECT语句的结果集组合成一个结果集。
每个SELECT语句在UNION运算符之前，并且所有SELECT语句必须拥有相同列。

分类：

• UNION ALL会将全部的数据直接合并在一起
• UNION会对合并之后的数据去重。

案例：我们想要查询所有学生的名字和所有班级的名字，并将这两个结果集合并成一个单一的列表

-- 查询所有学生的名字  
SELECT name  
FROM student  
UNION  
-- 查询所有班级的名字  
SELECT name  
FROM class;

子查询/嵌套查询

--外部可以是INSERT UPDATE SELECT SELECT * FROM 表1 WHERE 字段1 = (SELECT 字段1 FROM 表2);

案例:

-- 找出至少有一个学生所在的班级  
SELECT name  
FROM class  
WHERE id IN (  
    SELECT DISTINCT class_id  
    FROM student  
);

根据子查询结果不同又分为

• 标量子查询(子查询结果为单个值)
• 列子查询(子查询结果为一列)
• 行子查询(子查询结果为一行)
• 表子查询(子查询结果为多行多列列)

操作符

• ANY(SOME) 子查询返回列表中，有一个满足即可
• ALL必须满足所有

函数

数值函数

• 向上取整

CEIL(x)

• 向下取整

FLOOR(x)

• 返回x/y的模

MOD(x,y)

• 返回0~1内的随机数

RAND()

• 求参数x的四舍五入的值,并保留y位的小数

ROUND(x,y)

字符串函数

函数名称	函数格式	函数功能
字符串拼接函数	CONCAT(S1,S2....Sn)	将S1，S2，... ，Sn拼接成一个字符串
转小写函数	LOWER(str)	将字符串str全部转为小写
转大写函数	UPPER(str)	将字符串str全部转为大写
左填充函数	LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，使其达到n个字符串长度
右填充函数	RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，使其达到n个字符串长度
去除首尾空格函数	TRIM(str)	去掉字符串str头部和尾部的空格
字符串截取函数	SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

日期函数

• 返回当前日期

CURDATE()

• 返回当前时间

CURTIME()

• 返回当前日期和时间

NOW()

• 获取指定‘date’的年份

YEAR(date)

• 获取指定‘date’的月份

MONTH(date)

• 获取指定‘date’的日期

DAY(date)

• 返回一个日期/时间值加上一个时间间隔‘exper’后的时间值

DATE_ADD(date, INTERVAL expr type)

• 返回起始时间‘date1’和结束时间‘date2’之间的天数

DATEDIFF(date1,date2)

流程函数

• 如果value为true，则返回t，否则返回 f

lF(value , t , f)

• 如果value1不为空, 返回value1，否则返回value2

IFNULL(value1 , value2)

• 如果val1为true，返回res1，...否则返回default默认值

CASE WHEN [vall] THEN [res1] ...ELSE [default] END

• 如果expr的值等于val1，返回res1,...否则返回default默认值

CASE [expr] WHEN [vall] THEN [res1] ...ELSE [default] END

约束

概念

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

分类

名称	关键字	含义
非空约束	NOT NULL	改字段不能为NULL
唯一约束	NUIQUE	该字段不能有重复内容
主键约束	PRIMARY KEY	主键是一行数据的唯一标识，要求非空且唯一
默认约束	DEFAULT	保存数据时，如果未指定该字段的值，则采用默认值
检查约束	CHECK	(8.0.16版本之后)保证字段值满足某一个条件
外键约束	FOREIGN KEY	下面着重讲解

注意：约束作用与字段，在创建/修改表时添加约束

下面是使用例子

CREATE TABLE table_name (  
    column1 datatype PRIMARY KEY,  
    column2 datatype UNIQUE,
    column3 datatype NOT NULL,
    column4 datatype CHECK (condition),
    column5 datatype DEFAULT value,  
    column6 datatype,  
    ...  
);

外键约束

作用：让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

添加外键

• 添加外键方式一

CREATE TABLE table_name(
	字段名 数据类型,
	[CONSTRAINT][外键名称] FOREIGN KEY(外键字段名)
	REFERENCES 主表名(主表列名)
);

• 方式二

ALTER TABLE table_name ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);

删除外键 语法

ALTER TABLE table_name DROP FOREINGN KEY 外键名;

外键 删除 / 更新 行为

NO ACTION / RESTRICT

当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。

CASCADE

当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。

SET NULL

当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null (这就要求该外键允许取null)

SET DEFAULT

父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)

使用方法

ALTER TABLE table_name 
	ADD CONSTRAINT 外键名称 
	FOREIGN KEY (外键字段) 
	REFERENCES 主表名(主表字段名) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE;

下面是这段SQL的详细讲解

ALTER TABLE table_name:

这条命令用于修改已存在的表（table_name）。

ADD CONSTRAINT 外键名称

这条命令用于向表中添加一个新的约束，并且为这个约束指定一个名称（外键名称）。这里的“外键名称”是一个占位符，你需要将其替换为具体的外键约束名称。

FOREIGN KEY (外键字段)

这部分指定了哪个字段将被用作外键。外键字段是一个占位符，你需要将其替换为实际的外键字段名。

REFERENCES 主表名(主表字段名)

这部分指定了外键将引用哪个表（主表名）和哪个字段（主表字段名）。主表名和主表字段名都是占位符，你需要将它们替换为实际的主表名和主表字段名。

ON UPDATE CASCADE

它指定了当主表中的主表字段的值被更新时，外键表中对应的外键字段的值也应该被更新。CASCADE表示级联操作，即主表字段的更新会自动传播到外键字段。

ON DELETE CASCADE

它指定了当主表中的记录被删除时，外键表中所有引用该记录的外键字段所在的记录也应该被删除。同样，CASCADE表示级联操作，即主表记录的删除会自动传播到外键表。

事务

概念

事务是一组操作的集合，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作出错，要么同时成功，要么同时失败。

在 MySQL 中，默认情况下事务是自动提交的。这意味着当执行一条数据操作语言（DML）语句，如 INSERT、UPDATE 或 DELETE 时，MySQL 会立即隐式地提交事务。这种自动提交的行为在某些情况下可能会导致数据不一致或意外的结果。

查看 / 设置事务提交方式：

• SELECT @@autocommit;：用于查看当前的自动提交设置。如果返回值为 1，表示自动提交开启；如果返回值为 0，表示自动提交关闭。
• SET @@autocommit=0;：设置自动提交为关闭状态。关闭自动提交后，需要手动使用 COMMIT 提交事务或 ROLLBACK 回滚事务来控制事务的结束。

开启事务：

• START TRANSACTION 或 BEGIN;：这两个语句用于开启一个新的事务。在事务开启后，后续的数据库操作将被视为事务的一部分，直到事务被提交或回滚。

提交事务：

• COMMIT;：当事务中的所有操作都成功执行后，可以使用 COMMIT 语句提交事务。提交事务后，事务中对数据库的更改将被永久保存。

回滚事务：

• ROLLBACK;：如果在事务执行过程中出现错误或需要撤销事务中的操作，可以使用 ROLLBACK 语句回滚事务。回滚事务将撤销事务中对数据库的所有更改，使数据库恢复到事务开始之前的状态。

事务的特性

1. 原子性 (Atomicity): 事务是不可分割的最小操作单元，要么全部成功，要么全部失败。这意味着如果事务中的任何一个操作失败，整个事务都将被回滚，就好像这个事务从未发生过一样。例如，在一个银行转账的事务中，从一个账户扣除金额和向另一个账户增加金额这两个操作必须同时成功或同时失败，不能出现只完成了一部分操作的情况。
2. 一致性 (Consistency): 事务完成时，必须使所有的数据都保持一致状态。这意味着事务的执行必须遵循数据库的完整性约束和业务规则。例如，如果一个数据库中有一个表的字段定义了唯一约束，那么在事务中插入的数据不能违反这个唯一约束。如果事务中的操作违反了一致性要求，数据库系统将回滚事务，以确保数据的一致性。
3. 隔离性（Isolation）: 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。不同的事务之间应该相互隔离，互不干扰。然而，由于并发事务的存在，可能会引发一些问题，如脏读、不可重复读和幻读。为了解决这些问题，数据库系统提供了不同的事务隔离级别。
4. 持久性（Durability）︰ 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。这意味着即使在数据库系统发生故障的情况下，已经提交的事务的结果也不会丢失。数据库系统通常会使用日志和备份等技术来保证事务的持久性。

并发事务引发的问题

1. 脏读 一个事务读到另外一个事务还没有提交的数据。例如，事务 A 修改了一条数据，但还没有提交，此时事务 B 读取了这条被事务 A 修改的数据。如果事务 A 最终回滚了这个修改，那么事务 B 读取到的数据就是 “脏数据”，因为它实际上并不应该存在于数据库中。
2. 不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。例如，事务 A 读取了一条数据，然后事务 B 修改了这条数据并提交，此时事务 A 再次读取这条数据，发现两次读取的数据不同。
3. 幻读 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。例如，事务 A 按照某个条件查询数据，没有找到符合条件的数据行，然后事务 B 插入了一条符合事务 A 查询条件的数据并提交，此时事务 A 再次按照相同的条件查询数据，却发现了刚才事务 B 插入的数据行。

事务隔离级别，解决以上问题

1. Read uncommitted（读未提交） 在这个隔离级别下，一个事务可以读取另一个事务未提交的数据，这会导致脏读、不可重复读和幻读问题都可能发生。虽然这个隔离级别可以提供最高的并发性能，但由于数据的不一致性风险较高，一般不建议在实际应用中使用。
2. Read committed（读已提交） 在这个隔离级别下，一个事务只能读取另一个事务已经提交的数据，可以避免脏读问题，但不可重复读和幻读问题仍然可能发生。这个隔离级别在很多数据库系统中是默认的隔离级别，它提供了一定程度的数据一致性和较好的并发性能。
3. Repeatable Read (MySQL 默认) 在这个隔离级别下，一个事务在执行过程中多次读取同一数据时，会得到相同的结果，即可以避免不可重复读问题。但是，幻读问题仍然可能发生。MySQL 使用多版本并发控制（MVCC）技术来实现这个隔离级别，通过保存数据的多个版本，使得不同的事务可以看到不同版本的数据，从而保证了事务的隔离性。
4. Serializable（可串行化） 在这个隔离级别下，事务之间是完全隔离的，就像事务是依次执行的一样，可以避免脏读、不可重复读和幻读问题。但是，这个隔离级别会极大地降低数据库的并发性能，因为它需要对事务进行严格的锁定，以确保事务之间的完全隔离。

查看事务隔离级别：

• SELECT @@TRANSACTION_ISOLATION;：用于查看当前数据库的事务隔离级别。 设置事务隔离级别：
• SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READUNCOMMITTED |READCOMMITTED |REPEATABLE READ|SERIALIZABLE };：可以使用这个语句设置事务隔离级别。其中，SESSION 表示只对当前会话生效，GLOBAL 表示对所有新连接的会话生效。选择合适的事务隔离级别需要在数据一致性和并发性能之间进行权衡。在实际应用中，需要根据具体的业务需求和场景来选择合适的事务隔离级别，以达到最佳的性能和数据一致性。