其他-超详细学习笔记

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