Oracle数据库学习日记-实用性最强的Oracle学习总结6689.docx

《Oracle数据库学习日记-实用性最强的Oracle学习总结6689.docx》由会员分享，可在线阅读，更多相关《Oracle数据库学习日记-实用性最强的Oracle学习总结6689.docx（117页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle数据库学习Oracle数据库学习11基本使用51.1常用命命令51.1.1CConnecct/Dissconneect 数据据库连接命令令51.1.2PPL/SQLL连接数据库库配置，Orracle客客户端的配置置文件51.1.3配配置Oraccle数据库库监听Asssistannt61.1.4PPassword 修改密码命命令61.1.5SShow 显显示登录用户户61.1.6CClear 清屏61.1.7EExit 退退出命令61.1.8文文件操作命令令61.1.9& 交互式命命令61.1.10显示和设置置环境的变量72用户管理72.1用户管管理72.1.1CCreatee u

2、serr 创建用户户72.1.2PPasswoord 修改改密码72.1.3DDrop uuser删除除用户72.1.4赋赋予和收回权权限82.1.5PProfille管理用户户口令82.1.6给给账户解锁92.1.7终终止口令92.1.8口口令历史92.1.9删删除proffile文件件93数据类型103.1字符型型103.1.1CChar 定定长字符103.1.2VVarchaar2 变长长字符103.1.3CClob 字字符型大对象象103.2数值型型103.2.1NNumberr 数值型103.3日期103.3.1DDate 一一般日期（年年、月、日，时时、分、秒）103.3.2tti

3、mesttamp 精精确时间103.4图片类类型113.4.1BBlob 可可存储图片、视视频、声音114表格管理114.1创建表表(注意字母大大小写，一般般为大写)114.2添加一一个字段114.3修改字字段的长度114.4删除一一个字段114.5修改表表的名字124.6删除表表124.7所有字段都都插入数据124.8插入部部分字段124.9插入空空值124.10查询询空值124.11修改改字段124.12删除除数据135简单表查询询135.1查询表表的结构135.2查询表表的指定列和和所有列135.3Disstinctt 取消重复复行135.4疯狂复复制135.5Sellect中使使用列的

4、别名名145.6Sellect中使使用算术表达达式145.7使用NNVL函数处处理NULLL值145.8“|”字符串连连接符145.9Wheere 查询询条件子句145.10Liike 操作作符145.11Whhere 条条件中使用IIN155.12使用用is nuull的操作作符155.13使用逻辑操操作符号155.14Orrder bby排序语句句155.15使用用列的别名排排序156复杂表查询询166.1数据分分组 maxx，min，avg，sum，countt166.2Grooup byy和havinng子句166.3数据分组总总结167多表查询177.1多表查查询，bettween

5、and语句句177.2自自连接178子查询1178.1单行子子查询178.2多行子子查询188.3使用aany操作符符查询188.4多列子子查询188.5Froom子句中使使用子查询188.6分页查查询188.7用查询询结果创建新新表198.8合并查查询199Java操作作Oraclle209.1jdbbc.odbbc桥连接,不能远程连连接209.1.1引引sql包209.1.22加载驱动209.1.3得得到连接209.2jdbbc.Oraacle连接接，允许远程程连接209.2.1引引sql包209.2.2加加载驱动209.2.3得得到连接2010事物22110.1使用用子查询插入入数据21

6、10.2使用用子查询插入入数据2110.3Orracle中中的事物2111函数22211.1字符符函数2211.1.1lowerr(charr):将字符符串转化为小小写的格式2211.1.2upperr(charr):将字符符串转化为大大写的格式2211.1.3lengtth(chaar):返回回字符串的长长度2211.1.4substtr(chaar,m,nn):取字符串的的子串2211.1.5replaace(chhar1,sserch_strinng,repplace_strinng)替换字字符串2211.1.6instrr(charr1,chaar2,n,m)取子串串在字符串的的位置2

