久しぶりにMySQLを使ってテーブルを作ろうと思ったところ、いろいろと忘れていることにショック中です。
自分用にログインしてDB作成からテーブル作成し、CREATE文等の基本的な操作を一連にまとめました。
環境
MySQL 8.0.39
windowsのPowershellからSSH接続
ログインからDB作成まで
パスワードを利用したログイン
仮 user test_user
pass password1234
# ログインするまで
$ > mysql -u test_user -p
Enter password:********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1065
Server version: 8.0.39-0ubuntu0.24.04.2 (Ubuntu)
：
mysql>
# コンソールが、"mysql>"になっていればログインが成功しています現在のユーザに対する権限を確認する
mysqlではユーザ毎に権限が付与されており、できることとできないことが決まっています。
次のコマンドを使ってログインユーザにどのような権限が付与されているかを確認します。
# ログインユーザにどのような権限が付与されているか確認する
mysql> SHOW GRANTS FOR CURRENT_USER;
+--------------------------------------------------------+
| Grants for test_user@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%` | # 通常の操作は行うことができる
| GRANT ALL PRIVILEGES ON `a_db`.* TO `test_user`@`%` | # a_db に対してすべての操作が可能
+--------------------------------------------------------+この状態では、a_dbというデータベースだけが使える状態となっています。
DBの作成
次のコマンドでDBを作成することができます。
mysqlは通常インストールすると、dbの作成権限はrootだけに許可されているようですので、rootユーザでログインしなおします。
※rootユーザのパスワードはOSログイン時のパスワードと違うので、注意してください。
# DBの作成（権限が足りない）
mysql> CREATE DATABASE b_db;
ERROR 1044 (42000): Access denied for user 'test_user'@'%' to database 'test_db'
mysql>exit
# rootでログインしなおす
$> mysql -u root -p
Enter password: ****(一般ユーザと同じになっていたので後でコッソリ直しますw)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1069
:
mysql>
# DBの作成（権限が足りている）
mysql>CREATE DATABASE b_db; # ↑ボタンを押すと前のユーザで入力したコマンドが出るようです
Query OK, 1 row affected (0.02 sec)
# test_userにb_dbの権限を追加する
mysql> GRANT ALL PRIVILEGES ON b_db.* TO 'test_user'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql>exit # root から 一旦出る
# test_userでログインしなおす
ub-axis@ubaxis-virtual-machine:~$ mysql -u remort_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1070
:
mysql> SHOW GRANTS FOR CURRENT_USER;
+--------------------------------------------------------+
| Grants for remort_user@% |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `remort_user`@`%` |
| GRANT ALL PRIVILEGES ON `a_db`.* TO `remort_user`@`%` |
| GRANT ALL PRIVILEGES ON `b_db`.* TO `remort_user`@`%` | # b_dbが追加されている
+--------------------------------------------------------+テーブルの作成
作成したDBにログインし、簡単なテーブルを作成します。
データの挿入、削除、選択、カラムの追加についてメモしておきます。
# b_dbデータベースを使う
mysql>use b_db;
Database changed
# item_masterテーブルを作成する
mysql>mysql> CREATE TABLE `item_master` (
`no` int NOT NULL AUTO_INCREMENT, # AUTO_INCREMENT便利です
`name` varchar(20) NOT NULL,
`dist` text,
`cost` int ,
PRIMARY KEY (`no`)
);
Query OK, 0 rows affected (0.09 sec)
# データを挿入する
mysql> insert into item_master(name,dist,cost) values("赤い薬","HPを少し回復する",50);
Query OK, 1 row affected (0.01 sec)
mysql> insert into item_master(name,dist,cost) values("青い薬","MPを少し回復する",70);
lues("緑の薬","HPとMPを少し回復する",100);Query OK, 1 row affected (0.00 sec)
mysql> insert into item_master(name,dist,cost) values("緑の薬","HPとMPを少し回復する",100);
Query OK, 1 row affected (0.00 sec)
# データを更新する
mysql> update item_master set name="緑色の薬" where no=3;
Query OK, 1 row affected (0.00 sec)
# データを表示する
mysql> select * from item_master;
+----+-----------+------------------------------+------+
| no | name | dist | cost |
+----+-----------+------------------------------+------+
| 1 | 赤い薬 | HPを少し回復する | 50 |
| 2 | 青い薬 | MPを少し回復する | 70 |
| 3 | 緑の薬 | HPとMPを少し回復する | 100 |
+----+-----------+------------------------------+------+
3 rows in set (0.00 sec)
# データを削除する
mysql> DELETE FROM item_master WHERE no = 3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from item_master;
+----+-----------+-------------------------+------+
| no | name | dist | cost |
+----+-----------+-------------------------+------+
| 1 | 赤い薬 | HPを少し回復する | 50 |
| 2 | 青い薬 | MPを少し回復する | 70 |
+----+-----------+-------------------------+------+
2 rows in set (0.00 sec)
# カラムを追加する
mysql> ALTER TABLE item_master ADD kind varchar(10);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from item_master;
+----+-----------+-------------------------+------+------+
| no | name | dist | cost | kind |
+----+-----------+-------------------------+------+------+
| 1 | 赤い薬 | HPを少し回復する | 50 | NULL |
| 2 | 青い薬 | MPを少し回復する | 70 | NULL |
+----+-----------+-------------------------+------+------+
2 rows in set (0.00 sec)
# CREATE文を再出力する
mysql> SHOW CREATE TABLE item_master;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| item_master | CREATE TABLE `item_master` (
`no` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`dist` text,
`cost` int DEFAULT NULL,
`kind` varchar(10) DEFAULT NULL,
PRIMARY KEY (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)表示系
テーブルの一覧やデータベースの一覧を確認したいとき
# dbの一覧を確認したいとき
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| a_db |
| b_db |
+--------------------+
# テーブルの一覧を確認したいとき
mysql> use b_db
Database changed
mysql> show tables;
+----------------+
| Tables_in_b_db |
+----------------+
| item_master |
+----------------+
1 row in set (0.00 sec)データ移行
作成したDBを本番環境に複写する方法
コンソール画面から次のコマンド(mysqldump)を使用します。
実行するにはroot権限が必要です。
# b_db.item_master の全データを生成するSQLを発行する
$> mysqldump -u root -p --no-create-info --complete-insert b_db item_master > output.sql
Enter password: ****
$> ls
output.sql
# 内容を表示
$> cat output.sql
-- MySQL dump 10.13 Distrib 8.0.39, for Linux (x86_64)
--
-- Host: localhost Database: b_db
-- ------------------------------------------------------
-- Server version 8.0.39-0ubuntu0.24.04.2
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Dumping data for table `item_master`
--
LOCK TABLES `item_master` WRITE;
/*!40000 ALTER TABLE `item_master` DISABLE KEYS */;
INSERT INTO `item_master` (`no`, `name`, `dist`, `cost`, `kind`) VALUES (1,'赤い薬','HPを少し回復する',50,NULL),(2,'青い薬','MPを少し回復する',70,NULL);
/*!40000 ALTER TABLE `item_master` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2024-10-01 21:16:26
# データの挿入方法 ※挿入先のデータベースで実行
$>mysql -u username -p new_database < output.sql
最後に
CREATE系のSQLってプロジェクトの開始時にはよく使うのですが、開発中はあまり使わなくなるので、うっかり忘れがちです（個人的な見解）
件局、何度も構文を検索しているので、よく使う命令を自分用にまとめました。
