MYSQL必知必會

一.?DDL 數據定義語言

Data Definition Language 是指CREATE，ALTER和DROP語句。

DDL允許添加/修改/刪除包含數據的邏輯結構，或允許用戶訪問/維護數據(數據庫，表，鍵，視圖......)的邏輯結構。DDL是關于“元數據”的。

Delimiter詳解

Delimiter?指定命令行結束符。告訴MYSQL命令已經結束，可以執行了。MYSQL默認的delimiter是分號”;”,但是，有的情況下，希望輸入完整后，再一次執行。這種情況下，首先修改結束符為自己指定的符號。比如//或者$$.在最后輸入完成后，輸入指定的命令行結束符。開始執行整個語句。

(1)?表操作

1．創建表

創建表至少包含兩部分信息。表名稱和列的細節信息。

建表語言例子

CREATE TABLE table_name

(

Column1 int not null,

....

PRIMARY_KEY(Column1)

)engine= InnoDB|MyISAM;

建表語言描述

建表語句，CREATE TABLE + 表名稱 然后用小括號包含字段列表(需要定義主鍵primary_key)。最后是引擎類型。MySQL通常有InnoDB(事務處理的好)和MyISAM(支持全文檢索)兩種

引擎類型

MySQL具有多種引擎。SQL是在引擎中執行的。

InnoDB：可靠的事務處理引擎，不支持全文檢索。

MyISAM：不支持事務，支持全文檢索

MEMORY：功能和MyISAM類似。數據是在內存中保存，而不是磁盤。速度快，特別適合臨時表。

2．修改表

Alter table table_name add column_n data_type;

3．刪除表

Drop table table-name；

4．重命名表

Rename table table-name to table-name2;

(2)?視圖

視圖是虛擬的表，只包含使用時動態檢索數據的查詢。

1．使用視圖的原因

復用SQL語句

簡化基于視圖的SQL。

保護數據

因為視圖只能查詢部分字段。使用視圖可以授予指定部分訪問權限，而不是整個的訪問權限。

更改數據的格式和表示

可以把數據格式進行轉換之后，提供給用戶。(對檢索出來的數據重新格式化)

2．視圖相關的語句

創建視圖

Create view view_name AS ---；

刪除視圖

Drop view view_name；

更新視圖

Create or replace view_name?；

視圖的數據是可以更新的。意味著可以對視圖進行insert、update和delete操作的。但是視圖如果包含以下情況，將不可以更新。

分組(group by 和having)

關聯(任何join)

子查詢

并(union 和union All)

聚集函數(count()、sum()、avg()、max()、min())

去重distinct

導出(計算)列

查看創建視圖語句

Show create view view_name;

(3)?存儲過程

1)?使用存儲過程的原因

執行速度快

存儲過程只在創建的時候編譯一次。以后每次執行都不用編譯。而SQL語句每次執行，都需要編譯一次。所以，存儲過程能提高執行效率。

編譯：SQL通常在db接收到執行一般有以下幾個操作。

(1)語法、語義解析。

(2)優化SQL語句，制定執行計劃。

(3)執行并返回結構。

其中，語法語義解析、優化SQL語句、制定執行計劃是編譯的部分。

減少網絡傳輸

復雜邏輯需要多條SQL語句，客戶機和數據庫服務器之間的操作多時，產生大量的網絡傳輸。而封裝在一個存儲過程中，減少網絡負載。

更靈活

使用條件判斷和游標，使用數據庫內置函數，有更強的靈活性和復雜性。

2)?存儲過程的操作語句

創建存儲過程

Create procedure 存儲過程名稱([參數列表]) begin SQL語句end;

參數列表格式

[類型限定?變量名?數據類型]

類型限定：

(1)IN

限定這個參數是傳值給存儲過程的。【in修飾的值，在存儲過程內部不會被修改】

(2)OUT

限定這個值是從存儲過程傳出的一個值。

(3)INOUT

既可以作為參數傳遞給存儲過程，也可以在存儲過程中被修改，傳值出來。

存儲過程的調用

調用存儲過程

Call 儲存過程名(參數);

對于In類型的參數，參數可以是數值，也可以是變量。對于out/inout必須是變量。

MYSQL變量必須以@開始。比如call 存儲過程名(“xxx”,@變量名)

存儲過程的刪除

