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

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

1、Oracle数据库学习Oracle数据库学习11基本使用51.1常用命令51.1.1Connect/Disconnect 数据库连接命令51.1.2PL/SQL连接数据库配置，Oracle客户端的配置文件51.1.3配置Oracle数据库监听Assistant61.1.4Password 修改密码命令61.1.5Show 显示登录用户61.1.6Clear 清屏61.1.7Exit 退出命令61.1.8文件操作命令61.1.9& 交互式命令61.1.10显示和设置环境的变量72用户管理72.1用户管理72.1.1Create user 创建用户72.1.2Password 修改密码72.1.3

2、Drop user删除用户72.1.4赋予和收回权限82.1.5Profile管理用户口令82.1.6给账户解锁92.1.7终止口令92.1.8口令历史92.1.9删除profile文件93数据类型103.1字符型103.1.1Char 定长字符103.1.2Varchar2 变长字符103.1.3Clob 字符型大对象103.2数值型103.2.1Number 数值型103.3日期103.3.1Date 一般日期（年、月、日，时、分、秒）103.3.2timestamp 精确时间103.4图片类型113.4.1Blob 可存储图片、视频、声音114表格管理114.1创建表(注意字母大小写，一

3、般为大写)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.3Distinct 取消重复行135.4疯狂复制135.5Select中使用列的别名145.6Select中使用算术表达式145.7使用NVL函数处理NULL值145.8“|”字符串连接符145.9Where 查询条件子句145.10Like 操作符145.11Where 条件

4、中使用IN155.12使用is null的操作符155.13使用逻辑操作符号155.14Order by排序语句155.15使用列的别名排序156复杂表查询166.1数据分组 max，min，avg，sum，count166.2Group by和having子句166.3数据分组总结167多表查询177.1多表查询，between and语句177.2自连接178子查询178.1单行子查询178.2多行子查询188.3使用any操作符查询188.4多列子查询188.5From子句中使用子查询188.6分页查询188.7用查询结果创建新表198.8合并查询199Java操作Oracle209.1

5、jdbc.odbc桥连接,不能远程连接209.1.1引sql包209.1.2加载驱动209.1.3得到连接209.2jdbc.Oracle连接，允许远程连接209.2.1引sql包209.2.2加载驱动209.2.3得到连接2010事物2110.1使用子查询插入数据2110.2使用子查询插入数据2110.3Oracle中的事物2111函数2211.1字符函数2211.1.1lower(char):将字符串转化为小写的格式2211.1.2upper(char):将字符串转化为大写的格式2211.1.3length(char):返回字符串的长度2211.1.4substr(char,m,n):取字

6、符串的子串2211.1.5replace(char1,serch_string,replace_string)替换字符串2211.1.6instr(char1,char2,n,m)取子串在字符串的位置2211.2数学函数2311.2.1round(n,m) 四舍五入2311.2.2trunc(n,m) 截取数字2311.2.3mod(m,n) 取摩2311.2.4floor(n) 向下取最大整数2311.2.5ceil(n) 向上取最小整数2311.2.6abs(n) 返回数字n的绝对值2411.2.7acos(n) 返回数字的反余弦值2411.2.8asin(n) 返回数字的反正弦值2411

7、.2.9atan(n) 返回数字的反正切2411.2.10cos(n) 返回数字的余弦值2411.2.11exp(n) 返回e的n次幂2411.2.12log(m,n) 返回对数值2411.2.13power(m,n) 返回m的n次幂2411.3日期函数2411.3.1To_date函数2411.3.2sysdate 该函数返回系统时间；2411.3.3add_months(d,n)；2411.3.4last_day(d) 返回指定日期所在月份的最后一天2511.4转换函数2511.4.1To_char转换函数2511.4.2To_date函数2611.5系统函数2611.5.1Sys_con

8、text函数2612数据库管理2712.1数据库管理员2712.1.1管理数据库的用户主要有：sys和system2712.1.2Dba、Sysdba、Sysoper权限的用户2712.1.3管理初始化参数2812.2数据库（表）的逻辑备份和恢复2812.2.1导出2812.2.2导入3012.3数据字典和动态性能视图3112.3.1数据字典3112.3.2数据字典-用户名、权限、角色3112.3.3动态性能视图3213约束3213.1创建约束3313.2删除约束3413.3显示约束信息3413.4表级定义和列级定义3414索引、权限3514.1索引3514.1.1创建索引3514.1.2索引

