MySOL程序員面試分類真題3

MySOL程序員面試分類真題3一、簡答題1.

參數(shù)wait_timeout和interactive_timeout的作用和區(qū)別是什么?正確答案：interactive_timeout表示MySQL（江南博哥）服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù)；wait_timeout表示MySQL服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù)。這2個參數(shù)的默認(rèn)值都是28800，單位秒，即8個小時。需要注意的是，這2個參數(shù)需要同時設(shè)置才會生效。

參數(shù)interactive_timeout針對交互式連接，wait_timeout針對非交互式連接。所謂的交互式連接，即在mysql_real_connect()函數(shù)中使用了CLIENT_INTERACTIVE選項。說得直白一點就是，通過MySQL客戶端連接數(shù)據(jù)庫是交互式連接，通過jdbc連接數(shù)據(jù)庫是非交互式連接。其實，針對Client，真正生效的是會話級別的wait_timeout，空閑連接(交互和非交互)超過其會話級別的wait_timeout時間就會被回收掉。在客戶端連接啟動的時候，根據(jù)連接的類型，來確認(rèn)會話變量wait_timeout的值是繼承于全局變量wait_timeout，還是interactive_timeout。對于非交互式連接，類似于jdbc連接，wait_timeout的值繼承自服務(wù)器端全局變量wait_timeout。對于交互式連接，類似于MySQL客戶單連接，wait_timeout的值繼承自服務(wù)器端全局變量interactive_timeout。

可通過查看showprocesslist輸出中Sleep狀態(tài)的時間來判斷一個連接的空閑時間。若超過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數(shù)據(jù)庫中的表結(jié)構(gòu)?正確答案：查看MySQL表結(jié)構(gòu)的命令有如下幾種：

1)DESC表名：

2)SHOWCOLUMNSFROM表名；

3)DESC表名；

4)SHOWCREATETABLE表名；

5)查詢information_schema.tables系統(tǒng)表。

有如下表結(jié)構(gòu)，其中，NAME字段代表“姓名”，SCORE字段代表“分?jǐn)?shù)”。

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.

查詢單分?jǐn)?shù)最高的人和單分?jǐn)?shù)最低的人。

正確答案：查詢單分?jǐn)?shù)最高的人和單分?jǐn)?shù)最低的人。

4.

查詢兩門分?jǐn)?shù)加起來的第2至5名。

正確答案：查詢兩門分?jǐn)?shù)加起來的第2至5名。

5.

查詢兩門總分?jǐn)?shù)在150分以下的人。

正確答案：查詢兩門總分?jǐn)?shù)在150分以下的人。

6.

查詢兩門平均分?jǐn)?shù)介于60和80的人。

正確答案：查詢兩門平均分?jǐn)?shù)介于60和80的人。

7.

查詢總分大于150分，平均分小于90分的人數(shù)。

正確答案：查詢總分大于150分，平均分小于90分的人數(shù)。

8.

查詢總分大于150分，平均分小于90分的人數(shù)有幾個。

正確答案：查詢總分大于150分，平均分小于90分的人數(shù)有幾個。

9.

CAP定理和一般事務(wù)中的ACID特性中的一致性有什么區(qū)別?正確答案：一般事務(wù)ACID中的一致性是有關(guān)數(shù)據(jù)庫規(guī)則的描述，如果數(shù)據(jù)表結(jié)構(gòu)定義一個字段值是唯一的，那么一致性系統(tǒng)將解決所有操作中導(dǎo)致這個字段值非唯一性的情況，如果帶有一個外鍵的一行記錄被刪除，那么其外鍵相關(guān)記錄也應(yīng)該被刪除，這就是ACID一致性意思。

CAP理論的一致性是保證同一個數(shù)據(jù)在所有不同服務(wù)器上的拷貝都是相同的，這是一種邏輯保證，而不是物理，因為網(wǎng)絡(luò)速度限制，在不同服務(wù)器上這種復(fù)制是需要時間的，集群通過阻止客戶端查看不同節(jié)點上還未同步的數(shù)據(jù)維持邏輯視圖。

10.

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

11.

MySQL中的字符串和日期相互轉(zhuǎn)化的函數(shù)是什么?正確答案：MySQL中日期轉(zhuǎn)換為字符串使用DATE_FORMAT函數(shù)，相當(dāng)于Oracle中的TO_CHAR函數(shù)，而將字符串轉(zhuǎn)換為日期格式，使用的函數(shù)為STR_TO_DATE，相當(dāng)于Oracle中的TO_DATE函數(shù)。

