MySQL

一、数据库概述

1 基础

数据库（DataBase）：DB，存储数据的仓库，数据是有组织的进行存储。

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

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

Mysql有商业版和免费版，商业版官方会提供技术支持。

关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。Mysql是关系型数据库。

关系型数据库特点：

使用表存储数据，格式统一，便于维护。

使用SQL语言操作，标准统一，使用方便。

工作流程：Client链接DBMS，利用DBMS操作DB。

2 下载和管理

official::MySQL Community Downloads

安装：

Type and Networking：next

Authentication Method：next

Accounts and Roles：设置root的用户密码。

Windows Service：next

Apply Configuration：next

服务管理：win + R——services.msc——MySQL80（Windows Service设置的名字）

命令：

启动：net start mysql80

停止：net stop mysql80

连接：

工具：开始菜单——MySQL 8.0 Command Line Client - Unicode）——输入密码。

命令：mysql [-h 127.0.0.1] [-P 3306] -u root -p（IP和PORT不指定就是默认127.0.0.1:3306）

配置环境变量：环境变量——系统变量——Path——...\Mysql\Mysql Server 8.0\bin\

3 图形化界面

navicat：official::Download Navicat

datagrip：official::Download DataGrip

二、SQL语句

1 概念

SQL通用语法：

SQL语句可以单行或多行书写，以分号结尾。

SQL语句可以使用空格 / 缩进来增强语句的可读性。

MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

注释:

单行注释：-- 注释内容 或 # 注释内容(MySQL特有)（符号和注释间要有空格）

多行注释：/*注释内容*/

SQL分类：

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象（数据库，表，字）。

DML（Data Manipulation Language）：数据操作语言，用来对数据库表中的数据进行增删改。

DQL（Data Query Language）：数据查询语言，用来查询数据库中表的记录。

DCL（Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限。

2 DDL

2.1 库操作

IF NOT EXISTS用来对数据库是否存在进行判断，加上的话会自己判断，不加的话如果库存在再创建则会报错，IF EXISTS同理。

指定字符集合时，最好不要用utf8，而是使用utf8mb4。因为utf8默认每个字符3个字节，而有的特殊字符是4个字节，所以要用utf8mb4。

utf8mb4_unicode_ci：是针对 utf8mb4 字符集的一个特定的排序规则，它提供了较为通用且对非英语字符友好的排序和比较，通常被认为是较为包容和准确的选择。

/* 基础语法 */
# 查询
-- 查询所有数据库
SHOW DATABASE;

-- 查询当前数据库
SELECT DATABASE();


# 创建（IF NOT EXISTS：不存在才创建）
CREATE DATABASE [IF NOT EXISTS] 库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];


# 删除（IF EXISTS：存在才删除）
DROP DATABASE [IF EXISTS] 库名;


# 使用
USE 库名;

/* 案例 */
-- 如果new不存在则创建
CREATE DATABASE IF NOT EXISTS new DEFAULT CHARSET utf8mb4 COLLATE  utf8mb4_unicode_ci;

-- 如果new存在则删除
DROP DATABASE IF EXISTS new;

-- 使用new库
USE new;

2.2 表操作

字段类型：MySQL 数据类型 | 菜鸟教程 (runoob.com)

常用类型：

TINYINT：1字节（-128 - 127）。

TINYINT UNSIGNED：1字节（0 - 255）。UNSIGNED表示无符号。

INT：4字节。

DECIMAL(M, D)：小数值。M（精度）表示整个数长度，D（标度）是小数部分。

CHAR(n)：定长字符串，长度n。假设n=10，即使只存了一个字符，此时存储长度也是10。性能好但是需要额外消耗空间。

VARCHAR(n)：变长字符串，长度n。假设n=10，如果存了一个字符，此时存储长度就是1。性能差但省空间。

DATE：年月日。

DATETIME：年月日时分秒。

TIMESTAMP：时间戳，只能到2038年。

/* 基础语法 */
# 查询
-- 查询当前库中所有表
SHOW TABLES;

