SQL的一些小知识

多表关系的实现

1. 一对多（多对一）

   • 实现方式：在多的一方建立外键，指向一的一方的主键。

2. 多对多

   • 实现方式：需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

   • 联合主键：中间表的主键可以是指向另两张表主键的两个外键的联合。例：PRIMARY KEY(rid,uid)；*

3. 一对一

   • 实现方式：可以在任意一方添加唯一外键指向另一方的主键（一对一关系不常用，如果是一对一，为何不把两张表拼在一起呢）。

数据库设计范式

范式就是设计数据库时需要遵循的一些规范。

范式有六种范式：第一范式（1NF），第二范式（2NF），第三范式（3NF），巴斯-科德范式（BCNF），第四范式（4NF），第五范式（5NF，又称完美范式）。

各种范式呈递次规范，越高的范式，数据库冗余越小。一般设计数据库时满足前三范式就可以了。

第一范式（1NF）

每一列都是不可分割的原子数据项。

第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。

就是消除部分依赖。

• 几个概念

  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基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

就是消除传递依赖。

多表查询

1.内连接查询

1. 隐式内连接：使用WHERE条件消除无用数据

   • 例：SELECT * FROM emp,dept WHERE emp.id=dept.id;

2. 显式内连接：

   • 语法
     • SELECT 字段列表 FROM 表名1 [INNER] JOIN 表名2 ON 条件列表；

3. 内连接查询：

   1. 从哪些表中查数据。
   2. 条件是什么。
   3. 查询哪些字段。

2.外连接查询

1. 左外连接：

   • 语法：SELECT 字段名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件列表；

   • 查询的是左表（表名1的表）所有数据以及其符合查询条件的交集部分。

2. 右外连接：

   • 语法：SELECT 字段名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件列表；

   • 查询的是右表（表名2的表）所有数据以及其符合查询条件的交集部分。

3.子查询

就是查询中嵌套查询，被嵌套的查询称为子查询。
例：

1. SELECT MAX(salary) FROM emp;
2. SELECT * FROM emp WHERE salary = 9000;

可以替换为子查询
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

• 子查询的不同情况

  1. 子查询结果是单行单列：

     • 子查询可以作为条件，使用运算符去判断。
     • SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

  2. 子查询结果是多行单列：

     • 子查询可以作为条件，使用运算符IN去判断。
     • SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

  3. 子查询结果是多行多列：

     • 子查询可以作为一张虚拟表参与查询。
     • SELECT * FROM dept t1, (SELECT * FROM emp WHERE join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

事务

1. 基本介绍

   1. 概念：

      • 如果一个包含多个步骤的业务操作，被事务处理，那么这些操作要么同时成功，要么同时失败。

   2. 操作：

      1. 开启事务：START TRANSACTION；
      2. 回滚：ROLLBACK；
      3. 提交：COMMIT；

   3. MySQL数据库中事务默认自动提交

      • 事务提交的两种方式：

        • 自动提交：
          • MySQL就是自动提交的。
          • 一条DML语句会自动提交一次事务。
        • 手动提交：
          • Oracle默认是手动提交的。
          • 需要先开启事务，再提交。

      • 修改事务的默认提交方式：

        • 查看事务的默认提交方式：SELECT @@AUTOCOMMIT；
        • 修改默认提交方式：SET @@AUTOCOMMIT = 0；

2. 事务的四大特征

   1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
   2. 持久性：当事务提交或者回滚后，数据库将会持保存数据。
   3. 隔离性：多个事务之间，相互独立。
   4. 一致性：事务操作前后数据总量不变。

3. 事务的隔离级别

   • 概念：多个事务之间，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

   • 存在问题：

     1. 脏读：一个事务读取到另一个事务中没有提交的数据。
     2. 不可重复读（虚读）：在同一个事务中两次读取到的数据不一样。
     3. 幻读： 一个事务操作(DML)数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

   • 隔离级别

     1. read uncommited：读未提交
        • 产生的问题：脏读，不可重复读，幻读。
     2. read commited：读已提交（Oracle）
        • 产生的问题：不可重复读，幻读。
     3. repeatable read：可重复读 （MySQL）
        • 产生的问题：幻读。
     4. serializable：串行化
        • 可以解决所有 的问题。
     • 注意：隔离级别从小到大安全性越来越高，但是效率越来越低。
     • 数据库查询隔离级别：
       • SELECT @@TX_ISOLATION；
     • 数据库设置隔离级别：
       • SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串；