数据库开发-MySQL

YangeIT大约 32 分钟数据库数据库SQLDQL

数据库开发-MySQL

目标

  1. 能够进行多表查询 ✏️ ❤️
    1. 左关联 ✏️
    2. 右关联 ✏️
    3. 内关联 ✏️
    4. 子查询 ✏️
  2. 理解数据库事务 🍐
    1. 事务的四大特性
  3. 理解并使用索引 🍐 ❤️
    1. 知悉索引作用和结构
    2. 知悉创建索引
  4. mybatis的入门案例 ✏️
    1. 入门程序实现 🍐 ✏️ ❤️
    2. JDBC 🚀
    3. 数据库连接池 🍐

知识储备

  1. 熟悉表和表的关系以及表和表的关系建立方法
  2. 熟悉单表查询的操作

1. 多表查询 🍐✏️

初始数据 👇

点击查看数据准备的SQL脚本
#建议：创建新的数据库
create database db04;
use db04;

-- 部门表
create table tb_dept
(
    id          int unsigned primary key auto_increment comment '主键ID',
    name        varchar(10) not null unique comment '部门名称',
    create_time datetime    not null comment '创建时间',
    update_time datetime    not null comment '修改时间'
) comment '部门表';
-- 部门表测试
insert into tb_dept (id, name, create_time, update_time)
values (1, '学工部', now(), now()),
       (2, '教研部', now(), now()),
       (3, '咨询部', now(), now()),
       (4, '就业部', now(), now()),
       (5, '人事部', now(), now());

-- 员工表
create table tb_emp
(
    id          int unsigned primary key auto_increment comment 'ID',
    username    varchar(20)      not null unique comment '用户名',
    password    varchar(32) default '123456' comment '密码',
    name        varchar(10)      not null comment '姓名',
    gender      tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
    image       varchar(300) comment '图像',
    job         tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
    entrydate   date comment '入职时间',
    dept_id     int unsigned comment '部门ID',
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '修改时间'
) comment '员工表';
-- 员工表测试数据
INSERT INTO tb_emp(id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) 
VALUES 
(1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()),
(2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()),
(3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()),
(4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()),
(5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()),
(6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()),
(7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()),
(8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()),
(9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()),
(10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()),
(11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()),
(12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()),
(13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()),
(14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()),
(15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()),
(16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,now(),now()),
(17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now());

1️⃣ 多表查询-介绍

多表查询

多表查询：查询时从多张表中获取所需数据

单表查询的SQL语句：select 字段列表 from 表名;

那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;

查询用户表和部门表中的数据：

select * from  tb_emp , tb_dept;
执行截图
执行截图

此时,我们看到查询结果中包含了大量的结果集，总共85条记录，而这其实就是员工表所有的记录(17行)与部门表所有记录(5行)的所有组合情况，这种现象称之为笛卡尔积 。

总结

  1. 在多表查询中，就是需要找寻条件，消除无效笛卡尔积

课堂作业

  1. 什么是多表查询？有哪些应用场景？
  2. 笛卡尔积是什么？如何消除笛卡尔积🎤

2️⃣ 多表查询-分类

多表查询分类

多表查询可以分为：

  1. 连接查询
    • 内连接：相当于查询A、B交集部分数据 image-20221207165446062
  2. 外连接
    • 左外连接：查询左表所有数据(包括两张表交集部分数据)
    • 右外连接：查询右表所有数据(包括两张表交集部分数据)
  3. 子查询

分类代码操作

多表查询-内连接

内连接查询：查询两表或多表中交集部分数据。

内连接从语法上可以分为：

  • 隐式内连接
  • 显式内连接

隐式内连接语法：

select  字段列表   from   表1 , 表2   where  条件 ... ;

显式内连接语法：

select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

案例：查询员工的姓名及所属的部门名称

  • 隐式内连接实现
select tb_emp.name , tb_dept.name -- 分别查询两张表中的数据
from tb_emp , tb_dept -- 关联两张表
where tb_emp.dept_id = tb_dept.id; -- 消除笛卡尔积
  • 显式内连接实现
