掌握 MySQL：索引、事务与多表设计

肖申克的舅叔
38 阅读15分钟

第一部分 什么是数据库？

1.1 数据库（DataBase）：

是存储和管理数据的仓库。

数据库管理系统：DataBaseManagementSystem(DBMS),操纵和管理数据库的大型软件。
SQL : StructuredQueryLanquage,操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

我们想要进行增删查改的操作只需要向数据库管理系统发送SQL语句，由数据库管理系统再去操作数据库中的数据。

下面呢是对一些主流的关系型数据库进行介绍：

那既然有那么多数据库，那我咋知道以后上班会用到哪个呢？

这点完全不必担心，因为关系型数据库，都是通过SQL语句来操作的，而SQL语言又是统一标准的，所以都可以使用。

第二部分 MySQL的学习

2.1 数据模型

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

特点：

· 使用表存储数据，格式统一，便于维护

· 使用SQL语言操作， 标准统一， 使用方便， 可用于复杂查询

我们自己的计算机就是作为了一台MySQL服务器，接着我们就要通过mysql的客户端去连接MySQL服务器。 （在cmd命令行中通过一个Mysql的客户端命令 来连接MySQL数据库服务器）

而在MySQL服务器中就已经内置了一个软件DBMS（数据库管理系统） ，当我们向他发出SQL语句，它就可以操作数据库中的数据了。比如，向他发送SQL语句告诉DBMS创建一个数据库，它就会在我们的磁盘文件当中以文件夹的形式体现。下面是在命令行（管理员身份！）中的创建数据库语句：

这样在data文件中就创建好了一个数据库db02.（数据库都是存放在data目录下！）

我们还可以创建数据库db03，因为在一个数据库服务器中可以创建多个数据库（相互独立 互不影响），一个数据库中可以创建多张表结构的，创建的一张表，就是在该数据库文件（如db02）中的一个磁盘文件，其中就存放的是表结构中的数据。

2.2 数据库的设计

2.2.1 SQL简介:

SQL : 是一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

下面是SQL的语法：

SQL语句被分为四个大类：

先了解一下在项目中大致的步骤流程：

2.2.2 DDL

DDL 英文全称是 Data Definition Language, 数据定义语言, 用来定义数据库对象(数据库、表)。

DDL（数据库操作）

查询：

  • 查询所有数据库：show databases；
  • 查询当前数据库：：select database（）；

使用：

  • 使用数据库：use 数据库名；

创建：

  • 创建数据库：create database [if not exists] 数据库名；

删除：

  • 删除数据库：drop database [if exists] 数据库名；

在命令行中操作MySQL

①无提示

②操作繁琐

③无历史记录

所以我们会用到——图形化工具（DataGrip）

注意！！ 现在Idea中是已经集成了它~

Idea侧面 数据库面板上新建—数据源—MySQL

·

依次输入用户和密码就可以连接到本机端的数据库服务器。

·

这里添加所有架构（schema）就是database的意思。

这样就可以在侧栏看到所有数据库，等同于——show操作

（在SQL语句中schema等于同于database）

·

在控制台中就可以直接操作SQL语句

·

右上角这里可以切换数据库——等同于use操作+select操作

·

右键@localhost 新建-架构（schema）——等同于create操作

·

顾名思义 这个等同于——drop操作

·

这就是心爱的Idea的图形化操作！！！

DDL (表结构的操作)
创建：

表结构的创建~

create table 表名(
字段1 字段类型[约束][comment 字段1注释],
......
字段n 字段类型[约束][comment 字段n注释]
) [comment 表注释];

——————————

字段 可以理解为列名column~

①上方的约束：

概念: 约束是作用于表中字段上的规则, 用于限制存储在表中的数据。
目的: 保证数据库中数据的正确性、有效性和完整性。

auto_increment : 添加到主键后面，就可以让主键自动往上增长。

修改后：

②上方的字段类型：

📎MySQL数据类型.xlsx

见该Excel，（这里注意我们尽可能的在业务允许的情况下，选择占用磁盘小的数据类型，节省磁盘空间）

在Idea中一般直接根据图形化界面来创建表，

在某个数据库下建表

根据需求文档的要求，进行分析，每个字段的类型 约束等~

在这个信息栏中直接填写相应信息

很轻松就建好啦。

注意！ 每个表中都要默认添加两个字段：1.create_time 记录创建时间 2.update_time 记录更新的时间

设计表结构的基本流程：

查询：

表结构的查询~

● 查询当前数据库所有表: show tables;
● 查询表结构: desc 表名;
● 查询建表语句: show create table 表名 ;（可以直接点表 然后ctrl+B）

修改：

修改表结构~

● 添加字段: alter table 表名 add 字段名 类型(长度) [comment 注释][约束];
● 修改字段类型: alter table 表名 modify 字段名 新数据类型(长度);
● 修改字段名和字段类型: alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释][约束];
● 删除字段: alter table 表名 drop column 字段名;
● 修改表名: rename table 表名 to 新表名;

