[TOC]
第一章：MySQL介绍
1.1-什么是SQL
Structured Query Language 结构化查询语言
1.2-SQL的作用
- 是一种所有关系型数据库的查询规范，不同的数据库都支持。
- 通用的数据库操作语言，可以用在不同的数据库中。
- 不同的数据库 SQL 语句有一些区别
1.3-SQL语句分类
- Data Definition Language (DDL 数据定义语言) 如：建库，建表
-
Data Manipulation Language(DML 数据操纵语言)，如：对表中的记录操作增删改 -
Data Query Language(DQL 数据查询语言)，如：对表中的查询操作 - Data Control Language(DCL 数据控制语言)，如：对用户权限的设置
1.4-SQL语法注意事项
-
每条语句以分号结尾，如果在 SQLyog 中不是必须加的。
-
SQL 中不区分大小写，关键字中认为大写和小写是一样的 。
-
3 种注释：
注释语法 说明 --空格单行注释 /**/多行注释 #这是 mysql 特有的注释方式
第二章：DDL操作数据库
2.1-创建数据库
创建数据
CREATE DATABASE 数据库名;
判断数据库是否已经存在，不存在则创建数据库
CREATE DATABASE IF NOT EXISTS 数据库名;
创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
演示：
--创建一个名为db1的数据库
create database db1;
--若没有存在，则创建一个名为db2的数据库
create database if not exists db2;
--创建要给字符集为gbk的数据库db3
create database db3 character set gbk;
2.2-查看数据库
查看所有数据库
show databases;
查看指定的数据库
show create database 数据库名
演示：
--查看所有数据库
show databases;
--查看名为db1的数据库
show create database db1;
2.3-修改数据库
修改数据库的字符集
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
演示：
ALTER DATABASE db1 character set utf8;
2.4-删除数据库
语法
DROP DATABASE 数据库名;
演示：
DROP DATABASE db1;
2.5-使用数据库
查看使用的数据库
SELECT DATABASE();
切换使用的数据库
use 数据库名;
演示
select database();
第三章：DDL操作表结构
前提先使用某个数据库 。
3.1-创建表
格式
CREATE TABLE 表名 (
字段名 1 字段类型 1,
字段名 2 字段类型 2
);
关键字说明
-
CREATE创建 -
TABLE表
3.2-MySQL数据类型
类型
日期：TIMESTAMP 时间戳
演示
create table student (
id int, -- 整数
name varchar(20), -- 字符串
birthday date -- 生日，最后没有逗号
);
3.3-查看表
查看某个数据库的所有表
SHOW TABLES;
查看表结构
DESC 表名
查看创建表的SQL语句
SHOW CREATE TABLE 表名
3.4-快速创建一个与表结构相同的表
语法
CREATE TABLE 新表名 LIKE 旧表名;
演示
CREATE TABLE student LINKE stu;
3.5-删除表
直接删除表
DROP TABLE 表名;
判断表是否存在，如果存在则删除表
DROP TABLE IF EXISTS 表名
演示
DROP TABLE student;
DROP TABLE IF EXISTS student
3.6-修改表结构
添加一个列
alter table 表名 add 列名 类型
修改列的类型
ALTER TABLE 表名 MODIFY 列名 新的类型
修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
删除列
ALTER TABLE 表名 DROP 列名;
修改表名
RENAME TABLE 表名 TO 新表名;
修改表名字符集
ALTER TABLE 表名 character set 字符集;
第四章：DML操作表中的数据
4.1-插入记录
所有的字段名
INSERT INTO 表名 (字段名 1, 字段名 2, 字段名 3…) VALUES (值 1, 值 2, 值 3...);
不写字段名
INSERT INTO 表名 VALUES (值 1, 值 2, 值 3…);
插入部分数据
INSERT INTO 表名 (字段名 1, 字段名 2, ...) VALUES (值 1, 值 2, ...);
注意事项
- 插入的数据应与字段的数据类型相同
- 数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
- 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。
- 在 mysql 中可以使用 value，但不建议使 用，功能与 values 相同。
- 字符和日期型数据应包含在单引号中。MySQL 中也可以使用双引号做为分隔符。
- 不指定列或使用 null，表示插入空值。
4.2-更新记录
UPDATE 表名 SET 列名=值 [WHERE 条件表达式]
#UPDATE: 需要更新的表名
#SET: 修改的列值
#WHERE: 符合条件的记录才更新
#你可以同时更新一个或多个字段。
#你可以在 WHERE 子句中指定任何条件。
不带条件修改数据
UPDATE 表名 SET 字段名=值; -- 修改所有的行
带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
演示
-- 不带条件修改数据，将所有的性别改成女
update student set sex = '女';
-- 带条件修改数据，将 id 号为 2 的学生性别改成男
update student set sex='男' where id=2;
-- 一次修改多个列，把 id 为 3 的学生，年龄改成 26 岁，address 改成北京
update student set age=26, address='北京' where id=3;
4.3-删除记录
DELETE FROM 表名 [WHERE 条件表达式]
# 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
# 你可以在 WHERE 子句中指定任何条件
不带条件删除数据
DELETE FROM 表名
带条件删除数据
DELETE FROM 表名 WHERE 字段名=值;
使用 truncate 删除表中所有记录
TRUNCATE TABLE 表名;
truncate 和 delete 的区别
truncate 相当于删除表的结构，再创建一张表。
演示
-- 带条件删除数据，删除 id 为 1 的记录
delete from student where id=1;
-- 不带条件删除数据,删除表中的所有数据
delete from student;
第五章：DQL查询表中的数据
SELECT 列名 FROM 表名 [WHERE 条件表达式];
5.1-简单查询
查询表所有行和列的数据
SELECT * FROM 表名;
查询指定的列
SELECT 字段名 1, 字段名 2, 字段名 3, ... FROM 表名;
演示：
select * from student;
select name,age,sex from student;
5.2-指定列的别名
使用别名的好处： 显示的时候使用新的名字，并不修改表的结构。
表使用别名的原因：用于多表查询操作 。
对列指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名;
对列和表名指定别名
SELECT 字段名 1 AS 别名, 字段名 2 AS 别名... FROM 表名 AS 表别名;
演示
-- 使用别名
select name as 姓名,age as 年龄 from student;
-- 表使用别名
select st.name as 姓名,age as 年龄 from student as st
5.3-清除重复的值
**查询指定列并且结果不出现重复数据 **
SELECT DISTINCT 字段名 FROM 表名;
演示
-- 查询学生来至于哪些地方
select address from student;
-- 去掉重复的记录
select distinct address from student;
5.4-查询结果参与运算
注意: 参与运算的必须是数值类型
**某列数据和固定值运算 **
SELECT 列名 1 + 固定值 FROM 表名;
**某列数据和其他列数据参与运算 **
SELECT 列名 1 + 列名 2 FROM 表名;
演示
需求-准备数据：添加数学，英语成绩列,给每条记录添加对应的数学和英语成绩，查询的时候将数学和英语的成绩相 加
select * from student;
-- 给所有的数学加 5 分
select math+5 from student;
-- 查询 math + english 的和
select * from student;
select *,(math+english) as 总成绩 from student;
-- as 可以省略
select *,(math+english) 总成绩 from student;
5.5-条件查询
为什么要条件查询？
如果没有查询条件，则每次查询所有的行。实际应用中，一般要指定查询的条件。对记录进行过滤
语法
SELECT 字段名 FROM 表名 WHERE 条件;
流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回
准备数据
CREATE TABLE student3 (
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math int, -- 数学
english int -- 英语
);
INSERT INTO student3(id,NAME,age,sex,address,math,english)
VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
比较运算符
| 比较运算符 | 说明 |
|---|---|
>、<、<=、>=、=、<> |
<>在 SQL 中表示不等于，在 mysql 中也可以使用!= 没有==
|
BETWEEN...AND |
在一个范围之内，如：between 100 and 200 相当于条件在 100 到 200 之间，包头又包尾 |
IN(集合) |
集合表示多个值，使用逗号分隔 |
LIKE '张%' |
模糊查询 |
IS NULL |
查询某一列为 NULL 的值，注：不能写=NULL |
-- 查询 math 分数大于 80 分的学生
select * from student3 where math>80;
-- 查询 english 分数小于或等于 80 分的学生
select * from student3 where english <=80;
-- 查询 age 等于 20 岁的学生
select * from student3 where age = 20;
-- 查询 age 不等于 20 岁的学生，注：不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;
逻辑运算符
| 逻辑运算符 | 说明 |
|---|---|
and 或 && |
与，SQL 中建议使用前者，后者并不通用。 |
or或两个竖杠 |
或 |
not 或 ! |
非 |
-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)
select * from student3 where age>35 and sex='男';
-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
select * from student3 where age>35 or sex='男';
-- 查询 id 是 1 或 3 或 5 的学生
select * from student3 where id=1 or id=3 or id=5;
in关键字
SELECT 字段名 FROM 表名 WHERE 字段 in (数据 1, 数据 2...);
in 里面的每个数据都会作为一次条件，只要满足条件的就会显示 。
-- 查询 id 是 1 或 3 或 5 的学生
select * from student3 where id in(1,3,5);
-- 查询 id 不是 1 或 3 或 5 的学生
select * from student3 where id not in(1,3,5);
范围查询
BETWEEN 值 1 AND 值 2 表示从值 1 到值 2 范围，包头又包尾
比如：age BETWEEN 80 AND 100相当于： age>=80 && age<=100
查询 english 成绩大于等于 75，且小于等于 90 的学生
select * from student3 where english between 75 and 90;
like关键字
SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';
LIKE 表示模糊查询
MySQL通配符
| 通配符 | 说明 |
|---|---|
% |
匹配任意多个字符串 |
_ |
匹配一个字符 |
-- 查询姓马的学生
select * from student3 where name like '马%';
select * from student3 where name like '马';
-- 查询姓名中包含'德'字的学生
select * from student3 where name like '%德%';
-- 查询姓马，且姓名有两个字的学生
select * from student3 where name like '马_';
5.6-排序
通过 ORDER BY 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
ASC: 升序，默认值 DESC: 降序
单列排序
只按某一个字段进行排序，单列排序。
-- 查询所有数据,使用年龄降序排序
select * from student order by age desc;
组合排序
同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;
5.7-聚合函数
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询， 它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。
| SQL中的聚合函数 | 作用 |
|---|---|
| max(列名) | 求这一列中的最大值 |
| min(列名) | 求这一列的最小值 |
| avg(列名) | 求这一列的平均值 |
| count(列名) | 统计这一列有多少条记录 |
| sum(列名) | 对这一列求总和 |
语法：SELECT 聚合函数(列名) FROM 表名;
-- 查询学生总数
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;
对于 NULL 的记录不会统计，建议如果统计个数则不要使用有可能为 null 的列，但如果需要把 NULL 也统计进去呢？
IFNULL(列名，默认值) 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。
-- 查询 id 字段，如果为 null，则使用 0 代替
select ifnull(id,0) from student;
我们可以利用 IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏 。
select count(ifnull(id,0)) from student;
-- 查询年龄大于 20 的总数
select count(*) from student where age>20;
-- 查询数学成绩总分
select sum(math) 总分 from student;
-- 查询数学成绩平均分
select avg(math) 平均分 from student;
-- 查询数学成绩最高分
select max(math) 最高分 from student;
-- 查询数学成绩最低分
select min(math) 最低分 from student;
5.8-分组
分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组
SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
GROUP BY 怎么分组的
将分组字段结果中相同内容作为一组，如按性别将学生分成 2 组。
GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。 分组的目的就是为了统计，一般分组会跟聚合函数一起使用。
-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
效果如下
实际上是将每组的 math 求了平均,返回每组统计的结果
注意：当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的
查询男女各多少人
查询所有数据,
- 按性别分组。
- 统计每组人数
select sex, count(*) from student3 group by sex;
查询年龄大于 25 岁的人,按性别分组,统计每组的人数
- 先过滤掉年龄小于 25 岁的人。
- 再分组。
- 最后统计每组的人数
select sex, count(*) from student3 where age > 25 group by sex ;
查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据
-- 对分组查询的结果再进行过滤
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
having 与 where 的区别
| 子名 | 作用 |
|---|---|
where 子句 |
1. 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤 再分组。2. where 后面不可以使用聚合函数 |
having子句 |
1. having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。 2. having 后面可以使用聚合函数 |
5.9-limit语句
准备数据
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
作用
LIMIT 是限制的意思，所以 LIMIT 的作用就是限制查询记录的条数。
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句][GROUP BY 子句][HAVING 子句][ORDER BY 子 句][LIMIT 子句];
语法格式
LIMIT offset,length;
offset：起始行数，从 0 开始计数，如果省略，默认就是 0
length： 返回的行数
-- 查询学生表中数据，从第 3 条开始显示，显示 6 条。
select * from student3 limit 2,6;
使用场景
分页：比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的 条数。 假设我们每页显示 5 条记录的方式来分页
-- 如果第一个参数是 0 可以省略写：
select * from student3 limit 5;
-- 最后如果不够 5 条，有多少显示多少
select * from student3 limit 10,5;
第六章：数据库表的约束
6.1-数据库约束概述
约束的作用
对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无 法插入到表中。约束在创建表的时候添加比较合适。
约束的种类
| 约束名 | 约束关键字 |
|---|---|
| 主键 | primary key |
| 唯一 | unique |
| 不为空 | not null |
| 外键 | foreign key |
| 检查约束 | check 注：mysql 不支持 |
6.2-主键约束
主键的作用
用来唯一标识数据库中的每一条记录
哪个字段应该作为表的主键
通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用 的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。
如：身份证，学号不建议做成主键 。
创建主键
- 主键关键字：
primary key - 主键的特点：
- 非空 not null
- 唯一
- 创建主键的方式
- 在创建表的时候给字段添加主键 ：
字段名 字段类型 PRIMARY KEY ; - 在已有表中添加主键 ：
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
- 在创建表的时候给字段添加主键 ：
-- 创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键
create table st5 (
id int primary key, -- id 为主键
name varchar(20),
age int
)
desc st5;
删除主键
-- 删除 st5 表的主键
alter table st5 drop primary key;
添加主键
alter table st5 add primary key(id);
主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
-- 插入数据
insert into st6 (name,age) values ('小乔',18);
insert into st6 (name,age) values ('大乔',20);
-- 另一种写法
insert into st6 values(null,'周瑜',35);
select * from st6;
修改自增长的默认值起始值
默认地 AUTO_INCREMENT 的开始值是 1，如果希望修改起始值,请使用下列 SQL 语法 。
创建时指定起始值
CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;
-- 指定起始值为 1000
create table st4 (
id int primary key auto_increment,
name varchar(20)
) auto_increment = 1000;
insert into st4 values (null, '孔明');
修改起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;
alter table st4 auto_increment = 2000;
6.3-唯一约束
什么是唯一约束： 表中某一列不能出现重复的值
基本格式
字段名 字段类型 UNIQUE
演示
-- 创建学生表 st7, 包含字段(id, name),name 这一列设置唯一约束,不能出现同名的学生
create table st7 (
id int,
name varchar(20) unique
)
-- 添加一个同名的学生
insert into st7 values (1, '张三');
select * from st7;
-- Duplicate entry '张三' for key 'name'
insert into st7 values (2, '张三');
-- 重复插入多个 null 会怎样？
insert into st7 values (2, null);
insert into st7 values (3, null);
-- null 没有数据，不存在重复的问题
6.4-非空约束
什么是非空约束：某一列不能为 null。
基本格式
字段名 字段类型 NOT NULL
-- 创建表学生表 st8, 包含字段(id,name,gender)其中 name 不能为 NULL
create table st8 (
id int,
name varchar(20) not null,
gender char(1)
)
默认值
字段名 字段类型 DEFAULT 默认值
-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州
create table st9 (
id int,
name varchar(20),
address varchar(20) default '广州'
)
-- 添加一条记录,使用默认地址
insert into st9 values (1, '李四', default);
select * from st9;
insert into st9 (id,name) values (2, '李白');
-- 添加一条记录,不使用默认地址
insert into st9 values (3, '李四光', '深圳');
注意
疑问:如果一个字段设置了非空与唯一约束，该字段与主键的区别?
- 主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列。
- 自增长只能用在主键上。
6.5-外键约束
单表的缺点
创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 5 条数据
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
以上表的缺点：
- 数据冗余
- 后期还会出现增删改的问题
解决方案
-- 解决方案：分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int -- 外键对应主表的主键
)
-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
select * from department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;
新的问题：当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门， 实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id
目标: 需要约束 dep_id 只能是 department 表中已经存在 id
解决方式: 使用外键约束
什么是外键约束
什么是外键：在从表中与主表主键对应的那一列，如：员工表中的 dep_id
主表： 一方，用来约束别人的表
从表： 多方，被别人约束的表
创建约束的语法
- 新建表时增加外键：
[CONSTRAINT][外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) - 已有表增加外键：
ALTER TABLE 从表 ADD [CONSTRAINT][外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);
-- 1) 删除副表/从表 employee
drop table employee;
18 / 26
-- 2) 创建从表 employee 并添加外键约束 emp_depid_fk
-- 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references department(id)
)
-- 3) 正常添加数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;
删除外键
ALTER TABLE 从表 drop foreign key 外键名称;
-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;
新的问题
select * from employee;
select * from department;
-- 要把部门表中的 id 值 2，改成 5，能不能直接更新呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where id=2;
-- 要删除部门 id 等于 1 的部门, 能不能直接删除呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;
级联操作：在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
| 级联操作语法 | 描述 |
|---|---|
ON UPDATE CASCADE |
级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键 列也自动同步更新 |
ON DELETE CASCADE |
级联删除 |
-- 删除 employee 表，重新创建 employee 表，添加级联更新和级联删除
drop table employee;
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
-- 创建外键约束
constraint emp_depid_fk foreign key (dep_id) references
department(id) on update cascade on delete cascade
)
-- 再次添加数据到员工表和部门表
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
-- 删除部门表？能不能直接删除？
drop table department;
-- 把部门表中 id 等于 1 的部门改成 id 等于 10
update department set id=10 where id=1;
select * from employee;
select * from department;
-- 删除部门号是 2 的部门
delete from department where id=2;
6.6-数据约束小结
| 约束名 | 关键字 | 说明 |
|---|---|---|
| 主键 | primary key |
1) 唯一 、2) 非空 |
| 默认 | default |
如果一列没有值，使用默认值 |
| 非空 | not null |
这一列必须有值 |
| 唯一 | unique |
这一列不能有重复值 |
| 外键 | foreign key |
主表中主键列，在从表中外键列 |
第七章：表与表之间的关系
7.1-概述
现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们 在设计表的时候，就应该体现出表与表之间的这种关系！
表与表之间的三种关系
- 一对多：最常用的关系 部门和员工
- 多对多：学生选课表 和 学生表， 一门课程可以有多个学生选择，一个学生选择多门课程
- 一对一：相对使用比较少。员工表 简历表， 公民表 护照表
7.2-一对多
一对多（1:n） 例如：班级和学生，部门和员工，客户和订单，分类和商品 一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键 。
7.3-多对多
多对多（m:n） 例如：老师和学生，学生和课程，用户和角色 多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的 主键。
7.4-一对一
一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表。 两种建表原则：
- 外键唯一 ：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE
- 外键是主键 ：主表的主键和从表的主键，形成主外键关系
7.5-一对多关系案例
需求：一个旅游线路分类中有多个旅游线路
界面
表与表的关系
演示
-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
create table tab_category (
cid int primary key auto_increment,
cname varchar(100) not null unique
)
-- 添加旅游线路分类数据：
insert into tab_category (cname) values ('周边游'), ('出境游'), ('国内游'), ('港澳游');
select * from tab_category;
-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键，自动增长
rname 旅游线路名称非空，唯一，字符串 100
price 价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
create table tab_route(
rid int primary key auto_increment,
rname varchar(100) not null unique,
price double,
rdate date,
cid int,
foreign key (cid) references tab_category(cid)
)
-- 添加旅游线路数据
INSERT INTO tab_route VALUES
(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,
'2018-01-27', 1),
(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-
22', 3),
(NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州
往返 特价团】', 1699, '2018-01-27', 2),
23 / 26
(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23',
2),
(NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店
暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4);
7.6-多对多关系案例
案例描述
一个用户收藏多个线路，一个线路被多个用户收藏 。
演示
/*
创建用户表 tab_user
uid 用户主键，自增长
username 用户名长度 100，唯一，非空
password 密码长度 30，非空
name 真实姓名长度 100
birthday 生日
sex 性别，定长字符串 1
telephone 手机号，字符串 11
email 邮箱，字符串长度 100
*/
create table tab_user (
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1) default '男',
telephone varchar(11),
email varchar(100)
)
-- 添加用户数据
INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
select * from tab_user;
/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int,
date datetime,
uid int,
-- 创建复合主键
primary key(rid,uid),
foreign key (rid) references tab_route(rid),
foreign key(uid) references tab_user(uid)
)
-- 增加收藏表数据
INSERT INTO tab_favorite VALUES
(1, '2018-01-01', 1), -- 老王选择厦门
(2, '2018-02-11', 1), -- 老王选择桂林
(3, '2018-03-21', 1), -- 老王选择泰国
(2, '2018-04-21', 2), -- 小王选择桂林
(3, '2018-05-08', 2), -- 小王选择泰国
(5, '2018-06-02', 2); -- 小王选择迪士尼
select * from tab_favorite;
7.7-总结
| 表与表的关系 | 关系的维护 |
|---|---|
| 一对多 | 主外键的关系 |
| 多对多 | 中间表，两个一对多 |
| 一对一 | 1) 特殊一对多，从表中的外键设置为唯一 2) 从表中的主键又是外键 |
第八章：数据库设计
8.1-数据规范化
什么是范式
好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需 要满足一些规则来优化数据的设计和存储，这些规则就称为范式。
三大范式
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、 第四范式(4NF）和第五范式（5NF，又称完美范式）。 满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF）， 其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。
8.2-1NF
概念
数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值 时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。
班级表
8.3-2NF
概念
在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。 所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全 依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有 两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。
第二范式的特点：
- 一张表只描述一件事情。
- 表中的每一列都完全依赖于主键
示例
借书证表
分成两张表
8.4-3NF
概念
在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。 简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递 依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则 C 传递依赖于 A。因此，满足第三范 式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y
示例
学生信息表
存在传递的决定关系： 学号→所在学院 →学院地点
拆分成两张表
8.5-三大范式小结
| 范式 | 特点 |
|---|---|
| 1NF | 原子性：表中每列不可再拆分。 |
| 2NF | 不产生局部依赖，一张表只描述一件事情 |
| 3NF | 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。 |
第九章：数据库备份和还原
9.1-备份的应用场景
在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。 这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。
9.2-备份与还原的语句
备份格式： DOS 下，未登录的时候。这是一个可执行文件 exe，在 bin 文件夹
mysqldump -u 用户名 -p 密码 数据库 > 文件的路径
导出结果：数据库中的所有表和数据都会导出成 SQL 语句
-- 备份 day21 数据库中的数据到 d:\day21.sql 文件中
mysqldump -uroot -proot day21 > d:/day21.sql
还原格式：mysql 中的命令，需要登录后才可以操作
USE 数据库;
SOURCE 导入文件的路径;
还原 day21 数据库中的数据，注意：还原的时候需要先登录 MySQL,并选中对应的数据库。
- 删除 day21 数据库中的所有表
- 登录 MySQL
- 选中数据库
- 使用 SOURCE 命令还原数据
- 查看还原结果
use day21;
source d:/day21.sql;
9.3-图形化界面备份与还原
备份数据库中的数据
- 选中数据库，右键 ”备份/导出”
- 指定导出路径，保存成.sql 文件即可。
还原数据库中的数据
- 删除数据库
- 数据库列表区域右键“执行 SQL 脚本”， 指定要执行的 SQL 文件，执行即可
第十章：多表查询
10.1-多表查询介绍
数据准备
# 创建部门表
create table dept(
id int primary key auto_increment,
name varchar(20)
)
insert into dept (name) values ('开发部'),('市场部'),('财务部');
# 创建员工表
create table emp (
id int primary key auto_increment,
name varchar(10),
gender char(1), -- 性别
salary double, -- 工资
join_date date, -- 入职日期
dept_id int,
foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
)
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男
',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男
',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-
08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女
',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女
',4500,'2011-03-14',1);
为什么要多表查询？
比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。
如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。
多表查询的分类
10.2-笛卡尔积现象
什么是笛卡尔积现象？
-- 需求：查询所有的员工和所有的部门
select * from emp,dept;
结果分析
如何清除笛卡尔积无用的数据影响？
我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要 通过条件过滤掉没用的数据。
-- 设置过滤条件 Column 'id' in where clause is ambiguous
select * from emp,dept where id=5;
select * from emp,dept where emp.`dept_id` = dept.`id`;
-- 查询员工和部门的名字
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;
10.3-内连接
用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键
隐式内连接
隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定
SELECT 字段名 FROM 左表, 右表 WHERE 条件
select * from emp,dept where emp.`dept_id` = dept.`id`;
显示内连接
显示内连接：使用 INNER JOIN ... ON 语句, 可以省略 INNER
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
需求： 查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能 查询出需要的数据，使用内连接 。
1.确定查询哪些表
select * from emp inner join dept;
2.确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的
select * from emp e inner join dept d on e.`dept_id` = d.`id`;
3.确定查询条件，我们查询的是唐僧的信息，员工表.name='唐僧'
select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';
4.确定查询字段，查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称
select e.`id`,e.`name`,e.`gender`,e.`salary`,d.`name` from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';
5.我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名
select e.`id` 编号,e.`name` 姓名,e.`gender` 性别,e.`salary` 工资,d.`name` 部门名字 from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='唐僧';
内连接总结
- 确定查询哪些表
- 确定表连接的条件
- 确定查询的条件
- 确定查询的字段
10.4-左外连接
左外连接：使用 LEFT OUTER JOIN ... ON，OUTER 可以省略
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)
-- 在部门表中增加一个销售部
insert into dept (name) values ('销售部');
select * from dept;
-- 使用内连接查询
select * from dept d inner join emp e on d.`id` = e.`dept_id`;
-- 使用左外连接查询
select * from dept d left join emp e on d.`id` = e.`dept_id`;
10.5-右外连接
右外连接：使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证右表的数据全部显示
-- 在员工表中增加一个员工
insert into emp values (null, '沙僧','男',6666,'2013-12-05',null);
select * from emp;
-- 使用内连接查询
select * from dept inner join emp on dept.`id` = emp.`dept_id`;
-- 使用右外连接查询
select * from dept right join emp on dept.`id` = emp.`dept_id`;
第十一章：子查询
11.1 什么是子查询
子查询的概念
- 一个查询的结果做为另一个查询的条件
- 有查询的嵌套，内部的查询称为子查询
- 子查询要使用括号
演示
-- 需求：查询开发部中有哪些员工
select * from emp;
-- 通过两条语句查询
select id from dept where name='开发部' ;
select * from emp where dept_id = 1;
-- 使用子查询
select * from emp where dept_id = (select id from dept where name='市场部');
11.2-子查询的三种情况
子查询的结果是单行单列
子查询的结果是多行单列
**子查询的结果是多行多列 **
11.3-子查询结果是一个值
子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、= 等
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
**案例：查询工资最高的员工是谁？ **
-- 1) 查询最高工资是多少
select max(salary) from emp;
-- 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);
查询工资小于平均工资的员工有哪些？
-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);
11.4-子查询结果是多行单列
子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
**查询工资大于 5000 的员工，来自于哪些部门的名字 **
-- 先查询大于 5000 的员工所在的部门 id
select dept_id from emp where salary > 5000;
-- 再查询在这些部门 id 中部门的名字 Subquery returns more than 1 row
select name from dept where id = (select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);
查询开发部与财务部所有的员工信息
-- 先查询开发部与财务部的 id
select id from dept where name in('开发部','财务部');
-- 再查询在这些部门 id 中有哪些员工
select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));
11.5-子查询的结果是多行多列
子查询结果只要是多列，肯定在 FROM 后面作为表
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段
查询出 2011 年以后入职的员工信息，包括部门名称
-- 查询出 2011 年以后入职的员工信息，包括部门名称
-- 在员工表中查询 2011-1-1 以后入职的员工
select * from emp where join_date >='2011-1-1';
-- 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;
也可以使用表连接
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1';
select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';
11.6-总结
- 子查询结果只要是单列，则在 WHERE 后面作为条件
- 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询
第十二章：事务
12.1-事务概述
事务的应用场景
什么是事务： 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转 账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。
事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。
转账操作
模拟张三给李四转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句： 张三账号-500 李四账号+500
-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);
-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';
假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元，数据就出现问题了。我们需要保证其中 一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。
12.2-手动提交事务
MYSQL 中可以有两种方式进行事务的操作：
- 手动提交事务
- 自动提交事务
手动提交事务的SQL
| 功能 | SQL语句 |
|---|---|
| 开启事务 | start transaction; |
| 提交事务 | commit; |
| 回滚事务 | rollback; |
手动提交事务的执行过程
- 执行成功的情况：
开启事务 → 执行多条 SQL 语句 → 成功提交事务 - 执行失败的情况：
开启事务 → 执行多条 SQL 语句 → 事务的回滚
案例1：模拟张三给李四转 500 元钱（成功） 目前数据库数据如下
1- 使用 DOS 控制台进入 MySQL
2- 执行以下 SQL 语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500
3- 使用 SQLYog 查看数据库：发现数据并没有改变
4- 在控制台执行 commit 提交事务：
5- 使用 SQLYog 查看数据库：发现数据改变
**案例演示 2：事务回滚 **
模拟张三给李四转 500 元钱（失败） 目前数据库数据如下：
1- 在控制台执行以下 SQL 语句：1.开启事务， 2.张三账号-500
2- 使用 SQLYog 查看数据库：发现数据并没有改变
3- 在控制台执行 rollback 回滚事务：
4- 使用 SQLYog 查看数据库：发现数据没有改变
总结: 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。
12.3-自动提交事务
概述
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕 自动提交事务，MySQL 默认开始自动提交事务
案例：自动提交事务
- 将金额重置为 1000
- 更新其中某一个账户
- 使用 SQLYog 查看数据库：发现数据已经改变
取消自动提交
查看 MySQL 是否开启自动提交事务
@@表示全局变量，1 表示开启，0 表示关闭
**取消自动提交事务 **
执行更新语句，使用 SQLYog 查看数据库，发现数据并没有改变 ,在控制台执行 commit 提交任务
12.4-事务原理
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表 中，其他任何情况都会清空事务日志(rollback，断开连接)
原理图
事务的步骤
- 客户端连接数据库服务器，创建连接时创建此用户临时日志文件
- 开启事务以后，所有的操作都会先写入到临时日志文件中
- 所有的查询操作从表中查询，但会经过日志文件加工后才返回
- 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件
12.5-回滚点
什么是回滚点
在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成 功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称 之为回滚点。
回滚点的操作语句
| 操作 | 语句 |
|---|---|
| 设置回滚点 | savepoint 名字 |
| 回到回滚点 | rollback to 名字 |
具体操作
-
将数据还原到 1000
-
开启事务
-
让张三账号减 3 次钱，每次 10 块
-
设置回滚点：savepoint three_times;
-
让张三账号减 4 次钱，每次 10 块
-
回到回滚点：rollback to three_times;
-
分析执行过程
设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。
12.6-事务的四大特性
| 事务特性 | 含义 |
|---|---|
| 原子性（Atomicity） | 每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都失败。 |
| 一致性（Consistency） | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的 总金额是 2000，转账后 2 个人总金额也是 2000 |
| 隔离性（Isolation） | 事务与事务之间不应该相互影响，执行时保持隔离的状态。 |
| 持久性（Durability） | 一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。 |
12.7-事务的隔离级别
隔离级别介绍
概述
事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个 数据。可能引发并发访问的问题：
| 并发访问的问题 | 含义 |
|---|---|
| 脏读 | 一个事务读取到了另一个事务中尚未提交的数据 |
| 不可重复度 | 一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这 是事务 update 时引发的问题 |
| 幻读 | 一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致 的，这是 insert 或 delete 时引发的问题 |
MySQL 数据库有四种隔离级别
上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。
| 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 |
|---|---|---|---|---|---|---|
| 1 | 读未提交 | read uncommitted |
是 | 是 | 是 | |
| 2 | 读已提交 | read committed |
否 | 是 | 是 | Oracle 和 SQL Server |
| 3 | 可重复读 | repeatable read |
否 | 否 | 是 | MySQL |
| 4 | 串行化 | serializable |
否 | 否 | 否 |
隔离级别越高，性能越差，安全性越高。
MySQL 事务隔离级别相关的命令
- 查询隔离级别
select @@tx_isolation; - 设置隔离级别
set global transaction isolation level 级别字符串;
设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
脏读问题及解决方案演示
演示
将数据进行恢复：UPDATE account SET balance = 1000;
【01-打开 A 窗口登录 MySQL，设置全局的隔离级别为最低】
mysql -uroot -proot
set global transaction isolation level read uncommitted;
【02-打开 B 窗口,AB 窗口都开启事务】
【03-A 窗口更新 2 个人的账户数据，未提交】
update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;
【04-B 窗口查询账户】
【05-A 窗口回滚 】
【06-B 窗口查询账户，钱没了 】
脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱 已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。
解决脏读的问题：将全局的隔离级别进行提升
将数据进行恢复：
UPDATE account SET balance = 1000;
【01-在 A 窗口设置全局的隔离级别为 read committed 】
set global transaction isolation level read committed;
【02-B 窗口退出 MySQL，B 窗口再进入 MySQL 】
【03-AB 窗口同时开启事务 】
【04-A 更新 2 个人的账户，未提交 】
update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;
【05-B 窗口查询账户】
【06-A 窗口 commit 提交事务 】
【08-B 窗口查看账户 】
结论：read committed 的方式可以避免脏读的发生
不可重复读问题及解决方案
问题演示
将数据进行恢复：
UPDATE account SET balance = 1000;
【01-开启 A 窗口 】
set global transaction isolation level read committed;
【02-开启 B 窗口，在 B 窗口开启事务 】
start transaction;
select * from account;
【03-在 A 窗口开启事务，并更新数据 】
start transaction;
update account set balance=balance+500 where id=1;
commit;
【04-B 窗口查询 】
两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客 户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作 人员就不知道以哪个为准了。
解决不可重复读的问题
将全局的隔离级别进行提升为：repeatable read 将数据进行恢复：
UPDATE account SET balance = 1000;
【01-A 窗口设置隔离级别为：repeatable read 】
set global transaction isolation level repeatable read;
【02-B 窗口退出 MySQL，B 窗口再进入 MySQL ，开启事务，查询数据】
start transaction;
select * from account
【03-A 窗口更新数据 】
start transaction;
update account set balance=balance+500 where id=1;
commit;
【04-B 窗口查询 】
结论：同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别 。
幻读的演示
在 MySQL 中无法看到幻读的效果。
但我们可以将事务隔离级别设置到最高，以挡住幻读的发生 将数据进行恢复：
UPDATE account SET balance = 1000;
【01-开启 A 窗口 】
set global transaction isolation level serializable; -- 设置隔离级别为最高
【02-A 窗口退出 MySQL，A 窗口重新登录 MySQL 】
start transaction;
select count(*) from account;
【03-再开启 B 窗口，登录 MySQL 】
【04-在 B 窗口中开启事务，添加一条记录 】
start transaction; -- 开启事务
insert into account (name,balance) values ('LaoWang', 500);
【05-在 A 窗口中 commit 提交事务，B 窗口中 insert 语句会在 A 窗口事务提交后立马运行 】
【06-在 A 窗口中接着查询，发现数据不变 】
【07-B 窗口中 commit 提交当前事务 】
【08-A 窗口就能看到最新的数据 】
结论：使用 serializable 隔离级别，一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读
第十三章：DCL
我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面 可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管 理和维护数据库。 注：mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。
13.1-创建用户
语法
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
关键字说明
| 关键字 | 说明 |
|---|---|
| 用户名 | 将创建的用户名 |
| 主机名 | 指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以 从任意远程主机登陆，可以使用通配符% |
| 密码 | 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器 |
具体操作
-- 创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123
create user 'user1'@'localhost' identified by '123';
-- 创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123
create user 'user2'@'%' identified by '123';
注：创建的用户名都在 mysql 数据库中的 user 表中可以查看到，密码经过了加密。
13.2-给用户授权
用户创建之后，没什么权限！需要给用户授权
语法
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
关键字说明
| 关键字 | 说明 |
|---|---|
GRANT…ON…TO |
授权关键字 |
权限 |
授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授 予所有的权限则使用 ALL
|
数据库名.表名 |
该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作 权限则可用*表示，如 *.*
|
'用户名'@'主机名' |
给哪个用户授权，注：有 2 对单引号 |
具体操作
-- 给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';
-- 给 user2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';
-- 注：用户名和主机名要与上面创建的相同，要加单引号。
13.3-撤销授权
语法
REVOKE 权限 1, 权限 2... ON 数据库.表名 revoke all on test.* from 'user1'@'localhost'; '用户名'@'主机 名';
| 关键字 | 说明 |
|---|---|
| REVOKE…ON…FROM | 撤销授权的关键字 |
| 权限 | 用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等，所有的权 限则使用 ALL |
| 数据库名.表名 | 对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用 *表 示，如*.*
|
| '用户名'@'主机名' | 给哪个用户撤销 |
具体操作
-- 撤销 user1 用户对 test 数据库所有表的操作的权限
revoke all on test.* from 'user1'@'localhost';
13.4-查看权限
语法
SHOW GRANTS FOR '用户名'@'主机名';
具体操作
注：usage 是指连接（登陆）权限，建立一个用户，就会自动授予其 usage 权限（默认授予）。
13.5-删除用户
语法
DROP USER '用户名'@'主机名';
具体操作
-- 删除 user2
drop user 'user2'@'%';
13.6-修改管理员密码
语法
mysqladmin -uroot -p password 新密码
注意：需要在未登陆 MySQL 的情况下操作，新密码不需要加上引号。
具体操作
-
将 root 管理员的新密码改成 123456
-
要求输入旧密码
-
使用新密码登录
13.7-修改普通用户密码
语法
set password for '用户名'@'主机名' = password('新密码');
注意：需要在登陆 MySQL 的情况下操作，新密码要加单引号。
具体操作
-
将'user1'@'localhost'的密码改成'666666'
-
使用新密码登录，老密码登录不了
网友评论