MySQL 基础学习二 多表的基本操作/事务

外键约束

用于保存数据的完整性, 一个表的的关键字在另一个表中作为主键使用.
主表是被引入外键的表, 从表就是引入外键的表

添加外键

create table dept(
    did int primary key auto_increment,
    dname varchar(20)
);
//建表时插入
CREATE TABLE employee {
    eid int primary key auto_increment,
    dno int,
    foreign key(dno) references dept(did)
}
//建表后插入
alter table employee add foreign key (dno) references dept(did);

表与表之间的关系

• 一对一: 一个字段对应一个字段, 通常这种关系都是放在同一个表内, 当数据库的字段过多, 查询效率就会下降, 这时候需分表, 分出一些不常用的字段到附表中, 就有了这种关系(一种情况)
• 唯一外键对应: 为一方的字段设置外键和unique, 关联另一个表的主键
• 主键对应: 主键与主键相互设置外键, 多用于数据库优化
• 一对多: 多的一方建立外键指向一的一方的主键, 很常见 员工部门都是这种例子
• 多对多: 有中间表包含外键关联其他表的主键, 也很常见, 像订单, 成绩单, 选课表

多表连接

交叉连接(cross join)

得到两个表的笛卡儿积

select * from table1 cross join table2
select * from table1, table2

内连接(inner join)

内连接需要一个关联条件(join-predicate), 从而查找每一条满足双方表的列的值关联条件的数据

//显示内连接, 在SQL中显示调用inner join 关键字
select * from table1 inner join table2 on join-predicate;
//隐式内连接 没有使用inner join 关键字 
select * from table1, table2 where join-predicate;
//inner关键字可省略

外连接(outer join)

和内连接相似, 但是他是以其中一个表为基准, 从另一个表中查找相应的数据组合显示, 若没有查找到的数据,
便不组合加以显示.
外连接分为,左外连接(left outer join) 和右外连接(right outer join)

//左外连接: left outer join
select * from table1 left outer join table2 on join-predicate
//右外连接: right outer join
select * from table1 right outer join table2 on join-predicate
//outer关键字可以省略

子查询

就是查询查询的查询, 只能多练习, 最近也在 leetcode 上做 database 的练习, 到时候会做一个总结.

事务

事务时恢复和并发控制的基本单位

ACID特性

• 原子性(atomicity): 一个事务里的各个逻辑时密不可分的, 要么全部执行, 要么都不执行
• 一致性(consistency): 一个事务执行前后, 其数据总和是不变的, 个人觉得就是把 能量守恒定律替换成数据守恒定律
• 隔离性(isolation): 一个事务的执行不应该受到其他事务的干扰
• 持久性(durability): 当一个事务执行完毕, 它对数据的改变将是永久性的

事务基本流程

//启动事务
start transaction;
//提交事务
commit;
//回滚(回到事务执行前的状态)
rollback;

事务带来的安全性问题

• 脏读: 一个事务读到了 其他事务还没提交的数据
• 不可重复度: 一个事务读到了 其他事务 提交的update数据
  意思就是 一个事务的执行过程中, 它的查询结果需要保持不变, 若这个事务执行了查询,结果为A, 但是此时另一个事务修改了数据并提交, 这个事务所执行的查询若变成了更新的数据B. 这就是问题了.
• 虚读/幻读: 一个事务都到了 其他事务已经提交的insert 数据
  和上一条差不多, 只是insert数据了, 但是这个问题是概率性事件, 不同于不可重复度.

这时候需要设置隔离等级来解决这些问题

隔离等级:

• read uncommitted: 所有上述安全性问题都可能发生
• read committed: 能避免脏读
• read repeatable: 能避免脏读和不可重复度
• serializablie: 可串行化, 当一个事务执行时, 其他事务都会停滞, 能避免所有上述问题

实际开发中, 都是使用 中间的两种隔离等级, mysql中默认是 read repeatable, oracle 默认是 read committed.

//设置隔离级别
set session transaction isolation level 隔离级别
//查看隔离级别
select @@tx_isolation;