STR_TO_DATE函數(shù)的使用示例如下所示：

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數(shù)據(jù)庫?正確答案：連接到MySQL數(shù)據(jù)庫有多種寫法，假設(shè)MySQL服務(wù)器的地址為192.168.59.130，可以通過如下幾種方式來連接MySQL數(shù)據(jù)庫：

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中，索引、唯一索引、主鍵、聯(lián)合索引的區(qū)別，它們對數(shù)據(jù)庫的性能有什么影響。正確答案：索引、唯一索引、主鍵、聯(lián)合索引的區(qū)別如下所示：

1)索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。普通索引(由關(guān)鍵字KEY或INDEX定義的索引)的唯一任務(wù)是加快對數(shù)據(jù)的訪問速度。

2)唯一索引：普通索引允許被索引的數(shù)據(jù)列包含重復(fù)的值，如果能確定某個數(shù)據(jù)列只包含彼此各不相同的值，在為這個數(shù)據(jù)索引創(chuàng)建索引的時候就應(yīng)該用關(guān)鍵字UNIQE把它定義為一個唯一索引，唯一索引可以保證數(shù)據(jù)記錄的唯一性。

3)主鍵，一種特殊的唯一索引，在一張表中只能定義一個主鍵索引，逐漸用于唯一標(biāo)識一條記錄，是用關(guān)鍵字PRIMARYKEY來創(chuàng)建。

4)聯(lián)合索引：索引可以覆蓋多個數(shù)據(jù)列，例如INDEX索引，這就是聯(lián)合索引。

索引可以極大地提高數(shù)據(jù)的查詢速度，但是會降低插入刪除更新表的速度，因為在執(zhí)行這些寫操作時，還需要操作索引文件。

15.

MySQL數(shù)據(jù)類型有哪些屬性?正確答案：數(shù)據(jù)類型的屬性包括auto_increment、binary、default、index、notnull、null、primarykey、unique和zerofill，如下所示：

屬性列auto_increment1.auto_increment能為新插入的行賦予一個唯一的整數(shù)標(biāo)識符，該屬性只用于整數(shù)類型2.auto_increment一般從1開始，每行增加1。可以通過“ALTERTABLETB_NAMEAUTO_INCREMENT=n;”語句強帶被置自動增長列的初始值，但是該強制的默認(rèn)值是保留在內(nèi)存中的。如果該值在使用之前數(shù)據(jù)庫重新啟動，那么這個強制的默認(rèn)值就會丟失，就需要在數(shù)據(jù)庫啟動以后重新設(shè)置3.可以使用LAST_INSERT_ID()查詢當(dāng)前線程最后插入記錄使用的值。如果一次插入了多條記錄，那么返回的是第一條記錄使用的自動增長值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.如果達到最大值，那么繼續(xù)插入會報錯binarybinary屬性只用于char和varchar值。當(dāng)為列指定了該屬性時，將以區(qū)分大小寫的方式排序和比較defaultdefault屬性確保在沒有任何值可用的情況下，賦予某個常量值，這個值必須是常量，因為MySQL不允許插入函數(shù)或表達式值。此外，此屬性無法用于BLOB或TEXT列。如果已經(jīng)為此列指定了NULL屬性，那么當(dāng)沒有指定默認(rèn)值時默認(rèn)值將為NULL，否則默認(rèn)值將依賴于字段的數(shù)據(jù)類型index如果所有其他因素都相同，要加速數(shù)據(jù)庫查詢，那么使用索引通常是最重要的一個步驟。索引一個列會為該列創(chuàng)建一個有序的鍵數(shù)組，每個鍵指向其相應(yīng)的表行。以后針對輸入條件可以搜索這個有序的鍵數(shù)組，與搜索整個未索引的表相比，這將在性能方面得到極大的提升notnull如果將一個列定義為notnull，那么將不允許向該列插入null值。建議在重要情況下始終使用notnull屬性，因為它提供了一個基本驗證，確保已經(jīng)向查詢傳遞了所有必要的值null為列指定null屬性時，該列可以保持為空，而不論行中其他列是否已經(jīng)被填充。null精確的說法是“無”，而不是空字符串或0primarykeyprimarykey屬性用于確保指定行的唯一性。指定為主鍵的列中，值不能重復(fù)，也不能為空。為指定為主鍵a的知賦亨auto_increment屬性是很常見的，因為此列不必與行數(shù)據(jù)有任何關(guān)系，而只是作為一個唯一標(biāo)識符。主鍵又分為以F兩種：(1)單字段主鍵如果輸入到數(shù)據(jù)庫中的每行都已經(jīng)有不可修改的唯一標(biāo)識符，一般會使用單字段主鍵。注意，此主鍵一旦笈置就不能再修改(2)多字段主鍵如果記錄中任何一個字段都不可能保證唯一性，那么就可以使用多字段主鍵。這時，多個字段聯(lián)合起來確保唯一性。如果出現(xiàn)這種情況，那么指定一個auto_increment整數(shù)作為主鍵是更好的辦法unique被賦予unique屬性的列將確保所有值都有不同的值，只是null值可以重復(fù)。一般會指定一個列為unique，以確保該列的所有值都不同zerofillzerofill屬性可用于任何數(shù)值類型，用0填充所有剩余字段空間。例如，無符號int的默認(rèn)寬度是10；因此，當(dāng)“零填充”的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命令在創(chuàng)建表之后增加索引：

