ORACLE笔记.docx
《ORACLE笔记.docx》由会员分享，可在线阅读，更多相关《ORACLE笔记.docx（27页珍藏版）》请在冰豆网上搜索。
ORACLE笔记
第一章SQLPlus常用命令
1.connect建立连接connectscott/tiger;
2.password修改密码;
3.spoold:
\c,sqkl;
4.set环境变量值
Setlinesize100;
Setpagesize100;
创建和管理表
Orcle的数据类型：
varchar2（size）可变长的字符串，number（p,s）可变长的数值数据，date日期，long可变长字符数据最大2G，raw可变长二进制数据类型最大4KB，longraw可变长二进制数据，最大2GB，clob字符数据最大4GB，bfile存储外部文件的二进制数据最大4GB，rowid行地址，
Orcle数据表的命名规则：
必须以字母开头，必须在1–30个字符之间，必须只能包含A–Z,a–z,0–9,_,$,和#，必须不能和用户定义的其他对象重名，必须不能是Oracle的保留字
表的创建
创建空表CREATETABLEstu（
sidvarchar2（8）PrimaryKey,
namevarchar2（20）NotNull,
agenumber（3）,
addrvarchar2（40）
）;
拷贝一张表的全部信息
CREATETABLEnewtablename
ASSELECT*FROMexisttablename;
只拷贝已存在表的结构，不拷贝表的内容
CREATETABLEemp2ASSELECT*FROMempWHERE1=2;
修改表
添加新列（给emp2表增加新列）
ALTERTABLEemp2ADD（remarkVARCHAR2（100））;
更改现有列的数据类型和字段宽度（修改remark列宽为200字节）
ALTERTABLEemp2MODIFY（remarkVARCHAR2（200））;
更改列名（更改emp2表中remark列名为email）
ALTERTABLEemp2RENAMECOLUMNremarkTOemail;
删除表中现有列（删除emp2表中的email列）
ALTERTABLEemp2DROPCOLUMNemail;
添加或删除完整性约束条（将emp1表中的empno列设置为主键）
ALTERTABLEemp1ADDCONSTRAINTPK_emp2PRIMARYKEY（empno）;
删除主键或唯一键约束（删除emp2表中的主键约束）
ALTERTABLEemp2DROPCONSTRAINTPK_emp2;
重命名表将（emp2表的表名修改为newemp;）RENAMEemp2TOnewemp;
删除表（删除emp2表）DROPTABLEemp2;
添加数据
INSERTINTOtablename（field1，field2，…）Values（val1，val2,…）;
注意：
插入null和日期型字段
修改数据
UPDATEtablenameSETfield1=val1，field2=val2，…[WHERE子句];注意：
置空值
删除数据
1.DELETEFROMtablename[WHERE子句];注意：
WHERE子句中的空值判断
2TRUNCATETABLEtablename;
两种方法的区别：
DELETE删除，需要写日志和回滚段，能恢复。
TRUNCATE删除，不写日志和回滚段，不能恢复，但速度快。
第三章表及数据操作
表相关操作
查看用户定义的表
SELECTtable_nameFROMuser_tables;
查看用户定义的各种数据库对象
SELECTobject_name,object_typeFROMuser_objects;
查看用户定义的表,视图,同义词和序列
SELECT*FROMuser_catalog;
插入数据
向表中插入日期型值
1）SYSDATE记录当前系统的日期和时间
2）to_date（）函数。
to_date（'1982-01-01','yyyy-mm-dd'）
3.事务
以第一个DML语句的执行作为开始
以下面的其中之一作为结束:
COMMIT或ROLLBACK语句
DDL或DCL语句（自动提交）
用户会话正常结束
系统异常终了
COMMIT和ROLLBACK语句的优点
1）确保数据完整性。
2）数据改变被提交之前预览。
3）将逻辑上相关的操作分组。
回滚到保留点
使用SAVEPOINT语句在当前事务中创建保存点。
使用ROLLBACKTOSAVEPOINT语句回滚到创建的保存点。
？
所有员工工资涨10%
Updateempsetsal=sal*1.1;
？
更新7369号员工的工作和工资使其与7876号员工相同。
updateempset（job,sal）=（selectjob,salfromempwhereempno=7876）wheredeptno=7369；
？
把SMITH的部门号更新为70。
updateempsetdeptno=70whereename=‘SMITH’
？
删除部门名为SALES的员工信息。
deletefromempwheredeptno=（selectdeptnofromdeptwheredname='SALES'）
？
删除COMM为空的员工信息。
deletefromempwherecommisnull;
commit;
？
删除部门号为10的部门。
deletefromdeptwheredeptno=10;
第四章简单查询
1.select*fromempwheredeptnoin（10,20）;
2.%代表任意多个字符，_代表一个字符
3.使用ORDERBY子句排序
ASC:
升序默认
DESC:
降序
3.
？
查询SMITH的薪水、工作、所在部门
selectsal,job,deptnofromemp1whereename='SMITH'；
？
如何显示工资高于3000的员工
select*fromempwheresal>3000;
？
如何查找1982年以后入职的员工
select*fromemp1wherehiredate>to_date（'1982-01-01','yyyy-mm-dd'）；
？
如何显示工资在2000到2500之间的员工
select*fromempwheresalbetween200and2500
注：
字符和日期要包含在单引号中，字符大小写敏感
？
如何显示每个雇员的年薪
selectename年龄,sal*12年薪fromemp1;
？
如何显示部门号为10和20的员工信息
select*fromemp1wheredeptnoin（10,20）;
4.员工名字按小（大）写方式显示
selectlower（ename）,upper（ename）fromemp;
显示名字正好为5个字符的员工姓名
selectenamefromempwherelength（ename）=5;
显示所有员工姓名的前三个字符
selectsubstr（ename,1,3）fromemp;
以首字母小写的方式显示员工姓名
selectlower（substr（ename,1,1））||substr（ename,2,length（ename））fromemp;
？
如何显示首字母是S的员工姓名、工资
selectename,salfromempwhereenamelike'S%';
？
如何显示第三个字符为大写O的员工姓名和工资
selectename,salfromempwhereenamelike'__O%';
？
查找工资高于1000或者岗位是MANAGER，同时名字首字母为J的员工信息
select*fromempwhere（sal>1000orjob='MANAGER'）andenamelike'J%';
？
按工资从低到高的形式显示雇员信息
select*fromemporderbysal;
？
按部门升序、工资降序排序
select*fromemp1orderbydeptno,saldesc;
？
按年薪升序排序
select*fromemp1orderbysal*12desc;
？
Round函数
?
SELECTround（45.923,2）,round（45.923,0）,round（145.923,-2）FROMdual;
?
显示在1个月为30天的情况下，所有员工的日薪，忽略余数
selectround（sal/30）日薪fromemp1；
trim（leading/trailing/both‘子串’FROM‘字符串或列’）
----把子串从字符串或列的头/尾/两端删除，如缺省则默认为从两端删除
SQL>SELECTtrim（leading's'FROM'servicesservices'）FROMdual;结果：
ervicesservices
5.数值函数
SELECTround（45.923,2）,round（45.923,0）,round（145.923,-2）FROMdual;结果：
45.9246100
TRUNC（45.926,2）截断MOD（1600,300）求余FLOOR（45.926）向下去整CEIL（45.926）向上去整
6.日期类型
SYSDATE：
取系统日期函数
默认的日期格式是DD-MON-RR，更改默认的日期格式：
SQL>altersessionsetnls_date_format='yyyy-mm-dd';
MONTHS_BETWEEN返回两个指定日期之间的月数
显示入职30个月以上的员工信息Select*fromempwheremonths_between（sysdate,hiredate）>30
ADD_MONTHS返回给指定的日期加上指定的月数后的日期值（显示入职30个月以上的员工信息）
select*fromempwhereadd_months（hiredate,30）NEXT_DAY返回指定日期以后的第一个星期几的日期
返回当前日期的下一个星期二的日期
selectnext_day（sysdate,2）fromdual
LAST_DAY
返回指定日期当月的最后一天的日期值
selectlast_day（sysdate）fromemp1；
Selectround（sysdate,’year’）fromdual;
Selectround（sysdate）fromdual;
？
显示入职满10年的员工信息
select*fromempwhereadd_months（hiredate,120）？
显示各月倒数第三天受雇的所有员工
select*fromempwhere（last_day（hiredate）-hiredate）=3
select*fromempwherehiredate=（last_day（hiredate）-2）
？
显示员工进入公司的天数
selectename,sysdate-hiredatefromemp;
（在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
）
7.转换函数TO_CHAR，TO_DATE，TO_NUMBER
selectto_char（sysdate,’yyyy-mm-dd’）fromdual;
selectto_char（1210.73,'$9,999.00'）fromdual;
selectto_date（sysdate,’yyyy-mm-dd’）fromdual;
selectto_char（sysdate,'ccyear-mon-dd:
hh:
mi:
ssday'）fromdual；
8.通用函数
NVL（expr1,expr2）如果expr1为null，则返回expr2。
如果expr1不为null，则返回expr1
selectnvl（comm,0）fromemp;
NVL2（expr1,expr2,expr3）如果expr1为null，则返回expr3。
如果expr1不为null，则返回expr2
selectnvl（comm,comm||”元”,0）fromemp;
NULLIF（expr1,expr2）如果expr1＝expr2，则返回null；
否则返回expr1。
selectnullif（ename,’SCOTT’）fromemp;
第五章复杂查询
1.分组函数AVGCOUNTMAXMINSUM
SELECT[column,]group_function（column）,...
FROMtable
[WHEREcondition]
[GROUPBYcolumn]
[ORDERBYcolumn];
？
查询最高工资和最低工资
selectmax（sal）,min（sal）fromemp;
？
查询员工的平均工资和工资总和
selectavg（sal）,sum（sal）fromemp;
？
共有多少员工
selectcount（*）fromemp;
？
工资最高的员工的名字、工作岗位
selectename,jobfromempwheresal=（selectmax（sal）fromemp）;
？
工资高于平均工资的员工信息
select*fromempwheresal>（selectavg（sal）fromemp）;
？
工资低于平均工资且入职时间在80年前的涨10%
updateempsetsal=sal*1.1wheresal<（selectavg（sal）fromemp）andhiredate？
每个部门的平均工资和最高工资
selectavg（sal）,max（sal）fromempgroupbydeptno;
？
每个部门各岗位的平均工资和最高工资
selectavg（sal）,max（sal）fromempgroupbydeptno,job；
？
平均工资低于2000的部门号和平均工资
selectdeptno,avg（sal）fromempgroupbydeptnohavingavg（sal）<2000
不能在WHERE子句中使用组函数
可以在HAVING子句中使用组函数
？
想知道某员工所在的部门名，该怎么办
selectdnamefromemp,deptwhereemp.deptno=dept.deptnoandename='SMITH';
？
显示SALES部门位置及其员工姓名
Selectloc,enamefromemp,deptwhereemp.deptno=dept.deptnoanddname='SALES';
？
查找雇员名、工资及所在部门的名称
selectename,sal,dnamefromemp,deptwheredept.deptno=emp.deptno;
？
显示员工姓名、部门名，按部门号排序
selectename,dnamefromemp,deptwheredept.deptno=emp.deptnoorderbyemp.deptno
左外连接：
wheret1.column=t2.column（+）；
左表不变，右表+null
右外连接：
wheret1.column（+）=t2.column；
右表不变，左表+null
？
显示员工姓名、工资及工资级别
selecta1.ename,a1.sal,a2.gradefromempa1,salgradea2wherea1.salbetweena2.losalanda2.hisal;
？
显示某员工上级的名字
selectp1.ename,p2.enamefromempp1,empp2wherep1.mgr=p2.empno;
？
显示FORD的上级
selectp1.ename,p2.enamefromempp1,empp2wherep1.mgr=p2.empnoandp1.ename='FORD';
？
查询部门ACCOUNTING中薪水大于2000的员工信息。
select*fromemp,deptwhereemp.deptno=dept.deptnoanddname='ACCOUNTING'andsal>2000
？
谁的工资比SCOTT高?
select*fromempwheresal>（selectsalfromempwhereename='SCOTT'）;
？
显示与SMITH同一部门的所有员工
select*fromempwheredeptno=（selectdeptnofromempwhereename='SMITH'）;
？
查询与部门10的工种相同的员工姓名、工种
selectename,jobfromempwherejobin
（selectjobfromempwheredeptno=10）；
？
查找工资比部门30所有员工工资高的员工信息
select*fromempwheresal>all
（selectsalfromempwheredeptno=30）；
？
查找比部门30任意一个员工的工资高的员工
select*fromempwheresal>any
（selectmin（sal）fromempwheredeptno=30）；
？
查询与SMITH部门和岗位完全相同的员工
select*fromempwhere（job,sal）=
（selectjob,salfromempwhereename='SMITH'）；
？
查询高于自己部门平均工资的员工信息
selecta.*fromempa,（selectavg（sal）avgsal,deptnofromempgroupbydeptno）bwherea.deptno=b.deptnoanda.sal>b.avgsal；
？
查询高于自己部门平均工资的员工信息
select*fromempawhere
sal>（selectavg（sal）fromempbwhereb.deptno=a.deptno）
selecta.*fromempa,（selectavg（sal）avgsal,deptnofromempgroupbydeptno）bwherea.deptno=b.deptnoanda.sal>b.avgsal；
?
如何从某个雇员往他的上级列出该雇员的层次结构（从底到顶）
selectlpad（'',4*（level-1））||ename
name,empno,mgrfromemp
startwithempno=7369
connectbypriormgr=empno；
（colnamefora30）
？
创建视图
createorreplaceviewv_emp_20
as
select*fromempwheredeptno=20
第六章其他数据库对象
1.视图：
创建视图
CREATEVIEWempvu80
ASSELECTemployee_id,last_name,salary
FROMemployees
WHEREdepartment_id=80;
Withcheckoption字句能够确保DML只能在特定的范围内执行
当视图定义中包含以下元素之一时不能使用delete:
组函数
GROUPBY子句
DISTINCT关键字
ROWNUM伪列
当视图定义中包含以下元素之一时不能使用update:
组函数
GROUPBY子句
DISTINCT关键字
ROWNUM伪列
列的定义为表达式
当视图定义中包含以下元素之一时不能使用insert:
组函数
GROUPBY子句
DISTINCT关键字
ROWNUM伪列
列的定义为表达式
表中非空的列在视图定义中未包括
删除视图只是删除视图的定义，并不会删除基表的数据
DROPVIEWview;（DROPVIEWempvu80;）
2．序列：
创建序列
Createsequenceemp_sequence
Incrementby1
Startwith1
Nomaxvalue
Nocycle
Cache10;
修改序列
Altersequenceemp_sequence
Startwith1
Incrementby2
nomaxvalue
Cycle
Cache20；
Nextval返回序列中下一个有效的值currval存放序列的当前值
（Selectemp_sequence.nextvalfromdual;）
NEXTVAL和CURRVAL伪列不可用于下列场合：
（1）视图定义；
（2）带DISTINCT关键字的SELECT语句；
（3）带GROUPBY、HAVING或ORDERBY子句的
SELECT语句；
（4）SELECT、DELETE、UPDATE语句的子查询。
3.索引：
✓一种数据库对象
✓通过指针加速Oracle服务器的查询速度
✓通过快速定位数据的方法，减少磁盘I/O
✓索引与表相互独立
✓Oracle服务器自动使用和维护索引
既然索引能加快查询速度，是否索引数量越多越好呢？
为什么？
答：
不是，索引使用于数据量大的表，减少系统查找时间，从而优化系统，要是索引数量过多，不仅会占据大量物理空间，还极大的浪费系统资源。
以下情况适合创建索引:
•列中数据值分布范围很广
•列中包含大量空值
•列经常在WHERE子句或连接条件中出现
•表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%
下列情况不适合创建索引:
•表很小
•列不经常作为连接条件或出现在WHERE子句中
•查询的数据大于2%到4%
•表经常更新
•加索引的列包含在表达式中
D
升级会员 