Oracle11g第二章.ppt

第二章,SQL语言,SQL语言简介,SQL（Structured Query Language） 又称结构化查询语言，是一种在关系型数据库中定义和查询及操纵数据的标准语言，是用户和数据库之间进行交流的接口 SQL是IBM公司的圣约瑟研究实验室为其关系数据库管理系统SYSTEMR开发的一种查询语言 SQL的前身是SQUARE语言，研制于上世纪70年代，Oracle不久也推出商用的SQL语言。 SQL语言结构简洁，功能强大，简单易学，所以自从推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle ,Sybase,Informix,SQL server这些大型的数据库管理系统，还是像Visual Foxporo,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.create table语句的基本格式如下： create table ( 列级完整性约束条件, 列级完整性约束条件, ); 注意：建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由RDBMS自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级上，也可以定义在表级上 。,数据定义语言,表的约束 1.非空约束（not null）：该字段的值不能为空。 语法格式：constraint not null 2.唯一约束（unique）：该字段的值不能重复。 语法格式：constraint unique(,) 3.主键约束（primary key）：唯一的确定表中的每一行数据 语法格式：constraint primary key(,) 4.检查约束（check）：检查约束指表中的某一个列或一组列取 值应该满足检查约束指定的条件。 语法格式：constraint check(),数据定义语言,表的约束 5.缺省约束（default）：为字段提供默认值 语法格式：constraint default 6.外键约束（foreign key）：外键是指引用另一个表中的某个 列或某几个列、或本表中另一个列或几个列。 语法格式： constraint foreign key() references () on delete cascade|set null 注意： on delete/ update cascade表示修改或删除时做级联操作 on delete set null 将所有相关记录的外部码字段值设置为NULL,数据定义语言,修改表 1.增加列：增加列就是在表中增加一个新列。增加列是最常见的 修改表结构的操作。 语法格式：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 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 修改列名 ALTER TABLE tableName RENAME COLUMN oldColumnName TO newColumnName,数据定义语言,删除表 使用drop table语句删除表 语法格式： drop table 注意：在删除表时，如果该表中的数据被另外表的外键引用，那么必须要先删除外键表，才能删除该表。,数据操作语言,Select语句：用于检索数据库的数据，它是所有SQL 语句中语法最复杂、功能也是最强大的SQL语句。 单表查询：单表查询是指仅涉及一个表的查询 1.简单查询语句 语法格式：select distinct from 主要有以下查询： 检索所有列、检索特定列 、检索日期列 、取消重复行、 使用算术表达式、使用列别名、处理NULL 、连接字符串,数据操作语言,2.使用where子句 语法如下： select distinct from where condition; 主要有以下查询： 在where条件中使用数字值、字符值、日期值。betweenand操作符 、not betweenand不匹配检索、(=,!=,=,!)、比较操作符、like操作符、in操作符、not in 操作符、逻辑操作符(and、or、not)、查询null值。,数据操作语言,3. 使用order by子句 语法如下： select distinct from where condition(s) order by expr asc|desc; 主要有以下查询： 升序排序 、降序排序 、多列排序、非选择列进行排序、 列别名排序 、列位置编号排序,数据操作语言,4.数据分组 ：是通过使用group by子句、分组函数以及havnig子句共同实现的。其中group by子句用于指定要分组的列，分组函数则用于显示统计结果，而having子句则用于限制分组显示结果。 1）分组函数 max：该函数用于取得列或表达式的最大值，它适用于任何数据类型。 min：该函数用于取得列或表达式的最小值，它适用于任何数据类型。 avg：该函数用于取得列或表达式的平均值，它只适用于数字类型。 sum：该函数用于取得列或表达式的总和，它只适用于数字类型。 count：该函数用于取得总计行数。,数据操作语言,2）group by和having 语法格式：select column,group_function from where condition group by groupby_expression having group_condition; 例如： 使用group by进行单列分组 ：显示每个部门的平均工资和最高工资 使用group by进行多列分组：显示每个部门每种岗位的平均工资和最高工资 使用having子句限制分组显示结果 ：显示平均工资低于2000的部门号、平均工资及最高工资,注意点,分组函数只能出现在选择列、having子句和order by子句 如果查询语句中包括group by 和having子句，及order by 子句，则order by 必须放到最后 having子句，如果出现必须和group by 同时出现 如果查询语句中包括查询列、表达式、分组函数，则查询列和表达式必须出现在group by 子句中 当限制分组显示结果时，必须使用having子句，而不是在where子句中使用分组函数,数据操作语言,连接查询：连接查询是指基于两个或两个以上表或视图的查询。 相等连接：是指使用相等比较符（=）指定连接条件的连接查询，该种连接查询主要用于检索主从表之间的相关数据。 语法格式如下： select table1.column,table2.column from table1,table2 where table1.column=table2.column; 例如： 使用相等比较符执行主从查询 显示所有雇员的名称、工资及其所在的部门名称 使用AND指定其他查询条件 显示部门10的部门名、雇员名及其工资,数据操作语言,2.不等连接：是指在连接条件中使用除相等比较符外的其他比较操作符的 连接查询，并且不等连接主要用在查询不同表之间显示特定范围的信息。 例如： 显示所有雇员的名称、工资及其工资级别 3.自连接：是指在同一张表之间的连接查询，它主要用在参照 表上显示上下级关系或者层次关系。自参照表是指同一个表的不同列 之间具有参照关系或主从关系。 例如： 显示BLAKE雇员的上级领导,数据操作语言,4.内连接:是使用比较运算符比较连接表要连接列中的值的连接，用于返回满足连接条件的记录。 语法格式如下: select table1.column,table2.column from table1 inner join 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.外连接 外连接则是内连接的扩展，它不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的记录。主要方式有：左外连接 、右外连接 、完全外连接 语法格式如下： select table1.column,table2.column from table1 left / right / full join table2 on table1.column=table2.column; 说明：left join表示左外连接；right join表示右外连接；full join表示完全外连接；on子句用于指定连接条件,数据操作语言,左外连接 通过left outer join选项来实现，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的连接符左别表的其他行。 语法格式如下： select table1.column,table2.column from table1 left join table2 on table1.column=table2.column; 举例：显示部门10部门名、雇员名，以及其它部门名,数据操作语言,右外连接 通过right outer join选项来实现，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的连接符右别表的其他行。 语法格式如下： select table1.column,table2.column from table1 right join table2 on table1.column=table2.column; 举例：显示部门10部门名、雇员名，以及其它雇员名,数据操作语言,完全外连接 通过full outer join选项来实现，不仅会返回满足连接条件的所有记录，还会返回不满足连接条件的的其他行。 语法格式如下： select table1.column,table2.column from table1 full join table2 on table1.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谓词来实现的。 例子：查询在“NEW YORK”的所有雇员 当在from子句中使用子查询时，必须给子查询指定别名 例子：查询高于部门平均工资的雇员信息,EXISTS与IN的区别,EXISTS与IN的区别： exists检查是否有结果，判断是否有记录，返回的是一个布尔型值。in是对结果值进行比较，判断一个字段是否存在于几个值的范围中。 从查询效率来说，in适合内外表数据都很大的情况，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,数据操作语言,insert：当要给表增加数据时，可以使用insert语句。 1.插入单行数据：是使用insertvalues语句来完成的，其语法格式如下： insert into (column1,column2,) values(value1,value2,); 例: 不使用列插入单行数据 使用列插入单行数据 使用特定格式插入日期值 使用default提供数据,2.使用子查询插入数据：其语法格式如下： insert into (column1,column2,.) subQuery; 例子:,数据操作语言,update：当要更新表中行的数据时，可以使用update语句。 注意事项： 1.如果要更新数字列，则可以直接提供数字值；如