02-Mysql（二）多表

最新推荐文章于 2024-09-12 01:15:37 发布

派派OvO

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量109

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43241782/article/details/130191208

版权

本文介绍了数据库设计中的三种表关系——一对一、一对多和多对多，以及如何通过外键约束实现这些关系。接着详细讲解了多表查询，包括交叉连接、内连接、左外连接、右外连接和子查询的使用。最后，提到了事务的概念、特性以及其在数据库操作中的重要性。

摘要由CSDN通过智能技术生成

一、多表关系

1. 概述

在数据库设计上，表关系分为三种：一对多、多对多、一对一
一对多：（场景）：班级和场景(一个班级对应多个学生，一个学生只能对应一个班级) 部门和员工。
多对多：（场景）：学生和课程（一个学生对应多个课程，一个课程对应多个学生）学生和老师
一对一：（场景）：人和身份证号（一个人对应一个身份证号）

2. 一对多

班级和学生：

create database db3;-- 建库
use db3; -- 切换库;

-- 创建班级表
create table class(
    id int primary key,
    name varchar(30)
);

-- 创建学生表
create table student(
    id int primary key,
    name varchar(30),
    class_id int
);

-- 插入数据
insert into class values (1,'JAVAEE166'),(2,'JAVAEE167');
insert into student values (1,'张三',1),(2,'李四',2),(3,'王五',2);

3. 外键约束

-- 创建表的时候添加
	create table 表名(
		列名  列类型,
                [constraint 约束名: 外键列_fk]  foreign key (列名)  references  主表(主键)
            )
-- 创建表之后单独添加
	alter table 表名 add [constraint 约束名]  foreign key (列名)  references  主表(主键)
-- ===========================
-- 添加外键约束
alter table student add constraint class_id_fk foreign key(class_id) references class(id);

(物理外键 VS 逻辑外键)
物理外键：使用foreign key定义外键关联另外一张表
问题：1.影响增删改的效率（需要检查外键关系）
2.仅用于单节点数据库，不适用与分布式、集群场景
3.容易引发数据库死锁问题，消耗性能
逻辑外键：在业务逻辑中解决外键关联

4. 多对多

create database db4;
use db4;

-- 创建学生表
create table student(
        id int primary key,
        name varchar(30)
);

-- 创建课程表
create table course(
       id int primary key,
       name varchar(30)
);

-- 建立中间表
create  table student_course(
        id int primary key auto_increment,
        student_id int,
        course_id int,
        constraint student_id_fk foreign key (student_id) references student(id), -- 外键约束
        constraint course_id_fk foreign key (course_id) references course(id) -- 外键约束
);

5. 一对一

create database db5;
use db5;

-- 创建用户表
create table tb_user(
        id int unsigned  primary key auto_increment comment 'ID',
        name varchar(10) not null comment '姓名',
        gender tinyint unsigned not null comment '性别, 1 男  2 女',
        phone char(11) comment '手机号',
        degree varchar(10) comment '学历'
) comment '用户基本信息表';

-- 用户身份信息表
create table tb_user_card(
     id int unsigned  primary key auto_increment comment 'ID',
     nationality varchar(10) not null comment '民族',
     birthday date not null comment '生日',
     idcard char(18) not null comment '身份证号',
     issued varchar(20) not null comment '签发机关',
     expire_begin date not null comment '有效期限-开始',
     expire_end date comment '有效期限-结束',
     user_id int unsigned not null unique comment '用户ID', -- 外键,指向用户表主键, 必须有唯一约束,才能保证1对1
     constraint fk_user_id foreign key (user_id) references tb_user(id) -- 外键约束
) comment '用户身份信息表';

二、多表查询

1. 交叉连接(了解)

使用坐标的每一条数据分别去连接右表的每一条数据，将所有的连接结果都显示出来
语法：select 字段列表 from 左表，右表

2. 内连接

使用左表中的每一条数据分别去连接右表中的每一条数据，仅仅显示出匹配成功的那部分
语法：
隐式内连接： select * from 左表，右表 where 连接条件
显示内连接： select * from 左表 [inner] join 右表 on 连接条件
案例

