Oracle常用语句以及常用函数总结

辉哥的博客

已于 2022-05-11 09:31:50 修改

阅读量575

点赞数 1

分类专栏： Oracle 文章标签： etl oracle 数据库

于 2022-03-05 18:12:15 首次发布

本文链接：https://blog.csdn.net/qq_43278973/article/details/123298713

版权

Oracle 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

Oracle常用语句以及常用函数总结

常用语句

Oracle基础
数据定义语言DDL,主要的命令有 CREATE 、ALTER、DROP、TRUNCATE 用来定义或者改变表的结果,数据类型,表之间的连接和约束等

--1）Oracle 创建test1表
create table test1(
       TESTID number(2) primary key,
       NAME1 varchar(10) not null, --varchar存放固定长度的字符串，最大长度是2000;Oracle 11g数据库会把varchar自动转varchar2;varchar2每个汉字是3个字符;
       name2 VARCHAR2(10) not null --varchar2是存放可变长度的字符串，最大长度是4000;varchar2每个汉字是3个字符;  
       );                 
--2）Oracle 通过查询test1表,创建test1_view视图
create view test1_view as select * from test1;
--3）Oracle 通过查询test1表,创建test2表
CREATE TABLE test2 AS SELECT * FROM test1;  
--4）通过Witn语句创建表
CREATE TABLE db2_increment2 AS 
WITH db2_increment2 as
(SELECT * FROM palan.db2_increment)
SELECT  * FROM db2_increment2
--5）Oracle 创建索引
create index name1_index on test1(name1);
--6）Oracle 创建组合索引
create index name1_name2_index on test1(name1,name2);


--1）添加一列
alter table test1 ADD name3 varchar(255);
--2）修改列名
alter table TEST1 rename column name3 TO RAW_name3;   
--3）修改表名
alter table old_table_name rename to new_table_name;   
--4）修改列的类型
alter table TEST1 modify name1 VARCHAR2(255);
alter table TEST1 modify name2 VARCHAR2(255); 
--5）删除数据库一列     
alter table TEST1 drop column RAW_name3  


--1）删除索引
drop index name1_index;
drop index name1_name2_index;
--2）删除表格
DROP TABLE test1;
DROP VIEW test1_view;
DROP TABLE test2;
--3）删除数据库
--drop database database_name; (注意：这个谨慎操作，操作不当就把数据库删了，除非这个就是删除的数据库)

--1）删除表中的数据
truncate table table_name;

数据操作语言DML主要有三种形式：insert、update、delete 他用来对数据库里的数据进行操作的语言

--1）按顺序插入数据
insert into test1 values(1,'张三','李四');
COMMIT;   
--2）按指定顺序插入数据
insert into test1(NAME1,NAME2,TESTID）values('张老三','李四四',2);
COMMIT;
--3）通过 insert into 命令可以把一个select查询结果一次性插入到一张表中(注：需要保持字段顺序一致，不建议使用*号，最好使用字段名)。
insert into test2 select * from test1 WHERE testid = 1;
COMMIT;
--4）通过查询灵活插入数据 (建议使用下面这种方式插入数据)。
insert into test2 (testid,name1,name2) SELECT testid,name1,name2 FROM test1 where testid = 2;
COMMIT;

--1）更新数据
update test1
    set name1 = '张三'
where testid = 2;
COMMIT;
--2）更新进阶
update test1
    set name1 =
            case
                when testid = 3 AND name1 = '张老三' then '张三'
              else name1
            end;
COMMIT;            

--1）删除
DELETE FROM test1 WHERE testid = 3;
COMMIT;
DELETE FROM test1 WHERE testid = '2'; --testid字段是数值类型，等于字符床2删除数据也是可以删的掉的，可能是数据库自己在内部转类型了
COMMIT;
--2）也可以通过子查询删除数据
DELETE FROM test1 WHERE testid IN(SELECT testid FROM test1 WHERE testid = 1);            
COMMIT;

数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE,他用来对数据库里的数据进行查询显示的语言。

SELECT * FROM test1 WHERE testid IS NOT NULL;
SELECT * FROM test1_view;
SELECT * FROM test2;

DCL（Data Control Language，数据控制语言）：ALTER PASSWORD、GRANTREVOKE、CREATE SYNONYM 用于定义数据库用户的权限。


--1)创建用户test2，密码也是test2（记得最有以分；号结束）：
create user test2 identified by test2;
--2）给test2授权：create session;（允许用户登陆Oracle）：
grant create session to test2;
--3）给test2分配创建表的权限；
grant create table to test2;
--4）给test2分配表空间的使用权限；
grant unlimited tablespace to test2;