select tb_emp.name , tb_dept.name
from tb_emp inner join tb_dept
on tb_emp.dept_id = tb_dept.id;
image-20221207173435289
image-20221207173435289

多表查询时给表起别名：

  • tableA as 别名1 , tableB as 别名2 ;

  • tableA 别名1 , tableB 别名2 ;

image-20221207174234522
image-20221207174234522

使用了别名的多表查询：

select emp.name , dept.name
from tb_emp emp inner join tb_dept dept
on emp.dept_id = dept.id;

注意事项:

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

总结

  1. 内连接从语法上可以分为：
    • 隐式内连接没有明显的关键字
    • 显式内连接关键字inner join on

内连接题目:

  1. 查询每个员工的姓名和其所在部门的名称。
  1. 列出每个部门的名称以及该部门下的员工数。

左外连接题目

  1. 列出所有员工的姓名和他们所在的部门名称，如果员工没有分配到部门，也要包括他们。
  1. 显示每个部门的名称以及在该部门下的员工数，包括没有员工的部门。

右外连接题目

  1. 显示每个部门的名称以及在该部门下的员工数，包括没有部门的员工。

4️⃣ 多表查询-子查询

多表查询-子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询

SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

根据子查询结果的不同分为：

  1. 标量子查询（子查询结果为单个值[一行一列]）
  2. 列子查询（子查询结果为一列，但可以是多行）
  3. 行子查询（子查询结果为一行，但可以是多列）
  4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询可以书写的位置： 👇

  1. where之后
  2. from之后
  3. select之后

代码操作：

多表查询-子查询-标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询

常用的操作符： = <> > >= < <=

案例1：查询"教研部"的所有员工信息

可以将需求分解为两步：

  1. 查询 "教研部" 部门ID
  2. 根据 "教研部" 部门ID，查询员工信息
-- 1.查询"教研部"部门ID
select id from tb_dept where name = '教研部';    #查询结果：2
-- 2.根据"教研部"部门ID, 查询员工信息
select * from tb_emp where dept_id = 2;

-- 合并出上两条SQL语句
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');






 

点击查看运行截图open in new window

案例2：查询在 "方东白" 入职之后的员工信息

可以将需求分解为两步：

  1. 查询 方东白 的入职日期
  2. 查询 指定入职日期之后入职的员工信息
-- 1.查询"方东白"的入职日期
select entrydate from tb_emp where name = '方东白';     #查询结果：2012-11-01
-- 2.查询指定入职日期之后入职的员工信息
select * from tb_emp where entrydate > '2012-11-01';

-- 合并以上两条SQL语句
select * from tb_emp where 
      entrydate > (select entrydate from tb_emp where name = '方东白');






 
 

点击查看运行截图open in new window

总结

  • 标量子查询
    • 标量子查询的结果是单个值，通常是一行一列。
    • 用于比较运算，如子查询结果与外部查询进行比较。
    • 典型用途包括子查询结果作为条件进行过滤或比较。
  • 列子查询
    • 列子查询的结果是一列值，可以包含多行。
    • 常用于IN子句，筛选出某列中的特定值。
    • 通常用于子查询结果与外部查询中的某列进行匹配。
  • 行子查询
    • 行子查询的结果是一行值，可以包含多列。
    • 常用于子查询结果作为外部查询的条件。
    • 通常用于需要比较多个列的情况，子查询结果需匹配外部查询的多个列。
  • 表子查询
    • 表子查询的结果是多行多列，相当于子查询结果是一张表。
    • 常用于连接和关联，将子查询的结果与外部查询的表进行连接。
    • 适用于复杂查询，通常需要多个列和多行

课堂作业

  1. 题目：请解释什么是标量子查询？可以提供一个具体的标量子查询的示例吗？🎤

标量子查询是一种子查询类型，其结果是单个值，通常是一行一列。它通常用于比较运算，例如与外部查询进行比较。示例：查找员工工资高于公司平均工资的员工

  1. 题目：可以解释什么是列子查询，以及列子查询的常见用途吗？

