プログラミング

【PR】

【MariaDB】個人的によく使うSQLコマンドのメモ

MariaDBメモ：よく使うSQLコマンド

もくじ

よく使用するSQL構文をコピペで使用できるようまとめました。

MariaDBのパスワード設定についてはこちらの記事を参照してください。

MariaDBにSQLコマンドを貼り付ける場合は、「右クリック」　⇒　「貼り付け」で貼り付けることができます。

MariaDB基本操作

MariaDBのパスワード設定についてはこちらの記事を参照してください。

MariaDB（クライアントプログラム）を起動（ログイン）するSQLコマンド

SQL
Copy
  1. mysql -u ユーザ名 -p

MariaDB（クライアントプログラム）を終了するSQLコマンド

SQL
Copy
  1. quit
SQL
Copy
  1. exit

データベースの基本操作

データベース一覧を表示するSQLコマンド

SQL
Copy
  1. SHOW DATABASES;

データベースを作成するSQLコマンド

SQL
Copy
  1. CREATE DATABASE データベース名;

使用するデータベースを選択するSQLコマンド

SQL
Copy
  1. USE データベース名

現在使用しているデータベースを確認するSQLコマンド

SQL
Copy
  1. SELECT database();

データベースを削除するSQLコマンド

SQL
Copy
  1. DROP DATABASE データベース名;

テーブルの基本操作

主なフィールドの型

データ型説明
TYNYINT1バイト整数型（-128　～　127）
SMALLINT2バイト整数型（-32768　～　32767）
INT4バイト整数型（-2147483648　～　2147483647））
BIGINT8バイト整数型（-9223372036854775808　～　9223372036854775807）
FLOAT単精度の浮動小数点型（3.402823466E+38　～　3.402823466E+38）
DOUBLE倍精度の浮動小数点型（-1.7976931348623157E+308　～　1.7976931348623157E+308）
CHAR固定長文字型（0～255文字）
VARCHAR可変長文字型（0～65535バイト）
TEXT可変長文字型（0～65535バイト）
DATE日付型（YYYY-MM-DD）
DATETIME日付時刻型（YYYY-MM-DD HH:MM:SS）
TIMESTAMP日付時刻型（YYYY-MM-DD HH:MM:SS）
BLOBバイナリデータ

主なフィールドの属性

属性説明
PRIMARY KEY（主キー）ユニーク値で値が必ず入るようにします。
AUTO_INCREMENTレコードを追加するごとに自動で昇順の連番が入ります。
NOT NULL値を必ず入れる必要があります。
UNIQUEレコード内で同じ値を入れることはできません。
DEFAULT値が入ってない場合、指定した値が入ります。
ON UPDATEデータの更新が行われた時に指定した値が入ります。

論理演算子

論理演算子説明
AND条件を全て満たす
OR条件のいずれかを満たす
NOT条件を反転させる

比較演算子

比較演算子説明
=等しい
>～より大きい
>=以上
<～より小さい
<=以下
<>等しくない
!=等しくない
Between～And～～から～まで
IN(値1,値2,値3)いずれかの値を含む
IS NULLNULLである
IS NOT NULLNULLでない

テーブルを作成するSQLコマンド

最後の行の「,」は、エラーが発生するため付ける必要がありません。

SQL
Copy
  1. CREATE TABLE テーブル名 (
  2.   フィールド名1 フィールド型1 属性1,
  3.   フィールド名2 フィールド型2 属性2,
  4.   フィールド名X フィールド型X 属性X
  5. );

テーブル一覧を表示するSQLコマンド

SQL
Copy
  1. SHOW TABLES;

フィールドの詳細を表示するSQLコマンド

SQL
Copy
  1. SHOW COLUMNS FROM テーブル名;
SQL
Copy
  1. DESC テーブル名;

レコードを追加するSQLコマンド

SQL
Copy
  1. INSERT INTO テーブル名
  2. (フィールド名1, フィールド名2, フィールド名X) VALUES
  3. (値1, 値2, 値X);
SQL
Copy
  1. INSERT INTO テーブル名
  2. SET フィールド名1=値1, フィールド名2=値2, フィールド名X=値X;