7、211.2数学学函数2311.2.1roundd(n,mm) 四舍舍五入2311.2.2truncc(n,mm) 截取取数字2311.2.3mod(mm,n) 取取摩2311.2.4floorr(n) 向向下取最大整整数2311.2.5ceil(n) 向上上取最小整数数2311.2.6abs(nn) 返回数数字n的绝对值2411.2.7acos(n) 返回回数字的反余余弦值2411.2.8asin(n) 返回回数字的反正正弦值2411.2.9atan(n) 返回回数字的反正正切2411.2.100cos(nn) 返回数数字的余弦值值2411.2.11exp(nn) 返回e的n次幂2411.2.

8、122log(mm,n) 返返回对数值2411.2.133powerr(m,n) 返回m的n次幂2411.3日期期函数2411.3.1To_daate函数2411.3.2sysdaate 该函函数返回系统统时间；2411.3.3add_mmonthss(d,n)；2411.3.4last_day(dd) 返回指指定日期所在在月份的最后后一天2511.4转换换函数2511.4.1To_chhar转换函函数2511.4.2To_daate函数2611.5系统统函数2611.5.1Sys_ccontexxt函数2612数据库管管理2712.1数据据库管理员2712.1.1管理数据库库的用户主要要有：

9、syss和systeem2712.1.2Dba、Sysdbba、Sysopper权限的的用户2712.1.3管理初始化化参数2812.2数据据库（表）的的逻辑备份和和恢复2812.2.1导出2812.2.2导入3012.3数据据字典和动态态性能视图3112.3.1数据字典3112.3.2数据字典-用户名、权权限、角色3112.3.3动态性能视视图3213约束33213.1创建建约束3313.2删除除约束3413.3显示示约束信息3413.4表级级定义和列级级定义3414索引、权权限3514.1索引引3514.1.1创建索引3514.1.2索引使用原原则3514.1.3索引缺点分分析3514.1

10、.4其他索引3514.1.5显示表的所所有索引3514.2权限限3614.2.1系统权限3614.2.2对象权限3615角色33715.1预定定义角色3815.1.1Conneect角色3815.1.2resouurce角色色3815.1.3dba角色3815.2自定定义角色3915.2.1建立角色3915.2.2角色授权3915.2.3授予用户角角色权限3915.2.4删除角色4015.2.5显示角色信信息4015.2.6精细访问控控制4016plsqql编程4116.1PL/SQQL编程介绍绍4116.1.1PL/SQQL创建存储储过程4116.1.2PL/SQQL编程分类类4216.1.

11、3PL/SQQL编写规范范4216.1.4PL/SQQL编程块4216.1.5PL/SQQL编程过程4316.1.6PL/SQQL编程函数4416.1.7PL/SQQL编程包4516.1.8PL/SQQL编程触发器4616.1.9PL/SQQL编程变量4616.1.100PL/SQQL编程控制结构5016.1.111PL/SQQL编程分页过程编编写5316.1.122PL/SQQL编程例外6016.1.133PL/SQQL编程视图63基本使用Oracle安安装成功后，会会默认生成三三个用户Sys 用户：超级管理员员 权限最高高 它的角色色dba密码码changge_on_instaallSys

12、tem 用户：是系系统管理员 权限也很高高 他的角色色是 dbaaoper 密码 maanagerrScott用户户：普通用户户 密码是 tigerrSys与sysstem区别别：Sys有creeate ddatabaase的权限限，而sysstem没有有，其他相似似，日常对OOraclee管理过程中中使用sysstem就够够了。常用命令Connectt/Discconnecct 数据库库连接命令Connecct 用户户名/密码网络络服务名aas syssdba/ssysopeer,当用用特权用户身身份连接时，必必须带上ass sysddba或是aas syssoper ;Disconnnec

