Oracle练习04.docx
- 文档编号:17658692
- 上传时间:2023-07-27
- 格式:DOCX
- 页数:47
- 大小:615.25KB
Oracle练习04.docx
《Oracle练习04.docx》由会员分享,可在线阅读,更多相关《Oracle练习04.docx(47页珍藏版)》请在冰点文库上搜索。
Oracle练习04
Oracle练习
链接
createtableemployees
as
select*
fromsystem.employees
selectemployee_id,e.first_name,e.last_name,d.department_id,d.department_name,l.city
fromemployeese,departmentsd,locationsl
wheree.department_id=d.department_idandd.location_id=l.location_id
selectemployee_id,last_name,salary,grade_level
fromemployeese,job_gradesj
wheree.salarybetweenj.lowest_salandj.highest_sal
selecte.last_name,e.department_id,d.department_name
fromemployeese,departmentsd
wheree.department_id=d.department_id
selecte.last_name,e.department_id,d.department_name
fromemployeese,departmentsd
wheree.department_id=d.department_id(+)
有部门,部门内无人员
selectemployee_id,department_id,department_name
fromemployeesjoindepartments
using(department_id)
selectemployee_id,d.department_id,department_name
fromemployeesejoindepartmentsd
one.department_id=d.department_id
selectemployee_id,d.department_id,department_name,city
fromemployeesejoindepartmentsd
one.department_id=d.department_id
joinlocationsl
ond.location_id=l.location_id
满外链接
selectemployee_id,d.department_id,department_name
fromemployeesefullouterjoindepartmentsd
one.department_id=d.department_id
selectlast_name,salary,email
fromemployees
whereemployee_id=5
selecte.last_name,m.last_name,m.salary,m.email
fromemployeese,employeesm
wheree.manager_id=m.employee_id
andlower(e.last_name)='pan'
多表连接
selectlast_name,department_name,city
fromdepartmentsd,employeese,locationsl
whered.department_id=e.department_idand
d.location_id=l.location_id
selectlast_name,e.department_id,department_name
fromemployeese,departmentsd
wheree.department_id=d.department_id(+)
selectlast_name,e.department_id,department_name
fromemployeeseleftouterjoindepartmentsd
one.department_id=d.department_id
--查询90号部分员工的job_id,90号部门的location_id
selectdistinctjob_id,location_id
fromemployeesejoindepartmentsd
one.department_id=d.department_id
whered.department_id=10
selectlast_name,department_name,d.location_id,city
fromemployeesejoindepartmentsd
one.department_id=d.department_id
joinlocationsl
ond.location_id=l.location_id
wheremission_pctisnotnull
selectlast_name,job_id,e.department_id,department_name
fromemployeese,departmentsd,locationsl
wheree.department_id=d.department_idand
l.city='NJ'andd.location_id=l.location_id
单引号
selecte1.last_name"Employees",e1.employee_id"EMP#",e2.last_name"Manager",e2.employee_id"MGR#"
fromemployeese1,employeese2
wheree1.manager_id=e2.manager_id
selectavg(salary),max(salary),min(salary),sum(salary)
fromemployees
selectcount(employee_id),count(last_name),count(hire_date)
fromemployees
selectavg(salary),sum(salary),sum(salary)/count(salary)
fromemployees
selectavg(commission_pct),sum(commission_pct)/count(commission_pct)
fromemployees
selectcount(distinctdepartment_id)
fromemployees
selectdepartment_id,avg(salary)
fromemployees
groupbydepartment_id
过滤条件
selectdepartment_id,avg(salary)
fromemployees
wheredepartment_idin(10,20,40)
groupbydepartment_id
selectdepartment_id,job_id,avg(salary)
fromemployees
groupbyjob_id,department_id
orderbydepartment_id
selectdepartment_id,avg(salary)
fromemployees
havingavg(salary)>6000
groupbydepartment_id
orderbydepartment_idasc
selectavg(nvl(commission_pct,0))
fromemployees
单行函数
selectlower('RENYANL'),upper('REANYANL'),initcap('renyanl')
fromdual
/
LOWER('RENYANL')UPPER('REANYANL')INITCAP('RENYANL')
---------------------------------------------------
renyanlREANYANLRenyanl
selectlast_name,employee_id,rpad(salary,10,''),lpad(salary,10,'')
fromemployees
LAST_NAMEEMPLOYEE_IDRPAD(SALARY,10,'')LPAD(SALARY,10,'')
----------------------------------------------------------------------------
cheng12000020000
shuangcheng22000020000
zhijuan32000020000
liuxi41500015000
zong51200012000
yanliang678007800
quansheng798009800
yinchao880008000
jiawei954005400
ru1025002500
selecttrim('H'from'Hellowhhhordh')fromdual
selectreplace('abdcebfedmkkb','b','m')fromdual
REPLACE('ABDCEBFEDMKKB','B','M
------------------------------
amdcemfedmkkm
selectround(435.45,2),round(435.45),round(435.45,-2)fromdual
2/
ROUND(435.45,2)ROUND(435.45)ROUND(435.45,-2)
--------------------------------------------
435.45435400
保留2位小数;默认保留到0,保留整数;负2,在小数往前2位
selecttrunc(435.45,2),trunc(435.45),trunc(435.45,-2)fromdual
TRUNC(435.45,2)TRUNC(435.45)TRUNC(435.45,-2)
--------------------------------------------
435.45435400
selectmod(1600,300)fromdual
2/
MOD(1600,300)
-------------
100
selectsysdatefromdual
selectsysdate,sysdate+1,sysdate-1fromdual
SYSDATESYSDATE+1SYSDATE-1
---------------------------------
2016/4/99:
2016/4/1092016/4/89:
查询员工来公司多少天了
selectemployee_id,first_name,last_name,sysdate-hire_dateworked_days(别名)
fromemployees
selectemployee_id,first_name,last_name,trunc(sysdate-hire_date)worked_days
fromemployees
截断trunc
selectemployee_id,first_name,last_name,(sysdate-hire_date)/30,months_between(sysdate,hire_date)
fromemployees
EMPLOYEE_IDFIRST_NAMELAST_NAME(SYSDATE-HIRE_DATE)/30MONTHS_BETWEEN(SYSDATE,HIRE_DA
------------------------------------------------------------------------------------------------------------
21zhengchanghai16.247262731481516
22liangyuqian16.247262731481516
23tangxiangyun22.513929398148122.1747703853047
24zhoushun34.680596064814834.1747703853047
25zhangjing34.680596064814834.1747703853047
selectadd_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期五')
fromdual
ADD_MONTHS(SYSDATE,2)ADD_MONTHS(SYSDATE,-3)NEXT_DAY(SYSDATE,'星期五')
---------------------------------------------------------------------
2016/6/910:
19:
252016/1/910:
19:
252016/4/1510:
19:
25
selectlast_name,hire_date
fromemployees
wherehire_date=last_day(hire_date)-1
selectemployee_id,hire_date
fromemployees
whereto_char(hire_date,'yyyy/mm/dd')='2014/12/09'
--whereto_date('2014-12-09','yyyy-mm-dd')=hire_date
selectemployee_id,hire_date
fromemployees
whereto_char(hire_date,'yyyy"年"mm"月"dd"日"')='2014年12月09日'
--whereto_date('2014-12-09','yyyy-mm-dd')=hire_date
selectto_char(1234567.89,'999,999,999,99')fromdual
2/
TO_CHAR(1234567.89,'999,999,99
------------------------------
12,345,68
selectto_char(1234567.89,'000,000,000,99')fromdual
TO_CHAR(1234567.89,'000,000,00
------------------------------
000,012,345,68
selectto_char(1234567.89,'$000,000,000,99')fromdual
TO_CHAR(1234567.89,'$000,000,0
------------------------------
$000,012,345,68
selectto_char(1234567.89,'L000,000,000,99')fromdual
TO_CHAR(1234567.89,'L000,000,0
------------------------------
¥000,012,345,68
selectto_number('¥001,234,567.68','L000,000,999.99')+100
fromdual
TO_NUMBER('¥001,234,567.68','
------------------------------
1234567.68
查询last_name,department_id,当department_id为null时,显示‘没有部门’
selectlast_name,nvl(to_char(department_id,'999999'),'没有部门')
fromemployees
LAST_NAMENVL(TO_CHAR(DEPARTMENT_ID,'999
-------------------------------------------------------
cheng没有部门
shuangcheng40
zhijuan40
liuxi20
查询员工的奖金率,若为空,返回0.01,若不为空,返回实际奖金率+0.15
selectlast_name,commission_pct,nvl2(commission_pct,commission_pct+0.15,0.01)
fromemployees
LAST_NAMECOMMISSION_PCTNVL2(COMMISSION_PCT,COMMISSION
---------------------------------------------------------------------
cheng0.991.14
shuangcheng0.881.03
zhijuan0.770.92
liuxi0.660.81
zong0.200.35
yanliang0.050.2
quansheng0.010.16
yinchao0.050.2
jiawei0.010.16
ru0.01
hui0.01
pan0.01
renjie0.01
qiuying0.01
查询部门为10,20,30的员工信息,如部门为10,工资为1.1.,20,工资为1.2,30,工资为1.3
selectemployee_id,last_name,department_id,
casedepartment_idwhen10thensalary*1.1
when20thensalary*1.2
elsesalary*1.3
endnew_sal
fromemployees
wheredepartment_idin(10,20,30)
selectemployee_id,last_name,department_id,
decode(department_id,10,salary*1.1,
20,salary*1.2,
salary)new_sal
fromemployees
wheredepartment_idin(10,20,30)
EMPLOYEE_IDLAST_NAMEDEPARTMENT_IDSALARYNEW_SAL
---------------------------------------------------------------------
4liuxi2015000.0018000
5zong1012000.0013200
6yanliang307800.007800
7quansheng109800.0010780
selectto_char(sysdate,'yyyy"年"mm"月"dd"日"hh:
mi:
ss')fromdual
TO_CHAR(SYSDATE,'YYYY"年"MM"月
------------------------------
2016年04月09日04:
33:
52
查询员工号、姓名、工资,以及工资提高百分20后的结果
selectemployee_id,last_name,salary,salary*1.2"newsalary"
fromemployees
EMPLOYEE_IDLAST_NAMESALARYnewsalary
--------------------------------------------------------
1cheng20000.0024000
2shuangcheng20000.0024000
3zhijuan20000.0024000
4liuxi15000.0018000
5zong12000.0014400
6yanliang7800.009360
7quansheng9800.0011760
8yinchao8000.009600
9jiawei5400.006480
10ru2500.003000
11hui2700.003240
12pan3000.003600
13renjie2900.003480
14qiuying2100.002520
15weiquan4500.005400
selectlast_name,length(last_name)
fromemployees
orderbylast_nameasc、desc
selectlast_name,hire_date,months_between(sysdate,hire_date)
fromemployees
selectlast_name,hire_date,round(months_between(sysdate,hire_date),1)
fromemployees
selectlast_name,hire_date,round(months_between(sysdate,hire_date),0)word_month
fromemployees
orderbyword_month
子查询
selectlast_name,job_id,salary
fromemployees
wherejob_id=(
selectjob_id
fromemployees
whereemployee_id=14
)
andsalary>(
selectsalary
fromemployees
whereemployee_id=17
)
LAST_NA
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 练习 04
![提示](https://static.bingdoc.com/images/bang_tan.gif)