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

1、-查询没有佣金的员工where commission_pct is null-查询姓以B开头的员工where e.last_name like B%-查询部门号为10或者20或者30的员工where e.department_id in（10,20,30）2.基本查询select last_name from employees;select distinct last_name -创建一个查询，以显示employees表中的唯一职务代码select distinct t.job_idfrom employees t;-创建一个查询，使其显示每位员工的姓氏、职务代码、聘用日期和员工编号，并且首

2、先显示员工编号。为hire_date列提供一个别名：startdateselect t.employee_id,t.last_name,t.job_id,t.hire_date as startdateselect t.salary+t.salary*nvl（mission_pct,0）select 12*（t.salary+t.salary*nvl（mission_pct,0）select t.first_name|t.last_name-显示与职务标识连接的姓氏，它们之间由逗号和空格分隔，这列数据命名为 Employee and Titleselect t.job_id|, |t.last

3、_name as Employee and Title-创建一个查询，使其显示employees表的所有数据，用逗号分隔各列，命名列为THE_OUTPUTselect t.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_idas THE_OUTPUT=3.限定返回的行select distinct t.department_name from departments twhere t.

4、location_id=1700;-创建一个查询，显示员工编号为176的员工的姓氏和部门编号select t.last_name,t.department_idfrom employees twhere t.employee_id=176;-查询工资高于10000的员工的姓氏和薪资select t.last_name,t.salarywhere t.salary10000;select t.*where t.salary100;-找出部门10中所有的经理（MANAGER）和部门20中所有办事员（CLERK）和既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料（需用子查询，暂不

5、做）-显示员工姓氏中有“a”和“e”的所有员工的姓氏%a% or t.last_name like %e%-显示职务为销售代表（SA_REP）或仓库管理员（ST_CLERK）并且薪金不等于2500,3500,7000的所有员工的姓氏、职务和薪金select t.last_name,t.job_id,t.salarywhere （t.job_id=SA_REP or t.job_id=ST_CLERK） and t.salary not in （2500,3500,7000）;-显示薪金不在5000-1200这个范围之间的所有员工的姓氏和薪金5000 or t.salary1200;5.排序-查

6、询员工资料，按薪资升序排列order by t.salary;-查询员工资料，在部门号从大到小的情况下按按薪资升序排列order by t.department_id desc ,t.salary;-按姓名的字母顺序显示部门20和部门50中的所有员工的姓氏和部门编号select t.last_name , t.department_idwhere t.department_id in（20,50）order by t.last_name;-显示可以赚取佣金的所有员工的姓氏、薪金和佣金，按薪金和佣金的降序对数据进行排序select t.last_name , t.salary,mission_p

7、ctwhere mission_pct is not nullorder by t.salary desc,mission_pct desc;6.函数（已看到此处）-1、显示当前日期select sysdate from dual;-2、显示当前日期，格式为*年*月*日，别名为hdayselect to_char（sysdate,yyyy年mm月dd日） hday from dual;-3、编写一个查询，显示姓名以J、A或M开始的所有员工的姓氏（第一个字母大写，其余字母小写）和姓氏的长度，给每列一个合适的标签；select initcap（t.last_name） lname,length（t

8、.last_name） lenwhere substr（upper（last_name）,0,1） in（JAM -4、计算每位员工截止到当前时间入职的星期数，别名为weeks_worked。按聘用的星期数对结果进行排序。该星期数舍入到最接近的整数。同时显示员工的名字；select t.first_name,round（sysdate-t.hire_date）/7） as weeks_worked-5、计算每位员工截止到当前时间入职的月数，别名为months_worked。同时显示员工的名字。select t.first_name,round（months_between（sysdate,t.

9、hire_date） as months_worked-6、查询在1998年2月20日和1998年5月1日之间入职的员工的姓氏、职务标识和起始日期-7、创建一个查询。显示所有员工的姓氏和薪金。将薪金格式规定为15个字符长，左边填充$select lpad（t.salary,15,$） sal/*8、显示每位员工的姓氏、聘用日期和薪金复核日期，薪金复核日期是服务六个月之后的第一个星期一。将该列标记为review。这一日期的显示格式类似于：“Monday,the Thirty-First of July,2000”*/select t.last_name,t.hire_date,/*next_da

10、y（add_months（t.hire_date,6）,2） from employees t*/to_char（next_day（add_months（t.hire_date,6）,2）,fmday,the ddspth of month,yyyynls_date_language=english ） review/*第八题to_char的第三个参数用于设置本次查询使用的国家和地区，ddspth中spth为后缀，表示Spelled, ordinal number*/-9、显示员工的姓氏、聘用日期和该员工在星期几开始工作的。select t.last_name,t.hire_date,to_c

