[DB]の記事一覧 | 家studyをつづって

MySQLをかじる

MySQLを試してみる。SQLとはStructured Query Languageでデータベースを操作する言語DBMSとはData Base Manage Systemで、データベースを管理するシステムデータベースの種類階層型：データが１対1の親子関係で結ばれているネットワーク：データが多対多の関係で結ばれているリレーショナル：データが行（ロー）／列（カラム）で管理されている以下のようなものがRDBMSに含まれるMicrosoft SQL ServerOracleMySQLPostgre SQL※SQLはRDBMSで使用する言語試した環境OS:CentOS7MySQLのバージョン（サーバ）：mysqld Ver 5.7.25 for Linux on x86_64MySQLのバージョン（クライアント）：Ver 14.14 Distrib 5.7.25, for Linux (x86_64)インストールの手順yum localinstall http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpmyum -y install mysql-community-serverサービス起動systemctl enable mysqld.servicesystemctl start mysqld.service初期設定mysql_secure_installation 上記を実行するとパスワードが求められるがパスワードがわからなかった。そこで、以下のコマンドを実行してパスワードを確認した。cat /var/log/mysqld.log | grep 'temporary password'上記コマンドにより、パスワードを確認。再度、以下のコマンドで初期設定実行。mysql_secure_installationMySQLへログインmysql -u ユーザ名 -p※このあとパスワードを入力MySQLのログアウトいくつかコマンドがある。quitexitデータベースの確認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 BYSELECT カラム 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_bySELECTした結果をINSERTinsert into テーブル名(カラム名) select カラム名 from テーブル名２ where 条件あれば;行名を変更して表示SELECT 変更前のカラム AS 変更後のカラム FROM tbl_ehon;値の更新update テーブル名 set カラム名='文字ならこんな感じ' where カラム名 = '文字ならこう';削除delete from テーブル名 where カラム名 = '文字';※delete from テーブル名で全消しテーブルの編集alter table テーブル名 rename 新テーブル名;alter table テーブル名 add フィールド名データ型;alter table テーブル名 change フィールド名新フィールド名データ型; alter table テーブル名 modify フィールド名データ型;alter table テーブル名 drop フィールド名;サブクエリ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 TRANSACTIONSELECT ・・・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のプライマリキーを持たせる。参照制約の基本ルール参照元のテーブル（外部キーを指定する側）に行を追加する場合は、その外部キーの値が参照もとのテーブルに存在するもの、もしくはNULLしか追加できない参照先テーブルの行を更新・削除する場合は参照元テーブルの外部キーに存在するものしか操作できない

家studyをつづって

[DB] カテゴリの記事

MySQLをかじる

データベースの種類