Oracle--联合查询、复制表/表结构、函数

联合查询

Union: 对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
　　A U B: 把A与B的集合合并

Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
　　A ∩ B : 存在A,又存在于B的数据

Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序, 有方向
　　A - B: 存在于A, 但不存在于B的数据
　　B - A: 存在于B, 但不存在于A的数据

例：联合查询, 要求这个两个select的结果集数据是相关的

-- union: 并集操作,去重, 有排序, 升序
select deptno from emp
union 
select deptno from dept;

--union all  并集操作,不去重, 有排序, 升序
select deptno from emp
union all 
select deptno from dept;

--Intersect: 交集，去重
select deptno from emp
intersect
select deptno from dept;

--Minus: 差集, 有方向，去重
select deptno from emp
minus
select deptno from dept;

select deptno from dept
minus
select deptno from emp;

复制表/表结构，根据子查询

1、创建表:  create table  表名(列的声明)  as;

　　根据已存在的表结构, 复制一份

--创建一个emp_back(empno,ename,job)
create table emp_back as
select empno, ename,job from emp;

2、 创建表: 只需要表结构,不需要数据

create table emp_back2 as
select empno, ename,job from emp where 1!=1;

函数

字符串函数

Oracle: dual, 虚表.,  为了补全select的语法

length(字符串/列名) 

select length(ename),ename from emp;

首字母大写： 每一个单词的首字母大写  initcap(字符串/列名)

SELECT initcap('hello world') FROM dual;

大写、小写

--小写： lower(字符串/列名)
SELECT LOWER('HELLO') FROM dual;
--大写： upper(字符串/列名)
SELECT UPPER('ddd') FROM dual;

消除空格 / 指定字符

--左截 ltrim(字符串/列名)  清除字符串前面的空格
SELECT LTRIM('     hello   ddd    ')  FROM dual;

--右截 rtrim(字符串/列名)  清除字符串后面的空格
SELECT RTRIM('     hello   ddd    ')  FROM dual;
--左右截 trim（[c1 from c2]）
--去掉前后空格  trim(字符串/列名)  清除字符串前后的空格
SELECT TRIM('     hello   ddd    ')  FROM dual;

-- trim(去除的字符  from  字符串)  去除字符串前后指定的字符
-- 从  hello haha  xixi hh   去除字符串前后的h
select trim('h' from 'hello haha  xixi hh') from dual;

替换

----替换 replace(字符串/列名, 旧的字符串, 新的字符串)  全部替换
-- 把 emp中ename中S 替换为*
select ename,replace(ename,'S','*') from emp;

查找某个字符第一次出现的位置

-- 查找某个字符第一次出现的位置  instr(字符串/列名,查找的字符)
-- 查找 hello world 中l第一次出现位置, 找不到, 返回 0
-- java/js 字符串的下标从0开始的
-- ****oracle  字符串的下标从1开始的
select instr('hello world','a') from dual;

截取字符串

--截取字符串: substr(字符串/列名,开始位置[,截取的长度])
-- substr(字符串/列名,开始位置) 从字符串开始位置截取到字符串末尾
select substr('hello world',2) from dual;

--substr(字符串/列名,开始位置,截取的长度)
select substr('hello world',2,3) from dual;

字符串的拼接    推荐使用 ||    conact一次只能拼接两个

--字符串的连接  concat(字符串1,字符串2)     oracle:  ||连接符
select concat('hello','world') from  dual;

select 'hello'||'world'||'  haha' from  dual;
--练习:  使用concat 连接三个 'hello','world','  haha'
select concat(concat('hello','world'),' haha') from  dual;

数字函数

取整

--1)向上取整:  整数+ 1 ,小数去掉   15< 15.01 < 16  CEIL()  取的上限
 select CEIL(15.01) from dual;
--2)向下取整  取整数, 小数去掉  取的这个数字的下限   15<15.99<16
 select FLOOR(15.99) from dual;

--   -16< -15.01  < -15
select CEIL(-15.01) from dual;  ---15
select FLOOR(-15.99) from dual;  ---16

--3)四舍五入 *** ROUND(数字,保留小数位)
select ROUND(15.89,1) from dual; 
select ROUND(15.39,0) from dual; 

日期函数

获取当前系统时间

 **** sysdate: 获取当前的系统时间: oracle默认日期格式 "dd-m月-yy"
-- 工具设置日期的默认格式
select sysdate from dual;

相隔多少月

--获取两个日期相隔多少月 months_between（日期，日期）  相隔的月, number

--1999,1,1  -到今天相隔多少个月
select months_between(sysdate,'1999-01-01') from dual;

add_months（日期，月份增量）

-- 在日期加月份 add_months（日期，月份增量） 返回的日期
-- 计算: 今天日期加9个月是哪一天
select add_months(sysdate,-9) from dual;

指定星期的日期

--从指定日期开始往后找，找到指定星期的日期  next_day(日期字符串,星期字符串) 返回的还是日期
--不包括开始日期
SELECT next_day(SYSDATE, '星期二') FROM dual;

最后一天的日期

--查找指定日期这个月的最后一天的日期 last_day(日期)  返回的日期
SELECT last_day('2000-03-01') FROM dual;

转换函数

-- to_char() 转换为字符串类型
-- to_date() 转换为日期类型
-- to_number() 转换为number类型

-- to_char() 把一个数值转换指定格式的字符串   金钱:  $/￥ 1,999,999.99
--9 表示任意数值, 如果位数不够,不会补位
SELECT to_char(12345678.212,'999,999,999,999.99') FROM dual;

--0 表示任意数值, 如果位数不够,补0
SELECT to_char(12345678.212,'$000,000,000,000.00') FROM dual;

to_char() 获取日期指定部门的值  ***

/*
--yyyy:四位的年  yy：两位的年，  mm:两位的月数字， month：月份，带月字
-- dd: 日  ddd:表示一年的第几天     day：星期    ww:一年的第几个星期   w:一月的第几个星期
--hh: 小时， 12进制   hh24: 小时，24进制   mi：分钟   ss:秒
*/
select to_char(sysdate,'yyyy') 四位的年,to_char(sysdate,'mm') 二位的月, to_char(sysdate,'month') 月份,
to_char(sysdate,'dd') 日期,to_char(sysdate,'ddd') 年的第多少天,to_char(sysdate,'day') 星期,
to_char(sysdate,'ww') 年的第多少周, to_char(sysdate,'w') 月的第多少周 from dual; 

to_date(日期字符串, 日期格式)把指定格式的字符串转换为日期

INSERT  INTO t_student  values('1003','王五',22,to_date('1998-12-21','yyyy-mm-dd'),'男','0');

to_number() 把字符串转换为number类型

--  $99.12 + 12;
--  to_number('$99.12','$999,999,999.99') --> 99.12
select to_number('$99.12','$999,999,999.99')+12 from dual;

其他函数

对null处理函数:

 ****nvl(列名,转换值)   nvl2()     
-- nvl2(列名,值1,值2) 如果列名的值是null,返回的值2,如果不是null,返回 值1
--查询每一个员工的年收入   (工资+奖金) * 12
select (sal + nvl(comm,0))*12 from emp;

select (sal + nvl2(comm,comm,0))*12 from emp;

decode:  if...else if....else

--decode()    对case的简写   swicth  等值判断
--decode(value/列名，if1,then1,if2,then2...,else) 
-- 如果 value = if1, 返回的then1
-- 如果 value = if2, 返回的then2
--...
-- 都不满足: 返回else表达式的结果

--根据岗位发奖金:   CLERK  :500   SALESMAN: :600   MANAGER: :300   其他   :100
select e.*,decode(job,
　　　　　　　　　　'CLERK',500,
　　　　　　　　　　'SALESMAN',600,
　　　　　　　　　　'MANAGER',300,
　　　　　　　　　　100) 奖金  from  emp e;

case

select SNO ||NAME || SCORE || case  
                              when score>60 then
        　　　　　　　　　　　　　'pass' 
   　　　　　　　　　　　　　　 　 else 'fail' 
　　　　　　　　　　　　　　　　　　end  成绩 from score1;            

分析函数

分组排名函数  返回的名次  数字

　　　　函数名([参数])  over ([分组子句:partition by 列名] [排序子句: order by 列名 排序方式]))

-- 部门编号分组,根据员工的薪水高低,进行排名
--row_number()  组内编号连续,  相同的值, 不会在相同名次
select e.*,row_number() over(partition by deptno order by sal desc)   名次 from emp e;

--rank()  相同的值, 名次相同, 组内编号可能会跳跃, 相同的名次, 把后面编号跳过
select e.*,rank() over(partition by deptno order by sal desc)   名次 from emp e;

--dense_rank()   相同的值, 名次相同, 组内编号连续
select e.*,dense_rank() over(partition by deptno order by sal desc)   名次 from emp e;

SIGN(数字/列名)

-- SIGN(数字/列名)    正数 返回 1   负数:  返回 -1  0返回0
select sign(90-90)  from dual;