MySQL学习笔记（三）

唯小南
132 阅读7分钟

多表

单表的缺点

冗余, 同一个字段中出现大量的重复数据

CREATE TABLE emp(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT ,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);

设计为两张表

-- 创建部门表
-- 一方,主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 创建员工表
-- 多方 ,从表
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT
);

多表设计上的问题

在从表里面输入不存在的主表的id ,数据依然可以添加 显然这是不合理的

使用外键约束,约束 外键id ,必须是 主表中存在的id

外键约束

外键指的是在 从表 中 与 主表 的主键对应的那个字段

使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性

多表关系中的主表和从表

  • 主表         主键id所在的表, 约束别人的表
  • 从表         外键所在的表多, 被约束的表 

创建外键约束

新建表时添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名);

已有表添加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES
主表(主 键字段名);

删除外键约束

alter table 从表 drop foreign key 外键约束名称

外键约束的注意事项

  • 从表外键类型必须与主表主键类型一致 否则创建失败
  • 添加数据时, 应该先添加主表中的数据
  • 删除数据时,应该先删除从表中的数据

多表关系设计

表与表之间的三种关系

  • 一对多关系      最常见的关系, 学生对班级,员工对部门
  • 多对多关系      学生与课程, 用户与角色
  • 一对一关系      使用较少,因为一对一关系可以合成为一张表

一对多关系

一对多关系（1:n） 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

CREATE TABLE department(
  did INT PRIMARY KEY,
  dept_name VARCHAR(20)
)

CREATE TABLE employee(
  eid INT PRIMARY KEY,
  ename VARCHAR(20),
  age INT,
  dept_id INT,
  FOREIGN KEY(dept_id) REFERENCES department(did)
)

多对多关系

多对多（m:n）需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

CREATE TABLE student(
  id INT PRIMARY KEY,
  sname VARCHAR(20),
  age INT
)

CREATE TABLE course(
  id INT PRIMARY KEY,
  cname VARCHAR(20)
)

CREATE TABLE sc(
  sid INT,
  cid INT,
  FOREIGN KEY(sid) REFERENCES student(id),
  FOREIGN KEY(cid) REFERENCES course(id)
)

一对一关系

在实际的开发中应用不多.因为一对一可以创建成一张表

多表查询

DQL: 查询多张表,获取到需要的数据

笛卡尔积

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

SELECT 字段名 FROM 表1, 表2;

多表查询的分类

内连接查询

通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示

从表的外键 = 主表的主键 方式去匹配

隐式内连接

from子句 后面直接写 多个表名 使用where指定连接条件的 这种连接方式是 隐式内连接

使用where条件过滤无用的数据

SELECT 字段名 FROM 左表, 右表 WHERE 连接条件
SELECT * FROM products,category WHERE category_id = cid

显式内连接

使用 inner join ...on 这种方式, 就是显式内连接

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
-- inner 可以省略
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid

外连接查询

**左外连接
**

左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略

  • 以左表为基准, 匹配右边表中的数据,如果匹配的上,就展示匹配到的数据

  • 如果匹配不到, 左表中的数据正常展示, 右边的展示为null

    SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件 SELECT * FROM category c LEFT JOIN products p ON c.cid = p.category_id

右外连接

右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略

  • 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据

  • 如果匹配不到，右表中的数据正常展示, 左边展示为null

    SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件 -- 右外连接查询 SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;

  • 内连接             inner join , 只获取两张表中 交集部分的数据

  • 左外连接          left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分

  • 右外连接          right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分

子查询

一条select 查询语句的结果, 作为另一条 select 语句的一部分

子查询必须放在小括号中；子查询一般作为父查询的查询条件使用

子查询常见分类

  • where型 子查询    将子查询的结果, 作为父查询的比较条件
  • from型 子查询      将子查询的结果, 作为 一张表,提供给父层查询使用
  • exists型 子查询    子查询的结果是单列多行, 类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果 

子查询的结果作为查询条件

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）
SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products)

子查询的结果作为一张表

当子查询作为一张表的时候，需要起别名，否则无法访问表中的字段

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件

子查询结果是单列多行

子查询的结果类似一个数组, 父层查询使用 IN 函数 ,包含子查询的结果

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）

子查询总结

  1. 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用
  2. 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名)

数据库设计

数据库三范式(空间最省)

三范式就是设计数据库的规则

  • 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据 库中这种规则就称为范式。范式是符合某一种设计要求的总结
  • 满足最低要求的范式是第一范式（1NF）在第一范式的基础上进一步满足更多规范要求的 称为第二范式（2NF）其余范式以此类推。一般说来，数据库只需满足第三范式(3NF）

第一范式 1NF

  • 原子性, 做到列不可拆分 第一范式是最基本的范式
  • 数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式

第二范式 2NF

  • 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关
  • 一张表只能描述一件事

第三范式 3NF

  • 消除传递依赖
  • 表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放

反三范式示例

冗余的字段，查一张表就可以

创建一个关系型数据库设计，我们有两种选择

  • 尽量遵循范式理论的规约，尽可能少的冗余字段
  • 合理的加入冗余字段，减少join，让数据库执行性能更高更快
avatar
文章
阅读
粉丝