MySQL（三）

10. 数据约束

10.1 默认值 default

-- 创建数据表的过程中，指定字段可以带有默认值，如果用户没有指定数据的情况下，当前
-- 字段会采用默认值方式来进行数据赋值操作。
-- default
create table person1
(
    id int,
    name varchar(50),
    country varchar(50) default 'PRC' -- 默认值字段
);

desc person1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(50) | YES  |     | NULL    |       |
| country | varchar(50) | YES  |     | PRC     |       |
+---------+-------------+------+-----+---------+-------+

-- 不给予带有默认值字段对应的数据，会采用默认值方式赋值当前子弹
insert into person1(id, name) value (1, '骚磊');

-- 给予当前带有默认值字段数据赋值操作，会采用给予的数据赋值当前字段
insert into person1(id, name, country) value (2, '航海中路彭于晏', '中华人民共和国');

10.2 非空 not null

-- not null 非空，要求当前字段必须有对应的数据，如果没有赋值报错
-- NN
-- 实际使用中必要字段！！！

create table person2
(
    id int,
    name varchar(50) not null,
    country varchar(50) default 'PRC' -- 默认值字段
);

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(50) | NO   |     | NULL    |       |
| country | varchar(50) | YES  |     | PRC     |       |
+---------+-------------+------+-----+---------+-------+


insert into person2(id, name) VALUE (1, '46号技师');
-- ERROR Field 'name' doesn't have a default value
insert into person2(id) VALUE (2);

10.3 唯一 unique

-- 字段使用unique约束，当前字段中保存的数据在当前表内不可以重复

create table person3
(
    id int unique,
    name varchar(50) not null,
    country varchar(50) default 'PRC' -- 默认值字段
);

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  | UNI | NULL    |       |
| name    | varchar(50) | NO   |     | NULL    |       |
| country | varchar(50) | YES  |     | PRC     |       |
+---------+-------------+------+-----+---------+-------+

insert into person3(id, name) value (1, '哆啦A磊');
-- ERROR Duplicate entry '1' for key 'id'
-- 对应当前ID值已经存在，不能再次插入相同id数据
insert into person3(id, name) value (1, '老骚');

-- id使用unique限制唯一，但是null不作为唯一判断范围以内
insert into person3(name) value ('老骚');
insert into person3(name) value ('超超');
insert into person3(id, name) value (null, '贱贱的我就长大了');
insert into person3(id, name, country) value (null, '贱贱的我就长大了',null);

10.4 主键 primary key

-- 主要要求唯一，非空！！！
-- primary key 主键
-- 主键一般用于在开发中涉及到数据的唯一性参照物，但是不能使用带有业务逻辑要求数据作为
-- 主键，例如 性别 年龄 工资....

create table person4
(
    id int primary key,
    name varchar(50) not null,
    country varchar(50) default 'PRC' -- 默认值字段
);

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(50) | NO   |     | NULL    |       |
| country | varchar(50) | YES  |     | PRC     |       |
+---------+-------------+------+-----+---------+-------+

insert into person4(id, name) value (1, '骚磊');
-- ERROR Duplicate entry '1' for key 'PRIMARY'
-- 对应使用primary key修饰的主键id已存在。
insert into person4(id, name) value (1, '骚磊');
-- ERROR Field 'id' doesn't have a default value
insert into person4(name) value ('骚磊');
-- ERROR Column 'id' cannot be null
insert into person4(id, name) value (null, '骚磊');

10.5 自增长 auto_increment

-- 自增长修饰字段会在数据添加的过程中自动赋值叠加操作
-- auto_increment修饰的字段必须是一个Key
-- AI ==> auto_increment
create table person5
(
    id int primary key auto_increment,
    name varchar(50) not null,
    country varchar(50) default 'PRC' -- 默认值字段
);

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | NO   |     | NULL    |                |
| country | varchar(50) | YES  |     | PRC     |                |
+---------+-------------+------+-----+---------+----------------+

-- 自增长字段会从1开始
insert into person5(name) value ('郭德纲'); -- id = 1
insert into person5(name) value ('于谦'); -- id = 2

