MySQL必知必會

分頁

• 方式1：

  select * from table order by id limit m, n;

  該語句的意思為，查詢m+n條記錄，去掉前m條，傳回後n條記錄。無疑該查詢能夠實作分頁功能

  但是如果m的值越大，查詢的性能會越低（越後面的頁數，查詢性能越低），因為MySQL同樣需要掃描過m+n條記錄。

• 方式2：

  select * from table where id > #max_id# order by id limit n;

  該查詢每次會傳回n條記錄，卻無需像方式1掃描過m條記錄，在大資料量的分頁情況下，性能可以明顯好于方式1

  但該分頁查詢必須要每次查詢時拿到上一次查詢（上一頁）的一個最值id。

  該查詢的問題就在于，我們有時無法拿到上一次查詢（上一頁）的最值id

  比如目前在第3 頁，需要查詢第5頁的資料，該方法便愛莫能助

• 方式3：

  為了避免能夠實作方式2不能實作的查詢，就同樣需要使用到

  limit m, n

  子句

  為了性能，就需要将m的值盡力的小，比如目前在第3頁，需要查詢第5頁，每頁10條資料，目前第3頁的最大id為#max_id#：

  select * from table where id > #max_id# order by id limit 20, 10;

  其實該查詢方式是部分解決了方式2的問題，但如果目前在第2頁，需要查詢第100頁或1000頁，性能仍然會較差。
• 方式4：

  select * from table as a inner join (select id from table order by id limit m, n) as b on a.id = b.id order by a.id;

  該查詢同方式1一樣，m的值可能很大，但由于内部的子查詢隻掃描了字段id，而不是整張表，是以性能要強于方式1查詢，并且該查詢能夠解決方式2和方式3不能解決的問題。
• 方式5：

  select * from table where id > (select id from table order by id limit m, 1) limit n;

  該查詢方式同方式4，同樣通過子查詢掃描字段id，效果同方式4。

  至于性能的話，方式5的性能會略好于方式4，因為方式5不需要在進行表的關聯，而是一個簡單的比較。

where

如需有條件地從表中選取資料，可将 where 子句添加到select語句中。

SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....

• 查詢語句中你可以使用一個或者多個表，表之間使用逗号, 分割，并使用where設定查詢條件
• 可以在 where 子句中指定任何條件
• 可以使用 and 或者 or 指定一個或多個條件
• where 子句也可以運用于 SQL 的 DELETE 或者 UPDATE 指令。
• where 類似于 if 條件，根據 MySQL 表中的字段值來讀取指定的資料

  

  操作符清單，可用于 WHERE 子句

  想讀取指定的資料，WHERE是非常有用的。

  使用主鍵來作為 WHERE 子句的條件查詢是非常快速的

  如果給定的條件在表中沒有任何比對的記錄，那麼查詢不會傳回任何資料

• MySQL 的 where字元串比較是不區分大小寫的。 可以使用 BINARY 關鍵字來設定 where字元串比較區分大小寫

  

update

修改或更新 MySQL 中的資料

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

update 表 set 列=新值 where 更新條件;

• 可以同時更新若幹個字段
• 可以在

  where

  子句中指定任何條件

  當你需要更新資料表中指定行的資料時 WHERE 子句是非常有用的

• 可以在一個單獨表中同時更新資料

UPDATE替換某個字段中的某個字元

UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') [WHERE Clause]

limit

執行個體：每行5頁，傳回第2頁的資料

• 利用 limit 和 offset 。limit 後數代表傳回幾條記錄，offset後數代表從第幾條記錄開始傳回（第一條記錄序号為0），即偏移記錄條數

  SELECT * FROM employees LIMIT

  5

  OFFSET

  5

• 在 limit x,y
  • y:傳回幾條記錄
  • x:從第幾條記錄開始傳回（第一條記錄序号為0）

    SELECT * FROM employees LIMIT 5,5

limit y,x == limit x offset y

字元串截取

MySQL 字元串截取函數 ：left(), right(), substring(), substring_index()。還有 mid(), substr()。其中，mid(), substr() 等價于 substring() 函數，substring() 的功能非常強大和靈活。

• left(str, length)

  

• right(str, length)

  

• substring(字元串，起始位置); substring(字元串，起始位置，長度)

  

  從字元串的第 4 個字元位置開始取，直到結束

  

  從字元串的第 4 個字元位置開始取，隻取 2 個字元

  

  從字元串的第 4 個字元位置（倒數）開始取，直到結束

  

  從字元串的第 4 個字元位置（倒數）開始取，隻取 2 個字元

