データベース（Part.11）｜ MariaDB SQLと表の種類｜プログラミング学習おすすめ書籍情報発信パソコン初心者エンジニア希望者新人エンジニア IT業界への就職・転職希望者サポートサイト Programming learning Recommended schools Recommended books Information dissemination Computer beginners Prospective engineers New engineers Prospective job seekers in the IT industry Support site

目標
DBMSの仕組み
- DBMSの機能（再掲載）
「SQL」と「表（テーブル）」の種類
- 「SQL」の種類
- 「テーブル（表）」の種類
SQLコマンドの入力例（MariaDB）
ブックマークのすすめ

目標

「SQL」と「表」の種類について確認して理解する。
「SQLコマンド」の利用方法を理解する

DBMSの仕組み

DBMSの機能（再掲載）

データベースマネジメントシステムには次のような主な機能がありました。

①「データベース言語の提供」
データベースの定義・操作・制御などを行う言語を利用できる

②「効率的なデータアクセス機構」（Part.8の記事で扱いました）
データの効率的な格納方法の提供・インデックス設定による検索処理の効率化・問い合わせ最適化機能の提供

③「保全機能（整合性の維持）」（Part.9の記事で扱います）
データの整合性を保証するための機能

④「機密保護」
ユーザーへのアクセス権限の付与を提供し不正アクセスを防止する機能

⑤「排他制御（同時実行の制御）」（Part.9の記事で扱います）
複数の処理を同時に行ったときに矛盾が起きないようにする機能

⑥「障害復旧」（Part.9の記事で扱います）
システムの障害やトラブルに対してデータベースを復旧する機能

これらの機能を少しずつ見ていきたいと思います。
（データベース言語の提供については実際の操作で確認）

「SQL」と「表（テーブル）」の種類

「SQL」の種類

SQLの分類は次のようになります。

DDL Data Definition Language データ定義言語	テーブル作成・変更・削除 CREATE・ALTER・DROP リレーション定義リレーション制約の定義 PRIMARY KEY・ FOREIGN KEY・ CHECKなど
DML Data Manipulation Language データ操作言語	テーブルへの問い合わせ SELECT INSERT DELETE UPDATE
DCL Data Control Language データ制御言語	アクセス権限の設定 GRANT・REVOKE
TCL（もしくはDCL） Transaction Control Language トランザクション制御言語	トランザクション管理 BEGIN・COMMIT・ROLLBACK

「テーブル（表）」の種類

テーブル（表）は次のように分類できます。

実テーブル	データベースに作成される実際のテーブル
導出表	実テーブルから関係演算で作成される結果のテーブル
ビューテーブル	導出表（導出テーブル）に名前を付けたもの

実テーブルは実際のテーブルなのでデータの更新が行えますが、導出表やビューテーブルはデータの直接的な更新を行うことは基本出来ません。

SQLコマンドの入力例（MariaDB）

SQLコマンドデータベースの操作（MariaDB）

シンプルなデータベースの操作

MariaDBへの接続

mysql -u ユーザー名 -p
-u:ユーザー名 -p:パスワードを要求

MariaDBでのデータベース一覧表示

show databases;

MariaDBでのデータベース作成

create database データベース名;

MariaDBでのデータベース削除

drop database データベース名;

MariaDBでの利用データベースの指定

use データベース名;

MariaDBでの利用中データベースの確認

select database();
プロンプトに表示されます。

MariaDBの終了

quit（またはexit）
※セミコロンは要りません。

SQLコマンドテーブルの操作編（MariaDB）

シンプルなテーブル操作

MariaDBでのテーブルの作成

create table テーブル名 (
カラム名１１の型 primary key,
カラム名２２の型,
カラム名３３の型,
…
);

MariaDBでのテーブルの一覧表示

show tables;

MariaDBでのテーブルのカラムの構造表示（よく使います）

desc テーブル名;

MariaDBでのテーブルの中身の表示

【全カラムを表示】
select * from テーブル名;

【指定したカラムを表示】
select カラム名１,カラム４ … from テーブル名;

MariaDBでのテーブルの削除

drop table テーブル名;

