我半途而废的学习笔记之 MySQL (3)

本篇是sql基础的最后一篇，并附上前两篇笔记的地址：

我半途而废的学习笔记之 MySQL (1)

我半途而废的学习笔记之 MySQL (2)

其余内容比如习题我放在这里了 -->这里。

4. DML语言

数据操作语言
插入：insert
修改：update
删除：delete

4.1. 插入语句

1. 方式一（经典的插入）：

#用的多
insert into 表名(列名,...) values(值1，...);

插入的值的类型要与列的类型一致或兼容

SELECT * FROM beauty;
INSERT INTO beauty ( id, NAME, sex, borndate, phone, photo, boyfriend_id )
VALUES
	( 13, '唐艺昕', '女', '1990-4-23', '1898888888', NULL, 2 );

不可以为null的列必须插入值，可以为null的列如何插入值？

#方式一：列名写上，值为null
#方式二：列名省略，值省略
INSERT INTO beauty ( id, NAME, sex, borndate, phone, boyfriend_id )
VALUES
	( 15, '金星', '女', '1986-9-12', '1898888888', 2 );

列的顺序可以调换

INSERT INTO beauty ( NAME, sex, id, phone )#乱序字段名
VALUES
	( '蒋欣', '女', 14, '110' );

列数和值的个数必须一致

即方式二，要省必须一起省
错误示范：
INSERT INTO beauty ( NAME, sex, id, phone, boyfriend_id )
VALUES
	( '蒋欣', '女', 16, '110' );

可以省略列名，默认是所有列

INSERT INTO beauty
VALUES
	( 18, '马超', '男', NULL， '119', NULL, NULL );

2. 方式二

insert into 表名
set 列名 = 值，列名 = 值...

INSERT INTO beauty 
SET id = 16,
NAME = '刘涛',
phone = '999';

3. 两种方式比较

方式一支持插入多行

INSERT INTO beauty
VALUES
	( 23, '唐艺昕1', '女', '1990-4-23', '1898888888', NULL, 2 ),#逗号
	( 24, '唐艺昕2', '女', '1990-4-23', '1898888888', NULL, 2 ),
	( 25, '唐艺昕3', '女', '1990-4-23', '1898888888', NULL, 2 );	

方式一支持子查询，二不支持

INSERT INTO beauty ( id, NAME, phone ) SELECT
26,
'宋茜',
'11809866'; #这是查询，后面可以继续加FROM、WHERE等查询相关

4.2. 修改语句

4.2.1. 修改单表的记录

【重点掌握】

语法：
update 表名
set 列 = 新值,列 = 新值,#多个用逗号隔开
where 筛选条件;

案例1：修改beauty表中姓唐的女生电话为138277777

UPDATE beauty 
SET phone = '138277777' 
WHERE
	NAME LIKE '唐%';

案例2：修改boys表中id号为2的名称为张飞，魅力值为10

UPDATE boys 
SET boyname = '张飞',
usercp = 10 #多列用逗号，可以换位置

WHERE
	id = 2;

4.2.2. 修改多表的记录

【补充知识】

sql92语法：
update 表1 别名, 表2 别名
set 列=值...
where 连接条件
and 筛选条件;

sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列 = 值...
where 筛选条件;

案例1：修改张无忌的女朋友的手机号为114

UPDATE boys bo
INNER JOIN beauty be ON bo.id = be.boyfriend_id 
SET be.phone = '114' 
WHERE
	bo.boyName = '张无忌';

案例2：修改 没有男朋友的女神 的男朋友编号 都为张飞的编号

UPDATE boys bo
RIGHT JOIN beauty be ON bo.id = be.boyfriend_id
SET be.boyfriend_id = 2 
WHERE
	bo.id IS NULL;

4.3. 删除语句

4.3.1. 方式一: delete

单表的删除 【重点掌握】

delete from 表名 where 筛选条件 #删除整行

多表的删除 【补充知识】

sql92:
delete 表1的别名, 表2的别名 #删谁写谁，都写都删
from 表1 别名, 表2 别名
where 连接条件
and 筛选条件;


sql99:
delete 表1的别名,表2的别名 #删谁写谁，都写都删
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

案例1：删除手机号以9结尾的女生信息

DELETE 
FROM
	beauty 
WHERE
	phone LIKE '%9';

案例2：删除张无忌的女朋友的信息

DELETE b 
FROM
	beauty b
	INNER JOIN boys bo ON b.boyfriend_id = bo.id 
WHERE
	bo.boyName = '张无忌';

案例3：删除黄晓明的信息，以及他女朋友的信息

DELETE b,
bo 
FROM
	beauty b
	INNER JOIN boys bo ON b.boyfriend_id = bo.id 
WHERE
	bo.boyName = '黄晓明';

4.3.2. 方式二：truncate

truncate table 表名

#案例：将魅力值>100的男生信息删除
TRUNCATE TABLE boys;
#这个语句不能加where，所以一删就都没了，相对于清空了。

4.3.3. delete和truncate区别

