MySQL特有的SQL語句 第一彈

關於SQL，我們總是會有無窮無盡相關的話題，有時候碰到了一些覺得不錯的SQL功能會標記下來，好記性不爛爛筆頭，回頭來看，自己也收集了不少的點子，但是從整體來看，可能僅僅是一瓢水而已。大家有好的想法也歡迎留言，一起總結完善一下。

  首先MySQL裡面的SQL標準可以參考下面的表格，我們今天想總結的是MySQL特有的SQL語句。

  我們可以化繁為簡，把所有的SQL都按照增刪改查的四個維度來對待，只是有些語法的含義更廣一些，比如增，建立表我們也算增的範疇，改，修改欄位也算是改的範疇。

增

create語句

如果要複製表結構資訊，下面的語句真是簡潔有力，會把表test1的結構複製出來。

   create table test like test1;

select into語句

  select into語句可以很快的從表裡取出資料，快速構造出一個表來。

SELECT vale1, value2 into Table2 from Table1
   這裡會要求目標表Table2不存在，因為在插入時會自動建立表Table2

insert語句中的表示式

MySQL裡的insert語句還是很豐富的。比如下面的動態值，設定表示式的方式，MySQL就有自己的口味。

   INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);  

 --ok
    INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);  

--wrong

insert語句

MySQL支援的特有的insert語句，下面的第一種是mysqldump匯出的語句格式，這種方式比較清晰，相比多條SQL語句效果要好太多。

   INSERT INTO table (a, b, c) VALUES (1,2,3) ,(2,3,4);

下面的語句看起來比較特別，解析MySQL binlog會發現，裡面的insert語句是這個樣子的。

  INSERT INTO table SET a=1, b=2, c=3;

刪

drop語句

MySQL裡面的drop語句還是蠻有特點，它不會像Oracle那樣可以單獨刪除索引，刪除是始終要和表關聯起來，這樣想來和IOT的特點還是蠻對味的。

如果你直接刪除一個索引，就會丟擲如下的錯誤。

    ?Drop index ind_account_id2;

    ?ERROR 1064 (42000):

  可以使用如下的方式：

drop index ind_account_id2 on t_user_login_record;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

  或者是用比較經典的寫法：

alter table t_user_login_record drop index account;

級聯刪除

MySQL裡面支援級聯刪除，下面的語句會級聯刪除資料，這一點Oracle目前還是不支援這種方式的。

delete A, B from A, B where A.id = B.aid

改

級聯更新

update A, B set A.a = a1, B.b = b1 where A.id = B.aid

change modify

change,modify用法有些相似，但是細究起來還是有自己適合的使用場景,比如與修改欄位b的型別或者屬性,change還更適合於修改欄位名稱，修改資料型別modify更加直接一些。

ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

replace語句

 MySQL裡面的replace語句還是很有特色的，和Oracle裡面的merge有一拼，它支援兩種模式，資料值和子查詢的方式

replace into x values(...)

或者

replace into x select * from y

renmae語句

 這個功能很可能被大家忽略，但是實際上這個功能很實用，比如要把一個表清理表，如果你把它歸檔到一個歷史資料庫中而暫時不清理資料，這種方式就很快捷。

rename table testsync.t_fund_info to test.t_user_login_record;

Query OK, 0 rows affected (0.05 sec)

查

limit語法

  limit的使用算是MySQL中很經典的使用方法了，它也支援多種模式。

比如只返回第2條結果

select * from x

limit 2

返回第2條到第12條的結果

or limit 2, 10

order by的妙用

如果下面的資料需要排序，

select *from test order by name;
+------+------+
| id   | name |
+------+------+
|    1 | aa1  |
|    3 | aa10 |
|    4 | aa11 |
|    2 | aa2  |
+------+------+
我們希望是按照aa1,aa2,aa10,aa11的順序來顯示，直接這樣寫就可以了。

select *from test order by name+0;
+------+------+
| id   | name |
+------+------+
|    1 | aa1  |
|    2 | aa2  |
|    3 | aa10 |
|    4 | aa11 |
+------+------+

特有的函式

如果需要擷取一下字串，比如擷取出數字，就有很多種實現方式。

比如下面的方式就可行。

mysql> select replace('123456G', right('123456G',1), '');

+--------------------------------------------+

| replace('123456G', right('123456G',1), '')

|+--------------------------------------------+

| 123456                                    

|+--------------------------------------------+

或者是使用cast來過濾

> select cast('123124aabc' as unsigned); 

+--------------------------------+

| cast('123124aabc' as unsigned) |

+--------------------------------+

|                         123124 |

+--------------------------------+

1 row in set, 1 warning (0.01 sec)