Oracle 数据库所有查询命令Word格式.docx

资源描述

Oracle 数据库所有查询命令Word格式.docx

《Oracle 数据库所有查询命令Word格式.docx》由会员分享，可在线阅读，更多相关《Oracle 数据库所有查询命令Word格式.docx（44页珍藏版）》请在冰豆网上搜索。

Oracle 数据库所有查询命令Word格式.docx

--查询没有佣金的员工

wherecommission_pctisnull

--查询姓以B开头的员工

wheree.last_namelike'

B%'

--查询部门号为10或者20或者30的员工

wheree.department_idin（10,20,30）

2.基本查询

selectlast_name

fromemployees;

selectdistinctlast_name

--创建一个查询，以显示employees表中的唯一职务代码

selectdistinctt.job_id

fromemployeest;

--创建一个查询，使其显示每位员工的姓氏、职务代码、聘用日期和员工编号，并且首先显示员工编号。

为hire_date列提供一个别名：

startdate

selectt.employee_id,t.last_name,t.job_id,t.hire_dateasstartdate

selectt.salary+t.salary*nvl（mission_pct,0）

select12*（t.salary+t.salary*nvl（mission_pct,0））

selectt.first_name||'

||t.last_name

--显示与职务标识连接的姓氏，它们之间由逗号和空格分隔，这列数据命名为EmployeeandTitle

selectt.job_id||'

||t.last_nameas"

EmployeeandTitle"

--创建一个查询，使其显示employees表的所有数据，用逗号分隔各列，命名列为THE_OUTPUT

selectt.employee_id||'

||t.first_name||'

||t.last_name||'

t.email||'

||t.phone_number||'

||t.hire_date||'

||t.job_id||'

t.salary||'

||mission_pct||'

||t.manager_id||'

||t.department_id

as"

THE_OUTPUT"

============================================================================

3.限定返回的行

selectdistinctt.department_name

fromdepartmentst

wheret.location_id=1700;

--创建一个查询，显示员工编号为176的员工的姓氏和部门编号

selectt.last_name,t.department_id

fromemployeest

wheret.employee_id=176;

--查询工资高于10000的员工的姓氏和薪资

selectt.last_name,t.salary

wheret.salary>

10000;

selectt.*

wheret.salary<

3000;

--查询在1998年2月20日和1998年5月1日之间入职的员工的姓氏、职务标识和起始日期

selectt.last_name,t.job_id,t.hire_date

wheret.hire_datebetweento_date（'

19980220'

yyyyMMdd'

）andto_date（'

19980501'

）;

--显示在1994年聘用的每位员工的姓氏和聘用日期

selectt.last_name,t.hire_date

19940101'

19950101'

--*查询没有佣金的员工

wheremission_pctisnull;

wheret.last_namelike'

;

wheret.department_idin（10,20,30）;

--查询没有经理的所有员工的姓氏和职称

selectt.last_name,t.job_id

wheret.manager_idisnull;

--显示员工名字中的第三个字母为“a”的所有员工的姓氏

selectt.last_name

__a%'

4.逻辑运算

--找出部门10中所有的经理（MANAGER）和部门20中所有办事员（**_CLERK）（需用子查询，暂不做）

--找出有佣金的员工的都做什么工作（无重复）

wheremission_pctisnotnull;

--找出不收取佣金或收取的佣金高于100的员工

wheremission_pctisnullort.salary*mission_pct>

100;

--找出部门10中所有的经理（MANAGER）和部门20中所有办事员（CLERK）和既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料（需用子查询，暂不做）

--显示员工姓氏中有“a”和“e”的所有员工的姓氏

%a%'

ort.last_namelike'

%e%'

--显示职务为销售代表（SA_REP）或仓库管理员（ST_CLERK）并且薪金不等于2500,3500,7000的所有员工的姓氏、职务和薪金

selectt.last_name,t.job_id,t.salary

where（t.job_id='

SA_REP'

