insert all 多表插入
他可以同时操作多个表,将多条insert语句共同协作一句sql完成;
语法: insert all into 表名 values(列名) (可以跟搜索语句);
表连接:
表连接分为左联接,右连接,全连接;
左联接
select * from emp a left join dept b on a.deptno = b.deptno
右连接
select * from emp a right join dept b on a.deptno = b.deptno
全连接
select * from emp a full join dept b on a.deptno = b.deptno
自连接
select t.ename from emp join emp t on emp.EMPNO = t.MGR
92表连接语法:
select * from emp e,dept d where e.deptno = d.deptno
99年新语法:
cross 交叉,cross join 交叉连接;
表连接 :
select * from emp cross join dept;
左联接 :
select * from emp left cross join dept;
集合查询主要就三种:并集,交集,差集;
并集,不重复union:将两个查询语句的结果集合斌,但是显示条件确实必须一样的,如"select ename,dname ";
select ename,dname from emp e join dept d on e.deptno = d.deptno where DNAME='SALES'
union
select ename,dname from emp e join dept d on e.deptno = d.deptno where DNAME='RESEARCH'
并集,重复:union all
select ename,dname from emp e join dept d on e.deptno = d.deptno where DNAME='SALES'
union all
select ename,dname from emp e join dept d on e.deptno = d.deptno where DNAME='RESEARCH'
交集,intersect
用法如上;
差集,满足前者,而不满足后者;minus
用法如上;