Java mysql面試題

1、SQL中聚合函數有哪些？

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

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

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

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

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

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

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

● 外連接：

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

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

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

● 內連接：顯示表之間有連接匹配的所有行。

3、SQL之sql注入是什么？

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

● 防止sql注入的方式：

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

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

4、MySQL性能優化有哪些？

● 當只要一行數據時使用limit 1

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

● 選擇正確的數據庫引擎

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

● 用not exists代替not in

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

● 對操作符的優化，盡量不采用不利于索引的操作符

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

5、MySQL 數據庫架構圖了解嗎？

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

● MyISAM存儲引擎

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

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

● InnoDB存儲引擎

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

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

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

 

 

6、MySQL架構器中各個模塊都是什么？

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

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

● 解析器是什么

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

● 優化器怎么用

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

● 執行器是什么

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

7、MySQL存儲引擎有哪些？

● InnoDB存儲引擎

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

● MyISAM存儲引擎

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

● MEMORY存儲引擎

MEMORY存儲引擎將表中的數據存儲到內存中，未查詢和引用其他表數據提供快速訪問。

● NDB存儲引擎

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

● Memory(Heap)存儲引擎

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

● Archive存儲引擎

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

● Federated存儲引擎

Federated存儲引擎不存放數據，它至少指向一臺遠程MySQL數據庫服務器上的表，非常類似于Oracle的透明網關。

● Maria存儲引擎

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

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

8、MySQL事務介紹？

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

SHOW VARIABLES LIKE 'AUTOCOMMIT';

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

SET AUTOCOMMIT = 0;

或

SET AUTOCOMMIT = OFF;

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

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

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

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

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

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

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

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

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

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

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

11、MySQL怎么創建存儲過程?

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

● 創建MySQL存儲過程

下面代碼創建了一個叫pr_add的MySQL存儲過程，這個MySQL存儲過程有兩個int類型的輸入參數“a”、“b”，返回這兩個參數的和。

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

● 計算兩個數之和（備注：實現計算兩個整數之和的功能）

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;

● 調用 MySQL 存儲過程

call pr_add(10， 20);

12、MySQL觸發器怎么寫？

MySQL包含對觸發器的支持。觸發器是一種與表操作有關的數據庫對象，當觸發器所在表上出現指定事件時，將調用該對象，即表的操作事件觸發表上的觸發器的執行。

在MySQL中，創建觸發器語法如下：

CREATE TRIGGER trigger_name trigger_time
trigger_event ON tbl_name FOR EACH ROW
trigger_stmt

其中：

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

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

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

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

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

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

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

● 班級表class(班級號classID，班內學生數stuCount)

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

要創建觸發器來使班級表中的班內學生數隨著學生的添加自動更新，代碼如下：

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;

查看觸發器：和查看數據庫（showdatabases;）查看表格（showtables;）一樣，查看觸發器的語法如下：

SHOW TRIGGERS [FROM schema_name];

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

刪除觸發器：和刪除數據庫、刪除表格一樣，刪除觸發器的語法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

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