MySQL/dbonline/table

Last-modified: 2010-10-21 (木) 16:03:42

テーブルの作成

基本

通常の作成 

　CREATE TABLE db_name.tbl_name (col_name1 data_type1, col_name2 data_type2, ...);

　　※カラムは名前が最初で、データタイプが後ろなので注意
　　※useでデータベースに接続していれば、tbl_nameだけでＯＫ
　　※同名のテーブル名を指定してもエラーが出ないようにするには、

　CREATE TABLE IF NOT EXISTS db_name.tbl_name
　　(col_name1 data_type1, col_name2 data_type2, ...);

　　　とする。→show warningsでキャッチできる

NULLを許可するか否か 

　CREATE TABLE db_name.tbl_name
　　　(col_name1 data_type1 NULL, col_name2 data_type2 NOT NULL, ...);

　　※NOT NULLの場合、NULLを入れるとエラーになる

デフォルト値

　　テーブルにデータを追加する時、カラムに値が指定されなかった場合に
　　デフォルトで設定される値を指定できる

　CREATE TABLE db_name.tbl_name
　　　(col_name1 data_type1 DEFAULT default_value, col_name2 data_type2, ...);

　　※デフォルト値に設定できるのは定数だけ。関数の戻り値などは設定できない。

　　※TIMESTAMP型のカラムの場合、デフォルト値は
　　　1つ目がCURRENT_TIMESTAMP関数の戻り値が設定され、
　　　2つ目以降が0('0000-00-00 00:00:00')が設定される

AUTO_INCREMENT

　　・初期値が１～の場合、かつ、整数のデータ型に限り、設定可能。
　　　AUTO_INCREMENTが設定されたカラムには自動的に連番の値が格納される
　　　※初期値は１

　CREATE TABLE db_name.tbl_name
　 (col_name1 data_type1 AUTO_INCREMENT, col_name2 data_type2, ...,
　　INDEX(col_name1));

　　　※AUTO_INCREMENTはテーブル内の1つのカラムにしか設定できない
　　　※AUTO_INCREMENTを設定したカラムにはDEFAULTは設定できない

　　　※AUTO_INCREMENTが設定されたカラムには自動で値が格納されていくように利用するが、
　　　　任意の数値を指定して格納することも可能。また重複した値を格納することも可能。
　　　　ただし、正の値しか格納できない（負でもエラーにはならないが、予期せぬ値になる）

　　　※特定のカラムを削除しても、デクリメントはされない

　　　・初期値が１以外の場合

　CREATE TABLE db_name.tbl_name (col_name1 data_type1 AUTO_INCREMENT,
　　col_name2 data_type2, ..., INDEX(col_name1)) AUTO_INCREMENT = value;

プライマリーキーの設定

　・設定の仕方

　　CREATE TABLE db_name.tbl_name
　　 (col_name1 data_type1 NOT NULL PRIMARY KEY, col_name2 data_type2, ...);

　・NOT NULLを指定しなかった場合も、自動でNOT NULLになる

　・重複する値やNULLは設定できなくなる

　・複数のカラムに対して、プライマリキーを設定することも可能

　　CREATE TABLE db_name.tbl_name
　　 (col_name1 data_type1 NOT NULL, col_name2 data_type2, ...,
　　　PRIMARY KEY(index_col_name1, index_col_name2, ...));

　※複数カラムをプライマリキーにした場合には、「複数カラムすべて」が一致するデータは格納できないが、
　　カラムのいずれかが一致するデータは格納できるので注意

ユニークキーの設定

　・設定の仕方

　　CREATE TABLE db_name.tbl_name
　　　(col_name1 data_type1 UNIQUE, col_name2 data_type2, ...);

　・複数カラムに設定する場合

　　CREATE TABLE db_name.tbl_name
　　　(col_name1 data_type1, col_name2 data_type2, ...,
　 　　UNIQUE(index_col_name1, index_col_name2, ...));

　※プライマリーキーとの違いは
　　（１）NULLを設定できる
　　（２）NULLは重複可能

　※複数カラムをユニークキーにした場合には、
　　「複数カラムすべて」が一致するデータは格納できないが、
　　カラムのいずれかが一致するデータは格納できる

インデックスの作成

　インデックスは、カラムが特定の値をもつレコードの迅速な検索のために使用されるもの。
　インデックスがないと、最初のレコードから開始し、
　テーブル全体を読み取ることが必要になる（シーケンシャルアクセス）

　　→つまり、パフォーマンス向上のために設定するもの。
　　　ただし、結果的に全部をアクセスすることになった場合は、単純なシーケンシャルアクセスのほうが高速

　・また、後述の外部キー制約を使用するときに、
　　インデックスが定義されている必要があるということもある

　・設定の仕方

