SQL DMLシ壼憶蝠上>蜷医o縺

讎りヲ

迚ゥ逅逧縺ォ螳溷惠縺吶k陦ィ縲∬。後∝励〒縺ェ縺上∬ォ也炊逧縺ォ菴懈舌@縺溯。ィ縲∬。後∝励r蛻ゥ逕ィ縺吶k蝠上>蜷医o縺帙
荳榊ョ壹ョ蛟、縺ォ蝓コ縺・縺縺ヲ繝繝シ繧ソ繧呈、懃エ「縺ァ縺阪k縲

蜑ッ蝠上>蜷医o縺帛庄閭ス縺ェ縲悟唱縲

繝サSELECT譁
SELECT
縲(SELECT 蛻怜錐 FROM ス)
FROM ス

繝サFROM蜿・
SELECT
縲蛻怜錐1 , 蛻怜錐2
FROM
縲(SELECT 蛻怜錐1 , 蛻怜錐2 FROM ス)

繝サWHERE蜿・
SELECT ス FROM ス
WHERE
縲(SELECT 蛻怜錐1 FROM ス) = ス

繝サHAVING
SELECT ス FROM ス GROUP BY ス
HAVING
縲(SELECT ス FROM ス) = ス

SELECT蜿・縲:ROM蜿・縲仝HERE蜿・縲^RDER BY蜿・縺ァ蛻ゥ逕ィ蜿ッ閭ス

SELECT
縲縲int,
縲縲alf,
縲縲kana,
縲縲( SELECT MAX(int) FROM [Table1] ) AS MaxInt
FROM
縲縲[Table1]

繝繝シ繝悶Νシ啜able1シ2
int alf kana MaxInt
Record1 1 a 縺 6
Record2 2 b 縺 6
Record3 3 c 縺 6
Record4 4 縺 6
Record5 5 縺 6
Record6 6 縺 6

蜑ッ蝠上>蜷医o縺帷オ先棡繧堤畑縺縺溘ョ繝シ繧ソ謫堺ス

縲蝕NSERT譁縲
INSERT INTO 陦ィ蜷 SELECT ス FROM ス
蜑ッ蝠上>蜷医o縺帙〒縺ッINSERT蜈郁。ィ縺ョ蜈ィ蛻励r謖螳壹☆繧句ソ隕√≠繧

縲振PDATE譁縲
UPDATE 陦ィ蜷 SET (蛻怜錐1 , 蛻怜錐2) = (SELECT 蛻怜錐1 , 蛻怜錐2 FROM ス WHERE ス) WHERE ス

縲織ELETE譁縲
DELETE FROM 陦ィ蜷 (蛻怜錐1 , 蛻怜錐2) IN (SELECT 蛻怜錐1 , 蛻怜錐2 FROM ス WHERE ス)
DELETE FROM 陦ィ蜷 (蛻怜錐1 , 蛻怜錐2) ALL (SELECT 蛻怜錐1 , 蛻怜錐2 FROM ス WHERE ス)

縲燭REATE譁縲
CREATE TABLE 陦ィ蜷 AS SELECT ス FROM ス
CREATE VIEW 陦ィ蜷 AS SELECT ス FROM ス

縲振PDATE譁縲
譖エ譁ー蜈医→繧オ繝悶け繧ィ繝ェ縺悟酔縺倥ユ繝シ繝悶Ν縺ョ蝣エ蜷

UPDATE 陦ィ蜷 SET 蛻怜錐1 = (SELECT 蛻怜錐1 FROM ス WHERE ス) WHERE ス


UPDATE 陦ィ蜷, (SELECT sub蛻怜錐 FROM ス WHERE ス) AS sub
SET 蛻怜錐1 = sub.sub蛻怜錐
WHERE ス

蜊倅ク陦(蛻)蝠上>蜷医o縺

譛鬮倡オヲ荳朱。榊叙蠕苓繧貞撫縺蜷医o縺帙k
SELECT temp.豌丞錐
FROM 邨ヲ荳
WHERE 邨ヲ荳.邨ヲ荳朱。 = (SELECT MAX(邨ヲ荳朱。) FROM 邨ヲ荳)
窶サ蜑ッ蝠上>蜷医o縺帷オ先棡縺瑚、謨ー陦後≠繧区ァ倥↑蝠上>蜷医o縺帙ッ繧ィ繝ゥ繝シ
SELECT temp.豌丞錐
FROM 邨ヲ荳
WHERE 邨ヲ荳.邨ヲ荳朱。 = (SELECT 邨ヲ荳朱。 FROM 邨ヲ荳 WHERE 邨ヲ荳 > 500,000)

