表与表之间的关系
可以分成三类
一对一关系:一般情况下,一对一关系基本不用,当发现两张表是一对一的关系,就合成一张表,例如:人和身份证号
一对多关系:在表关系中是最多的情况,也是最常见的,比如:部门和员工
多对多关系:从两个方向观察发现都是1-n关系,比如:企业和应聘者
操作:
-- 创建学生表
DROP table student;
CREATE table student (
s_id int PRIMARY key auto_increment, -- 主键约束,自增
s_name VARCHAR(10) not null, -- 非空约束
s_num int UNIQUE not null -- 唯一,非空约束
);
-- 创建课程表
CREATE TABLE course (
c_id int PRIMARY KEY auto_increment COMMENT '课程id', -- 主键约束
c_name VARCHAR(10) not NULL unique COMMENT '课程名称' -- 唯一,非空约束
);
-- 创建中间表
create table t_stu_cour (
s_id int,
c_id int,
constraint stu_id foreign KEY(s_id) REFERENCES student(s_id),
constraint cout_id foreign KEY(c_id) REFERENCES course(c_id)
)
-- 创建用户表
DROP table `user`;
create table `user` (
u_id int PRIMARY KEY auto_increment COMMENT '用户表主键id', -- 主键约束
u_name VARCHAR(10) NOT null, -- 非空约束
u_phone VARCHAR(11) UNIQUE, -- 唯一约束
u_rid int, -- 需要添加的外键信息
CONSTRAINT user_rid FOREIGN KEY(u_rid) REFERENCES role(r_id)
);
-- 创建角色表
create table role (
r_id int PRIMARY KEY auto_increment COMMENT '角色表主键id', -- 主键约束
r_name VARCHAR(10) NOT NULL UNIQUE, -- 唯一非空约束
r_desc VARCHAR(255)
);
-- 如果两张表是一对多关系,设计时先创建主表,再创建从表
表与表之间关系总结
表之间关系 | 关系维护,创建 |
---|---|
一对一 | 合表,互为外键约束,表之间关系很少 |
一对多 | 在从表(多的那一方)的那一方创建外键,关联主表的主键字段,先创建主表,再创建从表 |
多对多 | 创建中间表,中间表分别添加外键约束关联各自对应的主键 |
数据库设计的范式
什么是范式
在设计数据库的时候,需要遵从的一些规范要求,根据这些规范要求设计出合理的数据库。这些规范被称作范式。这些范式针对的是关系型数据库。
目前关系型数据库的范式有六种:第一范式(1NF),第二范式(2NF),第三范式(3NF),第四范式(4NF),第五范式(完美范式)(5NF),巴斯-科德范式(BCNF)。
各种范式呈递次规范,越高的范式数据库的冗余性就越低。
前三种范式介绍:
第一范式(1NF):数据库中的每一列是不可分割的原子数据项。
第二范式(2NF):在第一范式的基础上,非码属性必须完全依赖于码(在第一范式的基础上消除非主属性对主属性的部分函数依赖)
概念:
- 函数依赖: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。
- 码:如果再一张表中,一个属性或属性组,被其他所有属性完全依赖,则称这个属性(属性组)为该表的码
- 主属性:码属性中的所有属性
- 非主属性:除主属性之外的其他属性
第三范式(3NF):在第二范式的基础上,任何非主属性不依赖于其他的非主属性。(在2NF基础上,消除传递函数依赖。)
三大范式总结:
范式 | 描述 |
---|---|
第一范式(1NF) | 表中的每一列具有原子性,表中的每一列不可分割 |
第二范式(2NF) | 消除部分函数依赖,一张表只做一件事 |
第三范式(3NF) | 消除传递函数依赖,表中的每一列都直接依赖于码(主键)不需要通过其他的字段(列)间接依赖于主键 |
多表连接查询
分类:
内连接(显示内连接,隐式内连接),外连接(左外连接,右外连接)
笛卡尔积现象:
左表中的每条记录和右表中的每条记录全关联组合,这种效果就称之为笛卡尔积现象
消除笛卡尔积现象:
添加条件过滤,使用where条件语句,达到过滤掉无效的数据。
内连接 inner join
-
隐式内连接:
省略掉内连接关键字 inner join
语法:
select 字段列表 from 表名1,表名2,...... where 条件语句
操作:select * from employee,department where e_did = d_id;
-
显示内连接:
使用内连接关键字 inner join … on 语句 inner 可以省略
语法:
select 字段列表 from 表名1 [inner] join 表名2 on 条件语句
操作:select * from employee inner join department on e_did = d_id;
总结:
1.查询那些表
2.确定表关联的条件
3.使用连接的方式
4.确定查询的字段信息,尽量少用*
外连接
-
左外连接
使用left [outer] join … on 条件语句 outer关键字可以省略
语法:
select 字段列表 from 左表(主表) left [outer] join 右表(子标/辅表) on 条件语句
注意事项:用左表中的记录数据去匹配右表中的记录数据,如果符合条件的则显示,不显示的数据一律显示为null。保证左表中的数据全部显示。
操作:select d.*,e.e_username from department as d left outer join employee as e on e.e_did = d.d_id;
-
右外连接
语法:
select 字段列表 from 左表(主表) right [outer] join 右表(子标/辅表) on 条件语句
注意事项:用右表中的记录去匹配左表中的记录数据,如果符合条件的则显示,不显示的数据一律显示为null,保证右表中的数据全部显示。
select * from employee right
子查询
什么是子查询
一个查询的结果是另一个查询的条件,形成查询嵌套,里面的查询称之为子查询。一定要出现小括号。
子查询有三种情况:
-
子查询的结果可以是单行单列,结果只有一个字段,这一个字段只有一个值
-
可以是多行单列,只有一个字段,这个字段有多个值
-
还可以是多行多列,有个多个字段,多个字段分别有多个值。
操作:
-
第一种情况:单行单列
语法:select 查询字段列表 from 表名 where 字段 比较运算符 (子查询);
特征:我们可以在where的后面使用比较运算符
-
第二种情况:多行单列
语法:select 查询字段列表 from 表名 where 字段 in (子查询);
特征:结果值是一个集合或者一个数组,父查询使用in运算符
操作:
select d_name FROM department WHERE d_id in( SELECT DISTINCT e_did FROM employee where age <= ( SELECT avg(age) FROM employee ) )
-
第三种情况:多行多列,一般情况下我们可以作为一张虚拟表,进行关联二次查询,一般需要给这个虚拟表起一个别名来实现。
语法:
select 查询字段列表 from 表名,(子查询) as 新表名 where 条件语句;
特征:多行多列不能再使用in运算符或者比较运算符,而是需要进行多表关联,给查询出来的多行多列起别名。
-
子查询总结:
- 单行单列:只有一个值,再where后面可以使用比较运算符,作为条件
- 多行单列:是一个集合或者数据值,再where后面使用的是in运算符,作为条件
- 多行多列:大多数多列结果值方法from后面,作为多表关联的,也可以进行二次条件查询。
事务
什么是事务:一个业务操作中,这个操作要么被完全执行成功,要么被撤销掉。这个业务操作是一个整体,在这个整体中所有的sql语句要么全部执行成功,要么被回滚 (业务执行失败)。
操作:张三给李四转账10000,张三的账户剪掉1万块,李四的账户增加1万块。
-- 转账业务
-- 张三 李四 张三给李四转账1万块
-- 创建账户表
create table account (
id int PRIMARY KEY auto_increment, -- 主键id
username VARCHAR(20) NOT NULL, -- 账户
balance DOUBLE -- 账户余额
);
-- 插入两条数据
insert into account VALUES(null,'张三',20000),(null,'李四',20000);
-- 张三给李四转账一万块钱
-- 先让张三的钱减掉一万
update account set balance = balance - 10000 where username = '张三';
-- 添加一条错误语句
update account set balance = balance - 10000 username = '张三';
-- 再让李四的钱增加一万
update account set balance = balance + 10000 where username = '李四';
-- 查询账户表
SELECT * FROM account;
-- 还原数据
UPDATE account set balance = 20000;
手动操作事务:
三个动作
开启事务:start transaction;
提交事务:commit;
回滚事务:rollback;
事务的四大特性
- 原子性:事务是一个整体,在这个整体中,是不可分割的,在事务中所有的sql语句要么完全的执行成功,要么都失败。
- 一致性:事务在执行前和执行后数据库中的数据状态时一致的。转账:张三和李四转账前余额都是20000,转账后,如果成功:张三是10000,李四是30000。如果失败:张三是20000,李四是20000。
- 隔离性:事务与事务之间是互不影响,在多个事务并发执行的时候应该处于隔离的状态。
- 持久性:一旦事务执行成功,对数据库的影响是持久的。
事务的隔离级别
读未提交–>read uncommitted
读已提交–>read committed
可重复读–>repeatable read
串行化–>serializable 锁表 安全性最高,性能最低
由事务隔离级别引发并发事务操作的问题:脏读,不可重复读,幻读。