家studyをつづって

MySQLを試してみる。

SQLとは
Structured Query Languageでデータベースを操作する言語

DBMSとは
Data Base Manage Systemで、データベースを管理するシステム

データベースの種類

• 階層型：データが１対1の親子関係で結ばれている
• ネットワーク：データが多対多の関係で結ばれている
• リレーショナル：データが行（ロー）／列（カラム）で管理されている
  以下のようなものがRDBMSに含まれる
  • Microsoft SQL Server
  • Oracle
  • MySQL
  • Postgre SQL

※SQLはRDBMSで使用する言語

試した環境
OS:CentOS7
MySQLのバージョン（サーバ）：mysqld  Ver 5.7.25 for Linux on x86_64
MySQLのバージョン（クライアント）：Ver 14.14 Distrib 5.7.25, for Linux (x86_64)

インストールの手順
yum localinstall http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
yum -y install mysql-community-server

サービス起動
systemctl enable mysqld.service
systemctl start mysqld.service

初期設定
mysql_secure_installation
上記を実行するとパスワードが求められるがパスワードがわからなかった。
そこで、以下のコマンドを実行してパスワードを確認した。
cat /var/log/mysqld.log | grep 'temporary password'

上記コマンドにより、パスワードを確認。
再度、以下のコマンドで初期設定実行。

mysql_secure_installation

MySQLへログイン
mysql -u ユーザ名 -p
※このあとパスワードを入力

MySQLのログアウト
いくつかコマンドがある。
quit
exit

データベースの確認
SHOW DATABASES;

データベースへの接続
接続すると、操作時にデータベースが省略された場合には接続したデータベースで実行される。
mysql> use データベース名;

データベースの作成
mysql> create database データベース名;

ユーザの作成と接続
「root」ユーザは管理者アカウントなので、通常の作業では使用しない。新しくユーザを作成するには、「GRANT」コマンドを使用する。
GRANT ALL PRIVILEGES ON データベース名.* TO ユーザ名@ホスト名 IDENTIFIED BY 'パスワード';

一度、quitでログアウト後に上記ユーザで接続する。
※上記で作成されたユーザはmysql.userに登録される。

テーブルの作成
Excelに例えるのであれば、ワークブックはデータベース、テーブルはワークブックの中のシートになる。

create table テーブル名(カラム名1 INT UNIQUE,カラム名2 VARCHAR(30),カラム名3 INT,PRIMARY KEY (カラム名1));

※UNIQUE：列制約
※PRIMARY KEY：テーブル制約

テーブルの一覧確認
show tables from データベース名;

テーブルの削除
DROP table テーブル名;

テーブルのカラム確認
 show fields from テーブル名;

データの挿入
INSERT INTO テーブル名(カラム1,カラム2,カラム3)VALUES(値1,'値2（文字列の場合）',値3);

昇順・降順
select * from テーブル名 ORDER BY 並べ替えのカラム;
※DESCを末尾につけると降順

GROUP BY
SELECT カラム FROM テーブル名 GROUP BY カラム名;
グループ化しているので、下手なカラムを抽出しようとすると怒られる。
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'おかしな指定' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECTした結果をINSERT
insert into テーブル名(カラム名) select カラム名 from テーブル名２ where 条件あれば;

行名を変更して表示
SELECT 変更前のカラム AS 変更後のカラム FROM tbl_ehon;

値の更新
update テーブル名 set カラム名='文字ならこんな感じ' where カラム名 = '文字ならこう';

削除
delete from テーブル名 where カラム名 = '文字';
※delete from テーブル名 で全消し

テーブルの編集
alter table  テーブル名 rename  新テーブル名;
alter table テーブル名 add フィールド名 データ型;

サブクエリ
SELECT * FROM テーブル名 WHERE 条件 (SELECT AVG(カラム名) FROM テーブル名);
()のサブクエリの実行を行った後、その値（上記はAVG）を使ってメインクエリを処理する。

