今日内容
-
外键约束
-
数据库表之间的关系
-
三大范式
-
多表查询
-
事务
-
DCL
外键约束
-
在新表中添加外键约束语法： constraint 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
-
在已有表中添加外键约束：alter table 从表表名 add constraints 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
-
删除外键语法： alter table 从表表名 drop foreign 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, -- constraint 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名) CONSTRAINT stu_cour_id FOREIGN key(s_c_id) REFERENCES course(c_id) -- 给s_c_id 添加外键约束 ON UPDATE CASCADE ON DELETE CASCADE ) insert into student VALUE(1,'小孙',1),(2,'小王',2),(3,'小刘',4); insert into student VALUE(4,'小司马',1),(5,'小赵',1),(6,'小钱',1); -- 查询学生表中的记录 select * from student; -- 级联操作。 -- 更新级联 on update cascade 级联更新 只能是创建表的时候创建级联关系。当更新主表中的主键，从表中的外键字段会同步更新。 update course set c_id = 10 where c_id = 1; -- 删除级联 on delete cascade 级联删除 当删除主表中的主键时，从表中的含有该字段的记录值会同步删除。 delete from course where c_id = 10;
-
约束总结
| 约束名 | 关键字 | 描述 |
|---|---|---|
| 主键 | primary key | 唯一不为空 |
| 默认 | default | 插入数据，该字段没有赋值，系统会自动赋指定的默认值 |
| 非空 | not null | 该字段不能为null(空) |
| 唯一 | unique | 该字段的值在整个表中只能出现1次 |
| 外键 | foreign key | 从表中添加外键字段，关联主表中的主键字段 |
表与表之间的关系
可以分成三类：
-
一对一的关系：一般情况下，一对一的关系基本不用，当发现两张表是一对一的关系，合成一张表，例如：人和身份证号，学生和学生证号
-
一对多的关系：在表关系中是最多的情况，也是最常见的，比如：部门和员工
-
多对多：从两个方向观察发现都是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 -- 唯一约束，非空约束 ); -- 创建课程表 drop table course; 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 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): 在第一范式的基础上，非码属性必须完全依赖于码(在第一范式的基础上消除非主属性对码的部分函数依赖)
-
概念：
-
函数依赖：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 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;
-
-
总结
-
查询哪些表
-
确定表关联的条件
-
使用连接的方式
-
确定查询的字段信息 ，尽量少用*
-
-
-
外连接
-
左外连接： 使用 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 department on e_did = d_id right outer join salary on e_id = s_eid
-
-
-
子查询
-
什么是子查询？
-
概念：一个查询的结果是另一个查询的条件，形成查询嵌套，里面的查询称之为子查询，一定要出现小括号。
-
子查询有三种情况：
-
子查询结果可以是单行单列，只有一个字段，这一个只有一个值
-
也可以是多行单利，只有一个字段，这个字段有多个值
-
还可以是多行多列，有多个字段，多个字段分别有多个值。
-
操作：
-
第一种情况： 单行单列
-
语法：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万块
-- 创建账户表 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); -- 张三给李四转账1000块钱 -- 先让张三的钱减掉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;
-
-
-
事务的四大特性：
-
原子性：这个事务是一个整体，在这个整体中，是不可分割的，在事务中所有的sql语句要么完全执行成功，要么都失败。
-
一致性：事务在执行前和执行后数据库中的数据状态是一致的。转账：张三和李四转账前余额都是20000，转账后，如果成功结果：张三是10000，李四是30000，如果失败结果：张三是20000，李四也是20000。
-
隔离性：事务与事务之间是互不影响的，在多个事务并发执行的时候应该处于隔离的状态。
-
持久性：一旦事务执行成功，对数据库的影响是持久的。
-
-
事务的隔离级别：读未提交-->read uncommitted 读已提交--->read committed 可重复读 ----> repeatable read
串行化-----> serializable 锁表 安全性最高 性能最低
-
由事务隔离级别引发并发访问操作的问题：脏读、不可重复读、幻读。
-
-
JDBC操作：6大步骤：
代码实现
-- 约束操作
-- 主键约束 PRIMARY KEY
-- 创建一张用户表
/*
字段：id 账号 密码 姓名 手机号 性别 年龄
*/
CREATE table user (
id int PRIMARY key, -- 添加主键约束
account varchar(15), -- 账号
password VARCHAR(255), -- 密码
username VARCHAR(10), -- 姓名
phone VARCHAR(11), -- 手机号
gender VARCHAR(1), -- 性别
age int(3) -- 年龄
)
-- 主键约束 唯一不为空
INSERT INTO user values(1,'0001','123456','小孙','12312312312','男',10);
INSERT INTO user values(2,'0002','123456','小王','12312312312','女',20);
INSERT INTO user values(3,'0003','123456','小赵','12312312312','男',10);
INSERT INTO user values(4,'0004','123456','小流','12312312312','男',30);
INSERT INTO user values(5,'0005','123456','小张','12312312312','男',15);
INSERT INTO user values(6,'0006','123456','小李','12312312312','男',10);
INSERT INTO user values(7,'0007','123456','小丽','12312312312','女',18);
-- 查询
SELECT * FROM user;
-- 非法数据插入 唯一 不重复
INSERT INTO user values(7,'0008','123123','小花','12312312313','女',19);
-- 非法数据插入 null值插入
INSERT INTO user values(null,'0012','123123','小玉','12312312313','女',23);
-- 删除主键 sql语句
alter table user drop PRIMARY key;
-- 添加主键
alter table user add PRIMARY KEY(id);
-- alter table user auto_increment = 起始值;
ALTER table user auto_increment = 100;
-- 创建一张表 角色表
create table role (
id int PRIMARY key auto_increment, -- 主键约束
rolename VARCHAR(10) UNIQUE -- 角色 唯一约束
)
-- 插入数据
insert into role values(null,'CEO');
insert into role values(null,'manager');
insert into role values(null,'CTO');
-- 查询角色表
SELECT * from role;
insert into role values(null,null);
-- 创建一张部门表
create table department (
id int PRIMARY KEY auto_increment, -- id 主键自增
departmentname VARCHAR(10) not null -- 部门不能为空
)
insert into department values(null,'总裁办');
insert into department values(null,'财务部');
insert into department values(null,'人事部');
insert into department values(null,'研发部');
insert into department values(null,'销售部');
-- 查询该表数据
SELECT * from department;
insert into department values(null,null);
-- 创建一张表 employee 员工表
create table employee (
id int PRIMARY key auto_increment,
username VARCHAR(10) not null,
gender VARCHAR(1) not null DEFAULT '男',
age int
)
insert into employee(id,username,age) values(null,'小孙',20);
-- 查询员工表
SELECT * from employee;
insert into employee(id,username,gender,age) values(null,'小丽','女',20);
insert into employee(id,username,gender,age) values(null,'小丽',default,20);
-- 外键约束
-- 课程表
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,
-- constraint 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
CONSTRAINT stu_cour_id FOREIGN key(s_c_id) REFERENCES course(c_id) -- 给s_c_id 添加外键约束
)
-- 往两张表中植入数据
insert into course VALUES(null,'数学'),(null,'英语'),(null,'历史'),(null,'物理');
insert into student VALUE(1,'小孙',1),(2,'小王',2),(3,'小刘',4);
-- 查询两张表的数据
select * from course;
select * from student;
-- 插入非法数据
-- 插入不存在的课程
-- 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(5,'小司马',5);
-- 删除从表student中的外键约束
-- alter table 从表表名 drop foreign key 外键名称;
alter table student drop FOREIGN key stu_cour_id;
-- 在已有的表中添加外键约束
-- alter table 从表表名 add constraint 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
alter table student add CONSTRAINT stu_cour_id FOREIGN key(s_c_id) REFERENCES course(c_id);
-- 修改从表中数据 修改课程信息
update student set s_c_id = 5 where s_id = 2;
-- 删除主表中的数据
delete from course where c_id = 1;
-- 给从表student添加级联操作
create table student(
s_id int PRIMARY key ,
s_name VARCHAR(10) not null,
s_c_id int,
-- constraint 外键约束名称 foreign key(外键的字段名称) references 主表表名(主键字段名)
CONSTRAINT stu_cour_id FOREIGN key(s_c_id) REFERENCES course(c_id) -- 给s_c_id 添加外键约束
ON UPDATE CASCADE ON DELETE CASCADE
)
insert into student VALUE(1,'小孙',1),(2,'小王',2),(3,'小刘',4);
insert into student VALUE(4,'小司马',1),(5,'小赵',1),(6,'小钱',1);
-- 查询学生表中的记录
select * from student;
-- 级联操作。
-- 更新级联 on update cascade 级联更新 只能是创建表的时候创建级联关系。当更新主表中的主键，从表中的外键字段会同步更新。
update course set c_id = 10 where c_id = 1;
-- 删除级联 on delete cascade 级联删除 当删除主表中的主键时，从表中的含有该字段的记录值会同步删除。
delete from course where c_id = 10;
-- 创建学生表
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 '课程名称' -- 唯一 非空约束
);
-- 中间表
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)
)
-- 如果两张表是一对多关系 设计创建时，先创建主表，再创建从表
-- 内连接
-- 隐式内连接 省略inner join
-- 员工表和部门表进行内连接查询
select * from employee,department where e_did = d_id;
-- 显式内连接 使用关键字 inner join ON
SELECT * from employee inner join department on e_did = d_id;
-- 禁用 回表操作
select
e.username as 姓名 ,d.d_name as 部门名称
FROM
employee as e ,department as d
WHERE
e_did = d_id;
-- 外连接
-- 左外连接
select
d.*, e.username
from
department as d
left outer join
employee as e
on
e.e_did = d.d_id;
-- 查询员工表和部门表
select * from department;
SELECT * from employee;
-- 查询出小孙所在的部门名称
-- 查询出小孙所在的部门id
select e_did from employee where username = '小孙'; -- 1
-- 再查询部门id为1的部门名称
select d_name from department where d_id = 1;
-- 两条查询合并一条sql语句
-- 单行单列
SELECT
d_name
from
department
WHERE
d_id = (
SELECT
e_did
from
employee
WHERE
username = '小孙'
);
-- 使用内连接
select
e.id,e.username, d.d_name
from
department as d,employee as e
WHERE
d_id = e_did
AND
username = '小孙';
-- 年龄 查询员工表中年龄最大的员工信息
-- 员工中年龄最大值
select MAX(age) from employee; -- 30
-- 查询年龄为30的那位员工
select * from employee where age = 30;
-- 合成一条sql语句
SELECT
*
FROM
employee
WHERE
age = (
SELECT
MAX(age)
FROM
employee
);
-- 查询员工表中年龄小于平均数的都有哪些员工？
SELECT AVG(age) from employee; -- 24.2
SELECT * from employee WHERE age < 24.2;
-- 合成一条sql语句
SELECT
*
FROM
employee
WHERE
age < (
SELECT
avg(age)
FROM
employee
);
-- 查询员工表年龄大于平均数都来自哪些部门？部门名称
SELECT
DISTINCT e_did
FROM
employee
WHERE
age > (
SELECT
avg(age)
FROM
employee
); -- 2 3 4
select d_name from department WHERE d_id in(2,3,4);
-- 合成一条sql语句
select
d_name
from
department
WHERE
d_id
in (
SELECT
DISTINCT e_did
FROM
employee
WHERE
age > (
SELECT
avg(age)
FROM
employee
)
);
-- 查询研发部与总裁办所有的员工信息
-- select d_id from department WHERE d_name = '研发部' or d_name = '总裁办';
select d_id from department WHERE d_name in('研发部','总裁办'); -- 1 4
select * from employee where e_did in(1,4);
-- 合成一条sql语句
select * from employee where e_did in (
select d_id from department WHERE d_name in('研发部','总裁办')
)
-- 查询员工表中年龄大于平均数的员工所在部门id值小于3的部门信息
SELECT
*
FROM
employee
WHERE
age > (
SELECT
avg(age)
FROM
employee
);
-- 查询字结果值部门id小于3
select d.* from department as d,(SELECT
*
FROM
employee
WHERE
age > (
SELECT
avg(age)
FROM
employee
)) as t1
where
d.d_id = t1.e_did
and
t1.e_did < 3;
-- 转账业务
-- 张三 李四
-- 张三给李四转账10000
-- 创建账户表
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);
-- 张三给李四转账1000块钱
-- 先让张三的钱减掉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;
-- 添加事务操作
-- 张三给李四转账1000块钱
-- 开启事务
start TRANSACTION;
-- 先让张三的钱减掉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 = '李四';
-- 提交事务 如果事务操作程序无异常，应该进行事务提交，持久化更改数据库中的记录值。
COMMIT;
-- 当在事务操作中程序出现异常，我们应该事务回滚
ROLLBACK;
1535
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
