6. MySQL入門(備忘録)

使用しているバージョン情報

$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.3 LTS"
$ mysql -V
mysql  Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using  EditLine wrapper

データベースとユーザを作成

$ mysql -u root -p
Enter password:						# パスワード入力
mysql> create database college;		# DB作成
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| college            |				# collegeDBが追加
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+

# testユーザを作成し、該当DBへ全権限を付与
mysql> grant all on college.* to test@localhost identified by 'test';
mysql> select user from mysql.user;			# testユーザ登録確認
+------------------+
| user             |
+------------------+
| debian-sys-maint |
| mysql.session    |
| mysql.sys        |
| root             |
| test             |				# testユーザが追加
| wordpress        |
+------------------+
mysql> exit

作業用ユーザでテーブル作成

$ mysql -u test -p
Enter password:				# パスワード入力
mysql> show databases;		# 作業ユーザで利用できるDBを確認
+--------------------+
| Database           |
+--------------------+
| information_schema |
| college            |
+--------------------+
mysql> use college;			# 利用するDBを設定

# staff_registerテーブル作成
mysql> create table staff_register(
id int(4) auto_increment primary key,
department varchar(32) not null,
section varchar(32) not null,
name varchar(32) not null,
phone int(8),
email varchar(32)
);

mysql> show tables;							# テーブル作成確認
+-------------------+
| Tables_in_college |
+-------------------+
| staff_register    |
+-------------------+

mysql> desc staff_register;					# カラムを表示
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(4)      | NO   | PRI | NULL    | auto_increment |
| department | varchar(32) | NO   |     | NULL    |                |
| section    | varchar(32) | NO   |     | NULL    |                |
| name       | varchar(32) | NO   |     | NULL    |                |
| phone      | int(8)      | YES  |     | NULL    |                |
| email      | varchar(32) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

作業用ユーザでレコードの追加/参照/更新/削除

# レコードの追加
mysql> insert into staff_register (id, department, section, name, phone, email) values (1, 'Technology', 'Applied Science', 'John Brawn', 74111111, 'aaa@gmail.com');

# 全てのカラムに値を指定する場合に限って、カラムの記述を省略可能
mysql> insert into staff_register values (1, 'Technology', 'Applied Science', 'John Brawn', 74111111, 'aaa@gmail.com');

mysql> select * from staff_register;					# レコードの追加を確認
+----+------------+-----------------+------------+----------+---------------+
| id | department | section         | name       | phone    | email         |
+----+------------+-----------------+------------+----------+---------------+
|  1 | Technology | Applied Science | John Brawn | 74111111 | aaa@gmail.com |
+----+------------+-----------------+------------+----------+---------------+

# John Brawn の名前を Jack Brawn に変更
mysql> update staff_register set name = 'Jack Brawn' where name = 'John Brawn';
mysql> select * from staff_register;					# レコードの更新を確認
+----+------------+-----------------+------------+----------+---------------+
| id | department | section         | name       | phone    | email         |
+----+------------+-----------------+------------+----------+---------------+
|  1 | Technology | Applied Science | Jack Brawn | 74111111 | aaa@gmail.com |	# Jackに変更された
+----+------------+-----------------+------------+----------+---------------+
# 続けて幾つかレコードを追加してみます。
mysql> insert into staff_register (department, section, name, phone, email) values ('Technology', 'ICT', 'Jacob', 74111112, 'bbb@gmail.com');
mysql> insert into staff_register (department, section, name, phone) values ('Technology', 'ICT Support', 'Jackson', 74111113);
mysql> insert into staff_register (department, section, name, email) values ('Administration', 'Administration', 'Noah', 'ddd@gmail.com');
mysql> insert into staff_register (department, section, name) values ('Administration', 'Finance', 'Lucas');
mysql> insert into staff_register (department, section, name) values ('Administration', 'Learning Resouces Centre', 'Sophia');
mysql> insert into staff_register (department, section, name, email) values ('Administration', 'Maintenance', 'Chloe', 'fff@yahoo.com');
mysql> insert into staff_register (department, section, name, email) values ('Administration', 'Supplies & Contracts', 'George', 'ggg@yahoo.com');

