前言
为找实习做准备，跟网课学习中，差不多一周时间把MySQL部分看完（2024.5.15-2024.5.24），手敲知识点，坐地铁的时候几分钟过一遍，分享给大家。
一、 SQL
1.1 SQL 简介
- SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。
1.2 通用语法
- SQL语句可以单行或多行书写，以分号结尾。
- SQL语句可以使用空格／缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写。
- 单行注释：- -注释内容 或 # 注释内容(MySQL特有)
- 多行注释：/* 注释内容 */
1.3 SQL分类
- DDL : 数据定义语言
- DML : 数据操作语言
- DQL : 数据查询语言
- DCL : 数据控制语言
二、 DDL 数据定义语言
- Data Definition Language,数据定义语言，用来定义数据库对象(数据库、表 )。
2.1 数据库查询、创建、使用、删除
# 1.查询所有数据库
show databases;
# 2.查询当前数据库
select database();
# 3.创建数据库
create database [if not exists] 数据库名；
# 4.使用数据库
use 数据库名；
# 5.删除数据库
drop database [if exists] 数据库名；
# 注意：上述语法中的database，也可以替换成schema。如：create schema db01;
2.2 表创建（语法、约束、数据类型）
2.2.1 语法
create table 表名(
字段1 字段类型 [约束] [comment 字段1注释]，
......
字段n 字段类型 [约束] [comment 字段n注释]
) [comment 表注释]；
2.2.2 约束
- 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
- 目的：保证数据库中数据的正确性、有效性和完整性。
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段值不能为null | not null |
| 唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未能指定该字段值，则采用默认值 | default |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 | foreign key |
2.2.3 数据类型
MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。
| 数值类型 | 大小(byte) | 范围（有符号） | 范围 （无符号） | 用途 |
|---|---|---|---|---|
| tinyint | 1 | (-128,127) | (0，225) | 小整数值 |
| smallint | 2 | (-32768，32767) | (0,65535) | 大整数值 |
| mediumint | 3 | (-8388608,8388607) | (0,16777215) | 大整数值 |
| int | 4 | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
| bigint | 8 | (-2 ^ 63 , 2 ^ 63-1) | (0，2^64-1） | 极大整数值 |
| float | 4 | (-3.402 823 466 E+38,3.402 823 466 E+38) | 0和(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度浮点数值 |
| double | 8 | (-1.797 693 134 862 315 7 E+308，1.797 693 134 862 315 7 E+308) | 0和(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度浮点数值 |
| decimal | 对 decimal(M,D),如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
| 字符串类型 | 大小(byte) | 描述 |
|---|---|---|
| char | 0 - 255 | 定长字符串 |
| varchar | 0 - 65 535 | 变长字符串 |
| tinyblob | 0 - 255 | 不超过255个字符的二进制数据 |
| tinytext | 0 - 255 | 短文本字符串 |
| blob | 0 - 65 535 | 二进制形式的长文本数据 |
| text | 0 - 65 535 | 长文本数据 |
| mediumblob | 0 - 16 777 215 | 二进制形式的中等长度文本数据 |
| mediumtext | 0 - 16 777 215 | 中等长度文本数据 |
| longblob | 0 - 4 294 967 295 | 二进制形式的极大文本数据 |
| longtext | 0 - 4 294 967 295 | 极大文本数据 |
| 日期时间类型 | 大小(byte) | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| data | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| time | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
| year | 1 | 1901至2155 | YYYY | 年份值 |
| datatime | 8 | 1000 - 01 - 01 00:00:00 至 9999 - 12 - 31 23:59:59 | YYYY-MM-SS | 混合时间和日期值 |
| timestamp | 4 | 1970 - 01 -01 00:00:01 至 2308 - 01 -19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
2.3 表查询
# 1.查看当前数据库下的表
show tables;
# 2.查看指定表结构
desc 表名;
# 3.查看数据库的建表语句
show create table 表名;
2.4 表修改
# 1.添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]；
# 2.修改字段类型
alter table 表名 modify 字段名 新数据类型(长度)；
# 3.修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束]；
# 4.删除字段
alter table 表名 drop column 字段名；
# 5.修改表名
rename table 表名 to 新表名；
2.5 表删除
# 删除表
drop table [if exists] 表名;
# 注意：在删除表时，表中的全部数据也会被删除。
三、 DML 数据操作语言
- Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。
3.1 insert语法
# 1.指定字段添加数据
insert into 表名(字段名1，字段名2) values(值1，值2);
# 2.全部字段添加数据
insert into 表名 values(值1，值2，....);
# 3.批量添加数据(指定字段)
insert into 表名(字段名1，字段名2) values(值1，值2),(值1，值2);
# 4.批量添加数据(全部字段)
insert into 表名 values(值1，值2，....),(值1，值2，....)；
3.2 update语法
#修改数据
update 表名 set 字段名1 = 值1,字段名2 = 值2,....[where 条件]；
# 注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
3.3 delete语法
# 删除数据
delete from 表名 [where 条件];
# 注意1：delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
# 注意2：delete语句不能删除某一个字段的值（如果要操作，可以使用update，将该字段的值置为null）。
四、 DQL 数据查询语言
- Data Query Language(数据查询语言)，用来查询数据库表中的记录。
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
4.1 基本查询
# 1.查询多个字段
select 字段1,字段2,字段3 from 表名;
# 2.查询所有字段(通配符)
select * from 表名;
# 注意：*号代表查询所有字段，在实际开发中尽量少用(不直观、影响效率)。
# 3.设置别名
select 字段1 [as 别名1],字段2 [as 别名2] from 表名;
# 4.去除重复记录
select distinct 字段列表 from 表名；
4.2 条件查询
# 条件查询
select 字段列表 from 表名 where 条件列表；
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| between…and… | 在某个范围之内(含最小、最大值) |
| in(…) | 在in之后的列表中的值，多选一 |
| like 占位符 | 模糊匹配( _匹配单个字符，%匹配任意个字符) |
| is null | 是null |
| is not null | 不是null |
| 逻辑运算符 | 功能 |
|---|---|
| and 或 && | 并且(多个条件同时成立) |
| or 或 || | 或者(多个条件任意一个成立) |
| not 或 ！ | 非，不是 |
4.3 分组查询
# 分组查询
select 字段列表 from 表名 [where条件] group by 分组字段名 [having 分组后过滤条件];
#例：查询入职时间在‘2015-01-01’(包含)以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位。
select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >=2;
-
注意：
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
- 执行顺序：where > 聚合函数> having
-
where与having区别
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
# 聚合函数：将一列数据作为一个整体，进行纵向计算。
select 聚合函数(字段列表) from 表名;
| 聚合函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
- 注意：
- null值不参与所有聚合函数运算
- 统计数量可以使用：count()、count(字段)、count(常量)，推荐使用count()。
4.4 排序查询
select 字段列表 from 表名[where 条件列表][group by 分组字段] order by 字段1 排序方式1，字段2 排序方式2...;
-
排序方式
- ASC：升序(默认值)
- DESC:降序
-
注意
- 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段排序。
4.5 分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;
- 注意：
- 起始索引从0开始，起始索引 = （页码 - 1）* 每页展示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
- 如果查询的是第一页数据，其实索引可以省略，直接简写为limit 10。（查询10条记录）
补充的函数：
if(表达式,tvalue，fvalue):当表达式为true时，取值tvalue；当表达式为false时，取值fvalue
case expr when value1 then result1[when value2 then value2...][else result] end
五、 多表设计
5.1 一对多
- 关系实现：在数据库表中多的一方，添加字段，来关联一的一方的主键。
# 外键语法
# 1.创建表时指定
create table 表名(
字段名 数据类型，
...
[constraint] [外键名称] foreign key(外键字段名) references 主表(字段名)
);
# 2.建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);
物理外键
- 概念：使用foreign key定义外键关联另外一张表。
- 缺点：
- 影响增、删、改的效率（需要检查外键关系）。
- 仅用于单字节数据库，不适用与分布式、集群场景。
- 容易引发数据库的死锁问题，消耗性能。
逻辑外键（推荐）
- 概念：在业务层逻辑中，解决外键关联。
- 通过逻辑外键，就可以很方便的解决上述问题。
5.2 一对一
- 一对一关系多用于单表拆分，将一张表的基础字放在一张表中，其他字段放在另一张表中，以提升操作效率。
实现：在任意一方加入外键，关联另外一方的主键，并且设置为唯一的（UNIQUE）
5.3 多对多
- 案例：学生 与 课程 的关系
- 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择。
- 实现：建立第三张表，中间表至少包含两个外键，分别关联两方主键。
5.4 设计表结构的步骤：
- 阅读页面原型及需求文档，分析各个模块涉及到的表结构，及表结构之间的关系。
- 根据页面原型及需求文档，分析各个表结构中具体的字段及约束。
六、 多表查询
- 多表查询：从多张表中查询数据
- 笛卡尔积：笛卡尔乘积是指在数学中，两个集合的所有组合情况。
6.1 连接查询
6.1.1 内连接
- 相当于查询两个表的交集部分数据
# 1.隐式内连接
select 字段列表 from 表1,表2 where 条件；
# 2.显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
6.2.2 外连接
- 左外连接：查询左表所有数据（包括两张表交集部分数据）
- 右外连接：查询右表所有数据（包括两张表交集部分数据）
# 1.左外连接
select 字段列表 form 表1 left [outer] join 表2 on 连接条件；
# 2.右外连接
select 字段列表 from 表1 right [outer] join 表2 on 连接条件；
6.2 子查询
- SQL语句中嵌套select语句，成为嵌套查询，又称子查询。
- 子查询外部的语句可以是insert/update/delete/select 的任何一个，最常见的是 select。
# 形式
select * from t1 where colunm1 = (select colunm1 from t2);
- 分类：
- 标量子查询：子查询返回的结果为单个值
- 列子查询：子查询返回的结果为一列
- 行子查询：子查询返回的结果为一列
- 表子查询：子查询返回的结果为多行多列
6.2.1 标量子查询
- 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式
- 常用的操作符：=、<> 、> 、 <、<=
6.2.2 标量子查询
- 子查询返回的结果是一列（可以是多行）
- 常用的操作符：in 、not in 等。
6.2.3 行子查询
- 子查询返回的结果是一行（可以是多列）。
- 常用的操作符：=、<>、in、not in
6.2.4 表子查询
- 子查询返回的结果是多行多列，常作为临时表
- 常见的操作符：in
七、 事务
7.1 介绍
- 事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即把这些操作要么同时成功，要么同时失效。
- 注意： 默认Mysql的事务是自动提交的，也就是说，当执行一条DML语句，Mysql会立即隐式的提交事务。
# 事务控制
# 开启事务
start transaction;/begin;
# 提交事务
commit;
# 回滚事务
rollback;
7.2 事务的四大特性(ACID)
- 原子性：事务是不可分割的最小单元，要么全部成功，要么全部失败。
- 一致性：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
八、 索引
8.1 介绍
-
索引（index）是帮助数据库高效获取数据的数据结构。
-
优点
- 提高查询的效率，降低数据库的IO成本。
- 通过索引对数据进行排序，降低数据排序的成本，降低CPU消耗。
-
缺点
- 索引会占用存储空间。
- 索引大大调高了查询效率，同事却也降低了insert、update、delete的效率。
8.2 结构
- MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平常所有的索引，如果没有特别指明，都是默认的B+Tree结构组织的索引。(MySQl数据库中默认的索引结构是B+Tree。)
- B+Tree（多路平衡搜索树)
- 每一个节点，可以存储多个key（有n个key，就有n个指针）。
- 所有数据都存储在叶子节点，非叶子节点仅用于索引数据。
- 叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询。
8.3 语法
# 1.创建索引
create [unique] index 索引名 on 表名(字段名);
# 2.查看索引
show index from 表名;
# 3.删除索引
drop index 索引名 on 表名;
- 注意
- 主键字段，在建表时，会自动创建主键索引。
- 添加唯一约束时，数据库实际上会添加唯一索引。