見出し画像
Photo by burningrabbit

SQL修行vol.1

つくし

『これならわかるSQL入門の入門』

４月からSQLを仕事で使うことになったので、２年ぶりにこの本を手に取った。
２年前に読んだ時は１週間くらいかかったのに、今回は１日半で読み終えて自分自身の成長を感じた。
その中で今後使いそうと思った関数を自分の辞書的にまとめておこうと思う。

データベース

--作成
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


データ取得（１テーブル）

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句の中で使える演算子の優先順位

  1. [*,/]算術演算子の乗除算

  2. [+,-]算術演算子の加減算

  3. [<,>, etc]比較演算子

  4. [NOT]論理演算子の否定

  5. [AND]論理演算子の論理積

  6. [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;

/*２人以上社員が所属している部署の部署コード、部署名、社員数を取得する*/

🐣サブクエリ

--例「総務部」に所属している社員の抽出
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                   /*結合条件*/

🐣交差結合
左側のテーブルにレコードが３件、右側のテーブルにレコードが５件あった場合、抽出結果は１５件。結合条件を指定しないで結合すると、これが適用される。

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   )

🐣１つの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から削除する*/

この記事が気に入ったらサポートをしてみませんか？