MySQL first knowledge (vi) Multi-Table Association

/**
MySQL Multi-Table Association

*/

One-to-one correlation such as city and area code correlation *******************

First a city table
CID City Coid
1 Beijing 1
2 Shanghai 2
3 Guangzhou 3

One more Area code table
Coid Code
1 010
2 020
3 0755

This connects two tables together through the Association of CID and Coid
Single-to-one type his associated fields can be placed in two tables.
Mysql> SELECT * from City,code where city.coid=code.coid;
+-----+------+------+------+------+
| CID | City | coid | coid | Code |
+-----+------+------+------+------+
| 1 | Beijing | 1 | 1 | 010 |
| 2 | Shanghai | 2 | 2 | 020 |
| 3 | guangzhou | 3 | 3 | 0755 |
+-----+------+------+------+------+

A pair of n associations, such as the relationship between students and classes ***************
A student table
Mysql> select * from student;
+----+------------+------+------+-------+
| ID | name | sex | Age | Class |
+----+------------+------+------+-------+
| 1 | Xiao Ming | Male | 14 | 1 |
| 2 | Li Lei | Male | 14 | 1 |
| 3 | Han Meimei | Women | 20 | 1 |
| 4 | Aboy | Male | 10 | 1 |
| 6 | Xiao Ming | Male | 14 | 1 |
| 7 | Lee Sledgehammer | Women | 17 | 2 |
| 8 | Mrjoker | Male | 42 | 2 |
| 9 | Mingzdi | Male | 19 | 2 |
| 10 | New | Male | 20 | 2 |
| 11 | Another newcomer | Women | 22 | 2 |
| 12 | NewBoy | Male | 19 | 3 |
| 13 | Oldboy | Male | 19 | 1 |
| 14 | As | Male | 17 | 3 |
+----+------------+------+------+-------+

A class table
Mysql> SELECT * from class;
+-----+-----------+
| CID | ClassName |
+-----+-----------+
| 1 | Class One |
| 2 | Class II |
| 3 | Class Three |
| 4 | Class Four |
+-----+-----------+

One-to-many association table association conditions to the more that side

Find out which class each person corresponds to
SELECT * from Student,class where student.class=class.cid;

Inner JOIN ****************** with an internal link method
SELECT * FROM student as s inner joins class as C on S.class=c.cid;
On is a conditional restriction in conjunction with inner join use

Find all the classmates in class two
SELECT * FROM student as s inner joins class as C on S.class=c.cid where class= "2";

Left associative and right associative ****************************************
Left Association
Mysql> SELECT * FROM student as S left join class as C on S.class=c.cid;
+----+------------+------+------+-------+------+-----------+
| ID | name | sex | Age | Class | CID | ClassName |
+----+------------+------+------+-------+------+-----------+
| 1 | Xiao Ming | Male | 14 | 1 | 1 | Class One |
| 2 | Li Lei | Male | 14 | 1 | 1 | Class One |
| 3 | Han Meimei | Women | 20 | 1 | 1 | Class One |
| 4 | Aboy | Male | 10 | 1 | 1 | Class One |
| 6 | Xiao Ming | Male | 14 | 1 | 1 | Class One |
| 7 | Lee Sledgehammer | Women | 17 | 2 | 2 | Class II |
| 8 | Mrjoker | Male | 42 | 2 | 2 | Class II |
| 9 | Mingzdi | Male | 19 | 2 | 2 | Class II |
| 10 | New | Male | 20 | 2 | 2 | Class II |
| 11 | Another newcomer | Women | 22 | 2 | 2 | Class II |
| 12 | NewBoy | Male | 19 | 3 | 3 | Class Three |
| 13 | Oldboy | Male | 19 | 1 | 1 | Class One |
| 14 | As | Male | 17 | 3 | 3 | Class Three |
+----+------------+------+------+-------+------+-----------+
Right Association
Mysql> SELECT * FROM student as S right joins class as C on S.class=c.cid;
+------+------------+------+------+-------+-----+-----------+
| ID | name | sex | Age | Class | CID | ClassName |
+------+------------+------+------+-------+-----+-----------+
| 1 | Xiao Ming | Male | 14 | 1 | 1 | Class One |
| 2 | Li Lei | Male | 14 | 1 | 1 | Class One |
| 3 | Han Meimei | Women | 20 | 1 | 1 | Class One |
| 4 | Aboy | Male | 10 | 1 | 1 | Class One |
| 6 | Xiao Ming | Male | 14 | 1 | 1 | Class One |
| 13 | Oldboy | Male | 19 | 1 | 1 | Class One |
| 7 | Lee Sledgehammer | Women | 17 | 2 | 2 | Class II |
| 8 | Mrjoker | Male | 42 | 2 | 2 | Class II |
| 9 | Mingzdi | Male | 19 | 2 | 2 | Class II |
| 10 | New | Male | 20 | 2 | 2 | Class II |
| 11 | Another newcomer | Women | 22 | 2 | 2 | Class II |
| 12 | NewBoy | Male | 19 | 3 | 3 | Class Three |
| 14 | As | Male | 17 | 3 | 3 | Class Three |
| NULL | NULL | NULL | NULL | NULL | 4 | Class Four |
+------+------------+------+------+-------+-----+-----------+

