100日後にエンジニアになるキミ - 38日目 - データベース - SQLの基礎1

本日はSQL文についてです。

昨日のはこちら

100日後にエンジニアになるキミ - 37日目 - データベース - SQLの環境構築

SQLについて

先日の復習になりますが
SQLはリレーショナルデータベースを操作するための言語になりますが
SQLそのものはプログラム言語ではありません。

ただしプログラム内ではSQLを作成して
データベースにアクセスするのに利用されます。

SQLには沢山の亜種も存在しますが、一応標準規格があるようで
ここで主に取り扱うのは標準SQLの話になります。

SQLは操作用の文を書いてコマンドラインなどで実行することにより
データベースの様々な操作を執り行う事が出来るようになっています。

SQLの文法の種別は主に3つの系統に別れています。
DDLデータ定義言語 (data definition language)
DMLデータ操作言語 (data manipulation language)
DCLデータ制御言語 (data control language)

データ定義言語(DDL)

データベースそのものを定義するために最初に行うもので
主に下記のような文があります。
CREATE データベースオブジェクト（表、インデックス、制約など）の定義
DROP データベースオブジェクトの削除
ALTER データベースオブジェクトの定義変更

データ操作言語(DML)

おそらく一番用いられる文法はDMLで
主に下記のような文があります。

INSERT行データもしくは表データの挿入
UPDATE表を更新
DELETE表から特定行の削除
SELECT表データの検索、結果集合の取り出し

データ制御言語(DCL)

データベースの利用に様々な制限をつけるための文法で
主に下記のような文があります。

GRANT特定のデータベース利用者に特定の作業を行う権限を与える
REVOKE特定のデータベース利用者からすでに与えた権限を剥奪する
SET TRANSACTIONトランザクションモードの設定
BEGINトランザクションの開始
COMMITトランザクションの確定
ROLLBACKトランザクションの取り消し
SAVEPOINT任意にロールバック地点を設定する
LOCK表などの資源を占有する

SQLの書き方

SQL文の書き方としては大文字、小文字
どちらで書いても実行する事ができますが
文法に用いられるものは大文字
カラム名などは小文字
というようにルールを決めて書くと読みやすくなります。

データ定義言語(DDL)について

DDLはデータベースの定義をしたりテーブルの作成、変更、削除
などを行うための文法です。
CREATE,DROP,ALTER文などがあります。
一つ一つを見ていきましょう。

CREATE

CREATE文はデータベースオブジェクト（表、インデックス、制約など）の定義を行います。

書き方：
CREATE TABLE テーブル名(カラム名1, カラム名2, ...);
CREATE TABLE テーブル名(カラム名1 データ型, カラム名2 データ型, ...)
CREATE TABLE テーブル名(カラム名1 データ型 制約, ...)

基本的な書き方としてはCREATE TABLEと書いた後にテーブル名を書きます。
()の中にはテーブルの定義となる、カラム名とそのデータ型、制約を書き
文の最後は;で閉じます。;は文の終わりを意味します。

create table personal(id integer, name text);

create table personal(id integer primary key, name text);

カラム名、データ型、制約などは後ほどALTER文で変更したりすることもできますが
プログラムからSQL文を構築する場合は、いっぺんに全てを設定します。

つけられる制約についてはまた後ほど説明いたします。

if not exists句

CREATE文はすでに同じ名前のテーブルが存在していると失敗してしまいます。
CREATE文を成功させるにはテーブルが存在していない事が条件になるので
テーブルが存在していない時だけテーブルを作成するというif not exists句があります。

