一、TCL事务控制语言 二、MySQL中的约束 三、多表查询（重点） 四、用户的创建和授权 五、MySQL中的索引

一、TCL事务控制语言
###<1>事务的概念
事务是访问并可能更新数据库中各种数据项的执行单元。
事务是一条SQL语句，一组SQL语句，或者整个程序。
事务是恢复和并发控制的基本单位。
事务四个特性：ACID特性，原子性，一致性，隔离性，持续性。

###<2>为什么要使用事务
转账操作：用户A给用户B转账1000元
隐含的条件：扣钱和加钱，要么同时成功，或者同时失败。


事务就是对数据库中的数据的多步操作，并且可以控制数据操作的一致性。对数据的多步操作要么同时成功，或者同时失败。

###<3>事务的特点
1. A(Atomicity)原子性
一个事务的执行被视为不可分割的最小单元。
要么同时成功，或者同时失败。
2. C(consistency)一致性
一个事务的执行不应该破坏数据库的完整性约束。
非法数据的录入，事务会进行撤销操作。
3. I(Isolation)隔离性
通常来说，事务和事务之间不会相互影响。
4. D(Durability)持久性
软硬件崩溃，事务会去自动恢复。（Innodb存储引擎会自动恢复）

###<4>事务的使用
准备：
create table account(
name varchar(20),
money float(6,2)
);

insert account value('张三',22.22);

语法：
set autocommit = true|false; -- 设置事务的提交方式（自动提交和手动提交）
rollback; -- 事务的回滚
commit; -- 事务的提交
savepoint 还原点名称； -- 保存还原点
rollback to 还原点名称; -- 回滚到某个还原点

例如：
set autocommit = false;//设置手动提交
delete from account ;
commit;

insert account value('张三',22.22);
update account set money = money-0.22;
savepoint p1;
update account set money = money-10;
savepoint p2;
update account set money = money-10;
savepoint p3;
delete from account ;
rollback to p2;

###<5>存储引擎
5.1 概念
存储数据用的技术（存储机制，索引技术，锁定水平等等）

5.2 查看存储引擎
show engines;

5.3 常用的存储引擎
MyISAM
相对简单的存储引擎，不支持事务，不支持外键，访问速度快
以查询为主，对数据的完整性要求不严格，推荐使用MyISAM

InnoDB
支持事务，支持外键，行级别锁机制
多重并发更新的操作比较多，推荐使用Innodb
支持auto_increment属性
自动灾难恢复机制，软硬件崩溃

# 二、MySQL中的约束
###<1>约束
约束就是一种限制，通过对表的行和列的数据进行限制，确保数据的完整性和唯一性。

###<2>使用场景
在创建表的时候，创建约束

###<3>分类
1. default,默认约束，指定某列的默认值， 限制此列数据的正确性。（保证数据的域完整性）
2. not null,非空约束，指定某列的值不为空，限制此列数据的正确性。（保证数据的域完整性）
3. unique ,唯一约束，指定某列或者列组合的数据不能重复，保证数据的实体完整性。
4. primary ，主键约束，指定某列或者列组合的数据不能重复并且不能为空，保证数据的实体完整性。
5. foreign key ,外键约束，子表中的一条数据参照主表中的数据，保证数据的参照完整性。
6. check ,检查约束（保证数据的域完整性），MySQL不支持检查约束。
7. unsigned,无符号约束，指定某列的数值为正数，保证数据的域完整性。

### 3.1 default约束
添加约束：
<1>创建表的时候添加
create table t1(
id int default 1,
name varchar(20) default '小明'
);

<2>通过alter...modify/change添加
alter table t1 modify id int default 2;
alter table t1 change name name int default '小李';

删除约束：
通过alter...modify/change
alter table t1 modify id int ;

###3.2 not null 非空约束
添加约束：
<1>创建表的时候添加
create table t1(
id int ,
name varchar(20) not null
);

<2>通过alter...modify/change添加
alter table t1 modify id int not null;

删除约束：
通过alter...modify/change
alter table t1 modify id int ;

###3.3 唯一约束
添加约束：
<1>创建表时添加
create table t2(
id int unique,
name varchar(20)

);
insert t2 value(1,'小明');
insert t2 value(1,'小张');

