【SQL数据库】第一时间送达干货(一)还收集整理

点击关注上方“SQL数据库开发”，

设为“置顶或星标”，第一时间送达干货

不仅基础题部份，本文还搜集整理的MySQL笔试题还包括如下知识点或题型：

问题1：char、varchar的区别是哪些？

varchar是变长而char的宽度是固定的。假如你的内容是固定大小的，你会得到更好的性能。

问题2:TRUNCATE和DELETE的区别是哪些？

DELETE命令从一个表中删掉某一行，或多行，TRUNCATE命令永久地从表中删掉每一行。

问题3：哪些是触发器，MySQL中都有什么触发器？

触发器是指一段代码，当触发某个风波时，手动执行这种代码。在MySQL数据库中有如下六种触发器：

问题4：FLOAT和DOUBLE的区别是哪些？

问题5：怎样在MySQL种获取当前日期？

SELECTCURRENT_DATE();

问题6：怎么查询第n高的薪水？

SELECTDISTINCT(salary)fromemployeeORDERBYsalaryDESCLIMITn-1,1

(提示：代码可以左右滑动)

问题7：请写出下边MySQL数据类型抒发的意义（int(0)、char(16)、varchar(16)、datetime、text）

知识点剖析

此题考察的是MySQL数据类型。MySQL数据类型属于MySQL数据库基础oracle两张表关联更新，由此延展出的知识点还包括如下内容：

数据类型考点：

使用策略：

对于时常变更的数据来说，CHAR比VARCHAR更好，由于CHAR不容易形成碎片。

对于十分短的列，CHAR比VARCHAR在储存空间上更有效率。

使用时要注意只分配须要的空间，更长的列排序时会消耗更多显存。

尽量避开使用TEXT/BLOB类型，查询时会使用临时表，造成严重的性能花销。

答：int(0)表示数据是INT类型，宽度是0、char(16)表示固定宽度字符串，宽度为16、varchar(16)表示可变宽度字符串，宽度为16、datetime表示时间类型、text表示字符串类型，能储存大字符串，最多储存65535字节数据）

MySQL基础操作：

常见操作

MySQL的联接和关掉：mysql-u-p-h-P

-u：指定用户名

-p：指定密码

-h：主机

-P：端口

步入MySQL命令行后：G、c、q、s、h、d

G：复印结果垂直显示

c：取消当前MySQL命令

q：退出MySQL联接

s：显示服务器状态

h：帮助信息

d：改变执行符

MySQL储存引擎：

1、InnoDB储存引擎，

2、MyISAM储存引擎，

3、其他表引擎，

Archive、Blackhole、CSV、Memory

使用策略

在大多数场景下建议使用InnoDB储存引擎。

MySQL锁机制

表锁是日常开发中的常见问题，因而也是笔试当中最常见的考察点，当多个查询同一时刻进行数据更改时，才会形成并发控制的问题。共享锁和排他锁，就是读锁和写锁。

锁的细度MySQL事务处理储存过程

使用策略

触发器

提供给程序员和数据剖析员来保证数据完整性的一种方式，它是与表风波相关的特殊的储存过程。

使用场景

问题8：请说明InnoDB和MyISAM的区别

问题9：innodb引擎的特点

问题10：请列出3个以上表引擎

InnoDB、MyISAM、Memory

问题11：请说明varchar和text的区别

问题12：varchar(50)中50的涵义

最多储存50个字符，varchar(50)和(200)储存hello所占空间一样，但前者在排序时会消耗更多显存，由于orderbycol采用fixed_length估算col宽度(memory引擎也一样)。

问题13：int(20)中20的涵义

是指显示字符的宽度，不影响内部储存，只是当定义了ZEROFILL时，后面补多少个0

问题14：简单描述MySQL中，索引，字段，惟一索引，联合索引的区别，对数据库的性能有哪些影响？

知识点剖析

此真题主要考察的是MySQL索引的基础和类型，由此延展出的知识点还包括如下内容：

下边我们就来将这种知识一网打尽

索引的基础

创建索引的句型：

索引对性能的影响：

索引的使用场景：

索引的类型：

索引好多种类型，是在MySQL的储存引擎实现的。

索引的区别：

-一个表只能有一个字段索引，并且可以有多个惟一索引。

MySQL索引的创建原则

MySQL索引的注意事项

1、联合索引遵守前缀原则

KEY(a,b,c)

WHEREa=1ANDb=2ANDc=3

WHEREa=1ANDb=2

WHEREa=1

#以上SQL句子可以用到索引

WHEREb=2ANDc=3

WHEREa=1ANDc=3

#以上SQL句子用不到索引

2、LIKE查询，%不能在前

WHEREnameLIKE”%wang%”

#以上句子用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎取代。

3、列值为空（NULL）时是可以使用索引的，但MySQL无法优化引用了可空列的查询,它会使索引、索引统计和值愈发复杂。可空列须要更多的存储空间，还须要在MySQL内部进行特殊处理。

