首页 运维知识 关于Oracle–sql

关于Oracle–sql

Oracle数据库和schema关系: 一个数据库包含多个schema,schema是数据库对象(table,view,sequence…)的集合,schema一般默认和用户名…

Oracle数据库和schema关系:

一个数据库包含多个schema,schema是数据库对象(table,view,sequence...)的集合,schema一般默认和用户名相同。schema无法单独创建,
在创建用户的时候数据库默认新增一个同名schema。A schema下的表可以授权给B schema。

主键:

oracle数据库表没有自增主键,mysql数据库有,oracle表主键自增可以通过序列或者触发器实现。

序列:

--删除序列
drop sequence seq_name;
--创建序列,从1开始,步长为1
create sequence seq_name increment by 1 start with 1;
--使用,每次调用seq_name.nextval,序列值加1
select seq_name.nextval from dual;
insert into table_name(id,name,age) values(seq_name.nextval,'Hello',20);

触发器:

--创建一个触发器         
CREATE TRIGGER ContestDB_trigger  
    BEFORE INSERT ON ContestDB  
    FOR EACH ROW  
    WHEN (new.TID is null) --只有在tid为空时,启动该触发器生成tid号  
  begin  
    select ContestDB_sequence.nextval into :new.TID from sys.dual;  
  end;  
   
insert into ContestDB(TEAMNUM,MARKNUM) values('A20007013','A002');   
insert into ContestDB(TEAMNUM,MARKNUM) values('A20007014','A003');

批量插入(借助中间表)–同一事务:

--包含序列
insert into table_name (id,name,age,create_time)
select seq_name.nextval,t.* from(
    select 'John',20,sysdate from dual
    union all
    select 'Anna',18,sysdate from dual
)t

insert all into 是指把同一批数据插入到不同的表中,insert into 是插入同一个表里。

--无条件插入
INSERT ALL
   INTO t1(object_id, object_name)
   INTO t2(object_id, object_name)
;
--有条件插入
--insert first:对于每一行数据,只插入到 第一个when 条件成立的表,不继续检查其他条件。
INSERT FIRST 
  WHEN s_id <= 2 THEN 
     INTO stu1 (s_id, s_xm) 
  WHEN s_id >= 2 THEN 
     INTO stu2 (s_id, s_xm)
;
--对于每一行数据,对 每一个when 条件都进行检查,如果满足条件就执行插入操作。
INSERT ALL
  WHEN s_id <= 2 THEN 
     INTO stu1 (s_id, s_xm) 
  WHEN s_id >= 2 THEN 
     INTO stu2 (s_id, s_xm)
;

批量更新:

--把某表类型为a的记录,name字段更新为大写
UPDATE table_name t1 set t1.name=(SELECT upper(name) FROM table_name t2 WHERE t1.ID=t2.ID) WHERE t1.type='a'

Start With(树查询):

SELECT ... FROM  table_name 
WHERE              + 条件3
START WITH         + 条件1
CONNECT BY PRIOR   + 条件2

--查询自身和子集
Select * From table_name
WHERE id = '00001' or pid ='00001'
Start With id = '00001'
Connect By Prior id = pid

union all 和 union区别:

union all不会去掉重复数据,union会去掉重复数据。

exists和in区别:

--前置条件:A表为大表,B表是小表。
方法一:
select * from tableA a,tableB b where a.aa = b.aa;//效率最差,数据量少差别不大,书写简单
方法二:使用in,小表作为主表
select * from tableB b where b.aa in(select a.aa from tableA a);
方法三:使用in,大表作为主表,效率低于方法二
select * from tableA b where a.aa in(select b.aa from tableB b);
方法四:使用exists,大表作为主表
select * from tableA a where EXISTS (SELECT * FROM tableB b WHERE b.aa = a.aa)

not exists和not in区别:

--not exists效率永远高于not in,因为not in全表扫描,而not exists子查询使用索引了。
Select name from employee where name not in (select name from student);
Select name from employee where not exists (select name from student);

分页查询:

select a.* from(select *,rownum  r from table_name)
where r>20 and r<50

死锁判断和解决方法:

1)执行以下语句:
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
如果有输出的结果,则说明有死锁,且能看到死锁的机器是哪一台。字段说明:
Username:死锁语句所用的数据库用户;
Lockwait:死锁的状态,如果有内容表示被死锁。
Status: 状态,active表示被死锁
Machine: 死锁语句所在的机器。
Program: 产生死锁的语句主要来自哪个应用程序。

2)执行以下语句,可以查看到被死锁的语句:
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

3)查找死锁的进程:
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

4)kill掉这个死锁的进程:
alter system kill session ‘session_id,serial#’; (引号内列名换为对应id)

Oracle插入/更新CLOB字段报ORA-01704(字符串文字太长):原因是sql在执行之前会把所有字符类型的数据转换成VARCHAR2类型,而VARCHAR2类型的最大长度为4000,所以当字符串超过这个长度就会转换失败。

declare
    content clob;
begin
  content := '长字符串';
  insert into article(id,title,content) values(1,'标题',content );
  update article set content = content  where id = 1;
end;

条件索引:同一个父节点下不允许出现相同的有效节点

create unique index MYDB.UNIQUE_NAME
  on MYDB.T_GOODS (CASE  WHEN ("STATUS"='A' AND "END_TS"=TO_DATE(' 9999-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 
                              THEN UPPER("NAME")||'_'||TO_CHAR(NVL("PARENT_ID",0)) ELSE NULL END)

Merge Into:注意:USING 后面的表或者子查询语句必须有数据,否则insert不生效

MERGE INTO schema. table alias
USING { schema. table | views | query} alias
ON {(condition) }
WHEN MATCHED THEN
  UPDATE SET {clause}
WHEN NOT MATCHED THEN
  INSERT VALUES {clause};
免责声明:文章内容不代表本站立场,本站不对其内容的真实性、完整性、准确性给予任何担保、暗示和承诺,仅供读者参考,文章版权归原作者所有。如本文内容影响到您的合法权益(内容、图片等),请及时联系本站,我们会及时删除处理。

作者: 小小编

为您推荐

dell R710 更换raid卡后,raid卡信息没有了,处理方案

dell R710 更换raid卡后,raid卡信息没有了,处理方案

1.将一台服务器(A)的硬盘依次拔出,按相同顺序插入另一台同样配置的服务器(B) 2.启动服务器(B) 3.按提示键盘按...
PL SQL Developer 13连接Oracle数据库并导出数据详细操作教程方法

PL SQL Developer 13连接Oracle数据库并导出数据详细操作教程方法

下载 并安装 PL SQL Developer 13,默认支持中文语言 ========================...
关于一条sql语句在mysql中是如何执行的

关于一条sql语句在mysql中是如何执行的

最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在my...
关于sql注入姿势总结(mysql)

关于sql注入姿势总结(mysql)

前言 学习了sql注入很长时间,但是仍然没有系统的了解过,这次总结一波,用作学习的资料。 从注入方法分:基于报错、基于布...
关于Oracle SQL外连接

关于Oracle SQL外连接

SQL提供了多种类型的连接方式,它们之间的区别在于:从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。 连接...

发表回复

返回顶部