SQLの基本のき(備忘録)

⚫︎文法

SELECT

select 行の名前 from テーブル名

⚫︎ SELECTのオプション群

• AS・・・as により別名を定義できる (oracleDBは不可)

例）

SELECT 費目 AS ITEM,

UPDATE

UPDATE テーブル名 
SET 列名 = 値,列名 = 値 
WHERE 条件

DELETE

DELETE FROM テーブル名 WHERE 条件

INSERT

INSERT INTO テーブル名
(列名1,列名2,列名3) 
VALUE (’値1’, ’値2’, ‘値3’)

上記の４つのSQL 命令をDML(Data Manipulation Language)という。

命令	各命令の固有	絞り込み	検索結果の加工
SELECT	列名••• FROM テーブル名	WHERE	その他修飾
UPDATE	テーブル名 SET 列名 = 値	WHERE
DELETE	FROM テーブル名	WHERE
INSERT	INTO テーブル名(列名) VALUE(値)

⚫︎WHERE

DBMSはwhereで絞り込んだ値を一行ずつ該当するかチェックを行いながら

操作を行う。

そのため、where句では結果が必ずtrue,falseのどちらかになるものしか書けない

DELETE FROM 家計簿 WHERE 出勤額 > 0

⚫︎比較演算子

比較演算子	意味
=	左右が等しい
>	左は右より小さい
<	左は右より大きい
<=	左は右　以下
>=	左は右　以上
<>	左右が等しくない

⚫︎NULL

①IS NULL

②IS NOT NULL

例）

SELECT *
FROM 家計簿
WHERE 出金額 IS NULL

• NULLの意味

①格納すべきデータが不明な状態 (何を登録すべきか不明な場合など)

②データの格納自体が無意味な状態 (出金の操作しか行っていない場合、入金の欄に何かを格納することはそもそも無意味)

⚫︎LIKE

パターンマッチングにLIKEを使う。

パターンマッチング・・・文字列が、あるパターンに合致しているのかを判定する。

LIKE演算子に使える記号

• %　任意の0文字以上の文字列
• _　任意の1文字

例）

SELECT * FROM 家計簿
WHERE メモ LIKE ‘%1月%’

1月の前後に0文字以上の文字がついている

%1月・・・1月で終わる。

1月_・・・1月で始まりその後ろに任意の文字列を意味する。

※記号を含む文字を検索したい時

ESCAPE句で指定した文字に続く%や_はパターン文字ではなくただの文字として扱われる。

SELECT * FROM テーブル名 
WHERE カラム名 
LIKE ‘%100$%’ ESCAPE ‘$’

⚫︎BETWEEN

「値１」以上〜 「値２」以下の場合真になる

SELECT *
FROM テーブル名
WHERE カラム名 BETWEEN 値１ AND 値２

⚫︎IN

括弧内に列挙した複数の値のうち、いずれかが一致するかを判定する。

SELECT *
FROM テーブル名
WHERE カラム名 IN ('値１','値２')

NOT IN

どれも一致しないことを判定

SELECT *
FROM テーブル名
WHERE カラム名 NOT IN ('値１','値２')

⚫︎ANY / ALL

ANY

値と比較していずれかが真なら真

SELECT * FROM テーブル名 基本比較演算子 ANY (値１、値２、値３)

ALL

値と比較して全てが真なら真

SELECT * FROM テーブル名 基本比較演算子 ALL (値１、値２、値３)

⚫︎論理演算子

⚫︎AND / OR

UPDATE 
SET 価格 = 6200
WHERE 名称 = 'スッキリ勇者クエスト' 
AND 
販売店 = 'B'

⚫︎NOT演算子

NOT演算子は真偽値の逆転を起こす。

WHERE NOT 販売店 = 'B'

販売店がB以外の行

WHERE 販売店 <> ‘B’　と同意

⚫︎論理演算子の優先度

NOT→AND→OR

（高→低）

⚫︎複数条件の検索の仕方

例)「販売店AかBで売っている、ゲームかDVD」を検索したいとする

※問題のあるコード

SELECT *
FROM テーブル名
where 
販売店 = 'A'    　　 --条件式1
OR
販売店 = 'B'　　　　  --条件式2
AND
カテゴリ = 'ゲーム'　 --条件式3
OR
カテゴリ = 'DVD'　　 --条件式4

この場合ANDが優先されるため、