　　CREATE TABLE db_name.tbl_name
　　 (col_name1 data_type1, col_name2 data_type2, ...,
　　 INDEX (index_col_name1, index_col_name2, ...));

　※インデックスが設定されているカラムにはユニーク制約はない

　（詳細については、別途、要調査）

既存テーブルのコピー

既存のテーブルから新しいテーブルを作る

　・記述法

　　CREATE TABLE new_tbl
　 　 SELECT col_name1, col_name2, ... FROM orig_tbl;

　※カラムが引き継がれるだけでなく、格納されているデータも引き継がれる

　※col_nameの代わりに、*を指定すると、テーブル丸々コピーできる

　※注意：　インデックスに関する設定は引き継がれない！

　・さらに、where文を使えば、特定の値を持つ要素だけをテーブルにできる

　　例）

　　create table drinkproductname select id, name from allproducts where cate = 1;

　・既存のテーブルに新しいカラムを追加して作ることも可能

　　create table pricetable(price int) select * from allproducts;

　・新しく属性を追加することも可能
　　以下の例は、allproductsにidとpriceというカラムが含まれていた場合の例

　　例）　

　　create table pricetable2(id int primary key, price int)
　　　select * from allproducts;

　
　　※上記のidカラムは新しく作られるのではなく、引き継がれる
　　※インデックスの情報は引き継がれないが、新しく設定可能になる
　　※新しいデータ型を設定すると、変換されて引き継がれる

既存のテーブルの定義だけコピーして新しいテーブルを作成 

　CREATE TABLE new_tbl LIKE orig_tbl;

　※プライマリーキーの設定などインデックスに関するものも含めて
　　元のテーブル定義と同じテーブルが作成される
　※格納されていたデータは新しいテーブルには追加されない

外部キー制約

　・概略
　　外部キー制約とは、テーブルの指定したカラムに
　　格納できる値を他のテーブルに格納されている値だけに限定するもの。
　　　→テーブルに格納できる値は、参照テーブルにあるものだけ！

　　参照される側のテーブルを親テーブル、参照する側のテーブルを子テーブルと呼ぶ。

外部キーの設定

　・親テーブルの書式：

　　CREATE TABLE 親テーブル名(親カラム名 データ型) ENGINE=InnoDB;

　・子テーブルの書式：

　　CREATE TABLE 子テーブル名(子カラム名 データ型,
　　 FOREIGN KEY (子カラム名)
　　 REFERENCES 親テーブル名(親カラム名)
　 　) ENGINE=InnoDB;

　・使用例
　　例えば親テーブルとして商品一覧のテーブル、
　　子テーブルとして売上テーブルを用意
　　　　　　　　　　↓
　　そして売上テーブルの中の商品カラムの値に格納できる値の制限として
　　商品一覧テーブルの商品カラムに格納されているものに限定させたい場合など

　　※作成するテーブルはInnoDB型テーブルである必要がある
　　※対象のカラムにはインデックスが作成されている必要がある
　　※デフォルトでは、親テーブルの外部キー制約の対象となっているカラムの値を更新・削除するとエラーになってしまう

外部キー制約の親テーブルの更新・削除用の設定

　・更新時の挙動の設定にはON UPDATE句を使用

　　CREATE TABLE 子テーブル名(子カラム名 データ型, FOREIGN KEY (子カラム名)
　 　REFERENCES 親テーブル名(親カラム名) ON UPDATE reference_option
　　 ) ENGINE=InnoDB;

　・削除時の挙動の設定にはON DELETE句を使用

　　CREATE TABLE 子テーブル名(子カラム名 データ型, FOREIGN KEY (子カラム名)
　　 REFERENCES 親テーブル名(親カラム名) ON DELETE reference_option
　　 ) ENGINE=InnoDB;

　　※「ON DELETE reference_option ON UPDATE reference_option」と
　　　続けてすれば、同時に指定することも可能

　・reference_optionに設定できるオプション一覧は以下の通り

　　RESTRICT ：　デフォルト値。更新と削除時にエラーを出す
　　NO ACTION：　MySQLにおいてはRESTRICTとまったく同じ挙動になる
　　CASCADE ：　子テーブルが親テーブルに合わせる
　　　　　　　　（親が更新されたら、子も更新。削除されたら、子も削除）
　　　　　　　　※ただし、子のテーブルにおいて該当しないものはそのまま
　　　　　　　　　たとえば、親が「消しゴム」を「ケシゴム」に変えたら、
　　　　　　　　　子の「消しゴム」も「ケシゴム」になるが、
　　　　　　　　　子の「鉛筆」は「鉛筆」のままで変わったり消えたりしない
　　SET NULL ：　親テーブルに対して更新を行うと子テーブルで同じ値を持つカラムの値がNULLになる
　　　　　　　　（CASCADEの子テーブルがNULLになるバージョン）

その他、コンフィグ

