天天看點
傳回

MySQL（七）之多表查詢

一、MySQL多表查詢和子查詢

1、聯結查詢

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

  • 交叉聯結：效率很低
  • 自然聯結：也叫等值聯結
  • 外聯結：常用的

   左外聯結：隻保留出現在左外連接配接運算之前（左邊）的關系中的元組；

    left_tb LEFT JOIN right_tb ON 連接配接條件

   右外聯結：隻保留出現在右外連接配接運算之後（右邊）的關系中的元組；

    left_tb RIGHT JOIN right_tb ON 連接配接條件

   全外聯結：mysql不支援此聯結

  • 自聯結

說明：在查詢中還可以使用别名的方法來縮減代碼，而别名可分為：表别名和字段别名，兩者的應用等大大減少代碼量。

2、子查詢

子查詢：在查詢中嵌套的查詢。

用于WHERE中的子查詢：

  • 用于比較表達式中的子查詢，子查詢的傳回值隻能有一個；
  • 用于EXISTS中的子查詢，判斷存在與否；
  • 用于IN中的子查詢，判斷存在于指定清單中；

用于FROM中子查詢：

   SELECT alias.col,... FROM (SELECT clause) AS alias WHERE condition

 注意：MySQL不擅長于子查詢：應該避免使用子查詢；

3、聯合查詢

MySQL的聯合查詢：把兩個或多個查詢語句的結果合并成一個結果進行輸出；

文法：SELECT clauase UNION SELECT clause UNION ...

二、應用舉例

1、列舉資料庫表的内容

MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)      
MariaDB [hellodb]> SELECT * FROM classes;
+---------+-----------------+----------+
| ClassID | Class           | NumOfStu |
+---------+-----------------+----------+
|       1 | Shaolin Pai     |       10 |
|       2 | Emei Pai        |        7 |
|       3 | QingCheng Pai   |       11 |
|       4 | Wudang Pai      |       12 |
|       5 | Riyue Shenjiao  |       31 |
|       6 | Lianshan Pai    |       27 |
|       7 | Ming Jiao       |       27 |
|       8 | Xiaoyao Pai     |       15 |
|       9 | Jiuyin Zhenjing |       22 |
+---------+-----------------+----------+
9 rows in set (0.00 sec)      
MariaDB [hellodb]> SELECT * FROM coc;
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
|  1 |       1 |        2 |
|  2 |       1 |        5 |
|  3 |       2 |        2 |
|  4 |       2 |        6 |
|  5 |       3 |        1 |
|  6 |       3 |        7 |
|  7 |       4 |        5 |
|  8 |       4 |        2 |
|  9 |       5 |        1 |
| 10 |       5 |        9 |
| 11 |       6 |        3 |
| 12 |       6 |        4 |
| 13 |       7 |        4 |
| 14 |       7 |        3 |
+----+---------+----------+
14 rows in set (0.00 sec)      
MariaDB [hellodb]> SELECT * FROM courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.00 sec)      
MariaDB [hellodb]> SELECT * FROM scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
|  1 |     1 |        2 |    77 |
|  2 |     1 |        6 |    93 |
|  3 |     2 |        2 |    47 |
|  4 |     2 |        5 |    97 |
|  5 |     3 |        2 |    88 |
|  6 |     3 |        6 |    75 |
|  7 |     4 |        5 |    71 |
|  8 |     4 |        2 |    89 |
|  9 |     5 |        1 |    39 |
| 10 |     5 |        7 |    63 |
| 11 |     6 |        1 |    96 |
| 12 |     7 |        1 |    86 |
| 13 |     7 |        7 |    83 |
| 14 |     8 |        4 |    57 |
| 15 |     8 |        3 |    93 |
+----+-------+----------+-------+
15 rows in set (0.00 sec)      
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | Zhao Ming     |  24 | F      |       8 |        11 |
|    27 | Zhang Wuji    |  25 | M      |       9 |         2 |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)      
MariaDB [hellodb]> SELECT * FROM teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)

2、應用舉例

交叉聯合應用：

MariaDB [hellodb]> SELECT * FROM students,classes;

此語句會輸出students表中的列數與classes表中列數的乘積個行，是以效率極低。

自然連接配接的應用：

MariaDB [hellodb]> SELECT * FROM students,classes WHERE students.ClassID = classes.ClassID;

這種連接配接會根據兩表的等值進行輸出，而一旦在比較的等值項目中出現NULL，則該行就不會輸出。

通過等值比較輸出指定的内容：

MariaDB [hellodb]> SELECT students.Name,classes.Class FROM students,classes WHERE students.ClassID=classes.ClassID;