-- 查询表结构
DESC 表名;

-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;


# 创建表（最后一个字段没有逗号）
CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释],
    字段2 字段2类型[COMMENT 字段2注释],
    ...
    字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];


# 修改表
-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;


# 删除表
-- 删除表
DROP TABLE [IF EXISTS] 表名;

-- 删除指定表并重新创建
TRUNCATE TABLE 表名;


# 修改字段
-- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

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

-- 修改字段名和类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度）[COMMENT 注释] [约束];


# 删除字段
ALTER TABLE 表名 DROP 字段名;

/* 案例 */
-- 创建员工表
create table emp (
	id int comment'编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    entrydate date comment '入职时间'
) comment '员工表';

-- 添加昵称
alter table emp add nickname varchar(20) comment '昵称';
-- 修改字段名和类型
alter table emp change nickname username varchar(30) comment '昵称';

3 DML

3.1 增删改

插入数据注意：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

字符串和日期型数据应该包含在引号中。

插入的数据大小，应该在字段的规定范围内。

修改数据注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据注意：

DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

DELETE语句不能删除某一个字段的值（可以使用UPDATE将某一字段设置为null）。

/* 基础语法 */
# 添加数据
-- 给指定字段添加数据
INSERT INTO 表名(字段名1，字段名2, ...) VALUES(值1,值2, ...);

-- 给全部字段添加数据
INSERT INTO 表名 VALUES(值1, 值2, ...);

-- 批量添加数据
INSERT INTO 表名(字段名1,字段名2, ...) VALUES(值1, 值2, ...),(值1，值2 , ...),(值1, 值2, ...);
INSERT INTO 表名 VALUES(值1,值2, ...),(值1,值2, ...),(值1,值2, ...);


# 修改数据
UPDATE 表名 SET 字段名1=值1, 字段名2=值2, ...[WHERE 条件];


# 删除数据
DELETE FROM 表名 [WHERE 条件]

/* 案例 */
# 插入数据
-- 给指定字段添加数据
INSERT INTO emp(id, workno, name, gender, age, idcard, entrydate)
VALUES(1, 'E001', '张三', 'M', 30, '123456789012345678', '2021-01-01');

-- 给全部字段添加数据
INSERT INTO emp
VALUES(2, 'E002', '李四', 'F', 28, '234567890123456789', '2021-03-02');

-- 批量添加数据
INSERT INTO emp(id, workno, name, gender, age, idcard, entrydate)
VALUES(3, 'E003', '王五', 'M', 32, '345678901234567890', '2021-01-03'),
      (4, 'E004', '赵六', 'F', 26, '456789012345678901', '2021-01-04');
      
INSERT INTO emp
VALUES(5, 'E005', '孙七', 'M', 35, '567890123456789012', '2021-01-05'),
      (6, 'E006', '周八', 'F', 24, '678901234567890123', '2021-01-06');
      
      
# 修改数据
update emp set name="张三" where id=1;
update emp set name="张三",gender="女" where id=1;
update emp set entrydate="2008-01-01";


# 删除数据
delete from emp where gender="女";
delete from emp;

4 DQL

4.1 基本查询

查询语法的执行的顺序：FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY、LIMIT

/* 基础语法 */
# 查询语法
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数;


# 基本查询
-- 查询多个字段
SELECT 字段1,字段2, ... FROM 表名;
-- 查询全部字段
SELECT * FROM 表名;

-- 设置别名输出
SELECT 字段1 [AS 别名1],字段2 [AS 别名2], ... FROM 表名;

-- 去重
SELECT DISTINCT 字段列表 FROM 表名;

/* 案例 */
# 基本查询
select * from emp;
select name,workno,age form emp;
select distinct gender as "性别" from emp;

4.2 条件查询

Mysql 条件查询

复杂条件查询：

<>：就是!=，表示不等于

BETWEEN A AND B：在A-B个范围（含最大和最小值）

IN (...)：在列表中的值，多选一，列表可以是查询结果（也就是嵌套select）

