Mysql 重要知识点1（含面试题1）

Mysql 知识点较多，这里涵盖了基本知识点，包括SQL语句、重要面试题等。后面还有几章Mysql的知识点，分别是刷题总结与进阶优化SQL 面试题等。

Mysql 安装

1.官网下载mysql5.7版本压缩包
https://dev.mysql.com/downloads/mysql/
2.解压到想要安装的目录
如：D:\Program Files\mysql-5.7.35-winx64

3.在D:\Program Files\mysql-5.7.35-winx64目录下创建data文件夹和my.ini配置文件。
4.编辑my.ini文件，并根据实际路径修改相应配置

[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#设置3306端口
port = 3306 
#设置mysql的安装目录
basedir=D:\Program Files\mysql-5.7.35-winx64
 设置mysql数据库的数据的存放目录
datadir=D:\Program Files\mysql-5.7.35-winx64\data
#允许最大连接数
max_connections=200
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#免密码登录
skip-grant-tables

5.将安装下的bin目录添加到环境变量
D:\Program Files\mysql-5.7.35-winx64\bin
6.初始化，用管理员权限打开命令行，切换到mysql的bin目录下
7.执行初始化命令

mysqld --initialize

8.执行安装MySQL命令

mysqld --install

9.安装不报错，启动MySQL服务

net start mysql

10.登录MySQL设置初始密码

mysql -u root -p

11.设置数据库密码
#切换到mysql数据库
use mysql
#设置数据库密码

update mysql.user set authentication_string=password('123456') where user='root';

12.重启MySQL服务

net stop mysql
net start mysql

13.报错：You must reset your password using ALTER USER statement before executing this statement.解决
ALTER USER USER() IDENTIFIED BY ‘123456’;

Mysql 重要知识点

关系型数据库（RDB，Relational Database）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

大部分关系型数据库都使用 SQL （是一种结构化查询语言(Structured Query Language)，专门用来与数据库打交道，目的是提供一种从数据库中读写数据的简单有效的方法。）来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。有哪些常见的关系型数据库呢？MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite）
Mysql 字段类型：
在这里插入图片描述
执行SQL 顺序：

连接器： 身份认证和权限相关(登录 MySQL 的时候)。查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。分析器：没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。优化器： **按照 MySQL 认为最优的方案去执行。**执行器：执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。# MySQL 存储引擎

通过 SHOW ENGINES 命令来查看 MySQL 支持的所有存储引擎.5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。支持行级锁(row-level locking) ,提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力,可支持外键，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！
在这里插入图片描述

使用 B+Tree 作为索引结构
为了保证数据的一致性，（比如：数据库中途突然因为某些原因挂掉了。客户端突然因为网络原因连接不上数据库了）系统必须能够处理这些问题。事务就是我们抽象出来简化这些问题的首选机制。
在这里插入图片描述
要么全部执行成功,要么全部不执行。语句：

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;

在这里插入图片描述
ACID特性： A、I、D 是手段，C 是目的
原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务的问题：（重要）：
1.脏读（Dirty)
1个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。
2.丢失修改
在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。
3.不可重复读
在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

发事务的控制方式
锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。
读写锁：
共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。
MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。
游标可以类比于C语言文件中的指针，可以根据需要使得选择对应的行。
游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。
这些都是固定的格式，对于游标使用还算是比较常规的。

SQL 重要语句

SELECT VERSION() 命令查看你的 MySQL 版本
SHOW VARIABLES LIKE '%storage_engine%'  查看 MySQL 当前默认的存储引擎。
# 开启查询缓存
set global  query_cache_type=1;
set global  query_cache_size=600000;
#查询各班的人数以及各种where
select d.班号 ,count(s.学号) as'人数' from depart2128 d,student2128 s where d.班号=s.班号 group by  d.班号

group by left(bo.图书编号,3) having  avg(b.定价)>30  以前3位数字进行分类并且均价大于30
where bo.图书名 like '%工程%' 查询书名包括“工程”关键词
where 定价=(select  max(定价) from  book2128)  询现有图书中价格最高的图书

# 建数据库
create  database  Library2128

#建表
create  table depart2128(
    班号 nvarchar(10) not null  primary key ,
    系名 nvarchar(10) not null
)

# 插入数据 （多个数据就（） 一行，最后分号结尾
insert  into student2128 (学号, 姓名, 性别, 出生日期, 班号)
values
('1',	'张任',      	'男',   	'1995-01-02'	,'0501'),
('2',	'程华',      	'男',   	'1996-01-10',	'0501')；

# 游标写法：

-- Test3-3
declare @bno char(20),@percent char(20),@price int # 声明变量
declare st_cursor cursor  # 对查询的结果 声明游标
	for select distinct book2128.图书名,book2128.定价
	from book2128
print 'BOOK                      Evaluate'
print '---------------------------------'
open st_cursor # 打开游标
fetch next from st_cursor into @bno,@price  读取数据
while @@FETCH_STATUS=0  #循环
begin
	set @percent= case
	when @price > 50 then 'A'
	when @price > 30 then 'B'
	when @price > 20 then 'C'
	when @price > 10 then 'D'
	else 'E'
	end
