ドンドン身に付く、スラスラ書ける書き込み式SQLドリル 4章読んだ

生CREATE文の書いたことの無さに、 woshidan先生の二周目に乞うご期待☆みたいな感じが漂いながらとりあえず進めています。

二周目やるとしても、一周目のメモ見ながら問題解くだけだからだいぶ楽ですが。

4-1

CREATE TABLE
  table_name
    (
       列名1 data_type column_flag,
       列名2 data_type column_flag,
       ...
       制約条件,
       ...
    )
;

という感じ。

列フラグは

• PRIMARY KEY(主キー。他のテーブルから検索に使う。一意な値、NULL不許可。)
• UNIQUE(一意な値、NULL許可), [NOT] NULL(NULL値を[不]許可)
• AUTO INCREMENT(INT型でだんだん増加していく)

など。

制約条件は、

• PRIMARY KEY(主キー。他のテーブルから検索に使う。一意な値、NULL不許可。)
• UNIQUE(インデックス。他のテーブルから検索に使う。一意な値、NULL不許可。)
• FOREIGN KEY(参照制約)など。

CHAR型などの文字数を()の中に書くのがやや慣れない。

FOREIGN KEYの書き方がちょっと自信無かったので、MySQL :: MySQL 5.6 Reference Manual :: 13.1.17.2 Using FOREIGN KEY Constraintsより例を引用。

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT, 
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) 
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

4-2

CREATE INDEX
  index_name
ON
  table_name
  (
    column1,
    column2,
    ..
  )
;

インデックスを作る構文(上)より、どのようなルールで作るかが大事。

そういえば、達人に聞く……の本に1万件くらいはつけてもつけなくてもパフォーマンスほとんど変わらないとか書いてあったので、自分が今まで書いたアプリの中だと3個くらいしか必要ないですね。 あと、その列の取りうる特定の値を指定したとき、全体のレコードの5%以下に絞り込めるくらいと判断したときにつけるといいらしいです。

DBMSや、検索・更新の頻度にも依りそうだから何とも言えないですけど。

インデックスの削除の構文は以下.

DROP INDEX index_name ON table_name;

インデックスが使用されない場合

• IS NULL, IS NOT NULL, <>, LIKE演算子(LIKE演算子の前方一致は可)
• インデックス列に対して演算や関数を適用している(× price * 1.05 < 3000 ○ price < 3000 / 1.05)
• 複合インデックスを設定しており、インデックスの先頭列がWHERE句に含まれていない場合

インデックスの内容確認は

SHOW INDEX FROM table_name;

4-3

テーブルの定義内容を追加するには、ALTER TABLE命令を使用する。

ALTER TABLE
  table_name
ADD
  column_name data_type
AFTER
  the prev column_name
;

制約条件の追加の場合は

ALTER TABLE
  table_name
ADD
  constration condition
;

逆に削除する場合は、

ALTER TABLE
  table_name
DROP
  column_name
;

複数追加する場合は、

ALTER TABLE
  table_name
ADD
  column_name1 data_type
AFTER
  the prev column_name1,
ADD
  column_name2 data_type
AFTER
  the prev column_name2
;

4-4

列定義の変更

ALTER TABLE
  table_name
MODIFY
  target_column new_data_type new_flag

テーブル名の変更

ALTER TABLE
  table_name
RENAME AS
  new_table_name

複数の列名の変更

ALTER TABLE
  table_name
MODIFY
  target_column1 new_data_type new_flag,
MODIFY
  target_column2 new_data_type new_flag
;