oracle常用sql语句函数

oracle常用sql语句函数
sql执行顺序
•FROM
•WHERE
•GROUP BY
•HAVING
•SELECT
•DISTINCT
•UNION
•ORDER BY

一、Oracle数据库操作
1、创建数据库

create database databasename

2、删除数据库

drop database dbname

3、备份数据库

 --完全备份
 exp demo/password@orcl buffer=1024 file=d：\back.dmp full=y
 demo：用户名
 password:密码
 buffer: 缓存大小
 file: 具体的备份文件地址
 full: 是否导出全部文件
 ignore: 忽略错误，如果表已经存在，则也是覆盖

二、Oracle表操作
1、创建表

create table qy_test(
    ID integer not null,
    name varchar(50) default 'name' ,
    age number not null,
    sex nvarchar2(50) default '男',
    tm timestamp default sysTIMESTAMP,
    primary key(ID)
)
--添加表注释：
comment on table qy_test IS '测试表';
--添加字段注释：
comment on column qy_test.id is '编号';
comment on column qy_test.name is '姓名';
comment on column qy_test.age is '年龄';
comment on column qy_test.sex is '性别';


--根据已有的表创建新表：
--表数据复制
insert into table1 (select * from table2);

--复制表结构
create table table1 select * from table2 where 1>1;

--复制表结构和数据
create table table1 as select * from table2;

--复制指定字段
create table table1 as select id, name from table2 where 1>1;

2、删除表

drop table tabname

3、重命名表

--说明：alter table 表名 rename to 新表名
eg：alter table tablename rename to newtablename

4、增加字段

--说明：alter table 表名 add (字段名 字段类型 默认值 是否为空);
例：alter table tablename add (ID int);
eg：alter table tablename add (ID varchar2(30) default '空' not null);

5、修改字段

说明：alter table 表名 modify (字段名 字段类型 默认值 是否为空);
eg：alter table tablename modify (ID number(4));
6、重名字段

说明：alter table 表名 rename column 列名 to 新列名 （其中：column是关键字）
eg：alter table tablename rename column ID to newID;

7、删除列

说明：alter table 表名 drop column 列名;
eg：alter table tablename drop column ID;

8、添加主键

alter table tabname add primary key(col)

9、删除主键

alter table tabname drop primary key(col)

10、创建索引

create [unique] index idxname on tabname(col….)

11、删除索引

drop index idxname
--注：索引是不可更改的，想更改必须删除重新建。

12、创建视图

create view viewname as select statement

13、删除视图

 drop view viewname

三、Oracle操作数据
1、数据查询

select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]

2、插入数据

insert into 表名 values(所有列的值);
例： insert into test values(1,'zhangsan',20);
insert into 表名(列) values(对应的值);
例： insert into test(id,name) values(2,'lisi');

3、更新数据

update 表 set 列=新的值 [where 条件] -->更新满足条件的记录
例：  update test set name='zhangsan2' where name='zhangsan'
update 表 set 列=新的值 -->更新所有的数据
例：  update test set age =20;

4、删除数据

delete from 表名 where 条件 -->删除满足条件的记录
例： delete from test where id = 1;
delete from test -->删除所有
commit; -->提交数据
rollback; -->回滚数据
--delete方式可以恢复删除的数据，但是提交了，就没办法了 delete删除的时候，会记录日志 -->删除会很慢很慢
truncate table 表名 （清空表数据速度很快）
drop table 表名（删除整个表）
--删除所有数据，包括表结构一并删除，不会记录日志，数据不能恢复-->删除很快

5、数据复制

--表数据复制
insert into table1 (select * from table2);
--复制表结构
create table table1 select * from table2 where 1>1;
--复制表结构和数据
create table table1 select * from table2;
--复制指定字段
create table table1 as select id, name from table2 where 1>1;

四、Oracle查询操作
1、SQL语言分为五大类：
DDL(数据定义语言 Data Definition Language) - Create、Alter、Drop 这些语句自动提交，无需用Commit提交。
DQL(数据查询语言) - Select 查询语句不存在提交问题。
DML(数据操纵语言 Data Manipulation Language) - Insert、Update、Delete 这些语句需要Commit才能提交。
DTL(事务控制语言) - Commit、Rollback 事务提交与回滚语句。
DCL(数据控制语言) - Grant、Revoke 授予权限与回收权限语句。
2、并发查询：

select /*+ parallel(8)*/ count(*) from table_name

3、查询当前用户：

select user from dual