条件式2と条件式3が優先的に処理される。

欲しかった結果とは違う答えになってしまう。

正しいコード

SELECT *
FROM テーブル名
where 
(販売店 = 'A' 
OR
販売店 = 'B') 
AND
(カテゴリ = 'ゲーム'
OR
カテゴリ = 'DVD') 

このようにカッコは優先順位を引き上げることになる。

カッコでくくることによって求めていた答えが得られる。

主キーと複合主キー

主キー(PRIMARY KEY)

• 自然キー(natural key)・・・社員番号など自然に発生した一意の値
• 人工キー(artificial key)、代替キー(surrogate key)・・・管理目的のためだけに割り振られたID

複合主キー(composite key)

単独の列だけでは重複があり判別できないが、複数のerつを組み合わせれば重複の可能性はなくなる。

このように複数の列を１つの主キーとするものを複合主キーと呼ぶ。

⚫︎書き方

• 整形されたSQL 文を書こう

❌

SELECT 費用, 出金額 FROM 家計簿 WHERE 出金額 > 3000

⭕️

SELECT 費用, 出金額

FROM 家計簿

WHERE 出金額 > 3000

• コメントの記述方法

① - - 行末までをコメント

② /* */　内部をコメント

• リテラル

SQL 文に書かれた具体的なデータ

例）INSERT INTO ‘家計簿’ VALUE(’2024-02-05’, ‘居住費’, 0,)

ここでいうvalueの値がリテラルに当たる。

リテラルのルール

①シングルクォーテーションで括らず記述されたリテラルは、数値情報として扱われる。

②シングルクォーテーションで括られたリテラルは、基本的に文字列情報として扱われる。

③シングルクォーテーションで括られた、かつ’2024-02-25’のような形式のリテラルは日付情報として扱われる。

• データ型

データ種別	区分	代表的なデータ型
数値	整数	INTEGER , INT
	小数	DECIMAL, NUMERIC, FLOAT, DOUBLE, REAL
文字列	固定長型	CHAR
	可変長型	VARCHAR
日付と時刻		TIMESTAMP, DATETIME, DATE, TIME

補足：CHARとVARCHAR

CHAR(固定長型)・・・CHAR(10)の場合、10バイト満たない文字は後ろに空白を追加することで10

バイトぴったりにしてから格納される。郵便番号など桁数が一定のものに使う。

VARCHAER(可変長型)・・・VARCHAER(10)の場合、7バイトなら7バイトのまま、3バイトなら3バイトのまま格納される。氏名や書籍名など、桁数が一定でないものに使う。

集合演算子

SELECTによって抽出した結果を１つのデータの集合と捉え、
その結果同士を足し合わせたり共通部分を探したりする。

※集合演算子が使える条件
SELECTで結果をまとめるときは、選択した列数とそれぞれのデータ型が一致していなければならない。

列数が一致しないselect文を繋げるには列数が少ない方に、nullやリテラルを用いることで列の数を合わせる。

⚫︎UNION(和集合)

SELECT 列名, 列名, FROM テーブル名 
UNION (ALL)
SELECT 列名, 列名, FROM テーブル名 

• UNION の場合

  ・重複行を一行にまとめて出力

• UNION ALL の場合

  ・重複業をまとめずに全てそのまま出力

⚫︎EXCEPT / MINUS(差集合)

最初の検索結果から次の検索結果と重複する部分を取り除いたもの

※文A EXCEPT 文B 　と　文B EXCEPT 文A　では結果が異なる点に注意

SELECT 列名, 列名, FROM テーブル名 
EXCEPT (ALL)
SELECT 列名, 列名, FROM テーブル名 

• EXCAPT の場合

  ・重複行を一行にまとめて出力

• EXCEPT ALL の場合

  ・重複業をまとめずに全てそのまま出力

使用例・・・先月のデータには存在せず、今月のデータには存在する項目を取得する。

⚫︎INTERSECT(積集合)

SELECT 列名, 列名, FROM テーブル名 
INTERSECT (ALL)
SELECT 列名, 列名, FROM テーブル名 

• INTERSECT の場合

  ・重複行を一行にまとめて出力

• INTERSECT ALL の場合

  ・重複業をまとめずに全てそのまま出力

SQLで修飾可能な演算子

SELECTだけで使用可能な修飾

⚫︎DESTINCT

検索結果から重複行を削除

SELECT DISTINCT 列名・・・
FROM テーブル名

