Java相关课程系列笔记之二Oracle学习笔记(共52页).doc

《Java相关课程系列笔记之二Oracle学习笔记(共52页).doc》由会员分享，可在线阅读，更多相关《Java相关课程系列笔记之二Oracle学习笔记(共52页).doc（52页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上Oracle学习笔记Java相关课程系列笔记之二专心-专注-专业笔记内容说明Oracle数据库、SQL（薛海璐老师主讲，占笔记内容100%）； 目 录一、 数据库介绍1.1表是数据库中存储数据的基本单位1.2数据库标准语言结构化查询语言SQL：Structureed Query Language1）数据定义语言DDL：Data Definition Languagecreate table列表结构、alter table修改列、drop table删除列2）数据操作语言DML：Data Manipulation Languageinsert增加一行，某些列插入值、up

2、date修改一行，这一行的某些列、delete删除一行，跟列无关3）事务控制语言TCL：Transaction Conrtol Languagecommit确认，提交（入库）、rollback取消，回滚，撤销4）数据查询语言DQL：Data Query Languageselect语句5）数据控制语言DCL：Data Control Language系统为多用户系统因此有隐私权限问题：grant 授权、revoke回收权限1.3数据库（DB）DATABASE 关系数据库使用关系或二维表存储信息。关系型数据库管理系统（EDBMS）：Relationship Database Management

3、 System是一套软件，用于在数据库中存储数据、维护数据、查询数据等。1.4数据库种类Oracle 10g（Oracle）、DB2（IBM）、SQL SERVER（MS）1.5数据库中如何定义表先画列即表头（列名，数据类型及长度，约束）；数据类型有字符、数值number、日期date。1.6 create database dbname的含义创建数据库即创建可用空间，创建出一堆数据文件data file1.7安装DBMS职位：DBA 数据库管理员（DataBase Administrator）1.8宏观上是数据-database开发流程：create tabale DML TCL - DQL

4、 select1.9远程登录：telnet IP地址sql developer在linux系统-连接-database在solaris系统1.10 TCP/IP通信协议两台机器上的两个应用程序要通信，必须依赖网络，依赖TCP/IP通信协议。IP：IP协议包中提供要连接机器的IP地址，用于标识机器。TCP：TCP协议包中提供与机器上的哪个具体应用程序通信，通过端口号实现，oracle数据库服务缺省端口为1521，用于标识Oracle此数据库应用。1.11数据库建连接必须提供以下信息ip地址（确认机器）、port号（确认进程（程序）确认Oracle）SID：一个端口可以为多个oracle数据库提供

5、监听，因此还需要提供具体的数据库名。（确认数据库里的哪个数据库）username、password：要想访问数据库，必须是该数据库上一个有效的用户。（确认身份）1.12一台机器可跑几个数据库，主要受内存大小影响1.13源表和结果集源表：被查询的表结果集：select语句的查询结果1.14几个简单命令show user：查看当前用户desc 表名：查看表结构drop table 表名 purge;删除表，Oracle中删除表不是真正的删除，而是占空间的移动到别的地方，因为为了不占空间，真正的删除需要用purge。delete from 表名：删除表中所有值；若加上where 列名=value则删

6、除某列中的值1.15 tarena给jsd1304授权connect tarena/tarenagrant select on account to jsd1304;grant select on service to jsd1304;grant select on cost to jsd1304;jsd1304 select tarena的表connect jsd1304/jsd1304create synonym 创建同义词create synonym account for tarena.account;create synonym service for tarena.service;

7、create synonym cost for tarena.cost;1.16课程中使用的5个表二、 select from语句2.1 select语句功能1）投影操作：结果集是源表中的部分“列”2）选择操作：结果集是源表中的部分“行”3）选择操作+投影操作：结果集是源表中的部分“行”部分“列”4）连接操作join：多表查询，结果集来自多张表，把多张的记录按一定条件组合起来2.2 select语句基本语法1）select colname(列名)from tabname（表名）2）select中指定多个列名，则用“逗号”分隔：select colname1,colname2 from tabn

