Oreacle 语句
SELECT * FROM "T_WORKDAY" where 1=1 and CREATETIME >to_date('2019-01-01' , 'yyyy-mm-dd hh24:mi:ss') and CREATETIME < to_date('2019-02-01' , 'yyyy-mm-dd hh24:mi:ss') delete from T_WORKDAY;
--查询字段类型与名称描述 select * from( select a.Table_name,a.column_name,a.data_type,a.data_length,a.data_precision,a.nullable,a.column_id,b.comments from user_tab_columns a left join user_col_comments b on a.TABLE_NAME=b.table_name and a.COLUMN_NAME=b.column_name) WHERE TABLE_NAME ='T_SYS_MENU' SELECT * from (select a.*,t.column_name,t.comments as column_comments,t.data_type,t.data_length,t.data_precision,t.nullable,t.column_id from user_tab_comments a left join ( select a.Table_name,a.column_name,a.data_type,a.data_length,a.data_precision,a.nullable,a.column_id,b.comments from user_tab_columns a left join user_col_comments b on a.TABLE_NAME=b.table_name and a.COLUMN_NAME=b.column_name ) t on a.TABLE_NAME = t.table_name order by a.table_name,t.column_id) WHERE TABLE_NAME='TBLUSER' select * from (select a.*,t.column_name,t.comments as column_comments,t.data_type,t.data_length,t.data_precision,t.nullable,t.column_id from user_tab_comments a left join ( select a.Table_name,a.column_name,a.data_type,a.data_length,a.data_precision,a.nullable,a.column_id,b.comments from user_tab_columns a left join user_col_comments b on a.TABLE_NAME=b.table_name and a.COLUMN_NAME=b.column_name ) t on a.TABLE_NAME = t.table_name order by a.table_name,t.column_id) WHERE TABLE_NAME ='T_SYS_MENU' select * from user_tab_comments --查询所有表信息 select * from user_tab_columns --查询所有列信息 select * from user_tab_columns WHERE TABLE_NAME ='T_SYS_MENU' --查询指定表中所有列信息 select * from user_col_comments WHERE TABLE_NAME ='T_SYS_MENU' --查询指定表中所有列名+列备注信息
-*-分页查询
SELECT * FROM
(
SELECT temp.*, ROWNUM RN
FROM (SELECT * FROM 表名) temp
)
WHERE RN BETWEEN 1 AND 2
-*-* 连表查询
select count(*) as 总次数 from T_HANDLEHISTORY where DUTYUSERNAME='洋'
select count(*) as 延期次数 from T_HANDLEHISTORY where DUTYUSERNAME='洋' and (PROCEDURESTATE=4 or PROCEDURESTATE=2)
select count(*) as 正常次数 from T_HANDLEHISTORY where DUTYUSERNAME='洋' and (PROCEDURESTATE=3 or PROCEDURESTATE=1)
select a.总次数,b.正常次数 from
(select count(1) as 总次数 from T_HANDLEHISTORY where DUTYUSERNAME = '洋') a,
(select count(1) as 正常次数 from T_HANDLEHISTORY where DUTYUSERNAME = '洋' and (PROCEDURESTATE=1 or PROCEDURESTATE=3)) b