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;
- 学习网站
6.oracle的DQL、DML、DDL、DCL的概念和区别
8.oracle 查看回收站空间,ORACLE 回收站当前状态查询整理
- 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 回滚