oracle ĬÈϸôÀëµÈ¼¶ÊÇ£º¶ÁÒÑÌá½»¡£
²éѯËø¶¨£¬·ÀÖ¹ÁíÍâÓû§¸üУº
select * from books for update;
µ±Ç°Óû§¸üÐÂÖ®ºó£¬ÁíÍâÓû§¿ÉÒÔ¸ü¸Ä¡£
01¡¢±íÁ¬½Ó
¼Ù¶¨from×Ó¾äÖдÓ×óµ½ÓÒÁ½¸ö±í·Ö±ðΪA£¬B±í¡£
ÄÚÁ¬½Ó£ºÑ¡È¡A¡¢B±íµÄÍêÈ«Æ¥ÅäµÄ¼¯ºÏ£¬Á½±í½»¼¯£º
select empno,ename,emp.deptno A,dept.deptno B,dname from emp inner join dept on emp.deptno=dept.deptno;
×óÍâÁ¬£ºÑ¡È¡A±íÈ«²¿ÒÔ¼°A¡¢B±í½»¼¯
select dname,dept.deptno A,emp.deptno B,empno,ename from dept left join emp on dept.deptno=emp.deptno;
ÓÒÍâÁ¬£ºÑ¡È¡B±íÈ«²¿ÒÔ¼°A¡¢B±í½»¼¯
select empno,ename,emp.deptno A,dept.deptno B,dname from emp right join dept on emp.deptno=dept.deptno;
02¡¢±íÁªºÏ
ÕûºÏ½á¹û¼¯²¢Ïû³ýÖظ´ÐУº
select empno,ename from emp
union
select deptno,dname from dept;
03¡¢¿ÕÖµ
select empno,ename,nvl(comm,0) from emp; --ÕýÈ·£¬commÓë0ÀàÐÍÏàͬ
select empno,ename,nvl(comm,'¿ÕÖµ') from emp; --´íÎó£¬commÓë'¿ÕÖµ'ÀàÐͲ»Í¬
select empno,ename,comm from emp where comm is null;
select empno,ename,comm from emp where comm is not null;
04¡¢ÅÅÐò
select * from emp order by empno,asc;
select * from dept order by deptno desc;
05¡¢Ïû³ýÖظ´Êý¾Ý
select distinct deptno from emp;
06¡¢Ä£ºý²éѯ
select ename from emp where ename like '%K';
_±íʾһ¸ö×Ö·û£¬%±íʾһ¸ö»ò¶à¸ö×Ö·û
07¡¢·Ö×é
select deptno, avg(sal) from emp group by deptno; --·Ö×éÇó³öƽ¾ùнˮ
select deptno, avg(sal) from emp group by deptno, job; --×éºÏ·Ö×é
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;
group by ºóÃæµÄ×ֶΣ¬¿ÉÒÔ²»³öÏÖÔÚselectºóÃ棻
select ºóÃæµÄ×ֶΣ¬Èç¹ûûÓгöÏÖÔÚ¾Û¼¯º¯ÊýÀ¾Í±ØÐë³öÏÖÔÚgroup byºóÃæ¡£
group by ·Ö×é¹ýÂËҪʹÓÃhaving¡£
08¡¢¾Û¼¯º¯Êý
select ename, max(sal) from emp;
ÕâÌõÓï¾ä´íÎó£ºmaxÖ»ÄÜÓÐÒ»¸öÖµ£¬Ö»ÄÜÑ¡È¡Ò»ÐУ¬µ«ÊǵÈÓÚmaxÖµµÄ¼Ç¼¿ÉÄÜÓкü¸¸ö£¬ÎÞ·¨ÕýÈ·Æ¥Å䣬ËùÒÔÓ¦¸ÃʹÓÃ×Ó²éѯ£º
select ename from emp where sal = (select max(sal) from emp);
¾Û¼¯º¯Êý²»ÄÜ×öΪÌõ¼þ³öÏÖÔÚwhere×Ó¾äµÄºó±ß£º
select deptno,sum(sal) from emp where sum(sa
½ñÌì×öÁË1¸öÊý¾Ýµ¼ÈëÐèÇó£¬Îı¾¼Ç¼ÓÐ12Íò¶àÌõ£¬TXTÎļþ´óС6M¶à£¬ÒòΪÒÔÇ°¶¼ÊÇÓÃoracleµÄtext import·½Ê½µ¼È룬¸ÕÊÔÑéÁËһϣ¬²»ÁéÑ飬µ±µ¼Èëµ½2Íò¶àÌõµÄʱºò£¬PLSQL¾Íµ±µôÁË£¬×ß²»¶¯ÁË¡£
ÒòΪ¿¼Âǵ½1´Îµ¼È룬ËùÓоö¶¨²ÉÓÃSQLLOADERµÄ·½Ê½¡£ ......