1. delete可以加where条件，truncate不可。
2. truncate删除，效率更高。
3. 假如要删除的表中有自增长列(假设是id)，如果用delete删除后，再插入数据，自增长列的值从断点(上次删除的时候最后一个id是多少，那么插入后就是从它开始往后加)开始；
而truncate删除后，再插入数据，自增长列的值从1开始
4. truncate删除没有返回值(不会说有几行收到影响)，delete删除有返回值。
5. truncate删除不能回滚，delete删除可以回滚。

5. DDL语言

数据定义语言，库和表的管理

1.库的管理
创建、修改、删除

2.表的管理
创建、修改、删除
创建：create
修改：alter
删除：drop

之前是对数据的操作，现在是库和表。

5.1. 库的操作

5.1.1. 库的创建

create database (if not exists) 库名; #如果不存在则创建

创建库books
CREATE DATABASE IF NOT EXISTS books;

5.1.2. 库的修改

基本不修改，可能会导致很多数据丢失错乱
修改库名直接去C盘的MySQL下的data文件夹改名

#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;#一般是utf8

5.1.3. 库的删除

DROP DATABASE IF EXISTS books;

5.2. 表的操作

5.2.2. 表的创建

creat table 表名(
	列名 列的类型【（长度）约束】，
	列名 列的类型【（长度）约束】，
	列名 列的类型【（长度）约束】，
	...
	列名 列的类型【（长度）约束】，
)

案例1：创建表book

CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#图书名，最多20个字符
	price DOUBLE,#价格
	authorId INT,#作者编号
	publishDate DATETIME#出版日期
	
);
DESC book;#查看

案例2：创建表author

CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
);
DESC author;

5.2.3. 表的修改

ALTER TABLE 表名 ADD | DROP | MODIFY | CHANGE COLUMN 列名【列名 列类型】
#①修改列名
ALTER TABLE book CHANGE COLUMN pubishdata pubDate DATETIME;#要加类型,即可以一起修改类型
#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
#④删除列
ALTER TABLE author DROP COLUMN annual;
#⑤修改表名
ALTER TABLE author RENAME TO book_author;

5.2.4. 表的删除

DROP TABLE
IF
	EXISTS book_author;
SHOW TABLES;
#通用的写法:
DROP DATABASE
IF
	EXISTS 旧库名;
CREATE DATABASE 新库名； 
DROP TABLE
IF
	EXISTS 旧表名;
CREATE TABLE 表名 ();

5.2.5. 表的复制

INSERT INTO author
VALUES
	( 1, '村上春树', '日本' ),
	( 2, '莫言', '中国' ),
	( 3, '冯唐', '中国' ),
	( 4, '金庸', '中国' );

1. 仅仅复制表的结构
CREATE TABLE copy LIKE author;#复制一个叫copy的表

2. 复制表的结构+数据
CREATE TABLE copy2 SELECT
* 
FROM
	author;

3. 只复制部分数据
CREATE TABLE copy3 SELECT
id,
au_name 
FROM
	author 
WHERE
	nation = '中国';

4. 仅仅复制某些字段
#只想要结构，不要数据
CREATE TABLE copy4 SELECT
id,
au_name 
FROM
	author 
WHERE
	0; #条件不满足，恒不成立，空列就过去了

5.3. 常见的数据类型

数值型：
		整型
		小数：定点数，浮点数
字符型：
		较短的文本：char、varchar
		较长的文本：text、blob（较长的二进制数据）
日期型

5.3.1. 数值型 - 整型

分类：

整数类型：	tinyint、smallint、mediumint、int/interger、bigint
字节: 		 1         2          3           4          8

特点：

 ①如果不设置有符号还是无符号，默认是有符号，若想设置无符号，要加unsigned
 ②如果插入的数值超出整型范围，会报out of range异常，并插入临界值
 ③如果不设置长度，会有默认长度
长度代表了现实的最大宽度，如果不够会用0在左边填充，但必须搭配ZEROFILL使用

#如何设置有符号和无符号
CREATE TABLE tab_int(
	t1 INT,  		 #有符号,能插入负数
	t2 INT UNSIGNED  #无符号
);

#长度
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,  #零填充，支持正数，不支持负数
	t2 INT UNSIGNED  #无符号
);

INSERT INTO tab_int VALUES(123,123);
#第一个123会在前面填充四个0，因为不加ZEROFILL的宽度是无效的

5.3.2. 小数

分类：

①浮点型
float(M,D)
double(M,D)
②定点型
dec(M,D)
decimal(M,D)

特点：

①M：整数部位+小数部位 的长度
 D： 小数部位
 M和D都可以省略
 
②M和D都可以省略
 如果是decimal，默认M为10，D为0
 而float和double会根据插入的数值的精度来决定精度
 
③定点型的精确度较高，适用于插入数值的精度要求较高，如货币运算

#测试M和D
DROP TABLE tab_float;
CREATE TABLE tab_float(
	f1 FLOAT(5,2),
	f2 DOUBLE(5,2),  #小数点保留两位
	f3 DECIMAL(5,2)
);
SELECT * FROM tab_float;
INSERT INTO tab_float VALUES(123.45,123.45,123.45);
INSERT INTO tab_float VALUES(123.456,123.456,123.456);#四舍五入插入123.46
INSERT INTO tab_float VALUES(123.4,123.4,123.4);#凑两位小数变成123.40
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);#临界值999.99

