SQL結構化查詢語——之DQL語言

記憶思路：SQL的DQL語言select查詢命令。
from從哪個表中where以什么條件select查詢哪些列，order by是否基于某字段排序，limit # 輸出多少行。
一、單表查詢
1. 常用查詢語法：

1. SELECT 輸出顯示字段 FROM 表名
2. 無條件查詢語法：SELECT 指定輸出的列 FROM 表名 ;
3. 限制輸出語法：SELECT 指定輸出的列 FROM 表名 LIMIT 顯示記錄數 ;
4. 條件查詢語法：SELECT 指定輸出的列 FROM 表名 WHRER 查詢條件 ；
5. 條件查詢再排序：SELECT 指定輸出的列 FROM 表名 WHRER 查詢條件 order by 指定排序字段 [desc|asc];
6. 條件查詢并限制輸出語法：SELECT 顯示輸出的列 FROM 表名 WHRER 查詢條件 LIMIT 顯示記錄數 ；

2. SELECT過濾輸出列：

1. 實例一：輸出顯示表所有行與列，【代表所有列】
   SELECT FROM students; 查詢students表的所有內容
2. 實例二：輸出顯示字段以別名輸出：【字段名 as 別名】注意as可以省略
   SELECT name as 姓名,age as 年齡 FROM vmlab;
   SELECT name 姓名,age 年齡 FROM vmlab;

3. WHERE過濾輸出行：通過where限定過濾條件

1. 算術操作符：+, -, *, /, %
2. 邏輯操作符：NOT，AND，OR，XOR

3. 比較操作符：=,<=>（相等或都為空）, <>, !=(非標準SQL), >, >=, <, <=
   select from vmlab where age >=30;
   SELECT FROM students WHERE gender='m';查詢所有男生
   SELECT FROM students WHERE id < 3; 查詢students表中id字段值小于3的所有行的所有字段
   SELECT * FROM students WHERE id >=2 and id <=4；查詢id大于等2小于等4的記錄；

4. 區間取值：BETWEEN minnum AND maxnum
   SELECT FROM students WHERE BETWEEN 2 AND 4；查詢id大于等2小于等4的記錄；
5. in 明確指定值：
   select * from vmlab where classid in (1,3,6);
6. 匹配空值與非空值 ：is null、is not null
   select from vmlab where classid is null 匹配classid為空值的行
   select from vmlab where classid is not null 匹配classid為非空值的行

7. 模糊匹配 like
   % 任意長度的任意字符 ，_ 任意單個字符
   SELECT * FROM students WHERE name LIKE 't%'; 基于模糊匹配查詢name字段以字母t開頭的所有記錄

8. 正則表達式匹配
   rlike：
   SELECT FROM students WHERE name RLIKE '.[lo].';基于正則匹配查詢name字段包含字母l或o的記錄
   REGEXP:
   SELECT FROM vmlab WHERE name REGEXP '^h';

4. 分組統計：
GROUP BY根據指定的條件對查詢結果進行“分組”以用于做“聚合”運算，輸出字段一般為：聚合計算的字段和計算結果。

1. 常用的聚合函數：count()計數、avg() 平均值、max() 最大值 、min() 最小值、sum() 求和
2. HAVING: 對分組聚合運算后的結果指定過濾條件。
3. 例：
   select classid,gender,avg(age) from students group by classid,gender;
   select classid,gender,avg(age) from students group by classid,gender having classid is not null;
   5. 數據整形操作
   ORDER BY: 根據指定的字段對查詢結果進行排序
4. 升序：ASC（默認為升序）
   select distinct classid from students order by classid;
   select distinct classid from students order by classid asc;
5. 降序：DESC
   select distinct classid from students order by classid desc;
6. 過濾顯示空值
   select distinct classid from students where classid is null;
7. 過濾不顯示空值；
   select distinct classid from students where classid is not null;
8. 去除重復列 DISTINCT
   select distinct classid from students;
   select from t1 union select from t1;
9. 為輸出列定義別名
   SELECT id stuid,name as stuname FROM students 查詢students表中stuid、stuname字段，并將name字段以別名stuname顯示輸出。

6. 限制輸出：

1. LIMIT [[offset,]row_count]：對查詢的結果進行輸出行數數量限制
   SELECT FROM students ORDER BY name DESC LIMIT 2; 按name字段做降序排列，并出輸前2條記錄
   SELECT FROM students ORDER BY name DESC LIMIT 3,5; 按name字段做降序排列，并從第3記錄開始輸出5條記錄

7. 操作實例：

1. 實例一：計算students表中男女生平均年齡，思路：用group by對性別字段進行分組，然后用avg()函數對年齡字段求平均數，最后輸出性別、年齡字段計算的平均數。

select gender,avg(age) from students group by gender;
2. 實例二：計算students表中每個班級的男女生平均年齡，思路：用group by先對班級字段進行分組，再對同班級的性別做分組，然后對年齡字段求平均烽，最后輸出字段為班級、性別、年齡字段計算的平均數。

