oracle四大宝典之1：Oracle Sql基础.docx

第 1 章 编写基本的SQL SELECT 语句 ............................................2

1.1

SQL的起源和分类 .....................................................3

1.1.1 SQL的起源......................................................3

1.1.2 SQL的分类......................................................4

1.2

本章学习目的 .........................................................5

1.3

SQL SELECT语句的功能................................................6

1.4

基本SELECT语句......................................................8

1.4.1 选择所有列.....................................................10

1.4.2 选择指定的列...................................................10

1.4.9 文字字符串.....................................................20

1.4.10 相同的行......................................................21

1.5

iSQL*Plus 简介 ......................................................23

1.5.1 iSQL*Plus概览.................................................24

1.5.2 SQL 和iSQL*Plus 交互作用......................................24

第 2 章 约束和排序............................................................25

2.1 本章学习目的 ........................................................26

2.2 用选择限定行 ........................................................27

2.2.1 限定语法.......................................................28

2.2.2 使用WHERE字句..................................................28

2.2.9 使用NULL/NOT NULL条件..........................................34

2.2.10 逻辑条件......................................................36

2.2.11 优先规则......................................................38

2.3 排序 ................................................................39

2.3.1 降序排序......................................................41

2.3.2 用列别名排序..................................................42

2.3.3 多列排序......................................................43

2.4 总结 ................................................................44

第 3 章 ORACLE SQL 单行函数 ................................................44

3.1 学习目的 ............................................................45

3.7.1 大小写处理函数................................................52

3.7.2 字符处理函数..................................................53

3.8 数字函数 ............................................................55

3.9 日期函数 .............................................................57

3.9.1 系统日期......................................................59

3.9.2 用日期计算....................................................59

3.9.3 日期函数......................................................60

3.10 转换函数 ...........................................................61

3.10.1 隐式数据类型转换.............................................62

3.10.2 显式数据类型转换.............................................63

3.13.1 CASE表达式...................................................69

3.13.2 DECODE函数...................................................70

3.14 总结 ...............................................................72

第 4 章 从多表中查询数据......................................................73

4.1 从多表中查询数据 ....................................................74

4.2 有效连接条件与笛卡儿积 ..............................................77

4.3 连接的类型 ..........................................................79

4.4 表连接的原则 ........................................................80

4.5 等值连接 ............................................................81

4.6 等值连接中的列别名、表别名 ..........................................84

4.11.2 自然连接.....................................................98

4.11.3 ON子句......................................................99

4.12 总结 ..............................................................100

第 5 章 用组函数合计数据.....................................................100

5.1 学习目的 ...........................................................101

5.2 组函数的概念 .......................................................101

5.3 组函数使用 .........................................................103

5.3.1 使用AVG、SUM、MAX、MIN.......................................104

5.3.2 使用COUNT....................................................105

5.3.6 HAVING 子句..................................................109

5.3.7 嵌套组函数...................................................110

5.4 总结 ...............................................................111

第 6 章 子查询...............................................................111

6.1 学习目的 ...........................................................112

6.2 子查询介绍 .........................................................112

6.3 使用子查询的原则 ...................................................114

6.4 子查询分类 .........................................................115

6.5 单行子查询 .........................................................116

6.6 单行子查询错误 .....................................................119

7.4 INSERT插入值的变化 ................................................129

7.4.1 插入特殊值...................................................130

7.4.2 插入指定日期.................................................131

7.4.3 插入可输入变量...............................................131

7.4.4 从另外一个表复制.............................................131

7.4.5 在INSERT语句中使用子查询.....................................132

7.5 UPDATE更新数据的语法 .............................................133

7.6 UPDATE更新的例子和问题 ...........................................135

7.6.1 用子查询更新两列.............................................136

7.6.2 更新基于另一个表的行.........................................137

7.9 显式默认值 .........................................................142

7.10 MERGE合并语句语法 ...............................................143

7.11 数据库事务处理 ....................................................146

7.11.1 数据库事务开始与结束........................................147

7.11.2 COMMIT和ROLLBACK语句........................................148

7.11.3 事务的控制..................................................149

7.11.4 事务的控制示例..............................................150

7.11.5 隐式事务处理................................................150

7.12 读一致性 ..........................................................151

7.13.3 按照对象分类锁定............................................155

7.14 本章总结 ..........................................................155

第 8 章 创建和管理表.........................................................156

8.1 数据库对象及表介绍 .................................................157

8.2 表的分类 ...........................................................159

8.3 表和列的命名规则 ...................................................161

8.4 CREATE TABLE语句创建表 ...........................................162

8.5 用子查询语法创建表 .................................................164

8.6 DEFAULT选项 ......................................................166

8.7 引用表 .............................................................167

8.13.1 添加列......................................................180

8.13.2 修改列......................................................180

8.13.3 删除列......................................................180

8.14 删除表 ............................................................181

8.15 改变对象名称 ......................................................182

8.16 截断表 ............................................................183

8.17 给表添加注释 ......................................................184

第 9 章 内置约束.............................................................186

9.1 约束的概念和原则 ...................................................187

9.3.1 NOT NULL约束.................................................192

9.3.2 UNIQUE约束...................................................192

9.3.3 PRIMARY KEY约束..............................................193

9.3.4 FOREIGN KEY约束..............................................194

9.3.5 CHECK约束....................................................195

9.4 管理约束 ...........................................................196

9.4.1 添加约束.....................................................196

9.4.2 删除约束.....................................................197

9.4.3 禁用、启用约束...............................................198

第 10 章 创建视图............................................................203

10.1 视图的概念和优势 ..................................................204

10.1.1 视图的概念..................................................204

10.1.2 使用视图的优势..............................................205

10.2 简单视图和复杂视图 ................................................205

10.2.1 创建简单视图................................................206

10.2.2 视图中查询数据..............................................207

10.2.3 创建复杂视图................................................208

10.3 视图中的规则 ......................................................209

10.3.1 视图中DML 操作的执行规则....................................210

10.5 Top-N 分析 ........................................................214

10.6 物化视图简介 ......................................................216

10.7 总结 ..............................................................218

第 11 章 其他数据库对象......................................................219

11.1 其他数据库对象的作用 ..............................................220

11.2 序列 ..............................................................221

11.2.1 序列的语法..................................................222

11.2.2 定义及查询序列..............................................223

11.2.3 NEXTVAL和CURRVAL伪列........................................223

11.2.4 使用序列....................................................224

11.3.4 基于函数的索引..............................................230

11.3.5 删除索引....................................................231

11.4 同义词 ............................................................231

11.4.1 概念语法....................................................231

11.4.2 创建删除同义词..............................................232

11.5 总结 ..............................................................232

第 12 章 控制用户访问........................................................233

12.1 控制用户访问的意义 ................................................234

12.2 用户及权限 ........................................................235