但在开发中常用的还是图形化界面：

右键修改表

就可以在图形化界面进行上述的任何操作（真方便啊 但是还是得会SQL语句哈~）

删除：

drop table [if exists]表名；

当然也可以直接右键表

注意！ 在删除表时 表中的所有数据也会被删除

所以综上所述呢，其实DDL语句已经被图形化界面工具所替代

2.3 数据库的操作（DML）

DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。

注意！ 在下面修改数据时的语句，都没有table这个单词！！只写表名

而上面表结构操作语句中都有table！~~~~~~~

2.3.1 INSERT 添加数据

INSERT语法：

● 指定字段添加数据: insert into 表名(字段名1,字段名2) values (值1,值2);
● 全部字段添加数据: insert into 表名 values (值1,值2,...);

（上面这两条都是一行中添加~）
● 批量添加数据(指定字段): insert into 表名(字段名1,字段名2) values (值1,值2),(值1,值2);
● 批量添加数据(全部字段): insert into 表名 values (值1,值2,...), (值1,值2,...);

（这两条是多行添加~）

注意事项：

  1. 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
  2. 字符串和日期型数据应该包含在引号中。
  3. 插入的数据大小,应该在字段的规定范围内。

2.3.2 Updata 修改数据

● 修改数据:update 表名 set 字段名1=值1,字段名2=值2,.... [where 条件];

语句后面要是没有加where则是修改整列数据

2.3.3 Delete 删除数据

· 删除数据：delete from 表名 [where 条件]；

  1. DELETE 语句的条件可以有, 也可以没有, 如果没有条件, 则会删除整张表的所有数据。
  2. DELETE 语句不能删除某一个字段的值(如果要操作, 可以使用UPDATE, 将该字段的值置为NULL)。

2.4 数据库的操作（DQL）

● DQL英文全称是Data Query Language(数据查询语言),用来查询数据库表中的记录。
● 关键字: SELECT

DQL基本语法：

● 查询多个字段: select 字段1,字段2,字段3 from 表名;
● 查询所有字段(通配符): select * from 表名;
● 设置别名: select 字段1 [as 别名1],字段2 [as 别名2] from 表名;
● 去除重复记录: select distinct 字段列表 from 表名;

补充：为什么不建议用星号查询数据库 select *？

  1. 性能问题：
    • 不必要的列读取：当查询返回比实际需要的列更多的数据时，数据库需要执行更多的工作来检索、处理和传输这些数据。这会增加数据库的负载，尤其是在处理大量数据时更为明显。
    • 索引失效：在某些情况下，如果查询中仅需要几个列，并且这些列被索引，而查询使用了SELECT *，则可能无法有效利用这些索引，因为数据库需要检索并返回表中的所有列，即使这些列中的大部分对于查询结果来说并不必要。
  1. 网络带宽和客户端处理：
    • 增加数据传输量：将不必要的列从数据库服务器传输到客户端应用程序会增加网络带宽的使用，可能导致性能下降，特别是在网络条件不佳或数据量大的情况下。
    • 客户端处理开销：客户端应用程序可能需要处理或解析比实际需要更多的数据，这会增加处理时间和资源消耗。
  1. 数据隐私和安全性：
    • 敏感信息泄露：在不需要某些敏感信息（如个人身份信息、财务信息等）的查询中使用SELECT *可能会无意中将这些信息暴露给不应该访问它们的用户或系统。
  1. 可读性和可维护性：
    • 降低SQL语句的清晰度：明确指定需要的列可以使SQL语句的意图更清晰，有助于其他开发人员或未来的自己更容易理解和维护代码。
    • 减少错误：当表结构发生变化时（如新增列或删除列），使用SELECT *的查询可能会突然返回不同的列集，这可能导致客户端应用程序出现错误。

DQL条件查询：

条件查询：select 字段列表 from 表名 where 条件列表；

DQL分组查询：

聚合函数： 将一列数据作为一个整体，进行纵向计算。

语法：select 聚合函数（字段列表） from 表名；

注意！ NUll值不参与所有聚合函数的运算；

统计数量a.count(字段) b.count(常量) c.count(*) ---- 推荐！

分组查询：

分组查询:select 字段列表 from 表名 [where 条件] group by 分组字段名【having 分组后过滤条件];

• where与having区别

  1. 执行时机不同: where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
  2. 判断条件不同: where不能对聚合函数进行判断！,而having可以。

DQL排序查询：

条件查询: select 字段列表 from 表名 [where 条件列表][group by 分组字段] order by 字段1 排序方式1, 字段2 排序方式2... ;

排序方式：

ASC：升序（默认）

DESC：降序

注意！ 如果是多字排序，当第一个字段相同时，才会根据第二个字段进行排序。