書き方：
create table if not exists テーブル名 ( カラム名 データ型・・・

これを使えばテーブルが無ければ新規作成、テーブルが存在する際は何もしない
というテーブル作成の使い分けができます。

create table if not exists personal(id integer, name text);

DROP

DROP文はデータベースオブジェクトの消去を行います。
作ったデータもテーブルごと消してしまうので
テーブルにデータが入っている場合は注意しましょう。

書き方：
DROP TABLE テーブル名;

DROP TABLE personal;

ALTER

ALTER文はデータベースに対して様々な変更を行えます。

テーブル名の変更

書き方：
ALTER TABLE テーブル名 RENAME TO 新しいテーブル名;

早速SQLiteでやってみましょう。まずは新規でテーブルを作成します。
Excute SQLにSQL文を打ち込んで実行してテーブルを作成します。

CREATE TABLE ta20200427(id integer , name text);

テーブル名を変えます。

ALTER TABLE ta20200427 rename to ta20209999;

実行して確認してみるとテーブル名が変わっています。

カラムを追加する

テーブル作成後にカラムを追加する事ができます。

書き方：
ALTER TABLE テーブル名 ADD COLUMN カラム名 データ型;

先ほど変更したテーブルにカラムを追加してみましょう。

ALTER TABLE ta20209999 ADD COLUMN num integer;

カラムは一番最後に追加されます。

カラムの削除

SQLiteではSQL文でカラム削除ができませんが
SQLの構文上ではALTER文でカラム削除ができます。

書き方：
ALTER TABLE テーブル名 DROP COLUMN カラム名;

DB Browserであればカラムの削除ができますので
それで代用する事が可能です。

画面上のDatabase Structureなどからデータベース名を右クリックして
Modify Tableを選択

削除したいカラム名にカーソルを合わせてRemove fieldをクリック

ポップ画面が出るのでYESをクリック

削除後にOKを押して画面を抜けるとカラムが消えています。

カラム名やデータ型、制約の変更

SQLiteではカラム名やデータ型の変更ができない様子です。
他のデータベースではALTER文でカラム名などの変更ができます。

書き方：
ALTER TABLE テーブル名 CHANGE COLUMN 既存のカラム名 新カラム名 型名 制約;

DB Browserであれば変更などができます。

画面上のDatabase Structureなどからデータベース名を右クリックして
Modify Tableを選択

name部分のカラム名をダブルクリックするとカラム名の変更できます。
Typeを選択して貰えばデータ型の変更ができます。
制約は右側のセレクトボックスをクリックして追加変更を行います。

データ型

データベースのカラムにはデータ型が定められており
データ型に従ったデータの格納をしなくてはなりません。

データ型によりデータの計算方法なども変わってきますので
SQLではデータ型はかなり重要です。

SQLiteでは以下のデータ型を設定する事ができます。

データの性質により使い分けが必要です。
例えば、名前や文章などはTEXT型
数値計算をしたければINTEGER型やREAL型,NUMERIC型になります。

他のデータベースでは上記以外のデータ型も存在します。

一般的なデータ型としては
文字列の場合はVARCHAR(文字数)やTEXT型があります。
文字列を扱うデータ型は文字数の制限があるので使い分けが重要です。
大量の文章を扱う際にはVARCHAR型などでは文字数が足りなくなり
格納出来なくなることもあります。

整数型と小数点型は別物なので、小数点計算まで行う場合は
小数点値を取り扱えるデータ型にする必要があります。

整数型の代表であるINT型などでは小数点値の格納は出来ないので
取り扱うデータの性質や、データの計算方法などでデータ型を決める必要があります。

制約

制約とはテーブルやカラムにつけることの出来る条件付けのことです。

通常のデータベースには以下のような制約があります。

SQLiteでの制約の設定方法は
'CREATE'文で制約をつけて作成
DB Browserで制約の変更
という方法があります。

PRIMARY KEY制約

プライマリーキーもしくは主キーと呼び主キーは1つまたは
複数のカラムの組み合わせに対して設定しテーブルの中で1つだけ存在します。

主キーが設定されたカラムでは他のデータと重複する値を取ることはできません。

書き方：
CREATE TABLE テーブル名(カラム名 データ型 PRIMARY KEY, ...);

CREATE TABLE テーブル名(カラム名1, カラム名2, ... ,
PRIMARY KEY(カラム名1, カラム名2, ...));

NOT NUL制約

NOT NULL制約を設定すると、対象のカラムには NULL値を格納することができなくなります。

書き方：
CREATE TABLE テーブル名(カラム名 データ型 NOT NULL, ...);

UNIQUE制約

UNIQUE制約を設定すると、対象のカラムには重複した値を設定することができなくなります。

書き方：
CREATE TABLE テーブル名(カラム名 データ型 UNIQUE, ...);

CREATE TABLE テーブル名(カラム名1, カラム名2, ... ,
UNIQUE(カラム名1, カラム名2, ...));

複数カラムに対しても設定する事ができます。

DEFAULT

DEFAULT制約を設定すると、データを追加する時に
値を省略した場合のデフォルト値を設定することができます。

CREATE TABLE テーブル名(カラム名 データ型 DEFAULT 値, ...);

CREATE TABLE table3(
id integer, 
name text DEFAULT 'no name', 
price integer DEFAULT 0
);

CHECK制約

CHECK制約を設定すると、データを追加する時に
値が指定した条件を満たしているかどうかのチェックをすることができます。

書き方：
CREATE TABLE テーブル名(カラム名 データ型 CHECK(条件式), ...);

CREATE TABLE テーブル名(カラム名1, カラム名2, ... , CHECK(条件式));

CREATE TABLE customer(
id integer, 
name text, 
old integer CHECK(old > 18)
);

カラムが条件に一致しないデータを追加しようとすると
エラーになります。

AUTOINCREMENT

AUTOINCREMENTを設定するとデータを追加した時に
INTEGER PRIMARY KEY を設定したカラムの値を指定しないと自動的に値が格納されます。

自動的に格納される値は、対象のカラムに格納されている最大の値に1を加えた値となります。

CREATE TABLE テーブル名(カラム名 データ型 AUTOINCREMENT, ...);

CREATE TABLE テーブル名(カラム名 INTEGER PRIMARY KEY AUTOINCREMENT, ...);

FOREIGN KEY制約

FOREIGN KEY制約は他のテーブルを参照してそのカラムの値にない値を
格納出来なくする事ができます。

CREATE TABLE テーブル名( カラム名 データ型 ,
FOREIGN KEY(カラム名) references 外部のテーブル名(外部のテーブルのカラム))

制約の変更

DB Browserでは各種制約の変更ができます。

画面上のDatabase Structureなどからデータベース名を右クリックして
Modify Tableを選択

各種制約のチェックをつけるか、ダブルクリックして設定を行います。

まとめ

データベースの作成方法やデータ型
基本的な制約について押さえておこう。

君がエンジニアになるまであと62日

作者の情報

乙pyのHP：
http://www.otupy.net/

Youtube：
https://www.youtube.com/channel/UCaT7xpeq8n1G_HcJKKSOXMw

Twitter：
https://twitter.com/otupython