CAOracle数据库Sql语句详解大全.ppt

OracleSQL,第一章,SELECT查询,本章目标,写一条SELECT查询语句在查询中使用表达式、运算符对空值的处理对查询字段起别名查询字段的连接,SELECT查询基本语法,SELECTFROM,SQLSELECT*FROMs_emp,请查询出s_emp表中所有的员工信息:,查询指定列,SQLSELECTdept_id,salaryFROMs_emp,请查询出s_emp表中所有的员工的部门ID，工资:,运用算数表达式,SQLSELECTsalary*12FROMs_emp,请查询出s_emp表中所有的员工的年薪:,运用算数表达式,括号可以改变运算符运算的优先顺序:,SQLSELECTlast_name,salary,12*salary+1002FROMs_emp;.Velasquez250030100,SQLSELECTlast_name,salary,12*(salary+100)2FROMs_emp;.Velasquez250031200,列别名,请查询出s_emp表中所有的员工的姓名:,SQLSELECTfirname_name|last_nameFROMs_emp,SQLSELECTfirname_name|last_name“姓名”FROMs_emp,请查询出s_emp表中所有的员工的姓名:,空值的处理,请查询出s_emp表中所有的员工的工资:,SQLSELECTlast_name,salary*commission_pct/100“工资”2FROMs_emp;,SQLSELECTlast_name,salary+salary*NVL(commission_pct,0)/1002FROMs_emp;,去掉重复行,请查询出s_dept表的部门名称:,SQLSELECTname2FROMs_dept;,SQLSELECTDISTINCTname2FROMs_dept;,去掉多列重复行,SQLSELECTDISTINCTdept_id,title2FROMs_emp;,请查询出s_emp表中所有的员工的部门ID及职称:,小结,查询表的全部记录查询指定的列给列起别名NVL函数及字符串连接符重复行的处理Distinct,SELECTDISTINCT*,columnalias,.FROMtable;,引言,第二章条件查询,本章目标,WHERE条件查询在查询中使用表达式、运算符使用LIKE、BETWEEN、IN进行模糊查询,第一章内容回顾,对员工表中信息进行查询，具体要求如下：1.查询s_emp表要求输出员工姓名(firs_name、last_name)和实际工资(基本工资+提成)：,查询基本语法,SELECTFROMWHERE,请查询出s_emp表中dept_id为41的员工信息:,WHERE条件查询,请查询出s_emp表中last_name为Smith的员工的信息:,SELECT*FROMs_empWHERElast_name=Smith,请查询出s_emp表中部门ID为50并且工资大于1500的员工的信息:,SELECT*FROMs_empWHEREsalary1500anddept_id=50,WHERE条件查询-BETWEEN,小结,Where条件查询Betweenand,第三章单行函数,本章目标,熟悉各种类型单行函数的使用掌握转换函数的使用,两种SQL函数,单行函数CharacterNumberDateConversion,SINGLE-ROWFUNCTION,多行函数Group,MULTI-ROWFUNCTION,字符函数,LOWER将字符串转换成小写UPPER将字符串变为大写INITCAP将字符串的第一个字母变为大写CONCAT拼接两个字符串，与|相同SUBSTR取字符串的子串LENGTH以字符给出字符串的长度NVL以一个值来替换空值,字符函数举例,LOWER(SQLCourse)sqlcourseUPPER(SQLCourse)SQLCOURSEINITCAP(SQLCourse)SqlCourse,SELECT*FROMs_empWHERElast_name=PATEL,SELECT*FROMs_empWHEREUPPER(last_name)=PATEL,字符操作函数,CONCAT(Good,String)GoodStringSUBSTR(String,1,3)StrLENGTH(String)6,数字函数,ROUND(value,precision)按precision精度4舍5入TRUNC(value,precision)按precision截取value,SQLSELECTround(55.5),round(-55.5),trunc(55.5),trunc(-55.5)FROMdual;round(55.5)round(-55.5)trunc(55.5)trunc(-55.5)-56-5655-55,SQLSELECTTRUNC(124.16666,-2)trunc1,trunc(124.16666,2)FROMdual;TRUNC1TRUNC(124.16666,2)-100124.16,Round,SQLSELECTOrder|TO_CHAR(id)|2wasfilledforatotalof|TO_CHAR(total,fm$9,999,999)3FROMs_ord4WHEREship_date=21-SEP-92;,To-char举例,查询员工表中入职日期在7月份的员工信息：,SELECT*FROMs_empWHEREto_char(start_date,mm)=07,RR日期格式,CurrentYear1995199520012001,SpecifiedDate27-OCT-9527-OCT-1727-OCT-1727-OCT-95,RRFormat1995201720171995,YYFormat1995191720172095,Ifthespecifiedtwo-digityearis,Iftwodigitsofthecurrentyearare,0-49,0-49,50-99,50-99,Thereturndateisinthecurrentcentury.,Thereturndateisinthecenturyafterthecurrentone.,Thereturndateisinthecenturybeforethecurrentone.,Thereturndateisinthecurrentcentury.,转换函数,TO_NUMBER(String)转换字符串到数字,TO_DATE(String)转换字符串到日期格式,SELECTto_date(2009-09-22,yyyy-mm-dd)FROMdual,转换函数的嵌套,F3(F2(F1(col,arg1),arg2),arg3),Step1=Result1,Step2=Result2,Step3=Result3,转换函数嵌套举例,SQLSELECTlast_name,2NVL(TO_CHAR(manager_id),NoManager)3FROMs_emp4WHEREmanager_idISNULL;,查询员工表中manager_id为空的员工查询出来，并将空列的值置为“NoManager”：,小结,字符函数日期函数数值函数转换函数,第四章关联查询,本章目标,在一张或多张表中使用等值或非等值连接使用外连接查询自连接查询,等值连接的种类,等值连接非等值连接外连接自连接,S_EMPTableIDLAST_NAMEDEPT_ID-1Velasquez502Ngao413Nagayama314Quick-To-See105Ropeburn506Urguhart417Menchu428Biri439Catchpole4410Havel4511Magee3112Giljum3213Sedeghi3314Nguyen3415Dumas3516Maduro41,表间的关系,S_DEPTTableIDNAMEREGION_ID-30Finance131Sales132Sales243Operations350Administration1,S_REGIONTableIDNAME-1NorthAmerica2SouthAmerica3Africa/MiddleEast4Asia5Europe,简单关联查询的语法,查询员工表中last_name为Biri的员工的last_name与部门名称查询出来：,SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column1=table2.column2,SQLSELECTe.last_name,2FROMs_empe,s_deptd3WHEREe.dept_id=d.idande.last_name=Biri,非等值连接,SQLSELECTe.ename,e.job,e.sal,s.grade2FROMempe,salgrades3WHEREe.salBETWEENs.losalANDs.hisal;,自连接,S_EMP(WORKER),S_EMP(MANAGER),LAST_NAMEMANAGER_IDIDLAST_NAME-Ngao11VelasquezNagayama11VelasquezRopeburn11VelasquezUrguhart22NgaoMenchu22NgaoBiri22NgaoMagee33NagaymaGiljum33Nagayma.,自连接,查询员工表中last_name为Biri的员工的last_name及其部门经理名称查询出来：,SQLSELECTworker.last_name|worksfor|manager.last_name2FROMs_empworker,s_empmanager3WHEREworker.manager_id=manager.id;,外连接,SQLSELECTworker.last_name|worksfor|manager.last_name2FROMs_empworker,s_empmanager3WHEREworker.manager_id=manager.id（+）;,SQLSELECTworker.last_name|worksfor|manager.last_name2FROMs_empworker,s_empmanager3WHEREworker.manager_id（+）=manager.id;,SQLSELECTworker.last_name|worksfor|manager.last_name2FROMs_empworkerleftouterjoins_empmanager3onworker.manager_id=manager.id;,内连接,SQLSELECTe.last_name,2FROMs_empe,s_deptd3WHEREe.dept_id=d.idande.last_name=Biri,SQLSELECTe.last_name,2FROMs_empeinnerjoins_deptdone.dept_id=d.id3WHEREe.last_name=Biri,小结,等值连接非等值连接外连接自连接,第五章组函数,本章目标,定义及有效的使用组函数使用GroupBy对查询数据分组使用HAVING子句对分组后的数据进行过滤,使用GroupBy的查询语法,SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expressionHAVINGgroup_conditionORDERBYcolumn;,常用组函数,AVG(DISTINCT|ALL|n)COUNT(DISTINCT|ALL|expr|*)MAX(DISTINCT|ALL|expr)MIN(DISTINCT|ALL|expr)SUM(DISTINCT|ALL|n),应用举例,查询s_emp表中所有员工的平均工资：,SQLSELECTavg（salary）2FROMs_emp,查询s_emp表中各个部门员工的平均工资及部门名称：,SQLSELECTe.dept_id,max(),avg(e.salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id；,应用举例,查询s_emp表中31部门一共有多少员工：,SQLSELECTCOUNT(*)2FROMs_emp3WHEREdept_id=31;,查询s_emp表中销售人员的数量(提成率不为空的记录个数)：,SQLSELECTCOUNT(commission_pct)2FROMs_emp,应用举例,SQLSELECTe.dept_id,max(),avg(e.salary),sum(salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id5ORDERBYsum(salary);,查询s_emp表中各个部门员工的平均工资,工资总和及部门名称并按照工资总和排序：,应用举例,SQLSELECTe.dept_id,max(),avg(e.salary),sum(salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.idande.dept_id!=414GROUPBYdept_id5ORDERBYsum(salary);,查询s_emp表中除41部门以外的部门员工的平均工资,工资总和及部门名称并按照工资总和排序：,SQLSELECTe.dept_id,max(),avg(e.salary),sum(salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id5HAVINGe.dept_id!=416ORDERBYsum(salary);,应用举例,求平均工资高于1500的部门的工资总和，最高工资，最低工资：,SQLSELECTe.dept_id,max(),avg(e.salary),sum(e.salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id5HAVINGavg(e.salary)15006ORDERBYavg(e.salary);,课堂练习,求不以“VP”开头职位的，各个职位中工资总和大于5000的职位及工资总和，并按工资总和排序：,SQLSELECTtitle,SUM(salary)PAYROLL2FROMs_emp3WHEREtitleNOTLIKEVP%4GROUPBYtitle5HAVINGSUM(salary)50006ORDERBYSUM(salary);,小结,SELECTcolumn,group_functionFROMtableWHEREconditionGROUPBYgroup_by_expressionHAVINGgroup_conditionORDERBYcolumn;,第六章子查询,子查询,查询s_emp表中工资最低的员工的姓名：,SQLSELECTmin（salary）2FROMs_emp,SQLSELECTlast_name2FROMs_emp3WHEREsalary=最小工资（上一条的运行结果）,SQLSELECTlast_name2FROMs_emp3WHEREsalary=(SELECTmin(salary)FROMs_emp),子查询,查询s_emp表中平均工资低于32部门的部门ID：,SQLSELECTavg（salary）2FROMs_emp3WHEREdept_id=32;,SQLSELECTdept_id,avg(salary)2FROMs_emp3GROUPBYdept_id4HAVINGavg(salary)SELECTdept_id,avg(salary)2FROMs_emp3GROUPBYdept_id4HAVINGavg(salary)SELECTdept_id,avg(salary),name2FROMs_emp,s_dept3WHEREs_emp.dept_id=s_dept.id4GROUPBYdept_id,name5HAVINGavg(salary)SELECTlast_name,first_name,title2FROMs_emp3WHEREdept_in=4(SELECTID5FROMs_dept6WHEREname=Finance7ORregion_id=2);ORA-01427:single-rowsubqueryreturnsmorethanonerow,小结,SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);,第七章数据建模及数据库设计,本章目标,了解系统开发的步骤数据关系的定义理解实体关系映射图（E-R图）,系统开发步骤,数据模型,Modelofsysteminclientsmind,Entitymodelofclientsmodel,Tablemodelofentitymodel,Tablesondisk,EntityRelationshipModelingConcepts,EntityAthingofsignificanceaboutwhichinformationneedstobeknownExamples:customers,salesrepresentatives,ordersAttributeSomethingthatdescribesorqualifiesanentityExamples:name,phone,identificationnumberRelationshipAnassociationbetweentwoentitiesExamples:ordersanditems,customersandsalesrepresentatives,EntityRelationshipModel,Createanentityrelationshipdiagramfrombusinessspecificationsornarratives.Scenario.Assignoneormorecustomerstoasalesrepresentative.Somesalesrepresentativesdonotyethaveassignedcustomers.,RelationshipTypes,One-to-oneHaveadegreeofoneandonlyoneinbothdirections.Arerare.Example:Husbandandwife.Many-to-oneHaveadegreeofoneormoreinonedirectionandadegreeofoneandonlyoneintheotherdirection.Areverycommon.Example:passengersandplane.Many-to-manyHaveadegreeofoneormoreinbothdirections.Areresolvedwithanintersectionentity.Example:Employeesandskills.,数据库3范式,数据库中的每一列都是不可再分的基本数据项，同一列中不能有多个值数据库表中不存在非关键字段对任何候选关键字段的部分依赖数据库表中不存在非关键字段对任何候选关键字段的传递引用,约束类型,PKPrimaryKey唯一且非空FKForeignKey外键约束，值引用另一张表已经存在的数据UKUniqueKey唯一且可为空NOTNULL非空,约束举例,IDLAST_NAMEFIRST_NAME.DEPT_ID.1VelasquezCarmen502NgaoLaDoris413NagayamaMidori314Quick-To-SeeMark105RopeburnAudry50,IDNAMEREGION_ID10Finance131Sales141Operations150Administration1,PrimaryKey,ForeignKey,S_EMPTable,S_DEPTTable,PrimaryKey,第八章创建表,本章目标,掌握创建表的语法Oracle的数据类型使用约束,数据结构,一个Oracle数据库包含下列几种数据结构：Table存储数据View从一个表或多个表的数句中得到的子集Sequence生成主键值Index提高查询性能,建表语法,CREATETABLEschema.table(columndatatypeDEFAULTexprcolumn_constraint,.table_constraint);,Oracle的数据类型,Char(size)定长字符型，字符长度不够自动在右边加空格符号Varchar2(size)可变长字符型，大小必须指定Number(m,n)数字型，可存放实数和整数Date日期类型Blob2进制大对象其最大大小为4GB此数据类型映射到Byte类型的Array。Clob2进制大对象其最大大小为4GB此数据类型映射到String,命名规范,必须以字母开头1-30个字符长度只允许包含AZ,az,09,_,$,and#在一个数据库保证命名的唯一不能使用Oracle内部的关键字,建表举例,CREATETABLEMytest(idnumber，namevarchar2(32);,CREATETABLEMytest(idnumberdeault11，namevarchar2(32);,建表使用约束举例,CREATETABLEMytest(idnumbercheck(id10),namevarchar2(32);,CREATETABLEMytest(idnumbercheck(id10),namevarchar2(32)notnull);,CREATETABLEMytest(idnumberUNIQUE,namevarchar2(32)notnull);,建表主键约束举例,CREATETABLEMytest(idnumberprimarykey,namevarchar2(32)notnull);,CREATETABLEMytest(idnumber,namevarchar2(32)notnull,primarykey(id);,CREATETABLEMytest(mnumber,nnumber,primarykey(m,n);,建表外键约束举例,CREATETABLEparent(idnumberprimarykey,namevarchar2(32);,CREATETABLEchild(idnumberprimarykey,p_idnumberreferencesparent(id);,CREATETABLEchild(idnumberprimarykey,p_idnumber,foreignkey(p_id)referencesparent(id);,约束命名,CREATETABLEchild(idnumberconstraintmytest_pkprimarykey,p_idnumber,foreignkey(p_id)referencesparent(id);,查看表的约束,SELECT*FROMuser_constraintsWHEREtable_name=CHILD,CONSTRAINT_TYPEC-checkP-primarykeyR-forgienkeyU-unique,级联删除,CREATETABLEchild(idnumberprimarykey,p_idnumberreferencesparent(id)ondeletecascade);,CREATETABLEchild(idnumberprimarykey,p_idnumberreferencesparent(id)ondeletesetnull);,子查询创建表,CREATETABLEemp_41as(select*froms_empwheredept_id=41);,第九章对数据的操作,本章目标,在已创建表中插入新的数据修改已经存在的数据删除表中的数据理解事物控制及其重要性,DML命令,DescriptionAddsanewrowtothetable.Modifiesexistingrowsinthetable.Removesexistingrowsfromthetable.Makesallpendingchangespermanent.Allowsarollbacktothatsavepointmarker.Discardsallpendingdatachanges.,CommandINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACK,Insert插入语法,INSERTINTOtable(column,column.)VALUES(value,value.);,插入举例,CREATETABLEMytest(idnumberprimarykey,namevarchar2(32),birthDate);,INSERTINTOmytest(id,name,brith)VALUES(1,BluesWang,?);,更新语法,UPDATEtableSETcolumn=value,column=valueWHEREcondition;,删除语法,DELETEFROMtableWHEREcondition;,DatabaseTransactions,Containoneofthefollowingstatements:DMLcommandsthatmakeuponeconsistentchangetothedataOneDDLcommandOneDCLcommandBeginwhenthefirstexecutableSQLcommandisexecuted.Endwithoneofthefollowingevents:COMMITorROLLBACKDDLorDCLcommandexecutes(automaticcommit)Errors,exit,orsystemcrash,还原点,SQLUPDATE.SQLSAVEPOINTupdate_done;Savepointcreated.SQLINSERT.SQLROLLBACKTOupdate_done;Rollbackcomplete.,ControllingTransactions,COMMIT,ROLLBACK,INSERT,UPDATE,INSERT,DELETE,SavepointMarkerA,SavepointMarkerB,ROLLBACK,ROLLBACKtoA,ROLLBACKtoB,第十章修改表结构及添加约束,本章目标,增加或者修改列添加删除约束删除表删除表所有的数据,添加列,ALTERTABLEtableADD(columndatatypeDEFAULTexprNOTNULL,columndatatype.);,添加列举例,向下表mytest添加名为age，类型为number的一列：,ALTERTABLEmytestADD(agenumber);,再向下表mytest添加名为salary,类型为number(9,1)缺省值为8888的一列：,ALTERTABLEmytestADD(salarynumber(9,1)default8888);,删除列,ALTERTABLEtableDROPcolumn,column.;,删除列举例,将表mytest的salary列删除：,ALTERTABLEmytestdropcolumnsalary;,修改列,ALTERTABLEtableMODIFY(columndatatypeDEFAULTexprNOTNULL,columndatatype.);,修改列举例,将表mytest的age列由原来的number类型更改为number（4，2）类型：,ALTERTABLEmytestmodify(agenumber(4,2);,添加约束,SQLALTERTABLEtable2ADDCONSTRAINTconstrainttype(column);,添加约束举例,将表mytest的id列添加主键约束：,ALTERTABLEmytestADDconstraintsmytest_pkprimarykey(id);,将表child的p_id列添加外键约束：,ALTERTABLEchildADDconstraintsc_fkforeignkey(p_id)referencesparent(id);,删除约束举例,SQLALTERTABLEchild2DROPCONSTRAINTc_fk;,删除表及表的重命名,DROPTABLEtableCASCADECONSTRAINTS;,SQLRENAME表名TO新名;,删除表数据,SQLTRUNCATETABLE表名;,小结,CommandCREATETABLEALTERTABLEDROPTABLERENAMETRUNCATE,DescriptionCreatesatableandindicatedconstraints.Modifiestablestructuresandconstraints.Removestherowsandtablestructure.Changesthenameofatable,view,sequence,orsynonym.Removesallrowsfromatableandreleasesthestoragespace.,第十一章创建序列,本章目标,掌握如何使用sequences创建sequences修改sequences删除sequences,创建sequences语法,CREATESEQUENCEnameINCREMENTBYnSTARTWITHnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHE,创建序列举例,SQLCREATESEQUENCEs_dept_id2INCREMENTBY13STARTWITH514MAXVALUE99999995NOCACHE6NOCYCLE;Sequencecreated.,序列举例,利用创建好的Sequence向mytest表中添加数据：,insertintomytestvalues(s.nextval,a),查看当前序列的值：,SELECTs.currvalFROMdual,修改sequences语法,ALTERSEQUENCEnameINCREMENTBYnSTARTWITHnMAXVALUEn|NOMAXVALUEMINVALUEn|NOMINVALUECYCLE|NOCYCLECACHEn|NOCACHE,删除sequences语法,DROPSEQUENCEname,第十二章视图、索引,本章目标,掌握如何使用视图、索引创建视图、索引修改视图、索引删除视图、索引,IDLAST_NAMEFIRST_NAMETITLEDEPT_ID-1VelasquezCarmenPresident502NgaoLaDorisVP,Operations4