Go to Qiita Advent Calendar 2024 Top
LoginSignup
16
33

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@maru401(Maru ryu)

[SQL] SQL初心者によるSQL書き方まとめ

Last updated at Posted at 2021-02-05

#概要
ほとんどSQLを触ったことがなく少々勉強したので学んだことのアウトプットのために記事の作成を決意。

SQLの書き方を忘れてしまったときの備忘録の役割をしてくれたら幸い。

※ わかりやすいように日本語で書いているが英語で書くことを推奨(文字化けの可能性がある)

#基本

# 大文字でも小文字でも動作する
SELECT * FROM TABLE;　←最後に;をつける

#目次

  1. SELECT FROM(列の取り出し)

    1. SELECT * FROM テーブル
    2. SLEECT カラム1, カラム2 FROM テーブル
    3. SELECT DISTINCT(カラム) FROM テーブル
    4. SELECT カラム*(+-/)数値 FROM テーブル
    5. SELECT SUM(カラム) (AVG COUNT MAX MIN)
    6. SELECT カラム AS 変更したいカラム名
    7. SELECT TABLE1.COL, TABLE2.COL

  2. JOIN
    2. LEFT JOIN
    3. 複数テーブルの統合

  3. WHERE(行の条件指定)
    2. WHERE カラム<A 比較
    3. WHERE カラム=A　　条件一致
    4. WHERE カラム LIKE "%ワード%" 部分一致
    5. WHERE NOT カラム=A　否定
    6. WHERE カラム IS NULL

  4. AND OR (WHEREの付け足し　書き方は変わらない)

  5. GROUP BY (集計関数のみ)

  6. HAVING (WHEREとほぼ同じ)

  7. ORDER BY 並べ替えたいカラム 並べ方;
    2. ORDER BY カラム ASC; (昇順)
    3. ORDER BY カラム DESC; (降順)

  8. LIMIT 件数

#実行順序
以下の順でSQLは実行される。
この順序を忘れないことでLIMITを使用することで時間が短くならない理由やWHEREの地点でSELECTで選択していないカラムについても条件つけ出来る理由に納得がいく

①FROM 　　　テーブルの選択
②JOIN 　　　テーブルの結合
③WHERE 　　　条件指定
④GROUP BY　　グループ化　　　　
⑤HAVING　　　グループ化したデータで条件指定
⑥SELECT　　　カラムの検索
⑦ORDER BY　　並び替え
⑧LIMIT　　　　データ件数の制限

1.SELECT カラム FROM テーブル;

SELECT カラム FROM テーブルで特定のテーブルから取り出したい列を取り出すことが出来る。

SQL

# 特定のテーブルからカラムを取り出すことが出来る
SELECT カラム FROM テーブル;

# 複数のカラムを取得
SELECT カラム1, カラム2 FROM テーブル;

# テーブルから全てのカラムを取得
SELECT * FROM テーブル;

データの加工

あらかじめカラムのデータを加工して挿入することも出来る。

このなかで特に,SUM AVG COUNT MAX MIN は集計関数と呼ばれデータを集約した値を算出できる

SQL
#カラム内に重複があったら細書のモノ以外削除する
##ごはんカラムに重複があったら削除
SELECT DISTINCT(ごはん) FROM テーブル;

#カラムに四則演算をした値を使える
##消費税を加味した値を追加
SELECT price*1.08 FROM テーブル

#関数をつかうことも出来る (SUM AVG COUNT MAX MIN)

寿司の値段の合計
SELECT SUM(price)
FROM テーブル
WHERE メイン="寿司"
;

寿司の値段の平均
SELECT AVG(price)
FROM テーブル
WHERE メイン="寿司"
;

テーブルのメインが寿司のデータの数を取得
SELECT COUNT(*)
FROM テーブル
WHERE メイン="寿司"
;

寿司の値段で一番高いもの
SELECT MAX(price)
FROM テーブル
WHERE メイン="寿司"
;

寿司の値段で一番低いもの
SELECT MIN(price)
FROM テーブル
WHERE メイン="寿司"
;

