Oracle学习笔记.docx
《Oracle学习笔记.docx》由会员分享，可在线阅读，更多相关《Oracle学习笔记.docx（73页珍藏版）》请在冰点文库上搜索。
Oracle学习笔记
Oracle学习笔记
Java相关课程系列笔记之二
笔记内容说明
Oracle数据库、SQL（薛海璐老师主讲，占笔记内容100%）；
目录
一、数据库介绍1
1.1表是数据库中存储数据的基本单位1
1.2数据库标准语言1
1.3数据库（DB）1
1.4数据库种类1
1.5数据库中如何定义表1
1.6createdatabasedbname的含义1
1.7安装DBMS1
1.8宏观上是数据-->database1
1.9远程登录：
telnetIP地址1
1.10TCP/IP通信协议2
1.11数据库建连接必须提供以下信息2
1.12一台机器可跑几个数据库，主要受内存大小影响2
1.13源表和结果集2
1.14几个简单命令2
1.15tarena给jsd1304授权2
1.16课程中使用的5个表3
二、selectfrom语句5
2.1select语句功能5
2.2select语句基本语法5
2.3列别名5
2.4算术表达式5
2.5空值null的处理5
2.6nvl（p1,p2）函数5
2.7拼接运算符||6
2.8文字字符串6
2.9消除重复行6
2.10其他注意事项6
三、SQL语句的处理过程7
3.1SQL语句处理过程7
3.2处理一条select语句7
四、where子句8
4.1where子句后面可以跟什么8
4.2语法和执行顺序8
4.3字符串是大小写敏感的，在比较时严格区分大小写8
4.4where子句后面可以跟多个条件表达式8
4.5betweenand运算符8
4.6in运算符（多值运算符）8
4.7like运算符9
4.8isnull运算符9
4.9比较和逻辑运算符（单值运算符）9
4.10多值运算符all、any9
4.11运算符的否定形式9
五、orderby子句10
5.1语法和执行顺序10
5.2升降序10
5.3null值在排序中显示10
5.4orderby后面可以跟什么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
九、groupby子句17
9.1语法和执行顺序17
9.2分组过程17
9.3常见错误17
9.4多列分组17
十、having子句18
10.1语法和执行顺序18
10.2执行过程18
10.3where和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.3exists21
12.4exists执行过程21
12.5notexists22
12.6notexists执行过程22
12.7in和exists比较22
十三、多表查询23
13.1按范式要求设计表结构23
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什么是rownum30
15.2whererownum<=5的执行过程30
15.3whererownum=5的执行过程30
十六、约束constraint31
16.1约束的类型31
16.2primarykey：
主键约束31
16.3notnull：
非空约束31
16.4uniquekey：
唯一建约束31
16.5referencesforeignkey：
外键约束32
16.6check：
检查约束34
十七、事务35
17.1transaction35
17.2定义35
17.3事务的特性：
ACID35
17.4事务的隔离级别35
17.5数据库开发的关键挑战35
17.6锁的概念36
17.7Oracle的锁机制36
17.8事务不提交的后果36
17.9回滚事务rollback36
17.10保留点savepoint36
十八、数据库对象：
视图view37
18.1带子查询的createtable37
18.2带子查询的insert37
18.3定义缺省值：
default37
18.4视图view38
18.5视图的应用场景38
18.6视图的分类39
18.7视图的维护39
十九、数据库对象：
索引index41
19.1创建index41
19.2扫描表的方式41
19.3索引的结构41
19.4为什么要使用索引42
19.5哪些列适合建索引42
19.6索引的类型42
19.7哪些写法会导致索引用不了43
二十、数据库对象：
序列号sequence44
20.1什么是sequence44
20.2创建sequence44
20.3缺省是nocycle（不循环）44
20.4缺省cache2044
二十一、其他注意事项46
21.1删除表，删除列，删除列中的值46
21.2多对多关系的实现46
21.3一对多（两张表）46
21.4一对一46
21.5数据库对象46
12.6缺省（默认）总结：
46
1、数据库介绍
1.1表是数据库中存储数据的基本单位
1.2数据库标准语言
结构化查询语言SQL：
StructureedQueryLanguage
1）数据定义语言DDL：
DataDefinitionLanguage
createtable列表结构、altertable修改列、droptable删除列
2）数据操作语言DML：
DataManipulationLanguage
insert增加一行，某些列插入值、update修改一行，这一行的某些列、delete删除一行，跟列无关
3）事务控制语言TCL：
TransactionConrtolLanguage
commit确认，提交（入库）、rollback取消，回滚，撤销
4）数据查询语言DQL：
DataQueryLanguage
select语句
5）数据控制语言DCL：
DataControlLanguage
系统为多用户系统因此有隐私权限问题：
grant授权、revoke回收权限
1.3数据库（DB）
DATABASE关系数据库使用关系或二维表存储信息。
关系型数据库管理系统（EDBMS）：
RelationshipDatabaseManagementSystem是一套软件，用于在数据库中存储数据、维护数据、查询数据等。
1.4数据库种类
Oracle10g（Oracle）、DB2（IBM）、SQLSERVER（MS）
1.5数据库中如何定义表
先画列即表头（列名，数据类型及长度，约束）；数据类型有字符、数值number、日期date。
1.6createdatabasedbname的含义
创建数据库即创建可用空间，创建出一堆数据文件datafile
1.7安装DBMS
职位：
DBA数据库管理员（DataBaseAdministrator）
1.8宏观上是数据-->database
开发流程：
createtabaleDMLTCL->DQLselect
1.9远程登录：
telnetIP地址
sqldeveloper在linux系统--->连接--->database在solaris系统
1.10TCP/IP通信协议
两台机器上的两个应用程序要通信，必须依赖网络，依赖TCP/IP通信协议。
IP：
IP协议包中提供要连接机器的IP地址，用于标识机器。
TCP：
TCP协议包中提供与机器上的哪个具体应用程序通信，通过端口号实现，oracle数据库服务缺省端口为1521，用于标识Oracle此数据库应用。
1.11数据库建连接必须提供以下信息
ip地址（确认机器）、port号（确认进程（程序）确认Oracle）
SID：
一个端口可以为多个oracle数据库提供监听，因此还需要提供具体的数据库名。
（确认数据库里的哪个数据库）
username、password：
要想访问数据库，必须是该数据库上一个有效的用户。
（确认身份）
1.12一台机器可跑几个数据库，主要受内存大小影响
1.13源表和结果集
源表：
被查询的表结果集：
select语句的查询结果
1.14几个简单命令
showuser：
查看当前用户desc表名：
查看表结构
droptable表名purge;删除表，Oracle中删除表不是真正的删除，而是占空间的移动到别的地方，因为为了不占空间，真正的删除需要用purge。
deletefrom表名：
删除表中所有值；若加上where列名=value则删除某列中的值
1.15tarena给jsd1304授权
connecttarena/tarena
grantselectonaccounttojsd1304;grantselectonservicetojsd1304;
grantselectoncosttojsd1304;
jsd1304selecttarena的表
connectjsd1304/jsd1304
createsynonym创建同义词createsynonymaccountfortarena.account;
createsynonymservicefortarena.service;createsynonymcostfortarena.cost;
1.16课程中使用的5个表
2、
selectfrom语句
2.1select语句功能
1）投影操作：
结果集是源表中的部分“列”
2）选择操作：
结果集是源表中的部分“行”
3）选择操作+投影操作：
结果集是源表中的部分“行”部分“列”
4）连接操作join：
多表查询，结果集来自多张表，把多张的记录按一定条件组合起来
2.2select语句基本语法
1）selectcolname（列名）fromtabname（表名）
2）select中指定多个列名，则用“逗号”分隔：
selectcolname1,colname2fromtabname
3）*号表示所有列：
select*fromtabname
4）select语句：
可有多个子句
5）select子句：
投影操作（列名）、列表达式、函数、from子句等
2.3列别名
1）给列起一个别名，能够改变一个列、表达式的标识。
2）不写的话默认都是转成大写。
3）适合计算字段。
4）在原名和别名之间可以使用as关键字。
5）别名中包含空格、特数字符或希望大小写敏感的，用“”双引号将其括起来。
2.4算术表达式
在number类型上使用算术表达式（加减乘除）。
eg：
一个月使用了250小时,每种资费标准下应缴纳的费用（首次实现）
selectbase_cost+（250-base_duration）*unit_costfeefromcost;
2.5空值null的处理
未知的，没写数
1）空值不等于0
2）空值不等于空格
3）在算术表达式中包含空值导致结果为空
4）在算术表达式中包含空值需要用空值转换函数nvl处理
2.6nvl（p1,p2）函数
空值转换函数
1）两个参数类型要一致！
2）参数的数据类型可以是数值number、字符character、日期date
3）但null转成字符串，null也要用to_char（）转化。
4）实现过程：
ifp1isnullthenreturnp2
elasereturnp1
endif
5）实现空值转换：
null－>非null值0
eg：
一个月使用了250小时,每种资费标准下应缴纳的费用（再次实现）
selectnvl（base_cost,0）+（250-nvl（base_duration,0））*nvl（unit_cost,0）feefromcost;
2.7拼接运算符||
表达字符（串）的拼接，可以将某几列或某列与字符串拼接在一起。
selectcolname1||colname2fromtabname
2.8文字字符串
select语句后面可以包含的文字值：
字符、表达式、数字。
1）字符常量（或字符串）必须用‘’单引号括起来，作为“定界符”使用。
2）表达单引号本身，需要两个单引号''''1，4定界2，3表单引号。
3）对于文字值每行输出一次。
eg：
显示客户姓名的身份证号是……
selectreal_name||'''sIDCARDNOis'||idcard_no||'.'cilentfromaccount;
4）函数转换大小写，尽量在进入数据时操作。
2.9消除重复行
distinct去重复行（对整条记录返回的结果去重，不是对后面的某个列去重），若后面有多列，则所有列联合起来唯一，即每列的值都可以重复，但组合不能重复。
eg1：
哪些unix服务器提供远程登录业务
selectdistinctunix_hostfromservice;
eg2：
每一台unix服务器在哪些天开通了远程登录业务
selectdistinctunix_host,create_datefromservice;
2.10其他注意事项
1）调常量时用单行单列的dual表，系统提供的表。
2）invalididentifier无效标识名，列名不。
3）tableorviewdoesnotexist表名不对。
3、
SQL语句的处理过程
3.1SQL语句处理过程
用户进程sqlplus→建立连接→服务进程ServerprocessoracleSID
↑--创建会话--Oracleserver
3.2处理一条select语句
1）分析语句：
①搜索是否有相同语句
②用hashvalue计算select语句是否长得一样：
大小写，关键字，空格要都一样，不一样则为两条语句，则服务进程会重新分析。
若为统一语句，则直接从内存拿执行计划，计算结果
③检查语法、表名、权限④在分析过程中给对象加锁
⑤生成执行计划
2）绑定变量：
给变量赋值
3）执行语句：
4）获取数据：
将数据返回给用会进程
4、
where子句
用where子句对表里的记录进行过滤，where子句跟在from子句后面。
4.1where子句后面可以跟什么
跟条件表达式：
列名、常量、比较运算符（单、多值运算符）、文字值；不能跟组函数！
不能跟列别名！
◆注意事项：
对列不经过运算的条件表达式效率会更高，建议在写where子句时尽量不要对列进行运算。
eg：
一年的固定费用为70.8元，计算年包在线时长
selectbase_duration*12ann_durationfromcostwherebase_cost*12=70.8;没下面效率高
selectbase_duration*12ann_durationfromcostwherebase_cost=70.8/12;
4.2语法和执行顺序
语法顺序：
selectfromwhere执行顺序：
fromwhereselect
4.3字符串是大小写敏感的，在比较时严格区分大小写
1）upper（）：
函数将字符串转换成大写。
2）lower（）：
函数将字符串转换成小写。
3）initcap（）：
函数将字符串转换成首字符大写（是将列中的值大小写转换然后去和等号后的字符串比，而不是把转字符串转换去和列比）。
eg：
哪些unix服务器上开通了os帐号huangr
selectunix_host,os_usernamefromservice
whereos_username='huangr';（有结果）
wherelower（os_username）='HUANGR';（无结果）
wherelower（os_username）='huangr';（有结果）
whereupper（os_username）='HUANGR';（有结果）
4.4where子句后面可以跟多个条件表达式
条件表达式之间用and、or连接，也可用（）改变顺序。
4.5betweenand运算符
表示一个范围，是闭区间，含义为大于等于并且小于等于。
eg：
哪些资费的月固定费用在5元到10元之间
selectbase_duration,base_cost,unit_costfromcost
wherebase_cost>=5andbase_cost<=10;wherebase_costbetween5and10;
4.6in运算符（多值运算符）
表示一个集合，是离散值，含义为等于其中任意一个值，等价于any。
eg：
哪些资费的月固定费用是5.9元,8.5元,10.5元
selectbase_duration,base_cost,unit_costfromcost
wherebase_cost=5.9orbase_cost=8.5orbase_cost=10.5;
wherebase_costin（5.9,8.5,10.5）;wherebase_cost=any（5.9,8.5,10.5）;
4.7like运算符
在字符串比较中，可用like和通配符进行模糊查找。
1）通配符：
%表示0或多个字符；_表示任意“一个”字符（要占位的）。
◆注意事项：
若要查找%和_本身，则需要escape进行转移。
eg：
哪些unix服务器上的os帐号名是以h开头的
selectos_usernamefromservicewhereos_usernamelike'h%'
eg：
哪些unix服务器上的os帐号名是以h_开头的
selectos_usernamefromservicewhereos_usernamelike'h\_%'escape'\';
4.8isnull运算符
测试null值需要用isnull。
1）null不能用等于号“＝”和不等于号“<>”跟任何值比较，包括它自身。
所以不能用“＝”和“<>”来测试是否有空值。
2）即：
null=null是不成立的；null不等于null也不成立；null和任何值比较都不成立。
eg：
列出月固定费用是5.9元，8.5元，10.5元或者没有月固定费。
selectbase_duration,base_cost,unit_costfromcost
wherebase_costin（5.9,8.5,10.5,null）;（错误）
wherebase_costin（5.9,8.5,10.5）orbase_costisnull;（正确）
4.9比较和逻辑运算符（单值运算符）
1）比较运算符：
=>>=<<=
2）SQL比较运算符：
betweenand、in、like、isnull
3）逻辑运算符：
and、or、not
4.10多值运算符all、any
1）>all：
大于所有的，等价于>（selectmax（）…）。
2）>any：
大于任意的，等价于>（selectmin（）…）。
4.11运算符的否定形式
1）比较运算符：
<>！
=^=
2）SQL比较运算符：
notbetweenandnotinnotlikeisnotnull
◆注意事项：
vin相当于=or=or=or等价于any
vnotin等价于<>and<>and<>and等价于<>all
vnotbetweenand小于下界or大于上界
v集合中有null，对in无影响；但对notin有影响，有一个就没有返回值！
eg：
哪些资费信息的月固定费用不是5.9元,8.5元,10.5元
selectbase_duration,base_cost,unit_costfromcost
wherenvl（base_cost,0）<>5.9andnvl（base_cost,0）<>8.5andnvl（base_cost,0）<>10.5;
wherenvl（base_cost,0）notin（5.9,8.5,10.5）;
5、
orderby子句
select语句输出的结果安记录在表中的存储顺序显示，orderby子句能够改变记录的输出顺序。
orderby子句对查询出来的结果集进行排序，即对select子句的计算结果排序。
5.1语法和执行顺序
语法顺序：
selectfromwhereorderby执行顺序：
fromwhereselectorderby
5.2升降序
ASC－升序，可以省略，默认值DESC－降序
orderbynvl（base_cost,0）;orderbyunix_host,create_datedesc;
◆注意事项：
orderby是select语句中最后一个子句
5.3null值在排序中显示
1）被排序的列如果包含null值，用ASC方式null值的在最后；
2）用DESC方式null在最前面；
5.4orderby后面可以跟什么
可以跟列名、列别名、列位置（数字）、表达式、函数。
orderby1：
表示列位置为1的列
select1from：
表示常量1
eg：
按年固定费用从大到小的顺序显示资费信息
方式一：
selectid,base_cost*12ann_cost,base_durationann_durationfromcost
orderbybase_costdesc;
方式二：
selectid,base_costann_cost,base_durationann_durationfromcost
orderbybase_cost*12de
升级会员 