MariaDBで事前に準備したsqlが書かれたファイルを利用する場合

source ファイル名.txt;

データ型

データ型の種類

整数：int
実数：double
固定長の文字列：char(文字の長さで「0～255」)
可変長の文字列：varchar(文字の最大の長さ「0～65535」)
日付：date
日時：datetime

制約の種類

UNIQUE制約…任意のカラムの中で重複を許さない制約のこと。

NOT NULL制約…「NULL」は「値が入っていないですよ！」という値（フラグのようなもの）で、空とも、0とも違います。NOT NULL制約は、この「NULL」という値の入力を許可しないという制約です。

キー制約（主キー制約）…主キーはリレーションでタプルを一意に識別できる値であるために重複を許さず（UNIQUE制約）、さらにNULLの入力も許さない（NOT NULL制約）制約です。また空値も許されません。

ドメイン制約…属性の値は、全てその属性のドメイン（属性の型とそのルール）に所属するという制約があります。この制約の範囲は属性の型（文字列や整数など）によって決まります。（例：「数値の範囲」や「特定の文字列の集合」など）

参照整合性制約…属性が他のテーブルを参照する場合に、参照先に必ずタプルが存在しなければいけない制約

制約の定義の種類

列制約…カラムごとに制約を定義します。
テーブル制約…テーブルでの制約を定義します。（列制約と同じ定義をテーブル制約で定義することができます。）

列制約があるテーブル作成

列制約はカラムごとに制約を定義します。

MariaDBでの例制約のあるテーブルの作成

create table テーブル名 (
カラム名１１の型 primary keyなどの列制約,
カラム名２２の型 not nullなどの列制約,
カラム名３３の型 uniqueなどの列制約,
カラム名４４の型 checkなどの列制約,
…
);

参照整合性制約の場合
create table テーブル名 (
カラム名１１の型 primary key,
カラム名２２の型,
カラム名３３の型 references 参照先のテーブル名(参照先テーブルのカラム名),
カラム名４４の型,
…
);

テーブル制約があるテーブル作成

MariaDBでのテーブル制約のあるテーブルの作成（カラムが１つ）

create table <テーブル名> (
カラム名１１の型,
カラム名２２の型,
カラム名３３の型,
…
　primary key(カラム名)
);

MariaDBでのテーブル制約のあるテーブルの作成（カラムが複数）

create table <テーブル名> (
カラム名１１の型,
カラム名２２の型,
カラム名３３の型,
…
　primary key(カラム名１, カラム名２, …)
);

複数のカラムを指定する場合はテーブル制約でないと定義できません。

create table <テーブル名> (
カラム名１１の型,
カラム名２２の型,
カラム名３３の型,
…
　unique(カラム名)
);

not null制約は列制約でないと定義できません。

create table テーブル名 (
カラム名１１の型,
カラム名２２の型,
カラム名３ int,
…
　check(カラム名３ > 0)
);

参照整合性制約の場合
create table テーブル名 (
カラム名１１の型 primary key,
カラム名２２の型,
カラム名３３の型,
カラム名４４の型,
…
foreigh key (カラム名) references 参照先のテーブル名(参照先テーブルのカラム名)
);

MariaDBでのデータの挿入

insert into テーブル名(カラム名１, カラム名２, …) values(‘文字列’,数値など…);

テーブルのカラム修正

MariaDBでのテーブルのカラムの名前変更

alter table テーブル名 modify 変更前のカラム名変更後カラム名変更後のデータ型;

MariaDBでのテーブルのカラムのデータ型変更

alter table テーブル名 modify カラム名変更後のデータ型;

MariaDBでのテーブルのカラムの追加

【末尾への追加】
alter table テーブル名 add カラム名データ型;
【先頭への追加】
alter table テーブル名 add カラム名データ型 first;
【指定箇所への追加】
alter table テーブル名 add カラム名データ型 after 指定箇所のひとつ前のカラム名;

MariaDBでのテーブルのカラムの削除

alter table テーブル名 drop カラム名;

テーブル内のデータの変更・修正

MariaDBでのテーブル内のデータの変更・修正

update テーブル名 set カラム名１=データ１,… where カラムの条件;

