多表联查

1. 内连接查询
2. 外连接查詢
3. 查询结果合并
4. 子查询

1 建表语句：
2 drop table if exists t_dept;
3 create table t_dept(
4 _id int primary key,
5 deptno int(11),
6 dname varchar(20),
7 loc varchar(50));

1 插入数据语句：
2 insert into t_dept
3 (_id,deptno,dname,loc)
4 values
5 ('1','10','ACCOUNTING','NEW YORK'),
6 ('2','20','RESEARCH','DALLAS'),
7 ('3','30','SALES','CHICAGO'),
8 ('4','40','OPERATIONS','BOSTON')

 

1.内连接

 1 方法1：
 2 select t1.ename,t1.job,t2.ename 
 3 from
 4       t_employee t1,
 5       t_employee t2 
 6 where
 7         t1.mgr = t2.empno; 
 8 
 9 
10 方法2：
11 select t1.ename,t1.job,t2.ename
12 from t_employee t1
13 inner join t_employee t2 on t1.mgr = t2.empno;

 

2.外链接查询

1 左外连接,left左边作为主表
2 select t1.ename,t1.job,t2.ename as mgr_name
3 from t_employee t1
4 left outer  t_employee t2 on t1.mgr = t2.empno;
5 
6 右外连接
7 select t1.empno,t1.ename,t1.job,t2.dname,t2.loc
8 from t_employee t1
9 right outer  t_dept t2 on t1.deptno = t2.deptno;

 

转载于:https://www.cnblogs.com/Allen-Wei/p/9016565.html