最全mysql面试问题练习题.docx

最全mysql面试问题练习题.docx

《最全mysql面试问题练习题.docx》由会员分享，可在线阅读，更多相关《最全mysql面试问题练习题.docx（45页珍藏版）》请在冰豆网上搜索。

最全mysql面试问题练习题

一、简单条件查询：

1、列起别名

Selectdnameasdeptnamefromdept;

2、从emp表中查询工资等于3000的员工编号和姓名

selectempno,enameFROMempWHEREsal=3000;

3、从emp表中查询工资在2450和3000的员工编号和姓名

SELECTempno,ename,salFROMempWHEREsal>=2450andsal<=3000;

4、从emp表中查询津贴为null的员工编号和姓名和工资

SELECTempno,ename,sal,commFROMempWHEREcommisNULL;

5、从emp表中查询工作岗位是manager并且工资大于2500的员工

SELECTempno,ename,sal,jobFROMempWHEREjob='MANAGER'andSAL>2500;

6、从emp表中查询工资大于2500且部门编号是10或者20的员工

SELECT*FROMempWHERESAL>2500and（DEPTNO=10orDEPTNO=20）;

SELECT*FROMempWHERESAL>2500andDEPTNOin（10,20）;

//and和or同时出现，and的优先级高。

in的作用相当于or

6、找出以t结尾、以k开头的、名字中间包含有a的、名字中第二个字母是a的、名字中带有下划线的员工名字

SELECTENAMEFROMempWHEREenamelike'%t';//以t结尾

SELECTENAMEFROMempWHEREenamelike'k%';//以k开头

SELECTENAMEFROMempWHEREenamelike'%a%';//名字中间包含有a

SELECTENAMEFROMempWHEREenamelike'_a%';//名字中第二个字母是a

SELECTENAMEFROMempWHEREenamelike'%\_%';//名字中带有下划线

7、从emp表中查询所有员工工资并排序

SELECTENAME,salFROMempORDERBYsal;//默认是升序

SELECTENAME,salFROMempORDERBYsalDESC;//降序

8、从emp表中查询所有员工工资并升序排序，并且当员工工资相同时按照名字的升序排列

SELECTENAME,salFROMempORDERBYsalasc,ENAMEasc;

9、从emp表中查询工资在1250到3000之间的员工，并且按照工资降序排序

SELECTENAME,salFROMempwheresalBETWEEN1250and3000ORDERBYsaldesc;

二、数据处理函数：

单行处理函数：

一个输入对应一个输出；

1、字段显换大小写

SELECTLOWER（ename）asenameFROMemp;//转为小写

SELECTupper（ename）asenameFROMemp;//转为大写

2、截取名字的第一字母

SELECTSUBSTR（enameFROM1FOR1）FROMemp;

3、将字段empno和ename进行拼接并赋新的字段名’result’

SELECTCONCAT（empno,ename）asresultfromemp;

4、将ename字段的首字符大写，其他字母小写

SELECTCONCAT（UPPER（SUBSTR（enameFROM1FOR1））,LOWER（SUBSTR（enamefrom2FORLENGTH（ename）-1）））asresultfromemp;

5、round（）函数

selectround（1234.567,1）asresultfromemp;//保留1位小数，结果为1234.6

selectround（1234.567,-1）asresultfromemp;//保留到十位，结果为1230

selectround（1234.567,-4）asresultfromemp;//结果为0

6、计算每位员工的年薪（月薪+补助）*12,并按照年薪降序排列

SELECTename,（sal+IFNULL（comm,0））*12asyearsalfromempORDERBYyearsalDESC;//IFNULL（comm,0）若comm中有null，则以0代替。

7、enamel中名字为'manager'时，工资涨50%，名字为'salesman'时，工资涨20%，其余人工资不变，并将原sal命名为oldsal，调整后的工资命名为newsal，进行输出对比

SELECTename,job,salASoldsal,（CASEjobWHEN'manager'THENsal*1.5WHEN'salesman'THENsal*1.2ELSEsalEND）ASnewsalFROMemp;

结果：

三、分组处理函数（多行处理函数）：

多行处理函数：

多个输入对应一个输出；

1、SELECTSUM（sal）fromemp;//求工资和

2、SELECTSUM（sal）fromemp;//求最高工资

3、SELECTMIN（sal）fromemp;//求最低工资

4、SELECTAVG（sal）fromemp;//求工资的平均数

