外键约束
? 在新表中添加外键约束语法:coustraint 外键约束名称 foreign key(外键的字段名称) references 主表的表名(主键字段名)
? 在已有表中添加主键外键约束:alter table 从表表名 add coustraints 外键约束名称 foreign key(外键的字段名称) references 主表的表名(主键字段名)
操作
-- 外键约束
-- 课程表
drop table course;
create table course (
c_id int primary key auto_increment,
c_name varchar(5)
);
-- 学生表 选课 外键约束添加在从表中 被约束的那一方
drop table student;
create table student(
s_id int primary key,
s_name varchar(10) not null,
s_c_id int,
-- 给s_c_id 添加外键约束
CONSTRAINT stu_cour_id FOREIGN KEY(s_c_id) REFERENCES course(c_id)
)
-- 往两张表中植入数据
insert into course VALUES(null,‘数学‘),(null,‘英语‘),(null,‘历史‘),(null,‘物理‘);
insert into student VALUES(1,‘小孙‘,1),(2,‘小王‘,2),(3,‘小刘‘,4);
-- 查询两张表的数据
select * from course;
select * from student;
-- 插入非法数据
-- 插入不存在的课程
-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`java31`.`student`, CONSTRAINT `stu_cour_id` FOREIGN KEY (`s_c_id`) REFERENCES `course` (`c_id`))
-- 因为从表中的s_c_id 被主表中的s_id约束
insert into student VALUES(4,‘小丽‘,5);
删除外键语法: alter table 从表名 drop froeign key 外键名称;
级联操作:
注意:
在从表中,修改关联主表中不存在的数据,是不合法的
在主表中,删除从表中已经存在的主表信息,是不合法的.直接删除主表(从表中有记录数据关联)会报删除失败
概念: 在修改或者删除主表的主键时,同时他会更新或者删除从表中的外键值,这种动作我们称之为级联操作
语法:
更新级联 on update cascade 级联更新 只能时创建表的时候创建级联关系.当更新主表中的主键,从表中的外键字段会同步更新
删除级联 on delete cascade 级联删除 当删除主表中的主键时,从表中的含有该字段的记录纸会同步删除
操作:
-- 给从表student添加级联操作
create table student(
s_id int primary key,
s_name varchar(10) not null,
s_c_id int,
-- 给s_c_id 添加外键约束
CONSTRAINT stu_cour_id FOREIGN KEY(s_c_id) REFERENCES course(c_id)
on update cascade on delete cascade
)
insert into student VALUES(1,‘小孙‘,1),(2,‘小王‘,2),(3,‘小刘‘,4);
insert into student VALUES(1,‘小司马‘,1),(2,‘小赵‘,1),(3,‘小钱‘,1);
-- 查询学生表中的记录
select * from student;
-- 级联更新
-- 更新级联
update course set c_id = 10 where c_id = 1;
-- 删除级联
delete from course where c_id = 10;
约束总结
约束名 关键字 描述
主键 primary key 唯一不为空
默认 default 插入数据该字段没有赋值,系统会自动赋指定的默认值
非空 not null 该字段不能为null(空)
唯一约束 unique 该字段的值在整个表中只能出现1次
外键 foreign key 从表中添加外键,关联主表中的主键字段
表与表之间的关系
可以分成三类:
○ 一对一的关系: 一般情况下,一对一的关系基本不用,当发现两张表是一对一的关系,合成一张表.例如:人和身份证号,学生和学生证号
○ 一对多的关系:在表关系中最多的情况,也是最常见的,比如,部门和员工
○ 多对多的关系:从两个方向观察发现都是1-n的关系,比如:学生表和课程1表,企业和应聘者, 双选会
操作:
学生表,课程表,多对多关系
用户表,角色表 一对多关系
-- 创建学生表
use java31;
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-- 唯一约束,非空约束
);
-- 创建课程表
drop table course;
create table course(
c_id int primary key auto_increment comment ‘课程id‘, -- 主键约束
c_name VARCHAR(10) NOT NULL UNIQUE COMMENT ‘课程名称‘ -- 非空唯一约束
)
drop table t_stu_cour;
-- 中间表
create table t_stu_cour(
s_id INT,
c_id INT,
CONSTRAINT stu_id FOREIGN KEY(s_id) REFERENCES student (s_id),
CONSTRAINT cour_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)
);
-- 创建角色表
drop table role;
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);在第一范式的基础上,非码属性必须完全依赖于码(在第一范式的基础上消除非主属性对于码的部分函数依赖)??
§ 概念:
1.函数依赖:A-->B 如果通过A属性(属性组)的值,可以确定唯一的B属性值,可以称B依赖于A
2.完全函数依赖:A-B 如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值
3.部分函数依赖 A-B 如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中的某一个或某一些属性即可
4.传递函数依赖 A-B B-C 如果通过A属性(属性组)的值,可以唯一确定B属性的值,再通过B属性的值,可以唯一确定C属性的值,
可以称C传递函数依赖于A
5.码:如果在一张表中,一个属性或者属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码??????
□ 主属性:码属性组中的所有属性
□ 非主属性:除主属性之外的其他属性
○ 第三范式(3NF):在第二范式的基础上,任何飞猪属性不依赖与其他的非主属性(在2NF基础上,消除传递函数依赖)
○ 三大范式总结
范式 特征
1NF 表中每一列具有原子性,表中的每一列不可分割
2NF 消除部分函数依赖,一张表只做一件事
3NF 消除传递函数依赖,表中的每一列都直接依赖于码(主键),不需要其他的字段(列)间接依赖于主键
多表连接查询
? 分类:内连接(显式内连接,隐式内连接),外连接(左外连接,右外连接)
? 笛卡尔积现象:
○ 左表中的每条记录和右表中的每条记录全关联组合,这种效果就称为笛卡尔积现象
? 消除笛卡尔积现象:
○ 添加条件过滤 ,使用where条件语句,达到过滤掉无效的数据.
? 内连接:inner join
○ 隐式内连接: 省略掉内连接关键字 inner join
§ 语法:select 字段列表 from 表民1,表名2,… where 条件语句
§ 操作:select * from department,employee where d_id = e_did;
○ 显式内连接:使用内连接关键字 inner join … on语句 inner可以省略
§ 语法: select 字段列表 from 表名1 [inner] join 表名2 on 条件语句
§ 操作:select * from department inner join employ on d_id = e_did;
○ 总结
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;
○ 右外连接:使用right [outer] join … on 条件语句 outer关键字可以省略
§ 语法:select 字段列表 from 右表(主表) right [outer] join 左表(从表/辅表) on 条件语句.
§ 注意事项:用右表中的记录数据去匹配左表中的记录数据,如果符合条件的则显示,不显示的数据一律显示为null.保证右表中的数据全部显示
§ select * from employ right outer join departement on e_did = d_id right outer join salary on e_id = s_eid
子查询:
? 什么是子查询?
? 概念:一个查询的结果是另一个查询的条件,形成查询嵌套,里面查询称之为子查询,一定要出现小括号.
? 子查询有三种情况:
○ 子查询的结果可以是单行单列,只有一个字段,这一个字段只有一个值
○ 也可以是多行单列,只有1个字段,这个字段有多个值
○ 还可以是多行多列,有多个字段,多个字段分别有多个值
○ 操作:
§ 第一种情况;单行单列
□ 语法: 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 -- 30
)-- 2 3 4
);
§ 第三种情况:多行多列,一般情况下我们可以作为一张虚拟表,进行关联二次查询,一般需要给这个虚拟表起一个别名来实现
□ 语法:select 查询字段列表 from 表名 ,(子查询) as 新表名 where 条件语句;
□ 特征:多行多列不能再使用in运算符或者是比较运算符,而是需要进行多表关联,给查询出来的多行多列起别名.
? 子查询总结:
○ 单行单列:只有一个值,在where后面可以使用比较运算符,作为条件
○ 多行单列:是一个集合值或者数组值,在where后面使用的是in运算符,作为条件
○ 多行多列:大多数多列结果值是放在from后面的,作为多表关联的.可以进行二次条件过滤
事务:
? 什么是事务:
一个业务操作中,这个操作,要么完全执行成功,要么被撤销掉.这个业务操作是一个整体操作,在这个整体中所有的sql语句要么全部执行成功
要么被回滚(业务执行失败).
? 操作:张三给李四转账10000,张三的账户剪掉1万块 利斯的账户增加1万块
-- 创建账户表account表()
create table account(
id int primary key auto_increment,-- 主键id
username VARCHAR(10) not null,-- 账户
balance double -- 账户余额
)
-- 插入两条数据
insert into account values(null,‘张三‘,20000),(null,‘李四‘,20000);
-- 张三给李四转账10000块钱
-- 先让张三的钱-10000
update account set balance = balance - 10000 where username = ‘张三‘;
-- 添加一条语句
update account set balance = balance - 10000 username = ‘张三‘;
-- 再让李四的钱+10000
update account set balance = balance + 10000 where username = ‘李四‘;
-- 还原数据
update account set balance = 20000;
-- 查询账户表
select * from account;
? 手动操作事务:
○ 三个动作:
§ 开启事务:start transaction;
§ 提交事务:commit;
§ 回滚事务:rollback;
? 事务的四大特性:
1.原子性:这个事务是一个整体,在这个整体中,是不可分割的,在事务中所有的sql语句要么完全执行成功,要么都失败
2.一致性:事务在执行前和执行后数据库中的数据状态是一致的.转账:张三和李四转帐前余额都是20000,转账后,如果成功:张三是10000,李四是30000
如果失败:张三是20000,李四也是20000.
3.隔离性:事务与事务之间是互不影响的,在多个事务并发执行的时候应该处于隔离状态.
4.持久性:一旦事务执行成功,对数据库的影响是持久的.
? 事物的隔离级别:
1.读未提交--read unconmmitted
2.读已提交--read committed
3.可重复读--repeatable read
4.串行化--serializable锁表(安全性最高,性能最低)
由事务隔离级别引发并发访问操作的问题:脏读,不可重复读,幻读,