MySQL数据库——从入门到删库跑路(三)

外键约束

在新表中添加外键约束语法： constraint 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
在已有表中添加外键约束：alter table 从表表名 add constraints 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
删除外键语法： alter table 从表表名 drop foreign key 外键名称;

级联操作：

注意：

在从表中，修改关联主表中不存在的数据，是不合法的

在主表中，删除从表中已经存在的主表信息，是不合法的。直接删除主表(从表中有记录数据关联) 会包删除失败。
概念：在修改或者删除主表的主键时，同时它会更新或者删除从表中的外键值，这种动作我们称之为级联操作。
语法：
- 更新级联 on update cascade 级联更新只能是创建表的时候创建级联关系。当更新主表中的主键，从表中的外键字段会同步更新。
- 删除级联 on delete cascade 级联删除当删除主表中的主键时，从表中的含有该字段的记录值会同步删除。

操作：

-- 给从表student添加级联操作
create table student(
    s_id int PRIMARY key ,
    s_name VARCHAR(10) not null,
    s_c_id int,
-- constraint  外键约束名称  foreign key(外键的字段名称)  references 主表表名(主键字段名)
     CONSTRAINT stu_cour_id FOREIGN key(s_c_id) REFERENCES  course(c_id)                -- 给s_c_id 添加外键约束
     ON UPDATE CASCADE  ON DELETE CASCADE
)

insert into student VALUE(1,'小孙',1),(2,'小王',2),(3,'小刘',4);
insert into student VALUE(4,'小司马',1),(5,'小赵',1),(6,'小钱',1);

-- 查询学生表中的记录
select * from student;

-- 级联操作。
-- 更新级联  on update cascade   级联更新 只能是创建表的时候创建级联关系。当更新主表中的主键，从表中的外键字段会同步更新。
update course set c_id = 10 where c_id = 1;
-- 删除级联  on delete cascade   级联删除  当删除主表中的主键时，从表中的含有该字段的记录值会同步删除。

delete from course where c_id = 10;

约束总结

约束名	关键字	描述
主键	primary key	唯一不为空
默认	default	插入数据，该字段没有赋值，系统会自动赋指定的默认值
非空	not null	该字段不能为null(空)
唯一	unique	该字段的值在整个表中只能出现1次
外键	foreign key	从表中添加外键字段，关联主表中的主键字段

表与表之间的关系

可以分成三类：

一对一的关系：一般情况下，一对一的关系基本不用，当发现两张表是一对一的关系，合成一张表，例如：人和身份证号，学生和学生证号
一对多的关系：在表关系中是最多的情况，也是最常见的，比如：部门和员工
多对多：从两个方向观察发现都是1-n关系，比如：学生表和课程表，企业和应聘者双选会

操作：

学生表、课程表多对多关系用户表、角色表一对多关系

-- 创建学生表
drop table student;
create table student(
        s_id int PRIMARY key auto_increment, -- 主键约束
    s_name VARCHAR(10) not null, -- 非空约束
        s_num int unique not null -- 唯一约束，非空约束
);
-- 创建课程表
drop table course;
create table course (
    c_id int PRIMARY key auto_increment COMMENT '课程id', -- 主键约束  
     c_name VARCHAR(10) not null unique COMMENT '课程名称' -- 唯一 非空约束
);

-- 中间表
create table t_stu_cour (
    
     s_id int,
     c_id int,

     CONSTRAINT stu_id foreign key(s_id) REFERENCES student(s_id),
     CONSTRAINT cour_id foreign key(c_id) REFERENCES course(c_id)
);

-- 创建用户表
drop table user;
create table user (

  u_id int PRIMARY key auto_increment COMMENT '用户表主键id', -- 主键约束
  u_name VARCHAR(10) not null, -- 非空约束
  u_phone VARCHAR(11) unique, -- 唯一约束
  u_rid int, -- 需要添加的外键信息
  CONSTRAINT user_rid FOREIGN key(u_rid) REFERENCES role(r_id) 
);
-- 创建角色表
drop table role;
create table role (

   r_id int PRIMARY key auto_increment COMMENT '角色表主键id', -- 主键约束
   r_name VARCHAR(10) not null UNIQUE, -- 唯一约束 非空约束
   r_desc VARCHAR(255) 
)
-- 如果两张表是一对多关系 设计创建时，先创建主表，再创建从表

