MySQL查詢用法總結

05-14

select語句的基本結構

select col1,col2from tb_namewhere col1= --where為行級篩選group by col2 --group by根據某個欄位進行分組having count(col1)>1 --having對分組後的數據進行組級篩選order by col1 desc --order by根據某個欄位進行排序 asc升序，desc降序limit 3,5 --limit設置查詢行的範圍

2. 行級篩選數據--where

# where字句的操作符：= 等於<> 不等於!= 不等於< 小於<= 小於等於> 大於>= 大於等於between A and B 指定的兩個值之間where col is null --查詢空值where col is not null --查詢非空值where col = --查詢空字元串where (條件1 and 條件2) or (條件3 and 條件4)--任何時候使用具有and 和 or操作符的where字句時，都應該用圓括弧明確的分組操作符，不要過分依賴默認計算次序where col in (a,b)--in/not in操作符與or是相同的功能，但比or檢索速度更快where col in (select ....) --用in包含其他select語句，實現子查詢的嵌套

模糊匹配查詢：

1.用like操作符+通配符where col like %A% --%可以拼配0個或1個以上的字元，但不能匹配null _只能匹配一個字元注意不要過度使用通配符，會降低檢索性能2.用正則表達式：where col REGEXP .000 --.標識任意匹配1個字元where col REGEXP 100|200|300 --|表示orwhere col REGEXP [123]ton --[]表示另一種形式的or語句，[123]是[1|2|3]的縮寫形式where col REGEXP [1-9]ton --[1-9]表示匹配1-9中任意一個數字where col REGEXP \. --\來匹配特殊字元，也用來引用元字元 \f(換頁),\n(換行),\r(回車)where col REGEXP $[0-9]stick?$ --?匹配0個或1個字元 *--0個或多個匹配，+--1個或多個匹配where col REGEXP 』[[:digit:]]{4}』 --表示連在一起的任意4位數字 {n}--指定數目的匹配 {n,}--不少於指定數目的匹配 {n,m}--指定匹配數目的範圍

3. MySQL中的常用函數

distinct() --去重聚合函數： count() --計數sum() --匯總計算avg() --平均值 max() --最大值min() --最小值std() --標準差abs() --絕對值rand() --隨機數sqrt() --平方根round() --保留幾位小數數據處理函數：concat() --拼接兩個欄位列trim()/ltrim()/rtrim() --去除兩邊空格/左邊空格/右邊空格upper()/lower() --轉換字母大小寫left()/right() --返回左右的字元串locate() --返回中間範圍的字元串length() --返回字元串的長度substring() --返回字串的字元日期時間處理函數：在Mysql中，日期格式必須為yyyy-mm-ddaddDate() --增加一個日期addTime() --增加一個時間curDate() --返回當前日期curDate() --返回當前時間Date() --返回日期部分Date_format(date,%Y-%m-%d) --返回一個格式化的日期或時間串DateDiff() --計算兩個日期之差Date_Add(date,INTERVAL 2 day/month/year) --日期運算函數Year()/Month()/Day()/DayofWeek()/HOur()/Minute()/Second()Time() --返回時間部分Now() --返回當前的日期時間# 有關聚合函數使用注意：avg()只能用來確定特定數值列的平均值，並忽略列值為null的行count(*) 對表中所有行計數，不管是null還是非null，count(col)會忽略null值min()/max()/sum()都忽略null值，sum()在括弧里可以添加合計，例如sum(col1*col2)函數之間可以嵌套使用，例如count(distinct(col))是計數去重後的結果

4. 分組篩選：

select col1,count(*) as cfrom tb_namegroup by col1 with rollup --使用with rollup可得到每組的匯總值having count(*)>1 --having中的條件欄位不能使用別名# 如果在select中使用了表達式，則在group by中要使用相同的表達式，不能使用別名如果分組中有null值，則將null值單獨分為一組 group by必須出現where字句之後，order by之前

5. 使用子查詢

1.使用子查詢進行過濾：select * from tb_name where col in (1,2,3)2.作為計算欄位的子查詢select a.col1, a.col2, (select count(*) from a where a.id = b.id) as countfrom aorder by a.col13.相關子查詢：delete from tb --刪除表中的重複記錄 where id in( select id from( select id from tb a group by a.id having count(id)>1) as b )

6. 表聯接：

自聯接：select col1,col2from tbwhere col3 = (select col3 from tb where col1 = SA)生成笛卡爾積表-cross joinselect a.col1,b.col2from across join b內聯接--inner join:select a.col1,b.col2from ainner join bon a.id = b.idleft join/right join(左聯接/右聯接）select a.col1,b.col2from aleft/right join bon a.id = b.id外連接--full outer joinselect a.col1,b.col2from afull outer join bon a.id = b.id延伸用法：left join excluding inner join返回左表有但右表沒有關聯數據的記錄集。select a.col1,b.col2from aleft join bon a.id = b.idwhere b.id is nullright join excluding inner join返回右表有但左表沒有關聯數據的記錄集。select a.col1,b.col2from aright join bon a.id = b.idwhere a.id is nullfull outer join excluding inner join返回左表和右表裡沒有相互關聯的記錄集。select a.col1,b.col2from afull outer join bon a.id = b.idwhere a.id is null or b.id is null

7.組合查詢：

select * from aunionselect * from b# union中的每個查詢必須包含相同的列，表達式及聚合函數 union會去除兩個查詢中的重複值，如果不要去重，用union all 只能使用一條order by字句，且必須放在最後一個查詢後面

8.創建視圖：

# 視圖是一張虛擬的表，不包含數據，可以簡化複雜的sql操作視圖必須唯一命名，且需要訪問許可權視圖可以嵌套，可以和表一起使用，但不能有索引和創建觸發器或默認值create view view_name asselect col1, col2, col3*col4 as col5from tb_nameselect * from view_name where col1=

9. 創建存儲過程：

# 存儲過程可以簡化複雜操作，提高檢索性能--創建存儲過程Delimiter $create procedure ordertotal( IN number INT, --IN將number傳入存儲過程 OUT total DECIMIAL(8,2) --OUT將total返回合計 )Begin select sum(price*quantity) from orderitems where order_num = number INTO total END $--調用存儲過程：CALL ordertotal(200, @total)select @total