13、t该命令用来来断开与当前前数据库的连连接。PL/SQL连连接数据库配配置，Oraacle客户户端的配置文文件Oracle客客户端的配制制文件，默认认会安装在“C:Orracleora900netwworkaadmin”目录下，名名为“tnsnaames.oora”参考格式如如下：YY_192.168.1.7 = - YY_1992.1688.1.7即即数据库名_数据库IPP地址 (DESCCRIPTIION = (ADDRRESS_LLIST = (ADDDRESS = (PRROTOCOOL = TTCP)(HHOST = 192.168.1.7)(PPORT = 15211) - HOST

14、数数据库IP地地址,PORRT端口 ) (COONNECTT_DATAA = (SSID = YY) -YYY数据库名 (SERVEER = DDEDICAATED) )配置Oraclle数据库监监听AssiistanttPasswoord 修修改密码命令令该命令用于修改改用户的密码码，如果想修修改其他用户户的密码，需需要用syss/systtem登录Show 显示示登录用户Show usser;显示示当前登录用用户名Clear 清清屏Clear;清清屏Exit 退出出命令该命令会断开与与数据库的连连接，同时会会退出sqll*pluss 。 文件操作命令1) Start 和和 运运行sql脚脚

15、本如：sql d:aa.sql 或者sqllStarrt d:a.sqll2) Edit 该命命令可以编辑辑指定的SQQL脚本如：Sqleedit dd:a.ssql3) Spool 该该命令可以将将sql*pplus屏幕幕上的内容输输出到指定的的文件中去(假脱机)如：Sqlsspool d:b.sql 并并输入sqllspoool offf；& 交互式命令令& 可以替代代变量在执行行时，需要用用户输入。如：SQLsselsctt * frrom emmp wheere joob=&工作；显示和设置环境境的变量可以用来控制输输出的各种格格式，sett showw 如果希望望永久的保存存相关的设

16、置置，可以去修修改gloggin.sqql脚本1) Linsesiize 设置置显示行的宽宽度，默认是是80个字符符Sqlshoow linnesizeeSqlsett lineesize 902) Pagesizze 设置每每页显示的行行数目，默认认是14，用用法和linnesizee一样用户管理用户管理 Create user 创建用户创建用户必须用用DBA权限限，或者拥有有alterr userr系统权限，密密码只能以字字母开头, 新创建的用用户没有任何何权限，不能能登录数据库库，需要授权权。Create user abc iidentiified by m1123；-创建用户名名为ab

17、c，密密码为m1223的用户Passworrd 修改密密码给自己修改密码码可以直接使使用Sqlpassswordd 用户名如果给别人修改改密码则需要要具有dbaa的权限，或或是拥有allter uuser的系系统权限Sqlaltter usser 用户户名 ideentitiied byy 新密码Drop usser删除用用户在删除用户时，如如果要删除的的用户，已经经创建了表，那那么就需要在在删除时带一一个参数caascadee，指删除该该用户下面所所有的表，一一般以dbaa的身份去删删除某个用户户，如果用其其他用户去删删除用户则需需要具有drrop usser的权限限SqlDroop use

18、er 用户名名caseeade赋予和收回权限限1) 授予权限命令ggrant，常常用的角色有有conneect、dbba、ressourcees三种；Resourcces角色可可以在任何一一个表空间建建表；Sqlgraant coonnectt to xiiaominng;-给给xiaomming授予予conneect角色的的权限SQL reevoke sysdbba from xxiaomiing; -回收xiiaominng的syssdba权限限2) 如何使用户可以以管理其他用用户的表，SSelectt查询、inssert插入入、updaate修改、dellete删除除、all全部部、cr

19、eaate inndex；只有syss、systtem和表的的创建者才可可以给其他用用户授予表的的管理权限(假设empp为Scottt用户的表表)；Grant sselectt on emp to abc；-Scottt用户将eemp表的查查询权限授予予给abc用用户Grant uupdatee on emmp to abc；-Scottt用户将eemp表的修修改权限授予予给abc用用户Grant aall onn emp to abcc；-Sccott用户户将emp表表的所有权限限授予给abbc用户3) 回收权限命令rrevokee，谁授予的的权限谁收回回Revoke selecct on

