oracle常用语句CRUD

没鞋的脚要努力奔跑

已于 2023-07-26 17:37:43 修改

阅读量424

点赞数

分类专栏： sql管理文章标签： oracle 数据库

于 2022-09-27 17:57:10 首次发布

本文链接：https://blog.csdn.net/qq_20377975/article/details/127075725

版权

sql管理专栏收录该内容

6 篇文章 0 订阅

订阅专栏

oracle常用语句

通用函数:
条件表达式 if - then - else
日期 (当前时间sysdate)
- trunc (date,dd )函数
alter table 语句
create sequence语句定义序列

1.sys_guid()方法生成过32位id

select sys_guid() from dual;

如果乱码，使用rawtohex()：将raw串转换为十六进制；

select rawtohex(sys_guid()) from dual;

2. Oracle 创建新用户

-----创建用户
create user 用户名 identified by 密码；
-----赋权限
grant dba,resource,connect to 用户名;

3. substr 函数：截取字符串

SELECT SUBSTR('Hello SQL!', 3, 6) FROM dual  --从第3个字符开始，截取6个字符。返回'llo SQ'

4. oracle中if…else的方法：

	第一种：写表达式
		SELECT
			CASE 
			 	WHEN 2>1 THEN '真'
				ELSE '假'
			END AS WE
		FROM DUAL
		
	第二种：固定
		SELECT
			CASE 1
				WHEN 1 THEN '字段的值是1'
				WHEN 2 THEN '字段的值是2'
				ELSE '字段的值3'
			END AS WE
		FROM
			DUAL
			
	第三种：DECODE（需要判断的值，判断1的结果条件（if），符合判断1的结果，判断2的结果条件(else if)，符合判断2的结果，(else)）
		SELECT
			DECODE(2, 1, '男', 2, '女', '未知')
		FROM
			DUAL

Oracle 中 TO_CHAR用法

（1）用作日期转换：

to_char(date,'格式');

select to_date('2005-01-01 ','yyyy-MM-dd') from dual;
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
（2）处理数字：

to_char(number,'格式');

select to_char(88877) from dual;
select to_char(1234567890,'099999999999999')  from dual;
select to_char(12345678,'999,999,999,999')  from dual;
select to_char(123456,'99.999')  from dual;
select to_char(1234567890,'999,999,999,999.9999')  from dual;
（3）to_char(salary,'$99,99');

 select TO_CHAR(123,'$99,999.9') from dual;
（4）用于进制转换：将10进制转换为16进制；

select to_char(4567,'xxxx') from dual;
select to_char(123,'xxx') from dual;
 to_char 例子
①其9代表：如果存在数字则显示数字，不存在则显示空格
②其0代表：如果存在数字则显示数字，不存在则显示0，即占位符。
③其FM代表：删除如果是因9带来的空格，则删除之
to_char(now(),'Day, HH12:MI:SS') 'Tuesday , 05:39:18'
to_char(now(),'FMDay, HH12:MI:SS') 'Tuesday, 05:39:18'
to_char(-0.1,'99.99') ' -.10'
to_char(-0.1,'FM9.99') '-.1'
to_char(0.1,'0.9') ' 0.1'
to_char(12,'9990999.9') ' 0012.0'
to_char(12,'FM9990999.9') '0012'
to_char(485,'999') ' 485'
to_char(-485,'999') '-485'
to_char(485,'9 9 9') ' 4 8 5'
to_char(1485,'9,999') ' 1,485'
to_char(1485,'9G999') ' 1 485'
to_char(148.5,'999.999') ' 148.500'
to_char(148.5,'999D999') ' 148,500'
to_char(3148.5,'9G999D999') ' 3 148,500'
to_char(-485,'999S') '485-'
to_char(-485,'999MI') '485-'
to_char(485,'999MI') '485'
to_char(485,'PL999') '+485'
to_char(485,'SG999') '+485'
to_char(-485,'SG999') '-485'
to_char(-485,'9SG99') '4-85'
to_char(-485,'999PR') '<485>'
to_char(485,'L999') 'DM 485
to_char(485,'RN') ' CDLXXXV'
to_char(485,'FMRN') 'CDLXXXV'
to_char(5.2,'FMRN') V
to_char(482,'999th') ' 482nd'
to_char(485, '"Good number:"999') 'Good number: 485'
to_char(485.8,'"Pre-decimal:"999" Post-decimal:" .999') 'Pre-decimal: 485 Post-decimal: .800'
to_char(12,'99V999') ' 12000'
to_char(12.4,'99V999') ' 12400'
to_char(12.45, '99V9') ' 125'

