Oracle-数据库所有查询命令.pdf

《Oracle-数据库所有查询命令.pdf》由会员分享，可在线阅读，更多相关《Oracle-数据库所有查询命令.pdf（33页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、目录1.单行查询.22.基本查询.33.限定返回的行.44.逻辑运算.55.排序.66.函数.77.分组查询.98.多表查询.109.多表连接.1310.子查询.1411.创建和管理表.1912.约束.2213.视图、序列、索引.2414.其他数据库对象.2615.plsql 基础.281.单行查询-查询所有员工的姓select e.last_name as 姓 from employees e;-消除重复的姓select distinct e.last_name as 姓氏 from employees e;-计算员工的月收入(工资+佣金)select salary/salary*(nvl(

2、commission_pct/0)+l)as 工资佣金 from employees;-计算员工的年收入select salary*(nvl(commission_pct,0)+l)*12 as 年收入 from employees;-查询员工的姓名select e.first_name11|e.last_name name from employees e;-查询位置为1700的部门名称(不重复)select distinct d.department_name as 部门名称 from departments dwhere d.locationJd=1700;-查询工资高于10000的员工

3、select*from employees where salary10000;-查询工资低于3000的员工select*from employees where salary10000;-查询工资低于3000的员工select t.*from employees twhere t.salary100;-找出部门1 0 中所有的经理（manager）和部门2 0 中所有办事员（clerk）和既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料（需用子查询，暂不做）-显示员工姓氏中有a和e的所有员工的姓氏select*from employees twhere t.last_na

4、me like%a%ort.last_name like%e%-显示职务为销售代表（saep）或仓库管理员（st_derk）并且薪金不等于2500,3500,7000的所有员工的姓氏、职务和薪金select t.last_name,t.jobjd,t.salary from employees twhere（t.jobjd=sa_rep or t.jobjd=st_clerk,）and t.salary not in（2500,3500,7000）;-显示薪金不在5000-1200这个范围之间的所有员工的姓氏和薪金select t.last_name,t.salary from employe

5、es twhere t.salary1200;5.排序-查询员工资料，按薪资升序排列select t.*from employees torder by t.salary;-查询员工资料，在部门号从大到小的情况下按按薪资升序排列select t.*from employees torder by t.departmentjd desc.salary;-按姓名的字母顺序显示部门20和部门50中的所有员工的姓氏和部门编号select t.last_name,t.departmentjd from employees twhere t.departmentjd in(20,50)order by t

6、.last_name;-显示可以赚取佣金的所有员工的姓氏、薪金和佣金，按薪金和佣金的降序对数据进行排序select t.last_name,t.salary,mission_pct from employees twhere mission_pct is not nullorder by t.salary mission_pct desc;6.函数-显示当前日期select sysdate from dual;-显示当前日期，格式为*年*月*日，别名为hdayselect to_char(sysdate；yyyymm dd 0)hday from dual;编写一个查询，显示姓名以j、a 或

7、m 开始的所有员工的姓氏(第一个字母大写，其余字母小写)和姓氏的长度，给每列一个合适的标签select initcap(t.last_name)lname,length(t.last_name)len from employees twhere substr(upper(last_name),O,l)in(j7a,m);-计算每位员工截止到当前时间入职的星期数，别名为weeks_worked按聘用的星期数对结果进行排序。该星期数舍入到最接近的整数。同时显示员工的名字；elect t.first_namezround(sysdate-t.hire_date)/7)as weeks_worked