ort.job_id='

ST_CLERK'

）andt.salarynotin（2500,3500,7000）;

--显示薪金不在5000-1200这个范围之间的所有员工的姓氏和薪金

5000ort.salary>

1200;

5.排序

--查询员工资料，按薪资升序排列

orderbyt.salary;

--查询员工资料，在部门号从大到小的情况下按按薪资升序排列

orderbyt.department_iddesc,t.salary;

--按姓名的字母顺序显示部门20和部门50中的所有员工的姓氏和部门编号

selectt.last_name,t.department_id

wheret.department_idin（20,50）

orderbyt.last_name;

--显示可以赚取佣金的所有员工的姓氏、薪金和佣金，按薪金和佣金的降序对数据进行排序

selectt.last_name,t.salary,mission_pct

wheremission_pctisnotnull

orderbyt.salarydesc,mission_pctdesc;

6.函数（已看到此处）

--1、显示当前日期

selectsysdatefromdual;

--2、显示当前日期，格式为****年**月**日，别名为hday

selectto_char（sysdate,'

yyyy"

年"

mm"

月"

dd"

日"

）hdayfromdual;

--3、编写一个查询，显示姓名以J、A或M开始的所有员工的姓氏（第一个字母大写，其余字母小写）和姓氏的长度，给每列一个合适的标签；

selectinitcap（t.last_name）lname,length（t.last_name）len

wheresubstr（upper（last_name）,0,1）in（'

--4、计算每位员工截止到当前时间入职的星期数，别名为weeks_worked。

按聘用的星期数对结果进行排序。

该星期数舍入到最接近的整数。

同时显示员工的名字；

selectt.first_name,round（（sysdate-t.hire_date）/7）asweeks_worked

--5、计算每位员工截止到当前时间入职的月数，别名为months_worked。

同时显示员工的名字。

selectt.first_name,round（months_between（sysdate,t.hire_date））asmonths_worked

--6、查询在1998年2月20日和1998年5月1日之间入职的员工的姓氏、职务标识和起始日期

--7、创建一个查询。

显示所有员工的姓氏和薪金。

将薪金格式规定为15个字符长，左边填充$

selectlpad（t.salary,15,'

）sal

/*8、显示每位员工的姓氏、聘用日期和薪金复核日期，薪金复核日期是服务六个月之后的第一个星期一。

将该列标记为review。

这一日期的显示格式类似于：

“Monday,theThirty-FirstofJuly,2000”*/

selectt.last_name,t.hire_date,/*next_day（add_months（t.hire_date,6）,2）fromemployeest*/

to_char（next_day（add_months（t.hire_date,6）,2）,'

fmday,"

the"

ddspth"

of"

month,yyyy'

nls_date_language=english'

）review

/*第八题to_char的第三个参数用于设置本次查询使用的国家和地区，ddspth中spth为后缀，表示Spelled,ordinalnumber*/

--9、显示员工的姓氏、聘用日期和该员工在星期几开始工作的。

selectt.last_name,t.hire_date,to_char（t.hire_date,'

DAY'

）as"

开始"

--10、计算员工的月收入（工资+佣金）

--11、创建一个查询，使其显示员工的姓氏，并用星号指明他们的年薪。

每个星号代表一千美元。

按薪资降序排列数据。

selectt.last_name||lpad（'

trunc（t.salary/1000）+1,'

）,t.salary

orderbyt.salarydesc;

--12、创建一个查询。

使其显示员工的姓氏和佣金额。

如果某位员工不赚取佣金则显示“NoCommission”，将该列标记为COMM

selectt.last_name,nvl（to_char（t.salary*mission_pct,'

999,999.99'

）,'

NoCommission'

）COMM

