和燕十八學習PHP-第二十七天-左右內串連的區別

跟燕十八學習PHP-第二十七天-左右內串連的區別
/**
燕十八 公益PHP培訓
課堂地址:YY頻道88354001
學習社區:www.zixue.it
**/




mysql> create table boy (
-> bname varchar(20),
-> other char(1)
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.23 sec)


mysql>
mysql> insert into boy
-> values
-> ('屌絲','A'),
-> ('李四','B'),
-> ('王五','C'),
-> ('高富帥','D'),
-> ('鄭七','E');
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0


mysql>
mysql>
mysql>
mysql> create table girl (
-> gname varchar(20),
-> other char(1)
-> )engine myisam charset utf8;
Query OK, 0 rows affected (0.16 sec)


mysql>
mysql> insert into girl
-> values
-> ('空姐','B'),
-> ('大S','C'),
-> ('阿嬌','D'),
-> ('張柏芝','D'),
-> ('林黛玉','E'),
-> ('寶釵','F');
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0


mysql> select * from boy;
+--------+-------+
| bname | other |
+--------+-------+
| 屌絲 | A |
| 李四 | B |
| 王五 | C |
| 高富帥 | D |
| 鄭七 | E |
+--------+-------+
5 rows in set (0.00 sec)


mysql> select * from girl;
+--------+-------+
| gname | other |
+--------+-------+
| 空姐 | B |
| 大S | C |
| 阿嬌 | D |
| 張柏芝 | D |
| 林黛玉 | E |
| 寶釵 | F |
+--------+-------+
6 rows in set (0.00 sec)


mysql> select boy.*,girl.* from
-> boy left join girl on boy.other=girl.other;
+--------+-------+--------+-------+
| bname | other | gname | other |
+--------+-------+--------+-------+
| 屌絲 | A | NULL | NULL |
| 李四 | B | 空姐 | B |
| 王五 | C | 大S | C |
| 高富帥 | D | 阿嬌 | D |
| 高富帥 | D | 張柏芝 | D |
| 鄭七 | E | 林黛玉 | E |
+--------+-------+--------+-------+
6 rows in set (0.00 sec)


mysql> #女生上台,帶著另一半,沒有另一半的,用NULL補齊
mysql> select boy.*,girl.* from
-> girl left join boy on boy.other=girl.other;
+--------+-------+--------+-------+
| bname | other | gname | other |
+--------+-------+--------+-------+
| 李四 | B | 空姐 | B |
| 王五 | C | 大S | C |
| 高富帥 | D | 阿嬌 | D |
| 高富帥 | D | 張柏芝 | D |
| 鄭七 | E | 林黛玉 | E |
| NULL | NULL | 寶釵 | F |
+--------+-------+--------+-------+
6 rows in set (0.00 sec)


mysql> #注意,a left join b,並不是說a表的就一定在左邊,只是說在查詢資料時,以a表為準
mysql> select * from boy;
+--------+-------+
| bname | other |
+--------+-------+
| 屌絲 | A |
| 李四 | B |
| 王五 | C |
| 高富帥 | D |
| 鄭七 | E |
+--------+-------+
5 rows in set (0.00 sec)


mysql> select other,bname from boy;
+-------+--------+
| other | bname |
+-------+--------+
| A | 屌絲 |
| B | 李四 |
| C | 王五 |
| D | 高富帥 |
| E | 鄭七 |
+-------+--------+
5 rows in set (0.00 sec)


mysql> #女生上台,帶著另一半,沒有另一半的,用NULL補齊
mysql> #不能用左串連來做
mysql> #用右串連. 剛才是 女生 left join 男
mysql> #用右串連，只需 男　right join　女
mysql> select boy.*,girl.*
-> from
-> boy right join girl
-> on boy.other = girl.other;
+--------+-------+--------+-------+
| bname | other | gname | other |
+--------+-------+--------+-------+
| 李四 | B | 空姐 | B |
| 王五 | C | 大S | C |
| 高富帥 | D | 阿嬌 | D |
| 高富帥 | D | 張柏芝 | D |
| 鄭七 | E | 林黛玉 | E |
| NULL | NULL | 寶釵 | F |
+--------+-------+--------+-------+
6 rows in set (0.01 sec)


mysql> select boy.*,girl.*
-> from
-> boy inner join girl
-> on boy.other=girl.other;
+--------+-------+--------+-------+
| bname | other | gname | other |
+--------+-------+--------+-------+
| 李四 | B | 空姐 | B |
| 王五 | C | 大S | C |
| 高富帥 | D | 阿嬌 | D |
| 高富帥 | D | 張柏芝 | D |
| 鄭七 | E | 林黛玉 | E |
+--------+-------+--------+-------+
5 rows in set (0.00 sec)


mysql> exit




燕十八老師太幽默了, 昨天的視頻如下:

http://www.tudou.com/programs/view/TVhY20adVL4/

