SQL DMLシ壼渕譛ャ謫堺ス(CRUD)

讎りヲ

DML

DataManipurationLanguage
Insert/Update/Delete遲峨ョ蜃ヲ逅

DML譁縺ァ繧ィ繝ゥ繝シ縺檎匱逕溘@縺溷エ蜷医ッ縺昴ョ蜃ヲ逅縺ョ縺ソ縺悟叙繧頑カ医&繧後
縺昴ョ蜑阪ョ蜃ヲ逅縺ッ菫晄戟縺輔l繧九やサ譁繝ャ繝吶Ν繝ュ繝シ繝ォ繝舌ャ繧ッ(證鈴サ吶ョ繧サ繝シ繝悶昴う繝ウ繝)

CRUD

Createシ扞nsert
Read=Select
Update
Delete

SELECT

窶サ繝輔ぅ繝シ繝ォ繝峨r髯仙ョ壹☆繧
諠蝣ア蜃ヲ逅雉譬シ逧縺ォ險縺縺ィ縲悟ー蠖ア縲

All/DISTINCT
SELECT
繝輔ぅ繝シ繝ォ繝牙錐1,
繝輔ぅ繝シ繝ォ繝牙錐2,
繝輔ぅ繝シ繝ォ繝牙錐3
FROM
[繝繝シ繝悶Ν蜷江
–蜈ィ繝輔ぅ繝シ繝ォ繝
SELECT
*
FROM
[繝繝シ繝悶Ν蜷江
DISTINCT
Select縺吶k繝ャ繧ウ繝シ繝峨r繝ヲ繝九シ繧ッ縺ェ繧ゅョ縺ォ髯仙ョ壹☆繧
驥崎、縺吶k繝ャ繧ウ繝シ繝峨rSelect縺励↑縺縲
(繝ヲ繝九シ繧ッ=荳諢鞘ヲ蜷後§繧ゅョ縺ョ縺ェ縺莠)
SELECT DISTINCT col1 FROM [繝繝シ繝悶Ν蜷江
All
謖螳壹@縺滓擅莉カ縺ョ蜈ィ繝ャ繧ウ繝シ繝峨rSelect縺吶k縲
逵∫払蜿ッ閭ス縲
SELECT All col1 FROM [繝繝シ繝悶Ν蜷江
SELECT col1 FROM [繝繝シ繝悶Ν蜷江縺ィ蜷後§
SELECT UNIQUE col1 FROM [繝繝シ繝悶Ν蜷江
SELECT DISTINCT col1 FROM [繝繝シ繝悶Ν蜷江縺ィ蜷後§

TOP ス

蜈磯ュス槭Ξ繧ウ繝シ繝峨ョ縺ソ蜿門セ励☆繧

5莉カ蜿門セ
SELECT * FROM 陦ィ蜷 WHERE ROWNUM <= 5
ROWNUM シ 蜀驛ィ縺ォ螳夂セゥ縺輔l縺溘ョ繧」繧ッ繧キ繝ァ繝翫Μ諠蝣ア
繧ス繝シ繝医@縺溽オ先棡縺九i蜿門セ励☆繧句エ蜷
SELECT * FROM ス
WHERE ROWNUM <= 5
ORDER BY ス

縺ァ縺ッテ
ROWNUM縺檎函謌舌&繧後※縺九i繧ス繝シ繝医&繧後k轤コ
竊薙′縲
SELECT * FROM
(
SELECT * FROM ス ORDER BY ス
縺薙%縺ァROWNUM縺檎函謌舌&繧後k
)
WHERE ROWNUM <= 5
SELECT * FROM [繝繝シ繝悶Ν蜷江
LIMIT 5

SELECT * FROM [繝繝シ繝悶Ν蜷江
LIMIT 5
5陦碁」帙ー縺吶6陦檎岼縺九i蜿門セ
OFSET 5

SELECT * FROM [繝繝シ繝悶Ν蜷江
LIMIT 5

SELECT * FROM [繝繝シ繝悶Ν蜷江
LIMIT 5
5陦碁」帙ー縺吶6陦檎岼縺九i蜿門セ
OFSET 5

SELECT
TOP 5
*
FROM
[繝繝シ繝悶Ν蜷江
ORDER BY

WHERE

窶サ繝ャ繧ウ繝シ繝峨r迚ケ螳壹ョ譚。莉カ縺ァ髯仙ョ壹☆繧九
諠蝣ア蜃ヲ逅雉譬シ逧縺ォ險縺縺ィ縲碁∈謚槭
SELECT 繝輔ぅ繝シ繝ォ繝牙錐 FROM [繝繝シ繝悶Ν蜷江
WHERE 繝輔ぅ繝シ繝ォ繝牙錐 = 蛟、
SELECT * FROM Table1
WHERE int = ‘1’

IN (ス,ス)

SELECT * FROM [繝繝シ繝悶Ν蜷江
WHERE 繝輔ぅ繝シ繝ォ繝牙錐 IN (1,5)
窶サス WHERE 繝輔ぅ繝シ繝ォ繝牙錐 = 1 OR 繝輔ぅ繝シ繝ォ繝牙錐 = 5 縺ィ蜷後§

BETWEEN

SELECT * FROM TEST_TBL
WHERE TEST_FLD BETWEEN 1 AND 10
WHERE 1 <= TEST_FLD AND TEST_FLD <= 10 縺ィ蜷後§
窶サBETWEEN蜿・縺ァ謖螳壹@縺滓焚蛟、縺ッ蜷ォ繧縲

ORDER BY

窶サ荳ヲ縺ウ譁ケ繧呈欠螳壹☆繧
縲梧鬆縲/縲碁剄鬆縲
SELECT * FROM ス ORDER BY ス ASC
SELECT * FROM ス ORDER BY ス DESC
ASC(譏鬆)縲‖scending縺ョ逡・シ壼ー鞘貞、ァ
DESC(髯埼)縲‥escending縺ョ逡・シ壼、ァ竊貞ー
ASC縲.ESC繧堤怐逡・縺吶k縺ィASC謇ア縺

謨ー蟄励↓繧医k鬆逶ョ謖螳
SELECT * FROM ス ORDER BY 1, 2
窶サ1縺、逶ョ縺ョSelect鬆逶ョ, 2縺、逶ョ縺ョSelect鬆逶ョ
Null蛟、縺ョ陦ィ遉コ鬆
ORDER BY ス ASC 竍 Null蛟、縺ッ譛蠕後↓
ORDER BY ス DESC 竍 Null蛟、縺ッ譛蛻昴↓
ORDER BY ス NULLS FIRST 竍 Null蛟、縺ッ譛蛻昴↓
ORDER BY ス NULLS LAST 竍 Null蛟、縺ッ譛蠕後↓
繝繝輔か繝ォ繝医ッNULLS LAST 窶サNULL縺ッ辟。髯仙、ァ謇ア縺

蛻怜挨蜷
繝サ螳夂セゥ譁ケ豕
SELECT 繝輔ぅ繝シ繝ォ繝牙錐1 蛻怜挨蜷1 FROM ス
SELECT 繝輔ぅ繝シ繝ォ繝牙錐1 AS 蛻怜挨蜷1 FROM ス
蟆乗枚蟄励r菴ソ縺縺溘>/蜻ス蜷崎ヲ丞援縺ォ蜿阪☆繧句挨蜷阪r螳夂セゥ縺吶k蝣エ蜷
SELECT 繝輔ぅ繝シ繝ォ繝牙錐1 “蛻怜挨 蜷1” FROM ス
SELECT 繝輔ぅ繝シ繝ォ繝牙錐1 “aaAAaaAA” FROM ス
竊貞ー乗枚蟄励→螟ァ譁蟄励′螳夂セゥ縺励◆縺セ縺セ陦ィ遉コ縺輔l繧
SELECT 繝輔ぅ繝シ繝ォ繝牙錐1 AS “999蛻怜挨蜷” FROM ス
繝サ螳夂セゥ蝣エ謇
SELECT 繝輔ぅ繝シ繝ォ繝牙錐1 蛻怜挨蜷1 FROM ス
SELECT 繝輔ぅ繝シ繝ォ繝牙錐1 蛻怜挨蜷1 FROM ス ORDER BY 蛻怜挨蜷1 = ス
繝サ豕ィ諢丈コ矩
蜈ィ縺ヲ螟ァ譁蟄励↓螟画鋤縺輔l縺ヲ陦ィ遉コ縺輔l繧九やサ”ス”縺ァ螳夂セゥ縺励◆蝣エ蜷医r髯、縺
‘ス’ 縺ァ縺ョ蛻怜挨蜷榊ョ夂セゥ縺ッテ
SUBSTR()遲峨髢「謨ー繧堤畑縺縺溷怜挨蜷榊ョ夂セゥ縺ッテ
WHERE蜿・縲;ROUP BY蜿・縲?AVING蜿・縺ァ縺ッ菴ソ逕ィ荳榊庄

蜿・縺ョ鬆蠎
SELECT FROM WHERE GROUP  HAVING ORDER
SELECT FROM WHERE HAVING GROUP  ORDER
GROUP HAVING 縺ッ蜈・繧梧崛縺亥庄

INSERT

繝輔ぅ繝シ繝ォ繝峨→蛟、繧呈欠螳壹@縺ヲ謖ソ蜈・

INSERT INTO
謖ソ蜈・蜈医ユ繝シ繝悶Ν蜷(繝輔ぅ繝シ繝ォ繝牙錐シ, 繝輔ぅ繝シ繝ォ繝牙錐シ, 繝輔ぅ繝シ繝ォ繝牙錐シ)
VALUES(蛟、シ, 蛟、シ, 蛟、シ)
INSERT INTO Table1 (int, alf, kana) VALUES (‘5’, ‘b’, ‘縺’)
窶サ謖ソ蜈・蜈医ヵ繧」繝シ繝ォ繝峨↓荳崎カウ縺後≠縺」縺溷エ蜷医√◎縺ョ繝輔ぅ繝シ繝ォ繝峨ッNULL蛟、縺ィ縺ェ繧九
INSERT INTO Table1 (int, kana) VALUES (‘5’, ‘縺’)
窶サalf蛻励↓(NULL)縺梧諺蜈・縺輔l繧
INSERT INTO Table1 (int, alf, kana) VALUES (‘5’, (NULL), ‘縺’)
INSERT INTO Table1 (int, alf, kana) VALUES (‘5’, DEFAULT, ‘縺’)
繧ょ酔縺
SELECT邨先棡(蜑ッ蝠上>蜷医o縺)繧呈諺蜈・
SQL DMLシ壼憶蝠上>蜷医o縺/蜑ッ蝠上>蜷医o縺帷オ先棡繧堤畑縺縺溘ョ繝シ繧ソ謫堺ス縲榊盾辣ァ

蛟、縺縺第欠螳壹@縺ヲ謖ソ蜈・

INSERT INTO
謖ソ蜈・蜈医ユ繝シ繝悶Ν蜷
VALUES(蛟、シ, 蛟、シ, 蛟、シ)
INSERT INTO Table1 VALUES (‘5’, ‘b’, ‘縺’)
窶サ謖ソ蜈・蜈医ヵ繧」繝シ繝ォ繝峨ッ蜈ィ謖螳壹→縺縺諢丞袖縺ァ荳崎カウ縺ッ隱阪a繧峨l縺ェ縺縲
謖ソ蜈・縺吶k蛟、縺ョ蛟区焚縺ッ謖ソ蜈・蜈医ユ繝シ繝悶Ν縺ョ繝輔ぅ繝シ繝ォ繝画焚縺ィ荳閾エ縺吶k蠢隕√′縺ゅk縲

UPDATE

窶サ繝ャ繧ウ繝シ繝峨ョ縺縺。縲∵欠螳壹@縺溘ヵ繧」繝シ繝ォ繝峨r縺ョ蛟、繧貞、画峩縺吶k

蛟、繧呈欠螳壹@縺ヲ譖エ譁ー
UPDATE [繝繝シ繝悶Ν蜷江 SET 繝輔ぅ繝シ繝ォ繝牙錐 = 蛟、 WHERE ス
UPDATE Table1 SET int = ’10’ WHERE ス;
UPDATE Table1 SET int = ’10’ , kana = ‘縺’ WHERE ス
蛻怜錐 蛻輸 蛻唯 蛻佑(JSONB蝙)
A蛻 B蛻
蛟、 1 aaa ‘{“A蛻”:1, “B蛻”:”AAA”}’
蛟、 2 bbb ‘{“A蛻”:2, “B蛻”:”BBB”}’

update 陦ィ蜷 set C蛻 = jsonb_set( C蛻, ‘{“B蛻”}’, ‘”XXX”‘) where C蛻->’B蛻’ = ‘”BBB”‘;

蛻怜錐 蛻輸 蛻唯 蛻佑(JSONB蝙)
A蛻 B蛻
蛟、 1 aaa ‘{“A蛻”:1, “B蛻”:”AAA”}’
蛟、 2 bbb ‘{“A蛻”:2, “B蛻”:”XXX”}’

ORDER BY繧剃スソ逕ィ縺励※譚。莉カ繧呈欠螳
繝サORDER BY縺ァ繧ス繝シ繝
繝サROWNUM <= 1 縺ァ1莉カ縺ョ縺ソ謖螳
縺ィ縺縺譚。莉カ縺ァ縺ョUPDATE縺ッ縺ァ縺阪↑縺
竊単L/SQL縺ァ荳サ繧ュ繝シ繧貞叙蠕励@縲∽クサ繧ュ繝シ繧呈擅莉カ縺ォ菴ソ逕ィ縺吶k
SQL DMLシ壼宛蠕。譁/SELECT譁縺九i蜿門セ縲榊盾辣ァ

SELECT邨先棡(蜑ッ蝠上>蜷医o縺)繧呈峩譁ー

SQL DMLシ壼憶蝠上>蜷医o縺/蜑ッ蝠上>蜷医o縺帷オ先棡繧堤畑縺縺溘ョ繝シ繧ソ謫堺ス縲榊盾辣ァ

邨仙粋縺励◆蛻励r譖エ譁ー

SQL DMLシ夂オ仙粋(JOIN)/邨仙粋縺励◆蛻励r譖エ譁ー縲榊盾辣ァ

MERGE

繝繝シ繧ソ縺梧怏繧後ーUPDATE縲∫┌縺代l縺ーINSERT

MERGE INTO MERGE蜈郁。ィ蜷 USING MERGE蜈陦ィ蜷
ON 譚。莉カ
WHEN MATCHD THEN
UPDATE 譁
WHEN NOT MATCHED THEN
INSERT 譁
MERGE INTO MERGE蜈郁。ィ蜷 USING MERGE蜈陦ィ蜷
ON (
蜈.蛻怜錐1 = 蜈.蛻怜錐1 AND
蜈.蛻怜錐2 = 蜈.蛻怜錐2
)
WHEN MATCHD THEN
UPDATE SET
蜈.蛻怜錐3 = 蜈.蛻怜錐3,
蜈.蛻怜錐4 = 蜈.蛻怜錐4
WHEN NOT MATCHED THEN
INSERT (蛻怜錐1, 蛻怜錐2, 蛻怜錐3, 蛻怜錐4)
VALUES(蜈.蛻怜錐1, 蜈.蛻怜錐2, 蜈.蛻怜錐3, 蜈.蛻怜錐4)
;
蜈.蛻怜錐1 = 蜈.蛻怜錐1 AND 蜈.蛻怜錐2 = 蜈.蛻怜錐2縺ョ繝繝シ繧ソ縺梧怏繧後ー縲
蜈.蛻怜錐3 = 蜈.蛻怜錐3,
蜈.蛻怜錐4 = 蜈.蛻怜錐4
縺ョUPDATE縺悟ョ溯。後&繧後∫┌縺代l縺ー縲
INSERT (蛻怜錐1, 蛻怜錐2, 蛻怜錐3, 蛻怜錐4) INTO MERGE蜈郁。ィ蜷 VALUES(蜈.蛻怜錐1, 蜈.蛻怜錐2, 蜈.蛻怜錐3, 蜈.蛻怜錐4)
縺ョINSERT縺悟ョ溯。後&繧後k

INSERT蜈医ョ蛻怜錐繧堤怐逡・(蜈ィ蛻玲諺蜈・)
MERGE INTO MERGE蜈郁。ィ蜷 USING MERGE蜈陦ィ蜷 ON ( ス )
WHEN MATCHD THEN UPDATE SET ス
WHEN NOT MATCHED THEN
INSERT
VALUES(
蜈.蛻怜錐1,
蜈.蛻怜錐2
)
;
MERGE蜈縺ォ蜑ッ蝠上>蜷医o縺帙r謖螳
MERGE INTO MERGE蜈郁。ィ蜷 USING (SELECT ス FROM ス) ON ( ス )
WHEN MATCHD THEN UPDATE SET ス
WHEN NOT MATCHED THEN INSERT ス;

DELETE

窶サ繝ャ繧ウ繝シ繝峨r蜑企勁縺吶k
DELETE [繝繝シ繝悶Ν蜷江 WHERE ス
DELETE FROM [繝繝シ繝悶Ν蜷江 WHERE ス
DELETE FROM Table1 SELECT * WHERE kana = ‘縺’ OR alf = ‘c’
テ暦シ咼ELETE * [繝繝シ繝悶Ν蜷江
テ暦シ咼ELETE * FROM [繝繝シ繝悶Ν蜷江

SELECT邨先棡(蜑ッ蝠上>蜷医o縺)繧呈擅莉カ縺ォ蜑企勁

SQL DMLシ壼憶蝠上>蜷医o縺/蜑ッ蝠上>蜷医o縺帷オ先棡繧堤畑縺縺溘ョ繝シ繧ソ謫堺ス縲榊盾辣ァ

邨仙粋縺励◆蛻励r蜑企勁

SQL DMLシ夂オ仙粋(JOIN)/邨仙粋縺励◆蛻励r譚。莉カ縺ォ蜑企勁縲榊盾辣ァ

繝舌ャ繧ッ繧「繝繝

BACKUP DATABASE [DBNM]
TO DISK = ‘C:\test.bak’
TO TAPE = ‘ス’
WITH
蟾ョ蛻繝舌ャ繧ッ繧「繝繝(譛ェ謖螳壽凾シ壼ョ悟ィ繝舌ャ繧ッ繧「繝繝)
DIFFERENTIAL,
繝舌ャ繧ッ繧「繝繝励そ繝繝亥錐(竕繝輔ぃ繧、繝ォ蜷)縲よ悴謖螳壼庄縲
NAME = N’DBNM-螳悟ィ 繝繝シ繧ソ繝吶シ繧ケ 繝舌ャ繧ッ繧「繝繝’,
隱ャ譏取枚
DESCRIPTION = N’ス’,
繝舌ャ繧ッ繧「繝繝励ヵ繧。繧、繝ォ縺ョ荳頑嶌縺(隕丞ョ夲シ哢OFORMAT)
NOFORMAT | FORMAT,
繝舌ャ繧ッ繧「繝繝励ヵ繧。繧、繝ォ縺ョ荳頑嶌縺(隕丞ョ夲シ哢OINIT)
NOINIT | INIT ,
荳頑嶌縺肴凾縺ョ遒コ隱肴怏辟。(隕丞ョ夲シ哢OSKIP)
NOSKIP | SKIP,
繝繝シ繝励↓繝舌ャ繧ッ繧「繝繝励☆繧矩圀縺ォ蟾サ縺肴綾縺
REWIND | NOREWIND(譌「螳壼、シ啌EWIND),
繝舌ャ繧ッ繧「繝繝怜ョ御コ蠕後√ユ繝シ繝励ョ蟾サ縺肴綾縺励♀繧医ウ繧「繝ウ繝ュ繝シ繝峨r陦後≧(隕丞ョ夲シ啅NLOAD)
UNLOAD | NOUNLOAD,
謖螳壹@縺滓焚蟄怜牡蜷医′螳御コ縺吶k豈弱↓繝。繝繧サ繝シ繧ク繧定。ィ遉コ
STATS = 10

Follow me!