指定したフィールドのデータを取得するSQLコマンド

SQL
Copy
  1. SELECT フィールド名1, フィールド名2, フィールド名X FROM テーブル名 WHERE 条件文;

全てのフィールドのデータを取得するSQLコマンド

SQL
Copy
  1. SELECT * FROM テーブル名;

指定したデータを変更するSQLコマンド

「WHERE」を指定しないと、指定したフィールドの全ての値が更新されるので注意してください。

SQL
Copy
  1. UPDATE テーブル名
  2. SET フィールド名1=値1, フィールド名2=値2, フィールド名X=値X
  3. WHERE 条件文;

既存のテーブルにフィールドを追加するSQLコマンド

SQL
Copy
  1. ALTER TABLE テーブル名 ADD フィールド名 データ型 DEFAULT 値;

フィールドのデータ型を変更するSQLコマンド

種類が異なるデータ型（文字列型から整数型）でも変更可能な場合もあります。

エラーが発生する場合は、「IGNORE」を使用することで強制的にデータ型を変更することができます。

SQL
Copy
  1. ALTER TABLE テーブル名 MODIFY フィールド名 データ型;

フィールドのデータ型を強制的に変更するSQLコマンド

新しいデータ型のフィールドに格納出来ない場合は、「Warning:」としてカウントされます。

SQL
Copy
  1. ALTER IGNORE TABLE テーブル名 MODIFY フィールド名 データ型;

フィールド名とデータ型を変更するSQLコマンド

フィールド名だけを変更する場合は、「新しいデータ型」の部分で既存のデータ型を指定してください。

SQL
Copy
  1. ALTER TABLE テーブル名 CHANGE 旧フィールド名 新しいフィールド名 新しいデータ型;

フィールドを削除するSQLコマンド

SQL
Copy
  1. ALTER TABLE テーブル名 DROP フィールド名;

テーブル名を変更するSQLコマンド

SQL
Copy
  1. ALTER TABLE 旧テーブル名 RENAME AS 新しいテーブル名;

指定したデータを削除するSQLコマンド

「WHERE」を指定しないと、テーブルのデータがすべて削除されるので注意してください。

SQL
Copy
  1. DELETE FROM テーブル名
  2. WHERE 条件文;

テーブルを削除するSQLコマンド

SQL
Copy
  1. DROP TABLE テーブル名;

テーブルの結合

内部結合と外部結合について

内部結合を使用した場合、照合できるレコードだけ表示されます。

照合できないレコードを表示させたい場合は、外部結合を使用します。

内部結合SQLコマンド

「表示したいフィールド名1, 表示したいフィールド名X」を「*」にすると全てのフィールドを表示できます。

SQL
Copy
  1. SELECT テーブル名.表示したいフィールド名1, テーブル名.表示したいフィールド名2 FROM テーブル名1 JOIN テーブル名2
  2. ON テーブル名1.照合用フィールド名=テーブル名2.照合用フィールド名;
SQL
Copy
  1. SELECT * FROM テーブル名1 JOIN テーブル名2
  2. ON テーブル名1.照合用フィールド名=テーブル名2.照合用フィールド名;

3つ以上のテーブルの内部結合SQLコマンド

FROMの後に結合したいテーブル名を「,」の後に指定することで複数のテーブルを結合することができます。

「表示したいフィールド名1, 表示したいフィールド名X」を「*」にすると全てのフィールドを表示できます。

SQL
Copy
  1. SELECT テーブル名.表示したいフィールド名1, テーブル名.表示したいフィールド名2, テーブル名.表示したいフィールド名X FROM テーブル名1, テーブル名2, テーブル名X
  2. WHERE テーブル名1.照合用フィールド名=テーブル名2.照合用フィールド名 AND テーブル名1.照合用フィールド名=テーブル名X.照合用フィールド名;
SQL
Copy
  1. SELECT * FROM テーブル名1, テーブル名2, テーブル名X
  2. WHERE テーブル名1.照合用フィールド名=テーブル名2.照合用フィールド名 AND テーブル名1.照合用フィールド名=テーブル名X.照合用フィールド名;

