除了基础题部分,本文还收集整理的MySQL面试题还包括如下知识点或题型:
问题1:char、varchar的区别是什么? varchar是变长而char的长度是固定的。如果你的内容是固定大小的,你会得到更好的性能。
问题2: TRUNCATE和DELETE的区别是什么? DELETE命令从一个表中删除某一行,或多行,TRUNCATE命令永久地从表中删除每一行。
问题3:什么是触发器,MySQL中都有哪些触发器? 触发器是指一段代码,当触发某个事件时,自动执行这些代码。在MySQL数据库中有如下六种触发器:
问题4:FLOAT和DOUBLE的区别是什么?
问题5:如何在MySQL种获取当前日期?
SELECT
CURRENT_DATE();
问题6:如何查询第n高的工资?
SELECT
DISTINCT(salary) from employee ORDER
BY salary DESC
LIMIT n-1,1
(提示:代码可以左右滑动)
问题7:请写出下面MySQL数据类型表达的意义(int(0)、char(16)、varchar(16)、datetime、text)
此题考察的是MySQL数据类型。MySQL数据类型属于MySQL数据库基础,由此延伸出的知识点还包括如下内容:
数据类型考点:
1、整数类型,
包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性,表示数据是无符号的,即非负整数。
长度:
整数类型可以被指定长度,例如:INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的,它不会限制值的合法范围,只会影响显示字符的个数,而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
例子,
假定类型设定为INT(5),属性为UNSIGNED ZEROFILL,如果用户插入的数据为12的话,那么数据库实际存储数据为00012。2、实数类型,
包括FLOAT、DOUBLE、DECIMAL。
DECIMAL可以用于存储比BIGINT还大的整型,能存储精确的小数。
而FLOAT和DOUBLE是有取值范围的,并支持使用标准的浮点进行近似计算。
计算时FLOAT和DOUBLE相比DECIMAL效率更高一些,DECIMAL你可以理解成是用字符串进行处理。3、字符串类型,
包括VARCHAR、CHAR、TEXT、BLOB
VARCHAR用于存储可变长字符串,它比定长类型更节省空间。
VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时,使用1字节表示,否则使用2字节表示。
VARCHAR存储的内容超出设置的长度时,内容会被截断。
CHAR是定长的,根据定义的字符串长度分配足够的空间。
CHAR会根据需要使用空格进行填充方便比较。
CHAR适合存储很短的字符串,或者所有值都接近同一个长度。
CHAR存储的内容超出设置的长度时,内容同样会被截断。使用策略: 对于经常变更的数据来说,CHAR比VARCHAR更好,因为CHAR不容易产生碎片。 对于非常短的列,CHAR比VARCHAR在存储空间上更有效率。 使用时要注意只分配需要的空间,更长的列排序时会消耗更多内存。 尽量避免使用TEXT/BLOB类型,查询时会使用临时表,导致严重的性能开销。
4、枚举类型(ENUM),
把不重复的数据存储为一个预定义的集合。
有时可以使用ENUM代替常用的字符串类型。
ENUM存储非常紧凑,会把列表值压缩到一个或两个字节。
ENUM在内部存储时,其实存的是整数。
尽量避免使用数字作为ENUM枚举的常量,因为容易混乱。
排序是按照内部存储的整数5、日期和时间类型,
尽量使用timestamp,空间效率高于datetime,
用整数保存时间戳通常不方便处理。
如果需要存储微妙,可以使用bigint存储。
看到这里,这道真题是不是就比较容易回答了。答:int(0)表示数据是INT类型,长度是0、char(16)表示固定长度字符串,长度为16、varchar(16)表示可变长度字符串,长度为16、datetime表示时间类型、text表示字符串类型,能存储大字符串,最多存储65535字节数据)
常见操作
-u:指定用户名 -p:指定密码 -h:主机 -P:端口
G:打印结果垂直显示 c:取消当前MySQL命令 q:退出MySQL连接 s:显示服务器状态 h:帮助信息 d:改变执行符
1、InnoDB存储引擎,
2、MyISAM存储引擎,
3、其他表引擎, Archive、Blackhole、CSV、Memory
使用策略 在大多数场景下建议使用InnoDB存储引擎。
表锁是日常开发中的常见问题,因此也是面试当中最常见的考察点,当多个查询同一时刻进行数据修改时,就会产生并发控制的问题。共享锁和排他锁,就是读锁和写锁。
使用策略
提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程。 使用场景
问题8:请说明InnoDB和MyISAM的区别
问题9:innodb引擎的特性
问题10:请列举3个以上表引擎 InnoDB、MyISAM、Memory
问题11:请说明varchar和text的区别
问题12:varchar(50)中50的含义 最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order by col采用fixed_length计算col长度(memory引擎也一样)。
问题13:int(20)中20的含义 是指显示字符的长度,不影响内部存储,只是当定义了ZEROFILL时,前面补多少个 0
问题14:简单描述MySQL中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响?
此真题主要考察的是MySQL索引的基础和类型,由此延伸出的知识点还包括如下内容:
索引的基础
创建索引的语法:
索引对性能的影响:
索引的使用场景:
索引的类型: 索引很多种类型,是在MySQL的存储引擎实现的。
索引的区别: -一个表只能有一个主键索引,但是可以有多个唯一索引。
MySQL索引的创建原则
MySQL索引的注意事项 1、联合索引遵循前缀原则
KEY(a,b,c)
WHERE a = 1
AND b = 2
AND c = 3
WHERE a = 1
AND b = 2
WHERE a = 1
#以上SQL语句可以用到索引
WHERE b = 2
AND c = 3
WHERE a = 1
AND c = 3
#以上SQL语句用不到索引
2、LIKE查询,%不能在前
WHERE name LIKE "%wang%"
#以上语句用不到索引,可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。
3、列值为空(NULL)时是可以使用索引的,但MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间,还需要在MySQL内部进行特殊处理。
4、如果MySQL估计使用索引比全表扫描更慢,会放弃使用索引,例如: 表中只有100条数据左右。对于SQL语句WHERE id > 1 AND id < 100,MySQL会优先考虑全表扫描。
5、如果关键词or前面的条件中的列有索引,后面的没有,所有列的索引都不会被用到。
6、列类型是字符串,查询时一定要给值加引号,否则索引失效,例如: 列name varchar(16),存储了字符串"100" WHERE name = 100; 以上SQL语句能搜到,但无法用到索引。
MySQL索引的原理
注:
B+ 树是一种树数据结构,是一个n叉排序树,每个节点通常有多个孩子,一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点,也可能是一个包含两个或两个以上孩子节点的节点。
B+ 树通常用于数据库和操作系统的文件系统中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系统都在使用B+树作为元数据索引。B+ 树的特点是能够保持数据稳定有序,其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。
InnoDB通过主键聚簇数据,如果没有定义主键,会选择一个唯一的非空索引代替,如果没有这样的索引,会隐式定义个主键作为聚簇索引。 下图形象说明了聚簇索引表(InnoDB)和普通的堆组织表(MyISAM)的区别:
最常问的MySQL面试题三——每个开发人员都应该知道 对于普通的堆组织表来说(右图),表数据和索引是分别存储的,主键索引和二级索引存储上没有任何区别。 而对于聚簇索引表来说(左图),表数据是和主键一起存储的,主键索引的叶结点存储行数据,二级索引的叶结点存储行的主键值。 聚簇索引表最大限度地提高了I/O密集型应用的性能,但它也有以下几个限制:
二级索引的叶节点存储的是主键值,而不是行指针,这是为了减少当出现行移动或数据页分裂时二级索引的维护工作,但会让二级索引占用更多的空间。
在一些MySQL索引基础考题中,我们可以轻松的通过索引基础和类型来解决此类问题,对于一些索引创建注意事项方面的考点,我们可以通过索引创建原则和注意事项来解决。
问题14:创建MySQL联合索引应该注意什么? 需遵循前缀原则
问题15:列值为NULL时,查询是否会用到索引? 在MySQL里NULL值的列也是走索引的。当然,如果计划对列进行索引,就要尽量避免把它设置为可空,MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。
问题16:以下语句是否会应用索引:SELECT FROM users WHERE YEAR(adddate) < 2007;* 不会,因为只要列涉及到运算,MySQL就不会使用索引。
问题17:MyISAM索引实现? MyISAM存储引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做非聚簇索引的,之所以这么称呼是为了与InnoDB的聚簇索引区分。
问题18:MyISAM索引与InnoDB索引的区别?
问题19:以下三条sql 如何建索引,只建一条怎么建?
WHERE a=1 AND b=1
WHERE b=1
WHERE b=1 ORDER BY time DESC
以顺序b,a,time建立联合索引,CREATE INDEX table1_b_a_time ON index_test01(b,a,time)。因为最新MySQL版本会优化WHERE子句后面的列顺序,以匹配联合索引顺序。
问题20:有A(id,sex,par,c1,c2),B(id,age,c1,c2)两张表,其中A.id与B.id关联,现在要求写出一条SQL语句,将B中age>50的记录的c1,c2更新到A表中同一记录中的c1,c2字段中 考点分析 这道题主要考察的是MySQL的关联UPDATE语句 延伸考点:
UPDATE A,B SET A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id
UPDATE A INNER
JOIN B ON A.id=B.id SET A.c1 = B.c1,A.c2=B.c2
再加上B中age>50的条件:
UPDATE A,B set A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id and B.age > 50;
UPDATE A INNER
JOIN B ON A.id = B.id set A.c1 = B.c1,A.c2 = B.c2 WHERE B.age > 50
六种关联查询
SELECT * FROM A,B(,C)或者
SELECT * FROM A CROSS
JOIN B (CROSS
JOIN C)
#没有任何关联条件,结果是笛卡尔积,结果集会很大,没有意义,很少使用
内连接(INNER
JOIN)
SELECT * FROM A,B WHERE A.id=B.id或者
SELECT * FROM A INNER
JOIN B ON A.id=B.id
多表中同时符合某种条件的数据记录的集合,INNER
JOIN可以缩写为JOIN
内连接分为三类
外连接(LEFT JOIN/RIGHT JOIN)
联合查询(UNION与UNION ALL)
SELECT * FROM A UNION
SELECT * FROM B UNION ...
全连接(FULL JOIN)
SELECT * FROM A LEFT
JOIN B ON A.id=B.id UNION
SELECT * FROM A RIGHT
JOIN B ON A.id=B.id
嵌套查询 用一条SQL语句得结果作为另外一条SQL语句得条件,效率不好把握 SELECT * FROM A WHERE id IN (SELECT id FROM B)
根据考题要搞清楚表的结果和多表之间的关系,根据想要的结果思考使用那种关联方式,通常把要查询的列先写出来,然后分析这些列都属于哪些表,才考虑使用关联查询
问题21: 为了记录足球比赛的结果,设计表如下: team:参赛队伍表 match:赛程表 其中,match赛程表中的hostTeamID与guestTeamID都和team表中的teamID关联,查询2006-6-1到2006-7-1之间举行的所有比赛,并且用以下形式列出:拜仁 2:0 不莱梅 2006-6-21
初步写一个基础的SQL:
SELECT hostTeamID,matchResult,matchTime guestTeamID from
match
where matchTime between
"2006-6-1"
and
"2006-7-1";
通过外键联表,完成最终SQL:
select t1.teamName,m.matchResult,t2.teamName,m.matchTime from
match
as m left
join team as t1 on m.hostTeamID = t1.teamID, left
join team t2 on m.guestTeamID=t2.guestTeamID where m.matchTime between
"2006-6-1"
and
"2006-7-1"
问题22:UNION与UNION ALL的区别?
问题23:一个6亿的表a,一个3亿的表b,通过外键tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。
select * from a,b where a.tid = b.id and a.tid>50000
limit
200;
select * from b , (select tid from a limit
50000,200) a where b.id = a .tid;
问题24:拷贝表( 拷贝数据, 源表名:a 目标表名:b)
insert
into b(a, b, c) select d,e,f from a;
问题25:Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表 查询没学过“叶平”老师课的同学的学号、姓名
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’);
问题26:随机取出10条数据
SELECT * FROM
users
WHERE
id >= ((SELECT
MAX(id) FROM
users)-(SELECT
MIN(id) FROM
users)) * RAND() + (SELECT
MIN(id) FROM
users) LIMIT
10
#此方法效率比直接用SELECT * FROM users order by rand() LIMIT 10高很多
问题27:请简述项目中优化SQL语句执行效率的方法,从哪些方面,SQL语句性能如何分析? 考点分析: 这道题主要考察的是查找分析SQL语句查询速度慢的方法 延伸考点:
如何查找查询速度慢的原因 记录慢查询日志,分析查询日志,不要直接打开慢查询日志进行分析,这样比较浪费时间和精力,可以使用pt-query-digest工具进行分析
set profiling=1;开启,服务器上所有执行语句会记录执行时间,存到临时表中
show
profiles
show profile for
query 临时表ID
show status会返回一些计数器,show global status会查看所有服务器级别的所有计数 有时根据这些计数,可以推测出哪些操作代价较高或者消耗时间多
观察是否有大量线程处于不正常的状态或特征
最常问的MySQL面试题五——每个开发人员都应该知道
分析单条SQL语句
优化查询过程中的数据访问
优化长难的查询语句
优化特定类型的查询语句
优化关联查询
优化子查询
优化LIMIT分页
优化UNION查询
优化WHERE子句
对于此类考题,先说明如何定位低效SQL语句,然后根据SQL语句可能低效的原因做排查,先从索引着手,如果索引没有问题,考虑以上几个方面,数据访问的问题,长难查询句的问题还是一些特定类型优化的问题,逐一回答。
SQL语句优化的一些方法?
select
id
from t where
num
is
null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select
id
from t where
num=
select
id
from t where
num=10
or
num=20可以这样查询:select
id
from t where
num=10
union all select
id
from t where
num=20
select
id
from t where
num
in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了:select
id
from t where
num
between
1
and
3
select
id
from t where
num=@num可以改为强制查询使用索引:select
id
from t with(index(索引名)) where
num=@num
select
id
from t where
num/2=100应改为:select
id
from t where
num=100*2
select
id
from t where
substring(name,1,3)=’abc’ ,name以abc开头的id应改为:
select
id
from t where
name
like ‘abc%’
整理自网络、SQL数据库开发