Oracle11g第二章.ppt

《Oracle11g第二章.ppt》由会员分享，可在线阅读，更多相关《Oracle11g第二章.ppt（44页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、第第第第二章二章二章二章SQLSQL语言语言语言语言SQLSQL语言简介语言简介SQL（Structured Query Language）又称结构化查询语言，又称结构化查询语言，是一种在关系型数据库中定义和查询及操纵数据的标准语言，是一种在关系型数据库中定义和查询及操纵数据的标准语言，是用户和数据库之间进行交流的接口是用户和数据库之间进行交流的接口SQL是是IBM公司的圣约瑟研究实验室为其关系数据库管理系统公司的圣约瑟研究实验室为其关系数据库管理系统SYSTEMR开发的一种查询语言开发的一种查询语言SQL的前身是的前身是SQUARE语言，研制于上世纪语言，研制于上世纪70年代，年代，Orac

2、le不不久也推出商用的久也推出商用的SQL语言。语言。SQL语言结构简洁，功能强大，简单易学，所以自从推出以来，语言结构简洁，功能强大，简单易学，所以自从推出以来，SQL语言得到了广泛的应用。如今无论是像语言得到了广泛的应用。如今无论是像Oracle,Sybase,Informix,SQL server这些大型的数据库管理系统，还这些大型的数据库管理系统，还是像是像Visual Foxporo,PowerBuilder这些微机上常用的数据库这些微机上常用的数据库开发系统，都支持开发系统，都支持SQL语言作为查询语言。语言作为查询语言。SQLSQL语言语言分类分类数据定义语言数据定义语言(DDL

3、)create,alter,drop数据操作语言数据操作语言(DML)select.insert,update,delete事务控制语言事务控制语言(TCL)commit,savepoint,rollback数据控制语言数据控制语言(DCL)grant,revoke表的概述表的概述表是最基本的数据库对象，对应于现实世界中的对象表是最基本的数据库对象，对应于现实世界中的对象表是数据库存储数据的基本单元表是数据库存储数据的基本单元表按列进行定义，存储若干行数据，表中至少有一列表按列进行定义，存储若干行数据，表中至少有一列表的列的类型由用户指定表的列的类型由用户指定关系型数据库的所有操作都围绕表进行

4、的关系型数据库的所有操作都围绕表进行的表和列名的定义规则表和列名的定义规则长度不能超过长度不能超过30字节字节必须以字母开头必须以字母开头不能使用保留字不能使用保留字,如如Number,Table,Index等等表名（列名）不区分大小写表名（列名）不区分大小写,但如果包括在但如果包括在“”内，内，则区分大小写则区分大小写在一个表中，列名不能重复在一个表中，列名不能重复(即列名是唯一的即列名是唯一的)表名在当前数据库必须唯一表名在当前数据库必须唯一OracleOracle的数据类型的数据类型列类型列类型说明说明CHAR(SIZE)char数据类型是一种有固定长度和最大长度的字符串。存储在数据类型

5、为char字段中的数据将以空格的形式补到最大长度。长度定义在1-2000字节之间。VARCHAR2()varchar2数据类型是一种可变长度的、有最大长度的字母数字型数据。varchar2类型的字段长度可以达到4000字节，varchar2类型的变量长度可以达到32676字节。一个空的varchar2(2000)字段和一个空的varchar2(2)字段所占用的空间是一样的。NCHAR()/NVARCHAR2()nchar()和 nvarchar2()数据类型分别与char()和 varchar2()类型是相同的，只不过它们用来存储NLS（National Language Support）数据

6、OracleOracle的数据类型的数据类型列类型列类型说明说明CLOB存储大量的单字节字符数据和多字节字符数据。例：非结构化的XML文档存储的最大容量为4GNCLOB存储可变长度的Unicode字符集字符数据存储的最大容量为4GBLOB存储较大的二进制对象。例：图形，视频，音频等BFILEbfile:文件定位器；指向位于服务器文件系统是的二进制文件(存储一个文件路径)OracleOracle的数据类型的数据类型列类型列类型说明说明NUMBER(p,s)存储数值数据类型，如浮点型、整数型、分数、双精度等。其中p为精度,表示数字的总位数;s为范围，表示小数点右边的位数,它在-84至127之间。D

