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 字段列表 FROMWHERE 字段名 = (子查询);
	多个值,使用innot in进行条件判断 :    SELECT 字段列表 FROMWHERE 字段名 in (子查询);
	一张表,将此结果声明为临时表来使用 :    SELECT * FROM (子查询) ASJOINON 条件;

规律
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 committedOracle
3可重复读repeatable readMySQL
4串行化serializable
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值