Oracle数据库Sql语句详解大全

OracleSQL第一章SELECT查询本章目标写一条SELECT查询语句在查询中使用表达式、运算符对空值的处理对查询字段起别名查询字段的连接SELECT查询基本语法SELECT<列名>FROM<表名>SQL>SELECT*FROMs_emp请查询出s_emp表中所有的员工信息:查询指定列SQL>SELECTdept_id,salaryFROMs_emp请查询出s_emp表中所有的员工的部门ID，工资:运用算数表达式SQL>SELECTsalary*12FROMs_emp请查询出s_emp表中所有的员工的年薪:运用算数表达式括号可以改变运算符运算的优先顺序:SQL>SELECTlast_name,salary,12*salary+1002FROMs_emp;...Velasquez250030100SQL>SELECTlast_name,salary,12*(salary+100)2FROMs_emp;...Velasquez250031200列别名请查询出s_emp表中所有的员工的姓名:SQL>SELECTfirname_name||last_nameFROMs_empSQL>SELECTfirname_name||last_name“姓名”FROMs_emp请查询出s_emp表中所有的员工的姓名:空值的处理请查询出s_emp表中所有的员工的工资:SQL>SELECTlast_name,salary*commission_pct/100“工资”2FROMs_emp;SQL>SELECTlast_name,salary+salary*NVL(commission_pct,0)/1002FROMs_emp;去掉重复行请查询出s_dept表的部门名称:SQL>SELECTname2FROMs_dept;SQL>SELECTDISTINCTname2FROMs_dept;去掉多列重复行SQL>SELECTDISTINCTdept_id,title2FROMs_emp;请查询出s_emp表中所有的员工的部门ID及职称:小结查询表的全部 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 查询指定的列给列起别名NVL函数及字符串连接符重复行的处理DistinctSELECT[DISTINCT]{*,column[alias],...}FROMtable;引言第二章条件查询本章目标WHERE条件查询在查询中使用表达式、运算符使用LIKE、BETWEEN、IN进行模糊查询第一章 内容 财务内部控制制度的内容财务内部控制制度的内容人员招聘与配置的内容项目成本控制的内容消防安全演练内容 回顾对员工表中信息进行查询，具体要求如下：1.查询s_emp表要求输出员工姓名(firs_name、last_name)和实际工资(基本工资+提成)：查询基本语法SELECT<列名>FROM<表名>[WHERE<查询条件表达式>]列名称SELECT*表名FROMS_emp过滤条件WHEREdept_id=41请查询出s_emp表中dept_id为41的员工信息:WHERE条件查询请查询出s_emp表中last_name为Smith的员工的信息:SELECT*FROMs_empWHERElast_name='Smith'请查询出s_emp表中部门ID为50并且工资大于1500的员工的信息:SELECT*FROMs_empWHEREsalary>1500anddept_id=50WHERE条件查询-BETWEEN&IN请查询出s_emp表中工资在1500到2000之间的员工信息：SELECT*FROMs_empWHEREsalarybetween1500and2000请查询出s_dept表中region_id为1，3的部门信息：SELECT*FROMs_deptWHEREregion_idin(1,3)WHERE条件查询-like请查询出s_emp表中姓中含有字母a的员工信息：SELECT*FROMs_empWHERElast_namelike'%a%'请查询出当前用户下所有以‘s_’开头的表：SELECTtable_nameFROMuser_tablesWHEREtable_namelike'S\_%'escape'\'请查询出s_emp表姓中第二个字母为a的员工信息：SELECT*FROMs_empWHERElast_namelike‘_a%'空值的查询查询出s_emp表中非销售职位的员工信息：SELECT*FROMs_empWHEREcommission_pctisnull课堂练习已建立好的S_emp表，对此表中的数据进行模糊查询，具体要求如下：查询一名last_name以“M”开头的员工，他的dept_id好像是’3X’查询工资在1200至1500之间的员工查询来自部门ID为(41，42，43)的员工查询结果排序查询出s_emp表将部门ID为41的员工的工资按从高到低排列显示出来：SELECT*FROMs_empWHEREdept_id=41ORDERBYsalaryDESCSQL>SELECTlast_name,dept_id,salary2FROMs_emp3ORDERBYdept_id,salaryDESC;小结Where条件查询Between…and&In&Like模糊查询对查询结果排序SELECT[DISTINCT]{*,column[alias],...}FROMtable[WHEREcondition(s)][ORDERBY{column,expr,alias}[ASC|DESC]];第三章单行函数本章目标熟悉各种类型单行函数的使用掌握转换函数的使用两种SQL函数单行函数CharacterNumberDateConversionSINGLE-ROWFUNCTION多行函数GroupMULTI-ROWFUNCTION字符函数LOWER将字符串转换成小写UPPER将字符串变为大写INITCAP将字符串的第一个字母变为大写CONCAT拼接两个字符串，与||相同SUBSTR取字符串的子串LENGTH以字符给出字符串的长度NVL以一个值来替换空值字符函数举例LOWER('SQLCourse')sqlcourseUPPER('SQLCourse')SQLCOURSEINITCAP('SQLCourse')SqlCourseSELECT*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截取valueSQL>SELECTround(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-55SQL>SELECTTRUNC(124.16666,-2)trunc1,trunc(124.16666,2)FROMdual;TRUNC1TRUNC(124.16666,2)----------------------------100124.16Round&Trunc函数ROUND(45.923,2)45.92ROUND(45.923,0)46ROUND(45.923,-1)50TRUNC(45.923,2)45.92TRUNC(45.923)45TRUNC(45.923,-1)40日期函数MONTHS_BETWEEN(date2,date1)给出Date2-date1的月数ADD_MONTHS增加或减去月份NEXT_DAY(date,’day’)给出日期date之后下一天的日期LAST_DAY(date)返回日期所在月的最后一天日期函数MONTHS_BETWEEN(‘01-SEP-95’,‘11-JAN-94’)19.774194ADD_MONTHS('11-JAN-94',6)'11-JUL-94‘NEXT_DAY('01-SEP-95','FRIDAY')'08-SEP-95‘LAST_DAY('01-SEP-95')'30-SEP-95'日期函数ROUND('25-MAY-95','MONTH')01-JUN-95ROUND('25-MAY-95','YEAR')01-JAN-95TRUNC('25-MAY-95','MONTH')01-MAY-95TRUNC('25-MAY-95','YEAR')01-JAN-95转换函数TO_CHAR(date,'fmt')转换日期格式到字符串用下列格式显示字符为数字：9代表一个数字.0显示前缀零.$根据本地语言环境显示货币.L采用当地货币符号.打印一个小数点.,千位计算法显示.日期格式DY星期几的缩写Mon,Tue,...DAY星期几的全拼Monday,Tuesday,...D一周的星期几，星期天=1，星期六=71,2,3,4,5,6,7DD一月的第几天，1311，2，...31W一个月的第几周，151,2,3,4,5WW,IW一年的第几周，一年的ISO的第几周1,2,3,4,...52MM两为数的月01,02,03,...12MON月份的缩写Jan,Feb,Mar,...DecMONTH月份的全拼January,February,...YYYY,YYY,YY,Y四位数的年，三位数的年1999,999,99,9YEAR年的全拼NineteenNinety-nineRR当前年份的后两位数字01代表2001年HH,HH1212小时制，1121,2,3,...12HH2424小时制，0230,1,2,3,...23MI一小时中的第几分，0590,1,2,3...59SS一分中的第几秒，0590,1,2,3,...59To-char举例SQL>SELECTlast_name,TO_CHAR(start_date,2'fmDdspth"of"MonthYYYYfmHH:MI:SSAM')HIREDATE3FROMs_emp4WHEREstart_dateLIKE'%91';SQL>SELECT'Order‘||TO_CHAR(id)||2'wasfilledforatotalof‘||TO_CHAR(total,'fm$9,999,999')3FROMs_ord4WHEREship_date='21-SEP-92';To-char举例查询员工表中入职日期在7月份的员工信息：SELECT*FROMs_empWHEREto_char(start_date,'mm')=07RR日期格式CurrentYear1995199520012001SpecifiedDate27-OCT-9527-OCT-1727-OCT-1727-OCT-95RRFormat1995201720171995YYFormat1995191720172095Ifthespecifiedtwo-digityearisIftwodigitsofthecurrentyearare0-490-4950-9950-99Thereturndateisinthecurrentcentury.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=Result1Step2=Result2Step3=Result3转换函数嵌套举例SQL>SELECTlast_name,2NVL(TO_CHAR(manager_id),'NoManager')3FROMs_emp4WHEREmanager_idISNULL;查询员工表中manager_id为空的员工查询出来，并将空列的值置为“NoManager”：小结字符函数日期函数数值函数转换函数第四章关联查询本章目标在一张或多张表中使用等值或非等值连接使用外连接查询自连接查询等值连接的种类等值连接非等值连接外连接自连接S_EMPTableIDLAST_NAMEDEPT_ID------------------------1Velasquez502Ngao413Nagayama314Quick-To-See105Ropeburn506Urguhart417Menchu428Biri439Catchpole4410Havel4511Magee3112Giljum3213Sedeghi3314Nguyen3415Dumas3516Maduro41表间的关系S_DEPTTableIDNAMEREGION_ID--------------------------30Finance131Sales132Sales243Operations350Administration1S_REGIONTableIDNAME-----------------------1NorthAmerica2SouthAmerica3Africa/MiddleEast4Asia5Europe简单关联查询的语法查询员工表中last_name为’Biri’的员工的last_name与部门名称查询出来：SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column1=table2.column2SQL>SELECTe.last_name,d.name2FROMs_empe,s_deptd3WHEREe.dept_id=d.idande.last_name=‘Biri’非等值连接SQL>SELECTe.ename,e.job,e.sal,s.grade2FROMempe,salgrades3WHEREe.salBETWEENs.losalANDs.hisal;自连接S_EMP(WORKER)S_EMP(MANAGER)LAST_NAMEMANAGER_IDIDLAST_NAME-------------------------------Ngao11VelasquezNagayama11VelasquezRopeburn11VelasquezUrguhart22NgaoMenchu22NgaoBiri22NgaoMagee33NagaymaGiljum33Nagayma......Server自连接查询员工表中last_name为’Biri’的员工的last_name及其部门经理名称查询出来：SQL>SELECTworker.last_name||'worksfor'||manager.last_name2FROMs_empworker,s_empmanager3WHEREworker.manager_id=manager.id;外连接SQL>SELECTworker.last_name||'worksfor'||manager.last_name2FROMs_empworker,s_empmanager3WHEREworker.manager_id=manager.id（+）;SQL>SELECTworker.last_name||'worksfor'||manager.last_name2FROMs_empworker,s_empmanager3WHEREworker.manager_id（+）=manager.id;SQL>SELECTworker.last_name||'worksfor'||manager.last_name2FROMs_empworkerleftouterjoins_empmanager3onworker.manager_id=manager.id;内连接SQL>SELECTe.last_name,d.name2FROMs_empe,s_deptd3WHEREe.dept_id=d.idande.last_name=‘Biri’SQL>SELECTe.last_name,d.name2FROMs_empeinnerjoins_deptdone.dept_id=d.id3WHEREe.last_name=‘Biri’小结等值连接非等值连接外连接自连接第五章组函数本章目标定义及有效的使用组函数使用GroupBy对查询数据分组使用HAVING子句对分组后的数据进行过滤使用GroupBy的查询语法SELECTcolumn,group_functionFROMtable[WHEREcondition][GROUPBYgroup_by_expression][HAVINGgroup_condition][ORDERBYcolumn];常用组函数AVG(DISTINCT|ALL|n)COUNT(DISTINCT|ALL|expr|*)MAX(DISTINCT|ALL|expr)MIN(DISTINCT|ALL|expr)SUM(DISTINCT|ALL|n)应用举例查询s_emp表中所有员工的平均工资：SQL>SELECTavg（salary）2FROMs_emp查询s_emp表中各个部门员工的平均工资及部门名称：SQL>SELECTe.dept_id,max(d.name),avg(e.salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id；应用举例查询s_emp表中31部门一共有多少员工：SQL>SELECTCOUNT(*)2FROMs_emp3WHEREdept_id=31;查询s_emp表中销售人员的数量(提成率不为空的记录个数)：SQL>SELECTCOUNT(commission_pct)2FROMs_emp应用举例SQL>SELECTe.dept_id,max(d.name),avg(e.salary),sum(salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id5ORDERBYsum(salary);查询s_emp表中各个部门员工的平均工资,工资总和及部门名称并按照工资总和排序：应用举例SQL>SELECTe.dept_id,max(d.name),avg(e.salary),sum(salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.idande.dept_id!=414GROUPBYdept_id5ORDERBYsum(salary);查询s_emp表中除41部门以外的部门员工的平均工资,工资总和及部门名称并按照工资总和排序：SQL>SELECTe.dept_id,max(d.name),avg(e.salary),sum(salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id5HAVINGe.dept_id!=416ORDERBYsum(salary);应用举例求平均工资高于1500的部门的工资总和，最高工资，最低工资：SQL>SELECTe.dept_id,max(d.name),avg(e.salary),sum(e.salary)2FROMs_empe,s_deptd3WHEREe.dept_id=d.id4GROUPBYdept_id5HAVINGavg(e.salary)>15006ORDERBYavg(e.salary);课堂练习求不以“VP”开头职位的，各个职位中工资总和大于5000的职位及工资总和，并按工资总和排序：SQL>SELECTtitle,SUM(salary)PAYROLL2FROMs_emp3WHEREtitleNOTLIKE'VP%'4GROUPBYtitle5HAVINGSUM(salary)>50006ORDERBYSUM(salary);小结SELECTcolumn,group_functionFROMtable[WHEREcondition][GROUPBYgroup_by_expression][HAVINGgroup_condition][ORDERBYcolumn];第六章子查询子查询查询s_emp表中工资最低的员工的姓名：SQL>SELECTmin（salary）2FROMs_empSQL>SELECTlast_name2FROMs_emp3WHEREsalary=最小工资（上一条的运行结果）SQL>SELECTlast_name2FROMs_emp3WHEREsalary=(SELECTmin(salary)FROMs_emp)子查询查询s_emp表中平均工资低于32部门的部门ID：SQL>SELECTavg（salary）2FROMs_emp3WHEREdept_id=32;SQL>SELECTdept_id,avg(salary)2FROMs_emp3GROUPBYdept_id4HAVINGavg(salary)<32部门的平均工资SQL>SELECTdept_id,avg(salary)2FROMs_emp3GROUPBYdept_id4HAVINGavg(salary)<(SELECTavg(salary)5FROMs_emp6WHEREdept_id=32)7ORDERBYDEPT_ID;子查询查询s_emp表中平均工资低于32部门的部门ID及名称：SQL>SELECTdept_id,avg(salary),name2FROMs_emp,s_dept3WHEREs_emp.dept_id=s_dept.id4GROUPBYdept_id,name5HAVINGavg(salary)<(SELECTavg(salary)6FROMs_emp7WHEREdept_id=32)8ORDERBYDEPT_ID;子查询举例SQL>SELECTlast_name,first_name,title2FROMs_emp3WHEREdept_in=4(SELECTID5FROMs_dept6WHEREname='Finance'7ORregion_id=2);ORA-01427:single-rowsubqueryreturnsmorethanonerow小结SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);第七章数据建模及数据库 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 本章目标了解系统开发的步骤数据关系的定义理解实体关系映射图（E-R图）系统开发步骤StrategyandAnalysisDesignBuildandDocumentTransitionProduction数据模型Modelofsysteminclient'smindEntitymodelofclient'smodelTablemodelofentitymodelTablesondiskServerEntityRelationshipModelingConceptsEntityAthingofsignificanceaboutwhichinformationneedstobeknownExamples:customers,salesrepresentatives,ordersAttributeSomethingthatdescribesorqualifiesanentityExamples:name,phone,identificationnumberRelationshipAnassociationbetweentwoentitiesExamples:ordersanditems,customersandsalesrepresentativesEntityRelationshipModelCreateanentityrelationshipdiagramfrombusinessspecificationsornarratives.Scenario"...Assignoneormorecustomerstoasalesrepresentative...""...Somesalesrepresentativesdonotyethaveassignedcustomers..."CUSTOMER#*id*nameophoneEMPLOYEE#*id*lastnameofirstnameassignedtothesalesreptoRelationshipTypesOne-to-oneHaveadegreeofoneandonlyoneinbothdirections.Arerare.Example:Husbandandwife.Many-to-oneHaveadegreeofoneormoreinonedirectionandadegreeofoneandonlyoneintheotherdirection.Areverycommon.Example:passengersandplane.Many-to-manyHaveadegreeofoneormoreinbothdirections.Areresolvedwithanintersectionentity.Example:Employeesandskills.数据库3范式数据库中的每一列都是不可再分的基本数据项，同一列中不能有多个值数据库表中不存在非关键字段对任何候选关键字段的部分依赖数据库表中不存在非关键字段对任何候选关键字段的传递引用约束类型PK——PrimaryKey唯一且非空FK——ForeignKey外键约束，值引用另一张表已经存在的数据UK——UniqueKey唯一且可为空NOTNULL非空约束举例IDLAST_NAMEFIRST_NAME...DEPT_ID...1VelasquezCarmen502NgaoLaDoris413NagayamaMidori314Quick-To-SeeMark105RopeburnAudry50IDNAMEREGION_ID10Finance131Sales141Operations150Administration1PrimaryKeyForeignKeyS_EMPTableS_DEPTTablePrimaryKey第八章创建表本章目标掌握创建表的语法Oracle的数据类型使用约束数据结构一个Oracle数据库包含下列几种数据结构：——Table存储数据——View从一个表或多个表的数句中得到的子集——Sequence生成主键值——Index提高查询性能建表语法CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][column_constraint],...[table_constraint]);Oracle的数据类型Char(size)定长字符型，字符长度不够自动在右边加空格符号Varchar2(size)可变长字符型，大小必须指定Number(m,n)数字型，可存放实数和整数Date日期类型Blob2进制大对象其最大大小为4GB此数据类型映射到Byte类型的Array。Clob2进制大对象其最大大小为4GB此数据类型映射到String命名规范必须以字母开头1-30个字符长度只允许包含A–Z,a–z,0–9,_,$,and#在一个数据库保证命名的唯一不能使用Oracle内部的关键字建表举例CREATETABLEMytest(idnumber，namevarchar2(32));CREATETABLEMytest(idnumberdeault11，namevarchar2(32));建表使用约束举例CREATETABLEMytest(idnumbercheck(id>10),namevarchar2(32));CREATETABLEMytest(idnumbercheck(id>10),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.CommandINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACKInsert插入语法INSERTINTOtable[(column[,column...])]VALUES(value[,value...]);插入举例CREATETABLEMytest(idnumberprimarykey,namevarchar2(32),birthDate);INSERTINTOmytest(id,name,brith)VALUES(1,’BluesWang’,’?’);更新语法UPDATEtableSETcolumn=value[,column=value][WHEREcondition];删除语法DELETE[FROM]table[WHEREcondition];DatabaseTransactionsContainoneofthefollowingstatements:DMLcommandsthatmakeuponeconsistentchangetothedataOneDDLcommandOneDCLcommandBeginwhenthefirstexecutableSQLcommandisexecuted.Endwithoneofthefollowingevents:COMMITorROLLBACKDDLorDCLcommandexecutes(automaticcommit)Errors,exit,orsystemcrash还原点SQL>UPDATE...SQL>SAVEPOINTupdate_done;Savepointcreated.SQL>INSERT...SQL>ROLLBACKTOupdate_done;Rollbackcomplete.ControllingTransactionsCOMMITROLLBACKINSERTUPDATEINSERTDELETESavepointMarkerASavepointMarkerBROLLBACKROLLBACKtoAROLLBACKtoB第十章修改表结构及添加约束本章目标增加或者修改列添加删除约束删除表删除表所有的数据添加列ALTERTABLEtableADD(columndatatype[DEFAULTexpr][NOTNULL][,columndatatype]...);添加列举例向下表mytest添加名为age，类型为number的一列：ALTERTABLEmytestADD(agenumber);再向下表mytest添加名为salary,类型为number(9,1)缺省值为8888的一列：ALTERTABLEmytestADD(salarynumber(9,1)default8888);删除列ALTERTABLEtableDROPcolumn[,column]...;删除列举例将表mytest的salary列删除：ALTERTABLEmytestdropcolumnsalary;修改列ALTERTABLEtableMODIFY(columndatatype[DEFAULTexpr][NOTNULL][,columndatatype]...);修改列举例将表mytest的age列由原来的number类型更改为number（4，2）类型：ALTERTABLEmytestmodify(agenumber(4,2));添加约束SQL>ALTERTABLEtable2ADD[CONSTRAINTconstraint]type(column);添加约束举例将表mytest的id列添加主键约束：ALTERTABLEmytestADDconstraintsmytest_pkprimarykey(id);将表child的p_id列添加外键约束：ALTERTABLEchildADDconstraintsc_fkforeignkey(p_id)referencesparent(id);删除约束举例SQL>ALTERTABLEchild2DROPCONSTRAINTc_fk;删除表及表的重命名DROPTABLEtable[CASCADECONSTRAINTS];SQL>RENAME表名TO新名;删除表数据SQL>TRUNCATETABLE表名;小结CommandCREATETABLEALTERTABLEDROPTABLERENAMETRUNCATEDescriptionCreatesatableandindicatedconstraints.Modifiestablestructuresandconstraints.Removestherowsandtablestructure.Changesthenameofatable,view,sequence,orsynonym.Removesallrowsfromatableandreleasesthestoragespace.第十一章创建序列本章目标掌握如何使用sequences创建sequences修改sequences删除sequences创建sequences语法CREATESEQUENCEname[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}]创建序列举例SQL>CREATESEQUENCEs_dept_id2INCREMENTBY13STARTWITH514MAXVALUE99999995NOCACHE6NOCYCLE;Sequencecreated.序列举例利用创建好的Sequence向mytest表中添加数据：insertintomytestvalues(s.nextval,'a')查看当前序列的值：SELECTs.currvalFROMdual修改sequences语法ALTERSEQUENCEname[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}]删除sequences语法DROPSEQUENCEname第十二章视图、索引本章目标掌握如何使用视图、索引创建视图、索引修改视图、索引删除视图、索引IDLAST_NAMEFIRST_NAMETITLEDEPT_ID---------------------------------------------------1VelasquezCarmenPresident502NgaoLaDorisVP,Operations413NagayamaMidoriVP,Sales314Quick-To-SeeMarkVP,Finance105RopeburnAudryVP,Administration506UrguhartMollyWarehouseManager417MenchuRobertaWarehouseManager428BiriBenWarehouseManager439CatchpoleAntoinetteWarehouseManager4410HavelMartaWarehouseManager4511MageeColinSalesRepresentative3112GiljumHenrySalesRepresentative3213SedeghiYasminSalesRepresentative3314NguyenMaiSalesRepresentative3415DumasAndreSalesRepresentative3516MaduroElenaStockClerk4117SmithGeorgeStockClerk4118NozakiAkiraStockClerk4219PatelVikramStockClerk4220NewmanChadStockClerk4321MarkarianAlexanderStockClerk4322ChangEddieStockClerk4423PatelRadhaStockClerk3424DancsBelaStockClerk4525SchwartzSylvieStockClerk45什么是视图IDLAST_NAMETITLE----------------------------10HavelWarehouseManager24DancsStockClerk25SchwartzStockClerkS_EMPTableEMPVU45View创建视图的语法CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias[,alias]...)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY]视图举例CREATEVIEWemp_41ASSelecte.first_name,d.name,e.salaryfroms_empe,s_deptdWheree.dept_id=e.idande.dept_id=41;索引一下情况下适合建立索引：1.查询多，增删少2.数据量大索引的结构NguSedeghiVelasquezGiljumNgaoIDLAST_NAME--------------1Velasquez2Ngao3Nagayama4Quick-To-See5Ropeburn6Urguhart7Menchu8Biri9Catchpole10Havel11Magee12Giljum13Sedeghi14Nguyen15Dumas16Maduro17Smith18Nozaki19Patel20Newman21Markarian22Chang23Patel24Dancs25SchwartzBiriGiljumMenchuNgaoNguyenSedeghiUrguhartVelasquezROWID索引的语法CREATEINDEXindexONtable(column[,column]...);索引举例SQL>CREATEINDEXs_emp_last_name_idx2ONs_emp(last_name);Indexcreated.演讲完毕，谢谢观看！