LIKE 占位符：模糊匹配（_匹配单个字符，%匹配任意字符）

IS NULL：空。

/* 基础语法 */
# 条件查询
SELECT 字段列表 FROM 表名列表 WHERE 条件列表

/* 案例 */
# 条件查询
select * from emp where age <= 80;
select * from emp where age <> 80;

select * from emp where idcard is not null;

select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20;

select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in (18, 20, 40);

-- 查询名字是两个字的员工
select * from emp where name like '__';

-- 查询省份证最后一位是X的员工信息
select * from emp where idcard like '%X';

4.3 聚合函数

常用聚合函数：count、max、min、avg、sum。

注意：所有的null不参与聚合函数的运算。

/* 基础语法 */
# 聚合函数
SELECT 聚合函数(字段列表) FROM 表名;

/* 案例 */
# 聚合函数
-- 统计员工总数（用主键或*，否则null可能会影响）
select count(id) from emp;

select avg(age) from emp;
select max(age) from emp;
select min(age) from emp;
select sum(age) from emp;

4.4 分组查询

where与having区别：

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意：

执行顺序：where > 聚合函数 > having。

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

/* 基础语法 */
# 分组查询
SELECT 字段列表 FROM 表名列表 [WHERE 条件列表] GROUP BY 分组字段列表 [HAVING 分组后条件列表];

/* 案例 */
# 分组查询
select gender, count(*) from emp group by gender;

-- 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress,　count(*) from emp where age < 45 group by workaddress having count(*) >=　3;

4.5 排序查询

排序方式：

ASC：升序（默认值）

DESC：降序

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

/* 基础语法 */
# 排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

/* 案例 */
# 排序查询
select * from emp order by desc;
select * from emp order by asc, entrydate desc;

4.6 分页查询

注意：

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

如果查询的索引是0，直接简写为limit 10。

/* 基础语法 */
# 分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数量;

/* 案例 */
# 分页查询
select * from emp limit 0, 10;
select * from emp limit 10;

select * from emp limit 10, 10;

5 DCL

5.1 用户设置

用户信息都存在系统user表中。

User表：

Host：可以访问的主机地址，localhost表示只能本机访问，不能远程访问。设置任意主机访问填%

User：用户

/* 基础语法 */
# 查询用户
USE mysql;
SELECT * FROM user;


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


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


# 删除用户
DROP USER '用户名'@'主机名';

/* 案例 */
-- 创建用户 itcast，只能够在当前主机localhost访问，密码123456
create user 'itcast'@'localhost' identified by '123456';

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

-- 修改用户 heima 的访问密码为1234
alter user 'heima'@'%' identified with mysql_native_password by '1234';

-- 删除itcast@localhost用户
drop user 'itcast'@'localhost'

5.2 权限控制

数据操作权限：

ALL, ALL PRIVILEGES：授予用户对特定对象（如数据库、表等）的所有权限。

SELECT：SELECT 权限允许用户执行 SELECT 语句，即查询指定表中的数据。

INSERT：允许用户向表中插入新的数据行。

UPDATE：允许用户修改表中已存在的数据。

DELETE：允许用户删除表中的数据行。

结构操作权限：

CREATE：允许用户创建新的数据库、表、索引、视图、触发器、存储过程等数据库对象。

ALTER：允许用户更改现有数据库对象的结构，如添加或删除列、修改列属性、重命名表等。

DROP：允许用户删除数据库对象，如删除表、索引、视图等。

管理权限：

GRANT OPTION：允许用户将自己拥有的权限进一步授予其他用户。

REFERENCES：允许用户在创建外键约束时引用其他表的键。

INDEX：允许用户创建或删除表上的索引。

TRIGGER：允许用户创建或删除触发器。

EXECUTE：允许用户执行存储过程和函数。

特殊权限：

CREATE TEMPORARY TABLES：允许用户创建临时表。

LOCK TABLES：允许用户锁定表，以便进行并发控制或备份操作。

SHOW VIEW：允许用户查看视图的定义。

EVENT：允许用户管理事件调度器中的事件。

