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 该字段的值不能为空 语法格式 constraint notnull2 唯一约束 unique 该字段的值不能重复 语法格式 constraint unique 3 主键约束 primarykey 唯一的确定表中的每一行数据语法格式 constraint primarykey 4 检查约束 check 检查约束指表中的某一个列或一组列取值应该满足检查约束指定的条件 语法格式 constraint check 数据定义语言 表的约束5 缺省约束 default 为字段提供默认值语法格式 constraint default6 外键约束 foreignkey 外键是指引用另一个表中的某个列或某几个列 或本表中另一个列或几个列 语法格式 constraint foreignkey references ondelete cascade setnull 注意 ondelete updatecascade表示修改或删除时做级联操作ondeletesetnull将所有相关记录的外部码字段值设置为NULL 数据定义语言 修改表1 增加列 增加列就是在表中增加一个新列 增加列是最常见的修改表结构的操作 语法格式 altertableadd 2 更新列语法格式 altertablemodify 3 删除列语法格式 altertabledropcolumn 数据定义语言 修改表约束1 非空约束 altertablemodify constraint notnull null 2 唯一约束 altertableaddconstraintunique 3 主键约束 altertableaddconstraintprimarykey 数据定义语言 修改表约束4 外键约束 altertableaddconstraintforeignkey references ondelete cascade setnull onupdate cascade 5 检查约束 altertableaddconstraintcheck 6 缺省约束 altertablemodifydefault 数据定义语言 修改表名ALTERTABLEoldTableNameRENAMETOnewTableName如果是当前用户库的表RENAMEoldTableNameTonewTableName修改列名ALTERTABLEtableNameRENAMECOLUMNoldColumnNameTOnewColumnName 数据定义语言 删除表使用droptable语句删除表语法格式 droptable注意 在删除表时 如果该表中的数据被另外表的外键引用 那么必须要先删除外键表 才能删除该表 数据操作语言 Select语句 用于检索数据库的数据 它是所有SQL语句中语法最复杂 功能也是最强大的SQL语句 单表查询 单表查询是指仅涉及一个表的查询1 简单查询语句语法格式 select distinct from主要有以下查询 检索所有列 检索特定列 检索日期列 取消重复行 使用算术表达式 使用列别名 处理NULL 连接字符串 数据操作语言 2 使用where子句语法如下 select distinct from wherecondition 主要有以下查询 在where条件中使用数字值 字符值 日期值 between and 操作符 notbetween and 不匹配检索 比较操作符 like操作符 in操作符 notin操作符 逻辑操作符 and or not 查询null值 数据操作语言 3 使用orderby子句语法如下 select distinct from wherecondition s orderbyexpr asc desc 主要有以下查询 升序排序 降序排序 多列排序 非选择列进行排序 列别名排序 列位置编号排序 数据操作语言 4 数据分组 是通过使用groupby子句 分组函数以及havnig子句共同实现的 其中groupby子句用于指定要分组的列 分组函数则用于显示统计结果 而having子句则用于限制分组显示结果 1 分组函数max 该函数用于取得列或表达式的最大值 它适用于任何数据类型 min 该函数用于取得列或表达式的最小值 它适用于任何数据类型 avg 该函数用于取得列或表达式的平均值 它只适用于数字类型 sum 该函数用于取得列或表达式的总和 它只适用于数字类型 count 该函数用于取得总计行数 数据操作语言 2 groupby和having语法格式 selectcolumn group functionfrom wherecondition groupbygroupby expression havinggroup 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子句用于指定连接条件 数据操作语言 左外连接通过left outer join选项来实现 不仅会返回满足连接条件的所有记录 还会返回不满足连接条件的连接符左别表的其他行 语法格式如下 selecttable1 column table2 columnfromtable1leftjointable2ontable1 column table2 column 举例 显示部门10部门名 雇员名 以及其它部门名 数据操作语言 右外连接通过right outer join选项来实现 不仅会返回满足连接条件的所有记录 还会返回不满足连接条件的连接符右别表的其他行 语法格式如下 selecttable1 column table2 columnfromtable1rightjointable2ontable1 column table2 column 举例 显示部门10部门名 雇员名 以及其它雇员名 数据操作语言 完全外连接通过full outer join选项来实现 不仅会返回满足连接条件的所有记录 还会返回不满足连接条件的的其他行 语法格式如下 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 插入单行数据 是使用insert values语句来完成的 其语法格式如下 insertinto column1 column2 values value1 value2 例 不使用列插入单行数据使用列插入单行数据使用特定格式插入日期值使用default提供数据 2 使用子查询插入数据 其语法格式如下 insertinto column1 column2 subQuery 例子 数据操作语言 update 当要更新表中行的数据时 可以使用update语句 注意事项 1 如果要更新数字列 则可以直接提供数字值 如果要更新字符列或日期列 则数据必须用单引号引住 2 当更新数据时 数据必须要满足约束规则 3 当更新数据时 数据必须要与列的数据类型匹配 数据操作语言 1 使用表达式更新数据语法格式如下 updatetablesetcolumn1 value1 column2 value2 where 例 更新一列数据 SCOTT的工资涨到3000 更新多列数据 同时更新工资和补助 各涨50 SCOTT更新出生日期更新部门默认地址为NEWYORK 数据操作语言 2 使用子查询更新数据更新相关联的数据时 使用子查询效率更高 例 使雇员SCOTT的岗位 工资 补助与雇员SMITH完全相同 数据操作语言 delete 当要删除表中某行的数据时 可以使用delete语句 语法格式如下 deletefromtable where table用于指定表名 view用于指定视图名 condition用于指定条件子句 当使用delete语句删除数据时 如果不指定where条件子句 那么会删除表或视图的所有行 删除主表数据的注意事项 当删除主表数据时 必须确保从表不存在相关记录 否则会显示错误信息 数据操作语言 Truncate删除数据语法如下 TRUNCATETABLEtableNameRUNCATETABLE在功能上与不带WHERE子句的DELETE语句相同 二者均删除表中的全部行 TRUNCATETABLE比DELETE速度快 且使用的系统和事务日志资源少 TRUNCATE不能触发任何DELETE触发器 不能授予任何人清空他人的表的权限 当表被清空后表和表的索引将重新设置成初始大小 而delete则不能