06テーブルを改造する(備忘録)→ > 01p > 02p > 03p > 04p > 05p > END
06テーブルを改造する(p66)
(1)《テーブルのカラム構造の変更》
(2)《カラムのデータ型を変更する》
(3)《カラムを追加する》
(4)《カラムの位置を変更する》
(5)《カラムの名前とデータ型を変更する》
(6)《カラムを削除する》
(7)《主キーを設定する》
(8)《自動的に連続番号が入力されるカラムにする》
(9)《連続番号付機能を使ってレコードを挿入する(p82/省略)》
(10)《連続番号の初期値を設定する》
(11)《最初からデータが入っているカラムにする》
(12)《インデックスを設定する》
<まとめ>
<チェック>
<練習問題>
END
06 テーブルを改造する
データベースの作成→テーブルの作成→データの挿入→データの確認、そしてテーブルを改造し、コピー、そして削除する方法です。
☆CHAPTER03⇒MySQLモニタ
☆CHAPTER04⇒データベースの作成
☆CHAPTER05⇒データ型とデータの入力
★CHAPTER06⇒テーブルを改造する
☆CHAPTER07⇒テーブルやレコードをコピー・削除する
★CHAPTER06⇒テーブルを改造する(p66)★
社員情報テーブル「tb1」を改造する。ここでは、カラムのデータ型を変更する方法を学びます。
なお、ここではテーブル「tb1」と同じ内容のテーブルをコピーして作った「tb1D」・・・をに対する操作を説明します。
(1)《テーブルのカラム構造の変更》
(1-1)ALTER TABLEコマンド
(2)《カラムのデータ型を変更する》
●書式⇒カラムのデータ型を変更する
ALTER TABLE テーブル名 MODIFY カラム名 データ型; |
●実行される内容 p67
●操作手順
①次を実行する ALTER TABLE tb1c MODIFY nama VARCHAR(100); ②変更後、次を実行する DESC tb1c; |
【実行結果】
mysql> ALTER TABLE tb1c MODIFY nama VARCHAR(100); Query OK, 6 rows affected (0.41 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> DESC tb1c; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | bang | varchar(10) | YES | | NULL | | | nama | varchar(100) | YES | | NULL | | | tosi | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.25 sec) mysql> |
(3)《カラムを追加する》
●書式⇒新規のカラムを最後の位置に追加する
ALTER TABLE テーブル名 AND カラム名 データ型; |
●実行される内容 p69
●操作手順
①次を実行する ALTER TABLE tb1c ADD umare DATETIME; ②次を実行する DESC tb1c; |
【実行結果】
mysql> ALTER TABLE tb1c ADD umare DATETIME; Query OK, 6 rows affected (0.53 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> mysql> DESC tb1c; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | bang | varchar(10) | YES | | NULL | | | nama | varchar(100) | YES | | NULL | | | tosi | int(11) | YES | | NULL | | | umare | datetime | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> |
(3-1)社員情報レコードの追加
●実行される内容 p70
●操作手順
①次を実行する INSERT INTO tb1c VALUES('N111','松田','33','1975-11-10'); ②次を実行する SELECT * FROM tb1c; |
●実行結果
mysql> INSERT INTO tb1c VALUES('N111','松田','33','1975-11-10'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tb1c; +------+------+------+---------------------+ | bang | nama | tosi | umare | +------+------+------+---------------------+ | A101 | 佐藤 | 40 | NULL | | A101 | 佐藤 | 40 | NULL | | A102 | 高橋 | 28 | NULL | | A103 | 中川 | 20 | NULL | | A104 | 渡辺 | 23 | NULL | | A105 | 西沢 | 35 | NULL | | N111 | 松田 | 33 | 1975-11-10 00:00:00 | +------+------+------+---------------------+ 7 rows in set (0.00 sec) mysql> |
(4)《カラムの位置を変更する》
(4-1)カラムを先頭に追加(p71/省略)
ALTER TABLE tb1D AND umare DATETIME FIRST;
※
(4-2)カラムを自由な位置に追加(p71/省略)
ALTER TABLE tb1E AND umare DATETIME AFTER bang;
※
(4-3)カラム順の変更
●実行される内容 p72
●操作手順
①次を実行する ALTER TABLE tb1c MODIFY umare DATETIME FIRST; ②次を実行する DESC tb1c; |
●実行結果
mysql> ALTER TABLE tb1c MODIFY umare DATETIME FIRST; Query OK, 7 rows affected (0.20 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> DESC tb1c; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | umare | datetime | YES | | NULL | | | bang | varchar(10) | YES | | NULL | | | nama | varchar(100) | YES | | NULL | | | tosi | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> |
(5)《カラムの名前とデータ型を変更する》
(5-1)カラム名を含めてカラムのデータ型・位置を変更する
●書式⇒カラム名を含めてデータ型や位置を変更する
ALTER TABLE テーブル名 CHANGE 変更前カラム名 変更後カラム名 変更後データ型; ※テーブル「」のカラムを「」を「」型に変更し、さらにカラム名を「」に変更しましよう。変更後、カラム構造を表示します。 |
●実行される内容 p73
●操作手順
①次を実行する ALTER TABLE tb1c CHANGE umare seinen DATE; ②次を実行する DESC tb1c; |
【実行結果】
mysql> ALTER TABLE tb1c CHANGE umare seinen DATE; Query OK, 7 rows affected, 1 warning (0.51 sec) Records: 7 Duplicates: 0 Warnings: 1 mysql> DESC tb1c; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | seinen | date | YES | | NULL | | | bang | varchar(10) | YES | | NULL | | | nama | varchar(100) | YES | | NULL | | | tosi | int(11) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> ※ |
(6)《カラムを削除する》
●書式⇒カラムの削除
ALTER TABLE テーブル名 DROP カラム名; ※テーブル「tb1c」のカラム「seinen」を削除し、削除後にカラム構造を表示してみましょう。 |
●実行される内容 p75
●操作手順
①次を実行する ALTER TABLE tb1c drop seinen; ②次を実行する DESC tb1c; |
【実行結果】
mysql> ALTER TABLE tb1c drop seinen; Query OK, 7 rows affected (0.56 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> DESC tb1c; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | bang | varchar(10) | YES | | NULL | | | nama | varchar(100) | YES | | NULL | | | tosi | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> ※ |
(7)《主キーを設定する》
(7-1)「一意」(ユニーク)とは
(7-2)主キーとは
●書式⇒主キーを設定してテーブルを作成する
CREATE TABLE テーブル名 (カラム名 データ型 PRIMARY KEY・・・); |
(7-3)主キーの作成
※たとえば、次のように入力すると、主キーであるINT型のカラム「a」と、VARCHAR(10)型のカラム「b」を持つテーブル「itii」を作成します。 CREATE TABLE itii(a INT PRIMARY KEY,b VARCHAR(10)); DESC itii; |
【実行結果】
mysql> CREATE TABLE itii(a INT PRIMARY KEY,b VARCHAR(10)); Query OK, 0 rows affected (0.54 sec) mysql> DESC itii; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | int(11) | NO | PRI | | | | b | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.15 sec) mysql> ※ |
(7-4)主キーの確認
INSERT INTO itii VALUES(1,'あ'); SELECT * FROM itii; ※テーブル「itii」の内容を確認しましょう。 |
①【実行結果】
mysql> INSERT INTO itii VALUES(1,'あ'); Query OK, 1 row affected (0.46 sec) mysql> SELECT * FROM itii; +---+------+ | a | b | +---+------+ | 1 | あ | +---+------+ 1 row in set (0.00 sec) mysql> ※ |
②【実行結果】
mysql> INSERT INTO itii (a) VALUES(1); ERROR 1062 (23000): Duplcate entry '1' for key 1 mysql> INSERT INTO itii (a) VALUES(NULL); ERROR 1048 (23000): Column 'a' cannot be null ※ |
③【実行結果】
mysql> INSERT INTO itii (a) VALUES(2); Query OK, 1 row affected (0.00 sec) ※ |
(7-5)一意キーの設定
※※・・・
CREATE TABLE unig(a INT UNIQUE,b VARCHAR(10));
DESC unig;
※
●【実行結果】
mysql> CREATE TABLE unig(a INT UNIQUE,b VARCHAR(10)); Query OK, 0 rows affected (0.07 sec) mysql> DESC unig; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | int(11) | YES | UNI | NULL | | | b | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> ※ |
(8)《自動的に連続番号が入力されるカラムにする》
(8-1)連続番号付きカラムの定義
連続番号付きカラムにするには、カラムの定義に次の3つの設定が必要です。
①データ型は「INT」等の整数型
②「AUTO_INCREMENT」を付ける
③「PRIMARY KEY」などを設定して一意にする
(8-2)連続番号付きカラムの作成
※※・・・
●実行される内容 p81
●操作手順 p81
①次を実行する CREATE TABLE renzoku (a INT AUTO_INCREMENT PRIMARY KEY,b VARCHAR(10)); ②次を実行する DESC renzoku; |
●実行結果p81
mysql> CREATE TABLE renzoku (a INT AUTO_INCREMENT PRIMARY KEY,b VARCHAR(10)); Query OK, 0 rows affected (0.49 sec) mysql> DESC renzoku; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | a | int(11) | NO | PRI | NULL | auto_increment | | b | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.04 sec) mysql> ※ |
(9)《連続番号付機能を使ってレコードを挿入する(p82/省略)》
●実行される内容 p82
●操作手順
①次を実行する INSERT INTO renzoku (b) VALUES('子'); INSERT INTO renzoku (b) VALUES('丑'); INSERT INTO renzoku (b) VALUES('寅'); ※ |
(9-1)挿入したレコードを確認(p83/省略)
※ SELECT * FROM renzoku;
●実行結果
mysql> INSERT INTO renzoku (b) VALUES('子'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO renzoku (b) VALUES('丑'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO renzoku (b) VALUES('寅'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM renzoku; +---+------+ | a | b | +---+------+ | 1 | 子 | | 2 | 丑 | | 3 | 寅 | +---+------+ 3 rows in set (0.00 sec) mysql> ※ |
(10)《連続番号の初期値を設定する》
※※・・・
(10-1)連続番号の初期化
※※・・・
●実行結果
mysql> SELECT * FROM renzoku; mysql> DELETE FROM renzoku; mysql> INSERT INTO renzoku (b) VALUES ('××'); mysql> SELECT * FROM renzoku; mysql> SELECT * FROM renzoku; +---+------+ | a | b | +---+------+ | 4 | 子 | | 5 | 丑 | | 6 | 寅 | +---+------+ 3 rows in set (0.00 sec) mysql> DELETE FROM renzoku; Query OK, 3 rows affected (0.00 sec) mysql> INSERT INTO renzoku (b) VALUES ('××'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM renzoku; +---+------+ | a | b | +---+------+ | 7 | ×× | +---+------+ 1 row in set (0.00 sec) mysql> ※ |
●書式⇒AUT_INCREMENTの値を初期化
ALTER TABLE テブール名 AUTO_INCREMENT=0; |
(11)《最初からデータが入っているカラムにする》
●書式⇒カラムの初期値を設定する
CREATE TABLE テブール名 (カラム名 データ型 DEFAULT 初期値・・・) |
●実行される内容 p85
(11-1)カラム構造の定義を変更する
●実行される内容 p86
●操作手順
①次を実行する ALTER TABLE tb1g MODIFY nama VARCHAR(10) DEFAULT '氏名未入力'; ②次を実行する DESC tb1G; |
●実行結果
mysql> CREATE TABLE tb1G SELECT * FROM tb1; Query OK, 6 rows affected (0.62 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tb1g MODIFY nama VARCHAR(10) DEFAULT '氏名未入力'; Query OK, 6 rows affected (0.09 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> DESC tb1G; +-------+-------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+------------+-------+ | bang | varchar(10) | YES | | NULL | | | nama | varchar(10) | YES | | 氏名未入力 | | | tosi | int(11) | YES | | NULL | | +-------+-------------+------+-----+------------+-------+ 3 rows in set (0.03 sec) mysql> ※ |
(11-2)データを入れる
※※・・・
INSERT INTO tb1G (bang,tosi) VALUES ('N999',38);
SELECT * FROM tb1G;
●実行結果
mysql> INSERT INTO tb1G (bang,tosi) VALUES ('N999',38); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM tb1G; +------+------------+------+ | bang | nama | tosi | +------+------------+------+ | A101 | 佐藤 | 40 | | A101 | 佐藤 | 40 | | A102 | 高橋 | 28 | | A103 | 中川 | 20 | | A104 | 渡辺 | 23 | | A105 | 西沢 | 35 | | N999 | 氏名未入力 | 38 | +------+------------+------+ 7 rows in set (0.00 sec) mysql> |
(12)《インデックスを設定する》
(12-1)インデックスとは
※
(12-2)インデックスの設定
※
●書式⇒インデックスの作成
CREATE INDEX インデックス名 ON テーブル名(カラム名); |
●操作手順
CREATE INDEX my_ind ON tb1(bang); |
【実行結果】
C:\Documents and Settings\Owner>mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.45-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> USE db1; Database changed mysql> CREATE INDEX my_ind ON tb1(bang); Query OK, 6 rows affected (0.72 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> |
(12-3)インデックスの表示
●書式⇒インデックスの確認
SHOW INDEX FROM テーブル名; |
①【実行結果】
mysql> SHOW INDEX FROM tb1; +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi nality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+------+------------+---------+ | tb1 | 1 | my_ind | 1 | bang | A | NULL | NULL | NULL | YES | BTREE | | +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+------+------------+---------+ 1 row in set (0.25 sec) mysql> |
②【実行結果】
※このままなら見ずらいので・・・ mysql> SHOW INDEX FROM tb1\G *************************** 1. row *************************** Table: tb1 Non_unique: 1 Key_name: my_ind Seq_in_index: 1 Column_name: bang Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: 1 row in set (0.00 sec) mysql> |
(12-4)インデックスの削除
※
●書式⇒インデックスの削除
DROP INDEX インデックス名 ON テーブル名; ※ |
①【実行結果】
mysql> DROP INDEX my_ind ON tb1; Query OK, 6 rows affected (0.53 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> ※「SHOW INDEX」で確認すると、削除されていることがわかります。 |
②【実行結果】
mysql> SHOW INDEX FROM tb1\G Empty set (0.00 sec) mysql> |
<まとめ>
●テーブルのカラム構造を変更する方法
●主キーとは何か
●連続番号機能を持つカラムを設定する方法
●カラムの初期値を設定する方法
●インデックスの役割とその設定方法
<チェック>
□「ALTER TABLE…」の命令を使ってカラム構造の変更、追加、削除ができる
□連続番号機能を持つカラムを設定できる。連続番号を入力できる
□「AUTO_INCREMENT=…」で、連続番号の初期値が設定できる
□「DEFAULT」を使って、カラムの初期値が設定できる
□「CREATE INDEX…」を使って、インデックスが設定できる
<練習問題>
問題1
次のようなテーブル「syokiti_renzoku」を作成し、入力した瞬間の日時をカラム「niti」に入力してレコードを挿入して下さい
●テーブル「syokiti_renzoku」 p93
《解答》
問題1
次を実行します。
CREATE TABLE syokiti_renzoku (bang INT AUTO_INCREMENT PRIMARY
KEY,niti DATETIME) AUTO_INCREMENT=100;
INSERT INTO syokiti_renzoku (niti) VALUES(NOW());
【実行結果】
mysql> CREATE TABLE syokiti_renzoku (bang INT AUTO_INCREMENT PRIMARY -> KEY,niti DATETIME) AUTO_INCREMENT=100; Query OK, 0 rows affected (0.48 sec) mysql> INSERT INTO syokiti_renzoku (niti) VALUES(NOW()); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM syokiti_renzoku; +------+---------------------+ | bang | niti | +------+---------------------+ | 100 | 2010-03-20 08:28:32 | +------+---------------------+ 1 row in set (0.00 sec) mysql> |