FILE：允许用户读取、写入或删除服务器上的文件（可能有安全风险，通常不推荐使用）。

系统权限：

SUPER：提供高级管理权限，如关闭服务器、更改全局系统变量、执行 KILL 命令等。

PROCESS：允许用户查看服务器中正在运行的线程和进程信息。

RELOAD：允许用户执行 FLUSH 命令，如刷新表缓存、重载授权表等。

SHUTDOWN: 允许用户关闭 MySQL 服务器。

注意:

多个权限之间，使用逗号分隔。

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

/* 基础语法 */
# 查询权限
SHOW GRANTS FOR '用户名'@'主机名';

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

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

/* 案例 */
-- 查询权限，返回USAGE表示只可以登录
show grants for 'heima'@'%';

-- 赋予所有权限给黑马，作用的对象是itcast的所有表
grant all on itcast.* to 'heima'@'%';

-- 撤销权限
revoke all on itcast.* from 'heima'@'%';

-- 赋予所有权限给所有的库和表
grant all on *.* to 'heima'@'%';

6 约束

6.1 概念

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

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

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

分类:

非空约束（NOT NULL）：限制该字段的数据不能为null

唯一约束（UNIQUE）：保证该字段的所有数据都是唯一、不重复的

主键约束（PRIMARY KEY）：主键是一行数据的唯一标识，要求非空且唯一

默认约束（DEFAULT）：保存数据时，如果未指定该字段的值，则采用默认值

检查约束（CHECK，8.0.16版本之后）：保证字段值满足某一个条件

外键约束（FOREIGN KEY）：用来让两张表的数据之间建立连接，保证数据的一致性和完整性

其他设置：

自增（AUTO_INCREMENT）：每次增加字段时数值是当前字段的最大值加一，从1开始。

/* 案例 */
-- 约束创建表
create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 && age <= 120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment'用户表';


-- 联合主键
CREATE TABLE user (
    id INT AUTO_INCREMENT COMMENT '主键',
    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 '性别',
    PRIMARY KEY (id, name)
) COMMENT '用户表';

6.2 外键

外键约束：

NO ACTION：默认。当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRCT一致)

RESTRICT：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与NO ACTION一致)

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

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

SET DEFAULT：父表有变更时，子表将外键列设置成一个默认的值（Innodb不支持）

/* 基础语法 */
-- 创建外键
CREATE TABLE 表名(
    字段名 数据类型,
    ...
    [CONSTRAINT] [外键名称] FOREISN KEY(外键字段名) REFERENCES 主表(主表列名) [ON UPDATE CASEADE ON DELETE CASEADE]
);


-- 添加外键
ALTER TABLE表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) [约束];
    
    
-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

/* 案例 */
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

alter table emp drop foreign key fk_emp_dept_id; 


-- 约束设置（更新时是caseade模式，删除时是caseade模式）
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id) on update caseade on delete caseade;


-- 指向联合主键的外键
CREATE TABLE purchase (
    user_id INT NOT NULL,
    user_name VARCHAR(10) NOT NULL,

    CONSTRAINT fk_purchase_user
        FOREIGN KEY (user_id, user_name)
        REFERENCES user (id, name)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    -- 可选：如果需要，为 user_id 和 user_name 列添加各自的唯一约束
    UNIQUE (user_id, user_name)
) COMMENT '购买记录表';

7 多表查询

7.1 多表关系

一对多：

案例：部门与员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键

多对多：

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

多对一：

案例：用户与用户详情的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

/* 案例 */
# 一对多
-- 部门表
CREATE TABLE department (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '部门ID',
    name VARCHAR(50) COMMENT '部门名称'
) COMMENT '部门表';

-- 员工表
CREATE TABLE employee (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
    name VARCHAR(50) COMMENT '员工姓名',
    department_id INT COMMENT '部门ID',

    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES department (id)
) COMMENT '员工表'


# 多对多
-- 学生表
CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '姓名',
    no VARCHAR(10) COMMENT '学号'
) COMMENT '学生表';

