DBにアクセスする言語、SQLの基礎の説明です。
テーブル操作などの基本構文をまとめております。
代表的なSQLであるMySQLやPostgreSQLを参考に基本をご紹介します。
「RDBMSとは?」「DBとは?」という方は下の記事をご覧ください。
WHERE句などのSQL文や、DML・DDL/DCL・TCLの詳細は別記事に掲載します。
SQLとは
RDBMSでデータの管理やクエリの実行に使用される特定のクエリ言語の一つです。
カンタンに言えば、SQLはDBを操作する為の言語ともいいます。
DBに蓄積されたデータを人間が操作する為に必要です。
正式名称を『Structured Query Language (構造化問い合わせ言語)』といいます。
SQLを使用すれば上記画像のように、RDBにある表形態のデータを操作できます。
クエリ言語について
DBや情報システムに対して情報を要求するための言語を指します。
クエリ言語はDBだけではなく、様々な情報システムなどに使用される言語の総称です。
クエリ言語とSQLの違いは？
・クエリ言語:データの参照、追加、変更、削除を行う言語の総称(SQL、APIクエリ言語など)
・SQL: クエリ言語の一種。様々な種類がある(MySQL、PostgreSQLなど)
SQLについて
SQLは下記の4種類に分かれます。
1. DML
DBのテーブル内データを操作するSQL言語です。
正式名称は『Data Manipulation Language』といいます。
SQLで一般的に使用されるのはDMLです。
テーブルの追加、参照、更新、削除といった基本のデータ操作を行います。
2. DDL
DBのオブジェクトを定義・変更するSQL言語です。
正式名称は『Data Definition Language』です。
DBやテーブルの構造を定義・変更するために使用します。
新規テーブルの追加や、DBの設定変更などですね。
3. DCL
DBへのアクセス権限とセキュリティを制御するSQL言語です。
正式名称は『Data Control Language』です。
4. TCL
トランザクションの制御を行うSQL言語です。
トランザクションの開始・終了、トランザクションのコミット・ロールバックなどを行います。
正式名称は『Transaction Control Language』です。
コミット
トランザクションによる変更を確定する
ロールバック
トランザクションを取り消す
構文の前に知っておきたいポイント
SQL構文をご紹介する前に、テーブルに格納するデータに『データ型』、『データ属性』に気を付ける必要があります。
データ型について
データ型を具体的にいうと『整数型』、『文字列型』、『日付と時刻型』などです。
DBにデータを格納する際には、データ型の指定が必須となります。
データ型を決めていないと、エラー発生、データを適切に保管できないなどの問題が起こるのでご注意を。
多数のデータ型がありますが、作成するデータの大きさによって選択しましょう。
それぞれメリットデメリットがあります。
内容量が小さいデータ型であれば、DBの処理速度も上がりますが保存できる内容に制限がかかります。
反対に、内容量が大きい・制度が高いデータ型であればDBの処理速度が遅くなります。
一般的なデータ型
SQLによって仕様が違います。下記の整数型の範囲はあくまで汎用例です
ご使用のSQLをご確認ください
- 整数型
整数を表現するための型。内容例としては『123456』、『-11』など
・INT→一般的に使用される整数型。-2,147,483,648 ～ 2,147,483,647が範囲
・SMALLINT→-32,768 ～ 32,767が範囲
・BIGINT→-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807が範囲 - 浮動小数点型
実数や浮動小数点数を表現するための型。内容例としては『1.40129846432481707e-45』など
・FLOAT→科学技術計算や広範な数値データ保存に使用
・DOUBLE→FLOATよりも値を精密に扱いたい場合に使用
・DECIMAL→DOUBLEよりも厳密な値の比較や計算をしたい場合に使用 - 文字列型
日本語や英語などの文字を扱うための型。内容例としては『’田中’』、『’Show’』など。一般的にデータ格納する際は『 ‘ (シングルクォーテーション)』を使用
・VARCHAR→『’こんにちは’』などの可変長の文字列型を扱う場合に使用
・CHAR→固定長の文字列を格納
・TEXT→非常に大きい文字列型を扱う。文字数に制限がない場合が多い - 日付と時刻型
日付や時刻を表現するための型。内容例としては『2023/12/15』など。一般的にデータ格納する際は『 ‘ (シングルクォーテーション)』を使用
・DATE→年・月・日を扱う
・TIME→時・分・秒を扱う
・DATETIME→年・月・日・時・分・秒を扱う
・TIMESTAMP→DATETIMEをより精度向上化して使用する場合に扱う - 真偽値型(ブーリアン型)
TrueまたはFalse真偽値を表現するための型。内容例としては『True』または『False』
・BOOLEAN→真、または偽の値
データの属性
DBのテーブルおいて、そのデータが持つ特性や情報を表現するためのモノです。
各列がデータの属性ともいいます。
属性はデータが持つ特定の情報や特性を表現します。
具体的には『学生の個人情報』データであれば、『学籍番号』、『氏名』、『性別』、『住所』などです。
属性は別名を『アトリビュート』といいます。
データ属性の概念
- 主キー
テーブル内で一意の値を持つ。重複は許可されないため、テーブル内の行を一意に識別できる - 外部キー
別テーブルの主キーと関連付けられる - データ型
属性が何型なのかを所持 - NULLの許容
NULL(空)の値を許容する、もしくは許容しないかどうか - デフォルト値
新規に行が追加された場合で、かつ値が使用されなかった時に適応されるデフォルトの値
なお、デフォルト値を設定しない場合は『NULL』がデフォルト値になる - インデックス
データベースの検索性能を向上させるために使用。適切に設定できないと処理速度低下につながる。最初は気にしないでOK - 制約
データの整合性や一貫性を保つために設定される条件。主キー制約や外部キー制約、NULLの許容などが当てはまる
データの制約
データ属性の概念で説明した通り、データの整合性や一貫性を保つために設定される条件を指します。
制約の具体例
- 主キー制約
『テーブル内の特定の列、または列の組み合わせが一意』、かつ『値がNULLでないこと』を設定 - 外部キー制約
『あるテーブル内の特定の列、または列の組み合わせ』が他テーブルの主キーに関連付けする制約 - ユニークキー制約
『指定された列、または列の組み合わせが一意』である制約
主キー制約との相違点は、NULLが複数存在しても許容される点 - デフォルト制約
特定の列に値が指定されなかった場合に使用される、デフォルトの値を定義 - 非NULL制約
特定の列にNULL値を許容させない
DMLの構文
| fruit_ID | fruit_name | fruit_stock |
|---|---|---|
| 2020020101 | バナナ | 200 |
| 2020020102 | りんご | 300 |
| 2020020103 | ドラゴンフルーツ | 55 |
| 2020020104 | みかん | 1 |
DBのテーブル内にあるデータを直接操作するSQL言語です。
上記のサンプルテーブル『fruit_table』を使用してご説明します。
実際には、自らご使用のDBテーブル名を適応してください。
それではテーブルのデータ追加、更新、削除、変更について紹介します。
INSERT -追加-
INSERT INTO テーブル名 (列名1, 列名2, 列名3, ...) VALUES (値1, 値2, 値3, ...);『INSERT』句という、行を新しく追加するSQL文を使用します。
INSERTの省略
・INSERT句では列名を省略できる
INSERT INTO テーブル名 VALUES (値1, 値2, 値3, …);
・特定の列にだけ値を追加も可能
INSERT INTO テーブル名 ( 列名2, 列名3) VALUES (列名2の値, 列名3の値);
具体例
INSER TINTO fruit_table (fruit_ID, fruit_name, fruit_stock)
VALUES ('2020020105', 'パイナップル', 100);得られる結果
上記にある『fruit_table』のテーブルに、下記の行が追加される
・2020020105 パイナップル 100
SELECT -参照-
SELECT 取得したい項目1, 取得したい項目2, ... FROM テーブル名;『SELECT』句という、条件に合うデータを参照・表示するSQL文です。
『取得したい項目』の中には列や関数を指定できます。
関数については別記事でご紹介します。
具体例
/** 指定列のデータを取得 */
SELECT fruit_ID, fruit_name, fruit_stock FROM fruit_table;
/** fruit_tableの全データを参照する */
SELECT * FROM fruit_table;得られる結果
上記にある『fruit_table』のテーブル内容が表示される
『WHERE』句と呼ばれる条件文と組み合わせて、表示する条件の指定ができます
取得したい項目を『 * (アスタリスク)』にすると、指定したテーブル、条件に該当するデータを全て取得します。
ただし練習や内容確認での使用は大丈夫ですが、システムに実装するのお勧めできません。
ビューについて
DBに格納されている、実態のあるデータをテーブルと呼びます。
SELECTで表示されるデータは、参照のために仮作成されたモノであり仮想的なテーブルです。
この仮想的なテーブルの別名を『ビュー』といいます。
UPDATE -更新-
UPDATE テーブル名 SET 列名1 = 値1, 列名2 = 値2 ... WHERE 条件 ;『UPDATE』句という、既存のデータを更新するSQL文です。
基本的に『WHERE』句と呼ばれる条件指定文と併せて使用します。
WHERE句については、当記事に後述しています。
条件をつけないと、関係ないデータも更新されるのでご注意を。
具体例
UPDATE fruit_table SET fruit_stock= 300 WHERE fruit_name LIKE 'バナナ';得られる結果
『fruit_table』のバナナの在庫数が300に更新。
/** 実行前 */
・2020020101 バナナ 200
/** 実行後 */
・2020020101 バナナ 300
DELETE -削除-
DELETE FROM テーブル名 WHERE 条件;『DELETE』句という、行を削除するSQL文です。
こちらもUPDATE句同様に、条件を指定しないと関係ないデータも削除されます。
具体例
DELETE FROM fruit_table WHERE fruit_name LIKE 'りんご'; 得られる結果
『fruit_table』から りんご の行が削除
DDLの構文
| fruit_sale_id | fruit_price | fruit_made_in_japan | fruit_catch_phrase |
|---|---|---|---|
| 最高バナナ_01 | 200 | false | 熟して美味しい最高のバナナ |
| ツヤツヤリンゴ_01 | 350 | true | ツヤツヤした甘酸っぱいりんご |
DBのオブジェクトを定義・変更するSQL言語です。
仮定としてDB『fruit_db』、上記テーブル『fruit_sale_table』を作成しています。
実際には、ご自分の使用されているDB名に置き換えてください。
CREATE TABLE -テーブル作成-
CREATE TABLE テーブル名 (
列名 データ型 任意で制約,
列名 データ型 任意で制約,
...
列名 データ型 任意で制約
) ;『CREATE TABLE』という、テーブルを新規作成するSQL文です。
テーブルを新規に作成する際は、当記事で前述したデータ属性・制約を意識しましょう。
具体例
CREATE TABLE fruit_sale_table (
fruit_id VARCHAR(25) PRIMARY KEY,
fruit_price INT NOT NULL,
fruit_made_in_japan BOOLEAN DEFAULT false,
fruit_catch_phrase TEXT
);得られる結果
『fruit_sale_table』が作成される
・列名『fruit_id』、VARCHAR型で25文字以内、主キー制約
・列名『fruit_price』、INT型、非NULL制約
・列名『fruit_made_in_japan』、BOOLEAN型、デフォルト値は ‘false’
・列名『fruit_catch_phrase』、TEXT型
制約の構文
制約を付与する場合、『CONSTRAINT』という1文を追加します。
追加しなくても大抵のSQLであれば、自動的に追加されます。
テーブル作成の詳細は別記事でご説明します。
ALTER TABLE -テーブルの変更-
/** 列の追加 */
ALTER TABLE テーブル名
ADD COLUMN 追加する列名 データ型;
/** 列の削除 */
ALTER TABLE テーブル名
DROP COLUMN 削除する列名;
/** 列のデータ型を変更 */
ALTER TABLE テーブル名
ALTER COLUMN 変更前のデータ型 変更後のデータ型;
/** 列のデフォルト値を変更 */
ALTER TABLE テーブル名
ALTER COLUMN 列名 SET DEFAULT データ型;
/** テーブルの名前を変更 */
ALTER TABLE 変更前のテーブル名
RENAME TO 変更後のテーブル名;『ALTER TABLE』という、既存のテーブルを変更するSQL文です。
列、テーブル、列のデフォルト値、列の追加・削除など様々な構造変更に対応しています。
具体例
/** 列名の変更 */
ALTER TABLE fruit_sale_table
RENAME COLUMN fruit_made_in_japan TO fruit_made_in_canada;得られる結果
『fruit_sale_table』の列名が変更される
・列『fruit_made_in_japan』→列『fruit_made_in_canada』
DROP TABLE -テーブルの削除-
DROP TABLE テーブル名;『DROP TABLE』という、既存のテーブルを削除するSQL文です。
具体例
DROP TABLE fruit_sale_table;得られる結果
『fruit_sale_table』が削除される
CREATE INDEX -インデックスの作成-
CREATE INDEX インデックス名
ON テーブル名 (列名1, 列名2, ...);『CREATE INDEX』という、既存テーブルに対してインデックスを付与するSQL文です。
具体例
CREATE INDEX idx_fruit
ON fruit_sale_table (fruit_sale_id);得られる結果
『fruit_sale_table』の列『fruit_sale_id』にインデックス『idx_fruit』が付与される
DROP INDEX -インデックスの削除-
DROP INDEX インデックス名;『DROP INDEX』という、インデックスを削除するSQL文です。
具体例
DROP INDEX idx_fruit;得られる結果
インデックス『idx_fruit』が削除される
DCLの構文
DBへのアクセス権限とセキュリティを制御するSQL言語です。
GRANT -ユーザへの権限付与-
GRANT 与える権限 ON オブジェクト TO 権限を付与したいユーザ名;『GRANT』という、ユーザへ権限を付与するSQL文です。
権限を設定することで、ユーザ毎に『テーブルの変更禁止』、『テーブルの追加禁止』などを制限できます。
セキュリティ向上には必須です。
オブジェクトは『テーブル』、『ビュー』、『インデックス』などを指します。
また、権限を付与したいユーザ名には『ロール』というユーザのグループにも指定可能です。
与える権限に『ALL』を記述すると、全ての権限を付与可能
具体例
GRANT SELECT ON fruit_table TO osakana_san;得られる結果
ユーザ『osakana_san』は『fruit_table』にSELECT(参照)できる権限が付与された
REVOKE -ユーザ権限の削除-
REVOKE 削除する権限 ON オブジェクト FROM ユーザ名、またはロール名;『REVOKE』という、ユーザの権限を削除するSQL文です。
具体例
REVOKE SELECT ON fruit_table TO osakana_san;得られる結果
ユーザ『osakana_san』が『fruit_table』にSELECT(参照)できる権限を削除
DENY –ユーザの特定権限を拒否-
DENY 拒否する権限 ON オブジェクト FROM ユーザ名、またはロール名;『DENY』という、ユーザの特定権限を拒否するSQL文です。
GRANTは権利を削除・剥奪します。その後権限を付与すれば、権利を取り戻せます。
ですが、DENYはそもそも特定の権限を拒否するので権利は与えられません。
具体例
DENY UPDATE ON fruit_table TO osakana_san;得られる結果
ユーザ『osakana_san』が『fruit_table』にUPDATE(更新)する権限を拒否
TCLの構文
トランザクションの制御を行うSQL言語です。
BEGIN TRANSACTION -トランザクションの開始-
BEGIN TRANSACTION;『BEGIN TRANSACTION』というトランザクションを開始するSQL文です。
『START TRANSACTION』の場合もあります。
後述するSQLと組み合わせてトランザクションを行います。
COMMIT -コミット-
COMMIT;『COMMIT』という、トランザクションによる変更をDBに確定するSQL文です。
具体例
BEGIN TRANSACTION;
-- トランザクションの内容
COMMIT;ROLLBACK -ロールバック-
ROLLBACK;『ROLLBACK』という、トランザクションの変更を取り消し、中断するSQL文です。
トランザクション中にエラー発生、変更を取り消す場合に使用します。
具体例
BEGIN TRANSACTION;
-- トランザクションの内容
ROLLBACK;SAVEPOINT -セーブポイントの設定-
SAVEPOINT セーブポイント名;『SAVEPOINT』という、トランザクション内でのセーブポイントを設定するSQL文です。
後述する『ROLLBACK TO SAVEPOINT』や『RELEASE SAVEPOIN』と併せて使用します。
ROLLBACK TO SAVEPOINT -セーブポイントまで変更取消-
ROLLBACK TO セーブポイント名;『ROLLBACK TO SAVEPOINT』という、設定したセーブポイントまで変更を取消するSQL文です。
具体例
BEGIN TRANSACTION;
-- トランザクションの内容01
SAVEPOINT sample_savepoint;
-- トランザクションの内容02
ROLLBACK TO sample_savepoint;得られる結果
『トランザクションの内容02』の内容はすべて取り消す
『トランザクションの内容01』までを保存した『sample_savepoint』まで戻る
RELEASE SAVEPOINT -セーブポイント削除・変更を確定-
RELEASE SAVEPOINT セーブポイント名;『RELEASE SAVEPOINT』という、セーブポイントの削除・変更を確定するSQL文です。
具体例
BEGIN TRANSACTION;
-- トランザクションの内容01
SAVEPOINT sample_savepoint; -- 旧セーブポイント
-- トランザクションの内容02
RELEASE SAVEPOINT sample_savepoint;得られる結果
『トランザクションの内容01』と『トランザクションの内容02』が保存される
『旧セーブポイント』は削除される
特殊・備考
句は大文字のみ?
SELECTやUPDATEなど沢山の句がありますが、全て大文字・小文字のどちらでも構いません。
読みやすいという理由から大文字であるケースが多いです。
ただし、テーブル名や列や行の名称は元のデータに合わせることを推奨します。
DBやテーブルが複数ある場合
テーブル名やデータ名の直前に『所属しているDB、もしくはテーブル名』と『 . (ドット)』を付与します。
・テーブル名を指定するのであれば、『DB名.テーブル名』
・データ名を指定する場合は、『テーブル名.データ名』
更にSQLによっては処理速度や可読性も向上するので、積極的な使用をおすすめします
列、テーブル名の省略 -AS-
参照したい列名やテーブル名が長い場合、結果・クエリ内で別名を付与できます。
所略したい列名、もしくはテーブル名の後に『AS 別名』を記述するだけです
SELECT long_long_loooooooooong_column AS long_column
FROM hoooooooooooooooo_long_table AS long_table;上記の場合
・実際の列名→『long_long_loooooooooong_column』、テーブル名→『hoooooooooooooooo_long_table』
・ビュー (仮想的なテーブル)
列名→『long_column』、テーブル名→『long_table』
; (セミコロン)
SQL文の終端にある『;』は命令文の終わり、つまりSQLがそこで終了という意味を持ちます。
『;』がないとエラーとなるSQLが多いです。
* (アスタリスク)
『SELECT * FROM table_name ;』のように、『*』を使用すると条件に合った行をすべて指定できます。
ただし、処理速度が遅くなる・何のデータを取ったのかが理解し辛いなどのデメリットがあるのでおすすめしません。
おさかなさんテーブルの項目を増やす可能性があるじゃない
そっちの方が楽だよ
海道おやめください
SQLで、後からテーブルを追加するのは好ましくありません
海道後々、データの整合性で問題になる可能性があります
新規テーブル追加など、DB設計から見直しましょう
ワイルドカード
ワイルドカードとは、検索・フィルタリングに使用する特殊な文字または文字列です。
例として、生徒情報のテーブルから苗字が『田中』の行だけ探すとします。
その場合はLIKE演算子と組み合わせてワイルドカードを使用します。
SELECT * FROM students_table WHERE
student_name LIKE '田中%' ;上記の場合、結果として『田中たかし』、『田中ゆりこ』、『田中りょうこ』などの行を取得できます。
主なワイルドカード
- _ (アンダースコア)
アンダースコア1個につき、1文字を任意にする
・『_ar』であれば、『car』、『ear』、『var』などが該当 - % (パーセント)
パーセント1個につき、%以降の文字列を任意にする
・『ま%』であれば、『まり』、『まつもと』、『まじま』などが該当 - [] (ブラケット)
ブラケット内に含まれる、文字のいずれかであれば任意
・『あ[かさた]り』であれば、『あかり』、『あさり』、『あたり』が該当
ワイルドカードを記号として検索
ワイルドカードではなく記号を検索する場合は、エスケープ文字と呼ばれる記号を使用します。
大半のSQLでは『 \ (バックスラッシュ)』です。
・ワイルドカードのエスケープ
下記のSQLだと『田中%』というデータを取得します。
SELECT * FROM students_table WHERE
student_name LIKE '田中\%' ;・エスケープ文字のエスケープ
下記のSQLだと『田中/%』というデータを取得します。
エスケープ文字を検索対象にしたい場合に使用できます。
SELECT * FROM students_table WHERE
student_name LIKE '田中\\%' ;SQLによってエスケープ文字は異なるので、ご自身のSQLドキュメントをご確認ください
最後に
SQLはDBを扱う上で必須の言語です。
業務でも昔のコードが意味もなく*を使用しているケースに出くわします。
分かりづらい、遅い、不要なデータを取得するSQL。
これではいけませんね。
かといって修正するわけにもいかず、忌み嫌われているため知識って大事だと思います。
文の構成次第で処理のスピードも変わるので、みなさまもぜひお試しあれ。
コメント