mysql基础知识复习
一、数据类型
- 整数类型
| 数据类型 | 字节数 |
|---|---|
| INT | 4 |
| BIGINT | 8 |
| … |
- 浮点类型和定点类型
| 数据类型 | 字节数 |
|---|---|
| FLOAT | 4 |
| DOUBLE | 8 |
| … |
- 字符串类型
| 数据类型 | 字节数 |
|---|---|
| VARCHAR(N) 可变长度的 | 根据具体的存储数值长度决定 |
| CHAR(N) 不可变长的 | N |
- 文本类型(用于表示大量文本数据)
| 数据类型 | 字节数 |
|---|---|
| TEXT | 0-65535 |
| … |
- 日期与时间类型
| 数据类型 | 字节数 | 格式 |
|---|---|---|
| YEAR | 1 | YYYY |
| DATE | 4 | YYYY-MM-DD |
| TIME | 3 | HH:MM:SS |
| DATETIME | 8 | YYYY-MM-DD HH:MM:SS |
| … |
- 二进制类型（存储图片等数据）
| 数据类型 | 存储范围(字节) |
|---|---|
| BLOB | 0-65535 |
| … |
二、数据库的基本操作
- 创建数据库
create database 数据库名称;
- 删除数据库
drop database 数据库名称;
- 查看所有数据库
show databases;
- 切换数据库
use 数据库名称;
- 执行sql脚本
source [脚本路径]
三、数据表的基本操作
- 创建数据表
create table 表名称(
字段1 类型,
字段2 类型,
..
)
- 查看所有表
show tables;
- 查看表结构
desc 表名称;
- 修改数据表
alter table 表名称 rename to 修改后名称; --修改表名称
alter table 表名称 change 字段名称 修改后字段名称 类型; --修改字段名称和类型
alter table 表名称 modify 字段名称 类型; --修改字段类型
alter table 表名称 add 添加字段 类型; --添加字段
alter table 表名称 drop 删除的字段;
- 删除数据表
drop table 表名称;
四、数据表的约束
| 约束 | 作用 |
|---|---|
| PRIMARY KEY | 主键约束（加上auto_increment表示自动增长） |
| FOREIGN KEY | 外键约束 |
| NOT NULL | 非空约束 |
| UNIQUE | 唯一约束 |
| DEFAULT | 默认值约束 |
五、DML 数据操作语言
1，添加数据
insert into 表名称(字段1,字段2,...) values(值1,值2,...);
insert into 表名称(字段1,字段2,...) values(值1,值2,...),(值1,值2,...),...; --同时添加多条数据
2，更新数据
update 表 set 字段1 = 值1,字段2 = 值2,... where 条件字段 = 条件值;
update 表 set 字段1 = 值1,...; --无条件地修改所有记录的值
3，删除数据
delete from 表 where[条件];
delete from 表; --删除表中所有数据
--------------
truncate 表; --清空表中所有数据
4，基础查询
- 简单查询(略)
select distinct * from 表; --distinct用于过滤相同的记录
-
条件查询
- IN 是否在集合中
select * from 表 where 字段 in (v1,v2,...);- BETWEEN AND 是否某个范围内
select * from 表 where 字段 BETWEEN v1 AND v2;- AND
- OR
- LIKE
select * from 表 where 字段 LIKE '%aaa'; --通配符'%'表示任意长度的字符串 select * from 表 where 字段 LIKE '_aaa'; --通配符'_'表示单个任意字符- LIMIT
select * from 表 limit 结果数量; --限制查询结果的记录数量- GROUP BY
- WHERE和HAVING
这两个关键字都用于表示过滤条件。
当过滤条件中有聚合函数时，必须使用HAVING；
当过滤条件中没有聚合函数时，WHERE和HAVING都可以使用，但是推荐使用WHERE，因为WHERE的效率更高；
HAVING通常都使用在GROUP BY后面；
#推荐写法： select 字段1,max(字段2),... from 表1 where 条件表达式1 group by 字段3 having 有关聚合函数的条件表达式; #不推荐写法： select 字段1,max(字段2),... from 表1 group by 字段3 having 有关聚合函数的条件表达式 and 条件表达式1;
5，多表查询
数据准备
student表：
teacher表：
5.1、笛卡尔积
多表查询的时候如果没有条件限制，各表中的每条数据都会和其他表的每条数据匹配一遍。这样的查询结果集就是产生了笛卡尔积，这种结果集一般没什么意义。
select * from student join teacher;
结果集（3*7）：
5.2、内连接
合并多个表的行，但结果集中只包含符合条件的的数据。
select * from student s join teacher t on s.teacher_id = t.id;
结果集：
5.3、左外连接
结果集中除了包含内连接的记录外，还包含左表（在left join关键字左边的表）中不满足条件的所有记录。
先添加点数据：
INSERT INTO student(name,teacher_id) VALUES("小秦",10),("小玄",11),("小迪",12);
INSERT INTO teacher(name) VALUES("秦老师"),("珍老师"),("奥老师");
SELECT * FROM student s LEFT JOIN teacher t ON s.teacher_id = t.id;
结果集：
5.4、右外连接
结果集中除了包含内连接的记录外，还包含右表（在left join关键字右边的表）中不满足条件的所有记录。
SELECT * FROM student s right JOIN teacher t ON s.teacher_id = t.id;
结果集：
5.5、其他连接1
SELECT s.id stu_id,s.`name` stu_name,t.`name` tea_name
FROM student s
LEFT JOIN teacher t
ON s.teacher_id = t.id
WHERE t.`name` IS NULL;
结果集：
5.6、其他连接2
SELECT t.id tea_id,t.`name` tea_name,s.id stu_id,s.`name` stu_name
FROM student s
RIGHT JOIN teacher t
ON s.teacher_id = t.id
WHERE s.id IS NULL;
结果集：
5.7、其他连接3（满外连接）
SELECT s.id stu_id,s.`name` stu_name,t.`name` tea_name,t.id tea_id
FROM student s
LEFT JOIN teacher t
ON s.teacher_id = t.id
WHERE t.`name` IS NULL
UNION ALL
SELECT s.id stu_id,s.`name` stu_name,t.id tea_id,t.`name` tea_name FROM student s right JOIN teacher t ON s.teacher_id = t.id;
结果集：
5.8、其他连接4
SELECT t.id tea_id,t.`name` tea_name,s.id stu_id,s.`name` stu_name
FROM student s
RIGHT JOIN teacher t
ON s.teacher_id = t.id
WHERE s.id IS NULL
UNION ALL
SELECT s.id stu_id,s.`name` stu_name,t.id tea_id,t.`name` tea_name
FROM student s
LEFT JOIN teacher t
ON s.teacher_id = t.id
WHERE t.`name` IS NULL;
结果集：
6、子查询
查询套查询，内层查询的结果集作为外层查询的条件参数。
重点在于根据实际业务灵活使用。
六、sql的执行流程
编写顺序
sql92语法：
select ...#可能包含聚合函数
from ...
where 多表连接的条件 and 过滤条件(不包含聚合函数)
group by ...
having 过滤条件(包含聚合函数)
order by ...(asc/desc)
limit ...
sql99语法
select ...#可能包含聚合函数
from ... (left/right) join ... on 多表连接的条件
(left/right) join ... on 多表连接的条件
...
where 过滤条件(不包含聚合函数)
group by ...
having 过滤条件(包含聚合函数)
order by ...(asc/desc)
limit ...
执行顺序
执行顺序：from、on、left/right join、where、group by、having、select、distinct、order by、limit
1，先执行from，如果有多个表，底层会先产生有笛卡尔积的结果集；
2，然后执行on进行连接条件的过滤；
3，然后再执行left/right join；
…(后面略，就是跟图上画的一样)
七、存储过程
一种数据库对象；类似定义函数。
八、触发器
一种数据库对象；对数据表进行操作时，自动触发自定义的操作。
进阶篇
1、mysql服务的体系结构
- 数据库__的本质实际上也是一个__应用程序。而它的功能是负责__组织数据__。分为关系型数据库（表）和非关系型数据库（其他）。
- MySQL是一种__数据库管理应用__。其他__数据库管理应用程序__还有诸如Oracle，SqlServer等
- __数据库管理应用程序__在__数据库__的基础上，扩展了一些其他功能，例如权限管理等
1、架构概况
- 架构图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dpN0MoUl-1678892176111)(https://gitee.com/lu-yunji/image/raw/master/MySQL%E6%9E%B6%E6%9E%84%E5%9B%BE.png)]
- 工作流程图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U4CGdLlW-1678892176111)(https://gitee.com/lu-yunji/image/raw/master/mysql%E5%B7%A5%E4%BD%9C%E6%B5%81%E7%A8%8B%E5%9B%BE.png)]
相关链接：MySQL架构原理(详解) - 腾讯云开发者社区-腾讯云 (tencent.com)
2、存储引擎
存储引擎是__数据库应用程序__中一个相对独立的模块，种类很多，它负责根据__解析后的sql__，调用自己的api，去操作__数据库__。
- 查看支持的引擎
show engines;
- 常用存储引擎和功能特点
| 功能 | Myisam | Memory | Innodb | Archive |
|---|---|---|---|---|
| 存储限制 | 256TB | RAM | 64TB | None |
| 事务 | 0 | 0 | 1 | 0 |
| 全文索引 | 1 | 0 | 0 | 0 |
| B-Tree索引 | 1 | 1 | 1 | 0 |
| Hash索引 | 0 | 1 | 0 | 0 |
| 数据缓存 | 0 | N/A | 1 | 0 |
| 外键 | 0 | 0 | 1 | 0 |
-
应用
-
查看数据库下表的存储引擎
show table status from [databasename] -
指定默认引擎（略）
-
建表时指定引擎
CREATE TABLE t1 (i INT) ENGINE = INNODB; -
修改表的存储引擎
ALTER TABLE t ENGINE = InnoDB;
-
2、索引
1、原理：索引的数据结构
索引是存储引擎的核心
相关链接：(73条消息) b+树详解_源头源脑的博客-CSDN博客
1、B+树
2、…
2、应用：索引的分类
- 按数据结构
| 索引 | 特点 | 说明 | 关键字 |
|---|---|---|---|
| b+tree | |||
| Hash | |||
| Full-text |
- 按物理存储
| 索引 | 特点 | 说明 | 关键字 |
|---|---|---|---|
| 聚集索引 | 索引中的key是表中的主键，value是表中的一条记录 | ||
| 非聚集索引（二级索引） | 索引中的key不是表中的主键，value是当前这条记录的主键 |
- 按功能特征
| 索引 | 特点 | 说明 | 关键字 |
|---|---|---|---|
| 主键索引 | 默认创建；是特殊的唯一性索引，UNIQUE+NOT NULL；一表一个 | PRIMARY | |
| 唯一索引 | 值唯一；允许为空值；一个表可以多个 | 添加唯一性约束的时候就是添加了唯一性索引 | UNIQUE |
| 普通索引 | 不附加其他限制条件；可以建立在任何数据类型的字段中； | INDEX | |
| 全文索引 | 只能创建在CHAR、VARCHAR、TEXT类型以及系列类型的字段上； | 搜索引擎的关键技术，使用分词技术等算法。 | FULLTEXT |
| 单列索引 | 作用在单个字段上。 | - | |
| 联合索引 | 作用在多个字段上；遵循__最左前缀原则__。 | - | |
| 空间索引 | 只能作用于空间数据类型（略）上。 | - |
3、应用：索引的相关操作
-
查看表的索引
SHOW INDEX FROM [表名] show create table [目标表格] --展示建表语句 -
添加索引
-
建表时添加索引（隐式）
--添加某些约束会默认创建索引。例如：UNIQUE、NOT NULL、PRIMARY KEY CREATE TABLE a( id INT PRIMARY KEY ) -
建表时添加索引（隐式）
--在建表语句的末尾显式地定义索引 --格式：[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名](字段[length]) [ASC|DESC] --字符串类型的字段可以指定一个取值长度建立索引 CREATE TABLE a( id INT UNIQUE INDEX indexName(id) ) -
向已有表添加索引
--方式1 ALTER TABLE [表名] ADD INDEX [索引名]([列名1],[列名2],...); --方式2 CREATE INDEX [索引名] ON [表名]([列1],...);
-
-
删除索引
ALTER TABLE [表名] DROP INDEX [索引名]; DROP INDEX [索引名] ON 表名; --注意：添加了auto_increment约束字段的唯一索引不能被删除。 --注意：删除联合索引的相关字段以后，联合索引会重置1、拓展：MySQL8.0索引新特性
-
降序索引；例如：
CREATE TABLE aandb( a INT, b INT, INDEX a_b(a DESC,b ASC) ) -
隐藏索引
当我们想删除一个索引的时候，先将索引设置为隐藏，即不生效。最后确认无误以后再真正地删除。这也叫软删除。
invisible
-
4、应用：索引的使用原则
1、适合添加索引的场景
-
1、字段的值在业务上有唯一性的约束。
-
2、频繁作为where语句的条件的字段，
-
3、经常GROUP BY 和 ORDER BY 的列
-
4、作为UPDATE和DELETE语句中WHERE的条件的字段
-
5、作为distinct去重的字段。（实操以后发现并没有使效率提高，不晓得是哪里有问题）
-
6、在业务允许的条件下，尽量使用数据类型小的（能够表示的数据范围大小）创建索引；因为数据类型越小，在查询时进行的比较操作就越快；索引占据的空间就越小，在一个数据页内就可以放下更多的记录。
-
7、对字符串类型的字段，使用字符串前缀创建索引。至于字符串前缀（又叫区分度）具体多长，公式如下：
select count(distinct a)/count(*) from t1 --越接近1，区分度越高 -
8、区分度高，也就是散列度高的字段适合创建索引。联合索引把区分度高的放在左侧。
-
9、使用最频繁的尽量放在联合索引的前面
-
10、在多个字段都要创建索引的条件下，联合索引优于单值索引。
-
11、一般一张表的索引数量不超过6个
2、不适合添加索引的场景
- where的条件当中几乎使用不到的字段，不加索引。
- 数据量小的表。
- 大量重复值（重复度高于10%）的字段。
- 经常做更新操作的字段和表。
- 不建议使用无序的值作为索引。索引的数据结构是有序的，无序值的插入可能会有页分裂的操作，拉低性能。
- 不要定义冗余的索引。
3、索引失效
- 范围查询
联合索引中，出现范围查询，则范围查询右边的列索引会失效。
为了避免出现这个问题，在业务允许的情况下，尽量使用’<=‘、’>='这种运算。
-
不要在索引列上进行运算操作，否则索引会失效
-
使用字符串类型的字段时，如果不给值加引号，这个字段的索引会失效。
-
字符串的头部模糊查询会使索引失效，尾部模糊查询就不会。
-
or涉及的任意字段条件中没有索引，那么所有索引都会失效
-
如果mysql评估使用索引比全表扫描还慢，那他会放弃使用索引。
4、注意点
- 多表join连接时，
- 连接表的数量不要超过3张；
- 要对where的条件字段创建索引；
- 对用于连接的字段创建索引，且该字段在多张表中的__类型必须一致__，否则索引会失效；
5、应用：sql性能分析
1、sql执行频率
#查看当前数据库各类操作的执行频率
show [session|global] status 条件语句;
2、慢查询日志
慢查询日志默认没有开启，需要再配置文件中开启
#查看慢查询日志的开关
show variables like'slow_query_log';
#在配置文件my.cnf中添加以下配置开启慢查询日志
# slow_query_log=1
#设置超时两秒视为慢查询
# long_query_time=2
日志文件存储目录（Linux）：/var/lib/mysql
3、profile详情
- 查看是否支持profile
show varibales like 'have_profiling';
- 开启profile
SET profiling = 1;
- 检查是否开启profiling
select @@profiling;
4、explain
#查看sql的执行计划
explain sql语句
3、sql优化
1、insert优化
- 尽量使用批量插入。
- 使用手动提交事务。
- 主键顺序插入。
2、load
#客户端连接服务器时，需要加上参数 --local-infile
mysql --local-infile -u rrot -p
#开启load功能
set global local_infile = 1;
#执行load
load data local infile '本地文件路径' into table `表名` fields terminated by '字段分隔符' lines terminated by '行分隔符';
注意：顺序插入的性能高于乱序插入。
3、主键优化
页分裂、页合并
- 降低主键长度
- 插入数据尽量选择顺序插入数据
- 不要使用类似uuid、身份证号这种自然主键
- 避免对主键的修改
4、order by优化
5、group by优化
6、limit优化
7、count函数优化
count函数没有什么有效的优化方法，不过计数功能不一定要使用count(),可以在增删数据库的时候进行累加，记录下这个变量。如果一定要用，count(*)性能较好。
8、update
使用update更新数据时候，如果条件语句中的根据是索引字段，则会行锁。如果不是索引字段，行锁会升级为表锁，要尽量避免，表锁一旦发生，并发性能严重降低。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
