diadia

興味があることをやってみる。自分のメモを残しておきます。

SQLの学習

SQL

参考書：
スッキリわかるSQL入門第２版
SQL ゼロから始めるデータベース操作

SQLの命令分類

  • DML(Data MAnipulation Language)
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
    • EXPLAIN
    • LOCK TABLE
  • DDL(Data Definition Language)
    • CREATE
    • ALTER
    • DROP
    • TRUNCATE
  • TCL(Transaction Control Language)
    • COMMIT
    • ROLLBACK
    • SET TRANSACTION
    • SAVEPOINT
  • DCL(Data Control Language)
    • GRANT
    • REVOKE

 

メモ

INSERT文で具体的な値を入れないと、NULLがデータベースに入力される。NULLではない値を入力したい場合にはCREATE TABLEでdefaultを設定すれば良い。

WHERE句でIS NULL, IS NOT NULLを使う。WHERE句ではLIKE演算子やBETWEEN演算子もある。IN,　NOT IN演算子もある。

主キーとなるカラムが持つべき特性
必ず何らかのデータが格納される（NULLではない）。
値が他の行と重複しない。

操作する行の絞り込み

行の絞り込みでIS NULLやIS NOT NULLを使うのでWHERE句で使うと仮定しておく。

検索結果を加工

  • DISTINCT
  • ORDER BY
  • OFFSET/FETCH
  • UNION
  • EXCEPT
  • INTERSECT

式と関数

 

式

関数

  • 文字列に係る関数
    • LENGTH
    • TRIM
    • REPLACE
    • SUBSTRING
    • CONCAT
  • 数値に係る関数
    • ROUND(四捨五入)
    • TRUNC(四捨五入の切り捨て)
    • POWER(べき乗を計算する)
  • 日付に係る関数
    • CURRENT_DATE(現在の日付を得る関数)
    • CURRENT_TIME(現在の時刻を得る関数)
  • 変換に係る関数
    • CAST(データ型を変換する)
    • COALESCE(最初に登場するNULLでない値を返す)
関数の補足

SUBSTRINGについて

SUBSTRINGは文字列からある位置から定められた文字数を抽出する関数である。pythnで言えば文字列のスライスであり、mojiretsu[5:8]みたいなことを実現する関数である。使い方は以下のように、

SUBSTRING(カラム名, 文字列抽出の開始位置, 開始位置から文字列取得数)

SUBSTRING(文字列, 6, 4)

疑問がある。プログラミング言語の場合文字列の開始時点は0から始まるけどSQLの場合は1から始まるのか？また-1とすることもできないのか？

 

TRIMについて

TRIMは文字列の空白に対して作用するものだけれども、それは文字列の始まる前、または終わりにある空白に対して作用する。したがって文字列中の空白を削除したい場合には、REPLACE関数を使うことになる。

 

集計とグループ化

集計の関数は以下の通り。集計の関数はSELECT文でしか利用できない。さらに言えば、SELECT文の選択列リストか、ORDER　BY句、HAVING句のみ記述することができる。

  • SUM
  • MAX
  • MIN
  • AVG
  • COUNT

集計関数を使うと集計結果として一行に集約される。例えば項目別にSUMやMAXの結果を得たいという動機にたいしてグループ化で対応できる。あるカラムの項目別に集計関数を適用する方法だ。これはGROUP　BY句を使えば実現できる。また集計結果に対し、フィルタリングしたい場合もある。それに対してはWHERE句と非常に似たHAVING句 を用いれば良い。両者の違いは集計関数が実行されるタイミングが異なるだけである。

テーブルを分割することについて

テーブルを分割すると、データを安全、確実、高速に取り扱うことができる。これはつまり、データの管理に適することを意味する。しかしながらテーブルを分割すると、データの一覧性が損なわれ理解しづらいデメリットがある。このデメリットを克服する技術がjoinである。

トランザクション

DBMSによるトランザクション制御とは具体的に、

を意味する。

DBMSにはトランザクションの制御を行うために、内部でロック(lock)と呼ばれる仕組みがある。ロックっていう仕組みは、あるトランザクションが現在読み書きしている行に鍵をかけ、他の人のトランザクションからは読み書きできなくする仕組み。このロックはトランザクションがコミットやロールバックで終了されると解除される。そして他人がロックしている間は、他人のトランザクションが終了するまでは自分は待たされる。通常このロックの待ち時間は通常数ミリ秒以下らしい。pythonのsqlite3を使って複数のスクリプトを同時に走らせると、lockと書かれたエラーが出るのはこれが原因なのだろう。

分離レベル

DBMSにはどの程度厳密にトランザクションを分離するかをトランザクション分離レベルとして指定することができる。これは各DBMSで固有のレベルが定まっているという意味ではなく、ユーザが分離レベルを指定することができるという意味である。多くのDBMSではデフォルトでREAD COMMITTEDになっている。他の分離レベルに設定するには、
SET TRANSACTION ISOLATION LEVEL 分離レベル を使って変更することができるようだ。

GRANT文とREVOKE文

DCLは、誰に、どのようなデータ操作やテーブル操作を許す家といった権限を設定するSQLの総称。権限を付与するGRANTと権限をなくすREVOKEが存在する。

GRANT 権限名 TO ユーザ名
REVOKE 権限名 FROM ユーザ名