MYSQL完全手册学习笔记（第十章）

连接

交叉连接


mysql> select * from color, attribute;
+-------+-----------+
| color | attribute |
+-------+-----------+
| black | eyes |
| black | hair |
| brown | eyes |
| brown | hair |
| gray | eyes |
| gray | hair |
+-------+-----------+
6 rows in set (0.00 sec)

这里面有个问题 当如果有4个表 每个包含100条记录的时候出来的结果会很多


内连接

mysql> select * from products;
+-----+------------+
| Pid | Pname |
+-----+------------+
| 1 | apples |
| 2 | oranges |
| 3 | pineapples |
| 4 | bananas |
+-----+------------+
4 rows in set (0.00 sec)

mysql> select * from sales;
+----+-----+----------+
| id | Pid | quantity |
+----+-----+----------+
| 1 | 3 | 2300 |
| 2 | 2 | 1500 |
| 3 | 1 | 3400 |
+----+-----+----------+
3 rows in set (0.00 sec)

mysql> select Pname ,quantity from products,sales where products.Pid=sales.Pid;
+------------+----------+
| Pname | quantity |
+------------+----------+
| apples | 3400 |
| oranges | 1500 |
| pineapples | 2300 |
+------------+----------+
3 rows in set (0.05 sec)

mysql> select products,quantity from products,sales where products.Pid=sales.Pid
and sales.quantity>2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'sales
.quantity>2000' at line 1
mysql> select Pnama,quantity from products,sales where products.Pid=sales.Pidand
sales.quantity>2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'sales
.quantity>2000' at line 1
mysql> select Pname,quantity from products,sales where products.Pid=sales.Pidand
sales.quantity>2000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'sales
.quantity>2000' at line 1
mysql> select Pname ,quantity from products,sales where products.Pid=sales.Pid;
+------------+----------+
| Pname | quantity |
+------------+----------+
| apples | 3400 |
| oranges | 1500 |
| pineapples | 2300 |
+------------+----------+
3 rows in set (0.02 sec)

mysql> select Pname ,quantity from products,sales where products.Pid=sales.Pid a
nd sales.quantity>2000;
+------------+----------+
| Pname | quantity |
+------------+----------+
| apples | 3400 |
| pineapples | 2300 |
+------------+----------+
2 rows in set (0.02 sec)



mysql> select * from user;
+-----+-------+
| uid | name |
+-----+-------+
| 100 | sue |
| 103 | hary |
| 104 | louis |
| 107 | sam |
| 110 | james |
| 111 | mark |
| 112 | rita |
+-----+-------+
7 rows in set (0.00 sec)

mysql> select * from group;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'grou
' at line 1
mysql> select * from groups;
+-----+-----------+
| gid | name |
+-----+-----------+
| 501 | authors |
| 502 | actors |
| 503 | musicians |
| 504 | chefs |
+-----+-----------+
4 rows in set (0.00 sec)

mysql> select * from users_groups;
+-----+-----+
| uid | gid |
+-----+-----+
| 11 | 502 |
| 100 | 501 |
| 100 | 502 |
| 100 | 503 |
| 102 | 501 |
| 104 | 502 |
| 107 | 502 |
| 110 | 501 |
| 112 | 501 |
+-----+-----+
9 rows in set (0.00 sec)


mysql> select user.name,groups.name from user,groups,users_groups where user.uid
=users_groups.uid and groups.gid= users_groups.gid;
+-------+-----------+
| name | name |
+-------+-----------+
| sue | authors |
| james | authors |
| rita | authors |
| sue | actors |
| louis | actors |
| sam | actors |
| sue | musicians |
+-------+-----------+
7 rows in set (0.01 sec)

左外连接
mysql> select * from user left join users_groups on user.uid=users_groups.uid;
从连接的左部（user表）选择所有的行，对于选中的每一行，或者从右部（users_groups表）显示相匹配的值（能够满足on或者using子句约束的值）。或者显示null的一行。这种类型的连接称为左外连接。

+-----+-------+------+------+
| uid | name | uid | gid |
+-----+-------+------+------+
| 100 | sue | 100 | 501 |
| 100 | sue | 100 | 502 |
| 100 | sue | 100 | 503 |
| 103 | hary | NULL | NULL |
| 104 | louis | 104 | 502 |
| 107 | sam | 107 | 502 |
| 110 | james | 110 | 501 |
| 111 | mark | NULL | NULL |
| 112 | rita | 112 | 501 |
+-----+-------+------+------+
9 rows in set (0.00 sec)


下面的语句和上面的结果是一样的


mysql> select * from user left join users_groups using(uid);
+-----+-------+------+
| uid | name | gid |
+-----+-------+------+
| 100 | sue | 501 |
| 100 | sue | 502 |
| 100 | sue | 503 |
| 103 | hary | NULL |
| 104 | louis | 502 |
| 107 | sam | 502 |
| 110 | james | 501 |
| 111 | mark | NULL |
| 112 | rita | 501 |
+-----+-------+------+
9 rows in set (0.00 sec)

右外连接