-- 课程表
CREATE TABLE course (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '课程名称'
) COMMENT '课程表';

-- 学生课程中间表
CREATE TABLE student_course (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    studentid INT NOT NULL COMMENT '学生ID',
    courseid INT NOT NULL COMMENT '课程ID',
    
    CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course (id),
    CONSTRAINT fk_studentid FOREIGN KEY (studentid) REFERENCES student (id)
) COMMENT '学生课程中间表';


# 多对一
-- 用户表
CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) COMMENT '用户名',
    email VARCHAR(50) COMMENT '邮箱'
) COMMENT '用户表';

-- 用户详情表
CREATE TABLE user_detail (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '详情ID',
    user_id INT UNIQUE COMMENT '用户ID',
    full_name VARCHAR(100) COMMENT '全名',
    address VARCHAR(100) COMMENT '地址',

    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES user (id)
) COMMENT '用户详情表';

7.2 内联查询

概述：指从多张表中查询两张表相交的数据。

笛卡尔积：笛卡尔乘积是指在数学中，两个集合，A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

/* 语法 */
# 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

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

/* 案例 */
# id相等消除笛卡尔积（隐式内连接）
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
-- 取别名（取了别名后就不能用原名了）
select e.name, d.name from emp e, dept d where e.dept_id = d.id;

# 显示内连接
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

7.3 外连接查询

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据。

右外连接：查询右表所有数据，以及两张表交集部分数据。

外连接也要除无效的笛卡尔积。

/* 语法 */
# 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

# 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

/* 案例 */
# 左外连接（完全包含左表数据，左表的NULL也会查出来）
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;

# 右外连接
select e.*, d.* from emp e right outer join dept d on e.dept_id = d.id;

7.4 自连接查询

自连接：当前表与自身的连接查询，自连接必须使用表别名。

/* 案例 */
# 查询所有员工emp及其领导的名字 emp，如果员工没有领导，也需要查询出来。
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

7.5 联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

/* 语法 */
SELECT 字段列表 FROM 表A ...
UNION[ALL]
SELECT 字段列表 FROM 表B ...;

/* 案例 */
# 薪资小于5000，年龄大于50
-- 加上all：返回所有结果，不会去重
select * from emp where salary < 5000 union all select * from emp where age > 50;

-- 不加all：返回去重后的结果。
select * from emp where salary < 5000 union select * from emp where age > 50;

7.6 子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

子查询外部的语句可以是INSERT / UPDATE/ DELETE/ SELECT的任何一个。

根据子查询结果不同，分为：

标量子查询：子查询结果为单个值，常用=, <>, >等操作符操作数据。

列子查询：子查询结果为一列，常用IN, NOT IN, ANY, SOME, ALL等对数据做操作。ANY和SOME意思相同，都是有一个满足即可。ALL则是要满足子查询所有数据。

行子查询：子查询结果为一行，常用=, <>, >等操作符操作数据。

表子查询：子查询结果为多行多列。常用IN对数据做操作。

/* 语法 */
SELECT * FROMt1 WHERE column1 = ( SELECT column1 FROM t2 );

/* 案例 */
# 标量子查询（查询"销售部"的所有员工信息）
select * from emp where dept_id = (select id from dept where name="销售部");

# 列子查询
-- 查询"销售部"和"市场部"的所有员工信息
select * from emp where dept_id in (select id from dept where name="销售部" or name="市场部");

-- 查询比"财务部"所有人工资都高的员工的信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));

-- 查询比"研发部"任一人工资都高的员工的信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));

# 行子查询
-- 查询与"张无忌"的薪资以及直属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name='张无忌');


# 表子查询
-- 查询入职日期是"2006-01-01"之后的员工信息，以及部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

三、函数

1 字符串函数

/* 基础语法 */
-- 字符串拼接，将S1，S2，...Sn拼接成一个字符串
CONCAT(S1, S2, ... Sn)  

-- 将字符串str全部转为小写
LOWER(str)             

-- 将字符串str全部转为大写
UPPER(str)             

