WEBデザイン・開発講義WEB Design&Development Lecture

「MySQL」の『SQL』動作の概要、『SQL』文法とコマンド操作

「データベース」の『SQL』動作の概要と「MySQL」の『SQL文』の文法、『SQLコマンド』操作

  • 「MySQL」環境の『SQL』動作の概要と『SQL文』の文法と『SQLコマンド』によるデータベース操作を説明する内容です。
  • 「リレーショナルデータベース」は、テーブルとテーブルの関連付けが特徴であり、DBサーバへのアクセスは、『SQL』文のコマンドによって操作することの動作の概要、加えて、「MySQL」のプライマリーキーと外部キー制約やスキーマとデータベースの相違点など理解します。
  • 「MySQLのSQL」の記述方法と項目（フィールド）の「データ型」などコマンド文法によるデータベース作成、テーブルの構成表示、データベース内の検索などをコマンドプロンプトによる操作を理解します。

『SQL』動作の概要と「MySQL」の『SQL文』の文法及び『SQLコマンド』によるデータベース操作

「MySQL」環境の『SQL』処理※画像クリックで拡大表示（リンク）

  • リレーショナルデータベース「RDB」のSQL処理の概要
    RDBでは、基本要素（データベース・オブジェクト）を「テーブル」で表現し、「テーブル」の集まりを「データベース」で、例えば「名簿管理データベース」と呼びます。「テーブル」は、複数の「行：レコード」（row）から構成され、行は「列：フィールド」（column）から構成されます。
    あるテーブルと別のテーブルの「フィールド」で関係付け（親と子のリレーション）し、連携させて、データの一意性（重複なし）を実現しています。 「RDB」のSQL処理と構成
    • クライアント端末のコマンドプロンプトやPHPなどのプログラムから、データベースシステム（DBサーバ）に対して「SQL」文で記述してRDBMSへ送信し、SQL文の文法解析する。SQL文がOKのときにSQLのデータベース関数などを実行し、結果をクライアント側へ戻す処理形態です。
    • 「SQL」は、体系的にデータ構造の作成：変更できる「DDL」、データの内容（レコード）を追加・変更・削除・検索できる「DML」、データの制御・管理・復旧できる「DCL」から構成され、命令文や制御文（コマンド）として実行します。
  • 「SQL」の構成
    内容代表的なコマンド
    データ定義言語：DDL（Data Definition Language）
    • データを格納すべき表の定義
    • 複数の表を関連づけるための規約や制約
    • データベースに必要な機密保護の宣言
    • 仮想的なテーブル（ビュー）作成と削除
    		 CREATE
    テーブルやインデックス作成
    DROP
    テーブルやインデックス削除
    ALTER
    データの定義内容を変更
    CREATE VIEW
    ビュー作成
    drop VIEW
    ビュー削除
    データ操作言語：DML（Data Manipulation Language）
    • 表に対するデータの登録・修正・削除
    • 複数の表の結合、ビュー表の作成などの集合操作
    • 表中のデータを検索
    		 INSERT
    テーブルに新しい値を挿入
    UPDATE
    テーブルの値を更新
    DELETE
    テーブルから値を削除
    SELECT
    テーブルから値を取得
    データ制御言語：DCL（Data Control Language）
    • データベースのユーザー権限の管理やデータのトランザクション処理を行う為の言語
    • 回復・同時実行のための最小単位として保障される一連の処理の操作
    		 GRANT
    権限の付加
    SHOW
    DB定義の構造表示
    BEGIN
    トランザクションを開始
    COMMIT
    トランザクションを確定
    ROLLBACK
    トランザクションを取消す

「MySQL」のキーとスキーマ

  • 「プライマリーキー制約」と「外部キー制約」
    プライマリーキーと外部キーの関連
    プライマリーキー制約は、テーブルには、１つの「プライマリキー（主キー）」を設定でき、テーブルの中で１つまたは複数の列を指定可能で、値は重複してはならず（ユニーク）、NULL（何も値が入っていない状態）でない必要があります。
    外部キー（FK）制約は、データ整合性を保つためテーブルの各行の外部キー値は、親のテーブル値と一致している必要あり、テーブルの行を削除及び更新すると、その行を参照していた別の列の値がなくなってしまうようなデータ矛盾を防ぐことができます。
    テーブル作成のCREATE文やテーブル情報変更のALTER文の外部参照の「FOREIGNKEY」を付加してテーブルとテーブルとの関係（リレーション）を指定する必要があります。
  • インデックスキーは、
    大量のデータや検索頻度の高いテーブルに対して、高速に検索するために利用し、キーとレコード位置（ポインター）を抽出して作成されたものです。
  • MySQLのスキーマ
    一般的なスキーマは、ユーザーと関連付けたりオブジェクトの所有者との関連で「データベース（構造）」と考えますが、MySQLではユーザー認証や権限のみに利用し、スキーマを利用せずに「データベース」としての取扱い方をします。

