Oracle实验总结.docx

Oracle实验总结.docx

《Oracle实验总结.docx》由会员分享，可在线阅读，更多相关《Oracle实验总结.docx（9页珍藏版）》请在冰点文库上搜索。

Oracle实验总结

Oracle实验总结

Oracle实验总结

Oracle实验总结

近日做了基于ORACLE的数据库安全、对象sql数据库，XML数据库的实验，其中遇到很多问题，先总结如下。

1、安装Oracle的过程中可以‘选择建立数据库’，也可以选择‘只安装软件’，后者可在安装好软件后，建立数据库，可参考网上的很多安装步骤。

2、Oracle的默认用户有sys和system,在命令行或者sQl*plus中登陆时，写法如下：

Connectsys/123456assysdba;其中123456是密码，在创建数据库的时候设置的。

普通用户登录，connectzhangsan/123456;

3、grantselect不支持对表中选定的几列授权，只能将整个表的select权限授权出去。

所以实验中先建立了基于选课信息表的视图SelectiveInfo1，视图中包含成绩列，学号列以及班级列（不包含其他的列）,再将对视图SelectiveInfo1的select权限授予角色teacher。

4、创建用户的时候，一定要说明该用户对表空间的权限即（quotaunlimitedontablespace或者具体限定大小的句子），否则该用户即使被授予了建表权限，也不能建表。

创建用户的时候必须给用户授予createsession的权限，这是最基本的权限。

5、不同的用户可以创建相同的表格，例如，teacher用户创建了table1,student用户也创建了table1,且这两个表格式完全相同的，那么其他用户需要用这两个表格时，可以用teacher.table1和student.table1加以区分。

系统用户创建的表，和用户自己创建的表，本用户引用是可不加前缀。

6、oracle有自带的密码验证函数，默认情况下是不启用的，可以用下面的语句启用，注意必须以Connectsys/123456assysdba的身份登陆（其他DBA身份的用户可不可以，本人没有试过）才能启用密码函数，启用方式如下

7、将日期装换为字符串可以to_char（）函数，将字符串转换为日期用to_date（）函数。

网上有很多这两个函数的资料。

8、Xml数据库使用的简单举例（具体路劲写法好像不对，只要将改改‘//’为‘/’并把路径写全就可以了，路径的用法其实是xpath技术）

建立含xmltype数据的表

createtableabc（idnumber,xmldocsys.xmltype）;

向表中插入数据

insertintoabc（id,xmldoc）values（1,

sys.xmlType.createXML（““

abc”“））;

查询表中的数据：

得到id=1的value变脸的值select

i.xmldoc.extract（““//name/a[@id=1]/@value”“）.getStringVal（）asennames,id

fromabci

得到a节点的值

selectid,i.xmldoc.extract（“/name/a/text（）”）.getStringVal（）astruename

fromabci

得到节点id属性的值

Selecthd.Data_t.extract（““/root/name/@id”“）.getStringVal（）AsName

FROMsehr_house_datahd

扩展阅读：

ORACLE实训总结

1、Conn/assysdba:

以sysdba连接数据库，在这里可以解锁system、hr（oracle内建的用户名）、scott（oracle内建的用户名）用户，并修改它们的密码。

2、修改密码：

Alteruseridentifiedby3、解锁用户：

Alteruseraccountunlock

4、解锁hr、scott，并修改system、hr、scott密码Alteruserhraccountunlock;Alteruserscottaccountunlock;Alteruserhridentifiedbyk;Alteruserscottidentifiedbyk;Alterusersystemidentifiedbyk;5、连接网络数据库

在\\product\\10.0.2\\db_1\\NETWORK\\ADMIN\\tnsname.ora文件中添加内容：

test=（

DESCRIPTION=

（ADDRESS=（PROTOCOL=TCP）（HOST=）（PORT=1521））（CONNECT_DATA=（SERVER=DEDICATED）（SERVICE_NAME=orcl）））

连接数据库时，输入:

conn/@test6、创建用户的语句以创建用户名hr为例：

Connsystem/assysdba;//连接到指定数据库Dropuserhrcascade;//如果hr用户名已存在的话删除该用户名

Createuserhridentifiedbyhrdefaulttablespaceuserstemporarytablespacetemp;//创建用户hr

Hostimphr/hrfile=\\hr.dmplog=\\1.logfromuser=hrtouser=hrignore=y;7、hr环境搭建

connhr/k：

以用户名hr、密码k连接数据库createtableEMP（EMPNONUMBER（6）notnull,ENAMEVARCHAR2（25）,HIREDATEDATE,JOBVARCHAR2（10）,SALNUMBER（8,2）,COMMNUMBER（2,2）,DEPTNONUMBER（4）

）;INSERTINTOEMPSELECT

EMPLOYEE_ID,LAST_NAME,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,DEPARTMENT_IDFROMEMPLOYEES;createtableDEPT（DEPTNONUMBER（4）notnull,DNAMEVARCHAR2（30）,LOCNUMBER（4）