9、使用原则3514.1.3索引缺点分析3514.1.4其他索引3514.1.5显示表的所有索引3514.2权限3614.2.1系统权限3614.2.2对象权限3615角色3715.1预定义角色3815.1.1Connect角色3815.1.2resource角色3815.1.3dba角色3815.2自定义角色3915.2.1建立角色3915.2.2角色授权3915.2.3授予用户角色权限3915.2.4删除角色4015.2.5显示角色信息4015.2.6精细访问控制4016plsql编程4116.1PL/SQL编程介绍4116.1.1PL/SQL创建存储过程4116.1.2PL/SQL编程分类4

10、216.1.3PL/SQL编写规范4216.1.4PL/SQL编程块4216.1.5PL/SQL编程过程4316.1.6PL/SQL编程函数4416.1.7PL/SQL编程包4516.1.8PL/SQL编程触发器4616.1.9PL/SQL编程变量4616.1.10PL/SQL编程控制结构5016.1.11PL/SQL编程分页过程编写5316.1.12PL/SQL编程例外6016.1.13PL/SQL编程视图63基本使用Oracle安装成功后，会默认生成三个用户Sys 用户：超级管理员 权限最高 它的角色dba密码change_on_installSystem 用户：是系统管理员 权限也很高

11、他的角色是 dbaoper 密码 managerScott用户：普通用户 密码是 tigerSys与system区别：Sys有create database的权限，而system没有，其他相似，日常对Oracle管理过程中使用system就够了。常用命令Connect/Disconnect 数据库连接命令Connect 用户名/密码网络服务名as sysdba/sysoper,当用特权用户身份连接时，必须带上as sysdba或是as sysoper ;Disconnect该命令用来断开与当前数据库的连接。PL/SQL连接数据库配置，Oracle客户端的配置文件Oracle客户端的配制文件，默

12、认会安装在“C:Oracleora90networkadmin”目录下，名为“tnsnames.ora”参考格式如下：YY_192.168.1.7 = - YY_192.168.1.7即数据库名_数据库IP地址 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1521) - HOST数据库IP地址,PORT端口 ) (CONNECT_DATA = (SID = YY) -YY数据库名 (SERVER = DEDICATED) )配置Oracle数据库监听Assistant

13、Password 修改密码命令该命令用于修改用户的密码，如果想修改其他用户的密码，需要用sys/system登录Show 显示登录用户Show user;显示当前登录用户名Clear 清屏Clear;清屏Exit 退出命令该命令会断开与数据库的连接，同时会退出sql*plus 。 文件操作命令1) Start 和 运行sql脚本如：sql d:a.sql 或者sqlStart d:a.sql2) Edit 该命令可以编辑指定的SQL脚本如：Sqledit d:a.sql3) Spool 该命令可以将sql*plus屏幕上的内容输出到指定的文件中去(假脱机)如：Sqlspool d:b.sql

14、并输入sqlspool off；& 交互式命令& 可以替代变量在执行时，需要用户输入。如：SQLselsct * from emp where job=&工作；显示和设置环境的变量可以用来控制输出的各种格式，set show 如果希望永久的保存相关的设置，可以去修改glogin.sql脚本1) Linsesize 设置显示行的宽度，默认是80个字符Sqlshow linesizeSqlset linesize 902) Pagesize 设置每页显示的行数目，默认是14，用法和linesize一样用户管理用户管理 Create user 创建用户创建用户必须用DBA权限，或者拥有alter u

15、ser系统权限，密码只能以字母开头, 新创建的用户没有任何权限，不能登录数据库，需要授权。Create user abc identified by m123；-创建用户名为abc，密码为m123的用户Password 修改密码给自己修改密码可以直接使用Sqlpassword 用户名如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限Sqlalter user 用户名 identitied by 新密码Drop user删除用户在删除用户时，如果要删除的用户，已经创建了表，那么就需要在删除时带一个参数cascade，指删除该用户下面所有的表，一般以dba的身份去删除

