MySql【后面附有练习题】

第一章：MySql基础

提醒： 使用mysql命令之前，一定要配置环境变量 ；并且这个MySql的版本是8.0的；

1.1 配置MySql的环境变量

本人是新手，刚开始学，有错的地方请大佬们提出来哦！！！

配置C:\Program Files\MySQL\MySQL Shell 8.0\bin\ 的环境变量
目的： 可以直接操作mysqlsh.exe文件，连接数据库。

这里配置C:\Program Files\MySQL\MySQL Server 8.0\bin环境变量；
目的： 方便使用，每次不用找mysql.exe来启动了，可以直接在命令行输入mysql的相关命令就好了。

1.2 MySql常用登录命令

登录方式
mysql -u 账户名-p 密码：默认登录本机的3306端口；
mysql -h 127.0.0.1 -u 账户名 -p 密码；
mysql -h 127.0.0.1 -P3306（端口号）-u
账号 -p密码： 指定ip和端口

查看编码
show variables like ‘char%’;
show databases:显示所有的数据库
use dbname:打开指定的数据库
show tables;显示当前数据库下所有的表

通过命令行来操作mysql

创建表

create table tblname(
	sid int,
	sname varchar(30)
);

插入记录

insert into tblname(sid,sname)values(1,'张三');

查看记录

select * from tblname;

查看表结构

desc tblname;

1.3 MySql的语法规范和要求

1. mysql的sql语法是不区分大小写的
   注意： MySQL的关键字和函数名等不区分大小写，但是对于数据值是否区分大小写，和字符集与校对规则有关。
