SQL小知识杂谈

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 级别字符串;
打赏
  • 版权声明: 本博客所有文章除特别声明外,均采用 Apache License 2.0 许可协议。转载请注明出处!

请我喝杯咖啡吧~

支付宝
微信