隍謨ー陦(蛻)蝠上>蜷医o縺

迚ケ螳壹ョ驛ィ髢縺ォ謇螻槭☆繧狗、セ蜩。繧貞撫縺蜷医o縺帙k

IN蜿・

SELECT * FROM 遉セ蜩。
WHERE 閨キ蜍 IN (SELECT 閨キ蜍 FROM 遉セ蜩。 WHERE 驛ィ髢 = 10)

ANY蜿・

繝ェ繧ケ繝医ョ譛蟆丞、繧医j螟ァ縺阪>
SELECT * FROM 遉セ蜩。
WHERE 邨ヲ荳 >= ANY (SELECT 邨ヲ荳 FROM 遉セ蜩。 WHERE 驛ィ髢 = 10)

繝ェ繧ケ繝医ョ譛螟ァ蛟、繧医j蟆上&縺
SELECT * FROM 遉セ蜩。
WHERE 邨ヲ荳 <= ANY (SELECT 邨ヲ荳 FROM 遉セ蜩。 WHERE 驛ィ髢 = 10)

繝ェ繧ケ繝医ョ縺縺壹l縺九r蜷ォ繧
SELECT * FROM 遉セ蜩。
WHERE 閨キ蜍 = ANY (SELECT 閨キ蜍 FROM 遉セ蜩。 WHERE 驛ィ髢 = 10)
WHERE 閨キ蜍 IN (SELECT 閨キ蜍 FROM 遉セ蜩。 WHERE 驛ィ髢 = 10) 縺ィ蜷後§

蜷ヲ螳
WHERE ス NOT > ANY (ス) シ WHERE ス <= ALL (ス) WHERE ス NOT < ANY (ス) シ WHERE ス >= ALL (ス)

ALL蜿・

繝ェ繧ケ繝医ョ譛螟ァ蛟、繧医j螟ァ縺阪>
SELECT * FROM 遉セ蜩。
WHERE 邨ヲ荳 >= ALL (SELECT 邨ヲ荳 FROM 遉セ蜩。 WHERE 驛ィ髢 = 10)

繝ェ繧ケ繝医ョ譛蟆丞、繧医j蟆上&縺
SELECT * FROM 遉セ蜩。
WHERE 邨ヲ荳 <= ALL (SELECT 邨ヲ荳 FROM 遉セ蜩。 WHERE 驛ィ髢 = 10)

NOT IN (!= ALL)

蛻1縲蛻2
1縲縲A
2縲縲B
3縲縲NULL

SELECT 蛻1 FROM 陦ィ WHERE 蛻2 IS NULL
竊 3

SELECT 蛻1 FROM 陦ィ WHERE 蛻2 IN (NULL)
竊 NULL (隧イ蠖鍋┌縺)

SELECT 蛻1 FROM 陦ィ WHERE 蛻2 IN (SELECT 蛻2 FROM 陦ィ)
SELECT 蛻1 FROM 陦ィ WHERE 蛻2 IN ('A', 'B', NULL)
竊 1
竊 1

SELECT 蛻1 FROM 陦ィ WHERE 蛻2 NOT IN ('A', 'B', NULL)
SELECT 蛻1 FROM 陦ィ WHERE 蛻2 != ALL (SELECT 蛻2 FROM 陦ィ)
SELECT 蛻1 FROM 陦ィ WHERE 蛻2 != ALL ('A', 'B', NULL)
竊 NULL (隧イ蠖鍋┌縺)

逶ク髢「蜑ッ蝠丞粋縺(EXISTS/NOT EXISTS)

縲職XISTS縲
SELECT * FROM 蝠蜩`
WHERE EXISTS (
縲SELECT 螢イ荳蟹D FROM 螢イ荳 WHERE 螢イ荳.螢イ荳蟹D = 蝠蜩`.蝠蜩!D
);



竊鍋オ先棡

繝輔Ο繝シ
1)荳サ蝠上>蜷医o縺(SELECT * FROM 蝠蜩`) 繧貞ョ溯。
2)1縺ョ邨先棡繧偵き繝シ繧ス繝ォ縺ョ讒倥↓菴ソ逕ィ縺励※蜈ィ莉カLoop縲ょシ代Ξ繧ウ繝シ繝牙ッセ縺励※縲
縲蜑ッ蝠上>蜷医o縺(SELECT 螢イ荳蟹D FROM 螢イ荳 WHERE 螢イ荳.螢イ荳蟹D = 蝠蜩`.蝠蜩!D) 繧貞ョ溯。
3)2縺ョ邨先棡繧貞愛螳

