第1页 / 共24页
第2页 / 共24页
第3页 / 共24页
第4页 / 共24页
第5页 / 共24页
第6页 / 共24页
第7页 / 共24页
第8页 / 共24页
Oracle-数据库所有查询命令【精排版】.pdf
目录
1.简单查询........................................................................................................................................ 2
2.时间函数........................................................................................................................................ 3
3.分组查询........................................................................................................................................ 5
4.多表查询........................................................................................................................................ 6
5.多表连接........................................................................................................................................ 9
6.子查询 ......................................................................................................................................... 10
7.创建和管理表 .............................................................................................................................. 12
8.约束 ............................................................................................................................................. 15
9.视图，序列，索引 ...................................................................................................................... 17
10.pl sql 基础 .................................................................................................................................. 19
1.简单查询
--1. 消除重复的姓
select distinct e.last_name as 姓氏 from employees e
--2. 计算员工的月收入（工资+佣金）
select salary,salary*(nvl(commission_pct,0)+1) as 工资佣金 from employees
--3. 查询在 1998 年入职的员工
select * from employees e
where to_char(e.hire_date,'yyyy')='1998'
--4. 查询姓以 B 开头的员工
select * from employees e
where e.last_name like 'B%'
--5. 查询部门号为 10 或者 20 或者 30 的员工
select *from employees e
where e.department_id in (10,20,30)
-6. -查询在 1998-02-02 和 1998-05-01 之间入职的员工的姓氏、职务标识和起始日期
select t.last_name,t.job_id,t.hire_date from employees t
where t.hire_date between
to_date('19980220','yyyyMMdd') and to_date('19980501','yyyyMMdd');
--7. 显示员工名字中的第三个字母为“a”的所有员工的姓氏
select t.last_name from employees t
where t.last_name like '__a%';
--8. 显示赚取佣金的所有员工的姓氏、薪金和佣金，按薪金和佣金的降序对数据进行排序
select t.last_name , t.salary,t.commission_pct from employees t
where t.commission_pct is not null
order by t.salary desc,t.commission_pct desc;
2.时间函数
--1. 显示当前日期
select sysdate from dual;
--2. 显示当前日期，格式为 yyyy 年 mm 月 dd 日，别名为 hday
select to_char(sysdate,'yyyy"年"mm"月"dd"日"') hday from dual;
--3. 编写一个查询，显示姓名以 J、A 或 M 开始的所有员工的姓氏（第一个字母大写，其余
字母小写）和姓氏的长度，给每列一个合适的标签
select initcap(t.last_name) lname,length(t.last_name) len from employees t
where substr(upper(last_name),0,1) in('J','A','M');
--4 . 计算每位员工截止到当前时间入职的星期数，别名为 weeks_worked。按聘用的星期数
对结果进行排序。该星期数舍入到最接近的整数。同时显示员工的名字
select t.first_name,round((sysdate-t.hire_date)/7) as weeks_worked from employees t;
--5. 创建一个查询。显示所有员工的薪金。将薪金格式规定为 15 个字符长，左边填充$
select lpad(t.salary,15,'$') sal from employees t;
--6. 显示每位员工的姓氏、聘用日期和薪金复核日期，薪金复核日期是服务六个月之后的第
一个星期一。将该列标记为 review，格式类似于：Monday,the Thirty-First of July,2000
select t.last_name,t.hire_date,
/*next_day(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,yyyy','nls_date_language=english') review
from employees t;
注：to_char 的第三个参数用于设置本次查询使用的国家和地区，ddspth 中 spth 为后缀，表
示 Spelled, ordinal number
--7. 显示员工的姓氏、聘用日期和该员工在星期几开始工作的
select t.last_name,t.hire_date,to_char(t.hire_date,'DAY') as start from employees t;
--8. 创建一个查询，使其显示员工的姓氏，并用星号指明他们的年薪。每个星号代表一千美
元。按薪资降序排列数据
select t.last_name||lpad(' ',trunc(t.salary/1000)+1,'*'),t.salary from employees t
order by t.salary desc;
--9. 创建一个查询。使其显示员工的姓氏和佣金额。如果某位员工不赚取佣金则显示“No
Commission”，将该列标记为 COMM
select t.last_name,nvl(to_char(t.salary*t.commission_pct,'999,999.99'),'No Commission') COMM
from employees t;
--10. 使用 decode 函数编写一个查询，使其按照以下数据根据 JOB_ID 列的值显示所有员工
的级别，同时显示员工的姓氏
job_id
grade
AD_PRES
ST_MAN
IT_PROG
SA_REP
ST_CLERK
都不
select t.last_name,t.job_id job,
A
B
C
D
E
0
decode(t.job_id,'AD_PRES','A'
,'ST_MAN','B'
,'IT_PROG','C'
,'SA_REP','D'
,'ST_CLERK','E'
,'0') as grade
from employees t;
--11. 使用 case 语法重写一遍上题
select t.last_name,t.job_id job,
case t.job_id
when 'AD_PRES'then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
when 'SA_REP' then 'D'
when 'ST_CLERK' then 'E'
else '0'
end as grade
from employees t;
--12. 显示当前日期，本月最后一天的日期，以及本月还剩多少天
select sysdate,last_day(sysdate) "last",last_day(sysdate)-sysdate "days left" from dual;
--13. 显示今年的第一天
select trunc(sysdate,'year') from dual;
--14. 显示本月的第一天
select trunc(sysdate,'month') from dual;
--15. 最近一个星期四是哪天（不含今日）
select next_day(sysdate,5) from dual;
3.分组查询
--1. 求所有员工的平均工资、最高工资、最低工资和工资总和，给予适当的别名
select avg(t.salary),max(t.salary),min(t.salary),sum(t.salary) from employees t;
--2. 查询出各部门的部门编号以及各部门的总工资和平均工资,按部门编号升序排列。
select t.department_id,sum(t.salary),avg(t.salary) from employees t
group by t.department_id
order by t.department_id;
--3. 显示每种工作的人数
select t.job_id,count(*) from employees t
group by t.job_id;
--4. 显示员工最高工资超过 10000 的部门的 id 及其员工最高工资
select t.department_id,max(t.salary) m from employees t
group by t.department_id
having max(t.salary)>10000;
--5. 显示平均工资最高的部门 id 及其平均工资
select * from(
select t.department_id,avg(t.salary)
from employees t
group by t.department_id
order by avg(t.salary) desc
)
where rownum<2;
4.多表查询
多表查询，基本知识：
什么是多表查询？
什么是笛卡尔积？
怎样避免笛卡尔积？
要将 n 个表联结在一起，至少需要多少个联结条件？
什么是等值联结？
如何在联结条件外附加限制条件？
如何限定模糊的列名？
如何进行非等值联结？
什么是外联结？
什么是自联结？
如何用 sql1999 标准表达：笛卡尔积、自然联结、等值联结、内联结、左外联结、右外
联结和全外联结？
联结的分类：
Joins that are compliant with the SQL:1999 standard include the following:
Natural joins:
NATURAL JOIN clause
USING clause
ON clause
Outer joins:
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
Cross joins
sql1999 联结语法：
SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2]
|[JOIN table2 USING (column_name)]
|[JOIN table2 ON (table1.column_name = table2.column_name)]
|[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]
|[CROSS JOIN table2];
--1. Write a query for the HR department to produce the addresses of all the departments. Use
the LOCATIONS and COUNTRIES tables. Show the 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_name
from locations lct
natural join countries cty;
--2. The HR department needs a report of employees in Toronto. Display the last name, job,
department number, and the department name for all employees who work in Toronto.
select emp.last_name,emp.job_id,dpt.department_id,dpt.department_name
from employees emp
join departments dpt on emp.department_id=dpt.department_id
join locations lct on dpt.location_id=lct.location_id
where lct.city='Toronto';
--3. Create a report for the HR department that displays employee last names, department
numbers, and all the employees who work in the same department as a given employee. Give
each column an appropriate label. Save the script to a file named lab_06_06.sql.
select emp.last_name Employee,emp.department_id,colleague.last_name colleague
from employees emp
join employees colleague on emp.department_id=colleague.department_id
where emp.employee_id<>colleague.employee_id
order by Employee;
--4. The HR department needs a report on job grades and salaries. To familiarize yourself with the
JOB_GRADES table, first show the structure of the JOB_GRADES table. Then create a query that
displays the name, job, department name, salary, and grade for all employees.*/
--建表
create table JOB_GRADES(
LOWEST_SAL NUMBER(6),
HIGHEST_SAL NUMBER(6),
GRADE_LEVEL CHAR(1)
)
--插入数据
insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL)
values (30000, 40000, 'F');
insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL)
values (20000, 30000, 'E');
insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL)
values (15000, 20000, 'D');
insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL)
values (8500, 15000, 'C');
insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL)
values (5500, 8500, 'B');
insert into JOB_GRADES (LOWEST_SAL, HIGHEST_SAL, GRADE_LEVEL)
values (2000, 5000, 'A');
commit;
select emp.last_name Employee,emp.salary,g.grade_level
from employees emp
join job_grades g on emp.salary between g.lowest_sal and g.highest_sal;
--5. The HR department wants to determine the names of all the employees who were hired after
Davies. Create a query to display the name and hire date of any employee hired after employee
Davies.
select emp.last_name Employee,emp.hire_date
from employees emp
join employees clg on emp.hire_date > clg.hire_date
where clg.last_name='Davies'
order by emp.hire_date;
--6. The HR department needs to find the names and hire dates of all the employees who were
hired before their managers, along with their managers’ names and hire dates. Save the script to
a file named lab_06_09.sql.
select emp.last_nam,emp.hire_date,mgr.last_name,mgr.hire_date
from employees emp
join employees mgr on emp.manager_id=mgr.employee_id and emp.hire_date < mgr.hire_date;
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