2. 命名时：尽量使用26个英文字母大小写，数字0-9，下划线，不要使用其他符号
3. 建议不要使用mysql的关键字等来作为表名、字段名等，如果不小心使用，请在SQL语句中使用`（飘号）引起来
4. 数据库和表名、字段名等对象名中间不要包含空格
5. 同一个mysql软件中，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名
6. 标点符号：
   必须成对
   必须英文状态下半角输入方式
   字符串和日期类型可以使用单引号’’
   列的别名可以使用双引号""，给表名取别名不要使用双引号。取别名时as可以省略
   如果列的别名没有包含空格，可以省略双引号，如果有空格双引号不能省略。
7. SQL语句加注释
   单行注释 ： #注释内容
   单行注释 ： – 空格注释内容，其中-- 后面的空格必须有
   多行注释： /* 注释内容 */

1.4 SQL 语句的分类

1.5 数据库列的数据类型

1.5.1 数值类型

1.5.2 字符类型

注意：
① char 和 varchar（M）的区别：
char 如果没有指定宽度，默认为1个字符。
varchar（M），必须指定宽度。

②：binary和varbinary类似于char和varchar，不同的是它们包含二进制字符串，不支持模糊查询之类的。

③：一般在保存少量字符串的时候，我们会选择char和varchar；而在保存较大文本时，通常会选择使用text或blob系列。blob和text值会引起一些性能问题，特别是在执行了大量的删除操作时，会在数据表中留下很大的“空洞”，为了提高性能，建议定期时候用optimize table功能对这类表进行碎片整理。可以使用合成的(Synthetic)索引来提高大文本字段的查询性能，如果需要对大文本字段进行模糊查询，MySql提供了前缀索引。但是仍然要在不必要的时候避免检索大型的blob或text值。

1.5.3日期类型

1.6 MySQL中表达式

算术运算符

’ + ’ , '- ', ’ * ‘,
’ / ‘，’%’(mysq中有特殊含义，表示任意个任意字符)

比较运算

1. 大于：>
2. 小于：<
3. 大于等于：>=
4. 小于等于：<=
5. 等于：= 不能用于null判断
6. 不等于：!= 或 <>
7. 安全等于: <=> 可以用于null值判断

逻辑运算

1. 逻辑与：&& 或 and
2. 逻辑或：|| 或 or
3. 逻辑非：! 或 not
4. 逻辑异或：^ 或 xor

关于运算范围的运算符

区间范围： between x and y
not between x and y
集合范围： in(x,x,x)
not in (x,x,x)

1.7 null 和 空值的区别

• null没有值，未知；
• null占用内存空间，而空值（“”）不占内存空间；
• 不要使用null值进行计算，而空值可以；
• ""表示空字符串，长度为0，不占内存空间

1.8 数据库的常用字段属性

1.8.1 主键

它是一种特殊的索引，它的值不能为空，并且它的值必须唯一；

1.8.2 非空

是否为空，默认为null，并且null 表示空， 它是非字符串；
如果设为not null，那就表示该列必须有值；

1.8.3 Unsigned

无符号，表示该列只能为整数，不能为负数；

1.8.4 自增

如果某列设为自增列，当你插入数据时，没有给该字段赋值，那么默认会自增；
一般它是给主键加的，默认从1开始；也可以设置步长和起始值；

	 -- 只设置当前数据库连接的步长和起始值
	 set session auto_increment_increment=2;
     set session auto_increment_offset=10;
     ************************************************
     -- 设置全局连接的步长和起始值
     set global auto_increment_increment=2;
     set global auto_increment_offset=10;

1.8.5 Zerofill

默认是用0来填充的，当位数不足直接所规定的，默认就填0；

1.8.6 默认

创建字段时可以给默认值，当插入数据时并没有给该字段赋值，则自动会添加默认值；

1.9 多表之间的关系

1.9.1 一对一关系

第一种的一对一关系：

在一对多关系中主表的一行数据可以对应从表的多行数据，反之从表的一行数据则只能对应主表的一行数据。这种一行数据对应一行数据的关系，我们可以将其看作一对一关系。

第二种的一对一关系：
A表中的一行数据对应B表中的一行数据，反之B表中的一行数据也对应A表中的一行数据，此时我们可以将A表当做主表B表当做从表，或者是将B表当做主表A表当做从表。

1.9.1.1一对一关系建表原则

在从表中指定一个字段创建外键并指向主表的主键，然后给从表的外键字段添加唯一约束

1.9.2 一对多关系

概念：一对多的关系是指: 主表的一行数据可以同时对应从表的多行数据，反过来就是从表的多行数据指向主表的同一行数据。

1.9.2.1一对多关系建表原则

将少的一方作为主表，多的一方作为从表，在从表中指定一个字段作为外键，指向主表的主键

1.9.3 多对多关系

概念： 两张表都是多的一方，A表的一行数据可以同时对应B表的多行数据，反之B表的一行数据也可以同时对应A表的多行数据

1.9.3.1多对多关系建表原则

因为两张表都是多的一方，所以在两张表中都无法创建外键，所以需要新创建一张中间表，在中间表中定义两个字段，这俩字段分别作为外键指向两张表各自的主键

第二章：数据库的范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
关系数据库有六种范式：第一范式（1NF），第二范式（2NF），第三范式（3NF），巴斯-科德范式（BCNF），第四范式（4NF），第五范式（5NF，又称完美范式）

2.1 第一范式（1NF）

第一范式是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项；

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）

2.2 第二范式（2NF）

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示

这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示

这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可

2.3 第三范式（3NF）

在第二范式的基础上，任何非主（属性）不依赖于其他非主属性（在第二范式的基础上消除传递依赖）
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余 。

简单来说：
第一范式： 列不可在分割
第二范式： 记录不重复，通过主键
第三范式：消除冗余，通过主外键（表不可再分）

第三章：操作数据库、操作表

两种方式：

1. 可视化界面操作，建库、建表、加约束；
2. SQL

3.1 DDL操作数据库

3.1.1 创建数据库

语法：

create database [if not exists] 数据库名;

例：创建一个day01的数据库，指定字符集utf8

create database if not exists 数据库名 character set utf8;

3.1.2 查看数据库

3.1.2.1 查看所有数据库

语法：

show databases;

3.1.2.2 查看数据库的定义结构

语法：

show create database 数据库名;

3.1.2.3 查看正在使用的数据库

select database();

3.1.3 删除数据库

语法：

drop database 数据库名;

3.1.4 修改数据库

语法：

alter database 数据库名 character set 字符集;

注意：字符集中的utf8，不是utf-8;
也不是修改数据库名

3.1.5 使用数据库

use 数据库名;

3.2 DDL操作表

3.2.1 创建表

create table 表名(
	列名 类型 [约束],
	列名 类型 [约束],
	...
);

注意：创建表时最后一行不用写逗号，否则会报语法错误。

3.2.2 修改表

修改表名； 对字段的添加删除修改操作会在下一小节讲解；

alter table 旧表名 rename as 新表名;

3.2.3 删除表

drop table if exists 表名;

3.2.4 查看表

3.2.4.1 查看所有表

show tables;

3.2.4.2 查看表的定义结构

desc 表名;

3.4 对字段增删改

3.4.1 添加字段

alter table 【数据库名.]表名称 add 【column】 字段名 数据类型;
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 first;
alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 after 另一个字段;

3.4.2 修改字段

alter table 表名 modify 字 类型 [约束];

3.4.3 修改字段的名称

alter table 表名 change 旧字段 新字段 类型 [约束];

3.4.4 删除一列字段

alter table 表名 drop 字段

3.3 约束

概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。

约束分类：
①：默认约束：给字段指定一个默认值，当向这个表中插入数据时，如果没有为该字段提供具体的值，那么该字段使用指定的默认值；
②：主键约束：该字段的值不能重复，且不可以为null。
③：非空约束：如果设置了非空，那么意思是该字段的值不能为null，但是可以是空字符串。
④：外键约束：两个表数据之间建立链接，使数据更加完整，其中一张表中的字段对应着另外一张表中的字段。其中，被约束的表叫做从表（子表），另外一张表叫做主表（父表），属于主从关系。主表中的关联字段叫做主键，从表中的关联字段叫做外键。
⑤：检查约束：用来检查字段的值是否合法；
⑥：唯一约束：字段的值不能重复，且可以为null；
⑦： 自增约束: 用在该字段属性为整型的，每次添加一个记录，该字段会自动加上去，步长默认为1，初始值默认为0；这里可以自己设置步长和初始值，上面已经说过怎么设置步长和初始值了。

问题：主键约束和唯一约束的区别？
主键约束： 唯一，且不能为空
唯一约束：可以为null， 唯一不能重复
那么问题又来了：唯一约束可以为null，那么他可以有几个null？
多个null。

可能有一些初学者对这些概念不懂，下面会给大家举例来说明的。

3.3.1 添加约束

这里给字段加约束有三种方式，
①：是在可视化图界面加约束条件
②：在创建表时就添加约束
③：创建完表之后添加约束

3.3.1.1在可视化图界面加约束

这里我会拿SQLyog 讲解。

1. 首页打开数据库，点到root@localhost,鼠标单击右键，点击创建数据库。
   
2. 他会弹出这个界面，输入好数据库名后，就点确定，剩下的两个默认就可以了。
   
3. 之后，你在数据库中找好你创建的，我这里创建的数据库名为one，然后点到表，鼠标单击右键，选创建表。
   如果你已经创建好表，想修改约束，那么就点击你创建好的表，单击鼠标右键，点击改变表。
   
4. 会到以下界面，然后根据自己的需要添加字段属性就好了。
   
5. 点击索引，这里在索引类型这里选择字段需要的约束，经常选的有主键和唯一约束；
   
6. 可以在外部键这里引用外键约束；
7. 在Check Constraint 添加检查约束；

3.3.1.2 在创建表时就添加约束

再创建表时添加外键约束时注意一下它的语法：

[CONSTRAINT 外键约束名] FOREIGN KEY(子表引用外键的字段) REFERENCES 主表名(主表要引用的字段)
 -- 外键只能在所有字段列表后面单独指定
 -- 如果要自己命名外键约束名，建议 主表名_从表名_关联字段名_fk

这里直接给他大家举例吧，这样更好理解。

-- 创建部门表
CREATE TABLE dept(
	deptno INT AUTO_INCREMENT PRIMARY KEY,		-- 在外键约束中这个字段作为了主键。
	dname VARCHAR(10) NOT NULL UNIQUE,		-- 设置了非空和不为空约束;
	loc VARCHAR(10)
);

-- 创建员工表
CREATE TABLE emp(
	empno INT  AUTO_INCREMENT PRIMARY KEY, 		-- 设置自增和主键约束;
	ename VARCHAR(10) NOT NULL UNIQUE,		-- 设置了非空和不为空约束;
	sal FLOAT CHECK (sal < 8000 AND sal > 2000), 	-- 设置检查约束
	sex CHAR(1) DEFAULT '男',			-- 设置了默认约束
	dept_id INT,					-- 这个是外键
	FOREIGN KEY(dept_id) REFERENCES dept(deptno)    -- 设置了外键约束，emp约束dept表，所以emp作为子表，dept作为主表,dept_id作为外键
);

运行结果：

注意：在引用外键约束时,必须先创建主表，才能用子表引用外表。

3.3.1.3 创建表完成之后添加约束

3.3.1.3.1 添加主键约束

ALTER TABLE 表名 [CONSTRAINT 约束名] ADD PRIMARY KEY(字段名);

-- 例子
ALTER TABLE dept ADD CONSTRAINT dad PRIMARY KEY(deptno);
ALTER TABLE dept ADD PRIMARY KEY(deptno);

3.3.1.3.2 添加唯一约束

注意: 如果不加[CONSTRAINT 约束名]的话，它的默认约束名是它的字段名

ALTER TABLE 表名 ADD [CONSTRAINT 约束名] UNIQUE(字段名);

-- 例子
ALTER TABLE dept ADD CONSTRAINT unique_deptno UNIQUE(deptno);
ALTER TABLE dept ADD UNIQUE(deptno);

3.3.1.3. 添加自增约束

注意： 在设置自增约束前，必须指定该字段为主键或者是唯一约束。

ALTER TABLE 表名 MODIFY 字段名 INT AUTO_INCREMENT;

-- 例子
ALTER TABLE dept MODIFY deptno INT AUTO_INCREMENT;

3.3.1.4. 添加非空约束

ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

-- 例子
ALTER TABLE dept MODIFY dname VARCHAR(10) NOT NULL;

在修改表示添加非空约束

-- 就字段名和新字段名可以一致。
ALTER TABLE dept CHANGE 旧字段名 新字段名 VARCHAR(10) NOT NULL;

-- 例子
ALTER TABLE dept CHANGE dname dname VARCHAR(10) NOT NULL;

3.3.1.5. 添加默认约束

ALTER TABLE 表名 MODIFY 字段名 数据类型 DEFAULT 默认值;

-- 例子
ALTER TABLE dept MODIFY sex VARCHAR(10) DEFAULT '男';

3.3.1.6. 添加检查约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(字段名 字段值的设定条件);

-- 例子
ALTER TABLE dept ADD CONSTRAINT age CHECK(age BETWEEN 20 AND 60);

3.3.1.7. 添加外键约束

注意： 如果要自己命名外键约束名，建议 主表名_从表名_关联字段名_fk

ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY(关联字段名) REFERENCES 主表名(被关联字段名);

-- 例子
ALTER TABLE emp ADD CONSTRAINT dept_emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(deptno);

3.3.2 删除约束

3.3.2.1 在可视化图界面删除约束

跟上面加约束的操作差不多，唯一要注意的是删除外键约束时，需要先删除从表的关联字段，才能删除主表的关联字段。

3.3.2.2 创建表完成之后删除约束

3.3.2.2.1 删除主键约束

ALTER TABLE 表名 DROP PRIMARY KEY;

-- 例:
ALTER TABLE dept DROP PRIMARY KEY;

3.3.2.2.2 删除唯一约束

ALTER TABLE 表名 DROP INDEX 唯一约束名;

-- 例:
ALTER TABLE dept DROP INDEX unique_deptno;

3.3.2.2.3 删除自增约束

-- 这里的旧字段名和新字段名可以相同，这个会把其他的约束也会同时删除；
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;

-- 例:
ALTER TABLE dept CHANGE deptno deptno INT;

3.3.2.2.4 删除非空约束

ALTER TABLE 表名 MODIFY 字段名 数据类型 NULL;
-- 例:
ALTER TABLE dept MODIFY dname VARCHAR(10)  NULL;

3.3.2.2.5 删除默认约束

-- 使用这种方法会把其他的约束也会同时删除；
ALTER TABLE 表名 MODIFY 字段名 VARCHAR(100);

-- 这种只会默认删除默认约束
ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT;
-- 例:
ALTER TABLE dept ALTER COLUMN loc DROP DEFAULT;

3.3.2.2.6 删除检查约束

ALTER TABLE 表名 DROP CHECK 约束名;
-- 例:
ALTER TABLE dept DROP CHECK age;

3.3.2.2.7 删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;
-- 例:
ALTER TABLE dept DROP FOREIGN KEY dept_emp_dept_id_fk;

第四章： DML操作表数据

4.1 插入记录(insert)

-- 插入指定列，如果没有把这个列列出来，以null进行自动赋值
insert into 表名(列,列,...) values (值,值,...);
-- *************************************************
-- 插入多条数据
insert into 表名(列,列,...) values(值,值,...),(值,值,...),(值,值,...)...
-- *************************************************
-- 省略字段,这样必须要插入该表的所有字段，否则会报错;
-- 如果该字段的值可以null，那么有你不想插入的列，可以直接赋值为 null;
insert into 表名 values (值,值,...);
-- *************************************************
-- insert 支持子查询
insert into 表名(列,列,...)values(列,列,(使用select查询的一个字段))
-- *************************************************
-- 使用union
insert into 表名 
select 列,列,....
union
select 列,列,...
union
....
-- *************************************************
-- 特殊用法  可以方便实现个别列赋值插入
insert into 表名 set 列=值,列=值;

注意：
①：如果你插入的字段设为了非空，那么就不能插入null值，不然会报错；但是可以插入空字符串。
②：如果省略了表名后的字段，那么必须要插入该表全部字段的值;否则，会报错误;

4.2 更新记录(update)

update 表名 set 字段1=值1,字段2=值2,...[where 条件]

4.3 删除记录(delete)

4.3.1 delete

删除表中的一条记录

delete from 表名 [where 条件]

删除表中所有的记录（但不会删除表）

delete from 表名;

4.3.2 truncate

会把表直接删掉，然后创建一个同样的新表。删除的数据不能找回，执行速度比delete快。

truncate table 表;

4.3.3 delete，truncate，drop的区别

相同点： 都可以清空表的内容

不同点：

1. delete属于dml操作，truncate,drop属性ddl操作；
2. 在速度上，一般来说，drop > truncate > delete；
3. 使用truncate后自增字段从头开始计时，delete还是保留之前的自增值，而drop是直接将表删除。
4. 想保留表而将所有数据删除，如果和事务无关，就使用truncate；如果和事务有关，或者想触发trigger，还是用delete；想删除表，直接用drop；
5. truncate只可以作用在table，而drop，delete可以作用在table，table；
6. 当表被truncate，这个表的索引和所占用的空间会恢复到初始值大小，而delete不会减少表或索引所占用的空间，drop语句将表所占用的空间都会释放；
7. 想删除所有记录使用truncate；想删除部分记录可以使用delete语句；
   想删除表，使用drop；

注意： 如果不想删除表，但想删除表中所有记录，切记不能用drop；

第五章：DQL查询表记录

语法：
SELECT
{ * | <字段列名> }
[
FROM <表1>,<表2>…
[ GROUP BY
[HAVING [ {}… ] ]
[ORDER BY ]
[LIMIT [ , ]]
]

5.1 基本查询

select 要查询的字段名 from 表名 [where 条件]

这里要查询的字段名可以有多样：可以是表中的一个字段，可以是表达式，可以是聚合函数，也可以是变量或常量，这些会在下边依次为大家讲解。

5.1.1 去重查询

select distinct 字段名 from 表名;
--  同一列数据中相同的值才能去重.

注意：这个去重是针对某列，一定要注意distinct前面不能出现列名，否则会造成语法错误。

5.2.1 别名查询

5.2.1.1 字段名起别名

注意： 这里的as可以省略，省略后，字段名和别名需要空开一个空格，字段名起别名可以加双引号或单引号，但是给表名起别名不能加。

select 字段名 as 别名, 字段名 as 别名 from 表

5.1.1.2 给表起别名

注意： 这里的as也可以省略，但也要注意有空格，要不然会报语法错误！还有一个注意点，给表起别名时不能加 双引号或者单引号，要不然也报错。

select 别名.字段名 from 表 as 别名

5.2.2 使用表达式

select 表达式 别名;

-- 如果各个字段是整型的，那么可以可以使用运算符
-- +  -  *  /  %
select 字段名(运算符)字段名 from 表名

5.2 条件查询

语法：

select * from 表 where 条件;

这里给大家举例可能会好理解，所以我们先写个环境，之后方便大家使用，在结尾我会给大家出个练习题来试试，练练手哈。

5.2.0 搭建条件查询环境

在搭建环境中有看不懂的语句大家先别着急，后边会为大家一一讲解；
创建部门表和员工表

-- 创建员工表
CREATE TABLE dept(
	deptno INT AUTO_INCREMENT PRIMARY KEY,
	dname VARCHAR(10),
	loc VARCHAR(10)
);

-- 创建员工表
CREATE TABLE emp(
	empno INT  AUTO_INCREMENT PRIMARY KEY,
	ename VARCHAR(10),
	sal FLOAT,
	sex char(1),
	dept_id INT,
	FOREIGN KEY(dept_id) REFERENCES dept(deptno)  -- emp依赖dept表，所以dept作为主表，dept_id作为外键
);

-- 给部门表插入数据
-- 因为deptno设置了自增，所以插入时可以省略deptno,他会默认+1
INSERT INTO dept(dname,loc) VALUES('人事部','19楼');
INSERT INTO dept(dname,loc) VALUES('技术部','19楼');
INSERT INTO dept(dname,loc) VALUES('开发部','18楼');

-- 给员工表插入数据
INSERT INTO emp VALUES (1,'张三',6500,2),
(2,'张四',8500,'男',1),
(3,'张五',9500,'男',3),
(4,'李三',5500,'男',1),
(5,'李三',4500,'女',2),
(6,'赵i三',16500,'男',3),
(7,'王三',8500,'男',2),
(8,'刘三',9500,'女',1),
(9,'赵六三',15500,'女',2),
(10,'赵琦',12000,'男',1);

这里的条件有六种；

5.5.1 比较运算符

比较运算符有： >，<，>=，<=，=(不能判断null)，！=（<>）,<=>(可以判断null)
语法：

select * from 表 where 字段名 比较运算符 值

例：

-- 查询性别为男的人数
select * from emp where sex = '男';

5.2.2 逻辑运算符

逻辑运算符有：and，or，not，xor(异或)
语法：

select * from 表名 where [字段名 比较运算符 值] 逻辑运算符 字段名 比较运算符 值

例：

-- 查询性别为男的人数，且姓名为李三
select * from emp where sex = '男' && ename = '李三';

5.2.3 范围

范围的运算符有：
区间范围：bewteen x and y，not between x and y
集合范围： in (x,x,x)，not in (x,x,x)
语法：

-- 区间范围,适用于整型或浮点型
select * from 表名 where 字段名 between 值 and 值;

-- 集合范围
select * from 表名 where 字段名 in(值,值,值);

例：

-- 查询员工工资在3000-6000的员工;
select * from emp where sal between 3000 and 6000;

5.2.4 对null值做判断

①： 使用 字段 is null；
②： 使用 字段 is not null;
③： 使用 字段 <=> null
语法：

select * from 表名 where 字段对空的判断

例：

-- 查询没有部门的员工;
select * from emp where dept_id is null;

5.2.5 使用like关键字(针对字符串)

like ‘xxx’ 模糊查询是处理字符串的时候进行部分匹配
如果想要表示0~n个字符，用%
如果想要表示确定的1个字符，用_

语法：

select * from 表名 where 字段名 like '要匹配的字符串';

例：

-- 查询姓李的员工
select * from emp where ename like '李%';

5.2.6 可以嵌套DQL语句

注意： 如果使用嵌套的，最里面查出的字段一定是单列的
语法:

select * from 表名 where 字段名 = (select 字段名 from 表名 where ...)

例：

-- 查询员工信息，且部门在技术部的
select * from emp where dept_id= (select deptno from dept where dname = '技术部');

5.2.7 使用case when

语法：

case when 条件 then 值 else 值 end

例：

-- 查询员工工资属于高工资，低工资还是一般工资
-- >=10000 高工资 >=6000 and < 10000  一般 <6000 低
SELECT empno,ename,
(
CASE 
	WHEN sal >= 10000 THEN '高工资'
	WHEN sal >= 6000 THEN '一般工资'
	ELSE ' 低工资'
	END 
) 工资等级
FROM emp;

5.3 函数

5.3.1 聚合函数

函数名	用途
max()	最大值
min()	最小值
avg()	平均值
sum()	总和
count()	记录总数
注意： 聚合函数不能嵌套使用。并且聚合函数会忽略空值NULL
例：

-- 求员工工资的最大值
select MAX(sql) from emp;

-- 求员工工资的最小值
select MIN(sql) from emp;

-- 求员工工资的平均值
select AVG(sql) from emp;

-- 求员工工资的总和
select SUM(sql) from emp;

-- 求员工人数
select COUNT(empno) from emp;

5.3.2 常用函数

5.3.2.1 字符串函数

函数	作用
concat(str1,str2)	将多个字符串进行连接
concat_ws(separator,str1,str2…)	指定间隔符，将多个字符串连接
upper(str)	将str全部大写
lower(str)	将str全部小写
trim(str)	将str两边空格去除
substring(str，pos)	从第pos个字符开始获取子串
substring(str，pos，len)	从第pos个字符开始获取长度为len的子串

5.3.2.2 数值函数

函数	作用
ceil(x)	返回大于x的最小整数值
floor(x)	返回小于x的最大整数值
mod(x,y)	返回x / y的余数
abs(x)	返回x的绝对值
rand()	返回0~1的随机数
round(x,y)	返回参数x的四舍五入的有y位的小数的值
truncate(x,y)	返回数字x截断为y位小数的结果
sqrt(x)	返回 x 的平方根
pow(x,y)	返回x的y次方

5.3.2.3 常用日期函数

函数	作用
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前系统日期时间
weekday()	返回周几，注意：周一是0，周日是6
dayname()	返回日期（日期的英文）
monthname()	返回月份
datediff(date1,date2)	返回date1 - date2 的日期间隔

5.3.2.4 流程函数

函数	作用
if(value,t,f)	如果value是真，返回t，否则返回f
ifnull(value1,value2)	返回value1不为空，返回value1，否则返回value2
case when 条件1 then result1 when 条件2 then result2…[else return] end	相当于Java的if…else…
case 值 when 常量值1 then 值1 when 常量值1 then 值1 … [else 值n] end	相当于Java的switch…case…

5.3.2.5 其他函数

函数	作用
database()	返回当前数据库名
version()	返回当前数据库版本
user()	返回当前登录用户名

5.4 分组查询

GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。
分组的目的就是为了统计，一般分组会跟聚合函数一起使用

5.4.1 分组

语法：

select * from 表名 [where 条件] group by 字段;

例：

-- 根据性别分组, 分别统计总人数
select * from 表名 group by sex;

5.4.2 分组后筛选

语法：

select * from 表名 [where 条件] group by 字段 HAVING [条件];

例：

-- 根据性别分组, 找出工资大于4000的那个人
SELECT * FROM emp GROUP BY sex HAVING AVG(sal)>8000;

5.4.3 where 和 having 的区别

名称	作用
where	①：where后边不能使用聚合函数；②：对于查询结果分组前，先将不满足where条件的行去掉，即先过滤在分组
having	②：having后边可以使用聚合函数；②：having是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤

5.5 排序查询

排序是写在查询后边的，是把数据查询出来后在排序
语法： ASC : 升序 ，默认值； DESC ： 降序

select * from 表名 [WHERE 条件] order by 字段名1[ASC|DESC], 字段名2 [ASC|DESC]

注意： 如果对多个字段进行排序时，如果第一个字段相等，那么就按照第二个字段排序，以此类推；
例：

-- 按照工资从小到大排序
select * from 表名 order by sal ASC;

5.6 分页查询

语法：

select * from 表名 limit 跳过的数据条数,要查询的数据条;

limit 关键字是使用在查询的后边，如果有排序的话则使用在排序的后边

-- 查询3-6条数据
select * from emp limit 3,3;

5.7 以上查询的使用顺序

select * from 表名 where 条件 group by 分组条件 having 筛选条件 order by   字段 limit 跳过的数据数,要查询的数据数

5.8 交叉查询

概念： 就是将多张表无条件的连接在一起，跟笛卡尔积类型。
笛卡尔积运算：A={a,b},B={1,2,3},A与B的笛卡尔积就是{(a,1),(a,2),(a,3),(b,1),(b,2),(b,3)}

select * from 表1名,表2名

上面这种情况会导致数据查询错误，
那么这种情况的解决办法：
①使用内连接查询；
②使用外连接查询；
③使用自连接查询；
④使用子查询；

5.9 内连接查询

内连接查询有两种写法。
第一种：
注意： 这里方法一不能查询所有字段，如果写了 * ，那么就会查出重复的数据，不推荐使用第一种写法。

select 字段1，字段2... from 表1名,表2名 where 表1的外键=表2的主键(表1的主键=表2
的外键)

例

-- 查询员工的所有信息以及部门的信息
SELECT * FROM dept,emp WHERE dept_id=deptno;

第二种:

select * from 表1名 [inner] join 表2名 on 表1的外键=表2的主键(表1的主键=表2
的外键)  where [where 其他条件]

例

-- 查询所有所有男员工的所有信息以及部门的信息
SELECT * FROM dept [INNER] JOIN emp ON  dept_id=deptno where sex = '男';

特点： 主表和从表的数据都是满足连接条件则能够查询出来，不满足连接条件则不会查询出来。

5.10 外连接查询

5.10.1 左连接查询

概念： 以join左边的表为主表，展示所有主表的数据，根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示。可以理解为：在内连接的基础上保证左边表的数据全部显示

SELECT * FROM 表1 LEFT JOIN 表2 ON 表1的外键=表2的主键(表1的主键=表2
的外键); 
```sql
-- 查询所有所有男员工的所有信息以及部门的信息
SELECT * FROM dept LEFT JOIN emp ON  dept_id=deptno where sex = '男';