繝輔Ο繝シ隱ャ譏
1)荳サ蝠上>蜷医o縺(SELECT * FROM 蝠蜩`)繧貞ョ溯。

2) 1繝ャ繧ウ繝シ繝臥岼シ荳サ蝠上>蜷医o縺帙ョ1繝ャ繧ウ繝シ繝臥岼縺ォ蟇セ縺励※縲∝憶蝠上>蜷医o縺
(SELECT 螢イ荳蟹D FROM 螢イ荳 WHERE 螢イ荳.螢イ荳蟹D = 蝠蜩`.蝠蜩!D)繧貞ョ溯。
窶サ1繝ャ繧ウ繝シ繝臥岼縺ョ蝣エ蜷医ッ縲∝膚蜩`.蝠蜩!D縺ッ'1'

3) 1繝ャ繧ウ繝シ繝臥岼シ2縺ョ邨先棡繧貞愛螳

竊2縺ョ蜑ッ蝠上>蜷医o縺帷オ先棡縺ッ縲√檎悄縲阪〒縺ゅk轤コ縲∫オ先棡縲∽クサ蝠上>蜷医o縺帙ョ1繝ャ繧ウ繝シ繝臥岼縺ッSELECT縺輔l繧

2) 2繝ャ繧ウ繝シ繝臥岼シ荳サ蝠上>蜷医o縺帙ョ2繝ャ繧ウ繝シ繝臥岼縺ォ蟇セ縺励※縲∝憶蝠上>蜷医o縺
(SELECT 螢イ荳蟹D FROM 螢イ荳 WHERE 螢イ荳.螢イ荳蟹D = 蝠蜩`.蝠蜩!D)繧貞ョ溯。
窶サ2繝ャ繧ウ繝シ繝臥岼縺ョ蝣エ蜷医ッ縲∝膚蜩`.蝠蜩!D縺ッ'2'

3) 2繝ャ繧ウ繝シ繝臥岼シ2縺ョ邨先棡繧貞愛螳

竊2縺ョ蜑ッ蝠上>蜷医o縺帷オ先棡縺ッ縲√悟⊃縲阪〒縺ゅk轤コ縲∫オ先棡縲∽クサ蝠上>蜷医o縺帙ョ2繝ャ繧ウ繝シ繝臥岼縺ッSELECT縺輔l縺ェ縺

縲侵OT EXISTS縲
SELECT * FROM 蝠蜩`
WHERE NOT EXISTS (
縲SELECT 螢イ荳蟹D FROM 螢イ荳 WHERE 螢イ荳.螢イ荳蟹D = 蝠蜩`.蝠蜩!D
);



竊鍋オ先棡

蜈ア騾壹ユ繝シ繝悶Ν

竕荳譎ゅユ繝シ繝悶Ν
Create/Drop縺ッ荳崎ヲ
竕谷oin

WITH CommonTableA AS
(
縲縲SELECT
縲縲縲縲FieldA,
縲縲縲縲FieldB,
縲縲縲縲FieldC
縲縲FROM
縲縲縲縲TestTableA
),
CommonTableB AS
(
縲縲SELECT
縲縲縲縲FieldA,
縲縲縲縲FieldB,
縲縲縲縲FieldC
縲縲FROM
縲縲縲縲TestTableB
)

SELECT
縲縲CommonTableA.FieldA,
縲縲CommonTableA.FieldB,
縲縲(SELECT FieldC FROM CommonTableB WHERE FieldA = CommonTableA.FieldA) AS FieldC
FROM
縲縲CommonTableA

;
WITH CommonTableA AS
(
縲縲SELECT
縲縲縲縲FieldA,
縲縲縲縲FieldB,
縲縲縲縲FieldC
縲縲FROM
縲縲縲縲TestTableA
),
CommonTableB AS
(
縲縲SELECT
縲縲縲縲FieldA,
縲縲縲縲FieldB,
縲縲縲縲FieldC
縲縲FROM
縲縲縲縲TestTableB
)

SELECT
縲縲CommonTableA.FieldA,
縲縲CommonTableA.FieldB,
縲縲(SELECT FieldC FROM CommonTableB WHERE FieldA = CommonTableA.FieldA) AS FieldC
FROM
縲縲CommonTableA

Follow me!