無料オープンソースDB MySQL入門 その2 – CRUD操作をしてみる –
この記事は前回の記事の続きです。
CRUDとは
CRUDとは、 Create（作成） Read（参照） Update（更新） Delete（削除） の頭文字をとって命名されたデータ操作の種類のことで、データベース操作の基本になります。
前回の記事では、MySQLをインストールし、データーベースを作成しましたが今回は作成したデータベースにテーブルを作成してCRUD操作をしていきます。
テーブルを作成
それではCRUD用のテーブルを作成していきます。 作成するテーブルは次のようなユーザテーブルです。Users
| カラム名 | 型 | オプション | 説明 |
|---|---|---|---|
| ID | INTEGER | PRIMARY KEY, AUTO_INCREMENT | ユーザーを一意に識別するID |
| NAME | VACHAR | NOT NULL | ユーザー名 |
| VARCHAR | NOT NULL | メールアドレス | |
| AGE | INTEGER | 年齢 | |
| CREATED_AT | DATETIME | 作成日時 | |
| UPDATED_AT | DATETIME | 更新日時 |
そして、このテーブルを作成するSQL文が以下になります。
create table users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
age INTEGER,
created_at DATETIME,
updated_at DATETIME);CREAT TABLEの基本的な文法としては、
create table [ユーザ名] ( [カラム名1] [型1] [オプション1],[カラム名2] [型2] [オプション2] ...)という形でテーブル定義をしていきます。先ほど作成したテーブルを確認するにはSHOW COLUMNSでカラム定義を確認します。(ちなみにSQLでは大文字小文字が区別されないです。）
テーブル作成のSQL文で指定している型ですが、これはデータの型になります。データの型には文字列を扱うVARCHAR、数値を扱うINTEGER、日付や日時を扱うデータ型DATETIME型など様々ありますが、各カラムで表現したいデータに合わせてデータ型を選択し定義を行なっていきます。
また、上ではオプションとしていますが、カラムの定義には、数値が設定されていないことを表すNULLを許容しないNOT NULL制約やIDの自動インクリメント（数値が自動的に繰り上がっていく）、主キーの設定をすることができます。
主キーとは、それぞれのレコードを一意に識別するためのカラムで、そのIDが他のレコードと重複せず、カラムの値でレコードを特定できるカラムです。
Create | INSERTでレコードを作成
テーブルが作成できたので実際にレコードを作成してみます。レコードを作成するのに使うSQL構文はINSERT文です。レコードを作成するときはCREATEではなく、INSERT(挿入する）ですので注意しましょう。
INSERT INTO users (name, email, age, created_at, updated_at)
VALUES('John', 'john@example.com', 20, NOW(), NOW())INSERT文はこのような形で挿入するテーブル、カラムとその値を指定してレコードを挿入します。
複数のカラムを挿入したい場合は次のようにします。
INSERT INTO users (name, email, age, created_at, updated_at)
VALUES('Mary', 'mary@example.com', 20, NOW(), NOW()),
('Sam', 'sam@example.com', 22, NOW(), NOW()),
('Brian', 'brian@example.com', 30, NOW(), NOW()),また、次のようにnameを指定しない形でのINSERT文もSQLの構文としては正しいですが、前の段階でnameカラム自体にNOT NULL（空でない）制約をかけているのでnameを省略するとエラーになります。
> INSERT INTO users ( email, age, created_at, updated_at)
-> VALUES('alice@example.com', 20, NOW(), NOW());
ERROR 1364 (HY000): Field 'name' doesn't have a default valueここでは、NOT NULL制約をかけているのでデフォルトの値がないよと言われてしまっています。
Read | SELECTでレコードを参照
前項で、レコードのCREATEはできるようになったので作ったレコードを確認してみましょう。テーブルのレコードを参照する場合にはSELECT文を利用します。
SELECT * FROM users;上のSQLはテーブルの全レコードを表示するSQLです。結果は次の通りです。
これでさきほどのデータが挿入されていることがわかります。SELECTは参照のためのSQLなので条件を絞って検索することができます。以下は30歳以上のユーザを表示する場合です。
SELECT * FROM users where age >= 30;
条件をしぼりこむにはwhere句を利用します。いくつか例をしめすと
名前がJohn
SELECT * FROM users where name = 'John';名前にaが含まれる。
SELECT * FROM users where name like '%a%';名前にaが含まれるかつ30歳以上の人。
SELECT * FROM users where name like '%a%' and age >= 30;名前に"a"が含まれるか"o"が含まれる人。
SELECT * FROM users where name like '%a%' or name like '%o%';作成日付が2018年3月25日より前のレコード
SELECT * FROM users where created_at <= '2018-3-25';のように様々な形で条件を書くことができます。それぞれの条件に対してAND条件やOR条件を設定できるのでより複雑なクエリにも対応できます。
Update | UPDATEでレコードを更新
次は更新処理です。下記はJohnの名前を'XXX'という形に更新するSQLです。UPDATE users SET name = 'XXXX' where name = 'John';もう少し厳密にやろうとすると更新日付も変更しないといけないので、
UPDATE users SET name = 'XXXX', updated_at = NOW() where name = 'John';こうすると同時に複数のカラムを更新することもできます。
また、UPDATEは少し取り扱いに注意が必要なSQLでうっかりWHERE句をわすれてしまうとテーブルの全レコードを更新してしまいます。UPDATE文を発行する場合には必ず事前に条件を確認するようにしましょう。
Delete | DELETEでレコードを削除
最後にDELETE文をやってCRUDの処理は完成です。構文は簡単で
DELELTE FROM users;ですが、これだとテーブルの全レコードを削除（俗にいうバルス）してしまうので、UPDATEと同様条件で絞り込みをして削除ましょう。
DELETE FROM users where age >= 30;
まとめ
ここまでで、CRUD操作を一通り説明したのでRDBS操作の基本はおさえられました。あとは、「自分の中でこうしたいんだけど」というのを適宜調べてSQL操作になれていきましょう。 Railsなどのフレームワークを使い始めるとSQLを書く機会が極端に減りますがフレームワークはこういった前提知識を持った上で取り組むものなので、Databaseの基本操作もしっかり身につけていきましょう。