-- 自增长可以指定数值，而且存在影响自增长计数情况
insert into person5(id, name) value (5, '高峰'); -- id = 5
insert into person5(name) value ('栾云平'); -- id = 6

-- 自增长可以指定数值，而且存在影响自增长计数情况 
insert into person5(id, name) value (8,'岳云鹏'); -- id = 8
insert into person5(name) value ('孙越'); -- id = 9

-- 指定id为7，但是当前id数据小于自增长，不影响自增长结果 数据保存排序
insert into person5(id, name) value (7, '郭麒麟'); -- id = 7
insert into person5(name) value ('阎鹤祥'); -- id = 10;

-- 删除数据 
delete from person5 where id = 7;
delete from person5 where id = 10;

-- 被删除的id可以指定使用
insert into person5(id, name) value (7, '郭麒麟'); -- id = 7
-- delete删除不会印象自增长计数
insert into person5(name) value ('阎鹤祥'); -- id = 11
delete from person5 where id = 11;

-- 清空数据表，同时会重置自增长
truncate person5;

-- 数据库彻底关闭，会影响到自增长保存

10.6 外键约束

create table employee
(
    id int primary key auto_increment,
    name varchar(50) not null,
    deptName varchar(50)
);

-- 这里会导致数据冗余问题。并且存在资源的浪费
insert into employee(name, deptName) value ('骚磊', '帅部');
insert into employee(name, deptName) value ('彭于晏', '帅部');
insert into employee(name, deptName) value ('吴彦祖', '帅部');
insert into employee(name, deptName) value ('烧饼', '骚气无敌部');
insert into employee(name, deptName) value ('岳云鹏', '贱到无敌部');
insert into employee(name, deptName) value ('曾小贤', '贱到无敌部');
insert into employee(name, deptName) value ('孟鹤堂', '骚气无敌部');

drop table employee;

-- 部门表
create table dept
(
    id int primary key auto_increment,
    deptName varchar(50) not null
);

-- 员工表，员工表中存在字段和部门表有直接联系，deptId ==》 dept表内的id
create table employee
(
    id int primary key auto_increment,
    name varchar(50) not null,
    deptId int
);

-- 准备部门表数据
insert into dept(deptName) value ('帅部');
insert into dept(deptName) value ('贱到无敌部');
insert into dept(deptName) value ('骚气无敌部');

-- 插入员工数据
insert into employee(name, deptId) value ('骚磊', 1);
insert into employee(name, deptId) value ('彭于晏', 1);
insert into employee(name, deptId) value ('吴彦祖', 1);
insert into employee(name, deptId) value ('烧饼', 3);
insert into employee(name, deptId) value ('岳云鹏', 2);
insert into employee(name, deptId) value ('曾小贤', 2);
insert into employee(name, deptId) value ('孟鹤堂', 3);

-- 内连接联表查询目标数据
select e.id, e.name, d.deptName
from nzgp2001.employee e
inner join dept d on  d.id = e.deptId;

-- 1. 目前数据存储效率是高于第一种方法
-- 2. 数据存储的复杂度，修改操作都是高于第一种方法

-- 当前数据插入操作是完全就可以执行，但是郭德纲的数据在存储到数据表中之后
-- 部门ID号是不合理的！！！
insert into employee(name, deptId) value ('郭德纲', 4);

-- SQL语句可以执行，但是存在部门表删除后，对应的员工数据存储存在隐患
delete from dept where id = 3;

drop table dept;
drop table employee;

-- 使用外键约束

-- 部门表 【主表】
create table dept
(
    id int primary key auto_increment,
    deptName varchar(50) not null
);

-- 员工表，员工表中存在字段和部门表有直接联系，deptId ==》 dept表内的id
-- 【从表】
create table employee
(
    id int primary key auto_increment,
    name varchar(50) not null,
    deptId int,
    -- 外键声明约束
    -- constraint 声明使用关键字
    -- fk_emp_dept 外键名称 fk ==> foreign key emp ==> employee dept ==> 部门表
    -- foreign key 外键声明关键字(从表外键字段) deptId employee表内的外键是deptId
    -- references 选择当前外键关联的主表字段关键字 主表(字段) dept(id)
    constraint fk_emp_dept foreign key (deptId) references nzgp2001.dept(id)
);