原则：所选择的类型越简单越好，能保存数值的类型越小越好

5.3.3. 字符型

较短的文本：
char
varchar


较长的文本：
text
blob（较大的二进制）

特点:

			写法        M的意思	        特点	   	空间的耗费    效率
char     char(M)	最大的字符数	  	固定长度的字符    比较节省	   高
		（char的M可以省略，默认为1）
varchar	  varchar(M)   最大的字符数   可变长度的字符    比较浪费	   低

#举例：
sex字段可以用char吧，name字段可以用varchar吧

#enum：
	要求插入的值必须属于列表中指定的值之一，不区分大小写
CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);

INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');#插入失败
INSERT INTO tab_char VALUES('A');

SELECT * FROM tab_char;#表中只有四项，abca


#set：
	集合，和enum类似，和enum最大的区别是，set类型一次可以选取多个成员，而enum只能选一个，都是一种限制
CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);
INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('a,b');
INSERT INTO tab_char VALUES('a,b,c');

5.3.4. 日期型

分类：

date只保存日期
time只保存时间
year只保存年

datetime保存日期+时间
timestamp保存日期+时间

特点：

①datetime：字节8个，范围1000-9999年，不受时区影响
②timestamp：字节4个，范围1970-2038年，受时区影响

CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP            #时间戳
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zone';
SET time_zone = '+9:00';

5.4. 常见约束

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

含义：
	一种限制，用于限制表中的行或列数据，为了保证表中数据的准确性和可靠性

分类：六大约束

	not null：非空，用于保证该字段的值不能为空。
	比如姓名、学号等
	
	default：默认，用于保证该字段有默认值。
	比如性别
	
	primary key：主键，用于保证该字段的值有唯一性，并且非空。
	比如学号、员工编号
	
	unique：唯一，保证该字段的值有唯一性，可以为空。
	比如座位号
	
	check：检查约束【mysql中不支持】
	比如年龄性别
	
	foreign key：外键，用于限制两个表的关系，用于保证该字段值必须来自于主表关联列的值。
	在从表添加 外键约束，用于引用主表中某列的值。
	比如：员工表的部门编号、工种号

添加约束的时间：
	1. 创建表时
	2. 修改表时
	3. 
约束的添加分类：
	1. 列级约束：
	六大约束语法上都支持，但外键约束没有效果
	2. 表级约束：
	除了非空、默认约束，其他都支持

5.4.1. 创建表时添加约束

1. 添加列级约束

语法：
	直接在字段名和字段类型后面追加约束类型即可
只支持：
	默认、非空、主键、唯一
一个列可以添加多个列级约束

代码：
CREATE DATABASE students;
USE students;

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL,#非空
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查，虽然没效果
	seat INT UNIQUE,#唯一
	age INT DEFAULT 18, #默认约束
	majorId INT 
)；
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);
DESC stuinfo;

#查看表中所有的索引，包括主键，外键，唯一
SHOW INDEX FROM stuinfo;

2. 添加表级约束

非空默认不支持
语法：在各个字段的最下面
[constraint] 约束名 约束类型(字段名)
括号里可不加
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	#来了
	CONSTRAINT pk PRIMARY KEY(id),#主键，括号里面 为谁加，主键这里的约束名pk是没有意义的
	CONSTRAINT uq UNIQUE(seat),#唯一
	CONSTRAINT ck CHECK(gender='男' OR gender='女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)	#外键
);
SHOW INDEX FROM stuinfo;#看所有的索引

#通用的写法：
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20) NOT NULL,
	gender CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键名fk_stuinfo_major
);

主键和唯一的对比：

1. 主键和唯一都能保证唯一性。但主键不允许为空，唯一允许。
2. 一个表中最多只能有一个主键，而唯一键可以有多个。
可以组合，多个列可以组合成一个主键或唯一键，但需注意 此时仍然代表一个，而不是多个主键或唯一。如果写成primary key(id,stuname)是联合主键的效果，要两个同时重复才会报错。
3. 不推荐用组合。

外键：

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

列级约束和表级约束：

1. 列级约束：位置—列的后面。支持的约束类型：语法都支持，但外键没有效果
2. 表级约束：位置—所有列的下面。支持的约束类型：默认和为空除外
3. 是否可以起约束名：列级约束不可以，表级约束可以（但是对主键起名无效

5.4.2. 修改表时添加约束

1. 添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2. 添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型（字段名）;


DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
);
DESC stuinfo;

1. 添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

2. 添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3.添加主键（记一种就行）

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;#列级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);#表级约束

4. 添加唯一

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

5. 添加外键

ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

5.4.3. 修改表时删除约束

1.删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

2.删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT;

3.删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

4.删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

5.删除外键

ALTER TABLE stuinfo DROP FOREIGN  KEY fk_stuinfo_major;

5.5. 标识列

又称为自增长列

含义：
就比如id列，没有标识列的话，你得自己输入123456保证不重复，麻烦，所以标识列可以不用手动插入值，系统提供默认的序列

