Java mysql面試題

1、SQL中聚合函數(shù)有哪些？

聚合函數(shù)是對(duì)一組值進(jìn)行計(jì)算并返回單一的值的函數(shù)，它經(jīng)常與select語(yǔ)句中的group by子句一同使用。

● avg()：返回的是指定組中的平均值，空值被忽略。

● count()：返回的是指定組中的項(xiàng)目個(gè)數(shù)。

● max()：返回指定數(shù)據(jù)中的最大值。

● min()：返回指定數(shù)據(jù)中的最小值。

● sum()：返回指定數(shù)據(jù)的和，只能用于數(shù)字列，空值忽略。

2、SQL之連接查詢（左連接和右連接的區(qū)別）？

● 外連接：

● 左連接（左外連接）：以左表作為基準(zhǔn)進(jìn)行查詢，左表數(shù)據(jù)會(huì)全部顯示出來(lái)，右表如果和左表匹配的數(shù)據(jù)則顯示相應(yīng)字段的數(shù)據(jù)，如果不匹配則顯示為null。

● 右連接（右外連接）：以右表作為基準(zhǔn)進(jìn)行查詢，右表數(shù)據(jù)會(huì)全部顯示出來(lái)，左表如果和右表匹配的數(shù)據(jù)則顯示相應(yīng)字段的數(shù)據(jù)，如果不匹配則顯示為null。

● 全連接：先以左表進(jìn)行左外連接，再以右表進(jìn)行右外連接。

● 內(nèi)連接：顯示表之間有連接匹配的所有行。

3、SQL之sql注入是什么？

通過在Web表單中輸入（惡意）SQL語(yǔ)句得到一個(gè)存在安全漏洞的網(wǎng)站上的數(shù)據(jù)庫(kù)，而不是按照設(shè)計(jì)者意圖去執(zhí)行SQL語(yǔ)句。舉例：當(dāng)執(zhí)行的sql為select * from user where username = “admin” or “a” = “a”時(shí)，sql語(yǔ)句恒成立，參數(shù)username毫無(wú)意義。

● 防止sql注入的方式：

預(yù)編譯語(yǔ)句：如，select * from user where username = ？，sql語(yǔ)句語(yǔ)義不會(huì)發(fā)生改變，sql語(yǔ)句中變量用?表示，即使傳遞參數(shù)時(shí)為“admin or ‘a’ = ‘a’”，也會(huì)把這整體當(dāng)做一個(gè)字符創(chuàng)去查詢。

Mybatis框架中的mapper方式中的#也能很大程度的防止sql注入（$無(wú)法防止sql注入）。

4、MySQL性能優(yōu)化有哪些？

● 當(dāng)只要一行數(shù)據(jù)時(shí)使用limit 1

查詢時(shí)如果已知會(huì)得到一條數(shù)據(jù)，這種情況下加上limit 1會(huì)增加性能。因?yàn)镸ySQL數(shù)據(jù)庫(kù)引擎會(huì)在找到一條結(jié)果停止搜索，而不是繼續(xù)查詢下一條是否符合標(biāo)準(zhǔn)直到所有記錄查詢完畢。

● 選擇正確的數(shù)據(jù)庫(kù)引擎

MySQL中有兩個(gè)引擎MyISAM和InnoDB，每個(gè)引擎有利有弊。MyISAM適用于一些大量查詢的應(yīng)用，但對(duì)于有大量寫功能的應(yīng)用不是很好。甚至你只需要update一個(gè)字段整個(gè)表都會(huì)被鎖起來(lái)。而別的進(jìn)程就算是讀操作也不行要等到當(dāng)前update操作完成之后才能繼續(xù)進(jìn)行。另外，MyISAM對(duì)于select count(*)這類操作是超級(jí)快的。InnoDB的趨勢(shì)會(huì)是一個(gè)非常復(fù)雜的存儲(chǔ)引擎，對(duì)于一些小的應(yīng)用會(huì)比MyISAM還慢，但是支持“行鎖”，所以在寫操作比較多的時(shí)候會(huì)比較優(yōu)秀。并且，它支持很多的高級(jí)應(yīng)用，例如：事務(wù)。

● 用not exists代替not in

