mysql数据库学习笔记–高级和进阶

mysql数据库学习笔记–高级和进阶

数据的完整性

• 实体完整性（行完整性）
• 域完整性（列完整性）
• 引用完整性（关联表完整性）

主键约束：primary key
唯一约束：unique [key]
非空约束：not null
默认约束：default
自动增长：auto_increment
外键约束: foreign key

实体完整性

• 主键约束，数据唯一且不为空

  在创建表的时候指定，也可以创建完表后指定

• 唯一约束

  unique

• 自动增长列（标识列identity，序列sequence）

域完整性

限制数据正确，不与此列的其他单元格比较

数据类型，非空约束，默认值约束，check约束（mysql不支持）

引用完整性

当一张表名字改了，这个名字的另一张表也要改，很麻烦。因此可以用主键的序号来代替，但容易某个id在一张表有意义在另一张表无定义，即数据不完整

外键约束：FOREIGN KEY

多表查询

关系

• 一一

  唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外
  键设置为unique.
  主键对应：让一对一的双方的主键进行建立关系

• 多多

  多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一
  方的主键

• 一多/多一

  对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键.

外键会使得性能降低，因此在大数据量之下，不要使用外键。

多表查询之合并结果集

1. 合并结果集:UNION 、 UNION ALL

2. 连接查询
   2.1内连接 [INNER] JOIN ON
   2.2外连接 OUTER JOIN ON
   -左外连接 LEFT [OUTER] JOIN

   -右外连接 RIGHT [OUTER] JOIN

   -全外连接（MySQL不 支持）FULL JOIN
   2.3 自然连接 NATURAL JOIN

   ​

3.子查询

合并结果集

将两个查询结果合并在一块

union/union all

只有当查询结果的列数和列类型相同的时候才可以

连接查询

笛卡尔积

select * from t1,t2

两表间两两匹配得到结果

但这样子会得到大量的无意义结果。我们会用where进行筛选

如：

select * from t1,t2 where t1.num=t2.num

也可以使用内联 inner join

select * from t1 inner join t2 on t1.num=t2.num

数据库并发时可能出现问题

脏读：读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的 数据是脏数
据。

不可重复读：同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同一数据，事务 B 在事务A 多次读取的
过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读：重复查询的过程中，数据就发⽣了量的变化（insert， delete）

数据库四种隔离级别：

1. 未提交读(Read uncommitted)：会出现脏读，不可重复读，幻读

• 事务在读数据的时候并未对数据加锁。
• 事务在修改数据的时候只对数据增加行级共享锁。

1. 提交读(Read committed)：会出现不可重复读，幻读

• 事务对当前被读取的数据加 行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；
• 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。

1. 可重复读(Repeatable reads)：会出现幻读

• 事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加 行级共享锁，直到事务结束才释放；
• 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。

1. 可序列化(Serializable)：安全性很高，但并发性很差

• 事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放；
• 事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放。

共享锁：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
排他锁：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁（即其他事务也不能对数据A再加共享锁进行读操作）。获准排他锁的事务既能读数据，又能修改数据。