Drop procedure if exists 存儲過程名稱;

存儲過程的亮點使用

游標

存儲過程中可以使用游標，可以對檢索出來的數據集合進行遍歷操作。

條件分支

可以根據條件分支，建立SQL執行分子，使存儲過程更加智能。

IF XXX THEN ?SQL語句END IF

(4)?游標

游標是MYSQL數據庫上的數據庫查詢出來的數據結果集。且只能用于存儲過程。

1)?使用游標的步驟

定義游標

這個過程實際上沒有檢索數據，只是定義要使用的SELECT語句。

打開游標

這個過程用前面定義的SELECT語句把數據實際檢索出來。

使用游標

對于填有數據的游標，根據需要取出(檢索)各行。

關閉游標

結束游標使用時，必須關閉游標。

2)?創建游標

定義游標

DELCAREordernumbers??CURSOR

FOR

SELECT order_num FROM orders;

打開游標

OPEN?ordernumbers;

關閉游標

CLOSEordernumbers;

3)?使用游標

1．FETCH：獲取每一條數據

FETCH指定檢索什么數據(所需的列)，檢索出來的數據存儲在什么地方。它還向前移動游標中的內部行指針，使下一條FETCH語句檢索下一行(不重復讀取同一行)。

2．FETCH：循環檢索數據

DELIMITER?//CREATE?PROCEDURE?processorders()BEGIN

-- Declare local variables

DECLARE?done BOOLEAN?DEFAULT?0;

DECLARE?o INT;

-- Delcare the cursor

DELCARE ordernumbers CURSOR

FOR

SELECT?order_num FROM?orders;

-- Declare continue handler

DECLARE?CONTINUE?HANDLER?FOR?SQLSTATE '02000'?SET?done=1;

-- Open the cursor

OPEN?ordernumbers;

-- Loop through all rows

REPEAT

-- Get order number

FETCH?ordernumbers INTO?o;

-- End of loop

UNTIL done END?REPEAT;

-- Close the cursor

CLOSE?ordernumbers;END?//DELIMITER?;

這里的FETCH在REPEAT內，因此反復執行直到done為真。

上面這條語句定義了一個CONTINUE HANDLER，它是在條件出現時被執行的代碼。指出當SQLSTATE '02000'出現時，SET done=1。

DECLARE語句的次序

i. DECLARE語句定義的局部變量必須在定義任意游標或句柄之前定義

ii. 句柄必須在游標之后定義(如循環的條件等)

(5)?觸發器

觸發器是一種和數據操作有關的數據庫對象。當觸發器所在的表出現指定的事件時，將調用該對象。

1)?創建觸發器

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個觸發器。

2)?NEW和OLD詳解

NEW和OLD用來表示觸發器的所在表中，觸發了觸發器的那一行數據。具體如下:

在INSERT觸發器中。NEW用來表示將要BEFORE或者已經AFTER插入的數據。

在UPDATE觸發器中，OLD用來表示將要或者已經被修改的原數據。NEW用來表示將要或者已經被修改后的新數據。

在DELETE觸發器中。OLD用來表示將要或者已經被刪除的原數據。

使用方法：

NEW.columnName (columnName為相應數據表某一列名)

OLD 是只讀的，而NEW則可以在觸發器中使用SET賦值，這樣不會再次觸發觸發器，造成循環調用(例如，在一列前添加前綴等)

3)?刪除觸發器

Drop trigger [IF EXISTS] trigger_name ;

4)?觸發器的執行順序

觸發器一般建立InnoDB引擎的MYSQL.所以，觸發器也是事務安全的。

如果BRFORE 觸發器執行失敗，SQL無法執行。

如果SQL執行失敗，則AFTER觸發器就不會執行。

如果AFTER觸發器執行失敗，SQL就會回滾。

二.?DML數據庫操作語言

Data Manipulation Language (數據操作語言)是指INSERT，UPDATE和DELETE語句

DML允許自己添加/修改/刪除數據。

(1)?增-插入數據

插入數據的同時，會插入數據對應的索引。所以，比較耗時。如果，需要盡快將數據插入，方便及時查詢數據。可以先插入數據。之后再生成索引。

在insert into之間加入關鍵字。LOW_PRIORITY. =>INSERT LOW_PRIORITY INTO

1)?插入單行數據

