SQL修行vol.1
『これならわかるSQL入門の入門』
4月からSQLを仕事で使うことになったので、2年ぶりにこの本を手に取った。
2年前に読んだ時は1週間くらいかかったのに、今回は1日半で読み終えて自分自身の成長を感じた。
その中で今後使いそうと思った関数を自分の辞書的にまとめておこうと思う。
データベース
--作成
CREATE DATABASE databaseName
--確認
SHOW DATABASES
--削除
DROP DATABASE databaseName
--選択
USE databaseName
テーブル
基本的なやつ
--作成
CREATE TABLE tableName (
fieldName1 dataType1,
fieldName2 dataType2,
...
)
--NOT NULL制約
CREATE TABLE tableName (
fieldName1 dataType1 NOT NULL,
fieldName2 dataType2,
...
)
--UNIQUE制約
CREATE TABLE tableName (
fieldName1 dataType1 UNIQUE,
fieldName2 dataType2,
...
)
--デフォルト値設定
CREATE TABLE tableName (
fieldName1 dataType1 DEFAULT value1,
fieldName2 dataType2,
...
)
--削除
DROP TABLE tableName
テンポラリーテーブル
データ分析用に必要な変数だけ集めてきてこのテーブル作ったりしそう
--作成
CREATE TEMPORARY TABLE tableName (
fieldName1 dataType1,
fieldName2 dataType2,
...
)
主キー・外部キー設定(テーブル作成時)
--主キー(1つのフィールド)
CREATE TABLE tableName (
fieldName1 dataType1 NOT NULL PRIMARY KEY,
fieldName2 dataType2,
...
)
--主キー(複数のフィールド)
CREATE TABLE tableName (
fieldName1 dataType1 NOT NULL,
fieldName2 dataType2 NOT NULL,
fieldName3 dataType3,
...
PRIMARY KEY ( fieldName1, fieldName2 )
)
--外部キー
CREATE TABLE tableName1 (
filedName1 datatype NOT NULL REFERENCES
tableName2 ( filedName2 )
)
--外部キー2
CREATE TABLE tableName1 (
filedName1 datatype NOT NULL,
...
FOREIGN KEY ( filedName1 ) REFERENCES
tableName2 ( filedName2 )
)
主キー・外部キー設定(既存テーブル)
--主キー
ALTER TABLE tableName ADD CONSTRAINT keyName
PRIMARY KEY ( filedName1, filedName2 )
--外部キー
ALTER TABLE tableName ADD CONSTRAINT keyName
FOREIGN KEY ( filedName1 ) REFERENCES
tableName2 ( filedName2 )
キーの削除
--主キー・外部キー
ALTER TABLE tableName DROP CONSTRAINT keyName
--主キー
ALTER TABLE tableName DROP PRIMARY KEY
インデックス
--テーブル作成時に作成
CREATE TABLE tableName (
fieldName1 dataType1,
fieldName2 dataType2,
...
INDEX indexName ( fieldName1, fieldName2 )
--既存テーブルに作成1
CREATE INDEX indexName
ON tableName ( fieldName1, fieldName2 )
--既存テーブルに作成2
ALTRER TABLE tableName
ADD INDEX indexName ( fieldName1, fieldName2 )
--削除
DROP INDEX indexName
テーブル定義変更
--フィールド追加
ALTER TABLE tableName
ADD fieldName dataType
--フィールド削除
ALTER TABLE tableName
DROP COLUMN fieldName
データ取得(1テーブル)
SELECT
*
FROM
tableName
これ↑はビッグデータ扱う時は絶対やっちゃだめそう。データ量多すぎて詰む。
🐣絞り込み条件(Where句)
--絞り込み条件
SELECT
fieldName1,
fieldName2
FROM
tableName
WHERE
expression
--具体例(従業員テーブルtbl_employeeから鈴木一郎の誕生日を取得)
SELECT
birthday
FROM
tbl_employee
WHERE
name = '鈴木一郎' /*文字列・日付型は’で囲む*/
--Where句の中で使う
(codeA = 10 ) AND (codeB =10) /*AND*/
(codeA = 10 ) OR (codeB =20) /*OR*/
name = '鈴木一郎' /*等号 NULLの場合は使えない*/
birthday <= '1975-01-01' /*不等号*/
codeA IS NULL /*NULLの場合*/
codeA IS NOT NULL /*NULじゃない場合*/
codeA <> 10 /*等しくない*/
codeA != 10 /*等しくない*/
between 10 AND 20 /*範囲指定 両端含む*/
name LIKE '鈴木%' /*曖昧な条件*/
codeA IN ( 10, 20 ) /*同一項目に複数の条件*/
codeA NOT IN ( 10, 20 ) /*指定した値以外*/
Where句の中で使える演算子の優先順位
[*,/]算術演算子の乗除算
[+,-]算術演算子の加減算
[<,>, etc]比較演算子
[NOT]論理演算子の否定
[AND]論理演算子の論理積
[OR]論理演算子の論理和
--エイリアス(具体例)
SELECT
code AS 社員コード
birthday AS 生年月日
FROM
tbl_employee
ORDER BY
code
--FROM句なくても使える(具体例)
SELECT UPPER ( 'abc' )
-----
ABC
重複したデータがある場合
--重複行は表示しない例
SELECT
DISTINCT ( dpt_code )
FROM
tble_employee
ORDER BY
dpt_code;
--重複行も表示する例
SELECT
ALL ( dpt_code ) /*ALLはなくても同じ結果になる*/
FROM
tble_employee
ORDER BY
dpt_code;
🐣CASE
特定の値と同じ場合に抽出される値を切り替える(だけではなさそうというのが次の本『達人に学ぶSQL徹底指南書』を読みかけて気づいた)
--単純
SELECT
CASE fieldName
WHEN value1 THEN returnValue1
WHEN value2 THEN returnValue2
...
ELSE returnValue3
END
FROM
tableName
--検索
SELECT
CASE
WHEN expression1 THEN returnValue1
WHEN expression2 THEN returnValue2
...
ELSE returnValue3
END
FROM
tableName
--具体例
SELECT
code AS 社員コード,
name AS 社員名,
CASE dpt_code
WHEN 10 THEN 'Yes'
ELSE 'No'
END AS 総務部
FROM
tbl_employee
ORDER BY
code;
/*dpt_code=10の場合は「Yes」、10以外場合は「No」*/
データ取得(複数テーブル)
等結合(INNER JOINのこと)
--書き方1
SELECT
tableName1.fieldName1,
tableName1.fieldName2,
tableName2.fieldName1,
tableName2.fieldName2,
FROM
tableName1,
tableName2
WHERE
tableName1.fieldName1 = tableName2.fieldName1
--書き方2
SELECT
fieldName1,
fieldName2,
...
FROM
tableName1
INNER JOIN tableName2
ON filter /*結合条件*/
データ追加
--1件ずつ
INSERT INTO tableName (
fieldName1,
filedName2,
filedName3,
...
)
VALUES (
dataValue1,
dataValue2,
dataValue3,
...
)
--別テーブルのデータを利用
INSERT INTO tableName
SELECT
tbl1.fieldName1,
tbl2.fieldName2,
tbl2.fieldName1,
tbl2.fieldName2,
FROM
tableName1 AS tbl1,
tableName2 AS tbl2
WHERE
tbl1.fieldName1 = tble2.fieldName1
データ更新
UPDATE
tableName
SET
fieldName1 = value1,
fieldName2 = value2,
--Where句あり例
UPDATE
tbl_employee
SET
dpt_code = 30,
birthday = '1990-03-19',
WHERE
code = 106;
データ削除
--全レコード
DELETE
FROM
tableName
--特定のレコード
DELETE
FROM
tableName
WHERE
filter
その他の基本的な関数
--文字列を左から数文字分だけ取得
LEFT ( stringfieldName, number )
--文字列を右から数文字分だけ取得
RIGHT ( stringfieldName, number )
--大文字から小文字に変換
SELECT
LOWER ( 'string' )
--小文字から大文字に変換
SELECT
UPPER ( 'string' )
--両端の空白を削除
TRIM ( stringValue )
--左端の空白を削除
LTRIM ( stringValue )
--右端の空白を削除
RTRIM ( stringValue )
--文字列の長さを測る
LENGTH ( stringValue )
--左端に文字列を補填する
LPAD ( stringValue, number, character ) /*numberは補填後の全文字数*/
--右端に文字列を補填する
RPAD ( stringValue, number, character ) /*characterは補填する文字列*/
--文字列を置換
REPLACE ( stringValue, character1, character2 ) /*character1は置換対象*/
/*character2置換する文字列*/
--文字列の部分抽出
SUBSTRING ( stringValue, startPosition, length ) /*startPositionは抽出開始位置*/
/*lengthは抽出する文字列の長さ*/
--剰余の計算
MOD ( value1, value2 ) /*value1が割られる数*/
--四捨五入
ROUND (value, digit ) /*valueが四捨五入される数、digitが四捨五入する桁*/
--切り捨て
TRUNCATE (value, digit ) /*valueが切り捨てされる数、digitが切り捨てする桁*/
--切り上げ
/*関数はない*/
ROUND ( ( value + 0.005), 1 ) /*切り上げ対象となる位に5を足して四捨五入*/
TRUNCATE ( ( value + 0.009), 1 ) /*切り上げ対象となる位に9を足して切り捨て*/
--平均値
AVG ( filedName )
--最大値
MAX ( filedName )
--最小値
MIN ( filedName )
--合計
SUM ( filedName )
--現在の日付
CURRENT_DATE ()
--現在の時刻
CURRENT_TIME()
--現在の日時
CURRENT_TIMESTAMP()
--レコード件数
COUNT
*
FROM
tableName
--指定したフィールドがNULLのレコードを省いた件数
COUNT
fieldName
FROM
tableName
--重複を省いた件数
SELECT
COUNT ( DISTINCT ( filedName ) )
FROM
tableName
--データ型の変換
CAST ( value AS dataType )
応用編
🐣グループ化(GROUP)
--例
SELECT
dpt.code AS dpt_code,
dpt.name AS dpt_name,
COUNT(*) AS emp_cnt
FROM
tbl_employee AS emp,
tbl_department AS dpt
GROUP BY
dpt.code
HAVING
emp_cnt >= 2 /*グループ化された集計項目を抽出条件に含める場合はWHEREではなくHAVING*/
ORDER BY
dpt.code;
/*2人以上社員が所属している部署の部署コード、部署名、社員数を取得する*/
🐣サブクエリ
--例「総務部」に所属している社員の抽出
SELECT
*
FROM
tbl_employee
WHERE
dpt_code = (
SELECT
code
FROM
tble_department
WHERE
name = '総務部'
)
ORDER BY
code;
/*総務部の部署コードに合致するdpt_codeのレコードをtbl_employeeeから取得する*/
--例「総務部」以外に所属している社員の抽出
SELECT
*
FROM
tbl_employee
WHERE
dpt_code IN ( /*値が複数になるのでIN*/
SELECT
code
FROM
tble_department
WHERE
name <> '総務部'
)
ORDER BY
code;
🐣テーブルの和
--重複データを省略する場合
SELECT
code,
name,
birthday
FROM
tbl_employee
WHERE
birthday >= '1975-01-01'
UNION
SELECT
code,
name,
birthday
FROM
tbl_parttimer
WHERE
birthday >= '1975-01-01'
ORDER BY
code;
--重複データを省略しない場合
SELECT
code,
name,
birthday
FROM
tbl_employee
WHERE
birthday >= '1975-01-01'
UNION ALL
SELECT
code,
name,
birthday
FROM
tbl_parttimer
WHERE
birthday >= '1975-01-01'
ORDER BY
code;
/*UNIONの前後の各SELECTの抽出フィールドの数、およびデータ型は揃ってないとだめ*/
🐣外部結合
参照先にデータがないレコードも抽出する。 RIGHT OUTER JOINもあるけど、LEFT OUTER JOINと混在するような使い方は避けた方が良い。
SELECT
fieldName1,
fieldName2,
...
FROM
tableName1
LEFT OUTER JOIN tableName2
ON filter /*結合条件*/
🐣交差結合
左側のテーブルにレコードが3件、右側のテーブルにレコードが5件あった場合、抽出結果は15件。結合条件を指定しないで結合すると、これが適用される。
SELECT
fieldName1,
fieldName2,
...
FROM
tableName1
CROSS JOIN tableName2
🐣範囲日付の重複チェック
SELECT
COUNT(*)
FROM
tbl_period
WHERE ( start_date <= input_start_date
AND end_date >= input_start_date )
OR ( start_date <= input_end_date
AND end_date >= input_end_date )
OR ( start_date >= input_start_date
AND end_date <= input_end_date )
🐣1つのSQLで同一テーブルを結合する
テーブルに仮名を設定し、同一テーブルを別テーブルとみなして結合
SELECT
emp.code AS emp_code,
emp.name AS emp_name,
mgr.manager AS manager
FROM
tbl_employee AS emp,
tbl_employee AS mgr
WHERE
emp.manager = mgr.code
ORDER BY
emp.code;
🐣サブクエリを使用した更新・削除
--更新(例)
UPDATE
tbl_employee
SET
dpt_code = (
SELECT
code
FROM
tbl_department
WHERE
name = '総務部'
)
WHERE
code = '105'
/*tbl_employee.code=105の社員の部署コードを総務部のコードに変更する*/
--削除(例)
DELETE FROM
tbl_employee
WHERE
code = (
SELECT
emp_code
FROM
tbl_salaray
WHERE
salary = 145000
);
/*tbl_salaray.salary=145000の社員コードのレコードをtbl_employeeから削除する*/
この記事が気に入ったらサポートをしてみませんか?