⚫︎ORDER BY

検索結果の順序を並び替える

SELECT 列名・・・ 
FROM テーブル名
ORDER BY 列名　並び順

ORDER BY 列名　の場合、昇順に表示する。

ORDER BY 日付　の場合、降順に表示する。

• 複数

SELECT 列名・・・ 
FROM テーブル名
ORDER BY 列名　並び順, 列名 並び順 

⚫︎OFSET - FETCH

取得行数の限定

※MYSQL,MariaDB(10.6未満),SQLiteではサポートされていない

SELECT 列名・・・ 
FROM テーブル名
ORDER BY 列名　並び順
OFFSET 先頭から除外する行数 ROWS
FETCH NEXT 取得行数 ROWS ONLY   

取得行数を限定する別の方法

⚫︎TOP(SQL Serverのみ)

SELECT TOP(3) 費目, 出金額
FROM 家計簿 ORDER BY 出金額 DESC

条件分岐

⚫︎CASE

CASE 評価する列や式 WHEN 値１(条件式、列名) THEN　値１の時に返す値
	WHEN 値２(条件式、列名) THEN 値２の時に返す値
	ELSE デフォルト値
END

例

SELECT カラム名1, カラム名2
CASE WHEN カラム名1 < 5000 THEN 'お小遣い'
		 WHEN カラム名1 < 10000 THEN '一時収入'
		 WHEN カラム名1 < 30000 THEN '給料出た！'
		 ELSE '想定外の収入です!'
END AS 収入の分類
FROM テーブル名
WHERE カラム名1 > 0

文字列関数

⚫︎LENGTH / LEN - 文字列の長さを取得

構文

LENGTH(文字列を表す列)

例

SELECT カラム名,LENGTH(カラム名) AS メモの長さ FROM 家計簿
WHERE LENGTH(カラム名) <= 10

⚫︎TRIM - 空白を除去

構文

TRIM(文字列を表す列) FROM テーブル名

例

SELECT カラム名,TRIM(カラム名) AS 空白除去したメモ 
FROM テーブル名

⚫︎REPLACE - 指定文字を置換

構文

REPLACE(置換対象の文字列,置換前の部分文字列,置換後の部分文字列)

例

UPDATE テーブル名
	SET カラム名 = REPLACE(カラム名,'購入','買った')

⚫︎SUBSTRING / SUBSTR - 文字列の一部を抽出

構文

SUBSTRING(文字列を表す列,抽出を開始する位置,抽出する文字の数)
SUBSTR(文字列を表す列,抽出を開始する位置,抽出する文字の数)

例

SELECT * FROM テーブル名
WHERE SUBSTRING(カラム名, 1, 3) LIKE '%費%'

⚫︎CONTACT - 文字列を連結

構文

CONCAT(文字列,文字列,[, 文字列])

例

SELECT CONCAT(カラム名,':' || カラム名) FROM テーブル名

数値関数

⚫︎ROUND - 指定した桁数で四捨五入

構文

ROUND(数値を表す列,有効とする桁数)

SELECT カラム名, ROUND(カラム名,-2) FROM　テーブル名

⚫︎TRUNC - 指定した桁数で切り捨て

構文

TRUNC(数値を表す列,有効とする桁数)

例

SELECT カラム名, ROUND(カラム名,-2) FROM　テーブル名

⚫︎POWER - べき乗を計算

構文

POWER(数値を表す列,何乗するか数値を入力)

例

SELECT カラム名, POWER(カラム名,3) FROM　テーブル名
//カラムを三乗する

時間関数

⚫︎CURRENT - 現在時刻を取得

構文

CURRENT_TIMESTAMP → 現在の日付(年, 月, 日, 時, 分, 秒)
CURRENT_DATE 　　　→ 現在の日付(年, 月, 日)
CURRENT_TIME　　　 → 現在の日付(時, 分, 秒)

例

INSERT INTO 家計簿 
VALUE (CURRENT_TIMESTAMP, '食費', 'ドーナッツを買った',0,260)

変換関数(型変換、null値の変換)

⚫︎CAST - データ型変換

構文

CAST(変換する値 AS 変換する型)

例

SELECT CAST(カラム名 AS INTEGER)FROM テーブル名

⚫︎COALECE - 最初に現れるNULLでない値を返す

構文

COALESE(列や式1,列や式２,列や式3,)

例