8、ame3）* 号表示所有列：select *from tabname4）select语句：可有多个子句5）select子句：投影操作（列名）、列表达式、函数 、from子句等2.3列别名1）给列起一个别名，能够改变一个列、表达式的标识。2）不写的话默认都是转成大写。3）适合计算字段。4）在原名和别名之间可以使用as关键字。5）别名中包含空格、特数字符或希望大小写敏感的，用“”双引号将其括起来。2.4算术表达式在number类型上使用算术表达式（加减乘除）。eg：一个月使用了250小时,每种资费标准下应缴纳的费用(首次实现)select base_cost + (250 - base_durat

9、ion)*unit_cost fee from cost;2.5空值null的处理未知的，没写数1）空值不等于02）空值不等于空格3）在算术表达式中包含空值导致结果为空4）在算术表达式中包含空值需要用空值转换函数nvl处理2.6 nvl(p1,p2)函数空值转换函数1）两个参数类型要一致！2）参数的数据类型可以是数值number、字符character、日期date3）但null转成字符串，null也要用to_char()转化。4）实现过程： if p1 is null then return p2 elase return p1 end if5）实现空值转换：null非null值 0eg：一

10、个月使用了250小时,每种资费标准下应缴纳的费用(再次实现)select nvl(base_cost,0) + (250 - nvl(base_duration,0) * nvl(unit_cost,0) fee from cost;2.7拼接运算符 |表达字符（串）的拼接，可以将某几列或某列与字符串拼接在一起。select colname1|colname2 from tabname2.8文字字符串select语句后面可以包含的文字值：字符、表达式、数字。1）字符常量（或字符串）必须用单引号括起来，作为“定界符”使用。2）表达单引号本身，需要两个单引号 1，4定界2，3表单引号。3）对于文字

11、值每行输出一次。eg：显示客户姓名的身份证号是select real_name | | s IDCARD NO is | | idcard_no | | . cilent from account;4）函数转换大小写，尽量在进入数据时操作。2.9消除重复行distinct去重复行（对整条记录返回的结果去重，不是对后面的某个列去重），若后面有多列，则所有列联合起来唯一，即每列的值都可以重复，但组合不能重复。eg1：哪些unix服务器提供远程登录业务select distinct unix_host from service;eg2：每一台unix服务器在哪些天开通了远程登录业务select di

12、stinct unix_host,create_date from service;2.10其他注意事项1）调常量时用单行单列的dual表，系统提供的表。2）invalid identifier 无效标识名，列名不。3）table or view does not exist 表名不对。三、 SQL语句的处理过程3.1 SQL语句处理过程用户进程sqlplus建立连接服务进程Server process oracleSID-创建会话-Oracle server3.2处理一条select语句1）分析语句：搜索是否有相同语句用hash value计算select语句是否长得一样：大小写，关键字，空

13、格要都一样，不一样则为两条语句，则服务进程会重新分析。若为统一语句，则直接从内存拿执行计划，计算结果检查语法、表名、权限在分析过程中给对象加锁生成执行计划2）绑定变量：给变量赋值3）执行语句：4）获取数据：将数据返回给用会进程四、 where子句用where子句对表里的记录进行过滤，where子句跟在from子句后面。4.1 where子句后面可以跟什么跟条件表达式：列名、常量、比较运算符（单、多值运算符）、文字值；不能跟组函数！不能跟列别名！u 注意事项：对列不经过运算的条件表达式效率会更高，建议在写where子句时尽量不要对列进行运算。eg：一年的固定费用为70.8元，计算年包在线时长se

14、lect base_duration*12 ann_duration from cost where base_cost*12=70.8; 没下面效率高select base_duration*12 ann_duration from cost where base_cost=70.8/12; 4.2语法和执行顺序语法顺序：select from where执行顺序：from where select4.3字符串是大小写敏感的，在比较时严格区分大小写1）upper()：函数将字符串转换成大写。2）lower()：函数将字符串转换成小写。3）initcap()：函数将字符串转换成首字符大写（是将

15、列中的值大小写转换然后去和等号后的字符串比，而不是把转字符串转换去和列比）。eg：哪些unix服务器上开通了os帐号huangrselect unix_host,os_username from service where os_username = huangr;(有结果)where lower(os_username)=HUANGR;(无结果)where lower(os_username)=huangr;(有结果)where upper(os_username)=HUANGR;(有结果)4.4 where子句后面可以跟多个条件表达式条件表达式之间用and、or连接，也可用（）改变顺序。4

16、.5 between and运算符表示一个范围，是闭区间，含义为大于等于并且小于等于。eg：哪些资费的月固定费用在5元到10元之间select base_duration,base_cost,unit_cost from costwhere base_cost = 5 and base_cost = 10;where base_cost between 5 and 10;4.6 in运算符（多值运算符）表示一个集合，是离散值，含义为等于其中任意一个值，等价于any。eg：哪些资费的月固定费用是5.9元,8.5元,10.5元select base_duration,base_cost,unit_

17、cost from costwhere base_cost = 5.9 or base_cost = 8.5 or base_cost = 10.5;where base_cost in(5.9,8.5,10.5); where base_cost =any(5.9,8.5,10.5);4.7 like运算符在字符串比较中，可用like和通配符进行模糊查找。1）通配符：%表示0或多个字符； _表示任意“一个”字符（要占位的）。u 注意事项：若要查找%和_本身，则需要escape进行转移。eg：哪些unix服务器上的os帐号名是以h开头的select os_username from servi

18、ce where os_username like h%eg：哪些unix服务器上的os帐号名是以h_开头的select os_username from service where os_username like h_% escape ;4.8 is null运算符测试null值需要用is null。1） null不能用等于号“”和不等于号“”跟任何值比较，包括它自身。所以不能用“”和“”来测试是否有空值。2） 即：null=null是不成立的；null不等于 null也不成立；null和任何值比较都不成立。eg：列出月固定费用是5.9元，8.5元，10.5元或者没有月固定费。select

19、 base_duration,base_cost,unit_cost from costwhere base_cost in (5.9,8.5,10.5,null);(错误)where base_cost in (5.9,8.5,10.5) or base_cost is null;(正确)4.9比较和逻辑运算符（单值运算符）1）比较运算符：= = all：大于所有的，等价于 (select max()。2）any：大于任意的，等价于 (select min()。4.11运算符的否定形式1）比较运算符：！= =2）SQL比较运算符：not between andnot innot like i

20、s not nullu 注意事项：v in相当于=or =or =or等价于anyv not in等价于 and and and等价于allv not between and 小于下界 or 大于上界v 集合中有null，对in无影响；但对not in有影响，有一个就没有返回值！ eg：哪些资费信息的月固定费用不是5.9元,8.5元,10.5元 select base_duration,base_cost,unit_cost from cost where nvl(base_cost,0) 5.9 and nvl(base_cost,0) 8.5 and nvl(base_cost,0) 10

21、.5; where nvl(base_cost,0) not in (5.9,8.5,10.5);五、 order by子句select语句输出的结果安记录在表中的存储顺序显示，order by子句能够改变记录的输出顺序。order by子句对查询出来的结果集进行排序，即对select子句的计算结果排序。5.1语法和执行顺序语法顺序：select from where order by执行顺序：from where select order by5.2升降序ASC升序，可以省略，默认值DESC降序order by nvl(base_cost,0);order by unix_host,crea

22、te_date desc;u 注意事项：order by是select语句中最后一个子句5.3 null值在排序中显示1）被排序的列如果包含null值，用ASC方式null值的在最后；2）用DESC方式null在最前面；5.4 order by后面可以跟什么可以跟列名、列别名、列位置（数字）、表达式、函数。order by 1：表示列位置为1的列select 1 from：表示常量1eg：按年固定费用从大到小的顺序显示资费信息 方式一： select id,base_cost*12 ann_cost,base_duration ann_duration from cost order by b

23、ase_cost desc; 方式二： select id,base_cost ann_cost,base_duration ann_duration from cost order by base_cost*12 desc;排序的效果和上面是一样的，但前一个效率高。5.5多列排序order by子句后面可以跟多列，而order by后面的列可以不出现在select后面。结果集先按第一列升序排列，若列值一样，再按第二列降序排列。eg：按unix服务器ip地址升序，开通时间降序显示业务帐号信息select id,unix_host,os_username,create_date from ser

24、viceorder by unix_host,create_date desc;六、 单行函数的使用SQL函数的两种类型：单行函数、多行函数（组函数）。单行函数：数值类型、日期类型、字符类型、转换函数。处理一列数据，返回一个结果。6.1数值类型1）定义：create table tabname( c1 number,c2 number(6),c3 number(4,3),c4 number(3,-3),c5 number(2,4) );2）数值类型说明number：不写数值，表可写38位数number(6)：6位整数.1number(4,3)：数字4位，小数点占3位，四舍五入1.1.235nu

25、mber(3,-3)：小数点前三位不写数，四舍五入，然后有效位3位1234 1000number(2,4)：小数点后4位，有效位2位0.009910.00993）数值函数：参数类型为numberround()：四舍五入函数，“缺省转成数字”；也可对日期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)：406.2日期类型1）Oracle

26、用7个字节来存储日期和时间：世纪、年、月、日、时、分、秒。Date不存在定宽度，就是7个字节。2）缺省（默认）日期格式为DD-MON-RR，格式敏感。3）sysdate是一个系统函数，返回当前系统时间和日期。4）改变session（会话）中的日期格式：session和connection是同时建立的，两者是对同一件事情的不同层次的描述。connection是物理上的客户机同服务器端的通信链路；session是逻辑上的用户同服务器的通信交互，SQL语句的运行环境。eg：显示的日期包含世纪、年、月、日、时、分、秒 alter session set nls_date_format = yyyy m

27、m dd hh24: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：案例select to_char(sysdate,DDD) from dual;年中的第几天select to_char(sysdate,DD) fro

28、m dual;月中的第几天select to_char(sysdate,D) from dual;星期中的第几天6）在数据库中如何处理日期类型 create table test(c1 date); insert into test values (01-JAN-08); insert into test values (2008-08-08);（报错） insert into test values (to_date(2008-08-08,yyyy-mm-dd); select c1 from test; select to_char(c1,yyyy-mm-dd) from test;在cr

29、eate table中定义日期类型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)函数：将字符串转换成一

30、个日期值。对应java中parse。to_char(date,char)函数：第一个参数为要处理的日期，第二个参数为格式；可获取一个日期的任意一部分信息；对应java中format。eg：创建一张表，包含date类型的列，插入2008年8月8日8点8分8秒并显示。 insert into test values (to_date(2008-08-08 08:08:08,yyyy-mm-dd hh24:mi:ss); select to_char(c1,yyyy-mm-dd hh24:mi:ss) from test;u 注意事项：v 格式必须用单引号括起来，并且大小写敏感。v 必须是有效的日期

31、格式。v fm能去掉前导0和两端的空格。v 对日期去重复问题，to_char获取当天日期即可，时分秒忽略，加上distinct即可做到eg：案例 where to_char(create_date,mm)=03; 若等式右边写成3，03 =3不成立，需要在mm前增加fm。 where to_char(create_date,fmmm)=3 where to_number(to_char(create_date,mm) = 3; 若等式右边写成3，03= 3成立，03是字符类型，3是数字类型，等式两边相等，说明系统做了隐式数据转换，缺省做法将字符转化为number。8）日期函数：参数类型为dat

32、eadd_months()：一个日期加、减一个月。months_between()：两个日期之间相差多少个月。last_day()：同一个月的最后一天next_day()：根据参数，出现下一个的日期。eg1：昨天，今天，明天 alter session set nls_date_format = yyyy mm dd hh24:mi:ss; select sysdate-1,sysdate,sysdate+1 from dual;eg2：十分钟之后 alter session set nls_date_format = yyyy mm dd hh24:mi:ss; select sysdate

33、,sysdate + 1/144 from dual;eg3：每台unix服务器上的os帐号开通了多长时间（以天为单位） select unix_host,os_username,create_date,round(sysdate - create_date) days from service;eg4：上个月的今天，今天，下个月的今天 alter session set nls_date_format = yyyy mm dd hh24:mi:ss; select add_months(sysdate,-1),sysdate,add_months(sysdate,1) from dual;e

34、g5：当前月的最后一天 alter session set nls_date_format = yyyy mm dd hh24:mi:ss; select last_day(sysdate) from dual;eg6：用户注册多长时间了 select trunc(months_between(sysdate,create_date)from service;6.3字符类型1）定义：create table tabname( c1char(10) , c2 varchar2(10) );2）char和varchar2区别：varchar2必须定义长度，按字符串的实际长度存，最大长度4000字节

35、，更省空间。char可以不定义长度，默认为1，按定义长度存，最大长度2000字节，操作更快。列的取值是定长，定义成char类型。列的取值长度不固定，定义成varchar2。u 注意事项：v 在字符串比较中，varchar2按实际字符串比，对空格是敏感的，对大小些敏感。v char会将短字符串补齐后，再与字符串比，对空格不敏感。v varchar类型是ANSI定义的，varchar2类型是Oracle定义的，目前是等价的。但如果ANSI对varchar类型定义有变化，则Oracle varchar2类型不变。eg：案例 varchar2(10)：abc=abc yes ; abc=abc no

36、char(10)：abc =abc yes ; abc =abc yes3）字符函数：参数类型为字符upper()：函数将字符串转换成大写。lower()：函数将字符串转换成小写。initcap()：函数将字符串转换成首字符大写（是将列中的值大小写转换然后去和等号后的字符串比，而不是把转字符串转换去和列比）。length()：字符串的长度。rpad()、lpad()：将字符补成同样长度，l和r表左右。rtrim()、ltrim()：压缩字符，l和r表左右，与fm相同效果。concat()：拼接函数与“| |”相似。substr()：求子串函数。eg：相关操作 select rpad(FEBRA

37、RY,9, *) from dual; where to_char(create_date,fmMONTH)=MARCH; where rtrim(to_char(create_date,MONTH)=MARCH; select concat(ab ,c)from dual; 从左往右 从右往左 select os_username,substr(os_username,1,2),substr(os_username,-2,2)from service; 6.4转换函数1）to_number()函数：将字符（串）转换成number数值类型，这也是系统的缺省做法，即to_number(03)=3

38、。u 注意事项：若to_number函数处理的字符串为ab，则系统报错，若转换后的值是十进制的，则要求字符串必须是数字字符。select to_number(ab) from dual;（报错invalid number）2）to_char(date,char)函数：第一个参数为要处理的日期，第二个参数为格式；可获取一个日期的任意一部分信息；对应java中format。3）函数格式说明：9代表数字位0定义宽度大于实际宽度时，0会被强制显示在前面，以补齐位数$美元符号L本地货币符号小数点，每千位显示一个逗号u 注意事项：如果显示位数不足（定义宽度小于实际宽度），用#代替。eg1：相关操作sele

39、ct to_char(base_cost,L99.99) from cost;select to_char(base_cost,L00.00) from cost;select to_char(base_cost,$00.00) from cost;eg2：显示月固定费用，单位费用，单位费用为null，显示no unit costselect base_cost,nvl(to_char(unit_cost),no unit cost) unit_cost3）to_date(char,date)函数：将字符串转换成一个日期值。对应java中parse。4）number、字符、data间的转化to

40、_char()：number-字符 date-字符to_number()：字符- numberto_date()：字符-date5）显式隐式转换隐式数据类型转换，系统调用转换函数 where create_date like %3%;隐式显式数据类型转换，用户调用转换函数 where to_char(create_date,mm)=03;显式6.5其他注意事项1）insert into 表名 values(1，2，3，4，null)有多列时，插入值必须都写，没值的也要写null2）insert into 表名(C5) values(1234) 表名最多30个字符且不能有特殊字符3）alter

41、session set nls_language=AMERICAN;28-MAY-13 alter session set nls_language=SIMPLIFIED CHINESE;28-5月 -134）alter session set nls_territory = AMERICA; alter session set nls_territory = CHINA;七、 SQL语句中的分支7.1分支表达式1）case when (then)，用于解决不同记录需要不同处理方式的问题。when后面跟条件表达式，当所有when条件都不满足时，若有else，表达式的返回结果为其后的值，否则返回

42、null值。2）寻找when的优先级：从上到下再多的when，也只有一个出口，即其中有一个满足了表达式expr就马上退出case。3）else expr和return expr的数据类型必须相同。eg：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用CASE WHEN实现）select base_duration,unit_cost,case when base_duration=20 then unit_cost+0.05 when base_duration=40 then unit_cost+0.03 else unit_cost end new_nuit_cos

43、tfrom cost;7.2分支函数decode，是简版的case when。1）decode(value,if1,then1,if2,then2,else)标识如果value等于if1时，返回then1。如果不等于任何一个if值，则返回else。eg：当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用decode实现）select base_duration,unit_cost,decode(base_duration,20,unit_cost+0.05, 40,unit_cost+0.03, unit_cost) n_base_costfrom cost;八、 组函数操作在一组行（记录）上，每组返回一个结果。8.1报表统计常用1）avg(distinct|all|n)：平均值，参数类型只能为number。2）sum(di