# カラム名の変更
SELECT SUSHI AS "寿司" 
FROM ごはん


# 複数テーブルでカラム名が被った時
SELECT TABLE1.COL, TABLE2.COL
FROM TABLE1
JOIN TABLE2
ON TABLE1.COL=TABLE2.COL
;

2.JOIN ON

共通のカラムを起点にテーブルを結合する事ができる。
ただしNULLについては消されてしまうので注意が必要。

#書き方
SELECT *
FROM テーブル1
JOIN テーブル2
#このカラムは対応しているもの
ON テーブル1.カラム = テーブル2.カラム

LEFT JOIN

FROM テーブルのNULLを残したままJOINのテーブルを合成することが出来る。

#テーブル1のNULLを残してテーブルを統合できる
SELECT テーブル1.カラム, テーブル2.カラム
FROM テーブル1
LEFT JOIN テーブル2
ON テーブル1.カラム=テーブル2.カラム
;

複数テーブルの合成

FROM テーブル1で基準のテーブルを決めたら、JOIN ON,LEFT JOIN ONを複数回使用することでテーブルを複数合成することが出来る。

#テーブル1を起点としてテーブル2とテーブル3(NULLをのこす)を統合
SELECT *
FROM テーブル1
JOIN テーブル2
ON テーブル1.カラム1=テーブル2.カラム1
#テーブル3はテーブル1のNULLを残して統合
LEFT JOIN テーブル3
ON テーブル1.カラム2=テーブル3.カラム2
;

3.WHERE 行の条件

WHEREで行に関して条件検索をすることが出来る。

SELECTが列の条件ならWHEREが行の条件になる

SQL
SELECT *
FROM テーブル
# colはselectで宣言されていないものでも使用可能⭕ (SQLの実行順を確認 WHERE→SELECT)
WHERE col = "条件"

(例) メインが寿司の行のみ取り出す
SELECT 主食 
FROM ご飯
WHERE メイン = "寿司";

#データ型が数値or日付のとき比較演算子(<, > orz)などが使用できる。
where money > 2000;
#日付には""をつけるので注意
where created_at <="2020-04-02";

#ワイルドカードの使用(部分一致) %に何でも入る
##うんこカレーOK カレーライス✗
where カラム like %カレー;

##うんこカレー✗　カレーライス○
where カラム like カレー%;

##うんこカレーOK カレーライスOK
where カラム like %カレー%;

#条件不一致
##うんこカレー以外OK
where not カラム like "%カレー";

#欠損値の扱い

##priceが欠損値となっている行を取り出し
where price is null;
##欠損してない
where price is not null;

###次と間違えないこと
where price = null;       ✗✗
where not price is null;  ✗✗

AND OR

WHEREの条件を付け足す。

SQL
#メインが寿司かつ副菜がステーキの行
SELECT 主食 
FROM ご飯
WHERE メイン = "寿司"
AND サブ="ステーキ";

#メインが寿司またはもやしの行
SELECT 主食 
FROM ご飯
WHERE メイン = "寿司"
OR メイン="もやし";

GROUP BY

GROUP BY カラムとすることで、カラムの値ごとでグループ化して集計することが出来る。
SELECT内では集計関数とGROUP BYで指定したカラム(こっちは必ずSELECTでいれる)のみ選択ができる。(集計されていないかつGROUP BY で選択されていないカラムは選択できない)

SQL

#書き方
SELECT {GROUP BY で宣言されたカラム} , 集計関数(OPTION)
FROM テーブル
WHERE {GROUP BYで宣言されて無くても使える}


#産地ごとでグループ分け
SELECT SUM(price), 産地
FROM テーブル
WHERE メイン = "寿司"
GROUP BY 産地
;

#このように産地と収穫日など複数のカラムのグループ化も可能
SELECT SUM(price), 産地, 収穫日
FROM テーブル
WHERE メイン = "寿司"
GROUP BY 産地, 収穫日
;

#だめな例　❌
# priceは集計されていなくgroup byでも宣言されていない為使えない
#産地ごとでグループ分け
SELECT price, 産地
FROM テーブル
WHERE メイン = "寿司"
GROUP BY 産地
;

