Oracle表的基本查询操作及注意事项

《Oracle表的基本查询操作及注意事项》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、Oracle表的基本查询操作及注意事项1.查询表结构descdept（dept为表名）；如下：SQL>descemp;NameTypeNullableDefaultComments-------------------------------------------EMPNONUMBER(4)ENAMEVARCHAR2(10)YJOBVARCHAR2(9)YMGRNUMBER(4)YHIREDATEDATEYSALNUMBER(7,2)YCOMMNUMBER(7,2)YDEPTNONUMBER(2)Y2.select*查询最花时间。在做复

2、杂查询的时候不要轻易使用。Select*fromemp；如下：SQL>select*fromemp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------------------------2009xxbCLERK2010xxbCLERK2012xxbCLERK7369SMITHCLERK79021980-12-17800.00207499ALLENSALESMAN76981981-02-201600.00300.0030752

3、1WARDSALESMAN76981981-02-221250.00500.00307566JONESMANAGER78391981-04-022975.00207654MARTINSALESMAN76981981-09-281250.001400.00307698BLAKEMANAGER78391981-05-012850.00307782CLARKMANAGER78391981-06-092450.00107788SCOTTANALYST75661987-04-193000.00207839KINGPRESIDENT1981-11-1

4、75000.00107844TURNERSALESMAN76981981-09-081500.000.00307876ADAMSCLERK77881987-05-231100.00207900JAMESCLERK76981981-12-03950.00307902FORDANALYST75661981-12-033000.00207934MILLERCLERK77821982-01-231300.00103.distinct消除重复行。Selectdistinctenamefromemp；如下：SQL>selectdistinctenam

5、efromemp;ENAME----------ALLENJONESFORDCLARKMILLERSMITHWARDxxbMARTINSCOTTTURNERADAMSBLAKEKINGJAMES4简单.疯狂复制法：insertintousersvalues（userID，userName，userpass）select*fromusers。5.oracle查询语句where限制语句里区分大小写如whereename='SMITH'，SMITH不能写成smith。否则查询不到结果。6.匹配符的使用%表示任意0到多个字符。-表示任意单子个字符

6、，如：whereenamelike'_o%'查询第二个字符为o的。如：whereenamelike'%a%'查询名字含有a的列。7.in关键词的使用。whereempNOin（2009,7900,6）//集。条件满足括号中的任意几个或一个。如下：SQL>select*fromempwhereempNOin(2009,7900,6);EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO----------------------------------------------------------------2009

7、xxbCLERK7900JAMESCLERK76981981-12-03950.00308.nvl（exp1，exp2）的使用。SQL>selectename,sal+nvl(comm,0)*13年工资fromemp;//sal年薪，comm月奖金。ENAME年工资--------------------xxbxxbxxbSMITH800ALLEN5500WARD7750JONES2975MARTIN19450BLAKE2850CLARK2450SCOTT3000KING5000TURNER1500ADAMS1100JAMES950FOR

8、D3000MILLER1300注：.nvl（exp1，exp2）如果exp1为null,则返回exp2不空就返回ｅｘｐ１的值；举个简单例子，通常是这么用的：如果一个字段，比如ename的值有可