【数据库系统原理】数据库设计、表关系、多表查询
数据库设计内容
- 设计有哪些表
- 设计表有哪些字段
- 设计表和表之间的关系
表关系
1.一对一
- 如：用户和用户详情
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
- 实现方式：在任意一方加入外键，关联另一方主键，并设置外键为唯一（UNIQUE）
2.一对多（多对一）
- 如：部门和员工
- 一个部门对应多个员工，一个员工对应一个部门
- 实现方式：从“多”的这个表创建一个外键指向“一”的表
3.多对多
- 如：商品和订单
- 一个商品对应多个订单，一个订单包含多个商品
- 实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
多表查询
select * from 表1, 表2 会根据笛卡尔积计算表1、表2中所有的组合情况；
因此重点在于如何消除无效数据？
连接查询
内连接
两个表的交集
隐式内连接（用得很多）
select * from table1, table2 where table1.xx = table2.xxx;
显式内连接
select * from table1 inner join table2 on table1.xx = table2.xxx;
select * from table1 join table2 on table1.xx = table2.xxx; inner 可以省略
外连接
左外连接 ：相当于查询A表的所有数据和交集部分数据；
select * from table1 left outer join 表2 on 条件;
outer 可以省略 select * from table1 left join 表2 on 条件;
右外连接 ：相当于查询B表的所有数据和交集部分数据；
select * from table1 right outer join 表2 on 条件;
outer 可以省略 select * from table1 right join 表2 on 条件;
子查询
概念： 查询中嵌套查询，称嵌套查询为子查询
根据查询结果不同，作用不同：
-
单行单列：作为条件值，使用 = != > <等进行条件判断
select 字段列表 from table1 where 字段名=（子查询） -
多行单列：作为条件值，使用 in 等关键字进行条件判断
select 字段列表 from table1 where 字段名 in（子查询） -
多行多列：作为虚拟表
select 字段列表 from （子查询）where 条件
这里的子查询后可以用as别名替代一下,方便写查询条件
分组查询
select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤]
where和having的区别：
- 执行时机不一样，where是分组之前进行限定，不满足where条件不参与分组，having是分组之后对结果进行过滤。
执行顺序： where > 聚合函数 > having
事务（transaction）
包含一组数据库操作命令
-
开启事务
BEGIN;或者START TRANSACTION; -
提交事务
COMMIT; -
回滚事务
ROLLBACK;
事务的四大特征(ACID)
- 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
- 一致性（Consistency）: 事务完成时，必须使所有的数据都保持一致状态
- 隔离性（Isolation）: 多个事务之间，操作的可见性
- 持久性（Durability）: 事务一旦提交或回滚，对数据库中数据的修改是永久的
MySQL中默认情况下事务自动提交
可以使用select @@autocommit;进行提交方式的查询
修改事务的提交方式：
set @@autocommit = 0 0为手动提交；1为自动提交