20、emmp to abc；-Scottt用户将eemp表的查查询权限从aabc用户收收回Revoke updatte on emmp to abc；-Scottt用户将eemp表的修修改权限从aabc用户收收回Revoke all onn emp to abcc；-Sccott用户户将emp表表的所有权限限从abc用用户收回4) 被授予权限用户户继续授予该该权限给其他他用户，如果是对象权限限，后面就加入witth graant opption；如果是系统权限限，后面就加加入withh admiin opttion；如果收回某用户户的权限则该用户户授予给其他他下级用户该该权限将全部部收回Grant

21、 sselectt on empp to abcc with grantt optiion；- Scoott用户将将emp表的的查询权限授授予给abcc用户，并且且让abc用用户继续给其其他用户授权权Grant sselectt on Scoott.emmp to abc1 ；abc用用户将Scoott.emmp表的查询询权限授予给给abc1用用户Profilee管理用户口口令Profilee是口令限制制，资源限制制的命令集合合，当建立数数据库时，OOraclee会自动建立立名为deffault的的profiile，当建建立用户没有有指定proofile选选项，那Orracle就就会将deff

22、ault分分配给用户，账户锁定:指定定该账户登录录时最多可以以输入错误密码的次次数，也可以以指定用户锁锁定的时间(天)，一般般用dba的的身份去执行行该命令。案例：指定abbc账户登录录时最多可以以输入错误密密码3次，锁锁定的时间22天Sqlcreeate pprofille locck_a limitt failled_loogin_aattemppts 3 passwword_llock_ttime 22;Sqlaltter usser abbc proofile lock_a;-给用户aabc指定pprofille选项给账户解锁Sqlaltter usser abbc acccount

23、unlocck;-给用户户abc解锁锁终止口令为了让用户定期期修改密码可可以使用终止止口令的指令令来完成，同同样这个命令令也需要dbba身份来操操作。案例:创建一个个profiile文件，要要求该用户每每隔10天要要修改自家的的登录密码，宽宽限期为2天天。Sqlcreeate pprofille locck_a1 limitt passsword_life_time 10 paassworrd_graace_tiime 2;Sqlaltter usser abbc proofile lock_a1;口令历史如果希望用户在在修改密码时时，不能使用用以前使用过过的密码，可可使用口令历历史，这样OO

24、raclee就会将口令令修改的信息息存放到数据据字典中，这这样当用户修修改密码时，OOraclee就会对新旧旧密码进行比比较，当发现现新旧密码一一样时，就提提示用户重新新输入密码。案例：建立profiile文件, passsword_reusee_timee /指定定口令可重用用时间，超过过该时间可再再次使用：Sqlcreeate pprofille passswordd_h liimit ppasswoord_liife_tiime 100 passsword_gracee_timee 2 passswordd_reusse_timme 10;分配给用户：Sqlaltter usser ab