not exists用到了連接能夠發(fā)揮已經(jīng)建立好的索引的作用，not in不能使用索引。not in是最慢的方式要同每條記錄比較，在數(shù)據(jù)量比較大的操作紅不建議使用這種方式。

● 對(duì)操作符的優(yōu)化，盡量不采用不利于索引的操作符

如：in、not in、is null、is not null 、<> 等某個(gè)字段總要拿來(lái)搜索，為其建立索引：MySQL中可以利用alter table語(yǔ)句來(lái)為表中的字段添加索引，語(yǔ)法為：alter table表名add index(字段名)

5、MySQL 數(shù)據(jù)庫(kù)架構(gòu)圖了解嗎？

MyISAM和InnoDB是最常見的兩種存儲(chǔ)引擎，特點(diǎn)如下。

● MyISAM存儲(chǔ)引擎

MyISAM是MySQL官方提供默認(rèn)的存儲(chǔ)引擎，其特點(diǎn)是不支持事務(wù)、表鎖和全文索引，對(duì)于一些OLAP（聯(lián)機(jī)分析處理）系統(tǒng)，操作速度快。

每個(gè)MyISAM在磁盤上存儲(chǔ)成三個(gè)文件。文件名都和表名相同，擴(kuò)展名分別是.frm（存儲(chǔ)表定義）、.MYD(MYData，存儲(chǔ)數(shù)據(jù))、.MYI(MYIndex，存儲(chǔ)索引)。這里特別要注意的是MyISAM不緩存數(shù)據(jù)文件，只緩存索引文件。

● InnoDB存儲(chǔ)引擎

InnoDB存儲(chǔ)引擎支持事務(wù)，主要面向OLTP（聯(lián)機(jī)事務(wù)處理過程）方面的應(yīng)用，其特點(diǎn)是行鎖設(shè)置、支持外鍵，并支持類似于Oracle的非鎖定讀，即默認(rèn)情況下讀不產(chǎn)生鎖。InnoDB將數(shù)據(jù)放在一個(gè)邏輯表空間中（類似Oracle）。

InnoDB通過多版本并發(fā)控制來(lái)獲得高并發(fā)性，實(shí)現(xiàn)了ANSI標(biāo)準(zhǔn)的4種隔離級(jí)別，默認(rèn)為Repeatable，使用一種被稱為next-keylocking的策略避免幻讀。

對(duì)于表中數(shù)據(jù)的存儲(chǔ)，InnoDB采用類似Oracle索引組織表Clustered的方式進(jìn)行存儲(chǔ)。InnoDB存儲(chǔ)引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是對(duì)比myisam的存儲(chǔ)引擎，InnoDB寫的處理效率差一些并且會(huì)占用更多的磁盤空間以保留數(shù)據(jù)和索引。以下是InnoDB體系架構(gòu)：

 

 

6、MySQL架構(gòu)器中各個(gè)模塊都是什么？

● 連接管理與安全驗(yàn)證是什么

每個(gè)客戶端都會(huì)建立一個(gè)與服務(wù)器連接的線程，服務(wù)器會(huì)有一個(gè)線程池來(lái)管理這些連接；如果客戶端需要連接到MYSQL數(shù)據(jù)庫(kù)還需要進(jìn)行驗(yàn)證，包括用戶名、密碼、主機(jī)信息等。

● 解析器是什么

解析器的作用主要是分析查詢語(yǔ)句，最終生成解析樹；首先解析器會(huì)對(duì)查詢語(yǔ)句的語(yǔ)法進(jìn)行分析，分析語(yǔ)法是否有問題。還有解析器會(huì)查詢緩存，如果在緩存中有對(duì)應(yīng)的語(yǔ)句，就返回查詢結(jié)果不進(jìn)行接下來(lái)的優(yōu)化執(zhí)行操作。前提是緩存中的數(shù)據(jù)沒有被修改，當(dāng)然如果被修改了也會(huì)被清出緩存。

● 優(yōu)化器怎么用

優(yōu)化器的作用主要是對(duì)查詢語(yǔ)句進(jìn)行優(yōu)化操作，包括選擇合適的索引，數(shù)據(jù)的讀取方式，包括獲取查詢的開銷信息，統(tǒng)計(jì)信息等，這也是為什么圖中會(huì)有優(yōu)化器指向存儲(chǔ)引擎的箭頭。之前在別的文章沒有看到優(yōu)化器跟存儲(chǔ)引擎之間的關(guān)系，在這里我個(gè)人的理解是因?yàn)閮?yōu)化器需要通過存儲(chǔ)引擎獲取查詢的大致數(shù)據(jù)和統(tǒng)計(jì)信息。