select classid,gender,avg(age) from students group by classid,gender;
select classid,gender,avg(age) from students group by classid,gender having classid is not null; having過濾班級為空的學生不統計。過濾條件having一定要用在group by之后，先做分組統計計算然后再做過濾。
select classid,gender,avg(age) from students where classid is not null group by classid,gender; where過濾班級為空的學生不統計。過濾條件where一定要用在group by之前，先做過濾，然后再做分組統計。
3. 實例三：分組統計完成后，用order by對指輸出字段做排序，asc升序排列，desc降序排列。

select classid,gender,avg(age) from students where classid is not null group by classid,gender order by avg(age) asc; asc升序排列
select classid,gender,avg(age) from students where classid is not null group by classid,gender order by avg(age) desc; desc降序排列
4. 實例四：做完分組統計、升降序排列后，做限制輸出，如僅輸出前100條記錄，即TOP100。

select classid,gender,avg(age) from students where classid is not null group by classid,gender order by avg(age) asc limit 5; 升序限制輸出
select classid,gender,avg(age) from students where classid is not null group by classid,gender order by avg(age) desc limit 5; 降序限制輸出
5. 實例五：利用輸出字段別名定制輸出表頭

select classid as 班級,gender as 性別,avg(age) as 平均年齡 from students where classid is not null group by classid,gender order by avg(age) asc limit 5;
6.實列六：以班級為分組計算平均年齡，并顯示平均年齡大于30，且班級ID大于3

select classid,avg(age) from students group by classid having avg(age) >30 and classid >3;

二、多表查詢

1. 子查詢：查詢語句中嵌入另一個查詢語句，將子查詢語句的結果做為父語句過濾條件或輸入結果。
   select name,age from students where age >(select avg(age)from students);
2. 縱向合并：利用union聯合查詢實現多表縱向合并，默認有去重功能，如果不想去重則可以用union all。
   必要條件：多個表之間的合并字段數據類型必須相同，在select后的輸出字段書寫順序必須都一致。
   select * from teachers union select Stuid,name,age,Gender from students;
3. 橫向合并：思路先確定哪個是主表。
   a. 字段數是兩個表選取字段之和
   b. 記錄數是兩個表記錄做笛卡爾乘積，即兩表記錄相乘，所有記錄字段交叉合并，主表的每條記錄與副表的每條記錄分別合并。
   select from students cross join teachers;
   select from students,teachers;
   select students.name,teachers.name,students.classid from students,teachers limit 10;輸出指定字列，必須指定要指定輸出哪個表的列。
   select students.name as 姓名,teachers.name as 老師,students.classid 班級 from students,teachers limit 10;用字段別名定制輸出表頭。
   select st.name as 姓名,te.name as 老師,st.classid 班級 from students as st,teachers as te limit 10;為表指定別名，然后再輸寫輸出字段會更簡潔，表一旦指定別名就必須使用。
4. 內連接：inner join 取兩個表的交集
   邏輯：基于兩個表的某個或某些共有特性為依據橫向合并兩表，使兩表內的記錄基于某條件建立關聯。如果不加合并條件就是做笛卡爾乘積合并。
   實例：
   select from students inner join teachers on students.teacherid=teachers.tid; 新式輸寫
   select from students as s,teachers as t where s.teacherid=t.tid order by stuid;舊式輸寫
   select s.Stuid,s.name,t.name,s.classid from students as s inner join teachers as t on s.teacherid=t.tid order by stuid; 基于表的別名定制輸出字段。
   select s.Stuid as 學號,s.name as 姓名,t.name as 老師,s.classid as 班級 from students as s inner join teachers as t on s.teacherid=t.tid order by stuid;為輸出字段定義別名達到定制輸出表頭的目錄。
   復合條件合并：
   select from students as s inner join teachers as t on s.teacherid=t.tid and s.stuid >10;
   select from students as s inner join teachers as t on s.teacherid=t.tid where s.stuid >10;
   
5. 外連接：
   邏輯：左右是相對概念，因此在合并前先確定哪個表為主表，主表所有記錄將全部輸出，被吞并的表為副 表，副表的記錄會被橫向合并到主表中。
   左外連接：設左側為主表，所有記錄將全部輸出，右為副表，兩表進行橫向合并，將副表（右側表）中符合合并條件的記錄填寫在主表（左側表）的對應記錄內，主表中其余記錄不符合條件的副表，并根據需求定制輸出顯示字段。
   select from students as s left outer join teachers as t on s.teacherid=t.tid;
   左外連接特例：用where設置過濾條件，排除左右表具有某共同特性的記錄，橫向合并輸出主表（左側）不具有共同某特性的記錄。
   select from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is null;
   右外連接：設右側為主表，所有記錄將全部輸出，左為副表，兩表進行橫向合并，將左側
   select from students as s right outer join teachers as t on s.teacherid=t.tid;
   右外連接特例：用where設置過濾條件，排除左右表具有某共同特性的記錄，橫向合并輸出主表（右側）不具有共同某特性的記錄。
   select from students as s right outer join teachers as t on s.teacherid=t.tid where s.teacherid is null;
   完全外連接：兩個表沒有主副之分，兩個表的記錄全部輸出，兩表字段合并，左側表某條記錄右側表沒有值來填寫時則對應字段為空，右側表的記錄左則表沒有值來填充時則對應字段也為空，從而將兩個表合并的同時也將兩個表的記錄全部輸出，相當于兩表互補全并，構成矩形。
   完全外連接特例：排除兩表的交集，保留剩余部分，即交集取反