oracle学习笔记.docx
《oracle学习笔记.docx》由会员分享，可在线阅读，更多相关《oracle学习笔记.docx（59页珍藏版）》请在冰豆网上搜索。
oracle学习笔记
oracle数据库
启动：
服务：
启动带有ServiceMYORA1简单理解成Oracle一个实例，一个数据库，oracle特点，你使用哪个数据库就进入哪个，用户名，可以不同，与其它的数据库不一样，
使用数据库中的表，根据用户权限来定
先启动service，再启动OraHomeListener,oracle实例已经启动
oracle卸载：
很麻烦，需要动注册表，要文件夹一个个删除，整体比较麻烦，
show显示当前用户，
oracle管理工具的介绍
oracle自带的工具软件，主要用于执行sql语句，pl\sql块，如何使用：
1）在开始—>程序oracleorachome90
applicationdevelopmentsql*plus
2）在运行栏中输入：
sqlplusw即可
3）在开始—>程序—>oracle
orahome90applicationdevelopmentsql*pluswordsheet
oracle的企业管理器（oemoracleenterprisemanager）
位置在开始：
程序：
oracle:
oracleoraclehome90:
enterprisemanagerconsole即可启动oracle的企业管理器，是一个图形界面环境！
pl/sqldeveloper属于第三方软件，主要用于开发，测试，优化oraclepl/sql的存储过程比如：
触发器，此软件oracle不带，需要单独安装。
sql*plus常用命令
连接命令
1）conn[ect]
用法：
conn用户名/密码@网络服务名【assysdba/sysoper】当用特权用户身份连接时，必须带上assysdba（代表超级用户，权限最高）或是assysoper（）
用当前用户转到另一个用户时，如果另一个用户登陆失败，则第一个用户也已经退出，
showuser;
scott；
connsystem/adminadmin
showuser
system
连接命令大致流程！
2）disc[onnect]
说明：
该命令用来断开与当前数据库的连接
3）passw[ord]
说明：
该命令用户修改用户的密码，如果想要修改其它用户的密码，需要用sys/system登陆。
\
passw
更改scottd口令
旧口令：
****
新口令：
****
重新键入新口令：
****
口令已更改
SQL>
4）showuser
说明：
显示当前用户名
5）exit
说明：
该命令会断开与数据库的连接，同时会退出
sql*plus
文件操作命令
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
oracle的查询语句有些和sql2000一样，有些是不一样的，比如：
select*fromempwherename=””;
这些基本都一样，但有些不一样，比如分页
selecttop2*fromemp;sql2000中能执行
oracle不能执行
select*fromempwhereename=”&name”;
会提示你输入一个值！
当输入一个值时，会替代&name所处的位置！
显示和设置环境变量
概述：
可以用来控制输出的各种格式，setshow如果希望永久的保存相关的设置，可以去修改glogin.sql脚本
1）linesize
说明：
设置显示行的宽度，默认是80个字符
sql>showlinesize
sql>setlinesize90
2）pagesize
说明：
设置每页显示的行数目，默认是14
注意：
表头和分界符，也算一行内容！
好处：
（在将来打印报表时，每页就显示多少）
用法和linesize一样
至于其它环境参数的使用也是大同小异
创建用户
概述：
在oracle中药创建一个新的用户使用createuser语句，一般是具有dba（数据库管理员）的权限才能使用！
createuserxiaomingidentifiedbym123;
identifiedby表示密码
给用户修改密码
概述：
如果给自己修改密码可以直接使用sql>password用户名
如果给别人修改密码则需要具有dba的权限，或者拥有alteruser的系统权限
sql>alteruser用户名identifiedby新密码
删除用户
概述：
一般以dba的身份去删除某个用户，如果用其它用户去删除用户则需要具有dropuser的权限。
比如dropuser用户名【cascade】
“cascade”在删用户时，必须注意：
如果要删除的用户，已经创建的有表了，那么就需要在删除的时候带一个参数cascade，表示：
这个用户中的表和用户一起删除，
用户管理的综合案例
概述：
创建的新用户时没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其制定相应的权限。
给一个用户赋值权限使用命令grant，回收权限使用命令revoke.
grantconnectxiaoming/m123;
权限：
两种，一种系统权限，一种是对象权限，
系统权限：
用户对数据库访问的相关权限，createsession（不管哪个用户，有了这个权限，才能登录到数据库上，）140多种
对象权限：
用户对其他用户的数据对象访问操作的权限，（过程，表，视图，包，）25个
常见的几种对象权限，select,insert,update,delete,allcreateindex…………；
角色：
权限，太多，使用时，比较麻烦，出现了角色，
分两种：
自定义角色，预定义角色，
自定义角色：
自己定义的把一些权限交给它！
预定义角色：
在安装数据库之后，就已经给你定义好了，
oracle最精华的地方，对用户的管理，对权限的管理！
精细度很细，
dba权限不要随便授予，
希望xiaoming用户可以去查询emp表查不到！
！
！
给xiaoming用户授予创建表的权限
grantresourcetoxiaoming;
这样xiaoming用户就可以创建表了，
createtabletest（userIdvarchar2（20））;
创建表，
查询表的结构
desctest；
和mysql一样！
希望xiaoming用户可以去scott中查询emp表，
grantselectonemptoxiaoming
授权查询emp表给xiaoming!
谁可以授权给xiaoming呢？
scott就可以
在查询时，
select*fromscott.emp;
这里提到方案！
希望xiaoming用户可以去修改scott的emp表
grantupdateonemptoxiaoming
希望xiaoming用户可以去修改/删除、查询、添加scott的emp表
grantallonemptoxiaoming
收回权限，revoke;
scott希望收回xiaoming对emp表的查询权限！
revokeselectonempfromxiaoming
撤销xiaoming用户对emp表的查询！
权限的传递：
对权限的维护
希望xiaoming用户可以去查询scott的emp表/还希望xiaoming可以把这个权限继续传给别人
如果你是对象权限，就在后面加入withgrantoption
grantselectonemptoxiaomingwithgrantoption;
如果是系统权限
system给xiaoming权限时：
后面加上adminoption
grantconnecttoxiaomingwithadminoption;
如果scott把xiaoming对emp表的权限给回收了，那么xiaohong的权限会怎么办？
通杀，株连，把xiaoming的权限收回后，xiaohong的权限也回收了，！
使用profile管理用户口令
概述：
profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile.当建立用户没有指定profile选项，那么oracle就会将default分配给用户。
1）账户锁定
概述：
指定该账户（用户）登陆时最多可以输入密码的次数，也可以指定用户锁定的时间（天）一般用dba的身份去执行该命令
例子：
指定scott这个用户最多只能尝试3次登陆，锁定时间为2天，让我们看看怎么实现。
sql>createprofilelock_accountlimit
failed_login_attempts3password_lock_time2;
sql>alteruserteaprofilelock_account;
3代表尝试的次数，2代表多少天，至少一天，lock_account是一个名字，可以随便命名！
2）给账户（用户）解锁
sql>alteruserteaaccountunlock;
红色字表示固定格式，不能修改
3）终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成，同样这个命令也需要dba身份来操作，
例子：
给前面创建的用户tea创建一个profile文件，要求该用户每隔10天要修改自家的登陆密码，宽限期为两天
sql>createprofilemyprofilelimitpassword_life_time10password_grace_time2;
sql>alteruserteaprofilemyprofile（把某个文件分配给某个用户）
口令历史
概述：
如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史，这样oracke就会将口令修改的信息存放到数据字典中，这样当用户修改密码时，oracle就会对新旧密码进行比较，当发现新旧密码一样时，就提示用户重新输入密码。
例子：
1）建立profile
sql>createprofilepassword_historylimitpassword_lift_time10password_grace_time2password_reuse_time10
password_reuse_time//指定口令可重用时间，即10天后就可以重用，
2）分配给某个用户，
删除profile
概述：
当不需要某个profile文件时，可以删除该文件，
sql>dropprofilepassword_history[cascade]（password_history指的是删除的名字！
）
oracle表的管理
表名和列的命名规则
必须以字母开头
长度不能超过30字符
不能使用oracle的保留字
只能使用如下字符A-Z,a-z,0-9,$,#等
字符型
char定长最大2000字符
例子：
char（10）‘小韩’前四个字符放‘小韩’，后添6个空格补全
占用空间，但效率很高，一般来讲，将固定字长放进去后，查询速度非常快，比如身份证，是定长，用char型，速度极快！
整体比较！
速度快，
varchar2（20）变长最大4000字符。
一个个字符比较，速度较慢！
varchar2是对varchar优化后的类型！
例子：
varchar2（10）‘小寒’oracle分配四个字符。
这样可以节省空间
clob（characterlargeobject）字符型大对象最大4G
数字类型，
number范围10的-38次方—10的38次方可以表示整数，也可以表示小数
number（5,2）
表示一个小数有5位有效数，2位小数，范围-999.99—999。
99
number（5）表示一个五位整数
范围-99999—99999
日期类型
date包含年月日和时分秒
timestamp这是oracle9i对date数据类型的扩展。
图片
blob二进制数据可以存放图片/声音4G
一般来讲，不会把图片和声音发在数据库中，在数据库中放的一般是地址！
如果放在数据库中，那么是出于安全性考虑，安全性高！
建表
学生表
sql>createtablestudent（
--表名
xhnumber（4）,--学号
xmvarchar2（20）,--姓名
sexchar
（2）,--性别
birthdaydate,--出生日期
salnumber（7,2）–奖学金（注意：
最后一条后面没有逗号！
）
）;
添加一个字段
sql>altertablestudentadd（classidnumber
（2））;
修改字段的长度
sql>altertablestudentmodify（xmvarchar2（30））;
修改字段的类型/或是名字（不能有数据）
sql>altertablestudentmodify（xmchar（30））;
删除一个字段
sql>altertablestudentdropcolumnsal;//这个最好不要用！
否则会出现严重错误！
修改表的名字
sql>renamestudenttostu;
删除表
sql>droptablestudent;
所有的字段都插入
insertintostudentvalues（‘A001’,’张三’，‘男’，‘01-5月-05’，10）;
ORACLE中默认的日期格式‘DD-MON-YY’dd日子（天）（5月“月”字不能省掉！
）
mon月份yy2位的年‘09-6月-99’1999年6月9号改成日期的默认格式
altersessionsetnls_date_format=”yyyy-mm-dd”;（定义日期的格式，）
修改后，可以用我们熟悉的格式添加日期类型：
insertintostudentvalues（‘A002’,’MIKE’,’男’，‘1905-05-06’，10）;
注：
默认方式（insertintostudentvalues（1,’xiaoming’,’男’，‘11-12月-1997‘，2345.6）
插入部分字段
insertintostudent（xh,xm,sex）values（‘A003’,’JOHN’,’女’）;
插入空值
insertintostudent（xh,xm,sex,birthday）
values（‘A004’,’MARTIN’,’男’,null）;
查询为空值内容：
select*fromstudentwherebirthdayisnull;
查询不为空的：
select*fromstudentwherebirthdayisnotnull;
改一个字段
updatestudentsetsex=’女’wherexh=’A001’;
比如把薪水降到一半，
updatestudentsetsal=’sal/2’wheresex=’男’
修改多个字段
updatestudentsetsex=’男’,birthday=’1980-04-01’多个字段中间打一个逗号
wherexh=’A001’;
修改含有null值的数据
不能等于null来查询，要用isnull;
删除数据
deletefromstudent;
删除所有记录，表结构还在，写日志，可以恢复的，速度慢，
droptablestudent；删除表的结构和数据
deletefromstudentwherexh=’A001’;删除一条记录
truncatetablestudent;
删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
回滚命令：
恢复表
savepointa;设置回滚点
在删除之前，先savepointaa
删除后，在用rollbacktoaa,表内容又会回来
有经验的数据库管理员，在用表时，先建立一个回滚点，savepointa
不做设置时，保存点只有一个，在做这个时，上个保存点，会被覆盖掉
ORACLE表基本查询
SELECT语句
clear:
清屏命令！
查看表结构：
descdept;
查询所有列：
select*fromdept;
查询指定列
selectename,sal,job,deptnofromemp;
如何取消重复行
selectdistinctdeptno,jobfromemp;
语句大小写不区分，里面内容时区分大小写的，
使用算数表达式
？
显示每个雇员的年工资
selectsal*12,enamefromemp;
给列取一个别名：
selectsal*13‘年工资’，enamefromemp;
selectsal*13+comm.*13‘年工资’，enamefromemp;
在oracle中如果有一个值为null,其它也为null
如果comm为空，那么年工资也为空了，
所以表达式要进行修改
selectsal*13+nvl（comm,0）*13……；
nvl（comm,0）表示如果comm为null,就以0来计算，如果有数字，就以原来的计算
使用列的别名
selectename‘姓名’
使用where子句
如何显示工资高于3000的员工
如何查找1982.1.1后入职的员工
如何显示工资在2000直2500的员工情况
如何使用like操作符
%：
表示0到多个字符_:
表示单个字符
？
如何显示首字符为S的员工姓名和工资
？
如何显示第三个字符为大写0的所有员工的姓名和工资
在where条件中使用in
?
如何显示empno为123,345,800……的雇员情况
使用isnull的操作符
？
如何显示没有上级的雇员的情况
使用逻辑操作符号
？
查询工资高于500或是岗位为MANAGER的雇员，同时还要满足他们的姓名首写字母为大写的J
使用orderby字句
？
如何按照工资的从低到高的顺序显示雇员的信息
默认即可asc，从高到底，需要orderbysaldesc;需要加上关键词desc修饰
？
按照部门号升序而雇员的工资降序排列
使用列的别名排序
selectename,sal*12‘年薪’fromemporderby‘年薪’asc;
别名需要使用“来注释，
分页查询
按雇员的ID号升序取出
（很难理解）
复杂查询
说明：
在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给大家介绍较为复杂的select语句
*数据分组-max,min,avg,sum,count（注：
）
?
如何显示所有员工中最高工资和最低工资
selectename,salfromempwheresal=（selectmax（sal）fromemp）;
里面有一个分组函数，则其它的也必须是分组函数，否则就会出错！
selectmin（sal）,max（sal）fromemp;
？
显示所有员工的平均工资和工资总和
？
计算共有多少员工
扩展要求：
？
请显示工资最高的员工的名字，工作岗位
？
请显示工资高于平均工资的员工信息
select*fromempwheresal>（selectavg（sal））;
groupby和having子句
groupby用于对查询的结果分组统计
having子句用于限制分组显示结果。
？
如何显示每个部门的平均工资和最高工资
selectavg（sal）,max（sal）,deptnofromempgroupbydeptno;
分组的字段一定要出现在里面，否则无法分组
？
显示每个部门的每种岗位的平均工资和最低工资
selectavg（sal）,max（sal）,deptno,jobfromempgroupbydeptno,job;
？
显示平均工资低于2000的部门号和它的平均工资
selectavg（sal）,min（sal）,deptnofromempgroupbydeptnohavingavg（sal）<2000;
扩展要求：
对数据分组的总结
1分组函数只能出现在选择列表、having、orderby子句种
2如果在select语句中同时包含有groupby、having,orderby那么他们的顺序是groupby,
having,orderby
3在选择列中如果有列，表达式、和分组函数、那么这些列和表达式必须有一个出现在groupby子句种，否则就会出错
如：
selectdeptno,avg（sal）,max（sal）fromempgroupbydeptnohavingavg（sal）<2000;
这里deptno就一定要出现在groupby中
多表查询
多表查询是指基于两个和两个以上的表或是视图的查询，在实际应用中，查询单个表可能不能满足你的要求，（如显示sales部门位置和其员工的姓名），这种情况下需要使用到（dept表和emp表）
selecta1.ename,a1.sal,a2.dnamefromempa1,depta2wherea1.deptno=a2.deptno;用到迪科尔基
在同时查两张表时，会把两张表连接一起会用到笛卡尔积14*4=56；
如果用=连接起来，就会排出笛卡尔集了；
笛卡尔集规定：
多表查询的条件是至少不能少于表的个数-1，如果说不能满足，肯定是错的
？
如何显示部门号位10的部门名，员工名和工资
selecta1.dname,a2.ename,a2.salfromdepta1,empa2wherea1.deptno=a2.deptnoanda1.deptno=10;
子查询：
是指嵌入在其它sql语句中的select语句，也叫嵌套查询
单行字查询
单行子查询是指只返回一行数据的子查询语句
select*fromempwheredeptno=（selectdeptnofromempwhereename=’smith’）;
数据库在执行sql时，是按照从右到左进行的（不确定因素）
在选择语句时，一般要把最筛选数据的语句放在最右边，速度会大大的提高
多行子查询
多行子查询指返回多行数据的子查询
多表查询（最多在4—5张之内，否则过于复杂，不利于维护）
如何显示工资比部门30的所有员工的工资高的员工的姓名。
工资和部门号
selectename,sal,deptfromempwheresal>all（selectsalfromempwheredept
升级会员 