oracle初步学习笔记文档格式.docx
《oracle初步学习笔记文档格式.docx》由会员分享，可在线阅读，更多相关《oracle初步学习笔记文档格式.docx（32页珍藏版）》请在冰点文库上搜索。
●OracleJobSchedulerORCL服务是主管Oracle里边的计划任务的。
这个基本没用，一般操作不需要管他。
1.7oracle的主键与外键
主键（Primary）：
主键是关系数据库表中的某一列或者某几列的集合。
它能够唯一标示数据库表中的一行。
主键不能包含空值（null）.
外键（Foreign）：
外键是关系数据库表中的一列或者某几列的组合。
它的值与另一个表的某一列或者某几列相匹配，这一列是另一个表的主键。
1.8Oracle数据库共有5个约束
Oracle数据库共有5个约束：
分别是主键、外键、非空、唯一、条件。
●非空：
就是这个列的值不能为空（null）
●唯一：
这个列的值在表中是唯一存在的，不能重复，但可以为空值（null）
●条件：
可以对列的值设定在某个条件范围之内。
比如，人的年龄就不能为负数，可以设置这个列的条件为大于0，且小于200.
表的主键和唯一约束很像，有哪些区别？
答：
表的主键是列的值为表中的唯一标识，不能为空值（null），而表的唯一约束是列的值在表中唯一存在，可以为空值（null）。
1.9Oracle数据库比较常用的字段数据类型
●Number（p,s）数值类型，其中1<
=p<
=38，-84<
=s<
=124.
●Date:
日期类型，用于记录时间。
●Char（size）：
定长字符串类型，比如某个字段的值，知道规定的长度，可以节省很大的空间，加快访问速度。
比如性别，可以定义一个字符，F表示女，M表示男。
Size表示的是多少个字符长度。
●Varchar（size）：
可变长度字符串类型，比较常用。
比如人名有长短，可以给一个最大的值。
●Blob（BinaryLargeObject，二级制大对象类型）：
用户存储二级制对象，比如照片，文档资料等。
●Clob（CharacterLargeObject，字符大对象类型）：
存储字节的大对象数据，比如简历之类。
●Bfile（BinaryFile，二级制文件）：
存储大对象，比如电影胶片等。
2oracle基本操作
2.1启动/关闭oracle数据库
startup/shutdownimmediate;
2.2查询当前数据库的名称
showparameterdb_name;
NAMETYPEVALUE
------------------------------------------------------------------------------------------------
db_namestringorcl
【注释】show_parameter:
表示查看oracle数据库中的各种参数值。
2.3查询oracle某个账号的状态
selectusername,account_statusfromdba_userswhereusername='
SCOTT'
;
USERNAMEACCOUNT_STATUS
-----------------------------------------------------------------------------------------
SCOTTOPEN
2.4查看dba_users表中有哪些列
descdba_users;
2.5查看当前数据库用户
showuser;
2.6解锁SCOTT用户
alteruserscottaccountunlock;
【注释】若某个用户的状态为（EXPIRED&
LOCKED），则表示“过期并且锁定”，需要解锁，解锁完成后，重新用该账号（以及原密码）连接oracle数据库，此时便会提示修改密码。
2.7为oracle用户重置密码
alteruserscottidentifiedbytiger;
（将用户scott的密码改为tiger）
【注释】需使用“sysdba权限“账号。
2.8查询当前用户有哪些表
selecttable_namefromuser_tables;
2.9改变当前会话日期显示格式
altersessionsetnls_date_format='
YYYY-MM-DD'
2.10查询数据库当前时间
selectsysdatefromdual;
3oracle基本增、删、改、查
3.1向dept表中插入一条数据
insertintodept（deptno,dname,loc）values（50,'
development'
'
Beijing'
）;
commit;
【注释】如果没有commit，则插入的sql语句是在计算机的内存中，如果遇上意外停电故障，插入的数据有可能不糊存储在数据库中。
（试了一下，如果没提交，退出sql后，就查不到刚刚插入的数据了）
3.2更新dept表中的某条数据
updatedeptsetloc='
Shanghai'
wheredeptno=50;
3.3删除dept表中的某条数据
deletefromdeptwhereloc='
3.4排序（orderby）
【例子】按照员工工作年数进行排序
selectename,hiredatefromemporderbyhiredate;
selectename,hiredatefromemporderby2;
注释：
此处orderby2表示，按照查询结果的第二列来排序；
oracle排序默认为升序asc（即从低到高），降序为desc，如下：
selectename,round（（sysdate-hiredate）/365,0）fromemporderby2desc;
另外：
oracle数据库在查询中，还可以用查询结果中没有的列进行排序，如下：
selectename,round（（sysdate-hiredate）/365,0）fromemporderbyhiredate;
3.5as用法（别名）
【例子】为查询出来的“列”增加中文。
selectenameas“姓名”,round（（sysdate-hiredate）/365,0）as“工作年限”fromemp;
另外，如果是英文字母，可以不使用as，如下：
selectenameE,hiredateHfromemp;
3.6运算符的用法
【例子】查询出基本工资加上1000元奖金后的，员工本月薪资。
selectename,sal+1000fromemp;
3.7对数据进行拼接（||…||）
selectename||'
员工工资为：
￥'
||（sal+1000）fromemp;
||（sal+1000）as"
员工本月工资表"
fromemp;
说明：
||：
两个竖杠是oracle的连接符，可以把查询出来的数据和其它字符连接起来，可以对多个字符串、多个表的列值相连接。
‘’：
两个单引号里面是字符串，可以把里面的字符串输出。
【注意】符号特别要注意在英文输入法下输入
3.8去重（distinct）
selectdistinctdeptnofromemp;
3.9where与orderby
【例子】查询工资少于2000的员工，并排序
selectename,salfromempwheresal<
=2000orderbysal;
3.10betwween…and
【例子】查询工资在1500到2500之间的员工，并排序
selectename,salfromemp
wheresalbetween1500and2500
orderbysal;
3.11where与and
【例子】查看工资为1250的销售人员
selectempno,ename,job,salfromempwherejob='
SALESMAN'
andsal=1250;
EMPNOENAMEJOBSAL
---------------------------------------
7521WARDSALESMAN1250
7654MARTINSALESMAN1250
【注意】where后面跟的条件，'
必须得跟数据库中保存的大小写一致，此处'
如果是小写'
salesman'
就查不到数据。
3.12where与or
【例子】查看没有奖金（comm）或者工资少于1500的员工
selectempno,ename,job,sal,commfromemp
2wherecommisnull
3orsal<
=1500;
注意：
null在oracle中即不表示0，也不表示空，是一个不能确定未知数。
3.13函数nvl（X,Y）
【例子】查看员工的工资加上提成，哪个员工的工资是最少的?
selectempno,ename,job,sal,comm,sal+nvl（comm,0）fromemp
2orderbysal+nvl（comm,0）;
【注释】nvl（X,Y）是oracle内部函数，表示如果X有值，则返回X，如果X的值为null，则返回Y。
3.14like用法
【例子】查看名字开头为“M”的员工
selectename,jobfromempwhereenamelike'
M%'
like是条件where中模糊查询的关键字；
“%”在sql中表示字符后面所有字符。
3.15in用法
【例子】查找哪些员工属于销售人员、分析师、管理人员？
selectename,jobfromemp
2wherejobin（'
ANALYST'
MANAGER'
【注释】in表示在某个列中存在多个值均符合，或者使用or代替。
如下：
2wherejob='
orjob='
3.16count用法
【例子】统计公司每个岗位有多少个员工。
selectjob,count（*）fromempgroupbyjob;
groupby是oracle数据库中的分组函数。
3.17groupby用法
【例子】统计公司每个部门分别有多少员工。
selectdeptno,count（*）fromempgroupbydeptno;
3.18groupby与orderby
【例子】统计公司每个部门分别有多少员工，并按照员工数量排序。
selectdeptno,count（*）fromemp
groupbydeptno
orderbycount（*）;
3.19函数round（X,Y）
【例子】查询员工入职的年限。
selectename,round（（sysdate-hiredate）/365,0）fromemp;
【注释】round（X,Y）：
是oracle数据库中的一个四舍五入函数，X表示这个数字需要进行四舍五入，Y表示在哪位数进行四舍五入，如果Y=0，则表示在个数据进行四舍五入，Y=2表示在保留小数点后面的两位数进行四舍五入。
3.20函数sum
【例子】统计公司本月需支付多少薪水.
selectsum（sal）+sum（nvl（comm,0））fromemp;
3.21函数avg
【例子】统计员工的平均薪水（保留到小数点后两位数）
selectround（avg（sal）,2）fromemp;
avg（sal）表示平均工资。
3.22函数max，min
【例子】统计员工最高，最低工资，以及差值。
selectmax（sal）,min（sal）,max（sal）-min（sal）fromemp;
max（sal）,min（sal）表示最高，最低工资
3.23having与groupby
【例子】查看哪些岗位的平均工资大于2500。
selectjob,avg（sal）fromemp
havingavg（sal）>
2500
groupbyjob;
在使用groupby分组是，如有条件限制，需要使用having，而不能使用where.
上面的例子中，数据库首先使用groupby进行岗位分组，再使用avg（sal）求出每个岗位的平均工资，最后平均工资大于2500的，由having进行限制。
3.24创建一个表
createtableitems（itemnonumber
（2）,itemnamevarchar2（10））;
3.25删除一个表
droptableitems;
3.26主键、非空约束
【例子】创建一个有主键，以及非空约束的表。
createtableItems（
ItemNonumber
（2）constraintPK_itemsprimarykey,
ItemNamevarchar2（10）notnull）;
●constraint:
定义表中约束所需的关键字，后面跟约束名。
●primarykey：
主键约束的关键字，表示这一列为主键。
●notnull：
非空约束关键字。
3.27外键约束、函数to_date（）
【例子】创建一个Business表，外键为Items表的主键
createtableBusiness（
BusiNonumber
（2）constraintPK_Businessprimarykey,
BusiNamevarchar2（20）notnull,
ItemNonumber
（2）,constraintFK_Business
foreignkey（ItemNo）referencesItems（ItemNo）,
StartDatedate
上面有个“,”,而其它约束时，是没有这个逗号的。
●foreignkey…references…:
这是创建表外键必须的SQL语句关键字，并且该列需要和另外一个表的主键对应起来。
此时，必须是Items表的ItemNo列存在的数据，才可以在Business表中的ItemNo列中插入数据。
即Items表中有了9号项目，才可以在Business表中插入项目号为9的数据。
反之，则不行。
另外，Business表中外键ItemNo的类型，可以设置为number
（1），或者number（3）等，只要字段类型一致就可。
insertintoBusiness（BusiNo,Businame,Itemno,Startdate）
values（4,'
SuperMarket'
2,to_date（'
2010-10-10'
））;
to_date（）:
是oracle的一个内部函数，可以把字符串变成时间。
3.28唯一约束、条件约束
【例子】创建一个Computers表，里面price有条件约束（在3W以内）
createtableComputers（
CompNonumber（4）constraintPK_computersprimarykey,
CompModelvarchar2（64）unique,
BuyTimedate,
Pricenumber（7,2）constraintComp_pricecheck（price>
0andprice<
=30000）,
Ownervarchar2（32）
●unique：
唯一约束的关键字。
●constraint…check…:
这是条件约束的关键字，插入、更改的数据需要符合这些条件，才能将数据提交到数据库中。
●number（7,2）：
这里的number数字类型，表示只能是7位数，可以保留2位小数点。
3.29复制某个表的数据
【例子1】创建一个新表Business_copy，并复制Business表的数据。
createtableBusiness_copy
as
select*frombusiness;
【注意】这种复制，只是复制了表的数据，但是原表的约束是没有复制过去的。
【例子2】将表Business_copy中的数据，复制到新建的空表Business中。
insertintoBusiness
select*fromBusiness_Copy;
或者：
insertintoBusiness（Busino,Businame,Itemno,Starttime）
3.30增加一个表字段
【例子】为表items增加一个manager的字段。
altertableitemsadd（managervarchar2（6））;
3.31修改表中某字段的属性
【例子】修改表items表中，字段manger的属性。
altertableitemsmodify（managervarchar2（8））;
3.32删除表中某字段
【例子】删除表items中的manager字段。
altertableitemsdropcolumnmanager;
4新增、修改、删除oracle用户以及授权
4.1创建数据库用户
【例子】创建数据库用户lisi，密码也为lisi。
createuserlisiidentifiedbylisi；
4.2为数据库用户授予权限
【例子1】将“连接”数据库的权限授予给lisi。
grantconnecttolisi;
●grant:
oracle数据库授权的关键字，表示“授权”的意思。
●connect：
oracle数据库的一个默认角色。
只有连接上数据库的权限。
【例子2】将scott用户的表emp的查询权限，授权给lisi用户。
a）使用scott用户登录oracle数据库；
b）执行SQL>
grantselectonemptolisi；
【例子3】回收上例中的权限。
revokeselectonempfromlisi;
【例子4】scott用户将表emp的增、删、改、查权限都授予给lisi用户。
grantinsert,delete,update,selectonemptolisi;
【注意】用户lisi在对emp表进行操作时，必须在表明前加上前缀scott.
Select*fromscott.emp;
insertintoscott.emp（empno,ename,hiredate,sal,deptno）
values（799,'
李四'
sysdate,2000,10）;
4.3修改用户密码
【例子】将用户lisi的密码修改为”tiger”.
alteruserlisiidentifiedbytiger;
4.4删除用户lisi
dropuserlisi；
dropuserlisicascade（这将会删除与lisi相关联的表）
4.5查看当前用户拥有哪些权限
select*fromsession_privs;
4.6查看当前用户拥有哪些角色
【例子】查看scott用户拥有哪些角色。
select*fromuser_role_privs;
可以看到scott用户，具有2个角色，CONNECT和RESOURCE角色。
4.7查看connect角色有哪些权限
【例子】使用sys用户，查看connect角色有哪些权限。
select*fromdba_sys_privswheregrantee='
CONNECT'
4.8创建具有connect和resource权限的用户
【步骤】
A、dba角色的用户登录；
B、执行：
C、执行：
grantconnect，resourcetolisi；
【注释】开发人员来说，一般只需要拥有CONNECT和RESOURCE角色的用户即可。
5数据库的对象
问题：
oracle数据库中函数和存储过程的区别？
（1）函数必须有返回值，而过程没有返回值。
升级会员 