表与表之间关系总结

表之间关系	关系维护、创建
一对一	合表，互为外键约束，表之间关系很少
一对多	在从表(多的那一方)的那一方创建外键，关联主表的主键字段，先创建主表，再创建从表
多对多	创建中间表，中间表分别添加外键约束关联各自对应的的主键

数据库设计的范式

什么是范式？

在设计数据库的时候，需要遵从的规范要求，根据这些规范要求设计出合理的数据库。这些规范被称为范式。

这些范式针对的是关系型数据库。

目前关系型数据库的范式有六种：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、第四范式(4NF)、第五范式(完美范式)(5NF)、巴斯-科德范式。(BCNF)

各种范式呈递次规范，越高的范式数据库的冗余性就越低。
前三种范式介绍：
- 第一范式(1NF)：数据库中的每一列是不可分割的原子数据项。
- 第二范式(2NF): 在第一范式的基础上，非码属性必须完全依赖于码(在第一范式的基础上消除非主属性对码的部分函数依赖)
  - 概念：
    1. 函数依赖：A---->B 如果通过A属性(属性组)的值，可以确定唯一的B属性值，可以称B依赖于A
    2. 完全函数依赖 A---->B 如果A是一个属性组，则B属性值的确定需要依赖于A属性组中的所有属性值
    3. 部分函数依赖 A---->B 如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一个或某一些属性值即可。
    4. 传递函数依赖 A---->B B---->C 如果通过A属性(属性组)的值，可以唯一确定B属性的值，在通过B属性的值可以唯一确定C属性的值，可以称 C 传递依赖于A
    5. 码：如果在一张表中，一个属性或者属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码。
      - 主属性：码属性组中的所有属性
      - 非主属性：除主属性外的其他属性
- 第三范式(3NF)：在第二范式的基础上，任何非主属性不依赖与其他的非主属性(在2NF基础上，消除传递函数依赖)

三大范式总结

范式	特征
1NF	表中的每一列具有原子性，表中的每一列不可分割
2NF	消除部分函数依赖，一张表只做一件事
3NF	消除传递函数依赖，表中的每一列都直接依赖于码(主键)。不需要通过其他的字段(列)间接依赖于主键

多表连接查询

分类：内连接(显式内连接、隐式内连式)、外连接(左外连接、右外连接)
笛卡尔积现象：
- 左表中的每条记录和右表中的每条记录全关联组合，这种效果就称之为笛卡尔积现象。
消除笛卡尔积现象

添加条件过滤，使用where条件语句，达到过滤掉无效的数据。
内连接 inner join
- 隐式内连接：省略掉内连接关键字 inner join
  - 语法： select 字段列表 from 表名1,表名2,.... where 条件语句
  - 操作： select * from department ，employee where d_id = e_did;
- 显式内连接：使用内连接关键字 inner join .... on语句 inner 可以省略
  - 语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件语句
  - 操作：select * from department inner join employ on d_id = e_did;
- 总结
  1. 查询哪些表
  2. 确定表关联的条件
  3. 使用连接的方式
  4. 确定查询的字段信息，尽量少用*
外连接
- 左外连接：使用 left [outer] join ..... on 条件语句 outer关键字可以省略
  - 语法： select 字段列表 from 左表(主表) left [outer] join 右表(从表/辅表) on 条件语句。
  - 注意事项：用左表中的记录数据去匹配右表中的记录数据，如果符合条件的则显示，不显示的数据一律显示为null。保证左表中的数据全部显示。
  - 操作：select d.*, e. e_username from department as d left outer join employee as e on e.e_did = d.d_id;
- 右外连接使用 right [outer] join ..... on 条件语句 outer关键字可以省略
  - 语法： select 字段列表 from 右表(主表) right [outer] join 左表(从表/辅表) on 条件语句。
  - 注意事项：用右表中的记录数据去匹配左表中的记录数据，如果符合条件的则显示，不显示的数据一律显示为null。保证右表中的数据全部显示。
  - select * from employ right outer join department on e_did = d_id right outer join salary on e_id = s_eid
