PLSQL 的简单命令之三
时间：2021-07-01 10:21:17
帮助过：35人阅读
查找两个表中ID相等的
select a.id, a.name,b.math
from stu a,scores b
where a.id
= b.id
-- 右外连接
select b.id, a.name,b.math
from stu a,scores b
where a.id(
+)
= b.id
select b.id, a.name,b.math
from stu a
right outer join scores b
on a.id
= b.id
-- 左外连接
select a.id, a.name,b.math
from stu a,scores b
where a.id
= b.id(
+)
select a.id, a.name,b.math
from stu a
left outer join scores b
on a.id
= b.id
--1. 显示所有员工的姓名，部门号和部门名称。
select a.last_name ,b.department_id,b.department_name
from employees a ,departments b
--2. 查询90号部门员工的job_id和90号部门的location_id
select a.job_id ,b.location_id
from employees a ,departments b
where b.department_id
=90
/*3. 选择所有有奖金的员工的
last_name , department_name , location_id , city
*/
select a.last_name,b.department_name,b.location_id,c.city
from employees a,departments b,locations c
/*4. 选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name
*/
select a.last_name,a.job_id,b.department_id,b.department_name
from employees a,departments b ,locations
where locations.city
= ‘Toronto‘
/*5. 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
*/
select a.last_name
as employees ,a.employee_id
as emp#,b.last_name
as manager,b.employee_id
as mgr#
from employees a,employees b
where a.manager_id
= b.employee_id(
+)
--查询公司员工工资的最大值，最小值，平均值，总和
select max(salary)
from employees
select min(salary)
from employees
select avg(salary)
from employees
select sum(salary)
from employees
--查询各job_id的员工工资的最大值，最小值，平均值，总和
select job_id,
max(salary),
min(salary),
avg(salary),
sum(salary)
from employees
group by job_id
--选择具有各个job_id的员工人数
select job_id,
count(job_id)
from employees
group by job_id
--查询员工最高工资和最低工资的差距（DIFFERENCE）
select max(salary)
-min(salary)
as difference from employees
/*查询各个管理者手下员工的最低工资，
其中最低工资不能低于6000，
没有管理者的员工不计算在内
*/
select manager_id,
min(salary)
from employees
where manager_id
is not null
having min(salary)
>=6000
group by manager_id
--查询所有部门的名字，location_id，员工数量和工资平均值
select a.department_name,a.location_id,
count(b.employee_id),
avg(b.salary)
from departments a
full outer join employees b
on a.department_id
= b.department_id
group by a.department_name,a.location_id
/*查询公司在1995-1998年之间，每年雇用的人数，结果类似下面的格式
total 1995 1996 1997 1998
20 3 4 6 7
*/
/*
select count(*), to_char(hire_date,‘yyyy‘) from employees
where to_char(hire_date,‘yyyy‘) between ‘1995‘ and ‘1998‘
group by to_char(hire_date,‘yyyy‘)
*/
/*decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
*/
select count(
*),
count(decode(to_char(hire_date,
‘yyyy‘),
‘1995‘,
1,
null)) "
1995",
count(decode(to_char(hire_date,
‘yyyy‘),
‘1996‘,
1,
null)) "
1996" ,
count(decode(to_char(hire_date,
‘yyyy‘),
‘1997‘,
1,
null)) "
1997",
count(decode(to_char(hire_date,
‘yyyy‘),
‘1998‘,
1,
null)) "
1998"
from employees
where to_char(hire_date,
‘yyyy‘)
between ‘1995‘ and ‘1998‘
PLSQL 的简单命令之三
标签：null to_char pre 显示 sql语句 cat 函数返回值 group 等于
