■データベースの作成
以下のように、「CREATE DATABASE」文や「CREATE SCHEMA」文を使用する。
CREATE DATABASE <dbname>;
CREATE SCHEMA <dbname>;
■テーブルの作成
以下のように、「CREATE TABLE」文を使ってテーブルを作成する。
CREATE TABLE <tablename> ( <colname> <coltype> [<colattributes>], [... <colname> <coltype> [<colattributes>]] );
例えば以下のようになる。
CREATE TABLE book ( id INT NOT NULL PRIMARY KEY, isbn VARCHAR(13), title VARCHAR(255), author VARCHAR(255), publisher VARCHAR(255) )
■インデックスの作成
CREATE INDEX <indexname> ON <tablename> (<column>[, ... <column>]);
例えば以下のようになる。
CREATE INDEX book_isbn ON book (isbn);
但し、一般的には以下のようにテーブルの作成と同時に行うことが多い。
CREATE TABLE `book` ( `id` int(11) NOT NULL auto_increment, `isbn` varchar(13) default NULL, `title` varchar(255) default NULL, `author` varchar(255) default NULL, `publisher` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `book_isbn` (`isbn`) );
■データベースの削除
DROP SCHEMA <dbname>;
■テーブルの削除
DROP TABLE <tablename>;
■データの操作
追加
データを追加するには、以下のようにINSERT( INTO)文を使用する。テーブルのカラム名は運用上のメリットが非常に大きいため、記述することを強くお勧めする(カラムの追加に対するSQL文の耐性)。
INSERT INTO <tablename>(<colname> [, ... , <colname>]) VALUES(<value> [, ... , <value>]);
更新
データを更新するためには、以下のようにUPDATE(~SET~)文を使用する。但し、以下のコードは全レコードが特定の値に上書きされてしまう。
UPDATE <tablename> SET <colname> = <value> [, ... , <colname> = <value>];
通常は、以下のようにWHERE節などと組み合わせて使用し、特定のレコードのみを更新する。
UPDATE `address_book` SET `name` = 'Mike' WHERE `id` = '1';
削除
データを削除するには、以下のようにDELETE文を使う。但し、以下のコードは全レコードが削除される。
DELETE FROM <tablename>;
通常は以下のようにWHERE節などと組み合わせて使用する。
DELETE FROM `address_book` WHERE `name` = 'John';
■データの表示
データを表示するには、以下のようにSELECT文を使用する。
SELECT * FROM `address_book`;
通常、アプリケーションで全件のデータが必要なときというのは稀である。そして全件をSQLから抽出すると、サーバのメモリを圧迫することになる。従って、以下のようにWHERE節などと組み合わせて使うことが多い。
SELECT `tel` FROM `address_book` WHERE `name` = 'John';
WHERE節について
WHERE節では、条件をANDやORで複数指定できる。また、LIKE演算子を使用し大文字/小文字を区別しないようにしたり、「%(任意の複数文字)」や「_(任意の一文字)」の記号を使用して検索できる。
SELECT `tel` FROM `address_book` WHERE (`name` = 'John') OR (`name` LIKE '%Joh%' AND `age` = '27')';
正規表現と記法が異なるのが、個人的にはシックリこない。
インデックス
日本語に直訳すると「索引」。リソース内の特定の項目を検索するためのメカニズム。一般的にはアプリケーションがWHERE節などで検索に使用するカラムに対して指定される。
- 「インデックス」はデータベースサーバがテーブルの行を特定するために使われる
- 「インデックス」は特殊なテーブルである
- 「インデックス」は行を特定するために使用される列と、行が物理的に配置されている場所を示す情報のみを含んでいる
主キー(primary key)
一組のデータレコードを一意に識別するための特殊なインデックス
テーブルの関係性
テーブルの関係性として「1:1」と「1:複数」の場合は簡単に処理できるが、「複数:複数」の場合はintermediate tableなどを使用しなくてはならない。
- 1:1
- 子テーブルの1レコードが、親テーブルの1レコードと関連する
- 1:複数
- 子テーブルの複数レコードが、親テーブルの1レコードと関連する
- 複数:複数
- 子テーブルの複数レコードが、親テーブルの複数レコードと関連する
関係性を意識したテーブルの作成
1:1
以下のように、primary keyを共通にしてみると良い。
CREATE TABLE `book` ( `id` int(11) NOT NULL, `isbn` varchar(13) default NULL, `title` varchar(255) default NULL, `author` varchar(255) default NULL, `publisher` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `book_isbn` (`isbn`) ); CREATE TABLE `book_author` ( `id` int(11) NOT NULL PRIMARY KEY, `author_id` int(11) NOT NULL );
1:多
以下のように、REFERENCESを使用してbookテーブルを参照するようにする。
CREATE TABLE `book` ( `id` int(11) NOT NULL auto_increment, `isbn` varchar(13) default NULL, `title` varchar(255) default NULL, `author` varchar(255) default NULL, `publisher` varchar(255) default NULL, PRIMARY KEY (`id`), KEY `book_isbn` (`isbn`) ); CREATE TABLE `book_chapter` ( `id` int(11) NOT NULL PRIMARY KEY auto_increment, `isbn` varchar(13) REFERENCES `book`(`isbn`), `chapter_number` INT NOT NULL, `chapter_title` varchar(255) );