MySQL总结二

本文主要对尚硅谷《MySQL核心技术》课程笔记做了相关整理，分为一二两个部分，第一部分主要是数据查询语言DQL相关语法，第二部分主要是数据操纵语言DML，数据定义语言DDL，数据控制语言DCL相关语法，以方便各位同学查询和复习，再次感谢尚硅谷能免费公开相关课程视频。

MySQL总结一：MySQL总结一 - 掘金 (juejin.cn)

SQL脚本文件：MySQL总结（脚本文件）

三、DML语言

3.1、插入语句

3.1.1、语法

# 方式一：
INSERT INTO 表名(列1,...) VALUES
(值1,...);

# 方式二：
INSERT INTO 表名 
SET 列1=值1, ...;

3.1.2、特点及对比

• 方式一特点：

1. 插入的值的类型要与列的类型一致或兼容
2. 不可以为null的列必须插入值。可以为null的列可以不写或者值为NULL
3. 列的顺序可以调换
4. 列数和值的个数必须一致
5. 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

• 方式一方式二对比：

1. 方式一支持插入多行,方式二不支持
2. 方式一支持子查询，方式二不支持

3.1.3、举例

INSERT INTO beauty (NAME, sex, borndate, phone, photo, boyfriend_id) VALUES 
('limei', '女', '1993-01-01', '18211112222', NULL, 2),
('limeimei', '女', '1993-02-02', '18211113333', NULL, 2);

INSERT INTO beauty SET
NAME='zhangsan', phone='18200001111';

3.2、修改语句

3.2.1、语法

# 单表修改
UPDATE 表名
SET 列1=值1,...
WHERE 筛选条件;

# 多表修改
UPDATE 表1 别名1
【left|right】 JOIN 表2 别名2
ON 连接条件
SET 列1=值1,...
WHERE 筛选条件;

3.2.2、举例

# 单表修改
UPDATE beauty 
SET phone=19200001111
WHERE id=17

# 多表修改
#案例 1：修改张无忌的女朋友的手机号为114
UPDATE beauty b
JOIN boys bo
ON b.boyfriend_id=bo.id
SET phone=114
WHERE bo.boyName='张无忌';

#案例2：修改没有男朋友的女神的男朋友编号都为2号
UPDATE beauty b
LEFT JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;

3.3、删除语句

3.3.1、语法

# 单表删除
DELETE FROM 表名
WHERE 筛选条件;

# 多表删除
DELETE 别名1,别名2
FROM 表1 别名1
【left|right】join 表2 别名2
ON 连接条件
WHERE 筛选条件

# 全删除
TRUNCATE TABLE 表名

3.3.2、DELETE和TRUNCATE区别

• DELETE可以添加筛选条件，TRUNCATE不行
• DELETE自增长列从断点继续，TRUNCATE从1开始
• DELETE有返回值，TRUNCATE没有
• DELETE可以回滚，TRUNCATE不行

3.3.3、举例

#1.单表的删除
#案例：删除手机号以9结尾的女神信息
DELETE FROM beauty
WHERE phone LIKE '%9';

#2.多表的删除
#案例1：删除张无忌的女朋友的信息
DELETE b
FROM beauty b
JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.boyName='张无忌';

#案例2：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`="黄晓明";

四、DDL语言

4.1、库的管理

4.1.1、库的创建

CREATE DATABASE IF NOT EXISTS Books;

4.1.2、库的修改

ALTER DATABASE Books CHARACTER SET utf8;

4.1.3、库的删除

DROP DATABASE IF EXISTS Books;

4.2、表的管理

4.2.1、表的创建

#案例：创建表Book
CREATE TABLE IF NOT EXISTS book (
id INT,
book_name VARCHAR(20),
price DOUBLE,
author_id INT,
publish_date DATETIME
);

#案例：创建表author
CREATE TABLE IF NOT EXISTS author (
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
)

4.2.2、表的修改

alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

#修改列名
ALTER TABLE book CHANGE COLUMN publish_date pub_date DATETIME;
#修改列的类型或约束
ALTER TABLE book MODIFY COLUMN price INT;
#添加新列
ALTER TABLE book ADD COLUMN text_count INT;
#删除列
ALTER TABLE book DROP COLUMN text_count;
#修改表名
ALTER TABLE author RENAME TO book_author;

4.2.3、表的删除

DROP TABLE IF EXISTS book;

4.2.4、表的复制

#仅仅复制表的结构
CREATE TABLE book_copy 
LIKE book;

#复制表的结构+数据
CREATE TABLE book_copy2
SELECT * FROM book;

#只复制部分数据
CREATE TABLE book_copy3
SELECT id, book_name FROM book
WHERE id=2;

4.3、常见数据类型

4.3.1、整型

• 样例

