こんにちは、ももやまです。
今回は、SQLの中編ということで、
- データベースの作成、削除
- 表の定義, 構造変更, 削除
- データの挿入, 削除
- データベースの権限
の4つについて説明します。
1.データベースの作成・削除
特に意識して覚える必要はなし。
(1) データベースの作成
create database 作成するデータベース名
(2) データベースの削除
drop database 削除するデータベース名
create
が作成、drop
が削除というのは頭に入れておきましょう。
本番、SQLの構文をど忘れしたときでも選択肢を絞ることができます。
2.表の定義, 構造変更, 削除
(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 削除する表名
3.データの挿入(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ランチ'
4.権限の変更
基本情報ではあまり重要ではないので、基本情報で受験するだけであれば読み飛ばしてもらって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
5.さいごに
今回は、少しシンプルにSQLの中編として、以下の4つについて説明しました。
- データベースの作成、削除
- 表の定義, 構造変更, 削除
- データの挿入, 削除
- データベースの権限
後編では、さらに応用として、以下の3つを中心に説明する予定です。
- 複数の表を用いた
select
文 - 副問合せ
- 仮想的な表「ビュー」の定義、利用
*1:主キーになるためには、列内で重複する値がなく、さらに値が空でない必要があるため。