MySQL数据库第一阶段学习笔记任务三

任务三：Mysql多表、外键和数据库设计

多表的概述

多表简述

创建一个表

添加数据

设计为两张表

一张部门表

一张员工表

添加数据

添加部门数据

两张表的关系

主从关系

插入一条不存在部门的数据

虽然可以插入成功，但基本不这样操作

创建外键约束

外键约束概念

外键约束的作用

主从表介绍

添加外键约束的语法格式

创建表时去添加外键

create table 表名（

字段...

【constraint】【外键约束名】 foreign key （外键字段名） references 主表（主键字段）

）；

创建员工表 添加外键

create table 表名（

eid int primary key auto_incremen， -- 主建自增

ename varchar（20），

age int，

dept_id int -- 外键字段 指向了主表的主键

-- 添加外键约束

constraint emp_dept_fk foreign key (depe_id) references 主表（主键字段）

）；

添加数据

插入不含部门信息的错误数据

操作失败

插入失败，外键约束的作用就体现在此，保证了数据的完整性和一致性

查看主表和从表之间的关系

删除外键约束和注意事项

删除外键约束

语法格式

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

删除从表中的外键

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

添加外键约束方式二

创建表后再进行添加外键

语法格式

alter table 从表 add 【constraint】 外键约束名 foreign key （外键字段名） references 主表 （主键字段）

简写

外键约束的注意事项

级联删除

级联删除介绍

关键字：on delete cascade

创建表添加级联操作

添加数据

删除主表中的部门信息

对应编号为2的员工信息也随之删除

多表关系介绍

多表关系介绍

一对多关系介绍

一对多关系

建表原则

在多的一方创建外键指向一的一方的主键

多对多关系介绍

建表原则

需要创建第三张表作为中间表，中间表至少要有两个字段，分别作为外键去指向各自一方的主键

一对一关系介绍

建表原则

设计省市表

设计省市表，一对多关系

建表原则

创建主表，省表

创建从表，市表，创建外键，添加外键约束

查看关系

设计演员角色表

演员角色表设计

关系对应和建表原则

建立演员表和角色表

先创建中间表

通过DDL语言去对中间表添加外键约束

查看表关系

多表查询的介绍

语法规则

select 字列标段 from 表名列表

创建分类表和商品表

向表中添加数据

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

笛卡尔积图解

内连接查询

内连接查询的特点

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

通过从表的外键 = 主表的主键 这种方式去匹配

语法格式

select 字段名 from 左表，右表 where 连接条件

隐私内连接

查询所有商品信息和对应的分类信息

select * from 商品表，分类表 where 商品表的外键 = 分类表的主键

查询商品表的商品名称 和 价格 ，以及商品的分类信息

select

p,'pname',

p,'price'.

c.'cname'

from 商品表 p ， 分类表 c where 商品表的外键 = 分类表的主键

可以对表取别名的方式去简化，通过别名.字段即可调用字段里的信息进行查询。

 

查询格力空调是属于哪一分类下的商品

select

商品名字段，

分类名字段

from 商品表 p ， 分类表 c where 商品表的外键 = 分类表的主键 and 商品名字段 = ‘格力空调’

where条件语句再加上筛选条件，查询出格力空调

隐式内连接

语法格式

select 字段名... from 左表 【inner】 join 右表 on 连接条件

查询所有商品信息和对应的分类信息

select * from 商品表 p inner join 分类表 c on 条件

条件子句也就是商品表的外键 = 分类表的主键

查询鞋服分类下，价格大于500的商品名称和价格

查询思路：

1. 查询几张表，主表和从表分别是那几张表
2. 表之间的关系，表的连接条件，从表.外键 = 主表.主键
3. 查询所用到的字段信息，如名称和价格
4. 查询的条件 分类 = 鞋服，价格 > 500

select

p.pname,

p.price

from produce p

inner join category c on p.category.id = c.cid

where c.cname = '鞋服' and p.price >500;

外连接查询

外连接查询介绍

左外连接和右外连接

左外连接查询 关键字 ： left 【outer】 join

语法格式

select 字段名 form 左表 left join 右表 on 连接条件

左外连接查询

查询每个分类下的商品个数

select

分类名字段 c.cname.

分类下的商品个数信息 count(p.pid)

from

-- 表连接

分类表 c left join 商品表 拍 on 分类表主键 = 商品表外键

-- 分组

group by 分类 c.cname;

右外连接介绍

语法格式

select 字段名 from 左表 right join 右表 on 条件

右外链接查询

select * from 商品表 p right join 分类表 c on 商品表外键 = 分类表主键

各种连接方式的总结

子查询介绍

子查询介绍

查询价格最高的商品信息

第一步：查询出最高的价格

第二步：根据最高的价格去查询出商品信息

使用一条sql完成 子查询方式

总结

子查询作为查询条件

子查询的分类

将子查询作为查询条件

查询化妆品分类下的商品名称和价格

第一步：查询出化妆品分类的id

select cid from category where cname = ‘化妆品’；

第二步：根据化妆品id来查询对应的商品信息

select

p.pname,

p.price

from product p

where 商品表.外键 = 化妆品.id 也就是 将第一步子查询的结果作为条件 （ select cid from category where cname = ‘化妆品’）；

查询小于平均价格的商品信息

第一步:求出平均价格

select avg（price） from products；

第二步：获取小于平均价格的商品信息

select * from products where price < 第一步（select avg（price） from products）；

子查询结果为一张表

将子查询的结果作为一张虚拟的表来使用

跟内连接查询类似，将连接查询中的表替换为子查询语句即可

注意子查询虚拟出来表必须要起别名，否则无法访问。

子查询结果是单列多行

子查询的结果是单列多行，则作为父查询的in函数中的条件来使用

语法格式

select 字段名 from 表名 where 字段 in（子查询）；

查询价格小于两千的商品都来自于哪些分类

第一步：查询出小于两千的商品的 分类id

select category_id (商品表的外键) from products where price < 2000;

添加distinct进行降重处理

根据分类的id来查询分类的信息

select * from

分类表 category where cid in 子查询（select category_id (商品表的外键) from products where price < 2000;）

查询家电类和鞋服类的全部商品信息

第一步：查询家电类和鞋服类的分类id

select cid from category where cname in （‘家电类’，‘鞋服类’）；

根据分类id去查询商品信息

select

*

from products where category_id in （select cid from category where cname in （‘家电类’，‘鞋服类’））

子查询总结

1. 子查询的结果如果是一个字段（单列），那么就作为where后面的条件，单列多行就放在父查询where后的in函数里。
2. 如果是多个字段（多列），就作为虚拟表使用（需要起别名）。

数据库设计三范式

三范式介绍：指的就是数据库设计的一个原则

作用：创建结构合理的数据库

第一范式 1NF

列具有原子性，设计列要做到不可拆分

地址信息表中coutry字段中，中国上海可以拆分为 中国 和上海 违背了第一范式

第二范式 2NF

一张表只能描述一件事情

学生信息表中 描述了两件事，学生信息表和课程信息表

第三范式 3NF

消除传递依赖

表中的信息如果能够被推导出来，就不要设计一个字段来单独记录

比如总价可以根据数量和单价来推导出来，违背了第三范式

三范式就是空间最省原则

数据库反三范式

反三范式介绍

冗余字段介绍

冗余字段name的出现，可以提高订单表的查询效率，不用在多表连接查询