Mysql

l_ethan

已于 2022-07-20 11:11:33 修改

阅读量393

点赞数

分类专栏：面试整理文章标签：大数据

于 2022-07-11 16:19:52 首次发布

本文链接：https://blog.csdn.net/l_ethan/article/details/125649170

版权

面试整理专栏收录该内容

6 篇文章 0 订阅

订阅专栏

数据类型

DECIMAL

依赖于M(精度)和D(标度)的值

精度就是数据总长度，标度是小数长度

CHAR	0-255 bytes	定长字符串	char(10) -----------> 性能好	用户名 username varchar(50)
VARCHAR	0-65535 bytes	变长字符串	varchar(10) ---------> 性能较差	性别 gender char(1)

char(10)就是10位字符串 varchar是根据字符串真实长度来申请空间

常用SQL语句

1.INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

insert into employee(id,workno,name,gender,age,idcard,entrydate) values(1,'1','Itcast','男',10,'123456789012345678','2000-01-01');

2.UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ]

update employee set name = 'itheima' where id = 1;

3.DELETE FROM 表名 [ WHERE 条件 ] ;

delete from employee where gender = '女';

DQL-数据查询

去除job中的重复元素

select distinct job from emp

查询job不为空
select * from emp where job is not null

查询job为空

select * from emp where job is null

A. 统计该企业员工数量

select count(*) from emp; -- 统计的是总记录数

select count(idcard) from emp; -- 统计的是idcard字段不为null的记录数

B. 统计该企业员工的平均年龄

select avg(age) from emp;

C. 统计该企业员工的最大年龄

select max(age) from emp;

D. 统计该企业员工的最小年龄

select min(age) from emp;

E. 统计西安地区员工的年龄之和

select sum(age) from emp where workaddress = '西安';

分组查询

1). 语法

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];

2). where与having区别执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项:

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

• 执行顺序: where > 聚合函数 > having 。 • 支持多字段分组, 具体语法为 : group by columnA,columnB

排序查询

1). 语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2

2). 排序方式 ASC : 升序(默认值) DESC: 降序

注意事项：

• 如果是升序, 可以不指定排序方式ASC ;

• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数

注意事项:

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

外键

多表关系

一对多

部门与员工的关系

关系: 一个部门对应多个员工，一个员工对应一个部门

实现: 在多的一方建立外键，指向一的一方的主键

多对多

案例: 学生与课程的关系

关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择

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

一对一

案例: 用户与用户详情的关系

关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

连接查询

内连接：相当于查询A、B交集部分数据

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

select e.name, d.name from emp e join dept d on e.dept_id = d.id;

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...

select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

右外连接：查询右表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...

select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

自连接：当前表与自身的连接查询，自连接必须使用表别名

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

联合查询:对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A ...

UNION [ ALL ]

SELECT 字段列表 FROM 表B ....;

union all查询出来的结果，仅仅进行简单的合并，并未去重。

union 联合查询，会对查询出来的结果进行去重处理。

子查询:

1.标量子查询

select * from emp where entrydate > (select entrydate from emp where name = '方东白');

2.列子查询

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 = '研发部') )

行子查询

select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

列子查询

select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

事务

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

控制事务一

1). 查看/设置事务提交方式

2). 提交事务

3). 回滚事务

SELECT @@autocommit ; SET @@autocommit = 0 ; 1 2 1 COMMIT; 1 ROLLBACK;

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

6.2.3 控制事务二

1). 开启事务 2). 提交事务 3). 回滚事务

开始:START TRANSACTION 或 BEGIN ;

提交:COMMIT;

撤回:ROLLBACK;

6.3 事务四大特性

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

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

持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。上述就是事务的四大特性，简称ACID。

并发事务问题

1). 赃读：一个事务读到另外一个事务还没有提交的数据。

2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

3). 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 "幻影"。

存储引擎

1.mysql体系结构

1). 连接层最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2). 服务层第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3). 引擎层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。

4). 存储层数据存储层，主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

1.3.1 InnoDB

1). 介绍 InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

2). 特点 DML操作遵循ACID模型，支持事务；行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

3). 文件 xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的、sdi-新版的）、数据和索引。

1.3.2 MyISAM

1). 介绍 MyISAM是MySQL早期的默认存储引擎。

2). 特点不支持事务，不支持外键支持表锁，不支持行锁访问速度快

3). 文件 xxx.sdi：存储表结构信息 xxx.MYD: 存储数据 xxx.MYI: 存储索引

1.3.3 Memory

1). 介绍 Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

2). 特点内存存放 hash索引（默认）

3).文件 xxx.sdi：存储表结构信息

索引