特点：
1. 标识列必须和主键搭配吗？
答：不一定，但要求是一个key（主键外键唯一）
2. 一个表中只可以有至多一个标识列
3. 标识列的类型只能是数值型
4. 标识列可以通过SET auto_increment_increment设置步长，也可以通过手动插入值设置起始值
5. 请你知道有这么个东西叫标识列

5.5.1. 创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,#AUTO_INCREMENT自动增长
	NAME VARCHAR(20)
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity VALUES(NULL,'john');#不用修改就可以执行，会自动编号
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3;#把自增长的步长改为3，起始值mysql不支持设置，但步长可以
#有个更改起始值的办法：
INSERT INTO tab_identity VALUES(10,'john');
INSERT INTO tab_identity VALUES(NULL,'john');#这样就会从10开始，步长还是上面设置的3，即达到设置起始值的效果

5.5.2. 修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

5.5.3. 修改表时删除标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT;

6. TCL语言的学习

6.1. 事务和事务处理

Transaction Control Language 事务控制语言

事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。如果单元中某条sql语句一旦执行失败或产生错误，整个单元将会回滚。
回滚：
把操作撤销，回到最初状态

案例：转账
张三丰	1000
郭襄	1000
update 表 set 张三丰的余额=500 where name = "张三丰";
update 表 set 张三丰的余额=1500 where name = "郭襄";
#为防止一些原因造成的上面第一句执行成功，第二句无法执行的现象出现，引入事务，使得其作为一个整体一起成功或失败，不允许半截拉块的情况出现

存储引擎：
在mysql中的数据用各种不同的技术存储在文件（或内存）中。
通过show engines查看。
比如InnoDB，MyISAM，MEMORY；不是所有的存储引擎都支持事务，Innodb就支持事务。

事务的ACID属性
1.原子性
原子性是指事务是一个不可分割的工作单位，要么都发生要么都不发生。
2.一致性
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
3.隔离性
事物的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及是用的数据对并发的其他事物是隔离的，并发执行的各个事务之间不难相互干扰。但有的会受干扰，需要看隔离级别
4.持久性
一个事务一旦提交，将会永久地改变数据库的数据

6.2. 事务的创建

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

比如select、insert、update、delete语句  增删改查
SHOW VARIABLES LIKE '%autocommit%';#自动开启的，value=on
delete from 表 where id=1；

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

前提：
必须先设置自动提交功能为禁用

set autocommit=0;
SHOW VARIABLES LIKE '%autocommit%';#value=off

步骤1：开启事务
set autocommit=0;
start transaction;#可选的

步骤2：编写事务中的sql语句(select,insert,update,delete增删改查，不包括create，drop这些)
语句1;
语句2;
...

步骤3：结束事务
commit;提交事务
rollback;回滚事务
save point 节点名;#设置保存点

CREATE DATABASE test;

CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);
INSERT INTO account(username,balance)
VALUES('张无忌',1000),('赵敏',1000);

6.2.1.演示事务的使用步骤

#开启事务	
SET autocommit=0;
#编写一组事务的语句
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=1500 WHERE username='赵敏';

#结束事务
commit;
#ROLLBACK;#执行回滚把上一句注释掉，并把500,1500改成1000,1000，执行，发现还是500,1500

SELECT * FROM account;

6.2.2. 事务的并发问题

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取有必要的隔离机制，会导致各种并发问题。

脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段，之后，若T2回滚，T1读取的内容就是临时且无效的
不可重复读：对于两个事物t1，t2，t1读取了一个字段，然后t2更新了该字段，之后，t1再次读取同一个字段，值就不同了
幻读：对于两个事务t1，t2，t1从一个表中读取了一个字段，然后t2在该表中插入了一些新的行。之后，如果t1再次读取同一个表，就会多出几行（和脏读有点相似，但脏读一般是针对更新，幻读针对插入、删除）

举个栗子：
女朋友要给我打钱，今天突然多打了一个零，连忙撤销，我看到这个的数据临时有效，就是脏读；
我刷新了数据发现女朋友已经撤销了之前的命令，因为并没有提交，所以我看到我的银行卡上多的那个零已经没有了，同样的东西，两次不一样的结果就是不可重复读；
我有了钱了，我想请银行大厅的各位吃饭，一共两个人，我低头看了一下手机，再抬头的时候发现是三个人一共，这就是幻读。

通过设置事务的隔离级别来避免这些现象

6.2.3. 四种事务隔离级别

READ UNCOMMITTED（读未提交数据）：
允许事务读取未被其他事物提交的变更，脏读、不可重复读和幻读的问题都会出现

READ COMMITED（读已提交数据）:
只允许事务读取已经被其他事务提交的变更。可以避免脏读，但不可避免不可重复读和幻读。

REPEATABLE READ（可重复读）：
确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事物对这个字段进行更新。可以避免脏读和不可重复读，但存在幻读。

SERIALIZABLE（串行化）：
确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表进行插入、更新和删除，所有并发问题都可以避免，但性能低下。

mysql中默认第三个隔离级别 repeatable read；
oracle中默认第二个隔离级别 read committed；