● 執(zhí)行器是什么

執(zhí)行器包括執(zhí)行查詢語(yǔ)句，返回查詢結(jié)果，生成執(zhí)行計(jì)劃包括與存儲(chǔ)引擎的一些處理操作。

7、MySQL存儲(chǔ)引擎有哪些？

● InnoDB存儲(chǔ)引擎

InnoDB是事務(wù)型數(shù)據(jù)庫(kù)的首選引擎，支持事務(wù)安全表（ACID），支持行鎖定和外鍵，InnoDB是默認(rèn)的MySQL引擎。

● MyISAM存儲(chǔ)引擎

MyISAM基于ISAM存儲(chǔ)引擎，并對(duì)其進(jìn)行擴(kuò)展。它是在Web、數(shù)據(jù)倉(cāng)儲(chǔ)和其他應(yīng)用環(huán)境下最常使用的存儲(chǔ)引擎之一。MyISAM擁有較高的插入、查詢速度，但不支持事務(wù)。

● MEMORY存儲(chǔ)引擎

MEMORY存儲(chǔ)引擎將表中的數(shù)據(jù)存儲(chǔ)到內(nèi)存中，未查詢和引用其他表數(shù)據(jù)提供快速訪問。

● NDB存儲(chǔ)引擎

NDB存儲(chǔ)引擎是一個(gè)集群存儲(chǔ)引擎，類似于Oracle的RAC，但它是ShareNothing的架構(gòu)，因此能提供更高級(jí)別的高可用性和可擴(kuò)展性。NDB的特點(diǎn)是數(shù)據(jù)全部放在內(nèi)存中，因此通過主鍵查找非常快。關(guān)于NDB，有一個(gè)問題需要注意，它的連接(join)操作是在MySQL數(shù)據(jù)庫(kù)層完成，不是在存儲(chǔ)引擎層完成，這意味著，復(fù)雜的join操作需要巨大的網(wǎng)絡(luò)開銷，查詢速度會(huì)很慢。

● Memory(Heap)存儲(chǔ)引擎

Memory存儲(chǔ)引擎（之前稱為Heap）將表中數(shù)據(jù)存放在內(nèi)存中，如果數(shù)據(jù)庫(kù)重啟或崩潰，數(shù)據(jù)丟失，因此它非常適合存儲(chǔ)臨時(shí)數(shù)據(jù)。

● Archive存儲(chǔ)引擎

正如其名稱所示，Archive非常適合存儲(chǔ)歸檔數(shù)據(jù)，如日志信息。它只支持INSERT和SELECT操作，其設(shè)計(jì)的主要目的是提供高速的插入和壓縮功能。

● Federated存儲(chǔ)引擎

Federated存儲(chǔ)引擎不存放數(shù)據(jù)，它至少指向一臺(tái)遠(yuǎn)程MySQL數(shù)據(jù)庫(kù)服務(wù)器上的表，非常類似于Oracle的透明網(wǎng)關(guān)。

● Maria存儲(chǔ)引擎

Maria存儲(chǔ)引擎是新開發(fā)的引擎，其設(shè)計(jì)目標(biāo)是用來(lái)取代原有的MyISAM存儲(chǔ)引擎，從而成為MySQL默認(rèn)的存儲(chǔ)引擎。

上述引擎中，InnoDB是事務(wù)安全的存儲(chǔ)引擎，設(shè)計(jì)上借鑒了很多Oracle的架構(gòu)思想，一般而言，在OLTP應(yīng)用中，InnoDB應(yīng)該作為核心應(yīng)用表的首先存儲(chǔ)引擎。InnoDB是由第三方的InnobaseOy公司開發(fā)，現(xiàn)已被Oracle收購(gòu)，創(chuàng)始人是HeikkiTuuri，芬蘭赫爾辛基人，和著名的Linux創(chuàng)始人Linus是校友。

8、MySQL事務(wù)介紹？

