SQL大杂烩

DML 语句（数据操作语言）Insert、Update、 Delete、Merge

DDL 语句（数据定义语言）Create、Alter、 Drop、Truncate

DCL 语句（数据控制语言）Grant、Revoke

----------------------------------------------------------------------------------------事务

事务控制语句 Commit 、Rollback、Savepoint savepoint a; rollback to a;（不能commit）

----------------------------------------------------------------------------------------时间

date ：表示年月日，如YY-MM-DD，7字节

timestamp：更精确

select sysdate from dual;

select systimestamp from dual;

select to_timestamp(to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff'),'yyyy-mm-dd hh24:mi:ss.ff') from dual;

修改时间：

update time set time_timestamp= to_timestamp('2006-01-01 12:10:10.1','yyyy-mm-dd hh24:mi:ss.ff'));

update time set time_date=to_date('20170321','yyyymmdd') where sno='108';

时间比较：

select * from time t where to_char(t.time_date,'yyyymmdd')>'20170915';

select * from time t where t.time_date>to_date('20170915','yyyymmdd');

计算相隔天数：

to_date(to_char(systimestamp, 'yyyymmdd'), 'yyyymmdd') - to_date(to_char(max(t.oper_time), 'yyyymmdd'), 'yyyymmdd')

----------------------------------------------------------------------------------------删除表

truncate table table_name;

delect from table_name;

--删除用户下所有表

select 'drop table '||table_name||';' from cat --user_tab_comments where table_type='TABLE'

--恢复删除的表

flashback table data_statistics to before drop;

----------------------------------------------------------------------------------------插入

insert into log_data_change t (TIMESTAMP,JC_STANDARD_TABLE_ID,COLUMN_NANE,CN_NAME, DATA_TYPE,DATA_LENGTH,BYZ_TYPE,JC_CHANGE_ID,USER_NAME,STANDARD_STATUS_CHANGED ) values(systimestamp,'','','','','','',sys_guid(),'','');

-----------------------------------------------------------------------------------------视图

create or replace view view_Account_dept as select * from emp where deptno=10

-----------------------------------------------------------------------------------------CREATE

? CREATE INDEX：创建数据表索引

? CREATE PROCEDURE：创建存储过程

? CREATE FUNCTION：创建用户函数

? CREATE VIEW：创建视图

? CREATE TRIGGER：创建触发程序

? CREATE SEQUENCE

----------------------------------------------------------------------------------------修改

select t.rowid,t.* from student t;

select t.* from student t for update;--容易锁表

update student set sclass='95032' where ssex='男';

update jc_column t set (COLUMN_TYPE, COLUMN_LENGTH) = (select COLUMN_TYPE, COLUMN_LENGTH from jc_column t where t.jc_table_code = 'OPER_APP_INFO') where t.jc_table_code= 'HIS_OPER_APP_INFO';

merge into reg_cp_rs_certificate z using cp_rs_ent_tmp t on (z.entid = t.ent_id) when matched then update set z.updatedate = t.reg_end_date;

----------------------------------------------------------------------------------------连接

内连接 inner join，from A,B where A.a=B.a

左连接 left join

右连接 right join

完全连接 full join

----------------------------------------------------------------------------------------函数

32位随机数: sys_guid()

字符函数：

Upper Lower Initcap（首字母大写）

Concat（连接字符串和||一个效果）

Substr Length Replace Instr（查找字符串b在字符串a中的位置）

Lpad（lpad('Smith',10,'*') 左侧填）

Rpad

Trim

数值函数

Round（四舍五入到小数点后n位） Mod（取余） Trunc（显示到小数点后n位）

日期函数

Months_between()

Add_months()

Next_day()

Last_day()

转换函数

To_char

To_number

To_date

通用函数

NVL(,)（取非空）

NVL2('a','b','c')（如果a不空取b，a空取c）

NULLIF()（如果表达式 exp1 与 exp2 的值相等则返回 null，否则返回 exp1 的值）

COALESCE()（依次考察各参数表达式，遇到非 null 值即停止并返回该值）

CASE() select empno, ename, sal, case deptno when 10 then '财务部' when 20 then '研发部' when 30 then '销售部' else '未知部门' end 部门 from emp;

DECODE() decode(a.standard_status, 1, 1, 0) select empno, ename, sal, decode(deptno, 10, '财务部', 20, '研发部', 30, '销售部', '未知部门') 部门 from emp;

分组函数

COUNT()

Avg，max，min，sum GROUP BY 子句 HAVING 子句

distinct()去重复

dump()

-------------------------------------------------------------------------------------------计数

sum(decode(t.state, 1, 1, 0)) hb, sum(decode(t.state, 2, 1, 3, 1, 0)) byz, sum(decode(t.state, 4, 1, 0)) more, sum(decode(t.state, null, 1, 0)) weibidui

数据按千位分隔显示 to_char(t.out, '99,999,999')

-------------------------------------------------------------------------------------------交并差

union/union all 并集

intersect 交集

minus 差集

