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