mysql> select * from users_groups right join groups using(gid);
+-----+-----------+------+
| gid | name | uid |
+-----+-----------+------+
| 501 | authors | 100 |
| 501 | authors | 102 |
| 501 | authors | 110 |
| 501 | authors | 112 |
| 502 | actors | 11 |
| 502 | actors | 100 |
| 502 | actors | 104 |
| 502 | actors | 107 |
| 503 | musicians | 100 |
| 504 | chefs | NULL |
+-----+-----------+------+
10 rows in set (0.00 sec)

通过浏览null 值，很容易看到哪一组没有成员
mysql> select * from users_groups right join groups using(gid) where uid is null
;
+-----+-------+------+
| gid | name | uid |
+-----+-------+------+
| 504 | chefs | NULL |
+-----+-------+------+
1 row in set (0.02 sec)


自查询

mysql> insert into menu(id,label,parent) values(1,'sevices','0');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(2,'company','0');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(3,'media center','0');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(4,'your account','0');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(5,'community','0');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(6,'for content publishers','1');

Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(7,'for small businesses','1');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(8,'backgroud','2');
Query OK, 1 row affected (0.05 sec)

mysql> insert into menu(id,label,parent) values(9,'client','2');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(10,'addresses','2');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(11,'jobs','2');
Query OK, 1 row affected (0.02 sec)

mysql> insert into menu(id,label,parent) values(11,'news','2');
ERROR 1062 (23000): Duplicate entry '11' for key 'PRIMARY'
mysql> insert into menu(id,label,parent) values(12,'news','2');
Query OK, 1 row affected (0.02 sec)

mysql> insert into menu(id,label,parent) values(13,'press releases','3');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(14,'media kit','3');
Query OK, 1 row affected (0.02 sec)

mysql> insert into menu(id,label,parent) values(15,'log in','4');
Query OK, 1 row affected (0.02 sec)

mysql> insert into menu(id,label,parent) values(16,'columns','5');
Query OK, 1 row affected (0.03 sec)

mysql> insert into menu(id,label,parent) values(17,'colophon','16');
Query OK, 1 row affected (0.02 sec)

mysql> insert into menu(id,label,parent) values(18,'cut','16');
Query OK, 1 row affected (0.02 sec)

mysql> insert into menu(id,label,parent) values(19,'boombox','16');
Query OK, 1 row affected (0.01 sec)

mysql> select * from menu;
+----+------------------------+--------+
| id | label | parent |
+----+------------------------+--------+
| 1 | sevices | 0 |
| 2 | company | 0 |
| 3 | media center | 0 |
| 4 | your account | 0 |
| 5 | community | 0 |
| 6 | for content publishers | 1 |
| 7 | for small businesses | 1 |
| 8 | backgroud | 2 |
| 9 | client | 2 |
| 10 | addresses | 2 |
| 11 | jobs | 2 |
| 12 | news | 2 |
| 13 | press releases | 3 |
| 14 | media kit | 3 |
| 15 | log in | 4 |
| 16 | columns | 5 |
| 17 | colophon | 16 |
| 18 | cut | 16 |
| 19 | boombox | 16 |
+----+------------------------+--------+
19 rows in set (0.00 sec)





它创建了第一个表的虚拟拷贝，然后使用一个常规的内连接来把两个表映射到一起，并且得到需要的输出。


mysql> select a.label as parent_label,b.label as child_label from menu as a,menu
as b where a.id =b.id;
+------------------------+------------------------+
| parent_label | child_label |
+------------------------+------------------------+
| sevices | sevices |
| company | company |
| media center | media center |
| your account | your account |
| community | community |
| for content publishers | for content publishers |
| for small businesses | for small businesses |
| backgroud | backgroud |
| client | client |
| addresses | addresses |
| jobs | jobs |
| news | news |
| press releases | press releases |
| media kit | media kit |
| log in | log in |
| columns | columns |
| colophon | colophon |
| cut | cut |
| boombox | boombox |
+------------------------+------------------------+
19 rows in set (0.00 sec)


联合


mysql> select symbol,price from exchangea union select symbol,price from exchang
eb;
+--------+--------+
| symbol | price |
+--------+--------+
| BGHU | 12.90 |
| HDGS | 6.48 |
| HDTE | 123.71 |
| HTYF | 90.10 |
| TDGB | 78.44 |
| WERR | 32.91 |
| YTEM | 39.65 |
| DFRM | 9.43 |
| HTYF | 89.70 |
| POYT | 87.10 |
| TDGB | 79.00 |
+--------+--------+
11 rows in set (0.01 sec)


mysql> select symbol,price from exchangea where price >35.00 union select symbol
,price from exchangeb where price between 4.00 and 95.00;
+--------+--------+
| symbol | price |
+--------+--------+
| HDTE | 123.71 |
| HTYF | 90.10 |
| TDGB | 78.44 |
| YTEM | 39.65 |
| DFRM | 9.43 |
| HTYF | 89.70 |
| POYT | 87.10 |
| TDGB | 79.00 |
+--------+--------+
8 rows in set (0.00 sec)


可以通过union运算符中添加all关键字来关掉mysql自动过滤重复记录的功能。


mysql> select symbol from exchangea union all select symbol from exchangeb;
+--------+
| symbol |
+--------+
| BGHU |
| HDGS |
| HDTE |
| HTYF |
| TDGB |
| WERR |
| YTEM |
| DFRM |
| HTYF |
| POYT |
| TDGB |
+--------+
11 rows in set (0.00 sec)