16、某个用户，如果用其他用户去删除用户则需要具有drop user的权限SqlDrop user 用户名caseade赋予和收回权限1) 授予权限命令grant，常用的角色有connect、dba、resources三种；Resources角色可以在任何一个表空间建表；Sqlgrant connect to xiaoming;-给xiaoming授予connect角色的权限SQL revoke sysdba from xiaoming; -回收xiaoming的sysdba权限2) 如何使用户可以管理其他用户的表，Select查询、insert插入、update修改、delete删除、all全部、

17、create index；只有sys、system和表的创建者才可以给其他用户授予表的管理权限(假设emp为Scott用户的表)；Grant select on emp to abc；-Scott用户将emp表的查询权限授予给abc用户Grant update on emp to abc；-Scott用户将emp表的修改权限授予给abc用户Grant all on emp to abc；-Scott用户将emp表的所有权限授予给abc用户3) 回收权限命令revoke，谁授予的权限谁收回Revoke select on emp to abc；-Scott用户将emp表的查询权限从abc用户收回

18、Revoke update on emp to abc；-Scott用户将emp表的修改权限从abc用户收回Revoke all on emp to abc；-Scott用户将emp表的所有权限从abc用户收回4) 被授予权限用户继续授予该权限给其他用户，如果是对象权限，后面就加入with grant option；如果是系统权限，后面就加入with admin option；如果收回某用户的权限则该用户授予给其他下级用户该权限将全部收回Grant select on emp to abc with grant option；- Scott用户将emp表的查询权限授予给abc用户，并且让abc

19、用户继续给其他用户授权Grant select on Scott.emp to abc1 ；abc用户将Scott.emp表的查询权限授予给abc1用户Profile管理用户口令Profile是口令限制，资源限制的命令集合，当建立数据库时，Oracle会自动建立名为default的profile，当建立用户没有指定profile选项，那Oracle就会将default分配给用户，账户锁定:指定该账户登录时最多可以输入错误密码的次数，也可以指定用户锁定的时间(天)，一般用dba的身份去执行该命令。案例：指定abc账户登录时最多可以输入错误密码3次，锁定的时间2天Sqlcreate profile

20、 lock_a limit failed_login_attempts 3 password_lock_time 2;Sqlalter user abc profile lock_a;-给用户abc指定profile选项给账户解锁Sqlalter user abc account unlock;-给用户abc解锁终止口令为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba身份来操作。案例:创建一个profile文件，要求该用户每隔10天要修改自家的登录密码，宽限期为2天。Sqlcreate profile lock_a1 limit password_life_time

21、 10 password_grace_time 2;Sqlalter user abc profile lock_a1;口令历史如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样Oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，Oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。案例：建立profile文件, password_reuse_time /指定口令可重用时间，超过该时间可再次使用：Sqlcreate profile password_h limit password_life_time 10 password

22、_grace_time 2 password_reuse_time 10;分配给用户：Sqlalter user abc profile password_h;删除profile文件删除profile文件，如果该profile已经分配给客户需在后面加参数cascade，所有受到该profile文件限制的用户全部解除该限制。Sqldrop profile password_h cascade;数据类型字符型Char 定长字符char(10) 定长字符 最大2000字符，字符数不足用空格不足，查询速度快，适合定长数据，如身份证、手机号码等；Varchar2 变长字符varchar(20) 变长字符

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