order by排序

設定你想按哪個字段哪種方式來進行排序，再傳回搜尋結果。

SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]

• 可用任何字段作為排序條件
• 可設定任意個字段來排序
• 可用

  asc

  或

  desc

  設定查詢結果按升/降序

  預設升序排列

• 可添加

  where ... like

  設定條件

ength和char_length

• length

  擷取字元串長度的内置函數方法，utf8編碼下一個漢字是算三個字元,一個數字或字母算一個字元

  其他編碼下,一個漢字算兩個字元, 一個數字或字母算一個字元。

• char_length

  在任何編碼下，不管漢字還是數字或者是字母都算是一個字元

replace函數

根據主鍵确定被替換的是哪一條記錄

• replace(object,search,replace)

  把object中出現search的全部替換為replace ,傳回替換後的字元串

  select replace('www.jb51.net','w','Ww')--->WwWwWw.jb51.net

  把表table中的name字段中的aa替換為bb

  update table set name=replace(name,'aa','bb')

• replace into

  replace into table (id,name) values('1','aa'),('2','bb')

  向表中插入兩條記錄。如果主鍵id為1或2不存在

  就相當于

  insert into table (id,name) values('1','aa'),('2','bb')

  如果存在相同的值則不會插入資料

1 鍵

主 鍵：

資料庫表中對儲存資料對象予以唯一和完整辨別的資料列或屬性的組合。一個資料列隻能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。

聯合主鍵

顧名思義就是多個主鍵聯合形成一個主鍵組合(主鍵原則上是唯一的，别被唯一值所困擾。)

聯合主鍵的意義：用2個字段(或者多個字段,後面具體都是用2個字段組合)來确定一條記錄，說明，這2個字段都不是唯一的，2個字段可以分别重複，這麼設定的好處，可以很直覺的看到某個重複字段的記錄條數。
           

主鍵A跟主鍵B組成聯合主鍵

主鍵A跟主鍵B的資料可以完全相同，聯合就在于主鍵A跟主鍵B形成的聯合主鍵是唯一的。

複合主鍵

表的主鍵含有一個以上的字段組成,不使用無業務含義的自增id作為主鍵

将多個字段設定為主鍵，形成複合主鍵，這多個字段聯合辨別唯一性，其中，某幾個主鍵字段值出現重複是沒有問題的，隻要不是有多條記錄的所有主鍵值完全一樣，就不算重複

超鍵：

在關系中能唯一辨別元組的屬性集稱為關系模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。超鍵包含候選鍵和主鍵。

候選鍵：

是最小超鍵，即沒有備援元素的超鍵。

外鍵：

在一個表中存在的另一個表的主鍵稱此表的外鍵。

2 資料庫事務的ACID

資料庫事務transanction正确執行的四個基本要素。ACID,原子性(Atomicity)、一緻性(Correspondence)、隔離性(Isolation)、持久性(Durability)。

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

一緻性:在事務開始之前和事務結束以後，資料庫的完整性限制沒有被破壞。

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

持久性:在事務完成以後，該事務所對資料庫所作的更改便持久的儲存在資料庫之中，并不會被復原。

3 視圖

視圖是虛拟的表，與包含資料的表不一樣，視圖隻包含使用時動态檢索資料的查詢；不包含任何列或資料。

使用視圖可以簡化複雜的sql操作，隐藏具體的細節，保護資料；視圖建立後，可以使用與表相同的方式利用它們。

視圖不能被索引，也不能有關聯的觸發器或預設值，如果視圖本身内有order by 則對視圖再次order by将被覆寫。

建立視圖：create view XXX as XXXXXXXXXXXXXX;

對于某些視圖比如未使用聯結子查詢分組聚集函數Distinct Union等，是可以對其更新的，對視圖的更新将對基表進行更新；但是視圖主要用于簡化檢索，保護資料，并不用于更新，而且大部分視圖都不可以更新。

4 删除

• drop直接删掉表
• truncate删除表中資料，再插入時自增長id又從1開始
• delete删除表中資料，可以加where字句

（1）

• DELETE

  每次從表中删除一行，并同時将該行的删除操作作為事務記錄在日志中儲存,以便復原

• TRUNCATE TABLE

  一次性地從表中删除所有的資料,并不把單獨的删除操作記錄記入日志儲存，删除行是不能恢複的,在删除的過程中不會激活與表有關的删除觸發器。執行速度快。