5、SELECTcount（ename）fromemp;//求员工的数量

count（字段名）：

表示统计该字段下所有不为null的元素的总数。

count（*）：

表示表当中的总行数。

四、分组查询：

1、关键字的书写顺序和执行顺序

Select…from…where…groupby…orderby…

执行顺序：

from->where->groupby->select->orderby

2、按工作岗位分组然后求工资和

SELECTjob,salFROMempGROUPBYjob;

3、找出每个部门中的最高工资

SELECTdeptno,MAX（sal）FROMempGROUPBYdeptno;

4、找出每个部门不同工作岗位中的最高工资

SELECTDEPTNO,JOB,MAX（sal）FROMempGROUPBYDEPTNO,Job;

5、使用having可以对分完组的数据进一步过滤，having不能单独使用，必须和groupby联合使用。

能用where实现的就不用having，where的效率更高

找出每个部门中的最高工资，并且要求最高工资大于3000

SELECTDEPTNO,MAX（sal）FROMempGROUPBYDEPTNOHAVINGMAX（sal）>3000;

SELECTDEPTNO,MAX（sal）FROMempWHERESAL>3000GROUPBYDEPTNO;

6、找出每个部门平均工资高于2500的

SELECTdeptno,AVG（sal）FROMempGROUPBYDEPTNOhavingAVG（sal）>2500;

这个要求用where不好实现，用having

7、找出每个岗位的平均工资，要求平均工资大于1500，除’manager’岗位之外，按照平均工资降序排序。

SELECTjob,avg（sal）asavgsalFROMempWHEREJOB<>'manager'GROUPBYjobHAVINGavg（sal）>1500ORDERBYavgsalDESC;

五、连接查询：

1、distinct去除重复记录

SELECTDISTINCT（job）FROMemp;

2、选出员工所在的部门名字（两张表连接）

SELECTename,dnamefromempe,deptdWHEREe.DEPTNO=d.DEPTNO;//SQL92的写法

SQL92的缺点：

结构不清晰，表的连接条件和后期进一步筛选条件都放到了where后面。

SELECTename,dnamefromempejoindeptdone.DEPTNO=d.DEPTNO;//SQL99的写法

SQL99的优点：

表连接的条件是独立的，连接之后，如果还需进一步筛选，再往后继续加where。

3、筛选在最低工资和最高工资之间的员工姓名和工资

SELECT

e.ename,

e.SAL,

s.GRADE

FROM

empe

JOINsalgradesONe.SALBETWEENs.LOSAL

ANDs.HISAL;

4、右外连接

SELECTe.ENAME,d.DNAMEFROMempeRIGHTJOINdeptdone.DEPTNO=d.DEPTNO;

//right：

表示将join关键字后边的这张表看成主表，为了将这张（主）表的数据全部查询出来，捎带关联查询左边的表。

//外连接的查询结果条数一定是>=内连接的查询结果条数

5、查询每个员工是上级领导，要求显示所有员工的名字和领导名字

SELECT

a.ENAMEAS'员工名',

b.enameAS'领导名'

FROM

empa

LEFTJOINempbONa.MGR=b.empno;

6、多张表的连接语法

SELECT

*

FROM

a

JOINbONa和b的连接条

JOINcONa和c的连接条件

RightJOINdONa和d的连接条件;

//一条SQL语句中内连接和外连接可以同时存在。

7、找出每个员工的部门名称和工资等级，要求显示员工名、部门名、工资和工资等级。

SELECT

e.ENAME,

d.DNAME,

e.SAL,

s.GRADE

FROM

empe

JOINdeptdONe.DEPTNO=d.DEPTNO

JOINsalgradesONe.SALBETWEENs.LOSAL

ANDs.HISAL;

8、查出所有比最低工资高的员工姓名和工资，要求按照工资的升序排列。

SELECT

ename,

sal

FROM

emp

WHERE

sal>（SELECTMIN（sal）FROMemp）

ORDERBY

salASC;

//where句中的子查询。

9、查询每个工资岗位的平均工资对应的工资等级

SELECT

*,s.GRADE

FROM

（

SELECT

JOB,AVG（sal）ASavgsal

FROM

emp

GROUPBY

JOB

）t

JOINsalgrades

ONt.avgsalBETWEENs.LOSALANDs.HISAL;

10、查询工作岗位是manager和salesman的员工