-- 隐式内连接
select * from emp e,dept d where e.dept_id = d.id;
-- 显示内连接
select * from emp e inner join dept d on e.dept_id = d.id;

3. 左外连接、右外连接（了解）

（左外连接）首先要显示出左表的全部，然后使用连接条件去匹配右表，能匹配中的就显示，匹配不中的就显示为null
语法：select * from 左表 left outer join 右表 on 连接条件
（右外连接）首先要显示出右表的全部，然后使用连接条件去匹配左表，能匹配中的就显示，匹配不中的就显示未null
语法：select * from 左表 rigth outer join 右表 on 连接条件
案例

-- 左外连接
select * from emp e left outer join dept d on e.dept_id = d.id
-- 右外连接
select * from emp e rigth outer join dept d on e.dept_id = d.id

4. 子查询

-- 查询工资小于平均工资的员工有哪些？(子查询结果为一个值)
-- 1 查询平均工资
select avg(salary) from emp; -- 6391.5
-- 2 查询谁的工资小于上面的数
select * from emp where salary < 6391.5;
-- 3. 合并
select * from emp where salary < (select avg(salary) from emp);


-- 2: 查询工资大于5000的员工，所在部门的名字 (子查询结果为多个值)
-- 1 查询工资大于5000的员工的部门id
select distinct dept_id from emp where salary > 5000; -- 1 2
-- 2 查询这些部门id的名称
select name from dept where id in (1,2);
-- 合并
select name from dept where id in (select distinct dept_id from emp where salary > 5000);


-- 3: 查询出2011年以后入职的员工信息，包括部门信息 (子查询结果为一张表)

-- 查询出2011年以后入职的员工信息
select * from emp where join_date >= '2011-01-01';

-- 使用上面的结果连接部门表,获取部门信息
select * from
    (select * from emp where join_date >= '2011-01-01')
as t1 left outer join dept d on t1.dept_id = d.id

三、多表查询

查询操作

-- 1. 查询价格低于 10元 的菜品的名称、价格及其菜品的分类名称
select d.name,d.price,c.name from dish d join category c on d.category_id = c.id where d.price < 10;
-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类, 也需要将菜品查询出来)
select d.name,d.price,c.name from dish d left join category c on d.category = c.id where d.price between 10 and 50 and d.status = 1;
-- 3. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）
select s.name,s.price,d.name,d.price,sd.copies from setmeal s join setmeal_dish sd on s.id = sd.setmeal_id join dish d on sd.setmeal_id = d.id where s.name = "商务套餐A";
-- 4. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格)
select name,price from dish where price < (select avg(price) from dish);
-- 5. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格
select c.name,max(price) from dish d join category c on c.id = d.category_id group by c.name;
-- 6. 查询各个分类下状态为'起售' , 并且该分类下菜品总数量大于等于3的分类名称
select c.name,count(1) as num from dish d join category c on c.id = d.category_id where d.status = 1 group by c.name having num >= 3;

四、事务

1. 概念

事务是一组操作的集合，它是一个不可分割的工作单位（这些操作要么同时成功，要么同时失败）

2. 事务解释（操作）

SQL语法	描述
begin; (start transaction)	开启手动控制事务
commit;	提交事务
rollback;	回滚事务

3. 事务特性

原子性：事务是不可分割的最小单位，要么全部成功，要么全部失败
一致性：一个事务执行前后，数据库的状态是一致的（存在两个账户A，B，转账之前AB两账户总余额为200，转账之后AB总余额依旧为200）
隔离性：当多个事务同时执行的时候，互相不会产生影响（每个事务对应一个临时区，B事务不可以访问A事务的临时区）
持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。(一旦数据提交到数据库，数据就不可变)
事务隔离性带来的问题：
脏读：一个事务读取到了另外一个事务没有提交的数据
不可重复读：一个事务读取到了另外一个事务已经修改的数据。
（A事务有两条一样的查询语句，先执行第一条查询到账户为100，此时B事务对账户数据进行修改为90，最后A事务执行第二条完全一样的查询语句。此时会出现A事务两条查询语句查询结果不一样的问题）
幻读（虚度）：一个事务读取到了另一个事务新增的数据。
事务隔离级别：

序号	名字	隔离级别	脏读	不可重复读	幻读	默认
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否