oracle学习笔记.docx
- 文档编号:5862212
- 上传时间:2023-01-01
- 格式:DOCX
- 页数:59
- 大小:335.66KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 学习 笔记