select column_name from all_tab_cols where table_name='' intersect select column_name from all_tab_cols where table_name=''

-------------------------------------------------------------------------------------------加注释

comment on table GC_G_DOC84 is '行政处罚撤销决定书';

comment on column GC_G_DOC84.CASEID is '案件记录ID';

--------------------------------------------------------------------------------------------alter

alter table GC_G_DOC84 modify(COL4 varchar2(4000));

ALTER TABLE (表名) ADD CONSTRAINT (索引名);

ALTER TABLE (表名) DROP CONSTRAINT (索引名);

ALTER TABLE (表名) ADD (列名数据类型);

ALTER TABLE (表名) MODIFY (列名数据类型);

ALTER TABLE (表名) RENAME COLUMN (当前列名) TO (新列名); /////不需要括号

ALTER TABLE (表名) DROP COLUMN (列名);

ALTER TABLE (当前表名) RENAME TO (新表名);

--------------------------------------------------------------------------------------------建表

create table table_name as select * from table_name1;

create table table_name as select * from table_name1 where 1=2;

insert into table_name select * from table_name1;

-------------------------------------------------------------------------------------------分页查询

select * from (select rownum no,e.* from (select * from emp order by sal desc) e where rownum<=5 ) where no>=3;

select * from (select rownum no,e.* from (select * from emp order by sal desc) e) where no>=3 and no<=5;

-------------------------------------------------------------------------------------------用户权限

用户：

create user sun identified by 123; --创建用户

alter user sun identified by 111; --修改密码

drop user sun cascade; --删除用户及对象

drop user sun; --删除用户

赋予权限：

connect，resource，dba---------------------------新用户授予权限

grant connect,resource,dba to scott;

grant select on student to scott;

grant select any table to scott;

grant unlimited tablespace to scott;--操作表空间

grant create tablespace to scott;

移除权限：

revoke dba from scott;

revoke select on student from scott;

select * from role_sys_privs where role='RESOURCE';

select * from dba_sys_privs where grantee='RESOURCE';

-- Create the user create user ZCK default tablespace USERS temporary tablespace TEMP profile DEFAULT;

-- Grant/Revoke role privileges grant connect to ZCK;

grant dba to ZCK;

grant resource to ZCK;

-- Grant/Revoke system privileges grant create tablespace to ZCK;

grant unlimited tablespace to ZCK;

------------------------------------------------------------------------------------------锁

SELECT object_name, machine, s.sid, s.serial# FROM gv$locked_object l, dba_objects o, gv$session s WHERE l.object_id　= o.object_id AND l.session_id = s.sid;

alter system kill session '44, 3961'; alter system kill session '51, 4751';

-------------------------------------------------------------------------------------------同义词

select * from dba_synonyms t where t.synonym_name='CC';--必须大写

select * from user_synonyms;--只查询私有同义词,因为共有同义词的owner为public

create public synonym aa for zck.jc_table;--公有同义词

create synonym bb for zck.jc_table;--私有同义词

alter synonym aa compile;--编译同义词

drop public synonym aa;--删除同义词

select t.*,t.rowid from aa t; select t.*,t.rowid from bb t;

-------------------------------------------------------------------------------------------跨库查询

--zck为用户名,只有高权限的能查低权限的

select * from zck.jc_table;

--zck为dblink

select * from jc_table@zck;

-------------------------------------------------------------------------------------------表空间

select username, default_tablespace from dba_users where username='SJZX_STDCHECK_TEST';

alter tablespace JDS_DATA rename to JDS;

create tablespace datacenter datafile 'datacenter.dbf' size 20m;

-------------------------------------------------------------------------------------------存储过程

--打开打印

set serveroutput on;

--执行存储过程

exec sp_select; execute sp_select; begin sp_select; end;

--创建存储过程

create or replace procedure sp_select as len number:=5; width number:=4; area number; v_total varchar2(30); begin select name into v_total from demo where rownum=1; DBMS_OUTPUT.put_line('name：'||v_total); end;

------------------------------------------------------------------------------------------其它查询SQL

with dj as (select C.TYPE_ID_FK,c.code_value,C.CODE_NAME from SYSMGR_CVALUE c where c.type_id_fk = 'CA01' ORDER BY CODE_VALUE), sb as (select d.dict_code,d.dm_value,d.dm_name from std_dict@std_dm s left join std_dm@std_dm d on s.dict_id = d.dict_id where s.dict_code = 'CA01' and s.src_type = '1' ORDER by d.dm_value) select code_value,CODE_NAME,dm_value,dm_name from dj left join sb on TYPE_ID_FK = dict_code where code_value=dm_value and CODE_NAME=dm_name order by code_value,dm_value

select t.casesrcid ,count(1) from ZJ_CASE_CF_SRCINF t group by casesrcid having count(1)>1---重复条数

select 'select "'||t.table_name||'" ,count(1) from '||t.table_name||' union all' from user_tab_comments t

---------------------------------------------------------------------------------------------游标

show parameter open_cursors;--查看游标

alter system set open_cursors=2000;

select * from gv$instance;