修正時にwhereで条件を指定しないと全てのデータに修正がかかります。

テーブル内のデータに対する削除

MariaDBでのテーブル内のデータに対する削除

【全データの削除】
delete from テーブル名;
【指定データの削除】
delete from テーブル名 where カラムの条件;

カラムの条件には「カラム名 = 1」などで指定します。

テーブルの中身の表示

MariaDBでのテーブルの中身の表示

【全カラムを表示】
select * from テーブル名;

【指定したカラムを表示】
select カラム名１,カラム４ … from テーブル名;

【指定した条件のカラムを表示】
select カラム名１,カラム４ … from テーブル名 where カラムの条件;

SQLコマンドでの条件（MariaDB）

SQLでの質問の種類

SQLでの条件には次の種類があります。

単純質問…「select文」の「from句」で指定するテーブルが１つで、かつ「where句」に「select文」を含まない質問です。
結合質問…「select文」の「from句」で指定するテーブルが複数で「where句」にはデータの結合を行うための条件を定義する質問です。
ネスト型質問（副問い合わせ）…「where句」に「select文」が入る質問です。

SQLでの色々な検索方法-単純質問（MariaDB）

集約関数を利用した検索

集約関数の種類

SUM…合計を計算します。
AVG…平均を計算します。
MIN…最小値を取り出します。
MAX…最大値を取り出します。
COUNT…行数（レコードの数）を計算します。

利用例：
select sum(カラム名) from テーブル名 where句で条件を指定できます。;
利用例：
select avg(カラム名) from テーブル名 where句で条件を指定できます。;
利用例：
select min(カラム名), max(カラム名) from テーブル名 where句で条件を指定できます。;
利用例：
select count(カラム名) from テーブル名 where句で条件を指定できます。;

where句では「等号・不等号」「null」「between」「in」「like」が利用できます。

where 句：１つの条件を利用した検索（単純質問）

単純質問

「select文」の「from句」で指定するテーブルが１つで、かつ「where句」に「select文」を含まない質問です。

等号・不等号の利用

利用例：
select * from テーブル名 where カラム名 = ‘検索する文字列’;
select * from テーブル名 where カラム名 = 1;
select * from テーブル名 where カラム名 > 0;

is nullの利用

利用例：
select * from テーブル名 where カラム名 is null;
select * from テーブル名 where カラム名 is not null;

betweenの利用

利用例：
select * from テーブル名 where カラム名 between ‘日付最小値’ AND ‘日付最大値’;
select * from テーブル名 where カラム名 between 数値最小値 AND 数値最大値;
select * from テーブル名 where カラム名 between ‘a’ AND ‘z’;

inの利用

利用例：
select * from テーブル名 where カラム名 in(‘検索の値１’, ‘検索の値２’,…);
select * from テーブル名 where カラム名 in(数値１, 数値２, 数値３,…);

notを利用する場合
select * from テーブル名 where カラム名 not in(‘検索の値１’, ‘検索の値２’,…);

likeの利用

利用例：
【文字列の「検索値」が末尾にある属性を取り出します。】
select * from テーブル名 where カラム名 like ‘％検索値’;
【文字列の「検索値」が先頭にある属性を取り出します。】
select * from テーブル名 where カラム名 like ‘検索値％’;
【文字列の「検索値」を含む属性を取り出します。】
select * from テーブル名 where カラム名 like ‘％検索値％’;
【_は一文字を表現するので「文字+検索値+文字+文字」となる属性を取り出します。】
select * from テーブル名 where カラム名 like ‘_検索値__’;

group by 句：重複したカラムの値をまとめる

利用例：
select sum(カラム名) from テーブル名 group by カラム名;
利用例：
select avg(カラム名) from テーブル名 group by カラム名;
利用例：
select min(カラム名), max(カラム名) from テーブル名 group by カラム名;
利用例：
select count(カラム名) from テーブル名 group by カラム名;

group by句は集約関数と併せて利用します。

group by + having 句：group byの結果に条件をかけての取り出し

利用例：
select sum(カラム名)
from テーブル名
group by カラム名
having sum(カラム名)>1000;