Through the above can be seen
When left-associative, the left table student is all displayed, and no one's class four will be displayed.
Right associative when the right table class is all show no one of the four classes will also show up
The above summarizes: Which table is associated with which table data is all displayed, and the other table all goes with the associated table

Count how many people are in each class
Mysql> Select COUNT (*), c.classname from student as s inner joins class as C on S.
Class=c.cid GROUP BY C.classname;
+----------+-----------+
| COUNT (*) | ClassName |
+----------+-----------+
| 6 | Class One |
| 2 | Class Three |
| 5 | Class II |
+----------+-----------+
Met
This statement has three key points:
1.count (*) is the total amount of information calculated
2.inner Join Associates multiple tables on set association conditions
3.group by grouping a table through a message

Find a classmate with Li Lei's classmates
Three steps
1. First find out the corresponding class of Li Lei
Mysql> Select class from student where Name= ' Li Lei '; is 1
2. Find out all the students in Li Lei's class
Mysql> SELECT * FROM student where class= "1";
3. Remove Li Lei
Mysql> SELECT * FROM student where class= "1" and name! = ' Li Lei ';

Merging the above into a single piece of data is:
SELECT * FROM student where class= (select class from student where Name= ' Li Lei ') and name! = ' Li Lei ';

Multi-Table Association ***********************************
Create an Article table article
+-----+----------------+
| Aid | Title |
+-----+----------------+
| 1 | Baidu on the Market |
| 2 | Tomorrow's Holiday |
| 3 | Weekends without rest |
| 4 | Tomorrow morning Class |
| 5 | Huang Xiaoming Divorced |
+-----+----------------+
Create an intermediate table
+------+------+
| Aid | Tid |
+------+------+
| 1 | 2 |
| 1 | 1 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 3 | 3 |
| 4 | 4 |
| 4 | 3 |
| 5 | 1 |
| 5 | 2 |
| 5 | 3 |
+------+------+
Create a label table
+-----+-------+
| Tid | Tname |
+-----+-------+
| 1 | Popular |
| 2 | Hot |
| 3 | Praise |
| 4 | Distress |
| 5 | Angry |
+-----+-------+

The basic idea of three-sheet association is (retrieving the label name of all articles)
SELECT * from article as a joins Arc_tag as at the A.aid=at.aid join tag as T on At.tid=t.tid;

