目標
「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 テーブル名 (
カラム名1 1の型 primary key,
カラム名2 2の型,
カラム名3 3の型,
…
);
MariaDBでのテーブルの一覧表示
show tables;
MariaDBでのテーブルのカラムの構造表示(よく使います)
desc テーブル名;
MariaDBでのテーブルの中身の表示
【全カラムを表示】
select * from テーブル名;
【指定したカラムを表示】
select カラム名1,カラム4 … 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 テーブル名 (
カラム名1 1の型 primary keyなどの列制約,
カラム名2 2の型 not nullなどの列制約,
カラム名3 3の型 uniqueなどの列制約,
カラム名4 4の型 checkなどの列制約,
…
);
参照整合性制約の場合
create table テーブル名 (
カラム名1 1の型 primary key,
カラム名2 2の型,
カラム名3 3の型 references 参照先のテーブル名(参照先テーブルのカラム名),
カラム名4 4の型,
…
);
テーブル制約があるテーブル作成
MariaDBでのテーブル制約のあるテーブルの作成(カラムが1つ)
create table <テーブル名> (
カラム名1 1の型,
カラム名2 2の型,
カラム名3 3の型,
…
primary key(カラム名)
);
MariaDBでのテーブル制約のあるテーブルの作成(カラムが複数)
create table <テーブル名> (
カラム名1 1の型,
カラム名2 2の型,
カラム名3 3の型,
…
primary key(カラム名1, カラム名2, …)
);
create table <テーブル名> (
カラム名1 1の型,
カラム名2 2の型,
カラム名3 3の型,
…
unique(カラム名)
);
create table テーブル名 (
カラム名1 1の型,
カラム名2 2の型,
カラム名3 int,
…
check(カラム名3 > 0)
);
参照整合性制約の場合
create table テーブル名 (
カラム名1 1の型 primary key,
カラム名2 2の型,
カラム名3 3の型,
カラム名4 4の型,
…
foreigh key (カラム名) references 参照先のテーブル名(参照先テーブルのカラム名)
);
MariaDBでのデータの挿入
insert into テーブル名(カラム名1, カラム名2, …) 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 カラム名1=データ1,… where カラムの条件;
テーブル内のデータに対する削除
MariaDBでのテーブル内のデータに対する削除
【全データの削除】
delete from テーブル名;
【指定データの削除】
delete from テーブル名 where カラムの条件;
テーブルの中身の表示
MariaDBでのテーブルの中身の表示
【全カラムを表示】
select * from テーブル名;
【指定したカラムを表示】
select カラム名1,カラム4 … from テーブル名;
【指定した条件のカラムを表示】
select カラム名1,カラム4 … from テーブル名 where カラムの条件;
SQLコマンドでの条件(MariaDB)
SQLでの質問の種類
SQLでの質問の種類
SQLでの条件には次の種類があります。
- 単純質問…「select文」の「from句」で指定するテーブルが1つで、かつ「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 句:1つの条件を利用した検索(単純質問)
単純質問
「select文」の「from句」で指定するテーブルが1つで、かつ「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(‘検索の値1’, ‘検索の値2’,…);
select * from テーブル名 where カラム名 in(数値1, 数値2, 数値3,…);
notを利用する場合
select * from テーブル名 where カラム名 not in(‘検索の値1’, ‘検索の値2’,…);
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 + 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;
order by 句:昇順降順での並び替え
【昇順で並び替え】
select * from テーブル名 where カラム名 asc;
【降順で並び替え】
select * from テーブル名 where カラム名 desc;
SQLでの色々な検索方法-結合質問(MariaDB)
where 句:複数の条件を利用した検索(結合質問)
結合質問
「select文」の「from句」で指定するテーブルが複数で「where句」にはデータの結合を行うための条件を定義する質問です。
select テーブル1.カラム名A, テーブル2.カラム名B
from テーブル1の名前 テーブル2の名前
where テーブル1.カラム名A = テーブル2.カラム名A
AND
テーブル2.カラム名 > 1000;
SQLでの色々な検索方法-ネスト型質問(MariaDB)
where 句:where 句にselect文が入る検索(ネスト型質問)
ネスト型質問(副問い合わせ)
「where句」に「select文」が入る質問です。where句の結果を外のselect文で利用するといった流れになります。
相関がなし
select カラム名1, カラム名2
from テーブル名
where カラム名1 >= (select avg(カラム名1) from テーブル名);
相関があり
select a.カラム名1, a.カラム名2
from テーブル名 as a
where カラム名1 >= (
select avg(b.カラム名1)
from テーブル名 as b
where a.カラム名1 = b.カラム名1
);
SQLでの色々な検索方法-結合(MariaDB)
結合
内部結合
select テーブル名1.カラム名1, テーブル名1.カラム名2, テーブル名2.カラム名2 …
from テーブル名1
join テーブル名2
on テーブル名1.カラム名 = テーブル名2.カラム名;
内部結合(asを利用した別名指定での記述)
select a.カラム名1, a.カラム名2, b.カラム名2 …
from テーブル名1as a
join テーブル名2 as b
on a.カラム名 = b.カラム名;
右外部結合
select テーブル名1.カラム名1, テーブル名1.カラム名2, テーブル名2.カラム名2 …
from テーブル名1
right join テーブル名2
on テーブル名1.カラム名 = テーブル名2.カラム名;
右外部結合(asを利用した別名指定での記述)
select a.カラム名1, a.カラム名2, b.カラム名2 …
from テーブル名1as a
right join テーブル名2 as b
on a.カラム名 = b.カラム名;
左外部結合
select テーブル名1.カラム名1, テーブル名1.カラム名2, テーブル名2.カラム名2 …
from テーブル名1
left join テーブル名2
on テーブル名1.カラム名 = テーブル名2.カラム名;
左外部結合(asを利用した別名指定での記述)
select a.カラム名1, a.カラム名2, b.カラム名2 …
from テーブル名1as a
left join テーブル名2 as b
on a.カラム名 = b.カラム名;
SQL ビューの作成と利用方法(MariaDB)(外部スキーマ)
ビューの概要・作成・利用方法
ビューの概要
ひとつ、もしくは複数の実テーブルから必要な項目を取り出す操作で「何度も繰り返して使う操作」を定義して「再利用可能にしたもの」をビューといいます。
ビューを利用するメリット
ビューを利用すると次のようなメリットがあります。
- 効率的なデータの取り出しができる
- 変更されたくないカラムを隠すことができる(SQLで問い合わせるときにカラムを書かないでいい。)
ビューを利用するデメリット
ビューを利用すると次のようなデメリットがあります。
- ビューに対いてデータの更新を行うと更新時異常を起こすことがある
ビューの作成
create view ビュー名 as select select文の中身…;
ビューの利用
定義したビューは実テーブルのように扱えます。
select * from ビュー名;
ビューの定義の変更
ビューの定義の変更には2つのコマンドがあります。
- create or replace ビュー名…ビューが存在すれば定義を変更して、存在しない場合は新規で作成
- alter view ビュー名…ビューのカラムの構造を変更できます。
【定義の変更パターン1】
create or replace view ビュー名 as select select文の中身…;
【定義の変更パターン2】
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)
トランザクションの開始方法
トランザクションの開始方法は次の2つがあります。
- start transaction;
- begin;
トランザクションの終了方法
トランザクションの終了方法は「処理が成功」した場合と「処理が失敗」した場合の2つがあります。
- commit;…処理が成功した場合に利用します。
- rollback;…処理が失敗した場合に利用します。
トランザクション分離レベル
トランザクションが引きおこすトラブル
トランザクションが別のトランザクションに影響を与えるトラブルとして次のものがあります。
- ダーティーリード…トランザクション内でコミットしていないデータが別のトランザクションで読み込まれてしまうトラブルです。
- ファジーリード…トランザクションで読み込んだ値に対して、別のトランザクションが値を更新し、その更新後の値を読み込めてしまうトラブルです。
- ファントムリード…トランザクションで読み込んだテーブルに対して別のトランザクションがテーブルに行を追加することができ、その更新後の行を含めてテーブルを読み込めてしまうトラブルです。
トランザクションの分離レベル
トランザクションの分離レベルは4段階に分かれています。それぞれ次のようになります。
- 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 ‘パスワード’;
ユーザーの確認
ユーザーの確認にはデータベースにある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 を除くすべての権限を指定されたアクセスレベルで付与します。 |
権限の確認
show grants for ‘ユーザー名’@’ホスト名’;
with grant option
grant all on テーブル名 to ‘ユーザー名’@’ホスト名’ with grant option;
with grant option はもらった権限と同じ権限を別ユーザーに与えることができる権限になります。
権限の剥奪
権限の剥奪
【グローバルレベルでの剥奪】
revoke 権限, 権限, 権限… on *.* to ‘ユーザー名’@’ホスト名’;
【データベースレベルでの剥奪】
revoke 権限, 権限, 権限… on データベース名.* to ‘ユーザー名’@’ホスト名’;
【テーブルレベルでの剥奪】
revoke 権限, 権限, 権限… on データベース名.テーブル名 to ‘ユーザー名’@’ホスト名’;
【利用中データベースでのテーブルへの権限剥奪】
revoke 権限, 権限, 権限… on テーブル名 to ‘ユーザー名’@’ホスト名’;
今回は以上になります。
![](https://howahowablog.com/wp-content/uploads/2023/04/データベースおすすめ書籍6選-320x180.jpg)
ブックマークのすすめ
「ほわほわぶろぐ」を常に検索するのが面倒だという方はブックマークをお勧めします。ブックマークの設定は別記事にて掲載しています。
![](https://howahowablog.com/wp-content/uploads/2022/04/Google-Chrome-:ブックマー-Microsoft-Edge:お気に入り-登録・削除方法-320x180.jpg)
![](https://howahowablog.com/wp-content/uploads/2022/12/パソコン選び-320x180.jpg)