）;INSERTINTODEPT

SELECTDEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_IDFROMDEPARTMENTS;

createtableemp_jobasselectempno,jobfromempwhere1=2;

insertintoemp_job（empno,job）selectempno,jobfromempwheredeptno=30;createtablenew_emp（id,name）asselectempno,enamefromemp;createtableSALGRADES（grade_levelvarchar2（4）,lowest_salaryNUMBER,highest_salaryNUMBER

）;insertintosalgradesvalues（“L1”,1000,2999）;insertintosalgradesvalues（“L2”,3000,5999）;insertintosalgradesvalues（“L3”,6000,8999）;insertintosalgradesvalues（“L4”,9000,14999）;insertintosalgradesvalues（“L5”,15000,22999）;insertintosalgradesvalues（“L6”,23000,30000）;commit;

8、SQL语句的分类DQL:

dataquerylanguage数据查询语言:

selectDML:

datamanipulationlanguage数据操作语言insert/update/delete/mergeDDL:

datedefinitionlanguage数据定义语言create/alter/drop/truncateDCL:

datecontrollanguage数据控制语言grant/revokeTCL:

transitoncontrollanguage事务控制语言commit/rollback/savepoint9、NULL:

它不等于零或空格，任意的类型都可以支持nulla）包含null的任何算术表达式的结果都为nullb）包含null的连接表达式等于与””（空字符串）连接10、列别明的两种书写形式a）列名列别名b）列名as列别名

11、列别名必须加””的情况a）列别名中包含有空格b）列别名中要求区分大小写c）列别名中包含有特殊字符12、使用||连接符代替，实例：

Selectfirst_name||||last_name||sphonenumberis||phone_number“employeePhonenumber”fromemployees;

其中first_name、last_name、phone_number位列变量，表示输出空格，中前两个表示输出,sphonenumberis表示输出sphonenumber，”employeePhonenumber”表示输出列的别名。

13、SQL*PLUS/ISQL*PLUS命令a）desc:

显示表结构，其中desc是describe的缩写，可以写全。

b）set[系统变量]on|off，例如：

setheadingon|off:

控制是否显示列标题。

setescapeon|off:

控制是否打开转义符，showescape:

显示转义字符setautocommiton|off:

空值是否打开自动提交，showautocommit:

显示当前提交方式14、ISQL*PLUS的默认显示格式Date和Character型左对齐，Numeric型右对齐、列标题居中大写字符函数：

其中字符串可以代表列名或字符表达式

a）lower（字符串）将所有大写字符转换为小写字符b）upper（字符串）将所有小写字符转换为大写字符

c）iniicap（字符串）所有单词的首字母大写，其它的小写d）concat（字符串1,字符串2）将字符串1与字符串2连接

e）substr（字符串,m,n）从字符串中第m位开始截取n位，字符串从1开始计数f）length（字符串）返回字符串的长度

g）instr（字符串1,字符串2,m,n）返回字符串2在字符串1中，从第m位开始，第n次出

现的位置。

h）lpad（字符串1,n,字符串2）用字符串2在字符串1的左边进行填充，使字符串1达到

n位

i）rpad（字符串1,n,字符串2）用字符串2在字符串1的右边进行填充，使字符串1达到

n位

j）trim（leading|trailing|both字符from字符串）将字符串中开头（leading）和结尾（trailing）

的指定字符（只能是字符）去掉。

k）replace（字符串1,字符串2,字符串3）将字符串1中的字符串2用字符串3替换。

数字函数

a）round（数字,n）将数字四舍五入到小数点后n位，n为负数表示小数点前|n|位。

b）trunc（数字,n）将数字截取到小数点后n位，其他同上。

c）mod（m,n）取m除以n的余数日期函数

a）sysdate返回当前系统日期，不能写成sysdate（）

b）months_between（date1,date2）返回date1与date2之间的月数c）add_months（date,n）在date的基础上增加n个月d）next_day（date,d）返回date之后的第一个指定d，例如：

next_day（16-8月-11,星期一）,

返回11年8月16日之后的第一个星期一是：

22-8月-11

e）last_day（date）返回date所在月的最后一天，例如：

last_day（16-8月-11）返回：

31-8

月-11

f）round（date,fmt）将date按fmt格式四舍五入，fmt可选默认为DD

g）trunc（date,fmt）将date按fmt格式四舍五入，fmt可选默认为DD，时间提前h）extract（XXfromdate）从date中提取出XX（month、day、year）数据类型之间的转换

a）to_char（date|number,fmt）将date或number转换为指定格式fmt的字符串，fmt可省

略b）to_number（char,fmt）将char转换为指定格式fmt数字，fmt可省略c）to_date（char,fmt）将char转换为指定格式fmtdate，fmt可省略常用的日期格式

a）yyyy：

四位数字的年份b）yy：

两位数字的年份c）year：

年份的英文拼写d）mm：

两位数字的月份e）month：

月份的英文拼写f）dy：