#查看当前隔离级别
SELECT @@transaction_isolation;

#设置当前连接的隔离级别
set transaction isolation level read committed;

#设置数据库系统全局的隔离级别
set global transaction isolation level read committed;

6.2.4. 演示回滚

#演示delete

SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;#回滚成功

#演示truncate

SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;#没有回滚，实际删除了

#演示savepoint的使用
#设置节点保存点
SET autocommit=0;
START TRANSACTION;

DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

SELECT * FROM account;#25号删了，28号没删

7. 视图的讲解

含义：
虚拟表，和普通表一样使用
是通过表动态生成的数据，只保存sql逻辑，不保存查询结果

比如：
	舞蹈班和普通版的对比

视图：
	create view，只是保存了sql逻辑，语法允许增删改查（一般不能增删改，只用来查）

表：
	create table，保存了数据，增删改查

#案例：查询姓张的学生名和专业名
CREATE VIEW v1 #在这
AS SELECT
	stuname,
	majorName 
FROM
	stuinfo s
	INNER JOIN major m ON s.majorid = m.id;
#
SELECT
	* 
FROM
	v1 
WHERE
	stuname LIKE '张%';

7.1. 创建视图

语法：

creat view 视图名
as 查询语句;

#1. 查询姓名中包含a字符的员工名、部门名、工种信息
#创建
CREATE VIEW myv1 AS SELECT
last_name,
department_name,
job_title 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id
	JOIN jobs j ON j.job_id = e.job_id;#上面跟查询都一样，就是加上视图的部分
#
SELECT
	* 
FROM
	myv1 
WHERE
	last_name LIKE '%a%';

#2. 查询各部门的平均工资级别

#创建视图查看每个部门平均工资，myv2就像一个表
CREATE VIEW myv2 AS SELECT
AVG( salary ) ag,
department_id 
FROM
	employees 
GROUP BY
	department_id;
SELECT
	* 
FROM
	myv2;
#
SELECT
	myv2.ag,
	g.grade_level 
FROM
	myv2
	JOIN job_grades g ON myv2.ag BETWEEN g.lowest_sal 
	AND g.highest_sal;#原来需要子查询，也可以把这两段用一个视图做

#3. 查询平均最低的工资信息
#在视图myv2里
SELECT
	* 
FROM
	myv2 
ORDER BY
	ag 
	LIMIT 1;

#4. 查询平均工资最低的部门名和工资
CREATE VIEW myv3 AS SELECT
* 
FROM
	myv2 
ORDER BY
	ag 
	LIMIT 1;
#
SELECT
	d.*,
	m.ag 
FROM
	myv3 m
	JOIN departments d ON m.department_id = d.department_id;

优点

1. 重用sql语句
2. 简化复杂的sql操作，不必知道他的查询细节
3. 保护数据，提高安全性；
   比如中国移动仅给别人提供手机号，不会提供原表。

7.2. 修改视图

方式一

create or replace view 视图名
as 查询语句; #存在则修改，不存在则创建

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

方式二

alter view 视图名
as 查询语句;

ALTER VIEW myv3 AS SELECT
* 
FROM
	employees;

7.3. 删除视图

DROP VIEW myv1,
myv2,
myv3;

7.4. 查看视图

DESC myv3;
SHOW CREATE VIEW myv3;

7.5. 更新视图

CREATE 
		OR REPLACE VIEW myv1 AS SELECT
		last_name,
		email,
		salary*12*(1+IFNULL(commission_pct,0)) 'annual salary'
	FROM
		employees;
	SELECT
		* 
	FROM
		myv1;

插入

INSERT INTO myv1
VALUES
	( '张飞', 'zf@qq.com' );
SELECT
	* 
FROM
	employees;#也插入了张飞

修改

UPDATE myv1 
SET last_name = '张无忌' 
WHERE
	last_name = '张飞';

删除

DELETE 
FROM
	myv1 
WHERE
	last_name = '张无忌';

7.6. 不能更新的视图

具备以下特点的视图不能更新
#基本是说 复杂的视图 都不允许
#包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all

CREATE 
	OR REPLACE VIEW myv1 AS SELECT
	MAX( salary ) m,
	department_id 
FROM
	employees 
GROUP BY
	department_id;
SELECT
	* 
FROM
	myv1;
#更新(错误)
UPDATE myv1 
SET m = 9000 
WHERE
	department_id = 10;	

常量视图

CREATE 
	OR REPLACE VIEW myv2 AS SELECT
	'john' NAME;
SELECT
	* 
FROM
	myv2;

#更新（错误）
UPDATE myv2 
SET NAME = 'lucy';	

select中包含子查询

CREATE 
	OR REPLACE VIEW myv3 AS SELECT
	( SELECT MAX( salary ) FROM employees ) 最高工资 
#更新，错误
SELECT
	* 
FROM
	myv3;
UPDATE myv3 
SET 最高工资 = 10000;

join

CREATE 
	OR REPLACE VIEW myv4 AS SELECT
	last_name,
	department_name 
FROM
	employees e
	JOIN departments d ON e.department_id = d.department_id;

