MySQL高级

君擎

已于 2023-09-21 16:28:56 修改

阅读量112

点赞数

文章标签： mysql

于 2023-09-16 16:55:21 首次发布

本文链接：https://blog.csdn.net/qq_64225133/article/details/132920848

版权

一、基础部分复习

1.修改表结构

添加列
```
alter table 表名 add 列名 类型
```

修改列

--只修改类型--
alter table 表名 modify 字段名 类型
--修改字段名和类型--
alter table 表名 change 旧字段名 新字段名 类型

删除字段
```
alter table 表名 drop 字段名
```

修改表名

alter table 旧表名 rename to 新表名

2.查询语句

基本查询结构：

select [distinct] 查询字段 from 表名 where 查询条件
									group by 分组字段
									having 分组后的条件
									order by 排序字段 排序条件
									limit 起始索引，记录数

语句执行的顺序：

第一步：from 语句

第二步：where 语句

第三步：group by 语句

第四步：having 语句

第五步：select 语句

第六步：order by 语句

第七步：limit 语句

3.查询语句注意事项

模糊匹配like的使用：_表示单个字符，%表示多个字符

值为null的数据不参与聚合函数的运算

where与having的区别：

执行的时机不同，where是分组之前过滤，having是分组之后过滤
判断条件不同：where不能对聚合函数进行判断，having可以

asc是升序（默认），desc是降序

排序可以针对多个字段

分页查询：

起始索引=（查询页码-1）×每页显示数

注：如果查询第一页，起始索引可以省略

分组查询的注意事项：

执行顺序：where>聚合函数>having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段没有意义

4.用户管理（DCL）

查询用户
```
use mysql；
select * from user;
```

创建用户

<--创建用户,这个时候只有查询权限-->
create user '用户名'@'主机名' identified by '密码';
<-- 主机名如果是%则可以远程访问-->

分配权限

<--查询权限-->
show grants for '用户名'@'主机名'
<--授予权限-->
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'
<--撤销权限-->
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'

修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户
```
drop user '用户名'@'主机名'
```

5.时间函数

rand( )：返回一个0~1内的随机函数
curdate( )：返回当前日期
curtime( )：返回当前时间
Now( )：返回当前日期和时间
year(date)：获取date中的年
month(date)：获取date中的月
day(date)：获取date中的日
date_add(date，interval 时间间隔)：返回一个时间加上指定间隔后的日期

interval是固定的格式，时间间隔的类型可以是day，month，year
datediff()：返回date1和date2相差的天数

6.流程函数

if(value1，值1，值2)：如果value1为true，返回值1，否则返回值2
ifnull(value1，value2)：如果value1不为空，返回value1，否则返回value2
case when value1 then res1 else default end :如果value1的值为true，返回res1，否则返回默认值default
case expr when value1 then res1 else default end:如果表达式expr的值等于value1，返回res，否则返回默认值default
上面这两个表达式的when和then可以写多个

例如：
```
select name,(case workaddress when '北京' then
 '一线' when '上海' then '一线' else '二线' end) as '工作地址' from emp
```

7.约束

只记录不太熟悉的约束，unique、not null等就不再记录了

check：自定义约束

例如：年龄规定要大于0岁，小于150岁
create table emp（
	...,
	age int check(age>0 && age<=120),
	...
）

default：设置默认值

例如：添加用户的时候要求status的值默认为1
create table emp（
	...,
	status char(1) default '1',
	...
）

8.外键

说明：外键只能在innodb存储引擎下设置

8.1 创建外键的方式

方式1：创建表的时候就定义
create table emp（
	[constraint] [约束名称] froeign key (外键字段) references 关联的主表(关联的字段)
）
方式2：使用alter语句定义
alter table 表名 add constraint 约束名称 foreign key (外键字段) references 主表(关联的字段)

8.2 删除外键

alter table 表名 drop foreign key 外键约束名

8.3 少用外键的原因