oracle 实现ID自增

CREATE TABLE testTable1
(
ID INT NOT NULL,
NAME VARCHAR2(4000) NOT NULL,
PRIMARY KEY(ID)
)
TABLESPACE MYDB;

--创建自增ID，名称为：表名_字段名_SEQ
CREATE SEQUENCE testTable1_ID_SEQ MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH 1 NOCACHE;

-- 为Insert操作创建触发器，无需在SQL语句里写NEXTVAL，名称为表名_INS_TRG
CREATE OR REPLACE TRIGGER testTable1_INS_TRG BEFORE INSERT ON testTable1 FOR EACH ROW WHEN(NEW.ID IS NULL)
BEGIN
SELECT testTable1_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

备份表数据

create table emp as select * from scott.emp

还原表数据

insert into emp select * from scott.emp

ORACLE 判断序列是否存在,如果存在就删除

declare
V_NUM number;

BEGIN
 ----多次删除时，每次都将v_num设置成为0
   V_NUM := 0;
   ----判断序列 seq_name_1 是否存在（区分大小写）
   select count(0) into V_NUM from user_sequences where sequence_name = 'SEQ_BUSINESS_PROCESS_INDEX_ID';
   ----如果存在立即删除
   if V_NUM > 0 then
   execute immediate 'DROP SEQUENCE  SEQ_BUSINESS_PROCESS_INDEX_ID';
   end if;
END;

Oracle 在 ORDER BY 子句中加入主键或唯一键

INSERT INTO STUDENT (STUDENT_ID,NAME,BIRTHDAY) VALUES (1,'张三',DATE '1991-01-01');
INSERT INTO STUDENT (STUDENT_ID,NAME,BIRTHDAY) VALUES (2,'李四',DATE '1991-01-01');
假设让你查找一下年龄最小的学生，我们很自然的会写出如下SQL：
SELECT * FROM (
SELECT * FROM STUDENT ORDER BY BIRTHDAY
) WHERE ROWNUM=1
上面的语句并不总是正确的，因为张三和李四的年龄是相同的，我们应该把学号(STUDENT_ID)添加在ORDER BY 子句中，正确的SQL 如下：
SELECT * FROM (
SELECT * FROM STUDENT ORDER BY BIRTHDAY, STUDENT_ID
) WHERE ROWNUM=1

Oracle 将null值转化为其他值

SELECT CASE WHEN BONUS IS NULL THEN 0.0 ELSE BONUS END FROM EMPLOYEE;
SELECT COALESCE(BONUS, 0.0) FROM EMPLOYEE;
SELECT NVL(BONUS, 0.0) FROM EMPLOYEE;
假设你要查找总工资(基本工资+奖金)大于等于3000元的员工，我们很自然的会写出下面的语句：
SELECT * FROM EMPLOYEE WHERE SALARY + BONUS >= 3000.0;
这条语句并不是永远正确，当SALARY或BONUS有一个值是null的时候，我们很可能会漏掉部分数据
SELECT * FROM EMPLOYEE WHERE NVL(SALARY, 0.0) + NVL(BONUS, 0.0) >= 3000.0;

更简单的办法
-- 如果条件是假或未知，LNNVL 函数返回真
SELECT * FROM EMPLOYEE WHERE LNNVL(SALARY + BONUS < 3000.0);

由于null，我们的 SQL 语句很有可能出现意想不到的结果

INSERT INTO test values (1, '张三');
INSERT INTO test values (2, '李四');
 
SELECT * FROM test WHERE ID NOT IN (1, NULL);
结果却是什么也查不出来

也许你认为不会有人那么傻，在 NOT IN 里写个 NULL，但是如果 NOT IN 里是一个子查询，而子查询的结果集里有 NULL 值呢？

Oracle 统计行数

现在让你统计以下这个表有多少条数据，怎么办？很简单，我们有下面三种方法。

-- 方法1
SELECT COUNT(*) FROM STUDENT;
 
