日常的にDB触ってないとすぐ忘れてしまう件

去年の暮れか今年の初めに、DBについて一通り勉強はした。が、もう忘れてしまった。ので、もう一度勉強するか―って感じでございます。 勉強するのに使う教材は以下のyoutubeである。前回もこれを使って学んだが、今回もこれを使いたいと思う。注意点としては、このlectureは データベース理論の説明に終始していて、具体的なSQLコマンドについてはまったく触れていない点。しかし、理論についてはめちゃめちゃわかりやすく説明してくれていたと記憶している。今回は講義の内容を、この記事にまとめていきたいと思う。

データベースの基本

データベースとは

データの集合

relational database とは

データを2次元の表で管理するデータベースのこと。 エンティティとは、データを保持する実体。属性(attirbute) とは、エンティティが持つ実際のデータ。 例えば、「田中」というエンティティがあるとする。この田中は、年齢：24歳、名前：田中、性別：男、である。この時、「田中」というエンティティは24歳、田中、男、という属性を持つ。Relational Databaseでは、「属性」を列にして、各行を一つのエンティティにしてデータを保存する。また、エンティティは具体的な実体だが、それを抽象化した型のことをエンティティタイプという。同じく、属性もあるエンティティに対する具体的なデータだったが、これを抽象化した肩を属性タイプという。つまり、「田中」というエンティティは「人間」というエンティティタイプに属し、「人間」というエンティティタイプには「年齢」、「名前」、「性別」の属性タイプがある。 そして、relatinal databaseでは、「エンティティタイプ」をテーブルの名前に、「属性タイプ」をカラムにして管理する。 が、実際は、Entityのことをテーブルと呼ぶ。Entity Relation図も同じだよね。

DBMSとは

データベースを管理するソフトウェアのこと(e.g., Mysql, Mariadb)。 データベースの操作には、queryというものを発行して、データベースに仕事を依頼する。 DBMSによってデータの扱いが非常にらくになる。例えば、ユーザテーブルで、2年以上ログインがないユーザを削除する場合、DBMSを使えば、queryによって簡単に変更を加えることが可能となる。 まあ、実際にはデータベースとDBMSの違いを意識する人は専門家くらいで、使う側はほぼ同じものだと思ってもいいよ。

SQLとは

SQLとはデータベースとやり取りをするときに使われる言語のこと。特定のDBMSに使われる言語の事ではなく、もっと一般的な概念である。SQLでやることは大きく分けて二つである。

  1. データベースの定義 database definition language (ddl)
  2. データベースの操作 datatabase manipulation language (dml) JOINがすごく大事なSQLである。なぜなら、DBMSではデータを様々なテーブルに分けて管理し、分散されたデータを一つにまとめるときにJOINを使うからである。

naming convention(名前の付け方)

SQLは大文字で書く。e.g,SELECT, JOIN, INSERT, データ自体は、小文字で書く。　user_id

データベース設計

データ整合性について

エンティティ整合性：エンティティがユニークか？テーブルのエンティティを一意に特定することができるか？ referential integrity: テーブル間の参照に整合性がとれているか？ domain integrity: フィールドに入るデータの型の整合性がとれているか？e.g., 「名前」カラムに年齢が入っていないか？ テーブル間のつながりのことをリレーションという。 よくER図を使ってデータベースを作成する。ERはEntity Relationの表す。

####　Relation ship エンティティ間の関係には3つのタイプがある。

  1. One to One 一つにエンティティに対して、一つのエンティティが紐づけられるとき、その関係はOne to Oneの関係。 e.g., 国民とマイナンバーカードの関係。　馬とその「父親」の関係。　現代の日本における人と人の婚姻関係。
  2. One to Many 一つのエンティティに対して、複数のエンティティが紐付けられるとき、その関係はOne to Manyの関係である。 e.g., Youtubeのユーザとユーザがしたコメント。　
  3. Many to Many e.g., SNSの友達の関係。 一人のユーザは複数の友達を持つことができるが、その友達も複数の友達を持つことができる。 開校される授業と、受講者の関係。 授業は受講者を複数持つことができるが、受講者も複数の授業をとることができる。

One to One Relationshipの設計

One to Oneリレーションは、エンティティとして分けるよりも属性として一つにまとめたほうがよい。 例えば、ユーザの登録が必要なサイトがあって、ID一つに対して、usernameが一意に決まるとする。この時、IDとUsernameはOne to Oneの関係である。 これは、一つのテーブルにまた方がよい。しかしあえて分けたほうがいいケースもある。 クレジットカード会社のシステムを考えてみる。カード所持者とカードは一対一の関係である。ここでカード所持者テーブルを作ることを考える。例えば、属性として、id, name, phone_number, card_id, card_issue_date, card_max, card_paymentがあるとする。 この際、card_issue_date以下は、card_idに依存する情報である。よって、card_holderとcard_idはあえて分けるべきである。

One to Many Relationship

MySQLでよく使うクエリ一覧をまとめておこうか

databaseを作る

CREATE DATABASE db1

使うデータベースを選ぶ

USE DATABASE db1

データベースにテーブルを作る

CREATE TABLE lab_member(
    id INT AUTO_INCREMENT, 
    name VARCHAR(255), 
    sex VARCHAR(8), 
    note VARCHAR(255), 
    PRIMARY KEY(id));

テーブルのカラム一覧を得る

DESCRIBE table_name

データベースにデータを登録する。

INSERT INTO lab_member set name='sakurai', sex='male', note='majors in HPC';

setを使うことで、カラム名に対して何をinsertするか決められる。便利です。

Projection (とってー北データに対して一括で変更を加える操作)

SELECT b_id-100, a_id FROM r WHERE a_id = 'a2'

テーブルの縦結合

(SELECT * FROM R) UNION ALL (SELECT * FROM S)

Intersaction 共通のタプル (レコード)が存在する場合にで抽出

(SELECT * FROM R ) INTERSECT (SELECT * FROM S)

Join (これはマジでめちゃめちゃ使います)

SELECT * FROM R JOIN S USING (ATTRIBUTE1, ATTRIBUTE2)

なんか若干SQLの内容が違うけどまあ仕方ないってことにしよう。

カラムやテーブルに別名を付ける法王

カラム

SELECT カラム名　AS <カラムの別名> FROM <テーブル名>；

テーブルに別名

SELECT * FROM <テーブル名> AS <テーブルの別名>；

sqlite3でよく使うコマンドをまとめておこうか？

まずはsqlite3について、軽く説明したいと思う。

サーバではなく、ライブラリというのが大きな特徴だと思います。立ち上げ方は、

sqlite> sqlite3

これでプログラムが走ります。

テーブル一覧を確認

sqlite> .tables

テーブルのスキーマを確認

> .schema <table name>

exit

> .exit