




版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
SELECTFROMemployees;DESCemployees;SETlinesizeSETPAGESIZE--例SELECTtable_nameFROMuser_tablesSELECTemployee_id,fisrt_name,last_name.salaryFROMemployees;SELECTFROMSELECTFROMdual;--这是Oracle--例SELECT'oOracleSQL'--在Oracle中如何表示字符串常量FROMemployees;SELECT'oOracleSQL'--在Oracle中如何表示字符串常量FROMdual;--例SELECT'oOracle''sSQL'--用两个连续单引号进行单引号转义FROMemployees;--例1:查询员工的、和年SELECTemployee_id,fist_name,last_name,salry,salary*12FROMemployees;--例SELECTFROM--例SELECTemployee_idASidFROMemployees;--例SELECTemployee_idAS"Id"--双引号中的字符原样显示FROMemployees;--例SELECTemployee_id,fist_name,last_name,salry,salary*12ASannual_salaryFROMemployees;--例SELECTemployee_id,fist_name,last_name,salry,salary*12AS"annualsalary"FROMemployees;SELECTemployee_id||''||fist_name,last_name,salry,salary*12ASFROM--例SELECTDISTINCTdepartment_idFROMemployees;--例SELECTDISTINCTdepartment_id,job_idFROMemployees;SELECTFROMemployeesWHEREsalary>8000;--例SELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREsalary=--例SELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREsalary<>17000;----例SELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREsalary>=2500ANDsalary<=SELECTemployee_id,job_id,_name,FROMemployeesWHEREjob_id=--例2:查询入职的员工信息SELECTemployee_id,job_id,_nameFROMemployeesWHEREhire_date09-7月---1)BETWEENSELECTemployee_id,job_id,_name,salaryFROMemployeesWHEREsalaryBETWEEN2500ANDSELECTemployee_id,job_id,_name,salary,hire_dateFROMemployeesWHEREhire_dateBETWEEN01-1月-1998'AND31-12月---2)SELECTemployee_id,job_id,_name,salary,hire_dateFROMemployeesWHEREjob_idIN--例SELECTemployee_id,job_id,_name,salary,hire_dateFROMemployeesWHERENOTjob_idINSELECTemployee_id,job_id,_name,salary,hire_dateFROMemployeesWHEREjob_idNOTIN--SELECTemployee_id,job_id,_name,salary,hire_dateFROMemployeesWHEREjob_idLIKE--查询_name首字母是大写S,第三个字母是e的员工SELECTemployee_id,job_id,_name,salary,hire_dateFROMemployeesWHERE_nameLIKESELECTemployee_id,job_id,_name,salary,hire_dateFROMemployees _nameLIKE'%a%e%'OR_nameLIKESELECTemployee_id,job_id,_name,salary,hire_dateFROMemployeesWHEREhire_daIKESELECTemployee_id,job_id,_name,salary,hire_dateFROMemployeesWHEREjob_idLIKE'%\_P%'ESC--4.SELECT,FROM和WHERESELECTemployee_id,last_nameASename,salaryFROMemployeesWHERElast_nameKing';Error,在WHERE
SELECTemployee_id,_name,last_name,salatyFROMemployeesORDERBYsalary--例SELECTemployee_id,_name,last_nameFROMemployeesORDERBY--例SELECTemployee_id,_name,last_name,salatyFROMemployeesORDERBY4;--是字段在SELECT--例SELECTemployee_id,_name,last_name,salatyFROMemployeesWHEREsalary>6000ORDERBYsalary;SELECTemployee_id,_name,last_name,salaty*12FROMemployeesORDERBYSELECTemployee_id,_name,last_name,salaty*12ASannualFROMemployeesORDERBY--例SELECTemployee_id,_name,last_name,salaty*12AS"annualSalary"FROMemployeesORDERBY"annual--例SELECTemployee_id,_name,last_name,salatyFROMemployeesORDERBYSELECTemployee_id,_name,last_name,salatyFROMemployeesORDERBYjob_id,salaryDESC;--只约束--SELECTLOWER(last_name)FROMSELECTUPPER(last_name)FROMSELECTINITCAP('last_name Odog')FROMdual;--SELECT_name||''||last_nameASfullnameFROMemployees;SELECTCONCAT(_name,CONCAT('',last_name))ASfullnameFROMemployees;SELECTLPAD(employee_id,8,'123')FROMSELECTRPAD(employee_id,2,'123')FROM--SELECTSUBSTR('ABCD1234234NARSna',4)FROMSELECTSUBSTR(last_name,4),last_nameFROMemployees;--SELECTINSTR('SQLallowsfordynamicDBchanges','all')FROMdual;SELECTINSTR('SQLallowsfordynamicDBchanges','a',6)FROMdual;SELECTINSTR('SQLallowsfordynamicDBchanges','a',6,2)FROMdual;--SELECT FROMSELECTTRIM(LEADING'A'FROM'AAABA FROMSELECTTRIM(TRAILING'A'FROM'AAABA FROMSELECTTRIM('A'FROM'AAABA FROM--SELECTREPLACE('SQL*PLUSsupportsloopsorifstatements.','doesnotsupport')FROMdual;--SELECTLENGTH('SQLletsyousupportsloopsorif)FROM--SELECTROUND(168.888,2),TRUNC(168.888,2)FROMSELECTFROMSELECTFROMFROM--SELECTFROM--SELECTFROM--SELECTSYSDATEFROMSELECTSYSDATE7FROMSELECTSYSDATE100/24FROM--FROM--SELECTADD_MONTHS(SYSDATE,3)FROM--SELECTNEXT_DAY(SYSDATE,FROMSELECTFROMSELECTFROMSELECTFROM--SELECTLAST_DAY(SYSDATE)FROM--SELECT_name,last_name,TO_CHAR(hire_date)FROMemployees;--例SELECT_name,last_name,TO_CHAR(hire_date,'YYYY-MM-DDDYHH24:MI:SS')FROM--日--分--秒SELECTFROMSELECT_name,last_name,TO_CHAR(salary*1.6,'$999,999.99')FROMemployees;--SELECTTO_DATE('03-9月-FROMSELECTFROMSELECTTO_DATE('05-FEB-199819:36:36','DD.MON.YYYYFROMFROMFROM--3)TO_NUMBER将字符串解析成数值--例SELECTFROMSELECTROUND(TO_DATE('16-2月-FROM周日
SELECTROUND(TO_DATE('16-10月-FROMSELECTROUND(TO_DATE('16-10月-FROMSELECTTRUNC(TO_DATE('3-MAR-2010'),'DAY')FROMSELECTTRUNC(TO_DATE('3-MAR-2010'),'MONTH')FROMSELECTTRUNC(TO_DATE('3-MAR-2010'),'YEAR')FROMSELECT_name,last_name,job_id,salary,)ASnew_salaryFROMemployeesORDERBYjob_id;--例SELECT_name,last_name,job_id,salary,CASEjob_idWHEN'IT_PROG'THENsalary*2WHEN'ST_CLERK'THENsalary*1.2ELSEsalary*1.1ENDASnew_salaryFROMemployeesORDERBYSELECT_name,last_name,job_id,salary,WHENjob_id='IT_PROG'THENsalary*2WHENjob_id='ST_CLERK'THENsalary*1.2ELSEsalary*1.1ENDASnew_salaryFROMemployeesORDERBYNULLSELECTemployee_id,eFROM--例SELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREcommission_pct=SELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREcommission_pct!=--例SELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREcommission_pctISSELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREcommission_pctISNOT--例SELECTemployee_id,_name,last_name,job_idFROMemployeesWHEREjob_id='IT_PROG'ANDjob_id!= --例SELECTemployee_id,_name,last_name,job_idFROMemployeesWHEREjob_id='IT_PROG'ORjob_id!= --例SELECTemployee_id,_name,last_name,job_idFROMemployeesWHERENOTjob_id!=--逻辑NOT取值规律:NOTNULL--4)ORDERBY子句中的--例SELECTemployee_id,_ FROMemployeesORDERBYSELECTemployee_id, FROMORDERBYcommission_pctNULLS;--NULLS--例eFROM--例 mission_pct,0)ASFROM--3.NULLIFSELECTemployee_id,_name,last_name,NVL(NULLIF(salary,17000),7000)
FROM--语法--4。--例employee_id,_name,last_name,salary+salary*COALESCE(commission_pct,0)ASFROM----例SELECTCOUNT(employee_id)FROMemployees;--例SELECTFROM--例SELECTFROM--例SELECTCOUNT(job_id)FROMemployees;SELECTCOUNT(DISTINCTFROM--例SELECTCOUNT(commission_pct)FROMemployees;SELECTCOUNT(1)FROMWHEREdepartment_id=----例SELECTAVG(salary)ASavg_salfFROMemployees;--例SELECTAVG(salary)ASavg_salfFROMemployeesWHEREdepartment_id=--例SELECTAVG(salary)ASavg_salfFROMemployeesWHEREjob_id=--SELECTSUM(salary)FROMemployees;SELECTSUM(salary),AVG(salary),COUNT(*)FROMWHEREdepartment_id=SELECTMIN(salary),MAX(salary)FROMemployees;--1)GROUP--例SELECTdepartment_id,AVG(salary)FROMemployeesGROUPBYSELECTSUM(salary),AVG(salary),COUNT(*)FROMemployeesGROUPBYSELECTdepartment_id,job_id,AVG(salary)FROMemployeesGROUPBY--2)分组查询中的ORDERSELECTdepartment_id,AVG(salary)ASavgFROMemployeesGROUPBYdepartment_idORDERBYavg;SELECTdepartment_id,AVG(salary)ASavgFROMemployeesGROUPBYdepartment_idORDERBYAVG(salary);SELECTdepartment_id,AVG(salary)ASavgFROMemployeesGROUPBYdepartment_idORDERBY2;SELECTdepartment_id,AVG(salary)ASavgFROMemployeesGROUPBYORDERBYsalary;--Error!SELECTjob_id,AVG(salary)ASavgFROMemployeesWHEREAVG(salary)>8000--Error!GROUPBYjob_id;--例2:HAVING只能跟GROUPBY一起使用,用于对分组后的信息进行过滤SELECTjob_id,AVG(salary)ASavgFROMemployeesGROUPBYjob_idHAVINGAVG(salary)SELECTjob_id,AVG(salary)ASavgFROMemployeesGROUPBYHAVINGsalary>8000;--SELECTjob_id,AVG(salary)ASavgFROMemployeesGROUPBYjob_idHAVINGavg>8000; SELECTdepartment_id,AVG(salary)FROMemployeesWHEREdepartment_id>60GROUPBYdepartment_id;SELECTdepartment_id,AVG(salary)FROMemployeesGROUPBYdepartment_idHAVINGdepartment_id>SELECTMIN(AVG(salary)),MAX(AVG(salary))FROMemployeesGROUPBYjob_idSELECTSUM(salary*NVL(commission_pct,0))ASsum_sal,AVG(salary*NVL(commission_pct,0))ASavg_sal,FROM--例SELECTcountries.*,regions.*FROMcountries,regions;SELECTcou.*,reg.*FROMcountriescou,regions--例SELECTc.country_name,c.region_id,r.region_id,r.region_nameFROMcountriesc,regionsr;FROMcountriesc,regionsrWHEREc.region_id=FROMemployeese,departmentsdWHEREe.department_id=--例2:查询员工的、和其主管SELECTe.employee_id,e._name,e.last_name,FROMemployeese,employeesWHEREe.manager_id=SELECTdepartment_name,country_nameFROMdepartmentsd,locationsl,countriescWHEREd.location_id=l.location_idANDl.country_id= SELECTe. FROMemployeese,job_gradesWHEREe.salaryBETWEENg.lowest_salAND--例SELECTe.employee_id,e.FROMemployeese,departmentsWHEREe.department_idd.department_id(+)--例SELECTe.employee_id,e._name,e.last_name,d.department_nameFROMemployeese,departmentsdWHEREd.department_id(+)e.department_idSELECTd.department_id,department_nameFROMemployeese,departmentsdWHEREd.department_id=--1)SELECTe.employee_id,e._name,d.department_nameFROMemployeeseCROSSJOINdepartments--例1:查询员工的和所在的部门的名称FROMemployeese,departmentsdWHEREd.department_id=SELECTe.employee_id,e._name,d.department_nameFROMemployeeseINNERJOINdepartmentsON(d.department_id=SELECTe.employee_id,e._name,d.department_nameFROMemployeeseJOINdepartmentsON(d.department_id=SELECTe.employee_id,e._name,d.department_nameFROMemployeeseJOINdepartments--例SELECTe.employee_id,e._name,d.department_nameFROMemployeeseJOINdepartmentsdeptUSINGSELECTe.employee_id,e._name,d.department_nameFROMemployeeseJOINdepartmentsUSINGSELECTe.employee_id,e.job_id,e.salary,d.department_nameFROMemployeeseJOINdepartmentsdUSING(department_id);SELECTdepartment_id,department_name,cityFROMdepartmentsdJOINlocationslUSING(location_id)WHEREdepartment_id>SELECTdepartment_id,department_name,country_nameFROMdepartmentsdJOINlocationslJOINcountriescSELECTe.employee_id,e._name,d.department_nameFROMemployeeseLEFTOUTERJOINdepartmentsON(e.department_idSELECTe.employee_id,e._name,d.department_nameFROMemployeeseRIGHTLEFTOUTERJOINdepartmentsON(d.department_idSELECTd.department_id,department_nameFROMdepartmentsdLEFTOUTERJOINemployeesON(d.department_id=e.department_id)WHEREe.department_idISNULL;SELECTe.employee_id,e._name,d.department_nameFROMemployeeseFULLOUTERJOINdepartmentsON(e.department_idSELECTe.employee_id,e._name,d.department_nameFROMemployeeseFULLOUTERJOINdepartmentsON(e.department_idWHEREe.department_idISNULLORd.department_idIS--第七章SELECTemployee_id,_name,last_name,job_idFROMemployeesWHEREjob_id=(SELECTjob_idFROMWHERE_name=SELECTemployee_id,_name,last_name,job_idFROMemployeesWHEREjob_id=(SELECTjob_idFROMWHERE_name=SELECTemployee_id,_name,last_name,salary,job_idFROMemployeesWHEREsalary<(SELECTAVG(salary)FROMemployeesSELECTjob_id,AVG(salary)FROMemployeesGROUPBYjob_idHAVINGAVG(salary)=(SELECTFROMemployeesGROUPBYjob_idSELECTemployee_id,_name,last_name,department_id,job_idFROMemployeesWHERE(department_id,job_id)=(SELECTdepartment_id,job_idFROMemployeesWHERE_name=SELECTemployee_id,_name,last_name,FROMemployeesWHEREemployee_idIN(SELECTmanager_idFROMemployeesSELECTemployee_id,_name,last_name,salary,job_idFROMemployeesWHERE(job_id,salary)IN(SELECTjob_id,MAX(salary)FROMemployeesGROUPBYSELECTemployee_id,_name,last_nameFROMemployeesWHERENOTemployee_idIN(SELECTmanager_idFROMemployeesSELECTemployee_id,_name,last_nameFROMemployeesWHERENOTemployee_idIN(SELECTNVL(manager_id,-1)FROMemployeesSELECTemployee_id,_name,last_name,department_idFROMemployeesWHEREdepartment_idIN(SELECTdepartment_idFROMdepartmentsWHERElocation_idIN(SELECTlocation_idFROMWHEREcity=)--2.查询与名字(_name)中包含字母x的员工在相同部门工作的员工的信息SELECTemployee_id,_name,last_name,department_idFROMWHEREdepartment_idIN(SELECTdepartment_idFROMemployeesWHERE_namelikeSELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREsalary<ALL(SELECTAVG(salary)FROMemployeesGROUPBYjob_idSELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREsalary<ALL(SELECTAVG(salary)FROMemployeesWHEREdepartment_id>1000GROUPBYjob_idSELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREsalary<ANY(SELECTAVG(salary)FROMemployeesWHEREdepartment_id>1000GROUPBYjob_idSELECTemployee_id,_name,last_name,salaryFROMemployeese,(SELECTjob_id,AVG(salary)FROMemployeesGROUPBYjob_id)WHEREe.job_id=a.job_idANDe.salary>SELECTemployee_id,_name,last_name,salaryFROMemployeese1WHEREsalary>(SELECTAVG(salary)FROMemployeese2WHEREe1.job_id=SELECTdepartment_id,salaryFROMemployeese1WHEREsalary=(SELECTMAX(salary)FROMemployeese2WHEREe1.department_id=SELECTemployee_id,_name,last_name,job_idFROMemployeese1WHEREEXISTSSELECTemployee_idFROMemployeese2WHEREe2.manager_id=SELECTemployee_id,_name,last_name,job_idFROMemployeese1WHERENOTEXISTSSELECTemployee_idFROMemployeese2WHEREe2.manager_id=SELECTemployee_id,_name,last_name,job_idFROMemployeese1WHEREEXISTSSELECTemployee_idFROMjob_historyWHEREe1.employee_id=--2:SELECTdepartment_id,department_nameFROMdepartmentsdWHERENOTEXISTS(SELECT'OK'FROMemployeesWHEREe1.department_id=--TOPNSELECTROWNUM,employee_id,_name,last_nameFROMemployees;SELECTROWNUM,employee_id,_name,last_nameFROMemployeesWHEREdepartment_id=SELECTROWNUM,employee_id,_name,last_nameFROMemployeesWHEREROWNUM--例SELECTROWNUM,employee_id,_name,last_nameFROMemployeesWHEREROWNUMBETWEEN6ANDSELECTROWNUM,employee_id,_name,last_nameFROMemployeesWHEREROWNUM--例SELECTROWNUM,employee_id,_name,last_nameFROMemployeesORDERBYSELECTemployee_id,_name,last_nameSELECTROWNUMlinenum,employee_id,_name,last_nameSELECTemployee_id,_name,last_nameFROMemployeesORDERBY)WHEREROWNUM<=)WHERElinenum--SELECTemployee_id,job_idFROMjob_historySELECTemployee_id,job_idFROMemployees;--例2:关于ORDERSELECTemployee_id,job_idFROMjob_historySELECTemployee_id,job_idFROMemployeesORDERBYSELECTemployee_id,job_idj1FROMjob_historySELECTemployee_id,job_idj2FROMemployeesORDERBYSELECTemployee_id,job_idj1FROMjob_historySELECTemployee_id,job_idj2FROMemployeesORDERBY--2.UNIONSELECTemployee_id,job_idFROMjob_historyUNIONSELECTemployee_id,job_idFROMemployeesORDERBY--例SELECTdepartment_nameASdname_and_enameFROMdepartmentsWHEREdepartment_id=50UNIONALLSELECT_nameFROMemployeesWHEREdepartment_id=SELECTemployee_id,_nameFROMemployeesWHEREdepartment_idisNULLUNIONALLSELECTd.department_id,department_nameFROMdepartmentsdLEFTOUTERJOINemployeesON(d.department_id=e.department_id)WHEREe.department_idISNULL;--SELECTemployee_id,job_idFROMjob_historySELECTemployee_id,job_idFROMemployees;SELECTemployee_idFROMjob_historySELECTemployee_idFROMemployees;nameCHARnameCHAR(45)nameCHAR(45BYTE)nameCHAR(45CHAR)nameVARCHAR2(45)nameVARCHAR2(45BYTE)nameVARCHAR2(45salaryNUMBERsalaryNUMBER(12)salaryNUMBER(8,2)2表示小数部分位数 CREATETABLEproducts(P_codeNUMBER(6)P_nameVARCHAR2(30CHAR)P_descVARCHAR2(100p_priceNUMBER(6,2)is_importCHAR(1)DEFAULT'N'
--例CREATETABLEworkersAS(SELECTemployee_id,_name,last_name,salaryFROMemployeesCREATETABLEworkersAS(SELECTemployee_id,_name,last_name,salaryFROMemployeesWHEREemployee_id=--例CREATETABLEworkersAS(AS
FROMCREATETABLEworkers()AS
SELECTemployee_id,_name,last_name,salaryFROMemployees--例DROPTABLE--例TRUNCATETABLE--第十章数据的CREATETABLEemp )ASFROMCREATETABLE)SELECTdept.department_id,dept.department_name,loc.cityFROMdepartmentsdeptJOINlocationslocCREATETABLEworkers(idNUMBER(6),nameVARCHAR2(20),salarycommissionNUMBER(4)DEFAULT----例INSERTINTOVALUESINSERTINTOworkers( VALUES('Peter',4500.00,2000,166);--例INSERTINTOworkers(name,salary)VALUES('Peter',4500.00);--例INSERTINTOworkers( VALUES('Peter',4500.00,DEFAULT);--例INSERTINTOVALUES--例INSERTINTOemp(empo,hiredate,job,sal)VALUES266,'08-3月-2011','IT_PROG',8888);--例INSERTINTO--例INSERTINTOVALUES(266,DATE'2010-03---例INSERTINTOworkers FROMINSERTINTOworkers(id,name)SELECTempo,enameFROM----例UPDATEempSETsal=999,job='IT_NARS'WHEREenameLIKE'%x%';--例UPDATEempSET(job,sal)=SELSCTjob,salFROMempWHEREempno=)WHEREempno=--DELETEFROMemp--FROMWHEREempno= --第十一章约束PRIMARYFOREIGN用户自定义完整性>NOTNOTNULL:列的值不能为空PRIMARYKEY:列的值可以唯一标识一条记录FOREIGNKEY:用来表与表之间的参照完整性--2.NOTNULL--1)在创建表的时候指定NOTNULLCREATETABLEdept(deptnodnameVARCHAR2(15)CONSTR
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 加盟合同范例4篇
- 专项购买服务合同范例
- app商业合同范例
- 仓储搬运合同范例
- 制作家具合同范例
- 个人签订业务合同范例
- 冰激凌销售合同范例
- 章末检测试卷(一)-物理人教版选择性必修第二册
- 2021一建《建设工程项目管理》点睛卷考试考点题库合集及答案解析八
- 2021年福建省南平市注册会计财务成本管理真题(含答案)
- 化工安全工程:第四章 泄漏源及扩散模式
- 流变性以及其调整
- 超市便利店日常工作检查记录表
- 医院关于待岗、停岗的规定
- 细支气管肺泡癌的影像诊断(61页)
- 10大气复合污染条件下新粒子生成与二次气溶胶增长机制
- X射线的物理学基础-
- 财务英语英汉对照表
- 脑电图学专业水平考试大纲初级
- 铁路贯通地线暂行技术条件_图文
- 某煤矿采空区积水疏放措施(标准版)
评论
0/150
提交评论