JavaWeb后端数据库MySQL的使用

MySQL

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

SQL

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

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

数据库设计

DDL
数据库操作

表操作
创建

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

DQL
基础查询
select 字段列表
from 表名列表

条件查询
where 条件列表

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

排序查询
order by 排序字段列表

分页查询
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 > '2006-01-01';

select e.*,tb_dept.name from (select * from tb_emp where entrydate > '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;