CREATE TABLE IF NOT EXISTS tab_int(
n1 INT,
n2 INT UNSIGNED,
n3 SMALLINT(3) ZEROFILL
)

• 分类 | 类型 | tinyint | smallint | mediumint | int | bigint | | --- | --- | --- | --- | --- | --- | | 范围（字节） | 1 | 2 | 3 | 4 | 8 | | 范围 | 2^8 | 2^16 | 2^24 | 2^32 | 2^64 |

• 特点

• 类型后(D)可以修改显示长度，搭配ZEROFILL可以在长度不够时左填充0

• 默认有符号，无符号使用UNSIGNED

• 插入数值超过范围，会报out of range

4.3.2、小数

• 样例

CREATE TABLE IF NOT EXISTS tab_float (
f1 FLOAT(10, 2),
f2 DOUBLE,
f3 DECIMAL
)

• 分类
• 浮点型

1. FLOAT(M,D)
2. DOUBLE(M,D)

• 定点型

1. DEC(M,D)
2. DECIMAL(M,D)

• 特点
• (M,D)->(整数部位+小数部位, 小数部位)
• (M,D)都可以省略

1. DECIMAL默认(10,0)
2. FLOAT和DOUBLE会根据插入的数值的精度来确定精度

• 小数部位如果超出精度，会四舍五入进位
• 定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

4.3.3、字符型

• 举例

CREATE TABLE IF NOT EXISTS tab_char(
c1 CHAR,
c2 VARCHAR(20),
c3 TEXT(100)
)

• 分类

1. 文本

char
varchar
text（较大）

2. 二进制

binary
varbinary
blob（较大）

3. 其他

enum
set

4.3.4、日期型

• 样例

CREATE TABLE IF NOT EXISTS tab_date(
d1 DATE,
d2 TIME,
d3 DATETIME,
d4 TIMESTAMP,
d5 YEAR
)

• 分类

类型	字节	范围	受时区等影响
DATETIME	8	1000—9999	不受（插入时多少就是多少）
TIMESTAMP	4	1970-2038	受

• 查看和设置时区

SHOW VARIABLES LIKE 'time_zone';
SET time_zone='+3:00';

4.4、常见约束

4.4.1、含义

用于限制表中的数据，为了保证数据的准确性和可靠性

4.4.2、语法

CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)

4.4.3、分类

• 六大类型

1. PRIMARY KEY：主键
2. UNIQUE：唯一
3. NOT NULL：非空
4. DEFAULT：默认
5. CHECK：检查(mysql不支持)
6. FOREIGN KEY：外键

• 添加方式

1. 列级约束：六大约束语法上都支持，但外键约束没有效果
2. 表级约束：除了非空、默认，其他的都支持

• 主键和唯一

约束	唯一性	为空	个数	组合
主键	是	不可以	至多只有一个	可以，但不推荐
唯一	是	可以	可以有多个	可以，但不推荐

• 外键

1. 要求在从表设置外键关系
2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3. 主表的关联列必须是一个key（一般是主键或唯一）
4. 插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表

4.4.4、添加约束

CREATE TABLE IF NOT EXISTS tab_major(
id INT PRIMARY KEY,
marjor_name VARCHAR(20)
)

# 一般外键使用表级约束，其他约束使用列级约束
CREATE TABLE IF NOT EXISTS tab_stu_info(
#列级约束
id INT PRIMARY KEY,
stu_name VARCHAR(20) NOT NULL,
seat_no INT UNIQUE,
age INT DEFAULT 18,
gender CHAR CHECK(gender='男' OR gender='女'),
major_id INT,
#表级约束
CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES tab_major(id)
)

4.4.5、修改约束

#1.添加非空约束
ALTER TABLE tab_stu_info MODIFY COLUMN stu_name VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE tab_stu_info MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
ALTER TABLE tab_stu_info MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE tab_stu_info ADD PRIMARY KEY(id);
#4.添加唯一
ALTER TABLE tab_stu_info MODIFY COLUMN seat_no INT UNIQUE;
ALTER TABLE tab_stu_info ADD UNIQUE(seat_no);
#5.添加外键
ALTER TABLE tab_stu_info ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCES tab_major(id); 

4.4.6、删除约束

#1.删除非空约束
ALTER TABLE tab_stu_info MODIFY COLUMN stu_name VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE tab_stu_info MODIFY COLUMN age INT;
#3.删除主键
ALTER TABLE tab_stu_info DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE tab_stu_info DROP INDEX seat_no;
#5.删除外键
ALTER TABLE tab_stu_info DROP FOREIGN KEY fk_stuinfo_major;

4.5、标识列

4.5.1、含义

又称自增长列，可以不用手动的插入值，系统提供默认的序列值

4.5.2、特点