-- 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
LPAD(str, n, pad)      

-- 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
RPAD(str, n, pad)      

-- 去掉字符串头部和尾部的空格
TRIM(str)              

-- 返回从字符串str从start位置起的len个长度的字符串（索引从1开始）
SUBSTRING(str, start, len)

/* 案例 */
-- Hello World
select CONCAT("Hello ","World");

-- ***aa
LPAD("aa", 5, "*")  

-- aa***
RPAD("aa", 5, "*")

-- Hello
SUBSTRING("Hello World", 1, 5)

2 数值函数

/* 基础语法 */
-- 向上取整
CEIL(x)

-- 向下取整
FLOOR(x)

-- 返回x/y的模
MOD(x,y)

-- 返回0~1内的随机数（不包括0和1）
RAND()

-- 求参数x的四舍五入的值，保留y位小数
ROUND(x,y)

3 日期函数

/* 基础语法 */
-- 返回当前日期
CURDATE()

-- 返回当前时间
CURTIME()

-- 返回当前日期和时间
NOW()

-- 获取指定date的年份
YEAR(date)

-- 获取指定date的月份
MONTH(date)

-- 获取指定date的日期
DAY(date)

-- 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATE_ADD(date,INTERVAL expr type)

-- 返回起始时间date1和结束时间date2之间的天数
DATEDIFF(date1,date2)

/* 案例 */
select YEAR(now());
select MONTH(now());
select DAY(now());

-- 向后推指定年月日
select date_add(now(), INTERVAL 70 DAY);
select date_add(now(), INTERVAL 70 MONTH);
select date_add(now(), INTERVAL 70 YEAR);

-- 相差61天
select datediff("2021-12-01", "2021-10-01");
-- 相差-61天
select datediff("2021-10-01", "2021-12-01");

4 流程控制函数

/* 基础语法 */
-- 如果value为true，则返回t，否则返回f
IF(value , t , f)

-- 如果value1不为空，返回value1，否则返回value2
IFNULL(value1 , value2)

-- 如果val1为true，返回res1, ...否则返回default默认
CASE WHEN [ val1 ] THEN [ res1 ] ...ELSE [ default ] END

-- 如果expr的值等于val1，返回res1, ...否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] ...ELSE [ default ] END

/* 案例 */
-- 返回error
select if(false, 'Ok', 'Error');

-- 返回Ok
select ifnull("ok", "Default");
-- 返回空字符串
select ifnull("", "Default");
-- 返回Default
select ifnull(null, "Default");

-- 北上返回一线，其他二线
select
	name,
	(case workaddress when '北京' then "一线城市" when '上海' then "一线城市" else "二线城市" end) as "工作地址";
from emp;

-- 数学评级
select 
	id,
	name,
	(case when math >= 85 then "A" when math >= 60 then "B" else 'C' end) as "数学"

四、事务

1 概述

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

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

事务特性（ACID）：

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

隔离性（lsolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

/* 基础语法 */
# 查看全局事务提交方式
SELECT @@Qautocommnet;

# 全局设置事务提交方式（1：自动提交。0：手动提交）
SET @@Qautocommnet = 0;

# 手动临时设置事务
START TRANSACTION;
BEGIN;

# 提交事务
COMMIT;

# 回滚事务
ROLLBACK;

/* 案例 */
# 转账操作（需要设置为原子操作，否则出错数据会异常）
-- 1.查询余额
select * from account where name='张三';

-- 2.将张三余额-1000
update account set money = money - 1000 where name = '张三';

程序异常... # 直接模拟程序异常，发现张三钱减了但李四钱没加（要加上...否则不报错）
-- 3.将李四余额+1000
update account set money = money + 1000 where name = '李四';


# 事务管理转账操作
-- 开启事务事务
start transaction;

-- 执行操作，执行完了以后数据库数据不会立刻改变
select * from account where name='张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';

-- 提交事务后，数据库才会修改数据
commit;
-- 如果出错，就回滚数据
rollback;

2 并发事务

问题：

