SQL

Oracle：テーブルDDL

CREATE TABLE MEMBER(

ID CHAR(8) PRIMARY KEY,

NAME CHAR(30) DEFAULT '仮名' NOT NULL,

AGE NUMBER(3) DEFAULT 0 NOT NULL,

REGISTER_DATE DATE DEFAULT SYSDATE NOT NULL,

DETAIL_ID CHAR(5) DEFAULT '99999' NOT NULL,

CONSTRAINT FK_XXX FOREIGH_KEY (DETAIL_ID)

REFERENCES MEMBER_DETAIL (DETAIL_ID)

)

★基本（SQL Server）

●SELECT文

SELECT DISTINCT name FROM table;

SELECT TOP 3 * FROM table ORDER BY NEWID(); --ランダムに取得

SELECT * FROM table ORDER BY RAND() LIMIT 3; --MySQL

SELECT COUNT(*) FROM table;

SELECT COUNT(name) FROM table; --NULLではない行

SELECT COUNT(DISTINCT name) FROM table;

SELECT t.id as 番号, t.name '名前' FROM table t;

SELECT (age + id) col FROM table;

SELECT t1.name + '-' t2.name FROM table1 t1, table2 t2 WHERE t1.id = t2.cid;

SELECT * FROM table WHERE id <> 3;

SELECT * FROM table WHERE id <= 3 AND sex = 1;

SELECT * FROM table WHERE id NOT BETWEEN 2 AND 5;

SELECT * FROM table WHERE name NOT LIKE '%[j,n]%';

SELECT * FROM table WHERE id NOT IN (2, 3);

SELECT * FROM table WHERE age IS NOT NULL;

SELECT * FROM table ORDER BY name ASC/DESC;

SELECT COUNT(age), age FROM table WHERE id > 2 GROUP BY age;

SELECT COUNT(*), set FROM table GROUP BY sex, age ORDER BY age;

SELECT COUNT(*), age FROM table GROUP BY age HAVING COUNT(age) >= 3 AND MAX(id) > 2;

SELECT

CASE WHEN LEN(news_content) > 10

THEN LEFT(news_content, 10) + '...'

ELSE news_content

END news_name,

news_id

FROM table

⇒内部クエリ

SELECT *, (SELECT COUNT(*) FROM table1 WHERE cid = table2.id) num

FROM table2 ORDER BY num;

SELECT * FROM table1 WHERE cid in (

SELECT id FROM table2 WHERE id > 5

);

SELECT t1.title, t2.updateDate

FROM table t1, (SELECT MAX(updateDate) updateDate FROM table2 WHERE table2.title = t1.title) t2;

SELECT t1.title, t1.updateDate FROM tabl1 t1

WHERE updateDate = (SELECT MAX(updateDate) FROM table2 t2 WHERE t2.name = t1.name)

WHERE句副クエリ

SELECT order_id, custom_id

FROM order

WHERE custom_id EXISTS

(SELECT custom_id FROM customer WHERE custom_name='Andy');

FROM句副クエリ

SELECT o.order_id, o.custom_id

FROM

order o,

(SELECT custom_id FROM customer WHERE custom_name='Andy') c

WHERE o.custom_id = c.custom.id;

⇒ソート関連

--連番

SELECT t1.id, t1.name, t2.id, t2.name, ROW_NUMBER() OVER(ORDER BY t2.name) num

FROM table1 t1, table2 t2 WHERE t1.cid = t2.id;

※RANK()、DENSE_RANK()

--部分に分ける

SELECT t1.id, t1.name, t2.id, t2.name, NTILE(3) OVER(ORDER BY t2.name) num

FROM table1 t1, table2 t2 WHERE t1.cid = t2.id;

⇒集合の演算

--結合

SELECT id, name FROM table WHERE name like 'an%';

UNION

SELECT id, name FROM table WHERE id = 3;

※UNION 重複なし

※UNION ALL 重複あり、速い

--同じ部分

SELECT id, name FROM table WHERE name like 'an%';

INTERSECT

SELECT id, name FROM table WHERE id = 3;

--違う部分

SELECT id, name FROM table WHERE name like 'an%';

EXCEPT

SELECT id, name FROM table WHERE id = 3;