学习网站

1.oracle数据库创建表

2.varchar和varchar2的区别

3.Oracle创建视图(View)

4.Oracle插入（insert into）

5.oracle插入数据需要commit提交

6.oracle的DQL、DML、DDL、DCL的概念和区别

7.Oracle 11g 新建数据库

8.oracle 查看回收站空间,ORACLE 回收站当前状态查询整理

9.Oracle中的DDL,DML,DCL总结

10.ORACLE中的DELETE和TRUNCATE的关系

Oracle 查看数据库版本

SELECT * FROM v$version;

Oracle 查看数据库名称

select name from v$database; --ORCL

如果返回值为“on”表明回收站是启动的，“off”表明是关闭的。

SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

常用函数

日期函数

--to_char转字符串格式
select to_char(sysdate,'yyyy-mm-dd') from dual;
--to_date转日期格式
select to_date('2022-01-01 22:32:10','yyyy-mm-dd hh24:mi:ss') from dual;
--extract获取年份（用于截取年、月、日、时、分、秒）{ year | month | day | hour | minute | second }
select extract(year from sysdate) as year from dual;

条件判断函数

--decode相当于case when函数的简写
select decode(column_name,'1.0','1','or else is null') from dual;
--case when 语句，在语句里面也可以写正则
case
    when xx then xx
  else null
end as 'xx';

正则函数

--regexp_like
select * from emp where regexp_like(name,'张三|李四');
--regexp_replace
select regexp_replace('张.?','张老三') from emp;
--regexp_substr
select regexp_substr('abc 123456 cd','12' ) regexp_substr from dual;
--result:12
--regexp_instr
select regexp_instr('12.345', '\.') regexp_instr from dual;
--result:3
--regexp_count
select regexp_count('welcome','\w')   from dual;
--result: 7
--https://blog.csdn.net/qq_43278973/article/details/118603509?spm=1001.2014.3001.5501

模糊匹配函数

--like
select * from test where name like '%张%';

nvl函数

--nvl 如果第一个字段为空，取第二个字段
select nvl(name,name2) from test;
--nvl2(v1, v2, v3) 定义：如果v1为空，返回v3； 不为空，返回v2
select nvl2(v1,v2,v3) from test;
--例子
select nvl2(1, sysdate-(sysdate-1/24/60), sysdate) from dual;
select nvl2(null,sysdate-(sysdate-1/24/60), sysdate) from dual;
-- https://www.cnblogs.com/hyang0/p/10633820.html

分组函数

--group by having
--按名字分组,过滤出年龄＞10且年龄≤20;
select name from test group by name having age >10 and age <=20;

--group by order desc
--按名字分组,按年龄排序升序（默认 升序 asc;降序 desc）;
select name from test group by name order by age asc;

聚合函数

--max
--查询test表中最大的年龄
select max(age) from test;

--min
--查询test表中最小的年龄
select min(age) from test;

--avg
--查询test表,年龄平均值
select avg(age) from test;

--sum
--按组织编码、患者类型、患者id、就诊id分组,算总费用（查看患者一次就诊的总费用）（price：单价）
select org_code,pat_type,pid,vid,sum(price) from cost group by org_code,pat_type,pid,vid;

--count
--统计test表的数据量（count(1)、countr(*)是一样的功能）
select count(1),countr(*) from test;

--统计test表中name字段不为空的数据量
select count(name) from test;

--统计test表中name字段不为空的比例
select count(name) / count(1) *100||'%' from test;

--按照test表中的sex分组，统计去重男女人数（一个人有可能来多次，所以需要去重）
select sex,count(distinct pid) from test group by sex;

限制行数函数

--rownum
--查询test表，条件：行数在>=1且<=10000(闭区间[1,10000]),就是查看前10000条数据
select * from test where rownum betweend 1 and 10000;
--如何理解左闭右开
--https://blog.csdn.net/qq_44309181/article/details/111311557