左外部結合SQLコマンド

「表示したいフィールド名1, 表示したいフィールド名X」を「*」にすると全てのフィールドを表示できます。

SQL
Copy
  1. SELECT テーブル名.表示したいフィールド名1, テーブル名.表示したいフィールド名2 FROM テーブル名1 RIGHT JOIN テーブル名2
  2. ON テーブル名1.照合用フィールド名=テーブル名2.照合用フィールド名;
SQL
Copy
  1. SELECT * FROM テーブル名1 RIGHT JOIN テーブル名2
  2. ON テーブル名1.照合用フィールド名=テーブル名2.照合用フィールド名;

右外部結合SQLコマンド

「表示したいフィールド名1, 表示したいフィールド名X」を「*」にすると全てのフィールドを表示できます。

SQL
Copy
  1. SELECT テーブル名.表示したいフィールド名1, テーブル名.表示したいフィールド名2 FROM テーブル名1 LEFT JOIN テーブル名2
  2. ON テーブル名1.照合用フィールド名=テーブル名2.照合用フィールド名;
SQL
Copy
  1. SELECT * FROM テーブル名1 LEFT JOIN テーブル名2
  2. ON テーブル名1.照合用フィールド名=テーブル名2.照合用フィールド名;

集計関数

合計値を取得するSQLコマンド

SQL
Copy
  1. SELECT SUM(フィールド名) FROM テーブル名;

平均値を取得するSQLコマンド

SQL
Copy
  1. SELECT AVG(フィールド名) FROM テーブル名;

最大値を取得するSQLコマンド

SQL
Copy
  1. SELECT MAX(フィールド名) FROM テーブル名;

最小値を取得するSQLコマンド

SQL
Copy
  1. SELECT MIN(フィールド名) FROM テーブル名;

データ件数を取得するSQLコマンド

SQL
Copy
  1. SELECT COUNT(フィールド名) FROM テーブル名;

集計関数で取得したフィールドに指定したフィールド名を付けるSQLコマンド

SQL
Copy
  1. SELECT 集計関数 AS 指定のフィールド名 FROM テーブル名;

ユーザの設定

主なユーザ権限

設定値権限
ALL PRIVILEGES全ての権限
CLEATEデータベースの作成・テーブルの作成
DROPデータベースの削除・テーブルの削除
SELECTテーブルの参照
INSERTテーブルのレコード追加
UPDATEテーブルのレコード変更
DELETEテーブルのレコード削除
CLEATE USERユーザ作成

新規ユーザ（権限も含む）を追加するSQLコマンド

「.（ドット）」と「*」の間に半角スペースを入れないように注意してください。

SQL
Copy
  1. GRANT 権限1, 権限2, 権限X
  2. ON データベース名.*
  3. TO ユーザ名@ホスト名 IDENTIFIED BY 'パスワード';

ユーザ情報を表示するSQLコマンド

「mysql.user」の後に「¥G」を付けることで、フィールドの並びを縦にすることができます。

SQL
Copy
  1. SELECT * FROM mysql.user;
SQL
Copy
  1. SELECT * FROM mysql.user ¥G;

ユーザ権限を表示するSQLコマンド

SQL
Copy
  1. SHOW GRANTS FOR ユーザ名@ホスト名;

ユーザを削除するSQLコマンド

SQL
Copy
  1. DROP USER ユーザ名@ホスト名;

データベースのバックアップ

データベースの情報をコマンドプロンプトに表示するSQLコマンド

コマンドプロンプトでログインしてない状態で行ってください。

passwordの入力が求められるので入力してください。

日本語が文字化けしている場合は、「日本語が文字化けしている場合の対策」のコードを実行することで解決する場合があります。

SQL
Copy
  1. mysqldump -u ユーザ名 -p データベース名

日本語が文字化けしている場合の対策

SQL
Copy
  1. mysqldump --default-character-set=binary -u ユーザ名 -p データベース名

バックアップファイルを出力するSQLコマンド

コマンドプロンプトでログインしてない状態で行ってください。

ファイルの出力先は、コマンドプロンプトが参照中のフォルダです。

passwordの入力が求められるので入力してください。