8、from employees t;-计算每位员工截止到当前时间入职的月数，别名为months_worked。该星期数舍入到最接近的整数。同时显示员工的名字select t.first_name,round(months_between(sysdate,t.hire_date)as months_workedfrom employees t;-查询在1998-2-20和 1998-5-1之间入职的员工的姓氏、职务标识和起始日期select t.last_name,t.job_id/t.hire_date from employees twhere t.hire_date between to_d

9、ate(199802207yyyymmdd)and to_date(199805017yyyymmdd);创建一个查询。显示所有员工的姓氏和薪金。规定薪金为15个字符长，左边填充$select lpad(t.salary,15,$,)sal from employees t;-显示每位员工的姓氏、聘用日期和薪金复核日期，薪金复核日期是服务六个月之后的第一个星期一。将该列标记为review,此日期格式类似于：monday,the thirty-first of july,2000select t.last_name,t.hire_date,next_day(add_months(t.h汗 e_d

10、ate,6),2)from employees tselect t.last_name/t.hire_date,to_char(next_day(add_months(t.hire_datez6),2)/fmday/the ddspth of monthyyyy,nls_date_language=english)reviewfrom employees t;注：to_char的第三个参数用于设置查询使用的国家和地区，ddspth中 spth为后缀，表示spelled,ordinal number-显示员工的姓氏、聘用日期和该员工在星期几开始工作的select t.last_namezt.hi

11、re_date,to_char(t.hire_date/day)as 开始 from employees t;-计算员工的月收入(工资+佣金)select t.salary+t.salary*nvl(mission_pct/0)from employees t;-创建一个查询，使其显示员工的姓氏，并用星号指明他们的年薪。每个星号代表一千美元，按薪资降序排列数据。select t.last_name|lpad(,/trunc(t.salary/1000)+l,*)/t.salary from employees torder by t.salary desc;-创建一个查询。使其显示员工的姓氏和

12、佣金额。如果某位员工不赚取佣金则显示nocommission”,将该列标记为commselect t.last_name,nvl(to_char(t.salary*mission_pct/999/999.99)/no commission)commfrom employees t-使 用 decode函数编写一个查询，使其按照以下数据根据jo b jd 列的值显示所有员工的级别，同时显示员工的姓氏job grade ad_pres a st_man b it_prog c sa_rep d st_clerk e 其它 0select t.last_name,t.jobjd job,decode

13、.jobjd/ad.pres/a/sCman/b；it_prog；c/sa.rep/d/st.clerk/e；0)grade from employees t;select t.last_name,t.job_id job,case t.jobjdwhen ad_pres then awhen st_man then b*when it_prog then cwhen sa_rep thendwhen st_clerk then*eelse Oend as grade from employees t;-显示当前日期，本月最后一天的日期，以及本月还剩多少天select sysdate,last

14、_day(sysdate)last,last_day(sysdate)-sysdate days left from dual;-显示今年的第一天select trunc(sysdatejyear)from dual;-显示本月的第一天select trunc(sysdate/month)from dual;-最近一个星期四是哪天(不含今日)select next_day(sysdatez5)from dual;7.分组查询-求所有员工的平均工资、最高工资、最低工资和工资总和，给予适当的别名select avg(t.salary),max(t.salary),min(t.salary),sum

15、(t.salary)from employees t;-求每种工作的平均工资select avg(t.salary)from employees tgroup by t.jobjd;.求每个部门中同一种工作的平均工资，同时显示部门号，按部门号升序显示select t.department_id,avg(t.salary)from employees tgroup by t.departmentJd,t.job_idorder by t.departmentjd;-查询出各部门的部门编号以及各部门的总工资和平均工资，按部门编号升序排列。select t.departmentJdumft.sala

16、ryJvgft.salary)from employees tgroup by t.departmentjdorder by t.departmentjd;-显示每种工作的人数select t.job_id,count(*)from employees tgroup by t.jobjd;-显示员工最高工资超过10000的部门的id及其员工最高工资select t.department_id,max(t.salary)m from employees tgroup by t.departmentjdhaving max(t.salary)10000;-显示平均工资最高的部门id 及其平均工资s

17、elect*from(select t.departmentjd,avg(t.salary)from employees tgroup by t.departmentjdorder by avg(t.salary)desc)where rownum nonequijoins outerjoins self-joins crossjoins natural joins outerjoinsfull(or two-sided)-l.write a query for the hr department to produce the addresses of all the departments,

18、use thelocations and countries tables,show the location id,street address,city,state or province,andcountry in the output,use a natural join to produce the results.select lct.location_idjct.street_address,lct.state_province,cty.country_name from locations letnatural join countries cty;-2,the hr depa

19、rtment needs a report of all employees,write a query to display the last name,department number,and department name for all the employees.select emp.last_name,departmentJd,dpt.department_name from employees emp natural joindepartments dpt;-3.the hr department needs a report of employees in toronto.d

20、isplay the last name,job,department number,and the department name for all employees who work in toronto.select emp.last_name,emp.jobjd,dpt.department_id,dpt.department_name from employeesemp join departments dpt on emp.department_id=dpt.departmentJdjoin locations let on dpt.locationjd=lct.location_

21、idwhere lct.city=toronto;-4.create a report to display employees last name and employee number along with theirmanagers last name and manager number.label the columns employee,emp#,manager;and mgr#,respectively,save your sql statement aslab_06_04.sql.run the query.-自联结select emp.last_name employee,e

22、mp.employee_id emp#,mgr.last_name manager,mgr.employeejd mgr#from employees empjoin employees mgr on emp.manager_id=mgr.employee_id;-5.modify lab_06_04.sql to display all employees including king,who has no manager,order theresults by the employee number,save your sql statement as lab_06_05.sql.run

23、the query inlab_06_05.sql.select emp.last_name employee,emp.employeeJd emp#,mgr.last_name managecmgr.employeejd mgr#from employees empleft outer join employees mgr on emp.managerJd=mgr.employee_idorder by emp#;-6.create a report for the hr department that displays employee last names,departmentnumbe

