MySQL整理（三）

標籤：es2017   nbsp   ima   最小數   拼接   串連查詢   UI   通過   一個   

　　 一、簡單單表操作

　　　　  （1）簡單CRUD　　　　　　

插入查詢結果	insert into table1(id,name,age) select id,name,age from table2 where id=1
更新操作	update 表名 set 欄位名=’abc’where  id=xxx;
避免重複資料查詢-distinct	Select distinct 去重欄位名 from 表名
數學運算	select id*10 from表名
字串拼接	select concat(name,’的工資是：’,salary)
範圍查詢	select name from 表名 where  id between 5 and 10
去除範圍查詢	select name from 表名 where id not between 5 and 10
為空白查詢	select * from 表名 where field is NULL;
不為空白查詢	select * from 表名 where field is not NULL;
帶in關鍵字集合查詢	select name from表名 where id in (1,2,3)
帶in關鍵字排除查詢	select name from表名 where id not in (1,2,3)
備忘：關於帶in的集合查詢，當集合中存在null值時，如Select name from表名 where id in (1,2,3,null),不會影響查詢結果。
模糊比對like	select name from 表名 where name like ‘XXX%’
備忘：匹配以“xx”開頭的字元以%結尾，以“xx”結尾的字元以%開頭
預留位置_	1、  select name from xuanjie where name like ‘_c%’;（第二個字元為c） 2、  select name from xuanjie where name like ‘__c%;（第三個字元為c）
備忘：“_”萬用字元能匹配單個字元，“%”萬用字元可以匹配任意長度的字串。Like匹配“%%”則表示查詢所有資料記錄。非匹配關鍵字可用not like 表示。
升序	select * from 表名 order by 欄位名 asc;
降序	select * from 表名 order by 欄位名 desc;
多欄位升序，降序	select * from 表名 order by 欄位名1 asc,欄位名2 desc;
Limit關鍵字	select * from 表名 where id < 8 order by id desc limit 3
Limit A,B	A從第幾開始，顯示B條
不等於運算式	select age from 表名where not name=’cc’;

　　　　  （2）統計函數和分組查詢

　　　　　　　

統計表中記錄的條數count()函數	select count(欄位名) from 表名 where id < xxx
統計平均值avg()函數	select avg(欄位名) as xxx from 表名
統計計算求和sum()函數	select sum(欄位名) as xxx from 表名
統計最大值max()函數	select max(欄位名),min(欄位名) from 表名
統計最大值min()函數
備忘：對於MySQL支援的統計函數，如果所操作的表中沒有任何記錄，則count()函數返回0，其他函數則返回NULL。
簡單分組查詢group by	select * from 表名 group by 欄位名
group_concat()函數用以指定顯示每個分組中的指定欄位值	select age,group_concat(欄位名)，count(欄位名) from 表名 group by 分組欄位名
多個欄位分組查詢	group by 欄位名1，欄位名2
Having字句限定分組查詢	select id as uid,group_concat(name) as uname,count(name),avg(age) from xuanjie group by id,age having age > 20;
備忘：在MySQL中，如果想實現對分組進行條件限制，不能通過where來實現，因為該關鍵字主要用來實現條件限制資料記錄。MySQL提供了專門的關鍵字having來實現條件限制分組資料記錄。同時，分組查詢必須為動作表中有重複的資料，否則沒有任何意義。

　　二、多表操作原理

　　　　  MySQL支援通過串連查詢來進行多表的操作，具體操作時，首先將兩個或兩個以上的表按照某個條件進行串連後，再按要求查詢目標資料，串連查詢包括內串連和外串連。但在實際應用中，一般不使用串連查詢，因為笛卡爾乘積的緣故，該操作的效率比較低，所以又出現了同樣適合多表查詢的子查詢。

　　　　  以下介紹，均圍繞以下班級和學生資訊表來展開

　　　　  

　　　　 

　　　　 （1）笛卡爾積：沒有串連表關係返回的結果。如select * from class,student,出現如下結果，笛卡爾積的結果集數為前一個表的資料總和 x 後一個表的資料總和，中間只是單純的串連兩個表，並沒有做資料的匹配等操作。

　　　　 　　　  

