Oracle学习笔记.docx
- 文档编号:12648524
- 上传时间:2023-06-06
- 格式:DOCX
- 页数:67
- 大小:510.87KB
Oracle学习笔记.docx
《Oracle学习笔记.docx》由会员分享,可在线阅读,更多相关《Oracle学习笔记.docx(67页珍藏版)》请在冰点文库上搜索。
Oracle学习笔记
Oracle学习笔记
Java相关课程系列笔记之二
笔记内容说明
Oracle数据库、SQL
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*12desc;排序的效果和上面是一样的,但前一个效率高。
5.5多列排序
orderby子句后面可以跟多列,而orderby后面的列可以不出现在select后面。
结果集先按第一列升序排列,若列值一样,再按第二列降序排列。
eg:
按unix服务器ip地址升序,开通时间降序显示业务帐号信息
selectid,unix_host,os_username,create_datefromservice
orderbyunix_host,create_datedesc;
6、
单行函数的使用
SQL函数的两种类型:
单行函数、多行函数(组函数)。
单行函数:
数值类型、日期类型、字符类型、转换函数。
处理一列数据,返回一个结果。
6.1数值类型
1)定义:
createtabletabname
(c1number,c2number(6),c3number(4,3),c4number(3,-3),c5number(2,4));
2)数值类型说明
①number:
不写数值,表可写38位数
②number(6):
6位整数999999.1999999
③number(4,3):
数字4位,小数点占3位,四舍五入1.2345671.235
④number(3,-3):
小数点前三位不写数,四舍五入,然后有效位3位12341000
⑤number(2,4):
小数点后4位,有效位2位0.009910.0099
3)数值函数:
参数类型为number
①round():
四舍五入函数,“缺省转成数字”;也可对日期
②trunc():
截取函数(不管多大值直接舍去);也可对日期
eg:
round和trunc
round(45.923,2):
45.92round(45.923,0):
46round(45.923,-1):
50
trunc(45.923,2):
45.92trunc(45.923):
45trunc(45.923,-1):
40
6.2日期类型
1)Oracle用7个字节来存储日期和时间:
世纪、年、月、日、时、分、秒。
Date不存在定宽度,就是7个字节。
2)缺省(默认)日期格式为DD-MON-RR,格式敏感。
3)sysdate是一个系统函数,返回当前系统时间和日期。
4)改变session(会话)中的日期格式:
session和connection是同时建立的,两者是对同一件事情的不同层次的描述。
connection是物理上的客户机同服务器端的通信链路;session是逻辑上的用户同服务器的通信交互,SQL语句的运行环境。
eg:
显示的日期包含世纪、年、月、日、时、分、秒
altersessionsetnls_date_format='yyyymmddhh24:
mi:
ss';
5)日期格式
yyyy
用数字表达的四位年(2013年)
mm
用数字表达的两位月(01月)
dd
用数字表达的两月日(01日)
hh24
用数字表达的24进制的小时(20点)
h12
用数字表达的12进制的小时(8点)
mi
用数字表达的分钟(30分)
ss
用数字表达的小时(30秒)
D
用数字表达的一周内的第几天(周日:
1)
day
用全拼表达的星期几(sunday)
month
用全拼表达的月(march)
mon
用简拼表达的月(mar)
eg:
案例
selectto_char(sysdate,'DDD')fromdual;年中的第几天
selectto_char(sysdate,'DD')fromdual;月中的第几天
selectto_char(sysdate,'D')fromdual;星期中的第几天
6)在数据库中如何处理日期类型
createtabletest(c1date);
insertintotestvalues('01-JAN-08');
insertintotestvalues('2008-08-08');(报错)
insertintotestvalues(to_date('2008-08-08','yyyy-mm-dd'));
selectc1fromtest;
selectto_char(c1,'yyyy-mm-dd')fromtest;
在createtable中定义日期类型date时一定不能指定宽度。
日期在数据库中用固定的7个字节存储,表示世纪、年、月、日、时、分、秒。
缺省的日期格式为'DD-MON-RR','01-JAN-08'符合缺省日期格式可以插入表中,因为系统会自动调用to_date函数将它转成日期。
'2008-08-08'插入时报错,原因是不符合缺省格式,需要手工使用函数to_date对字符串的格式进行说明,如'2008-08-08'的格式说明串为'yyyy-mm-dd'。
select时日期按缺省日期格式显示,若用指定日期格式,需要使用to_char函数。
7)日期与字符串相互转换:
to_date(char,date)函数:
将字符串转换成一个日期值。
对应java中parse。
to_char(date,char)函数:
第一个参数为要处理的日期,第二个参数为格式;可获取一个日期的任意一部分信息;对应java中format。
eg:
创建一张表,包含date类型的列,插入2008年8月8日8点8分8秒并显示。
insertintotestvalues(to_date('2008-08-0808:
08:
08','yyyy-mm-ddhh24:
mi:
ss'));
selectto_char(c1,'yyyy-mm-ddhh24:
mi:
ss')fromtest;
◆注意事项:
v格式必须用单引号括起来,并且大小写敏感。
v必须是有效的日期格式。
vfm能去掉前导0和两端的空格。
v对日期去重复问题,to_char获取当天日期即可,时分秒忽略,加上distinct即可做到
eg:
案例
whereto_char(creat
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 学习 笔记