HAVING

GROUP BYでグループ化したデータの条件でWHERE 条件のように絞り込むことが出来る。
ほぼWHEREと書き方は一緒。

SQL
#基本
#産地ごとでグループ分け
SELECT SUM(price), 産地
FROM テーブル
WHERE メイン = "寿司"
GROUP BY 産地
HAVING SUM(price)>2000
;

# WHEREとおなじくAND を使うことも出来る
#産地ごとでグループ分け
SELECT SUM(price),AVG(price),収穫日, 産地
FROM テーブル
WHERE メイン = "寿司"
GROUP BY 産地, 収穫日
HAVING SUM(price)>2000
AND AVG(price)>300
;

ORDER BY

並び替えたいカラムを起点に全体のテーブルを並び替えることができる。

ASCが昇順, DESCが降順を示す

SQL
#寿司の値段の順に昇順
SELECT 主食 
FROM ご飯
WHERE メイン = "寿司"
ORDER BY price ASC;

##LIMIT
テーブルの上からの最大取得件数を制限することが出来る。

SQL
#寿司のデータを値段が高い順に10件分獲得
SELECT 主食 
FROM ご飯
WHERE メイン = "寿司"
#値段を高い順に変更
ORDER BY price DESC
LIMIT 10;

#自分が誤解していた点

SELECTで選択していないカラムはWHEREで条件指定できないと思っていたが実際は出来る。

SQL
#このように300円以上の寿司の寿司ネタのみを表示させられる
SELECT 寿司 FROM ごはん
WHERE price>=300;

#　応用
SQLの応用的な操作は以下のようなものがある。

  • サブクエリ
  • INSERT INTO テーブル(カラムA,カラムB) VALUES(A1,B1);
  • UPDATE テーブル SET KEY=VALUE ... WHERE 条件
  • DELETE FROM テーブル WHERE 条件;
  • テーブルの作成

##サブクエリ
WHERE 条件で条件指定する際に比較先にもうひとつSQL文を書くことが出来る。
それにより例えば、寿司であればぶりより高い寿司飲みに選択などテーブル情報から比較をすることが出来る。

SQL

#ぶりの平均価格より高い値段の寿司ネタを表示
SELECT price, 寿司ネタ
FROM ごはん
# ()内に条件を入れる
where price > (
  select AVG(price),寿司ネタ
  from ごはん
  where 寿司ネタ="ぶり"
)
;

##カラムの挿入
INSERT INTO テーブル(KEY) VALUES(VALUE);とすることでデータの挿入を行える。PRIMARY KEYは書かなくても自動で保管される。

SQL
#寿司のテーブルにネタと値段をいれたい時
INSERT INTO 寿司(neta, price) 
VALUES("まぐろ", 1000);

-- 複数レコード一括INSERT
INSERT 寿司(neta, price) 
VALUES 
  ("まぐろ", 1000);
  ("ぶり", 300);
  ("サーモン", 600);

##データの更新
UPDATE テーブル SET KEY=VALUE ... WHERE 条件とすることでWHEREで条件指定したところについてデータの更新を行うことが出来る。

WHEREを指定しないと全てのカラムが更新されてしまうので注意が必要。

#3番のidのネタをぶりに, 値段を100に変更

UPDATE 寿司
SET neta="ブリ", price=100
WHERE id=3
;


#コレだとすべての行のネタとねだんがぶりで100になってしまう😨😨
UPDATE 寿司
SET neta="ブリ", price=100
;

データの削除

DELETE FROM テーブル WHERE 条件;で消したいデータを削除できる。

SQL
#寿司テーブルからidが3番を削除
DELETE FROM 寿司
WHERE id=3
;

#寿司テーブルからidが3番を削除
DELETE FROM 寿司
WHERE id=3
;


#コレだと全ての寿司テーブルの業が削除されてしまう😨😨
DELETE FROM 寿司
;

##テーブルの作成
データベースのSchemaとTableの対応関係を示すと以下のような頭になる。(間違ってたらすいません)