列子查询的结果是一列值，可以包含多行。它通常用于IN子句，筛选出某列中的特定值。常见用途包括查找符合某个条件的多个值。示例：查找订购特定产品的订单。

5️⃣ 多表查询-案例

多表查询-案例

基于之前设计的多表案例的表结构，我们来完成今天的多表查询案例需求。

准备环境

将资料中准备好的多表查询的数据准备的SQL脚本导入数据库中。

imageimage-20221208143318921

  • 分类表：category
  • 菜品表：dish
  • 套餐表：setmeal
  • 套餐菜品关系表：setmeal_dish
image-20221208143312292
image-20221208143312292

需求实现

  1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称
/*查询技巧：
     明确1：查询需要用到哪些字段
        菜品名称、菜品价格 、 菜品分类名
     明确2：查询的字段分别归属于哪张表
        菜品表：[菜品名称、菜品价格]
        分类表：[分类名]
     明确3：如查多表，建立表与表之间的关联
        菜品表.caategory_id = 分类表.id
     其他：（其他条件、其他要求）
        价格 < 10
*/
select d.name , d.price , c.name from dish AS d , category AS c
   where d.category_id = c.id
           and d.price < 10;
image-20221208145036602
image-20221208145036602

总结

课堂作业

  1. 将上述案例练习一遍

2. 事务 🍐

1️⃣事务-介绍

事务-介绍

场景：学工部整个部门解散了，该部门及部门下的员工都需要删除了。

  • 操作：

    -- 删除学工部
delete from dept where id = 1;  -- 删除成功

-- 删除学工部的员工
delete from emp where dept_id = 1; -- 删除失败（操作过程中出现错误：造成删除没有成功）

  • 问题：如果删除部门成功了，而删除该部门的员工时失败了，此时就造成了数据的不一致。

​ 要解决上述的问题，就需要通过数据库中的事务来解决。

代码操作

使用事务控制删除部门和删除该部门下的员工的操作：

-- 开启事务
start transaction ;

-- 删除学工部
delete from tb_dept where id = 1;

-- 删除学工部的员工
delete from tb_emp where dept_id = 1;
  • 上述的这组SQL语句，如果如果执行成功，则提交事务
-- 提交事务 (成功时执行)
commit ;
  • 上述的这组SQL语句，如果如果执行失败，则回滚事务
-- 回滚事务 (出错时执行)
rollback ;

总结

  • 事务
    • 事务是一组操作的集合，它是一个不可分割的工作单位。
    • 事务会将所有的操作作为一个整体提交或撤销操作请求，要么同时成功，要么同时失败。
  • 事务操作SQL语句
    • START TRANSACTION或BEGIN: 开启手动控制事务。
    • COMMIT: 提交事务，如果所有操作成功，则提交事务。
    • ROLLBACK: 回滚事务，如果其中一项操作失败，则回滚事务。
  • 两种方式进行事务操作
    • 自动提交事务：执行一条SQL语句即提交一次事务（默认MySQL的事务是自动提交）。
    • 手动提交事务：先开启事务，执行SQL语句，然后根据成功或失败，选择提交或回滚事务。

课堂作业

  1. 日志中有哪些信息？🎤

  2. 什么是事务，以及它的主要作用是什么？

事务是一组操作的集合，是一个不可分割的工作单位。它的主要作用是将多次操作视为一个整体执行，要么所有操作成功，要么所有操作失败，以确保数据的一致性和完整性。

  1. 请说明事务操作的SQL语句有哪些，以及它们的作用？

事务操作的SQL语句包括START TRANSACTION或BEGIN（开启事务），COMMIT（提交事务），和ROLLBACK（回滚事务）。START TRANSACTION用于开启手动控制事务，COMMIT用于提交事务，如果所有操作成功，ROLLBACK用于回滚事务，如果其中一项操作失败。这些语句一起实现了事务的控制和管理。