有一部分公司不推荐使用外键，可以举一个案例：

有一个外卖项目，这个项目中有菜品和套餐。菜品的主键为菜品的id，套餐表的外键是菜品的id。

添加套餐之后会关联上相关的菜品，如果这个时候需要删除菜品，那么就需要删除关联的套餐，否则就会删除不了。

8.4 外键删除行为

no action和restrict：这两个作用相同，会在主表删除数据的时候检查是否有对应的外键，如果有会禁止删除
cascade：会在主表删除数据的时候检查是否有对应的外键，如果有会同时删除从表记录
setnull：会在主表删除数据的时候检查是否有对应的外键，如果有会同时将子表对应中的部分置为空

9.多表查询

9.1 多表查询的种类

9.2 内连接

第一种：隐式内连接

基本语法
select 字段 from 表1,表2 where 条件

第二种：显式内连接

基本语法
select 字段 from 表1 [inner] join 表2 on 条件

9.3 外连接

第一种：左外连接

基本语法
select 字段 from 表1 left join 表2 on 条件

第二种：右外连接

基本语法
select 字段 from 表1 right join 表2 on 条件

9.4 自连接

使用说明：必须给别名，可以使用内连接方式，也可以使用外连接方式

显式内连接方式：
select 字段 from 表1 别名1 inner join 表2 别名2 on 条件
例如：
select a.name,b.age from emp a inner join emp b where a.no=b.no

10.联合查询

联合查询介绍：把多次查询的结果合并起来，形成一个心得查询结果

使用的关键字：union【去重】、unionall【不去重】

要求：每次查询出来的字段的类型和名字一致

使用说明：union会自动去除重复的数据，unionall会将两次的查询结果直接合并【因此不会去重】

使用语法：

select 字段 from 表1 union[all] select 字段 from 表2

使用案例：将工资低于500和年龄大于18的人查询出来

select * from emp where salary > 500 union select * from emp where age > 18

11.子查询

查询的条件是一个查询语句(通俗来说就是where 后面的条件是一个查询语句)

例如：
select * from emp where sno=(select sno from sc)

11.1 子查询种类

根据子查询结果分类

根据子查询位置分类：where之后、from之后、select之后

11.2 标量子查询

子查询的结果为单行单列的单个值

例子：查询”销售部“的员工信息

select * from emp where dept_id=(select id from dept where name='销售部')

11.3 列子查询

子查询的结果为单列多行，常用操作符：in、not in 、any、some、all

说明：in和not in 表示是否在里面，any表示有任意一个即满足，all表示全部的必须都满足

例子：查询“销售部”和“市场部”的员工信息

select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部')

例子：查询比财务部所有人工资都搞的员工信息

select * from emp where salary > all(select salary from emp where dept_id=(select id from dept where name ='财务部'))

例子：查询比研发部任意一人工资高的员工信息

select * from emp where salary > any(select salary from emp where dept_id=(select id from dept where name='研发部'))

11.4 行子查询

子查询的结果为一行

常用操作符：=、<>、in、not in

例子：查询与“张无忌”的薪资及直属领导相同的员工信息

--使用括号包起来，这样就对应了查询的结果--
select * from emp where (salary,mangerid)=(select salary,mangerid from emp where name='张无忌')

11.5 表字查询

子查询的结果是多行多列

常用操作符：in

例子：查询与“张无忌”、“宋远桥”的薪资和职位相同的员工信息

select * from emp where (salary,mangerid) in (select salary,mangerid from emp where name='张无忌' or name='宋远桥')

例子：查询入职日期是“2006-01-01”之后的员工信息，及其部门信息

--先查询员工信息，再查询部门信息--
select e1.*,d1.* from (select * from emp where entrydate > '2006-01-01') as e1 left join dept d1  on d1.id=e1.dept_id

11.5 总结

一方的外键一定是另一方的主键
如果子查询需要上一级的字段，可以给父查询设置别名，然后将字段写到子查询的语句里面

