最新Oracle11g教案.docx
- 文档编号:12848997
- 上传时间:2023-04-22
- 格式:DOCX
- 页数:41
- 大小:35.55KB
最新Oracle11g教案.docx
《最新Oracle11g教案.docx》由会员分享,可在线阅读,更多相关《最新Oracle11g教案.docx(41页珍藏版)》请在冰豆网上搜索。
最新Oracle11g教案
Oracle11g教案
一.Oracle入门
1.安装Oracle11g
2.验证Oracle11g是否安装成功
在控制面板中查看2个服务是否已启动
3.登录SQL*PLUS
SQL*PLUS是最常用的用于连接和使用Oracle数据库的实用程序。
SQL*PLUS包括在OracleDatabase11g服务器软件和客户机软件中。
在服务器或客户机上安装了SQL*PLUS软件之后,登录服务器或客户机并启动一个SQL*PLUS会话是一个非常的过程。
由于每个SQL*PLUS会话都涉及与数据库的连接,只要提供一个合法的用户名/密码组合就可以启动SQL*PLUS会话并连接数据库。
sqlplus账号/密码
例:
sqlplussystem/密码(此种方式会泄密)
连接指定数据库
例:
sqlplususername@connect_identifier
使用具有sysdba或sysoper权限的身份登录
例:
sqlplususername/passwordassysdba
注意:
as子句允许有sysdba或sysoper系统权限的用户连接SQL*PLUS
4.用connect命令进行连接
登录到SQL*PLUS后,SQL*PLUS的connect命令将帮助你以一个不同的用户身份进行连接。
在使用connect命令连接一个数据库后,还可以登录到另一个不同的数据库。
connectnewuser/newpasswd[@orcl1]
5.用/nolog的无连接SQL*PLUS会话
在启动一个新SQL*PLUS会话时,使用sqlplus命令以及/nolog选项,可以不连接数据库而只启动sql*plus会话。
Sqlplus/nolog
6.在SQL*PLUS中进行操作
在登录到SQL*PLUS界面后,可以输入任何SQL*PLUS、SQL或PL/SQL命令。
一条SQL语句由一个;或一个/结束,一个PL/SQL块由一个/结束,SQL*PLUS命令以一个换行符结束。
如果输入的是一个SQL*PLUS命令,SQL*PLUS客户机程序将处理它,如果是一条SQL或PL/SQL语句,则数据库服务器会进行处理。
7.用于查看当前登录用户
showuser;
8.清屏
clearscreen;
9.退出SQL*PLUS
输入exit或quit可以退出SQL*PLUS。
注意:
如果在SQL*PLUS中输入exit(quit)命令正常地退出会话,则事务将立即被提交。
否则不提交事务。
10.修改SQL提示符
setsqlprompt‘_user@_content_identifier>’;
11.DESCRIBE命令
describe命令描述或列出表的列和列的说明。
12.将SQL缓冲区内容保存到文件中
savec:
\\abc.sqlappend[replace]
13.在SQL*PLUS中执行SQL脚本
runc:
\\abc.sql
/c:
\\abc.sql
@c:
\\abc.sql
14.在SQL*PLUS中进行编辑
当SQL语句出现错误时,通过edit指令调用编辑器修改语句,然后在SQLPLUS中用“/”执行刚刚修改过的SQL语句。
2.创建Oracle数据库
1.通过DatabaseConfigurationAssistant创建数据库
2.查看当前连接的Oracle实例名
selectinstance_namefromv_$instance;
3.修改账号密码
alterusersystemidentifiedby123456;
4.解锁scott用户
alteruserscottaccountunlock;
5.切换登录用户
connscott/tigger;
6.创建用户
createuserabcidentifiedby123;
7.授予用户权限
新创建的用户还不能登录到Oracle系统,必须对其授予权限后才能登录。
一般给用户授予2个系统内置的角色权限,分别是connect和resource。
grantconnect,resourceto<用户>;
8.撤销用户权限
revokeresourcefrom<用户>;
注意:
当开发完毕不需要再建表时,可以将resource角色撤销。
二.创建Oracle表
1.建表时字段常用的数据类型
数据类型
英文描述
类型名称
说明
字符
char
定长
表示固定长度的字符串,最大长度为2000个字符,1个汉字按2个字符处理,如char(10)中存放’Hello’,这个字符串是5个字符,存放到char(10)的字段后,会后补5个空格使得字符的长度为10。
varchar2
变长
存放长度不确定的字符串,最大长度为4000字符,如varchar2(10)中存放’Hello’,放入字段后实际长度就是5个字符。
Long
超长字符串
最大长度2G
数字
integer
整形
小的整数
number
任意精度数字
number可以表示任意精度的整数和小数。
number(n)
n位长度的整数
只能存放最大长度为n的整数,例如number(5)存放的最大整数是99999。
number(n,m)
有效数字位数为n,小数位为m
存放n位有效数字和m位小数的小数类型,例如number(5,2)存放的最大小数位999.99。
日期
date
含有年月日时分秒的完整日期
系统日期可以用函数sysdate来获得。
大对象
clob
文本大对象
存放的字符串的最大长度为4G。
blob
二进制大对象
存放的二进制数据的最大容量为4G。
示例表结构
EMP(雇员表)
字段编号
字段名称
字段描述
字段类型
1
EMPNO
雇员编号
NUMBER(4)
2
ENAME
雇员姓名
VARCHAR2(10)
3
JOB
工作职位
VARCHAR2(9)
4
MGR
雇员的领导编号
NUMBER(4)
5
HIREDATE
雇佣日期
DATE
6
SAL
工资
NUMBER(7,2)
7
COMM
奖金或佣金
NUMBER(7,2)
8
DEPTNO
部门编号
NUMBER
(2)
DEPT(部门表)
字段编号
字段名称
字段描述
字段类型
1
DEPTNO
部门编号
NUMBER
(2)
2
DENAME
部门姓名
VARCHAR2(14)
3
LOC
部门位置
VARCHAR2(13)
2.语句创建数据库表
createtabledept(--创建部门表
deptnonumber
(2)primarykey,
dnamevarchar2(14)notnull,
locvarchar2(13)notnull
)[tablespace<表空间名称>];
createtableemp(
empnonumber(4)primarykey,
enamevarchar2(10)notnull,
jobvarchar2(9),
mgrnumber(4),
hiredatedate,
salnumber(7,2),
commnumber(7,2),
deptnonumber
(2)constraintdept_fkreferencesdept(deptno)
)[tablespace<表空间名称>];
在指定表空间建表
3.添加表列
altertableempaddsalnumber(7,2);
4.删除表列
altertableempdropcolumnsal;
5.重命名表列
altertableemprenamecolumncommtobanus;
6.修改字段类型
altertableempmodify<字段名><字段类型>
7.重命名表
altertableemprenametoemmp;
8.清空表中所有数据
truncatetableemp;
9.删除表
通过使用droptabletable_name命令可以删除一个表。
但是在使用此命令时,表不会立即被删除,Oracle只是简单地重命名此表并将其存储在回收站中(回收站实际上是一个简单的数据字典表)。
这样,还可以恢复被意外删除的表。
恢复被删除的表
flashbacktabletb_nametobeforedrop;
恢复被删除的表的能力称为闪回删除特性。
特别注意:
在默认的表空间创建的表将无法被闪回。
彻底删除数据表
droptabletb_namepurge;
删除具有关联性的表
在删除一个表时,该表上的所有索引也将被删除。
如果要删除的表中包含主键或由其他表的外键引用的唯一键,则必须在droptable命令中包括cascade子句,以便将约束一并删除。
droptableempcascadeconstraints;
10.创建表空间(tablespace)
createtablespaceora2datafile‘d:
\ora\oracle2.ora’size100m;
在创建表空间时,相对应的目录必须存在。
11.将数据导出
(1)导出单表
exp
system/manager@myoracle
file=d:
\daochu.dmp
tables=(table1);
system是用户名,manager是密码,myoracle是数据库名。
(2)导出整个数据库
expsystem/manager@myoraclefile=d:
\daochu.dmpfull=y;
12.利用脚本文件向表中插入数据
脚本文件(create.sql)
createsequenceseq_banjistartwith10incrementby10;--创建序列
insertintobanjivalues(seq_banji.nextval,'A01');--粗体字代表获取序列内容
insertintobanjivalues(seq_banji.nextval,'A02');
执行脚本文件
SQL>@c:
\create.sql
13.导入单表数据
imp用户名/密码tables=<表名>ignore=yfile=文件名
14.导入整个数据库
imp用户名/密码tables=<表名>ignore=yfile=文件名
注意:
数据备份时,要脱离当前Oracle的环境,退到DOS文件系统内。
15.相关语句
(1)查询表结构
descbanji;
(2)查询已存在的序列
select*fromuser_sequences;
(3)删除序列
dropsequenceseq_banji;
(4)查询当前用户下的表
select*fromtab;
16.练习
按照以下表结构建表
部门表(dept)
deptno
dname
loc
10
ACCOUNTING
NEWYORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
员工表(emp)
empno
ename
job
mgr
hiredate
Sal
comm
deptno
7369
SMITH
CLERK
7902
1980-12-17
1000
20
7499
ALLEN
SALESMAN
7698
1981-02-20
1800
300
30
7521
WARD
SALESMAN
7698
1981-02-22
1450
500
30
7566
JONES
MANAGER
7839
1981-04-02
3175
20
7654
MARTIN
SALESMAN
7698
1981-09-28
1350
1400
30
7698
BLAKE
MANAGER
7839
1981-05-01
3050
30
7782
CLARK
MANAGER
7839
1981-06-09
2650
10
7788
SCOTT
ANASTLY
7566
1981-12-09
3200
20
7839
KING
PRESIDENT
1981-11-17
5200
10
7844
TURNER
SALESMAN
7698
1981-09-08
1700
0
30
7876
ADAMS
CLERK
7788
1983-01-12
1300
20
7900
JAMES
CLERK
7698
1981-12-03
1150
30
7902
FORD
ANASTLY
7566
1981-12-03
3200
20
7934
MILLER
CLERK
7782
1982-01-23
1500
10
三.数据库完整性约束
关系数据库中的完整性约束可以容易地且自动地在数据库表中实施重要的业务规则。
1.主键约束
主键是表的非常重要的一类约束。
如果一个列值要被唯一标识,可以为该列值创建主键。
定义为主键的列必须是唯一且非空的。
一个表只有一个主键,可以在创建表时创建主键。
createtabledept(deptnonumberprimarykey);
altertabledeptaddprimarykey(deptno);
altertabledeptdropprimarykey;
altertabledeptdropconstraintpk_a
由于上例中的约束没有分配名字,因此Oracle将分配一个系统生成的约束名。
如果想要自定义一个约束名,可以使用以下方式:
altertabledeptaddconstraintdept_pkprimarykey(deptno);
注意:
如果主键中有多个列(即是一个组合键),则不能在表创建中对列名指定主键名称。
必须在createtable命令的结尾并且在列出所有列之后,作为一个单独项指定主键列。
2.非空约束
一个表通常有一个或多个列不允许为空,即没有值。
可以在表的创建阶段使用notnull选项,强制用户必须为此列输入值。
createtabledept(dnamevarchar2(30)notnull);
altertabledeptmodifydnamenotnull;
3.检查约束
可以使用检查约束确保列中的数据在某个指定的参数范围内。
createtabledept(commvarchar2(15)check(comm<2));
altertableabcmodifyenamecheck(enamein('Tom','Smith'));
4.唯一约束
唯一约束在关系数据库中很常见。
此约束确保关系表中的行的唯一性。
一个表中可以有多个唯一约束。
createtabledept(locvarchar2(20),constraintloc_ukeyunique(loc));
altertabledeptaddconstraintuk_dnameunique(dname);
5.引用完整性约束
引用完整性约束确保某些重要列的值有意义。
使用引用完整性约束,可以确保合法对应字段值的存在。
如果一个表中的字段是关联表的主键,则称该字段为外键字段,因此称为外键。
包含外键的表通过称为子表,而包含被引用键的表称为父表。
createtableemp(
deptnonumbernotnull
constraintdept_fkeyreferencesdept(deptno)
);
altertableempaddconstraintfk_deptnoforeignkey(deptno)referencesdept(deptno)ondeletecascade;注意级联的作用。
四.Oracle查询和函数
1.基本SQL语句
基本SQL语句是指对单表的增、删、改、查等操作。
(1)查询员工的姓名和薪金
selectename,salfromemp;
(2)查询津贴为空的所有员工
select*fromempwherecommisnull;
(3)查询姓名以字母‘S’开头的员工
select*fromempwhereenamelike‘s%’;(多字符匹配)
select*fromempwhereenamelike‘s_’;(单字符匹配)
注意:
like比较符,如果用的是like"Java企业级开发%",这种方式会用索引扫描,但如果是like"%Java企业级开发%",这种方式会用表扫描,该字段上的索引不起作用,速度会非常慢。
(4)查询所有的经理(MANAGER)和办事员(CLERK)的员工
select*fromempwherejobin(‘MANAGER’,’CLERK’);
(5)按参加工作时间从早到晚显示员工
select*fromemporderbyhiredateasc;
(6)增加一个部门
insertintodeptvalues(50,’培训部’,’天津’);
(7)给员工涨5%的工资
updateempsetsal=sal*1.05;
(8)删除新增的部门“培训部”
deletefromdeptwheredname=’培训部’;
所有的关系型数据库都必须遵守ANSI92的SQL语句标准,ANSI92中定义的一套SQL语句就称为标准SQL,每种关系型数据库都会对标准SQL语句进行扩展,加入自己专有的SQL语句和自己的一套函数,下面就来看看Oracle中的函数。
2.Oracle函数
(1)字符函数
concat(char1,char2)
返回将char2拼接在char1之后的字符串,也可以表示为char2||char1。
initcap(string)
将string的字符全部转化为首字母大写的英文规范格式。
upper(string)
返回全部大写的string。
lower(string)
将string转化为小写字符串。
lpad(char1,n[,char2])
使用char2的字符补充在char1字符的左边,最终补足n个字符,如果没有提供char2字符,则用空格补足n个字符。
rpad(char1,n[,char2])
返回“char1”,右侧用“char2”中的字符补充到n个字符长。
如果“char1”比n长,则函数返回“char1”的前n个字符。
ltrim(string,trim_set)
从左边删除字符,此处“string”是数据库的列,或者是字面字符串,而“trim_set”是要去掉的字符的集合。
rtrim(string,trim_set)
类似于ltrim函数,从右侧删除字符,此处“string”是数据库的列,或者是字面字符串,而“trim_set”是要去掉的字符的集合。
replace(string,if,then)
用0或其他字符代替字符串中的字符。
“if”是字符或字符串,对于每个出现在“string”中的“if”,都用“then”的内容代替。
substr(string,start[,count])
返回“string”中截取的一部分。
该命令截取“string”的一个子集,从“start”位置开始,持续“count”个字符。
如果我们不指定“count”,则从“start”开始截取到“string”的尾部。
chr(n)
返回ASCII值n所对应的字符。
instr(string,set[,start[,occurrence]])
从string中针对set进行查询,从start位置开始查询,找出set在se的位置。
length(string)
获得“string”字符串的长度。
例:
把员工姓名和工作类型连接在一起,中间用“-”分隔显示。
selectename||’-’||jobfromemp;
例:
让员工姓名右对齐显示
selectename,lpad(ename,6,’’)fromemp;
例:
截取员工姓名的前3个字符和第4个字符以后的内容。
selectename,substr(ename,1,3),substr(ename,4)fromemp;
例:
字母”T”在员工姓名中第一次和第二次出现的位置
selectename,instr(ename,’T’,1,1),instr(ename,’T’,1,2)fromemp;
(2)日期和时间函数
sysdate
返回当前的日期和时间
add_months(d,no_of_month)
为日期“d”加上“no_of_month”的月份,返回加上月数之后的日期,参数“no_of_month”可以为任何整数。
last_day(month_day)
返回指定日期的本月最后一天的日期。
months_between(d1,d2)
返回日期d1和d2之间的月份数。
如果d1晚于d2,结果为正,否则返回负数。
next_day(d,day_of_week)
指定时间的下一个星期几(由char指定)所在的日期。
day_of_week为1-7或Monday/Mon-Sunday
next_day(sysdate,1)
例:
查询系统时间。
selectsysdatefromdual;
例:
查询在12年前参加工作的员工。
selectempno,ename,hiredatefromempwhere
months_between(sysdate,hiredate)>144;
例:
查询在当月倒数第三天参加工作的员工。
selectempno,ename,hiredatefromempwherelast_day(hiredate)-2=hiredate;
例:
查询每个员工的工作天数
selectempno,ename,hiredate,trunc(sysdate-hiredate)fromemp;
trunc函数:
截取小数位
例:
显示系统时间是xxxx年xx月xx日,是一年中的第几天,是星期几。
selectto_char(sysdate,’yyyy”年”mm”月”dd”日”DDDDAY’)fromdual;
(3)数字函数
abs(n)
返回n的绝对值
ceil(n)
该函数又称“天花板”,返回值是大于等于n的最小整数。
floor(n)
该函数
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 最新 Oracle11g 教案
![提示](https://static.bdocx.com/images/bang_tan.gif)