通過表别名的方法來實作上例的查詢：

MariaDB [hellodb]> SELECT s.Name,c.Class FROM students AS s,classes AS c WHERE s.ClassID=c.ClassID;

由于在students表中有兩個學生的班級好為NULL，是以使用上面的查詢并不能查出結果，若想能将其查詢出來，就可以使用左外連接配接查詢，或者右外連接配接查詢：

MariaDB [hellodb]> SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID =c.ClassID;
MariaDB [hellodb]> SELECT c.Class,s.Name FROM classes AS c RIGHT JOIN students AS s ON s.ClassID =c.ClassID;

查詢前五名學生所學的課程并以升序的方式顯示：

MariaDB [hellodb]>  SELECT StuID,Name,Course FROM students AS s,courses AS c,coc WHERE s.ClassID=coc.ClassID AND coc.CourseID=c.CourseID AND s.StuID <= 5 ORDER BY StuID;
+-------+-------------+----------------+
| StuID | Name        | Course         |
+-------+-------------+----------------+
|     1 | Shi Zhongyu | Kuihua Baodian |
|     1 | Shi Zhongyu | Weituo Zhang   |
|     2 | Shi Potian  | Daiyu Zanghua  |
|     2 | Shi Potian  | Kuihua Baodian |
|     3 | Xie Yanke   | Weituo Zhang   |
|     3 | Xie Yanke   | Kuihua Baodian |
|     4 | Ding Dian   | Daiyu Zanghua  |
|     4 | Ding Dian   | Kuihua Baodian |
|     5 | Yu Yutong   | Dagou Bangfa   |
|     5 | Yu Yutong   | Hamo Gong      |
+-------+-------------+----------------+
10 rows in set (0.00 sec)

将上例的輸出結果在加上成績：

MariaDB [hellodb]> SELECT s.StuID,s.Name,c.Course,sc.Score FROM students AS s,courses AS c,coc,scores AS sc WHERE s.ClassID=coc.ClassID AND coc.CourseID=c.CourseID AND s.StuID <= 5 AND s.StuID=sc.StuID AND coc.CourseID=sc.CourseID ORDER BY s.StuID;
+-------+-------------+----------------+-------+
| StuID | Name        | Course         | Score |
+-------+-------------+----------------+-------+
|     1 | Shi Zhongyu | Weituo Zhang   |    93 |
|     1 | Shi Zhongyu | Kuihua Baodian |    77 |
|     2 | Shi Potian  | Daiyu Zanghua  |    97 |
|     2 | Shi Potian  | Kuihua Baodian |    47 |
|     3 | Xie Yanke   | Weituo Zhang   |    75 |
|     3 | Xie Yanke   | Kuihua Baodian |    88 |
|     4 | Ding Dian   | Kuihua Baodian |    89 |
|     4 | Ding Dian   | Daiyu Zanghua  |    71 |
|     5 | Yu Yutong   | Hamo Gong      |    39 |
|     5 | Yu Yutong   | Dagou Bangfa   |    63 |
+-------+-------------+----------------+-------+
10 rows in set (0.00 sec)

前8位同學每位同學自己兩門課的平均成績，并按降序排列：

MariaDB [hellodb]> SELECT Name,AVG(Score) FROM students AS s,courses AS c,coc,scores AS sc WHERE s.ClassID=coc.ClassID AND coc.CourseID=c.CourseID AND s.StuID <=8 AND s.StuID=sc.StuID AND coc.CourseID=sc.CourseID GROUP BY Name ORDER BY AVG(Score)DESC;
+-------------+------------+
| Name        | AVG(Score) |
+-------------+------------+
| Shi Qing    |    96.0000 |
| Shi Zhongyu |    85.0000 |
| Xi Ren      |    84.5000 |
| Xie Yanke   |    81.5000 |
| Ding Dian   |    80.0000 |
| Lin Daiyu   |    75.0000 |
| Shi Potian  |    72.0000 |
| Yu Yutong   |    51.0000 |
+-------------+------------+
8 rows in set (0.00 sec)

顯示其年齡大于平均年齡的同學的名字：

MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age >(SELECT AVG(Age) FROM students);
+--------------+-----+
| Name         | Age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Sun Dasheng  | 100 |
+--------------+-----+
5 rows in set (0.00 sec)

顯示所有課程及其班級ID：

