Web制作メモ -トップへ-

MySQLの基本的な操作方法

Ruby2016.01.28 17:56

今後RailsのデータベースでMySQLを使うので、MySQLの基本的な操作方法をまとめました。

ログイン/ログアウト

ログインする

# mysql -u root -p
Enter password:

ログアウトする

mysql> quit

データベースの操作

データベースの一覧を表示する

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sakura_db          |
+--------------------+

データベースを作成する

mysql> create database sakura_db character set utf8;

データベースを削除する

mysql> drop database sakura_db;

テーブルの操作

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

mysql> show tables from sakura_db;
+---------------------+
| Tables_in_sakura_db |
+---------------------+
| sakura_table        |
+---------------------+

テーブルを作成する

mysql> create table sakura_db.sakura_table ( id int, name varchar(100) );

テーブルを削除する

mysql> drop table sakura_db.sakura_table;

列(カラム、フィールド)の操作

テーブル内の列を表示する

mysql> show columns from sakura_db.sakura_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(30)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

テーブル内の列を追加する

mysql> alter table sakura_db.sakura_table add address varchar(30);

テーブル内の列を削除する

mysql> alter table sakura_db.sakura_table drop address;

特定の列をユニークと指定する

mysql> alter table sakura_db.sakura_table add unique ( id );

データ(レコード)の操作

データの一覧を表示する

mysql> select * from sakura_db.sakura_table;
+------+-------+
| id   | name  |
+------+-------+
|    1 | tarou |
+------+-------+

データを追加する

mysql> insert into sakura_db.sakura_table ( id, name ) values ( 1, "tarou" );

データを削除する

mysql> delete from sakura_db.sakura_table where id = 1;

データベースを選択する

※選択しておけばデータベース名の指定を省略できる

操作するデータベースを選択する

mysql> use sakura_db;
Database changed

現在選択しているデータベースを表示する

mysql> select database();
+------------+
| database() |
+------------+
| sakura_db  |
+------------+

ユーザー管理

※ユーザー情報はmysqlデータベースのuserテーブルに格納されています。

登録されているユーザー一覧を表示する

mysql> select Host, User, Password from mysql.user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *2716E81B2DB90555DF0B6E64B145247E37AE517C |
| 127.0.0.1 | root | *2716E81B2DB90555DF0B6E64B145247E37AE517C |
+-----------+------+-------------------------------------------+

ユーザーの権限を表示する

mysql> show grants for 'root'@'localhost';
+-------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                             |
+-------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '(省略)' WITH GRANT OPTION   |
+-------------------------------------------------------------------------------------------------------+

コマンドを間違えてキャンセルしたい時

プロンプトに戻るには「\c」

mysql> show de
    -> \c
mysql>

データベースのバックアップ/リストア

データベースをバックアップする

# mysqldump --single-transaction -u root -p sakura_db > /tmp/mysql-sakura_db-`date "+%Y%m%d-%H%M%S"`.sql

データベースをリストアする

# mysql -u root -p sakura_db < /tmp/mysql-sakura_db-20160128-163345.sql 

CSVでデータ管理

データをCSVで出力する

mysql> select * from sakura_db.sakura_table into outfile '/tmp/mysql-sakura_table.csv' fields terminated by ',' optionally enclosed by '"';

CSVファイルからデータを読み込む

mysql> load data local infile '/tmp/mysql-sakura_table.csv' replace into table sakura_db.sakura_table fields terminated by ',' enclosed by '"';
※replaceオプションにより、ユニーク行に合わせてデータが更新される

Excelで作成したCSVファイルの場合は改行コードに注意

mysql> load data local infile '/tmp/mysql-sakura_table.csv' replace into table sakura_db.sakura_table fields terminated by ',' enclosed by '"' LINES TERMINATED BY '\r\n';