create table t2(
id int unique,
name varchar(20)
[constraint unique_name] unique(字段1,字段2,...)
);

<2>通过alter...modify/change
alter table t2 modify/change name unique;
alter table t2 ADD [constraint unique_name] unique(字段1,字段2,...);

<3>删除约束
alter table t2 drop index unique_name;
drop index unique_name on table_name;

注意：删除唯一约束时，如果没有给唯一约束起名称，可以使用删除建立了唯一约束的第一个列名。

###3.4 主键约束
<1>创建约束
create table t6(
id int primary key,
name varchar(20)
);

<2>通过alter...modify/change
alter table t6 modify/change id primary key;
alter table t6 add [constraint pk_name] primary key(id,name);

<3>删除约束
alter table t6 drop primary key;

注意：
show create table 表名；查看当前表的约束设置

一张表只能有一个主键
删除主键后，当前列还保存非空约束
删除主键前，保证当前主键列没有设置成自增长。
create table t7(
id int primary key auto_increment,
name varchar(20)
);

###3.5 自增长约束
约束列的数量自动增加，设置到整数类型的列上
通常在主键上设置自增长约束

删除约束：alter...modify/change

create table xxx() auto_increment=初始值；

###3.6 外键约束
#####问题一：为什么要创建多张表？
减少数据的冗余，避免资源的过度浪费

用户订单表：user_order
用户名 密码 地址 联系方式 ..... 订单编号 状态 日期 商品信息...
小明 123 北京 15555225 0001 0 20180101 ...
小明 123 北京 15555225 0002 1 20180108 ...
小明 123 北京 15555225 0003 2 20180104 ...
小明 123 北京 15555225 0004 3 20180101 ...
小明 123 北京 15555225 0005 4 20180104 ...
小明 123 北京 15555225 0006 5 20180101 ...

######解决：拆表
用户表 user （主表）
用户名 密码 地址 联系方式
小明 123 北京 15555225


订单表 order （从表）
订单编号 状态 日期 商品信息... 用户名
0001 0 20180101 ... 小明
0002 1 20180108 ... 小明
0003 2 20180104 ... 小明
0004 3 20180101 ... 小明
0005 4 20180104 ... 小明
0006 5 20180101 ... 小明

######建立关系
建立关系：将主表中能够唯一的字段，添加到从表中。

######添加约束
添加外键约束

<0>准备
create table users(
uid int primary key auto_increment,
uname varchar(20) not null unique,
upwd varchar(20) not null
);

create table orders(
oid int primary key auto_increment,
stat tinyint not null,
date datetime not null,
uid int
);

<1>添加约束
create table orders(
oid int primary key auto_increment,
stat tinyint not null,
date datetime not null,
uid int,
[constraint fk_name] foreign key(uid) references users(uid)
);

通过：alter table orders add [constraint fk_name] foreign key(uid) references users(uid);

<2>删除外键约束
alter table orders drop foreign key fk_name;
如果不知道当前删除的外键的名称，show create table 表名；

#####问题二：表于表之间的关系？
一对一：
一对多：
多对多： 订单表 ---------- 商品表
学生表 ---------- 课程表
关系表
sid cid

需求分析:分析数据表（属性），分析表和表之间的关系
逻辑设计：将需求转换成关系模型ER图
表的设计：
物理设计：

### 外键中的级联关系
1. on delete cascade 删除主表中的数据时，从表中的数据会随之一起删除；
2. on update cascade 更新主表中的数据时，从表中的数据会随之更新
3. on delete set null 删除主表中的数据时，从表中的数据会置空

设置级联删除：
create table orders(
oid int primary key auto_increment,
stat tinyint not null,
date datetime not null,
uid int,
constraint fk_name foreign key(uid) references users(uid) on delete cascade
);

### 3.7 无符号和零填充约束
添加约束：
<1>创建表的时候添加
create table t1(
id int default 1 unsigned , -- 添加无符号约束
name varchar(20) default '小明',
age int(3) zerofill -- 添加零填充约束
);

<2>通过alter...modify/change添加

删除约束：
通过alter...modify/change

