こんばんにちは！itoshinです。
前回はMySQL レコードの登録から外部キーの制約まで
レコード操作、データ型、制約系の学習をしてきました。
今記事では
データベース・テーブルの作成、レコードの登録が済み、ある程度形になってきたデータベースの操作言語について触れていきます。
データベースの展開
この項では展開用のサンプルデータベースを扱います。
データベースのバックアップや他の環境に移動させる際にも有効な操作なので覚えておきたい。
まず、○○.sqlファイルをダウンロードします。
これまでbasicデータベースに作成したデータは必要ないため、削除し、新しくbasicデータベースを作り、その中にsql拡張子ファイルを展開します。
mysql> drop database basic;
Query OK, 0 rows affected (0.01 sec)
mysql> create database basic;
Query OK, 1 row affected (0.01 sec)
展開先のデータベースを選択
use basic;
source構文にsqlファイルがあるパスを指定し、展開
source c:\data\basic.sql
mysql> use basic;
Database changed
mysql> source c:\data\basic.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
-----------------(省略) -------------------------------------
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_basic |
+-----------------+
| category |
| schedule |
| usr |
+-----------------+
3 rows in set (0.00 sec)
↑ファイル内のデータがbasicデータベースに移動できました！
重複の除去
レコードが多くなってくると、内容が重複してくることがありますね。
一意のレコードだけにまとめて表示したいことがあります。
その際はselect構文にdistinctを用いると重複行を除いて表示ができるようです。
↓実際のフィールド内のレコードがいくつか重複していても
mysql> select uid from schedule;
+---------+
| uid |
+---------+
| hinoue |
| nkakeya |
| nkakeya |
| nkakeya |
| ssuzuki |
| tsatou |
| yyamada |
| yyamada |
| yyamada |
| yyamada |
+---------+
10 rows in set (0.00 sec)
｛select distinct フィールド名 from テーブル名｝
select distinct uid from schedule;
[scheduleテーブルのuidフィールドから重複を除いて表示して]
mysql> select distinct uid from schedule;
+---------+
| uid |
+---------+
| hinoue |
| nkakeya |
| ssuzuki |
| tsatou |
| yyamada |
+---------+
5 rows in set (0.00 sec)
↑'nkakeya','yymada'の重複行なしで表示できました
↓当然複数列でも可能です！
mysql> select distinct uid, cid from schedule;
+---------+------+
| uid | cid |
+---------+------+
| yyamada | 1 |
| tsatou | 3 |
| yyamada | 3 |
| yyamada | 5 |
| nkakeya | 1 |
| nkakeya | 2 |
| ssuzuki | 1 |
| hinoue | 4 |
| nkakeya | 5 |
+---------+------+
9 rows in set (0.00 sec)
レコードの絞り込み
前項では重複を除いて表示できたけど、ならば指定した条件のレコードだけ表示したい場合もあるはず。
その場合はselect構文にwhereという句を使ってレコードを絞り込めます。
↓普通に全て表示すると、知りたい情報だけを見つけにくい・・
mysql> select * from schedule;
+-----+---------+-----------------+------------+----------+------+------------------+
| pid | uid | subject | pdate | ptime | cid | memo |
+-----+---------+-----------------+------------+----------+------+------------------+
| 1 | yyamada | WINGS会議 | 2018-06-25 | 15:00:00 | 1 | 配布プリント持参 |
| 2 | tsatou | B企画書提出 | 2018-07-05 | 17:00:00 | 3 | サンプル添付 |
| 3 | yyamada | MySQL本原稿提出 | 2018-07-31 | 17:00:00 | 3 | NULL |
| 4 | yyamada | WINGSメンバ面接 | 2018-08-05 | 13:00:00 | 5 | NULL |
| 5 | nkakeya | WINGS会議 | 2018-06-25 | 14:00:00 | 1 | 事前に会場準備 |
| 6 | nkakeya | C社打ち合わせ | 2018-07-31 | 14:00:00 | 2 | NULL |
| 7 | ssuzuki | WINGS会議 | 2018-06-25 | 15:00:00 | 1 | ファイル持参 |
| 8 | hinoue | 小学校参観日 | 2018-08-10 | 14:00:00 | 4 | NULL |
| 9 | yyamada | D企画打ち上げ | 2018-08-21 | 18:00:00 | 5 | NULL |
| 10 | nkakeya | D企画打ち上げ | 2018-08-21 | 18:00:00 | 5 | 出席者確認 |
+-----+---------+-----------------+------------+----------+------+------------------+
10 rows in set (0.00 sec)
なので、selectする条件を指定してしまえばいい！
select * from schedule
where pdate = '2018-07-31';
[scheduleテーブルの全て（*）からpdateが'2018-07-31'のレコードを表示して]
mysql> select * from schedule
-> where pdate = '2018-07-31';
+-----+---------+-----------------+------------+----------+------+------+
| pid | uid | subject | pdate | ptime | cid | memo |
+-----+---------+-----------------+------------+----------+------+------+
| 3 | yyamada | MySQL本原稿提出 | 2018-07-31 | 17:00:00 | 3 | NULL |
| 6 | nkakeya | C社打ち合わせ | 2018-07-31 | 14:00:00 | 2 | NULL |
+-----+---------+-----------------+------------+----------+------+------+
2 rows in set (0.00 sec)
↑とてもシンプルになり、二人がその日は何をしていたか分かりました。
あいまい検索
人は忘れやすい生き物です。一文字二文字は分かるのに名称が思い出せない。
そんなことは多々あるはずです。（外部キーがとっさに出てこない私のように）
そんなときに大変便利なのが、Like演算子とワイルドカードなんですね。
実際にwings~と後に続くイベント名を忘れてしまったら、
select構文に表示したいテーブルのフィールドと、
前項のレコードを条件で絞り込むためのwhereで指定して、
select uid, subject, pdate, ptime, memo from schedule
where subject like 'wings%';
[scheduleテーブルのuid, subject, pdate, ptime, memoを]
[subject欄がwings～のレコードだけ絞って表示して]
mysql> select uid, subject, pdate, ptime, memo from schedule
-> where subject like 'wings%';
+---------+-----------------+------------+----------+------------------+
| uid | subject | pdate | ptime | memo |
+---------+-----------------+------------+----------+------------------+
| yyamada | WINGS会議 | 2018-06-25 | 15:00:00 | 配布プリント持参 |
| yyamada | WINGSメンバ面接 | 2018-08-05 | 13:00:00 | NULL |
| nkakeya | WINGS会議 | 2018-06-25 | 14:00:00 | 事前に会場準備 |
| ssuzuki | WINGS会議 | 2018-06-25 | 15:00:00 | ファイル持参 |
+---------+-----------------+------------+----------+------------------+
4 rows in set (0.00 sec)
↑wing~に当てはまるレコードだけを絞り込み検索することができました
論理演算子(or,and)
whereで指定できる条件は一つだけではありません。
orやandなどの論理演算子を使用することで複数の条件を指定してより細かくレコード絞り込みできるようになります。
では、andを使って絞り込んでみます。
select subject, pdate, ptime, cid from schedule
where cid = 3 and pdate >= '2018-07-10'
[scheduleテーブルのsubject, pdate, ptime, cidを表示]
[cidの値が3かつ、pdateの値が2018-07-10以降の条件で]
mysql> select subject , pdate, ptime, cid from schedule
-> where cid = 3 and pdate >= '2018-07-10';
+-----------------+------------+----------+------+
| subject | pdate | ptime | cid |
+-----------------+------------+----------+------+
| MySQL本原稿提出 | 2018-07-31 | 17:00:00 | 3 |
+-----------------+------------+----------+------+
1 row in set (0.03 sec)
条件が厳しいので一件のみ表示されました。↑
andの部分をorに変えて、条件を緩和してみます↓
select subject , pdate, ptime, cid from schedule
where cid = 3 or pdate >= '2018-07-10'
mysql> select subject , pdate, ptime, cid from schedule
-> where cid = 3 or pdate >= '2018-07-10';
[scheduleテーブルのsubject, pdate, ptime, cidを表示]
[cidの値が3、または、pdateの値が2018-07-10以降の条件で]
+-----------------+------------+----------+------+
| subject | pdate | ptime | cid |
+-----------------+------------+----------+------+
| B企画書提出 | 2018-07-05 | 17:00:00 | 3 |
| MySQL本原稿提出 | 2018-07-31 | 17:00:00 | 3 |
| WINGSメンバ面接 | 2018-08-05 | 13:00:00 | 5 |
| C社打ち合わせ | 2018-07-31 | 14:00:00 | 2 |
| 小学校参観日 | 2018-08-10 | 14:00:00 | 4 |
| D企画打ち上げ | 2018-08-21 | 18:00:00 | 5 |
| D企画打ち上げ | 2018-08-21 | 18:00:00 | 5 |
+-----------------+------------+----------+------+
7 rows in set (0.00 sec)
レコードの更新
例えば、顧客の家族構成が新たな命を迎えることになって
扶養する人数が増え、レコードを登録し直さなければならない時が必ずあるでしょう。
そういう時、今までのレコードを削除して新しく登録するにはとても手間が掛かります。
なので、update...set構文でレコードを更新するのが楽です。
↓更新前のレコード。家族人数は３。
mysql> select * from usr
-> where uid = 'yyamada';
+---------+--------+----------+--------+
| uid | passwd | uname | family |
+---------+--------+----------+--------+
| yyamada | 12345 | 山田祥寛 | 3 |
+---------+--------+----------+--------+
1 row in set (0.00 sec)
update usr set family = family + 1
where uid = 'yyamada';
[usrテーブルのuidの値がyymadaの、familyの値に+1して更新]
mysql> update usr set family = family + 1
-> where uid = 'yyamada';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from usr
-> where uid = 'yyamada';
+---------+--------+----------+--------+
| uid | passwd | uname | family |
+---------+--------+----------+--------+
| yyamada | 12345 | 山田祥寛 | 4 |
+---------+--------+----------+--------+
1 row in set (0.00 sec)
↑家族数が4になりましたね。
レコードの削除
不要なレコードを削除する機会も多々あることでしょう。
↓まずはレコードやフィールドの確認を忘れずに
mysql> select * from usr;
+---------+--------+----------+--------+
| uid | passwd | uname | family |
+---------+--------+----------+--------+
| hinoue | 24680 | 井上花子 | 4 |
| hsugita | 45231 | ゲスト | 3 |
| mtanaka | 00112 | 田中美紀 | NULL |
| nharada | 01230 | 原田直樹 | 3 |
| nkakeya | 73440 | 掛谷奈美 | 5 |
| ssuzuki | 98765 | 鈴木正一 | 4 |
| tsatou | 13579 | 佐藤留吉 | 1 |
| yyamada | 12345 | 山田祥寛 | 4 |
+---------+--------+----------+--------+
8 rows in set (0.00 sec)
ユーザーネームがデフォルト制約によってゲストになっているレコードを削除してしまいましょう。
レコードの削除はdelete構文を用います。
delete from usr
where uid = 'hsugita';
[usrテーブルのuidの値が'hsugita'のレコードを削除]
mysql> delete from usr
-> where uid = 'hsugita';
Query OK, 1 row affected (0.01 sec)
mysql> select * from usr;
+---------+--------+----------+--------+
| uid | passwd | uname | family |
+---------+--------+----------+--------+
| hinoue | 24680 | 井上花子 | 4 |
| mtanaka | 00112 | 田中美紀 | NULL |
| nharada | 01230 | 原田直樹 | 3 |
| nkakeya | 73440 | 掛谷奈美 | 5 |
| ssuzuki | 98765 | 鈴木正一 | 4 |
| tsatou | 13579 | 佐藤留吉 | 1 |
| yyamada | 12345 | 山田祥寛 | 4 |
+---------+--------+----------+--------+
7 rows in set (0.00 sec)
↑再度テーブルを確認すると、ユーザーネームがゲストのレコードを削除できたことが分かります。
ここまででレコードの
登録(insert文)、更新(update文)、削除(delete文)、参照(select文)といったデータベースの基本的な操作言語を学ぶことができました。
簡単なものなら自分でデータベースを構築できるようになっているでしょう。
今夜はここまで！
おやすみなさい＾＾