目次
はじめに
自分用のSQL構文メモ。
MySQL用。
MySQLへのログイン
mysql -u ユーザー名 -pデータベース操作
データベース作成
CREATE DATABASE データベース名;データベース削除
DROP DATABASE データベース名;データベース一覧確認
SHOW DATABASES;使用するデータベースの切り替え
USE データベース名;現在使用中のデータベース確認
SELECT DATABASE();テーブル操作
テーブル作成（基本）
CREATE TABLE テーブル名 (
フィールド名1 データ型,
フィールド名2 データ型
);プライマリーキー・AUTO_INCREMENT付きテーブル作成
CREATE TABLE テーブル名 (
id INT AUTO_INCREMENT,
フィールド名 データ型,
PRIMARY KEY (id)
);テーブル削除
DROP TABLE テーブル名;テーブル一覧確認
SHOW TABLES;テーブル定義確認
SHOW FIELDS FROM テーブル名;フィールド（カラム）操作
フィールド追加
ALTER TABLE テーブル名 ADD 追加フィールド データ型 AFTER 既存フィールド;フィールド削除
ALTER TABLE テーブル名 DROP 削除対象フィールド;フィールド名変更
ALTER TABLE テーブル名 CHANGE 旧フィールド名 新フィールド名 データ型;フィールドのデータ型変更
ALTER TABLE テーブル名 MODIFY フィールド名 データ型;NOT NULL 制約の設定
ALTER TABLE テーブル名
MODIFY フィールド1 データ型 NOT NULL,
MODIFY フィールド2 データ型 NOT NULL;デフォルト値の設定
ALTER TABLE テーブル名
ALTER フィールド名 SET DEFAULT 'デフォルト値';プライマリーキー設定
ALTER TABLE テーブル名 ADD PRIMARY KEY (フィールド名);外部キー制約設定
ALTER TABLE テーブル名
ADD FOREIGN KEY (外部キー列)
REFERENCES 参照先テーブル (主キー列);ユーザー・権限管理
ユーザー作成
CREATE USER 'ユーザー名'@'localhost';現在のユーザー確認
SELECT USER();パスワード設定・変更
ALTER USER 'ユーザー名'@'localhost'
IDENTIFIED BY '新しいパスワード';権限付与
GRANT 権限 ON データベース名.* TO 'ユーザー名'@'localhost';主要権限一覧
| 権限名 | 対象 | できること | 実務での主な用途 | 危険度 |
|---|---|---|---|---|
| SELECT | データ | データ参照のみ | 参照専用、分析、レポート | 低 |
| INSERT | データ | レコード追加 | 登録処理、ログ保存 | 中 |
| UPDATE | データ | レコード更新 | ステータス・数量更新 | 中 |
| DELETE | データ | レコード削除 | 物理削除が必要な処理 | 高 |
| CREATE | 構造 | テーブル作成 | 開発作業 | 高 |
| DROP | 構造 | テーブル削除 | 管理作業 | 非常に高 |
| ALTER | 構造 | テーブル定義変更 | カラム追加・変更 | 非常に高 |
| INDEX | 構造 | インデックス作成・削除 | パフォーマンス調整 | 中 |
| REFERENCES | 構造 | 外部キー制約作成 | リレーション設計 | 中 |
| ALL / ALL PRIVILEGES | 全体 | そのスコープ内の全権限 | 管理者・開発環境 | 非常に高 |
| GRANT OPTION | 権限 | 権限を他人に付与 | DBA | 非常に高 |
データ操作
レコード追加
INSERT INTO テーブル名 (フィールド1, フィールド2)
VALUES ('データ1', 'データ2');レコード追加（全カラム指定）
INSERT INTO テーブル名 VALUES ('データ1', 'データ2');レコード更新
UPDATE テーブル名
SET 更新方法1
WHERE 条件;レコード削除
DELETE FROM テーブル名 WHERE 条件;SELECT文（検索）
全件取得
SELECT * FROM テーブル名;重複除外
SELECT DISTINCT フィールド名 FROM テーブル名;条件付き検索
SELECT フィールド名
FROM テーブル名
WHERE フィールド名 比較演算子 '条件'
AND / OR / NOT フィールド名 比較演算子 '条件';主要な比較演算子一覧
| 演算子 | 意味 | 使用例 | 実務での主な用途 |
|---|---|---|---|
= | 等しい | age = 30 | 完全一致検索（ID、コードなど） |
!=<> | 等しくない | status != '削除' | 特定値を除外 |
> | より大きい | score > 80 | 閾値超過の判定 |
< | より小さい | price < 1000 | 上限チェック |
>= | 以上 | created_at >= '2025-01-01' | 開始日以降 |
<= | 以下 | pdate <= CURDATE() | 今日以前など |
BETWEEN | 範囲指定（含む） | age BETWEEN 20 AND 29 | 年齢・期間指定 |
IN | いずれかに一致 | status IN ('未処理','保留') | OR条件の簡略化 |
NOT IN | いずれにも一致しない | id NOT IN (1,2,3) | 除外リスト |
LIKE | 部分一致 | name LIKE '%山田%' | あいまい検索 |
NOT LIKE | 部分一致しない | name NOT LIKE '%テスト%' | 不要データ除外 |
IS NULL | NULL判定 | deleted_at IS NULL | 未設定データ抽出 |
IS NOT NULL | NULLでない | memo IS NOT NULL | 入力済み判定 |
EXISTS | 存在確認 | EXISTS (SELECT 1 …) | サブクエリ存在チェック |
NOT EXISTS | 存在しない | NOT EXISTS (SELECT 1 …) | 未対応データ抽出 |
よく一緒に使う論理演算子
| 演算子 | 意味 | 使用例 |
|---|---|---|
AND | 両方満たす | age >= 20 AND age < 30 |
OR | どちらか満たす | role = 'admin' OR role = 'user' |
NOT | 否定 | NOT status = '削除' |
並び替え
SELECT フィールド名
FROM テーブル名
ORDER BY フィールド名 ASC / DESC;件数制限
SELECT フィールド名
FROM テーブル名
ORDER BY フィールド名
LIMIT 件数;集計・グループ化
GROUP BY + 集計関数
SELECT フィールド名, 集計関数
FROM テーブル名
GROUP BY フィールド名;主要な集計関数一覧
| 集計関数 | 意味 | 使用例 | 実務での主な用途 |
|---|---|---|---|
COUNT(*) | 行数を数える（NULL含む） | COUNT(*) | 件数集計、レコード数確認 |
COUNT(フィールド) | NULLを除いた件数 | COUNT(score) | 入力済み件数の把握 |
SUM(フィールド) | 合計値 | SUM(amount) | 売上合計、数量合計 |
AVG(フィールド) | 平均値 | AVG(score) | 平均点、平均金額 |
MIN(フィールド) | 最小値 | MIN(price) | 最安値、最古日付 |
MAX(フィールド) | 最大値 | MAX(price) | 最高値、最新日付 |
別名（エイリアス）
SELECT フィールド名 AS 別名
FROM テーブル名;JOIN（結合）
内部結合（INNER JOIN）
SELECT t1.フィールド, t2.フィールド
FROM テーブル1 AS t1
INNER JOIN テーブル2 AS t2
ON 条件2外部結合（LEFT / RIGHT JOIN）
SELECT t1.フィールド, t2.フィールド
FROM テーブル1 AS t1
LEFT / RIGHT OUTER JOIN テーブル2 AS t2
ON 条件;完全外部結合（FULL OUTER JOIN）
MySQLでは FULL OUTER JOIN は未対応
SELECT t1.フィールド, t2.フィールド
FROM テーブル1 AS t1
FULL OUTER JOIN テーブル2 AS t2
ON 条件;MySQLでの代替方法（UNION）
SELECT t1.フィールド, t2.フィールド
FROM テーブル1 AS t1
LEFT JOIN テーブル2 AS t2
ON 条件
UNION
SELECT t1.フィールド, t2.フィールド
FROM テーブル1 AS t1
RIGHT JOIN テーブル2 AS t2
ON 条件;※ UNION は重複行を除外する。重複を許可したい場合は UNION ALL を使用する。
※ WHERE 句で結合先テーブルの条件を指定すると、外部結合の意味が失われることがあるため注意。
インデックス
インデックス作成
CREATE INDEX インデックス名 ON テーブル名 (フィールド名);インデックス削除
DROP INDEX インデックス名 ON テーブル名;インデックスの利用状況確認
EXPLAIN SELECT 文;トランザクション
トランザクション開始
BEGIN;確定
COMMIT;取り消し
ROLLBACK;SQLファイルの実行（データ展開）
SOURCE ファイルパス;
コメント