（2） ##表和索引所占空間。

• 表被TRUNCATE 後，這個表和索引所占用的空間會恢複到初始大小
• 而DELETE操作不會減少表或索引所占用的空間
• drop語句将表所占用的空間全釋放掉

（3） 一般而言，drop > truncate > delete

（4） 應用範圍

• TRUNCATE 隻能對TABLE
• DELETE可以是table和view

（5）

• TRUNCATE 和DELETE隻删除資料
• 而DROP則删除整個表（結構和資料）

（6）

• truncate與不帶where的delete :隻删除資料,而不删除表的結構（定義）
• drop語句将删除表的結構被依賴的限制（constrain),觸發器（trigger)索引（index);依賴于該表的存儲過程/函數将被保留，但其狀态會變為：invalid。

（7）

• delete語句為DML（data maintain Language),這個操作會被放到 rollback segment中,事務送出後才生效。如果有相應的 tigger,執行的時候将被觸發
• truncate、drop是DLL（data define language),操作立即生效，原資料不放到 rollback segment中，不能復原

（8）

• 在沒有備份情況下,謹慎使用 drop 與 truncate
• 要删除部分資料行采用delete且注意結合where來限制影響範圍。復原段要足夠大。
• 要删除表用drop;
• 若想保留表而将表中資料删除
• 如果與事務無關，用truncate即可實作。
• 如果和事務有關，或老是想觸發trigger,還是用delete

（9） Truncate table 表名 速度快,而且效率高,因為:

truncate table 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少。DELETE 語句每次删除一行，并在事務日志中為所删除的每行記錄一項。TRUNCATE TABLE 通過釋放存儲表資料所用的資料頁來删除資料，并且隻在事務日志中記錄頁的釋放。

（10）

• TRUNCATE TABLE 删除表中的所有行，但表結構及其列、限制、索引等保持不變。新行辨別所用的計數值重置為該列的種子。
• 如果想保留辨別計數值，請改用 DELETE。
• 如果要删除表定義及其資料，請使用 DROP TABLE 語句。

（11） 對于由 FOREIGN KEY 限制引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，是以它不能激活觸發器

連接配接

在真正的應用中經常需要從多個資料表中讀取資料。

如何使用 MySQL 的 JOIN 在兩個或多個表中查詢資料呢

可以在 SELECT, UPDATE 和 DELETE 語句中使用 MySQL 的 JOIN 來聯合多表查詢。

JOIN 按照功能大緻分為如下三類：

• INNER JOIN（内連接配接,或等值連接配接）：擷取兩個表中字段比對關系的記錄。
• LEFT JOIN（左連接配接）：擷取左表所有記錄，即使右表沒有對應比對的記錄。
• RIGHT JOIN（右連接配接）： 與 LEFT JOIN 相反，用于擷取右表所有記錄，即使左表沒有對應比對的記錄。

在指令提示符中使用 INNER JOIN

我們在RUNOOB資料庫中有兩張表 tcount_tbl 和 runoob_tbl。兩張資料表資料如下：

執行個體

tcount_tbl表

runoob_tbl表

使用INNER JOIN(也可以省略 INNER)來連接配接以上兩張表來讀取runoob_tbl表中所有runoob_author字段在tcount_tbl表對應的runoob_count字段值：

INNER JOIN

以上 SQL 語句等價于：

WHERE 子句

image

LEFT JOIN

left join 與 join 有所不同。 LEFT JOIN 會讀取左邊資料表的全部資料，即便右邊表無對應資料。

嘗試以下執行個體，以 runoob_tbl 為左表，tcount_tbl 為右表，了解 MySQL LEFT JOIN 的應用：

以上執行個體中使用了 LEFT JOIN，該語句會讀取左邊的資料表 runoob_tbl 的所有選取的字段資料，即便在右側表 tcount_tbl中 沒有對應的 runoob_author 字段值。

MySQL RIGHT JOIN

MySQL RIGHT JOIN 會讀取右邊資料表的全部資料，即便左邊邊表無對應資料。

嘗試以下執行個體，以 runoob_tbl 為左表，tcount_tbl 為右表，了解MySQL RIGHT JOIN的應用：

RIGHT JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鳥教程 | 10 | | 2 | 菜鳥教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | NULL | NULL | 22 | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)

以上執行個體中使用了 RIGHT JOIN，該語句會讀取右邊的資料表 tcount_tbl 的所有選取的字段資料，即便在左側表 runoob_tbl 中沒有對應的runoob_author 字段值。