數(shù)據(jù)庫(kù)編程day03oracle mysql-oralce學(xué)習(xí)超詳細(xì)筆記

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'--用兩個(gè)連續(xù)單引號(hào)進(jìn)行單引號(hào)轉(zhuǎn)義FROMemployees；SELECTemployee_id,fist_name,last_name,salry,salary*12FROMemployees；--例SELECTFROM--例SELECTemployee_idASidFROMemployees；--例SELECTemployee_idAS"Id"--雙引號(hào)中的字符原樣顯示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，first_name,last_name,salaryFROMemployeesWHEREsalary=--例SELECTemployee_id，first_name,last_name,salaryFROMemployeesWHEREsalary<>17000;例SELECTemployee_id，first_name,last_name,salaryFROMemployeesWHEREsalary>=2500ANDsalary<=SELECTemployee_id,job_id，first_name,FROMemployeesWHEREjob_id=--例2：查詢?nèi)肼毜膯T工信息SELECTemployee_id,job_id,first_nameFROMemployeesWHEREhire_date09-7月1）BETWEENSELECTemployee_id,job_id,first_name,salaryFROMemployeesWHEREsalaryBETWEEN2500ANDSELECTemployee_id,job_id,first_name,salary,hire_dateFROMemployeesWHEREhire_dateBETWEEN01-1月-1998'AND31-12月2)SELECTemployee_id,job_id,first_name,salary,hire_dateFROMemployeesWHEREjob_idIN--例SELECTemployee_id,job_id,first_name,salary,hire_dateFROMemployeesWHERENOTjob_idINSELECTemployee_id,job_id,first_name,salary,hire_dateFROMemployeesWHEREjob_idNOTIN--SELECTemployee_id,job_id,first_name,salary,hire_dateFROMemployeesWHEREjob_idLIKESELECTemployee_id,job_id,first_name,salary,hire_dateFROMemployeesWHEREfirst_nameLIKESELECTemployee_id,job_id,first_name,salary,hire_dateFROMemployeesWHEREfirst_nameLIKE'%a%e%'ORfirst_nameLIKESELECTemployee_id,job_id,first_name,salary,hire_dateFROMemployeesWHEREhire_dateLIKESELECTemployee_id,job_id,first_name,salary,hire_dateFROMemployeesWHEREjob_idLIKE'%\_P%'ESCAPE--4.SELECT,FROM和WHERESELECTemployee_id,last_nameASename,salaryFROMemployeesWHERElast_nameKing';Error,在WHERE

SELECTemployee_id,first_name,last_name,salatyFROMemployeesORDERBYsalary--例SELECTemployee_id,first_name,last_nameFROMemployeesORDERBY--例SELECTemployee_id,first_name,last_name,salatyFROMemployeesORDERBY4;--是字段在SELECT--例SELECTemployee_id,first_name,last_name,salatyFROMemployeesWHEREsalary>6000ORDERBYsalary;SELECTemployee_id,first_name,last_name,salaty*12FROMemployeesORDERBYSELECTemployee_id,first_name,last_name,salaty*12ASannualFROMemployeesORDERBY--例SELECTemployee_id,first_name,last_name,salaty*12AS"annualSalary"FROMemployeesORDERBY"annual--例SELECTemployee_id,first_name,last_name,salatyFROMemployeesORDERBYSELECTemployee_id,first_name,last_name,salatyFROMemployeesORDERBYjob_id,salaryDESC;--只約束--SELECTLOWER(last_name)FROMSELECTUPPER(last_name)FROMSELECTINITCAP('last_name Odog')FROMdual;--SELECTfirst_name||''||last_nameASfullnameFROMemployees;SELECTCONCAT(first_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--SELECTfirst_name,last_name,TO_CHAR(hire_date)FROMemployees;--例SELECTfirst_name,last_name,TO_CHAR(hire_date,'YYYY-MM-DDDYHH24:MI:SS')FROM--日--分--秒SELECTFROMSELECTfirst_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將字符串解析成數(shù)值--例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')FROMSELECTfirst_name,last_name,job_id,salary,)ASnew_salaryFROMemployeesORDERBYjob_id;--例SELECTfirst_name,last_name,job_id,salary,CASEjob_idWHEN'IT_PROG'THENsalary*2WHEN'ST_CLERK'THENsalary*1.2ELSEsalary*1.1ENDASnew_salaryFROMemployeesORDERBYSELECTfirst_name,last_name,job_id,salary,WHENjob_id='IT_PROG'THENsalary*2WHENjob_id='ST_CLERK'THENsalary*1.2ELSEsalary*1.1ENDASnew_salaryFROMemployeesORDERBYNULLSELECTeFROM--例SELECTemployee_id,first_name,last_name,salaryFROMemployeesWHEREcommission_pct=SELECTemployee_id,first_name,last_name,salaryFROMemployeesWHEREcommission_pct!=--例SELECTemployee_id,first_name,last_name,salaryFROMemployeesWHEREcommission_pctISSELECTemployee_id,first_name,last_name,salaryFROMemployeesWHEREcommission_pctISNOT--例SELECTemployee_id,first_name,last_name,job_idFROMemployeesWHEREjob_id='IT_PROG'ANDjob_id!= --例SELECTemployee_id,first_name,last_name,job_idFROMemployeesWHEREjob_id='IT_PROG'ORjob_id!= --例SELECTemployee_id,first_name,last_name,job_idFROMemployeesWHERENOTjob_id!=--邏輯NOT取值規(guī)律：NOTNULL--4)ORDERBY子句中的--例SELECTemployee_id,first_ FROMemployeesORDERBYSELECT FROMORDERBYcommission_pctNULLSFIRST;--NULLS--例eFROM--例 mission_pct,0)ASFROM--3.NULLIF