脏读：一个事务读到另外一个事务还没有提交的数据。

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

/* 案例 */
# 脏读（A更新数据但是还没提交，此时B读取到了A还没提交的数据）
select id = 1; -- 事务A
update id = 1; -- 事务A
select id = 1; -- 事务B


# 不可重复读（事务A前后执行了两次select，但是数据却不一样）
select id = 1; -- 事务A
update id = 1; -- 事务B
commit;
select id = 1; -- 事务A


# 幻读（事务A在insert时报错，因为虽然一开始没查到，但是事务B先提交了，后续会插入不了，产生了幻读）
select id = 1; -- 事务A
insert id = 1; -- 事务B
commit;        -- 事务B
insert id = 1; -- 事务A
select id = 1; -- 事务A

3 事务隔离级别

分类：

Read uncommitted：可读未提交。事务可以读取其他未提交事务所做的修改。因此可能会出现脏读和不可重复读的问题。这种隔离级别的优点是读取数据的性能较高，但缺点是可能会导致一定程度的数据不一致。

Read committed：读已提交。事务只能读取已经提交的数据，无法读取未提交的数据。这种隔离级别可以避免脏读，但仍可能出现不可重复读和幻读的问题。

Repeatable Read：可重复读。事务在读取数据时会保持一致性，因此在同一个事务内多次读取相同数据，结果都是一样的。这种隔离级别可以避免脏读和不可重复读，但仍可能出现幻读的问题。

Serializable：串行化。事务彼此之间完全隔离，每个事务运行时都像是在系统中独自运行一样。这种隔离级别保证了数据的一致性，避免了脏读、不可重复读和幻读的问题。但串行化的隔离级别会降低数据库的并发性能，因为事务需要按顺序依次执行。

从上向下，性能越来越差，数据越来越安全。

隔离级别 \	会出现的问题	脏读	不可重复读
Read uncommitted	√	√	√
Read committed（Oracle默认）	x	√	√
Repeatable Read（Mysql默认）	x	x	√
Serializable	x	x	x

/* 基础语法 */
# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

# 设置事务隔离级别
-- SESSION：会话级，仅当前窗口。GLOBAL：全局。
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

/* 案例 */
set session transaction isolation level repeatable read;

五、存储引擎

1 概述

MySQL体系结构：

连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。包括：认证授权、线程管理、连接数限制、检查内存等。

服务层：第二层架构主要完成大多数的核心服务功能。提供SQL接口、解析器、查询优化器、缓存等。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，提供可插拔的存储引擎，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，可以使用不同的存储引擎来存储数据。索引是在存储引擎层实现的。

存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互。包括系统文件、文件和日志等。

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。

存储引擎是MySQL特有的。

存储引擎是基于表的，而不是基于库的，一个库下面的不同的表可以选择不同的存储引擎。所以存储引擎也可被称为表类型。

2 使用存储引擎

在创建表时候可以选择存储引擎，如果不指定默认是InnoDB

/* 基础语法 */
# 查看支持的存储引擎
SHOW ENGINES;

# 创建表时选择存储引擎
CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释]
	...
)ENGINE = INNODB [COMMENT 表注释]

/* 案例 */
# 选择MyISAM存储引擎
create table my_myisam(
	id int,
    name varchar(10)
) engine = MyISAM;

3 InnoDB

定义：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认的MySQL存储引擎。

特点：

事务：DML操作遵循ACID模型，支持事务。

行级锁：行级锁，提高并发访问性能。

外键：支持外键FOREIGN KEY约束，保证数据的完整性和正确性。

文件：xxx.ibd：xx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。参数: innodb_file_per_table。8.0以前表结构存储在frm中，8.0以后存储在sdi这个数据字典中。

查看数据：首先找到MySQL8.0——Data——库名，然后在该路径下打开cmd，输入ibd2sdi xxx.ibd，表示将idb显示为sdi，就可以看到里面的数据了。直接打开看到的是二进制。

逻辑存储结构：

Tablespece：表空间，表空间下有若干个段。

Segment：段，段空间下有若干个区。

