day21 多表设计、多表查询、事务、索引

最新推荐文章于 2024-07-20 20:04:43 发布

岁月磨少年志,时光凉我少年心

最新推荐文章于 2024-07-20 20:04:43 发布

阅读量64

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/qq_53453104/article/details/132793047

版权

一、多表设计

概述：

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：一对多、一对一、多对多

在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的，所以我们需要使用约束：外键，在字表中设定一个自字段为外键和父表建立起联系

创建外键的方式：①在创建表的时候添加②在创建完表之后图形化界面修改表

一对多

一对多关系实现：在数据库表中多的一方，添加字段，来关联一的一方的主键

一对一

关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

可以理解为把一张数据较多的表，分成几个表

多对多

关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

二、多表查询

内连接

隐式内连接：select 字段列表 from 表1 , 表2 where 条件 ... ;

显式内连接：select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;

外连接

左外连接：select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;

右外连接：select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;

子查询

介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

形式：select * from t1 where column1 = ( select column1 from t2 … );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

标量子查询：

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= <> > >= < <=

-- ========================= 子查询 ================================
-- 标量子查询
-- A.查询 "教研部" 的所有员工信息
-- 先查看教研部所属id
select id
from tb_dept
where name = '教研部';
-- 再根据教研部ID去到员工表找外键[dept_id]
select *
from tb_emp
where dept_id = 2;

select *
from tb_emp
where dept_id = (select id from tb_dept where name = '教研部');

-- B.查询在 "方东白" 入职之后的员工信息
-- 先找出方东白入职日期
select entrydate
from tb_emp
where name = '方东白';
-- 根据入职日期去 emp 找对应的员工信息
select *
from tb_emp
where entrydate >= (select entrydate from tb_emp where name = '方东白');
select *
from tb_emp
where entrydate >= '2012-11-01';

列子查询：

子查询返回的结果是一列（可以是多行）

常用的操作符：in 、not in等

行子查询：

子查询返回的结果是一行（可以是多列）。

常用的操作符：= 、<> 、in 、not in

-- 列子查询  只有一列，有多行数据
-- A.查询 "教研部" 和 "咨询部" 的所有员工信息
-- 我们应该先要查询出 教研部 和 咨询部 所属id
select id
from tb_dept
where name in ('教研部', '咨询部');

select *
from tb_emp
where dept_id in (select id from tb_dept where name in ('教研部', '咨询部'));
select *
from tb_emp
where dept_id in (2, 3);

表子查询：

子查询返回的结果是多行多列，常作为临时表

常用的操作符：in

-- 表子查询  多行多列
-- A.查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- 先根据日期去找员工信息
select *
from tb_emp
where entrydate > '2006-01-01';
-- 再根据员工信息去找部门信息

--   select * from 表 inner join 表 on 外键 = 主键;
select *
from (select * from tb_emp where entrydate > '2006-01-01') temp
         inner join tb_dept on temp.dept_id = tb_dept.id;