MariaDB [hellodb]> SELECT Class,coc.ClassID FROM classes LEFT JOIN coc ON classes.ClassID = coc.Classid;
+-----------------+---------+
| Class           | ClassID |
+-----------------+---------+
| Shaolin Pai     |       1 |
| Shaolin Pai     |       1 |
| Emei Pai        |       2 |
| Emei Pai        |       2 |
| QingCheng Pai   |       3 |
| QingCheng Pai   |       3 |
| Wudang Pai      |       4 |
| Wudang Pai      |       4 |
| Riyue Shenjiao  |       5 |
| Riyue Shenjiao  |       5 |
| Lianshan Pai    |       6 |
| Lianshan Pai    |       6 |
| Ming Jiao       |       7 |
| Ming Jiao       |       7 |
| Xiaoyao Pai     |    NULL |
| Jiuyin Zhenjing |    NULL |
+-----------------+---------+
16 rows in set (0.00 sec)

顯示沒有開課的班級：

MariaDB [hellodb]> SELECT Class,coc.ClassID FROM classes LEFT JOIN coc ON classes.ClassID = coc.ClassID WHERE coc.ClassID IS NULL;
+-----------------+---------+
| Class           | ClassID |
+-----------------+---------+
| Xiaoyao Pai     |    NULL |
| Jiuyin Zhenjing |    NULL |
+-----------------+---------+
2 rows in set (0.00 sec)

顯示沒有開課的班級的同學：

MariaDB [hellodb]> SELECT Name FROM students WHERE ClassID IN(SELECT classes.ClassID FROM classes LEFT JOIN coc ON classes.ClassID = coc.Classid WHERE coc.ClassID IS NULL);
+------------+
| Name       |
+------------+
| Zhao Ming  |
| Zhang Wuji |
+------------+
2 rows in set (0.00 sec)

自連接配接的使用，檢視學生及其老師：

MariaDB [hellodb]> SELECT t.Name,s.Name FROM students AS s,students AS t WHERE t.TeacherID=s.StuID;
+-------------+---------------+
| Name        | Name          |
+-------------+---------------+
| Shi Zhongyu | Xie Yanke     |
| Shi Potian  | Xi Ren        |
| Xie Yanke   | Xu Zhu        |
| Ding Dian   | Ding Dian     |
| Yu Yutong   | Shi Zhongyu   |
| Zhao Ming   | Yuan Chengzhi |
| Zhang Wuji  | Shi Potian    |
+-------------+---------------+
7 rows in set (0.00 sec)

三、MySQL視圖

   視圖就是存儲下來的SELECT語句；

舉例：

MariaDB [hellodb]> CREATE VIEW stu AS SELECT StuID,Name,Age,Gender FROM students;
Query OK, 0 rows affected (0.12 sec)
MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'stu'\G;
*************************** 1. row ***************************
           Name: stu
         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)
MariaDB [hellodb]> DESC stu;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| StuID  | int(10) unsigned    | NO   |     | 0       |       |
| Name   | varchar(50)         | NO   |     | NULL    |       |
| Age    | tinyint(3) unsigned | NO   |     | NULL    |       |
| Gender | enum('F','M')       | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.07 sec)

四、INSERT INTO、UPDATE、DELETE指令簡單介紹

1、INSERT INTO：插入資料

  • 第一種使用方法： 

    INSERT INTO tb_name [(col1,col2,....)]{values|value}(val1,val2,...)[,(val21,val22,....),....]

  • 第二種使用方法： 

     INSERT INTO tb_name set col_name=val1,col2=val2,....

  • 第三種使用方法：(将一個表中的資料插入到另外一張表中) 

     INSERT INTO tb_name SELECT clause

2、REPLACE

   說明：此指令與INSERT INTO 使用的性質一樣，即替換表中資料，但是其除了插入資料這一功能外還有當新插入的資料與表中的主鍵或唯一索引定義的資料相同會替換老的行 。

3、UPDATE：更新資料 

文法： UPDATE [LOW_PRIORITY] [IGNORE] table_reference

            SET col_name1=val1 [, col_name2={val2] ...

            [WHERE where_condition]

            [ORDER BY ...]

            [LIMIT row_count]

   注意：UPDATE通常情況下，後面必須要使用WHERE字句，或者使用LIMIT限制要修改的行數 ，不然後将表中所有資料給更新了，為避免在使用中出現此種狀況，建議在登入系統時後面帶有--safe-updates選項，意思為：當在執行UPDATE、DELETE語句時後面忘記帶有限制條件是系統是拒絕執行的，是以在登入是建議帶上此選項。   

4、DELETE：删除資料

文法：DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name 

mysql、資料庫
上一篇: 四大 web 架構
下一篇: Truffle測試架構

繼續閱讀