SQL学習の基本
SQL学習の構築の手順
手順概要
1.Amazon RDSを用いてMySQLが稼働するデータベースサーバーの初期設定を行う
2.PCにDBServerをインストールする
3.DBeverからデータベースサーバーへ接続する
DBeaverとは
パソコンにインストールして利用するSQLクライアントの一種で、マウスなどで簡単に操作できるソフト（GUI）
SQLクライアントとは
ネットワークを通じてデータベースサーバーに接続するためのソフトウェア。
SQLの実行や記述をサポートしているだけでなく、GUIで設定や操作を行えるため、SQLの実務経験量に関わらず、開発の現場で使用されている。
Amazon RDSとは
AmazonRDSとはクラウド上でRDBを簡単に構築・運用できる便利なサービス。
RDBとは
Relational DataBaseの略称。
関係データベースと訳され、データを複数の表で管理し表と表の関係を定義することで複雑なデータの関係性を表現できる
DBeaverからDBサーバーへ接続する
1.インストールしたDBeaverを起動する
2.「新しい接続」アイコンをクリック
3.MySqlを選択
4.一般タブを開く
・Server Host
RDSのDBサーバーのエンドポイント
以下AWSのRDSで作成したデータベースを参照。
・ユーザー名
RDSのDBサーバーのマスタユーザ名(RDSで設定した名前）
・パスワード
RDSのDBサーバーのマスタパスワード（RDSで設定したもの）
5.テスト接続を確認
・データベース
・データベースの作成（指定した名前のデータベースを作成）
・データベースの一覧の取得（作成済みのデータベースの一覧を取得）
・データベースの削除（指定した名前のデータベースを削除）
・テーブル
・テーブルの作成（指定した名前のテーブルを作成）
・テーブルの一覧の取得
・テーブルの削除
・データ
・データの追加
・データの削除
データベースの操作
１.データベース
DBeaverでの操作
データベースの作成
CREATE DATABASE DB名;
指定した名前でデータベースの作成
データベース一覧の取得
SHOW DATABASE;
データベース一覧の取得
データベースの削除
DROP DATABASE DB名;
データベースの削除
テーブルの操作
・テーブル
データを保存するための「表」を指す
・カラム
テーブルの縦一列
・レコード
テーブルの横一列
・フィールド
各々の入力項目を指す。
「セル」のイメージ
テーブルの作成
CREATE TABLE DB名.テーブル名（カラム名 データ型, カラム名 データ型)
create table testdb.users(id int, name varchar(10));
テーブル一覧の取得
SHOW TABLES FROM DB名;
show tables from testdb;
対象DBの中から、作成済みのテーブルの一覧を取得
テーブルの削除
DROP TABLE DB名.テーブル名;
drop table testdb.users;
データの操作
データの追加
INSERT INTO DB名.テーブル名 VALUES (値①, 値②, ...)
テーブルに含まれるすべてのカラムに値を指定して、データの追加する。記述する値の順序は、カラムの順序と同じ。
データの取得
select * from testdb.users;!
複数のデータを追加
INSERT INTO DB名.テーブル名 VALUES (value1, value2, ...), ・・・
テーブルに含まれる全てのカラムに値を指定して、複数のデータをまとめて追加します。記述する値の順序は、カラム名の順序と同じにする。
insert into testdb.users values (3, 'スズキ'),(4, 'John'), (5, 'メアリー');
データの削除
DELETE FROM DB名.テーブル名 WHERE 条件式;
指定したDBの対象テーブルに格納されている全てのデータを削除する
delete from testdb.users where id=2;
格納されているデータ全て削除
DELETE FROM DB名.テーブル名
delete from testdb.users;
データの取得
一つのカラムのデータの取得
SELECT カラム名① FROM DB名.テーブル名
select email from training1.users;
全てのカラムの値を取得
SELECT * FROM DB名.テーブル名
select * from training1.users;
コメントの書き方
#コメント
--コメント
/コメント/
特定のデータを取得
SELECT * FROM DB名.テーブル名 WHERE 条件式
指定した条件式をみたす全てのデータを取得する
WHEREの次に記述する条件式には、カラム名、演算子、関数などを記述
select * from training1.users where gender='男';
SELECT カラム①, カラム②, ... FROM DB名.テーブル名 WHERE 条件式
指定した条件式をみたす特定のデータを取得する
select name, email, tel from training1.users where email='jiro.tanaka@ccc.com';
##演算子
条件式に入れる演算子は以下のように存在する。
取り出したいデータによって条件を変えよう！
演算子の種類
演算子とは、加算、減算、乗算、除算、剰余などを計算すること。
＜演算子一覧＞
| 演算子 | 使用例 | 意味 |
|---|---|---|
| + | A + B | AにBを加える (加算) |
| - | A - B | AからBを引く(減算) |
| * | A * B | AにBをかける (乗算) |
| / | A / B | AをBで割る (除算) |
| DIV | A DIV B | AをBで割る(整数除算) |
| % | A % B | AをBで割った余り (剰余) |
| MOD | A MOD B | AをBで割った余り (剰余) |
（例）
select A + B;
select A - B;
select height + weight from training1.users;
select weight - height from training1.users;
比較演算子
select 5 between 2 and 10, 1 not between 2 and 10;
論理演算子
データの並び替え
SELECT 文の中で、ORDER BY を使用することで、指定したカラムの値を並び替えたデータを取得することができる。
SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 ORDER BY カラム名③ 並び替え条件
select id, name, height from training1.users order by height asc;
・ascとは、昇順に並び替える。
・descとは、降順に並び替える。
複数のカラムを並び替える
ORDER BY カラム名① 並び替え条件①, カラム名② 並び替え条件②
'''
select * from training1.users order by gender desc, age asc;
'''
最初にgenderカラムの降順で並び替えて、さらにageカラムの昇順で並び替える。
whereとorderとの組み合わせ
WHERE 条件式 ORDER BY カラム名 並び替え条件
select * from training1.users where age >= 20 order by gender asc, age desc;
より複雑なデータを取得する
csvデータをインポートする
AND ORで条件を指定し、データを抽出
SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE 条件式① AND/OR 条件式② AND/OR ...
WHEREの条件式を、ANDまたはORを使って組み合わることができる。
条件式をみなしたい場合は、括弧で囲う。
例
select * from training1.transactions where purchaseDate >= '2020-05-05' and (item='Beef' or item='Pork');
BETWEEN で指定の範囲を条件としてデータを抽出
SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE カラム名③ BETWEEN 最小値 AND 最大値
最小値以上、最大値以下の範囲に含まれるデータを取得する
（例）
purchaseDataカラムの値が、2020-05-01 〜 2020-05-08 の範囲のデータを取得する
select * from training1.transactions where purchaseDate between '2020-05-01' and '2020-05-08';
IN
値を指定してデータを抽出する
SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE カラム名③ IN (値①, 値②,...)
（例）item カラムの値が、Coffee(S), Coffee(M), Coffee(L) のいずれかに等しいデータを取得する。
select * from training1.transactions where item in ('Coffee(S)', 'Coffee(M)', 'Coffee(L)');
NOT IN
それに該当しないデータを抽出する
select * from training1.transactions where item not in ('Coffee(S)', 'Coffee(M)', 'Coffee(L)');
LIKE
該当するものが含まれるデータを抽出する
SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE カラム名③ LIKE パターン
（例）
item カラムの値が、Coffeeという文字列から始まるデータを取得する
select * from training1.transactions where item like 'Coffee%';
LIMIT
レコード数の上限を設定してデータを抽出する
SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 LIMIT 最大件数
（例）
最大10件までの購入履歴を取得する
select * from training1.transactions limit 10;
GROPU BY
カラム名のの値を基準にグループ化して、データを取得する
SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 GROUP BY カラム名①, カラム名②,...
（例）
buyerごとのsalesの合計値を取得する
select buyer from training1.transactions group by buyer;
select buyer, sum(sales) from training1.transactions group by buyer;
HAVING
グループ化したデータに対してさらに条件を指定してデータを絞り込みたい時にHAVINGを使用する
SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 GROUP BY カラム名①, カラム名②,... HAVING 条件式
SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE 条件式① GROUP BY カラム名①, カラム名②,... HAVING 条件式②
（例）
buyerごとのsales の合計値が1500円以上のデータを取得する
select buyer, sum(sales) from training1.transactions group by buyer having sum(sales) >= 1500;
SQLの関数
SUM
合計値を取得する
（例）
商品ごとの購入金額の合計値を降順で取得する
select item, sum(sales) from training1.transactions group by item order by sum(sales) desc;
COUNT
指定したカラムの行数を抽出する
（例）
購入者の延べ人数を取得する
select count(buyer) from training1.transactions;
AVG
指定したカラムの平均値を抽出する
（例）
購入者ごとの購入金額平均値を昇順で取得する
select buyer, avg(sales) from training1.transactions group by buyer order by avg(sales) asc;
MAX, MIN
指定したカラムの値の最大値と最小値を抽出する
（例）
購入履歴の最大値と最小値を抽出
select max(sales), min(sales) from training1.transactions;