4、查询所有用户：

select * from all_users

5、查询所有表：

select * from all_tables

6、查询当前用户下的所有表：

select * from user_tables;
select * from all_tables where owner=upper('用户名');
--all_tables 比 user_tables 多了一个 OWNER 字段,用来区分用户

7、查询表字段注释：

select comments from user_col_comments where TABLE_NAME='table_name'

8、限制返回记录条数 rownum ：

select * from acct_credit.vs_hive_account_credit where rownum<=10;

9、计数统计：

--去重统计
select count(distinct column_name) from table_name; 

--先统计再求和
select sum(a) from (
    select count(*) as a from CHENGDU.CFG_C_FTTHPONCUTOVER UNION ALL
    select count(*) as a from SUINING.CFG_C_FTTHPONCUTOVER 
)

10、case when:

select ename,
	case
	when sal<1000 then 'lower'
	when sal>1001 and sal<2000 then 'modest'
	when sal>2001 and sal<4000 then 'high'
	else 'too high'
	end
from emp;

11、创建序列号：

-- 创建序列  Student_stuId_Seq
create sequence Student_stuId_Seq
increment by 1	--自增步长
start with 1	--起始值
minvalue 1		--最小值		
maxvalue 999999999	--最大值

-- 更改序列  Student_stuId_Seq--
alter sequence Student_stuId_Seq
increment by 2	--自增步长设置为2
minvalue 1
maxvalue 999999999;

--获取序列自增ID 
select Student_stuId_Seq.Nextval as sequence from dual;

-- 删除序列 -- 
drop sequence Student_stuId_Seq;

--调用序列，插入Student数据
insert into Student(stuId,Stuname) values(Student_stuId_Seq.Nextval,'张三');
insert into Student(stuId,Stuname) values(Student_stuId_Seq.Nextval,'李四');

12、查看表分区：

--查询分区字段
SELECT * FROM all_PART_KEY_COLUMNS where name='table_name'
--查询分区
SELECT * FROM ALL_TAB_PARTITIONS where table_name=upper('table_name'); 
--查询每个分区的数据量
select t.partition_name,t.num_rows from all_tab_partitions t where table_name='table_name'

五、Oracle查询操作
1、查询结果排序

 acs：升序排序（默认）
 desc：降序排序
 dbms_random.value() / dbms_random.value：随机排序

–升序排序

select * from qy_test order by id;

–降序排序

select * from qy_test order by desc;

–随机排序

select * from qy_test order by dbms_random.value()：

2、where 和 having 的区别

where：where子句的作用是在分组之前过滤数据，条件中不能包含聚合函数
having：having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中可以包含聚合函数（每组的聚合函数结果可能不一样，所以有聚合函数的条件是针对每组内部的），使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

–where中不能使用聚合函数

select id from qy_test where id > 1 group by id;

–在having中可以使用聚合函数

select id from qy_test group by id having id>1 and id>max(age);

五、Oracle查询骚操作

2、按照A字段分组，取B字段的最大值，查询所有字段信息：

select p.*
  from (select A, max(B) as B from table_name group by A) t
  left join (select * from td_log_runjob) p
    on t.A = p.A
   and t.B = p.B

3、比较两张表的数据差异，minus: A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即在A中存在，而在B中不存在的记录。

–比较两张表的数据差异

select * from A minus select * from B

–比较两张表某个字段或某些字段的差异

select A.a,A.b from A minus select B.a,B.b from B

六、Oracle函数
1.字符函数
1、 字符与ASCII码的相互转化

chr(x)： 给出整数X，返回对应的ASCII码字符。

ASCII()： 给出ASCII码字符，返回chr对应的整数X。

--chr(x)： 给出整数X，返回对应的ASCII码字符。

  select chr(94) as str from dual;

--ASCII()： 给出ASCII码字符，返回chr对应的整数X。

  select ASCII('^') as str from dual;

2、连接两个字符串
CONCAT（string1,string2）：连接两个字符串
string1 || string2：连接两个字符串
–CONCAT（string1,string2）

   select concat('yyyyMM','dd HH') as time from dual;
     string1 || string2

  select 'yyyyMM'||'dd HH' as time from dual;
    结果:  yyyyMMdd HH

3、大小写转换：

LOWER()： 转成小写

UPPER()：转成大写

select lower('ASDASDAD') as str from dual;
select upper('asdasdasd') as str from dual;

4、在string1字符左边或右边粘贴数个string2字符，直到字符总字节数达到x字节。string2默认为空格。

