![見出し画像](https://assets.st-note.com/production/uploads/images/86156977/rectangle_large_type_2_5c8e002c09e61b4a0e0462816d68ff56.jpeg?width=1200)
【本要約】たった2日でわかるSQL
1. データベースについて
現在の主流データベースは表形式でデータを扱うRDB
テーブルはレコードと列で構成されるデータベースを管理するソフトウェア=DBMS
▼ Relational Data Base Mnagement System(RDBMS)には以下のようなものが例に挙げられる。
【有償(商用)】
Oracle(Salesforceの根幹システムもOracle)
SQL Server【無償(オープンソース)】
PostgreSQL(Maria DB)
My SQL
MySQL補足
mysql> = プロンプト = 「操作を受け付けることができます」
コマンドには短縮形が存在し、元の形or短縮どちらを使用しても同様の結果を得られる
序盤は、MySQLを'quit'で終了してSQLに戻ることだけ覚えておけばよい
2. SQLの基礎
SQLの分類
DDL(Data Difinition Language) = データ操作
DML(Data Manipuration Language) = データ定義
DCL(Data Control Language) = データ制御
参考:SQLの分類
SQL基本書式 SELECT文の構造
SELECT name from fruits;
命令 パラメータ from句 最後は必ず「;」で終わる
「;」を付けず開業すると、プロンプトが"入力が終わっていない"ことを意味する mysql-> となる
SQLではパラメータ(テーブル名,列名)を除いて大文字と小文字が区別されない
命令句 = 予約語、テーブルなどで使用不可
3 データ出力
where句で条件を指定して検索
SELECT fullname, age FROM customer
WHERE age IN(28, 38, 48);
+--------------------------------------+-----+
| fullname | age |
+--------------------------------------+-----+
| エラ・フィッツジェラルド | 48 |
| エディ・ロバーツ | 28 |
| マーサ・リーブス | 38 |
検索で文字列を使用するとき「'」で囲まないとテーブル名(パラメータ)として認識されてしまう
条件が増えて(orで繋がって)長くなるSQL文をIN演算子を用いて条件をまとめる
※詳細はリンク先の記事を参考
SQLで計算
列名を使って四則演算(+ - * /)
ROUND関数 = 四捨五入
TTUNCATE = 切り捨て
AS = 列名に別名を付ける
4 データ出力
グループ化
GROUP BY句で列単位でグループ化
WHERE 句と HAVING 句を同時に記述した場合
WHERE 句で設定した条件を満たすデータだけを対象にグループ化が⾏われる
そのグループ化したデータに対して HAVING 句で設定した条件を満たすデータだけが取得され る
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テーブル結合とパフォーマンス比較
ユニオン結合
AとBのテーブルで条件に合った情報をすべて持ってきたい
※詳細はリンク先の記事を参考
サブクエリ
サブクエリはSELECT文を( )でくくり、複数のSQL文をまとめられる
サブクエリの出力結果が複数の値となる場合=IN、1つなら比較演算子を用いる
5 データ挿入/変更/削除
レコードの追加
INSERT INTO テーブル名 VARUES (値1, 値2...);
INSERT命令でテーブルにレコード追加できる
数字を文字列で出力したい場合「'」で囲む
2つ以上のレコードを一括追加した場合、(値1, 値2), (値3, 値4...)とカンマで区切って繋げていく
NULL = 値が空(存在していない状態)
レコード更新と削除
UPDATE命令でレコードを更新
WHERE句で予め指定しないとUPDATE条件にマッチする全てのレコードの値を更新してしまう
一度更新すると元に戻せないため、SELECT文とWHERE句の内容が適切かは要確認
DELETE命令でレコード削除
いったん削除したレコードは復元不可
コマンド履歴は「↑」「↓」でたどって再表示できる
WHERE句を忘れると全レコードが削除される
※詳細はリンク先の記事を参考
6 トランザクションとテーブルの設計
データの整合性を維持する仕組み
トランザクション = 分割できないひとまとまりの処理
送金を例とすると、"引き落とし処理" と "入金処理" がセットの構成
START TRANSACTION で実行
データ整合性の担保のため、トランザクション処理の途中でエラーが発生した場合、全処理開始時の状態に戻す設計になっている
1つのトランザクション完了時点でそれを確定させる処理をコミット(COMMIT)
問題発生時、該当処理を取り消してもとに戻すことを*ロールバック(ROLLBACK)
トランザクションに対応していないストレージエンジンもある(例:MyISAM)
コミット後にロールバックしてもトランザクション前の状態には戻らない
※詳細はリンク先の記事を参考
テーブル設計
データ型はRDBMSにより多少の違いがある
非NULL制約 = 値がNULLであってはならない
複数列をまとめて主キーとして扱う場合もあり、複合キーと呼ばれる
主キー(プライマリキー) = 特定のレコードをユニークにし区別するための列
主キーは重複した値がなく、且つ必ず値が入っていて空欄でない(NULLは不可)
他のテーブルの主キーが参照している列を外部キーという
主キーと外部キーの参照整合性がないと結合時にレコードを適切に取得できない
※詳細はリンク先の記事を参考
正規化
第1正規化 = 1つのレコードで繰り返し部分がない状態にする
第2正規化 = 主キーが決まれば、主キー以外の値も決まるようにする
第3正規化 = 主キー以外の列の値は、主キーによってのみ確定されるようにする
データの重複や矛盾が生じないようテーブルを設計すること
データの冗長性をなくすことでデータベースの保守性を高める
正規化には段階があり第1~第3正規化が良く使用される(第4以降もあるが第3までで十分なケースが多い傾向)
テーブルを正規化により分割することで、複数テーブルを1つのテーブルに再結合する処理が発生する
サーバーによっては単一テーブル処理より、結合処理の方が負担と場合もある
必ずしも第3正規化まで行う必要はない(ケースバイケース)
7 テーブルとビューの作成
テーブルの管理
CREATE TABLE = テーブルを作成する
データ型を間違えてもSQLは実行されてしまうので注意
テーブルをコピーするときは「構造のみ or データ丸ごと」、更にDB毎にコマンドが異なる(※)
※データごとコピーの場合
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
#本要約
この記事が気に入ったらサポートをしてみませんか?