#更新（可以）
SELECT
	* 
FROM
	myv4;
UPDATE myv4 
SET last_name = '张飞' 
WHERE
	last_name = 'whalen';
#插入（报错）
INSERT INTO myv4
VALUES
	( '陈真', 'xxxx' );

from一个不能更新的视图

CREATE 
	OR REPLACE VIEW myv5 AS SELECT
	* 
FROM
	myv3;#更新
SELECT
	* 
FROM
	myv5;
UPDATE myv5 
SET 最高工资 = 10000 
WHERE
	department_id = 60;

where子句的子查询引用了from子句中的表

CREATE 
	OR REPLACE VIEW myv6 AS SELECT
	last_name,
	email,
	salary 
FROM
	employees 
WHERE
	employee_id IN ( SELECT manager_id FROM employees WHERE manager_id IS NOT NULL );
SELECT
	* 
FROM
	myv6;

#更新（错误）
UPDATE myv6 
SET salary = 10000 
WHERE
	last_name = 'K_ing';

		创建语法的关键字		是否实际占用物理空间				使用
视图	 create view		   只是保存了sql逻辑		 增删改查，一般不能增删改
表		 create table			保存了数据					增删改查

8. 变量

8.1. 系统变量

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

使用的语法：
1. 查看所有的系统变量
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;#会话变量，不写session也行

2. 查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';

3. 查看指定的某个系统变量
select @@global.系统变量名;

4. 为某个系统变量赋值
方式一：
set global 系统变量名=值;
方式二：
set @@global.系统变量名=值;

#会话变量可以将global替换成session，如果是全局级别要加global，不写默认session

8.1.1. 全局变量

作用域：
服务器每次启动将为所有全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启
服务器重启后修改后的就没用

①查看所有的全局变量
SHOW GLOBAL VARIABLES;

②查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';

③查看指定全局变量
SELECT @@global.autocommit;
SELECT @@transaction_isolation;

④为某个指定的全局变量赋值
SET @@global.autocommit = 0;

8.1.2. 会话变量

作用域：仅仅针对于当前会话（连接）有效

①查看所有的会话变量
SHOW SESSION VARIABLES;
SHOW VARIABLES;

②查看部分的会话变量
SHOW VARIABLES LIKE '%char%';

③查看指定的会话变量
SELECT @@transaction_isolation;
SELECT @@session.transaction_isolation;

④为某个会话变量赋值
SET @@session.transaction_isolation = 'read-uncommitted';
SET SESSION transaction_isolation = 'read-committed';

8.2. 自定义变量

说明：
	变量是用户自定义的，不是由系统指定的
使用步骤：
	声明
	赋值
	使用、查看、比较、运算等

8.2.1. 用户变量

作用域：
	针对当前会话有效，和会话变量的作用域相同

赋值操作符：
	=或:=

必须加@

1.声明并初始化

SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;

2.赋值（更新）

#方式一：set或select（和初始化一样）
	SET @用户变量名 = 值;
	SET @用户变量名 := 值;
	SELECT @用户变量名 := 值;
#方式二：select into
	SELECT 字段 INTO @变量名
	FROM 表;

SELECT
	COUNT(*) INTO @count 
FROM
	employees;

3.使用（查看用户变量）

SELECT @count; #107

8.2.2. 局部变量

作用域:仅仅在定义它的begin end中有效。

应用在begin end中的第一句话

需要限定类型

1.声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值; #类型和值要兼容或一致

2.赋值

#方式一：set或select（和初始化一样）
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT @局部变量名:=值;
#方式二：select into局部
	SELECT 字段 INTO 变量名
	FROM 表;
	
3、使用
SELECT 局部变量名;

3.对比用户变量和局部变量：

				作用域			定义和使用的位置			语法
用户变量		当前会话			会话中的任何地方				必须加@符号，不用限定类型
局部变量		begin end中		只能在begin end中，且为第一句话	一般不用加@符号，需要限定类型

4.案例：申明两个变量并赋值，求和，并打印

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

#2. 局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 2;
DECLARE SUM INT;
SET SUM = m + n;
SELECT SUM;	#报错，因为begin and

9.存储过程和函数

好处：
1. 提高代码的重用性
2. 简化操作

9.1. 存储过程

大型的会用到，比较复杂

含义：
一组预先编译好的sql语句的集合，理解成批处理语句

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

9.1.1. 存储过程的创建

创建语法

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

注意：

1.参数列表包含三部分

参数模式 参数名 参数类型
举例：
IN stuname VARCHAR(20)


参数模式：
IN : 该参数可以作为输入，即需要调用方传入值
OUT : 参数可以作为输出，即可以作为返回值
INOUT : 该参数既可以作为输入又可以作为输出，也就是既需要传入值，又可以返回值

2.如果存储过程体只有一句话, BEGIN END 可以省略

存储过程体中每条sql语句结尾必须加分号
存储过程的结尾可以使用 **DELIMITER** 重新设置
语法：
	DELIMITER 结束标记
例如：
	DELIMITER $ #navicat不加这个标记符号

调用语法
CALL 存储过程名(实参列表);

