MySQLと向き合うための 現場で使えるデータベース操作・SQLノート

MySQL開発環境のセットアップ

インストールが完了したらCLIでmysqlコマンドを使えるように、環境パスを通しておきます。

export PATH="/opt/homebrew/opt/mysql@8.0/bin:$PATH"

mysqlの起動と停止

$ mysql.server start # 起動
$ mysql.server stop # 停止

MySQLの初期設定

rootユーザーにパスワードを設定する

$ mysql -u root # rootユーザーでログインします。

以下、MySQLのプロンプトは>で表現します。

> ALTER USER 'root'@'localhost' IDENTIFIED BY '新しいパスワード'; -- MySQLのプロント上で、rootユーザーに新しいパスワードを設定します。
> FLUSH PRIVILEGES; -- 変更を反映させます。
> exit; # または quit; --MySQLのプロントを終了します。

ここまでの設定が完了したら、新しいパスワードでログインできるか試します。パスワードを入力してログインできたら成功です。

$ mysql -u root -p

後からrootのパスワードを変更したい場合

MySQLへログイン後、以下を実行することでパスワードを変更できます。

> SET PASSWORD = '[新しいパスワード]';

データベース操作

データベースの作成

> CREATE database [データベース名];

データベースを確認する

> SHOW databases;

データベースへ接続する

> USE [データベース名]

MySQLへログインすると同時に、データベースへ接続することも可能です。

$ mysql [データベース名] -u [ユーザー名] -p

テーブルを確認する

> SHOW tables;

テーブルのカラムを一覧表示する

> SHOW COLUMNS FROM テーブル名;

データベースを削除する

> CREATE database [データベース名];

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

データベースを丸ごとバックアップ（ダンプ）する

次のコマンドで、既存のデータベースをエクスポート（ダンプ）することが可能です。

$ mysqldump -u root -p [データベース名] > mydb_dump.sql

SQLクエリとしてファイルが保存されます。

データベースをインポートする（外部ファイルのSQLの実行）

mysqlへログインして、新規でデータベースを作成しておきます。

> CREATE DATABASE [データベース名];

mysqlからログアウトして、シェルで以下コマンドを実行します。

$ mysql [データベース名] -u [ユーザー名] -p < mydb_dump.sql

これでデータベースのテーブルの構造やデータを復元できました。

特定のテーブルをバックアップする

mysqldump -u [ユーザー名] -p[パスワード] [データベース名] [テーブル名] > [バックアップファイル名].sql

外部ファイルのSQLを読み込んで実行する

MySQLにログインした状態でも外部ファイルを取り込んでSQLを実行できます。

> source test.sql

SQLの結果表示

SQL結果を縦表示に整形する

SQLの終端のセミコロン; を\Gに変えると、縦表示が可能です。

> SELECT * FROM users\G

SQL結果をlessページャーで表示する

テーブルカラムが多い時や、SQL結果が大量な時に便利です。

下記コマンドでlessを使うようにセットアップします。

> pager less -S

これ以降、通常のSQLクエリを発行すると、結果がlessで表示されるようになります。

ページャーを終了するときは以下を実行します。

> nopager;

MySQLクライアントの起動時にカスタム設定を反映させる

以下、~/.my.iniに書き込む内容です。~/.my.iniファイルが存在しない場合は、新規作成してください。

[client]
pager=less -S

これでSQL実行時に、結果テーブルをlessで開くようになります。

MySQL テーブル＆コメント一覧表示

操作したいデータベースを選択:

> USE your_database_name;

テーブル一覧とそれぞれのコメントを表示する:

> SELECT TABLE_NAME, TABLE_COMMENT 
> FROM INFORMATION_SCHEMA.TABLES 
> WHERE TABLE_SCHEMA = '[データベース名]';

MySQLで使用できる主なデータ型

ここでMySQLで使用できる主なデータ型を整理しておきます。

数値型

日付と時刻型

文字列型

スペーシャル型

ちなみに、SQLiteはMySQLと比べてデータ型がかなり少ないので、互換性のアプリを作りたい場合には注意が必要です。

SQLiteでデータベースをはじめる　macOS/Linux/Unix

2024-02-24

データ操作

レコードを挿入する

TEXT、INT、DATETIMEを挿入する例:

INSERT INTO my_table (title, num, date)
VALUES ('hoge', 1000, NOW());

レコードを更新する

UPDATE `tablename` SET age=17 WHERE id=2;

idが最大値（最後の行）のレコードを更新する

次のクエリは、id カラムがオートインクリメントされている場合に有効です

UPDATE `tablename`
SET age = 17
WHERE id IN (
    SELECT MAX(id) FROM `tablename`
);

MySQLでは自身のテーブルの参照をサブクエリで実行すると、エラーになるので注意が必要です。下記のような書き方はMySQLではできません。

UPDATE `tablename` SET age = 17 WHERE id = (SELECT MAX(id) FROM `tablename`);

オートインクリメントの連番をリセット

ALTER TABLE `tablename` auto_increment = 1;

TRUNCATEでテーブルデータを全件削除

TRUNCATE TABLE `tablename`

DELETE FROM tablename でも全件削除はできますが、TRUNCATEとDELETEでは次のような違いがあります。

ロールバックの必要がなければ、TRUNCATEが良いでしょう。

データ検索

合計値を取得する

以下は my_table テーブルから、dateカラムが2024年1月のデータを合計する例です。

SELECT 
  category_id, 
  SUM(num) AS num_sum
FROM 
  my_table
WHERE 
  date >= '2024-01-01 00:00:00' AND date <= '2024-01-31 23:59:59'
GROUP BY 
  category_id;

category_id ごとの合計値が取得できます。

JOINで複数のテーブルを結合する

次は、INNER JOINを使って2つのテーブルの両方に存在する行のみをセレクトする例です。

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

GROUP_CONCATで複数の行の値を単一の文字列に結合する

次は、各従業員が持つスキルを一覧表示する例です。GROUP_CONCAT はMySQLやSQLite、MariaDBで使用可能です。

SELECT e.name, GROUP_CONCAT(s.skill ORDER BY s.skill SEPARATOR ', ') AS skills
FROM employees e
JOIN skills s ON e.id = s.employee_id
GROUP BY e.id;

関連記事