「MySQL」の『SQL』文法

  • 「SQL」文の記述規則
    • 予約語は、SELECT, FROM, WHERE, INSERT, UPDATE, DELETE, CREATE, GRANTなどの命令文や属性語（句）など利用できないが、利用したい場合は、[予約語]のように「[」と「]」で括ります。
    • 大小文字の区別がありません。
    • コメント行は、複数行の「/* ～ */」で囲まれた部分と1行単位の「--」（ハイフォン2個）があります。
    • 命令文の文末は、「;」（セミコロン）です。
      ※SQL serverは不要です。
    • 空白文字は、半角スペース、TAB、改行で連続した場合でも1文字として扱われます。
    • 文字定数の文字列の場合、シングルクォーテーション「'～'」又はダブルクォーテーション「"～"」で括ります。
    • 数値定数の場合、整数「10」、小数点「10.5」、指数「1.5e10」、16進数「0xFF」などで表現します。
    • 日付は、シングルクォーテーション「'～'」又はダブルクォーテーション「"～"」で括ります
    • 期間は、MySQLの場合、シングル又はダブルクォーテーションで括る必要なく、「INTERVAL 5 DAY」にINTERVALの属性キーワードを付けて指定します。
  • MySQLの基本的なデータ型は、
    数値、文字列、日付の３種類あり、大きなデータを扱うLOB（Large Object）やイメージの型の利用可能です。
    • 数値型
      型範囲「サイン付き」バイト数
      範囲「サインなし」
      TINYINT-128～1271
      0～255
      SMALLINT-32768～327672
      0～65535
      MEDIUMINT-8388608～83886073
      0～ 16777215
      INT
      INTEGER      		-2147483648～ 21474836474
      0～ 4294967295
      BIGINT-9223372036854775808 ～92233720368547758078
      0～ 18446744073709551615
      NUMERIC[(n[,d])]
      DECIMAL,DEC      		 4(pack型)
      例： DECIMAL(5,2)→DECIMAL や NUMERIC カラムを宣言する時は、精度（その値に格納された有効な桁数を表す）とスケール（小数点以下に格納できる桁数）を指定する事ができます。（5は精度、2がスケール）
    • 浮動小数点型
      型範囲バイト数
      FLOAT(x)x:4 or 8 (浮動小数点)4～53
      DOUBLE [PRECISION] [(n,d)]倍精度の浮動小数点8
      REAL倍精度の浮動小数点8
      BIT(n)ビットフィールド値
      n：1から64までが可能      		約(n+7)/8
    • 日付型
      型表示形式バイト数
      DATE
      日付      		'YYYY-MM-DD'3
      DATETIME
      日付と時刻      		'YYYY-MM-DD HH:MM:SS'8
      TIMESTAMP
      日付と時刻      		'YYYY-MM-DD HH:MM:SS'4
      TIME
      時刻      		'HH:MM:SS'3
      YEAR[(n)]
      年（n:2 or 4）      		YYYY1
    • 文字列型（L と n の値は文字数）
      型文字数バイト数
      CHAR(n)
      固定長文字列      		0 <= n <= 255n
      VARCHAR(n)
      可変長文字列      		0 <= n <= 255
      256 <= n <= 65535      		n+1
      n+2
      TINYTEXT
      可変長文字列      		255L+1
      (L<28)
      TEXT
      可変長文字列      		65,535L+2
      (L<255)
      MEDIUMTEXT
      可変長文字列      		1,677,215L+3
      (L<224)
      LONGTEXT
      可変長文字列      		4,294,967,295L+4
      (L<232)
    • バイナリデータ型（L と n の値はバイト数）
      型範囲（バイト）バイト数
      BINARY(n)0 <= n <= 255n
      VARBINARY(n)0 <= n <= 255
      256 <= n <= 65535      		n+1
      n+2
      TINYBLOB255L+1
      (L<28)
      BLOB65,535L+2
      (L<255)
      MEDIUMBLOB1,677,215L+3
      (L<224)
      LONGBLOB4,294,967,295L+4
      (L<232)
    • 列挙型
      型最高値バイト数
      ENUM('value1',...)
      括弧内に指定された任意の文字列      		65,535値1 or 2
      SET('value1',...)
      括弧内に指定された任意の文字列の組み合わせ      		64メンバ1/2/3/4/8
  • 名簿DBの「ER図」とフィールド内容の事例
    「名簿DB」には、名簿テーブル「persons」と県名テーブル「prefecture」、市名テーブル「city」の構成です。 「名簿DB」のER図
    • プライマリキーは、
      名簿テーブルが「seq_num」、県名テーブルが「code」、市名テーブル「code」です。
    • 名簿テーブルの外部キー（FX）は、
      「pref_code」が県名テーブルの「code」と関係し、「city_code」が市名テーブルの「code」と関係しています。
    「名簿（persons）」テーブルのフィールド設定内容は、
    「名簿（persons）」テーブルの詳細なフィールド設定内容
    • 「シーケンスNo」は、
      プライマリーキーであり、「auto_increment」を指定することで入力しなくても自動的に番号付けされます。
    • 「更新日」は、
      「CURRENT_TIMESTAMP」を指定することで入力しなくてもレコードの追加・変更時に実行時の日付・時刻が挿入されます。
    • 「誕生日」は、
      「必須」指定がないので、入力しなくてもエラーとならず、入力しないと「デフォルト値」（'0000-00-00'）が自動挿入されます。
    • ER図作成「A5:SQL Mk-2」ツールを利用したDDL作成
      【ER図作成ツール】のダウンロードサイト
      「A5:SQL Mk-2」ツールのダウンロードサイト
      「A5:SQL Mk-2」ツールに「DDL作成」機能があり、ER図を作成した内容から「sql」ファイルとして保存ができます。
      「A5:SQL Mk-2」ツールによる「ER」図の作成例
    • ER図とデータベース設計、DDL生成の詳細は、
      WEBデザイン・開発講義：「システム設計とWEBデザイン」にある「ER図とデータベース設計」と「セキュリティ対策とDB対応、DDL生成と実装」を参照して下さい。