24、rs,and all the employees who work in the same department as a given employee.giveeach column an appropriate label,save the script to a file named lab_06_06.sql.select emp.last_name employee,emp.departmentJd,colleague.Iast_name colleaguefrom employees empjoin employees colleague on emp.department_id=

25、colleague.departmentjdwhere emp.employeeJdocolleague.employeeJdorder by employee;-7.the hr department needs a report on job grades and salaries.to familiarize yourself with thejob_grades table,first show the structure of the job_grades table,then create a query thatdisplays the name job,department n

26、ame,salary,and grade for all employees.-建表create table job_gradeslowest_sal number(6)zhighest_sal number，gradejevel char(l)-插入数据insert into job_grades(lowest_sal,highest_sal,gradejevel)values(30000,40000,f);insert into job_grades(lowest_sal,highest_sal,gradejevel)values(20000,30000,e)；insert into jo

27、b_grades(lowest_sal,highest_sal,gradejevel)values(15000,20000,d);insert into job_grades(lowest_sal,highest_sal,gradejevel)values(8500,15000,c);insert into job_grades(lowest_sal,highest_sal,gradejevel)values(5500,8500,b);insert into job_grades(lowest_sal,highest_sal,gradejevel)values(2000,5000,a);com

28、mit;select emp.last_name employee,emp.salary,g.gradejevel from employees empjoin job_grades g on emp.salary between g.lowest_sal and g.highest_sal;-8.the hr department wants to determine the names of all the employees who were hired afterdavies.create a query to display the name and hire date of any

29、 employee hired after employeedavies.select emp.last_name employee,emp.hire_date from employees empjoin employees cig on emp.hire_date clg.hire_datewhere clg.last_name=daviesorder by emp.hire_date;-9,the hr department needs to find the names and hire dates of all the employees who werehired before t

30、heir managers,along with their managers names and hire dates,save the script toa file named lab_06_09.sql.select emp.last_name employee,emp.hire_date/mgr.last_name manager;mgr.hire_date mgr_hire_day from employees empjoin employees mgron emp.manager_id=mgr.employee_id and emp.hire_date ,=,in，等2.1 子查

31、询先于主查询执行;2.2 子查询的结果用于外查询-3.子查询可以用于什么位置？where/having/from 子句一 4.举例:查询工资比abel高的员工的姓氏和工资select last_name,salary from employeeswhere salary (select salary from employees where last_name=abel);5.使用指南：5.1 子查询用括号包含5.2 将子查询放在比较运算符右边以增加可读性5.3 单行子查询使用单行运算符，多行子查询使用多行运算符(in,any,all)6.区分单行与多行子查询6.1 单行子查询:返回单行数据，

32、适用的比较运算符为=,=,=6.2 多行子查询:返回多行数据，适用的比较运算符为in,any,allselect employeejd,last-name,jobjd,salary from employeeswhere salary any(select salary from employees where jobjd=it_prog)and jobjd it_prog;7.子查询中的空值：in(.)的含义等价于:=any(.),所以子查询中是否有空值，对结果没有影响但是,noting.)的 含 义 等 价 于 如 果 子 查 询 中 出 现 空 值，整个表达式为空自 然:任 意 比 较