7、ATE存储表的日期和时间数据，使用7个字节固定长度，每个字节分别存储世纪，年，月，日，小时，分和秒；值从公元前4712年1月1日到公元9999年12月31日。Oracle中的sysdate函数功能是返回当前的日期和时间。TIMESTAMP存储日期的年，月，日以及时间的小时，分和秒值。其中秒值精确到小数点后6位，同时包含时区信息。Oracle中的systimestamp函数功能是返回当前日期，时间和时区。OracleOracle的数据类型的数据类型列类型列类型说明说明LONG存储可变长度的字符数据,最多存储2GB。RAW存储基于字节的数据。最多存储2000个字节，使用需指定大小。raw数据类型可

8、以建立索引。LONG RAW存储可变长度的二进制数据。最多能存储2GB。不能索引。数据定义语言数据定义语言DDLDDL创建表创建表 1.create table语句的基本格式如下：create table(列级完整性约束条件,列级完整性约束条件,);注意：建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由RDBMS自动检查该操作是否违背这些完整性约束条件。如果完完整性约束条件涉及到该表的多个属性列，则必须定义在表级上整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否否则既可以定义在列级上，也可以定义在表级上则既可以定义在

9、列级上，也可以定义在表级上。数据定义语言数据定义语言表的约束表的约束1.非空约束（not null）：该字段的值不能为空。语法格式：constraint not null 2.唯一约束（unique）：该字段的值不能重复。语法格式：constraint unique(,)3.主键约束（primary key）：唯一的确定表中的每一行数据 语法格式：constraint primary key(,)4.检查约束（check）：检查约束指表中的某一个列或一组列取值应该满足检查约束指定的条件。语法格式：constraint check()数据定义语言数据定义语言表的约束表的约束5.缺省约束（defa

10、ult）：为字段提供默认值语法格式：constraint default 6.外键约束（foreign key）：外键是指引用另一个表中的某个列或某几个列、或本表中另一个列或几个列。语法格式：constraint foreign key()references()on delete cascade|set null注意：on delete/update cascade表示修改或删除时做级联操作 on delete set null 将所有相关记录的外部码字段值设置为将所有相关记录的外部码字段值设置为NULL 数据定义语言数据定义语言修改表修改表1.增加列：增加列就是在表中增加一个新列。增加列是

11、最常见的修改表结构的操作。语法格式：alter table add ;2.更新列语法格式：alter table modify ;3.删除列 语法格式：alter table drop column;数据定义语言数据定义语言修改表约束修改表约束1.非空约束：alter table modify constraint not null|null;2.唯一约束：alter table add constraint unique(,);3.主键约束：alter table add constraint primary key(,);数据定义语言数据定义语言修改表约束修改表约束4.外键约束：alter

12、 table add constraint foreign key()references()on delete cascade|set nullon update cascade;5.检查约束：alter table add constraint check();6.缺省约束：alter table modify default()数据定义语言数据定义语言修改表名修改表名ALTER TABLE oldTableName RENAME TO newTableName如果是当前用户库的表如果是当前用户库的表RENAME oldTableName To newTableName修改列名修改列名AL

13、TER TABLE tableName RENAME COLUMN oldColumnName TO newColumnName数据定义语言数据定义语言删除表删除表使用drop table语句删除表语法格式：drop table 注意：在删除表时，如果该表中的数据被另外表的外键引用，那么必须要先删除外键表，才能删除该表。数据操作语言数据操作语言Select语句：用于检索数据库的数据，它是所有语句：用于检索数据库的数据，它是所有SQL语句中语法最复杂、功能也是最强大的语句中语法最复杂、功能也是最强大的SQL语句。语句。n单表查询单表查询：单表查询是指仅涉及一个表的查询 1.简单查询语句简单查询语

14、句语法格式：select distinct from 主要有以下查询：检索所有列、检索特定列、检索日期列、取消重复行、使用算术表达式、使用列别名、处理NULL、连接字符串 数据操作语言数据操作语言2.使用使用where子句子句语法如下：select distinct from where condition;主要有以下查询：在where条件中使用数字值、字符值、日期值。betweenand操作符、not betweenand不匹配检索、(=,!=,=,!,)、比较操作符、like操作符、in操作符、not in 操作符、逻辑操作符(and、or、not)、查询null值。数据操作语言数据操作语

