MySOL程序员面试分类真题3

MySOL程序员面试分类真题3一、简答题1.

参数wait_timeout和interactive_timeout的作用和区别是什么?正确答案：interactive_timeout表示MySQL（江南博哥）服务器关闭交互式连接前等待活动的秒数；wait_timeout表示MySQL服务器关闭非交互连接之前等待活动的秒数。这2个参数的默认值都是28800，单位秒，即8个小时。需要注意的是，这2个参数需要同时设置才会生效。

参数interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点就是，通过MySQL客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。其实，针对Client，真正生效的是会话级别的wait_timeout，空闲连接(交互和非交互)超过其会话级别的wait_timeout时间就会被回收掉。在客户端连接启动的时候，根据连接的类型，来确认会话变量wait_timeout的值是继承于全局变量wait_timeout，还是interactive_timeout。对于非交互式连接，类似于jdbc连接，wait_timeout的值继承自服务器端全局变量wait_timeout。对于交互式连接，类似于MySQL客户单连接，wait_timeout的值继承自服务器端全局变量interactive_timeout。

可通过查看showprocesslist输出中Sleep状态的时间来判断一个连接的空闲时间。若超过wait_timeout的值，则会报类似于如下的错误：

mysql＞setsessionWAIT_TIMEOUT=3;

QueryOK,0rowsaffected(0.00sec)

mysql＞showprocesslist;

ERROR2013(N000):LostconnectiontoMySQLserverduringquery

mysql＞

mysql＞showprocesslist;

ERROR2006(HY000):MySQLserverhasgoneaway

Noconnection.Tryingtoreconnect...

Connectionid:50

Currentdatabase:lhrdb

2.

用哪些命令可以查看MySQL数据库中的表结构?正确答案：查看MySQL表结构的命令有如下几种：

1)DESC表名：

2)SHOWCOLUMNSFROM表名；

3)DESC表名；

4)SHOWCREATETABLE表名；

5)查询information_schema.tables系统表。

有如下表结构，其中，NAME字段代表“姓名”，SCORE字段代表“分数”。

CREATETABLE'T1'(

'ID'DOUBLE,

'NAME'VARCHAR(300),

'SCORE'DOUBLE

);

INSERTINTO'T1'('ID','NAME','SCORE')VALUES('1','N1','59');

INSERTINTO'TI'('ID','NAME','SCORE')VALUES('2','N2','66');

INSERTINTO'TI'('ID','NAME','SCORE')VALUES('3','N3','78');

INSERTINTO'TI'('ID','NAME','SCORE')VALUES('4','N1','48');

INSERTINTO'T1'('ID','NAME','SCORE')VALUES('5','N3','85');

INSERTINTO'T1'('ID','NAME','SCORE')VALUES('6','N5','51');

INSERTINTO'T1'('ID','NAME','SCORE')VALUES('7','N4','98');

INSERTINTO'TI'('ID','NAME','SCORE')VALUES('8','N5','53');

INSERTINTO'T1'('ID','NAME','SCORE')VALUES('9','N2','67');

INSERTINTO'T1'('ID','NAME','SCORE')VALUES('10','N4','88');

完成下列查询：3.

查询单分数最高的人和单分数最低的人。

正确答案：查询单分数最高的人和单分数最低的人。

4.

查询两门分数加起来的第2至5名。

正确答案：查询两门分数加起来的第2至5名。

5.

查询两门总分数在150分以下的人。

正确答案：查询两门总分数在150分以下的人。

6.

查询两门平均分数介于60和80的人。

正确答案：查询两门平均分数介于60和80的人。

7.

查询总分大于150分，平均分小于90分的人数。

正确答案：查询总分大于150分，平均分小于90分的人数。

8.

查询总分大于150分，平均分小于90分的人数有几个。

正确答案：查询总分大于150分，平均分小于90分的人数有几个。

9.

CAP定理和一般事务中的ACID特性中的一致性有什么区别?正确答案：一般事务ACID中的一致性是有关数据库规则的描述，如果数据表结构定义一个字段值是唯一的，那么一致性系统将解决所有操作中导致这个字段值非唯一性的情况，如果带有一个外键的一行记录被删除，那么其外键相关记录也应该被删除，这就是ACID一致性意思。

