Oracle数据库从零开始学习.docx
《Oracle数据库从零开始学习.docx》由会员分享，可在线阅读，更多相关《Oracle数据库从零开始学习.docx（67页珍藏版）》请在冰豆网上搜索。
Oracle数据库从零开始学习
Oracle数据库学习
Oracle数据库学习1
1基本使用5
1.1常用命令5
1.1.1Connect/Disconnect数据库连接命令5
1.1.2PL/SQL连接数据库配置，Oracle客户端的配置文件5
1.1.3配置Oracle数据库监听Assistant6
1.1.4Passw[ord]修改密码命令6
1.1.5Show显示登录用户6
1.1.6Clear清屏6
1.1.7Exit退出命令6
1.1.8文件操作命令6
1.1.9&交互式命令6
1.1.10显示和设置环境的变量7
2用户管理7
2.1用户管理7
2.1.1Createuser创建用户7
2.1.2Password修改密码7
2.1.3Dropuser删除用户7
2.1.4赋予和收回权限8
2.1.5Profile管理用户口令8
2.1.6给账户解锁9
2.1.7终止口令9
2.1.8口令历史9
2.1.9删除profile文件9
3数据类型10
3.1字符型10
3.1.1Char定长字符10
3.1.2Varchar2变长字符10
3.1.3Clob字符型大对象10
3.2数值型10
3.2.1Number数值型10
3.3日期10
3.3.1Date一般日期（年、月、日，时、分、秒）10
3.3.2timestamp精确时间10
3.4图片类型11
3.4.1Blob可存储图片、视频、声音11
4表格管理11
4.1创建表（注意字母大小写，一般为大写）11
4.2添加一个字段11
4.3修改字段的长度11
4.4删除一个字段11
4.5修改表的名字12
4.6删除表12
4.7所有字段都插入数据12
4.8插入部分字段12
4.9插入空值12
4.10查询空值12
4.11修改字段12
4.12删除数据13
5简单表查询13
5.1查询表的结构13
5.2查询表的指定列和所有列13
5.3Distinct取消重复行13
5.4疯狂复制13
5.5Select中使用列的别名14
5.6Select中使用算术表达式14
5.7使用NVL函数处理NULL值14
5.8“||”字符串连接符14
5.9Where查询条件子句14
5.10Like操作符14
5.11Where条件中使用IN15
5.12使用isnull的操作符15
5.13使用逻辑操作符号15
5.14Orderby排序语句15
5.15使用列的别名排序15
6复杂表查询16
6.1数据分组max，min，avg，sum，count16
6.2Groupby和having子句16
6.3数据分组总结16
7多表查询17
7.1多表查询，betweenand语句17
7.2自连接17
8子查询17
8.1单行子查询17
8.2多行子查询18
8.3使用any操作符查询18
8.4多列子查询18
8.5From子句中使用子查询18
8.6分页查询18
8.7用查询结果创建新表19
8.8合并查询19
9Java操作Oracle20
9.1jdbc.odbc桥连接,不能远程连接20
9.1.1引sql包20
9.1.2加载驱动20
9.1.3得到连接20
9.2jdbc.Oracle连接，允许远程连接20
9.2.1引sql包20
9.2.2加载驱动20
9.2.3得到连接20
10事物21
10.1使用子查询插入数据21
10.2使用子查询插入数据21
10.3Oracle中的事物21
11函数22
11.1字符函数22
11.1.1lower（char）:
将字符串转化为小写的格式22
11.1.2upper（char）:
将字符串转化为大写的格式22
11.1.3length（char）:
返回字符串的长度22
11.1.4substr（char,m,n）:
取字符串的子串22
11.1.5replace（char1,serch_string,replace_string）替换字符串22
11.1.6instr（char1,char2,[,n[,m]]）取子串在字符串的位置22
11.2数学函数23
11.2.1round（n,[m]）四舍五入23
11.2.2trunc（n,[m]）截取数字23
11.2.3mod（m,n）取摩23
11.2.4floor（n）向下取最大整数23
11.2.5ceil（n）向上取最小整数23
11.2.6abs（n）返回数字n的绝对值24
11.2.7acos（n）返回数字的反余弦值24
11.2.8asin（n）返回数字的反正弦值24
11.2.9atan（n）返回数字的反正切24
11.2.10cos（n）返回数字的余弦值24
11.2.11exp（n）返回e的n次幂24
11.2.12log（m,n）返回对数值24
11.2.13power（m,n）返回m的n次幂24
11.3日期函数24
11.3.1To_date函数24
11.3.2sysdate该函数返回系统时间；24
11.3.3add_months（d,n）；24
11.3.4last_day（d）返回指定日期所在月份的最后一天25
11.4转换函数25
11.4.1To_char转换函数25
11.4.2To_date函数26
11.5系统函数26
11.5.1Sys_context函数26
12数据库管理27
12.1数据库管理员27
12.1.1管理数据库的用户主要有：
sys和system27
12.1.2Dba、Sysdba、Sysoper权限的用户27
12.1.3管理初始化参数28
12.2数据库（表）的逻辑备份和恢复28
12.2.1导出28
12.2.2导入30
12.3数据字典和动态性能视图31
12.3.1数据字典31
12.3.2数据字典-用户名、权限、角色31
12.3.3动态性能视图32
13约束32
13.1创建约束33
13.2删除约束34
13.3显示约束信息34
13.4表级定义和列级定义34
14索引、权限35
14.1索引35
14.1.1创建索引35
14.1.2索引使用原则35
14.1.3索引缺点分析35
14.1.4其他索引35
14.1.5显示表的所有索引35
14.2权限36
14.2.1系统权限36
14.2.2对象权限36
15角色37
15.1预定义角色38
15.1.1Connect角色38
15.1.2resource角色38
15.1.3dba角色38
15.2自定义角色39
15.2.1建立角色39
15.2.2角色授权39
15.2.3授予用户角色权限39
15.2.4删除角色40
15.2.5显示角色信息40
15.2.6精细访问控制40
16plsql编程41
16.1PL/SQL编程介绍41
16.1.1PL/SQL创建存储过程41
16.1.2PL/SQL编程分类42
16.1.3PL/SQL编写规范42
16.1.4PL/SQL编程—块42
16.1.5PL/SQL编程—过程43
16.1.6PL/SQL编程—函数44
16.1.7PL/SQL编程—包45
16.1.8PL/SQL编程—触发器46
16.1.9PL/SQL编程—变量46
16.1.10PL/SQL编程—控制结构50
16.1.11PL/SQL编程—分页过程编写53
16.1.12PL/SQL编程—例外60
16.1.13PL/SQL编程—视图63
1基本使用
Oracle安装成功后，会默认生成三个用户
Sys用户：
超级管理员权限最高它的角色dba密码change_on_install
System用户：
是系统管理员权限也很高他的角色是dbaoper密码manager
Scott用户：
普通用户密码是tiger
Sys与system区别：
Sys有createdatabse的权限，而system没有，其他相似，日常对Oracle管理过程中使用system就够了。
1.1常用命令
1.1.1Connect/Disconnect数据库连接命令
Conn[ect]用户名、密码@网络服务名[assysdba/sysoper],当用特权用户身份连接时，必须带上assysdba或是assysoper;
Disc[onnect]该命令用来断开与当前数据库的连接。
1.1.2PL/SQL连接数据库配置，Oracle客户端的配置文件
Oracle客户端的配制文件，默认会安装在“C:
\Oracle\ora90\network\admin”目录下，名为“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）
）
）
1.1.3配置Oracle数据库监听Assistant
1.1.4Passw[ord]修改密码命令
该命令用于修改用户的密码，如果想修改其他用户的密码，需要用sys/system登录
1.1.5Show显示登录用户
Showuser;显示当前登录用户名
1.1.6Clear清屏
Clear;清屏
1.1.7Exit退出命令
该命令会断开与数据库的连接，同时会退出sql*plus。
1.1.8文件操作命令
1）Start和@运行sql脚本
如：
sql>@d:
\a.sql或者sql>Startd:
\a.sql
2）Edit该命令可以编辑指定的SQL脚本
如：
Sql>editd:
\a.sql
3）Spool该命令可以将sql*plus屏幕上的内容输出到指定的文件中去
如：
Sql>spoold:
\b.sql并输入sql>spooloff；
1.1.9&交互式命令
&可以替代变量在执行时，需要用户输入。
如：
SQL>selsct*fromempwherejob=’&工作’；
1.1.10显示和设置环境的变量
可以用来控制输出的各种格式，setshow如果希望永久的保存相关的设置，可以去修改glogin.sql脚本
1）Linsesize设置显示行的宽度，默认是80个字符
Sql>showlinesize
Sql>setlinesize90
2）Pagesize设置每页显示的行数目，默认是14，用法和linesize一样
2用户管理
2.1用户管理
2.1.1Createuser创建用户
创建用户必须用DBA权限，或者拥有alteruser系统权限，密码只能以字母开头,新创建的用户没有任何权限，不能登录数据库，需要授权。
Createuserabcidentifiedbym123；--创建用户名为abc，密码为m123的用户
2.1.2Password修改密码
给自己修改密码可以直接使用
Sql>password用户名
如果给别人修改密码则需要具有dba的权限，或是拥有alteruser的系统权限
Sql>alteruser用户名identitiedby新密码
2.1.3Dropuser删除用户
在删除用户时，如果要删除的用户，已经创建了表，那么就需要在删除时带一个参数cascade，指删除该用户下面所有的表，一般以dba的身份去删除某个用户，如果用其他用户去删除用户则需要具有dropuser的权限
Sql>Dropuser用户名[caseade]
2.1.4赋予和收回权限
1）授予权限命令grant，常用的角色有connect、dba、resources三种；
Resources角色可以在任何一个表空间建表；
Sql>grantconnecttoxiaoming;--给xiaoming授予connect角色的权限
SQL>revokesysdbafromxiaoming;--回收xiaoming的sysdba权限
2）如何使用户可以管理其他用户的表，Select查询、insert插入、update修改、delete删除、all全部、createindex；只有sys、system和表的创建者才可以给其他用户授予表的管理权限（假设emp为Scott用户的表）；
Grantselectonemptoabc；--Scott用户将emp表的查询权限授予给abc用户
Grantupdateonemptoabc；--Scott用户将emp表的修改权限授予给abc用户
Grantallonemptoabc；--Scott用户将emp表的所有权限授予给abc用户
3）回收权限命令revoke，谁授予的权限谁收回
Revokeselectonemptoabc；--Scott用户将emp表的查询权限从abc用户收回
Revokeupdateonemptoabc；--Scott用户将emp表的修改权限从abc用户收回
Revokeallonemptoabc；--Scott用户将emp表的所有权限从abc用户收回
4）被授予权限用户继续授予该权限给其他用户，
如果是对象权限，后面就加入withgrantoption；
如果是系统权限，后面就加入withadminoption；
如果收回某用户的权限则该用户授予给其他下级用户该权限将全部收回
Grantselectonemptoabcwithgrantoption；--Scott用户将emp表的查询权限授予给abc用户，并且让abc用户继续给其他用户授权
GrantselectonScott.emptoabc1；abc用户将Scott.emp表的查询权限授予给abc1用户
2.1.5Profile管理用户口令
Profile是口令限制，资源限制的命令集合，当建立数据库时，Oracle会自动建立名为default的profile，当建立用户没有指定profile选项，那Oracle就会将default分配给用户，
账户锁定:
指定该账户登录时最多可以输入错误密码的次数，也可以指定用户锁定的时间（天），一般用dba的身份去执行该命令。
案例：
指定abc账户登录时最多可以输入错误密码3次，锁定的时间2天
Sql>createprofilelock_alimitfailed_login_attempts3password_lock_time2;
Sql>alteruserabcprofilelock_a;--给用户abc指定profile选项
2.1.6给账户解锁
Sql>alteruserabcaccountunlock;--给用户abc解锁
2.1.7终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba身份来操作。
案例:
创建一个profile文件，要求该用户每隔10天要修改自家的登录密码，宽限期为2天。
Sql>createprofilelock_a1limitpassword_life_time10password_grace_time2;
Sql>alteruserabcprofilelock_a1;
2.1.8口令历史
如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样Oracle就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，Oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
案例：
建立profile文件,password_reuse_time//指定口令可重用时间，超过该时间可再次使用：
Sql>createprofilepassword_hlimitpassword_life_time10password_grace_time2password_reuse_time10;
分配给用户：
Sql>alteruserabcprofilepassword_h;
2.1.9删除profile文件
删除profile文件，如果该profile已经分配给客户需在后面加参数cascade，所有受到该profile文件限制的用户全部解除该限制。
Sql>dropprofilepassword_h[cascade];
3数据类型
3.1字符型
3.1.1Char定长字符
char（10）定长字符最大2000字符，字符数不足用空格不足，查询速度快，适合定长数据，如身份证、手机号码等；
3.1.2Varchar2变长字符
varchar（20）变长字符最大4000字符；
3.1.3Clob字符型大对象
clob（30000）字符型大对象，最大4G，可以存图片、视频等；
3.2数值型
3.2.1Number数值型
number（5,2）可以表示-10的38次方到10的38次方，如number（5）表示5位整数，number（5,2）表示3位整数2位小数；
3.3日期
3.3.1Date一般日期（年、月、日，时、分、秒）
包含年月日和时分秒；
3.3.2timestamp精确时间
时间可以精确到更小的单位
3.4图片类型
3.4.1Blob可存储图片、视频、声音
blob二进制数据，最大4G，可以存图片、视频、声音等；
4表格管理
4.1创建表（注意字母大小写，一般为大写）
学生表：
createtablestudent（，--学生
xhnumber（4）,--学号
xmvarchar2（20）,--姓名
xbchar
（2）,--性别
birthdaydate,--出生日期
valnumber（7,2）--奖学金
）;
班级表：
createtableclass（--班级
classid（4）,--班级编号
name（20）,--班级名称
）;
4.2添加一个字段
altertablestudentadd（classidnumber
（2））;
4.3修改字段的长度
altertablestudentmodify（xmvarchar2（30））;
4.4删除一个字段
altertablestudentdropcolumnsal；
实际过程中不要轻易删除字段。
4.5修改表的名字
renamestudenttostu;--将’STUDENT’修改为’STU’
4.6删除表
droptablestudent;--删除’STUDENT’表
4.7所有字段都插入数据
insertintostudentvalues（'1003','张三','01-5月-05',10）;
日期格式默认为：
'DD-MON-YY'
该日期格式：
altersessionsetnls_dateformat='YYYY-MM-DD';
日期格式可以任意修改，Y年份，M月份，D日；
4.8插入部分字段
insertintostudent（xh,xm,sex）values（'1004','李四','女'）;
4.9插入空值
insertintostudent（xh,xm,sex,birthday）values（'1005','杨慧','女'，null）;
4.10查询空值
select*fromstudentwherebirthdayisnull；--查询空值
select*fromstudentwherebirthdayisnotnull；--查询非空值
4.11修改字段
updatestudentsetsex='男'wherexh='1004';--修改一个字段
updatestudentsetsex='男'，birthday='1980-04-01'wherexh='1004';--修改多个字段
例如：
updatestudentsetsal=sal*1.5wheresex='男';--所有男性员工薪水加1.5倍
updatestudentsetbirthdayisnullwherexh='1004';--修改为空值
4.12删除数据
deletefromstudent；--删除所有记录，表结构还在，写日志，可以恢复的（回滚），速度慢
deletefromstudentwherexh='1005';--删除一条记录
droptablestudent；--删除表结构和数据
truncatetablestudent;--删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快；
savepointAA；--创建回滚点AA
rollbacktoAA;--回滚到AA点，需先创建回滚点
5简单表查询
5.1查询表的结构
Sql>descdept；--查看dept表的结构
5.2查询表的指定列和所有列
Select*fromemp;--查询全部列
Selectename，sal，jobfromemp；--查询指定列
注意：
查询过程中尽量少用查询全部列，以节省查询时间，提高查询效率
5.3Distinct取消重复行
Selectdistinctdeptno，jobfromemp；
5.4疯狂复制
Insertintousers（userid,username,userpass）select*fromusers;--向users表中添加users自己表中的数据
案例1：
查询SMITH的薪水，工作，所在部门
Selectdeptno，job，salfromempwheree
升级会员 