WHEREとHAVINGの違い
＝命令の実行順の違い
SQLの命令の実行順は以下の通り。WHEREとHAVINGは同じ処理だが、GROUP BYの前後に位置する。
１．FROM
２．WHERE
３．GROUP BY
４．HAVING
５．SELECT
６．ORDER BY
なので、
WHERE・・・グループ化をされる前の段階、つまり元々のデータでの抽出条件を指定できる
HAVING・・・グループ化した後の情報での、抽出条件を指定できる。

結合とは
複数のテーブルやビューを連結して離れた場所にあるデータを一度に扱えるようにすること。

交差結合
テーブルを単純に結合する
SELECT * FROM テーブルA CROSS JOIN テーブルB
→SELECT * FROM テーブルA,テーブルBも同様の結果

内部結合
交差結合のように単純なテーブル接続ではなく、指定した列が一致する行のみを抽出

mysql> select * from tbl_namelist;
+------+-----------+
| no   | name      |
+------+-----------+
|    1 | yamada    |
|    2 | yauti     |
|    3 | ichihashi |
+------+-----------+

mysql> select * from tbl_grades;
+------+---------+---------+
| no   | history | science |
+------+---------+---------+
|    1 |      78 |      65 |
|    2 |      81 |      93 |
+------+---------+---------+

mysql> select * from tbl_namelist inner join tbl_grades on tbl_namelist.no=tbl_grades.no;
+------+--------+------+---------+---------+
| no   | name   | no   | history | science |
+------+--------+------+---------+---------+
|    1 | yamada |    1 |      78 |      65 |
|    2 | yauti  |    2 |      81 |      93 |
+------+--------+------+---------+---------+
※no=3のデータが表示されない。

外部結合
内部結合した結果に加えて、処理しなかったデータも抽出する
外部結合は以下の３つがある。
１．左外部結合
２．右外部結合
３．全外部結合

mysql> select * from tbl_namelist left join tbl_grades on tbl_namelist.no = tbl_grades.no;
+------+-----------+------+---------+---------+
| no   | name      | no   | history | science |
+------+-----------+------+---------+---------+
|    1 | yamada    |    1 |      78 |      65 |
|    2 | yauti     |    2 |      81 |      93 |
|    3 | ichihashi | NULL |    NULL |    NULL |
+------+-----------+------+---------+---------+
※内部結合の結果に加えてno=3が表示されている。

mysql> select * from tbl_namelist right join tbl_grades on tbl_namelist.no = tbl_grades.no;
+------+--------+------+---------+---------+
| no   | name   | no   | history | science |
+------+--------+------+---------+---------+
|    1 | yamada |    1 |      78 |      65 |
|    2 | yauti  |    2 |      81 |      93 |
| NULL | NULL   |    4 |      85 |     100 |
+------+--------+------+---------+---------+

VIEWの作成
CREATE VIEW ビュー名 AS SELECT * FROM テーブル名 WHERE 条件;
※SELECT以降は通常のSQL文

VIEWには通常のINSERTやDELETE、UPDATEの操作ができる。
VIEWに操作した内容は、もとのテーブルにも反映される。

○もとのテーブル表示
mysql> select * from tbl_race;
+------+----------+--------+
| no   | team     | result |
+------+----------+--------+
|   92 | team9292 |      1 |
|   10 | nonstop  |      3 |
|   46 | v-rossi  |      4 |
|   74 | daichan  |      5 |
|   19 | senpai   |      6 |
|   11 | ukya     |      7 |
+------+----------+--------+

○ビューの作成
mysql> create view viw_winner as select * from tbl_race where result <= 3;

○ビュー操作前
mysql> select * from viw_winner;
+------+----------+--------+
| no   | team     | result |
+------+----------+--------+
|   92 | team9292 |      1 |
|   10 | nonstop  |      3 |
+------+----------+--------+

