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

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

昨日のはこちら

[100日後にエンジニアになるキミ - 37日目 - データベース - SQLの環境構築]
(https://qiita.com/otupy/items/a0896107989805abc71d)


## 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`と書いた後にテーブル名を書きます。
`()`の中にはテーブルの定義となる、カラム名とそのデータ型、制約を書き
文の最後は`;`で閉じます。`;`は文の終わりを意味します。

```sql
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 テーブル名 ( カラム名 データ型・・・`

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

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

**DROP**

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

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

```sql
DROP TABLE personal;
```


**ALTER**

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

***テーブル名の変更***

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

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

```sql
CREATE TABLE ta20200427(id integer , name text);
```

<img width="475" alt="スクリーンショット 2020-04-27 18.06.06.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/5b194250-0f16-58cc-3993-1718b84b77e6.png">

<img width="555" alt="スクリーンショット 2020-04-27 18.06.19.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/781fcd53-9c9a-f00e-e30f-5c7cd905d856.png">

テーブル名を変えます。

```sql
ALTER TABLE ta20200427 rename to ta20209999;
```

実行して確認してみるとテーブル名が変わっています。
<img width="545" alt="スクリーンショット 2020-04-27 18.08.02.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/c835bad5-8f30-39dd-3e5e-1a52f8207aa9.png">

***カラムを追加する***

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

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

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

```sql
ALTER TABLE ta20209999 ADD COLUMN num integer;
```

カラムは一番最後に追加されます。
<img width="497" alt="スクリーンショット 2020-04-27 18.13.45.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/7a37598a-4db8-88e8-a937-e5ce7f880084.png">


***カラムの削除***

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

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

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

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

<img width="392" alt="スクリーンショット 2020-04-27 18.18.39.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/dddc3bd3-4bb8-192a-36e5-eebca2693e00.png">

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

<img width="450" alt="スクリーンショット 2020-04-27 18.20.57.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/8846bc17-47db-2c19-2772-6df3053f366c.png">

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

<img width="408" alt="スクリーンショット 2020-04-27 18.21.57.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/ad2b29ac-bac5-545d-c24f-28b26c7b5aff.png">

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

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

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

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

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

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

<img width="504" alt="スクリーンショット 2020-04-27 18.31.29.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/aa21350d-48b1-d432-52ea-33d763f119b0.png">

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

## データ型

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

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

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

|データ型|説明|
|:--:|:--|
|NULL|NULL値|
|INTEGER|符号付整数値。1, 2, 3, 4, 6, or 8 バイトで格納|
|NUMERIC|可変長数値|
|REAL|浮動小数点数値。8バイトで格納
|TEXT|テキスト値。UTF-8, UTF-16BE or UTF-16-LEのいずれかで格納|
|BLOB|Binary Large OBject。入力データをそのまま格納|

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

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

| 一般的なSQLの型 | SQLiteでの型 |
|:--|:--|
| INT<br/>INTEGER<br/>TINYINT<br/>SMALLINT<br/>MEDIUMINT<br/>BIGINT<br/>UNSIGNED BIG INT<br/>INT2<br/>INT8 | INTEGER |
| CHARACTER(20)<br/>VARCHAR(255)<br/>VARYING CHARACTER(255)<br/>NCHAR(55)<br/>NATIVE CHARACTER(70)<br/>NVARCHAR(100)<br/>TEXT<br/>CLOB | TEXT |
| BLOB<br/>[データ型の指定なし] | NONE |
| REAL<br/>DOUBLE<br/>DOUBLE PRECISION<br/>FLOAT | REAL |
| NUMERIC<br/>DECIMAL(10,5)<br/>BOOLEAN<br/>DATE<br/>DATETIME | NUMERIC |

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

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

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



## 制約

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

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

| 制約 | 内容 |
|:--|:--|
| PRIMARY KEY制約 | 重複とNULLを禁止して一意を保証する |
| NOT NULL制約 | NULL値を禁止する |
| UNIQUE制約 | 重複したデータを禁止する |
| DEFAULT制約 | デフォルト値を設定する |
| CHECK制約 | 条件を指定し、条件を満たさないデータを禁止する |
| AUTOINCREMENT | 自動的に値が割り当てられる |
| FOREIGN KEY制約 | 他のテーブルの列を参照し、その列にないデータを禁止する |

`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 値, ...);`

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

**CHECK制約**

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

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

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

```sql
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`を選択

<img width="751" alt="スクリーンショット 2020-04-27 19.15.36.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/379626/96a552ca-c3b9-fdf5-f9b4-9ebd3d896c08.png">

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



## まとめ

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


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

## 作者の情報
乙pyのHP：
http://www.otupy.net/

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

Twitter：
https://twitter.com/otupython