Oracle数据库从零开始学习.docx

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

2、1.1 Create user 创建用户 72.1.2 Password 修改密码 72.1.3 Drop user删除用户 72.1.4 赋予和收回权限 82.1.5 Profile管理用户口令 82.1.6 给账户解锁 92.1.7 终止口令 92.1.8 口令历史 92.1.9 删除profile文件 93 数据类型 103.1 字符型 103.1.1 Char 定长字符 103.1.2 Varchar2 变长字符 103.1.3 Clob 字符型大对象 103.2 数值型 103.2.1 Number 数值型 103.3 日期 103.3.1 Date 一般日期（年、月、日，时、分、秒

3、） 103.3.2 timestamp 精确时间 103.4 图片类型 113.4.1 Blob 可存储图片、视频、声音 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.3 Distinct 取消重复行 135.

4、4 疯狂复制 135.5 Select中使用列的别名 145.6 Select中使用算术表达式 145.7 使用NVL函数处理NULL值 145.8 “|”字符串连接符 145.9 Where 查询条件子句 145.10 Like 操作符 145.11 Where 条件中使用IN 155.12 使用is null的操作符 155.13 使用逻辑操作符号 155.14 Order by排序语句 155.15 使用列的别名排序 156 复杂表查询 166.1 数据分组 max，min，avg，sum，count 166.2 Group by和having子句 166.3 数据分组总结 167 多表

5、查询 177.1 多表查询，between and语句 177.2 自连接 178 子查询 178.1 单行子查询 178.2 多行子查询 188.3 使用any操作符查询 188.4 多列子查询 188.5 From子句中使用子查询 188.6 分页查询 188.7 用查询结果创建新表 198.8 合并查询 199 Java操作Oracle 209.1 jdbc.odbc桥连接,不能远程连接 209.1.1 引sql包 209.1.2 加载驱动 209.1.3 得到连接 209.2 jdbc.Oracle连接，允许远程连接 209.2.1 引sql包 209.2.2 加载驱动 209.2.3

6、 得到连接 2010 事物 2110.1 使用子查询插入数据 2110.2 使用子查询插入数据 2110.3 Oracle中的事物 2111 函数 2211.1 字符函数 2211.1.1 lower(char):将字符串转化为小写的格式 2211.1.2 upper(char):将字符串转化为大写的格式 2211.1.3 length(char):返回字符串的长度 2211.1.4 substr(char,m,n):取字符串的子串 2211.1.5 replace(char1,serch_string,replace_string)替换字符串 2211.1.6 instr(char1,cha

7、r2,n,m)取子串在字符串的位置 2211.2 数学函数 2311.2.1 round(n,m) 四舍五入 2311.2.2 trunc(n,m) 截取数字 2311.2.3 mod(m,n) 取摩 2311.2.4 floor(n) 向下取最大整数 2311.2.5 ceil(n) 向上取最小整数 2311.2.6 abs(n) 返回数字n的绝对值 2411.2.7 acos(n) 返回数字的反余弦值 2411.2.8 asin(n) 返回数字的反正弦值 2411.2.9 atan(n) 返回数字的反正切 2411.2.10 cos(n) 返回数字的余弦值 2411.2.11 exp(n)

8、 返回e的n次幂 2411.2.12 log(m,n) 返回对数值 2411.2.13 power(m,n) 返回m的n次幂 2411.3 日期函数 2411.3.1 To_date函数 2411.3.2 sysdate 该函数返回系统时间； 2411.3.3 add_months(d,n)； 2411.3.4 last_day(d) 返回指定日期所在月份的最后一天 2511.4 转换函数 2511.4.1 To_char转换函数 2511.4.2 To_date函数 2611.5 系统函数 2611.5.1 Sys_context函数 2612 数据库管理 2712.1 数据库管理员 271

9、2.1.1 管理数据库的用户主要有：sys和system 2712.1.2 Dba、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

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