利用例：
select avg(カラム名)
from テーブル名
group by カラム名
having avg(カラム名)>=1000;

利用例：
select min(カラム名), max(カラム名)
from テーブル名
group by カラム名
having min(カラム名)<200 and max(カラム名)>=1000;

利用例：
select count(カラム名)
from テーブル名
group by カラム名
having count(カラム名) > 0;

having 句にはgroup by 句で利用した属性や集約関数しか記述できません。

order by 句：昇順降順での並び替え

【昇順で並び替え】
select * from テーブル名 where カラム名 asc;
【降順で並び替え】
select * from テーブル名 where カラム名 desc;

SQLでの色々な検索方法-結合質問（MariaDB）

where 句：複数の条件を利用した検索（結合質問）

結合質問

「select文」の「from句」で指定するテーブルが複数で「where句」にはデータの結合を行うための条件を定義する質問です。

select テーブル１.カラム名A, テーブル２.カラム名B
from テーブル１の名前テーブル２の名前
where テーブル１.カラム名A = テーブル２.カラム名A
AND
テーブル２.カラム名 > 1000;

参考：結合にはひとつのテーブルを別々のテーブルに見立てて結合を行う自己結合というものがあります。下の例では「テーブル名A」を「a」「b」の２つのテーブルに見立てて結合を行っています。

利用例：
select a.カラム名１, b.カラム名１,
from テーブル名A as a, テーブル名A as b
where a.カラム名２ > 1000 AND b.カラム名２ > 1000

SQLでの色々な検索方法-ネスト型質問（MariaDB）

where 句：where 句にselect文が入る検索（ネスト型質問）

ネスト型質問（副問い合わせ）

「where句」に「select文」が入る質問です。where句の結果を外のselect文で利用するといった流れになります。

相関がなし

select カラム名１, カラム名２
from テーブル名
where カラム名１ >= (select avg(カラム名１) from テーブル名);

相関があり

select a.カラム名１, a.カラム名２
from テーブル名 as a
where カラム名１ >= (
select avg(b.カラム名１)
from テーブル名 as b
where a.カラム名１ = b.カラム名１
);

ネスト型質問（副問い合わせ）には相関があるものとないものがあります。

相関がない問い合わせ…外の問い合わせが内側の問い合わせと分離されている問い合わせ。

相関がある問い合わせ（相関副問い合わせ）…外側select文で利用しているテーブルが内側のselect文に影響を与える問い合わせ。内側のselect文にあるwhere句の中でカラムの比較があるなどが当てはまります。
※上のSQL文でいう「a.カラム１＝b.カラム２」の部分（自己結合）

相関副問い合わせの処理は次のようになります。
①：外側のselect文（１つのレコード分を抽出）
②：内側のselect文（比較を行う）
③：①、②を行数分行う

SQLでの色々な検索方法-結合（MariaDB）

結合

内部結合

select テーブル名１.カラム名１, テーブル名１.カラム名２, テーブル名２.カラム名２ …
from テーブル名１
join テーブル名２
on テーブル名１.カラム名 = テーブル名２.カラム名;

内部結合（asを利用した別名指定での記述）

select a.カラム名１, a.カラム名２, b.カラム名２ …
from テーブル名１as a
join テーブル名２ as b
on a.カラム名 = b.カラム名;

右外部結合

select テーブル名１.カラム名１, テーブル名１.カラム名２, テーブル名２.カラム名２ …
from テーブル名１
right join テーブル名２
on テーブル名１.カラム名 = テーブル名２.カラム名;

右外部結合（asを利用した別名指定での記述）

select a.カラム名１, a.カラム名２, b.カラム名２ …
from テーブル名１as a
right join テーブル名２ as b
on a.カラム名 = b.カラム名;

左外部結合

select テーブル名１.カラム名１, テーブル名１.カラム名２, テーブル名２.カラム名２ …
from テーブル名１
left join テーブル名２
on テーブル名１.カラム名 = テーブル名２.カラム名;

左外部結合（asを利用した別名指定での記述）

select a.カラム名１, a.カラム名２, b.カラム名２ …
from テーブル名１as a
left join テーブル名２ as b
on a.カラム名 = b.カラム名;