12.事务

事务的概念：一组操作的集合，是一个不可分割的单位，要么同时成功，要么同时失败

典型案例：银行转账

注意事项：MySQL事务默认自动提交

12.1 使用事务的方式

12.1.1 方式一：设置提交方式

查看当前事务提交方式：
```
select @@autocommit
```

设置事务提交方式

--说明：值为1则自动提交，值为0则手动提交--
set @@autocommit=值

提交事务
```
commit
```
回滚
```
rollback
```

12.1.2 方式二：开启事务

开启事务：
```
start transaction 或者 begin
```
提交事务
```
commit
```
回滚事务
```
rollback
```

12.2 事务的四大特性

原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
一致性：事务完成时，必须使所有的数据都保持抑制状态
隔离性：保证事务在不受外部并发操作影响的状态下独立运行
持久性：事务一旦提交或者回滚，他对数据库中的数据的改变是永久的

12.3 事务并发问题

事务的并发会产生下面的三个问题

问题	解释
脏读	一个事务读取到了另一个事务还没有提交的数据
不可重复读	一个事务读取同一条记录，但是记录的数据不同
幻读	一个事务查询数据的时候，没有对应的数据，但是插入这个数据的时候，又发现这个数据已经存在了。好像是出现了”幻影“一样

12.4 事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交		√	√
可重复读			√
串行化

说明：

读未提交性能最好，但是出现的问题最多；串行化性能最差，但是不会出现事务并发问题
orcal数据库默认的事务隔离级别是读已提交
MySQL数据库默认的隔离界别是可重复读，已经足够我们去使用，所以一般不会去改变

12.5 事务隔离级别的相关命令

查看当前事务隔离级别：
```
select @@transaction_isolation
```

设置事务隔离级别：

set [session | gloable] transaction isolation level [四个隔离界别选一个]

二、MySQL高级

1.存储引擎

1.1 存储引擎简介

存储引擎的概念：存储引擎就是存储数据、简历索引，更新/查询数据等技术的实现方式
存储引擎是基于表的
默认存储引擎是InnoDB

指定存储引擎的方式：

create table 表名 (...) engine=引擎类型

查看当前数据库支持的所有引擎：
```
show engines;
```

1.2 三种主要存储引擎介绍

1.2.1 InnoDB引擎

InnoDB引擎介绍：
- InnoDB是高可靠和高性能的通用存储引擎。
- 在MySQL5.5之后，InnoDB是默认的MySQL引擎
特点：
- DML（增删改）操作遵循ACID模型，支持事务
- 支持行级锁，提高并发访问性能
- 支持外键 foreign key约束，保证数据的完整性和正确性
- 文件类型是xxx.ibd类型（xxx是表名，innodb的每张表都会对应这样一个表空间）
- 逻辑存储结构：
  - 1个区包含16个页
  - 磁盘操作的最小单位是页
  存储结构大小
  tableSpace：表空间包含多个段
  segment：段包含多个区空间
  extent：区 1M 包含多个页
  page页 16k 包含多行
  row 包含多个字段

存储结构	大小
tableSpace：表空间		包含多个段
segment：段		包含多个区空间
extent：区	1M	包含多个页
page页	16k	包含多行
row		包含多个字段

1.2.2 MyISAM引擎

MyISAM引擎介绍：是MySQL早期的默认存储引擎
特点：
- 不支持事务
- 不支持外键
- 支持表级锁，不支持行级锁
- 访问速度快
文件结构：
- xxx.sdi ：表结构
- xxx.myd ：存储数据
- xxx.myz :存储索引

1.2.3 Memory引擎

Memory引擎介绍：表中的数据存储在内存中，因此只能作为临时表
特点：
- 数据存放在内存中，访问速度快
- 支持hash索引
文件结构：xxx.sdi存储表结构文件

1.3 三种存储引擎特点

