MySQLの基本はデータベースオブジェクトの生成と破棄、『MySQL徹底入門 第4版』から紹介|翔泳社の本

MySQLの基本はデータベースオブジェクトの生成と破棄、『MySQL徹底入門 第4版』から紹介

2020/07/13 07:00

 RDBMSの1つ、MySQLを初心者向けに一から解説した『MySQL徹底入門 第4版 MySQL 8.0対応』がCodeZineを運営する翔泳社より発売となりました。今回は本書から、MySQLの基本的な操作となるデータベースオブジェクトの生成(CREATE文)と破棄(DROP文)について説明した「Chapter 3 MySQL入門」の一部を抜粋して紹介します。

本記事は『MySQL徹底入門 第4版 MySQL 8.0対応』の「Chapter 3 MySQL入門」から抜粋したものです。掲載にあたり一部を編集しています。

3.2.1 SQLとは

 SQLは国際標準化機関であるISOによって標準化されているデータベース言語です。世の中には数多くのSQLデータベースが公開されていて、それらは原則として、このISO標準に準拠することを目指しています。MySQLも標準のSQLへの準拠度を高めつつありますが、ほかのDBMSと同様、独自の方言を持つ部分もあります。学習にあたっては、標準的なSQLであるものと、独自のものを意識しておくとよいでしょう。

3.2.2 データベースオブジェクトの生成と破棄

 データベースオブジェクトの生成にはCREATE文を、破棄にはDROP文を使用します。またALTER文により変更ができるものもあります。MySQLで扱えるオブジェクトの種類には、表3.2のものがあります。

表3.2 主なデータベースオブジェクト
表3.2 主なデータベースオブジェクト
基本構文
生成:CREATEオブジェクト種類名オブジェクト名オブジェクトに応じた各種情報;
破棄:DROPオブジェクト種類名オブジェクト名;
変更:ALTERオブジェクト種類名オブジェクト名オブジェクトに応じた各種情報;

データベースの作成─CREATE DATABASE

 データベースを作成するにはCREATE DATABASE文を使います。MySQLでは「データベース」という言葉は「スキーマ」と同じ意味で使われています。CREATE DATABASEは、CREATE SCHEMAと同じ意味です(「SCHEMA」が「DATABASE」の別名となっています)。データベースを作成するために必ず必要となるのは、作成したい「データベース名」のみです。そのほか、データベース内に作成されるテーブルに対してデフォルトで適用される文字コードや照合順序、暗号化の情報などを指定できます。

 CREATE DATABASE文は、通常、作成しようとしているデータベース名と同じ名前のものが存在する場合はエラーになります。IF NOT EXISTSを付けると、既存の場合にはエラーではなく、何もせずに終了するようになります。