24、学生xh number(4), -学号xm varchar2(20),-姓名xb char(2),-性别birthday date,-出生日期val number(7,2)-奖学金);班级表：create table class(-班级classid(4),-班级编号name(20),-班级名称);添加一个字段alter table student add(classid number(2);修改字段的长度alter table student modify (xm varchar2(30);删除一个字段alter table student drop column sal；实际过程中不要轻易

25、删除字段。修改表的名字rename student to stu;-将STUDENT修改为STU删除表drop table student;-删除STUDENT表所有字段都插入数据insert into student values(1003,张三,01-5月-05,10);日期格式默认为：DD-MON-YY该日期格式： alter session set nls_date format =YYYY-MM-DD;日期格式可以任意修改，Y年份，M月份，D日；插入部分字段insert into student (xh,xm,sex) values (1004,李四,女);插入空值insert in

26、to student (xh,xm,sex,birthday) values (1005,杨慧,女，null);查询空值select * from student where birthday is null；-查询空值select * from student where birthday is not null；-查询非空值修改字段update student set sex=男 where xh=1004; -修改一个字段update student set sex=男，birthday=1980-04-01 where xh=1004; -修改多个字段例如：update student

27、 set sal=sal*1.5 where sex=男; -所有男性员工薪水加1.5倍update student set birthday is null where xh=1004; -修改为空值删除数据delete from student；-删除所有记录，表结构还在，写日志，可以恢复的(回滚)，速度慢delete from student where xh=1005; -删除一条记录drop table student；-删除表结构和数据truncate table student;-删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快；savepoint AA；-创

28、建回滚点AArollback to AA;-回滚到AA点，需先创建回滚点简单表查询查询表的结构Sqldesc dept；-查看dept表的结构查询表的指定列和所有列Select * from emp;-查询全部列Select ename，sal，job from emp；-查询指定列注意：查询过程中尽量少用查询全部列，以节省查询时间，提高查询效率Distinct 取消重复行Select distinct deptno，job from emp；疯狂复制Insert into users(userid,username,userpass) select * from users;-向users表

29、中添加users自己表中的数据案例1：查询SMITH 的薪水，工作，所在部门Select deptno，job，sal from emp where ename=SMITH；注意：Select语句中的大小写不区分，但是单引号中的内区分大小写Select中使用列的别名Select ename “姓名”， sal*12 as “年收入” from emp;-从emp表中查询ename别名“姓名”、sal*12别名“年收入”Select中使用算术表达式注意：在select运算过程中如果其中有一个值为NULL则整个值为NULLSelect ename ，sal*12 from emp;-查询年工资Se

30、lect sal*12+comm*12 “年总工资”,ename，comm from emp；-年总工资等于年工资加年奖金可以使用 +，-，*，/ 进行运算使用NVL函数处理NULL值Nvl(comm,0);如果comm值为空（NULL）则返回值为后面的0，如果comm值不为空则返回comm的值Select sal*12+nvl(comm,0)*12 “年总工资”,ename，comm from emp；-年总工资等于年工资加年奖金“|”字符串连接符Select ename |是| job from emp；-将查询出的值和字符串连接在一起Where 查询条件1948.78+120子句案例1：

31、如何显示工资高于3000的员工Select ename,sal from emp where sal3000;案例2：如何查找1982.1.1后入职的员工Select ename,hiredate from emp where hiredate1-1月-1981；案例3：如何显示工资在2000到2500的员工情况Select ename,sal from emp where sal=2000 and sal500 or job=MANAGER) and ename like J%;Order by排序语句Order by 默认升序(asc)，降序(desc) 。如何按照工资的从低到高的顺序显示

32、雇员的信息Select * from emp order by sal;按照部门号升序而雇员的工资降序排列Select * from emp order by deptno， sal desc;使用列的别名排序注意：别名需要加双引号” ，英文不用加双引号，给列加别名时中间可以加as 。Select ename,sal*12 “年薪” from emp order by “年薪” asc;复杂表查询数据分组 max，min，avg，sum，count显示所有员工中最高工资和最低工资Select max(sal),min(sal) from emp;显示工资最高员工的名字，工作岗位Select e

33、name,sal from emp where sal=(Select max(sal) from emp)显示所有员工的平均工资和工资总和Select avg(sal),sum(sal) from emp;计算共有多少员工Select count(*) from emp；显示工资高于平均工资的员工信息Select * from emp where sal(Select avg(sal) from emp);Group by和having子句Group by用于对查询的结果分组统计,分组查询中分组字段必须出现在查询结果中Having子句用于限制分组显示结果显示每个部门的平均工资和最高工资Sel

34、ect avg(sal),max(sal),deptno from emp group by deptno;显示每个部门的每种岗位的平均工资和最低工资Select avg(sal),min(sal),deptno，job from emp group by deptno，job;显示平均工资低于2000的部门号和它的平均工资Select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)2000;数据分组总结1、 分组函数只能出现在选择列表、having、order by子句中；2、 如果在select语句中同时包

