Oracle 笔记Word格式.docx
- 文档编号:16431303
- 上传时间:2022-11-23
- 格式:DOCX
- 页数:53
- 大小:44.81KB
Oracle 笔记Word格式.docx
《Oracle 笔记Word格式.docx》由会员分享,可在线阅读,更多相关《Oracle 笔记Word格式.docx(53页珍藏版)》请在冰豆网上搜索。
建表语句。
第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条件一定是根据某个字段来进行过滤操作.
wheredept_id=42;
查看部门号为42的所有员工
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
注意区间:
[
]是一个闭区间
IN(LIST)
在某个集合中
IN
(list)
空值会有影响
(等于list其中任何一个就行,
为提高效率常把比例高的放在前面)
LIKE
模糊配置
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后面定义是什么字符为转义字符,那就用哪个字符
table_namefromuser_tableswhere
table_namelike
'
S\_%'
escape'
\'
1.3.5.单行函数
单行函数:
(dual
哑表)
字符函数:
lower
转小写
select
lower('
SQLPLUS'
)
fromdual;
-->
对纯字
符串处理的时候
upper
转大写
upper('
sqlplus'
initcap
首字符大写
initcap('
tarena'
)fromdual;
concat
连接字符串
concat(first_name,last_name)
from
s_emp;
等效于||
substr
求子串
substr('
tarenasd0603'
1,6)fromdual;
(取前
六个字符)
selectsubstr('
-2)fromdual;
(取后两个字符)
length
求字符长度
length('
nvl
空值函数
两个参数的类型要匹配,统一的,表示:
如果有,则
返回前面的参数,如果没有就返回后面的参数
eg:
selectfirst_name,salaryfroms_empwherelower(first_name)='
george'
first_name,substr(first_name,-2)from
s_emp;
(查出s_emp表中所有
用户名字的最后两个字符)
默认的是从左向右,如果是-2则表示从右向左数
练习5:
first_name
salary
froms_emp
where
第5页
lower(first_name)='
数值函数:
round
函数(四舍五入)
round(45.935,2)fromdual;
不带参数时默认
为0位小数
trunc函数(截取,不管后面的数字)
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
当前世纪年份
selectto_char(sysdate,'
yyyymmddhh24:
mi:
ss'
fmyyyymmddhh24:
查出三月分入职的员工:
selectfirst_name,start_datefroms_empwhereto_char(start_date,'
mm'
)='
03'
to_date表达日期:
字符转日期
to_date('
20001120'
'
yyyymmdd'
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只能作用于数字类型)
求有提成员工的提成的平均值;
avg(nvl(commission_pct,0)
)froms_emp;
有多少人有提成:
count(commission_pct)from
s_emp;
count(*)
用于统计记录数:
sum(commission_pct)/count(*)
员工分部在多少个不同的部门:
count
默认为作all的动作
count(dept_id)
froms_emp;
count(distinctdept_id)from
求各个部门的平均工资:
group
by
子句也会触发排序
dept_id,
avg(salary)aa
s_emp
groupby
dept_id
orderby
aa;
//对平均工资排序
dept_id;
注意:
groupby子句后面跟有条件只能是组函数查询的结果中的字段,所以我
们会人为在结果要加入一些groupby
要用的字段,如:
dept_id可能不想要。
region_id,count(*)
s_dept此句会有错,count(*)是单组分组函
数,如果加上groupbyregion_id就是找出同地区的部门数量。
max(region_id)
count(*)from
s_dept;
(强制语法上可以正确,但是
不能保证结果也会正确)
求各个部门不同工种的平均工资:
dept_id,title,
avg(salary)
group
bydept_id,
title
;
哪些部门的平均工资比2000高:
dept_id,
groupby(dept_id)
having
avg(salary)>
2000;
除了42部门以外的部门的平均工资:
groupby(dept_id)having
dept_id!
=42;
第8页
dept_id!
=42
groupby(dept_id);
(此种sql效率要高,先过滤)
再计算)
where
单行函数。
having
组函数。
求各个部门的平均工资:
//这样统计不详细
max(d.name),
avg(s.salary)
s,
s_dept
dwhere
s.dept_id=d.id
d.name;
//****这问题很经典,为了过oraclesql语法关而写max(d.name)
***
max(d.name)
avg(e.salary)
max(r.name)
froms_empe,
d,s_region
r
e.dept_id=d.id
and
d.region_id=r.id
d.id;
1.3.7.多表连接
多表连接操作:
两表没有任何关联时会产生迪卡尔机:
first_name,name
s_emp,s_dept;
1)
等值连接:
练习一:
查看员工的姓名和员工部门号:
(要考虑到表中实际数据中空值的影响)
first_name,
namefrom
s_empe,s_dept
dwheree.dept_id=d.id;
同时起了别名
dwheree.dept_id=d.idand
e.first_name='
George'
具体到哪个人所在的部门
表的两边有空值的话,不会显示出来。
练习二:
每个员工所在的部门和部门所在的地区
first_name,s_dept.name,s_region.namefroms_emp,s_dept,s_region
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;
等值连接:
练习三:
找出每个员工和每个员工的工资级别
a.ename,a.sal,b.gradefromempa,salgradeb
wherea.salbetween
b.losalandb.hisal;
a.ename,a.sal,b.gradefrom
empa,salgradeb
wherea.sal>
=b.losal
a.sal<
=b.hisal;
2)
自连接:
(又名:
内连接)
当一个表的插入行之间有了关系时就发生了
manager_id
l查出所有员工的部门领导的名称:
(这种sq会少一条记录,总经理没有被配置
上)
e.first_name,m.first_name
froms_empe,s_empm
e.manager_id=m.id;
//关键是同一张表用不同的别名
3)
外连接:
+)的一方会模拟一条记录配置另一方)这就称为外连接,不(防止空值忽略,用(
加(+)一个记录都不能少;
where
e.manager_id=m.id(+);
+号放在哪边就表示在哪边补空,来跟对方来匹配,使得数据一个都不会漏掉,
这个例子中的领导有可能会没有(最高领导就再没有领导了,所以就
+号放在可能出现空值的一边)
标准写法:
显示没有员工的部门
distinctd.name
from
s_empe,s_deptd
第10页
where
e.dept_id(+)=d.id
e.dept_idisnull
显示有员工的部
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 笔记