表与表之间的关系
可以分成三类
一对一关系：一般情况下，一对一关系基本不用，当发现两张表是一对一的关系，就合成一张表，例如：人和身份证号
一对多关系：在表关系中是最多的情况，也是最常见的，比如：部门和员工
多对多关系：从两个方向观察发现都是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 锁表 安全性最高，性能最低
由事务隔离级别引发并发事务操作的问题：脏读，不可重复读，幻读。
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