1.4 面试题：InnoDB和MyISAM存储引擎的区别？

innodb引擎支持事务，myISAM不支持事务
innodb支持行级锁，myISAM只支持表级锁
innodb支持外键，myISAM不支持外键

1.5 存储引擎的选择和使用场景

大部分场景下都使用innodb引擎，因为MyISAM引擎和Memory引擎都有替代产品

InnoDB引擎：如果对事务的完整性有较高要求、在并发条件下要求数据的一致性，插入、查询、更新、删除都是用的多，那么使用这个引擎
MyISAM引擎：如果以插入和查询为主，更新删除使用的少，并且对事务的完整性、并发性的要求不是很高，那么选择这个

MyISAM引擎一般不会使用，因为他有替代产品mongodb数据库
Memory引擎：通常用于临时表及缓存

Memory引擎一般也不会使用，因为可以使用Redis替代

2.索引

2.1 索引概述

索引是帮助MySQL高效获取数据的有序数的据结构

没有索引会发生全表扫描，性能极低。有索引不会发生全表扫描，效率高。

如果没有特别说明，以后指的索引都是B+Treey引擎

2.2 索引的优缺点

优点：
1. 提高查询效率，降低数据库IO成本
2. 降低数据排序的成本，降低CPU的消耗
缺点：
1. 索引也需要占用硬盘的存储空间
2. 大大提高查询效率的同时，也降低了表更新的速度

2.3 索引结构介绍

MySQL的索引是在存储引擎层实现的（Innodb的图有数据库的存储结构），不同的存储引擎有不同的结构

主要包含以下几种：

B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
Hash索引：底层使用哈希表实现的，只有精确查询才有效（=，in），不支持范围查询（between，>,<等）
R-Tree索引（空间索引）：是MyISAM引擎的一种特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）：通过建立倒排索引，快速匹配文档，使用较少

2.4 存储引擎对索引的支持情况

2.5 索引的基本数据结构

2.5.1 二叉树

2.5.2 B-Tree(多路平衡查找树)

前提知识：数的度数指的是一个节点的子节点个数

例如：5度就是4个key，然后分成了5个指针

说明：以此题为例，4个数据会有5个指针。小于20走第一个指针，20-30走第二个而指针，30-62走第三个指针，62-80走第四个指针，大于80走第五个指针。
B树的生成案例：以5度B树为例，一开始没有数据，先插入4个数据，这4个数据会在一个节点，然后当插入第五个数据的时候，这5个数据会排序，然后中间的那个数据会向上分裂，生成一个新的节点，这个数据两边的数据会分裂成两个节点，挂到新节点两边，依次类推。

2.5.3 B+Tree

基本结构和B树一样（节点不存储数据，下面形成的链表才存储数据）。

以最大度数为4的B+tree为例：4阶就是3个key，4个指针

说明：
1. 所有的元素都会出现在叶子节点（最下面那一层）
2. 非叶子节点部分（最后一层往上的），只起到索引作用
3. 叶子节点用来存放数据【下面挂的绿色的】
4. 叶子节点会形成单向链表，每个叶子节点指向下一个叶子节点
案例：5阶B+树为例
B树和B+树的区别：
1. B+树所有的元素都会出现在叶子节点
2. B+树叶子节点会形成单向链表，每个叶子节点指向下一个叶子节点

2.5.4 hash索引

基本介绍：采用一定的hash算法，将键值换算成心得hash值，映射到对应的槽位上，然后存储到hash表中
说明：多个key映射到同一个槽位上，就会产生hash冲突，可以通过链表解决
hash索引的特点：
1. Hash索引只能用于对等比较（例如：=，in），不支持范围查询between，>，<
2. 无法利用索引完成排序
3. 查询效率高，通常只需要依次检索就可以找到数据，效率高于B+树索引
4. 只有memory支持hash索引，而InnoDB引擎中具有自适应hash功能，会自动根据B+树构成hash索引