工作中常用的Sql语句

Oracle

select to_date(CSRQ,'YYYY-MM-DD HH24:MI:SS') as CSRQ2 from JW_XJGL_XSJBXXB;

查询字符串型转为date型的语句

Select * from user_col_comments;查询所有表注释和字段注释

Selcet * from user_tab_comments;查询所有表注释

Select * from all_all_tables; 查询所有表

Select column_name,count(*) from table_name group by column_name having count(*)>1; 查询重复的数据

TRUNCATE TABLE TABLE_NAME; 清空表的数据

commit;恢复上一层

Select * from tabs; 查询一个用户下的所有表;

Select * from cols; 查询一个用户下的所有表和字段;

Alter table table_name rename column old_col_name to new_col_name;更改字段名

SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'T_GXJX_BKSJXJHKC';查找某个表的主键字段

alter table table_name add constraint pk_table_name primary key(column_name);给表添加主键

Sql Server

Sql server 统计表的数据量

select schema_name(t.schema_id) as [Schema], t.name as TableName,i.rows as [RowCount]

from sys.tables as t, sysindexes as i

where t.object_id = i.id and i.indid <=1 order by i.rows desc  

Sql serever 查看表字段注释

SELECT  A.name AS table_name, B.name AS column_name, C.value AS column_description

FROM sys.tables A  

INNER JOIN sys.columns B ON B.object_id = A.object_id  

LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id WHERE A.name = 'table_name'

sql server 加表注释和字段注释

comment on table table_name is 'table_mark'
comment on column table_name."Column" is 'column_mark'

Sql server获取所有数据库名

Select Name FROM Master.dbo.SysDatabases order by Name desc(asc);

获取SqlServer中表结构

Select syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length

FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype

AND syscolumns.id = object_id('table_name');

 

Mysql

select * from

(

(select * from card_transactions where TRANSACTION_TIME>now() order by TRANSACTION_TIME limit 100)

union all

(select * from card_transactions where TRANSACTION_TIME<now() order by TRANSACTION_TIME desc limit 100)

) t

order by TRANSACTION_TIME desc limit 100

查看表结构

查询大于当前时间的第一条数据，如果没有就查询小于当前时间且离当前时间最近的一条数据

查询表结构

select b.COMMENTS,a.TABLE_NAME, row_number()over(partition by a.TABLE_NAME  order by d.COLUMN_ID) xh,c.COLUMN_NAME,c.COMMENTS ,decode(d.DATA_TYPE,'NVARCHAR2','VARCHAR2','CHAR','VARCHAR2',d.DATA_TYPE),decode(d.DATA_TYPE,'CLOB','','DATE','',d.DATA_LENGTH), '' as 主键   ,d.NULLABLE   from  user_tables a

left join user_tab_comments  b on a.TABLE_NAME=b.TABLE_NAME

left join user_col_comments c on a.TABLE_NAME=c.TABLE_NAME

left join  user_tab_columns d  on a.TABLE_NAME=d.TABLE_NAME and  c.COLUMN_NAME=d.COLUMN_NAME

where   a.TABLE_NAME   like '%T_GXJG_DZZW%'

order by  a.TABLE_NAME,d.COLUMN_ID

 

Oracle删除锁

select l.session_id,o.owner,o.object_name,l.*,o.*

from gv$locked_object l,dba_objects o where l.object_id=o.object_id and o.OWNER='JNTYDB'

 

select sid ,serial#,username,osuser from v$session where sid =5;

 

alter system kill session '5,1';

 

截取字段

 

substr(xkkh,instr(xkkh,')')+2,instr(xkkh,'-',1,4)-instr(xkkh,')')-2)

Update T_GXXS_BZKS_CJXX a set a.kch= substr(xkkh,instr(xkkh,')')+2,instr(xkkh,'-',1,4)-instr(xkkh,')')-2)     where  a.kch is  null  and xn='2015-2016'

 

mysql判断日期格式是否正确（返回值：1-正确 0-错误）

DROP FUNCTION IF EXISTS fc_ck_date;

 CREATE FUNCTION fc_ck_date( p_cont CHAR(32)  )  

 RETURNS tinyint(4)   NOT DETERMINISTIC   

SQL SECURITY DEFINER   

COMMENT '判定日期格式是否正确' BEGIN /*判定日期格式是否正确（返回值：1-正确 0-错误）*/ /*输入值格式为：yyyyMMdd 或 yyyy-MM-dd*/  

IF(SELECT DATE_FORMAT(p_cont,'%Y%m%d')) IS NULL THEN   RETURN 0; ELSE   RETURN 1; END IF;   END;

 

create view  V_YKT_ORG_MAIN_CONSUME_365 as

select  t1.customerid,t1.scardsnr,t1.outid,t2.opdt,t2.termid,t2.termname,t2.accdscrp,

t2.opcount,t2.opfare,t2.oddfare  from  

(select a.customerid,a.opdt,a.termid,b.termname,b.accdscrp,a.opcount,a.opfare,a.oddfare

from   t_ykt_og_rec_main_consume partition(TBS_PAR2018)  a  left join 

(select m.termname,m.termid,n.accdscrp ,m.portid from   t_ykt_og_term  m  left join  t_ykt_og_acc_type n

on  m.acccode=n.acccode) b

on  a.termid=b.termid

) t2 left join  t_ykt_og_customers  t1

on  t1.customerid=t2.customerid

--当年的一卡通流水数据，每年年底12月31号需要修改分区，

;

 

create view  V_YKT_ORG_MAIN_CONSUME_7 as

select  t1.customerid,t1.scardsnr,t1.outid,t2.opdt,t2.termid,t2.termname,t2.accdscrp,

t2.opcount,t2.opfare,t2.oddfare  from  

(select a.customerid,a.opdt,a.termid,b.termname,b.accdscrp,a.opcount,a.opfare,a.oddfare

from   t_ykt_og_rec_main_consume partition(TBS_PAR2018)  a  left join

(select m.termname,m.termid,n.accdscrp ,m.portid from   t_ykt_og_term  m  left join  t_ykt_og_acc_type n

on  m.acccode=n.acccode) b

on  a.termid=b.termid

) t2 left join  t_ykt_og_customers  t1

on  t1.customerid=t2.customerid

where t2.opdt >=sysdate-7

--7天内的一卡通流水数据，每年年底12月31号需要修改分区

;

 

添加或者修改数据自动更新时间

alter table book_library_infos modify created_at datetime not null default current_timestamp on update current_timestamp