はじめに

SQLについて学習したので、これまでに学んだ知識をまとめました。

学習した事

MySQLのインストール
MySQL Workbenchの使用方法
SQL構文の基本の使い方

今回は、SQL構文の基本の使い方について、自分用メモと知識定着の目的で書きました。訂正箇所などありましたら、ご指摘いただけると幸いです。

SQLの基本

SQLは大文字と小文字を区別しない
文字と日付はシングルクォーテーションで囲う

データ型

テーブルを作成する時に、それぞれのカラムに指定した形式のデータしか入力できないように設定する。この時指定するデータの形式をデータ型という。

データ型名	データ種別
int型	数値型	整数
tinyint型	数値型	-128〜127
char型	文字列	固定長の文字列255文字まで
varchar型	文字列	可変長の文字列255文字まで
text	文字列	長い文字列65535文字まで
date型	日付	1000-01-01〜9999-12-31まで
datetime型	日付・時刻	00:00:00.000000〜23:59:59.999999
time型	時刻	838:59:59〜838:59:59

集約関数

集約関数ではnullは基本的に無視されるが、COUNT(*)とする事でnullを含めたレコードを数える。

関数	意味
COUNT	テーブルのレコード数を数える
SUM	カラムの数値を合計する
AVG	カラムの数値を平均する
MAX	カラムの中の最大値を求める
MIN	カラムの中の最小値を求める

SQL文の記述順序と実行順序

記述順序	実行順序
SELECT	FROM
FROM	JOIN
JOIN	WHERE
WHERE	GROUP BY
GROUP BY	HAVING
HAVING	SELECT
ORDER BY	ORDER BY
LIMIT	LIMIT

in演算子・・・　ある値が値セット内に含まれているかどうか。
not in演算子・・・　ある値が値セット内に含まれていないかどうか。

テーブル操作

CRUD

CRUDとは(Create, Read, Update, Delete)のこと。

SQLではこの4つの処理を

Create = INSERT
Read = SELECT
Update = UPTATE
Delete = DELETE

の構文で操作する。

テーブルの作成

CREATE TABLE [テーブル名](
  列名 [データ型] [その他の記述],
  列名 [データ型] [その他の記述],
  列名 [データ型] [その他の記述]
);

テーブルにデータの追加

INSERT INTO [テーブル名](列1,列2,列2) VALUES(値1,値2,値3);

この時、列と値の数が一致している必要がある。
複数行を一度に追加する場合は、それぞれが括弧で囲まれ、カンマで区切られた、カラム値の複数のリストを記入していく。

INSERT INTO [テーブル名](列1,列2,列2) VALUES(値1,値2,値3),(値1,値2,値3),(値1,値2,値3);

列の追加

ALTER TABLE [テーブル名] ADD [追加する列名] [データ型] AFTER [列名];

列名の変更

ALTER TABLE [テーブル名] CHANGE [旧列名] [新列名] [データ型];

データの取得(SELECT)

SLECT ・・・ カラムの指定
FROM ・・・ 対象テーブルの指定
JOIN ・・・ テーブルの結合
WHERE ・・・ 絞り込み条件の指定
GROUP BY ・・・ グループ化の条件の指定
HAVING ・・・ グループ化した後の絞り込み条件を指定
ORDER BY ・・・ 並び替え条件を指定
LIMIT ・・・ 取得する行数を指定

例 adventurerテーブル

id	name	sex	lv	mp	job
1	山田	man	20	40	戦士
2	鈴木	woman	30	230	魔法使い
3	田中	man	40	280	僧侶
4	小野	woman	50	360	魔法戦士
5	佐藤	man	40	200	レンジャー
6	西野	woman	70	220	パラディン

WHERE句

SELECT name, lv FROM adventurer WHERE name = '鈴木';

実行結果

name     lv
鈴木      25

LIKE

ワイルドカード文字で文字列のパターンを指定する事ができる。

'山%' → '山'で始まる文字列
'%山' → '山'で終わる文字列
'%山%' → '山'を含む文字列
'__山' → 何かしらの2文字で始まり、'山'で終わる文字列

jobに戦士の文字列を含む情報を取得

SELECT * FROM adventurer WHERE job LIKE '%戦士%';

実行結果

id  name  sex   lv   mp    job
1   山田   man   20   40    戦士
4   小野  woman  50   360  魔法戦士

サブクエリ

鈴木さんよりもmpが高い人の名前を取得

SELECT name FROM adventurer WHERE mp > (SELECT mp FROM adventurer WHERE name = '鈴木');

実行結果

name
田中
小野

CASE

CASE式を使う事で条件分岐を指定する事ができる。

lvが60以上なら'A'
lvが40以上なら'B'
lvが40未満なら'C'

SELECT name,
       CASE WHEN lv >= 60 THEN 'A'
            WHEN lv >= 40 THEN 'B'
            ELSE 'C'
       END AS ranking
FROM adventurer;

実行結果

name   ranking
山田       C
鈴木       C
田中       B
小野       B
佐藤       B
西野       A

GROUP BY句

性別毎のレベルの平均を取得

SELECT sex, AVG(lv) FROM adventurer GROUP BY sex;

実行結果

sex     lv
man     50
woman   75

ORDER BY句

並び替え条件を指定する。複数条件を指定する時は、,区切りで指定する。
ASC・・・昇順（デフォルト）
DESC・・・降順

lvが高い順に並び替え。lvが同じ場合はid順。

SELECT * FROM adventurer ORDER BY lv DESC, id ASC;

実行結果

id  name  sex  lv  mp  job
6   西野  woman 70 220  パラディン
4   小野  woman 50 360  魔法戦士
3   田中   man  40 280  僧侶
5   佐藤   man  40 200  レンジャー
2   鈴木  woman 30 230  魔法使い
1   山田   man  20 40   戦士

LIMIT句

取得する行数を指定する事ができる。

SELECT * FROM adventurer ORDER BY lv DESC, id ASC LIMIT 3;

実行結果

id  name  sex  lv  mp  job
6   西野  woman 70 220  パラディン
4   小野  woman 50 360  魔法戦士
3   田中   man  40 280  僧侶

データの更新(UPDATE)

UPDATE [テーブル名] SET [変更したい列名] = ['新しい値'] WHEREで条件指定;

データを削除(DELETE)

DELETE FROM [テーブル名] WHERE [条件];

データベース操作

データベース接続

mysql -u root -p

MySQL起動

mysql.server start

MySQL終了

mysql.server stop

MySQLの状態確認

mysql.server status

データベースの作成

CREATE DATABASE [作成するデータベース名];

データベースの命名ルール

半角のアルファベット a,b,cなど
半角の数字 1,2,3など
アンダースコア _
名前の最初は半角のアルファベット

データベースの削除

DROP DATABASE [削除したいデータベース名];

データベースの一覧表示

SHOW DATABASES;

使用しているデータベースの確認

SELECT DATABASE();

使用するデータベースの選択

USE [データベース名];