oracle初步学习笔记.docx
《oracle初步学习笔记.docx》由会员分享，可在线阅读，更多相关《oracle初步学习笔记.docx（32页珍藏版）》请在冰豆网上搜索。
oracle初步学习笔记
1oracle基本概念
1.1字典表
字典表是oracle数据库的内部表。
是oracle不需要创建就存在的表，不能更改其信息。
1.2动态表
oracle的动态表也属于字典表，属于oracle内部表。
通常使用“v$”开头。
1.3登录oracle的两种认证方式
●操作系统认证：
在默认情况下，如果在本机服务器登录oracle数据库，可以使用操作系统认证，不用输入数据库用户名以及密码。
●用户密码认证：
如果是远程连接数据库，则需要使用数据库的用户名、密码。
●连接oralce命令conn“/assysdba”;默认使用的账号为sys；
1.4设置数据库查询结果的显示格式
SQL>columnempnoheading'员工编号'format9999
SQL>setlinesize500
SQL>setpagesize50
setpagesizen一页显示多少行数据
setlinesizen一行可以容纳的字符数量
1.5oracle常用运算符
“<”（小于），”>”（大于），“<=“，“>=”，“=”，“<>”和“!
=”（都是不等于）
1.6oracle各个服务的作用
●OracleServiceORCL服务是主管Oracle的，那个服务不启动，Oracle没法用。
●OracleDBConsoleorcl服务主管Oracle客户端操作的，你安装完Oracle之後，在开始->程序->Oracle有个网页链接，这个服务不开启，你的网页链接进不去。
●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数据库
SQL>startup/shutdownimmediate;
2.2查询当前数据库的名称
SQL>showparameterdb_name;
NAMETYPEVALUE
------------------------------------------------------------------------------------------------
db_namestringorcl
【注释】show_parameter:
表示查看oracle数据库中的各种参数值。
2.3查询oracle某个账号的状态
SQL>selectusername,account_statusfromdba_userswhereusername='SCOTT';
USERNAMEACCOUNT_STATUS
-----------------------------------------------------------------------------------------
SCOTTOPEN
2.4查看dba_users表中有哪些列
SQL>descdba_users;
2.5查看当前数据库用户
SQL>showuser;
2.6解锁SCOTT用户
SQL>alteruserscottaccountunlock;
【注释】若某个用户的状态为（EXPIRED&LOCKED），则表示“过期并且锁定”，需要解锁，解锁完成后，重新用该账号（以及原密码）连接oracle数据库，此时便会提示修改密码。
2.7为oracle用户重置密码
SQL>alteruserscottidentifiedbytiger;（将用户scott的密码改为tiger）
【注释】需使用“sysdba权限“账号。
2.8查询当前用户有哪些表
SQL>selecttable_namefromuser_tables;
2.9改变当前会话日期显示格式
SQL>altersessionsetnls_date_format='YYYY-MM-DD';
2.10查询数据库当前时间
SQL>selectsysdatefromdual;
3oracle基本增、删、改、查
3.1向dept表中插入一条数据
SQL>insertintodept（deptno,dname,loc）values（50,'development','Beijing'）;
SQL>commit;
【注释】如果没有commit，则插入的sql语句是在计算机的内存中，如果遇上意外停电故障，插入的数据有可能不糊存储在数据库中。
（试了一下，如果没提交，退出sql后，就查不到刚刚插入的数据了）
3.2更新dept表中的某条数据
SQL>updatedeptsetloc='Shanghai'wheredeptno=50;
SQL>commit;
3.3删除dept表中的某条数据
SQL>deletefromdeptwhereloc='Shanghai';
SQL>commit;
3.4排序（orderby）
【例子】按照员工工作年数进行排序
SQL>selectename,hiredatefromemporderbyhiredate;
SQL>selectename,hiredatefromemporderby2;
注释：
此处orderby2表示，按照查询结果的第二列来排序；oracle排序默认为升序asc（即从低到高），降序为desc，如下：
SQL>selectename,round（（sysdate-hiredate）/365,0）fromemporderby2desc;
另外：
oracle数据库在查询中，还可以用查询结果中没有的列进行排序，如下：
selectename,round（（sysdate-hiredate）/365,0）fromemporderbyhiredate;
3.5as用法（别名）
【例子】为查询出来的“列”增加中文。
SQL>selectenameas“姓名”,round（（sysdate-hiredate）/365,0）as“工作年限”fromemp;
另外，如果是英文字母，可以不使用as，如下：
SQL>selectenameE,hiredateHfromemp;
3.6运算符的用法
【例子】查询出基本工资加上1000元奖金后的，员工本月薪资。
SQL>selectename,sal+1000fromemp;
3.7对数据进行拼接（||…||）
SQL>selectename||'员工工资为：
￥'||（sal+1000）fromemp;
SQL>selectename||'员工工资为：
￥'||（sal+1000）as"员工本月工资表"fromemp;
说明：
||：
两个竖杠是oracle的连接符，可以把查询出来的数据和其它字符连接起来，可以对多个字符串、多个表的列值相连接。
‘’：
两个单引号里面是字符串，可以把里面的字符串输出。
【注意】符号特别要注意在英文输入法下输入
3.8去重（distinct）
SQL>selectdistinctdeptnofromemp;
3.9where与orderby
【例子】查询工资少于2000的员工，并排序
SQL>selectename,salfromempwheresal<=2000orderbysal;
3.10betwween…and
【例子】查询工资在1500到2500之间的员工，并排序
SQL>selectename,salfromemp
wheresalbetween1500and2500
orderbysal;
3.11where与and
【例子】查看工资为1250的销售人员
SQL>selectempno,ename,job,salfromempwherejob='SALESMAN'andsal=1250;
EMPNOENAMEJOBSAL
---------------------------------------
7521WARDSALESMAN1250
7654MARTINSALESMAN1250
【注意】where后面跟的条件，'SALESMAN'必须得跟数据库中保存的大小写一致，此处'SALESMAN'如果是小写'salesman'就查不到数据。
3.12where与or
【例子】查看没有奖金（comm）或者工资少于1500的员工
SQL>selectempno,ename,job,sal,commfromemp
2wherecommisnull
3orsal<=1500;
注意：
null在oracle中即不表示0，也不表示空，是一个不能确定未知数。
3.13函数nvl（X,Y）
【例子】查看员工的工资加上提成，哪个员工的工资是最少的?
SQL>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”的员工
SQL>selectename,jobfromempwhereenamelike'M%';
注释：
like是条件where中模糊查询的关键字；
“%”在sql中表示字符后面所有字符。
3.15in用法
【例子】查找哪些员工属于销售人员、分析师、管理人员？
SQL>selectename,jobfromemp
2wherejobin（'SALESMAN','ANALYST','MANAGER'）;
【注释】in表示在某个列中存在多个值均符合，或者使用or代替。
如下：
SQL>selectename,jobfromemp
2wherejob='SALESMAN'orjob='ANALYST'orjob='MANAGER';
3.16count用法
【例子】统计公司每个岗位有多少个员工。
SQL>selectjob,count（*）fromempgroupbyjob;
注释：
groupby是oracle数据库中的分组函数。
3.17groupby用法
【例子】统计公司每个部门分别有多少员工。
SQL>selectdeptno,count（*）fromempgroupbydeptno;
3.18groupby与orderby
【例子】统计公司每个部门分别有多少员工，并按照员工数量排序。
SQL>selectdeptno,count（*）fromemp
groupbydeptno
orderbycount（*）;
3.19函数round（X,Y）
【例子】查询员工入职的年限。
SQL>selectename,round（（sysdate-hiredate）/365,0）fromemp;
【注释】round（X,Y）：
是oracle数据库中的一个四舍五入函数，X表示这个数字需要进行四舍五入，Y表示在哪位数进行四舍五入，如果Y=0，则表示在个数据进行四舍五入，Y=2表示在保留小数点后面的两位数进行四舍五入。
3.20函数sum
【例子】统计公司本月需支付多少薪水.
SQL>selectsum（sal）+sum（nvl（comm,0））fromemp;
3.21函数avg
【例子】统计员工的平均薪水（保留到小数点后两位数）
SQL>selectround（avg（sal）,2）fromemp;
注释：
avg（sal）表示平均工资。
3.22函数max，min
【例子】统计员工最高，最低工资，以及差值。
SQL>selectmax（sal）,min（sal）,max（sal）-min（sal）fromemp;
注释：
max（sal）,min（sal）表示最高，最低工资
3.23having与groupby
【例子】查看哪些岗位的平均工资大于2500。
SQL>selectjob,avg（sal）fromemp
havingavg（sal）>2500
groupbyjob;
注意：
在使用groupby分组是，如有条件限制，需要使用having，而不能使用where.
上面的例子中，数据库首先使用groupby进行岗位分组，再使用avg（sal）求出每个岗位的平均工资，最后平均工资大于2500的，由having进行限制。
3.24创建一个表
SQL>createtableitems（itemnonumber
（2）,itemnamevarchar2（10））;
3.25删除一个表
SQL>droptableitems;
3.26主键、非空约束
【例子】创建一个有主键，以及非空约束的表。
SQL>createtableItems（
ItemNonumber
（2）constraintPK_itemsprimarykey,
ItemNamevarchar2（10）notnull）;
●constraint:
定义表中约束所需的关键字，后面跟约束名。
●primarykey：
主键约束的关键字，表示这一列为主键。
●notnull：
非空约束关键字。
3.27外键约束、函数to_date（）
【例子】创建一个Business表，外键为Items表的主键
SQL>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','YYYY-MM-DD'））;
to_date（）:
是oracle的一个内部函数，可以把字符串变成时间。
3.28唯一约束、条件约束
【例子】创建一个Computers表，里面price有条件约束（在3W以内）
SQL>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表的数据。
SQL>createtableBusiness_copy
as
select*frombusiness;
【注意】这种复制，只是复制了表的数据，但是原表的约束是没有复制过去的。
【例子2】将表Business_copy中的数据，复制到新建的空表Business中。
SQL>insertintoBusiness
select*fromBusiness_Copy;
或者：
insertintoBusiness（Busino,Businame,Itemno,Starttime）
select*fromBusiness_Copy;
3.30增加一个表字段
【例子】为表items增加一个manager的字段。
SQL>altertableitemsadd（managervarchar2（6））;
3.31修改表中某字段的属性
【例子】修改表items表中，字段manger的属性。
SQL>altertableitemsmodify（managervarchar2（8））;
3.32删除表中某字段
【例子】删除表items中的manager字段。
SQL>altertableitemsdropcolumnmanager;
4新增、修改、删除oracle用户以及授权
4.1创建数据库用户
【例子】创建数据库用户lisi，密码也为lisi。
SQL>createuserlisiidentifiedbylisi；
4.2为数据库用户授予权限
【例子1】将“连接”数据库的权限授予给lisi。
SQL>grantconnecttolisi;
●grant:
oracle数据库授权的关键字，表示“授权”的意思。
●connect：
oracle数据库的一个默认角色。
只有连接上数据库的权限。
【例子2】将scott用户的表emp的查询权限，授权给lisi用户。
a）使用scott用户登录oracle数据库；
b）执行SQL>grantselectonemptolisi；
【例子3】回收上例中的权限。
a）使用scott用户登录oracle数据库；
b）执行SQL>revokeselectonempfromlisi;
【例子4】scott用户将表emp的增、删、改、查权限都授予给lisi用户。
a）使用scott用户登录oracle数据库；
b）执行SQL>grantinsert,delete,update,selectonemptolisi;
【注意】用户lisi在对emp表进行操作时，必须在表明前加上前缀scott.
如下：
SQL>Select*fromscott.emp;
SQL>insertintoscott.emp（empno,ename,hiredate,sal,deptno）
values（799,'李四',sysdate,2000,10）;
4.3修改用户密码
【例子】将用户lisi的密码修改为”tiger”.
SQL>alteruserlisiidentifiedbytiger;
4.4删除用户lisi
SQL>dropuserlisi；
或者：
SQL>dropuserlisicascade（这将会删除与lisi相关联的表）
4.5查看当前用户拥有哪些权限
SQL>select*fromsession_privs;
4.6查看当前用户拥有哪些角色
【例子】查看scott用户拥有哪些角色。
SQL>select*fromuser_role_privs;
可以看到scott用户，具有2个角色，CONNECT和RESOURCE角色。
4.7查看connect角色有哪些权限
【例子】使用sys用户，查看connect角色有哪些权限。
SQL>select*fromdba_sys_privswheregrantee='CONNECT';
4.8创建具有connect和resource权限的用户
【步骤】
A、dba角色的用户登录；
B、执行：
SQL>createuserlisiidentifiedbylisi；
C、执行：
SQL>grantconnect，resourcetolisi；
【注释】开发人员来说，一般只需要拥有CONNECT和RESOURCE角色的用户即可。
5数据库的对象
问题：
oracle数据库中函数和存储过程的区别？
答：
（1）函数必须有返回值，而过程没有返回值。
升级会员 