print @bno+'  '+@percent
fetch next from st_cursor into @bno,@price
end
close st_cursor   关闭游标
deallocate st_cursor  释放游标
go


# 表中字段更改： 
设置主键
alter table table10
add primary key (学号)

alter table  table10
add check(分数>0 and 分数<101)

alter table table11
add foreign key (借书人学号) references table10 (学号)

alter table student2128
add constraint  性别  check (性别 in ('男','女'))


# 事务： （可以不加go 直接查询）
-- Test 6-1
-- Test 6-2
begin transaction
    insert into depart2128 values ('2001','数学系')
    insert into depart2128 values ('2003','物理系')
    save transaction Mysavp
    insert into depart2128 values ('2004','化学系')
rollback transaction  Mysavp
select  * from depart2128

# 编写函数
--drop function nbook
create function  nbook(@ch nvarchar(10))
returns table
as
  return
    (select s.学号,s.班号,bo.图书名,br.借书日期
    from depart2128 d,student2128 s,borrow2128 br,book2128 bo
    where d.系名=@ch and d.班号=s.班号 and s.学号=br.学号 and bo.图书编号=br.图书编号)
--测试数据
select * from dbo.nbook('电子工程系')


# 存储过程 （注意和函数的区别）
create procedure  getBookNum(@ch nvarchar(10))
as select 图书名,count(*) as '数目' from book2128where 图书名=@ch group by 图书名
--测试数据
exec getBookNum 'C程序设计'

# 设置触发器 （类似一个表的感觉）
create  trigger  tribor on borrow2128 for insert,update
as
    insert  borrow1  --插入数据到borrow1表中
    select  ins.*
    from inserted ins,book2128 b
    where ins.图书编号=b.图书编号 and b.图书名='网络工程'
-- 测试
insert into borrow2128 values ('19','20090','2022-4-27')
go --查看数据
select * from borrow1
drop trigger tribor

## 修改更新
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

## 删除
DELETE FROM table_name
WHERE condition;

面试题精选

1.CHAR 和 VARCHAR 的区别是什么？

CHAR 和 VARCHAR 是最常用到的字符串类型，两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。
CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。

VARCHAR(100)和 VARCHAR(10)的区别是什么？

ARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时，就需要修改表结构才可以。虽说 VARCHAR(100)和 VARCHAR(10)能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，这也是很多人容易误解的一点。不过，VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。
推荐冗余10%的长度（因业务而异）。

3.NULL 和 ‘’ 的区别是什么？

’ '的长度是 0，是不占用空间的，而NULL 是需要占用空间的。
NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。

4.为什么 MySQL 采用 B+ 树作为索引？

持久化的，意味着数据（索引+记录）是保存到磁盘上的，因为这样即使设备断电了，数据也不会丢失。
非叶子节点data，只存储索引，可以放更多的索引。所有叶子节点之间都有一个链指针（顺序访问指针，可以提高访问的性能）。数据记录都存放在叶子节点中，查询性能稳定，查找最坏情况下到叶子节点。
B+树只需要去遍历叶子节点就可以实现整棵树的遍历。MySQL中的数据一般是放在磁盘中的，读取数据的时候肯定会有访问磁盘的操作
B+树空间利用率更高，可减少I/O次数，增删文件（节点）时，效率更高

或提问：数据库为什么使用B+树而不是B树
1、 B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
2、 B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
3、 B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
4、 B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
5、增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

5.级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。

6.InnoDB 有哪几类行锁？

记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

LIKE声明中的％和_是什么意思？

％对应于0个或更多字符，_只是LIKE语句中的一个字符。

8.主键索引与唯一索引的区别

1、主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
2、主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
3、唯一性索引列允许空值，而主键列不允许为空值。
4、主键列在创建时，已经默认为空值 ++ 唯一索引了。
5、一个表最多只能创建一个主键，但可以创建多个唯一索引。
6、主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

9.数据库三大范式是什么？

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

怎么优化SQL查询语句?

1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
2、 用索引可以提高查询
3、 SELECT子句中避免使用*号，尽量全部大写SQL
4、应尽量避免在 where 子句中对字段进行 is null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用 IS NOT NULL
5、 where 子句中使用 or 来连接条件，也会导致引擎放弃使用索引而进行全表扫描
6、 in 和 not in 也要慎用，否则会导致全表扫描

建立索引：
CREATE INDEX idx_name ON students (name);
修改索引：
ALTER TABLE employees
ADD INDEX idx_age (age);
删除索引：
DROP INDEX idx_age ON employees;

11.MySQL数据库cpu飙升的话，要怎么处理呢？

排查过程：
1、使用top 命令观察，确定是MySQLd导致还是其他原因。
2、如果是MySQLd导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。
3、找出消耗高的 sql，看看执行计划是否准确，索引是否缺失，数据量是否太大。
处理：
1、 kill 掉这些线程(同时观察 cpu 使用率是否下降)，
2、进行相应的调整(比如说加索引、改 sql、改内存参数)
3、重新跑这些 SQL。