image.png

そのため、この包括関係を意識してテーブルも設計していなかなければならない。
ソレを踏まえて以下のように設計する。

ここで以下のようにnot nullをつけることでそのカラムには欠損値を与えてないけないという制限をかけることが出来る。(デフォルトはnull)

id int not null,

ソレを踏まえコードを書くと以下のようなサンプルコードが書ける。

SQL
# sample_schemaがあったら削除し再作成する　
DROP SCHEMA IF EXISTS sample_schema;
CREATE SCHEMA sample_schema;
USE sample_schema;

# sample_tableがあったら削除し再作成する
DROP TABLE IF EXISTS sample_table;

CREATE TABLE sample_table(
#PRIMARY KEYに設定し自動で値が保管されるようにする
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
profile text
)DEFAULT CHARACTER SET=utf8mb4 COLLATE = utf8mb4_general_ci;

テーブルの作成で使用するデータ型

よく使用するテーブルのデータ型には以下のようなものがあるらしい。

データ型 表しているもの 例 オプション
int 整数 2 ()にMAX10で桁数の指定が可能(しない方を推奨)
float 小数 1E-7 ()荷桁数の指定が可能(しない方を推奨)
decimal 小数 0.00001 floatと違いEがつかないので数字で表記できる
money 金額 1,000 -
varchar 文字 短い文字 255文字以下 varchar(10)で10文字までなど指定可能
text 文字 長い文章 255文字以上の時に採用
date 日付 2008-11-11 YYYY-MM-DD がフォーマット
datetime 日時 2008-11-11 13:23:44 YYYY-MM-DD HH:MI:SS がフォーマット

テーブル作成で使用するオプション

よく使用するテーブルのオプションには以下のようなものがあるらしい。

オプション 例 用途
not null id int not null カラムのnullを禁止
auto_increment id INT NOT NULL AUTO_INCREMENT 自動で連番の数値を入力 primary keyと相性が良い
default name varchar(255) DEFAULT 'Yamada Taro' カラムのデフォルト値を設定
primary key id INT NOT NULL AUTO_INCREMENT PRIMARY KEY primary keyを設定したカラムを主キーにする。　not nullのオプションも同時に付けなければならない
unique Fullname varchar unique uniqueを指定したカラムの重複を禁止

#エラーハンドリング

##Incorrect string value: '\xF0\x9F\x8E\x89 \xE6

dbの文字コードがutf-8担っているのが原因と思われる。
そのため、テーブルを作成するときに以下のようにあらかじめデフォルトの文字コードをutf8mb4に変更するのが好ましい。

CREATE TABLE zenn_article (
#PRIMARY KEYに設定し自動で値が保管されるようにする
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username varchar(255),
user_url varchar(255),
article_title varchar(255),
article_url text,
article_likes int,
created_datetime text
)DEFAULT CHARACTER SET=utf8mb4 COLLATE = utf8mb4_general_ci;

primary keyの重複

csvファイルなどをインポートしようとする時、pythonのpandasの場合0,1,2・・・というふうになってしまう。しかしidは1から入力しなければいけないため0が1になり重複になってしまう。
そのため、idの部分の入力をignore fieldとして自動入力に頼るように工夫する。

image.png

Data truncated for column…

importしようとしているdataの型が異なっているため起こるエラー。

数値の時 3,111のように","を含むと数値として扱えないので以下のようにしてpandasであらかじめ変換を加える。

strを数値へ変換
データ型の確認
df.dtypes
 df["総いいね数"] =  df["総いいね数"].str.replace(",","")
df.総いいね数=df.総いいね数.astype("int32")

#参考文献
・データベースの実行の流れ
[【SQL】実行の順序] (https://qiita.com/yurina0402/items/78ae1f536b6755d6221a)

・初期データの投入
DockerのMySQLに初期データを投入する

・カラムの作成のnullの制限について
NOT NULL制約(カラムにNULLの格納を許可するかどうか)

・データ型の説明
MySQL入門CREATE TABLE文でテーブルを作成する方法

16
33
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
16
33

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?