SELECTename,jobFROMempWHEREJOB='manager'unionSELECTename,jobFROMempWHEREJOB='salesman';

//union的效率相较于之前写的WHEREJOB='manager'orJOB='salesman'和WHEREJOBin（'manager','salesman'）都要高，因为union减少了表连接匹配的次数。

11、按照工资降序排序，取前5名员工

SELECTename,salFROMempORDERBYsaldesclimit0,5;//0是起始下标，5是长度

12、按照工资降序排序，取前第3-第5的3名员工

SELECTename,salFROMempORDERBYsaldesclimit2,3;

六、建表：

1、创建表的语法格式：

createtable表名（字段名1字段类型，字段名2字段类型…）

2、给表中插入数据

Insertintot_student（no,name,sex,age,email）values（1,’zhangsan’,’man’,20,’zhangsan@）;

3、format数字格式化

让emp表中工资sal字段，每三位数字用逗号隔开

SELECTename,FORMAT（sal,'$999,999'）assalFROMemp;

4、str_to_data字符串转日期

INSERTINTOt_user（id,NAME,birth）VALUES（1,'zhnagsan',STR_TO_DATE（'1993-01-02','%Y-%m-%d'）;

INSERTINTOt_user（id,NAME,birth）VALUES（2,'lisi','1993-01-02'）;//当给定的日期就是年月日时，不需要写str_to_data。

七、修改表：

1、语法

Update表名set字段名1=值1，字段名2=值2…where条件；

UPDATEt_userset`name`='jack',birth='1994-09-20'WHEREid=2;

2、删除数据delete

语法：

deletefrom表名where条件;

Deletefromt_userwhereid=2;

Deletefromt_user;//删除表中所有数据

3、快速创建表

将查询的结果直接创建为一个新表

CREATETABLEe2asSELECT*FROMemp;

4、快速删除表中数据

TRUNCATETABLEe2;

八、约束：

常见的约束有哪些：

非空约束：

notnull

唯一性约束：

unique

主键约束：

primarykey

外键约束：

foreignkey

1、非空约束

DROPTABLEifEXISTSt_vip;

CREATETABLEt_vip（

idint,

nameVARCHAR（36）notNULL

）;

2、唯一性约束

DROPTABLEifEXISTSt_vip;

CREATETABLEt_vip（

idint,

nameVARCHAR（36）UNIQUE

）;

//添加到列后面的叫列级约束

2.1两个字段联合起来唯一，例如：

只要name字段和email字段同时一样才出错。

DROPTABLEifEXISTSt_vip;

CREATETABLEt_vip（

idint,

nameVARCHAR（36）,

emailVARCHAR（255）,

UNIQUE（name,email）

）;

//没有添加到列后的约束叫表级约束

Notnull只能做列级约束，不能做表级约束。

在mysql中，如果一个字段被unique和notnull同时约束，则该字段自动成为primarykey（主键）。

3、主键约束（primarykey）

主键的特征：

notnull+unique；

复合主键：

DROPTABLEifEXISTSt_vip;

CREATETABLEt_vip（

idint,

nameVARCHAR（36）,

emailVARCHAR（255）,

PRIMARYkey（id,name）

）;

//id和name就是复合主键

一张表只能添加一个主键，建议使用int，char等定长的作为主键。

4、主键的自动增加auto_increment

DROPTABLEifEXISTSt_vip;

CREATETABLEt_vip（

idintPRIMARYkeyauto_increment,

nameVARCHAR（255）

）;

INSERTintot_vip（name）VALUES（'zhangsan'）;

INSERTintot_vip（name）VALUES（'lisi'）;

INSERTintot_vip（name）VALUES（'wangwu'）;

INSERTintot_vip（name）VALUES（'zhaoliu'）;

5、外键约束（foreignkey）

DROPTABLEifEXISTSt_student;

DROPTABLEifEXISTSt_class;

CREATETABLEt_class（

classnointPRIMARYkey,

classnameVARCHAR（255）

）;

CREATETABLEt_student（

nointPRIMARYkeyauto_increment,

nameVARCHAR（255）,

cnoINT,

FOREIGNkey（cno）REFERENCESt_class（classno）

）;

从上图中可以明显看出，t_student的cno只能从100和101中选择，因为表t_student的外键cno是表t_class的主键。

问：

字表中的外键因为父表中的某个字段，被引用的这个字段必须是主键吗？

答：

