見出し画像

【本要約】たった2日でわかるSQL

Emma

1. データベースについて

  1. 現在の主流データベースは表形式でデータを扱うRDB

  2. テーブルはレコードと列で構成されるデータベースを管理するソフトウェア＝DBMS

▼ Relational Data Base Mnagement System（RDBMS）には以下のようなものが例に挙げられる。

  • 【有償(商用)】
    Oracle（Salesforceの根幹システムもOracle）
    SQL Server

  • 【無償(オープンソース)】
    PostgreSQL（Maria DB）
    My SQL

MySQL補足

  • mysql> = プロンプト = 「操作を受け付けることができます」

  • コマンドには短縮形が存在し、元の形or短縮どちらを使用しても同様の結果を得られる

  • 序盤は、MySQLを'quit'で終了してSQLに戻ることだけ覚えておけばよい

参照：MySQLコマンドの一覧とコマンドの実行

2. SQLの基礎

SQLの分類

  1. DDL(Data Difinition Language)　=　データ操作

  2. DML(Data Manipuration Language)　=　データ定義

  3. DCL(Data Control Language)　=　データ制御

参考：SQLの分類

SQL基本書式　SELECT文の構造

    SELECT name from fruits;

  • 命令　パラメータ　from句　最後は必ず「;」で終わる

  • 「;」を付けず開業すると、プロンプトが"入力が終わっていない"ことを意味する mysql-> となる

  • SQLではパラメータ(テーブル名,列名)を除いて大文字と小文字が区別されない

  • 命令句　＝　予約語、テーブルなどで使用不可

参考：SQLの基本を覚える【初心者向け】

3 データ出力

where句で条件を指定して検索

    SELECT fullname, age FROM customer
    WHERE age IN(28, 38, 48);
    +--------------------------------------+-----+
    | fullname                             | age |
    +--------------------------------------+-----+
    | エラ・フィッツジェラルド             |  48 |
    | エディ・ロバーツ                     |  28 |
    | マーサ・リーブス                     |  38 |

  • SELECT構文：WHEREで検索条件を設定する

  • 検索で文字列を使用するとき「'」で囲まないとテーブル名(パラメータ)として認識されてしまう

  • 条件が増えて(orで繋がって)長くなるSQL文をIN演算子を用いて条件をまとめる

※詳細はリンク先の記事を参考

SQLで計算

  • 列名を使って四則演算(+ - * /)

  • ROUND関数 = 四捨五入

  • TTUNCATE = 切り捨て

  • AS = 列名に別名を付ける


4 データ出力

グループ化

SELECT col_name FROM table_name
[WHERE where_condition]
[GROUP BY col_name, ... [WITH ROLLUP]]
[HAVING where_condition]

※詳細はリンク先の記事を参考


テーブルの結合

  • 内部結合

    • 共通する列を紐づけてテーブルを結合

    • 紐づけたデータが⽚⽅のテーブルにしかないレコードは出⼒されない

select
A.item1
,A.item2
,A.item3
,B.item4
,B.item5
from
A
inner join B on
 A.Item3 = B.Item3
where
A.item1 = "~"

  • 外部結合

    • 紐づけたデータが片方のテーブルにしかないレコードも出力される

    • どちらかのレコードのみ全て抽出したい場合は外部結合を使用

select
A.item1
,A.item2
,A.item3
,B.item4
,B.item5
from
A
left(right) outer join B on
A.Item3 = B.Item3
 where
A.item1 = "~"

参考：SQL 外部結合サンプルコード 3テーブル結合とパフォーマンス比較


※詳細はリンク先の記事を参考


サブクエリ

  • サブクエリはSELECT文を(　)でくくり、複数のSQL文をまとめられる

  • サブクエリの出力結果が複数の値となる場合＝IN、1つなら比較演算子を用いる


5　データ挿入/変更/削除

レコードの追加

  • INSERT INTO テーブル名 VARUES (値1, 値2...);

  • INSERT命令でテーブルにレコード追加できる

  • 数字を文字列で出力したい場合「'」で囲む

  • 2つ以上のレコードを一括追加した場合、(値1, 値2), (値3, 値4...)とカンマで区切って繋げていく

  • NULL　=　値が空(存在していない状態)

レコード更新と削除

  • UPDATE命令でレコードを更新

    • WHERE句で予め指定しないとUPDATE条件にマッチする全てのレコードの値を更新してしまう

    • 一度更新すると元に戻せないため、SELECT文とWHERE句の内容が適切かは要確認

  • DELETE命令でレコード削除

    • いったん削除したレコードは復元不可

    • コマンド履歴は「↑」「↓」でたどって再表示できる

    • WHERE句を忘れると全レコードが削除される

  • SQLでのデータを追加、更新、削除

※詳細はリンク先の記事を参考

6　トランザクションとテーブルの設計

データの整合性を維持する仕組み

  • トランザクション　=　分割できないひとまとまりの処理

  • 送金を例とすると、"引き落とし処理" と "入金処理" がセットの構成

  • START TRANSACTION で実行

  • データ整合性の担保のため、トランザクション処理の途中でエラーが発生した場合、全処理開始時の状態に戻す設計になっている

  • 1つのトランザクション完了時点でそれを確定させる処理をコミット(COMMIT)

  • 問題発生時、該当処理を取り消してもとに戻すことを*ロールバック(ROLLBACK)

  • トランザクションに対応していないストレージエンジンもある(例:MyISAM)

  • コミット後にロールバックしてもトランザクション前の状態には戻らない

  • SQLの基礎【トランザクションについて】

※詳細はリンク先の記事を参考

テーブル設計

※詳細はリンク先の記事を参考

