こんにちはフロントエンドエンジニアのまさにょんです！
今回は、MySQL (MariaDB)の基本的な操作方法・コマンドについてまとめて解説します。
よく使うMySQLコマンド&構文集 さんをメインにしながら、よく使用するコマンドをまとめています。
目次
Login/Logout関連の操作
MySQL (MariaDB)にLoginする
MySQL (MariaDB)を直接操作するために、Loginします。
-pオプションを付与すると、Passwordの入力を求められるので、入力してEnterをします。
(-pに続けて、パスワードを設定すると省略できます)
注意点として、localhostに接続する場合は、127.0.0.1を指定するようにしましょう。
環境によってはlocalhostのままだとErrorが発生してログインできないことがあります。
# localhost(127.0.0.1)のMySQLサーバに接続する場合
mysql -u root -p
# DataBase名指定でログインする
mysql -u root -p RobotamaDB
# Host指定で、接続先を指定する Ver. localhost
mysql -u Robotama -p -h 127.0.0.1
# rootユーザーで、127.0.0.1 の 3308ポートの RobotamaDB にログインする(パスワード入力済み)
mysql -u root -ppassword -h 127.0.0.1 -P 3308 RobotamaDB
# 外部MySQLサーバに接続する場合
$ mysql -u [ユーザー名] -p -h [host名] -P [ポート番号]MySQL (MariaDB)からLogoutする
次の3つのコマンドのいずれかでログアウトできます。
mysql> \q
mysql> quit
mysql> exitヘルプ
MySQL (MariaDB)にLoginしている状態で、次のどちらかのコマンドでヘルプの確認ができます。
mysql> help
mysql> \hUser情報の操作(rootログイン後)
User情報を取得する
mysql> select user from mysql.user;
+-------------+
| User |
+-------------+
| root |
| root |
| root |
| mariadb.sys |
| root |
+-------------+
5 rows in set (0.001 sec)User情報とHost情報を取得する
mysql> select user, host from mysql.user;
+-------------+-----------+
| User | Host |
+-------------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | robotama |
| mariadb.sys | localhost |
| root | localhost |
+-------------+-----------+
5 rows in set (0.001 sec)User情報とHost情報、Password を取得する
mysql> select user, host, Password from mysql.user;
+-------------+-----------+-------------------------------------------+
| User | Host | Password |
+-------------+-----------+-------------------------------------------+
| mariadb.sys | localhost | |
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | robotama | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | ::1 | *9E1AA81F840662856BA1AFA79A5DB9B99140499C |
+-------------+-----------+-------------------------------------------+
5 rows in set (0.001 sec)新しいUserを作成する
新しくユーザを作成するためには、CREATE USER文を使用します。
ユーザ名、パスワードには、任意の文字列を設定してください。
ホスト名には、MySQLへ接続するホストを指定してください。
ホスト名の代わりにIPアドレスやlocalhostを指定することも可能です。
CREATE USER 'ユーザ名'@'ホスト名' IDENTIFIED BY 'パスワード';試しに、Userを作成してみる
mysql> create user Robotama@localhost identified by 'robotama-nanoda';
Query OK, 0 rows affected (0.000 sec)
mysql> select user, host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| Robotama | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.000 sec)Userのパスワードを変更する
MySQL内部からパスワードを変更する
パスワードを文字列として渡すことに注意してください。
# SET PASSWORD FOR ユーザ名@ホスト名=password('新しいパスワード');
# 現在接続しているユーザーに新しいパスワードを設定する場合
set password = password('new-pass');
# 特定のユーザーのパスワードを設定する場合場合
set password for Robotama@localhost = password('new-pass');MySQLにログインせずにパスワードを変更する
パスワードをクオーテーションで囲む必要はありません。
# mysqladmin password 新しいパスワード -u ユーザー名 -p
mysqladmin password new-pass -u Robotama@localhost -pUser を削除する
drop user 'ユーザー名'@'ホスト';Userの権限の確認
show grants for 'ユーザー名'@'ホスト名';root User の権限確認をしてみます。
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)作ったばかりの一般ユーザーの権限を確認してみます。
mysql> show grants for 'Robotama'@'localhost';
+----------------------------------------------+
| Grants for Robotama@localhost |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'Robotama'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)権限の付与
GRANT 権限 ON データベース名.テーブル名 TO 'ユーザー名';作ったばかりの一般ユーザーにすべての操作権限を与えてみます。
# すべてのDB・すべてのテーブルに対する権限を付与する場合
mysql> GRANT ALL PRIVILEGES ON * . * TO Robotama@localhost;
Query OK, 0 rows affected (0.01 sec)
# 特定のDBのすべてのテーブルに権限を付与する場合
mysql> grant all on RobotamaDB.* to Robotama@localhost;
Query OK, 0 rows affected (0.03 sec)
mysql> show grants for 'Robotama'@'localhost';
+------------------------------------------------------------------+
| Grants for Robotama@localhost |
+------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'Robotama'@'localhost' |
| GRANT ALL PRIVILEGES ON `robotamadb`.* TO 'Robotama'@'localhost' |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)付与した権限設定の反映がされない時は、次のコマンドを実行して、リロードしてください。
FLUSH PRIVILEGES;データベースの操作系統
データベース一覧の表示・確認
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| express1 |
| mysql |
| next_ts_ec |
| performance_schema |
| sys |
| todo_app |
| ts_todo |
+--------------------+
8 rows in set (0.00 sec)データベースの追加
mysql> create database RobotamaDB;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| RobotamaDB |
| express1 |
| mysql |
| next_ts_ec |
| performance_schema |
| sys |
| todo_app |
| ts_todo |
+--------------------+
9 rows in set (0.01 sec)データベースの選択
mysql> use robotamadb;
Database changedTableの操作系統
テーブル一覧の表示
mysql> show tables;# テーブルの詳細情報
mysql > show table status;テーブルの作成
mysql > CREATE TABLE [テーブル名] (
[フィールド名] [データ型] [オプション]
) ENGINE=[InnoDB/MyISAM] DEFAULT CHARSET=[文字コード];mysql > CREATE TABLE `m_users` (
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT "ID",
`user_name` VARCHAR(100) NOT NULL COMMENT "ユーザー名",
`mail_address` VARCHAR(200) NOT NULL COMMENT "メールアドレス",
`password` VARCHAR(100) NOT NULL COMMENT "パスワード",
`created` datetime DEFAULT NULL COMMENT "登録日",
`modified` datetime DEFAULT NULL COMMENT "更新日"
) ENGINE=InnoDB DEFAULT CHARSET=utf8;テーブルの削除
mysql > DROP TABLE [テーブル名]TABLEが存在しない時にエラーで止めたくなければ、
次のように IF EXISTS を追加することでTABLEが存在する時のみ DROP TABLE 文を実行するようにできます。
mysql > DROP TABLE IF EXISTS [テーブル名]テーブル名の変更
mysql > ALTER TABLE [旧テーブル名] RENAME [新テーブル名]テーブルにカラムの追加
mysql > ALTER TABLE [テーブル名] ADD [追加カラム名] [型] [必要であればオプション等];テーブル設計の確認
# desc [テーブル名]
mysql> desc users;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_name | varchar(100) | NO | | NULL | |
| mail_address | varchar(200) | NO | | NULL | |
| password | varchar(100) | NO | | NULL | |
| created | datetime | YES | | NULL | |
| modified | datetime | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)もっと詳細が知りたければ
# show full columns from [テーブル名];
mysql> show full columns from users;
+--------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+-----------------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+-----------------------+
| id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | ID |
| user_name | varchar(100) | utf8_general_ci | NO | | NULL | | select,insert,update,references | ユーザー名 |
| mail_address | varchar(200) | utf8_general_ci | NO | | NULL | | select,insert,update,references | メールアドレス |
| password | varchar(100) | utf8_general_ci | NO | | NULL | | select,insert,update,references | パスワード |
| created | datetime | NULL | YES | | NULL | | select,insert,update,references | 登録日 |
| modified | datetime | NULL | YES | | NULL | | select,insert,update,references | 更新日 |
+--------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+-----------------------+
6 rows in set (0.01 sec)レコード操作関連
レコードの追加
mysql > INSERT INTO [テーブル名] [フィールド名] VALUES [値]レコードの更新
mysql > UPDATE [テーブル名] SET [フィールド名]=[値] WHERE [条件式]すべてのレコードを削除する
mysql > DELETE FROM [テーブル名]一部レコード削除
mysql > DELETE FROM [テーブル名] WHERE [条件式]DBのダンプを取る (ログイン不要・外部から取れる)
Database の Dumpファイルを取る際は、MySQL・MariaDBにログインしなくても、ダンプファイルを取れます。
.dump や .sql どちらかの拡張子で Dumpファイルを取ることが多いです。
すべてのデータベースを対象とする
mysqldump -u [ユーザー名] -p -x --all-databases > [出力ファイル名]特定のデータベースを対象とする
# mysqldump -u [ユーザー名] -p -x [DB名] > [出力ファイル名]
mysqldump -u root -p RobotamaCMS > robotama.dumptest_dbデータベースのusersテーブルを対象とする
mysqldump -u [ユーザー名] -p -x test_db users > [出力ファイル名]Restoreする
# mysql -u[ユーザー名] -p new_db < [ダンプファイル名]
mysql -u root -p RobotamaCMS < C:\robotama.dumpSQL実行結果をファイルに出力する
# mysql -u ユーザー名 -p -e "SQLの実行文" SQLの対象DB > 出力先のPath
mysql -u root -p -e "select * from users" test_db > /tmp/mysql.txt
mysql -u root -p -e "select * from users" RobotamaDB > C:\Users\Robotama\Desktop\robotama.txtMySQL (MariaDB)のポートを確認する
MariaDBが接続するときに使うポート番号を確認します。デフォルトのままであれば、3306です。
mysql > show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+MySQLログイン状態の見え方の変更
mysql> \R \d(\U) >\_
PROMPT set to '\d(\U) >\_'
(none)(root@localhost) > MySQL システム変数(DB設定値)を表示する
# MySQL システム変数の値を表示します。
SHOW GLOBAL VARIABLES;LIKE文で特定の項目のみに絞れます。
# SHOW GLOBAL VARIABLES LIKE '%「検索項目」%';
mysql> show global variables like 'character%';
+--------------------------+----------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/Cellar/mysql@5.7/5.7.36/share/mysql/charsets/ |
+--------------------------+----------------------------------------------------------+
8 rows in set (0.01 sec)ストレージエンジンの種類を確認する
SHOW ENGINES;
# データベースのストレージエンジンの種類を確認する
SHOW TABLE STATUS FROM データベース名;
SHOW TABLE STAUS WHERE NAME = [テーブル名] \G;mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)robotamadbの ストレージエンジンがInnoDBであることがわかります。
mysql> show table status from robotamadb;
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| users | InnoDB | 10 | Dynamic | 3 | 5461 | 16384 | 0 | 0 | 0 | 4 | 2022-11-14 10:24:11 | 2022-11-14 10:44:39 | NULL | utf8_general_ci | NULL | | |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.001 sec)DB・SQL関連書籍
参考・引用
- よく使うMySQLコマンド&構文集
- 【MariaDB/MySQL】外部接続の設定方法
- MySQLで新しいユーザーを作成して権限を付与する方法
- 【MySQL入門】ユーザー作成の方法を解説！8.0からの変更点も紹介
- MySQLでユーザを作成し、権限を設定する方法
- MySQLでパスワードを変更する「SET PASSWORD」
最近の投稿
【SQL】1回のINSERTで、複数行のレコードを一括挿入・一括追加する方法
JavaScriptでランダムなuuidを作成する方法(randomUUIDの使い方)
PythonでCSVをJSONに変換する方法
PythonでCSV・Dataframeを指定の行数まで切り取る方法
JavaScript(TypeScirpt)のfor-of文でindexの値も取得する方法と、Type IterableIteratorエラーの解決方法
VSCode上のPython import文の「インポート “XXXXX” をソースから解決できませんでした」エラーの解決方法