in、not in、exists、not exists 函数

--in
select * from test where name in ('张三','李四','王五')
--not in
select * from test where name not in ('张三','李四','王五')

--not exists、exists用法
--如果有平均工资不小于1500的部门信息则查询所有部门信息(使用not exists)
select * from dept where not exists (select deptno from emp where deptno = emp.deptno group by deptno having avg(sal) < 1500) and exists (select * from emp where emp.deptno = deptno);

--oracle中的exists 和not exists 用法详解
--https://www.cnblogs.com/liuqiyun/p/6549828.html

并集、交集、差集函数


--并集：将查询出的两个结果合并成一个结果集。
-- union 去重，合并后的结果都是唯一
-- 注：合表的时候列的顺序必须保持一致且数据类型一致（不建议使用*，最好使用字段名）（合表的时候默认使用第一张表的名称命名）

select classid from student
union
select classid from class;

-- union all 不去重，合并后的结果有可能出现重复的
select classid from student
union all
select classid from class;    -- Oracle 和 MySQL都支持这种方法查询

--交集：返回两个查询结果集中相同部分的结果；

--  intersect 交集，取相同的记录
select classid form student
intersect
select classid from classid;   -- Oracle数据库中的查询方法

select s.*
from (select classid from student) s
join (select classid from class) c
on s.classid=c.classid;        -- MySQL数据库中的查询方法

--差集：返回第一个查询结果中与第二个查询结果不相同的那部分记录。 查询结果1 - 查询结果2

--  minus 差集，主表  减去  从表中与主表相同的记录
select classid form student
minus
select classid from classid  -- Oracle 数据库中的查询方法

select classid
from student
where classid not in(select classid from class);  -- Oracle 和 MySQL 这种查询方法

ceil、trunc、floor 函数

--ceil 向上取整
select ceil(9.5) from dual;

select * from test;


floor(n)取小于等于数值n的最大整数
如下例子
SQL> select ceil(9.5) from dual;

 CEIL(9.5)
----------
        10

SQL> select floor(9.5) from dual;

FLOOR(9.5)

创建临时表函数

with
    temp as (select * from dual),
    temp2 as (select * from temp)
select * from temp2;

行转列函数

--PIVOT
WITH TEMP AS(
SELECT DATE1,DIAG_NAME||'---'||PID AS DIAG_NAME_PID,ROW_NUMBER() OVER(PARTITION BY DATE1
ORDER BY TO_NUMBER(PID) DESC) AS RN FROM PALAN.IN_DIAG_TOP50
)
SELECT * FROM (SELECT DATE1,DIAG_NAME_PID,RN FROM TEMP)PIVOT(MAX(DIAG_NAME_PID) FOR RN IN (

'1' AS "1",
'2' AS "2",
'3' AS "3",
-- 代码太长，此处省略(4-49)
'50' AS "50"
));

--https://blog.csdn.net/qq_43278973/article/details/120186020

oracle常用函数补充
10.ceil、trunc
11.round
12.+、-、*、/
13.lower、upeer
14.trim、rtrim、ltrim
15.replace（）
16.instr
17.substr
18.count（name）、count（1）、count（*）
19.betweend and >= and <= 闭区间
20.if
21.concat
22.字符串拼接 ｜｜
23.length、lengthb
24.is null、is not null、<>= 相当于 ！=、=
25. and、or、括号逻辑清晰
26. join、left join、right join、ineer join、full join
27. where、select、from
28. not like
29. as
30. cast（name as varchar（255））
31. to_number
32. row number 获取出现的次数，查看之前写的博客
33. 建表、ddl、视图
34. drop 表应该是先放在回收站里，等表空间不足时会自己删除表、truncate 可以释放内存
35. oracle 分区 partition
36. commit
37. rollback 回滚

辉哥的博客

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle常用语句以及常用函数总结

Oracle常用语句以及常用函数总结常用语句Oracle基础数据定义语言DDL,主要的命令有 CREATE 、ALTER、DROP、TRUNCATE 用来定义或者改变表的结果,数据类型,表之间的连接和约束等--1）Oracle 创建test1表create table test1( TESTID number(2) primary key, NAME1 varchar(10) not null, --varchar存放固定长度的字符串，最大长度是2000
复制链接

扫一扫