⇒JOIN

SELECT t1.id, t1.name, t2.id, t2.name FROM table1 t1, table2 t2 WHERE t1.cid = t2.id;

SELECT t1.id, t1.name, t2.id, t2.name FROM table1 t1 LEFT/RIGHT/INNER JOIN table2 t2 ON t1.cid = t2.id;

--同じテーブル

SELECT DISTINCT t1.* FROM table t1, table t2 WHERE t1.id <> t2.id AND t1.set = t2.set;

●SELECT文以外

UPDATE table SET name = 'Andy', sex = 2 WHERE id = 5;

UPDATE

table1

SET

updateDate = GETDATE()

,bitFlg ^= 4

,version += 1

FROM

table1 t1

LEFT JOIN table2 t2

ON t1.id = t2.id

WHERE

t1.bitFlg & 1 = 1;

INSERT INTO table(id, name, set) VALUES(10, 'Andy', 2);

INSERT INTO table1

SELECT t2.id, t2.name, t2.sex FROM table2 WHERE id > 9;

DELETE FROM table1 t1 WHERE NOT EXISTS(

SELECT * FROM table2 WHERE t1.cid = t2.id

);

★NULLについて

SELECT * FROM TableA A

WHERE A.ID NOT IN (SELECT ID FROM TableB) ←TableB.IDがNULLの場合を考慮してない

対策１

SELECT * FROM TableA A

WHERE A.ID NOT IN (SELECT ID FROM TableB WHERE ID IS NOT NULL)

対策２(推薦)

SELECT * FROM TableA A

WHERE NOT EXISTS (SELECT * FROM TableB B WHERE B.ID = A.ID)

NULLと算術式

SELECT ID, NVL(COST, 0) AS COST FROM TableC

★SQL文のエスケープ

対策：WildCardを無効にする

LIKE条件の場合

% 任意の文字列

_ 任意の１文字

SELECT * FROM TableA WHERE NAME LIKE '%_%'; ×

SELECT * FROM TableA WHERE NAME LIKE '%￥_%' ESCAPE '￥'; 〇

Java＋Oracleサンプル

public String method(String query){

if(query != null && query.length() > 0){

String escaped = query.replace("'", "''") // 特殊文字

.replace("@", "@@") // エスケープ

.replace("%", "@%") // 半角

.replace("_", "@_") // 半角

.replace("％", "@％") // 全角

.replace("＿", "@＿"); // 全角

return String.format("SELECT * FROM TableA WHERE DEL_FLG='0' AND NAME LIKE '%%s%' ESCAPE '@'", escaped);

}

return null;

}

★制約（Oracle）

・NOT NULL NN_テーブル名_カラム名

・UNIQUE UK_テーブル名_カラム名

・PRIMARY KEY PK_テーブル名

・FOREIGN KEY FK_テーブル名_カラム名

・CHECK CK_テーブル名_カラム名

・REF DF_テーブル名_カラム名

★ソートについて

ソート処理が重いので、避けるべき

下記のSQL文ではソート処理が行うかもしれない

ORDER BY

GROUP BY

DISTINCT

EXISTS / NOT EXISTS

IN / NOT IN

UNION（UNION ALLではソート処理なしが、重複データを除かない）

MINUS

INTERSECT

CREATE INDEX

MERGE JOIN

★日付について

Oracleの場合

SELECT * FROM Table A

WHERE A.UPDATE_DATE >= TO_DATE('2012-04-11', 'YYYY-MM-DD') ←時分秒を考慮せず

AND A.UPDATE_DATE <= TO_DATE('2012-04-11', 'YYYY-MM-DD');

対策１ × Indexが利用できない

SELECT * FROM Table A

WHERE TO_CHAR(A.UPDATE_DATE, 'YYYY-MM-DD') >= '2012-04-11'

AND TO_CHAR(A.UPDATE_DATE, 'YYYY-MM-DD') <= '2012-04-11';

対策２ 〇

SELECT * FROM Table A

WHERE A.UPDATE_DATE >= TO_DATE('2012-04-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

AND A.UPDATE_DATE <= TO_DATE('2012-04-11 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

★SQL注意点

・WHERE文には、選択率の低い順から書く

・IN文には、選択率の高い順から書く