はじめに
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 [データベース名];