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

1

4.11运算符的否定形式 ........................................................................................................... 9 五、 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

2