2️⃣ 事务-四大特性

事务四大特性

  • 原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

事务的四大特性简称为：ACID

拓展

  • 原子性：通过回滚日志（undolog）来实现。
  • 持久性：通过逻辑备份日志（binlog）、重做日志（redolog）来实现。
  • 隔离性：通过(读写锁+MVCC)来实现。
  • 一致性：MySQL通过原子性，持久性，隔离性最终实现（或者说定义）数据一致性。
  • redo log：重做日志，记录事务操作的变化，确保事务的持久性。
  • undo log：回滚日志，保存了事务发生之前的数据的一个版本，用于事务的回滚操作

拓展

事务的隔离性是数据库管理系统（DBMS）中的一个重要特性，用于控制并发事务之间的相互影响程度。SQL 标准定义了四个隔离级别，每个级别都有不同的隔离程度和可能出现的问题。这些隔离级别从低到高分别是：

  • 读未提交（Read Uncommitted）：

问题： 一个事务可以读取另一个正在进行中的事务未提交的修改。 最低的隔离级别。 可能出现脏读、不可重复读和幻读等问题。 MySQL默认不使用此隔离级别。

  • 读提交（Read Committed）：

问题： 一个事务只能读取另一个已经提交的事务的修改。 避免了脏读问题，但可能出现不可重复读和幻读。

  • 可重复读（Repeatable Read）：

问题：在一个事务中的查询返回的数据集始终保持一致，即不可重复读。 可避免脏读和不可重复读，但仍然可能出现幻读问题。 MySQL默认隔离级别。

  • 串行化（Serializable）：

问题：所有事务按顺序执行，事务之间不会相互干扰。 提供最高的隔离性，但可能导致性能下降。 避免脏读、不可重复读和幻读。

MySQL默认的隔离级别是可重复读（Repeatable Read）。这意味着在一个事务中，查询的结果集始终保持一致，不可重复读问题得到了避免。但仍然可能出现幻读问题，即一个事务在查询同一数据集时可能看到不同的行。

注意：在实际应用中，选择合适的隔离级别取决于具体的业务需求和性能要求。更高的隔离级别通常会导致更多的锁和性能开销。MySQL提供了设置隔离级别的选项，可以根据需要进行调整。

总结

事务的四大特性简称为：ACID

课堂作业

  1. 请简要解释事务的一致性（Consistency）特性是如何实现的？ 🎤

事务的一致性特性是通过确保事务在完成时使所有数据保持一致状态来实现的。这意味着事务必须满足业务规则，不会导致数据不一致。数据库管理系统在执行事务期间会检查和维护数据的一致性，如果事务违反了一致性规则，系统将回滚事务。

  1. 请解释 redo log 和 undo log 在数据库事务中的作用是什么？ 🎤

redo log（重做日志）用于记录事务的操作变化，确保事务的持久性。它可以在崩溃后用于重演事务，以确保数据不会丢失。undo log（回滚日志）用于保存事务发生之前的数据的一个版本，用于事务的回滚操作。它可以用来还原事务修改的数据，以维护事务的原子性和一致性。这两种日志协同工作，确保了数据库的可靠性

3. 索引 🍐

1️⃣ 索引-介绍

索引

如何造一些假数据？

  1. 创建goods表格
create table goods
(
 id int auto_increment,
 name varchar(100) not null,
 price int default 1 not null,
 constraint goods_pk
  primary key (id)
)
comment '商品表';
  1. 往其中插入一条数据
  1. 反复执行下面的语句，造一堆假数据（每执行一次就观察下是否创建了假数据） 直到创建10万假数据
insert into goods(name, price)  SELECT concat(name,round(rand()*10,0)) as name,price from goods

总结

优点：

  1. 提高数据查询的效率，降低数据库的IO成本。
  2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

缺点：

  1. 索引会占用存储空间。
  2. 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

课堂作业

  1. 索引的作用是什么？🎤

2️⃣ 索引-结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。

我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree 结构组织的索引。

