【基本情報対策】うさぎでもわかるデータベース 第04羽 SQL中編

こんにちは、ももやまです。

今回は、SQLの中編ということで、

• データベースの作成、削除
• 表の定義, 構造変更, 削除
• データの挿入, 削除
• データベースの権限

の4つについて説明します。

１．データベースの作成・削除

特に意識して覚える必要はなし。

(1) データベースの作成

create database 作成するデータベース名

(2) データベースの削除

drop database 削除するデータベース名

create が作成、drop が削除というのは頭に入れておきましょう。

本番、SQLの構文をど忘れしたときでも選択肢を絞ることができます。

２．表の定義, 構造変更, 削除

(1) 表の定義

定義に関する問題はたまに見かけるので、どんなふうに書くのかをさらっと確認しておきましょう。

基本構文

create table 表名 (
    列名1  データ型  [列名1の列制約]
    列名2  データ型  [列名2の列制約]
    列名3  データ型  [列名3の列制約]
    　︙

    [表制約]
)

それぞれの列の列制約、および表制約が必要ない場合は省略できます。また、2つ以上の制約を入れることもできます。

データ型、[列制約]、[表制約] に具体的に何が入れられるかは下で説明しています。

データ型

種類	説明
int	整数
real	浮動小数
char(n)	n文字の固定長文字列
varchar(n)	n文字以下の可変長文字列
date	日付
time	時間

char と varchar の違いに気をつけましょう。

例えば、char(5) であれば必ず「5文字」の文字列が格納される必要があります。一方 varchar(5) であれば「5文字以下」の文字列を格納することができます（5文字でなくてもOK）。

列制約

種類	説明
unique	列内で値の重複を許さない
primary key	主キー扱いにする※
not null	空値(NULL)を許さない
check(条件)	条件に一致する値のみを許す
default X	追加時の初期値をXとする
references 表名(列名)	外部キー扱いにする※

[※注意]

• primary keyを使って主キー制約を行うと、unique制約とnot null 制約も自動的に加わる*1
• 参照先は references 表名(列名) で指定した表、列となる

表制約

表制約では (列名1, 列名2,…) と指定した列名に対して、一括で制約を加えることができます。

種類	説明
unique(列名1,列名2,…)	値の重複を禁止に
primary key (列名1,列名2,…)	主キー扱いに
foreign key(列名1, 列名2) references 表名 (列名1,列名2,…)	外部キー※
check(条件)	表全体に対して条件を設定※

[※注意]

• foreign key(列名1, 列名2) の部分で外部キーを設定し、references 以降で参照先を決定する
• check(条件) では、表全体に対して条件を満たすもののみを登録できるようにする

具体例

具体的に表の定義例を見てみましょう。

表：メニュー

番号	名前	値段
N001	カレー	250
N002	ラーメン	300
N003	日替わり丼	350
N004	Aランチ	400
N005	Bランチ	350

以下の条件を満たすように、表「メニュー」を定義してみましょう。

• 必要な列は番号、メニュー、値段の3つ
• 番号は4文字の文字列型、メニューは10文字以内型、値段は0円以上の整数型
• 列「番号」に主キー制約を書ける

実際に書いて見ると、以下のようになります。皆さんも同じになりましたか？

create table メニュー (
    番号  char(4), primary key,
    名前  varchar(10),
    値段  int, 

    check(値段 >= 0)
)

(2) 表の変更

表の変更 alter は、過去に1～2回ほど基本情報の午後問題で出題されたことがあります。（例えば平成22年度秋期）

列の追加

列の追加と削除は念の為に覚えておくと安心。

alter table 表名 add 列名 データ型

列名　　：追加する列の名前
データ型：追加する列のデータ型

列の削除

alter table 表名 drop 列名

列名　　：削除する列の名前

表名の変更

出ないから覚えなくてOK

alter table 旧表名 renames to 新表名

(3) 表の削除

drop database の database を table に変えただけ。

drop table 削除する表名

３．データの挿入(insert)・削除

(1) データの挿入

insert 文によるデータの挿入です。

insert into 表名 (列名1, 列名2, …)
           values (値1, 値2, …)

「列名1が値1、列名2が値2、…」となっているデータが挿入されます（列名1と値1、列名2と値2、のように同じ番号の列と値が1対1に対応しています。）。

例えば、下のように挿入を行うとします。

このような操作を行うSQLは以下で表せます。

insert into メニュー (番号, 名前, 値段) values ('N006', 'そば', 200)

[注意]

• 挿入対象は必ず1つの表（複数の表に同時挿入はできない）
• 文字列は必ず '' で囲うこと

[応用] select文との併用

データの挿入の際に select 文の結果をそのまま挿入することもできます。

select 文の結果を挿入する場合、values (値1, 値2,…) の部分に select 文が入ります。

insert into 表名 (列1-1, 列1-2, …) select 列2-1, 列2-2, … from 参照表名 where 条件

[注意]

• 列1-1と列1-2、列2-1と列2-2、…のように列の並びが1対1に対応するようにすること
• whereによる条件が不要であれば省略もOK

例えば、右側にある表「一覧」からジャンルが「うどん」であるものを表「メニュー」に追加する場合を考えましょう。

この処理は、以下のSQLで実現できます。

insert into メニュー (番号, 名前, 値段) 
    select 番号, 名前, 値段 from 一覧 where ジャンル = 'うどん'

(2) データの更新

列を指定する部分では、更新する列と値のみを記述すればOKです。

update 表名 set 列1 = 値1, 列2 = 値2, … where 条件

[注意]

• 条件には、select文で使える条件と同じものが使える（between やら in やら like なども使用OK）
• 必要がなければ where 条件 は不要

先程出てきた下の表で具体例を考えてみましょう。

表：メニュー

番号	名前	値段
N001	カレー	250
N002	ラーメン	300
N003	日替わり丼	350
N004	Aランチ	400
N005	Bランチ	350

例えば、ラーメンの値段を20円下げるのであれば以下のSQLで実現できます。

update メニュー set 値段 = 値段 - 20 where 名前 = 'ラーメン'

同じように、全部のメニューを50円値下げするSQLは以下のように書けます。

update メニュー set 値段 = 値段 - 50

(3) データの削除

データの削除方法も確認しておきましょう。

delete from 表名 where 条件

例えば、先程の表「メニュー」から、Bランチを削除するのであれば以下のSQLで記述できます。

delete from メニュー where 名前 = 'Bランチ'

４．権限の変更

基本情報ではあまり重要ではないので、基本情報で受験するだけであれば読み飛ばしてもらってOKです。

権限名には、以下の5つを選ぶことができます。

名前	説明
select	検索
update	更新
insert	挿入
delete	削除
all privileges	上の4つ全部

(1) 権限の付与

grant 権限名 on 表名 to ユーザー名

例：owner には表「メニュー」に対しての全部の権限を付与する

grant all privileges on メニュー to owner

(2) 権限の取り消し

revoke 権限名 on 表名 to ユーザー名

例：momoyama には表「メニュー」に対しての削除の権限を剥奪する

revoke delete on メニュー to momoyama

５．さいごに

今回は、少しシンプルにSQLの中編として、以下の4つについて説明しました。

• データベースの作成、削除
• 表の定義, 構造変更, 削除
• データの挿入, 削除
• データベースの権限

後編では、さらに応用として、以下の3つを中心に説明する予定です。

• 複数の表を用いた select 文
• 副問合せ
• 仮想的な表「ビュー」の定義、利用

*1:主キーになるためには、列内で重複する値がなく、さらに値が空でない必要があるため。