（尚硅谷）Orcale sql 查询select 日期转换 内连接外连接 多文件查询

<span style="font-size:18px;">--1.使用一个变量
declare
--声明一个变量
v_name varchar2(25);
begin
--通过select..into..语句为变量赋值
 select last_name into v_name 
 from employees
 where employee_id = 186;
--打印变量
dbms_output.put_line(v_name);
end ;

declare
    --声明变量
    v_name varchar2(25);
    v_email varchar2(25);
    v_salary number(8,2);
    v_job_id varchar2(10);
begin
    --通过select ...into ...语句为变量赋值
    --被赋值的变量与SELECT中的列明要一一对应
    select last_name , email ,salary , job_id into 
    v_name , v_email , v_salary , v_job_id 
    from employees
    where employee_id =112 ;
    --打印
    Dbms_Output.put_line(v_name||','||v_email||','||v_salary
    ||','||v_salary||','||v_job_id);
end;
---------
declare 
--定义一个记录类型
type customer_type is record(
 v_cust_name varchar2(20),
 v_cust_id number (10)
 );
 --声明记录类型的变量
 v_customer_type customer_type;
begin
 v_customer_type.v_cust_name :='刘德华';
 v_customer_type.v_cust_id :=1001;
dbms_output.put_line(v_customer_type.v_cust_name ||','
||v_customer_type.v_cust_id);
end;
--使用 %type 定义变量，动态的获取数据的声明类型
declare
--定义一个记录类型
     type emp_record is record(
          v_name employees.last_name%type,
          v_emal employees.email%type,
          v_salary employees.salary%type,
          v_job_id employees.jb_id%type);
    --声明自定义记录的类型的变量
 v_emp_record emp_record;
begin
--通过 select..into...语句为变量赋值
select last_name , email , salary , job_id into v_emp_record
from employees
where employee_id = 186;
--打印变量的值
dbms_output.put_line(v_emp_record.v_name ||','||v_emp_record.v_email
||','||v_emp_record.v_salary||','||v_emp_record.v_job_id);
end;


--last_name 为 'King' 的员工信息
select Last_name 
from employees
where last_name = 'King';
--查询 1998-4-24 来公司的员工有哪些?
select hire_date 
from employees
--where hire_date = to_date('1998-4-24','yyyy,mm,dd');
where hire_date = '24/4月/1998';
--查询工资在 5000 -- 10000 之间的员工信息.
select last_name , salary
from employees
--where salary between 5000 and 10000;
---where salary in(5000,10000);??????????????
where salary >=5000 and salary <=10000;

--查询工资等于 6000, 7000, 8000, 9000, 10000 的员工信息

select last_name , salary
from employees
--where salary = 6000 or salary = 7000 or salary = 8000 or salary = 9000 or salary =10000;  
where salary in(6000,7000,8000,9000,10000);

-- 查询 LAST_NAME 中有 'o' 字符的所有员工信息.
select last_Name 
from employees
where last_name like '%o%'; 
--查询 LAST_NAME 中第二个字符是 'o' 的所有员工信息.
select last_name
from employees
where last_name like '_o%';
-- 查询 LAST_NAME 中含有 '_' 字符的所有员工信息
update employees
set last_name = 'wangqi_Tom'
where employee_id = 195;

select last_name
from employees
where last_name like '%\_%'escape '\';

--查询 COMMISSION_PCT 字段为空的所有员工信息
select last_name , commission_pct
from employees
where commission_pct is null;
--查询 COMMISSION_PCT 字段不为空的所有员工信息
select last_name , commission_pct
from employees 
where commission_pct is not null;

select last_name , commission_pct
from employees 
where commission_pct is not null
order by commission_pct desc;

--第 3 章  单行函数
--打印出 "2009年10月14日 9:25:40" 
--格式的当前系统的日期和时间.

select to_char(sysdate , 'yyyy"年"mm"月"dd hh:mi:ss')
from dual;

--格式化数字: 1234567.89 为 1,234,567.89
select to_char (1234567.89 , '999,999,999.99')
from dual;
-- 字符串转为数字时
--若字符串中没有特殊字符, 可以进行隐式转换:
select '1231241.1231'+10
from dual;

--若字符串中有特殊字符, 例如 '1,234,567.89', 
--则无法进行隐式转换, 需要使用 to_number() 来完成

select to_number('1,234,567.89' , '999,999,999.99')+10
from dual;

--对于把日期作为查询条件的查询, 一般都使用 to_date()
--把一个字符串转为日期, 这样可以不必关注日期格式

select last_name , hire_date 
from employees
--where hire_date = to_date ( '1998-5-23' , 'yyyy-mm-dd' );
where to_char(hire_date ,'yyyy-mm-dd') = '1998-5-23';--?????????????
--查询每个月倒数第 2 天入职的员工的信息.
select last_name , hire_date 
from employees
where hire_date = last_day(hire_date )-2;

--计算公司员工的年薪
select last_name , salary*12*(1+commission_pct) year_sal
from employees;

select last_name , salary*12*(1+nvl(commission_pct , 0)) 
year_val
from employees;

--查询部门号为 10, 20, 30 的员工信息, 若部门号为 10,
-- 则打印其工资的
-- 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍,
-- 30 号部门打印其工资的 1.3 倍数
--使用 case-when-then-else-end
select last_name , department_id , salary ,
case department_id 
when 10 then salary * 1.1  
when 20 then salary * 1.1

when 30 then salary * 1.3
else salary
end
from employees
--where department_id in (10,20,30);

--使用descode
select last_name  , department_id , salary , 
decode (department_id , 10 , salary *1.1 ,
                     20 , salary *1.2 ,
                     30 , salary *1.3 ,
                     salary)
from employees
where department_id in (10,20,30) ;

--第 4 章  多表查询
--表连接查询时, 若两个表有同名的列,
-- 必须使用表的别名对列名进行引用, 否则出错!
--27. 查询出公司员工的 last_name, department_name, city
select last_name , e.department_id ,l.city
from  employees e left outer join departments d
on e.department_id = d.department_id
left outer join locations l 
on d.location_id = l.location_id;
	
--查询出 last_name 为 'Chen' 的 manager 的信息. 
--(员工的 manager_id 是某员工的 employee_id) 
select e1.last_name , e1 .manager_id , e2.last_Name
from employees e1 left outer join employees e2
on e1.manager_id = e2.employee_id;

--查询每个员工的 last_name 和 GRADE_LEVEL(在 JOB_GRADES 
--表中). ---- 非等值连接
select GRADE_LEVEL from JOB_GRADES;

select last_name , e.salary , GRADE_LEVEL ,
lowest_sal , highest_sal
from employees e join job_grades j 
on e.salary between j.lowest_sal and j.highest_sal;

select last_name , e.department_id , department_name
from employees e left outer join departments d
on e.department_id= d.department_id;

select e.last_name , d.department_name 
from employees e join departments d
using (department_id);

select e.last_name , e.department_id ,d.department_name ,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id;


</span>