Oracle学习笔记
Java相关课程系列笔记之二
笔记内容说明
Oracle数据库、SQL（薛海璐老师主讲，占笔记内容100%）；
目 录
一、 数据库介绍 ........................................................................................................................... 1
1.1表是数据库中存储数据的基本单位 ................................................................................. 1 1.2数据库标准语言 ................................................................................................................. 1 1.3数据库（DB） ................................................................................................................... 1 1.4数据库种类 ......................................................................................................................... 1 1.5数据库中如何定义表 ......................................................................................................... 1 1.6 create database dbname的含义 .......................................................................................... 1 1.7安装DBMS ........................................................................................................................ 1 1.8宏观上是数据-->database .................................................................................................. 1 1.9远程登录：telnet IP地址 .................................................................................................. 1 1.10 TCP/IP通信协议 .............................................................................................................. 2 1.11数据库建连接必须提供以下信息 ................................................................................... 2 1.12一台机器可跑几个数据库，主要受内存大小影响 ....................................................... 2 1.13源表和结果集 ................................................................................................................... 2 1.14几个简单命令 ................................................................................................................... 2 1.15 tarena给jsd1304授权 ..................................................................................................... 2 1.16课程中使用的5个表 ....................................................................................................... 3 二、 select from语句 ..................................................................................................................... 5
2.1 select语句功能 ................................................................................................................... 5 2.2 select语句基本语法 ........................................................................................................... 5 2.3列别名 ................................................................................................................................. 5 2.4算术表达式 ......................................................................................................................... 5 2.5空值null的处理 ................................................................................................................ 5 2.6 nvl(p1,p2)函数 .................................................................................................................... 5 2.7拼接运算符 || ..................................................................................................................... 6 2.8文字字符串 ......................................................................................................................... 6 2.9消除重复行 ......................................................................................................................... 6 2.10其他注意事项 ................................................................................................................... 6 三、 SQL语句的处理过程 ........................................................................................................... 7
3.1 SQL语句处理过程 ............................................................................................................ 7 3.2处理一条select语句 .......................................................................................................... 7 四、 where子句 ............................................................................................................................. 8
4.1 where子句后面可以跟什么 .............................................................................................. 8 4.2语法和执行顺序 ................................................................................................................. 8 4.3字符串是大小写敏感的，在比较时严格区分大小写 ..................................................... 8 4.4 where子句后面可以跟多个条件表达式 .......................................................................... 8 4.5 between and运算符 ............................................................................................................ 8 4.6 in运算符（多值运算符） ................................................................................................. 8 4.7 like运算符 .......................................................................................................................... 9 4.8 is null运算符 ...................................................................................................................... 9 4.9比较和逻辑运算符（单值运算符） ................................................................................. 9 4.10多值运算符all、any ........................................................................................................ 9 4.11运算符的否定形式 ........................................................................................................... 9
1
五、 order by子句 ....................................................................................................................... 10
5.1语法和执行顺序 ............................................................................................................... 10 5.2升降序 ............................................................................................................................... 10 5.3 null值在排序中显示 ....................................................................................................... 10 5.4 order by后面可以跟什么 ................................................................................................ 10 5.5多列排序 ........................................................................................................................... 10 六、 单行函数的使用.................................................................................................................. 11
6.1数值类型 ........................................................................................................................... 11 6.2日期类型 ........................................................................................................................... 11 6.3字符类型 ........................................................................................................................... 13 6.4转换函数 ........................................................................................................................... 14 6.5其他注意事项 ................................................................................................................... 14 七、 SQL语句中的分支 ............................................................................................................. 15
7.1分支表达式 ....................................................................................................................... 15 7.2分支函数 ........................................................................................................................... 15 八、 组函数 ................................................................................................................................. 16
8.1报表统计常用 ................................................................................................................... 16 8.2缺省情况组函数处理什么值 ........................................................................................... 16 8.3当组函数要处理的所有值都为null时 ........................................................................... 16 8.4行级信息和组级信息 ....................................................................................................... 16 九、 group by子句 ...................................................................................................................... 17
9.1语法和执行顺序 ............................................................................................................... 17 9.2分组过程 ........................................................................................................................... 17 9.3常见错误 ........................................................................................................................... 17 9.4多列分组 ........................................................................................................................... 17 十、 having子句 .......................................................................................................................... 18
10.1语法和执行顺序 ............................................................................................................. 18 10.2执行过程 ......................................................................................................................... 18 10.3 where和having区别 ..................................................................................................... 18 十一、 非关联子查询 ................................................................................................................. 19
11.1语法 ................................................................................................................................. 19 11.2子查询的执行过程 ......................................................................................................... 19 11.3常见错误 ......................................................................................................................... 19 11.4子查询与空值 ................................................................................................................. 19 11.5多列子查询 ..................................................................................................................... 20 十二、 关联子查询 ..................................................................................................................... 21
12.1语法 ................................................................................................................................. 21 12.2执行过程 ......................................................................................................................... 21 12.3 exists ............................................................................................................................... 21 12.4 exists执行过程 ............................................................................................................... 21 12.5 not exists ......................................................................................................................... 22 12.6 not exists执行过程 ........................................................................................................ 22 12.7 in和exists比较 ............................................................................................................. 22 十三、 多表查询 ......................................................................................................................... 23
13.1按范式要求设计表结构 ................................................................................................. 23
2
13.2多表连接的种类 ............................................................................................................. 23 13.3交叉连接 ......................................................................................................................... 23 13.4内连接 ............................................................................................................................. 23 13.5外连接 ............................................................................................................................. 25 13.6非等值连接 ..................................................................................................................... 27 13.7表连接总结 ..................................................................................................................... 27 十四、 集合 ................................................................................................................................. 28
14.1表连接主要解决的问题 ................................................................................................. 28 14.2集合运算 ......................................................................................................................... 28 14.3集合运算符 ..................................................................................................................... 28 14.4子查询、连接、集合总结 ............................................................................................. 29 十五、 排名分页问题 ................................................................................................................. 30
15.1什么是rownum .............................................................................................................. 30 15.2 where rownum<=5的执行过程 ..................................................................................... 30 15.3 where rownum=5的执行过程 ....................................................................................... 30 十六、 约束constraint ................................................................................................................ 31
16.1约束的类型 ..................................................................................................................... 31 16.2 primary key：主键约束 ................................................................................................. 31 16.3 not null：非空约束 ........................................................................................................ 31 16.4 unique key：唯一建约束 ............................................................................................... 31 16.5 references foreign key：外键约束 ................................................................................. 32 16.6 check：检查约束 ........................................................................................................... 34 十七、 事务 ................................................................................................................................. 35
17.1 transaction ....................................................................................................................... 35 17.2定义 ................................................................................................................................. 35 17.3事务的特性：ACID ....................................................................................................... 35 17.4事务的隔离级别 ............................................................................................................. 35 17.5数据库开发的关键挑战 ................................................................................................. 35 17.6锁的概念 ......................................................................................................................... 36 17.7 Oracle的锁机制 ............................................................................................................. 36 17.8事务不提交的后果 ......................................................................................................... 36 17.9回滚事务rollback ........................................................................................................... 36 17.10保留点savepoint .......................................................................................................... 36 十八、 数据库对象：视图view ................................................................................................ 38
18.1带子查询的create table ................................................................................................. 38 18.2带子查询的insert ........................................................................................................... 38 18.3定义缺省值：default ...................................................................................................... 38 18.4 视图view ....................................................................................................................... 39 18.5视图的应用场景 ............................................................................................................. 39 18.6视图的分类 ..................................................................................................................... 40 18.7视图的维护 ..................................................................................................................... 40 十九、 数据库对象：索引index ............................................................................................... 42
19.1创建index ....................................................................................................................... 42 19.2扫描表的方式 ................................................................................................................. 42 19.3索引的结构 ..................................................................................................................... 42
3