在没有了解B+Tree结构前，我们先回顾下之前所学习的树结构：

二叉查找树：左边的子节点比父节点小，右边的子节点比父节点大

image-20221208174135229
image-20221208174135229

当我们向二叉查找树保存数据时，是按照从大到小(或从小到大)的顺序保存的，此时就会形成一个单向链表，搜索性能会打折扣。

image-20221208174859866
image-20221208174859866

问题

在Mysql数据库中并没有使用二叉搜索数或二叉平衡数或红黑树来作为索引的结构。

思考：采用二叉搜索树或者是红黑树来作为索引的结构有什么问题？

答案：

  1. 最大的问题就是在数据量大的情况下，树的层级比较深，会影响检索速度。
  2. 因为不管是二叉搜索数还是红黑数，一个节点下面只能有两个子节点。
  3. 此时在数据量大的情况下，就会造成数的高度比较高，树的高度一旦高了，检索速度就会降低。

说明：如果数据结构是红黑树，那么查询1000万条数据，根据计算树的高度大概是23左右，这样确实比之前的方式快了很多，但是如果高并发访问，那么一个用户有可能需要23次磁盘IO，那么100万用户，那么会造成效率极其低下。所以为了减少红黑树的高度，那么就得增加树的宽度，就是不再像红黑树一样每个节点只能保存一个数据，可以引入另外一种数据结构，一个节点可以保存多个数据，这样宽度就会增加从而降低树的高度。这种数据结构例如BTree就满足。

点击查看拓展(B+Tree的特点)

下面我们来看看B+Tree(多路平衡搜索树)结构中如何避免这个问题：

image-20221208181315728
image-20221208181315728

B+Tree结构：

  • 每一个节点，可以存储多个key（有n个key，就有n个指针）
  • 节点分为：叶子节点、非叶子节点
    • 叶子节点，就是最后一层子节点，所有的数据都存储在叶子节点上
    • 非叶子节点，不是树结构最下面的节点，用于索引数据，存储的的是：key+指针
  • 为了提高范围查询效率，叶子节点形成了一个双向链表，便于数据的排序及区间范围查询

非叶子节点都是由key+指针域组成的，一个key占8字节，一个指针占6字节，而一个节点总共容量是16KB，那么可以计算出一个节点可以存储的元素个数：16*1024字节 / (8+6)=1170个元素。

  • 查看mysql索引节点大小：show global status like 'innodb_page_size'; -- 节点大小：16384

当根节点中可以存储1170个元素，那么根据每个元素的地址值又会找到下面的子节点，每个子节点也会存储1170个元素，那么第二层即第二次IO的时候就会找到数据大概是：1170*1170=135W。也就是说B+Tree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。

对于第二层每个元素有指针，那么会找到第三层，第三层由key+数据组成，假设key+数据总大小是1KB，而每个节点一共能存储16KB，所以一个第三层一个节点大概可以存储16个元素(即16条记录)。那么结合第二层每个元素通过指针域找到第三层的节点，第二层一共是135W个元素，那么第三层总元素大小就是：135W*16结果就是2000W+的元素个数。

结合上述分析B+Tree有如下优点：

  • 千万条数据，B+Tree可以控制在小于等于3的高度
  • 所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找

3️⃣ 索引-语法

索引-语法

创建索引

create  [ unique ]  index 索引名 on  表名 (字段名,... ) ;

案例：为tb_emp表的name字段建立一个索引

create index idx_emp_name on tb_emp(name);
image-20221209105119159
image-20221209105119159

在创建表时，如果添加了主键和唯一约束，就会默认创建：主键索引、唯一约束

image-20221209105846211
image-20221209105846211

总结

key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等。

  • primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index；
  • unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个index；
  • foreign key 也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；

index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；因此，索引只是索引，它不会去约束索引的字段的行为。

  • 例如：create table t(id int, index inx_tx_id (id));

4.Mybatis-入门案例 ✏️

MyBatis入门案例教程

焱哥的博客
Copyright © 2025 YangeIT