Retrieve the label name of the article
SELECT * from article as a joins Art_tag as at on A.aid=at.aid;
+-----+----------------+------+------+
| Aid | Title | Aid | Tid |
+-----+----------------+------+------+
| 1 | Baidu on the Market | 1 | 2 |
| 1 | Baidu on the Market | 1 | 1 |
| 1 | Baidu on the Market | 1 | 3 |
| 2 | Tomorrow's Holiday | 2 | 2 |
| 2 | Tomorrow's Holiday | 2 | 3 |
| 3 | Weekends without rest | 3 | 3 |
| 4 | Tomorrow morning Class | 4 | 4 |
| 4 | Tomorrow morning Class | 4 | 3 |
| 5 | Huang Xiaoming Divorced | 5 | 1 |
| 5 | Huang Xiaoming Divorced | 5 | 2 |
| 5 | Huang Xiaoming Divorced | 5 | 3 |
+-----+----------------+------+------+

Retrieve the label name of Baidu
Mysql> SELECT * from article as a join Arc_tag as in on A.aid=at.aid join tag as T in At.tid=t.tid where title like '% Hundred Degree% ';
+-----+--------------+------+------+-----+-------+
| Aid | Title | Aid | Tid | Tid | Tname |
+-----+--------------+------+------+-----+-------+
| 1 | Baidu on the Market | 1 | 1 | 1 | Popular |
| 1 | Baidu on the Market | 1 | 2 | 2 | Hot |
| 1 | Baidu on the Market | 1 | 3 | 3 | Praise |
+-----+--------------+------+------+-----+-------+

Retrieved articles with the same label as ' Baidu '
First retrieve the corresponding tag ID of Baidu
Select At.tid from article as a joins Arc_tag as at in A.aid=at.aid where A.title like '% Baidu ';
+------+
| Tid |
+------+
| 2 |
| 1 |
| 3 |
+------+
And then this as a condition to query by the way Baidu corresponding article shielding
Mysql> SELECT * from article as a joins Arc_tag as at the A.aid=at.aid join tag as T on At.tid=t.tid where At.tid in ( , 3) and A.aid!=1;
+-----+----------------+------+------+-----+-------+
| Aid | Title | Aid | Tid | Tid | Tname |
+-----+----------------+------+------+-----+-------+
| 5 | Huang Xiaoming Divorced | 5 | 1 | 1 | Popular |
| 2 | Tomorrow's Holiday | 2 | 2 | 2 | Hot |
| 5 | Huang Xiaoming Divorced | 5 | 2 | 2 | Hot |
| 2 | Tomorrow's Holiday | 2 | 3 | 3 | Praise |
| 3 | Weekends without rest | 3 | 3 | 3 | Praise |
| 4 | Tomorrow morning Class | 4 | 3 | 3 | Praise |
| 5 | Huang Xiaoming Divorced | 5 | 3 | 3 | Praise |
+-----+----------------+------+------+-----+-------+

Combining the above two strips is a complete statement
SELECT * from article as a join Arc_tag as in on A.aid=at.aid join tag as T on T.tid=at.tid W Here At.tid in (select At.tid from article as a joins Arc_tag as at on A.aid=at.aid where A.title like '% Baidu ') and A.title Not like '% Baidu% ';
+-----+----------------+------+------+-----+-------+
| aid | title | aid | tid | tid | tname |
+-----+----------------+------+------+-----+-------+
| 5 | Huang Xiaoming divorced | 5 | 1 | 1 | popular |
| 2 | Tomorrow is on holiday | 2 | 2 | 2 | hot |
| 5 | Huang Xiaoming divorced | 5 | 2 | 2 | hot |
| 2 | Tomorrow is a day off | 2 | 3 | 3 | likes |
| 3 | Weekends do not REST | 3 | 3 | 3 | likes |
| 4 | tomorrow morning Class | 4 | 3 | 3 | likes |
| 5 | Huang Xiaoming divorced | 5 | 3 | 3 | likes |
+-----+----------------+------+------+-----+-------+

Retrieve the number of articles per label
Select COUNT (*), tname from article as a join Arc_tag as in on A.aid=at.aid join tag as T in T.tid=at.tid Group by Tname;
+----------+-------+
| COUNT (*) | Tname |
+----------+-------+
| 3 | Hot |
| 2 | Popular |
| 1 | Distress |
| 5 | Praise |
+----------+-------+

MySQL first knowledge (vi) Multi-Table Association