SQL 教育用資料

データ編集と特定データの抽出

テーブルに登録したデータの編集(更新・削除)方法を学びます。始める前に、データが少ないと説明しにくいので、前回の解答を提示しつつ、追加データを登録します。

前回の課題の解答。

INSERT INTO employeemaster VALUES ('11','宮地','勤','1','1');
INSERT INTO employeemaster VALUES ('12','鈴木','ヒカル','0','1');
INSERT INTO employeemaster VALUES ('13','佐藤','真一','1','0');
INSERT INTO employeemaster VALUES ('14','岡部','幸平','1','0');
INSERT INTO employeemaster VALUES ('15','河本','哲平','1','1');
INSERT INTO employeemaster VALUES ('16','鈴木','はるか','0','1');
INSERT INTO employeemaster VALUES ('17','花田','一徳','1','1');
INSERT INTO employeemaster VALUES ('18','野村','ヒロ','1','0');
INSERT INTO employeemaster VALUES ('19','浜崎','惇','1','0');
INSERT INTO employeemaster VALUES ('20','梅本','真希','0','1');

INSERT INTO MaterielDivMaster VALUES ('1','デスクトップパソコン',null);
INSERT INTO MaterielDivMaster VALUES ('2','ノートパソコン',null);
INSERT INTO MaterielDivMaster VALUES ('3','ディスプレイ',null);
INSERT INTO MaterielDivMaster VALUES ('4','パッケージソフトウェア',null);
INSERT INTO MaterielDivMaster VALUES ('5','シェアウェア',null);

例題と異なり、ステータスも決めてインサートしているので、ひとつ余計に加わっています。また、本来は必要ないのですが、int型もすべてシングルクォートで括って書いています。

なぜこのような解答になったかというと、ページからコピーしてテキストエディタで一括置換かけているからです。正規表現機能のあるテキストエディタで編集すると比較的楽に作成することができます。

MaterielDivMasterは、MaterielDivNoteがすべて空白になっていました。その場合、書かなくてもいいのですが、あえてnullを登録しているということを明確にするために書いています。

さらに下記の追加データを登録してください。ファイルからのインポートを使わないと手間かもしれません。

INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('101','松田','麗奈','0');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('102','森本','ヒロ','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('103','田端','直人','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('104','仲村','慶太','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('105','佐藤','恵梨香','0');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('106','奥寺','豊','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('107','佐藤','美幸','0');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('108','松岡','淳子','0');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('109','鈴木','文世','0');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('110','紺野','竜次','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('111','小木','憲一','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('112','小杉','一哉','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('113','小松','まなみ','0');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('114','鈴木','慎之介','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('115','杉下','賢治','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('116','栗林','太朗','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('117','手塚','敏和','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('118','磯村','光臣','1');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('119','佐藤','里奈','0');
INSERT INTO employeemaster (EmployeeCode, EmployeeSurname, EmployeeName, EmployeeSex) VALUES ('120','鈴木','惇','1');

INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ( '1','2','1','DoLL','INSPIRON','D0001','000001');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ( '2','2','1','DoLL','INSPIRON','D0002','000012');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ( '3','2','1','DoLL','INSPIRON','D0003','000031');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ( '4','2','1','DoLL','INSPIRON','D0004','000400');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ( '5','2','1','DoLL','INSPIRON','D0005','000512');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ( '6','1','1','DoLL','VOSTRO','DD001','001261');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ( '7','1','1','DoLL','VOSTRO','DD002','024714');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ( '8','1','1','DoLL','VOSTRO','DD003','004614');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ( '9','1','1','DoLL','VOSTRO','DD004','000335');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ('10','1','1','DoLL','VOSTRO','DD005','004812');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ('11','3','1','oIZO','S2100','E0001','021341');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ('12','3','1','oIZO','S2100','E0002','002321');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ('13','3','1','oIZO','S2100','E0003','002324');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ('14','3','1','oIZO','S2100','E0004','002325');
INSERT INTO materielmaster (MaterielCode, MaterielDivCode, MaterielStatus, MaterielMaker, ModelNo, ProductNo, SerialNo) VALUES ('15','3','1','oIZO','S2100','E0005','002365');

これで普通にSELECTをかけるとそこそこの量になったと思います。

特定データの抽出方法と表示順

さて前回はデータの登録と簡単な抽出方法を学びました。今回は編集などについて行なうのですが、その前に条件指定してデータを抽出する方法と、表示順について学びます。

まずは、条件を指定して、一件だけデータを呼び出してみます。条件の指定はWHEREを使います。では、実際に実行してみましょう。

sqlite> SELECT * FROM employeemaster WHERE employeecode = '1';
1|演習|一号|1|1
sqlite>

複数の条件をつける場合は、ANDやORで条件を繋げます。では例として、employeecodeが10未満で、なおかつemployeesexが1の人だけを抽出してみます。

sqlite> SELECT * FROM employeemaster WHERE employeecode<10 AND employeesex=1;
1|演習|一号|1|1
3|演習|三号|1|1
5|演習|五号|1|1
sqlite>

また、特定のフィールド(列)だけを抽出することも出来ます。先ほどの実行結果の中から、usercd、name、regdateだけ取り出してみましょう。

sqlite> SELECT employeecode,employeesurname,employeename FROM employeemaster
   ...> WHERE employeecode<10 AND employeesex=1;
1|演習|一号
3|演習|三号
5|演習|五号
sqlite>

データの更新(UPDATE)

データの更新方法ですが、UPDATEを使って行ないます。また、一括して更新する場合を除いて、ほとんどが条件指定をした上で更新することになります。

では、更新処理を実行してみましょう。

sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|1
2|演習|二号|0|1
3|演習|三号|1|1
4|演習|四号|0|1
5|演習|五号|1|1
sqlite> UPDATE employeemaster SET employeesex='0' WHERE employeecode='3';
sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|1
2|演習|二号|0|1
3|演習|三号|0|1
4|演習|四号|0|1
5|演習|五号|1|1
sqlite>

というような感じで更新を行ないます。また、フィールドを二箇所、同時に更新する場合は以下のように行ないます。ついでに、WHEREで範囲指定した場合も見ておきましょう。

sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|1
2|演習|二号|0|1
3|演習|三号|0|1
4|演習|四号|0|1
5|演習|五号|1|1

元データを確認してから、更新。

sqlite> UPDATE employeemaster SET employeesex='1',employeestatus='0' WHERE employeecode='4';
sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|1
2|演習|二号|0|1
3|演習|三号|0|1
4|演習|四号|1|0
5|演習|五号|1|1

変わったことが確認出来たら条件指定するとどうなるかを確認してみましょう。

sqlite> UPDATE employeemaster SET employeestatus='0' WHERE employeecode<10;
sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|0
2|演習|二号|0|0
3|演習|三号|0|0
4|演習|四号|1|0
5|演習|五号|1|0
sqlite>

データの削除(DELETE)

では、引き続き削除方法について学びます。削除には、DELETEを使います。

sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|0
2|演習|二号|0|0
3|演習|三号|0|0
4|演習|四号|1|0
5|演習|五号|1|0
sqlite> DELETE FROM employeemaster WHERE employeecode='5';
sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|0
2|演習|二号|0|0
3|演習|三号|0|0
4|演習|四号|1|0
sqlite>

削除されたレコードはINSERTしない限り元には戻りませんので注意してください。

トランザクション

DBの機能の中にトランザクションと呼ばれるものがあります。主にBEGIN、ROLLBACK、COMMITというコマンドがあります。ここでは取り扱いませんが、ちょっと調べてみると、恐る恐るUPDATEやDELETEを行なわないですむかもしれません。

データの並び順(ORDER BY)

あとは並び順について。並び順の指定は、ORDER BY で行ないます。ここまでの例では順序良く並んでいますが、これはたまたまです。

基本的には無作為で、一応、結合計画の種類や、ディスク上に格納されている順序に依存します。ですので、Updateを発行すると、現在表示されている順序と変わる可能性があるということです。

では、本当にそうなるか見てみましょう。

sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|0
2|演習|二号|0|0
3|演習|三号|0|0
4|演習|四号|1|0
sqlite>
sqlite>
sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|0
2|演習|二号|0|0
3|演習|三号|0|0
4|演習|四号|1|0
sqlite> UPDATE employeemaster SET employeestatus='1' WHERE employeecode='2';
sqlite> SELECT * FROM employeemaster WHERE employeecode<10;
1|演習|一号|1|0
2|演習|二号|0|1
3|演習|三号|0|0
4|演習|四号|1|0
sqlite>

というように変化してしまうことがわかります。そのため、並び順を決めるために ORDER BY を使用します。

sqlite> SELECT * FROM employeemaster WHERE employeecode<10 ORDER BY employeecode;
1|演習|一号|1|0
2|演習|二号|0|1
3|演習|三号|0|0
4|演習|四号|1|0
sqlite>

ORDER BY は、指定しなければ基本的に、昇順になります。明示的にASCをつけても構いません。また、DESCをつけて降順になります。

sqlite> SELECT * FROM employeemaster WHERE employeecode<10 ORDER BY employeecode DESC;
4|演習|四号|1|0
3|演習|三号|0|0
2|演習|二号|0|1
1|演習|一号|1|0
sqlite>

演習

問題１

employeemaster の employeecodeが、10より小さい人のステータス(employeestatus)をすべて 1に更新してください。

問題２

employeesexが1、かつemployeestatusが1という条件のSQL QUERYを考えてください。下記の図を参考に並び順についても考慮してください。

sqlite> 'SQL QUERY'
 employeecode | employeesurname | employeename | employeesex | employeestatus
--------------+-----------------+--------------+-------------+----------------
            1 | 演習            | 一号         |           1 |              1
            4 | 演習            | 四号         |           1 |              1
           11 | 宮地            | 勤           |           1 |              1
           15 | 河本            | 哲平         |           1 |              1
           17 | 花田            | 一徳         |           1 |              1
          102 | 森本            | ヒロ         |           1 |              1
          103 | 田端            | 直人         |           1 |              1
          104 | 仲村            | 慶太         |           1 |              1
          106 | 奥寺            | 豊           |           1 |              1
          110 | 紺野            | 竜次         |           1 |              1
          111 | 小木            | 憲一         |           1 |              1
          112 | 小杉            | 一哉         |           1 |              1
          114 | 鈴木            | 慎之介       |           1 |              1
          115 | 杉下            | 賢治         |           1 |              1
          116 | 栗林            | 太朗         |           1 |              1
          117 | 手塚            | 敏和         |           1 |              1
          118 | 磯村            | 光臣         |           1 |              1
          120 | 鈴木            | 惇           |           1 |              1