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;