5.10.2 右连接查询

概念：以join右边的表为主表,展示右边表的所有数据,根据条件查询join左边表的数据,若满足则展示,若不满足则以null显示。可以理解为：在内连接的基础上保证右边表的数据全部显示

SELECT * FROM 表1 LEFT RIGHT 表2 ON 表1的外键=表2的主键(表1的主键=表2
的外键); 
```sql
-- 查询所有所有男员工的所有信息以及部门的信息
SELECT * FROM dept RIGHT JOIN emp ON  dept_id=deptno where sex = '男';

5.11 自连接查询

概念： 自连接查询是一种特殊的多表连接查询，因为两张表关联查询的表是同一张表，通过取别名的方式来虚拟成两张表。进行两张表的连接查询

搭配环境

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  boss_id INT , -- 上级领导
  salary DECIMAL(7,2) -- 工资
);
-- 添加员工
INSERT INTO emp(id,ename,boss_id,salary) VALUES 
(1001,'孙悟空',1004,'8000.00'),
(1002,'卢俊义',1006,'16000.00'),
(1003,'林冲',1006,'12500.00'),
(1004,'唐僧',1009,'29750.00'),
(1005,'李逵',1006,'12500.00'),
(1006,'宋江',1009,'28500.00'),
(1007,'刘备',1009,'24500.00'),
(1008,'猪八戒',1004,'30000.00'),
(1009,'罗贯中',NULL,'50000.00'),
(1010,'吴用',1006,'15000.00'),
(1011,'沙僧',1004,'11000.00'),
(1012,'李逵',1006,'9500.00'),
(1013,'小白龙',1004,'30000.00'),
(1014,'关羽',1007,'13000.00');

语法:

select * from 表1 表1别名,表2 表2别名 where 表1别名关联的字段名=表2别名被关联的字段名（表2别名关联的字段名=表1别名被关联的字段名）

例：

SELECT * FROM emp e1,emp e2 WHERE e1.boss_id = e2.id; 

5.12 union联合查询全外连接查询

注意： 全外连接并不是一种多表连接查询的一种方式。联合查询是将多条查询语句的查询结果合并成一个结果并去掉重复数据。
语法：

select * from 表名1 
union
select * from 表名2

例：

-- 查询所有员工和所有部门的信息
SELECT * FROM emp LEFT JOIN dept ON dept_id=deptno
UNION
SELECT * FROM emp RIGHT JOIN dept ON dept_id=deptno;

5.13 子查询

一个查询语句嵌套在另外一个查询语句里面，那么这个查询语句为子查询。注意： 嵌套的那个查询语句必须使用（）套起来。
子查询有三种形式：
①：where
②：from
③：exists

5.13.1 where 型

① 可以使用比较运算符
需要注意的是：嵌套里面查询的结果必须只能有一个，不然会报错。
语法：

-- 注意： 里面的查询语句查到结果只能是一行一列，否则会报错
select  * from 表名 where 字段名 = (查询语句)

例：

-- 查询员工的最低工资的员工信息。
SELECT * FROM emp WHERE empno = (SELECT empno FROM emp WHERE ename='张四');

② 使用范围运算符
使用原因： 因为嵌套里面的查询查出多条记录，但是只能有一列，否则就会报错。

常用范围运算符：

not in;
in;
= all (求最大值) ;
<=all(求最小值);
<=any(求小于最大值的所有值)；
=any(求大于最小值的所有值)

语法：

select *from 表名 where 字段名 = (查询语句)

例:

-- 查询员工工资最高的那个员工信息
SELECT * FROM emp WHERE sal >= ALL(SELECT sal FROM emp);

5.13.2 from 型

使用原因： 嵌套里面的查询结果是多行多列，此时必须要给里面查询的表取临时别名，这里再给大家提醒一下，给表取别名时前往别加引号，否则会报错。

这里给大家举个例子，便于理解。

-- 先查出员工表的姓名，工号，薪水，然后选择查出来后的员工的薪资大于5000的员工。
SELECT * FROM (SELECT ename,dept_id,sal FROM emp) com WHERE com.sal > 5000;

5.13.3 exists 型

exists关键字： 只要查询语句查询到数据，就返回true，即使查询到的数据为null也返回true。否则，返回false

-- 查询部门 id<30 的部门下的员工有哪些
SELECT * FROM emp WHERE EXISTS (SELECT * FROM dept WHERE  deptno < 30 and deptno = dept_id)

第六章：视图

视图是一个虚拟表，是sql的查询结果，其内容由查询定义。当然，视图的数据变化会影响到基表，同样基表的数据变化也会影响到视图[insert update delete]。

注意：
视图可以执行DML操作，但不能违反基表约束，不能同时操作多个基表，不能操作分组、含有表达式、函数的视图，否则会报错。

6.1 创建视图

语法:

create view v_emp
as
查询语句

例 ：

CREATE VIEW v_emp
AS
SELECT empno,ename,sal FROM emp;

6.2 使用视图

语法：

select * from v_emp;

6.3 通过视图执行插入操作

注意： 插入操作时不能违反基表的约束： 主键、非空、检查、外键等约束。
语法：

-- 注意： 这里插入的值的数据类型与字段的数据类型相对应
insert into 视图名 (字段1,字段2...) values(值1，值2..)

例：

INSERT INTO v_emp (empno,ename,sal)VALUES(12,'篱笆',5000)

6.4 通过视图执行修改操作

注意： 只能修改通过视图查询到的记录。
语法：

update 视图名 set 字段执行的操作 [where 条件];

例：

UPDATE v_emp SET sal=sal+1000 WHERE empno = 13;

6.4 通过视图执行删除操作

注意： 不能删除连接视图。，否则会报错。
语法：

-- 注意： 不加where 条件，会删除整张表的记录，但并不会删除表.
delete from 视图名 [where 条件];

例：

DELETE FROM v_emp WHERE empno = 13; 

6.5 使用视图优点

①简化数据的操作：用户将经常查询的结果定义为视图，这样用户每次调用时，只需要调用视图就可以查询出结果了，同时也调高了查询效率。
②安全：用户可以通过设置视图，让一些特点的用户只能查看或修改他们权限内的数据，对其他的数据库或表不能进行操作，这样保证了数据的安全性。

第七章： 自定义存储过程和变量

7.1 使用自定义存储过程

[DELIMITER 符号] 是自定义分隔符，中括号里面的符合是自己定义的，它的作用是用来替换原来的默认分号的;

DROP PROCEDURE 自定义的名字,此语句是用来删除 自定义存储过程；

这里定义和语法可能不太好理解，可以直接看例子，定义这个存储过程它的框架是固定的，主要变化的是BEGIN和END中间的语句。

语法：

DELIMITER 自定义符号
CREATE PROCEDURE 自定义的名字()
BEGIN
	语句...
END 前面自定义的符号
DELIMITER;

例：

DROP PROCEDURE IF EXISTS myproc;-- 判断myproc存储过程是否存在，存在就删除。

DELIMITER $$  -- 自定义分隔符 用来替换原来的默认的分号的
CREATE PROCEDURE myproc()
BEGIN
	SELECT 'Hello,Procedure!';
END$$
DELIMITER ;  -- 恢复默认的分隔为分号

-- 调用过程
CALL myproc(); -- 如果无参时，可以省略小括号

运行结果：

在BEGIN 和 END 中间的语句是可以写分支条件、case … when … 、循环等语句。
注意 ： 在自定义存储过程中，声明的变量要放在语句块之前，要不然会报错

**①： 使用if - elseif 语句 **

-- 判断自己的成绩的等级
DELIMITER $$
CREATE PROCEDURE myproc(score FLOAT)
BEGIN
	IF score>=85
		THEN SELECT '优秀';
	ELSEIF score>=70
		THEN SELECT '良好';
	ELSEIF score>=60
		THEN SELECT '一般';
	ELSE
		SELECT '不及格';
	END IF;

END$$
DELIMITER ;

CALL myproc(2);

**②： 使用 case - when 语句 **
第一种写法：
case - when 语法：

case 值
	when 变量1 then select 值1;
	when 变量2 then select 值2;
	when 变量3 then select 值3;
end case;

例：

-- 将数字转化为中文
DELIMITER $$
CREATE PROCEDURE myproc(num INT)  -- 数字转为大写 1-壹
BEGIN
	CASE num				-- switch(num)
		WHEN 1 THEN SELECT '壹';	--  case 1
		WHEN 2 THEN SELECT '贰';
		WHEN 3 THEN SELECT '叁';
		ELSE	SELECT '输入有误！';
	END CASE;

END$$
DELIMITER ;

第二种写法：
case - when语法：

case 
	when 表达式 then select 值1;
	when 表达式 then select 值2;
	when 表达式 then select 值3;
	...
end case;

例：

DELIMITER $$
CREATE PROCEDURE myproc(sal INT)  -- 工资 -- 高工资 中等工资 低工资
BEGIN
	CASE 
		WHEN sal > 10000 THEN SELECT '高工资';
		WHEN sal > 7000  THEN SELECT '中等工资';
		WHEN sal > 5000  THEN SELECT '一般工资';
		ELSE SELECT '低工资';
	END CASE;

END$$
DELIMITER ;

**③： 使用 循环 语句 **
循环的三要素：
①：初始化条件变量
②：==条件表达式 ==
③：修改条件变量

第一种写法：while
例：求 1 - 100的累加和

DELIMITER $$
CREATE PROCEDURE myproc()
BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE sm INT DEFAULT 0;
	WHILE i <= 100
		DO
			SET sm = sm + i;
			SET i = i + 1;
	END WHILE;
	SELECT sm;
END $$
DELIMITER ;

CALL myproc();

第二种写法：repeat … util 条件 end repeat
例：求 n - m的累加和
这里的n和m自己取值测试

DELIMITER $$
CREATE PROCEDURE myproc(n INT, m INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE sm INT DEFAULT 0;
	REPEAT 
		SET sm = sm + i;
		SET i = i + 1;
			UNTIL i >m
	END REPEAT;
	SELECT sm;
END $$
DELIMITER ;

CALL myproc(n,m);

第三种写法 loop … leave end loop
例：求 n - m的累加和
这里的n和m自己取值测试

DELIMITER $$
CREATE PROCEDURE myproc(n INT, m INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE sm INT DEFAULT 0;
	xx:LOOP
		SET sm = sm + i;
		SET i = i + 1;
		IF i > m
			THEN LEAVE xx; -- 跳出循环
		END IF;
	END LOOP;
	SELECT sm;
END $$
DELIMITER ;

CALL myproc(n,m);

7.2 全局变量

以@@打头， @@global.variable_name;
注意： 有些系统全局变量是只读的，可以找一个修改的全局变量试试

SET @@global.character_set_client=gbk;

当执行完上面这条语句后，你重新打开客户端后，这个值没有被修改，但是重启电脑后，值修改了。

查询所有的全局变量

SHOW GLOBAL VARIABLES

7.3 会话变量

以@@打头，@@session.variable_name，当省略session时，@@variable_name时，是session变量。session变量存活周期当前connection

SET @@session.character_set_client=latin1;

当执行完上面这条语句后，你重新打开客户端或重启电脑后，这个值修改了。

查询所有的全局变量

SHOW SESSION VARIABLES

7.4 自定义变量

以@打头，存活周期当前connection,connection断掉后释放变量，用户自定义变量不需要声明，可以直接使用.

SET @num1 = 5;
SET @num2 = 6;
SELECT @num1,@num2;  
SET @sum = @num1+@num2;
SELECT @num1,@num2,@sum;

7.5 局部变量

存在于存储过程、函数中，必须先声明然后才能使用

DELIMITER $$
CREATE PROCEDURE myproc()
BEGIN
	DECLARE i INT DEFAULT 5;  -- 声明的局部变量
	SET i = i + 5;		  -- 局部变量必须要在声明前在使用
	SELECT i;
END $$
DELIMITER;

局部变量和用户变量的测试

SET @user_var=0;				-- 用户变量声明和初始化，默认null
DELIMITER $$
CREATE PROCEDURE myproc()
BEGIN
	DECLARE loc_var INT DEFAULT 0;
	SET loc_var=loc_var+1;			-- 局部变量
	SET @user_var = @user_var+1;		-- 用户变量
	SELECT loc_var,@user_var;
END$$
DELIMITER ;

-- 多次调用 查看两种变量变化
CALL myproc;

变化： 局部变量的值一直是1，而用户变量是根据调用的次数决定的。
注意： 在存储过程外，不能访问局部变量，可以访问用户变量。

第八章： 函数

8.1 使用函数

return返回的数据类型与定义的数据类型要保持一致。

注意： 我们使用函数时会出现 1418 的错误；解决办法如下：
①： 在定义函数前设置变量log_bin_trust_function_creators值为1，因为log_bin_trust_function_creators的默认值为0；

SET GLOBAL log_bin_trust_function_creators=1;

DELIMITER $$
CREATE FUNCTION myfun(num1 INT,num2 INT) RETURNS INT -- 注意： 定义返回值类型是returns，不是return。 
BEGIN
	RETURN num1+num2;  -- 注意： 必须要有return，这里不加s，且return返回的数据类型与定义的数据类型要保持一致。
END$$
DELIMITER ;

SELECT myfun(3,4);

②：需要在函数中声明 DETERMINISTIC，NO SQL或READS SQL DATA的其中一种

DELIMITER $$
CREATE FUNCTION myfun(num1 INT,num2 INT) RETURNS INT DETERMINISTIC  -- 注意： 定义返回值类型是returns，不是return。
BEGIN
	RETURN num1+num2;-- 注意： return返回的数据类型与定义的数据类型要保持一致。
END$$
DELIMITER ;

SELECT myfun(3,4);

8.2 函数和过程的区别

①：函数必须有返回值：声明时（定义式）必须有 returns，返回值的语句中使用return；
②：函数语句中不能使用select；
③：函数不能使用call调用，函数只可以用于sql语句中，存储过程中不能用于sql语句中；
④：可以在存储过程中调用函数，不可以在函数中调用存储过程

8.3 注意点

我们开启了 bin-log，所以我们必须要指定我们的函数是那哪种类型的？
① DETERMINISTIC 不确定的
② NO SQL 没有SQl语句，当然也不会修改数据
③ READS SQL DATA 只是读取数据，当然也不会修改数据
④ MODIFIES SQL DATA 要修改数据
⑤ CONTAINS SQL 包含了SQL语句
其中在function里面，只有前三个支持。

第九章：事务

9.1 概念

事务是针对数据库的一组操作，他可以由一条或者多条SQL语句组成，同一个事务的操作具备同步的特点，如果其中有一条语句不能执行的话，那么所有的语句都不会执行，也就是说，事务中的语句要么都执行，要么都不执行。

9.2 事务的四大特性ACID

指数据库事务正确正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
① 原子性： 原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
② 一致性：一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这就是说数据库事务不能破坏关系数据库的完整性以及业务逻辑上的一致性。
③ 隔离性： 隔离性是指并发的事务是相互隔离.即一个事务内部的操作及正在操作的数据必须封装起来，不被企图进行修改的事务看到。
④ 持久性： 持久性是指在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会回滚。即使出现了任何事故（比如断电），事务一旦提交，则持久化的保存在数据库。

9.3 事务的原理

事务开启后，所有的操作都会临时保存到事务日志，事务日志只有得到 commit 命令才会同步到数据库表中，其他情况都会清空事务日志（rollback，断开连接）

9.4 事务的使用场景

如果从某个业务中需要执行多条SQL语句，那么此时一般是需要使用到事务，从而保证这多条SQL语句执行同时成功或同时失败。例如： 批量删除，转账，从购物车中提交订单等等。

9.5 事务处理的相关操作

注意： myql 默认是自动提交事务的，一条sql语句就是一个事务。

9.5.1 手动开启事务

使用 BEGIN,ROLLBACK,COMMIT来实现
BEGIN：开始一个事务
ROLLBACK： 事务回滚，不会改变数据库的数据
COMMIT：事务提交，会改变数据库的数据
例：

-- 对员工id为1004的工资+10000，进行测试

-- 进行事务的提交  
BEGIN;-- 或  START TRANSACTION;
UPDATE emp SET sal=sal+10000 WHERE id = 1004;
COMMIT;
-- 执行上面的语句会发现员工工资加了10000；

-- 进行事务的回滚
BEGIN;
UPDATE emp SET sal=sal+10000 WHERE id = 1004;
ROLLBACK; 
-- 执行上面的语句会发现员工工资没有变化。

9.5.2 使用set来设置自动提交

语法：

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

9.6 事务的隔离级别

事务在操作时的理想状态：所有事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问一个数据，可能会引发并发访问的问题。如果没有事务的隔离性，就容易出现脏读、不可重复读和幻读等情况。

① 脏读：指一个事务正在访问数据，并且对数据进行了修改，但是这种修改还没有提交到数据库中，此时，另外一个事务也在访问这个数据，然后使用了这个数据。
②不可重复读：指在一个事务中，多次读取到同一个数据。
在这个事务还没有结束时，另外一个事务也访问了该同一数据。那么，在第一个事务中的两次读取数据之间，由于第二个事务的修改，那么第一个事务两次读取到的数据可能是不一样的。
③幻读：指事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。

9.6.1 四种隔离级别

9.6.1.1 读未提交

Read uncommited(读未提交)：可以读到未提交的内容。也就是说如果一个事务读取到了另一个为提交事务修改过的数据。
在此隔离级别下，所有事务都可以看到其他未提交事务的执行结果。

9.6.1.2 读已提交

Read commited(读已提交)：可以读到已经提交了的内容。也就是说如果一个事务只能读取到另一个已提交事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询到最新值。
Oracle、SQL Server的默认事务隔离级别，但不是MYSQL的默认的。

9.6.1.3 可重复读

Repeatable read (可重复读)：可重复读专门针对不可重复读这种情况而制定的隔离级别，可以有效的避免不可重复读。
在一些场景中，一个事务只能读取到另一个已提交事务修改过的数据，但是第一次读过某条记录后，即使其他事务修改了该记录的值并且提交，之后该事务在读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。
可重复读是MySql的默认事务隔离级别，他能确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
在该隔离级别下，如果有事务正在读取数据，就不允许有其它事务进行修改操作，这样就解决了可重复读问题。

9.6.1.4 串行化

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。那么这种隔离级别就称之为串行化。

SERIALIZABLE 是最高的事务隔离级别，主要通过强制事务排序来解决幻读问题。简单来说，就是在每个读取的数据行上加上共享锁实现，这样就避免了脏读、不可重复读和幻读等问题。但是该事务隔离级别执行效率低下，且性能开销也最大，所以一般情况下不推荐使用。

| 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读| 数据库默认隔离级别 |
|–|–|-- |–|–|-|–|–|
| 1 |读未提交 |Read uncommited|是 | 是 |是| |
| 2 |读已提交 |Read commited|否 | 是 |是| Oracle |
| 3 |可重复读 |Repeatable read|否 | 否 |是| MySql |
| 4 |串行化 |Serializable|否 | 否 |否| 最高的隔离级别 |

注意：隔离级别越高，性能（效率）越差，安全性越高

9.6.2 设置隔离级别

语法

-- 设置事务隔离级别
set session transaction isolation level  隔离级别;

-- 查询当前事务隔离级别， Mysql8.0后使用这个语句
SELECT @@transaction_isolation;

9.6.3 演示数据库安全问题

9.6.3.1 准备环境

create table account(
    id int primary key auto_increment,
    name varchar(20),
    money double
);

insert into account values (null,'zs',1000);
insert into account values (null,'ls',1000);
insert into account values (null,'ww',1000);

9.6.3.2 演示脏读

步骤：
①：开启两个客户端，分别记作A，B
②：在A客户端设置隔离级别为Read uncommited（读未提交）,并且把事务设为手动提交

SET autocommit= 0; -- 事务设为手动提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 设置隔离级别为Read uncommited

③：在A，B客户端开启事务，但不提交

BEGIN;
UPDATE  account SET money=money-500 WHERE id=1;
select * from  account;

④：在B客户端查询用户信息

select * from  account;

你就会发现A客户端没有提交事务，B客户端发现信息改变。

9.6.3.3 演示不可重复读（解决脏读）

不可重复读： 在一个事务里面，同一条语句，两次查询的结果不一致。
步骤：
①：开启两个客户端，分别记作A，B
②：在A客户端设置隔离级别为Read commited（读已提交）,并且把事务设为手动提交

SET autocommit= 0; -- 事务设为手动提交
SET global TRANSACTION ISOLATION LEVEL READ COMMITTED;-- 设置隔离级别为Read commited

③：在A，B客户端开启事务，但不提交，A客户端进行修改信息

BEGIN;
UPDATE  account SET money=money-500 WHERE id=1;
select * from  account;

④：在B客户端查询用户信息

select * from  account;

⑤：你就会发现A客户端没有提交事务，B客户端的信息没有改变，但是A客户端的信息改变了。然后在A客户端提交事务，你会发现B客户端信息改变了。

9.6.3.3 演示可重复读（避免不可重复读）

步骤：
①：开启两个客户端，分别记作A，B
②：在A客户端设置隔离级别为Repeatable read（不可重复读）,并且把事务设为手动提交

SET autocommit= 0; -- 事务设为手动提交
SET global TRANSACTION ISOLATION LEVEL Repeatable read;;-- 设置隔离级别为Repeatable read;

③：在A和B客户端开启事务，但不提交

BEGIN;
select * from  account;

④：在B客户端开启事务，并执行修改name值，然后commit。

UPDATE ACCOUNT SET NAME = 'bbbbb' WHERE id = 1;

你就会发现A客户端的信息没有修改
⑤：然后在A中提交事务，然后查询A中的信息

commit;
select * from account;

你就会发现A客户端的信息改变了。注意： 只要不在A客户端提交，那么A客户端的值一直会保持不变。

9.6.3.4 演示隔离级别Serializable（避免幻读）

步骤：
①：开启两个客户端，分别记作A，B
②：在A客户端设置隔离级别为Read commited（读未提交）,并且把事务设为手动提交

SET autocommit= 0; -- 事务设为手动提交
SET global TRANSACTION ISOLATION LEVEL Serializable;-- 设置隔离级别为Serializable

③：在A，B客户端开启事务，在A客户端插入数据，但不提交

BEGIN;
INSERT INTO ACCOUNT (id,NAME,money) VALUES(4,'dd',1000);
select * from  account;

④：在B客户端查询用户信息

select * from  account;

你就会发现A客户端在等待B客户端的提交，当A客户端提交后，就可以发现B客户端可以查询到A客户端插入的数据。

9.6.4 myIsam和innodb 两种引擎

注意： mysql 5 默认引擎为mylsam； mysql 8 默认引擎为innodb

9.6.4.1 查看所有支持引擎

语法：

SHOW ENGINES;

9.6.4.2 区别

mylsam可以默认提交事务，不能回滚，并且mylsam不支持事务，因为mylsam的引擎是默认提交事务的。适用于修改，插入，删除相对较少的，查询多的场景。

innodb可以进行事务回滚，并且innodb是支持行级锁的。适用于更新（插入，删除，修改）多的场景
例：

-- 创建默认引擎 MYISAM 的表
CREATE TABLE MYISAM_TBL(
	id INT PRIMARY KEY AUTO_INCREMENT,
	t_name VARCHAR(30),
	num INT
) ENGINE MYISAM; 		-- 引擎MYISAM
-- 创建默认引擎 INNOB 的表
CREATE TABLE INNODB_TBL(
	id INT PRIMARY KEY AUTO_INCREMENT,
	t_name VARCHAR(30),
	num INT
) ENGINE INNODB;		-- 引擎INNODB

INSERT INTO myisam_tbl VALUES(1,'AAA',10);
INSERT INTO Innodb_tbl VALUES(1,'AAA',10);

SELECT * FROM myisam_tbl;
SELECT * FROM innodb_tbl;

BEGIN;
UPDATE myisam_tbl SET num=num+5 WHERE id=1;	-- 默认提交
UPDATE InnoDB_tbl SET num=num+5 WHERE id=1;  	-- 可以回滚
ROLLBACK;

结果：myisam_tbl 的num值为15；InnoDB_tbl 的num值为10；

9.6.4.3 测试innodb是支持行级锁的

测试方法如下

CREATE TABLE bank_account(
	accname VARCHAR(50),
	balance FLOAT,	-- 账户余额
	trandate DATETIME
);
INSERT INTO bank_account(accname,balance,trandate)VALUES('zhang',1000,NOW()),('wang',1000,NOW());

-- 是否锁 方法：在这里关闭自动提交，执行更新。然后再打开新客户端，在执行此更新。发现一直等待。
START TRANSACTION; -- 不自动提交了
UPDATE bank_account SET balance = balance+1000 WHERE accname='wang';

COMMIT; -- 在没有执行 commit 或 rollback 之前，此记录是一直会被锁定
ROLLBACK;

9.6.5 应用（存储过程应用、事务处理性能）

目标： 向表中插入1万~10万数据。
测试MYISAM的性能,插入10000条数据需要9.827秒。

DELIMITER $$
CREATE PROCEDURE myproc(num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	CREATE TABLE test(
		tid INT,
		tname VARCHAR(100)
	)ENGINE MYISAM;
	
	WHILE i < num
	DO
		INSERT INTO test VALUES(i+1,CONCAT('AAA',i));
		SET i = i +1;
	END WHILE;
END $$
DELIMITER ;

CALL myproc(10000);

测试innodb关闭自动提交事务的性能,插入10000条数据需要22.2秒。注意：innodb默认是自动提交事务。

DELIMITER $$
CREATE PROCEDURE myproc(num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	CREATE TABLE test(
		tid INT,
		tname VARCHAR(100)
	)ENGINE INNODB;
	
	WHILE i < num
	DO
		INSERT INTO test VALUES(i+1,CONCAT('AAA',i));
		SET i = i +1;
	END WHILE;
END $$
DELIMITER ;
CALL myproc(10000);

结论： myisam 比 innodb 的速度快一倍。

测试 innodb 开启事务的性能，,插入10000条数据需要0.22秒

DELIMITER $$
CREATE PROCEDURE myproc(num INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	CREATE TABLE test(
		tid INT,
		tname VARCHAR(100)
	)ENGINE INNODB;
	START TRANSACTION; -- 开启事务，默认不再自动提交
	WHILE i < num
	DO
		INSERT INTO test VALUES(i+1,CONCAT('AAA',i));
		SET i = i +1;
	END WHILE;
	COMMIT;
END $$
DELIMITER ;
CALL myproc(10000);

结论： innodb 开启事务，关闭自动提交，执行完后，一次性的提交，这个性能比innodb默认的提交事务快100倍左右。

9.6.6 总结mylsam和innodb两种引擎

引擎名字	是否支持事务	是否支持锁	主键	外键	全文索引
mylsam	否，默认自动提交事务	是，支持表锁	必须有，没有指定会默认生成一个隐藏列作为主键	不支持	支持
innodb	是，可以手动提交事务	是 ，支持行锁，表锁	可以没有	支持	mysql5.7后支持
补充一个区别：InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表。

注意：行锁是实现在索引上的，如果没有索引，就没法使用行锁，将退化为表锁。

mylsam优点：查询数据相对较快，适合大量的select，可以全文索引。
mylsam缺点：不支持事务，不支持外键，并发量较小，不适合大量update。

innodb优点：支持事务，支持外键，并发量较大，适合查询。
innodb缺点：查询数据相对较快，不适合查询，适合更新。
？

第十章：练习

1. 完成表复制

注意： 复制表内容，但不复制表的约束

CREATE TABLE 新表名 SELECT 复制的字段 FROM 旧表名;

例：

-- 将emp中的内容复制到emp_one内容中。
CREATE TABLE emp_one SELECT * FROM emp;

2. 练习2

2.1 使用DDL创建表

要求：员工表 emp 员工编号，姓名，薪水 (empno,ename,sal)
部门表 dept 部门编号，部门名称（deptno,dname）

CREATE TABLE dept(
           deptno INT PRIMARY KEY,
           dname VARCHAR(50)
    );
    CREATE TABLE emp(
           empno INT PRIMARY KEY,
           ename VARCHAR(30),
           sal  FLOAT
    );

2.2 使用DDL修改表

要求①： 给 emp 表添加一个外键列：所属的部门编号， deptno 关联员工表。
要求②：给 emp 表增加列 hiredate 入职日期

-- 要求1：
ALTER TABLE emp ADD deptno INT;
ALTER TABLE emp ADD CONSTRAINT fk_emp FOREIGN KEY(deptno) REFERENCES dept(deptno);

-- 要求2：
ALTER TABLE emp ADD hiredate DATE;

2.3 插入数据

要求①：向部门插入4条数据
10 研发部
20 人事部
30 市场部
40 渠道部
要求②：向员工表任意插入10条数据，但不使用40

-- 要求1：
INSERT INTO dept(deptno,dname)
  SELECT 10,'研发部'  UNION
  SELECT 20,'人事部'  UNION
  SELECT 30,'市场部'  UNION
  SELECT 40,'渠道部' ;

-- 要求2：
INSERT INTO emp(empno,ename,sal,deptno)
  SELECT 1,'张飞',3000,10  UNION
  SELECT 2,'关羽',2000,10  UNION
  SELECT 3,'张小五',1800,20  UNION
  SELECT 4,'诸葛亮',3900,20  UNION
  SELECT 5,'黄盖',4200,30  UNION
  SELECT 6,'周瑜',6300,10  UNION
  SELECT 7,'马超',2080,30  UNION
  SELECT 8,'马德华',3500,10  UNION
  SELECT 9,'曹操',1000,20  UNION
  SELECT 10,'司马光',2200,10 UNION
  SELECT 11,'马岱',2200,10 ;

2.4 查询数据

2.4.1 查询没有员工的部门

SELECT * FROM dept WHERE deptno NOT IN(SELECT DISTINCT deptno FROM emp); 

2.4.2 查询有员工的部门数

 SELECT COUNT(DISTINCT deptno) FROM emp;

2.4.3 查询员工信息及部门名称（子查询和连接查询分别实现）

-- 子查询
SELECT empno,ename,sal,(SELECT dname FROM dept WHERE deptno=emp.deptno)dname FROM emp;

-- 连接查询
SELECT empno,ename,sal,dname FROM emp e,dept d WHERE e.deptno=d.deptno;
-- 或
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

2.4.4. 查询各自部门下员工的平均工资(显示部门编号 部门名称 平均工资)

-- 使用IFNULL函数
SELECT deptno,dname,IFNULL((SELECT AVG(sal) FROM emp WHERE deptno=dept.deptno),0) 平均工资 FROM dept;

-- 使用分组查询
SELECT dept.deptno,dname,IFNULL(AVG(sal),0) 平均工 FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
GROUP BY dept.deptno,dname;

2.4.5 查询平均工资最高的部门

-- 分组方式实现
SELECT d.deptno,dname,AVG(sal) ax FROM dept d,emp e
WHERE d.deptno=e.deptno GROUP BY d.deptno,dname
ORDER BY AVG(sal) DESC LIMIT 0,1;

-- 子查询实现
SELECT d.deptno,dname,(SELECT AVG(sal) FROM emp WHERE deptno=d.`deptno`)平均工资 FROM dept d  ORDER BY 平均工资 DESC LIMIT 0,1;

2.4.6 查询部门平均工资高于所有员工平均工资的部门

-- 子查询
SELECT deptno,dname,(SELECT AVG(sal) FROM emp WHERE deptno=dept.deptno ) vv FROM dept WHERE 
(SELECT AVG(sal) FROM emp WHERE deptno=dept.deptno )-- 当前记录所属部门的平均工资
>(SELECT AVG(sal) FROM emp);  -- 所有员工平均工资

-- 连接查询
SELECT d.deptno,dname,AVG(sal) myavg
FROM dept d,emp e
WHERE d.`deptno`=e.`deptno`
GROUP BY d.`deptno`,dname
HAVING myavg > (SELECT AVG(sal) FROM emp);

2.4.7 查询按工资高到低排序，显示第3-5名的员工信息

SELECT * FROM emp ORDER BY sal DESC LIMIT 2,3;

2.4.8 查询存在同一部门下具有相同工资的员工

SELECT e1.empno,e1.ename,e1.sal,e1.deptno FROM
emp e1,emp e2
WHERE e1.deptno=e2.deptno AND e1.sal=e2.sal AND e1.empno<>e2.empno

2.4.9 查询所有工资超过他所在部门平均工资的员工

SELECT * FROM emp
WHERE sal<(SELECT MAX(sal) FROM emp WHERE deptno=30) AND deptno<>30;

-- 使用any关键字
SELECT * FROM emp
WHERE sal< ANY(SELECT sal FROM emp WHERE deptno=30) AND deptno<>30;

2.4.10 查询公司中工资最低的员工的所在部门的其他的员工信息

-- 子查询
SELECT * FROM emp WHERE 
deptno=(SELECT deptno FROM emp WHERE sal=(SELECT MIN(sal) FROM emp)) -- 工资最低
AND empno<>(SELECT empno FROM emp WHERE sal=(SELECT MIN(sal) FROM emp));

-- 使用limit关键字
SELECT * FROM emp 
WHERE deptno =(SELECT deptno FROM emp ORDER BY sal ASC LIMIT 0,1)
AND empno <> (SELECT empno FROM emp ORDER BY sal ASC LIMIT 0,1);

2.4.11 查询员工工资大于3000 的部门的名称

-- 第一种：
SELECT dname FROM dept WHERE deptno IN(SELECT deptno FROM emp WHERE sal>3000)
-- 第二种：
SELECT dname FROM dept d WHERE EXISTS(SELECT empno FROM emp e WHERE sal>3000 AND deptno=d.deptno )

2.5 把高于5000的工资隐藏显示“高工资”，小于2000的工资隐藏显示“低工资”，其余正常显示

SELECT empno,ename,(CASE WHEN sal>5000 THEN '高工资' WHEN sal<2000 THEN '低工资' ELSE sal END ) 工资
FROM emp;

学习总结： 第一次写这么多字【34k】去分享自己学到的知识，还是非常高兴的，只不过还有一些部分并没有完善好，过段时间后会给大家分享数据库的高级部分的内容。