select lpad(rpad('log',10,'*'),17,'*') as log from dual;
结果： *******log*******

select lpad(rpad('log',10),17) as log from dual;

5、截取字符串左右两边指定字符串，默认去除空格

 trim: 去除左右两边指定字符串字符串

 ltrim: 去除左边指定字符串

 rtrim: 去除右边指定字符串

select trim(' ABCD ') from dual;
select trim('A' from 'ABCD') from dual;
select ltrim('ABCD', 'A') from dual;
select rtrim('ABCD', 'D') from dual;

6、替换字符串中指定字符为新的字符串
replace(str,old,new)

select replace('ABCD', 'B', 'F') from dual;        --AFCD

7、从指定位置开始截取指定长度的字符串
substr(str,start, [length]): start 截取起始位置（索引从1开始），length不填默认截取到末尾

select substr('ABCDEF', 2, 3) from dual;        --BCD
select substr('ABCDEF', 2) from dual;        --BCDEF

2.数字函数
1、保留指定位数的小数
round(num, digits) ： 四舍五入
trunc(num, digits) ： 直接截取指定位数的小数

select round(3.1415926, 3) from dual;        --3.142
select round(3.1415926, 2) from dual;        --3.14
select trunc(3.1415926, 3) from dual;        --3.141
select trunc(3.1415926, 2) from dual;        --3.14

2、取绝对值 ABS(num)，获取 num 的绝对值

select ABS(-5) from dual;        --5

3、求余弦值，反余弦值

  余弦值： cos(num)

 反余弦值：acos(num)

select cos(1) from dual;        --0.54030230586814
select acos(1) from dual;        --0

4、求平方根，sqrt(num)

select sqrt(3) from dual;        --1.73205080756888

3.日期函数
1、某一个日期上，加上或减去指定的月数 正数为加 负数为减

select add_months(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 2) from dual;        --2019/9/9 7:25:31
select add_months(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), -3) from dual;        --2019/4/9 7:25:31

2、获取指定日期的当月的最后一天

select LAST_DAY(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss')) from dual;        --2019/7/31 7:25:31

3、日期的四舍五入
round(date, format) : date 为指定日期，format 为指定格式，默认为 format 为 ddd 四舍五入到某天
year：四舍五入到某年的1月1日
month：四舍五入到某月的1日
ddd：四舍五入到某天
day：四舍五入到某周的周日

select round(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'year') from dual;        --2020/1/1
select round(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'month') from dual;        --2019/7/1
select round(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'ddd') from dual;        --2019/7/9
select round(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'day') from dual;        --2019/7/7

4.转换函数
1、把日期和数字转换为制定格式的字符串
to_char(date, format) ：将日期的指定格式内容转换为字符串
format： ‘yyyy"年"MM"月"dd"日"’

select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyy"年"MM"月"dd"日"') from dual;        
--2019年07月09日
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yy') from dual;        --19
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyy') from dual;        --2019
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyyMM') from dual;        --201907
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyyMMdd') from dual;        --20190709
select to_char(to_date('20190709 7:25:31', 'yyyyMMdd hh24:mi:ss'), 'yyyyMMdd HH') from dual;        --20190709 07

to_char(number, format) ：将数字转换为字符串 (当保留小数点时时四舍五入)

select to_char(123456789.123, '999,999,999.99') from dual;        --123,456,789.12
select to_char(123456789.125, '999,999,999.99') from dual;        --123,456,789.13

2、把字符串转换为数字类型
to_number(str, format)： 将字符串转换为数字类型

select to_number('-$12,345.67','$99,999.99')"num" from dual;

3、把字符串转换为日期类型
to_date(str,[,format]) ： 将str转换为 format格式的日期

select to_date('2019-07-09 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual;        --2019/7/9 13:23:44
select to_date('20190709','yyyyMMdd') from dual;        --2019/7/9
select to_date('201907','yyyyMM') from dual;        --2019/7/1
select to_date('2019','yyyy') from dual;        --2019/当前月份/1

5.空值处理函数
1、空值判断，返回不通结果
nvl(str, value1) : 如果str为空，返回value1，否则返回str
nvl2(str, value1， value2) : 如果str为空，返回value2，否则返回value1

select nvl('', 'null') from  dual;        --null
select nvl('s', 'null') from  dual;        --s
 
select nvl2('', 'not null', 'null') from dual;        --null
select nvl2('s', 'not null', 'null') from dual;        --not null

6.聚合函数
1、聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。

.