星期英文的前三位g）day：

星期英文的全拼h）d：

星期的第几天，星期日为第一天i）dd：

一个月中的第几天j）ddd：

一年中的第几天k）am或pm：

上下午

l）hh或hh12或hh24：

表示小时12小时制24小时制m）mi：

表示分钟n）ss：

表示秒数字格式a）9:

一位数字b）0:

一位数字或前导零c）$:

美元符号d）L:

按照地区显示货币符号e）.:

小数点f）,千位分隔符其它函数

a）nvl（表达式1,表达式2）:

如果表达式1的值不为null，返回表达式1的值，否则返回

表达式2的值，数据格式可以是日期、字符、数字，但表达式1和表达式2的数据类型必须一致。

b）nvl2（表达式1,表达式2,表达式3）:

如果表达式1的值不为空，返回表达式2的值，

否则返回表达式3的值。

表达式2和表达式3的数据类型必须相同。

c）nullif（表达式1,表达式2）:

如果两个参数不相等，返回表达式1的值；相等，则返回

空值。

d）coalesce（x1,x2……xn）:

返回第一个非空的参数。

e）（case表达式1when条件1then结果1when条件2then结果2……else结果nend）:

如

果表达式为条件1匹配就返回结果1……最后默认返回else后的结果n。

f）Decode（表达式，条件1，结果1，条件2，结果2……缺省值）:

如果表达式与条件1

匹配则返回结果1，缺省返回缺省值。

表别名如果定义了表别名，那么之后只能使用表别名而不能使用原表名。

连接语法

Select……fromtable1[crossjointable2]|[naturaljointable2]|

[jointable2using（column_name）]|

[jointable2on（table1.column_name=table2.column_name）]|

[left|right|fullouterjointable2on（table1.column_name=table2.column_name）]a）crossjointable2:

生成笛卡尔积b）nutualjointable2:

自然连接

c）jointable2using（column_name）:

使用指定列连接，两列同名

d）jointable2on（table1.column_name=table2.column_name）:

使用指定列连接，两列可以

不同名

e）left|right|fullouterjointable2on（table1.column_name=table2.column_name）:

外连接union和unionall

a）union和unionall连接的两个结果集所包含的字段要相等。

b）union的结果去掉重复的记录，unionall不去掉重复的记录。

Groupby的特点

a）在groupby子句的列可以不在select语句中出现，但在select字句中出现的非分组

列必须在groupby字句中出现b）Groupby后面可以有多列

Select语句的执行过程:

顺序为from-where-groupby-having-select-orderby

a）from:

从from子句中找到需要查询的表b）where:

进行非分组函数的筛选判断c）groupby:

完成分组操作

d）having:

完成分组函数的筛选判断

e）select:

选择要显示的列或表达式或组函数f）orderby:

对得到的结果集进行排序组函数的嵌套：

最多为两级，例如：

Selectmax（count（employee_id））fromemployeesgroupbydepartment_id;子查询

特点:

a）子查询只能在括号中

b）子查询需要写在运算符右边

c）子查询可以写在where、having、from中。

分类:

a）单行子查询:

用=、>=、=等连接，子查询返回结果只有一条

b）多行子查询:

用any、all、in等连接，子查询的返回结果只有一条c）多列子查询:

子查询的返回结果中含有多列。

例如：

selecte.last_namee.salary,e.job_id,j.avgsalfromemployeese,（selectjob_id,avg（salary）avgsalfromemployeesgroupbyjob_id）jwheree.job_id=j.job_idande.salary>j.avgsal

子查询中的空值问题

示例:

selecta.last_name,a.salaryfromemployeesawherea.employee_idnotin（selectb.manager_idfromemployeesb）的返回结果为空

原因:

在employees表中有manager_id为null的记录，而null&&true=trueExists和notexists

a）子查询中如果找到符合条件的记录，子查询语句不会继续执行，返回trueb）子查询中如果到表的末尾也没有找到符合条件的记录，返回falseInsert插入空值插入空值时，可以写null或使用默认值修改示例:

updatedossiersetcountry_code=defaultwhereid=2创建表的语法

Createtabletable_name[（column,column……）]asselect……:

对于select中的表达式（比如：

salary*12）必须给定列别名，否则报错。

引用另一个用户的表用户名.表名Oracle中表的分类

a）用户表:

有用户创建和维护的表的集合，它包括用户所使用的数据

b）数据字典:

由Oracle服务器创建和维护的表的集合，它包括数据库信息，比如表的

定义、数据库结构信息等。

Oracle数据库的伪列和伪表

伪表:

dual用于查询不涉及具体表时伪列:

rowin由系统自动产生，表示行地址添加、修改、删除列

a）添加:

altertabletable_nameadd（columnname,datatypenotnulldefault……）b）修改:

altertabletable_namemodify（columnname,datatypenotnulldefault……）c）删除:

altertabletable_namedrop（columnname1,columnname2）重命名表名

语法:

renameoldnametonewname