15、言3.使用使用order by子句子句语法如下：select distinct from where condition(s)order by expr asc|desc;主要有以下查询：升序排序、降序排序、多列排序、非选择列进行排序、列别名排序 、列位置编号排序 数据操作语言数据操作语言4.数据分组数据分组：是通过使用：是通过使用group by子句、分组函数以及子句、分组函数以及havnig子句子句共同实现的。其中共同实现的。其中group by子句用于指定要分组的列，分组函子句用于指定要分组的列，分组函数则用于显示统计结果，而数则用于显示统计结果，而having子句则用于限制分组显示结子

16、句则用于限制分组显示结果。果。1）分组函数max：该函数用于取得列或表达式的最大值，它适用于任何数据类型。min：该函数用于取得列或表达式的最小值，它适用于任何数据类型。avg：该函数用于取得列或表达式的平均值，它只适用于数字类型。sum：该函数用于取得列或表达式的总和，它只适用于数字类型。count：该函数用于取得总计行数。数据操作语言数据操作语言2）group by和having 语法格式：select column,group_function from where condition group by groupby_expression having group_condition;

17、例如：例如：使用使用group by进行单列分组进行单列分组：显示每个部门的平均工资和最高：显示每个部门的平均工资和最高工资工资使用使用group by进行多列分组：显示每个部门每种岗位的平均工进行多列分组：显示每个部门每种岗位的平均工资和最高工资资和最高工资 使用使用having子句限制分组显示结果子句限制分组显示结果：显示平均工资低于：显示平均工资低于2000的的部门号、平均工资及最高工资部门号、平均工资及最高工资注意点注意点分组函数只能出现在选择列、分组函数只能出现在选择列、having子句和子句和order by子句子句如果查询语句中包括如果查询语句中包括group by 和和havi

18、ng子句，及子句，及order by 子子句，则句，则order by 必须放到最后必须放到最后having子句，如果出现必须和子句，如果出现必须和group by 同时出现同时出现如果查询语句中包括查询列、表达式、分组函数，则查询列和如果查询语句中包括查询列、表达式、分组函数，则查询列和表达式必须出现在表达式必须出现在group by 子句中子句中当限制分组显示结果时，必须使用当限制分组显示结果时，必须使用having子句，而不是在子句，而不是在where子句中使用分组函数子句中使用分组函数数据操作语言数据操作语言连接查询：连接查询是指基于两个或两个以上表或视图的连接查询：连接查询是指基于两

19、个或两个以上表或视图的查询。查询。1.相等连接相等连接：是指使用相等比较符（=）指定连接条件的连接查询，该种连接查询主要用于检索主从表之间的相关数据。语法格式如下：select table1.column,table2.column from table1,table2 where table1.column=table2.column;例如：使用相等比较符执行主从查询显示所有雇员的名称、工资及其所在的部门名称 使用AND指定其他查询条件显示部门10的部门名、雇员名及其工资 数据操作语言数据操作语言2.不等连接不等连接：是指在连接条件中使用除相等比较符外的其他比较操作符的连接查询，并且不等连接

20、主要用在查询不同表之间显示特定范围的信息。例如：显示所有雇员的名称、工资及其工资级别 3.自连接自连接：是指在同一张表之间的连接查询，它主要用在参照表上显示上下级关系或者层次关系。自参照表是指同一个表的不同列之间具有参照关系或主从关系。例如：显示BLAKE雇员的上级领导 数据操作语言数据操作语言4.内连接内连接:是使用比较运算符比较是使用比较运算符比较连接表连接表要连接列中的值的连接要连接列中的值的连接，用于返，用于返回满足连接条件的记录回满足连接条件的记录。语法格式如下语法格式如下:select table1.column,table2.column from table1 inner jo

21、in table2 on table1.column=table2.column;在在SQL-92 标准中，可以在标准中，可以在FROM子句或子句或WHERE子句中指定内部连接。子句中指定内部连接。此时其语法可简单表示如下：此时其语法可简单表示如下：select table1.column,table2.column from table1,table2 where table1.column=table2.column;举例：显示部门举例：显示部门10的部门名及其雇员名的部门名及其雇员名数据操作语言数据操作语言5.外连接外连接 外连接则是内连接的扩展，它不仅会返回满足连接条件的所有记录，而且