SQL
Copy
  1. mysqldump --default-character-set=binary -u ユーザ名 -p データベース名 > 任意のファイル名.dmp

データベースをダンプファイルから復元するSQLコマンド

コマンドプロンプトでログインしてない状態で行ってください。

復元を行う場合、コマンドプロンプトがダンプファイルがあるフォルダを参照している必要があります。

事前にデータベースを作成しておく必要があります。

passwordの入力が求められるので入力してください。

SQL
Copy
  1. mysql --default-character-set=binary -u ユーザ名 -p データベース名 < ダンプファイル名.dmp

データの検索

検索件数を指定して検索するSQLコマンド

「表示したいフィールド名1, 表示したいフィールド名X」を「*」にすると全てのフィールドを表示できます。

SQL
Copy
  1. SELECT 表示したいフィールド名1, 表示したいフィールド名X FROM テーブル名 LIMIT 検索件数;
SQL
Copy
  1. SELECT * FROM テーブル名 LIMIT 検索件数;

開始行と検索件数を指定して検索するSQLコマンド

「表示したいフィールド名1, 表示したいフィールド名X」を「*」にすると全てのフィールドを表示できます。

SQL
Copy
  1. SELECT 表示したいフィールド名1, 表示したいフィールド名X FROM テーブル名 LIMIT 開始位置,検索件数;
SQL
Copy
  1. SELECT * FROM テーブル名 LIMIT 開始位置,検索件数;

範囲を指定して検索するSQLコマンド

「表示したいフィールド名1, 表示したいフィールド名X」を「*」にすると全てのフィールドを表示できます。

SQL
Copy
  1. SELECT 表示したいフィールド名1, 表示したいフィールド名X FROM テーブル名 WHERE フィールド名 BETWEEN 値 AND 値;
SQL
Copy
  1. SELECT * FROM テーブル名 WHERE フィールド名 BETWEEN 値 AND 値;

MariaDBで使える主な正規表現

正規表現説明
.任意の1文字に一致
^前方一致
$後方一致
[abc]aまたはbまたはcの1文字に一致
[a-z]aからz（大文字、小文字）のアルファベットのいずれかに一致
[^a-z]aからz以外の文字
[0-9]0から9の数字に一致
?直前の文字の0回または1回に一致
*直前の文字の0回以上の繰り返しに一致
+直前の文字の1回以上の繰り返しに一致
{n}直前の文字のn回に一致
{m,n}直前の文字のm回以上n回以上に一致
()グループ化を意味する
(X|Y)XまたはY
¥検索条件に特殊記号を含める場合にエスケープ文字として使います

正規表現を使用した検索SQLコマンド

「表示したいフィールド名1, 表示したいフィールド名X」を「*」にすると全てのフィールドを表示できます。

SQL
Copy
  1. SELECT 表示したいフィールド名1, 表示したいフィールド名X FROM テーブル名 WHERE フィールド名 REGEXP '正規表現のパターン';
SQL
Copy
  1. SELECT * FROM テーブル名 WHERE フィールド名 REGEXP '正規表現のパターン';

データの並び替え

昇順に並び替えるSQLコマンド

SQL
Copy
  1. SELECT * FROM テーブル名 ORDER BY フィールド名;

降順に並び替えるSQLコマンド

SQL
Copy
  1. SELECT * FROM テーブル名 ORDER BY フィールド名 DESC;

その他

データをグループ化するSQLコマンド

SQL
Copy
  1. SELECT * FROM テーブル名 GROUP BY フィールド名;

MariaDBでスクリプトファイルを実行するSQLコマンド

コマンドプロンプトに「円記号」「ドット」「半角スペース」と入力して、スクリプトファイルをドラッグ&ドロップすることでファイルパスが自動的に入力されます。

SQL
Copy
  1. ¥. ファイルパスとスクリプトファイル名

【初級者向け】SQL入門におすすめの1冊

アイコン画像

もみじ

私もこの本でSQLを学習し、実務でも活かすことができています。

丁寧で分かりやすい解説と実際にSQLを実行しながら学習できるため、初級者でも安心して取り組むことができます。

-プログラミング
-, ,