MySQL常见面试题总结

最新推荐文章于 2024-06-11 00:27:20 发布

大可爱家的小可爱

最新推荐文章于 2024-06-11 00:27:20 发布

阅读量1.2k

点赞数 1

分类专栏： MySQL常见面试题总结文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/yuandenixin/article/details/121430571

版权

MySQL常见面试题总结专栏收录该内容

6 篇文章 0 订阅

订阅专栏

常用的MySQL语句

ALTER TABLE

ALTER TABLE用来更新已经存在的模式。如果要创建新的表应使用CREATE TABLE。

ALTER TABLE tablename
(
	ADD	    column			datatype	[NULL | NOT NULL]	[CONSTRAINTS],
	CHANGE	column columns	datatype	[NULL | NOT NULL]	[CONSTRAINTS],
	DROP	column,
	...
);

COMMIT

COMMIT用来将事务处理写到数据库。

CREATE INDEX

CREATE INDEX用于在一个或多个列上创建索引。

CREATE PROCEDURE

CREATE PROCEDURE用于创建存储过程。

CREATE PROCEDURE procedurename( [parameters] )
BEGIN
...
END;

CREATE TABLE

CREATE TABLE用于创建新的数据库表。为更新已经存在的表结构，应使用ALTER TABLE。

CREATE TABLE tablename
(
	column	datatype	[NULL | NOT NULL]	[CONSTRAINTS],
	column	datatype	[NULL | NOT NULL]	[CONSTRAINTS],
	...
);

CREATE USER

CREATE USER用于向系统中添加新的用户账户。

CREATE USER username[@hostname]
[IDENTIFIED BY [PASSWORD] 'password'];

CREATE VIEW

CREATE VIEW用来创建一个或多个表上的新视图。

CREATE [ON REPLACE] VIEW viewname
AS
SELECT ...;

DELETE

DELETE从表中删除一行或者多行。

DELETE FROM tablename
[WHERE ...];

DROP

DROP永久地删除数据库对象（表、视图、索引等）。

DROP DATABASE|INDEX|PROCEDURE|TABLE|TRIGGER|USER|VIEW
	itemname;

INSERT

INSERT给表增加一行。

INSERT INTO tablename [(columns, ...)]
VALUES(values, ...);

INSERT SELECT

INSERT SELECT插入SELECT的结果到一个表。

INSERT INTO tablename [(columns, ...)]
SELECT columns, ... FROM tablename, ...
[WHERE ...];

ROLLBACK

ROLLBACK用于撤销一个事务处理块。

ROLLBACK [ TO savepointname];

SAVEPOINT

SAVEPOINT为使用ROLLBACK语句设立保留点。

SAVEPOINT sp1;

SELECT

SELECT用于从一个或多个表（视图）中检索数据。

SELECT columnname, ...
FROM tablename, ...
[WHERE ...]
[UNION ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY];

START TRANSACTION

START TRANSACTION表示一个新的事务处理块的开始。

START TRANSACTION;

UPDATE

UPDATE更新表中一行或多行。

UPDATE tablename
SET columnname = value1, ...
[WHERE ...];

CHAR

CHAR（10）若输入数据的字符小于10，则系统自动在其后添加空格来填满设定好的空间。若输入的数据过长，将会截掉其中超出部分。VARCHAR（10）数据类型的存储长度为实际数值长度。

union

SQL语句中使用了or关键字索引会失效，在数据量较大的时候查找效率较低，因此可以使用union或union all代替。

join

left join、right join、inner join之间的区别：

left join是以左表为准的。左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为NULL；
right join和left join的结果刚好相反，以右表为基础，左表不足的地方用NULL填充；
inner join并不以谁为基础，它只显示符合条件的记录；

写法顺序：select–from–where–group by–having–order by
执行顺序：from–where–group by–having–select–order by
就是select要放后面，如果有order by，则order by放最后，因为order by 是对结果进行排序

Count

一般情况下，Select Count ()和Select Count(1)两者返回结果是一样的。假如表没有主键(Primary key),那么count(1)比count()快，如果有主键的话，那主键作为count的条件时候count(主键)最快，如果你的表只有一个字段的话那count()就是最快的。count() 跟 count(1) 的结果一样，都包括对NULL的统计，而count(column) 是不包括NULL的统计。

