oracle基础练习.docx
《oracle基础练习.docx》由会员分享，可在线阅读，更多相关《oracle基础练习.docx（21页珍藏版）》请在冰豆网上搜索。
oracle基础练习
基础概念:
数据库:
存储数据的数据库,Oracle一般只有一个全局数据库XE,ORCL。
表空间:
是Oracle存储数据的地方,由若干个数据文件组成,*.dbf。
模式和模式对象:
模式就是用户，模式对象是数据库对象的集合，比如表、视图、索引、存储过程。
卸载Oracle的注意事项:
0:
需要先备份重要数据
1:
得合法删除（通过程序自带的卸载工具卸载,如360,程序和功能）
2:
卸载完成后,需要手动移除安装文件夹
3:
查看系统服务,如果有Oracle开始的服务,删除服务
打开服务快捷方式:
window+R（services.msc）
安装Oracle的注意事项:
1.安装目录中不能有中文目录,最好不要有空格
2.最好不要安装到系统盘,怕数据丢失
Oracle:
8i/9i-->Oracle10g/11g-->Oracle12C
版本:
企业版（enterpriseEdition）
个人版（ExpressEdition）
占用端口号:
1521和8080,tomcat换端口号,否则登录Oracle数据库
SSH:
Struts+spring+hibernate
SSI:
SpringMVC+Spring+IBatis
配置监听器:
Oracle..Listener
OracleService
如何配置监听器:
主机名和listener里的不一样
Oracle的基本概念:
1.数据库:
存储数据的仓库,数据一般只有一个（个人版XE,企业版orcl）
2.用户（模式schema）:
Oracle是通过不同的用户来区分数据库对象
3.模式（用户/数据库）对象:
指的是这个用户所拥有的表/视图/索引/过程...
4.表空间:
是Oracle存储数据的地方,一个表空间对应一个或者多个的数据文件*.dbf
如何登陆Oracle数据库
1.pl/sqldeveloper
sqlplus:
系统用户:
sys（只能以sysdba,sysoper的身份登陆） system（normal身份）
登陆/切换用户的sql命令:
conn用户/密码[assysdba];
Oracle的sql应用
1>查询不重复的列值:
stuName,stuAge（）
selectdistinctstuName,stuAgefromstudent;
selectstuName,stuAgefromstudentgroupbystuName,stuAgehavingcount
（1）=1;
2>如何起别名?
（空格或者as）如何排序?
如何添加条件?
selectstuNameas姓名,stuAgeas"年龄"fromstudent;
orderby列名asc|desc;
where
3>如何根据现有表创建一个新表
createtable表名
as
select*fromstudent[where1=2];
创建新表并添加一列
createtable表名
as
selects.*,'T113'ascnamefromstudents;
常用的sql命令:
1.showuser:
显示当前登录的用户中括号里的超级管理员才加
2.conn用户名/密码[assysdba]:
切换用户,登录数据库
3.desc:
描述表结构
在Oracle中增删改语句会自动开启事务,必须提交事务
查看表:
select*from表名;
查看表结构:
desc表名;
sql语句的分类:
4类
数据操纵语音（DML）:
insertupdatedeleteselect
事务控制语音（TCL）:
commitrollbacksavepoint
数据定义语音（DDL）:
createdropaltertruncate
数据控制语音（DCL）:
grantrevoke
在editor中编辑,点击齿轮运行:
么事别乱敲回车,check约束可以直接写,选tables可以直接添加数据
失效后,更改:
tools-->pre..-->keyconf..-->resettodefault-->ok
Oracle常用的数据类型有哪些?
char varchar2 number date lob（blobclob）
标准日期格式:
（1-6月2016）to_date（2016-6-1,'yyyy-MM-dd'）date'2016-6-1'
或者直接编辑选择myobject,tables,editdata
分析函数:
rank（）over（partitionby列名orderby列名）
查询平均工资大于3000的部门
/*select*fromdeptd
where（selectavg（sal）fromemployeeewheree.deptno=d.deptno）>3000;*/
===================================================================================================================================
创建表空间注意事项:
1.数据文件不要放在容易看到和删除的地方（比如桌面,磁盘根目录）
2.表空间不能非法删除,否则数据库无法启动
修改表空间大小:
1.修改数据文件大小
alterdatabasedatafile
'd:
\data\d.dbf'
resize10M;
2.添加数据文件
alterdatafile
'd:
\data\d2.dbf'
size5M
autoextendon;
3.修改表空间为只读状态
altertablespacehelloreadonly;
altertablespacehelloreadwrite;
删除表空间
droptablespacehello
includingcontentsanddatafiles;连带文件夹和数据一同删除
创建用户
createuserzhao
identifiedbysa
defaulttablespacehello;
privilege权限
expire:
密码定期过期
授权:
系统权限:
grantconnect,resourcetozhao;
revokeconnect,resourcefromzhao;
grantcreatesynonymtozhao;
grantcreatepublicsynonymtozhao;
grantselectondepttopublic;
对象权限:
grantallonemptozhao;
revokeallonempfromzhao;
创建序列:
createsequenceseq_dept
startwith50
incrementby10;
1.添加数据
insertintodept（列名）values （seq_dept.nextval）;
2.查看当前序列值
selectseq_dept.currvalfromdual;
3.dropsequence名
创建同义词:
在其他用户中创建Scott的同义词;
createorreplacesynonymempforScott.emp;
创建公有同义词:
表是谁的谁创建（前提条件,其他用户得有权限访问同义词对应的对象）
createpublicsynonymempforScott;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
查询工资总额大于1000的部门信息（2种方式）
1.分组：
select*fromdept
wheredeptnoin（selectdeptnofromempgroupbydeptnohavingsum（sal）>1000）
2.子查询
select*fromdeptd
where（selectsum（sal）fromempewheree.deptno=d.detpno）>1000;
--删除数据库不删除结构
/*truncatetabledept;*/
--查询所有
/*select*fromdept;*/
--添加列
/*altertabledeptadd（loocvarchar2（10））;*/
--删除列
/*altertabledeptdropcolumnlooc;*/
--添加数据
/*insertintodeptvalues（'40','accp4','邯郸4'）;*/
--选择无重复的数据查询
/*selectdistinctdname,locfromdept;*/
--升序排列
/*select*fromdeptorderbylocASC;*/
--降序排序
/*select*fromdeptorderbylocDESC;*/
--用别名显示
/*selectdeptonas'学号',dnameas'课程',loc'地区',FROMdept;*/
--复制个表出来吧数据,和结构，复制过来
/*createtabledept2
as
select*fromdept;*/
----复制个表出来不复制数据,只结构，复制i过来
/*createtabledept3
as
select*fromdeptwhere1=2;*/
--查看表中的函数
/*selectcount
（1）fromdept;*/
--查看表中的函数（效率低）
/*selectcount（*）fromdept;*/
--取出列不重复的数据
/*selectdname,loc
fromdept
groupbydname,loc
having（count（dname||loc）<2）;*/
--事务控制语句
--commit提交事务
--rollback回滚事务
--savepoint 在事务中创建节点
-----------------------------
/*commit;
insertintodept2values（'01','accp01','邯郸01'）;
/*savepointa;
insertintodeptvalues（'4000','accp400','邯郸400'）;
rollbacktosavepointa;
select*fromdept;
;
select*fromdept;*\*/
------------------------------
--分页查询
/*SELECT*
FROM（SELECTe.*,rownumrn
FROM（SELECT*
FROMemployee
ORDERBYsalDESC
） e
）
WHERErn>=5ANDrn<=9;*/
-------------------------
--SQL操作符
--1.算数操作符号+-*/加减乘除
--2.比较运算符=<=>=ANDINLIKE
--3.逻辑运算符orandnot或者，和，非
--4.集合操作符nuion（联合）unionall（联合所有）intersect（交集）minus（减级）
--union联合查询两个（不重复）的数据
/*select*fromdept
union
select*fromdept2;*/
--union联合查询排序
/*selectlocfromdept
union
selectlocfromdept2
orderbyloc;*/
--unionall查询所有的数据
/*select*fromdept
unionall
select*fromdept2;
*/
--intersect交集返回两个都有的行
/*select*fromdept
intersect
select*fromdept2;*/
--minus查询第一个中在第二个里面没有的行
/*select*fromdept
miuns
select*fromdept2;*/
--链接操作符
--链接运算符||用于将两个或者多个字符串合并成一个字符串，或者将一个字符与一个数值合并在一起
/*selectdname||'_'||locfromdept;*/
--主键
/*altertabledept
addconstraintdeptonPRIMARYKEY（depton）;*/
--自增
--sql函数
--to_char把时间转换为varchar2数据
/*selectto_char（sysdate,'YYYY"年"fmMM"月"fmDD"日"HH24:
MI:
ss'）fromdual;*/
--将数值转换为字符串并且用货币当为前缀
/*selectto_char（1210.7,'$9,999.00'）fromdual;*/
/*selectto_char（sysdate,'YYYY"年"fmMM"月"fmDD"日"hh:
mi:
ss'）fromdual;*/
/*selectto_char（1200.2,'$9,999.00'）fromdual;*/
/*selectto_char（sysdate,'YYYY"年"fmMM"月"fmDD"日"HH24:
MI:
SS'）fromdual;
selectto_char（123.1,'$9,999.00'）fromdual;*/
--将charvarchar2转换为日期函数
/*selectto_date（'2017-01-01','yyyy-mm-dd'）fromdual;*/
--nvl的用法如果money2是个null那么什么和它想加都是null了，nvl意思是如果money2是个null就把它换成0
--selectstuname,money+nvl（money2,0）fromstuinfo;
--判断money2如果money2不是null就返回money+money2,如果是null就返回money.
/*selectstuname,nvl2（money2,money+money2,money）fromstuinfo;*/
--转换date和Java中的switch语句条件如果是01就是一月如果02就二月else就是后十月
/*select decode（to_char（day,'fmmm'）,'01','一月','02','二月','后十月'）fromstuinfo;*/
--插入数据
/*insertintoempvalues（1111,'雷老虎','管理',2,to_date（'1991-01-02','yyyy/mm/dd'）,1000,1000,12）;*/
-----------------------------------------------------
--分析函数
--rank
/*selectempno,ename,sal,DENSE_rank（）over（orderbysaldesc）as排名fromemp;*/
/*selectempno,ename,sal,ROW_NUMBER（）over（orderbysaldesc）fromemp;*/
---------------------------------------------------------------------------
--SQL综合练习
--显示员工的就职年度
--使用round对年份四舍五入
/*selectempno,ename,hiredate,to_char（round（hiredate,'mm'）,'YYYY'）||'年份'fromemp;*/
--列出只有3个雇员的部门having是在groupby后面使用
/*selectdeptno,count
（1）fromemp
groupbydeptno
havingcount
（1）>3;
*/
--查看是不是最后一天入职的
/*selectename,hiredate,last_day（hiredate）fromempwherehiredate=last_day（hiredate）;*/
--创建个表空间
/*createtablespaceEPET
datafile'D:
\orcale\app\Oracle\oradata\XE\EPET.DBF'
size5m
autoextendon;*/
--删除表空间数据文件需要手动删除
/*droptablespaceEPET;*/
--删除表空间数据文件一起删除
/*droptablespaceEPETincludingcontentsanddatafiles;*/
--修改表空间的大小改变数据文件的大小
/*alterdatabase
datafile'D:
\orcale\app\oracle\oradata\XE\EPET.DBF'
resize10m;*/
--表空间只读
/*altertablespaceEPETreadonly;*/
--表空间可读可写
/*altertablespaceEPETreadwrite;*/
--创建用户
/*createuserli
identifiedbyyapeng
defaulttablespaceepet;--默认表空间*/
--删除用户
/*dropuserlicascade;*/
--用户授权
/*grantresourcetoli;*/
--撤销权限
/*revokeresourcefromli;*/
--授权查询
/*grantselectonemptoli;*/
--序列
/*createtabletoys
（
toyidnumbernotnull,
toynamevarchar2（10）,
toydatedate
）;*/
/*createsequencese
startwith0
incrementby1
maxvalue2000
nocycle
cache30; */
/* insertintotoys（toyid,toyname,toydate）
values（se.nextval,'李逵',to_date（'2012/01/01','YYYY,MM,dd'））; */
--查看当前序列的值
/*selectse.currvalfromtoys;
*/
--更改序列
/*--dba给予创建同义词权限
grantcreatesynonymtoT111;*/
--创建同义词
/*createorreplacesynonymempforsystem.emp;*/
--dba给予创建公共同义词权限
/*grantcreatepublicsynonymtosystem;*/
--创建公共同义词
/*createorreplacepublicsynonymdforemp;*/
--把查询权限给所有人
/*grantselectonemptopublic;*/
--查看用户有哪些同义词
/*select*fromuser_synonyms;*/
--索引
--1：
B树索引是Oracle的标准索引用于数值，适用于高基数列
--2:
反向键索引，主要解决IO瓶颈问题的
--3：
位图索引用于取值较少的（主键不适合）
--表分区范围分区
/*
CREATETABLESALES1
（
SALES_IDNUMBER,
PRODUCT_IDVARCHAR2（5）,
SALES_DATEDATENOTNULL
）
PARTITIONBYRANGE（SALES_DATE）
（
PARTITIONP1VALUESLESSTHAN（to_date（'2013-04-1','yyyy-mm-dd'））,
PARTITIONP2VALUESLESSTHAN（to_date（'2013-07-1','yyyy-mm-dd'））,
PARTITIONP3VALUESLESSTHAN（to_date（'2013-10-1','yyyy-mm-dd'））,
PARTITIONP4VALUESLESSTHAN（to_date（'2014-01-1','yyyy-mm-dd'））,
PARTITIONP5VALUESLESSTHAN（maxvalue）
）;
--要查看在第三季度的数据
SELECT * FROMSALES1partition（P3）;
--要删除第三季度的数据
DELETEFROMSALES1partition（P3）;*/
--间隔分区
--创建间隔分区表
CREATETABLESALES2
（
SALES_IDNUMBER,
PRODUCT_IDVARCHAR2（5）,
SALES_DATEDATENOTNULL
）
PARTITIONBYRANGE（SALES_DATE）
升级会员 