25、bc proofile passwword_hh;删除profiile文件删除profiile文件，如果该profile已经分配给客户需在后面加参数cascade，所有受到该profile文件限制的用户全部解除该限制。Sqldroop proofile passwword_hh cascaade;数据类型字符型Char 定长长字符char(100) 定长字字符 最大22000字符符，字符数不不足用空格不不足，查询速速度快，适合合定长数据，如如身份证、手手机号码等；Varcharr2 变长字字符varcharr(20) 变长字符 最大40000字符；Clob 字符符型大对象clob(300000

26、) 字符型大对对象，最大44G，可以存存图片、视频频等；数值型Number 数值型 nuumber(5,2) 可以表示-10的388次方到100的38次方方，如nummber(55)表示5位位整数，nuumber(5,2)表表示3位整数数2位小数；日期Date 一般般日期（年、月月、日，时、分分、秒）包含年月日和时时分秒；timestaamp 精确确时间时间可以精确到到更小的单位位图片类型Blob 可存存储图片、视视频、声音 bloob 二进制制数据，最大大4G，可以存存图片、视频频、声音等；表格管理创建表(注意字字母大小写，一一般为大写)学生表：create tablee studeent(

27、，-学生xh numbber(4), -学学号xm varcchar2(20),-姓名xb charr(2),-性别birthdaay datte,-出出生日期val nummber(77,2)-奖学金);班级表：create tablee classs(-班级classidd(4),-班级编号号name(200),-班班级名称);添加一个字段alter ttable studeent addd(claassid numbeer(2);修改字段的长度度alter ttable studennt modiffy (xmm varcchar2(30);删除一个字段alter ttable stud

28、eent drrop coolumn sal；实际过程中不要要轻易删除字字段。修改表的名字rename studeent to stu;-将STUDEENT修改为STU删除表drop taable sstudennt;-删除STUDEENT表所有字段都插入入数据insert into studeent vaalues(10033,张三三,011-5月-005,100);日期格式默认为为：DD-MON-YYY该日期格式： alterr sesssion sset nlls_datte forrmat =YYYYY-MM-DDD;日期格式可以任任意修改，YY年份，M月月份，D日；插入部分字段ins

29、ert into studeent (xxh,xm,sex) valuees (11004,李四,女);插入空值insert into studeent (xxh,xm,sex,bbirthdday) vvaluess (10005,杨慧,女，nuull);查询空值select * froom stuudent wheree birtthday is nuull；-查询空空值select * froom stuudent wheree birtthday is noot nulll；-查询非非空值修改字段update studeent seet sexx=男 wheree xh=1004; -

30、修修改一个字段段update studeent seet sexx=男，bbirthdday=11980-004-01 wherre xh=10044; -修改多个字字段例如：updaate sttudentt set ssal=saal*1.55 where sex=男; -所有男性性员工薪水加加1.5倍update studeent seet birrthdayy is nnull wwhere xh=11004; -修改改为空值删除数据delete from studeent；-删除所有记记录，表结构构还在，写日日志，可以恢恢复的(回滚滚)，速度慢慢delete from studeen

31、t whhere xxh=10005; -删除一一条记录drop taable sstudennt；-删删除表结构和和数据truncatte tabble sttudentt;-删除表中中的所有记录录，表结构还还在，不写日日志，无法找找回删除的记记录，速度快快；savepoiint AAA；-创建建回滚点AAArollbacck to AA;-回滚到AAA点，需先创创建回滚点简单表查询查询表的结构Sqldessc deppt；-查查看deptt表的结构查询表的指定列列和所有列Select * froom empp;-查询询全部列Select enamee，sal，jjob frrom emmp

32、；-查查询指定列注意：查询过程程中尽量少用用查询全部列列，以节省查查询时间，提提高查询效率率Distincct 取消重重复行Select distiinct ddeptnoo，job from eemp；疯狂复制Insert into userss(userrid,ussernamme,useerpasss) sellect * fromm userrs;-向向userss表中添加uusers自自己表中的数数据案例1：查询SSMITH 的薪水，工工作，所在部部门Select deptnno，jobb，sal from eemp whhere eename=SMITHH；注意：Seleect语句

33、中中的大小写不不区分，但是是单引号中的的内区分大小小写Select中中使用列的别别名Select ename “姓名”， sal*12 ass “年收入” from eemp;-从emp表表中查询enname别名名“姓名”、sal*112别名“年收入”Select中中使用算术表表达式注意：在sellect运算算过程中如果果其中有一个个值为NULLL则整个值值为NULLLSelect enamee ，sall*12 frrom emmp;-查询年工工资Select sal*112+commm*12 “年总工资”,enamme，commm froom empp；-年总总工资等于年年工资加年奖奖金可

34、以使用 +，-，*，/ 进行运算使用NVL函数数处理NULLL值Nvl(commm,0);如果comm值值为空（NUULL）则返返回值为后面面的0，如果果comm值值不为空则返返回commm的值Select sal*112+nvll(commm,0)*112 “年总工资”,enamme，commm froom empp；-年总总工资等于年年工资加年奖奖金“|”字符串串连接符Select enamee |是| joob froom empp；-将查查询出的值和和字符串连接接在一起Where 查查询条件19948.788+120子句案例1：如何显显示工资高于于3000的的员工Select enam

35、ee,sal from emp whhere ssal30000;案例2：如何查查找19822.1.1后后入职的员工工Select enamee,hireedate from emp whhere hhiredaate1-1月-11981；案例3：如何显显示工资在22000到22500的员员工情况Select enamee,sal from emp whhere ssal=22000 aand saal500 oor jobb=MANAGGER) and enamee likee J%;Order bby排序语句句Order bby 默认升升序(ascc)，降序(desc) 。如何按照工资的的

36、从低到高的的顺序显示雇雇员的信息Select * froom empp ordeer by sal;按照部门号升序序而雇员的工资资降序排列Select * froom empp ordeer by deptnno， sal deesc;使用列的别名排排序注意：别名需要要加双引号” ，英文不用用加双引号，给给列加别名时时中间可以加加as 。Select enamee,sal*12 “年薪” from eemp orrder bby “年薪” asc;复杂表查询数据分组 maax，minn，avg，ssum，coount显示所有员工中中最高工资和和最低工资Select max(ssal),mmin

37、(saal) frrom emmp;显示工资最高员员工的名字，工工作岗位Select enamee,sal from eemp whhere ssal=(SSelectt max(sal) from eemp)显示所有员工的的平均工资和和工资总和Select avg(ssal),ssum(saal) frrom emmp;计算共有多少员员工Select countt(*) from emp；显示工资高于平平均工资的员员工信息Select * froom empp wherre sall(Selectt avg(sal) from eemp);Group bby和havving子句句Group

38、bby用于对查查询的结果分分组统计,分分组查询中分分组字段必须须出现在查询询结果中Having子子句用于限制制分组显示结结果显示每个部门的的平均工资和和最高工资Select avg(ssal),mmax(saal),deeptno from eemp grroup bby depptno;显示每个部门的的每种岗位的的平均工资和和最低工资Select avg(ssal),mmin(sall),depptno，jjob frrom emmp grooup byy depttno，joob;显示平均工资低低于20000的部门号和和它的平均工工资Select avg(ssal),mmax(saal),

39、deeptno from eemp grroup bby depptno hhavingg avg(sal)2000;数据分组总结1、 分组函数只能出出现在选择列列表、havving、oorder by子句中中；2、 如果在seleect语句中中同时包含ggroup by，haaving，oorder by那么他他们的顺序是是groupp by，hhavingg，ordeer by ；3、 在选择列中如果果有列、表达达式和分组函函数，那么这这些列和表达达式必须有一一个出现在ggroup by子句中中，否则就会会出错案例1:显示平平均工资低于于2000的的部门号和它它的平均工资资并按平均工工资排

40、序Select avg(ssal),mmax(saal),deeptno from eemp grroup bby depptno hhavingg avg(sal)2000 orderr by aavg(saal);多表查询多表查询，beetweenn and语语句多表查询是指基基于两个和两两个以上的表表或是视图的的查询,多表表查询查询条条件不能少于于表的个数减减1 。案例1：显示雇雇员名，雇员员工资及所在在部门的名字字；【笛卡尔尔集】Select a1.enname,aa1.sall,a2.ddname from eemp a11 deptt a2 wheree a1.ddeptnoo=a

41、2.ddeptnoo;案例2：显示部部门号为100的部门名、员员工名和工资资Select a1.enname,aa1.sall,a2.ddname from eemp a11 deptt a2 wheree a1.ddeptnoo=a2.ddeptnoo and a1.deeptno=10; 案例3：显示部部门号为100的部门名、员员工名和工资资Select a1.enname,aa1.sall,a2.ggrade from eemp a11,salggrade a2 beetweenn a2.llosal and a22.hisaal;案例4：显示雇雇员名、雇员员工资及所在在部门的名字字，并按部门门排序Select a1.enname,aa1.sall,a2.ddname from eemp a11 deptt a2 wheree a1.ddeptnoo=a2.ddeptnoo ordeer by a1.deeptno;自连接自连接是指在同同一张表的链链接查询显示FORDD上级的姓名名Select a1.enname，aa2.enaame frrom a11.emp，aa2.empp wheree a1.mmgr=a22.empnno andd a1.eename=FORDSavepoint;左连接和右连接接左连接和右连接接以如下方式式来实现：查看如下语句： SELECT