count(1)、count(*)、count(字段)之间的区别：

count(1)和count(*)：统计所有记录的条数，包括 NULL 值和重复项。
count(1)，同样会遍历，但不取值，引擎告诉不为空那我就 “+1”
count(字段)来针对某一特殊字段进行查询，不包含null。为了去除col列中包含的NULL行，SQL Server必须读取该col的每一行的值，然后确认下是否为NULL，然后在进行计数。

group by

GROUP BY表示根据哪个字段进行分组。select 的字段只能是分组的字段类别以及使用聚合函数如，max(),min(),count()的字段。

where在前，group by在后，注意group by紧跟在where最后一个限制条件后面，不能被夹在where限制条件之间：要先用where过滤掉不进行分组的数据，然后在对剩下满足条件的数据进行分组。

having是在分好组后找出特定的分组，通常是以筛选聚合函数的结果，如sum(a) > 100等，且having必须在group by 后面，使用了having必须使用group by，但是使用group by 不一定使用having。不允许使用双重聚合函数，所以在对分组进行筛选的时候可以用order by 排序，然后用limit也可以找到极值。

rank()

SELECT ROW_NUMBER() OVER( ORDER BY studentScore desc) number, *
FROM studentScore;
SELECT rank() OVER( ORDER BY studentScore desc) studentRank, *
FROM studentScore;

在这里插入图片描述

drop delete truncate

drop

drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

delete

delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

truncate

truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。

对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

truncatetable不能用于参与了索引视图的表。

drop delete truncate之间的区别

（1）DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2）表和索引所占空间。

当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，

DELETE操作不会减少表或索引所占用的空间。

drop语句将表所占用的空间全释放掉。

（3）执行速度上，一般而言，drop > truncate > delete

（4）应用范围。

TRUNCATE 只能对TABLE；
DELETE可以是table和view。

（5）TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）。

（6）truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7）delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

（8）truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

（9）在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（12）对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

总结

在速度上，一般来说，drop> truncate > delete。

在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

如果想删除表，当然用drop；

如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

如果和事务有关，或者想触发trigger，还是用delete；

如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

数据库三范式

一张数据表的表结构所符合的某种设计标准的级别。

第一范式（1NF）

即表的列的具有原子性，不可再分解。数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性。通俗理解即一个字段只存储一项信息。

要求数据库表的每一列都是不可分割的原子数据项。

举例说明：
在这里插入图片描述在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

第二范式（2NF）

确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

举例说明：
在这里插入图片描述
在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是订单号和产品号联合组成，但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，这样就不满足第二范式的要求，调整如下，需分成两个表：
在这里插入图片描述

如订单号，电影Id，订单价格，订单时间，电影场次，（电影介绍）。

第三范式（3NF）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

举例说明：
在这里插入图片描述
上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，而不是主键“学号”，所以需做如下调整：

这样以来，就满足了第三范式的要求。

对于我们项目而言，影片类型、影片来源（大陆、欧美等）、演员image地址不是直接依赖于电影id的，单独列一个表出来。

sql注入

未经充分检查的用户输入，变成了代码被执行。
用户名为’or 1=1 # #在sql中是注释导致全表查询。

SELECT * FROM USER WHERE userName = ''or 1=1 #'and password = '';

1、参数化查询—使用预编译语句数据与代码分离

在使用 PreparedStatement 执行SQL命令时，命令会带着占位符被数据库进行编译和解析，并放到命令缓冲区。然后，每当执行同一个 PreparedStatement 语句的时候，由于在缓冲区中可以发现预编译的命令，虽然会被再解析一次，但不会被再次编译。

而SQL注入只对编译过程有破坏作用，执行阶段只是把输入串作为数据处理，不需要再对SQL语句进行解析，因此解决了注入问题。

在使用参数化查询的情况下，数据库系统不会将参数的内容视为SQL指令的一部分来处理，而是在数据库完成SQL指令的编译后，才套用参数运行，因此就算参数中含有破坏性的指令，也不会被数据库所运行。

2、词法分析

使用正则表达式过滤传入的参数；对特殊字符进行转义

Mybatis中的占位符
# {} 对传入的参数视为字符串，会首先对sql语句进行预编译。
如select * from user where id = #{id}，传入id值为5，sql语句预编译为 select * from user where id
= ?
#仅仅是参数替换