○ビューにレコード追加
mysql> insert into viw_winner values(23,'team2323',2);

○操作後ビュー確認
mysql> select * from viw_winner;
+------+----------+--------+
| no   | team     | result |
+------+----------+--------+
|   92 | team9292 |      1 |
|   10 | nonstop  |      3 |
|   23 | team2323 |      2 |
+------+----------+--------+

○操作後もとのテーブル確認
mysql> select * from tbl_race;
+------+----------+--------+
| no   | team     | result |
+------+----------+--------+
|   92 | team9292 |      1 |
|   10 | nonstop  |      3 |
|   46 | v-rossi  |      4 |
|   74 | daichan  |      5 |
|   19 | senpai   |      6 |
|   11 | ukya     |      7 |
|   23 | team2323 |      2 |
+------+----------+--------+

集合演算子
UNION＝和集合、SELECT文の結果の結合、UNION ALLにすると重複もそのままで表示する。

mysql> select * from tbl_club1;
+------+--------+--------+
| no   | fname  | sname  |
+------+--------+--------+
|   10 | takao  | suzuki |
|   11 | ichiro | suzuki |
+------+--------+--------+

mysql> select * from tbl_club2;
+------+--------+----------+
| no   | fname  | sname    |
+------+--------+----------+
|    1 | noriko | miyasaka |
|    2 | yuko   | satoh    |
|    3 | tamao  | okada    |
+------+--------+----------+

mysql> select * from tbl_club1 where no < 12 union select * from tbl_club2 where no < 3 order by no desc;
+------+--------+----------+
| no   | fname  | sname    |
+------+--------+----------+
|   11 | ichiro | suzuki   |
|   10 | takao  | suzuki   |
|    2 | yuko   | satoh    |
|    1 | noriko | miyasaka |
+------+--------+----------+

INTERSECT=積集合
2つのテーブルの共通項を抽出する。

EXCEPT,MINUS＝差集合
左のテーブルにあり、右のテーブルにないものを抽出する。

ALL＝すべての値と比較する
サブクエリによって抽出された結果をメインクエリの結果と照らし合わせる。

mysql> select * from tbl_dinner;
+------+---------+-------+
| no   | menu    | price |
+------+---------+-------+
| 1111 | humberg |  1300 |
| 1112 | karaage |   900 |
| 1113 | buta    |  1000 |
| 1114 | tori    |  1350 |
| 1115 | fire    |  1800 |
| 1116 | nato    |  1550 |
+------+---------+-------+

mysql> select * from tbl_lunch;
+------+----------+-------+
| no   | menu     | price |
+------+----------+-------+
| 2221 | torikara |   850 |
| 2222 | curry    |   900 |
| 2223 | menti    |  2000 |
| 2224 | udon     |   110 |
| 2225 | spa      |  1350 |
| 2225 | fukahire |  1400 |
| 2226 | fukahire |  1400 |
+------+----------+-------+

mysql> select * from tbl_dinner where price < all (select price from tbl_lunch where menu like '%fukahire%');
+------+---------+-------+
| no   | menu    | price |
+------+---------+-------+
| 1111 | humberg |  1300 |
| 1112 | karaage |   900 |
| 1113 | buta    |  1000 |
| 1114 | tori    |  1350 |
+------+---------+-------+

結果として、fukahire(price=1400)以下のメニューがtbl_dinnerより抽出された。

インデックスについて
データベースより、ある条件のレコードを抽出する場合、2万件のDBからの検索であれば、2万件を調べる。＝テーブルスキャン

これに対し、効率のいい検索を実現するのがインデックススキャン
インデックスでは、特定の列を基準としてデータを整理（木構造など）することで検索性能を向上する。

CREATE INDEX インデックス名 ON テーブル名（カラム名）；

インデックス設定が適しているケース