CREATE DATABASE の構文
CREATE DATABASE [IF NOT EXISTS] db name
  [DEFAULT CHARACTER SET = charset name]
  [DEFAULT COLLATE = collation name]
  [DEFAULT ENCRYPTION = ’Y’/’N’

 データベースを削除するにはDROP DATABASE文を使います。IF EXISTSを付ければ、対象のデータベースが存在しない場合でもエラーになりません。

DROP DATABASEの構文
DROP DATABASE [IF EXISTS] db name

 ALTER DATABASEで既存データベースのデフォルト指定内容を変更できます。データベース名の変更はできません。

ALTER DATABASEの構文
ALTER DATABASE db name
  [DEFAULT CHARACTER SET = charset name]
  [DEFAULT COLLATE = collation name]
  [DEFAULT ENCRYPTION = ’Y’/’N’]

テーブルの作成─CREATE TABLE

 テーブルを作成するにはCREATE TABLE文を使います。テーブルはデータベース(スキーマ)の中に作ります。テーブル名は設定により大文字小文字が区別されることがあります。使用するスキーマ(デフォルトスキーマ)を指定するには、「USE db name」とします。

CREATE TABLE の構文
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl name (
   列1の情報 ,
   列2の情報 ,
      :
インデックスに関する情報
)
[DEFAULT CHARACTER SET = charset name]
[DEFAULT COLLATE = collation name]
[ENGINE = engine name]
[COMMENT= comment string]

 DEFAULT CHARACTER SETでテーブルの文字コードを、DEFAULT COLLATEでコレーションを指定できます。ENGINEは使用したいストレージエンジンを指定します。指定しない場合は、データベースのデフォルト、それもない場合は動作中のMySQLサーバーのデフォルトでの動作となります。

「列nの情報」の部分は、列に関する情報を記述します。コンマで区切られた1つのかたまりが1つの列を表し、それを必要な列数だけ羅列します。1つの列の情報は次のような項目を指定できます。必須項目は、列の名前とその型です。

列の情報部分の構文
col name data type
[NOT NULL]
[DEFAULT default value]
[AUTO_INCREMENT]
[UNIQUE]
[PRIMARY KEY]
[COMMENT comment string]

●NOT NULL
 カラムにNULLを許しません。テーブル設計の話になりますが、基本的には特別にNULLを許可したいカラム以外の全カラムにNOT NULLを指定すべきです。

●DEFAULT
 INSERT 文でカラムに入れる値が記述されなかったときのデフォルトの値を指定します。

●AUTO_INCREMENT
 テーブルに行が追加されるたびに自動的にインクリメントされた値がセットされる列であることを指示するオプションです。1つのテーブルに1列だけ指定可能です。AUTO_INCREMENTを指定する列の型は整数型である必要があります。

●UNIQUE
 この列へ入る値に重複を許しません。

●PRIMARY KEY
 この列が主キーであることを指定します。複合キーの場合はこの方法ではなく、列を羅列する最後の「インデックスに関する情報」の部分に、次のように記述します。

PRIMARY KEY (col1, col2)

 CREATE TABLEの実際の記述例を示します。

CREATE TABLE文の例
CREATE TABLE SAMPLE_INFO (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  birth DATETIME,
  comment VARCHAR(200)
) ENGINE=InnoDB;

 既存のテーブルを削除するには、DROP TABLE文を使います。

DROP TABLEの構文
DROP TABLE [IF EXISTS] tbl name

 ALTER TABLE文を使うことで、既存テーブルの定義を変更できます。ここでは、既存のテーブルSAMPLE_INFOにカラムを追加する例を紹介します。

ALTER TABLE SAMPLE_INFO
  ADD COLUMN yomi VARCHAR(100) NOT NULL AFTER name;

 descでテーブル定義を確認すると、yomiカラムが追加されていることがわかります。このように既存テーブルの任意の位置にカラムを追加できるのも、MySQLの特徴のひとつです。

yomiカラムが追加

インデックスの作成─CREATE INDEX

 テーブルのカラムに対してインデックスを作成すると、そのカラムに対する検索を高速化できます。インデックスはテーブル作成時に指定することもできますが、既存テーブルに対して新たなインデックスを作成することもできます。インデックスを作るにはCREATE INDEX文を使います。最低限必要なのは、インデックスを張るテーブル名とそのカラム名(複数の場合もある)、そしてこのインデックスに付ける名前です。

CREATE INDEXの構文
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX ind name
       [USING BTREE/HASH]
    ON tbl name (col1 name, col2 name....)
       [COMMNET comment string]
最小限の指定を行ってインデックスを作成する例
CREATE INDEX idx01 ON mytable (name1, name2);

 インデックスを削除するには、DROP INDEXを使用します。DROP INDEX には[IF EXISTS] のオプション指定はありません。

DROP INDEX の構文
DROP INDEX ind name ON tbl name

 インデックスの変更を行いたい場合は、いったん既存のインデックスをDROPしてから新たにCREATEします。インデックスの扱いには、ALTER文はありません。

ストアドルーチンの作成─CREATE PROCEDURE / CREATE FUNCTION

 ストアドプロシージャを作成するには、CREATE PROCEDURE文を、ストアドファンクションを作成するには、CREATE FUNCTION文を使います。総称してストアドルーチンと呼ばれます。どちらも記述された一連の処理を行う点は同じですが、両者の違いは、ストアドプロファンクションが実行結果として1つの値を戻り値としてを呼び出し元に返すのに対して、ストアドプロシージャは戻り値を返さないという点です。

CREATE PROCEDURE の構文
CREATE PROCEDURE procedure name
    ([IN/OUT/INOUT] param1 name data type, ...)
    [COMMENT comment str]
    [DETERMISTIC]
  routine body
CREATE FUNCTION の構文
CREATE FUNCTION function name
    (param1 name data type, ...)
    RETURNS data type
    [COMMENT comment str]
    [DETERMISTIC]
  routine body

 MySQLでストアドルーチンを作成するにはすこしコツがいります。ストアドルーチンは、複数のSQL処理を実施するものですから、CREATE文で定義を記述する際に、途中に命令の終了を示す「;」が入ります。この記号があると、mysqlコマンドラインクライアントは内容をMySQLサーバーに送出し、実行を試みてしまうのです。そのため、ストアドルーチン内に出てくる「;」を命令の終了とみなされないようにするために、ストアドルーチン作成前に一次的に終端文字の変更を行います(delimiter文)。

 次の例では、CREATE FUNCTIONの前に一時的に終端文字を「//」に変更し、ファンクション生成終了を、その「//」で伝えたあと、再びdelimiter文で元の終端文字に戻しています。

ストアドファンクションの例
delimiter //
CREATE FUNCTION dfb2deg(d FLOAT, f FLOAT, b FLOAT)
  RETURNS FLOAT DETERMINISTIC
BEGIN
  RETURN d + f/60 + b/60/60;
END
//
delimiter ;
MySQL徹底入門 第4版

Amazon SEshop その他

MySQL徹底入門 第4版 MySQL 8.0対応

著者:yoku0825、坂井恵、鶴長鎮一、とみたまさひろ、深町日出海、福山裕大、班石悦夫、山﨑由章
発売日:2020年7月6日(月)
価格:3,800円+税

本書について

2011年刊行の『MySQL 徹底入門 第3版』の改訂版です。メジャーバージョンアップした8.0に対応。旧版の内容を全面的に見直して書下ろし。改訂にあたり、新機能についてはデータベース担当者が必要となる部分を中心にピックアップ!