oracle快速入门笔记.docx
《oracle快速入门笔记.docx》由会员分享,可在线阅读,更多相关《oracle快速入门笔记.docx(30页珍藏版)》请在冰豆网上搜索。
![oracle快速入门笔记.docx](https://file1.bdocx.com/fileroot1/2023-7/24/4ed53c36-72b4-48aa-931b-5b2b52306b88/4ed53c36-72b4-48aa-931b-5b2b52306b881.gif)
oracle快速入门笔记
小型数据库:
Access、Foxbase
中型数据库:
MySql、SqlServer、Informix
大型数据库:
Oracle、DB2、sybase
数据库的选择考虑因素主要有以下3点:
1.负载量有多大,用户有多少
2.成本
3.安全性
Sys超级管理员权限高于system
在命令行中输入:
setORACLE_SID=DAVE(实例名)
Sqlplus
Connsys/adminassysdba(assysdba不能少否则报错);也可使用connsystem/admin
Sqlplus常用命令:
创建用户-授权
和我们普通项目中的权限思想基本一致,相当于oracle数据库已经建好了用户和角色类,我们可以创建新的用户,把角色或权限直接授予用户,我们也可以创建新的角色。
自己不能删除自己的用户。
新创建的用户连登录数据库的权限都没有需要授权。
权限分系统权限(140多个)和对象权限(25个左右)。
系统权限是指用户对数据库的相关权限如:
建库、建表、建索引、建存储过程、建函数、登录数据库等。
对象权限:
用户对其他用户的数据对象(表、视图、存储过程、触发器、表空间、序列)操作的权限。
角色是权限的集合。
角色分:
预定义角色和自定义角色。
SQL>connsystem/admin;
已连接。
SQL>grantresourcetoxiaoming;
授权成功。
SQL>connxiaoming/m123;
已连接。
SQL>createtabletest(useridvarchar2(12));
表已创建。
授权:
Grantselectonemptoxiaoming;(将emp表的查询权限授予小明)
Grantallonemptozhangsan;(将emp表的所有权限增删改查等授予张三)
此时如果小明的表空间中也有emp表如何验证?
select*fromscott.emp;
删除权限:
revokeselectonempfromxiaoming;
对权限的维护:
grantselectonemptoxiaomingwithgrantoption;
withadminoptionVSwithgrantoption
不同点:
-withadminoption只能在赋予systemprivilege的时使用
-withgrantoption只能在赋予objectprivilege的时使用
-撤消带有adminoption的systemprivileges时,连带的权限将保留
例如:
1.DBA给了CREATETABLE系统权限给张三WITHADMINOPTION
2.张三建了若干张表。
3.张三将建表系统权限给李四
4.李四建了若干张表
5.DBA将张三的建表权限收回
结果:
张三‘STABLE依然存在,但不能创建新的TABLE了
李四’STABLE依然存在,他还保留着CREATETABLE系统权限
-撤消带有grantoption的objectprivileges时,连带的权限也将撤消
例如:
1.JEFF给了SELECTobjectprivileges在EMP上WITHADMINOPTION
2.JEFF给了SELECT权限在EMP上TOEMI
3.后来,撤消JEFF的SELECT权限
结果:
EMI的权限也被撤消了
建表
添加一个字段:
altertable表名add(classidnumber(4));
修改多个字段
SQL>altertablestumodify(stunonumber(4),stunamechar
(2));
删除表的某个字段:
SQL>altertablet_studropcolumnclassid;
Tablealtered
修改表的名字:
SQL>renamestutot_stu;
Tablerenamed
删除表:
droptalbet_stu;
insertintot_stuvalues(1,10001,'小明','1999-09-09',12300,15)
ORA-01861:
文字与格式字符串不匹配
SQL>altersessionsetnls_date_format='yyyy-mm-dd';
Sessionaltered
SQL>insertintot_stuvalues(1,10001,'小明','1999-09-09',12300,15);
1rowinserted
SQL>select*fromt_stu;
IDSTUNOSTUNAMEBIRTHDAYSALARYCLASSID
----------------------------------------------------------------
110001小明1999-9-91230015
SQL>
设置保存点:
savepointaa;
恢复至保存点:
rollbacktoaa;
删除表:
truncatetablet_class;删除表的所有数据,不写日志,速度很快,但无法找回删除的记录。
显示查询时间:
settimingon;
24rowsselecte;
Executedin0.438seconds
对表已有数据的复制:
insertintot_class(classid,classname)select*fromt_class;
selectsal*12+nvl(comm,0)*12“年薪”fromemp;
模糊查询:
%表示匹配任意多个字符_表示匹配一个字符
使用列的别名排序:
orderby1
显示每个部门的每种岗位的平均工资和最低工资。
其实就是按照多个字段分组groupbydeptno,job;
多表查询的条件:
不能少于表的个数-1如4张表的关联至少需要3个条件才能避免出现笛卡尔积。
多列子查询:
多列子查询则是指返回多列数据的子查询语句。
当多列子查询返回单行数据时,在where子句中可以使用单行比较符。
而返回多行数据时,在where字句中必须使用多行比较符。
(in,all,any)
select*fromempwhere(deptno,job)=(selectdeptno,jobfromempwhereename=‘Smith’);
使用子查询比较多个列的数据时,既可以使用成对比较,也可以使用非成对比较。
其中,成对比较要求多个列的数据必须同时匹配,而非成对比较则不要求多个列的数据必须同时匹配。
1.成对比较示例
SELECTename,sal,comm,deptnoFROMemp
WHERE(sal,nvl(comm,-1))IN(SELECTsal,nvl(comm,-1)FROMempWHEREdeptno=30);
2.非成对比较示例
SELECTename,sal,comm,deptnoFROMemp
WHEREsalIN(SELECTsalFROMempWHEREdeptno=30)ANDnvl(comm,-1)IN(SELECTnvl(comm,-1)FROMempWHEREdeptno=30);
oracle表取别名时不要加as否则报错
oracle分页:
共有3种方式:
select*from(
selecte.*,rownumrnfrom(select*fromemp)ewhererownum<=最大值)wherern>=最小值
2.如果只查询几列,只需要修改最里层视图即可
select*from(
selecte.*,rownumrnfrom(selectempno,enamefromemp)ewhererownum<=12)wherern>=9
3.如果想在分页中按某些字段排序也只修改最里层视图即可。
select*from(selecte.*,rownumrnfrom(selectempno,ename,salfromemporderbysaldesc)ewhererownum<=12)wherern>=9
用查询结果直接创建表
createtableemp_bak(id,name,sal)asselectempno,ename,salfromemp;
union:
该操作符用于取得两个结果集的幷集,当使用该操作符时会自动去掉结果集中重复行。
过滤掉重复的记录这点我之前不知道。
unionall:
两个结果集直接相加不去除重复行
intersect取交集
minus:
从集合A中排除集合B中的元素。
A可以比B小,不是数学意义上的减法,而是只要是这个元素在集合B中也存在就过滤掉。
事务一旦提交,之前设置的保存点将失效(消失)。
只读事务
如18点时统计本日销售量,之后新增或删除的记录都不在统计之列
1.首先以system/admin账户登录,查看emp_bak表数据并设置开启只读事务。
2.以proxy/proxy登录系统插入2条数据
proxy看到17条数据
4.proxy看到的还是14条数据
函数:
substr(列名,从第几个字符开始,截取几个字符);
将每个人名首字母大写,其余字母小写:
replace(列名,要替换的字符,替换后的字符)函数:
round(col,n)四舍五入到小数点后几位,如果不写n默认为整数
trunc(sal,n):
截取到小数点后第几位,默认为整数,砍掉的位置0;
floor(n):
返回<=n的最大整数
ceil(n):
返回>=n的最小整数
mod(m,n)取模函数10%3=1
add_months函数:
时间向前,或向后几个月后的时间
入职30年以上的员工:
selectfloor(sysdate-hiredate)"入职天数",trunc(sysdate-hiredate)fromemp;
last_day(日期)该日期月份的最后一天
转换函数:
to_char(hiredate,'yyyy/MM/ddhh24:
mi:
ss')
to_char(sal,'$99,999.99')显示工资时前面带$:
$2,975.00
to_char(sal,'L99,999.99')本地货币:
¥1,600.00
to_char(sal,'C99,999.99')国际货币:
CNY2,850.00
查询1980年入职的员工
selectempno,ename,hiredatefromempwhereto_char(hiredate,'yyyy')='1980';
*:
表连接条件为多个时可以用on(...and...and...)
如:
每个部门薪水最高的员工
以前:
selectename,e.sal,e.deptno,t.msfromempeinnerjoin(selectdeptno,max(sal)msfromempgroupbydeptno)tone.deptno=t.deptnowheree.sal=t.ms;
selectename,e.sal,e.deptnofromempeinnerjoin(selectdeptno,max(sal)msfromempgroupbydeptno)ton(e.deptno=t.deptnoande.sal=t.ms);
**查询出每个部门中薪水高于其部门平均薪水的职员信息。
SQL>selectename,e.sal,t.avg_salfromempeinnerjoin(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)ton(e.deptno=t.deptnoande.sal>t.avg_sal);
**显示每个部门的名称和平均薪水等级
SQL>selectt.deptno,d.dname,s.gradefrom(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)t
2innerjoindeptdont.deptno=d.deptno
3innerjoinsalgradeson(t.avg_salbetweens.losalands.hisal)
4orderby1asc;
**查询工资>1200的员工,按部门编号分组后平均薪水>1500的按倒序排列(不是最初想象的那么简单)。
SQL>selectename,sal,e.deptnofromempeinnerjoin(selectdeptnofromempwheresal>1200groupbydeptnohavingavg(sal)>1500)tone.deptno=t.deptno
wheree.sal>1200orderbye.deptnoasc,salasc;
**显示每个员工的姓名,工作,和经理人姓名
selecte1.ename,e1.job,e2.enamemgrfromempe1leftouterjoinempe2one1.mgr=e2.empnoorderbye1.empnoasc;
**求部门平均的薪水等级
**不用组函数求最高薪水:
selectdistinctsalfromempwheresalnotin(selectdistincte1.salfromempe1joinempe2on(e1.sal我的:
selectrownum,e.*from(select*fromemporderbysaldesc)ewhererownum=1;
**按薪水排序,查询第4-9高的员工信息
select*from(selectrownumrn,e.*from(select*fromemporderbysaldesc)ewhererownum<=9)twheret.rn>=4;
****求平均薪水最高的部门编号
selectt.*from(selectdeptno,avg(sal)avg_salfromempgroupbydeptnoorderby2desc)twhererownum=1;(每个部门平均薪水,排序后取第一个)
下面的写法很不常用:
部门平均薪水=最高的平均薪水利用了函数的嵌套
selectdeptno,avg(sal)asmaxfromempgroupbydeptnohavingavg(sal)=(selectmax(avg(sal))fromempgroupbydeptno);
**平均薪水最高的部门名称
selectd.*fromdeptdinnerjoin(
selectdeptno,avg(sal)asmaxfromempgroupbydeptnohavingavg(sal)=(selectmax(avg(sal))fromempgroupbydeptno)
)tond.deptno=t.deptno;
*****平均薪水的等级最低的部门名称,
select*fromdeptwheredeptnoin(
selectt.deptnofrom(
selectdeptno,avg(sal)avgfromempgroupbydeptno
)tinnerjoinsalgradegon(t.avgbetweeng.losalandg.hisal)
whereg.grade=
(selectmin(g.grade)from(selectdeptno,avg(sal)avgfromempgroupbydeptno)tjoinsalgradegon(t.avgbetweeng.losalandg.hisal))
);
如果还要显示平均薪水、平均薪水等级
【我的】selectd.dname,t.avg,t.gradefromdeptdinnerjoin(
selectt.deptno,avg,gradefrom(
selectdeptno,avg(sal)avgfromempgroupbydeptno
)tinnerjoinsalgradegon(t.avgbetweeng.losalandg.hisal)
wheregrade=
(selectmin(g.grade)from(selectdeptno,avg(sal)avgfromempgroupbydeptno)tjoinsalgradegon(t.avgbetweeng.losalandg.hisal))
)tond.deptno=t.deptno;
函数:
sys_context
selectsys_context('userenv','db_name')"数据库名",sys_context('userenv','terminal')"客户端机器名称",
sys_context('userenv','language')"语言",
sys_context('userenv','session_user')"数据库用户名",
sys_context('userenv','current_schema')"方案名"
fromdual;
数据库管理员的职责:
从下图可以看出:
system不具有sysoper的权限
通过对比看出:
sysoper比sysdba不具备的权限有:
创建数据库、删除数据库,改变字符集、恢复数据库等重要权限。
showparameter;显示参数
oracle导出表、方案、数据库
前提:
打开命令行,切换到进入oracle_home\bin目录下(直接在sqlplush命令行中无效)
D:
\oracle\product\10.2.0\db_1\BIN
1.导出某些表:
***示例:
expuserid=proxy/proxy@cslgsfile=d:
\db_bak.dmptables=(emp,dept,salgrade)
rows=ndirect=y[可选]
补充:
只导出表结构不导数据只需后面加rows=n,当数据量很大时可以增加direct=y直接导出方式来加快速度。
2.导出用户所有的表|导出方案
***示例:
exp[userid=]proxy/proxy@cslgs
file=d:
\expAll.dmp
owner=proxy(以proxy身份导出该方案的所有表)
【以system身份导出所有表】:
***示例:
expuserid=system/admin@cslgs
file=d:
\expSys.dmp
owner=(system,proxy)
3.导出整个数据库
要求该用户必须具备dba或exp_full_database的权限
***示例:
expuserid=system/admin@cslgs
full=y[关键是这句话]
file=d:
\expAllDb.dmp
oracle入表、方案、数据库
1.导入某些表
***示例:
impuserid=proxy/proxy@cslgsfile=d:
\db_bak.dmptables=(emp,dept,salgrade)
2.导入方案
***示例:
【导入自己的方案】
impuserid=proxy/proxy@cslgsfile=d:
\fangan.dmp
fromuser=proxytouser=proxy或者full=y(写一个即可)
3.导入数据库
***示例:
impuserid=proxy/proxy[实例名不用写oracle会自动创建]
full=yfile=d:
\db.dmp
从数据字典中查询所有的账号,密码:
SQL>selectusername,passwordfromdba_users;
查看proxy具有的角色:
select*fromdba_role_privswheregrantee='PROXY';
创建表空间:
createtablespacelobom_tbldatafile'D:
\oracle\product\10.2.0\db_1\database\data01.dbf'size100muniformsize128k[指定区的大小]autoextendon;
创建完成后就可以使用表空间了,在其上创建表
createtabletest_tbl_space(idint,namevarchar2(10))tablespaceds_weisy;
改为只读后无法插入数据,脱机后无法查询,如何消除只读呢?
altertablespace空间名readwrite
如果输入的empno不存在呢?
函数:
输入雇员姓名返回雇员年薪
在pl/sql中如何调用?
在java程序中如何调用?
语法如下:
createorreplacefunctionfunction_name(argu1[mode1]datatype1,argu2[mode2]datatype2,........)
returndatatypeis
begin
end;
执行varv1varchar2(100)exec:
v1:
=function_na
包体:
类似于java的类,调用其中的函数或过程时