Javaweb-mysql多表查询-事务

mysql 多表

1.多表关系

1.1 概述

项目开发中，因为业务之间相互关联，所以实体与实体之间存在联系，故而表跟表之间也存在着各种联系
我们把这种能够表示表间关系的数据库称为关系型数据库（RDBMS）
在数据库设计上，表关系分为三种：一对多、多对多、一对一

• 一对多
  场景：班级和学生 部门和员工
  说明：一个班级对应多个学生，一个学生只能对应一个班级
• 多对多
  场景：学生和课程 学生和老师
  说明：一个学生对应多个课程，一个课程对应多个学生
• 一对一
  场景：丈夫和妻子 人和身份证号
  说明：一个丈夫只有一个妻子，一个妻子也只有一个丈夫

1.2 一对多

建表原则:在从表中添加一个字段（列），字段名（主表名_id）类型与主表的主键一致，这个字段称为外键，通过外键指向主表的主键，建立关联关系
例子: 班级和学生

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,'166'),(2,'167');
insert into student values (1,'张三',1),(2,'李四',2),(3,'王五',2);

1.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);

物理外键和逻辑外键

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

1.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) -- 外键约束
);

1.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 '用户身份信息表';

2. 多表查询

在企业开发中，我们一个业务需要的数据往往是来自多张表的，所以这时候就需要多表联合查询。
所谓的多表联合查询就是使用一条SQL语句将多张表的数据一起查询展示出来。

新建两个表,用于作为查询表

-- 创建部门表（主表）
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 创建员工表（从表）
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),  -- 性别（sex）
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT -- 外键字段
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',6666,'2011-03-14',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',4500,'2017-03-04',null);

2.1 交叉连接

查询员工表和部门表中的所有信息

-- 解释
	使用左表中的每一条数据分别去连接右表中的每一条数据, 将所有的连接结果都展示出来
	
-- 语法
 	select * from  左表,右表
 
-- 案例
	select * from emp,dept

2.2 内连接

查询员工表和部门表中的信息, 仅显示两表中全部符合的数据

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

-- 案例
    -- 隐式内连接
    select * from emp,dept where emp.dept_id = dept.id;
    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; -- 记住这个
    select * from emp e join dept d on e.dept_id = d.id;

2.3 左外连接

查询所有员工和对应的部门信息, 不存在部门的使用null补齐

-- 解释
	首先要显示出左表的全部, 然后使用连接条件匹配右表,能匹配中的就显示,匹配不中的显示为null

-- 语法
	select * from 左表 left [outer] join 右表 on 连接条件

-- 案例
	select * from emp e left outer join dept d on e.dept_id = d.id;

2.4 右外连接

查询所有员工和对应的部门信息, 不存在部门的使用null补齐

-- 解释
	首先要显示出右表的全部, 然后使用连接条件匹配左表,能匹配中的就显示,匹配不中的显示为null
	
-- 语法
	select * from 左表 right outer join 右表 on 连接条件

-- 案例
	select * from emp e right outer join dept d on e.dept_id = d.id;

2.5 子查询

一个查询使用了另一个查询的结果(分为标量子查询,列子查询和表子查询)

- 解释:
	一个查询使用了另一个查询的结果

-- 标量子查询
-- 1: 查询工资小于平均工资的员工有哪些？(子查询结果为一个值)
-- 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

2.6 小结

-- 内连接：
	select * from 左表 [inner] join 右表 on 连接条件
	
-- 左外连接：
	select * from 左表 left [outer] join 右表 on 连接条件

-- 子查询根据查询结果不同，作用不同
	单个值，使用 = > <  等进行条件判断 :    SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
	多个值，使用in、not in进行条件判断 :    SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
	一张表，将此结果声明为临时表来使用 :    SELECT * FROM (子查询) AS 表 JOIN 表 ON 条件;

规律
1.确定几张表
2.确定连接条件
使用 on关键字
3.确定业务条件
使用where关键字
4.确定显示字段

3. 事务

是指的是多个步骤的一组业务操作，要么都成功，要么都失败

3.1 事务引入

场景:有一张账户表,表中有两个账户

-- 创建账户表,并且添加两条测试数据
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(32),
	money DOUBLE 
);
INSERT INTO account (NAME, money) VALUES ('AA', 1000), ('B', 1000);

需求: 实现这样一个业务,从A向B账户转账10元

-- 将A账户减去10元
update account set money=money-10 where name = 'A';  --执行成功

-- 将B账户加上10元
update account set money=money+10 where name = 'B';  --遇到问题,执行失败了

问题: 如果上面的两条sql，一条执行成功了，但是另一条执行失败了，此时就造成了数据的不一致
要解决上述的问题，就需要通过数据库中的事务来解决

3.2 事务解释

一个业务操作包含多个子操作，如果这个业务操作被事务管理了，那么这些子操作要么同时成功，要么同时失败。

在MySQL数据库中，默认情况下，一条DML语句就是一个独立的事务。

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

-- 事务的三个api
-- 开启事务 begin;
-- 提交事务 commit;
-- 回滚事务 rollback;

-- 全部成功后提交,修改数据库
begin; --开启事务
update account set money=money-10 where name = 'AA';
update account set money=money+10 where name = 'BB';
commit; -- 提交事务

-- 失败时回滚,恢复成修改之前的数据库
begin; --开启事务
update account set money=money-10 where name = 'AA';
update account set money=money+10 where name = 'BB';
rollback; -- 回滚事务

3.3 事务特性

3.3.1 事务特性(ACID)

• 原子性：atomicity 事务是不可分割的最小单元，要么全部成功，要么全部失败
• 一致性：consistency 一个事务执行前后，数据库的状态是一致的
• 隔离性：isolation 当多个事务同时执行的时候，互相是不会产生影响的
• 持久性：durability 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

3.3.2 事务隔离性带来的问题

• 脏读：一个事务读取到了另外一个事务没有提交的数据
• 不可重复读：一个事务读取到了另外一个事务修改的数据（修改）
• 幻读（虚读）：一个事务读取到了另外一个事务新增的数据（新增）

3.3.3 事务隔离级别

MySQL提供了事务隔离级别来解决这些问题。在MySQL中共支持四种隔离级别，分别是：

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