『MySQLのSQL』の主なコマンド操作

これから「MySQL」のコマンドプロンプト操作する上で、サンプルの「名簿DB」（データベース名「list_db」）を通して、データベースの作成、挿入・修正・削除などの操作と「SQL」文法を説明します。

  • 「SQL」コマンドプロンプトの起動と終了
    操作内容SQLコマンド
    操作例
    データベース接続
    （シェルコマンド）    		mysql -u[ユーザ名] [-p][パスワード]
    ※[-p]とパスワードの間にスペースなし
    > mysql -u root -padmin
    使用データベースの指定use データベース名
    sql> USE list_db;
    コマンドプロンプトのコード変換
    （使用データベースの文字セット名）    		set names [コード名]
    ※コード名は、ハイフンを含まない：「Shift_JIS」は"cp932"、
    「UTF-8」は"utf8"
    sql> SET NAMES cp932;
    データベース切断exit 又は quit
    sql> exit;
    sql> quit;
    外部ファイルからのSQL文入力source [ファイルパス]
    ※パスは、クォーテーションなし
    sql> SOURCE C:\user\list_db_DDL.sql
    • 「MySQL」のコマンドプロンプトの起動は、
      2種類の方法があり、「すべてのプログラム」→「MySQL」→「MySQL Server 5.5」→「MySQL Command Line...」を選択して起動する場合、プロンプトで「Enter password:」が表示されるので「パスワード」（admin）を入力します。
      「MySQL Command Line...」を選択して起動 別の方法は、通常のコマンドプロンプトで「MySQL」起動して「ユーザ名」（root）と「パスワード」（admin）を入力する場合があります。
      通常のコマンドプロンプトで「MySQL」起動
    • 「MySQL」コマンドの使用データベースの指定は、
      「名簿DB」（list_db）を選択する場合のコマンドは、「USE list_db;」を入力します。
    • 「MySQL」コマンドプロンプトの文字コード設定
      MySQLの保存の文字コードが「UTF-8」であり、Windows-OSのコマンドプロンプトが「Shift_JIS」のため、コマンド入力・表示の文字コードを「Shift_JIS」に設定をする必要があり、「SET NAMES cp932;」コマンドを入力します。
      MySQLのコマンド起動から、SET文、CREATE database文で「名簿DB」作成とUSE文の操作方法
  • 【DDL】コマンド
    操作内容SQLコマンド
    操作例
    データベース作成create database [データベース]文
    sql> CREATE database list_db;
    テーブル作成create table [テーブル名]文
    sql> CREATE table persons (
    -> code int,name varchar(128),
    -> PRIMARY KEY-> (code));
    テーブル修正alter table [テーブル名]文
    sql> ALTER table city MODIFY name
    -> city_name;
    テーブル削除drop table [テーブル名]文
    sql> DROP table persons;
    データベース一覧表示show databases文
    sql> SHOW databases;
    テーブル一覧表示show tables文
    sql> SHOW tables;
    項目内容一覧表示show fields from [テーブル名]文
    describe [テーブル名]文
    sql> SHOW fields from persons;
    sql> DESCRIBE persons;
    • 「MySQL」のデータベース作成コマンドは、
      「Shift_JIS」コードを設定し、「CREATE DATABASE list_db;」のコマンドを入力すると作成できます。データベース作成した後、そのデータベースを利用するため、「USE DATABASE list_db;」のコマンドを入力します。
      「データベース作成」と「データベース利用」コマンド
    • 「MySQL」のテーブル作成コマンドは、
      「CREATE TABLE persons ( ... );」のコマンドを入力します。作成したテーブルのカラム（フィールド）の内容を参照するために「DESCRIBE persons;」のコマンドを入力します。
      「テーブル作成」と「フィールド表示」コマンド
      各カラムは、「,」区切りします。「seq_num」カラムに対して、自動番号付けの「auto_increment」とプライマリーキー制約の「primary key」を付加します。省略値を指定するには、「default xxx」、必須の場合は「not null」を付加します。 外部（参照照合）キー制約の設定は、参照先のテーブルがないとエラーとなるので、前もってテーブル作成しておく必要があります。「foreign key (カラム名) references 参照先テーブル名(親カラム名)」を入力します。
      テーブルカラム表示で「key」列の内容は、プライマリキーは「PRI」、外部キーは特定の値の複数あることで「MUL」（Multi）が表示されます。
      CREATE文で「県名と市名テーブル」作成とDESCRIBE文の操作方法
      CREATE文で「名簿テーブル」作成の操作方法
    • 「sql」ファイルから一括DDLの作成
      ER図ツールで作成した「sql」ファイルから一括に入力する「SOURCE パス名」コマンドを利用します。
      「sql」ファイルの「SOURCE」コマンド
      DESCRIBE文、DROP文、SHOW文とSOURCE文を利用したDDLファイルの操作方法
  • 【DML】コマンド
    操作内容SQLコマンド
    操作例
    レコード挿入insert into [テーブル名] values('xxx',...)
    sql> INSERT INTO persons VALUES
    -> (1,'友子','1970-1-1','tomoko@xx.yy','KGW',
    -> 'K01',now());
    レコード挿入
    の項目選択    		insert into [テーブル名] (項目名,...) values('xxx',...)
    sql> INSERT INTO persons (seq_num,name)
    -> VALUES(1,'てすと');
    レコード更新update [テーブル名] set 文
    sql> UPDATE persons SET name='弥生'
    -> WHERE seq_num = 2;
    レコード削除delete from [テーブル名] 文
    sql> DELETE FROM persons WHERE
    -> seq_num = 2;
    レコード検索（選択）SELECT文(all)
    sql> SELECT * FROM city;
    レコード検索
    複数テーブル    		SELECT WHERE xxx = xxx 文
    sql> SELECT persons.name,
    -> prefecture.name,city.name FROM
    -> persons,prefecture,city WHERE
    -> persons.pref_code = prefecture.code
    -> AND persons.city_code = city.code;
    レコード検索
    join(外部）    		SELECT LEFT JOIN文
    sql> SELECT * FROM persons LEFT JOIN
    -> city ON persons.city_code = city.code;
    レコード検索
    複数join(内部)    		SELECT INNER JOIN文
    sql> SELECT persons.name,
    -> city.name,prefecture.name FROM persons
    -> INNER JOIN city ON
    -> city_code=city.code
    -> INNER JOIN prefecture ON
    -> pref_code = prefecture.code;
    • 「名簿DB」の挿入レコードは、次の内容とします。
      「名簿DB」の挿入レコード内容
    • 「県名」テーブルのレコード挿入のコマンドは、
      必須のレコードのみの入力としたいので、カラム名（フィールド）を指定した「INSERT INTO prefecture (code,name) VALUES ( ... ),( ... ), ...;」コマンドを入力します。
      ※valuesの後の( ... )は、1レコード分を意味し、コンマ区切りで連続したレコードの挿入ができます。
      「県名」テーブルのレコード挿入
      レコード挿入した内容の一覧を確認するため、「SELECT * FROM prefecture;」コマンドを入力します。
      ※「*」は、ワイルドカードですべてを意味します。
    • 同様に「市名」テーブルもレコード挿入します。
    • 「名簿」テーブルのレコード挿入のコマンドは、
      外部キーの設定をしているため、親の「県名」テーブルと「市名」テーブルがレコード挿入済みでないとエラーとなります。逆にテーブル削除する場合は、外部キー設定のテーブルから「DROP」する必要があります。レコード挿入のカラム名（フィールド）を指定した「INSERT INTO persons (name,birth,email,pref_code,city_code) VALUES ( ... ),( ... ), ...;」コマンドを入力します。 「名簿」テーブルのレコード挿入
      INSERT文を利用したレコード挿入の操作方法
      UPDATE文を利用したレコード更新の操作方法
    • レコード検索で「横浜市」に絞り込み、表示内容のカラムを「名前」「誕生日」「市名」のみとした場合のコマンドは、
      「複数のテーブル」指定のコマンドの場合、「FROM persons,city」のようにして「WHERE city_code = city.code」で関係付け、加えて条件を「AND」に続けて「city_code = 'K001'」を記述します。
      「JOIN文」指定のコマンドの場合、「FROM persons」のようにしてJOIN文「INNER JOIN city ON city_code=city.code」で関係付け、加えて条件を「city_code = 'K001'」を記述します。
      ※「city.name」のカラム指定は、記述コマンド内で『name』が複数ある場合にどのテーブルの『name』かを指定するために、テーブル名の後に「.」(dot)を付けて階層下の『name』を記述します。
      ※「city_name」の表示（処理）カラム名は、『name』が同じ場合など、「city.name AS city_name」の『AS』以降の「処理カラム名」を記述することで、それ以降の記述を「処理カラム名」で利用できます。
      レコード検索で「横浜市」に絞り込み、「名前」「誕生日」「市名」のみ表示
      名簿DBの「名簿」「県名」「市名」テーブルのレコード挿入：『list_db_insertrecord.sql』ファイルのダウンロード
      「SELECT JOIN」文を利用したレコード検索の操作方法
  • 【DCL】コマンド
    操作内容SQLコマンド
    操作例
    ログイン権限制限grant privileges文
    sql> GRANT all PRIVILEGES ON list_db.*
    -> TO user@localhost
    -> IDENTIFIED BY 'yoshi';
    ※上記の例は、データベース名「list_db」すべてに対して、「ユーザー名」が「user」、パスワード「yoshi」でデータベース接続できる制限です。再度、「データベースに接続する操作」をすることで有効にできます。
    GRANT文を利用したログイン権限制限の操作方法

MySQLのSQL文法補足説明

『SQL』とは
「Structured Query Language」の略で、データベースの定義・操作などの問合せ（Query）言語のことを言い、ANSI(アメリカ規格協会)やISO(国際標準化機構)の標準として規格化されています。
「ER図」とは
「Entity-Relationship」の略で、テーブルとテーブルの関連を表現する図をいいます。データベースの設計を行う際、情報を整理し、分析するときに「ER図」を用います。
エンティティとは、ある目的を持って同じようなデータを集め､その目的を明確に表す名前を付けたもので、人（誰が）、物（何を）、金、時間、やりとり･活動･行為（どうする）などが対象になります。

MySQLリファレンス

参考資料の表示

推奨参考図書

  • 「ポケット詳解 SQL辞典 第2版」（秀和システム）
    ポケット詳解 SQL辞典

pagetopへ