Oracle 基础sql

一、引用参考
-- Oracle数据库中 to_date()、to_char()函数的用法
https://blog.csdn.net/idomyway/article/details/78785112
--oracle获得当前时间，精确到毫秒并指定精确位数
https://www.cnblogs.com/mq0036/p/6813147.html
--解决乱码：oracle中的timestamp字段的值乱码
https://www.cnblogs.com/lowerCaseK/p/timeStamp_coding.html
-- ORACLE中Like与Instr模糊查询性能大比拼(转载)
http://blog.csdn.net/bamboopot/article/details/8127033
--Oracle 增删列,为列增加注释,修改主键等
https://www.cnblogs.com/furenjun/archive/2009/03/13/oraclecolumn.html
--oracle alter table详解
http://blog.csdn.net/chen_linbo/article/details/6323727
--ORACLE中通过SQL语句(alter table)来增加、删除、修改字段
http://blog.csdn.net/piaoxuan1987/article/details/12277407
--Oracle Purge和drop的区别
https://zhidao.baidu.com/question/1639310045220424220.html
--oracle SEQUENCE 修改，删除，创建
http://blog.csdn.net/xiechunmei13/article/details/5656845

--跨库查询
https://www.cnblogs.com/liuqiyun/p/6589623.html

--check约束
https://blog.csdn.net/jssg_tzw/article/details/40985081

二、工作SQL

-----------------------------
dual、时间 to_date()、to_char()
-----------------------------
select t.id,t.stu_name,t.create_time,t.update_time from crm.t_stu_info t 
where t.create_time>to_date('2018-04-18 15:30:30', 'yyyy-MM-dd HH24:mi:ss') and t.create_time<to_date('2018-04-19', 'yyyy-MM-dd HH24:mi:ss');
//
select sysdate from dual;  //DATE: 2018/8/16 19:08:10
select to_char(sysdate, 'yyyy-MM-dd HH:mi:ss') from dual; //HH: 12小时制； HH24: 24小时制；
select to_char(sysdate, 'YYYY-mm-dd hh24:mi:ss') from dual; // 时间格式不区分大小写 注意区分月份和分钟，月份用MM或mm，分钟用mi
select to_char(sysdate, 'yyyy-MM-dd HH24:mi:ss') from dual; // 2018-08-16 19:02:57
select to_char(systimestamp, 'yyyy-MM-dd HH24:mi:ss.ff') from dual; // 2018-08-16 18:49:13.730531
select to_char(systimestamp, 'yyyy-MM-dd HH24:mi:ss.ff3') from dual; // 2018-08-16 19:03:18.815 ff的位数取值范围：[1,9]
--------------------------
基础sql
--------------------------
select * from CRM.T_STUDENT_INFO;
select count(1) from CRM.T_STUDENT_INFO;  --176
select * from CRM.T_STUDENT_INFO order by id desc; 
select t.rowid, rownum,t.* from CRM.T_STUDENT_INFO t order by t.id desc; -- AAAY jnAA iAAA Cx3A Ai  18位; rownum不能用t.rownum
select t.rowid,rownum,t.id,t.short_url from CRM.T_STUDENT_INFO t order by t.rowid asc;

-- 【rownum本身只能用 <=的比较方式，只有转成实例才能做 >=的比较】
select *, rownum rn from demo.t_demo_info where rownum<=120;  //查不出来
select id, rownum rn from demo.t_demo_info where rownum<=120; //可以查出来
select * from demo.t_demo_info where rownum>=101; //查不出来
select * from demo.t_demo_info where rownum>101; //查不出来
select t.* from (
select id, url, rownum rn from demo.t_demo_info where rownum<=120
) t where  t.rn>=101;  //查询第101至第120条记录

--查询前10条记录，无序排序
select t.rowid,rownum,t.id,t.url from CRM.T_STUDENT_INFO t where rownum<11;
--查询前10条记录，按id升序
select t.rowid,rownum,t.id,t.url from CRM.T_STUDENT_INFO t where rownum<11 order by t.id asc;
--查询第11到第20条记录
 select rownum,t.id,t.url from CRM.T_STUDENT_INFO t where rownum<21;
/*写法1*/
select s.* from(
       select rownum rn,t.order_id from CRM.T_STUDENT_INFO t
) s  where s.rn>10 and s.rn<=20;

/*写法2*/
select s.* from(
       select rownum rn,t.order_id from CRM.T_STUDENT_INFO t where rownum<=20
) s  where s.rn>10