• 标识列不一定必须和主键搭配吗，但要求是一个key
• 一个表至多一个标识列
• 标识列的类型只能是数值型
• 标识列可以通过SET auto_increment_increment=3;来设置步长
• 可以通过手动插入来设置起始值

五、TCL语言

5.1、事务

5.1.1、定义

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

5.1.2、特点：ACID

• 原子性：一个事务不可再分割，要么都执行要么都不执行
• 一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
• 隔离性：一个事务的执行不受其他事务的干扰
• 持久性：一个事务一旦提交，则会永久改变数据库中的数据

5.1.3、分类

• 显示事务：事务具有明显的开启和结束的标记

• 隐式事务：事务没有明显的开启和结束的标记

5.1.4、步骤

1. 开启事务

set autocommit=0;
start transaction; #可选的

2. 编写事务中的sql语句(select insert update delete)

语句1;
savepoint 节点名; #设置保存点
语句2;

3. 结束事务

commit; #提交事务
rollback【节点名】; #回滚事务

5.1.4、事务并发

• 事务的并发问题是如何发生的？

多个事务同时操作数据库中的同一数据

• 并发问题种类

1. 脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
2. 不可重复读：一个事务多次读取数据，结果不一样
3. 幻读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“插入”的数据

5.1.5、隔离级别

• 分类

1. 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
2. 提交读(Read Committed)：只能读取到已经提交的数据，Oracle默认级别
3. 可重复读(Repeated Read)：可重复读，在同一个事务内的查询都是事务开始时刻一致的，MySQL默认级别
4. 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

• 特点

隔离级别	脏读	不可重复读	幻读
read uncommitted	有	有	有
read committed	无	有	有
repeatable read	无	无	有
serializable	无	无	无

• 查看和设置

# 查看隔离级别
select @@tx_isolation;

# 设置隔离级别
set session|global transaction isolation level 隔离级别;

5.1.6、举例

SET autocommit=0;

UPDATE tab_account 
SET balance=1500
WHERE id=1; 

SAVEPOINT a;

UPDATE tab_account 
SET balance=500
WHERE id=2; 

ROLLBACK TO a;

SET autocommit=1;

六、其他

6.1、视图

6.1.1、创建视图

• 语法

create view 视图名
as
查询语句;

• 样例

#1.查询姓名中包含a字符的员工名、部门名和工种信息
CREATE VIEW myv1
AS
SELECT e.last_name, d.department_name, j.job_title
FROM employees e
JOIN departments d ON e.department_id=d.department_id
JOIN jobs j ON e.job_id=j.job_id;

SELECT * FROM myv1 
WHERE last_name LIKE '%a%';

#2.查询各部门的平均工资级别
CREATE VIEW myv2
AS
SELECT AVG(salary) avg_salary,department_id
FROM employees
GROUP BY department_id;

SELECT avg_salary, department_id, jg.grade_level
FROM myv2
JOIN job_grades jg ON avg_salary BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;

#3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY avg_salary LIMIT 1;

#4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY avg_salary LIMIT 1;

SELECT d.*, m.avg_salary
FROM myv3 m
JOIN departments d ON m.`department_id`=d.`department_id`;

6.1.2、修改视图

• 语法

#方式一
create or replace view  视图名
as
查询语句;

#方式二
alter view 视图名
as 
查询语句;

• 样例

# 方式一
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

# 方式二
ALTER VIEW myv3
AS
SELECT * FROM employees;

6.1.3、删除视图

• 样例

DROP VIEW IF EXISTS myv1;

6.1.4、更新视图

• 样例

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
#1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');

#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';

• 具备以下特点的视图不允许更新

1. 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
2. 常量视图
3. Select中包含子查询
4. join
5. from一个不能更新的视图
6. where子句的子查询引用了from子句中的表

6.2、变量

6.2.1、系统变量

• 说明

变量由系统定义，不是用户定义，属于服务器层面

• 分类

1. 全局变量 需要添加global关键字，作用域：针对于所有会话（连接）有效，但不能跨重启

2. 会话变量 需要添加session关键字，如果不写，默认会话变量，作用域：针对于当前会话（连接）有效

• 使用步骤

1. 查看所有系统变量
SHOW GLOBAL|【SESSION】 VARIABLES;
2. 查看满足条件的系统变量
SHOW GLOBAL|【SESSION】 VARIABLES LIKE '%char%';
3. 查看指定的系统变量的值
SELECT @@global|【SESSION】.系统变量名;
4. 为某个系统变量赋值
SET @@global|【SESSION】.系统变量名=值;

6.2.2、自定义变量

• 说明

变量由用户自定义，而不是系统提供的

• 分类

1. 用户变量 作用域：针对于当前会话（连接）有效，作用域同于会话变量