11、/SQL编程介绍 4116.1.1 PL/SQL创建存储过程 4116.1.2 PL/SQL编程分类 4216.1.3 PL/SQL编写规范 4216.1.4 PL/SQL编程块 4216.1.5 PL/SQL编程过程 4316.1.6 PL/SQL编程函数 4416.1.7 PL/SQL编程包 4516.1.8 PL/SQL编程触发器 4616.1.9 PL/SQL编程变量 4616.1.10 PL/SQL编程控制结构 5016.1.11 PL/SQL编程分页过程编写 5316.1.12 PL/SQL编程例外 6016.1.13 PL/SQL编程视图 631 基本使用Oracle安装成功后，

12、会默认生成三个用户Sys 用户：超级管理员 权限最高 它的角色dba密码change_on_installSystem 用户：是系统管理员 权限也很高 他的角色是 dbaoper 密码 managerScott用户：普通用户 密码是 tigerSys与system区别：Sys有create databse的权限，而system没有，其他相似，日常对Oracle管理过程中使用system就够了。1.1 常用命令1.1.1 Connect/Disconnect 数据库连接命令Connect 用户名、密码网络服务名as sysdba/sysoper,当用特权用户身份连接时，必须带上as sysdba

13、或是as sysoper ;Disconnect该命令用来断开与当前数据库的连接。1.1.2 PL/SQL连接数据库配置，Oracle客户端的配置文件Oracle客户端的配制文件，默认会安装在“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

14、地址,PORT端口 ) (CONNECT_DATA = (SID = YY) -YY数据库名 (SERVER = DEDICATED) )1.1.3 配置Oracle数据库监听Assistant1.1.4 Password 修改密码命令该命令用于修改用户的密码，如果想修改其他用户的密码，需要用sys/system登录1.1.5 Show 显示登录用户Show user;显示当前登录用户名1.1.6 Clear 清屏Clear;清屏1.1.7 Exit 退出命令该命令会断开与数据库的连接，同时会退出sql*plus 。 1.1.8 文件操作命令1) Start 和 运行sql脚本如：sql d:

15、a.sql 或者sqlStart d:a.sql2) Edit 该命令可以编辑指定的SQL脚本如：Sqledit d:a.sql3) Spool 该命令可以将sql*plus屏幕上的内容输出到指定的文件中去如：Sqlspool d:b.sql 并输入sqlspool off；1.1.9 & 交互式命令& 可以替代变量在执行时，需要用户输入。如：SQLselsct * from emp where job=&工作；1.1.10 显示和设置环境的变量可以用来控制输出的各种格式，set show 如果希望永久的保存相关的设置，可以去修改glogin.sql脚本1) Linsesize 设置显示行的宽

16、度，默认是80个字符Sqlshow linesizeSqlset linesize 902) Pagesize 设置每页显示的行数目，默认是14，用法和linesize一样2 用户管理2.1 用户管理 2.1.1 Create user 创建用户创建用户必须用DBA权限，或者拥有alter user系统权限，密码只能以字母开头, 新创建的用户没有任何权限，不能登录数据库，需要授权。Create user abc identified by m123；-创建用户名为abc，密码为m123的用户2.1.2 Password 修改密码给自己修改密码可以直接使用Sqlpassword 用户名如果给别人

17、修改密码则需要具有dba的权限，或是拥有alter user的系统权限Sqlalter user 用户名 identitied by 新密码2.1.3 Drop user删除用户在删除用户时，如果要删除的用户，已经创建了表，那么就需要在删除时带一个参数cascade，指删除该用户下面所有的表，一般以dba的身份去删除某个用户，如果用其他用户去删除用户则需要具有drop user的权限SqlDrop user 用户名caseade2.1.4 赋予和收回权限1) 授予权限命令grant，常用的角色有connect、dba、resources三种；Resources角色可以在任何一个表空间建表；Sq

18、lgrant connect to xiaoming;-给xiaoming授予connect角色的权限SQL revoke sysdba from xiaoming; -回收xiaoming的sysdba权限2) 如何使用户可以管理其他用户的表，Select查询、insert插入、update修改、delete删除、all全部、create index；只有sys、system和表的创建者才可以给其他用户授予表的管理权限(假设emp为Scott用户的表)；Grant select on emp to abc；-Scott用户将emp表的查询权限授予给abc用户Grant update on e

