 |
|
|
 首页 |
名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash |
|
??是先 Create table 吧 create table emp( id int not null primary key, name varchar(10) ); create table emp_dept( dept_id varchar(4) not null, emp_id int not null, emp_name varchar(10), primary key (dept_id,emp_id)); insert into emp() values (1,"Dennis-1"), (2,"Dennis-2"), (3,"Dennis-3"), (4,"Dennis-4"), (5,"Dennis-5"), (6,"Dennis-6"), (7,"Dennis-7"), (8,"Dennis-8"), (9,"Dennis-9"), (10,"Dennis-10"); insert into emp_dept() values ("R&D",1,"Dennis-1"), ("DEv",2,"Dennis-2"), ("R&D",3,"Dennis-3"), ("Test",4,"Dennis-4"), ("Test",5,"Dennis-5"); >> left join ------------- select a.id,a.name,b.dept_id from emp a left join emp_dept b on (a.id=b.emp_id); # 挑出左??的 table emp 中的所有儋料,即使 emp_dept 中?]有的儋料也挑出?憩?]有的就用 NULL ?盹@示, # 也即锢示儋料是以左??的 table emp 中的儋料?榛?盗 mysql> select a.id,a.name,b.dept_id -> from emp a left join emp_dept b on (a.id=b.emp_id); +----+-----------+---------+ | id | name | dept_id | +----+-----------+---------+ | 1 | Dennis-1 | R&D | | 2 | Dennis-2 | DEv | | 3 | Dennis-3 | R&D | | 4 | Dennis-4 | Test | | 5 | Dennis-5 | Test | | 6 | Dennis-6 | NULL | | 7 | Dennis-7 | NULL | | 8 | Dennis-8 | NULL | | 9 | Dennis-9 | NULL | | 10 | Dennis-10 | NULL | +----+-----------+---------+ # 挑出 table emp 中有而 table emp_dept 中?]有的人?T儋料 select a.id,a.name,b.dept_id from emp a left join emp_dept b on (a.id=b.emp_id) where b.dept_id IS NULL; mysql> select a.id,a.name,b.dept_id -> from emp a left join emp_dept b on (a.id=b.emp_id) -> where b.dept_id IS NULL; +----+-----------+---------+ | id | name | dept_id | +----+-----------+---------+ | 6 | Dennis-6 | NULL | | 7 | Dennis-7 | NULL | | 8 | Dennis-8 | NULL | | 9 | Dennis-9 | NULL | | 10 | Dennis-10 | NULL | +----+-----------+---------+ # 把 table emp_dept 放在左??的情形(??然以 emp_dept 中的?????榛?盗?盹@示儋料,emp 中比emp_dept 中多的儋料也就不??锢示出?砹拴: select a.id,a.name,b.dept_id from emp_dept b left join emp a on (a.id=b.emp_id); mysql> select a.id,a.name,b.dept_id -> from emp_dept b left join emp a on (a.id=b.emp_id); +------+----------+---------+ | id | name | dept_id | +------+----------+---------+ | 2 | Dennis-2 | DEv | | 1 | Dennis-1 | R&D | | 3 | Dennis-3 | R&D | | 4 | Dennis-4 | Test | | 5 | Dennis-5 | Test | +------+----------+---------+ >> right join --------------- select a.id,a.name,b.dept_id from emp a right join emp_dept b on (a.id=b.emp_id); # 挑儋料?r以右?? table emp_dept 中的儋料?榛?盗?盹@示儋料 mysql> select a.id,a.name,b.dept_id -> from emp a right join emp_dept b on (a.id=b.emp_id); +------+----------+---------+ | id | name | dept_id | +------+----------+---------+ | 2 | Dennis-2 | DEv | | 1 | Dennis-1 | R&D | | 3 | Dennis-3 | R&D | | 4 | Dennis-4 | Test | | 5 | Dennis-5 | Test | +------+----------+---------+ 5 rows in set (0.00 sec) # 我??再把 table 的位置交?Q一下,再用 right join ???? select a.id,a.name,b.dept_id from emp_dept b right join emp a on (a.id=b.emp_id); mysql> select a.id,a.name,b.dept_id -> from emp_dept b right join emp a on (a.id=b.emp_id); +----+-----------+---------+ | id | name | dept_id | +----+-----------+---------+ | 1 | Dennis-1 | R&D | | 2 | Dennis-2 | DEv | | 3 | Dennis-3 | R&D | | 4 | Dennis-4 | Test | | 5 | Dennis-5 | Test | | 6 | Dennis-6 | NULL | | 7 | Dennis-7 | NULL | | 8 | Dennis-8 | NULL | | 9 | Dennis-9 | NULL | | 10 | Dennis-10 | NULL | +----+-----------+---------+ # 是不是和 left join 一?恿丝 >> direct join -------------- # 假如用right join 同不用 Join 直接挑儋料是一样的,它等介於以下的指令 select a.id,a.name,b.dept_id from emp a ,emp_dept b where a.id=b.emp_id; mysql> select a.id,a.name,b.dept_id -> from emp a ,emp_dept b -> where a.id=b.emp_id; +----+----------+---------+ | id | name | dept_id | +----+----------+---------+ | 2 | Dennis-2 | DEv | | 1 | Dennis-1 | R&D | | 3 | Dennis-3 | R&D | | 4 | Dennis-4 | Test | | 5 | Dennis-5 | Test | +----+----------+---------+ 怎?蝇弄明白了?峥 返回类别: 教程 上一教程: SQL注入天书之ASP注入漏洞全接触(1) 下一教程: 当SQL SERVER数据库崩溃时如何恢复？ 您可以阅读与"MYSQL JOIN?解"相关的教程: · MYSQL JOIN详解 · 一个实例讲解MYSQL查询优化 · MYSQL中文问题解决 · MySQL安全性指南 (1) · LINUX网络备份MYSQL |
  |
| 快精灵印艺坊 版权所有 |
首页 会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店
|
||
