MySQL multi-table query and sub-query

Multi-table query :
Cross Link: Cartesian product
Natural connections: Two tables establishing equivalent relationships
Outer joins: Two tables without equivalent relationship
Left outer connection: Whichever table on the right is on the left, regardless of the value
... Ieft JOIN ... On ...
Right outer link: Whichever table is on the right, regardless of the value on the left table
... Right JOIN ... On ...
Self-connect: Connect yourself

subquery : All data is from a single table
Subqueries are used in comparison operations: Subqueries can only return a single value;
In (): Use sub-query;
Using subqueries in from;


Instance:
Displays data for students and courses tables, by default Cartesian product display
Mysql> SELECT * from students,courses;
The display volume is too large and the result is slightly.

Displays data in the students table where the CID1 field equals the CID field in the courses table

Mysql> select * from students,courses where students. Cid1 = courses. cid;+-----+--------------+------+--------+------+------+------+---------------------+-----+------------------+- ----+| sid | name         | age  |  Gender | CID1 | CID2 | TID  | CreateTime           | CID | Cname             | tid |+-----+--------------+------+--------+------+------+----- -+---------------------+-----+------------------+-----+|   1 | guojing       |   19 | M      |     2 |    7 |    3 | 2012-04-06  10:00:00 |   2 | taijiquan        |   3 | |    2 | yangguo      |   17 | m       |    2 |    3 |     1 | 2012-04-06 10:00:00 |   2 | TaiJiquan         |   3 | |    3 | dingdian     |   25 | m       |    6 |    1 |     7 | 2012-04-06 10:00:00 |   6 | qishangquan       |   5 | |    4 | hufei        |   31  | m      |    8 |   10 |     5 | 2012-04-06 10:00:00 |   8 | wanliduxing       |   8 | |    5 | HuangRong    |   16 | F       |    5 |    9 |     9 | 2012-04-06 10:00:00 |   5 | qianzhuwandushou  |   4 | |    6 | YueLingshang |   18 | F       |    8 |    4 | null | 2012-04-06  10:00:00 |   8 | Wanliduxing      |    8 | |    7 | zhangwuji    |   20 | m       |    1 |    7 | NULL | 2012-04-06  10:00:00 |   1 | hamagong         |    2 | |    8 | xuzhu        |   26  | m      |    2 |    4  | NULL | 2012-04-06 10:00:00 |   2 | TaiJiquan         |   3 |+-----+--------------+------+--------+--- ---+------+------+---------------------+-----+------------------+-----+

Shows the name of the student taking the first course and the name of the course
Mysql> SELECT name,cname from students,courses WHERE students. CID1 = courses. CID;
+--------------+------------------+
| Name | Cname |
+--------------+------------------+
| guojing | Taijiquan |
| Yangguo | Taijiquan |
| Dingdian | Qishangquan |
| Hufei | wanliduxing |
| Huangrong | Qianzhuwandushou |
| Yuelingshang | wanliduxing |
| Zhangwuji | Hamagong |
| Xuzhu | Taijiquan |
+--------------+------------------+

Using table aliases to do a long table connection
Mysql> SELECT s.name,c.cname from students as s,courses as C WHERE s.cid1=c.cid;
+--------------+------------------+
| Name | Cname |
+--------------+------------------+
| guojing | Taijiquan |
| Yangguo | Taijiquan |
| Dingdian | Qishangquan |
| Hufei | wanliduxing |
| Huangrong | Qianzhuwandushou |
| Yuelingshang | wanliduxing |
| Zhangwuji | Hamagong |
| Xuzhu | Taijiquan |
+--------------+------------------+


Use the left side link to show the first course for each student, and if there are no elective courses, the empty
Mysql> SELECT S.name,c.cname from students as s left joins courses as C on s.cid1=c.cid;
+--------------+------------------+
| Name | Cname |
+--------------+------------------+
| guojing | Taijiquan |
| Yangguo | Taijiquan |
| Dingdian | Qishangquan |
| Hufei | wanliduxing |
| Huangrong | Qianzhuwandushou |
| Yuelingshang | wanliduxing |
| Zhangwuji | Hamagong |
| Xuzhu | Taijiquan |
| Linghuchong | NULL |
| Yilin | NULL |
+--------------+------------------+


Right out of the link shows no way course choose the name of the classmate, if no one chooses so show null value
Mysql> SELECT S.name,c.cname from students as s right joins courses as C on s.cid1=c.cid;
+--------------+------------------+
| Name | Cname |
+--------------+------------------+
| Zhangwuji | Hamagong |
| guojing | Taijiquan |
| Yangguo | Taijiquan |
| Xuzhu | Taijiquan |
| NULL | Yiyangzhi |
| NULL | Jinshejianfa |
| Huangrong | Qianzhuwandushou |
| Dingdian | Qishangquan |
| NULL | Qiankundanuoyi |
| Hufei | wanliduxing |
| Yuelingshang | wanliduxing |
| NULL | Pixiejianfa |
| NULL | Jiuyinbaiguzhua |
+--------------+------------------+

Self-linking the names of students and teachers come from a single table
Tid for SID's teacher, condition Tid=sid
Mysql> SELECT C.name as student,s.name as teacher from students as c,students as S WHERE c.tid=s.sid;
+-----------+-------------+
| Student | Teacher |
+-----------+-------------+
| guojing | Dingdian |
| Yangguo | guojing |
| Dingdian | Zhangwuji |
| Hufei | Huangrong |
| Huangrong | Linghuchong |
+-----------+-------------+

The age of the students is more than the average age.
Mysql> Select Name from Students WHERE > (SELECT AVG (age) from students);
+-------------+
| Name |
+-------------+
| Dingdian |
| Hufei |
| Xuzhu |
| Linghuchong |
+-------------+


Using subqueries in From
Mysql> Select Name,age from (select Name,age from students) as T WHERE t.age >= 20;
+-------------+------+
| Name | Age |
+-------------+------+
|   Dingdian | 25 |
|   Hufei | 31 |
|   Zhangwuji | 20 |
|   Xuzhu | 26 |
|   Linghuchong | 22 |
+-------------+------+

Compound query that shows the age of students in the student table and the teacher's age in the teacher's table in a single table
Mysql> (select Name,age from students) UNION (select Tname,age from tutors);
+--------------+------+
| Name | Age |
+--------------+------+
|   guojing | 19 |
|   Yangguo | 17 |
|   Dingdian | 25 |
|   Hufei | 31 |
|   Huangrong | 16 |
|   Yuelingshang | 18 |
|   Zhangwuji | 20 |
|   Xuzhu | 26 |
|   Linghuchong | 22 |
|   Yilin | 19 |
|   HongQiGong | 93 |
|   Huangyaoshi | 63 |
|   Miejueshitai | 72 |
|   Ouyangfeng | 76 |
|   Yideng | 90 |
|   Yucanghai | 56 |
|   Jinlunfawang | 67 |
|   Huyidao | 42 |
|   Ningzhongze | 49 |
+--------------+------+

This article from "Plum blossom fragrance from bitter cold" blog, please be sure to keep this source http://wangjunkang.blog.51cto.com/8809812/1582666

MySQL multi-table query and sub-query