本文共 5020 字,大约阅读时间需要 16 分钟。
使用联结表的优点:
MariaDB [test]> select * from users;+-------+--------+------+------+-------+------------+-------+| user | passwd | sex | age | price | math_price | class |+-------+--------+------+------+-------+------------+-------+| user2 | 222 | girl | 23 | 155 | 36 | 1 || user3 | 333 | boy | 12 | 78 | 66 | 1 || user4 | 444 | boy | 22 | NULL | NULL | 2 || user5 | 555 | boy | 30 | NULL | 43 | 2 || user1 | 111 | boy | 18 | 35 | 88 | 1 || user6 | 666 | girl | 16 | 35 | 43 | 2 |+-------+--------+------+------+-------+------------+-------+6 rows in set (0.01 sec)MariaDB [test]> select * from hour;+-------+-------+| hours | user |+-------+-------+| 12 | user2 || 8 | user6 || 9 | user1 || 11 | user3 || 10 | user4 || 10 | user5 |+-------+-------+6 rows in set (0.00 sec)MariaDB [test]> select class,sex,hours from users,hour where hour.user = users.user order by class;+-------+------+-------+| class | sex | hours |+-------+------+-------+| 1 | boy | 11 || 1 | girl | 12 || 1 | boy | 9 || 2 | boy | 10 || 2 | boy | 10 || 2 | girl | 8 |+-------+------+-------+6 rows in set (0.00 sec)
这里所指定的两个列(class,sex)在users表中,而另一个列(hours)在 hour表中。这两个表用WHERE子句正确联结,WHERE 就是他们的联结条件.
没有联结条件的表关系返回的结果为笛卡儿积。
检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
对于上面的查询语句,我们可以用内联结的方式表示:
这里使用innor join 的方式联结,用 ON 进行筛选。SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。
首先列出所有表,然后定义表之间的关系。MariaDB [test]> select u.class,u.sex,h.hours from users as u,hour as h where u.user = h.user;+-------+------+-------+| class | sex | hours |+-------+------+-------+| 1 | girl | 12 || 2 | girl | 8 || 1 | boy | 9 || 1 | boy | 11 || 2 | boy | 10 || 2 | boy | 10 |+-------+------+-------+6 rows in set (0.00 sec)
这种方式可以缩短SQL语句;并允许在单条SELECT语句中多次使用相同的表
一直有一位同学请假,找出同一班中其他的同学是否请假,我们首先要找出这个班的其他同学;
ariaDB [test]> select user,sex,math_price from users where class = (select class from users where user = 'user1');+-------+------+------------+| user | sex | math_price |+-------+------+------------+| user2 | girl | 36 || user3 | boy | 66 || user1 | boy | 88 |+-------+------+------------+3 rows in set (0.00 sec)
这是第一种解决方案,它使用了子查询,那么我们是用自联结如何解决。
MariaDB [test]> select u1.user,u1.sex,u1.math_price from users as u1,users as u2 where u1.class = u2.class and u2.user = 'user1';-------+------+------------+| user | sex | math_price |+-------+------+------------+| user2 | girl | 36 || user3 | boy | 66 || user1 | boy | 88 |+-------+------+------------+3 rows in set (0.01 sec)
通常情况下使用自联结的查询速率比使用子查询的速率要快。
无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结(前一章中介绍的内部联结)返回所有数据,甚至相同的列多次出现。自然联结排除多次出现,使每个列只返回一次。
这项工作,由自己完成。自然联结是这样一种联结,其中你只能选择那些唯一的列。 这一般是通过对表使用通配符(SELECT *),对所有其他表的列使用明确的子集来完成的。MariaDB [test]> select * from hour;+-------+-------+| hours | user |+-------+-------+| 12 | user2 || 8 | user6 || 9 | user1 || 11 | user3 || 10 | user4 || 10 | user5 || 11 | NULL |+-------+-------+7 rows in set (0.00 sec)MariaDB [test]> select * from users;+-------+--------+------+------+-------+------------+-------+| user | passwd | sex | age | price | math_price | class |+-------+--------+------+------+-------+------------+-------+| user2 | 222 | girl | 23 | 155 | 36 | 1 || user3 | 333 | boy | 12 | 78 | 66 | 1 || user4 | 444 | boy | 22 | NULL | NULL | 2 || user5 | 555 | boy | 30 | NULL | 43 | 2 || user1 | 111 | boy | 18 | 35 | 88 | 1 || user6 | 666 | girl | 16 | 35 | 43 | 2 || user7 | 777 | boy | NULL | NULL | NULL | NULL |+-------+--------+------+------+-------+------------+-------+7 rows in set (0.00 sec)MariaDB [test]> select users.user,hour.hours from users left join hour on users.user = hour.user;+-------+-------+| user | hours |+-------+-------+| user2 | 12 || user6 | 8 || user1 | 9 || user3 | 11 || user4 | 10 || user5 | 10 || user7 | NULL |+-------+-------+7 rows in set (0.00 sec)MariaDB [test]> select users.user,hour.hours from users right join hour on users.user = hour.user;+-------+-------+| user | hours |+-------+-------+| user2 | 12 || user3 | 11 || user4 | 10 || user5 | 10 || user1 | 9 || user6 | 8 || NULL | 11 |+-------+-------+7 rows in set (0.00 sec)
使用了关键字OUTER JOIN来指定联结的类型,outer 可以省略,并且必须加上 LEFT 或RIGHT 指明方向。
左联结会打印出左边表的所有行,二右联结会打印出右边表的所有行。如果要检索所有客户及每个客户所下的订单数,下面使用了COUNT()函数的代码可完成此工作:
转载地址:http://yyxli.baihongyu.com/