11、har（t.hire_date,DAY） as 开始-10、计算员工的月收入（工资+佣金）-11、创建一个查询，使其显示员工的姓氏，并用星号指明他们的年薪。每个星号代表一千美元。按薪资降序排列数据。select t.last_name|lpad（,trunc（t.salary/1000）+1,*）,t.salaryorder by t.salary desc;-12、创建一个查询。使其显示员工的姓氏和佣金额。如果某位员工不赚取佣金则显示“No Commission”，将该列标记为COMMselect t.last_name,nvl（to_char（t.salary*mission_pct,99

12、9,999.99）,No Commission） COMM/*13、使用decode函数编写一个查询，使其按照以下数据根据JOB_ID列的值显示所有员工的级别，同时显示员工的姓氏job gradeAD_PRES AST_MAN BIT_PROG CSA_REP DST_CLERK E都不是 0*/select t.last_name,t.job_id job,decode（t.job_id,AD_PRES ,ST_MANBIT_PROGCDE0 ） as grade -14、使用case语法重写一遍上题case t.job_id when then then else end as grade

13、 *补充*-1、显示当前日期，本月最后一天的日期，以及本月还剩多少天SELECT SYSDATE, LAST_DAY（SYSDATE） Last, LAST_DAY（SYSDATE） - SYSDATE Days Left FROM DUAL;-2、显示今年的第一天select trunc（sysdate,year） from dual;-3、显示本月的第一天month-4、最近一个星期四是哪天（不含今日）SELECT NEXT_DAY（sysdate,5）FROM DUAL;7.分组查询-求所有员工的平均工资、最高工资、最低工资和工资总和，给予适当的别名select avg（t.salary

14、）,max（t.salary）,min（t.salary）,sum（t.salary） -求每种工作的平均工资select avg（t.salary）group by t.job_id;-求每个部门中同一种工作的平均工资,同时显示部门号,按部门号升序显示select t.department_id,avg（t.salary）group by t.department_id,t.job_idorder by t.department_id;-查询出各部门的部门编号以及各部门的总工资和平均工资,按部门编号升序排列。select t.department_id,sum（t.salary）,avg（t

15、.salary） from employees t group by t.department_id-显示每种工作的人数select t.job_id,count（*）-显示员工最高工资超过10000的部门的id及其员工最高工资select t.department_id,max（t.salary） mhaving max（t.salary）-显示平均工资最高的部门id及其平均工资（要用到还未学习的知识）select * from（order by avg（t.salary） desc）where rownum28.多表查询/*多表查询，基本知识（预习自检）：什么是多表查询?什么是笛卡尔积？怎

16、样避免笛卡尔积？要将n个表联结在一起，至少需要多少个联结条件？什么是等值联结？如何在联结条件外附加限制条件？如何限定模糊的列名？如何进行非等值联结？什么是外联结？什么是自联结？如何用sql1999标准表达：笛卡尔积、自然联结、等值联结、内联结、左外联结、右外联结和全外联结？*/联结的分类：Joins that are compliant with the SQL:1999 standard include the following:Natural joins: NATURAL JOIN clause USING clause ON clauseOuter joins: LEFT OUTER

17、JOIN RIGHT OUTER JOIN FULL OUTER JOINCross joinssql：1999联结语法SELECT table1.column, table2.columnFROM table1NATURAL JOIN table2 |JOIN table2 USING （column_name） |JOIN table2 ON （table1.column_name = table2.column_name）|LEFT|RIGHT|FULL OUTER JOIN table2 CROSS JOIN table2;知识总结：In this lesson, you should

18、 have learned how to use joins to display data from multiple tables by using:EquijoinsNonequijoinsOuter joinsSelf-joinsNatural joinsFull （or two-sided） outer joins/*1.Write a query for the HR department to produce the addresses of all the departments. Use the LOCATIONS and COUNTRIES tables. Show the

19、 location ID, street address, city, state or province, and country in the output. Use a NATURAL JOIN to produce the results.*/select lct.location_id,lct.street_address,lct.state_province,cty.country_namefrom locations lct natural join countries cty;/*2.The HR department 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,department_id,dpt