MySQL和其它的數(shù)據(jù)庫(kù)產(chǎn)品有一個(gè)很大的不同就是事務(wù)由存儲(chǔ)引擎所決定，例如MYISAM，MEMORY，ARCHIVE都不支持事務(wù)，事務(wù)就是為了解決一組查詢要么全部執(zhí)行成功，要么全部執(zhí)行失敗。MySQL事務(wù)默認(rèn)是采取自動(dòng)提交的模式，除非顯示開始一個(gè)事務(wù)。

SHOW VARIABLES LIKE 'AUTOCOMMIT';

修改自動(dòng)提交模式，0=OFF，1=ON，注意：修改自動(dòng)提交對(duì)非事務(wù)類型的表是無(wú)效的，因?yàn)樗鼈儽旧砭蜎]有提交和回滾的概念，還有一些命令是會(huì)強(qiáng)制自動(dòng)提交的，比如DLL命令、locktables等。

SET AUTOCOMMIT = 0;

或

SET AUTOCOMMIT = OFF;

9、事務(wù)的四大特征是什么？

數(shù)據(jù)庫(kù)事務(wù)transanction正確執(zhí)行的四個(gè)基本要素。ACID，原子性(Atomicity)、一致性(Correspondence)、隔離性(Isolation)、持久性(Durability)。

● 原子性：整個(gè)事務(wù)中的所有操作，要么全部完成，要么全部不完成，不可能停滯在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會(huì)被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來(lái)沒有執(zhí)行過一樣。

● 一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性約束沒有被破壞。

● 隔離性：隔離狀態(tài)執(zhí)行事務(wù)，使它們好像是系統(tǒng)在給定時(shí)間內(nèi)執(zhí)行的唯一操作。如果有兩個(gè)事務(wù)，運(yùn)行在相同的時(shí)間內(nèi)，執(zhí)行相同的功能，事務(wù)的隔離性將確保每一事務(wù)在系統(tǒng)中認(rèn)為只有該事務(wù)在使用系統(tǒng)。這種屬性有時(shí)稱為串行化，為了防止事務(wù)操作間的混淆，必須串行化或序列化請(qǐng)求，使得在同一時(shí)間僅有一個(gè)請(qǐng)求用于同一數(shù)據(jù)。

● 持久性：在事務(wù)完成以后，該事務(wù)所對(duì)數(shù)據(jù)庫(kù)所作的更改便持久的保存在數(shù)據(jù)庫(kù)之中，并不會(huì)被回滾。

10、MySQL中四種隔離級(jí)別分別是什么？

● 讀未提交（READ UNCOMMITTED）：未提交讀隔離級(jí)別也叫讀臟，就是事務(wù)可以讀取其它事務(wù)未提交的數(shù)據(jù)。

● 讀已提交（READ COMMITTED）：在其它數(shù)據(jù)庫(kù)系統(tǒng)比如SQL Server默認(rèn)的隔離級(jí)別就是提交讀，已提交讀隔離級(jí)別就是在事務(wù)未提交之前所做的修改其它事務(wù)是不可見的。

● 可重復(fù)讀（REPEATABLE READ）：保證同一個(gè)事務(wù)中的多次相同的查詢的結(jié)果是一致的，比如一個(gè)事務(wù)一開始查詢了一條記錄然后過了幾秒鐘又執(zhí)行了相同的查詢，保證兩次查詢的結(jié)果是相同的，可重復(fù)讀也是MySQL的默認(rèn)隔離級(jí)別。

● 可串行化（SERIALIZABLE）：可串行化就是保證讀取的范圍內(nèi)沒有新的數(shù)據(jù)插入，比如事務(wù)第一次查詢得到某個(gè)范圍的數(shù)據(jù)，第二次查詢也同樣得到了相同范圍的數(shù)據(jù)，中間沒有新的數(shù)據(jù)插入到該范圍中。

11、MySQL怎么創(chuàng)建存儲(chǔ)過程?

MySQL存儲(chǔ)過程是從MySQL5.0開始增加的新功能。存儲(chǔ)過程的優(yōu)點(diǎn)有一籮筐。不過最主要的還是執(zhí)行效率和SQL代碼封裝。特別是SQL代碼封裝功能，如果沒有存儲(chǔ)過程，在外部程序訪問數(shù)據(jù)庫(kù)時(shí)，要組織很多SQL語(yǔ)句。特別是業(yè)務(wù)邏輯復(fù)雜的時(shí)候，一大堆的SQL和條件夾雜在代碼中，讓人不寒而栗。現(xiàn)在有了MySQL存儲(chǔ)過程，業(yè)務(wù)邏輯可以封裝存儲(chǔ)過程中，這樣不僅容易維護(hù)，而且執(zhí)行效率也高。

