こんばんは。itoshinです。
前回はテーブルに収めるレコードを分かりやすく整理するために、フィールドというデータ項目を追加・名前/データ型の変更までやりました！
今日はテーブルに収めるデータ、レコードの登録をしていきます。
レコードの登録
insert into usr (uid, passwd, uname, family)
values ('yyamada', '12345', '山田太郎', 3);
[usrテーブルのuid, passwd, uname, familyという項目ごとに]
['yyamada', '12345', '山田太郎', 3 という値をそれぞれに登録して。]
覚えるべき構文は次の通り
insert into テーブル名 (フィールド名1, フィールド名2, ...)
values (フィールド1の値, フィールド2の値, ...)
mysql> insert into usr (uid, passwd, uname, family)
-> values ('yyamada', '12345', '山田太郎', 3);
Query OK, 1 row affected (0.02 sec)
省略構文で新規レコードを登録
フィールド数が多くていちいち書きたくない...
そんな時は省略構文でもレコード登録することができます。
insert into usr values('ssuzuki', '98765', '鈴木正一', 4);
[usrテーブル(の、uid, passwd, uname, family項目ごと)に'ssuzuki', '98765', '鈴木正一', 4 という値をそれぞれに登録して。]
構文
insert into テーブル名 values (フィールド1の値, フィールド2の値, ...)
mysql> insert into usr
-> values('ssuzuki', '98765', '鈴木正一', 4);
Query OK, 1 row affected (0.04 sec)
省略できるとかなり楽ですが、大きなプログラムでinsert構文を使う場合は可能な限り省略せずに命令を出す方が望ましいようです。
理由はフィールド数や順番が変更された場合、混乱してしまうことやミスを後から気付きにくいからです。
レコードの検索(&絞り込み)
登録した多数のレコードが並ぶさまを見れたら、いよいよデータベースを作成している実感も湧いてくるかもしれません。検索して表示してみましょう。
select * from usr;
[usrテーブルにある全て(*)のレコードを参照して。]
構文
select フィールド名, ... from テーブル名
mysql> select * from usr;
+---------+--------+----------+--------+
| uid | passwd | uname | family |
+---------+--------+----------+--------+
| yyamada | 12345 | 山田太郎 | 3 |
+---------+--------+----------+--------+
1 row in set (0.01 sec)
もちろん、表示するレコードを絞り込むこともできます。↓
mysql> select uid, passwd from usr;
+---------+--------+
| uid | passwd |
+---------+--------+
| yyamada | 12345 |
| ssuzuki | 98765 |
+---------+--------+
2 rows in set (0.00 sec)
データ型を定義したフィールドが正しく機能しているか
ところで、フィールドに指定するデータ型とはなんぞや？
Excelをある程度マスターしてる方は馴染みがあると思うのですが、
フィールドに入力できる値を文字列か、数字か、それとも全く違うものを扱うのかを
指定してしまうのです。
入力形式を予め設定してしまうことでレコードの記憶容量を節約したり、視認性も上がり統一感が出ますね。
↓の入力ではfamilyフィールドに1ではなく1人と余計な漢字も入力してしまい、エラーを返されています。
厳しいですが、小さな積み重ねが後々に影響してきますからね。
mysql> insert into usr
-> (uid, passwd, uname, family)
-> values('tsatou', '13579', '佐藤一郎', '1人');
ERROR 1265 (01000): Data truncated for column 'family' at row 1
主キー制約(重複防止も確認)
今でこそExcelでも各シート間を跨いでデータを統合できるようになっていますが、MySQL（データベース）もデータの統合をすることができます。
そして、テーブルの中でレコードを一意（唯一）に特定するための機能もあります。
主キー設定です。
例えば、社員番号や商品コード、マイナンバーなどがそうですね。
テーブルにつき１フィールドのみ主キーを設定でき、そのフィールドには重複した値や空の値（NULL）が入力できなくなります。
alter table usr add primary key (uid);
[usrテーブルのuidフィールドにプライマリーキー(主キー)を追加（設定）して。]
mysql> alter table usr add primary key (uid);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
↓ちゃんと設定されてるか確認（uidのKey項目にプライマリーキー[PRI]）
mysql> show fields from usr;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| uid | varchar(7) | NO | PRI | NULL | |
| passwd | varchar(15) | YES | | NULL | |
| uname | varchar(20) | YES | | NULL | |
| family | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
↓uidの重複やその他の値が異なっても、主キーに設定したuidフィールドで一意のレコードを新たに登録する必要があり、エラーを返される。
mysql> insert into usr
-> (uid, passwd, uname, family)
-> values('hinoue', '24680', '井上花子', 4);
Query OK, 1 row affected (0.01 sec)
mysql> insert into usr
-> (uid, passwd, uname, family)
-> values('hinoue', '13579', '井上秀子', 3);
ERROR 1062 (23000): Duplicate entry 'hinoue' for key 'usr.PRIMARY'
オートインクリメント
さて、主キーの設定による制約で一意のフィールドができることは分かりました。
では、主キーになりうるフィールドがない場合はどうするかを解決してくれるのが、オートインクリメント（自動連番）機能です。
レコードを入力していく度に「1，2，3、...」という風に一意のレコード登録をしていけるフィールドを作成するだけです。
create table schedule
(pid int auto_increment, uid varchar(7),
subject varchar(100), pdate date, ptime time,
cid int, memo text, primary key(pid));
[scheduleテーブルを作って]
[pidを整数の自動連番で, uidを7文字以内の文字列で,..（省略）..プライマリーキーをpidに設定して。]
構文
create table テーブル名 （フィールド名 データ型 auto_increment, ...）
注意しておきたいのがオートインクリメントは
『整数（INT）型のフィールドのみに設定できる』点です。
事前にフィールドのデータ型を確認しておくことをお勧めします。
mysql> create table schedule
-> (pid int auto_increment, uid varchar(7),
-> subject varchar(100), pdate date, ptime time,
-> cid int, memo text, primary key(pid));
Query OK, 0 rows affected (0.07 sec)
NOT NULL制約
レコードを登録する際、必ず入力してもらわないといけない情報があるはずです。パスワードやユーザ名、連絡先が必要なら電話番号やメールアドレスもですね。
デフォルトでは無記入だとNULL値が当てられ、未定義な値として表示されることになります。
ですので、このNULL値を禁止することでレコード登録するには指定したフィールドに必ず値を入力させることが可能になります。
alter table usr
modify passwd varchar(15) not null,
modify uname varchar(20) not null;
[usrテーブルのpasswd(データ型含むフィールド定義)フィールドをNULL禁止、uname(データ型含むフィールド定義)フィールドをNULL禁止に変えて]
mysql> alter table usr
-> modify passwd varchar(15) not null,
-> modify uname varchar(20) not null;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show fields from usr;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| uid | varchar(7) | NO | PRI | NULL | |
| passwd | varchar(15) | NO | | NULL | |
| uname | varchar(20) | NO | | NULL | |
| family | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
↑確認するとNULLの行のuid,passwd,unameはNOとあり、NULL状態に制約を掛けることができました。
↓試しにnot nullしたunameフィールドにはレコードを登録しない形で入力してしてます。
mysql> insert into usr (uid, passwd, family)
-> values ('nharada', '01230', 3);
ERROR 1364 (HY000): Field 'uname' doesn't have a default value
エラーを出されてしまったので、unameフィールドもちゃんと値を入力すると↓
mysql> insert into usr (uid, passwd, uname, family)
-> values ('nharada', '01230','原田直樹', 3);
Query OK, 1 row affected (0.01 sec)
mysql> select * from usr;
+---------+--------+----------+--------+
| uid | passwd | uname | family |
+---------+--------+----------+--------+
| hinoue | 24680 | 井上花子 | 4 |
| nharada | 01230 | 原田直樹 | 3 |
| ssuzuki | 98765 | 鈴木正一 | 4 |
| tsatou | 13579 | 佐藤一郎 | 1 |
| yyamada | 12345 | 山田太郎 | 3 |
+---------+--------+----------+--------+
5 rows in set (0.01 sec)
↑命令が受付けられ、無事レコードを登録することができました。
デフォルト値
デフォルト値とはフィールドに値が指定されなかった場合に自動的にセットされる値です。
前項でいうと、NULLですね。通常はNOT NULL制約がない場合、デフォルトで'NULL'と表示されます。
そのデフォルト値を任意の値に変更することが可能です。
alter table usr
alter passwd set default '後で変更';
[usrテーブルの変更、passwdフィールドのデフォルト値を'後で変更'に変えて]
mysql> alter table usr
-> alter passwd set default '後で変更';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show fields from usr;
+--------+-------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+----------+-------+
| uid | varchar(7) | NO | PRI | NULL | |
| passwd | varchar(15) | NO | | 後で変更 | |
| uname | varchar(20) | NO | | ゲスト | |
| family | int | YES | | NULL | |
+--------+-------------+------+-----+----------+-------+
4 rows in set (0.00 sec)
外部キー
さて、主流となっているリレーショナルデータベースで主キー（プライマリーキー）と同じくらい重要な概念となるのが、外部キー（命令文では'foreign key'）です。
主キーと外部キーとの対応関係によって、異なるテーブル間のレコードを紐付けできます。
この対応関係を保つ機能が外部キー制約（参照性制約）。
外部キーを設定することで、
- 外部キーを追加/更新するときに対応する主キーが存在するか
- 主キーを削除するときに対応する外部キーが存在しないか
などをデータベースがチェックをしてくれるというものです。
alter table schedule
add foreign key (uid) references usr (uid);
[scheduleテーブルの編集]
[uidフィールドに外部キーを付けて。参照先（事前に設定してある主キー）はusrテーブルのuidフィールドね]
mysql> alter table schedule
-> add foreign key (uid) references usr (uid);
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show fields from schedule;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| pid | int | NO | PRI | NULL | auto_increment |
| uid | varchar(7) | YES | MUL | NULL | |
| subject | varchar(100) | YES | | NULL | |
| pdate | date | YES | | NULL | |
| ptime | time | YES | | NULL | |
| cid | int | YES | | NULL | |
| memo | text | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
↑外部キーはkeyの行に'MUL'と表示されます。
↓主キー（参照先）のあるテーブルはscheduleテーブルとは別のusrテーブル
mysql> show fields from usr;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| uid | varchar(7) | NO | PRI | NULL | |
| passwd | varchar(15) | NO | | NULL | |
| uname | varchar(20) | NO | | ゲスト | |
| family | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
↓主キー（参照先）となっているusrテーブルの'yyamada'に関するレコードを消去しようとすると・・・
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 | 山田祥寛 | 5 |
+---------+--------+----------+--------+
7 rows in set (0.00 sec)
mysql> delete from usr where uid = 'yyamada';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`basic`.`schedule`, CONSTRAINT `schedule_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `usr` (`uid`))
↑外部キーと結びついてるため、削除できません。
また、主キーに設定されていない値を外部キー（scheduleテーブルのuid）に追加しようとすると、参照先に存在しない値のためエラーで返してくれます。↓
mysql> insert into schedule
-> (uid, subject, pdate, ptime, cid, memo)
-> values ('nyamada', '病院', '2023-08-03', '9:00', 3, '午後から出社');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`basic`.`schedule`, CONSTRAINT `schedule_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `usr` (`uid`))
mysql>
↑'nyamada'さんに当てはまるデータが参照先usrテーブルにいないぞ。と・・・、
なので、新たな人のレコードを外部キーに追加する際は先に主キーにレコード登録しておく必要があるわけです。ユーザー情報が無いのに予定を押し込まれることが無くなりますね。
今回はここまで！
反省点
数日空いてしまったけど、無理に取り戻そうとしないことにします。
Qiitaで復習しなくても、実践繰り返せば習得早そうなんだよなぁとか・・・、
思ってしまってはいるけども、どうにかもう少し楽に記事制作するために語り口調を減らすとか、見せ方を変えるべきだろうか。
そもそも何のためにQiitaで再度書くか、それも自分なりの解釈を言葉にして。
自分の習得度を上げる為のアウトプットとして適当か、は再考してみてもいいかもしれない（記事制作レベルを簡素にする？）
MySQLはとにかく実践し、制作物を作り上げることの方がより理解度の高まるアウトプットだろうと思う。
よかった点
数日空いてしまったが再開することができた。復習のやり方に改善点を見出せた。MySQLで現実の問題解決できる制作物をアウトプットしてみたい欲が出た。
お疲れさまでした！
MySQLの復習用記事はまだ3,4回分あります（下書きが）