• 検索対象表の行数が多い
• 検索対象表において、検索項目の属性値（キー値）に重複・偏りが少ない
• 検索対象の表の更新が少ない
• 検索対象の表の追加・削除が少ない

データを保護する仕組み
トランザクション：データに対する操作をひとまとめにしたもの
トランザクション実行中は他のトランザクションがデータを操作できないようにする仕組み
→ロック
また、トランザクションによる結果を反映するかどうかはトランザクション実行後に決定できる。
コミット：反映
ロールバック：処理の前の状態に戻す

トランザクションの書き方
BEGIN TRANSACTION
SELECT ・・・
INSERT ・・・
COMMIT;

ロックの種類
排他ロック：誰かが操作中はデータの参照等の操作が一切できない
共有ロック：誰かが操作中はデータの参照はできるが変更はできない

ストアドプロシージャ
関連するクエリをひとまとめにしておく機能。
複雑なデータ処理でも、ストアドプロシージャに登録があれば、ストアドプロシージャを実行することで、一連の処理が実行される。
MySQLのアーキテクチャ
MySQLはOS上では１つのプロセスとして動作し、実行されたSQL文の処理は内部の複数のスレッドで処理する「シングルプロセスマルチスレッド型」となる。
クライアントアプリケーションとSQLサーバ間の通信はTCP/IPで、SSLにより暗号化されている。（5.7以降）

ストレージエンジンについて
ストレージエンジンは、データフォーマットの定義、データ永続化、インデックス管理、トランザクション管理、ロックと排他機能を司る。
利用可能なストレージエンジンは以下のコマンドで確認できる。
mysql> SHOW ENGINES;

SQLの絵本 第2版 データベースが好きになる新しい9つの扉 [ 株式会社アンク ] 価格：1922円（税込、送料無料) (2019/3/8時点) 楽天で購入

MySQLのセキュリティ
MySQLのセキュリティ概要は以下のようになっている。





認証
MySQLにおける認証は、「ユーザID」、「パスワード」、「接続元アドレス／ホスト名」の３つで認証を行う。
ユーザの作成はGRANT文で行う。
GRANT 権限 ON テーブル名 TO ユーザ名@ホスト名
上記の例では、ホスト名で指定したマシンからユーザ名で指定したユーザでのみ、データベースに接続できる、ユーザを作成している。

※MySQLでは名前のないユーザ（匿名ユーザ、ユーザ名なし＠ホスト名、等）が作成できる。
通常、このようなユーザは削除したほうが良い

※デフォルトでいる「root」等は狙われやすいので、デフォルトアカウントを削除して、別に管理者アカウントを立てることも、セキュリティのレベルを上げる一つの対策となる。

アクセスコントロール
ユーザごとのデータベースに対する操作権限の設定で以下のようなものがある。

• グローバル権限・・・管理者権限
• データベース権限／スキーマ権限・・・データベース内での権限
• テーブル権限・・・個々のテーブルに限定した権限
• 列権限・・・特定の列に限定した権限

監査
操作ログの監視
General Logには、清浄操作を含むすべての操作ログが記録される。

通信経路の暗号化
MySQLサーバの通信はデフォルトでは暗号化されていないため、やり取りされるデータは平文のまま流れるため、通信を傍受されるとデータの内容が読み取られる。

データベースのキー
キー：テーブルの中でレコードを識別するための情報
候補キー：レコードを識別するために使用できそうなキー
主キー：実際にレコードを識別するために使用するキー
複合キー：複数のフィールドを使用して主キーとして使う

1対多の関係
1対多の関係では多のほうに外部キーとして1のプライマリキーを持たせる。


参照制約の基本ルール

1. 参照元のテーブル（外部キーを指定する側）に行を追加する場合は、その外部キーの値が参照もとのテーブルに存在するもの、もしくはNULLしか追加できない
2. 参照先テーブルの行を更新・削除する場合は参照元テーブルの外部キーに存在するものしか操作できない