从0回顾（基础篇）-MySQL知识全回顾

零、回顾准备

a、基础：

• MySQL概述
• SQL
• 函数
• 约束
• 多表查询
• 事务

一、MySQL概述

1、数据库相关概念

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

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

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

2、MySQL数据库

①启动和停止

• 启动：net start mysql
• 停止：net stop mysql

②客户端连接：

• 账号：mysql -u root -p
• 密码：xxxx

3、数据模型

客户端-DBMS-数据库-表

①关系型数据库（RDBMS）：

概念：建立在挂你模型基础上，由多张互相连接的二维表组成的数据库。

特点：

• 使用表存储数据，格式统一，便于维护
• 使用SQL语言操作，标准统一，使用方便

二、SQL

1、SQL通用语法

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

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

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

④注释：

• 单行注释：-- 注释内容  或者  # 注释内容（MySQL特有）
• 多行注释： /* 注释内容 */

2、SQL分类

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

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

③数据查询语言（Data Query Language，DQL）：用于查询数据库中标的记录

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

3、DDL

数据库操作

①查询

查询所有数据库：

SHOW DATABASES;

查询当前数据库：

SELECT DATABASE();

② 创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]

create database itcast;
create database if not exists test;
create database if not exists itwzw default charset utf8mb4;

③删除

DROP DATABASE [IF EXISTS] 数据库名;

drop database if exists test;

 ④使用

USE 数据库名;

use itcast;

DDL-表操作-查询 

①查询单签数据库所有表

SHOW TABLES;

②查询表结构

DESC 表名;

③查询指定表的建表语句

SHOW CREATE TABLE 表名;

DDL-表操作-创建

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

DDL-表操作-数据类型

MySQL中数据类型主要分为三类：数值类型、字符串类型、日期时间类型。

分类	类型	大小	有符号范围 （SIGNED）	无符号范围 (UNSIGNED)	描述
数值类型	TINYINT	1 byte	(-128,127)	(0,255)	小整数值
	SMALLINT	2 bytes	(-32768,32767)	(0,65535)	大整数值
	MEDIUMINT	3 bytes	(-8388608,8388607)	(0,16777215)	大整数值
	INT或INTEGER	4 bytes	不写了	不写了	大整数值
	BIGINT	8 bytes	(-2^63,2^63-1)	(0,2^64-1)	极大整数值
	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	长文本数据
	MEDIUMBLOB	0-16777215 bytes	二进制形式的中等长度文本数据
	MEDIUMTEXT	0-16777215 bytes	中等长度文本数据
	LONGBLOB	0-4294967295 bytes	二进制形式的极大文本数据
	LONGTEXT	0-4294967295 bytes	极大文本数据

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

测试案例

• 设计一张员工信息表，要求如下：

1. 编号（纯数字）
2. 员工工号（字符串类型，长度不超过10位）
3. 员工姓名（字符串类型，长度不超过10位）
4. 性别（男/女，存储一个汉字）
5. 年龄（正常人年龄，不可能存储负数）
6. 身份证号（二代身份证均为18位，身份证中有X这样的字符）
7. 入职时间（取值年月日即可）

create table emp(
    id int comment '编号',
    user_num varchar(10) comment '员工工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    id_card char(18) comment '身份证号',
    entry_date date comment '入职时间'
)comment '员工表';

DDL-表操作-修改 

①添加字段

alter table 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

 测试案例1

为emp表增加一个新的字段“昵称”为nickname，类型为varchar(20)

alter table emp add nickname varchar(20) comment '昵称';

②修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)；

 ③修改字段名和字段类型

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

测试案例2 

将emp表的nickname字段修改为username，类型为varchar(30)

alter table emp change nickname username varchar(30) comment '昵称';

④删除字段

ALTER TABLE 表名 DROP 字段名;

 测试案例3

将emp表的字段username删除

alter table emp drop username;

⑤修改表名

ALTER TABLE 表名 RENAME TO 新表名;

 测试案例4

将emp表的表名修改为employee

alter table emp rename to employee;

 DDL-表操作-删除

①删除表

DROP TABLE [IF EXISTS] 表名;

②删除指定表，并重新创建该表

TRUNCATE TABLE 表名;

4、DML

DML-添加数据

① 给指定字段添加数据

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,,...);

 *注意：

• 插入数据时，指定的字段顺序需要与值得顺序是一一对应的；
• 字符串和日期型数据应该包含在引导中；
• 插入的数据大小，应该在字段的规定范围内。

DML-修改数据 

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

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

DML-删除数据 

DELETE FROM 表名 [WHERE 条件];

 *注意：

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
• DELETE语句不能删除某一个字段的值（可以使用UPDATE）

5、DQL

DQL-语法

①编写顺序

SELECT
    字段列表
FROM
    表明列表
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后条件列表
ORDER BY 
    排序字段列表
LIMIT
    分页参数

② 执行顺序

FROM 
    表名列表
WHERE
    条件列表
GROUP BY 
    分组字段列表