Extent：区，区空间下有若干个页。大小固定，1M，最多包含64个页。

Page：页，页空间下有若干个行。磁盘操作的最小单元。大小固定，16K。

Row：行。包括Trx id（最后一次操作事务的id），Roll pointer（指针），若干个col（字段）

/* 基础语法 */

# 查看参数是否打开（打开表示每一张表都对应一个表空间文件）
show variables like 'innodb_file_per_table';

4 MyISAM

介绍：MyISAM是MySQL早期的默认存储引擎。

特点：

不支持事务，不支持外键。

支持表锁，不支持行锁。

访问速度快。

相关文件：

MYD：存放数据。

MYI：存放所索引。

sdi：存放表结构的信息。可以直接查看，JSON格式。

5 Memory

介绍：Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

内存存放

hash索引（默认)

文件：xxx.sdi，存储表结构信息。

6 存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合：

InnoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

但是实际上MySQL几乎只会使用InnoDB存储引擎，MyISAM引擎的作用现在常用MongoDB数据库替代，MEMORY引擎的作用现在常用Redis数据库替代。

六、索引

1 定义

定义：是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

简述：索引是一种数据结构，用于高效地获取数据。

案例：select * from user where age = 45：

无索引：全表扫描，从上到下依次匹配。时间：O(n)

有索引：可以建立一个二叉搜索树（只是案例，实际上不是二叉搜索树），时间：O(logn)

优点：

提高数据检索的效率，降低数据库的IO成本。

通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点：

索引列也是要占用空间的。（实际上现在空间问题几乎可以忽略）

索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。（实际上一个正常的系统大多数查，增删改比例较少）

2 索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构。

B+Tree索引：MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。其中，每一个B+Tree结点（包括叶子节点）都是一个页。

简单来说MySQL的B+Tree索引就是：原来的B+Tree是单向链表，MySQL改成了双向循环链表。

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

为什么使用B+tree索引结构：

相对于二叉树，层级更少，搜索效率高。

对于BTree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。

同时B+Tree的链表可以用于范围查找。

索引结构	描述	InnoDB	MyISAM	Memory
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引。使用较多。	√	√	√
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询	x	x	√
R-tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少	x	√	x
Full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr，ES，通常使用较少	5.6版本后支持	√	x

3 索引分类

普通索引分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，分成：

聚集索引（Clustered Index）：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。必须有，而且只有一个。简单来说就是B+Tree的叶子节点，key是主键值，value是那一行的数据。

二级索引（Secondary Index）：将数据与索引分开存储（像B+Tree），索引结构的叶子节点关联的是对应的主键。可以有多个。简单来说就是B+Tree的叶子节点，key是对应值，value是主键值。

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一索引（UNIQUE）作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

案例select * from user where name="Arm"：先通过二级索引name，找到Arm对应的主键值（id），再用id到聚集索引id中，定位到id对应的行数据，然后返回那一行的数据。该过程的专业术语为回表查询。

MySQL

一、数据库概述

1 基础

2 下载和管理

3 图形化界面

二、SQL语句

1 概念

2 DDL

2.1 库操作

2.2 表操作

3 DML

3.1 增删改

4 DQL

4.1 基本查询

4.2 条件查询

4.3 聚合函数

4.4 分组查询

4.5 排序查询

4.6 分页查询

5 DCL

5.1 用户设置

5.2 权限控制

6 约束

6.1 概念

6.2 外键

7 多表查询

7.1 多表关系

7.2 内联查询

7.3 外连接查询

7.4 自连接查询

7.5 联合查询

7.6 子查询

三、函数

1 字符串函数

2 数值函数

3 日期函数

4 流程控制函数

四、事务

1 概述

2 并发事务

3 事务隔离级别

五、存储引擎

1 概述

2 使用存储引擎

3 InnoDB

4 MyISAM

5 Memory

6 存储引擎选择

六、索引

1 定义

2 索引结构

3 索引分类

七、SQL优化

八、视图

九、存储过程

十、触发器

十一、锁