35、含group by，having，order by那么他们的顺序是group by，having，order by ；3、 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错案例1:显示平均工资低于2000的部门号和它的平均工资并按平均工资排序Select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)2000 order by avg(sal);多表查询多表查询，between and语句多表查询是指基于两个和两个以上的表或是视图的查询,多表查询查询条

36、件不能少于表的个数减1 。案例1：显示雇员名，雇员工资及所在部门的名字；【笛卡尔集】Select a1.ename,a1.sal,a2.dname from emp a1 dept a2 where a1.deptno=a2.deptno;案例2：显示部门号为10的部门名、员工名和工资Select a1.ename,a1.sal,a2.dname from emp a1 dept a2 where a1.deptno=a2.deptno and a1.deptno=10; 案例3：显示部门号为10的部门名、员工名和工资Select a1.ename,a1.sal,a2.grade from e

37、mp a1,salgrade a2 between a2.losal and a2.hisal;案例4：显示雇员名、雇员工资及所在部门的名字，并按部门排序Select a1.ename,a1.sal,a2.dname from emp a1 dept a2 where a1.deptno=a2.deptno order by a1.deptno;自连接自连接是指在同一张表的链接查询显示FORD上级的姓名Select a1.ename，a2.ename from a1.emp，a2.emp where a1.mgr=a2.empno and a1.ename=FORDSavepoint;左连接和

38、右连接左连接和右连接以如下方式来实现：查看如下语句： SELECT emp_name, dept_name FORM Employee, Department WHERE Employee.emp_deptid(+) = Department.deptid 此SQL使用了右连接，即“(+)”所在位置的另一侧为连接的方向，右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配，也就是说上例中无论会不会出现某个部门没有一个员工的情况，这个部门的名字都会在查询结果中出现。 反之： SELECT emp_name, dept_name FORM Employee, Department WH

39、ERE Employee.emp_deptid = Department.deptid(+)则是左连接，无论这个员工有没有一个能在Department表中得到匹配的部门号，这个员工的记录都会被显示一般的相等连接select * from a,b where a.id = b.id;这个是内连接子查询子查询是指嵌入其他sql语句中的select语句，也叫嵌套查询注意：数据库在执行sql时是从后往前执行，有括号先执行括号里面的语句，包含多个条件时，尽量将能过滤数据量大的条件放在后面，以提高执行效率单行子查询单行子查询是指子语句只返回一行数据的子查询语句如：显示与SMITH同一部门的所有员工SELE

40、CT * from emp where deptno=(Select deptno from emp where ename=SMITH);多行子查询多行子查询指子语句返回多行数据的子查询如：查询和部门10的工作相同雇员的名字、岗位、工资、部门号SELECT * from emp where job in (Select distinct job from emp where deptno=10)显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号Select ename，sal，deptno from emp where salall (select sal from emp wh

41、ere deptno=3);Select ename，sal，deptno from emp where sal(select max(sal) from emp where deptno=3);-效率比第一种高使用any操作符查询显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号Select ename，sal，deptno from emp where salany(select sal from emp where deptno=30);Select ename，sal，deptno from emp where sal(select min(sal) from emp

42、where deptno=30);多列子查询多列子查询是指查询返回多个列数据的子查询语句查询与SMITH的部门和岗位完全相同的所有雇员Select * from EMP where (deptno,job)=(select deptno,job from emp where ename=SMITH);-注意列名前后顺序对应From子句中使用子查询当在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌图，当在from子句中使用子查询时，必须给予查询指定别名。案例1：显示高于自己部门平均工资员工的信息Select a1.ename,a1.sal,a1.deptno,a2.my

43、sal from emp a1,(Select avg(sal) mysal,deptno from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sala2.mysal;分页查询Oracle分页方式有三种：1、rownum第一步：Select a1.*，rownum rn from (select * from emp) a1 where rownum=10;-找出前10行记录;第二步：Select * from （Select a1.*，rownum rn from (select * from emp) a1 where rownum=10） wh