Java mysql面試題

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

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

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

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

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

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

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

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

● 外連接：

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

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

● 全連接：先以左表進行左外連接，再以右表進行右外連接。

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

3、SQL之sql注入是什么？

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

● 防止sql注入的方式：

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

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

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

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

查詢時如果已知會得到一條數(shù)據(jù)，這種情況下加上limit 1會增加性能。因為MySQL數(shù)據(jù)庫引擎會在找到一條結(jié)果停止搜索，而不是繼續(xù)查詢下一條是否符合標準直到所有記錄查詢完畢。

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

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

● 用not exists代替not in

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

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

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

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

MyISAM和InnoDB是最常見的兩種存儲引擎，特點如下。

● MyISAM存儲引擎

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

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

● InnoDB存儲引擎

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

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

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

 

 

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

● 連接管理與安全驗證是什么

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

● 解析器是什么

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

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

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

● 執(zhí)行器是什么

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

7、MySQL存儲引擎有哪些？

● InnoDB存儲引擎

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

● MyISAM存儲引擎

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

● MEMORY存儲引擎

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

● NDB存儲引擎

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

● Memory(Heap)存儲引擎

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

● Archive存儲引擎

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

● Federated存儲引擎

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

● Maria存儲引擎

Maria存儲引擎是新開發(fā)的引擎，其設計目標是用來取代原有的MyISAM存儲引擎，從而成為MySQL默認的存儲引擎。

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

8、MySQL事務介紹？

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

SHOW VARIABLES LIKE 'AUTOCOMMIT';

修改自動提交模式，0=OFF，1=ON，注意：修改自動提交對非事務類型的表是無效的，因為它們本身就沒有提交和回滾的概念，還有一些命令是會強制自動提交的，比如DLL命令、locktables等。

SET AUTOCOMMIT = 0;

或

SET AUTOCOMMIT = OFF;

9、事務的四大特征是什么？

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

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

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

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

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

10、MySQL中四種隔離級別分別是什么？

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

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

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

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

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

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

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

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

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

● 計算兩個數(shù)之和（備注：實現(xiàn)計算兩個整數(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 存儲過程

call pr_add(10， 20);

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

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

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

CREATE TRIGGER trigger_name trigger_time
trigger_event ON tbl_name FOR EACH ROW
trigger_stmt

其中：

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

trigger_time：標識觸發(fā)時機，取值為BEFORE或AFTER；

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

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

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

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

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

● 班級表class(班級號classID，班內(nèi)學生數(shù)stuCount)

● 學生表student(學號stuID，所屬班級號classID)

要創(chuàng)建觸發(fā)器來使班級表中的班內(nèi)學生數(shù)隨著學生的添加自動更新，代碼如下：

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ù)庫（showdatabases;）查看表格（showtables;）一樣，查看觸發(fā)器的語法如下：

SHOW TRIGGERS [FROM schema_name];

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

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

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

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