HAVING 
    分组后条件列表
SELECT 
    字段列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

 DQL-基本查询

①查询多个字段

SELECT 字段1,字段2,字段3... FROM 表名;
SELECT * FROM 表名;

 ②设置别名

SELECT 字段1 [AS 别名1],字段2 [AS 字段2] ... FROM 表名;

③ 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询

① 语法

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

②条件

比较运算符	功能	逻辑运算符	功能
>	大于	AND 或 &&	并且    (多个条件同时成立)
>=	大于等于	OR 或 ||	或者    (多个条件任意成立一个)
＜	小于	NOT 或 !	非，不是
<=	小于等于
＝	等于
<>或!=	不等于
BETWEEN...AND...	在某个范围之内(含最大、最小值)
IN(...)	在in之后列表中的值，多选一
LIKE 占位符	模糊匹配 (_匹配单个字符，%匹配任意个字符)
IS NULL	是NULL

 DQL-聚合函数

①语法

SELECT 聚合函数（字段列表） FROM 表名;

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

②常见的聚合函数

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

DQL-分组查询 

①语法

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

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

②where和having的区别

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

DQL-排序查询

①语法

SELECT 字段列表 FROM 表明 ORDER BY 字段1 排序方式1,字段2 排序方式2;

②排序方式

•  ASC：升序（默认值）
• DESC：降序
• 注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

DQL-分页查询 

①语法

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

*注意：

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

6、DCL

DCL-介绍

DCL 英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限

DCL-管理用户

①查询用户

USE mysql;
SELECT * FROM user;

②创建用户

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

③ 修改用户密码

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

④删除用户

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

*注意：

• 主机名可以使用%通配
• 这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用

DCL-权限控制 

权限	说明
ALL,ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	更新数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

①查询权限

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

②授予权限

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

③撤销权限

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

*注意：

• 多个权限之间，使用逗号分隔
• 授权时，数据库名和表名可以使用*进行通配，代表所有

 三、函数

1、字符串函数

函数	功能
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)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	返回字符串str从strat位置起的len个长度的字符串

2、数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入值，保留y位小数

3、日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

4、流程函数

函数	功能
IF(value,t,f)	如果value为true，则返回t，否则返回f
IFNULL(value1,value2)	如果value不为空，返回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默认值

四、约束

1、概述

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

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

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

2、约束演示

字段名	字段含义	字段类型	约束条件	约束关键字
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)	无

3、外键约束

• 概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
• 语法

①添加外键

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

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

②删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

4、外键约束-删除/更新行为

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT一致）
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NO ACTION一致）
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录。
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求外键允许取null）
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值（Innodb不支持）

•  语法

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

五、多表查询

1、多表关系

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

一对多（多对一）

案例：部门 与 员工的关系

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

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

多对多

案例：学生 与 课程的关系

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

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

一对一

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

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

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

2、多表查询概述

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

多表查询分类

①连接查询

• 内连接：相当于查询A、B交集部分数据
• 外连接：
  • 左外连接：查询左表所有数据，以及两张表交集部分数据
  • 右外连接：查询右表所有数据，以及两张表交集部分数据 
•  自连接：当前表与自身的连接查询，自连接必须使用表别名

②子查询

连接查询-内连接

①隐式内连接

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

②显式内连接 

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

连接查询-外连接

①左外连接：相当于查询表1（左表）的所有数据，包含 表1和表2交集部分的数据

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

②右外连接：相当于查询表2（右表）的所有数据，包含 表1和表2交集部分的数据

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

连接查询-自连接 

①自连接查询语法：自连接查询，可以使内连接查询，也可以试外连接查询。

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

 连接查询-联合查询：union，union all

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

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;

*注意：对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致；union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

连接查询-子查询

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

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

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

• 根据子查询结果不同，分为
  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）
• 根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。 

子查询-标量子查询

• 标量子查询：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询
• 常用的操作符：=  <>  >  >=  <  <=

子查询-列子查询

• 列子查询：子查询返回的结果是一列（可以是多行），这种子查询称为列子查询
• 常用的操作符：IN、NOT IN、ANY、SOME、ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

子查询-行子查询

• 行子查询：子查询返回的结果是一列（可以是多列），这种子查询称为行子查询

子查询-表子查询

• 表字查询：子查询返回的结果是多行多列，这种子查询称为表子查询。
• 常用的操作符：IN

六、事务 

1、事务简介

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

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

2、事务操作

方式一：

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

SELECT @@autocommit;
SET @@autocommit = 0;

②提交事务

COMMIT;

③回滚事务

ROLLBACK;

方式二：

①开启事务

START TRANSACTION 或 BEGIN;

 ②提交事务

COMMIT;

③回滚事务

ROLLBACK;

 3、事务的四大特性（ACID）

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

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

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

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

 4、并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”

5、事务隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read（默认）	×	×	√
Serializable（串行化）	×	×	×

①查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION

②设置事务隔离级别

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ UNCOMMITTED|REPEATABLE READ|SERIALIZABLE}