22、还会返回不满足连接条件的记录。主要方式有：左外连接左外连接、右外、右外连接连接、完全外连接、完全外连接 语法格式如下：语法格式如下：select table1.column,table2.column from table1 left/right/full join table2 on table1.column=table2.column;说明：说明：left join表示左外连接；表示左外连接；right join表示右外连接；表示右外连接；full join表示完表示完全外连接；全外连接；on子句用于指定连接条件子句用于指定连接条件数据操作语言数据操作语言左外连接左外连接 通过通过lef

23、t outer join选项来实现，不仅会返回满足连接条件的所有记选项来实现，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的连接符录，还会返回不满足连接条件的连接符左别表左别表的其他行。的其他行。语法格式如下：语法格式如下：select table1.column,table2.column from table1 left join table2 on table1.column=table2.column;举例：显示部门举例：显示部门10部门名、雇员名，以及其它部门名部门名、雇员名，以及其它部门名数据操作语言数据操作语言右外连接右外连接 通过通过right outer join

24、选项来实现，不仅会返回满足连接条件的所有选项来实现，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的连接符右别表的其他行。记录，还会返回不满足连接条件的连接符右别表的其他行。语法格式如下：语法格式如下：select table1.column,table2.column from table1 right join table2 on table1.column=table2.column;举例：显示部门举例：显示部门10部门名、雇员名，以及其它雇员名部门名、雇员名，以及其它雇员名数据操作语言数据操作语言完全外连接完全外连接 通过通过full outer join选项来实现，不仅会返

25、回满足连接条件的所有记选项来实现，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的的其他行。录，还会返回不满足连接条件的的其他行。语法格式如下：语法格式如下：select table1.column,table2.column from table1 full join table2 on table1.column=table2.column;举例：显示部门举例：显示部门10部门名、雇员名，以及其它部门名和雇员名部门名、雇员名，以及其它部门名和雇员名数据操作语言数据操作语言嵌套查询（也叫子查询）：嵌套查询（也叫子查询）：将一个查询块嵌套在另一个查询块的将一个查询块嵌套在另一个查询块

26、的where子句中或子句中或having短语中的查询语句称为嵌套查询短语中的查询语句称为嵌套查询,也叫子查也叫子查询。询。根据子查询返回结果的不同，子查询又被分为单行子查询、多行子查询、多列子查询和其它子查询。前三者的区别前三者的区别：单行子查询是指子查询语句只返回单列单行数据，多行子查询是指子查询语句返回单列多行数据，二者都是针对单列而言的。而多列子查询则是指子查询语句返回多列数据的子查询语句。数据操作语言数据操作语言单行子查询单行子查询 只返回一行数据的子查询语句只返回一行数据的子查询语句.当在当在where子句中引用单行子查子句中引用单行子查询时询时,可以使用单行比较符可以使用单行比较符

27、(如如:=,=,)举例:查询显示SCOTT同事的姓名、工资和部门号信息数据操作语言数据操作语言多行子查询多行子查询 多行子查询是指返回多行数据的子查询语句多行子查询是指返回多行数据的子查询语句.当在当在WHERE子句中使子句中使用多行子查询时用多行子查询时,必须要使用多行比较符必须要使用多行比较符(如如 in,all,any)运算符运算符含义含义举例举例in匹配于子查询结果的任意一个值即可查询匹配匹配于部门10岗位岗位的雇员名、工资、部门号all必须要符合子查询结果的所有值查询高于部门30的所有雇员工资的雇员名、工资和部门号any只要符合子查询结果的任意一个值即可查询高于部门30的任一雇员工资

28、的雇员名、工资和部门号数据操作语言数据操作语言多列子查询多列子查询 当多列子查询返回单行数据时，在当多列子查询返回单行数据时，在WHERE 子句可以使用单行比较符；子句可以使用单行比较符；当多列子查询返回多行数据时，在当多列子查询返回多行数据时，在WHERE 子句中可以使用多行比较子句中可以使用多行比较符（符（IN,ANY.ALL）举例：举例：1.多列子查询，返回单行数据多列子查询，返回单行数据 查询与查询与SMITH部门和岗位相同部门和岗位相同的所有雇员的所有雇员数据操作语言数据操作语言多列子查询多列子查询 使用子查询比较多个列的数据时，可以使用成对比较，也可使用非成使用子查询比较多个列的数