空参列表

要在窗口中运行，sqlyog没用
#案例：插入到admin表中5条记录

SELECT
	* 
FROM
	admin;

DELIMITER 
CREATE PROCEDURE myp1 () BEGIN
	INSERT INTO admin ( username, PASSWORD )
	VALUES
		( 'john1', '000' ),(
			'lily',
			'000' 
			),(
			'rose',
			'000' 
			),(
			'jack',
			'000' 
			),(
			'tom',
			'000' 
		);

END 

#调用
CALL myp1() 

创建带in模式参数的存储过程

#案例1：创建存储过程实现 根据女生名查询对应的男生信息

CREATE PROCEDURE myp2 (
	IN beautyName VARCHAR ( 20 )) BEGIN
	SELECT
		bo.* 
	FROM
		boys bo
		RIGHT JOIN beauty be ON bo.id = be.boyfriend_id 
	WHERE
		be.NAME = beautyName;
	
END 
SET NAMES gbk
CALL myp2 ( '周芷若' )

#案例2：创建存储过程，实现用户是否登陆成功

CREATE PROCEDURE myp4 (
	IN username VARCHAR ( 20 ),
	IN PASSWORD VARCHAR ( 20 )) BEGIN
	DECLARE
		res INT DEFAULT 0;#声明并初始化
	SELECT
		COUNT(*) INTO res #赋值
		
	FROM
		admin 
	WHERE
		admin.username = username 
		AND admin.PASSWORD = PASSWORD;
	SELECT
	IF
		( res > 0, '成功', '失败' );#使用
	
END 
#调用
CALL myp4 ( '张飞', '888' ) #失败

创建带out模式的存储过程

#案例1：根据女生名返回对应的男生名
CREATE PROCEDURE myp5 (
	IN beautyName VARCHAR ( 20 ),
	OUT boyName VARCHAR ( 20 )) BEGIN
	SELECT
		bo.boyName INTO boyName 
	FROM
		boys bo
		INNER JOIN beauty be ON be.boyfriend_id = bo.id 
	WHERE
		be.NAME = beautyName;

END
#调用
SET @bName
CALL myp5('小昭',@bName)
SELECT @bName

#案例2：根据女生名，返回对应的男生名和魅力值
CREATE PROCEDURE myp6 ( IN beautyName VARCHAR ( 20 ), OUT boyName VARCHAR ( 20 ), OUT userCP INT ) BEGIN
	SELECT
		bo.boyName,
		bo.userCP INTO boyName,
		userCP 
	FROM
		boys bo
		INNER JOIN beauty be ON be.boyfriend_id = bo.id 
	WHERE
		be.NAME = beautyName;

END
#调用
CALL myp6 (
	'小昭',
@bName,
@usercp)
SELECT
	@bName,
	@usercp$

创建带in，out模式参数的存储过程

#案例1：传入a和b两个值，最终a和b都翻倍并返回

CREATE PROCEDURE myp7 ( INOUT a INT, INOUT b INT ) BEGIN
	
	SET a = a * 2;
	
	SET b = b * 2;
	
END 


SET @m=10
SET @n=20
CALL myp7(@m,@n)
SELECT @m,@n

9.1.2. 存储过程的删除和查看

#删除存储过程
#语法 drop procedure 存储过程名
DROP PROCEDURE myp1;
DROP PROCEDURE myp1,myp2;#错误

#查看存储过程的信息
DESC myp2;#错误

SHOW CREATE PROCEDURE myp2;

9.2. 函数

与存储过程的区别
1. 存储过程可以有0个返回，也可以有多个，函数只能有1个返回（0个也不行，多个也不行）
2. 存储过程适合做批量插入、批量更新；函数适合做处理数据后返回一个结果
#创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

注意：
1. 参数列表包含两部分-----参数名 参数类型
2. 函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体最后也不报错，但不建议

return 值;

3. 当函数体只有一句话可以省略begin，end

4. 使用delimiter语句设置结束标记


#调用语法
SELECT 函数名(参数列表)

9.2.1. 无参有返回

#案例：返回公司员工的个数
SET GLOBAL log_bin_trust_function_creators=1;#不加这句下面的可能会报错

CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义变量
	SELECT COUNT(*) INTO c #赋值
	FROM employees;
	RETURN c;
END 

SELECT myf1()

9.2.2. 有参有返回

#案例1：根据员工名返回它的工资

CREATE FUNCTION myf2 (
	empName VARCHAR ( 20 )) RETURNS DOUBLE BEGIN
	
	SET @sal = 0;#定义用户变量
	SELECT
		salary INTO @sal #赋值
		
	FROM
		employees 
	WHERE
		last_name = empName;
	RETURN @sal;
	
END SELECT
	myf2 ( 'kochhar' )

#案例2：根据部门名，返回该部门平均工资

CREATE FUNCTION myf3 (
	deptName VARCHAR ( 20 )) RETURNS DOUBLE BEGIN
	DECLARE
		sal DOUBLE;#局部变量
	SELECT
		AVG( salary ) INTO sal 
	FROM
		employees e
		JOIN departments d ON e.department_id = d.department_id 
	WHERE
		d.department_name = deptName;
	RETURN sal;
	
