赞
踩
- create table emp(
- id int,
- name string,
- deptno int
- )clustered by (id) into 2 buckets row format delimited fields terminated by ",";
-
- truncate table emp;
-
- insert into emp (id, name, deptno)
- values (1,"zhangsan",10),(2,"lisi",20),(3,"wangwu",30),(4,"zhaoliu",10),(5,"xiaoqi",30),(6,"gouba",10),
- (7,"jiujiu",20),(8,"shishi",30),(9,"shiyi",null),(10,"shier",10),(11,"shisan",10),(12,"shisi",30);
-
- select *
- from emp order by id;
-
-
- truncate table dept;
- create table dept(
- deptno int,
- deptname string
- )clustered by (deptno) into 3 buckets row format delimited fields terminated by ",";
-
- insert into dept
- values (10,"研发部"),(20,"开发部"),(30,"宣传部");
-
- select *
- from dept;

- select *
- from emp e inner join dept d on e.deptno = d.deptno order by id;
-
-
- 1,zhangsan,10,10,研发部
- 2,lisi,20,20,开发部
- 3,wangwu,30,30,宣传部
- 4,zhaoliu,10,10,研发部
- 5,xiaoqi,30,30,宣传部
- 6,gouba,10,10,研发部
- 7,jiujiu,20,20,开发部
- 8,shishi,30,30,宣传部
- 10,shier,10,10,研发部
- 11,shisan,10,10,研发部
- 12,shisi,30,30,宣传部
- -- 全连接
- select *
- from emp e full join dept d on e.deptno = d.deptno order by id;
-
-
- 1,zhangsan,10,10,研发部
- 2,lisi,20,20,开发部
- 3,wangwu,30,30,宣传部
- 4,zhaoliu,10,10,研发部
- 5,xiaoqi,30,30,宣传部
- 6,gouba,10,10,研发部
- 7,jiujiu,20,20,开发部
- 8,shishi,30,30,宣传部
- 9,shiyi,,,
- 10,shier,10,10,研发部
- 11,shisan,10,10,研发部
- 12,shisi,30,30,宣传部

- select *
- from emp e left join dept d on e.deptno = d.deptno order by id;
-
-
-
- 1,zhangsan,10,10,研发部
- 2,lisi,20,20,开发部
- 3,wangwu,30,30,宣传部
- 4,zhaoliu,10,10,研发部
- 5,xiaoqi,30,30,宣传部
- 6,gouba,10,10,研发部
- 7,jiujiu,20,20,开发部
- 8,shishi,30,30,宣传部
- 9,shiyi,,,
- 10,shier,10,10,研发部
- 11,shisan,10,10,研发部
- 12,shisi,30,30,宣传部

- -- 修改表数据,将右表某个只设置为空
-
- truncate table dept;
-
- insert into dept
- values (10,"研发部"),(null,"开发部"),(30,"宣传部");
-
- select id, name, e.deptno, d.deptno, deptname
- from emp e right join dept d on e.deptno = d.deptno order by id;
-
-
- ,,,,开发部
- 1,zhangsan,10,10,研发部
- 3,wangwu,30,30,宣传部
- 4,zhaoliu,10,10,研发部
- 5,xiaoqi,30,30,宣传部
- 6,gouba,10,10,研发部
- 8,shishi,30,30,宣传部
- 10,shier,10,10,研发部
- 11,shisan,10,10,研发部
- 12,shisi,30,30,宣传部

Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。