ps:查询第11到20条记录


--分组统计

select to_char(create_time, 'yyyy-MM-dd'), count(1) 
from demo.t_demo_info where 1=1 and create_time>to_date('2018-06-01', 'yyyy-MM-dd')  
group by to_char(create_time, 'yyyy-MM-dd')
order by to_char(create_time, 'yyyy-MM-dd');

--函数
https://www.cnblogs.com/lxl57610/p/7442130.html
https://www.cnblogs.com/chuangege/p/6258658.html
https://www.cnblogs.com/ZwyBky/p/7159191.html

//wm_concat()  多行单字段拼接，为空则跳过
select WM_CONCAT(a.activity_name) from pcs.t_activity_info a where rownum<=20
//extract()
https://blog.csdn.net/github_37830343/article/details/80333174

--查询锁表情况、解锁
https://www.cnblogs.com/XQiu/p/5212787.html
https://zhidao.baidu.com/question/1924996799512691147.html
http://blog.csdn.net/zengmingen/article/details/51007745

//查看当前锁表情况
select p.spid,a.sid,
       a.serial#,
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name
  from v$process p, v$session a, v$locked_object b, all_objects c
 where p.addr = a.paddr
   and a.process = b.process
   and c.object_id = b.object_id;

//解锁
alter system kill session 'sid , s.serial#';  
--杀掉进程 sid,serial#
alter system kill session'210,11562';

三、修改表结构

/*
创建表 T_STUDENT_INFO
*/
-- Create table
create table XTSK.T_STUDENT_INFO
(
  ID         NUMBER not null,
  STUDENT_ NO VARCHAR2(32) not null,
  CRT_TIME   DATE not null,
  UPT_TIME   DATE not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 8
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table XTSK.T_STUDENT_INFO
  is '学生学号表';
-- Add comments to the columns 
comment on column XTSK.T_STUDENT_INFO.ID
  is '主键id';
comment on column XTSK.T_STUDENT_INFO.STUDENT_ NO
  is '用户STUDENT_ NO';
comment on column XTSK.T_STUDENT_INFO.CRT_TIME
  is '创建时间';
comment on column XTSK.T_STUDENT_INFO.UPT_TIME
  is '修改时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table XTSK.T_STUDENT_INFO
  add constraint PK_T_STUDENT_INFO primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255;  
/*
创建序列SEQ_STUDENT_INFO
*/
-- Create sequence 
create sequence XTSK.SEQ_STUDENT_INFO
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
----------------------------------------------------------------------------
--回滚
----------------------------------------------------------------------------
/*
删除表 T_STUDENT_INFO
*/  
drop table XTSK.T_STUDENT_INFO cascade constraints;
/*
删除序列 SEQ_STUDENT_INFO
*/  
DROP SEQUENCE XTSK.SEQ_STUDENT_INFO;

四、SQL性能优化
------------------------------------------------------------
模糊查询instr 和 like 对比
-------------------------------------------------------------
--instr（str, targetStr）函数
instr(title,'手册')>0  相当于  title like '%手册%'

instr(title,'手册')=1  相当于  title like '手册%'

instr(title,'手册')=0  相当于  title not like '%手册%'
==========================================================

另外，我在结另外一个2亿多的表，使用8个并行，使用like查询很久都不出来结果，但使用instr,4分钟即完成查找，性能是相当的好。这些小技巧用好，工作效率提高不少。通过上面的测试说明，ORACLE内建的一些函数，是经过相当程度的优化的。

instr(title,’aaa’)>0 相当于like

instr(title,’aaa’)=0 相当于not like

特殊用法：
select   id, name from users where instr('101914, 104703', id) > 0;
  它等价于
select   id, name from users where id = 101914 or id = 104703;

--使用Oracle的[instr函数与索引配合]提高模糊查询的效率

一般来说，在Oracle数据库中，我们对tb表的name字段进行模糊查询会采用下面两种方式：
1.select * from tb where name like '%XX%';
2.select * from tb where instr(name,'XX')>0;

若是在name字段上没有加索引，两者效率差不多，基本没有区别。

为提高效率，我们在name字段上可以加上非唯一性索引：
create index idx_tb_name on tb(name);
这样，再使用
select * from tb where instr(name,'XX')>0;

这样的语句查询，效率可以提高不少，表数据量越大时两者差别越大。但也要顾及到name字段加上索引后DML语句会使索引数据重新排序的影响。