搜索
©逻辑运算符和谓词in和notin有哪些职员和分析员SELECTename,jobFORMempWHEREjobIN('clerk','analyst')between和notbetween哪些雇员的工资在2000和3000之间SELECTename,job,salFORMempWHEREsalBETWEEN2000AND3000like,notlikeSELECTename,deptnoFORMempWHEREenameLIKE'S%'isnull,isnotnullSELECTename,jobFORMempWHEREcommISNULL©数据操作数据的插入普通插入INSERTINTOdept(empno,ename,deptno,empdate)VALUES(10,'accounting','newyork',to_char(SYSDATE,'YYYY/MM/DDhh24:mi:ss'))插入的时候同时查询INSERTINTOdept(empno,ename,deptno,empdate)VALUES(10,'accounting',(SELECTdeptnoFROMdepempWHEREempno=10ANDrownum=1),to_char(SYSDATE,'YYYY/MM/DDhh24:mi:ss'))从其它表中选择插入数据INSERTINTOemp(empno,ename,deptno)SELECTid,name,departmentFORMold_empWHEREdepartmentin(10,20,30,40)插入空值（NULL）INSERTINTOdeptVALUES(50,'education',NULL)数据更新UPDATEempSETdeptno=40,job='marketrep'WHEREjob='salesman'数据删除DELETEempWHEREempno=765;数据查询等值连接joinSELECTempno,ename,job,emp.deptno,dnameFORMempJOINdeptONemp.deptno=dept.deptno等价于SELECTempno,ename,job,emp.deptno,dnameFORMemp,deptWHEREemp.deptno=dept.deptno左外连接left(outer)joinSELECTm_user.user_cd,m_user.user_number,m_user.user_nameFORMm_userLEFTOUTERJOINm_user_accountONm_user.user_cd=m_user_account.user_cd等价于SELECTm_user.user_cd,m_user.user_number,m_user.user_nameFORMm_user,m_user_accountWHEREm_user.user_cd=m_user_account.user_cd(+)右外连接right(outer)joinSELECTm_user.user_cd,m_user.user_number,m_user.user_nameFORMm_userRIGHTJOINm_user_accountONm_user.user_cd=m_user_account.user_cd等价于SELECTm_user.user_cd,m_user.user_number,m_user.user_nameFORMm_user,m_user_accountWHEREm_user.user_cd(+)=m_user_account.user_cd全外连接full(outer)joinSELECTm_user.user_cd,m_user.user_number,m_user.user_nameFROMm_userFULLJOINm_user_accountONm_user.user_cd=m_user_account.user_cd等价于SELECTm_user.user_cd,m_user.user_number,m_user.user_nameFORMm_user,m_user_accountWHEREm_user.user_cd=m_user_account.user_cd(+)UNIONSELECTm_user.user_cd,m_user.user_number,m_user.user_nameFORMm_user,m_user_accountWHEREm_user.user_cd(+)=m_user_account.user_cd非等值连接SELECTename,salFORMemp,salgradeWHEREsalgrade.grade=3andemp.salbetweensalgrade.losalandsalgrade.hisal子查询SELECTename,deptnoFORMempWHEREdeptno=(SELECTdeptnoFORMempWHEREename='smith')字符串拼接SELECTrecvwork_user_cd||'_GP'FROMt_receivework使用CASE语句SELECT(CASEWHENrecvwork_grouping_cd='GP0001'THENto_char(recvwork_user_cd||'_GP1')WHENrecvwork_grouping_cd='GP0002'THENto_char(recvwork_user_cd||'_GP2')ELSEto_char(recvwork_user_cd||'_GPN')END)ASrecvwork_user_cdFROMt_receivework取TOPN行SELECT*FROMcatWHEREROWNUM=N取N1-N2行SELECTemName,emNoFROM(SELECTROWNUMROWSEQ,emName,emNoFROMcat)WHEREROWSEQBETWEENN1ANDN2使用Group和HavingSELECTrecvwork_user_cd,count(*)ASworkcountFROMt_receiveworkGROUPBYrecvwork_user_cdHAVINGcount(*)2使用Exists语句SELECT*FROMt_receiveWHEREEXISTS(SELECT*FROMt_receivework_requestWHEREt_receive.receive_cd=t_receivework_request.receive_cdANDt_receivework_request.contact_grouping_cd='GP0001')union运算返回一个查询结果中有但又不重要的行，它将基表或视图中的记录合并在一起SELECTename,salFORMaccountWHEREsal2000UNIONSELECTename,salFORMresearchWHEREsal2000intersect运算返回查询结果中相同的部分SELECTjobFORMaccountINTERSECTSELECTjobFORMresearchminus运算返回在第一个查询结果中与第二个查询结果不相同的那部分行记录SELECTjobFORMaccountMINUSSELECTjobFORMsales;©ORACLE系统函数字符型函数initcap(ename)将ename中每个词的第一个字母改为大写length(ename)计算字符串的长度substr(job,1,4)取子字符串。如果job值为helloworld，则值为helllower变为小写的字符串upper变为大写的字符串least取出字符串列表中按字母排序排在最前面的一个串greatest取出字符串列表中按字母排序排在最后的一个串instr(字段名,'字符串')0是否在字符串中日期函数add_month(hiredate,5)在hiredate时间上加5个月month_between(sysdate,hiredate)计算hiredate时间与系统时间之间相差的月数next_day(hiredate,'FRIDAY')计算hiredate日期之后的第一个星期五的日期日期=to_date(字符串,格式)to_date('2001-08-01','YYYY-MM-DD')字符串=to_char(日期,格式)to_char(SYSDATE,'YYYY/MM/DDhh24:mi:ss')D一周中的星期几DAY天的名字，使用空格填充到9个字符DD月中的第几天DDD年中的第几天DY天的简写名IWISO标准的年中的第几周IYYYISO标准的四位年份YYYY四位年份YYY,YY,Y年份的最后三位，两位，一位HH小时，按12小时计HH24小时，按24小时计MI分SS秒MM月Mon月份的简写Month月份的全名W该月的第几个星期WW年中的第几个星期当前时间减去7分钟的时间;selectsysdate-interval'7'MINUTEfromdual;当前时间减去7小时的时间;selectsysdate-interval'7'hourfromdual;当前时间减去7天的时间;selectsysdate-interval'7'dayfromdual;当前时间减去7月的时间;selectsysdate-interval'7'monthfromdual;当前时间减去7年的时间;selectsysdate-interval'7'yearfromdual;时间间隔乘以一个数字;selectsysdate-8*interval'2'hourfromdual;selectto_char(sysdate,'yyyy-mm-ddhh24:mi:ss')fromdual;selectto_char(sysdate,'yyyy-mm-ddhh:mi:ss')fromdual;selectto_char(sysdate,'yyyy-dddhh:mi:ss')fromdual;selectto_char(sysdate,'yyyy-mmiw-dhh:mi:ss')fromdual;算术函数least(v1,v2)trunc(sal,0)取sal的近似值（截断）avg(sal)计算sal的平均值stddev(sal)计算sal的平均差sum(sal)计算sal的总值空值函数nvl(v1,v2)NVL(EXPR1,EXPR2)函数解释:IFEXPR1=NULLRETURNEXPR2ELSERETURNEXPR1switch语句DECODE(AA,V1,R1,V2,R2...)IFAA=V1THENRETURNR1IFAA=V2THENRETURNR2..…ELSERETURNNULL补位函数LPAD(char1,n,char2)字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位聚组函数count(comm)selectcount(distinctjob)fromempmin(comm)最小值max(comm)最大值列名select*fromUSER_COL_COMMENTSuwhereu.table_name='M_NAME'分页SELECT*FROM(SELECTA.*,rownumrFROM(XXXXXXXXXXX)AWHERErownum=PageUpperBound)BWHERErPageLowerBound;©关于表、View和索引的操作创建表CHAR固定长度的字符串VARCHAR2可变长度的字符串NUMBER(M,N)数字型M是位数总长度,N是小数的长度DATE日期类型默认值DEFAULTSYSDATE不允许重复UNIQUE关键字PRIMARYKEYCREATETABLEdept(deptnonumber(2)notnull,dnam