29、据时，可以使用成对比较，也可使用非成对比较对比较1.成对比较成对比较:要求多个列的数据必须同时匹配要求多个列的数据必须同时匹配 例子：查询工资和补助与部门例子：查询工资和补助与部门30雇员的都相同的雇员信息雇员的都相同的雇员信息2.非成对比较非成对比较:多个列的数据是独立的匹配多个列的数据是独立的匹配 例子：查询工资匹配部门例子：查询工资匹配部门30，补助也匹配部门，补助也匹配部门30的雇员信息的雇员信息数据操作语言数据操作语言其他子查询其他子查询:1.相关子查询是指需要引用主查询某些表或某些列的子查询语句相关子查询是指需要引用主查询某些表或某些列的子查询语句.父查询父查询可能是一条可能是一条

30、SELECT、UPDATE、DELETE语句。语句。相关子查询是通过相关子查询是通过Exists谓词来实现的。谓词来实现的。例子：查询在例子：查询在“NEW YORK”的所有雇员的所有雇员2.当在当在from子句中使用子查询时，必须给子查询指定别名子句中使用子查询时，必须给子查询指定别名 例子：查询例子：查询高于高于部门部门平均工资平均工资的雇员信息的雇员信息EXISTSEXISTS与与ININ的区别的区别EXISTS与与IN的区别：的区别：exists检查是否有结果，判断是否有记录，返回的是一个布尔型值。in是对结果值进行比较，判断一个字段是否存在于几个值的范围中。从查询效率来说，in适合内

31、外表数据都很大的情况，exists适合外表结果集很小的情况。几种情况的分析：a、当只显示一个表的数据，关系条件只一个，使用in更快 select*from A where id in(select id from B)b、当只显示一个表的数据，关系条件不只一个，使用exists select*from A where exists(select 1 from B where id=A.id and col1=A.col1)c、当显示两个表的数据时，使用in，exists都不合适，要使用连接 select*from A left join B on id=A.id 数据操作语言数据操作语言ins

32、ert：当要给表增加数据时，可以使用：当要给表增加数据时，可以使用insert语句。语句。1.插入单行数据：是使用insertvalues语句来完成的，其语法格式如下：insert into(column1,column2,)values(value1,value2,);例:1.不使用列插入单行数据2.使用列插入单行数据3.使用特定格式插入日期值4.使用default提供数据2.使用子查询插入数据：其语法格式如下：使用子查询插入数据：其语法格式如下：insert into(column1,column2,.)subQuery;例子例子:数据操作语言数据操作语言update：当要更新表中行的数据

33、时，可以使用：当要更新表中行的数据时，可以使用update语句。语句。注意事项：1.如果要更新数字列，则可以直接提供数字值；如果要更新字符列或日期列，则数据必须用单引号引住。2.当更新数据时，数据必须要满足约束规则。3.当更新数据时，数据必须要与列的数据类型匹配。数据操作语言数据操作语言1.使用表达式更新数据使用表达式更新数据语法格式如下：语法格式如下：update table set column1=value1,column2=value2,where;例例;1.更新一列数据更新一列数据(SCOTT的工资涨到的工资涨到3000)2.更新多列数据更新多列数据(同时更新工资和补助同时更新工资和

34、补助,各涨各涨50%)3.SCOTT更新出生日期更新出生日期4.更新部门默认地址为更新部门默认地址为NEW YORK数据操作语言数据操作语言2.使用子查询更新数据使用子查询更新数据更新相关联的数据时，使用子查询效率更高。更新相关联的数据时，使用子查询效率更高。例例:使雇员使雇员SCOTT的岗位、工资、补助与雇员的岗位、工资、补助与雇员SMITH完全相同完全相同数据操作语言数据操作语言delete：当要删除表中某行的数据时，可以使用：当要删除表中某行的数据时，可以使用delete语句。语句。语法格式如下：delete from table where;table用于指定表名；view用于指定视图

35、名；condition用于指定条件子句。当使用delete语句删除数据时，如果不指定where条件子句，那么会删除表或视图的所有行。删除主表数据的注意事项删除主表数据的注意事项：当删除主表数据时，必须确保从表不存在相关记录，否则会显示错误信息。数据操作语言数据操作语言Truncate 删除数据删除数据语法如下:TRUNCATE TABLE tableName RUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。1.TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。2.TRUNCATE不能触发任何DELETE触发器。3.不能授予任何人清空他人的表的权限。4.当表被清空后表和表的索引将重新设置成初始大小，而delete则不能