文章目录
1. 多表关系
1.1 多表关系分类与实现
(1)一对一
- 如:人和身份证
- 分析:一个人只有一张身份证,一张身份证也只能对应一个人。
- 实现方式:可以在任意一方添加唯一外键(unique)指向另一方的主键。
(2)一对多(多对一)
- 如:部门和员工
- 分析:一个部门有多个员工,一个员工只能对应一个部门。
- 实现方式:在多的一方建立外键,指向一的一方的主键。
(3)多对多
- 如:学生和课程
- 分析:一个学生可以选择多门课程,一门课程也可以被很多学生选择。
- 实现方式:需要借助第三张中间表,中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键。
1.2 数据库设计的范式
(1)概念:设计数据库时,需要遵循的一些规范。要遵循后面的范式要求,必须先遵循前面的所有范式要求。
(2)分类
- 普通表(不是其中一类,仅拿出来做对比)
- 第一范式(INF)
定义:每一列都是不可分割的原子数据项。
分析:在普通表中,不满足第一范式要求的 每一列都是不可分割的原子数据项。如:系 可以分为 系名和系主任两列原子数据项。
- 第二范式(2NF)
定义:在1NF基础上,非码属性必须完全依赖于码。(在1NF基础上,消除非主属性对主码的部分函数依赖。)
几个概念:
① 函数依赖: A–>B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A。
例如:学号–>姓名,即通过学号可以确定唯一的学生,此时姓名依赖于学号。
或者:(学号,课程名称)–>分数,即通过学号和课程名称这个属性组唯一确定分数,此时分数依赖于(学号,课程名称)属性组。
② 完全函数依赖: A–>B,如果A为属性组,则B属性值的确定需要依赖A属性组的所有属性值。
例如:(学号,课程名称)—>分数,即分数的确定需要依赖属性组中的学号和课程名称,单独用其中某个属性不能确定分数,此时就称分数 完全依赖 于(学号,课程名称)属性组。
③ 部分函数依赖: A–>B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中某些值即可。
例如:(学号,课程名称)—>姓名,即通过学号就可以确定唯一的姓名,不需要课程名称。这就称姓名 部分依赖 于(学号,课程名称)属性组。
④ 传递函数的依赖: A–>B,B–>C。如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称C 传递函数依赖于 A。
⑤ 码: 如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码。
例如:该表中码为属性组(学号,课程名称)。
⑥ 主属性: 码属性组中的所有属性。
⑦ 非主属性: 除了码属性组的属性。
分析:
满足1NF的表中存在非常严重的数据冗余(重复):如姓名、系名、系主任三列;
满足1NF的表中数据添加存在问题:如添加一个新开设的系和系主任时,数据不合法;
满足1NF的表中数据删除存在问题:如张无忌同学毕业,删除该同学数据,会将系的数据一起删除。
因此,在1NF的基础上,消除非主属性对主码的部分函数依赖。可以将满足1NF的表分为 选课表 和 学生表,也即消除非主属性对主码(学号,课程名称)的部分函数依赖。
- 第三范式(3NF)
- 定义:在2NF的基础上,任何非主属性不依赖于其他非主属性。(在2NF基础上消除函数传递依赖)
- 分析:
满足2NF的表中数据添加存在问题:如添加一个新开设的系和系主任时,数据不合法;
满足2NF的表中数据删除存在问题:如张无忌同学毕业,删除该同学数据,会将系的数据一起删除。
因此,在2NF的基础上,消除函数传递依赖。可以将满足2NF的学生表分为 学生表 和 系表,也即消除非主属性对其他非主属性的函数传递依赖。此时,系主任在系表中只依赖于系名;系名在学生表中只依赖于学号。而非2NF中,系主任在学生表中依赖系名,系名在学生表中依赖于学号,而造成学生表中产生函数传递依赖。
2. 多表查询
(1)以下各查询方法的实例演示基于 部门表dept 和 员工表emp。其中员工表中的外键关联部门表的主键id。
# 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
# 向部门表插入数据
INSERT INTO dept(NAME)VALUE('开发部'),('市场部'),('财务部');
# 查询部门表
SELECT * FROM dept;
# 创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
gender VARCHAR(10), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 日期
dept_id INT,
FOREIGN KEY(dept_id) REFERENCES dept(id) -- 外键,关联部门表(部门表的主键)
);
# 查询员工表
SELECT * FROM emp;
# 向员工表插入数据
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('猪八戒','男',3600,'2010-12-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('唐僧','男',9000,'2008-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('沙悟净','男',8000,'2012-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('白骨精','女',5000,'2011-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('蜘蛛精','女',1200,'2019-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('小白龙','男',3000,'2018-02-24');
(2)以下分别是 部门表dept 和 员工表emp 查询结果。
2.1 笛卡尔积
(1)笛卡尔积也就是取两张表的所有组成情况。
# 笛卡尔积 emp,dept
SELECT * FROM emp,dept;
(2)emp 和 dept表笛卡尔积查询结果:
2.2 内连接查询
2.2.1 隐式内连接
(1)语法
select 字段列表 from 表名1,表名2 where 条件;
(2)示例
# 查询所有员工信息和对应部门信息
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
# 查询员工表的姓名,性别和部门表的部门名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;
# 查询员工表的姓名,性别和部门表的部门名称(用别名,且推荐此sql编写方式)
SELECT
t1.name,
t1.gender,
t2.name
FROM
emp t1,
dept t2
WHERE
t1.dept_id = t2.id;
(3)查询结果
2.2.2 显式内连接
(1)语法
select 字段列表 from 表名1 [inner] join 表名2 on 条件; #(inner可以省略)
(2)示例
# 显式内连接 查询员工表的姓名,性别和部门表的部门名称(起别名)
SELECT
t1.name,
t1.gender,
t2.name
FROM
emp t1
JOIN
dept t2
ON
t1.dept_id = t2.id;
(3)查询结果
2.3 外连接查询
2.3.1 左外连接
(1)左外连接查询的是 左表所有数据以及其交集部分(交集部分也就是满足条件的数据)。(而内连接本质上是只查询交集部分)
(2)语法
select 字段列表 from 表名1 left [outer] join 表2 on 条件; #outer可省略
(3)示例
# 左外连接
SELECT
t1.name,
t1.gender,
t2.name
FROM
emp t1
LEFT JOIN
dept t2
ON
t1.dept_id = t2.id;
(4)查询结果
2.3.2 右外连接
(1)右外连接查询的是 右表所有数据以及其交集部分(交集部分也就是满足条件的数据)。
(2)语法
select 字段列表 from 表名1 right [outer] join 表2 on 条件; #outer可省略
(3)示例
# 左外连接
# 右外连接
SELECT
t1.name,
t1.gender,
t2.name
FROM
emp t1
RIGHT JOIN
dept t2
ON
t1.dept_id = t2.id;
(4)查询结果
2.4 子查询
(1)概念
查询中的嵌套查询,称嵌套查询为子查询。
(2)子查询的不同情况
- 子查询的结果是单行单列的,则子查询可以作为条件,使用运算符去判断。如:> >= < <= = != <>
# 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
- 子查询的结果是多行单列的,则子查询可以作为条件使用运算符in来判断。
# 查询’财务部'和'市场部'所有的员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
- 子查询的结果是多行多列的,则子查询可以作为虚拟表参与查询
# 查询员工的入职日期是2011-1-1之后的员工信息和部门信息(此sql仅仅为了测试子查询结果是多行多列的情况)
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-1-1') t2 WHERE t1.id = t2.dept_id;
# 上面的查询用普通的内连接方式更简便
SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-1-1') t2 WHERE t1.id = t2.dept_id;