Oracle 笔记.docx
《Oracle 笔记.docx》由会员分享，可在线阅读，更多相关《Oracle 笔记.docx（53页珍藏版）》请在冰豆网上搜索。
Oracle笔记
第1页
1.Oracle的使用
1.1.SQLPLUS的命令
初始化表的位置：
setNLS_LANG=american_america.us7ascii （设置编码才可以使用下面脚本）
cd$ORACLE_HOME/rdbms cddemo summit2.sql
*********************************
我们目前使用的是oralce9i 9201版本
select*fromv$version;
恢复练习表命令：
sqlplus **/**@summit2.sql //shell要在这个文件的位置。
登陆oracle的命令：
sqlplus 用户名/密码
show user 显示当前登陆的身份.
set pauseon
set pauseoff 分页显示.
oracle中默认日期和字符是左对齐，数字是右对齐
tableorviewdoes not exist;表或示图不存在
edit命令用于自动打开vi修改刚修执行过的sql的命令。
修改方法二：
l 3先定位到行 c /旧串/新串
执行出错时，利用错误号来查错误:
!
oerrora942 （装完系统后会装一个oerr工具，用于通过错误号来查看错误的具
体信息）
想在sql中执行unix命令时，把所有的命令前加一个!
就可以， 或者host（用于
从sql从切换至unix环境中去）
/*** 初次使用时注意 ****
运行角本时的命令：
先切换到unix环境下，cd$oracle_home cdsqlplus cddemo下面有两个角本
建表语句。
第2页
@demobld.sql
sqlplusnanjing/nanjing@demobid.sql直接运行角本，后面跟当前目录或者是绝对
路径
保存刚才的sql语句：
save命令 第二次保存时要替换之前的角本save
文件名 replace
把刚才保的sql重新放入 buffer中
spoolon 开启记录
spooloff 关闭记录
spool 文件名 此命令会把所有的操作存在某个文件中去
常见缩写：
nlsnationallanguagesupport国家语言支持
1.2.SQL的结构
|DDL 数据库定义
|DML 数据库管理
SQL――Commitrollback
|DCL 数据库控制
|grant+revoke 权限管理
表分为：
系统表（数据字典），用户表
注：
知道数据字典可以更便于使用数据库。
1.3.SQL语句
1.3.1.纵向投影操作select
select*fromstudent;
selectname||’（‘||id||’）’EMPLOYEEfromemployee;
selectname,salary*13fromemployee;
NVLfunction
如果原来的数值是null的话，由指定数值替代。
selectlast_name,title,salary*NVL（commission_pct,0）/100COMMfroms_emp; 第3页
1.3.2.column使用
column（col） columnNameclear/format/heading/justifyformat
columnsalaryformat$9999999.00 设置数字显示形式
columnnamefromata15; 设置字符串显示15个字符
columnsalaryjustifyleft/right/center 输出格式
columnsalaryheadingtext 设置显示的字段名
columnclear清除格式
columnlast_name;显示该字段名所用的格式
columnsalaryjustifyleftformat$99,999.00 （定义工资的显示形式）
1.3.3.orderby
ORDERBY 排序 升序和降序 ASC 升序（默认） DESC降序
select*froms_emp orderbydept_id,salarydesc
部门号升序，工资降序
关键字distinct也会触发排序操作。
select*fromemployeeorderby1; //按第一字段排序
NULL被认为无穷大。
orderby可以跟别名。
1.3.4.where 选择操作（横向投影）
where条件一定是根据某个字段来进行过滤操作.
select*froms_emp wheredept_id=42; 查看部门号为42的所有员工
select*froms_emp wheresalary>1000 查看工资高于1000的所有员工
selectsalaryfrom s_empwherefirst_name='Geroge' 找出名字为Geroge的员
工的工资数
select table_namefrom user_tables wheretable_name='S_EMP'; 查某个具
体表名时，表名的字符串必须要为大写
或者采用 upper（table_name）
select*fromuser_talbeswheretable_namelike‘s\_%’escape‘\’;
使用转义字符对关键字进行转义。
逻辑运算：
BETWEEN AND 在什么之间 第4页
NOT BETWEEN AND 注意区间：
[ ]是一个闭区间
IN（LIST） 在某个集合中
NOT IN （list） 空值会有影响 （等于list其中任何一个就行，
为提高效率常把比例高的放在前面）
LIKE 模糊配置
NOT LIKE 通配比较
ISNULL 是空
AND
OR
NOT
练习3：
（查出s_emp表中所有员工的一年的总收入）
selectfirst_name,salary*12*（1+nvl（commission_pct/100,0））"yearsalary"
froms_emp;
nvl函数 专用于处理空值的影响.
练习4:
（找出表名以S_开头的所有表）对于一些特殊字符，要用到escape转义,并
不是一定要用\，escape后面定义是什么字符为转义字符，那就用哪个字符
select table_namefromuser_tableswhere table_namelike 'S\_%' escape'\';
1.3.5.单行函数
单行函数：
（dual 哑表）
字符函数：
lower 转小写 select lower（'SQLPLUS'） fromdual;-->对纯字
符串处理的时候
upper 转大写 select upper（'sqlplus'） fromdual;
initcap 首字符大写 select initcap（'tarena'）fromdual;
concat 连接字符串 select concat（first_name,last_name） from
s_emp;等效于||
substr 求子串 select substr（'tarenasd0603',1,6）fromdual;（取前
六个字符） selectsubstr（'tarenasd0603',-2）fromdual;（取后两个字符）
length 求字符长度
select length（'tarena'）fromdual;
nvl 空值函数 两个参数的类型要匹配，统一的，表示：
如果有，则
返回前面的参数，如果没有就返回后面的参数
eg:
selectfirst_name,salaryfroms_empwherelower（first_name）='george';
select first_name,substr（first_name,-2）from s_emp; （查出s_emp表中所有
用户名字的最后两个字符）
默认的是从左向右，如果是-2则表示从右向左数
练习5：
select first_name ,salary froms_emp where 第5页
lower（first_name）='george';
数值函数：
round 函数（四舍五入） select round（45.935,2）fromdual; 不带参数时默认
为0位小数
trunc函数（截取，不管后面的数字） select trunc（45.995,1）fromdual;
日期函数：
oracle数据库中存放时间格式的数据，是以oracle特定的格式存贮的，占7个字
节，与查询时显示的时间格式无关，具体哪个字节表示什么，我不太清楚，请高
手补充。
存贮的时间包括年月日时分秒，最小精度为秒，不存贮秒以下的时间单
位。
因此在一些前台支持毫秒级的程序（如PB客户端程序）连接到oracle数据库
时应注意这点。
查询时显示的时间格式，由会话环境决定，或者由用户定义，与
数据库无关。
selectsysdatefromdual; 从伪表查系统时间，以默认格式输出。
sysdate+（5/24/60/60）在系统时间基础上延迟5秒
sysdate+5/24/60 在系统时间基础上延迟5分钟
sysdate+5/24 在系统时间基础上延迟5小时
sysdate+5在系统时间基础上延迟5天
所以日期计算默认单位是天
内置函数：
months_between（sysdate,addmonth（sysdate,5））//两个月有多少天。
add_months（sysdate,-5）在系统时间基础上延迟5月
add_months（sysdate,-5*12）在系统时间基础上延迟5年
last_day（sysdate）一个月最后一天
next_day（sysdate,’Friday’）下个星期星期几。
round（sysdate,’day’） 不是四除五入了，是过了中午留下，不过的略掉
trunc（sysdate,’month’）不到一月都省略
例子：
上月末的日期：
selectlast_day（add_months（sysdate,-1））fromdual;
本月的最后一秒：
selecttrunc（add_months（sysdate,1）,'month'）-1/24/60/60fromdual
本周星期一的日期：
selecttrunc（sysdate,'day'）+1fromdual
年初至今的天数：
selectceil（sysdate-trunc（sysdate,'year'））fromdual;
格式转换函数：
to_char显示日期：
从数字转化为char to_char（date,'格式'）
从日期转化为char to_char（date, 'fmt'）
第6页
yyyy 2007 年份
year twothousandseven 年份
mm 03 （格式缩写显示也缩写）
month march 月份
dy fri 星期几缩写
day Friday 星期几
dd 16 一个月第几天
mi 30 分钟
ss 35 秒钟
hh 18 小时
rr 07 年 最近时间
yy 07 年 当前世纪年份
selectto_char（sysdate,'yyyymmddhh24:
mi:
ss'）fromdual;
selectto_char（sysdate,'fmyyyymmddhh24:
mi:
ss'）fromdual;
查出三月分入职的员工：
selectfirst_name,start_datefroms_empwhereto_char（start_date,'mm'）='03';
to_date表达日期：
字符转日期
select to_date（'20001120','yyyymmdd'） fromdual;
select round（to_date（'10-OCT-06','dd-mon-RR'））from dual;
to_number
字符转数字
selectto_number（'10'） from dual;
函数、表达式、隐式数据类型转换会导致索引用不上，where条件后面只能放单
行函数，它起了一个过滤的的作用。
1.3.6.组函数
groupby 分组子句 对分组后的子句进行过滤还可以用having 条件
对分组后的条件进行过滤 where是对记录进行过滤
avg（distinct|all）求平均值
count（distinct|all）统计 第7页
max（distinct|all）求最大值
min（distinct|all）求最小值
sum（distinct|all） 求和
（所有组函数会忽略空值,avg sum只能作用于数字类型）
求有提成员工的提成的平均值；
select avg（nvl（commission_pct,0） ）froms_emp;
有多少人有提成：
select count（commission_pct）from s_emp;
count（*） 用于统计记录数：
select sum（commission_pct）/count（*） from s_emp;
员工分部在多少个不同的部门：
count 默认为作all的动作
select count（dept_id） froms_emp;
select count（distinctdept_id）from s_emp;
求各个部门的平均工资：
group by 子句也会触发排序
select dept_id, avg（salary）aa from s_emp groupby dept_id
orderby aa; //对平均工资排序
select dept_id, avg（salary）aa from s_emp groupby dept_id;
注意：
groupby子句后面跟有条件只能是组函数查询的结果中的字段,所以我
们会人为在结果要加入一些groupby 要用的字段，如：
dept_id可能不想要。
select region_id,count（*） from s_dept此句会有错，count（*）是单组分组函
数，如果加上groupbyregion_id就是找出同地区的部门数量。
select max（region_id） ,count（*）from s_dept; （强制语法上可以正确,但是
不能保证结果也会正确）
求各个部门不同工种的平均工资：
select dept_id,title, avg（salary） froms_emp group bydept_id,
title ;
哪些部门的平均工资比2000高:
select dept_id, avg（salary）aa froms_emp groupby（dept_id） having
avg（salary）>2000;
除了42部门以外的部门的平均工资:
select dept_id , avg（salary） from s_emp groupby（dept_id）having
dept_id!
=42; 第8页
select dept_id , avg（salary） from s_emp where dept_id!
=42
groupby（dept_id）;（此种sql效率要高，先过滤）
再计算）
where 单行函数。
having 组函数。
求各个部门的平均工资:
//这样统计不详细
select max（d.name）, avg（s.salary） from s_emp s, s_dept dwhere
s.dept_id=d.id groupby d.name;
//****这问题很经典，为了过oraclesql语法关而写max（d.name） ***
select max（d.name） ,avg（e.salary） ,max（r.name） froms_empe, s_dept
d,s_region r where e.dept_id=d.id and d.region_id=r.id
group by d.id;
1.3.7.多表连接
多表连接操作：
两表没有任何关联时会产生迪卡尔机：
select first_name,name from s_emp,s_dept;
1） 等值连接：
练习一:
查看员工的姓名和员工部门号:
（要考虑到表中实际数据中空值的影响）
select first_name, namefrom s_empe,s_dept dwheree.dept_id=d.id;
同时起了别名
select first_name, namefrom s_empe,s_dept dwheree.dept_id=d.idand
e.first_name='George';
具体到哪个人所在的部门
表的两边有空值的话，不会显示出来。
练习二：
每个员工所在的部门和部门所在的地区
select first_name,s_dept.name,s_region.namefroms_emp,s_dept,s_region
where
s_emp.dept_id=s_dept.id
and 第9页
s_dept.region_id=s_region.id;
等价于
selectfirst_name,d.name,r.name
froms_empe,s_deptd,s_regionr
wheree.dept_id=d.idandd.region_id=r.id;
等值连接：
练习三:
找出每个员工和每个员工的工资级别
select a.ename,a.sal,b.gradefromempa,salgradeb wherea.salbetween
b.losalandb.hisal;
select a.ename,a.sal,b.gradefrom empa,salgradeb wherea.sal>=b.losal
and a.sal<=b.hisal;
2） 自连接：
（又名：
内连接） 当一个表的插入行之间有了关系时就发生了
select first_name ,manager_id from s_emp;
l查出所有员工的部门领导的名称：
（这种sq会少一条记录，总经理没有被配置
上）
select e.first_name,m.first_name froms_empe,s_empm where
e.manager_id=m.id; //关键是同一张表用不同的别名
3） 外连接：
+）的一方会模拟一条记录配置另一方）这就称为外连接,不（防止空值忽略，用（
加（+）一个记录都不能少；
select e.first_name,m.first_name froms_empe,s_empm where
e.manager_id=m.id（+）;
+号放在哪边就表示在哪边补空，来跟对方来匹配，使得数据一个都不会漏掉，
这个例子中的领导有可能会没有（最高领导就再没有领导了，所以就
＋号放在可能出现空值的一边）
标准写法：
显示没有员工的部门
select distinctd.name
from
s_empe,s_deptd 第10页
where
e.dept_id（+）=d.id
and
e.dept_idisnull
显示有员工的部
升级会员 