Oracle-01-语法基础

说明:
数据库版本11g
下面所用的表为使用scott用户登录时系统默认表
1.日期类型转换

# 字符串转日期
SELECT TO_DATE('2020-07-11', 'YYYY-MM-DD') AS A_DAY FROM DUAL;
# 当前日期转字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS TODAY FROM DUAL;
# 字符串转时间戳
SELECT TO_TIMESTAMP('2020-07-11 12:33:33.11', 'YYYY-MM-DD HH24:MI:SS.FF') AS A_DAY FROM DUAL;
# 时间戳转字符串
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF') AS TODAY FROM DUAL;
# 当前日期转字符串替换-然后转数字
SELECT TO_NUMBER(REPLACE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), '-')) FROM DUAL;
# 参数q表示季度
SELECT TO_CHAR(SYSDATE, 'q') FROM DUAL;

参考:
https://blog.csdn.net/delphi308/article/details/25654455
DUAL是虚表,可执行计算

SELECT 1+1 FROM DUAL;
# mysql语句
SELECT sysdate() FROM DUAL;

2.decode函数

# DEPTNO匹配10输出部门1,匹配20输出部门2,其他输出部门3
SELECT DEPTNO, DECODE(DEPTNO, 10, '部门1', 20, '部门2', '部门3') FROM EMP;

3.CASE WHEN

SELECT DEPTNO,
       CASE WHEN DEPTNO = 10
            THEN '部门1'
            WHEN DEPTNO = 20
            THEN '部门2'
            ELSE '部门3' END FROM EMP;

4.交集\差集

create table A(id int, name varchar2(10));
create table B(id int, name varchar2(10));
insert into A values (1, '张三');
insert into A values (2, '李四');
insert into B values (2, '李四');
insert into B values (3, '王五');
# 交集
select * from A intersect select * from B;
# 差集
select * from A minus select * from B;

5.子查询

# 非关联子查询
select a.ename, a.sal
from emp a
where a.deptno = (
select b.deptno from dept b where b.loc = 'NEW YORK'
) 
# 关联子查询
select a.deptno, (select b.loc from dept b where b.deptno = a.deptno)
from emp a

6.exists和in
in做全表扫描,exists非全表扫描

# 查询属于领导的员工
select * from emp a where exists (select 1 from emp b where b.mgr = a.empno)
# 查询不存在员工的部门
select * from dept d where not exists (select 1 from emp e where e.deptno = d.deptno)

7.rownum分页

# 查询前5条记录
select * from emp where rownum < 5;
# 使用>要使用别名
select * from (select e.*, rownum as rn from emp e) where rn > 5;
# 查询薪水前3的记录
select * from (select * from emp order by sal desc) where rownum <= 3;
# 分页
select * from (select emp.*, rownum rn from emp) a where a.rn between 5 and 10;
# 时间区间不能用between and 要用>=和<

8.复制

# 将查询表中数据复制到目标表中,要求目标表不存在自动建表,可以加过滤条件
# 这种方式是oracle特有语法
create table myemp as select * from emp where deptno = 10;
# 要求目标表存在
insert into myemp select * from emp;

9.merge

# 数据准备
create table products
(product_id integer, req_no varchar(32), product_name varchar2(60), catagory varchar2(60));

insert into products values (1501, '001', 'VIVITAR 25MM', 'ELECTRNCS');
insert into products values (1502, '002', 'OLYMPOS IS50', 'ELECTRNCS');
insert into products values (1600, '003', 'PLAY GYM', 'TOYS');
insert into products values (1601, '003', 'LAMALE', 'TOYS');
insert into products values (1717, '001', 'HARRY POTTER', 'DVD');
insert into products values (1666, '002', 'HARRY POTTER', 'DVD');

commit;

select * from products t;


# 不存在则插入,存在则更新
merge into products a
using (select 1717 product_id, '002' req_no from dual) b
on (a.product_id = b.product_id and a.req_no = b.req_no)
when matched then
  update set product_name = '进行更新啦', catagory = '新的category'
when not matched then
  insert (product_id, req_no, product_name, catagory) values (1717, '002', '新产品', 'CCA');

10.递归查询

# 递归查领导
# 如果pid在前属于向上查询
# select语句后可加过滤条件
select * from emp
start with empno=7369 or empno=7934
connect by prior mgr=empno;

11.分析函数
根据部门分区对薪水连续求和
求薪资总和
计算各员工薪资的份额

# 份额保留5位有效数字
select deptno, ename, sal, 
sum(sal) over (order by deptno) 连续求和,
sum(sal) over () 总和,
100*round(sal/sum(sal) over (),5) "份额(%)"
from emp;

select deptno, ename, sal,
sum(sal) over (partition by deptno order by ename) 部门连续求和,
sum(sal) over (partition by deptno) 部门总和,
100*round(sal/sum(sal) over (partition by deptno, 4)) "部门份额(%)",
sum(sal) over (order by deptno, ename) 连续求和,
sum(sal) over () 总和,
100*round(sal/sum(sal) over (),4) "总份额(%)"
from emp;

根据部门分组,员工薪资排序

# 数据准备
create table employee (empid int, deptid int, salary decimal(10,2));
insert into employee values (1, 10, 5500.00);
insert into employee values (2, 10, 4500.00);
insert into employee values (3, 20, 1900.00);
insert into employee values (4, 20, 4800.00);
insert into employee values (5, 40, 6800.00);
insert into employee values (6, 40, 14500.00);
insert into employee values (7, 40, 44500.00);
insert into employee values (8, 50, 6800.00);
insert into employee values (9, 50, 7800.00);
commit;

select * from employee;

# 根据部门分组,员工薪资排序
select employee.*, row_number() over (partition by deptid order by salary desc) rank from employee;

group by\rollup\cube

# 根据月份\区域分组
select earnmonth, area, sum(personincome)
from earnings
group by earnmonth, area;

# 同月份对区域分组求和
select earnmonth, area, sum(personincome)
from earnings
group by rollup(earnmonth, area);

# 同区域对月份\同月份对区域分组求和
select earnmonth, area, sum(personincome)
from earnings
group by cube(earnmonth, area)
order by earnmonth, area;

grouping显示小计\合计字段

select earnmonth,
(case when ((grouping(area) = 1) and (grouping(earnmonth) = 0)) then '月份小计'
      when ((grouping(area) = 1) and (grouping(earnmonth) = 1)) then '总计'
      else area end) as area,
sum(personincome)  
from earnings
group by rollup(earnmonth, area);

排名rank\dense_rank\row_number

# 如果前两名并列,第3名排名为2
select earnmonth 月份, area 地区, sname 打工者, personincome 收入,
       rank() over (partition by earnmonth, area order by personincome desc) 排名
from earnings;  

# 如果前两名并列,第3名排名为3
select earnmonth 月份, area 地区, sname 打工者, personincome 收入,
       dense_rank() over (partition by earnmonth, area order by personincome desc) 排名
from earnings;  

# 如果前两名并列,则其排名为1,2
select earnmonth 月份, area 地区, sname 打工者, personincome 收入,
       row_number() over (partition by earnmonth, area order by personincome desc) 排名
from earnings;      

最高\最低\平均\求和

select distinct earnmonth 月份, area 地区,
       max(personincome) over (partition by earnmonth, area) 最高值,
       min(personincome) over (partition by earnmonth, area) 最低值,
       avg(personincome) over (partition by earnmonth, area) 平均值,
       sum(personincome) over (partition by earnmonth, area) 求和
from earnings;