SQL 覚書

■　覚書なので気にしないでね！


★　テーブル作成

SQL> CREATE TABLE BLOG_MASTER
(
AMEBA_ID VARCHAR2(30 BYTE) NOT NULL
, BLOG_NAME VARCHAR2(30 BYTE)
, CREATE_DATE VARCHAR2(8 BYTE)
, CONSTRAINT IDX_AMEBA_ID PRIMARY KEY
(
AMEBA_ID
)
USING INDEX
(
CREATE UNIQUE INDEX IDX_AMEBA_ID ON BLOG_MASTER (AMEBA_ID ASC)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOPARALLEL
)
ENABLE
);


★ INDEXの確認

SQL> SELECT INDEX_NAME ,TABLE_NAME ,COLUMN_NAME FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'BLOG_MASTER';


【USER_IND_COLUMNS】
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND






★　カラム参照


SQL> SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'BLOG_MASTER';
SQL> SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'BLOG_MASTER';

【USER_TAB_COLUMNS】
"TABLE_NAME","COLUMN_NAME","DATA_TYPE","DATA_TYPE_MOD","DATA_TYPE_OWNER","DATA_LENGTH","DATA_PRECISION","DATA_SCALE","NULLABLE","COLUMN_ID","DEFAULT_LENGTH","DATA_DEFAULT","NUM_DISTINCT","LOW_VALUE","HIGH_VALUE","DENSITY","NUM_NULLS","NUM_BUCKETS","LAST_ANALYZED","SAMPLE_SIZE","CHARACTER_SET_NAME","CHAR_COL_DECL_LENGTH","GLOBAL_STATS","USER_STATS","AVG_COL_LEN","CHAR_LENGTH","CHAR_USED","V80_FMT_IMAGE","DATA_UPGRADED","HISTOGRAM"





★　表の参照

SELECT * FROM BLOG_MASTER;
SELECT * FROM ARTICLE;


★　表内のデータ存在判定

SELECT * FROM ARTICLE WHERE EXISTS(SELECT 'X' FROM BLOG_MASTER WHERE AMEBA_ID='new-blue-777');
SELECT * FROM ARTICLE WHERE NOT EXISTS(SELECT 'X' FROM BLOG_MASTER WHERE AMEBA_ID='new-blue-777');

★　IN判定

SELECT * FROM ARTICLE WHERE ARTICLE_ID IN(10082221232,11868034144);
SELECT * FROM ARTICLE WHERE ARTICLE_ID NOT IN(10082221232,11868034144);


★　LIKE判定

SELECT * FROM ARTICLE WHERE ARTICLE_ID LIKE '11%';

★　ORDER BY 表示上の並べ替え

SELECT * FROM ARTICLE ORDER BY THEME_NO;
SELECT * FROM ARTICLE ORDER BY THEME_NO DESC;


★　サマリー

SELECT AMEBA_ID,COUNT(*) FROM ARTICLE GROUP BY AMEBA_ID;
SELECT AMEBA_ID,SUM(ARTICLE_ID),MAX(ARTICLE_ID),MIN(ARTICLE_ID),AVG(ARTICLE_ID),COUNT(*) FROM ARTICLE GROUP BY AMEBA_ID;

★ AS(ヘッダー表示)

SELECT AMEBA_ID AS "アメーバＩＤ",COUNT(*) AS "記事件数" FROM ARTICLE GROUP BY AMEBA_ID;


★ HAVING

SELECT AMEBA_ID,SUM(ARTICLE_ID) AS "合計" FROM ARTICLE GROUP BY AMEBA_ID HAVING SUM(ARTICLE_ID)>=5000;






★　表へのデータ挿入

SQL> INSERT INTO BLOG_MASTER VALUES( 'new-blue-777','リアナのカスタマイズ日記','20110727');



★　表の更新
SQL> UPDATE ARTICLE SET ARTICLE_NAME='テーマ　記事数の非表示',ARTICLE_DATE='20140601' WHERE ARTICLE_ID='11868034144';

★ 　表の削除
SQL> DELETE FROM ARTICLE WHERE ARTICLE_ID='11868034144';


★　ダンプ（文字コード、１６進表示）
SQL> select dump(AMEBA_ID,1016) from BLOG_MASTER where rownum <= 1;


DUMP(AMEBA_ID,1016)
--------------------------------------------------------------------------------
Typ=96 Len=30 CharacterSet=AL32UTF8: 6e,65,77,2d,62,6c,75,65,2d,37,37,37,20,20,2
0,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20



★　表のロック

lock table "PGINFO"."ARTICLE" in EXCLUSIVE mode nowait;
lock table "PGINFO"."ARTICLE" in ROW SHARE mode;
lock table "PGINFO"."ARTICLE" in ROW EXCLUSIVE mode;
lock table "PGINFO"."ARTICLE" in SHARE ROW EXCLUSIVE mode;
lock table "PGINFO"."ARTICLE" in SHARE mode;


★　ロック状態の表示(管理者の権限必要)

SQL> SELECT SID, SERIAL# FROM V$SESSION WHERE SID IN (SELECT SID FROM V$LOCK WHERE TYPE IN ('TM','TX'));

SID SERIAL#
---------- ----------
93 35

SQL> SELECT SID,SERIAL#,USERNAME,PROGRAM FROM V$SESSION WHERE SID IN (SELECT SID FROM V$LOCK WHERE TYPE IN ('TM','TX'));


★　ロックの解除

ROLLBACK;
COMMIT;


★　セッションのkill

alter system kill session '93,35';