⚾
データベースMySQLの基礎知識
データベースの基礎を固めるために、学びながらアウトプットしようと思います。
以下のストーリーを追うことで、理解が深まるでしょう。
流れ
- 基本状態の確認
- テーブル自体に対する操作：スキーマ変更
- テーブル内のデータに対する操作：CRUD操作
環境
MySQL 8.0.39
セットアップ
# MySQLにログイン(デフォルトのrootユーザで)
sudo mysql
スキーマ変更とは
テーブル内のデータではなく、テーブルの構造自体に変更を施すことです。
データベース作成
すべての原点、データベース作成を行います。
# 基本情報を確認
STATUS;
# データベース`myapp_db`を作成
CREATE DATABASE myapp_db;
# 今ログインしているアカウントでアクセス可能なすべてのデータベースをリストする
SHOW DATABASES;
# 新しいデータベース`myapp_db`を使う（に入る）。
USE myapp_db;
ユーザ作成 CREATE USER (Optional)
最初から権限管理を意識してデータベース操作をやりましょう。
# ユーザ名`user1`、パスワード`password`で作成
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'user1'@'localhost';
FLUSH PRIVILEGES;
# 今後は`sudo mysql -u user1 -p`でログイン
テーブル作成 CREATE TABLE
2024年にもなりますし、そろそろ文字列の部分をすべて国際基準のUTF-8に対応させましょう！
CREATE TABLE contact_tab (
id INT, name VARCHAR(31),
age INT, address VARCHAR(51),
created_at DATETIME
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# データベース内のすべてのテーブルをリストする
SHOW TABLES;
補足：
- UTF-8対応で、ケースセンシティブなし
-
INTの後ろに何も付けなくて良い(MySQL 8以降の仕様)
メリット：
- ユーザフレンドリー
- 多言語対応
あれ、郵便番号の列をcontact_tabテーブルに追加したいですね。
テーブル変更 ALTER TABLE
ALTER TABLE <table_name>
ADD COLUMN <column_name> <data_type> <optional: rule>;
郵便番号のフォーマットは、一般的に上3桁と下4桁をハイフンで接続した「999-9999」の形式
ALTER TABLE contact_tab
ADD COLUMN postcode VARCHAR(8)
CHECK (postcode REGEXP '^[0-9]{3}-[0-9]{4}$');
idを主キーにして、自動で追加してほしいですね。
ALTER TABLE contact_tab
MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
テーブルcontact_tabの中身を確認したいです！
mysql> SHOW COLUMNS FROM contact_tab;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(31) | YES | | NULL | |
| age | int | YES | | NULL | |
| address | varchar(51) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| postcode | varchar(8) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
テーブル削除 DROP TABLE
テーブル自体を削除
DROP TABLE <table_name>
CRUD操作とは
テーブル内のデータを操作することです。
Create(C)、データの追加。ただし、実際にはSQL文のINSERTに対応する
Read(R)、既存のデータを取得。ただし、実際にはSQL文のSELECTに対応する
Update(U)、 既存のデータを変更する
Delete(D)、データ行を削除する
C, データ追加 INSERT
# まずはテーブル内の列情報を確認しておく
SHOW COLUMNS FROM contact_tab;
# 例
INSERT INTO contact_tab (name, address) VALUES ('田中花子', '東京都新宿区 新宿');
# 主キーが１と仮定しよう
# NG例：同じ人(行)に情報追加/更新する場合、以下のように追加すると、新しい１行が増えるだけで、必ずUPDATEを使おう
INSERT INTO contact_tab (name, postcode) VALUES ('田中花子', '160-0022');
R, データ取得 SELECT
# まずはテーブル内の列情報を確認しておく
SHOW COLUMNS FROM contact_tab;
# 例
SELECT id, address FROM contact_tab;
U, データ更新 UPDATE
UPDATE <tab_name>
SET <target_column_name> = <target_column_value>
WHERE <column_name1> = <column_name1_value>
実際に使ってみましょう。
# WHERE句の後に主キーを条件として行を特定する。 WHEREなしでの使用は大惨事に繋がりかねない。
UPDATE contact_tab SET postcode='160-0022' WHERE id = 1;
SELECTで実行結果を確認
SELECT * FROM contact_tab;
mysql> SELECT * FROM contact_tab;
+----+--------------+------+----------------------------+------------+----------+
| id | name | age | address | created_at | postcode |
+----+--------------+------+----------------------------+------------+----------+
| 1 | 田中花子 | NULL | 東京都新宿区 新宿 | NULL | 160-0022 |
+----+--------------+------+----------------------------+------------+----------+
後で年齢も追加しましょう
補足：誤操作を挽回するための対策
autocommitを一時オフにします。
SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
SET autocommit = OFF;
これで、再度ログインまで自動セーブ機能がオフになっています。
# 自動セーブがオフになっているため、手動でセーブする
COMMIT;
D, データ削除 DELETE
テーブルのすべてのデータ(つまり行)を削除、ただしテーブル自体は残ります。
DELETE FROM <table_name>
DELETE FROM contact_tab;
Discussion