CAP理论的一致性是保证同一个数据在所有不同服务器上的拷贝都是相同的，这是一种逻辑保证，而不是物理，因为网络速度限制，在不同服务器上这种复制是需要时间的，集群通过阻止客户端查看不同节点上还未同步的数据维持逻辑视图。

10.

如何得到TA_LHR表的建表语句?正确答案：SHOWCREATETABLETA_LHR;

11.

MySQL中的字符串和日期相互转化的函数是什么?正确答案：MySQL中日期转换为字符串使用DATE_FORMAT函数，相当于Oracle中的TO_CHAR函数，而将字符串转换为日期格式，使用的函数为STR_TO_DATE，相当于Oracle中的TO_DATE函数。

STR_TO_DATE函数的使用示例如下所示：

selectstr_to_date('09/01/2009','%m/%d/%Y');

selectstr_to_date('20140422154706','%Y%m%d%H%i%s');

selectstr_to_date('2014-04-2215:47:06','%Y-%m-%d%H:%i:%s');

12.

如何连接到MySQL数据库?正确答案：连接到MySQL数据库有多种写法，假设MySQL服务器的地址为192.168.59.130，可以通过如下几种方式来连接MySQL数据库：

1)mysql-p。

2)mysql-uroot-p。

3)mysql-uroot-h192.168.59.130-p。

13.

MySQL如何查看帮助命令?正确答案：MySQL的帮助命令比Oracle丰富的多，可以使用help或“?”，如下所示：

mysql＞?contents

mysql＞?DataTypes

mysql＞?createdatabase;

mysql＞?int

14.

简单描述在MySQL中，索引、唯一索引、主键、联合索引的区别，它们对数据库的性能有什么影响。正确答案：索引、唯一索引、主键、联合索引的区别如下所示：

1)索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

2)唯一索引：普通索引允许被索引的数据列包含重复的值，如果能确定某个数据列只包含彼此各不相同的值，在为这个数据索引创建索引的时候就应该用关键字UNIQE把它定义为一个唯一索引，唯一索引可以保证数据记录的唯一性。

3)主键，一种特殊的唯一索引，在一张表中只能定义一个主键索引，逐渐用于唯一标识一条记录，是用关键字PRIMARYKEY来创建。

4)联合索引：索引可以覆盖多个数据列，例如INDEX索引，这就是联合索引。

索引可以极大地提高数据的查询速度，但是会降低插入删除更新表的速度，因为在执行这些写操作时，还需要操作索引文件。

15.

MySQL数据类型有哪些属性?正确答案：数据类型的属性包括auto_increment、binary、default、index、notnull、null、primarykey、unique和zerofill，如下所示：

属性列auto_increment1.auto_increment能为新插入的行赋予一个唯一的整数标识符，该属性只用于整数类型2.auto_increment一般从1开始，每行增加1。可以通过“ALTERTABLETB_NAMEAUTO_INCREMENT=n;”语句强带被置自动增长列的初始值，但是该强制的默认值是保留在内存中的。如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要在数据库启动以后重新设置3.可以使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入了多条记录，那么返回的是第一条记录使用的自动增长值4.MySQL要求将auto_increment属性用于作为主键的列5.每个表只允许有一个auto_increment列6.自动增长列可以手工插入，但是插入的值如果是空或者0，那么实际插入的将是自动增长后的值7.对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前几列进行排序后递增的8.对于TRUNCATE操作，则表中的auto_increment属性的值会被置为1，而DELETE并不会9.可以使用SQL语句“altertableai3addid0intauto_incrementprimarykeyfirst;”来添加主键列10.可以使用SQL语句“altertableai4modifyidintauto_incrementprimarykey;”来修改主键列11.如果达到最大值，那么继续插入会报错binarybinary属性只用于char和varchar值。当为列指定了该属性时，将以区分大小写的方式排序和比较defaultdefault属性确保在没有任何值可用的情况下，赋予某个常量值，这个值必须是常量，因为MySQL不允许插入函数或表达式值。此外，此属性无法用于BLOB或TEXT列。如果已经为此列指定了NULL属性，那么当没有指定默认值时默认值将为NULL，否则默认值将依赖于字段的数据类型index如果所有其他因素都相同，要加速数据库查询，那么使用索引通常是最重要的一个步骤。索引一个列会为该列创建一个有序的键数组，每个键指向其相应的表行。以后针对输入条件可以搜索这个有序的键数组，与搜索整个未索引的表相比，这将在性能方面得到极大的提升notnull如果将一个列定义为notnull，那么将不允许向该列插入null值。建议在重要情况下始终使用notnull属性，因为它提供了一个基本验证，确保已经向查询传递了所有必要的值null为列指定null属性时，该列可以保持为空，而不论行中其他列是否已经被填充。null精确的说法是“无”，而不是空字符串或0primarykeyprimarykey属性用于确保指定行的唯一性。指定为主键的列中，值不能重复，也不能为空。为指定为主键a的知赋亨auto_increment属性是很常见的，因为此列不必与行数据有任何关系，而只是作为一个唯一标识符。主键又分为以F两种：(1)单字段主键如果输入到数据库中的每行都已经有不可修改的唯一标识符，一般会使用单字段主键。注意，此主键一旦笈置就不能再修改(2)多字段主键如果记录中任何一个字段都不可能保证唯一性，那么就可以使用多字段主键。这时，多个字段联合起来确保唯一性。如果出现这种情况，那么指定一个auto_increment整数作为主键是更好的办法unique被赋予unique属性的列将确保所有值都有不同的值，只是null值可以重复。一般会指定一个列为unique，以确保该列的所有值都不同zerofillzerofill属性可用于任何数值类型，用0填充所有剩余字段空间。例如，无符号int的默认宽度是10；因此，当“零填充”的hat值为4时，将表示它为0000000004下面针对每种类型举例说明。

