比CRUD多一點兒（二）：基礎INSERT、SELECT語句

碼農肥波發表於2019-03-04

這是MySQL系列筆記的第一部分，本系列筆記希望能按照筆者自己學習MySQL技術的經歷來記錄，避免純粹按照內容一塊一塊總結，也就是不同於一般按內容分配章節的書籍的結構，有一個平滑的閱讀曲線。內容比較豐富的技術點會按照專題在多個學習筆記中逐漸深入。

這部分的標題叫比CRUD多一丁點兒，比起最基礎的w3c的SQL教程之外，只多一點的擴充套件，滿足應付從純粹閱讀入門資料到可以上手完成一個簡單的工作的需求。

第二篇的主要內容是基礎SQL語句，會根據我工作中的經驗，每個語句多介紹一點實際開發中比較常見的用法。

SQL是一種程式語言

SQL是Structure Query Language的簡寫。SQL並不只是用來跟資料庫，而是一種完備程式語言，在各種程式語言排行榜上也都是名列前茅。SQL語言的歷史和C語言一樣長，從1970年Codd博士將資料庫領域逐漸發揚光大至今近半個世紀，已經可以算是程式語言中的老人了。筆者認為伴隨著關聯式資料庫(RDBMS)產生的SQL語言隨著軟體底層技術的日益成熟，軟體應用日益滲透到生活方方面面，軟體開發教育也越來越普及（比如前段時間有江蘇省已經將Python語言列入高考），未來可能掌握SQL語言的工程師會遠遠多於掌握C語言的工程師。

同其他語言類似，SQL有標準組織出具的規範，但不同的資料庫也有自己的不同的實現。可類比為各種語言有自己的標準，也有不同的編譯器（或直譯器等）的實現，相信做C++開發的都會被微軟的VC編譯器和GNU組織的GCC編譯器的細微不同而苦惱的經歷。這個問題在SQL語言領域應該是更大一些，不同的資料庫SQL實現有不小的區別，但基本的看SQL語言可以分為三類：

  • DML(Data Manipulation Language)：資料操縱語句，使用者增刪查改資料庫記錄，包括insert, select, update, delete，也就是我們文章標題裡面常說CRUD(create read update delete)時候指代的這四個語句。可能入門學習時候一般說的SQL就是指這種型別的。本篇基礎SQL語句其實也只會介紹此類。

  • DDL(Data Definition Language)：資料定義語言，定義資料庫、資料表、列、索引等。包括create, drop, alter等。

  • DCL(Data Control Language)：資料控制語句，用於控制不同資料的訪問許可權，配置密碼等。暴扣grant, revoke。

本文下來分別介紹DML中的INSERT,SELECT,UPDATE,DELETE。

INSERT語句

最簡單的形式如下：

insert into table_name(id, cardno, `name`, `desc`) values(`1`, 100001, "good", "good boy");  
複製程式碼

第一個括號裡是列名，第二個括號裡是對應順序的值。

一次插入多條資料：

insert into table_name(id, cardno, `name`, `desc`) values(`1`, 100001, "good", "good boy"),(`2`, 100002, "bad", "bad girl"); 
複製程式碼

幾個常見Tip

順帶說幾個常見的小問題，在上面的寫法裡出現的：

  • 表名和列名可以使用“`做轉移符，用途是當列名和表名為desc,select這種SQL的關鍵字和保留字時候必須使用，否則是會報語法錯誤的。
  • 關於保留字和關鍵字不同，比如select是關鍵字，desc是保留字具體可看手冊。實際上人腦也不會記憶這些關鍵字，所以在使用時候就全都使用轉義就好了。學海無涯，每種技術的縮寫、特例等等層出不窮，筆者的習慣是記憶一種比較通用和安全的用法，始終去使用即可。因為很多技術點的多種方法也是一項技術發展的歷史原因造成的，並不是說都記著多種辦法會有切實的場景。如果表自己建立時候，推薦的做法就還是要過一遍上述連結中的保留字，直接不要使用這些詞為好，畢竟這個選名字的時間在建表做設計階段總的時間比起來並不會佔用多少比例。筆者遇到的一些ORM庫之類對這些關鍵字可能並不是處理的十全十美，所以不用是最佳。
  • 不止字元型，其實所有插入內容其實都可以用`引起來。用`不用"還有一個好用的地方是當輸入字串裡面有雙引號"時候，可以免於寫一級轉義。
  • 表常常會有自增ID，其實被設定為自增的id也是可以在insert和update時候指定的，只是當不指定值時候才會自增。

INSERT INTO … SELECT 用法：

這是很常用的技巧，當進行資料匯入時候會碰到插入的資料需要從另一個表中獲取，可以使用insert into ... select的寫法。

例如有供應商和顧客兩張表，有一部分列內容是重合的，就如下寫法

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
複製程式碼

但注意，在statement-based的同步中，這種語句是並不安全的。類似的用法還有 INSERT … ON DUPLICATE KEY UPDATE , INSERT IGNORE。可以看手冊中的詳細介紹。

SELECT語句

最基礎的不再贅述，SELECT用法的內容最豐富，後續會專門開一個題。這裡說幾個常見的Tips。

關於NULL

首先是注意NULL，建表應該避免用NULL值，所有列都可以賦予一個預設值。因為NULL在SELECT、索引、統計函式等很多場合都會有一些讓人Surprise的現象，後續可能會單獨寫一篇關於NULL的部落格。

查詢NULL時候用IS NULL, IS NOT NULL，如果NULL值需要轉換，需要用好關於NULL的幾個函式IFNULL(), ISNULL(), COALESCE()

  • IFNULL(exp，value)可以用在語句中代替exp的位置，表示當exp不為NULL時候使用value中的值替代。
  • ISNULL(exp) 做邏輯判斷
  • COALESCE(....)函式是個多值函式，意思是返回這多個引數中按順序第一個非NULL的值。

ANY和ALL

在各種操作符號如>,<,=後使用ANY和ALL可以表示任意一個和所有的意思。

比如查詢比任意Alert表中id大的Account表值：

select id from Account where id > ANY (select id from Alert);
複製程式碼

比如查詢比自己的id還大的（^_^結果當然是沒有）

select id from Account where id > ALL (select id from Account);
複製程式碼

GROUP BY

GROUP BY的基本用法比較常見，但統計函式除了count,sum等還有幾個常用的聚合函式(Aggregate Function)。

連線函式 group_concat會將分組內的值組裝為一個逗號分隔的陣列

select ag.id, ag.name, group_concat(a.id) as resource_ids from AccountGroup ag left join AccountGroup_Account aga on ag.id=aga.account_group_id left join Account a on aga.account_id=a.id group by ag.id;
複製程式碼

返回會是類似

+----+------------------------------------+--------------+
| id | name                               | resource_ids |
+----+------------------------------------+--------------+
|  1 | admin                              | 1,2,3        |
+----+------------------------------------+--------------+
複製程式碼

count函式可以加distinct表示去重

SELECT COUNT(DISTINCT results) FROM student;
複製程式碼

更多可以看手冊

相關文章