数据库的设计与多表查询

---

前言

数据库设计的基本操作以及事务的四大特性

---

一、数据库设计

1.1 约束

id不重复、年龄不可能很大、数学不可能为负

• 非空、唯一：主键
• 检查（MYSQL不支持，但JAVA代码支持）
• 默认：规避null
• 外键：两个表

create table 员工表 （
		id INT PRIMARY KEY， # 员工id，主键
		name Varchar（50）NOT NULL UNIQUE，# 非空唯一
		date Date NOT NULL， #入职时间
		salary double（7,2 ）NOT NULL， 
		bouns double（7,2）DEFAULT 0 # 默认为0
	）；
		id INT PRIMARY KEY auto_increment, # 主键自增长，数字类型，且唯一约束

1.2 外键约束

让两个表建立物理连接，保证一致性和完整性

constraint fk_emp_dept foreign key (dep_id) references dept(id); 
#需先添加参考的数据，再添加自身
#删除时要先删除参考的数据

#建完表之后添加外键约束
alter table 表名 add constraint 外键名称 foreign key （外键字段） references 主表名称（主表列）；
#删除约束
alter table 表名 drop foreign key 外键名称；

1.3 设计数据库（设计表）

需求分析 - 设计 - 编码 - 测试 - 安装部署
根据业务需求，选用DBMS（ER图-逻辑建模 ），设计最优的数据存储模型。
表关系：一对一：用户 和 用户信息
一对多：部门 和 员工
多对多：订单 和 商品

1.4 表关系

在多的一方建立外键一的主键

可以在第三张表（中间表）添加其他的列（数量）

一对一：有些是经常使用到，提高查询性能
任意一方加入外键，关联另外一方主键，且外键约束唯一

二、多表查询&事务

多表查询：

消除无效的数据，emp.id = dept.id

2.1 内连接

隐式内连接

select t1.name, t1.gender, t2.dname
from emp t1, dept t2
where t1.dep_id = t2.did;

显式内连接

select t1.name, t1.gender, t2.dname
from emp t1 join dept t2
on t1.dep_id = t2.did;

2.2 外连接

左外连接：相当于查询A表所有数据和交集部分的数据，小白龙是只有A表才有的

select * 
from t1 left join t2
on t1.dep_id = t2.did;

右外连接：相当于查询B表所有数据和交集部分的数据，销售部是只有B表才有的

select * 
from t1 right join t2
on t1.dep_id = t2.did;

2.3 子查询

嵌套查询

三种子查询方式：

2.4 事务

事务是一组数据库操作命令，要么同时成功，要么同时失败。（类似于操作系统的原子性）

BEGIN ； # 开启事务，临时，万一出错了，当前用户查询金额发生改变，但其他用户查不会改变
COMMIT； # 提交事务
ROLLBACK； # 回滚事务，万一出错了，当前用户不会改变，其他用户也不会改变

四大特性

set @@autocommit=0；# 修改MYSQL事务默认自动提交为手动提交
commit； # 提交修改，其他用户也会看到