SQL ビューの作成と利用方法（MariaDB）（外部スキーマ）

ビューの概要・作成・利用方法

ビューの概要

ひとつ、もしくは複数の実テーブルから必要な項目を取り出す操作で「何度も繰り返して使う操作」を定義して「再利用可能にしたもの」をビューといいます。

ビューを利用するメリット

ビューを利用すると次のようなメリットがあります。

効率的なデータの取り出しができる
変更されたくないカラムを隠すことができる（SQLで問い合わせるときにカラムを書かないでいい。）

ビューを利用するデメリット

ビューを利用すると次のようなデメリットがあります。

ビューに対いてデータの更新を行うと更新時異常を起こすことがある

ビューで更新を行うのであれば、ビューのカラムが実テーブルのカラムと一意に対応してる必要があります。

例えば２つの実テーブルから取り出しを行ったとき、取り出したテーブルをビューとする場合、このビューのカラムの値と２つの実テーブル内の値が異なる値と結びついていると更新時異常がおこります。

ビューの作成

create view ビュー名 as select select文の中身…;

例えば、上の相関副問い合わせのSQLをビューで定義すると次のようになります。

create view ビュー名 as
select a.カラム名１, a.カラム名２
from テーブル名 as a
where カラム名１ >= (
select avg(b.カラム名１)
from テーブル名 as b
where a.カラム名１ = b.カラム名１
);

ビューの利用

定義したビューは実テーブルのように扱えます。
select * from ビュー名;

ビューの定義の変更

ビューの定義の変更には２つのコマンドがあります。

create or replace ビュー名…ビューが存在すれば定義を変更して、存在しない場合は新規で作成
alter view ビュー名…ビューのカラムの構造を変更できます。

【定義の変更パターン１】
create or replace view ビュー名 as select select文の中身…;
【定義の変更パターン２】
alter view ビュー名 as select select文の中身…;

ビューの定義内容の確認

ビューの定義を確認できます。
show create view ビュー名

ビューの削除

ビューを削除することができます。
drop view ビュー名;

SQL ストレージエンジンの変更（MariaDB）（内部スキーマ）

ストレージエンジンの概要と設定

ストレージエンジンとは実行可能コードを利用してデータへアクセスを行うエンジンです。データの格納方法やトランザクション処理の処理方法などを定義しています。

ストレージエンジンには次のようなものがあります。

MyISAM（マイアイサム）…MySQL5.4まで標準エンジン。ロックをテーブル単位で行います。トランザクション非対応。
InnoDB（イノディービー）…MySQL5.5から標準エンジン。ロックを行単位で行います。トランザクション対応。

ストレージエンジンの確認

次のコマンドで現在のストレージエンジンを確認できます。
show create table テーブル名;

ストレージエンジンの変更

次のコマンドで現在のストレージエンジンを変更できます。
alter table テーブル名 engine=エンジン名;

※エンジン名には「MyISAM」や「InnoDB」が入ります。

SQL トランザクション（MariaDB）

トランザクションが利用できるストレージエンジンの「InnoDB」が設定されている必要があります。

トランザクションは全てのコマンドで利用できるわけではありません。削除コマンドの「dropコマンド」や定義変更コマンドの「alterコマンド」などはトランザクション処理の対象外となります。

トランザクションの開始方法

トランザクションの開始方法は次の２つがあります。

start transaction;
begin;

トランザクションの終了方法

トランザクションの終了方法は「処理が成功」した場合と「処理が失敗」した場合の２つがあります。

commit;…処理が成功した場合に利用します。
rollback;…処理が失敗した場合に利用します。

MariaDBでは自動コミットがオンになっています。「start transaction;」や「begin;」が利用されると自動コミットが働かないようになっています。

トランザクション分離レベル

トランザクションが引きおこすトラブル

トランザクションが別のトランザクションに影響を与えるトラブルとして次のものがあります。