-- 方法2
SELECT COUNT(1) FROM STUDENT;
 
-- 方法3
SELECT COUNT(BIRTHDAY) FROM STUDENT;
遗憾的是方法3统计出的数据并不总是正确的，那是因为 COUNT 函数会忽略 NULL 值。 所以，千万不要统计可以为 NULL 的列。

Oracle 聚合字符串

INSERT INTO EMPLOYEE VALUES (1, '张三');
INSERT INTO EMAIL VALUES (1, 'san.zhang@163.com');
INSERT INTO EMAIL VALUES (1, 'san.zhang@qq.com');


现在让你写一个 SQL 返回下面的结果集，怎么办？
EMPLOYEE_ID   NAME  EMAIL
1             张三  san.zhang@163.com;san.zhang@qq.com
2             李四  li.si@163.com;li.si@qq.com
————————————————
Oracle 提供了一个叫做 LISTAGG 的函数，如下。
SELECT
  E.EMPLOYEE_ID,
  E.NAME,
  LISTAGG(A.EMAIL, ';') WITHIN GROUP (ORDER BY A.EMAIL) EMAIL
FROM
  EMPLOYEE E,
  EMAIL A
WHERE
  E.EMPLOYEE_ID = A.EMPLOYEE_ID
GROUP BY
  E.EMPLOYEE_ID,
  E.NAME;

Oracle ROLLUP 和 CUBE

INSERT INTO employee VALUES ('张三','男','中国','市场部',4000);  
INSERT INTO employee VALUES ('李四','男','中国','市场部',5000);  
INSERT INTO employee VALUES ('王五','女','美国','市场部',3000);    
INSERT INTO employee VALUES ('赵红','男','中国','技术部',2000);  
INSERT INTO employee VALUES ('李白','女','中国','技术部',5000);    
INSERT INTO employee VALUES ('王蓝','男','美国','技术部',4000);
————————————————
通常我们是在生成报表时算小计或总计的，其实通过 SQL 就可以实现。

SELECT 
  country, 
  department, 
  round(avg(salary), 2) AVG
FROM 
  employee 
GROUP BY ROLLUP(country, department);
————————————————
结果如下：
country department AVG
中国	市场部	4500
中国	技术部	3500
中国	null	4000
美国	市场部	3000
美国	技术部	4000
美国	null	3500
null	null	3833.33

上面的 null 是不是让人看着很不爽，要是能返回有意义的值就更好了，为此 Oralce 提供几个函数实现这个功能。

SELECT 
  DECODE(GROUPING(country), 1, '总计', country) AS country,
  DECODE(GROUPING(department), 1, '小计', department) AS department,
  round(avg(salary), 2) AVG
FROM 
  employee 
GROUP BY ROLLUP(country, department);


结果如下：
country department AVG
中国	市场部	4500
中国	技术部	3500
中国	小计	4000
美国	市场部	3000
美国	技术部	4000
美国	小计	3500
总计	小计	3833.33

还有个 CUBE 关键字，它比 ROLLUP 语句返回更多的内容，以下是将上面语句的 ROLLUP 替换为 CUBE 后得到的结果：

SELECT 
  DECODE(GROUPING(country), 1, '总计', country) AS country,
  DECODE(GROUPING(department), 1, '小计', department) AS department,
  round(avg(salary), 2) AVG
FROM 
  employee 
GROUP BY CUBE(country, department);


结果如下：
country department AVG
总计	小计	3833.33
总计	市场部	4000
总计	技术部	3666.67
中国	小计	4000
中国	市场部	4500
中国	技术部	3500
美国	小计	3500
美国	市场部	3000
美国	技术部	4000

通用函数:

nvl(a,b): 当a为null时，展示b
nvl2(a,b,c): 当a不为null返回b,为null 返回c
nullif(a,b): 当a等于b时返回null,不等返回a
coalesce(a,b,c): 当a有值时返回a,当a为null时,返回b,当a、b为null时,返回c

条件表达式 if - then - else

case: case t_code when code then name end: 当t_code 等于 code 返回 name
decode: decode(t_name,name,code): 当t_name 等于 name 返回 code

日期 (当前时间sysdate)