INSERT INTO TABLE_NAME(column1,...column_N) values(value1，... value_N)

2)?插入多行數據

INSERT INTO TABLE_NAME(column1,...column_N) values(value1，... value_N),(value1，... value_N),(value1，... value_N)...,(value1，... value_N);

3)?插入查詢數據 insert into select

INSERT INTO TABLE_NAME(column1,...column_N) selectcolumn1 , ... column_N fromTABLE_NAME2;

(2)?刪-刪除數據

DELETE FROM TABLE WHERE 子句;

Truncate 和delete比較?; -刪除整張表。比delete from table速度快。主要原因是，先刪除原來的表，再創建一個新表。

(3)?改-修改數據

UPDATE TABLE SET COLUMN =“xxx”WHERE 子句;

三.?DQL 數據庫查詢語言

Data Query Language (數據查詢語言)是指SELECT，SHOW和HELP語句(查詢)

SELECT是主要的DQL指令。它會檢索您需要的數據。SHOW檢索有關元數據的信息。HELP...適合需要幫助的人。

(1)?基本查詢

Select 的子句順序如下：

Select -> from ->where ->group by -> having -> order by ->limit

1)?Select

Select?columns|* from table ；字段別名使用 column as alias

1．常規字段

Select子句中，column一個或者多個或者*

2．拼接字段

使用拼接函數concat(),拼接一個或者多個字符/字段 作為一個字段返回。

3．計算字段

多個字段進行四則計算，作為一個字段返回。

4．測試使用

Select 可以不配合from子句，自己直接運行。通常用來測試/驗證函數。

5．去重

在select 和form之間 使用distinct。對檢索出的數據集去重。

2)?Aggregate function

聚集函數

需注意兩點：

其一：一種是ALL模式，也是默認的模式。另外一種DISTINCT模式。DISTINCT是對數據先進行去重，之后再進行聚合運算。

其二：聚合函數和分組子句并不需要一定配合使用。也就是說，聚合函數單獨使用就是對當前數據集進行聚合運算。聚合函數和分組子句配合使用，就是先分組，然后在各個組上再進行聚合運算。

1．Count(*|column)

有兩種使用，count(*) 和count(column)。都是確定表中的行數。區別。第一個count(*)代表所有的行數，真正查出集合的行數。第二個column(column)代表所有行中column非空的行數。

2．Avg(column)

針對column 如果為null，在計算平均值的時候，不計算在內(總值不會加,行數也不會加)。

3．Sum(column)

返回列之和

4．Max(column)

返回列中最大值

5．Min(column)

返回列中最小值

3)?Where

搜索條件、過濾條件。復雜邏輯的時候，最好使用小括號，邏輯關系更清楚一些。

1．單條件過濾

等于、不等于、大于、小于等直接過濾

范圍 Where column begin?start_value and?end_value ;

空值 where column is null ;

2．組合Where

And 操作

Where?column1_condition and?column2_condition ;

可以是2個或者多個條件，使用and鏈接。相當于邏輯且。檢索出的數據集合必須兩個條件都滿足。

Or 操作

Where?column1_condition orcolumn2_condition ;

相當于邏輯或，只要滿足多個條件中的一個就行。

In 操作

Where?columnin (值域集合，用逗號隔開);

功能上類似or，column是集合中任何一個即可。

Not 操作

否定它之后跟的任何條件。Not 只和IN、Between和Exists三個配合使用。

3．Like

模糊匹配，對不確定的進行匹配。是整列匹配。意思是，描述的是整列的結構。整列的結構和通配符描述的結構一致，才可以。通配符:用來匹配值的一部分的特殊字符。

模糊匹配效率比較低，所以在多個條件過濾的時候，將模糊匹配放在最后執行。

Wherecolumn likeXXX(使用通配符)

%

%表示任意字符出現任意多次【0或者多個】。

_

只匹配單個字符而不是多個字符。

4．Regexp

正則表達式的作用是匹配文本。將一個模式(正則表達式)和一個文本進行比較。不是整列匹配。只要正則描述的單元在列中出現，這行數據即匹配上了。

Wherecolumn regexp“model”?;

基本字符匹配

進行OR匹配

Or 在正則中表示”|”

匹配幾個字符之一

用中括號[]

匹配范圍

[1-5]

匹配特殊字符

