皆さんMySQL使ってますか？この記事ではMySQLの基本であるテーブルの基礎を学習します。テーブルといえばデータベースの情報が詰まったSQLの核心部分。
MySQLはテーブルの操作に始まりテーブルの操作に終わると言ってもいいくらい重要な部分です。まずは基礎的な部分をきっちり習得し、より応用的な操作ができる下地を作っていきます。さっそくいってみましょう！
テーブルの作成
まずはテーブルを作成するところから始めましょう。テーブルを作成するには、利用するデータベースが選択されている必要があります。データベースの作成と選択はこちらの記事を参照してください。
この記事ではデータベース名、「table_test_db」に対してテーブルの追加や削除を行っていきます。以下のクエリでデータベースの作成と選択を済ませておいてください。
CREATE DATABASE table_test_db; USE table_test_db;
テーブル作成のクエリ
テーブル作成クエリの書式は次のようになります。
CREATE TABLE テーブル名 (
カラム名1 データ型 オプション,
カラム名2 データ型 オプション,
カラム名3 データ型 オプション,
……
);
CREATE TABLEでテーブル名を指定したあと、かっこ内部でカラムを定義していきます。細かい説明はさておき、実際にテーブルを1つ作ってみましょう。以下のクエリを実行してみてください。
CREATE TABLE tb1( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10) DEFAULT 'no_name', age INT(3) );
このような表示が出ればオッケーです。
Query OK, 0 rows affected (0.14 sec)
データ型
データ型はカラムに入力できるデータの種類を限定します。
例えばVARCHAR（整数）型は整数の数を上限とする文字列が入力できるデータ型。INT（整数）は整数の数を上限とした桁数の整数が入力できるデータ型、という具合です。
データ型は非常に細かく設定することができ、上手に設定することでデータベースの信頼性や速度も上がります。が、初心者のうちは上で紹介したVARCHAR型とINT型あたりが使えれば、しばらくは困らないでしょう。
より多くのデータ型を知りたい場合は公式リファレンスをご参照ください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 11 データ型
オプションの設定
テーブル作成では、カラムにオプションを設定することができます。例えば値にNULLを許可しなかったり、連番で埋める機能を追加したりと様々なオプションがあります。ここではよく使われるオプションを紹介します。
NULLを許可しない
書式
NOT NULLオプションが指定されるとそのカラムではNULL値が許可されなくなります。かならず値を設定しなければいけなくなるので手間は増えますが、データベースとしての信頼性は向上します。
主キーを設定する
書式
主キーとはこれを含むカラムで、その値を指定すればレコードが一意に決定できる情報という意味です。現実ではIDやマイナンバーなどの重複がなく、個人を特定できるものに相当します。
プライマリーキーとも呼ばれますね。データが一意に識別できますし、レスポンスの向上などにもつながるので、何はともあれ設定しておくのがセオリーでしょう。
デフォルト値を設定する
書式
カラムへデータを追加した時、値を指定されないときにはデフォルト値が入るようにするオプションです。
連番を挿入する
書式
データが追加されるごとに1ずつ増えていく連番がカラムに挿入されるようになります。IDのようにデータごとに数字を変えたい場面で役に立つオプションですね。
ただし制約も多く、デフォルト値が設定できない、正の整数しか使えない、データを削除したとしても連番は減らない、テーブル内の一つのカラムにしか設定できないなどがあります。
テーブルの一覧取得
この章ではテーブルの一覧を取得するクエリを紹介します。
書式
たとえば、デフォルトで存在するデータベースのmysqlからテーブルの一覧を取得するには次のクエリを使います。
SHOW TABLES FROM mysql;
+---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | password_history | | plugin | | procs_priv | | proxies_priv | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 33 rows in set (0.01 sec)
mysqlデータベースには、このように基本となる様々なテーブルが収められているのがわかるかと思います。表の一番下をみると、含まれているテーブルの数もわかりますね。
ユーザーデータなども入っているので、ここにアクセスするとユーザー関連の色々な情報を引き出すこともできます。そういったことに興味のある方はこちらの記事もどうぞ。
ただ、ここまで量があると、何か探している時に少々見つけるのが大変です。こんなときに役に立つのがLIKE句です。
LIKE句を使うことであいまい検索ができるようになるのです。LIKE句を使ったテーブルの一覧表示の書式は次のようになります。
検索条件は探したい単語とワイルドカードを組み合わせてつくります。MySQLでつかえるワイルドカードはアンダーバー「_」とパーセント「%」。
それぞれ
- _：任意の1文字
- %：任意の0文字以上の文字列
に対応します。たとえば、mysqlデータベースからtimeが頭につくテーブルのみを抽出する場合、クエリは次のようになります。
SHOW TABLES FROM mysql LIKE 'time%';
実行結果
+---------------------------+ | Tables_in_mysql (time%) | +---------------------------+ | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | +---------------------------+ 5 rows in set (0.01 sec)
確かに頭にtimeを含むテーブルのみを抽出する事ができました。
テーブルへのデータ追加・データの表示
この章ではテーブルへのデータの追加を扱います。先程作成したテーブル、tb1にデータの追加していきましょう。データの追加の書式は次のようになります。
tb1にデータを追加するクエリは次のようになります。
INSERT INTO tb1(name, age) VALUES('タロー', 25);
実行結果
Query OK, 1 row affected (0.04 sec)
tb1ではIDカラムにAUTO_INCREMENTを設定しているので、値の入力は不要です。では今追加したデータを表示してみましょう。テーブルの表示にはSELECTを用います。
書式
実行するクエリは次のようになります。
SELECT * FROM tb1;
実行結果
+----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | タロー | 25 | +----+-----------+------+ 1 row in set (0.00 sec)
なお、SELECTは様々な使い方が可能な奥の深いクエリです。より深く学びたい方はこちらの記事もご覧ください。
テーブルの削除
この章ではテーブルの削除を扱います。先に注意点を1つ。MySQLに限らず、データベースにおける削除は基本もとに戻すことができません。
MySQLの練習として作成・削除を繰り返すのは問題ありませんが、本番環境などの間違えが許されない環境で実行する際は細心の注意を払うように常に心がけてください。
書式
実際のクエリは次のようになります。
DROP TABLE tb1;
実行結果
Query OK, 0 rows affected (0.14 sec)
SHOW TABLESで削除できたことを確認してください。
SHOW TABLES;
実行結果
Empty set (0.00 sec)
テーブルの行数を取得する
最後に少し応用技術を2つほど紹介します。1つ目がテーブルの行数、レコードの数を数える方法。実践のために下記クエリを実行し、テスト用のテーブルを作成してください。
CREATE TABLE tb2(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10) DEFAULT 'no_name',
age INT(3)
);
INSERT INTO tb2(name, age) VALUES
('タロー', 25),
('ジロー', 24),
('サブロー', 23),
('シロー', 22),
('ゴロー', 20);
まずはテーブルの行数を数える方法でした。これはテーブルの大きさを調べるときに役に立ちます。
書式
これはCOUNTという関数を使ったSELECT文になります。COUNT関数は引数のカラムの総数を返すので、適切なカラムを使うことでテーブルの行数を調べることができるわけです。実際にクエリを書くと次のようになります。
SELECT COUNT(id) FROM tb2;
実行結果
+-----------+ | COUNT(id) | +-----------+ | 5 | +-----------+ 1 row in set (0.00 sec)
確かにレコードは5行あったので、カウントが5で正しく集計されているのがわかりますね。
テーブルのカラム情報を取得する
応用編その2はテーブルのカラム情報を取得する方法です。カラム情報とは、そのカラムのデータ型だったり、オプションの適用状態のことです。データが上手く挿入できない時はカラム情報を確認すると、間違えに気づくことができて便利ですよ。
書式
tb2で動かしてみると次のようになります。
DESC tb2;
実行結果
+-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | no_name | | | age | int(3) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.02 sec)
データ型や主キー、初期値、オートインクリメントの有無などがひと目でわかって便利ですね。
まとめ
いかがでしたか？今回はテーブルについて、作成・一覧表示・データ追加・削除・レコード数の取得・カラム情報の取得までお伝えしました。各項目について、本当にはじめの一歩の部分しか紹介できていませんが、それでもなかなかの量になってしまいましたね。
MySQLに入門したての人は、まずこの記事の操作をマスターすると、より応用的な操作も習得しやすくなります。一歩一歩着実に学習を進めていきましょう。この記事が皆様のMySQL学習の一助となれば幸いです。
