JavaWeb后端数据库MySQL的使用

本文介绍: 在进行数据库表设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在各种联系。物理的对数据库表中一列或多列的值进行排序的一种存储结构，帮助数据库高效获取数据。在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。在数据库表中多的一方，添加字段，来关联1的一分的主键。约束：作用于表中字段上的规则，用于限制表中的数据。原子性、一致性、隔离性、持久性。

Jav aWe b

- MySQL
- - SQL
  - 数据库设计
- 多表设计
- - 1对多
  - 1对1
  - 多对多
- 多表查询
- - 连接查询
  - 子查询
  - 事务
  - 索引

MySQL

在这里插入图片描述

MySQL数据模型
 关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

SQL

SQL：操作关系型数据库的编程语言

SQL语句通常分为4大类（我们主要学前面3个）
DDL：数据定义语言
DML：数据操作语言
DQL：数据查询语言
DCL：数据控制语言

数据库设计

DDL
数据库操作

表操作
创建

约束：作用于表中字段上的规则，用于限制表中的数据

DQL
基础查询
 select 字段列表
fro m 表名列表

条件查询
 where 条件列表

分组查询
 group b y 分组字段
 having 分组之后的条件

排序查询
 order b y 排序字段列表

分页查询
 limit 分页参数

多表设计

在进行数据库表设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在各种联系。

1对多

在数据库表中多的一方，添加字段，来关联1的一分的主键

1对1

在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）
在这里插入图片描述

多对多

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
在这里插入图片描述

案例：
在这里插入图片描述

多表 查询

在做多表查询时，需要消除无效的笛卡尔积

连接查询

内连接

-- ============================= 内连接 ==========================
-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;


-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;

外连接

左外连接

查询左表所有数据（包含两表交集部分数据）

右外连接

查询右表所有数据（包含两表交集部分数据）

-- =============================== 外连接 ============================
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
select tb_emp.name,tb_dept.name from tb_emp left join tb_dept on tb_emp.dept_id = tb_dept.id;



-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select tb_emp.name,tb_dept.name from tb_emp right join tb_dept on tb_emp.dept_id = tb_dept.id;

子查询

-- 标量子查询
-- A. 查询 "教研部" 的所有员工信息
-- a.先查"教研部"的部门ID - tb_dept
select id from tb_dept where name = '教研部';
-- b.再查该部门ID下的员工信息
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');


-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
select id from tb_dept where name = '教研部' or name = '咨询部';
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');


-- 行子查询
-- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
select entrydate,job from tb_emp where name = '韦一笑';

select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑');


-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select * from tb_emp where entrydate &gt; '2006-01-01';

select e.*,tb_dept.name from (select * from tb_emp where entrydate &gt; '2006-01-01') e ,tb_dept where e.id = tb_dept.id;

事务

一组操作的集合。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，及这些操作要么同时成功或者失败。

四大特征：
原子性、一致性、隔离性、持久性
在这里插入图片描述

-- ================================== 事务 ====================================
-- 开启事务
start transaction ;

-- 删除部门
delete from tb_dept where id = 4;
-- 删除部门下的员工
delete from tb_emp where dept_id = 4;

-- 提交事务
commit ;

-- 上面如果有个操作失败，回滚事务
rollback ;

select * from tb_dept;
select * from tb_emp;

索引

物理的对数据库表中一列或多列的值进行排序的一种存储结构，帮助数据库高效获取数据。
在这里插入图片描述

在这里插入图片描述

-- ================================== 索引 ====================================
-- 创建
create index idx_emp_name on tb_emp(name);

-- 查询
show index from tb_emp;

-- 删除
drop index idx_emp_name on tb_emp;