轉義字符 \\緊跟特殊字符。

匹配多個實例

*代表0或者多個；+代表一個或多個；？代表0個或者1個；｛n｝代表指定數據個；｛n,｝不少于指定數目個；｛n,m｝匹配數據的范圍

定位符

^代表文本的開始。$代表文本的結束。[[:<:>:]]代表詞的結束。

4)?Group By

數據分組：允許對數據邏輯分組，以便對每個組進行聚合計算。但是聚集函數不是必須需要和分組子句配合使用的。

1．創建分組

通常在where 子句后 ，使用Group By columns；

如果多個分組字段，使用逗號隔開。

Group by 語句中的對應的select子句。必須是聚集函數或者是group by后的字段。

如果有where 子句。則group by出現在where子句之后，在order by子句之前。

Rollup，使用with ROLLUP關鍵字，可以使得每個分組以及每個分組匯總級別的值。

2．Having 分組過濾

所有的where 都可以使用having替代。唯一差別是where是過濾行，having是過濾分組。

Having aggregate function() 比較符 數值。

3．分組和排序

Order by 可以和Group By配合使用，通常order by跟在group By子句之后。對數據集合分組聚合之后，再按照指定的字段進行排序。

5)?Order by

排序

后面跟一個或多個字段。升序或者降序排列的確定，需要給每個字段指定。比如 order by column1 desc?，column2 asc

其中 desc 為降序，asc為升序。

6)?Limit 限制返回結果

1．Limit 2

返回最前面2行。如果總行數小于2，則全部返回。

2．Limit 2，3

從第2行開始，返回3行，即為行3至行5。檢索出來第一行是0而不是1.

(2)?復雜查詢

1)?子查詢

嵌套在其它查詢中的查詢。

子查詢的執行順序是由內向位處理的。

1．子查詢做過濾

即在where 子句中使用子查詢。子查詢的結果作為外層查詢的過濾集合。

2．子查詢作為計算字段

即在select 子句中，某些字段使用子查詢來實現。

2)?關聯組

關聯的本質是將第一張表的每一行和第二張表中的每一行配對。

1．內關聯

Inner join

使用格式為select XXX from table1 inner join?table2 oncondition

最后選擇出的是兩側都有值的行。

2．外關聯

Left/right join

假設 A left/right B ，如果是左關聯，則可以理解為A是主表。如果是右關聯，則B表位主表；主表的所有的行都存在，附表中只有關聯上的行才存在。

3．自關聯

同一張表和自己關聯。稱之為自關聯。關聯需要使用別名。

3)?組合查詢

Union?將多個查詢結果并起來返回 稱之為組合查詢。

場景：其一：兩種表結構類似的表查詢，返回后union。其二：對同一張表多次查詢，結果合并返回。

1．Union 和union all區別

Union 是去除重復的。

Union All 是不去重復的。多個元素行，如果有重復的。會保留。

2．組合查詢排序

Union 的 最后一條select語句之后，使用order by子句。結果就是對整個union 句子進行排序。

(3)?操作符

1)?比較操作符

大于>小于

2)?算數操作符

加+減-乘*除/

3)?連接操作符

且and 或or

4)?邏輯操作符

Between ... and

like/not like

in/not in

(4)?通配符

% 代表0個或多個字符

_ 代表一個字符

(5)?常用函數

1)?字符串函數

Left(str,length)、right(str,length)

從左開始截取指定長度的字符串

從右開始截取指定長度的字符串

Length(str)

返回字符串的長度

Locate(substr,str)

返回子串在字符串中第一個位置。

Lower(str)、Upper(str)

字符串轉小寫、字符串轉大寫

Trim(Str)、LTrim(str)、RTrim(str)

字符串兩邊去空格、字符串左邊去空格、字符串右邊去空格

substring(str, pos),substring(str, pos, length)

分別代表

字符串從pos位置開始截取，直至字符串尾部。

字符串從pos位置開始截取，截取長度為length個字符的字符串。

2)?聚集函數

3)?數值處理函

4)?數日期函數

AddDate()、DayOfWeek()

(6)?復雜函數

(7)?全文本搜索

四.?DCL數據庫控制語言

data control language (數據控制語言)是指GRANT和REVOKE語句

DCL用于授予/撤消對數據庫及其內容的權限。DCL很簡單，但MySQL的權限相當復雜。DCL是關于安全性的。