/*13、使用decode函数编写一个查询，使其按照以下数据根据JOB_ID列的值显示所有员工的级别，同时显示员工的姓氏

jobgrade

AD_PRESA

ST_MANB

IT_PROGC

SA_REPD

ST_CLERKE

都不是0

selectt.last_name,t.job_idjob,

decode（t.job_id,'

AD_PRES'

ST_MAN'

IT_PROG'

）asgrade

--14、使用case语法重写一遍上题

caset.job_id

when'

then'

else'

end

asgrade

************************************补充************************************

--1、显示当前日期，本月最后一天的日期，以及本月还剩多少天

SELECTSYSDATE,

LAST_DAY（SYSDATE）"

Last"

LAST_DAY（SYSDATE）-SYSDATE"

DaysLeft"

FROMDUAL;

--2、显示今年的第一天

selecttrunc（sysdate,'

year'

）fromdual;

--3、显示本月的第一天

month'

--4、最近一个星期四是哪天（不含今日）

SELECTNEXT_DAY（sysdate,5）

FROMDUAL;

7.分组查询

--求所有员工的平均工资、最高工资、最低工资和工资总和，给予适当的别名

selectavg（t.salary）,max（t.salary）,min（t.salary）,sum（t.salary）

--求每种工作的平均工资

selectavg（t.salary）

groupbyt.job_id;

--求每个部门中同一种工作的平均工资,同时显示部门号,按部门号升序显示

selectt.department_id,avg（t.salary）

groupbyt.department_id,t.job_id

orderbyt.department_id;

--查询出各部门的部门编号以及各部门的总工资和平均工资,按部门编号升序排列。

selectt.department_id,sum（t.salary）,avg（t.salary）

fromemployeest

groupbyt.department_id

--显示每种工作的人数

selectt.job_id,count（*）

--显示员工最高工资超过10000的部门的id及其员工最高工资

selectt.department_id,max（t.salary）m

havingmax（t.salary）>

--显示平均工资最高的部门id及其平均工资（要用到还未学习的知识）

select*from（

orderbyavg（t.salary）desc

）

whererownum<

8.多表查询

多表查询，基本知识（预习自检）：

什么是多表查询?

什么是笛卡尔积？

怎样避免笛卡尔积？

要将n个表联结在一起，至少需要多少个联结条件？

什么是等值联结？

如何在联结条件外附加限制条件？

如何限定模糊的列名？

如何进行非等值联结？

什么是外联结？

什么是自联结？

如何用sql1999标准表达：

笛卡尔积、自然联结、等值联结、内联结、左外联结、右外联结和全外联结？

**/

联结的分类：

JoinsthatarecompliantwiththeSQL:

1999standardincludethefollowing:

Naturaljoins:

NATURALJOINclause

USINGclause

ONclause

Outerjoins:

LEFTOUTERJOIN

RIGHTOUTERJOIN

FULLOUTERJOIN

Crossjoins

sql：

1999联结语法

SELECTtable1.column,table2.column

FROMtable1

[NATURALJOINtable2]|

[JOINtable2USING（column_name）]|

[JOINtable2

ON（table1.column_name=table2.column_name）]|

[LEFT|RIGHT|FULLOUTERJOINtable2

[CROSSJOINtable2];

知识总结：

Inthislesson,youshouldhavelearnedhowtousejoinstodisplaydatafrommultipletablesbyusing:

Equijoins

Nonequijoins

Outerjoins

Self-joins

Naturaljoins

Full（ortwo-sided）outerjoins

/*1.WriteaqueryfortheHRdepartmenttoproducetheaddressesofallthedepartments.

UsetheLOCATIONSandCOUNTRIEStables.ShowthelocationID,streetaddress,city,stateorprovince,andcountryintheoutput.

UseaNATURALJOINtoproducetheresults.*/

selectlct.location_id,lct.street_address,lct.state_province,cty.country_name

fromlocationslctnaturaljoincountriescty;

/*2.TheHRdepartmentneedsareportofallemployees.

Writeaquerytodisplaythelastname,departmentnumber,anddepartmentnameforalltheemployees.*/

selectemp.last_name,department_id,dpt

展开阅读全文