1)auto_increment

mysql＞createtableai(idsmallintnotnullauto_incrementprimarykey);

QueryOK,0rowsaffected(0.01sec)

mysql＞showcreatetableai\G;

**************************1.row**************************

Table:ai

CreateTable:CREATETABLE'ai'(

'id'smallint(6)NOTNULLAUTO_INCREMENT,

PRIMARYKEY('id')

)ENGINE=InnoDBDEFAULTCHARSET=Iatin1

1rowinset(0.00see)

mysql＞insertintoail(id2,name)values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');

QueryOK,6rowsaffected(0.00sec)

Reeords:6Duplicates:0Warnings:0

添加主键列：

修改某个列：

2)binary

hostnamechar(25)binarynotnull

3)default

subscribedenum('0','1')notnulldefault'0'

4)index

createtableemployees

(

idvarchar(9)notnull,

firstnamevarchar(15)notnull,

lastnamevarchar(25)notnull,

emailvarchar(45)notnull,

phonevarchar(10)notnull,

indexlastname(lastname),

primarykey(id)

);

mysql＞createtableemployees

-＞(

-＞idvarchar(9)notnull,

-＞firstnamevarchar(15)notnull,

-＞lastnamevarchar(25)notnull;

-＞emailvarchar(45)notnull,

-＞phonevarchar(10)notnull,

-＞indexlastname(lastname),

->primarykey(id)

-＞);

QueryOK,0rowsaffected(0.03sec)

mysql＞SHOWINDEXFROMemployees\G;

**************************1.row**************************

Table:employees

Non_unique:0

Key_name:PRIMARY

Seq_in_index:1

Column_name:id

Collation:A

Cardinality:0

Sub_part:NULL

Packed:NULL

Null:

Index_type:BTREE

Comment:

Index_comment:

**************************2.row**************************

Table:employees

Non_unique:1

Key_name:lastname

Seq_in_index:1

Column_name:lastnmne

Collation:A

Cardinality:0

Sub_part:NULL

Packed:NULL

Null:

Index_type:BTREE

Comment:

Index_comment:

也可以利用MySQL的createindex命令在创建表之后增加索引：

createindexlastnameonemployees(lastname(7));

这一次只索引了名字的前7个字符，因为可能不需要其他字母来区分不同的名字。因为使用较小的索引时性能更好，所以应当在实践中尽量使用小的索引。

5)zerofill

mysql＞createtabletzerofill(c1int,c2int(5),c3intzerofill,c4int(5)zeroflll);

QueryOK,0rowsaffected(0.02sec)

16.

哪个命令可以查看所有数据库?正确答案：运行命令：showdatabases；

17.

如何查看某一用户的权限?正确答案：SHOWGRANTSFORUSERNAME;

18.

用什么命令整理表数据文件的碎片?正确答案：OPTIMIZETABLETABLENAME;

19.

