-- 时间转字符串
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual
select to_char(systimestamp, 'yyyy-mm-dd hh24:mi:ss.ff') from dual
select to_date(to_char(to_timestamp('2018-06-19 12:10:10.1','yyyy-mm-dd hh24:mi:ss.ff'),'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual;
-- 符串转时间
-- 字符串转 data
select to_date('2005-12-25 13:25:59','yyyy-mm-dd hh24:mi:ss') from dual ;
-- 字符串转 timestamp
SELECT to_timestamp('2018-06-19 12:10:10.1','yyyy-mm-dd hh24:mi:ss.ff')FROM dual;
-- timestamp 和 data 互相转换
select to_date(to_char(to_timestamp('2018-06-19 12:10:10.1','yyyy-mm-dd hh24:mi:ss.ff'),'yyyy-mm-dd'),'yyyy-mm-dd') from dual;
select systimestamp+0 from dual;
select to_timestamp('2018-06-19 12:10:10.1','yyyy-mm-dd hh24:mi:ss.ff')+0 from dual;
-- 时间转时间戳
select (to_date('2019-07-03 10:43:57','yyyy-mm-dd hh24:mi:ss') - to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss'))*86400000 from dual;
-- 时间戳转时间
select to_char(时间戳的那一列 / (1000 * 60 * 60 * 24) + to_date('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS') AS cdate FROM 表名 ;
-- 建表语句
create table table_name (id number VARCHAR2(255 CHAR) primary key)
-- 新建索引
-- Create Index user_name_index On temperature_detection(user_id);
create index 索引名称 on 表名(列名);
create unique index login_nameUnique on sys_user1(LOGIN_NAME);
-- 删除表
drop table table_name
-- 修改表名
ALTER TABLE table_name RENAME TO new_table_name;
RENAME table_name TO new_table_name;
-- 增加列明和备注
alter table SYS_USER_EXT add (
OPEN_THERMOMETRY_EQUIPMENT VARCHAR2(20)
);
comment on column SYS_USER_EXT.OPEN_THERMOMETRY_EQUIPMENT is '是否启动测温蓝牙设备(1:开启;0:关闭';
-- 修改列的名称
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
-- 修改列的数据类型
ALTER TABLE table_name MODIFY column_name new_data_type;
-- 如,将person表中的age列的数据类型修改为NUMBER(4),如下:
ALTER TABLE person MODIFY age NUMBER(4);表已更改。
-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;
-- 查看表的所有列
select * from user_tab_columns where Table_Name='TEMPERATURE_DETECTION'
-- 一次删除多列的语法形式如下:
ALTER TABLE table_name DROP (column_name , …);
-- Oracle分组后取每组第一条
SELECT * FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY x ORDER BY y DESC) rn,
t.*
FROM test1 t
) WHERE rn = 1;
-- 根据相同的字段分组后。聚合某一地字段
select listagg(t.MANAGER_ID, ',') within GROUP(order by(t.PROJECT_ID)) MANAGER_ID from table_name grop by PROJECT_ID;
select qlrid,wm_concat(qlr) as qlr,wm_concat(qlrzjh) as qlrzjh from qlr t group by qlrid;
-- 游标循环
declare
cursor emp_cursor is select id from project_Work_Order t where t.remarks like '%初始化%' for update;
v_i number(11);
begin
v_i := 1;
for var_emp in emp_cursor loop
update project_Work_Order set accept_date = to_date(to_char(((accept_date - to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')) *86400000 + v_i * 1000) / (1000 * 60 * 60 * 24) + to_date('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS')
where current of emp_cursor;
v_i := v_i + 1;
end loop;
commit;
end;