END SELECT
	myf3 ( 'IT' )

9.2.3. 查看函数

SHOW CREATE FUNCTION myf3;

9.2.4. 删除函数

DROP FUNCTION myf3;

10. 流程控制结构

顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上重复执行一段代码

10.1. 分支结构

10.1.1. if函数

功能：
	实现简单的双分支
语法：
	SELECT IF(表达式1,表达式2,表达式3)
执行顺序：
	如果表达式成立，则if函数返回表达式2的值，否则返回表达式3的值   #很像EXCEL

应用：
	任何地方

10.1.2. case结构

情况1：

类似于java中的switch语句，一般用于实现的等值判断

语法：
	CASE 变量|表达式|字段
	WHEN 要判断的值 THEN 返回的值1或语句1;#语句要加分号
	WHEN 要判断的值 THEN 返回的值2或语句2;
	...
	ELSE 要返回的值n或语句n;
	END CASE;

情况2：

类似于就java中的多重if，一般用于实现区间判断

语法：
	CASE 
	WHEN 要判断的条件1 THEN 返回的值1或语句1;
	WHEN 要判断的条件2 THEN 返回的值2或语句2;
	...
	ELSE 要返回的值n或语句n;
	END CASE;

特点：
	1. 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方;也可以作为独立的语句使用，只能放在 BEGIN END 中

	2. 如果 WHEN 中的值或条件成立，则执行对应的 THEN 后面的语句，并且结束 CASE;如果都不满足，则执行 ELSE 中的语句或值

	3. ELSE 可以省略，如果省略了，并且所有的 WHEN 都不满足，则返回 NULL

案例:
#创建存储过程，根据传入的成绩，来显示等级，比如传入90-100分显示A

CREATE PROCEDURE test_case ( IN score INT ) BEGIN
	CASE
			
			WHEN score >= 90 
			AND score <= 100 THEN SELECT 'A'; WHEN score >= 80 THEN
			SELECT
				'B';
			
			WHEN score >= 60 THEN
			SELECT
				'C';
			ELSE SELECT
				'D';
			
		END CASE;
		
	END CALL test_case (
95)

10.1.3. if结构

功能：
	实现多重分支

语法：
	IF 条件 1 THEN
		语句 1;
		
		ELSEIF 条件 2 THEN
		语句 2;
	...【 ELSE 语句 n;】 
	END IF;
		 
应用场合：只能用在 BEGIN END 中

#案例1：根据传入的成绩，来返回等级，比如传入90-100分返回A

CREATE FUNCTION test_if ( score INT ) RETURNS CHAR BEGIN
	IF
		score >= 90 
		AND score <= 100 THEN RETURN 'A'; ELSEIF score >= 80 THEN
		RETURN 'B';
		
		ELSEIF score >= 60 THEN
		RETURN 'B';
		ELSE RETURN 'D';
		
	END IF;

END

SELECT
	test_if (86)

10.2. 循环结构

10.2.1. while、loop、repeat

分类：
	while、loop、repeat

循环控制：
	iterate类似于continue，结束本次循环，继续下一次
	leave类似于break，跳出当前所在的循环

适用范围：
	仅begin end中

while

#先判断后执行
语法：
【标签:】while 循环条件 do
	循环体;
end while 【标签】;

loop

#没有条件的死循环 
语法：
【标签：】loop
	循环体
end loop 【标签】;

没有条件，可以用来模拟简单的死循环，不然要搭配leave

repeat

#先执行后判断
语法：
【标签:】 repeat
	循环体;
until 结束循环的条件
end repeat【标签】;

10.2.2. 没有添加循环控制语句

#案例：批量插入，根据次数插入到admin表中多条记录
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 (
10)

10.2.3. 添加leave语句

#案例：批量插入，根据次数插入到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 ), '000' ); IF i >= 20 THEN
			LEAVE a;
			
		END IF;
		
		SET i = i + 1;
		
	END WHILE a;
	
END CALL test_while1 (
21)

10.2.3. 添加iterate语句

#案例：批量插入，根据次数插入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 ), '000' );
		
	END WHILE a;
	
END CALL test_while1 (
21)

10.3. 经典案例

已知表stringcontent
其中字段：
id：自增长
content varchar(20)

#要求：向该表插入指定个数的，随机的字符串

	CREATE TABLE IF NOT EXISTS stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
);

DELIMITER 
CREATE PROCEDURE test_randstr_insert(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1; #定义一个循环变量i,表示插入的次数
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startIndex INT DEFAULT 1;#代表起始索引
	DECLARE len INT DEFAULT 1;#代表截取的字符的长度
	WHILE i<=insertCount DO		
		SET len=FLOOR(RAND()*(20-startIndex+1)+1);#产生一个随机的整数，代表截取长度 1-(26-startIndex+1)
		SET startIndex=FLOOR(RAND()*26+1);#产生一个随机的整数，代表起始索引1-26
		INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startIndex,len));
		SET i=i+1; #循环变量更新
	END WHILE;
END 

CALL test_randstr_insert(6)