mysql 多表，外键和数据库设计

发表评论
207 views

创建外键约束

mysql 创建表的外键输入举例

/*创建department,employee ,department 一对多 employee */
/*创建department*/
 create table department(
     id bigint primary key auto_increment,
     dep_name varchar(200),
     dep_location varchar(200)
 );

/*创建employee*/
create table employee(
    id bigint primary key auto_increment,
    ename varchar(10),
    age int(3),
    dept_id bigint,
    constraint emp_dept_pk foreign key (dept_id) references department(id)
)

是否要使用外键约束

使用外键约束可以保证数据的完整性和一致性，另外使用外键约束还可以自动执行关联表之间的操作，类似级联更新和级联删除，另外使用 外键约束可以清晰定义表之间的关系，更易于理解和维护。在银行，工业领域需要 ## 外键约束带来问题 1. 性能问题 每次插入时一定会查询外表 2. 死锁 外键查询依赖于外表，表名innodb需要在父级表中检验相应的值同时也会锁定父级表的数据行，用来保证事务完成前不会删除，可能导致死锁， 3. 分库分表困难 4. 开发测试效率降低

删除外键约束

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

删除 employee 外键表

alter table employee drop foreign key emp_dept_fk;

创建表之后添加外键

alter table 从表 add constraint emp_dept_fk foreign key(dept_id) references department(id) 简写 alter table employee add foreign key(dept_id) references department(id)

外键约束的注意事项

  1. 从表的外键类型和主表的类型必须一致
  2. 添加数据时，应该添加主表的数据
  3. 删除数据时候，先删除从表中的数据

级联删除

删除主表中的数据同时，可以删除与之相关的从表的数据 on delete cascade

    create table employee (
        eid bigint primary key auto_increment,
        ename varchar(20),
        age int,
        dept_id bigint,
        constraint emp_dept_pk foreign key(dept_id) references department(id)
        on delete cascade
    );

多对多，演员与角色

    create table actor(
    id int primary key auto_increment,name varchar(20));
    create table role(id int primary key auto_increment, name varchar(20));
    create table actor_role(
    id int primary key auto_increment, aid int ,rid int);
    alter table actor_role add foreign key(aid) references actor(id);
    alter table actor_role add foreign key(rid) references role(id);

多表查询， 交叉连接查询的结果会产生 笛卡尔积，是不能够使用的。

select * from 左表，右表 where 条件， 1. 内连接查询 select * from 左表 inner join右表 on 左表.字段 = 右表.字段 where 条件 2. 外连接查询 左外连接 语法格式 关键字 left [outer] join select 字段名 from 左表 left join 右表 on 连接条件 左外连接特点 以左表为基准 匹配右表中的数据 如果匹配上就显示，如果匹配不上，左表中的数据正常显示，右表数据显示null select * from category c left join products p on c.cid = p.category_id; 右外连接 语法格式 关键字 right [outer] join select 字段名 from 左表 right join 右表 on 条件 右连接的特点： 以右表为基准匹配左表中的数据，如果匹配上就显示如果匹配不上 右表中的数据就正常显示，左表显示null

  1. 子查询 查询价格最高的商品信息，1. 查询出最高的价格，2.根据价格查询商品信息 子查询分类 where 型子查询，将子查询的结果作为父查询 ，比较条件实用 from 型子查询 将子查询的查询结果作为一张表使用 exists 型子查询，查询结果是单列多行的情况，可以将子查询的结果作为父查询in函数中的条件实用

数据库 三范式

作用 创建 冗余较小， 结构合理的数据库，空间最少原则 范式 就是设计数据库的要求 ### 第一范式 满足最低要求的范式，列具有原子性，列设计要做到不可以拆分 ### 第二范式 在满足第一范式的基础上，进一步满足更多范式，一张表只能描述一件事情 ### 第三范式 在满足第二范式基础上，表中信息如果能够被推到出来，就不要涉及一个字段单独来记录

反三范式

指的是通过增加冗余字段或者重复数据，提高数据库的读性能 浪费存储空间 节省查询时间 冗余字段，某一个字段属于一张表，但是又在多张表中都有出现。减少join操作，数据库执行更快