MySQL的分库分表和表分区(Partitioning)有什么区别?正确答案：分库分表是指把数据库中的数据物理地拆分到多个实例或多台机器上去。分表指的是通过一定规则，将一张表分解成多张不同的表。

表分区(Partitioning)可以将一张表的数据分别存储为多个文件。如果在写SQL的时候，遵从了分区规则，那么就能把原本需要遍历全表的工作转变为只需要遍历表里某一个或某些分区的工作。这样降低了查询对服务器的压力，提升了查询效率。如果分区表使用得当，那么也可以大规模地提升MySQL的服务能力。但是这种分区方式，一方面，在使用的时候必须遵从分区规则写SQL语句，如果不符合分区规则，性能反而会非常低下；另一方面，Partitioning的结果受到MySQL限制，或者说MySQL单实例的数据文件无法隔离/摆脱分布式存储的限制，不管怎么分区，所有的数据还是都在一个服务器上，没办法通过水平扩展物理服务的方法把压力分摊出去。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

20.

如何解决“ERROR1203(42000):Userrootalreadyhasmorethan'max_user_connections'activeconnections”?正确答案：与错误“ERROR1040(00000):Toomanyconnections”类似的还有“ERROR1203(42000):Userrootalreadvhasmorethan'max_user_connections'activeconnections”。该错误表示，某个用户的连接数超过了max_user_connections的值。参数max_user_cormections表示每个用户的最大连接数，默认为0，表示没有限制。需要注意的是，此处的用户是以“用户名+主机名”为单位进行区分，如下所示：

mysql＞setglobalmax_user_connections=i;

QueryOK,0rowsaffected(0.00sec)

mysql＞systemmysql

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis21

Serverversion:5.7.19MySQLCommunityServer(6PL)

Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits

affiliates.Othernamesmaybetrademarksoftheirrespective

owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql＞Systemmysql

ERROR1203(42000):Userrootalreadyhasmorethan'max_user_connections'activeconnections

mysql＞

mysql＞systemmysql-h192.168.59.159-uroot-plhr

mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis23

Servetversion:5.7.19MySOLCommunityServer(GPL)

Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits

affiliates.Othernamesmaybetrademarksoftheirrespective

owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql＞systemmysql-h192.168.59.159-uroot-plhr

mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.

ERROR1203(42000):Userrootalreadyhasmorethan'max_user_connections'actireconnections

设有关系模式R(A，B，C，D，E)，其函数依赖集为F={A→B，CE→A，E→D}

请回答如下问题：21.

指出R的所有候选码，并说明理由；

正确答案：R的候选码为(C，E)，根据已知的函数依赖可知，CE→ABCDE，而C和E之间不存在函数依赖关系，所以R的主键是CE。

22.

R最高属于第几范式(在INF～3NF范围内)，为什么?

正确答案：R最高属于INF，因为CE→D是部分依赖关系。

23.

将R分解到3NF。

正确答案：R分解如下：R1={C，E，A}，R2={E，D}，R3={A，B}，则以上三个关系模式均属于3NF。

24.

在登录MySQL时遇到“ERROR1040(00000):Toomanyconnections”错误，如何解决?正确答案：该错误表示连接数过多，不能正常登录数据库。主要原因是max_connections参数设置过小，该参数表示允许客户端并发连接的最大数量，默认值是151，最小值为1，最大值为100000。需要注意的是，其实MySQL允许的最大连接数为：max_connections+1，因为超出的一个用户是作为超级管理员来使用的。所以，若max_connections的值设置为1，则第3个客户端登录才会报“Toomanyconnections”的错误。

示例如下所示：

mysql＞setglobalmax_connections=1;

QueryOK,0rowsaffected(0.00sec)

mysql＞systemmysql

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis26

Serverversion:5.7.19MySQLCommunityServer(GPL)

Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits

affiliates.Othernamesmaybetrademarksoftheirrespective

owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql＞systemmysql

ERROR1040(HY000):Toomanyconnections

mysql＞systemmysql-h192.168.59.159-uroot-plhr

mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.

ERROR1040(HY000):Toomanyconnections

mysql＞setglobalmax_connections=151;

QueryOK,0rowsaffected(0.00sec)

mysql＞systemmysql

WelcometotheMrSQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis27

Serverversion:5.7.19MySQLCommunityServer(GPL)

Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsrese