oracle复制表去重,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 fromempunion

select deptno from dept;

--union all 并集操作,不去重, 有排序, 升序

select deptno fromempunion all

select deptno from dept;

--Intersect: 交集，去重

select deptno fromempintersect

select deptno from dept;

--Minus: 差集, 有方向，去重

select deptno fromemp

minusselect deptno fromdept;select deptno fromdept

minusselect 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‘) FROMdual;--大写： upper(字符串/列名)

SELECT UPPER(‘ddd‘) FROM dual;

消除空格 / 指定字符

--左截 ltrim(字符串/列名) 清除字符串前面的空格

SELECT LTRIM(‘hello ddd‘) FROMdual;--右截 rtrim(字符串/列名) 清除字符串后面的空格

SELECT RTRIM(‘hello ddd‘) FROMdual;--左右截 trim([c1 from c2])--去掉前后空格 trim(字符串/列名) 清除字符串前后的空格

SELECT TRIM(‘hello ddd‘) FROMdual;--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) fromdual;--substr(字符串/列名,开始位置,截取的长度)

select substr(‘hello world‘,2,3) from dual;

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

--字符串的连接 concat(字符串1,字符串2) oracle: ||连接符

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

select concat(concat(‘hello‘,‘world‘),‘haha‘) from dual;

数字函数

取整

--1)向上取整: 整数+ 1 ,小数去掉 15< 15.01 < 16 CEIL() 取的上限

select CEIL(15.01) fromdual;--2)向下取整 取整数, 小数去掉 取的这个数字的下限 15<15.99<16

select FLOOR(15.99) fromdual;---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) fromdual;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‘) FROMdual;--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 fromemp;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;

原文：https://www.cnblogs.com/64Byte/p/12709150.html