在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回的日期之间相差的天数。(日期尽量不做加运算,无意义)
可以用数字除24,向日期中加上或减去天数
两个日期相差的月数: months_between(create_time,update_time)
向指定日期中加上若干月数: add_months(create_time,2)
指定日期的下一个星期对应的日期: next_day(sysdate,'星期三')
本月最后一天: last_day(sysdate)
日期四舍五入: round()
日期截取: trunc()

trunc (date,dd )函数

确定一年中的某月的某一天就要用trunc(date,‘‘dd’’).
通俗的说吧,format
为年时,精确到-----年
为月时,精确到------年,月(不管哪年,只要是相同的月和哪天)
为日时,精确到------年,月,日(不管哪年的哪月,只关心是哪天)
select trunc(sysdate ,‘dd’) from dual ; – 2007-9-19
select trunc(sysdate ,‘yyyy’) from dual ; --2007-1-1
select trunc(sysdate ,‘mm’) from dual ; --2007-9-1

alter table 语句

- 追加新的列：alter table emp1 add(email varchar2(20));
- 修改现有的列：alter table emp1 modify(id number(10));
- 为新追加的列定义默认值：alter table emp1 modify(salary number(10,2) default 200);
- 删除一个列: alter table emp1 drop column email;
- 重命名表的一个列名: alter table emp1 rename column salary to sal;


- 使用alter table 添加约束的语法 语句
- alter table emp drop constraint emp_name_;
- alter table emp add constraint emp_name_uk unique;

truncate table emp1;清空表数据表结构还在释放存储空间

rename emp2 to emp5; 修改表名称不可回滚

create sequence语句定义序列

create sequence sequence
increment by n  --每次增长的数值
start with n  --从哪个值开始
maxvalue n 
minvalue n
cycale/nocycle  --是否需要循环
cache n/nocache  -- 是否缓存登录

查询序列值

> select 序列名.nextval from dual;

查询当前的序列值

> select 序列名.currval from dual;

修改序列

需改序列的增量，最大值，最小值，循环选项，或是否装入内存

alter sequence depe_deptid_seq
increment by 20
maxvalue 9999999
nocache
nocycle;

注意事项

1.必须是序列的拥有着或对序列有later 权限
2.只有将来的序列值会被改变
3.改变序列的初始值只能通过删除序列之后重建序列的方法实现

oracle 中的（+）是一种特殊的用法，（+）表示外连接，并且总是放在非主表的一方。

例如

左外连接：select A.a,B.a from A LEFT JOIN B ON A.b=B.b;

等价于 select A.a,B.a from A,B where A.b = B.b(+);

再举个例子，这次是右外连接：select A.a,B.a from A RIGHT JOIN B ON A.b=B.b;

等价于 select A.a,B.a from A,B where A.b (+) = B.b;

oracle limit用法

用法

--  查询前10行记录
SELECT ROWNUM, t1.* FROM XXX t1 WHERE ROWNUM <= 10;

--  查询从11行到20行的记录
SELECT * FROM (SELECT ROWNUM NO, t1.* FROM XXX t1) t2 WHERE t2.NO > 10 AND t2.NO < 21;

常用记录

创建新表： create table table1 (col1 datatype, col2 datatype);
创建新视图 create view view1 as select col1, col2 from table1;
删除表： drop table table1;
删除视图： drop view view1;

定义一个名为myvar的6位的数字变量类型：

DECLARE myvar NUMBER(6);

可以使用SET语句为变量赋值

SET myvar = 123456;

需要使用该变量时，可以使用“&”符号引用

SELECT * FROM mytable WHERE id = &myvar;

在Oracle中，定义变量的方法为

DECLARE 
	v_name VARCHAR2(10) :='myname';
	v_age NUMBER(2) := 20;
BEGIN
	NULL;
END;

判断是否包含某个字段

使用like
List item

. 使用contains函数

select * from user where contains(hobby,  'yanggb');
--第一个参数为要检索的字段，第二个参数为匹配的字符串。

上面语句表中的hobby列如果没有建立索引，那么就会报错，局限性比较大。

使用instr函数

--第一个参数为要从中检索的字符串，第二个参数为要检索的字符串
--如果检索到了就返回首次检索到的位置（整数），检索不到则返回0。
select * from user where instr(hobby, 'yanggb') > 0

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中

--创建 Customers 的备份复件：

SELECT *
INTO CustomersBackup2013
FROM Customers;