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

OracleSQL 第一章 SELECT查询 本章目标 写一条SELECT查询语句在查询中使用表达式 运算符对空值的处理对查询字段起别名查询字段的连接 SELECT查询基本语法 SELECTFROM 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 Velasquez250030100 SQL SELECTlast name salary 12 salary 100 2FROMs emp Velasquez250031200 列别名 请查询出s emp表中所有的员工的姓名 SQL SELECTfirname name last nameFROMs emp SQL 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函数及字符串连接符重复行的处理Distinct SELECT DISTINCT column alias FROMtable 引言 第二章条件查询 本章目标 WHERE条件查询在查询中使用表达式 运算符使用LIKE BETWEEN IN进行模糊查询 第一章内容回顾 对员工表中信息进行查询 具体要求如下 1 查询s emp表要求输出员工姓名 firs name last name 和实际工资 基本工资 提成 查询基本语法 SELECTFROM WHERE 请查询出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 50 WHERE条件查询 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 41ORDERBYsalaryDESC SQL 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函数 单行函数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 SQL 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 55 SQL SELECTTRUNC 124 16666 2 trunc1 trunc 124 16666 2 FROMdual TRUNC1TRUNC 124 16666 2 100124 16 Round 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采用当地货币符号 打印一个小数点 千位计算法显示 日期格式 To 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 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 转换函数嵌套举例 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 30Finance131Sales132Sales243Operations350Administration1 S REGIONTableIDNAME 1NorthAmerica2SouthAmerica3Africa MiddleEast4Asia5Europe 简单关联查询的语法 查询员工表中last name为 Biri 的员工的last name与部门名称查询出来 SELECTtable column table columnFROMtable1 table2WHEREtable1 column1 table2 column2 SQL 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 自连接 查询员工表中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 emp SQL 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 第七章数据建模及数据库设计 本章目标 了解系统开发的步骤数据关系的定义理解实体关系映射图 E R图 系统开发步骤 数据模型 Modelofsysteminclient smind Entitymodelofclient smodel 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范式 数据库中的每一列都是不可再分的基本数据项 同一列中不能有多个值数据库表中不存在非关键字段对任何候选关键字段的部分依赖数据库表中不存在非关键字段对任何候选关键字段的传递引用 约束类型 PK PrimaryKey唯一且非空FK ForeignKey外键约束 值引用另一张表已经存在的数据UK UniqueKey唯一且可为空NOTNULL非空 约束举例 IDLAST NAMEFIRST NAME DEPT ID 1VelasquezCarmen502NgaoLaDoris413NagayamaMidori314Quick To SeeMark105RopeburnAudry50 IDNAMEREGION ID10Finance131Sales141Operations150Administration1 PrimaryKey ForeignKey S EMPTable S DEPTTable PrimaryKey 第八章创建表 本章目标 掌握创建表的语法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 CommandINSERTUPDATEDELETECOMMITSAVEPOINTROLLBACK Insert插入语法 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 DatabaseTransactions Containoneofthefollowingstatements DMLcommandsthatmakeuponeconsistentchangetothedataOneDDLcommandOneDCLcommandBeginwhenthefirstexecutableSQLcommandisexecuted Endwithoneofthefollowingevents COMMITorROLLBACKDDLorDCLcommandexecutes automaticcommit Errors exit orsystemcrash 还原点 SQL UPDATE SQL SAVEPOINTupdate done Savepointcreated SQL INSERT SQL ROLLBACKTOupdate done Rollbackcomplete ControllingTransactions COMMIT ROLLBACK INSERT UPDATE INSERT DELETE SavepointMarkerA SavepointMarkerB ROLLBACK ROLLBACKtoA ROLLBACKtoB 第十章修改表结构及添加约束 本章目标 增加或者修改列添加删除约束删除表删除表所有的数据 添加列 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表名 小结 CommandCREATETABLEALTERTABLEDROPTABLERENAMETRUNCATE DescriptionCreatesatableandindicatedconstraints 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