4、如果MySQL恐怕使用索引比全表扫描更慢，会舍弃使用索引，比如：

表中只有100条数据左右。对于SQL句子WHEREid>1ANDid<100，MySQL会优先考虑全表扫描。

5、如果关键词or上面的条件中的列有索引，前面的没有，所有列的索引都不会被用到。

6、列类型是字符串，查询时一定要给值加冒号，否则索引失效，比如：

列namevarchar(16)，储存了字符串”100″

WHEREname=100;

以上SQL句子能搜到，但未能用到索引。

MySQL索引的原理

注：

B+树是一砍树数据结构，是一个n叉排序树，每位节点一般有多个儿子，一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上小孩节点的节点。

B+树一般用于数据库和操作系统的文件系统中。NTFS,ReiserFS,NSS,XFS,JFS,ReFS和BFS等文件系统都在使用B+树作为元数据索引。B+树的特征是还能保持数据稳定有序，其插入与更改拥有较稳定的对数时间复杂度。B+树元素自底向下插入。

InnoDB通过外键聚簇数据，假如没有定义字段，会选择一个惟一的非空索引取代，假如没有这样的索引oracle两张表关联更新，会隐式定义个字段作为聚簇索引。

右图形象说明了聚簇索引表(InnoDB)和普通的堆组织表(MyISAM)的区别：

最常问的MySQL笔试题三——每个开发人员都应当晓得

对于普通的堆组织表来说（下图），表数据和索引是分别储存的，字段索引和二级索引储存上没有任何区别。

而对于聚簇索引表来说（左图），表数据是和字段一起储存的，字段索引的叶结点储存行数据，二级索引的叶结点储存行的字段值。

聚簇索引表最大限度地提升了I/O密集型应用的性能，但它也有以下几个限制：

二级索引的叶节点储存的是字段值，而不是行表针，这是为了减轻当出现行联通或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

解题技巧

在一些MySQL索引基础试题中，我们可以轻松的通过索引基础和类型来解决这种问题，对于一些索引创建注意事项方面的考点，我们可以通过索引创建原则和注意事项来解决。

问题14：创建MySQL联合索引应当注意哪些？

需遵守前缀原则

问题15：列值为NULL时，查询是否会用到索引？

在MySQL里NULL值的列也是走索引的。其实，倘若计划对列进行索引，就要尽量避开把它设置为可空，MySQL无法优化引用了可空列的查询,它会使索引、索引统计和值愈加复杂。

问题16：以下句子是否会应用索引：SELECTFROMusersWHEREYEAR(adddate)<2007;*

不会，由于只要列涉及到运算，MySQL就不会使用索引。

问题17：MyISAM索引实现？

MyISAM储存引擎使用B+Tree作为索引结构，叶节点的data域储存的是数据记录的地址。MyISAM的索引方法也称作非聚簇索引的，之所以如此爱称是为了与InnoDB的聚簇索引分辨。

问题18：MyISAM索引与InnoDB索引的区别？

问题19：以下三条sql怎么建索引，只建一条如何建？

WHEREa=1ANDb=1

WHEREb=1

WHEREb=1ORDERBYtimeDESC

以次序b,a,time构建联合索引，CREATEINDEXtable1_b_a_timeONindex_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句子

延展考点：

针对刚刚这道题，答案可以是如下两种方式的写法：UPDATEA,BSETA.c1=B.c1,A.c2=B.c2WHEREA.id=B.id

UPDATEAINNERJOINBONA.id=B.idSETA.c1=B.c1,A.c2=B.c2

再加上B中age>50的条件：

UPDATEA,BsetA.c1=B.c1,A.c2=B.c2WHEREA.id=B.idandB.age>50;

UPDATEAINNERJOINBONA.id=B.idsetA.c1=B.c1,A.c2=B.c2WHEREB.age>50

MySQL的关联查询句子

六种关联查询

SELECT*FROMA,B(,C)或则

SELECT*FROMACROSSJOINB(CROSSJOINC)

#没有任何关联条件，结果是笛卡尔积，结果游行很大，没有意义，极少使用

内联接（INNERJOIN）

SELECT*FROMA,BWHEREA.id=B.id或则

SELECT*FROMAINNERJOINBONA.id=B.id

多表中同时符合某种条件的数据记录的集合，INNERJOIN可以简写为JOIN

内联接分为三类

外联接（LEFTJOIN/RIGHTJOIN）

联合查询（UNION与UNIONALL）

SELECT*FROMAUNIONSELECT*FROMBUNION…

全联接（FULLJOIN）

SELECT*FROMALEFTJOINBONA.id=B.idUNION

SELECT*FROMARIGHTJOINBONA.id=B.id

嵌套查询

用一条SQL词句得结果作为另外一条SQL词句得条件，效率不好掌握

SELECT*FROMAWHEREidIN(SELECTidFROMB)