不一定，但是被引用的这个字段至少是unique。

给子表t_student中添加数据时，外键值cno可以为null，不是必须得写。

九、存储引擎：

存储引擎时一个表存储/组织数据的方式。

Mysql默认的存储引擎（engine）是：

innoDB，默认的字符编码方式：

utf8.

Mysql支持9大存储引擎。

版本不同，支持的引擎个数不同。

十、事务（transaction）：

一个事务其实就是一个完整的业务逻辑，是一个最小的工作单元，不可再分。

什么叫清空事务？

答：

在事务的执行过程中，每一条DML语句（insert、delete和update）的操作都会记录到“事务性活动的日志文件”中。

在事务的执行过程中，我们可以提交事务，也可以回滚事务。

什么叫提交事务？

清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。

提交事务标志着事务的结束。

并且是一种全部成功的结束。

什么叫回滚事务？

回滚事务将之前所有的DML语句操作全部撤销，并且清空事务性活动的日志文件，回滚事务标志着，事务的结束。

并且是一种全部失败的结束。

提交事务：

commit，mysql默认自动提交，starttransaction关闭mysql的自动提交机制。

回滚事务：

rollback，回滚永远都是只能回滚到上一次的提交点。

事务的四个特性：

A:

原子性

说明事务是最小的工作单元，不可再分。

C:

一致性

所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。

I:

隔离性

A事务和B事务之间具有一定的隔离。

D:

持久性

事务最终结束的一个保障，事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

事务和事务之间的隔离级别：

读未提交（readuncommitted）：

最低的隔离级别

事务A可以读取到事务B未提交的数据，容易出现脏读现象。

读已提交（readcommitted）：

事务A只能读到事务B已提交之后的数据，解决脏读现象。

可重复读（repeatedread）：

事务A开启之后，不论过了多久，每一次在事务A中读取到的数据都是一致的。

序列化（serializable）：

最高的隔离级别

表示事务排队进行，不能并发。

十一、索引：

索引是在数据库表的字段上添加的，为了提高查询效率，类似一本书的目录。

Mysql当中索引是一个B-tree数据结构。

遵循做小又大存放数据，采用中序遍历方式取数据。

主键（primarykey）上会自动添加索引，unique字段上也会添加索引。

1、创建索引

CREATEINDEXe2_ename_indexone2（ename）;

//给e2表的ename字段创建索引，并且索引命名为e2_ename_index.

2、删除索引

DROPINDEXe2_ename_indexone2;

//删除e2表中名为e2_ename_index的索引。

3、如何查看一条sql语句是否是使用了索引进行检索。

explainSELECT*fromempWHEREename='king';

rows为14，说明进行了全表扫描，没有使用索引。

当添加索引后，查看结果如下：

底层会用到hash算法

十二、视图：

什么是视图？

View：

站在不同角度去看同一份数据

1、创建视图对象：

CREATEVIEWdept2_viewasSELECT*FROMdept2;

2、删除视图：

Dropviewdept2_view;

十三、DBA常用命令：

数据导出：

在windows的dos命令窗口：

mysqldumpb>D:

\b.sql–uroot–p123456，导出整个数据库b

mysqldumpbemp>D:

\b.sql–uroot–p123456,导出数据库b下的emp表。

数据导入：

注意：

需要先登录到mysql数据库服务器上。

然后创建数据库：

createdatabaseb;

使用数据库：

useb;

初始化数据库：

sourceD:

\b.sql

十四、数据库三范式：

第一范式：

要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：

建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

第三范式：

建立在第二范式基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

十三、mysql作业题

1、获取每个部门最高薪水的人员名称

第一步：

先取得每个部门的最高薪水（按照部门编号分组，找出每一组的最大值）

SELECTdeptno,MAX（sal）asmaxsalfromempGROUPBYdeptno;

第二步：

将以上的查询结果当做一张临时表t，t和emp做表连接，条件：

t.deptno=e.deptnoandt.maxsal=e.sal.

SELECT

e.ename,

t.*

FROM

empe

JOIN（

SELECT

deptno,

MAX（sal）ASmaxsal

FROM

emp

GROUPBY

deptno

）tONt.deptno=e.deptno

ANDt.maxsal=e.sal;

2、哪些人的薪水在部门的平均薪水之上

第一步：

先求出部门的平均薪水

SELECTdeptno,AVG（sal）avgsalfromempGR