FROM--語(yǔ)法--4。--例employee_id,first_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一起使用，用于對(duì)分組后的信息進(jìn)行過(guò)濾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=SELECTe.employee_id,e.first_name,e.last_name,FROMemployeese,employeesWHEREe.manager_id=SELECTdepartment_name,country_nameFROMdepartmentsd,locationsl,countriescWHEREd.location_id=l.location_idANDl.country_id=SELECTe.first_name,e.last_name,e.salary,g.grade_level,FROMemployeese,job_gradesWHEREe.salaryBETWEENg.lowest_salAND--例SELECTFROMemployeese,departmentsWHEREe.department_idd.department_id(+)--例SELECTe.employee_id,e.first_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.first_name,d.department_nameFROMemployeeseCROSSJOINdepartmentsSELECTe.last_name,e.first_name,d.department_nameFROMemployeese,departmentsdWHEREd.department_id=SELECTe.employee_id,e.first_name,d.department_nameFROMemployeeseINNERJOINdepartmentsON(d.department_id=SELECTe.employee_id,e.first_name,d.department_nameFROMemployeeseJOINdepartmentsON(d.department_id=SELECTe.employee_id,e.first_name,d.department_nameFROMemployeeseJOINdepartments--例SELECTe.employee_id,e.first_name,d.department_nameFROMemployeeseJOINdepartmentsdeptUSINGSELECTe.employee_id,e.first_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.first_name,d.department_nameFROMemployeeseLEFTOUTERJOINdepartmentsON(e.department_idSELECTe.employee_id,e.first_name,d.department_nameFROMemployeeseRIGHTLEFTOUTERJOINdepartmentsON(d.department_idSELECTd.department_id,department_nameFROMdepartmentsdLEFTOUTERJOINemployeesON(d.department_id=e.department_id)WHEREe.department_idISNULL;SELECTe.employee_id,e.first_name,d.department_nameFROMemployeeseFULLOUTERJOINdepartmentsON(e.department_idSELECTe.employee_id,e.first_name,d.department_nameFROMemployeeseFULLOUTERJOINdepartmentsON(e.department_idWHEREe.department_idISNULLORd.department_idIS--第七章SELECTemployee_id,first_name,last_name,job_idFROMemployeesWHEREjob_id=(SELECTjob_idFROMWHEREfirst_name=SELECTemployee_id,first_name,last_name,job_idFROMemployeesWHEREjob_id=(SELECTjob_idFROMWHEREfirst_name=SELECTemployee_id,first_name,last_name,salary,job_idFROMemployeesWHEREsalary<(SELECTAVG(salary)FROMemployeesSELECTjob_id,AVG(salary)FROMemployeesGROUPBYjob_idHAVINGAVG(salary)=(SELECTFROMemployeesGROUPBYjob_idSELECTemployee_id,first_name,last_name,department_id,job_idFROMemployeesWHERE(department_id,job_id)=(SELECTdepartment_id,job_idFROMemployeesWHEREfirst_name=SELECTemployee_id,first_name,last_name,FROMemployeesWHEREemployee_idIN(SELECTmanager_idFROMemployeesSELECTemployee_id,first_name,last_name,salary,job_idFROMemployeesWHERE(job_id,salary)IN(SELECTjob_id,MAX(salary)FROMemployeesGROUPBYSELECTemployee_id,first_name,last_nameFROMemployeesWHERENOTemployee_idIN(SELECTmanager_idFROMemployeesSELECTemployee_id,first_name,last_nameFROMemployeesWHERENOTemployee_idIN(SELECTNVL(manager_id,-1)FROMemployeesSELECTemployee_id,first_name,last_name,department_idFROMemployeesWHEREdepartment_idIN(SELECTdepartment_idFROMdepartmentsWHERElocation_idIN(SELECTlocation_idFROMWHEREcity=)SELECTemployee_id,first_name,last_name,department_idFROMWHEREdepartment_idIN(SELECTdepartment_idFROMemployeesWHEREfirst_namelikeSELECTemployee_id,first_name,last_name,salaryFROMemployeesWHEREsalary<ALL(SELECTAVG(salary)FROMemployeesGROUPBYjob_idSELECTemployee_id,first_name,last_name,salaryFROMemployeesWHEREsalary<ALL(SELECTAVG(salary)FROMemployeesWHEREdepartment_id>1000GROUPBYjob_idSELECTemployee_id,first_name,last_name,salaryFROMemployeesWHEREsalary<ANY(SELECTAVG(salary)FROMemployeesWHEREdepartment_id>1000GROUPBYjob_idSELECTemployee_id,first_name,last_name,salaryFROMemployeese,(SELECTjob_id,AVG(salary)FROMemployeesGROUPBYjob_id)WHEREe.job_id=a.job_idANDe.salary>SELECTemployee_id,first_name,last_name,salaryFROMemployeese1WHEREsalary>(SELECTAVG(salary)FROMemployeese2WHEREe1.job_id=SELECTdepartment_id,salaryFROMemployeese1WHEREsalary=(SELECTMAX(salary)FROMemployeese2WHEREe1.department_id=SELECTemployee_id,first_name,last_name,job_idFROMemployeese1WHEREEXISTSSELECTemployee_idFROMemployeese2WHEREe2.manager_id=SELECTemployee_id,first_name,last_name,job_idFROMemployeese1WHERENOTEXISTSSELECTemployee_idFROMemployeese2WHEREe2.manager_id=SELECTemployee_id,first_name,last_name,job_idFROMemployeese1WHEREEXISTSSELECTemployee_idFROMjob_historyWHEREe1.employee_id=--2:SELECTdepartment_id,department_nameFROMdepartmentsdWHERENOTEXISTS(SELECT'OK'FROMemployeesWHEREe1.department_id=--TOPNSELECTROWNUM,employee_id,first_name,last_nameFROMemployees;SELECTROWNUM,employee_id,first_name,last_nameFROMemployeesWHEREdepartment_id=SELECTROWNUM,employee_id,first_name,last_nameFROMemployeesWHEREROWNUM--例SELECTROWNUM,employee_id,first_name,last_nameFROMemployeesWHEREROWNUMBETWEEN6ANDSELECTROWNUM,employee_id,first_name,last_nameFROMemployeesWHEREROWNUM--例SELECTROWNUM,employee_id,first_name,last_nameFROMemployeesORDERBYSELECTemployee_id,first_name,last_nameSELECTROWNUMlinenum,employee_id,first_name,last_nameSELECTemployee_id,first_name,last_nameFROMemployeesORDERBY)WHEREROWNUM<=)WHERElinenum--SELECTemployee_id,job_idFROMjob_historySELECTemployee_id,job_idFROMemployees;--例2：關(guān)于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=50UNIONALLSELECTfirst_nameFROMemployeesWHEREdepartment_id=--練習(xí)：查詢沒(méi)有員工的部門(mén)編號(hào)、名稱，以及沒(méi)有部門(mén)的員工的編號(hào)和SELECTemployee_id,first_nameFROMWHEREdepartment_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表示小數(shù)部分位數(shù) CREATETABLEproducts(P_codeNUMBER(6)P_nameVARCHAR2(30CHAR)P_descVARCHAR2(100p_priceNUMBER(6,2)is_importCHAR(1)DEFAULT'N'

--例CREATETABLEworkersAS(SELECTemployee_id,first_name,last_name,salaryFROMemployeesCREATETABLEworkersAS(SELECTemployee_id,first_name,last_name,salaryFROMemployeesWHEREemployee_id--例CREATETABLEworkersAS(AS

FROMCREATETABLEworkers()AS

SELECTemployee_id,first_name,last_name,salaryFROMemployees--例DROPTABLE--例TRUNCATETABLE--第十章數(shù)據(jù)的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:列的值可以唯一標(biāo)識(shí)一條記錄FOREIGNKEY：用來(lái)表與表之間的參照完整性--2.NOTNULL