解题技巧

按照试题要认清楚表的结果和多表之间的关系，依照想要的结果思索使用那个关联形式，一般把要查询的列先写下来，之后剖析那些列都属于什么表，才考虑使用关联查询

问题21：

为了记录网球赛事的结果，设计表如下：

team：参赛队伍表

match：赛程表

其中，match赛程表中的hostTeamID与guestTeamID都和team表中的teamID关联，查询2006-6-1到2006-7-1之间举办的所有赛事，而且用以下方式列举：利物浦2:0切尔西2006-6-21

首先列举须要查询的列：其次列举结果列：

初步写一个基础的SQL：

SELECThostTeamID,matchResult,matchTimeguestTeamIDfrommatchwherematchTimebetween”2006-6-1″and”2006-7-1″;

通过字段联表，完成最终SQL：

selectt1.teamName,m.matchResult,t2.teamName,m.matchTimefrommatchasmleftjointeamast1onm.hostTeamID=t1.teamID,leftjointeamt2onm.guestTeamID=t2.guestTeamIDwherem.matchTimebetween”2006-6-1″and”2006-7-1″

问题22：UNION与UNIONALL的区别？

问题23：一个6亿的表a，一个3亿的表b，通过字段tid关联，你怎么最快的查询出满足条件的第50000到第50200中的这200条数据记录。

select*froma,bwherea.tid=b.idanda.tid>50000limit200;

select*fromb,(selecttidfromalimit50000,200)awhereb.id=a.tid;

问题24：拷贝表(拷贝数据,源表名：a目标表名：b)

insertintob(a,b,c)selectd,e,ffroma;

问题25：Student(S#,Sname,Sage,Ssex)中学生表Course(C#,Cname,T#)课程表SC(S#,C#,score)成绩表Teacher(T#,Tname)班主任表查询没学过“叶平”老师课的朋友的学号、姓名

selectStudent.S#,Student.Sname

fromStudent

whereS#notin(selectdistinct(SC.S#)fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname=’叶平’);

问题26：随机取出10条数据

SELECT*FROMusersWHEREid>=((SELECTMAX(id)FROMusers)-(SELECTMIN(id)FROMusers))*RAND()+(SELECTMIN(id)FROMusers)LIMIT10

#此方式效率比直接用SELECT*FROMusersorderbyrand()LIMIT10高好多

问题27：请阐述项目中优化SQL句子执行效率的方式，从什么方面，SQL句子性能怎么剖析？

考点剖析：

这道题主要考察的是查找剖析SQL句子查询速率慢的方式

延展考点：

怎么查找查询速率慢的缘由

记录慢查询日志，剖析查询日志，不要直接打开慢查询日志进行剖析，这样比较浪费时间和精力，可以使用pt-query-digest工具进行剖析

使用showprofilesetprofiling=1;开启，服务器上所有执行句子会记录执行时间，存到临时表中

showprofiles

showprofileforquery临时表ID

使用showstatus

showstatus会返回一些计数器，showglobalstatus会查看所有服务器级别的所有计数

有时按照这种计数，可以推断出什么操作代价较高或则消耗时间多

showprocesslist

观察是否有大量线程处于不正常的状态或特点

最常问的MySQL笔试题五——每个开发人员都应当晓得

使用explain

剖析单条SQL句子

优化查询过程中的数据访问

优化长难的查询句子

优化特定类型的查询句子

优化关联查询

优化子查询

优化LIMIT分页

优化UNION查询

优化WHERE谓词

解题技巧

对于这种试题，先说明怎样定位低效SQL句子，之后按照SQL句子可能低效的诱因做排查，先从索引着手，假如索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

SQL句子优化的一些技巧？

selectidfromtwherenumisnull可以在num上设置默认值0，确保表中num列没有null值，之后这样查询：selectidfromtwherenum=

selectidfromtwherenum=10ornum=20可以这样查询：selectidfromtwherenum=10unionallselectidfromtwherenum=20

selectidfromtwherenumin(1,2,3)对于连续的数值，能用between就不要用in了：selectidfromtwherenumbetween1and3

selectidfromtwherenum=@num可以改为强制查询使用索引：selectidfromtwith(index(索引名))wherenum=@num

selectidfromtwherenum/2=100应改为:selectidfromtwherenum=100*2

selectidfromtwheresubstring(name,1,3)=’abc’，name以abc开头的id应改为:

selectidfromtwherenamelike‘abc%’

搜集整理自网路

——End——
后台回复关键字：1024，获取一份精心整理的技术干货
后台回复关键字：进群，带你进入高手如云的交流群。
推荐阅读
阿里规定超过3张表，禁止JOIN，为何？
MySQL 常用命令手册
5个免费在线 SQL 数据库环境
我司空降了一名专家后，一半程序员疯了...
一款免费比Navicat还要好用，功能强大的数据库管理工具

这是一个能学到技术的公众号，欢迎关注