关于连接
等值连接的几种不同写法
1.两个相连的表用’,‘相隔开
select e.deptno,dname,ename from emp e,dept d where e.deptno = d.deptno;
2.’,'用’inner join’替代,'where’用’on’替代
select e.deptno,dname,ename from emp e inner join dept d on e.deptno=d.deptno;
3.相等的条件用using(deptno)代替
select deptno,dname,ename from emp e inner join dept d using(deptno) order by deptno;
左外连接几种不同写法:等号左边的所查询的表中的内容会全部显示,等号右边没有对应的选项用空值代替
1.select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+);
2.select e1.ename,e2.ename from emp e1 left outer join emp e2 on e1.mgr = e2.empno;
3.select e1.ename,e2.ename from emp e1 left outer join emp e2 using(deptno);
全外连接(full outer join)
select d.deptno,dname,ename from emp e full outer join dept d on e.deptno = d.deptno;
一些函数
replace 函数:替代函数,用于替代某个字符串
1.用字符’wang’替代字符’tang’
select replace(‘tangshuo’,‘tang’,‘wang’) from dual;
2.没有第三个参数表示删除字符’tang’
select replace(‘tangshuo’,‘tang’) from dual;
trim 函数:消除函数,只能消除字符串前后的某些字符
1.消除字符左右两端空格
select trim(’ tangshuo ‘) || ‘is a student’ from dual;
2.消除字符串前后的所有字符’z’
select trim(‘z’ from ‘zzztangshuozzz’) from dual;
3.效果同上
select trim(both’z’ from ‘zzztangshuozzz’) from dual;
4.只删除字符串头部的’z’
select trim(leading ‘z’ from ‘zzztangshuozzz’) from dual;
5.只删除字符串尾部的’z’
select trim(trailing ‘z’ from ‘zzztangshuozzz’) from dual;