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