mysql進階之MySQL查詢

一、MySQL多表查詢和子查詢

別名：as

       表別名

       字段別名

       查詢結果亦可命名別名

聯結查詢：事先將兩張或多張表join，根據join的結果進行查詢。

交叉聯結（cross join）：第一張表的每一行與第二張表的每一行做交叉相乘。

自然聯結，也叫等值聯結、內聯結。兩張表中的字段做等值關聯。

自聯結：

外聯結：

       左外聯結：只保留出現在左外聯結運算之前左側的關系中的元組。以左表為尊，右側沒有，右側留空。

       右外聯結：只保留出現在左外聯結運算之前右側的關系中的元組。以右表為尊，左側沒有，左側留空。

mysql> select s.name,c.class from students as
s LEFT JOIN classes as c on s.classid=c.classid; 
+---------------+----------------+
| name| class          |
+---------------+----------------+
| Shi Zhongyu| Emei Pai       |
| Shi Potian| Shaolin Pai    |
| Xie Yanke| Emei Pai       |
| Ding Dian| Wudang Pai     |
| Yu Yutong| QingCheng Pai  |
| Shi Qing| Riyue Shenjiao |
| Xi Ren| QingCheng Pai  |
| Lin Daiyu| Ming Jiao      |
| Ren Yingying| Lianshan Pai   |
| Yue Lingshan| QingCheng Pai  |
| Yuan Chengzhi | Lianshan Pai   |
| Wen Qingqing| Shaolin Pai    |
| Tian Boguang| Emei Pai       |
| Lu Wushuang| QingCheng Pai  |
| Duan Yu| Wudang Pai     |
| Xu Zhu| Shaolin Pai    |
| Lin Chong| Wudang Pai     |
| Hua Rong| Ming Jiao      |
| Xue Baochai| Lianshan Pai   |
| Diao Chan| Ming Jiao      |
| Huang Yueying | Lianshan Pai   |
| Xiao Qiao| Shaolin Pai    |
| Ma Chao| Wudang Pai     |
| Xu Xian| NULL           |
| Sun Dasheng| NULL           |
+---------------+----------------+
25 rows in set (0.00 sec)

mysql> select s.name,c.class from students as s RIGHT JOIN classes as c on s.classid=c.classid;
+---------------+----------------+
| name          | class          |
+---------------+----------------+
| Shi Potian| Shaolin Pai    |
| Wen Qingqing| Shaolin Pai    |
| Xu Zhu| Shaolin Pai    |
| Xiao Qiao| Shaolin Pai    |
| Shi Zhongyu| Emei Pai       |
| Xie Yanke| Emei Pai       |
| Tian Boguang| Emei Pai       |
| Yu Yutong| QingCheng Pai  |
| Xi Ren| QingCheng Pai  |
| Yue Lingshan| QingCheng Pai  |
| Lu Wushuang| QingCheng Pai  |
| Ding Dian| Wudang Pai     |
| Duan Yu| Wudang Pai     |
| Lin Chong| Wudang Pai     |
| Ma Chao| Wudang Pai     |
| Shi Qing| Riyue Shenjiao |
| Ren Yingying| Lianshan Pai   |
| Yuan Chengzhi | Lianshan Pai   |
| Xue Baochai| Lianshan Pai   |
| Huang Yueying | Lianshan Pai   |
| Lin Daiyu| Ming Jiao      |
| Hua Rong| Ming Jiao      |
| Diao Chan| Ming Jiao      |
| NULL| Xiaoyao Pai    |
+---------------+----------------+

24 rows in set (0.00 sec) 

子查詢：在查詢中嵌套查詢，mysql子查詢性能優化一般，應盡量避免使用，可使用聯結查詢替代。

       1、用于比較表達式（WHERE）中的子查詢，其返回值只能唯一。

       2、用于EXISTS中的子查詢，判斷存在與否

       3、用于IN（IN （list））中的子查詢，判斷存在與指定列表中。

       4、用于FROM中的子查詢

              select
Alias_name.col1,Alias_name.col2… from (selectclause ) As Alias_name Where condition; 

MySQL視圖（view）：存儲下來的select語句，用于限定查詢結果，隱藏表中的某些信息。將限定的查詢結果從原表中獲取，客戶的查詢 被限制在限定的結果中。

mysql> create VIEW testview as select
name,age from students;
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
|testview          |
| toc               |
+-------------------+
8 rows in set (0.01 sec)

查看視圖中

mysql> select * from testview;
+---------------+-----+
| name| age |
+---------------+-----+
| Shi Zhongyu|  22 |
| Shi Potian|  22 |
| Xie Yanke|  53 |
| Ding Dian|  32 |
| Yu Yutong|  26 |
| Shi Qing|  46 |
| Xi Ren|  19 |
| Lin Daiyu|  17 |
| Ren Yingying|  20 |
| Yue Lingshan|  19 |
| Yuan Chengzhi |  23 |
| Wen Qingqing|  19 |
| Tian Boguang|  33 |
| Lu Wushuang|  17 |
| Duan Yu|  19 |
| Xu Zhu|  21 |
| Lin Chong|  25 |
| Hua Rong|  23 |
| Xue Baochai|  18 |
| Diao Chan|  19 |
| Huang Yueying |  22 |
| Xiao Qiao|  20 |
| Ma Chao|  23 |
| Xu Xian|  27 |
| Sun Dasheng| 100 |
+---------------+-----+

視圖的狀態與table的狀態是有區別的，但其可以被當作表來使用，在插入數據或更新數據時，不應該基于view來實現。

mysql> show table status like
"testview"\G
*************************** 1. row
***************************
Name: testview
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
 Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
 Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

刪除視圖，使用drop

select name,age from students where age > (select avg(age) from students);

select students.name,courses.course from students,courses,scores where students.StuID = scores.StuID 
and scores.CourseID = courses.CourseID and courses.courseid in (1,2,4,7);

select s.name,s.classid from (
select classid from students group by classid having count(ClassID)>=3) as
c,students as s where c.classid=s.classid and age>(select avg(age) from
students);

select s.name,s.classid,s.age
from students as s where classid is not null and s.age>(select avg(age) from
students) ;

來自為知筆記(Wiz)