ダーティーリード…トランザクション内でコミットしていないデータが別のトランザクションで読み込まれてしまうトラブルです。
ファジーリード…トランザクションで読み込んだ値に対して、別のトランザクションが値を更新し、その更新後の値を読み込めてしまうトラブルです。
ファントムリード…トランザクションで読み込んだテーブルに対して別のトランザクションがテーブルに行を追加することができ、その更新後の行を含めてテーブルを読み込めてしまうトラブルです。

トランザクションの分離レベル

トランザクションの分離レベルは４段階に分かれています。それぞれ次のようになります。

READ UNCOMMITTED…トランザクション内でコミットしていないデータが別のトランザクションで読み込まれてしまうレベル。
READ COMMITTED…トランザクションで読み込んだ値に対して、別のトランザクションが値を更新し、その更新後の値を読み込めてしまうレベル。
REPEATABLE READ…トランザクションで読み込んだテーブルに対して別のトランザクションがテーブルに行を追加することができ、その更新後の行を含めてテーブルを読み込めてしまうレベル。
SERIALIZABLE…全てのトランザクションが直列化され逐次実行されるので別のトランザクションの影響を全く受けないレベル。ただし、ロックの解除待ちが多くなるので、なかなか使用されるレベルではありません。

分離レベルと発生する問題の関係

分離レベルと問題	ダーティーリード	ファジーリード	ファントムリード
READ UNCOMMITTED	起こる	起こる	起こる
READ COMMITTED	起こらない	起こる	起こる
REPEATABLE READ	起こらない	起こらない	起こる
SERIALIZABLE	起こらない	起こらない	起こらない

トランザクションの分離レベルではどの程度の問題を許容するかで決定をします。

トランザクションの分離レベルの確認

select @@tx_isolation;

デフォルトでは「REPEATABLE READ」に設定されています。

トランザクションの分離レベルの変更

set session transaction isolation level 分離レベル;

分離レベルには「READ UNCOMMITTED」「READ COMMITTED」「REPEATABLE READ」「SERIALIZABLE」を入力します。

このコマンドは一時的な設定です。トランザクションで現在のセッションを切るまで持続します。

SQL ユーザーの作成＆権限の付与と剥奪（MariaDB）

ユーザーの作成

MariaDBではユーザーの作成に「create userコマンド」を利用します。

create user ‘ユーザー名’@’ホスト名’ identified by ‘パスワード’;

ユーザーの作成後にパスワードを作成する場合はset passwordコマンドを利用します。

set password for ‘ユーザー名’@’ホスト名’ = password(‘パスワード’);

ユーザーの確認

ユーザーの確認にはデータベースにあるuserテーブル（mysql.user）を確認します。

select user, host from mysql.user;

ユーザーの削除

drop user ‘ユーザー名’@’ホスト名’;

権限の付与

作成したユーザーはデータベースへの操作が全くできない状態です。作成したユーザーには利用できるコマンドやテーブルなどを指定して操作権限を付与します。

【グローバルレベルでの付与…全てのデータベースに対して権限が得られます。】
grant 権限, 権限, 権限… on *.* to ‘ユーザー名’@’ホスト名’;

【データベースレベルでの付与…指定したデータベースに対して権限が得られます。】
grant 権限, 権限, 権限… on データベース名.* to ‘ユーザー名’@’ホスト名’;

【テーブルレベルでの付与…テーブルに対して権限が得られます。】
grant 権限, 権限, 権限… on データベース名.テーブル名 to ‘ユーザー名’@’ホスト名’;

【利用中データベースでのテーブルへの権限付与】
grant 権限, 権限, 権限… on テーブル名 to ‘ユーザー名’@’ホスト名’;

レベルはこの他にカラムレベルで付与ができます。

権限	権限の内容
select	SELECT の使用を有効にします。レベル: グローバル、データベース、テーブル、カラム。
insert	INSERT の使用を有効にします。レベル: グローバル、データベース、テーブル、カラム。
update	UPDATE の使用を有効にします。レベル: グローバル、データベース、テーブル、カラム。
delete	DELETE の使用を有効にします。レベル: グローバル、データベース、テーブル。
create	データベースおよびテーブルの作成を有効にします。レベル: グローバル、データベース、テーブル。
all privileges	GRANT OPTION および PROXY を除くすべての権限を指定されたアクセスレベルで付与します。