19、mp 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用户收回Revoke update on emp to abc；-Scott用户将emp表的修改权限从abc用户收回Revoke all on emp to abc；-Scott用户将emp表的所有权限从abc用户收回4) 被授予权限用户继续授予该权限给其他用户，如果是对象权限

20、，后面就加入with grant option；如果是系统权限，后面就加入with admin option；如果收回某用户的权限则该用户授予给其他下级用户该权限将全部收回Grant select on emp to abc with grant option；- Scott用户将emp表的查询权限授予给abc用户，并且让abc用户继续给其他用户授权Grant select on Scott.emp to abc1 ；abc用户将Scott.emp表的查询权限授予给abc1用户2.1.5 Profile管理用户口令Profile是口令限制，资源限制的命令集合，当建立数据库时，Oracle会自动

21、建立名为default的profile，当建立用户没有指定profile选项，那Oracle就会将default分配给用户，账户锁定:指定该账户登录时最多可以输入错误密码的次数，也可以指定用户锁定的时间(天)，一般用dba的身份去执行该命令。案例：指定abc账户登录时最多可以输入错误密码3次，锁定的时间2天Sqlcreate profile lock_a limit failed_login_attempts 3 password_lock_time 2;Sqlalter user abc profile lock_a;-给用户abc指定profile选项2.1.6 给账户解锁Sqlalter

22、 user abc account unlock;-给用户abc解锁2.1.7 终止口令为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba身份来操作。案例:创建一个profile文件，要求该用户每隔10天要修改自家的登录密码，宽限期为2天。Sqlcreate profile lock_a1 limit password_life_time 10 password_grace_time 2;Sqlalter user abc profile lock_a1;2.1.8 口令历史如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样Oracle就会将口令

23、修改的信息存放到数据字典中，这样当用户修改密码时，Oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。案例：建立profile文件, password_reuse_time /指定口令可重用时间，超过该时间可再次使用：Sqlcreate profile password_h limit password_life_time 10 password_grace_time 2 password_reuse_time 10;分配给用户：Sqlalter user abc profile password_h;2.1.9 删除profile文件删除profile文件，如果

24、该profile已经分配给客户需在后面加参数cascade，所有受到该profile文件限制的用户全部解除该限制。Sqldrop profile password_h cascade;3 数据类型3.1 字符型3.1.1 Char 定长字符char(10) 定长字符 最大2000字符，字符数不足用空格不足，查询速度快，适合定长数据，如身份证、手机号码等；3.1.2 Varchar2 变长字符varchar(20) 变长字符 最大4000字符；3.1.3 Clob 字符型大对象clob(30000) 字符型大对象，最大4G，可以存图片、视频等；3.2 数值型3.2.1 Number 数值型 nu

25、mber(5,2) 可以表示-10的38次方到10的38次方，如number(5)表示5位整数，number(5,2)表示3位整数2位小数；3.3 日期3.3.1 Date 一般日期（年、月、日，时、分、秒）包含年月日和时分秒；3.3.2 timestamp 精确时间时间可以精确到更小的单位3.4 图片类型3.4.1 Blob 可存储图片、视频、声音 blob 二进制数据，最大4G，可以存图片、视频、声音等；4 表格管理4.1 创建表(注意字母大小写，一般为大写)学生表：create table student(，-学生xh number(4), -学号xm varchar2(20),-姓名x

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

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

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

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

30、nt AA；-创建回滚点AArollback to AA;-回滚到AA点，需先创建回滚点5 简单表查询5.1 查询表的结构Sqldesc dept；-查看dept表的结构5.2 查询表的指定列和所有列Select * from emp;-查询全部列Select ename，sal，job from emp；-查询指定列注意：查询过程中尽量少用查询全部列，以节省查询时间，提高查询效率5.3 Distinct 取消重复行Select distinct deptno，job from emp；5.4 疯狂复制Insert into users(userid,username,userpass) select * from users;-向users表中添加users自己表中的数据案例1：查询SMITH 的薪水，工作，所在部门Select deptno，job，sal from emp where e