DQL 分页查询：

● 分页查询: select 字段列表 from 表名 limit 起始索引,查询记录数;

注意！

  1. 起始索引从0开始, 起始索引 = (查询页码-1) * 每页显示记录数。
  2. 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
  3. 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 5。

补充：

第三部分 多表设计

3.1 一对多

外键：

外键语法
-- 创建表时指定
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表(字段名)
);

-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

【但一般还是用图形化界面】

这样就把两张表绑定到一起了，但也有不妥的地方 如下：

物理外键

● 概念: 使用 foreign key 定义外键关联另外一张表。
● 缺点:
● 影响增、删、改的效率(需要检查外键关系)。
● 仅用于单节点数据库, 不适用与分布式、集群场景。
● 容易引发数据库的死锁问题, 消耗性能。

所以我们更推荐 逻辑外键

逻辑外键
● 概念:在业务层逻辑中,解决外键关联。
● 通过逻辑外键,就可以很方便的解决上述问题。

3.2 一对一

● 案例:用户与身份证信息的关系
● 关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率

user_id 是外键 并添加个unique 唯一

3.3 多对多

● 案例:学生与课程的关系
● 关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择

● 实现：建立第三张-中间表，中间表至少包含两个外键，分别关联两方主键

第四部分 多表查询

·多表查询： 指从多张表中查询数据

4.1 内连接

● 隐式内连接: select 字段列表 from 表1,表2 where 条件...;
● 显式内连接: select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

4.2 外连接

● 左外连接: select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
● 右外连接: select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;

表1是左边 表2是右边，如果是左外连接，那么左表（表1）全包含；

如果是右外连接，那么右表（表2）全包含。

先确定用左连接还是右连接 要查哪张表的全部信息，就把那张表放左边或者右边。

再项目开发中一般使用左外连接

4.3 子查询

● 介绍: SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
● 形式: select * from t1 where column1 = (select column1 from t2 ...);
● 子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是 select。

4.3.1 标量子查询

● 子查询返回的结果是单个值(数字、字符串、日期等), 最简单的形式
● 常用的操作符: = < > >= <=

4.3.2 列子查询

● 子查询返回的结果是一列（可以是多行）
● 常用的操作符: in 、not in等

4.3.3 行子查询

● 子查询返回的结果是一行（可以是多列）
● 常用的操作符: =、<>、in 、not in等

4.3.4 表子查询

● 子查询返回的结果是多行多列,常作为临时表
● 常用的操作符: in

第五部分 事务

5.1 事务 介绍与操作

事务是一组操作的集合,它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。

默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。所以上面的两条语句会分开单独成为两个事务进行提交。

● 开启事务: start transaction; / begin;
● 提交事务: commit;
● 回滚事务:rollback;

先开启事务，再执行事务中的所有语句，当commit提交了才会在表中看到数据的改变，只要有一个语句出错了，我们就回滚事务 将删除后的数据恢复过来，所以事务就体现出来了—— 一致性

5.2 事务的四大特性ACID（面试题）

原子性

事务是不可分割的最小单元，
要么全部成功，要么全部失败

一致性

事务完成时，必须使所有的数据都
保持一致状态

隔离性

数据库系统提供的隔离机制，保
证事务在不受外部并发操作影响
的独立环境下运行

持久性

事务一旦提交或回滚，它对数据
库中的数据的改变就是永久的

第六部分 索引

索引：是帮助数据库 高效获取数据的 数据结构（提高效率）

如果没有索引就是约等于全书扫描了；

但如果有索引就是存在类似二叉树这样的数据结构，查找效率就特别高（空间换时间），也有点缺点（下面讲）。

优点
● 提高数据查询的效率,降低数据库的IO成本。
● 通过索引列对数据进行排序,降低数据排序的成本,
降低CPU消耗。
缺点
● 索引会占用存储空间。
● 索引大大提高了查询效率,同时却也降低了insert、
update、delete的效率。（每次改变 都会重新去更新那个树）


磁盘目录下就会生成一个文件来存放数据 而且还挺大 -- 所以就是空间换时间

结构：

MySQL数据库支持的索引结构有很多,如:Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引,如果没有特别指明,都是指默认的B+Tree结构组织的索引。

为什么不采用 二叉搜索树或红黑树？

因为在大数据量的情况下，层级越深，检索速度就越慢

语法：

● 创建索引
create [unique] index 索引名 on 表名(字段名,...);
● 查看索引
show index from 表名;
● 删除索引
drop index 索引名 on 表名;

注意：

● 主键字段,在建表时,会自动创建主键索引。
● 添加唯一约束时,数据库实际上会添加唯一索引。

啊啊啊啦啦啦MySQL终于结束啦~~~~~~~~~

“只要一直在跑，就一定错不了！”

avatar
文章
阅读
粉丝