Oracle11g第二章.ppt

第二章,SQL语言,SQL语言简介,SQL（StructuredQueryLanguage）又称结构化查询语言，是一种在关系型数据库中定义和查询及操纵数据的标准语言，是用户和数据库之间进行交流的接口SQL是IBM公司的圣约瑟研究实验室为其关系数据库管理系统SYSTEMR开发的一种查询语言SQL的前身是SQUARE语言，研制于上世纪70年代，Oracle不久也推出商用的SQL语言。SQL语言结构简洁，功能强大，简单易学，所以自从推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle,Sybase,Informix,SQLserver这些大型的数据库管理系统，还是像VisualFoxporo,PowerBuilder这些微机上常用的数据库开发系统，都支持SQL语言作为查询语言。,SQL语言分类,数据定义语言(DDL)create,alter,drop数据操作语言(DML)select.insert,update,delete事务控制语言(TCL)commit,savepoint,rollback数据控制语言(DCL)grant,revoke,表的概述,表是最基本的数据库对象，对应于现实世界中的对象表是数据库存储数据的基本单元表按列进行定义，存储若干行数据，表中至少有一列表的列的类型由用户指定关系型数据库的所有操作都围绕表进行的,表和列名的定义规则,长度不能超过30字节必须以字母开头不能使用保留字,如Number,Table,Index等表名（列名）不区分大小写,但如果包括在“”内，则区分大小写在一个表中，列名不能重复(即列名是唯一的)表名在当前数据库必须唯一,Oracle的数据类型,Oracle的数据类型,Oracle的数据类型,Oracle的数据类型,数据定义语言DDL,创建表1.createtable语句的基本格式如下：createtable(列级完整性约束条件,列级完整性约束条件,);注意：建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由RDBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级上，也可以定义在表级上。,数据定义语言,表的约束1.非空约束（notnull）：该字段的值不能为空。语法格式：constraintnotnull2.唯一约束（unique）：该字段的值不能重复。语法格式：constraintunique(,)3.主键约束（primarykey）：唯一的确定表中的每一行数据语法格式：constraintprimarykey(,)4.检查约束（check）：检查约束指表中的某一个列或一组列取值应该满足检查约束指定的条件。语法格式：constraintcheck(),数据定义语言,表的约束5.缺省约束（default）：为字段提供默认值语法格式：constraintdefault6.外键约束（foreignkey）：外键是指引用另一个表中的某个列或某几个列、或本表中另一个列或几个列。语法格式：constraintforeignkey()references()ondeletecascade|setnull注意：ondelete/updatecascade表示修改或删除时做级联操作ondeletesetnull将所有相关记录的外部码字段值设置为NULL,数据定义语言,修改表1.增加列：增加列就是在表中增加一个新列。增加列是最常见的修改表结构的操作。语法格式：altertableadd;2.更新列语法格式：altertablemodify;3.删除列语法格式：altertabledropcolumn;,数据定义语言,修改表约束1.非空约束：altertablemodifyconstraintnotnull|null;2.唯一约束：altertableaddconstraintunique(,);3.主键约束：altertableaddconstraintprimarykey(,);,数据定义语言,修改表约束4.外键约束：altertableaddconstraintforeignkey()references()ondeletecascade|setnullonupdatecascade;5.检查约束：altertableaddconstraintcheck();6.缺省约束：altertablemodifydefault(),数据定义语言,修改表名ALTERTABLEoldTableNameRENAMETOnewTableName如果是当前用户库的表RENAMEoldTableNameTonewTableName修改列名ALTERTABLEtableNameRENAMECOLUMNoldColumnNameTOnewColumnName,数据定义语言,删除表使用droptable语句删除表语法格式：droptable注意：在删除表时，如果该表中的数据被另外表的外键引用，那么必须要先删除外键表，才能删除该表。,数据操作语言,Select语句：用于检索数据库的数据，它是所有SQL语句中语法最复杂、功能也是最强大的SQL语句。单表查询：单表查询是指仅涉及一个表的查询1.简单查询语句语法格式：selectdistinctfrom主要有以下查询：检索所有列、检索特定列、检索日期列、取消重复行、使用算术表达式、使用列别名、处理NULL、连接字符串,数据操作语言,2.使用where子句语法如下：selectdistinctfromwherecondition;主要有以下查询：在where条件中使用数字值、字符值、日期值。betweenand操作符、notbetweenand不匹配检索、(=,!=,=,!)、比较操作符、like操作符、in操作符、notin操作符、逻辑操作符(and、or、not)、查询null值。,数据操作语言,3.使用orderby子句语法如下：selectdistinctfromwherecondition(s)orderbyexprasc|desc;主要有以下查询：升序排序、降序排序、多列排序、非选择列进行排序、列别名排序、列位置编号排序,数据操作语言,4.数据分组：是通过使用groupby子句、分组函数以及havnig子句共同实现的。其中groupby子句用于指定要分组的列，分组函数则用于显示统计结果，而having子句则用于限制分组显示结果。1）分组函数max：该函数用于取得列或表达式的最大值，它适用于任何数据类型。min：该函数用于取得列或表达式的最小值，它适用于任何数据类型。avg：该函数用于取得列或表达式的平均值，它只适用于数字类型。sum：该函数用于取得列或表达式的总和，它只适用于数字类型。count：该函数用于取得总计行数。,数据操作语言,2）groupby和having语法格式：selectcolumn,group_functionfromwhereconditiongroupbygroupby_expressionhavinggroup_condition;例如：使用groupby进行单列分组：显示每个部门的平均工资和最高工资使用groupby进行多列分组：显示每个部门每种岗位的平均工资和最高工资使用having子句限制分组显示结果：显示平均工资低于2000的部门号、平均工资及最高工资,注意点,分组函数只能出现在选择列、having子句和orderby子句如果查询语句中包括groupby和having子句，及orderby子句，则orderby必须放到最后having子句，如果出现必须和groupby同时出现如果查询语句中包括查询列、表达式、分组函数，则查询列和表达式必须出现在groupby子句中当限制分组显示结果时，必须使用having子句，而不是在where子句中使用分组函数,数据操作语言,连接查询：连接查询是指基于两个或两个以上表或视图的查询。相等连接：是指使用相等比较符（=）指定连接条件的连接查询，该种连接查询主要用于检索主从表之间的相关数据。语法格式如下：selecttable1.column,table2.columnfromtable1,table2wheretable1.column=table2.column;例如：使用相等比较符执行主从查询显示所有雇员的名称、工资及其所在的部门名称使用AND指定其他查询条件显示部门10的部门名、雇员名及其工资,数据操作语言,2.不等连接：是指在连接条件中使用除相等比较符外的其他比较操作符的连接查询，并且不等连接主要用在查询不同表之间显示特定范围的信息。例如：显示所有雇员的名称、工资及其工资级别3.自连接：是指在同一张表之间的连接查询，它主要用在参照表上显示上下级关系或者层次关系。自参照表是指同一个表的不同列之间具有参照关系或主从关系。例如：显示BLAKE雇员的上级领导,数据操作语言,4.内连接:是使用比较运算符比较连接表要连接列中的值的连接，用于返回满足连接条件的记录。语法格式如下:selecttable1.column,table2.columnfromtable1innerjointable2ontable1.column=table2.column;在SQL-92标准中，可以在FROM子句或WHERE子句中指定内部连接。此时其语法可简单表示如下：selecttable1.column,table2.columnfromtable1,table2wheretable1.column=table2.column;举例：显示部门10的部门名及其雇员名,数据操作语言,5.外连接外连接则是内连接的扩展，它不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的记录。主要方式有：左外连接、右外连接、完全外连接语法格式如下：selecttable1.column,table2.columnfromtable1left/right/fulljointable2ontable1.column=table2.column;说明：leftjoin表示左外连接；rightjoin表示右外连接；fulljoin表示完全外连接；on子句用于指定连接条件,数据操作语言,左外连接通过leftouterjoin选项来实现，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的连接符左别表的其他行。语法格式如下：selecttable1.column,table2.columnfromtable1leftjointable2ontable1.column=table2.column;举例：显示部门10部门名、雇员名，以及其它部门名,数据操作语言,右外连接通过rightouterjoin选项来实现，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的连接符右别表的其他行。语法格式如下：selecttable1.column,table2.columnfromtable1rightjointable2ontable1.column=table2.column;举例：显示部门10部门名、雇员名，以及其它雇员名,数据操作语言,完全外连接通过fullouterjoin选项来实现，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的的其他行。语法格式如下：selecttable1.column,table2.columnfromtable1fulljointable2ontable1.column=table2.column;举例：显示部门10部门名、雇员名，以及其它部门名和雇员名,数据操作语言,嵌套查询（也叫子查询）：将一个查询块嵌套在另一个查询块的where子句中或having短语中的查询语句称为嵌套查询,也叫子查询。根据子查询返回结果的不同，子查询又被分为单行子查询、多行子查询、多列子查询和其它子查询。前三者的区别：单行子查询是指子查询语句只返回单列单行数据，多行子查询是指子查询语句返回单列多行数据，二者都是针对单列而言的。而多列子查询则是指子查询语句返回多列数据的子查询语句。,数据操作语言,单行子查询只返回一行数据的子查询语句.当在where子句中引用单行子查询时,可以使用单行比较符(如:=,=,)举例:查询显示SCOTT同事的姓名、工资和部门号信息,数据操作语言,多行子查询多行子查询是指返回多行数据的子查询语句.当在WHERE子句中使用多行子查询时,必须要使用多行比较符(如in,all,any),数据操作语言,多列子查询当多列子查询返回单行数据时，在WHERE子句可以使用单行比较符；当多列子查询返回多行数据时，在WHERE子句中可以使用多行比较符（IN,ANY.ALL）举例：多列子查询，返回单行数据查询与SMITH部门和岗位相同的所有雇员,数据操作语言,多列子查询使用子查询比较多个列的数据时，可以使用成对比较，也可使用非成对比较成对比较:要求多个列的数据必须同时匹配例子：查询工资和补助与部门30雇员的都相同的雇员信息2.非成对比较:多个列的数据是独立的匹配例子：查询工资匹配部门30，补助也匹配部门30的雇员信息,数据操作语言,其他子查询:相关子查询是指需要引用主查询某些表或某些列的子查询语句.父查询可能是一条SELECT、UPDATE、DELETE语句。相关子查询是通过Exists谓词来实现的。例子：查询在“NEWYORK”的所有雇员当在from子句中使用子查询时，必须给子查询指定别名例子：查询高于部门平均工资的雇员信息,EXISTS与IN的区别,EXISTS与IN的区别：exists检查是否有结果，判断是否有记录，返回的是一个布尔型值。in是对结果值进行比较，判断一个字段是否存在于几个值的范围中。从查询效率来说，in适合内外表数据都很大的情况，exists适合外表结果集很小的情况。几种情况的分析：a、当只显示一个表的数据，关系条件只一个，使用in更快select*fromAwhereidin(selectidfromB)b、当只显示一个表的数据，关系条件不只一个，使用existsselect*fromAwhereexists(select1fromBwhereid=A.idandcol1=A.col1)c、当显示两个表的数据时，使用in，exists都不合适，要使用连接select*fromAleftjoinBonid=A.id,数据操作语言,insert：当要给表增加数据时，可以使用insert语句。1.插入单行数据：是使用insertvalues语句来完成的，其语法格式如下：insertinto(column1,column2,)values(value1,value2,);例:不使用列插入单行数据使用列插入单行数据使用特定格式插入日期值使用default提供数据,2.使用子查询插入数据：其语法格式如下：insertinto(column1,column2,.)subQuery;例子:,数据操作语言,update：当要更新表中行的数据时，可以使用update语句。注意事项：1.如果要更新数字列，则可以直接提供数字值；如果要