33、如 果 子 查 询 中 出 现 空 值，整个表达式为空select*from employees e where e.departmentjd=(select d.departmentjd from departments dwhere d.department_name inCmarketing/it);-查询部门名称为marketing和 it 的员工信息select*from employees e where e.departmentjd in(select d.department_id from departments dwhere d.department_name in(,ma

34、rketing/it);-查询不是经理的员工的信息select*from employees e where e.employeejd not in(select distinct el.managerjd from employees elwhere el.managerjd is not null);-查询出所有经理的信息select e.last_name,e.department_id from employees ewhere e.employeejd in(select distinct el.managerjd from employees elwhere el.managerj

35、d is not null);-查询工资比10号部门中其中一个员工低的员工信息select*from employees e where e.salaryany(select el.salary from employees elwhere el.department_id=10);-查询工资比10号部门都要低的员工信息select*from employees e where e.salary(select min(el.salary)from employees elwhere el.departmentJd=10);-如果要显示这个最低工资select e.last_name,e.sal

36、ary,sl.ms from employees e,(select min(el.salary)ms from employees elwhere el.department_id=10)siwhere e.salarysl.ms;-列出与sewall(指的是last_name)从事相同工作的所有员工及部门名称select e.last_name,d.department_name from employees e,departments dwhere e.departmentJd=d.departmentJdand e.job_id=(select jobJd from employees

37、 where last_name=,sewall,)and e.last_nameoswair；-显示和austin同部门，工资低于baer的雇员有哪些select e.last_name from employees ewhere e.departmentjd=(select departmentjd from employees where last_name=,austin)and e.salary(select avg(salary)from employees);-查询出部门名称，部门员工数，部门平均工资，部门最低工资雇员的姓名，其工资，及工资等级一方法1select d.depar

38、tment_name 部门,s.empnum 部门员工数，s.avgsal 部门平均工资,e.salary 最低工资工资,e.last_name部门最低工资雇员,g.grade evel工资等级from employees e,departments d,job_grades g(select el.departmentjd dptid,count(el.employee_id)empnum,avg(el.salary)avgsal,min(el.salary)minsal from employees elgroup by el.departmentjd)szwhere e.departme

39、ntJd=d.department_id and d.department_id=s.dptidand e.salary=s.minsal and e.salary between g.lowest_sal and g.highest_sal;一方法2:select d.department_name 部 门，d.departmentjd 部 门号，sl.empcount 部门员 工数,si.avgsal部 门 平 均 工 资，si.m insal部 门 最 低 工 资，e.last_name部 门 最 低 工 资 雇 员，g.gradejevel 工资等级 from employees ej

40、oin departments d on e.department_id=d.department_idjoin job_grades g on e.salary between g.lowest_sal and g.highest_saljoin(select el.departmentjd dptid,count(*)empcount,avg(el.salary)avgsal,min(el.salary)minsal from employees el group by departmentjd)sion e.department_id=sl.dptid and e.salary=sl.m

41、insal;-l.the hr department needs a query that prompts the user for an employee last name,the querythen displays the last name and hire date of any employee in the same department as theemployee whose name they supply(excluding that employee),for example,if the user enterszlotkey,find all employees w

42、ho work with zlotkey(excluding zlotkey).select e.last_name,e.hire_dateze.departmentjd from employees ewhere e.department_id=(select el.departmentjd from employees elwhere el.last_name=&last_name)and e.last_nameo&last_name;-2.create a report that displays the employee number;last name,and salary of a

43、ll employeeswho earn more than the average salary.sort the results in order of ascending salary.select e.employeejd,e.last_name,e.salary from employees ewhere e.salary(select avg(salary)from employees)order by e.salary;-3.write a query that displays the employee number and last name of all employees

44、 who work ina department with any employee whose last name contains the letter u.save your sqlstatement as lab_07_03.sql.run your query.select e.employeeJd,e.last_name from employees ewhere e.departmentjd in(select departmentjd from employees where last_name like%u%);-4,the hr department needs a rep

45、ort that displays the last name,department number,and job idof all employees whose department location id is 1700.select e.last_name,e.department_idze.job_id from employees ewhere departmentjd in(select departmentjd from departments where location_id=1700);-5.create a report for hr that displays the

46、 last name and salary of every employee who reports toking.select e.last_name,e.salary from employees ewhere e.managerjd in(select employeejd from employees where last_name=king);-6.create a report for hr that displays the department number;last name,and job id for everyemployee in the executive dep

47、artment.select e.department_id,e.last_name,e.jobjd from employees ewhere e.departmentjd in(select departmentjd from departments dwhere d.department_name=*executive)-7.modify the query in lab_07_03.sql to display the employee number,last name,and salary ofall employees who earn more than the average

48、salary,and who work in a department with anyemployee whose last name contains a u.resave lab_07_03.sql as lab_07_07.sql.run thestatement in lab_07_07.sql.select e.employeejd,e.last_name,e.salary from employees ewhere e.departmentjd in(select departmentjd from employees where last_name like,%u%)and e

49、.salary(select avg(salary)from employees);11.创建和管理表创建和管理表，预习自检：1.有哪些数据库对象？表：用于存储数据视图：一个或者多个表中的数据的子集序列：数字值生成器索引：提高某些查询的性能同义词：给出对象的替代名称2.建表是要指定哪些内容？3.如何建表时为列指定默认值？4.如何使用子查询语法创建表？5.如何为己有表新增列，删除列,修改列，为新增列定义默认值?6.如何标记列为unused7.如何批量删除unused列8.如何删除表9.如何更改表名？10.如何舍去表中的内容？11.如何为表,列添加注释？12.orade有哪些常用的数据类型？-使

50、用 sql语句完成以下练习：.显示当前用户拥有的表select table_name from user_tables;显示当前用户拥有的表，视图，同义词和序列select*from user_catalog;一或者select*from cat;-3 创建dept表，结构如下：列 名 id name数据类型 number varchar2长 度 7 25create table dept2(id number(7),name varchar(25)；-4.使用departments表中的数据填充dept表,只包含所需列insert into dept(id,name)select depar