正規化

  1. 第1正規化　=　1つのレコードで繰り返し部分がない状態にする

  2. 第2正規化　=　主キーが決まれば、主キー以外の値も決まるようにする

  3. 第3正規化　=　主キー以外の列の値は、主キーによってのみ確定されるようにする

  • データの重複や矛盾が生じないようテーブルを設計すること

  • データの冗長性をなくすことでデータベースの保守性を高める

  • 正規化には段階があり第1～第3正規化が良く使用される(第4以降もあるが第3までで十分なケースが多い傾向)

  • テーブルを正規化により分割することで、複数テーブルを1つのテーブルに再結合する処理が発生する

  • サーバーによっては単一テーブル処理より、結合処理の方が負担と場合もある

  • 必ずしも第3正規化まで行う必要はない(ケースバイケース)


7　テーブルとビューの作成

テーブルの管理

CREATE TABLE　=　テーブルを作成する

※データごとコピーの場合

CREATE TABLE 新規テーブル名 LIKE コピー元テーブル名;

※構造のみコピーの場合

CREATE TABLE 新規テーブル名 SELECT * FROM コピー元テーブル名;


ALTER TABLE　=　列の追加/削除, テーブル名の変更

  • 既存のテーブルに列(=カラム)を追加

  • 通常は右端に追加されるが、左端に追加したい場合、FIRSTで指定可能

ALTER TABLE テーブル名 ADD カラム名 データ型 [FIRST];


DROP TABLE = 列・テーブルの削除

  • テーブルから列を削除

ALTER TABLE テーブル名 DROP 列名;

  • テーブル⾃体の削除には DROP TABLE ⽂ を使⽤

DROP TABLE テーブル名;

参考：SQL（MySQL）入門！ALTER（テーブルの作成・削除・変更・主キー・NOT NULL）編

ビュー(View)をつくる

CREATE VIEW　=　ビューの作成

  • 実際のテーブルと異なる"仮想的なテーブル"

DROP TABLE テーブル名;

UPDATE　=　ビューの更新

  • ビューを更新すると元のデータも更新される

UPDATE ビュー名;

  • ビューを更新できる条件

    • 元のテーブルが1つ(複数テーブル負荷)

    • SELECT文に式や集合関数を含まない

    • WHERE句にサブクエリを含まない

    • GROUP BY句 や HAVING句 を含まない(集計値は不可)

    • DISTINCTを使用していない
      複数テーブルや集計値、変更行が特定できないビューは更新不可
      WITH READ ONLYを定義した場合も明示的に読取専用となるため更新できない

DROP VIEW　=　ビューの削除

  • 不要となったビューは削除する

DROP VIEW ビュー名;


JOIN句　=　ビューの結合(抽出)

  • INNER JOIN　(内部結合)
    　テーブルそれぞれに存在する行を抽出

  • 左外部結合（LEFT OUTER JOIN）
    　左テーブルに存在する行のみ抽出、存在しない行の場合は空白

  • IGHT OUTER JOIN　(右外部結合)
    　右テーブルに存在する行のみ抽出、存在しない行の場合は空白

  • FULL OUTER JOIN　(全外部結合)
    　テーブルいずれかに存在する行を抽出、存在しない行の場合は空白

  • CROSS JOIN　(交差結合)
    　テーブルの抽出数の組み合わせ数すべてを抽出

CREATE VIEW ビュー名 AS SELECT 抽出データ FROM テーブルa JOIN テーブルb ON
USING(テーブルID) ;


SQLビューの活用メリット

  • ビューで繰り返し発生するSELECT文を単純化が可能

  • 実データが伴わない仮想テーブルにて、セキュリティリスクを低減

  • 1度作成したビューを他の抽出でも転用し、SQL文の使用を効率化


8 MySQLの活用

データベース管理

CREATE　DATABASE　=　データベース作成

  • SHOW DATABASE でデータベース(DB)一覧を確認可能

  • DB⼀覧に表⽰される「information_schema」「mysql」はMySQLサーバー管理⽤DBにて編集し ない

CREATE DATABASE データベース名;

  • CHARACTER SET (キャラクタセット) = 利⽤できる⽂字の種類を指定

CREATE DATABASE データベース名 CHARACTER SET uft8;
// ⽂字コードをUTF8に設定してデータベースを新規作成


DROP　DATABASE　=　データベースの削除

  • 警告など何も憑依されないため、実⾏時は慎重に対応する

DROP DATABASE データベース名;


CREATE USER =　ユーザ作成

  • MySQLの使用には予めデータベースユーザを作成の必要あり

  • MySQLインストール直後はrootユーザが用意される (権限大)
    　こちらは使用せず管理者ユーザを作成して使用する

  • ユーザ作成時はホスト名も同時作成

CREATE USER ユーザ名＠ホスト名 IDENTIFIED BY 'パスワード';


権限設定

  • 新規作成したデータベースユーザにはアクセス権限を設定する

GRANT 権限 ON データベース名. テーブル名 TO ユーザ名＠ホスト名 [IDENTIFIED BY 'パスワ
ード'];


SET PASSWORD　=　パスワード変更

  • 初期化とは異なるので、設定したパスワードは忘れないよう注意

SET PASSWORD FOR ユーザ名＠ホスト名 = password ('新しいパスワード') ;


SELECT USER　=　ユーザ確認

  • データベースに登録されているユーザの参照

SELECT USER, Host FROM Mysql. user;


DROP USER　=　ユーザ削除

  • 不要になったユーザは削除対応

DROP USER ユーザ名＠ホスト名;


参考：データベース活用

#学習記録
#プログラミング初心者
#読書記録
#私の仕事
#SQL
#本要約


この記事が気に入ったらサポートをしてみませんか？