MYSQL數據庫的安全基礎是：用戶對自己需要的數據具有適當的權限。

DCL思維導圖

(1)權限

權限類型

常用權限(pri_type)包括：ALL, SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP等

級別全局級

*.* ?所有數據庫

數據庫級

.* ??某一個數據庫，例如：test.*

表級

. ??某個數據庫的某個表：test.students

(2)用戶權限操作

給用戶授權

grant pri_type on db_name.t_name to 'username'@'host' ; grant pri_type on db_name.t_name to 'username'@'host' ;

刪除用戶權限

revoke pri_type on db_name.t_name from 'user'@'host';

查看權限指定用戶(root用戶)

show grants for 'username'@'%';

自己

show grants;

(1)?用戶管理

1)?創建用戶

MYSQL是一個數據庫管理系統。有自帶數據庫和用戶創建的數據庫。用戶創建數據庫的結構在自帶數據庫中保存。同樣，創建的用戶在mysql數據庫的user表中保存。

Create USER‘用戶名’@’允許其登錄的地址’identified by?‘密碼’；

說明

創建的用戶需同時指定該用戶可以在哪個地址進行登錄。

其中“%”代表“任何地址”。

用戶創建之后，自動在mysql的user表中添加了一條記錄，但該用戶還沒有權限。

2)?刪除用戶

DROP USER?‘用戶名’@’允許其登錄的地址’；

3)?修改用戶密碼

修改自己密碼

set password = password(‘新密碼’);

修改其它用戶密碼

set password for?‘用戶名’@’允許其登錄的地址’ =password(‘新密碼’);

(2)?權限分配

1)?添加權限

Grant

添加權限至少包含三部分內容。要授予的權限、被授予權限的數據庫或者表、用戶名。

格式：

Grant權限名1，...權限名nON數據庫名.對象名TO‘用戶名’@’允許登錄的地址’?；

常用權限(pri_type)包括：ALL, SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP等

對象名：代表數據庫中的東西，通常是表、視圖、存儲過程。

其中”*.*”代表所有數據庫的所有對象。

2)?收回權限

Revoke，格式如下

revoke?權限名1，權限名2，....on 數據庫名．對象名from‘用戶名’@’允許其登錄的地址’ ;

五.?DTL事務控制語言

Data Transaction Language(數據事務語言)是指START TRANSACTION，SAVEPOINT，COMMIT和ROLLBACK [TO SAVEPOINT]語句。

DTL用于管理事務(包含更多指令的操作，如果其中一個指令失敗，則不能執行這些指令)。

(1)?是什么事務

事務就是用來保證多條”增刪改”語句執行的一致性。要么都執行，要么都不執行。

MYSQL 的執行引擎，innoDB支持事務。MyISAM不支持事務。

(2)?事務的特點

1)?原子性

一個事務中的所有語句，應該做到：要么都做，要么都不作。

2)?一致性

數據在邏輯上保持”合理性”。

3)?隔離性

如果多個事務并發執行，每個事務都像各自獨立執行一樣。

4)?持久性

一個事務執行成功，對數據而言，是一個明確的磁盤數據更改(而不是僅僅內存的變化)。

(3)?事務模式

在cmd命令行模式中，是否開啟了”一行命令就是一個事務”的這個開關。這個稱謂自動提交模式。

1．自動提交模式

MYSQL默認是自動提交模式。autocommit=1；

2．手動提交模式

Set autocommit=0；如果設置為手動提交模式，則所有的增刪改操作都需要手動的提交。即在執行SQL語句后，再執行commit才能生效；

(4)?事務執行流程

1．事務的幾個概念

事務Transaction

指一組SQL語句。

回退Rollback

指撤銷執行指定SQL語句的過程。

提交Commit

指將為未存儲的SQL語句結果寫入數據庫。

保留點Savepoint

指事務處理中設置的臨時占位符。可以對它發布回退。可以在之前定義一個位置為保留點，需要回滾的時候，可以回滾到指定的保留點。

2．事務的執行流程

開始事務

Start transaction ；

執行多條增刪改語句

根據語句的執行結果，提交或者回退

If(沒有出錯){

Commit; //事務提交，所都都執行

}else {

Rollback; //回滾事務，所有都沒執行

}