1.创建新表:
create TABLE table_name
(
colunm1 NUMBER(11) NOT NULL PRIMARY KEY,
column2 VARCHAR2(150) NOT NULL,
colunm3 TIMESTAMP(6)
);
2.创建新表,表结构同已存在的表:
CREATE TABLE table_name AS
select * from table_1;
3.新增列:
ALTER TABLE table_name ADD (colunm_name TIMESTAMP(6) default SYSDATE);
4.增加关键字:
ALTER TABLE table_name ADD PRIMARY KEY(column_name);
alter table table_name add [constraint constraint_name] primary key(column_name);
5.oracle 增加注释:
comment on table_name IS '描述';
comment on column table_name.colunm_name is '描述';
6.创建表索引
create index index_name on table_name(colunm1,colunm2);
7.删除表索引
DROP INDEX index_name;
8.修改表名
ALTER TABLE table_name_old RENAME TO table_name_new;
9.删除表字段
alter table table_name drop(colum_name);
10.删除主键
alter table table_name drop primary key;
11.两个字段col1和col2的类型为timestamp,求时间差
SELECT
TO_char(a.col1, 'yyyy-MM-dd hh24:mi:ss'),
TO_char(a.col2, 'yyyy-MM-dd hh24:mi:ss'),
CASt(a.col1 AS DATE)+1
FROM table_a a
WHERE CASt(a.col1 AS DATE)+1<CAST(a.col2 AS DATE);
12.N个月前的当月第一天
SELECT to_char(add_months(TRUNC(SYSDATE, 'Q'), -18), 'YYYY-MM-DD') FROM dual; --2015-01-01
SELECT to_char(add_months(TRUNC(SYSDATE, 'Q'), -15), 'YYYY-MM-DD') FROM dual; --2015-04-01
13.本季度第一天
SELECT to_char(TRUNC(SYSDATE, 'Q'), 'YYYY-MM-DD') FROM dual; --2016-07-01
14. 上个季度最后一天(可以用本季度第一天减去1得到)
SELECT to_char(TRUNC(SYSDATE, 'Q') - 1, 'YYYY-MM-DD') FROM dual; --2016-06-30
15.Oracle将表授权给用户的命令
命令:grant xxx权限 on TableA to USERA
grant select,insert,update,delete on 表名 to 用户名
例如:将test表的查询权限赋予solidwang这个用户
grant select on test to solidwang
16.
SELECT TRUNC(SYSDATE, 'month') 本月第一天,
TRUNC(LAST_DAY(SYSDATE)) 本月最后一天,
TRUNC(ADD_MONTHS(SYSDATE, -1), 'month') 上月第一天,
TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1))) 上月最后一天,
TRUNC(ADD_MONTHS(SYSDATE, -12), 'month') 去年本月第一天,
TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -12))) 去年本月最后一天
FROM DUAL