createindexlastnameonemployees(lastname(7));

這一次只索引了名字的前7個字符，因為可能不需要其他字母來區(qū)分不同的名字。因為使用較小的索引時性能更好，所以應(yīng)當(dāng)在實踐中盡量使用小的索引。

5)zerofill

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

QueryOK,0rowsaffected(0.02sec)

16.

哪個命令可以查看所有數(shù)據(jù)庫?正確答案：運行命令：showdatabases；

17.

如何查看某一用戶的權(quán)限?正確答案：SHOWGRANTSFORUSERNAME;

18.

用什么命令整理表數(shù)據(jù)文件的碎片?正確答案：OPTIMIZETABLETABLENAME;

19.

MySQL的分庫分表和表分區(qū)(Partitioning)有什么區(qū)別?正確答案：分庫分表是指把數(shù)據(jù)庫中的數(shù)據(jù)物理地拆分到多個實例或多臺機器上去。分表指的是通過一定規(guī)則，將一張表分解成多張不同的表。

表分區(qū)(Partitioning)可以將一張表的數(shù)據(jù)分別存儲為多個文件。如果在寫SQL的時候，遵從了分區(qū)規(guī)則，那么就能把原本需要遍歷全表的工作轉(zhuǎn)變?yōu)橹恍枰闅v表里某一個或某些分區(qū)的工作。這樣降低了查詢對服務(wù)器的壓力，提升了查詢效率。如果分區(qū)表使用得當(dāng)，那么也可以大規(guī)模地提升MySQL的服務(wù)能力。但是這種分區(qū)方式，一方面，在使用的時候必須遵從分區(qū)規(guī)則寫SQL語句，如果不符合分區(qū)規(guī)則，性能反而會非常低下；另一方面，Partitioning的結(jié)果受到MySQL限制，或者說MySQL單實例的數(shù)據(jù)文件無法隔離/擺脫分布式存儲的限制，不管怎么分區(qū)，所有的數(shù)據(jù)還是都在一個服務(wù)器上，沒辦法通過水平擴展物理服務(wù)的方法把壓力分?jǐn)偝鋈ァ?/p>

分表與分區(qū)的區(qū)別在于：分區(qū)從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。

20.

如何解決“ERROR1203(42000):Userrootalreadyhasmorethan'max_user_connections'activeconnections”?正確答案：與錯誤“ERROR1040(00000):Toomanyconnections”類似的還有“ERROR1203(42000):Userrootalreadvhasmorethan'max_user_connections'activeconnections”。該錯誤表示，某個用戶的連接數(shù)超過了max_user_connections的值。參數(shù)max_user_cormections表示每個用戶的最大連接數(shù)，默認(rèn)為0，表示沒有限制。需要注意的是，此處的用戶是以“用戶名+主機名”為單位進行區(qū)分，如下所示：

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

設(shè)有關(guān)系模式R(A，B，C，D，E)，其函數(shù)依賴集為F={A→B，CE→A，E→D}

請回答如下問題：21.

指出R的所有候選碼，并說明理由；

正確答案：R的候選碼為(C，E)，根據(jù)已知的函數(shù)依賴可知，CE→ABCDE，而C和E之間不存在函數(shù)依賴關(guān)系，所以R的主鍵是CE。

22.

R最高屬于第幾范式(在INF～3NF范圍內(nèi))，為什么?

正確答案：R最高屬于INF，因為CE→D是部分依賴關(guān)系。

23.

將R分解到3NF。

正確答案：R分解如下：R1={C，E，A}，R2={E，D}，R3={A，B}，則以上三個關(guān)系模式均屬于3NF。

24.

在登錄MySQL時遇到“ERROR1040(00000):Toomanyconnections”錯誤，如何解決?正確答案：該錯誤表示連接數(shù)過多，不能正常登錄數(shù)據(jù)庫。主要原因是max_connections參數(shù)設(shè)置過小，該參數(shù)表示允許客戶端并發(fā)連接的最大數(shù)量，默認(rèn)值是151，最小值為1，最大值為100000。需要注意的是，其實MySQL允許的最大連接數(shù)為：max_connections+1，因為超出的一個用戶是作為超級管理員來使用的。所以，若max_connections的值設(shè)置為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