2. 局部变量 作用域：仅仅在定义它的begin end块中有效；应用在 begin end中的第一句话

• 使用步骤

1. 声明 SET @变量名=值;

DECLARE 变量名 类型 【DEFAULT 值】;

2. 赋值 SET @变量名=值;

SET 局部变量名=值;

3. 使用（查看、比较、运算等） SELECT @变量名;

SELECT 局部变量名;

• 样例

#用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;

#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

• 用户变量和局部变量的对比

自定义变量	作用域	定义位置	语法
用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@,需要指定类型

6.3、存储过程

6.3.1、含义

一组预先编译好的SQL语句的集合，理解成批处理语句

6.3.2、特点

• 简化操作，提高代码的重用性
• 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

6.3.3、语法

#创建
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END

#使用
CALL 存储过程名(实参列表);

#删除
DROP PROCEDURE 存储过程名

6.3.4、举例

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp1(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	LEFT JOIN beauty b ON b.boyfriend_id=bo.id
	WHERE b.name=beautyName;
END$
CALL myp1('小昭')

#案例2 ：创建存储过程实现，用户是否登录成功
SELECT * FROM admin
DROP PROCEDURE myp2
DELIMITER $
CREATE PROCEDURE myp2(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;
	SELECT COUNT(*) INTO result
	FROM admin ad
	WHERE ad.username=username AND ad.password=PASSWORD;
	SELECT IF(result>0,'pass','no pass');
END$
CALL myp2('zhangsan', '111')$

#案例3：根据输入的女神名，返回对应的男神名和魅力值
DELIMITER $
CREATE PROCEDURE myp3(IN beautyName VARCHAR(20),OUT boyname VARCHAR(20),OUT usercp INT) 
BEGIN
	SELECT boys.boyname, boys.usercp INTO boyname, usercp
	FROM boys 
	RIGHT JOIN
	beauty b ON b.boyfriend_id = boys.id
	WHERE b.name=beautyName;
END $
CALL myp3('小昭',@name,@cp)$
SELECT @name,@cp$

#案例4：传入a和b两个值，最终a和b都翻倍并返回
DELIMITER $
CREATE PROCEDURE myp4(INOUT m1 INT, INOUT m2 INT)
BEGIN
	SET m1=m1*2;
	SET m2=m2*2;
END$

SET @a=10$
SET @b=15$
CALL myp4(@a, @b)$ 
SELECT @a,@b$

6.4、函数

6.4.1、含义

一组预先编译好的SQL语句的集合，理解成批处理语句

6.4.2、特点

函数和存储过程特点相同

6.4.3、语法

#创建
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

#使用
SELECT 函数名(参数列表);

#查看
SHOW CREATE FUNCTION 函数名;

#删除
DROP FUNCTION 函数名;

6.4.4、举例

#根据部门名，返回该部门的平均工资
DELIMITER $
CREATE FUNCTION myf1(departmentName VARCHAR(20)) RETURNS FLOAT
BEGIN
	DECLARE result FLOAT DEFAULT 0;
	SELECT AVG(salary) avg_salary INTO result
	FROM employees e
	JOIN departments d ON e.department_id=d.department_id
	WHERE d.department_name=departmentName;
	RETURN result;
END $
SELECT myf1('Adm')
DROP FUNCTION myf1;

6.5、流程控制结构

6.5.1、分支结构

1. 语法 /* IF 条件1 THEN 语句1; ELSEIF 条件2 THEN 语句2; .... ELSE 语句n; END IF; */

2. 举例

#创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $

SELECT test_if(87)$

• case结构

1. 语法

情况1：
CASE 变量或表达式
WHEN 值1 THEN 语句1;
WHEN 值2 THEN 语句2;
...
ELSE 语句n;
END 

情况2：
CASE 
WHEN 条件1 THEN 语句1;
WHEN 条件2 THEN 语句2;
...
ELSE 语句n;
END 

2. 举例

#创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE ch CHAR DEFAULT 'A';
	
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $

SELECT test_case(56)$

6.5.2、循环结构

• 语法

1. WHILE

【标签:】WHILE 循环条件 DO
	循环体;
END WHILE 【标签】;

2. LOOP

【标签:】LOOP
	循环体;
END LOOP 【标签】;

3. REPEAT

【标签：】REPEAT
	循环体;
UNTIL 结束循环的条件
END REPEAT 【标签】;

4. ITERATE：类似于continue

5. LEAVE：类似于break

• 举例

#案例1：批量插入，根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('Rose',i),'666');
		SET i=i+1;
	END WHILE;
	
END $

CALL pro_while1(100)$

#案例2：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $

CALL test_while1(100)$

#案例3：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		
	END WHILE a;
END $

CALL test_while1(100)$