はじめに
この記事はPHPをやってみたいので、MySQLもやってみる4の続きです。
備忘録なので、足りない分や、もっとこうしたらよかったとかは、後から追加修正していきます。
作成済みのテーブルのフィールドを操作
新規フィールドを追加
alter table {テーブル名} add {フィールド名} {フィールド定義};
mysql> alter table users add tel int;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
先頭に新規フィールドを追加
alter table {テーブル名} add {フィールド名} {フィールド定義} first;
mysql> alter table users add id int first;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
特定のフィールドの後にフィールドを追加
alter table {テーブル名} add {フィールド名} {フィールド定義} after {指定のフィールド名};
mysql> alter table users add mail varchar(255) after age;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| mail | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
auto_incrementのフィールドを追加
普通に追加しようとするとエラーとなる。
mysql> alter table users add id int not null auto_increment first;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
下記のように、index（索引）を追加する必要があるようです。
mysql> alter table users add id int not null auto_increment first,add index id (id);
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | MUL | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
フィールドを削除
alter table {テーブル名} drop {フィールド名} ;
mysql> alter table users drop mail;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
フィールド名、型情報を変更
alter table users change {変更前のフィールド名} {変更後のフィールド名} {フィールド定義};
mysql> alter table users change mail email varchar(100);
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| email | varchar(100) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
索引を追加
alter table users add index {フィールド名} ({key});
mysql> alter table {テーブル名} add index mail (mail);
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| mail | varchar(255) | YES | MUL | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
索引を削除
alter table users drop index {フィールド名};
mysql> alter table {テーブル名} drop index mail;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| mail | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| tel | int(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
テーブル間での連携
まず下記のようなテーブルを作ります。
create table groups (
id int not null primary key auto_increment,
name varchar(255)
);
insert into groups (name) values
('group1'),
('group2'),
('group3');
+----+--------+
| id | name |
+----+--------+
| 1 | group1 |
| 2 | group2 |
| 3 | group3 |
+----+--------+
create table users (
id int not null primary key auto_increment,
group_id int not null,
name varchar(255),
address varchar(255),
age int
);
insert into users (group_id, name,address,age) values
(1,'sato','Tokyo',75),
(2,'ando','Saitama',15),
(3,'kato','Kanagawa',25),
(2,'ito','Chiba',13),
(1,'goto','Yamagata',80);
+----+----------+------+----------+------+
| id | group_id | name | address | age |
+----+----------+------+----------+------+
| 1 | 1 | sato | Tokyo | 75 |
| 2 | 2 | ando | Saitama | 15 |
| 3 | 3 | kato | Kanagawa | 25 |
| 4 | 2 | ito | Chiba | 13 |
| 5 | 1 | goto | Yamagata | 80 |
+----+----------+------+----------+------+
テーブルを２つ作ったら、groupsのidと、usersのgroup_idで２つのテーブルを関連付けて値を取得してみます。
select {table名.フィールド名,table名.フィールド名,...} {table名,table名,...} where {表示条件};
mysql> select groups.id,groups.name,users.name from groups,users where groups.id = users.group_id;
+----+--------+------+
| id | name | name |
+----+--------+------+
| 1 | group1 | sato |
| 2 | group2 | ando |
| 3 | group3 | kato |
| 2 | group2 | ito |
| 1 | group1 | goto |
+----+--------+------+
データベースのバックアップ
下記のようなデータベースがあるとします。
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
//testデータベースの中身はこれ
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
これのtestデータベースのバックアップをとります。
mysqldump -u {ユーザー} -p {データベース名} > {バックアップファイル名}
下記コマンドを実行すると、データベースのバックアップファイルが作成されました。
[vagrant@localhost ~]$ mysqldump -u testuser -p test > dump.sql
Enter password:
[vagrant@localhost ~]$ ls
dump.sql
データベースの復元
上記でバックアップしたファイルを使って、データベースを復元したいと思います。
さきほど、バックアップをとったtestデータベースの中身を空にします。
//空にする処理の記載は省きます。
mysql> use test;
mysql> show tables;
Empty set (0.00 sec)
データベースを復元してみます。
mysql -u {ユーザー} -p {データベース名} < {バックアップファイル名}
<の向きがバックアップの場合と逆向きになっていることに注意してください。
下記コマンドを実行するとusersテーブルが復元されていることが確認できます。
[vagrant@localhost ~]$ mysql -u testuser -p test < test.dump.sql
//mysqlにログイン
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
まとめ
基礎の基礎はなんとなくわかりましたが、実践には程遠いです。