-- 准备部门表数据
insert into dept(deptName) value ('帅部');
insert into dept(deptName) value ('贱到无敌部');
insert into dept(deptName) value ('骚气无敌部');

-- 插入员工数据
insert into employee(name, deptId) value ('骚磊', 1);
insert into employee(name, deptId) value ('彭于晏', 1);
insert into employee(name, deptId) value ('吴彦祖', 1);
insert into employee(name, deptId) value ('烧饼', 3);
insert into employee(name, deptId) value ('岳云鹏', 2);
insert into employee(name, deptId) value ('曾小贤', 2);
insert into employee(name, deptId) value ('孟鹤堂', 3);

-- Cannot add or update a child row: a foreign key constraint fails (`nzgp2001`.`employee`,
-- CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 该数据条件时会检查主表，询问主表中是否存在指定的字段数据
insert into employee(name, deptId) value ('郭德纲', 4);

-- Cannot add or update a child row: a foreign key constraint fails (`nzgp2001`.`employee`,
-- CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 修改部门id为3的数据，改为10,deptId == 10 在主表中不存在
update employee set deptId = 10 where deptId = 3;

-- Cannot delete or update a parent row: a foreign key constraint fails (`nzgp2001`.`employee`,
-- CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- 删除主表数据行，需要询问从表中是否有使用当前主表数据的数据行，如果有，无法删除。
delete from nzgp2001.dept where nzgp2001.dept.id = 3;

update nzgp2001.dept set id = 10 where id = 3;

-- 使用外键约束操作优势和问题
-- 优势：
--      1. 主表从表关联性更强
--      2. 主表从表数据操作时存在更多约束
--      3. 操作规范性更强’

-- 问题：
--      1. 从表操作过程中添加和修改约束过强，需要首先操作主表
--      2. 主表操作过程中删除和修改约束过程，需要首先操作从表
--      3. 整个数据库更新，删除操作过于繁琐，并且后期维护压力过大

-- 外键约束虽然可以从SQL语句的语法上约束一定操作，但是会导致更多的问题，在开发中不允许使用。

10.7 级联操作

-- 在外键约束的情况下，操作主表和从表都有很多约束
-- SQL中引入的级联操作
-- 级联删除 on delete cascade
-- 级联修改 on update cascade
-- 主表操作执行删除或者修改时，对于外键约束的从表会对应操作。

drop table dept;
drop table employee;

-- 使用外键约束

-- 部门表 【主表】
create table dept
(
    id int primary key auto_increment,
    deptName varchar(50) not null
);

-- 员工表，员工表中存在字段和部门表有直接联系，deptId ==》 dept表内的id
-- 【从表】
create table employee
(
    id int primary key auto_increment,
    name varchar(50) not null,
    deptId int,
    -- 外键声明约束
    -- constraint 声明使用关键字
    -- fk_emp_dept 外键名称 fk ==> foreign key emp ==> employee dept ==> 部门表
    -- foreign key 外键声明关键字(从表外键字段) deptId employee表内的外键是deptId
    -- references 选择当前外键关联的主表字段关键字 主表(字段) dept(id)
    constraint fk_emp_dept foreign key (deptId) references nzgp2001.dept(id)
    on delete cascade -- 级联删除
    on update cascade -- 级联修改
);

-- 准备部门表数据
insert into dept(deptName) value ('帅部');
insert into dept(deptName) value ('贱到无敌部');
insert into dept(deptName) value ('骚气无敌部');

-- 插入员工数据
insert into employee(name, deptId) value ('骚磊', 1);
insert into employee(name, deptId) value ('彭于晏', 1);
insert into employee(name, deptId) value ('吴彦祖', 1);
insert into employee(name, deptId) value ('烧饼', 3);
insert into employee(name, deptId) value ('岳云鹏', 2);
insert into employee(name, deptId) value ('曾小贤', 2);
insert into employee(name, deptId) value ('孟鹤堂', 3);

-- 主表中有外键约束的字段数据发生修改，从表中对于字段同时发生修改，级联修改
update dept set id = 10 where id = 3;

-- 主表字段删除，从表中使用主表约束的数据行也需要删除
delete from nzgp2001.dept where id = 1;

11. 数据表修改 alter

11.1 添加字段

-- 默认在最后一个字段之后添加
alter table employee add age int;

11.2 修改字段

-- 修改字段对应数据类型和约束
alter table employee modify name char(50) not null;

11.3 修改字段

-- 修改字段名字，对应数据类型和约束问题
alter table employee change name userName varchar(50) not null;

11.4 删除字段

-- 删除对应的字段
alter table employee drop age;

11.5 修改表名

-- 修改表名字
rename table employee to emp;

12 事务

12.1 什么是事务

	操作数据库，修改，删除【慎用】，修改删除操作过程中，如果出现操作失误，会导致数据丢失或者全部被毁，而且没有Ctrl + Z
	数据库操作中操作失误是很常见的，数据库开发中设置有事务处理机制，也就是所谓回滚。

12.2 模拟银行转账

12.2.1 正确情况

-- 转账1000过程
update acount set money = money - 1000 where id = 1;
update acount set money = money + 1000 where id = 2;

12.2.2 异常情况

-- 错误情况:转账1000过程
update acount set money = money - 1000 where id = 1;
-- 断电，断网，电脑被砸了，数据丢失了，网络传输中断了。。。。
-- 第一句SQL语句是没有执行成功的

-- 一切都恢复了！！！
update acount set money = money + 1000 where id = 2;

12.3 事务的概念

	事务可以认为是SQL语句处理的最小单元，具有一定的原子性，是由一条或者多条SQL语句组成，事务执行完成，那么所有的SQL语句全部执行，都要执行成功。一旦出现任何一个SQL语句异常，回到事务执行之前，事务执行失败。

12.4 事务的边界

-- 开始:
-- 	当前事务，当前一整套和业务逻辑相关的内容一起执行
-- 	一旦执行 set autocommit = 0; 数据库SQL语句执行不再立即执行，而且一种类似于Stream流状态，需要等待提交生效，但是操作的过程中数据库可观察。
	set autocommit = 0;
	start transaction;
	
-- 结束
-- 	1. 确认没有问题
	commit; -- 显式提交
-- 	隐式方式提交: 一条创建，删除SQL语句，或者数据库正常关闭

-- 2. 回滚操作
	rollback; -- 显式回滚
-- 自动回滚，执行创建，删除语句失败，或者数据库非正常退出

12.5 事务操作演示

-- 开启事务
set autocommit = 0;
update acount set money = money - 2000 where id = 1;
select * from acount;

update acount set money = money + 2000 where id = 2;
select * from acount;

-- 确认无误，SQL语句提交执行
commit;

-- 开启事务
set autocommit = 0;
update acount set money = money - 2000 where id = 1;
select * from acount;

update acount set money = money + 2000 where id = 2;
select * from acount;

-- 发现问题，手动提交rollback 回滚
rollback;

-- 开启事务
start transaction;
update acount set money = money - 2000 where id = 1;
select * from acount;

update acount set money = money + 2000 where id = 2;
select * from acount;

commit ;

12.6 事务的特征

原子性
	事务操作是一个整体，所有的SQL语句完成一个事务，事务只有两种可能性，执行成功，或者失败。
	
一致性
	事务操作如果失败，其中所有的数据操作过程，全部会回到原来的起点。
	
隔离性
	在事务情况下，查询数据是一种事务处理过程中数据展示情况，但是在实际的数据库中是没有发送数据的变化的。只有事务提交之后才会更改

持久性
	事务一定执行成功，对于数据库的操作影响是持久性的。

13.权限管理

13.1 创建用户

-- 用户名 saolei 密码 123456
create user 'saolei' identified by '123456';

13.2 授权用户

-- 授权操作所有nzgp2001库权限
grant all on nzgp2001.* to 'saolei';

13.3 撤销授权

-- 撤销saolei用户所有nzgp2001数据库权限
revoke all on nzgp2001.* from 'saolei';

13.4 删除用户

-- 删除用户
drop user 'saolei';

14. 视图

14.1 什么是视图

 	视图，是一个虚拟表，通过查询语句，从一个表或者多个表中查询得到的一个结果，可以作为一个视图使用，使用的效果和真实表一致。可以在视图中使用update，delete，insert操作，可以提供数据库的安全性，同时便于用户操作。

14.2 视图的特点

优点:
	1. 简单化，数据所见即所得
	2. 安全，用户有且只能修改操作当前可见数据
	3. 逻辑独立性，这里使用使用的一张虚拟表，所有操作并不会对数据库真实表结构导致影响。

缺点:
	1. 性能较差，不管是SQL操作还是针对数据存储的性能
	2. 修改不方便，复杂视图在操作中是无法修改的

14.3 创建视图和使用视图

14.3.1 创建视图

-- 语法
-- create view 视图名 as 查询数据结果[查询语句]
-- 创建视图
create view empInfo as
select employee_id, first_name, last_name, email, salary
from t_employees;

14.3.2 视图使用

-- empInfo不是一张真实存在的表，而是一个自定义查询结果的视图
select *
from empInfo
where employee_id = 150;


-- 利用视图中的结果进行排序操作
select *
from empInfo
order by salary desc;

-- 修改视图中的数据，原表数据会受到影响
update empInfo
set first_name = 'Jame',
    last_name  = 'Martin'
where employee_id = 100;

select *
from empInfo;

select *
from t_employees;

-- 视图原表数据发送修改，视图中数据也会修改
update t_employees
set first_name = 'Steven',
    last_name  = 'King'
where employee_id = 100;

select *
from empInfo;

14.3.3 修改视图

-- Table 'empInfo' already exists
-- 当前视图已经存在
-- [验证]
create view empInfo as
select employee_id, first_name, last_name, salary
from t_employees;

-- 修改当前视图的结构
alter view empinfo
as
select employee_id, first_name, last_name, salary
from t_employees;

14.3.4 删除视图

-- 删除不会影响原始数据
drop view empInfo;

删除用户

-- 删除用户
drop user 'saolei';

14. 视图

14.1 什么是视图

 	视图，是一个虚拟表，通过查询语句，从一个表或者多个表中查询得到的一个结果，可以作为一个视图使用，使用的效果和真实表一致。可以在视图中使用update，delete，insert操作，可以提供数据库的安全性，同时便于用户操作。

14.2 视图的特点

优点:
	1. 简单化，数据所见即所得
	2. 安全，用户有且只能修改操作当前可见数据
	3. 逻辑独立性，这里使用使用的一张虚拟表，所有操作并不会对数据库真实表结构导致影响。

缺点:
	1. 性能较差，不管是SQL操作还是针对数据存储的性能
	2. 修改不方便，复杂视图在操作中是无法修改的

14.3 创建视图和使用视图

14.3.1 创建视图

-- 语法
-- create view 视图名 as 查询数据结果[查询语句]
-- 创建视图
create view empInfo as
select employee_id, first_name, last_name, email, salary
from t_employees;

14.3.2 视图使用

-- empInfo不是一张真实存在的表，而是一个自定义查询结果的视图
select *
from empInfo
where employee_id = 150;


-- 利用视图中的结果进行排序操作
select *
from empInfo
order by salary desc;

-- 修改视图中的数据，原表数据会受到影响
update empInfo
set first_name = 'Jame',
    last_name  = 'Martin'
where employee_id = 100;

select *
from empInfo;

select *
from t_employees;

-- 视图原表数据发送修改，视图中数据也会修改
update t_employees
set first_name = 'Steven',
    last_name  = 'King'
where employee_id = 100;

select *
from empInfo;

14.3.3 修改视图

-- Table 'empInfo' already exists
-- 当前视图已经存在
-- [验证]
create view empInfo as
select employee_id, first_name, last_name, salary
from t_employees;

-- 修改当前视图的结构
alter view empinfo
as
select employee_id, first_name, last_name, salary
from t_employees;

14.3.4 删除视图

-- 删除不会影响原始数据
drop view empInfo;