1.索引结构

B+树索引

思考题：

为什么InnoDB存储引擎选择使用B+tree索引结构?

A. 相对于二叉树，层级更少，搜索效率高；

B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

C. 相对Hash索引，B+tree支持范围匹配及排序操作；

hash索引

2.索引分类

聚集索引选取规则:

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

具体过程如下:

①. 由于是根据name字段进行查询，所以先根据name='Arm'到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③. 最终拿到这一行的数据，直接返回即可。

回表查询：这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

索引语法

1.创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ;

2.查看索引

SHOW INDEX FROM table_name ;

3.删除索引

DROP INDEX index_name ON table_name;

3.索引性能分析

-- 直接在select语句之前加上关键字 explain / desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

4.索引使用

最左前缀法则:如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

当范围查询使用>= 或 = 或 <=时，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引的。所以，在业务允许的情况下，尽可能的使用类似于 >= 或或 <=

索引失效的情况

1.不要在索引列上进行运算操作，索引将失效。

2.字符串不加引号

3.如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

4.用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

5.如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示

1). use index ：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

2). ignore index ：忽略指定的索引。

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

3). force index ：强制使用索引。

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

覆盖索引

尽量使用覆盖索引，减少select *。那么什么是覆盖索引呢？覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

原因:如果索引只是针对某一个列在进行一次查询后还会进行回表查询来查询出剩下的信息，因为如果仅有那一个索引是一个二级索引的叶子节点只返回一部分值。

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

create index idx_xxxx on table_name(column(n))

SQL优化

1.主键优化

满足业务需求的情况下，尽量降低主键的长度。

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。(乱序有可能发生页分裂)

尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

业务操作时，避免对主键的修改。

2.orderby优化

MySQL的排序，有两种方式： Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。 Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

B. 尽量使用覆盖索引。

C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。

D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

3.limit优化

优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id

4.count优化

5.update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁降低并发性。

视图

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法

检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL ，默认值为 CASCADED 。

1). CASCADED 级联。比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

2). LOCAL 本地。比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。则在执行检查时，知会检查v2，不会检查v2的关联视图v1

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。

如果视图包含以下任何一项，则该视图不可更新：

A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）

B. DISTINCT

C. GROUP BY

D. HAVING

E. UNION 或者 UNION ALL

4.1.5 视图作用

1). 简单视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2). 安全数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据 1 create view stu_v_count as select count(*) from student; 1 insert into stu_v_count values(10);

3). 数据独立视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

定义

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

1.创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])

BEGIN

-- SQL语句

END ;

2.调用

CALL 名称 ([ 参数 ])

3. 查看

SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

4.删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称

变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

1. 查看系统变量

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量

SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量 SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

2. 设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;

SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

用户自定义变量

1.赋值

SET @var_name = expr [, @var_name = expr] ... ;

SET @var_name := expr [, @var_name := expr] ... ;

SELECT @var_name := expr [, @var_name := expr] ... ;

SELECT 字段名 INTO @var_name FROM 表名;

2.使用

SELECT @var_name ;

注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块。

1.声明

DECLARE 变量名变量类型 [DEFAULT ... ] ;

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

2.赋值

SET 变量名 = 值 ;

SET 变量名 := 值 ;

SELECT 字段名 INTO 变量名 FROM 表名 ... ;

if

参数

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名参数类型 ])

BEGIN

-- SQL语句

END ;

循环

while

游标和条件处理程序

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name,profession from tb_user where age <=
uage;
-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
    declare exit handler for SQLSTATE '02000' close u_cursor;
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    open u_cursor;
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values (null, uname, upro);
    end while;
    close u_cursor;
end;
call p11(30);

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：
CREATE FUNCTION 存储函数名称 ([ 参数列表 ])

RETURNS type [characteristic ...]

BEGIN

-- SQL语句

RETURN ...;

END ;

characteristic说明：

DETERMINISTIC：相同的输入参数总是产生相同的结果

NO SQL ：不包含 SQL 语句。

READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

案例

create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;
    while n>0 do
    set total := total + n;
    set n := n - 1;
    end while;
    return total;
end;
select fun1(50);

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

1.创建

CREATE TRIGGER trigger_name

BEFORE/AFTER INSERT/UPDATE/DELETE

ON tbl_name FOR EACH ROW -- 行级触发器

BEGIN

trigger_stmt ;

END;

2.查看

SHOW TRIGGERS ;

3.删除

DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，默认为当前数据库。

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、 RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁，按照锁的粒度分，分为以下三类：

全局锁：锁定数据库中的所有表。

表级锁：每次操作锁住整张表。

行级锁：每次操作锁住对应的行数据。