　　　　（2）串連：所謂串連，其實就是在表關係的笛卡爾積中，按照某個條件產生的一個新的關係，串連可分為內串連和外串連。

　　　　（3）內串連（INNER JOIN）：所謂內串連，就是在表關係的笛卡爾積中，保留表關係中匹配的記錄，捨棄不匹配的資料記錄，按照匹配的條件可以分為自然串連，等值串連和不等值串連。

　　　　（4）自然串連（NATURAL JOIN）：所謂自然串連，就是表關係的笛卡爾積，根據表關係中相同名稱的欄位自動進行資料匹配，然後去掉重複欄位。

　　　　　　  select * from class natural join student;

　　　　 

　　　　（5）等值串連：所謂等值串連，就是表關係的笛卡爾積，選擇所匹配欄位值相等的資料。如下執行結果，發現與自然串連相比，等值串連會去匹配"="條件，並且在新關係中不會去掉重複欄位，如class_id。

　　　　  

　　　　（6）不等串連：所謂不等串連，就是在表關係的笛卡爾積中，選擇所匹配欄位不等於的條件。如下執行結果，會在笛卡爾積中擷取"!="不等於條件中的資料，並且不會去掉重複欄位，如class_id。

　　　　  

　　　　（7）外串連（OUTER JOIN）：就是在表關係的笛卡爾積中，不僅會保留部分不匹配的記錄，還會保留部分不匹配的記錄。外串連包括左外串連（LEFT OUTER JOIN），右外串連（RIGHT OUTER JOIN）和全外串連（FULL OUTER JOIN），以下基於此兩張表講解外串連：

　　　　  

　　　　（8）左外串連（LEFT OUTER JOIN）：所謂左外串連，就是表關係的笛卡爾積中除了選擇匹配的資料記錄，還包含左邊表中不匹配的資料記錄，如下：

　　　　 

　　　　（9）右外串連（RIGHT OUTER JOIN）：所謂右外串連，就是表關係的笛卡爾積中除了選擇匹配的資料記錄，還包含右邊表中不匹配的資料記錄，如下：

　　　　 

　　　　（10）全外串連（FULL OUTER JOIN）：所謂全外串連，就是表關係的笛卡爾積中，除了選擇相匹配的記錄，還包含左右兩邊表中不匹配的資料記錄。

　　三、子查詢

　　　　 為什麼使用子查詢：在平常的多表串連查詢中，由於會對錶進行笛卡爾積操作，如果多張表的資料記錄大，或欄位多，則進行笛卡爾積的時候就會出現死機，對於有經驗的SQL開發人員，會首先通過統計函數count(*)統計多表的資料記錄數，然後才決定是否使用多表查詢。

　　　　 但如果通過統計函數得到的資料記錄數過大，則不適合使用多表查詢，此時便推薦使用子查詢，所謂子查詢，即在一個主查詢中嵌套了其他的若干查詢，如在select xxx from where xxx中嵌套多select，此時，外層的select被稱為主查詢，內層的則稱為子查詢。

　　　　 以下使用該student表作為樣本：

　　　　 

　　　　 樣本1：子查詢為單行多列

　　　　 

　　　　 樣本2：子查詢為單行多列

　　　　 

　　　　 樣本3：子查詢為多行單列

　　　　 當子查詢返回結果是多行單列資料時，通常會包含in,any,all,exists關鍵字。

　　　　 （1）in關鍵字

　　　　  

　　　　 （2）any關鍵字

　　　　  =any：功能與in一樣

　　　　  >any：比子查詢中返回的最小資料還要大的記錄

　　　　  <any：比子查詢中返回的最大資料還要小的記錄

　　　　  

　　　　 （3）all關鍵字

　　　　  >all：比子查詢中返回的最大的記錄數還要大的資料

　　　　  <all：比子查詢中返回的最小的記錄數還要小的資料

　　　　  

　　　　 （4）exists關鍵字

　　　　  exists查詢時會對外表進行遍曆逐條查詢，然後將結果傳到子查詢中。

　　　　  　　

　　　　  樣本4：多行多列子查詢

　　　　  

 

 

MySQL整理（三）