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;
-- 开启事务
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 ;