● 創(chuàng)建MySQL存儲(chǔ)過程

下面代碼創(chuàng)建了一個(gè)叫pr_add的MySQL存儲(chǔ)過程，這個(gè)MySQL存儲(chǔ)過程有兩個(gè)int類型的輸入?yún)?shù)“a”、“b”，返回這兩個(gè)參數(shù)的和。

● drop procedure if exists pr_add;（備注：如果存在pr_add的存儲(chǔ)過程，則先刪掉）

● 計(jì)算兩個(gè)數(shù)之和（備注：實(shí)現(xiàn)計(jì)算兩個(gè)整數(shù)之和的功能）

create procedure pr_add (a int,b int)
begin
declare c int;
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set c = a + b;
select c as sum;

● 調(diào)用 MySQL 存儲(chǔ)過程

call pr_add(10， 20);

12、MySQL觸發(fā)器怎么寫？

MySQL包含對(duì)觸發(fā)器的支持。觸發(fā)器是一種與表操作有關(guān)的數(shù)據(jù)庫(kù)對(duì)象，當(dāng)觸發(fā)器所在表上出現(xiàn)指定事件時(shí)，將調(diào)用該對(duì)象，即表的操作事件觸發(fā)表上的觸發(fā)器的執(zhí)行。

在MySQL中，創(chuàng)建觸發(fā)器語(yǔ)法如下：

CREATE TRIGGER trigger_name trigger_time
trigger_event ON tbl_name FOR EACH ROW
trigger_stmt

其中：

trigger_name：標(biāo)識(shí)觸發(fā)器名稱，用戶自行指定；

trigger_time：標(biāo)識(shí)觸發(fā)時(shí)機(jī)，取值為BEFORE或AFTER；

trigger_event：標(biāo)識(shí)觸發(fā)事件，取值為INSERT、UPDATE或DELETE；

tbl_name：標(biāo)識(shí)建立觸發(fā)器的表名，即在哪張表上建立觸發(fā)器；

trigger_stmt：觸發(fā)器程序體，可以是一句SQL語(yǔ)句，或者用BEGIN和END包含的多條語(yǔ)句。

由此可見，可以建立6種觸發(fā)器，即：BEFOREINSERT、BEFOREUPDATE、BEFOREDELETE、AFTERINSERT、AFTERUPDATE、AFTERDELETE。

另外有一個(gè)限制是不能同時(shí)在一個(gè)表上建立2個(gè)相同類型的觸發(fā)器，因此在一個(gè)表上最多建立6個(gè)觸發(fā)器。假設(shè)系統(tǒng)中有兩個(gè)表：

● 班級(jí)表class(班級(jí)號(hào)classID，班內(nèi)學(xué)生數(shù)stuCount)

● 學(xué)生表student(學(xué)號(hào)stuID，所屬班級(jí)號(hào)classID)

要?jiǎng)?chuàng)建觸發(fā)器來(lái)使班級(jí)表中的班內(nèi)學(xué)生數(shù)隨著學(xué)生的添加自動(dòng)更新，代碼如下：

create trigger tri_stuInsert after insert on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID); 
update class set stuCount = c + 1 where classID = new.classID;

查看觸發(fā)器：和查看數(shù)據(jù)庫(kù)（showdatabases;）查看表格（showtables;）一樣，查看觸發(fā)器的語(yǔ)法如下：

SHOW TRIGGERS [FROM schema_name];

其中，schema_name即Schema的名稱，在MySQL中Schema和Database是一樣的，也就是說(shuō)，可以指定數(shù)據(jù)庫(kù)名，這樣就不必先“USE database_name;”了。

刪除觸發(fā)器：和刪除數(shù)據(jù)庫(kù)、刪除表格一樣，刪除觸發(fā)器的語(yǔ)法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

其中，schema_name即Schema的名稱，在MySQL中Schema和Database是一樣的，也就是說(shuō)，可以指定數(shù)據(jù)庫(kù)名，這樣就不必先“USEdatabase_name;”了。