# 三、多表查询(重点)
### 1. 分类
<1>联合查询、合并查询 ： union /union all
<2>连接查询:
内连接：inner join on
外连接：
左外连接：
右外连接：
交叉连接：
全外连接：
自然连接：natural join on
自连接：

<3>子查询:SQL语句的嵌套

### 2. 合并查询
将两个select语句查询的结果合并在一起。

合并的方式一：union ,去除重复的数据
create table t9(
id int primary key auto_increment,
name varchar(20)
);

create table t10(
num int primary key auto_increment,
name2 varchar(20)
);

insert t9 value(null,'a1');
insert t9 value(null,'a2');
insert t9 value(null,'a3');

insert t10 value(default,'a1');
insert t10 value(default,'a2');
insert t10 value(default,'a4');

select * from t9 union select * from t10;

合并的方式二：union all
select * from t9 union all select * from t10;

注意：被合并的两个结果集中的属性和属性的类型必须一致。

### 3. 连接查询
多个表的数据的乘积。
产生笛卡尔积现象。
给两张表建立关联关系，过滤错误的数据

insert users value(1,'xiaozhang','123');
insert users value(2,'xiaoli','456');
insert users value(3,'xiaowang','789');

insert orders value(1001,1,now(),1);
insert orders value(1002,1,now(),1);

insert orders value(1003,1,now(),3);
insert orders value(1004,1,now(),3);

select * from users,orders; -- 笛卡尔积现象
select * from users,orders where users.uid=orders.uid;

#####3.1 内连接
语法格式：
select u.uid from users u inner join orders o on u.uid=o.uid;

#####3.2 外连接
左外连接：
select u.uid,u.uname,o.oid, o.date from users u left outer join orders as o on u.uid=o.uid;
先查左表，然后查询右表。右表中满足条件的数据项显示，不满足条件的则显示null。

select u.uid,u.uname,o.oid, o.date from orders o left outer join user as u on u.uid=o.uid;
先查左表，然后查询右表。右表中满足条件的数据项显示，不满足条件的则显示null。

右外连接：
select u.uid,u.uname,o.oid, o.date from users u right outer join orders as o on u.uid=o.uid;
先查右表，然后查询左表。左表中满足条件的数据项显示，不满足条件的则显示null;

自然连接：natural ,自动寻找主外键连接等式
select * from users natural join orders ;
select * from users natural left join orders ;
select * from users natural right join orders ;

自连接：自己连接自己
create table employee(
num int primary key ,
name varchar(20) not null,
leadnum int
) ;

insert employee value(1001,'蒙奇D龙');
insert employee value(1003,'伊万科娃',1001);
insert employee value(1004,'暴君熊',1001);

insert employee value(1002,'五老星');
insert employee value(1005,'七武海',1002);
insert employee value(1006,'黄猿',1002);
insert employee value(1007,'赤犬',1002);
insert employee value(1008,'青稚',1002);

需求：打印伊万科娃和其领导的信息
select * from employee e1 ,employee e2 where e1.num=e2.leadnum and e2.name='伊万科娃'；

select * from employee e1 ,employee e2 where e1.num=e2.leadnum and e1.name='伊万科娃';
查找伊万科娃的下属

###4. 子查询
select查询语句的嵌套

子查询中的查询语句的位置：
where后面：作为条件
from后面：作为查询的数据表

例如：
查询订单表中价格大于平均订单价格的订单信息
select * from orders where price>avg(price); -- 不可行

第一步：求订单的平均价格
select avg(price) from orders;
第二步：查询
select * from orders where price>(select avg(price) from orders) ;

练习：
1. 查询密码和用户小刘一样的用户信息
2. 查询订单数量大于1的用户信息

# 四、用户的创建和授权
授权语法格式：
GRANT 权限 ON 数据库.数据表 TO 用户名@ip地址 IDENTIFIED BY 密码;

例如：
0. 使用root账号登陆数据库
1. create database testDB;

2. grant all privileges on testDB.* to xiaoming@localhost identified by '123';
flush privileges;

3. grant select ,update on testDB.* to laowang@localhost identified by '123';

4. grant select ,update ,delete ,create ,drop ,alter on *.* to wangayi@'%' identified by '123';

注意：'%'对非本地的主机授权，赋予所有权限可以使用：all privileges

删除用户：
drop user 用户名@"%"