- (文字列⇒日付)
TO_DATE('20171201','YYYY/MM/DD')
SELECT SYSDATE + 1 FROM DUAL; --1日足す
SELECT SYSDATE - 1 FROM DUAL; --1日引く
SELECT SYSDATE + 1/24 FROM DUAL; --1時間足す
SELECT SYSDATE + 2/1440 FROM DUAL; --2分足す
SELECT SYSDATE + 3/86400 FROM DUAL; --3秒足す
TRUNC（日時） 使用例 fmtは、設定した時間要素以下を切り捨てるという設定
SQL> select dt, fmt, TRUNC(dt, fmt) from trunc_date_sample;
DT FMT TRUNC(DT,FMT)
------------------- -------- -------------------
2007-07-07 23:30:40 MI 2007-07-07 23:30:00
2007-07-07 23:30:40 HH 2007-07-07 23:00:00
2007-07-07 23:30:40 DD 2007-07-07 00:00:00
2007-07-07 23:30:40 DAY 2007-07-01 00:00:00 -- 2007-07/01（日曜日）
2007-07-07 23:30:40 MM 2007-07-01 00:00:00
2007-07-07 23:30:40 YYYY 2007-01-01 00:00:00
2000-01-01 00:00:00 CC 1901-01-01 00:00:00
SELECT ...
WHERE TO_CHAR(date型列, 'HH24:MI:SS') BETWEEN '10:00:00'
AND '14:00:00';
曜日を表す数字(1～7)を取得する
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL
---------------------------
1
戻り値 曜日 1 日曜日 2 月曜日 3 火曜日 4 水曜日 5 木曜日 6 金曜日 7 土曜日
日本語で曜日を取得する方法
SELECT TO_CHAR(SYSDATE,'Day') FROM DUAL
---------------------------
月曜日
サブクエリを別名を付けて括りだす機能。
例) SELECT文の内容をINSERTする
INSERT INTO
new_table (
col
)
SELECT
col
FROM
old_table
これをWITH句を使うと以下のように書ける。サブクエリが長い場合に別名付けて、可読性をあげられる
INSERT INTO
new_table (
col
)
WITH
tmp_table AS (
SELECT
col
FROM
old_table
)
SELECT
col
FROM
tmp_table
表ロックを行う場合、ロックテーブルを作成するなどする。 SELECT時点で行ロック、COMMIT or ROLLBACK された時点で解除
SELECT
ORDER_ID
FROM
ORDER_INFO
WHERE
ORDER_ID = /*orderId*/9999
FOR UPDATE
OF ORDER_INFO.ORDER_ID
## 2.1 Create
### create as select
create table ADMIN_NEW
as
select
USER_ID
,USER_NM
,USER_STS
,UPDATE_TM
from ADMIN where UPDATE_TM >= to_timestamp('20200220','YYYYMMDD');
# 2.2 comment
COMMENT ON COLUMN ADMIN.USER_ID IS 'ユーザーID';
# 2.3 index
CREATE INDEX IX_ADMIN_02 ON ADMIN (USER_ID);
# 2.4 ALTER TABLE
## 列追加
ALTER TABLE "SCM_NAME"."SHOP" ADD "SHOP_KIND_FLG" NVARCHAR2(2);
-- 新規追加項目にnot nullを付ける場合、既存レコードの新規追加項目を更新してからnot nullを付与する必要がある
ALTER TABLE "SCM_NAME"."SHOP" MODIFY ("SHOP_KIND_FLG" NOT NULL ENABLE); -- not null制約追加
## 列変更
ALTER TABLE ADMIN MODIFY (USER_STS default '10');
## 列削除
ALTER TABLE table1 DROP (pno) CASCADE CONSTRAINTS; -- 1列を削除(CASCADE CONSTRAINTSは制約ごと削除するオプション)
ALTER TABLE table1 DROP (pno,memo); -- 複数列を削除
## テーブル変更
ALTER TABLE SHOP_ITME RENAME TO SHOP_ITME_BK; -- バックアップテーブルとしてリネーム
# 2.5 シノニム
## 設定
CREATE SYNONYM SCHEME_A.USER FOR USER;
# 2.6 ビュー
## View作成
CREATE VIEW v_emp AS
SELECT
a.name
,a.age
from
emp a
inner join dept b on a.dept_cd = b.dept_cd
;
## View更新
ALTER VIEW v_emp AS
SELECT
～
;
ビューの再定義をするときは、CREATE文で[OR REPLACE]を指定し、再定義する方法もあり
CREATE OR REPLACE VIEW v_emp AS
SELECT
～
;
## View削除
DROP VIEW v_emp;
select * from user_sequences; -- display seq list
select * from user_sequences where sequence_name = 'MY_SEQ';
-- カラム名の確認
SELECT COLUMN_NAME
,COMMENTS
FROM USER_COL_COMMENTS
WHERE TABLE_NAME = 'USER';
-- テーブルINDEXの確認
SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = UPPER('USER') ORDER BY INDEX_NAME, COLUMN_POSITION ASC;
-- PKEYの確認
SELECT C.TABLE_NAME, CC.COLUMN_NAME
FROM USER_CONSTRAINTS C, USER_CONS_COLUMNS CC
WHERE C.TABLE_NAME = CC.TABLE_NAME
AND C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
AND C.CONSTRAINT_TYPE = 'P' -- この情報がほしいから USER_CONSTRAINTS テーブルと結合
AND C.TABLE_NAME = 'USER'
ORDER BY CC.TABLE_NAME, CC.COLUMN_NAME;
-- シノニムの確認
SELECT * FROM ALL_SYNONYMS WHERE table_name = 'USER';
UNDOデータを参照して間違って削除してしまったデータを 復活させることができます。
-- 1時間前のデータを参照
select * from hoge
as of timestamp (systimestamp - interval '1' hour );
-- 1日前のデータを参照
select * from hoge
as of timestamp (systimestamp - interval '1' day );
-- 指定した日付、時間のデータを参照
select * from hoge
as of timestamp to_timestamp('2013-09-07 09:30:00', 'yyyy-mm-dd hh:mi:ss');
SELECT
avg(i.con) AS avg -- 平均
,median(i.con) AS median -- 中央値
,stddev(i.con) AS stddev -- 標準偏差
FROM (
SELECT
count(1) as con
FROM
ITEM
) i