- 我的MYSQL學習心得（1） ：簡單語法
- 我的MYSQL學習心得（2） ：資料型別寬度
- 我的MYSQL學習心得（3） ： 檢視欄位長度
- 我的MYSQL學習心得（4） ： 資料型別
- 我的MYSQL學習心得（5） ： 運算子
- 我的MYSQL學習心得（6） ： 函式
在這個《我的MYSQL學習心得》系列裡面，我一直都把MYSQL跟SQLSERVER進行比較，相互進行比較是學習一樣東西比較好的方法
比較出大家的異同點，從而加深記憶
這一篇《我的MYSQL學習心得（七）》也是一樣，相同的地方略略帶過，不同的地方我會給出例子，謝謝大家的支援o(∩_∩)o
這一節主要介紹MYSQL裡的基本查詢（MYSQL官方參考手冊）
MySQL中select的基本語法形式：
|
1 2 3 4 5 6 |
select 屬性列表 from 表名和檢視列表 [where 條件表示式] [group by 屬性名[having 條件表示式]] [order by 屬性名[asc|desc]] [limit <offset>,row count] |
說明：
where子句：按照“條件表示式”指定的條件進行查詢。
group by子句：按照“屬性名”指定的欄位進行分組。
having子句：有group by才能having子句，只有滿足“條件表示式”中指定的條件的才能夠輸出。
group by子句通常和count()、sum()等聚合函式一起使用。
order by子句：按照“屬性名”指定的欄位進行排序。排序方式由“asc”和“desc”兩個引數指出，預設是按照“asc”來排序，即升序。
建立測試表
建立測試表
|
1 2 3 4 5 6 7 8 |
CREATE TABLE fruits ( f_id CHAR(10) NOT NULL, s_id INT NOT NULL, f_name CHAR(255) NOT NULL, f_price DECIMAL(8,2) NOT NULL, PRIMARY KEY(f_id) ) |
插入測試資料
|
1 2 3 4 5 6 |
INSERT INTO fruits(f_id,s_id,f_name,f_price) VALUES('a1',101,'apple',5.2), ('b1',102,'blackberry',5.2), ('bs1',105,'orange',5.2), ('bs2',103,'melon',5.2), ('t1',106,'banana',5.2); |
使用select語句查詢f_id欄位的資料
|
1 |
SELECT f_id,f_name FROM fruits |
注意：MYSQL中SQL語句是不區分大小寫的，因此select和SELECT作用是相同的
這一點跟SQLSERVER是一樣的！
常用查詢
|
1 2 3 4 5 6 7 8 |
CREATE TABLE fruits ( f_id CHAR(10) NOT NULL, s_id INT NOT NULL, f_name CHAR(255) NOT NULL, f_price DECIMAL(8,2) NOT NULL, PRIMARY KEY(f_id) ) |
帶like的字元匹配查詢
1、百分號萬用字元“%”，匹配任意長度的字元，甚至包括零字元
|
1 |
SELECT f_id,f_name FROM fruits WHERE f_name LIKE 'b%y' |
2、下劃線萬用字元“_”,一次只能匹配任意一個字元
下面語句有四個下劃線
|
1 |
SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____n' |
同樣，在SQLSERVER裡面也是有的
|
1 2 3 |
USE [sss] GO SELECT * FROM [dbo].[aaa] WHERE [name] LIKE 's_____' |
查詢空值
|
1 2 3 4 5 6 7 |
CREATE TABLE customers ( c_id INT NOT NULL AUTO_INCREMENT, c_name CHAR(25) NOT NULL, c_city CHAR(50) NULL, PRIMARY KEY(c_id) ) |
|
1 2 3 |
INSERT INTO customers(c_name,c_city) VALUES('liming','china'), ('hongfang',NULL) |
|
1 |
SELECT * FROM customers WHERE c_city IS NULL |
|
1 |
SELECT * FROM customers WHERE c_city IS NOT NULL |
AND、OR、DISTINCT關鍵字
|
1 |
SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____n' AND f_id='bs2' |
|
1 |
SELECT f_id,f_name FROM fruits WHERE f_name LIKE '____n' OR f_id='bs2' |
|
1 |
SELECT DISTINCT s_id FROM fruits |
GROUP BY
|
1 |
SELECT s_id ,COUNT(1) AS total FROM fruits GROUP BY s_id |
再插入兩條記錄
|
1 2 3 |
INSERT INTO fruits(f_id,s_id,f_name,f_price) VALUES('a6',101,'cherry',6), ('a8',102,'coconut',7) |
如果要檢視每個供應商提供的水果的種類的名稱，MYSQL中可以在GROUP BY中使用GROUP_CONCAT()函式，
將每個分組中各個欄位的值顯示出來
|
1 |
SELECT s_id,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id |
SQLSERVER是沒有GROUP_CONCAT()函式的，SQLSERVER要達到同樣效果需要使用xml函式，MYSQL這方面做得非常好
having：過濾分組
根據s_id對fruits表中的資料進行分組，並顯示水果種類大於1的分組資訊
|
1 |
SELECT s_id ,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id HAVING COUNT(f_name)>1 |
在group by中使用with rollup
|
1 |
SELECT s_id ,COUNT(1) AS total FROM fruits GROUP BY s_id WITH ROLLUP |
增加了最後一行，7表示total列的所有值的總和
而rollup關鍵字在SQLSERVER裡面也有，詳見：SQLSERVER中的ALL、PERCENT、CUBE關鍵字、ROLLUP關鍵字和GROUPING函式
注意：當使用ROLLUP時，不能同時使用ORDER BY子句進行結果排序，即ROLLUP和ORDER BY是互相排斥的！
limit限制查詢結果的數量
在SQLSERVER中是使用TOP關鍵字，而在MYSQL中是使用LIMIT關鍵字
|
1 |
LIMIT[位置偏移量]，行數 |
第一個“位置偏移量”引數指示MYSQL從哪一行開始顯示，是一個可選引數，如果不指定“位置偏移量”
將會從表中第一條記錄開始（第一條記錄的位置偏移量是0，第二天記錄的位置偏移量是1……以此類推）
第二個引數“行數”指示返回的記錄條數
|
1 |
SELECT * FROM fruits |
|
1 |
SELECT * FROM fruits LIMIT 4,3 |
上面結果返回從第5條記錄行（因為從0開始數）開始之後的3條記錄
注意：在MYSQL5.6中可以使用 LIMIT 4 OFFSET 3 ，意思是獲取從第5行記錄開始的3條記錄，和 LIMIT 4,3 返回的結果是一樣的
在SQLSERVER2012裡面開始支援類似MYSQL的這種語法，但是需要按某一列先排序，而不像MYSQL那樣不用按照某一列排序
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
USE [sss] GO CREATE TABLE fruits ( f_id CHAR(10) NOT NULL , s_id INT NOT NULL , f_name CHAR(255) NOT NULL , f_price DECIMAL(8, 2) NOT NULL , PRIMARY KEY ( f_id ) ) INSERT INTO fruits ( f_id , s_id , f_name , f_price ) SELECT 'a1' , 101 , 'apple' , 5.2 UNION ALL SELECT 'b1' , 102 , 'blackberry' , 5.2 UNION ALL SELECT 'bs1' , 105 , 'orange' , 5.2 UNION ALL SELECT 'bs2' , 103 , 'melon' , 5.2 UNION ALL SELECT 't1' , 106 , 'banana' , 5.2 UNION ALL SELECT 'a6' , 101 , 'cherry' , 6 UNION ALL SELECT 'a8' , 102 , 'coconut' , 7 |
先按s_id排序，然後返回第5行開始的3條記錄
|
1 2 3 4 5 |
SELECT * FROM [dbo].[fruits] ORDER BY [s_id] ASC OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY; |
雖然沒有MYSQL那麼方便，不過也算是一種進步，而對於OFFSET FETCH NEXT的效能可以參考宋沄劍的文章：
子查詢
子查詢這個特性從MYSQL4.1開始引入。
插入測試資料
|
1 2 3 4 5 |
CREATE TABLE tbl1(num1 INT NOT NULL); CREATE TABLE tbl2(num2 INT NOT NULL) INSERT INTO tbl1 VALUES(1),(4),(13),(27); INSERT INTO tbl2 VALUES(6),(14),(11),(20) |
ANY關鍵字接在一個比較操作符的後面，表示若與子查詢返回的任何值比較為TRUE，則返回TRUE
返回tbl2表的所有num2列，然後將tbl1中的num1的值與之進行比較，只要大於num2的任何一個值，即為符合查詢條件的結果
|
1 |
SELECT num1 FROM tbl1 WHERE num1>ANY(SELECT num2 FROM tbl2) |
ALL關鍵字接在一個比較操作符的後面，表示與子查詢返回的所有值比較為TRUE，則返回TRUE
|
1 |
SELECT num1 FROM tbl1 WHERE num1>ALL(SELECT num2 FROM tbl2) |
在SQLSERVER中也可以使用ANY和ALL關鍵字
|
1 2 3 4 5 6 7 8 9 10 |
USE [sss] GO CREATE TABLE tbl1(num1 INT NOT NULL) CREATE TABLE tbl2(num2 INT NOT NULL) INSERT INTO tbl1 VALUES(1),(4),(13),(27) INSERT INTO tbl2 VALUES(6),(14),(11),(20) SELECT num1 FROM tbl1 WHERE num1>ANY(SELECT num2 FROM tbl2) SELECT num1 FROM tbl1 WHERE num1>ALL(SELECT num2 FROM tbl2) |
結果是一樣的
合併查詢
使用UNION關鍵字，合併結果時，兩個查詢對應的列數和資料型別必須相同。
各個SELECT語句之間使用UNION或UNION ALL關鍵字分隔
UNION：執行的時候刪除重複的記錄，所有返回的行都是唯一的
UNION ALL：不刪除重複行也不對結果進行自動排序
|
1 2 3 4 5 6 7 |
SELECT s_id,f_name,f_price FROM fruits WHERE f_price<9.0 UNION SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN (101,103) |
第一個查詢把f_price小於9.0的記錄查詢出來，第二個查詢把s_id為101和103的記錄查詢處理
因為f_price小於9.0的記錄裡有些記錄的s_id是102、105、106，這些結果不會被去掉會跟第二個查詢進行合併
所以最終的結果會有s_id為102、105、106的記錄
正規表示式查詢
正規表示式在SQLSERVER裡面是沒有的，但是在MYSQL裡不單隻有，而且功能也比較豐富
MYSQL中使用REGEXP關鍵字指定正規表示式的字元匹配模式
1、查詢以特定字元或字串開頭的記錄
字元“^”匹配以特定字元或者字串開頭的文字
|
1 |
SELECT * FROM fruits WHERE f_name REGEXP '^b' |
返回f_name欄位以b開頭的記錄
2、查詢以特定字元或字串結尾的記錄
字元“$”匹配以特定字元或者字串結尾的文字
|
1 |
SELECT * FROM fruits WHERE f_name REGEXP 'y$' |
返回f_name欄位以y結尾的記錄
3、用符號“.”來代替字串中的任意一個字元
字元“.”匹配任意一個字元
|
1 |
SELECT * FROM fruits WHERE f_name REGEXP 'a.g' |
a和g兩個字母之間包含單個字元，orange符合要求
4、使用“*”和“+”來匹配多個字元
星號“*”匹配前面的字元任意多次，包括0次。加號“+”匹配前面的字元至少一次
|
1 |
SELECT * FROM fruits WHERE f_name REGEXP '^ba*' |
blackberry和banana符合要求，b開頭，a匹配任意多次，不管出現的順序在哪裡
|
1 |
SELECT * FROM fruits WHERE f_name REGEXP '^ba+' |
“a+”匹配字母“a”至少一次，只有banana滿足匹配條件
5、匹配指定字串
正規表示式可以匹配指定字串，只要這個字串在查詢文字中即可，如要匹配多個字串，多個字串之間使用分隔符“|”隔開
|
1 |
SELECT * FROM fruits WHERE f_name REGEXP 'on|ap' |
可以看到apple 、melon 、coconut 3個值中都包含有字串“on”和“ap”，滿足匹配條件
6、匹配指定字元中的任意一個
方括號“[]”指定一個字符集合，只匹配其中任何一個字元，即為所查詢的文字
|
1 |
SELECT * FROM fruits WHERE f_name REGEXP '[ot]' |
方括號[]還可以指定數值集合
|
1 |
SELECT * FROM fruits WHERE s_id REGEXP '[456]' |
s_id欄位值中有3個數字中的1個即為匹配記錄欄位
[456]也可以寫成[4-6]即指定集合區間
7、匹配指定字元以外的字元
“[^字符集合]”匹配不在指定集合中的任何字元
|
1 |
SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]' |
返回開頭不在a-e 1-2字母的記錄，例如a1，b1這些記錄就不符合要求
8、使用{n,} 或者{n,m}來指定字串連續出現的次數
“字串{n,}”，表示至少匹配n次前面的字元；“字串{n,m}”表示匹配前面的字串不少於n次，不多於m次。
|
1 |
SELECT * FROM fruits WHERE f_name REGEXP 'b{1,}' |
至少匹配1次字母b，blackberry和banana都符合要求
|
1 |
SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}' |
“ba”字串最少出現一次，最多三次，banana這個字串符合要求
總結
這一節介紹了MYSQL裡的查詢，並且比較了與SQLSERVER的區別，特別是MYSQL裡的正則查詢靈活多變
這一點比SQLSERVER略為優勝