对于sql语句：

select * from user where account = #{account} and password = #{pwd}

如果前台传来的账户名是“a”，密码是 “1 or 1=1”，用#的方式就不会出现sql注入，sql语句为:

select * from user where account = ‘a’ and password = ‘1 or 1=1’

查出account为a，password为1 or 1=1的用户
而如果换成$方式，$没有预编译过程，将传入的数据直接在sql中生成，语句就变成了：

select * from user where account = a and password = 1 or 1=1

查出全部用户数据。这样会产生sql注入的情况，比较不安全。

mysql主从复制
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

例题1

现有如下MySQL数据表，请写出合适的SQL语句，查询出女生数量最多的前三个专业的名称和对应女生人数？（gender字段说明：1为男，2为女）。要求查询的表头如Subject_name，girl_count。
在这里插入图片描述

建表程序：

create table Student_Info(id int primary key not null,  name varchar(30),  gender int);
insert into Student_Info(id,name,gender) values(1,'Kate',2);
insert into Student_Info(id,name,gender) values(2,'Mary',2);
insert into Student_Info(id,name,gender) values(3,'Tom',1);
insert into Student_Info(id,name,gender) values(4,'Jim',1);
insert into Student_Info(id,name,gender) values(5,'Lily',2);
insert into Student_Info(id,name,gender) values(6,'Rose',2);
insert into Student_Info(id,name,gender) values(7,'Lucy',2);
insert into Student_Info(id,name,gender) values(8,'Meimei',2);

create table Subject_Register(student_id int primary key not null,  subject_id int);
insert into Subject_Register(student_id,subject_id) values(1, 1);
insert into Subject_Register(student_id,subject_id) values(2, 2);
insert into Subject_Register(student_id,subject_id) values(3, 3);
insert into Subject_Register(student_id,subject_id) values(4, 3);
insert into Subject_Register(student_id,subject_id) values(5, 2);
insert into Subject_Register(student_id,subject_id) values(6, 4);
insert into Subject_Register(student_id,subject_id) values(7, 4);
insert into Subject_Register(student_id,subject_id) values(8, 4);

create table Subject_Info(id int primary key not null,  name varchar(30));
insert into Subject_Info(id,name) values(1, 'Math');
insert into Subject_Info(id,name) values(2, 'English');
insert into Subject_Info(id,name) values(3, 'Computer');
insert into Subject_Info(id,name) values(4, 'News');

要求输出：
在这里插入图片描述
答案解析：

SELECT Subject_Info.name, COUNT(*)
FROM Student_Info, Subject_Register, Subject_Info
WHERE Student_Info.id = Subject_Register.student_id
AND Subject_Register.subject_id = Subject_Info.id
AND Student_Info.gender = 2
GROUP BY Subject_Info.name
ORDER BY COUNT(*) desc LIMIT 3;

或

SELECT Subject_Info.name, COUNT(*) girl_count
FROM Student_Info left join
(Subject_Register left join Subject_Info
on Subject_Register.subject_id = Subject_Info.id)
on Student_Info.id = Subject_Register.student_id
WHERE Student_Info.gender = 2
GROUP BY Subject_Info.name
ORDER BY girl_count desc LIMIT 3;

例题2

创建一张员工表，表明 EMPLOYEES,有四个字段，EMPLOYEE_ID:员工表(主键)、DEPT_ID:部门号、EMPLOYEE_NAME:员工姓名、EMPLOYEE_SALARY:员工工资。
（1）写出建表语句
（2）检索出员工工资最高的员工姓名和工资
（3）检索出部门中员工最多的部门号和此部门员工数量

（1）

CREATE TABLE employees
(
	employee_id     int          NOT NULL PRIMARY KEY,
	dept_id         int          NULL,
	employee_name   varchar(20)  NULL,
	employee_salary decimal      NULL
);

（2）

SELECT employees.employee_name AS name, employees.employee_salary AS salary 
FROM employees
WHERE employees.employee_salary = (SELECT MAX(employee_salary) FROM employees);

（3）

SELECT employees.dept_id, COUNT(employees.employee_id) AS employee_num
FROM employees
GROUP BY employees.dept_id
HAVING (SELECT MAX(COUNT(employees.employee_id)));