ストレージエンジンの設定

　・ENGINE=で設定すればＯＫ

　　CREATE TABLE db_name.tbl_name
　　 (col_name1 data_type1, col_name2 data_type2, ...
　　 ) ENGINE=engine_name;

　・指定可能なストレージエンジンの種類

　　ARCHIVE
　　CSV
　　EXAMPLE
　　FEDERATED
　　InnoDB　　　　　　←よく使われる
　　MEMORY (又はHEAP)
　　MERGE
　　MyISAM　　　　　　←よく使われる
　　NDBCLUSTER

　・指定しなければデフォルトストレージンエンジンになっている

文字セットと照合順序の設定 

　　CREATE TABLE db_name.tbl_name
　　 (col_name1 data_type1, col_name2 data_type2, ...)
　　　CHARACTER SET charset_name COLLATE collation_name;

　　※データベースを作るときに以下のように指定するので、軽くデジャブ

　　CREATE DATABASE db_name
　　　CHARACTER SET charset_name COLLATE collation_name;

　　設定しない場合は、DBの文字セット
　　DBの文字セットも定義されていない場合は、
　　MySQLのデフォルトの文字セットを使って設定される

テーブルに関する情報の取得

　　これらの情報を得るときは、\Gでの取得を使い分けるべし
　　※\Gの末尾に、「;」をつけないこと（エラーになる）

テーブル一覧 

　　　SHOW TABLES;
　　　SHOW TABLES FROM db_name;

　　　※このコマンドの後ろに　LIKE 'pattern'を付けると絞り込みができる。

　　　例）先頭の１文字が「a」のテーブルを表示
　　　　SHOW TABLES LIKE 'a%';

CREATE TABLE文の取得 

　　　SHOW CREATE TABLE tbl_name

　　　「tbl_nameを作るのに必要なcreate table文を取得」とあるが、
　　　つまり、「どうやってtbl_nameテーブルを作ったか？」がわかる。　
　　　　→カラムの名前や型、文字セットなどがわかる

テーブルの詳細情報 

　　　SHOW TABLE
　　　STATUS FROM db_name

　　　テーブルに現在含まれているデータ数、最終更新時間などを取得

　　　※このコマンドの後ろに　LIKE 'pattern'を付けるとテーブルの絞り込みができる。

カラムに関する情報の取得

　　　カラム毎にデータ型、NULLを許可するかどうか、プライマリキーかどうか、
　　　デフォルト値、などについて表示される。

　　　SHOW COLUMNS FROM tbl_name
　　　SHOW COLUMNS FROM tbl_name FROM db_name

　　　※後ろにLIKEで、テーブルの絞り込み可

テーブル構造の変更方法（ALTER TABLE ～）

テーブル名の変更 

　ALTER TABLE tbl_name RENAME [TO] new_tbl_name;

　　※中のデータは引き継がれる
　　※テーブルに対してユーザーに権限を設定してある場合は注意が必要！
　　　テーブル名が変更されても権限は、自動的に新しいテーブル名に
　　　変更されないので、手動で権限を変更する必要がある

　　例）
　　show grants for 'test'@'localhost'\G　を見たときに、
　　GRANT SELECT ON 'sampledb'.'table' TO 'test'@'localhost'のような
　　記述があったら、'table'をリネーム後のテーブル名に変更する必要あり

カラムの名前と定義の変更 

　ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name column_definition;

　　※上記書式は公式のものだが、なんか間違っているような...？
　　　changeの後に、旧カラム名 新カラム名 定義　という順番で並べる

　　例）alter table personal change id userid int;
　　　　＃id が userid に変わる　　　

　　※文字数を変えたり、デフォルト値を変更したりということも可能
　　※名前を変えずに、定義だけを変えることも可能
　　※NOT NULLなどをつけ忘れると定義から削除されてしまうので注意
　　　（NOT NULLのまま変更がない場合も、再度、記述する）

カラムの追加と削除 

　　ALTER TABLE tbl_name ADD [COLUMN] column_definition;

　　上記の方法だとテーブルの末尾にカラムが追加される

　　先頭に追加した場合は以下のようにする

　　ALTER TABLE tbl_name ADD [COLUMN] column_definition FIRST;

　　任意の個所に追加したい場合は以下のようにする

　　ALTER TABLE tbl_name ADD [COLUMN] column_definition AFTER col_name;

　　　※追加したカラムにはデフォルトを適用された状態で追加される
　　　※NOT NULLをセットした場合は空白がセットされる

　　　
　　カラムの削除は以下の通り

　　ALTER TABLE tbl_name DROP [COLUMN] col_name;

テーブルの削除 

　　　DROP TABLE tbl_name [, tbl_name] ...

　　　DROP TABLE IF EXISTS tbl_name [, tbl_name] ...
　　　とすると（ｒｙ