mysql> select * from staff_register;					# レコードの追加を確認
+----+----------------+--------------------------+------------+----------+---------------+
| id | department     | section                  | name       | phone    | email         |
+----+----------------+--------------------------+------------+----------+---------------+
|  1 | Technology     | Applied Science          | Jack Brawn | 74111111 | aaa@gmail.com |
|  2 | Technology     | ICT                      | Jacob      | 74111112 | bbb@gmail.com |
|  3 | Technology     | ICT Support              | Jackson    | 74111113 | NULL          |
|  4 | Administration | Administration           | Noah       |     NULL | ddd@gmail.com |
|  5 | Administration | Finance                  | Lucas      |     NULL | NULL          |
|  6 | Administration | Learning Resouces Centre | Sophia     |     NULL | NULL          |
|  7 | Administration | Maintenance              | Chloe      |     NULL | fff@yahoo.com |
|  8 | Administration | Supplies & Contracts     | George     |     NULL | ggg@yahoo.com |
+----+----------------+--------------------------+------------+----------+---------------+
# レコードの削除
mysql> delete from staff_register where section = 'Finance';
mysql> select * from staff_register;					# レコードの削除を確認
+----+----------------+--------------------------+------------+----------+---------------+
| id | department     | section                  | name       | phone    | email         |
+----+----------------+--------------------------+------------+----------+---------------+
|  1 | Technology     | Applied Science          | Jack Brawn | 74111111 | aaa@gmail.com |
|  2 | Technology     | ICT                      | Jacob      | 74111112 | bbb@gmail.com |
|  3 | Technology     | ICT Support              | Jackson    | 74111113 | NULL          |
|  4 | Administration | Administration           | Noah       |     NULL | ddd@gmail.com |
|  6 | Administration | Learning Resouces Centre | Sophia     |     NULL | NULL          |
|  7 | Administration | Maintenance              | Chloe      |     NULL | fff@yahoo.com |
|  8 | Administration | Supplies & Contracts     | George     |     NULL | ggg@yahoo.com |
+----+----------------+--------------------------+------------+----------+---------------+
# where句で抽出条件を指定できます。
mysql> select * from staff_register where department = "Administration";
+----+----------------+--------------------------+--------+-------+---------------+
| id | department     | section                  | name   | phone | email         |
+----+----------------+--------------------------+--------+-------+---------------+
|  4 | Administration | Administration           | Noah   |  NULL | ddd@gmail.com |
|  6 | Administration | Learning Resouces Centre | Sophia |  NULL | NULL          |
|  7 | Administration | Maintenance              | Chloe  |  NULL | fff@yahoo.com |
|  8 | Administration | Supplies & Contracts     | George |  NULL | ggg@yahoo.com |
+----+----------------+--------------------------+--------+-------+---------------+

# LIKE演算子とワイルドカード文字「%」を使用して、文字列の部分一致条件を指定することもできます。
mysql> select * from staff_register where email like '%yahoo.com';
+----+----------------+----------------------+--------+-------+---------------+
| id | department     | section              | name   | phone | email         |
+----+----------------+----------------------+--------+-------+---------------+
|  7 | Administration | Maintenance          | Chloe  |  NULL | fff@yahoo.com |
|  8 | Administration | Supplies & Contracts | George |  NULL | ggg@yahoo.com |
+----+----------------+----------------------+--------+-------+---------------+

作業用ユーザでテーブルとデータベースを削除

mysql> truncate table staff_register;	# staff_registerテーブル内データ削除
mysql> select * from staff_register;		# データ削除確認
mysql> drop table staff_register;		# staff_registerテーブル削除
mysql> show tables;						# テーブル削除確認

mysql> drop database college;			# collegeDB削除
mysql> show databases;					# DB削除確認
mysql> exit

ユーザ削除

$ mysql -u root -p
Enter password:							# パスワード入力
mysql> revoke all privileges on *.* from test@localhost;	# testユーザから全てのデータベースへのアクセス権限を剥奪
mysql> drop user test@localhost;			# testユーザ削除
mysql> select user from mysql.user;		# testユーザ削除確認
mysql> flush privileges;					# testユーザの削除をMySQLサーバーへ反映
mysql> exit								# データベースから抜ける

参考
MySQLでテーブルを作成する方法
MySQLの操作