子查询
- 什么是子查询？
- 概念：一个查询的结果是另一个查询的条件，形成查询嵌套，里面的查询称之为子查询，一定要出现小括号。
- 子查询有三种情况：
  - 子查询结果可以是单行单列，只有一个字段，这一个只有一个值
  - 也可以是多行单利，只有一个字段，这个字段有多个值
  - 还可以是多行多列，有多个字段，多个字段分别有多个值。
  - 操作：
    - 第一种情况：单行单列
      1. 语法：select 查询字段列表 from 表名 where 字段比较运算符 (子查询)；
      2. 特征：我们可以在where的后面使用比较运算符 > < >= <= != <>
    - 第二种情况：多行单列
      1. 语法：select 查询字段列表 from 表名 where 字段 in (子查询)；
      2. 特征：结果值是一个集合或者一个数组，父查询使用in运算符
      3. 操作：
        
        select d_name from department WHERE d_id in ( SELECT DISTINCT e_did FROM employee WHERE age > ( SELECT avg(age) FROM employee ) )
    - 第三种情况：多行多列，一般情况下我们可以作为一张虚拟表，进行关联二次查询，一般需要给这个虚拟表起一个别名来实现。
      - 语法：select 查询字段列表 from 表名 ,(子查询) as 新表名 where 条件语句；
      - 特征：多行多列不能再使用in运算符或者比较运算符，而是需要进行多表关联，给查询出来的多行多列起别名。
- 子查询总结：
  - 单行单列：只有一个值，在where后面可以使用比较运算符，作为条件
  - 多行单列：是一个集合值或者数组值，在where后面使用的是in运算符，作为条件
  - 多行多列：大多数多列结果值是放在from后面的，作为多表关联的。可以进行二次条件过滤。

事务

什么是事务：一个业务操作中，这个操作要么被完全执行成功，要么被撤销掉。这个业务操作是一个整体，在这个整体中所有的sql语句要么全部执行成功，要么被回滚。(业务执行失败)。
操作：张三给李四转账10000，张三的账户减掉1万块李四的账户增加1万块

-- 创建账户表
create table account (
   
    id int PRIMARY key auto_increment, -- 主键id
    username VARCHAR(10) not null, -- 账户
    balance double -- 账户余额 
)

-- 插入两条数据
insert into account values(null,'张三',20000),(null,'李四',20000);
-- 张三给李四转账1000块钱
-- 先让张三的钱减掉10000
update account set balance = balance - 10000 where username = '张三';

-- 添加一条语句
update account set balance = balance - 10000  username = '张三';

-- 再让李四的钱增加10000
update account set balance = balance + 10000 where username = '李四';

-- 还原数据
update account set balance = 20000;

-- 查询账户表
SELECT * from account;

手动操作事务：

- 三个动作：
- 开启事务： start transaction;
- 提交事务: commit;
- 回滚事务: rollback;

事务的四大特性：

- 原子性：这个事务是一个整体，在这个整体中，是不可分割的，在事务中所有的sql语句要么完全执行成功，要么都失败。
- 一致性：事务在执行前和执行后数据库中的数据状态是一致的。转账：张三和李四转账前余额都是20000，转账后，如果成功结果：张三是10000，李四是30000，如果失败结果：张三是20000，李四也是20000。
- 隔离性：事务与事务之间是互不影响的，在多个事务并发执行的时候应该处于隔离的状态。
- 持久性：一旦事务执行成功，对数据库的影响是持久的。

事务的隔离级别：读未提交-->read uncommitted 读已提交--->read committed 可重复读 ----> repeatable read

串行化-----> serializable 锁表安全性最高性能最低

由事务隔离级别引发并发访问操作的问题：脏读、不可重复读、幻读。

备注：如果表的引擎是MyISAM，则该表无法不支持事务，最好将其改为InnoDB引擎。

posted @ 2021-01-07 21:07 寒江雨阅读(177) 评论(0) 编辑收藏举报

刷新页面返回顶部