Oracle-数据库所有查询命令
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,tmission_pct from employees t where tmission_pct is not null order by t.salary desc,tmission_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，格式类似于：nday,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*tmission_pct,‘999,999.99’),‘No Commission’) COMM from employees t;
–10. 使⽤ decode 函数编写⼀个查询，使其按照以下数据根据 JOB_ID 列的值显⽰所有员⼯ 的级别，同时显⽰员⼯的姓⽒ job_id grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E 都不 0 select t.last_name,t.job_id job,
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.depa
rtment_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
Cross joins
sql1999 联结语法：
lumn, lumn
FROM table1
[NATURAL JOIN table2]
|[JOIN table2 USING (column_name)]
|[JOIN table2 ON (lumn_name = lumn_name)]
|[LEFT|RIGHT|FULL OUTER JOIN table2 ON (lumn_name = lumn_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_untry_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
ployee_id<&ployee_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.ade_level
from employees emp
join job_grades g on emp.salary between g.lowest_sal and g.highest_sal;
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_ployee_id and emp.hire_date < mgr.hire_date; 5.多表连接
using ⼦句
ployee_id, e.last_name, d.location_id
from employees e join departments d
using (department_id);
–on ⼦句
ployee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e
join departments d on (e.department_id=d.department_id);
–使⽤ on ⼦句创建多表连接
select employee_id, city, department_name
from employees e
join departments d on d.department_id=e.department_id
join locations l on d.location_id=l.location_id;
–左外连接
select e.last_name, e.department_id, d.department_name
from employees e
left outer join departments d on (e.department_id=d.department_id);
–右外连接
select e.last_name, e.department_id, d.department_name
from employees e
right outer join departments d on (e.department_id=d.department_id) ;
–满外连接
select e.last_name, e.department_id, d.department_name
from employees e
full outer join departments d on (e.department_id=d.department_id) ;
–增加条件连接
ployee_id, e.last_name, e.department_id,d.department_id,d.location_id
from employees e
join departments d on (e.department_id=d.department_id)
and e.manager_id=149
6.⼦查询
基础知识：
什么是⼦查询？
⼦查询能解决什么类型的问题？
⼦查询可以⽤在什么位置？
⼦查询有哪些类型？
核⼼知识：
–1. ⼦查询概念：
⼦查询就是按顺序执⾏系列查询并将前⼀个查询的结果作为下⼀个查询使⽤的值
–2. ⼦查询语法
select select_list from table
where expr operator(
select select_list from table
);
注：operator 包含⽐较表达式，如: >, =, IN ，等
2.1 ⼦查询(内查询)先于主查询(外查询)执⾏
2.2 ⼦查询的结果⽤于外查询
–3. 查询⼯资⽐ Abel ⾼的员⼯的姓⽒和⼯资
select last_name, salary from employees
where salary > (
select salary from employees
where last_name=‘abel’
);
–4. ⼦查询中的空值：
in(…)的含义等价于= any(…)，所以⼦查询中是否有空值，对结果没有影响
但是，not in(…)的含义等价于<> all(…)，如果⼦查询中出现空值，整个表达式为空
⾃然：任意⽐较 all(…)，如果⼦查询中出现空值，整个表达式为空
–1. 查询部门名称为 Marketing 和 IT 的员⼯信息
select * from employees e
where e.department_id in (
select d.department_id from departments d
where d.department_name in(‘Marketing’,‘IT’)
);
–2. 查询不是经理的员⼯的信息
select *
from employees e
ployee_id not in
(select distinct e1.manager_id
from employees e1
where e1.manager_id is not null);
–3. 查询⼯资⽐ 10 号部门中其中⼀个员⼯低的员⼯信息
select *from employees e
where e.salary<any (
select e1.salary from employees e1
where e1.department_id=10
);
–4. 显⽰和 Austin 同部门，⼯资低于 Baer 的雇员有哪些oracle数据库怎么查询表
select e.last_name from employees e
where e.department_id=(select department_id from employees where last_name=‘Austin’) and e.salary<(select salary from employees where last_name=‘Baer’);
7.创建和管理表
知识点：
1.有哪些数据库对象？
表：⽤于存储数据
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论