[前半](My)SQLの概念・基本操作・演習までやってみた。[Paiza]

つ、ついに、、避け続けたSQLに関して、、、、、
今回こそ聞かれたら答えられるくらい、、、理解できるかな、、、、🥺🥺
でもSQLの前にさらっとDBについておさらい🤕
🌟Paizaで練習するのが楽ちん👇👇
https://paiza.io/ja/projects/new

DB[補足等]

DBはデータの集まり
DBの利用は直接ユーザーがデータにアクセスするわけではなく、データの要求や取り出しを整理する働きがあるDBMSによって行われる。

DBの種類

• 🌟リレーショナルデータベース（主流）
• 階層型データベース
• オブジェク指向データベース
• キー・バリュー型データストア
  など、、他にもいろいろな種類がある！

RDS（リレーショナルデータベース）とは？？🙁

Excelのように列（カラム）と行（レコード）からなるテーブル（表）形式で複数データを関連付け、使えるようにしたデータベースのこと

複数のテーブルの中に共通の意味を持つ列を作ることで２つのテーブルを関係付け（リレーション）しているDBってこと🥺💗理解っ

RDSとともに覚えておくべきなのがRDBMS
RDBMS：RDSを操作するDBMS（ﾏﾈｼﾞﾒﾝﾄｼｽﾃﾑ）のこと。

DBの利用は直接ユーザーがデータにアクセスするわけではなく、データの要求や取り出しを整理する働きがあるDBMSによって行われる。

そしてやっとここでSQLとの関連が、、、👇👇

SQLとは

SQLとはRDBMSを操作する言語（げ、言語やったんか、、、、）
よく聞くMySQLはその１種類！（オープンソースやから有名なんね）

オープンソース：無償（または最低限の必要なコスト）で公開されたプログラムなどのソースコードで、自由に利用や、改変、再配布ができるという特徴がある。

どのRDBMSに対しても使える標準SQLとRDBMS毎に使える特有の方言があるらしい～とりま標準を学ぶ🥺

コラム：SQL/Pythoon/Rの使い分けの目安

データ分析にはSQL以外にもRやPythoonなどがある。（
＜使い分けの目安＞

• SQL:データ分析に用いろデータをDBから用意するまで
• R:アドホック（その場限りの）な分析の場合に使用
• Pythoon:処理をシステム化したり、機械学習を用いた分析の場合

SQLがDBからデータを持ってきてデータ分析しやすい形に整えて
R（現状確認など）やPythoon（処理をシステム化して繰り返し使いたい時など）で分析していく
という流れ。にゃるへそ😺

SQLの３つの機能

1. データ操作：票のデータの登録・修正・検索・複数の票の結合など
2. データ定義：データベースのデータ構造の定義など
3. データ制御：データベースへのアクセス制御など

データ操作の構成要素

SQLのデータ操作は以下の４つの命令文から構成されている。

1. SELECT:テーブルからデータを抽出
2. INSERT:テーブルにレコードを追加
3. UPDATE:テーブル中にあるレコードのデータ内容を更新する
4. DELETE:テーブル内にあるレコードを削除する

🌟SELECT文によって必要なデータを取り出すことを問い合わせ（クエリ）とよぶ😼

ここから下の例でつかう例

-- 定義部分
CREATE TABLE members
(
id INTEGER NOT NULL,
name CHAR(32),
height REAL,
weight REAL,
age INTEGER,
job_id INTEGER,
PRIMARY KEY (id)
)
;

CREATE TABLE jobs
(
id INTEGER NOT NULL,
name CHAR(32),
salary INTEGER,
PRIMARY KEY (id)
)
;

-- 入力部分
INSERT
INTO members(id, name, height, weight, age, job_id)
VALUES
(1, '佐藤', 170.2, 65.2, 60, 4),
(2, '鈴木', 151.5, 50.3, 53, 2),
(3, '高橋', 182.1, 85.1, 31, 8),
(4, '田中', 163.5, 70.6, 36, 3),
(5, '渡辺', 157.8, 55.8, 62, 7),
(6, '伊藤', 173.0, 65.3, 75, 1),
(7, '山本', 166.4, 49.1, 25, 5),
(8, '中村', 144.1, 56.9, 45, 7),
(9, '小林', 168.7, 90.1, 38, 3),
(10, '加藤', 178.6, 78.5, 26, 6)
;

INSERT
INTO jobs(id, name, salary)
VALUES
(1, '医師', 1232),
(2, '弁護士', 1028),
(3, 'SE', 515),
(4, '会計士', 1024),
(5, '薬剤師', 542),
(6, '保育士', 341),
(7, '大学教授', 1050),
(8, '塾講師', 361)
;

SELECT文

テーブルからデータを抽出・取得するために用いる命令文

基本の書き方

SELECT カラム(列)名　FROM テーブル名；
// FROMの後ろで指定したテーブルからSELECT後に指定したカラムを取り出せる

例）membersテーブルからheightカラムを取得する
SELECT height FROM members;

例）membersテーブルからheightカラム/weightを取得する
SELECT height, weight FROM members; // 複数のカラムを取得する場合はカンマ(,)で区切る！

応用

SELECT文に書くカラム名には定数や式も書くことが出来る。
身長の単位をｍ（÷100）で表示・身長測定日・測定者を表示したい場合
SELECT height / 100, '2018-04-01', '吉田' FROM members;
// 文字列や日付を書く場合シングルクォーテーション('')で囲う🥺

SQL操作でのコメントアウトは--(ハイフン２つ)
カラム名のところに*(アスタリスク)を書くとすべてのカラムを指定できる☺

WHERE句

特定の条件を満たすレコード(行)のみ取得したい時に使用する。

基本構文

SELECT カラム FROM テーブル WHERE 条件式;

例) 身長が180cm以上の人の名前を取り出したい
SELECT name FROM members WHERE height >= 180;

SQLで使う比較演算子（特殊なものだけ）
a = b // a と b は等しい
a <> b // a と b は等しくない

応用

複数の条件式を書く場合は AND か OR でつなげて書く(カンマじゃないからね～～)

例) 身長が170cm以上かつ体重が70㎏以下の人を取得
SELECT name FROM members WHERE height >= 170 AND weight <=70;

例) 身長が180cm以上または160cm以下の人を取得
SELECT name FROM members WHERE height >= 180 OR height <= 160;

COUNT関数

そのまま！レコード数を数える！

基本

SELECT COUNT(*) FROM テーブル名;

例) 50歳以上の人数を取得
SELECT COUNT(*) FROM members WHERE age >=50;

COUNT関数のように集計に用いる関数を集約関数と呼ぶ

COUNT以外の集約関数

1. SUM関数
2. AVG関数（平均値）
3. MAX関数（最大値）
4. MIN関数（最小値）

このままだとCOUNT数字だけ出力され何の数かわかりにくい。
そこでカラム名に別名を付けることが出来る🙃
⇒　カラム名に別名を付けるにはASを使う

実演

例) 50歳以上の人数を取得してカラム名を「50歳以上の人数」とする
SELECT COUNT(*) AS "50歳以上の人" FROM members WHERE age >= 50;

例) SELECT height / 100, '2018-04-01', '吉田' FROM members;にもそれぞれ名前を付けたい
SELECT height / 100 AS height_m, '2018-04-01' AS "測定日", '吉田' AS "測定者" FROM members;

毎回ASつけるのと,の前につけるの注意🙁💗

昇順・降順に並び替えて出力する

昇順・降順に並び替えるにはORDER BYを使用する。

基本

SELECT カラム　FROM テーブル OREDER BY 並び替えの基準となるカラム名;

例) membersテーブルの名前と年齢を年齢の若い順に並べる（昇順）
SELECT name, age FROM members ORDER BY age; //デフォルトでは昇順（小⇒大）

例)  membersテーブルの名前と年齢を年齢の若い順に並べる（降順）
SELECT name, age FROM members OREDER BY age DESC; // DESCを追加するだけ！

応用

// 昇順とかではなくID順に並び替えたい場合(すべてのデータver)
SELECT * FROM members OREDER BY id;

例) membersテーブルから身長が170cm以上の人の名前と年齢を年齢が高い順に並び替える
SELECT name AS'名前', age AS "年齢" FROM members WHERE height >= 170 ORDER BY age DESC;

GROUP BY句

テーブルをいくつかのグループに切り分けて扱うときに使う

基本

SELECT カラム FROM テーブル　GROUP BY 集約キー;
// 集約キー = どのようにテーブルを切り分けるかを指定する列

例) 職種(job_id)毎の人数を取得する
SELECT job_id, COUNT(*) FROM members GROUP BY job_id;

集約関数やGROUPBY(HAVING句も)を使用するときの注意点

1. SELECT邦書けるカラムに制限がかかる。
   ・定数
   ・GROUP BY句で指定したカラム名（集約キーの事）
   ・集約関数
2. GROUP BY句にASで命名した別名は指定できない（SQLの実行順がGROUP BY⇒SELECTだから）
3. GROUP BYに並び替え機能はない(並び替えたければORDER BY使う事‼)

ここまでの書き方順

SELECT⇒FROM⇒WHERE⇒GROUP BY⇒ORDER BY

応用

例) 職種ごとの平均身長を取得しよう
SELECT job_id, AVG(height) AS "平均身長" FROM menbers GROUP BY job_id
// job_idとheightを取得してる。heightは平均計算＆ASで名前命名

HAVING句

WHERE句はGROUP BY句より先に処理が行われるのでグループ化した後のテーブルに対しては条件付けはできない。
グループ化したテーブルに対して条件を指定するにはHAVING句を用いる

基本

SELECT カラム　FROM テーブル GROUP BY 集約キー　HAVING グループの値に対する条件式;

例) job_idごとの人数を数えて2人いたjob_idだけを取得する
SELECT job_id, COUNT(*) FROM members GROUP BY job_id HAVING COUNT(*) =2;

例) 職種ごとの平均身長が170cmよりも高い職種とその職種ごとの平均身長を取得
SELECT job_id AS "職種", AVG(height) AS "平均身長" FROM members GROUP BY job_id HAVING AVG(height) >170;

JOIN句

テーブルを結合すのにはJOINを用いる
結合の種類はいくつかある。（今回はINNER JOINを使用）

基本

SELECT カラム FROM テーブル１ INNER JOIN テーブル２ ON 結合条件;

例) memberテーブルのjob_idとjobテーブルのidを紐付けて結合する
SELECT * FROM members INNER JOIN jobs ON jobs.id = members.job_id;
// テーブル結合だから(*)アスタリスク
// jobs(テーブル).(の)id(idと) = members(テーブル).(の)job_id(を紐付ける)

例) 名前と年収をそれぞれ取得する
SELECT members.name, jobs.salary AS "年収（万）" FROM members INNER JOIN jobs ON jobs.id = members.job_id;

やってみよう

例) 年収1000万以上の人の名前と年収を年収の高い順に取得してみよう
SELECT members.name, jobs.salary AS "年収" FROM members INNER JOIN jobs ON jobs.id = members.job_id WHERE jobs.salary >=1000  ORDER BY jobs.salary DESC;
// GROUP BY使ってないからHAVINGではなくてWHEREをつかう

---

最終的に書いてたSQLのコード（保存できないためコピーして残す）

-- 定義部分
CREATE TABLE members
(
id INTEGER NOT NULL,
name CHAR(32),
height REAL,
weight REAL,
age INTEGER,
job_id INTEGER,
PRIMARY KEY (id)
)
;

CREATE TABLE jobs
(
id INTEGER NOT NULL,
name CHAR(32),
salary INTEGER,
PRIMARY KEY (id)
)
;

-- 入力部分
INSERT
INTO members(id, name, height, weight, age, job_id)
VALUES
(1, '佐藤', 170.2, 65.2, 60, 4),
(2, '鈴木', 151.5, 50.3, 53, 2),
(3, '高橋', 182.1, 85.1, 31, 8),
(4, '田中', 163.5, 70.6, 36, 3),
(5, '渡辺', 157.8, 55.8, 62, 7),
(6, '伊藤', 173.0, 65.3, 75, 1),
(7, '山本', 166.4, 49.1, 25, 5),
(8, '中村', 144.1, 56.9, 45, 7),
(9, '小林', 168.7, 90.1, 38, 3),
(10, '加藤', 178.6, 78.5, 26, 6)
;

INSERT
INTO jobs(id, name, salary)
VALUES
(1, '医師', 1232),
(2, '弁護士', 1028),
(3, 'SE', 515),
(4, '会計士', 1024),
(5, '薬剤師', 542),
(6, '保育士', 341),
(7, '大学教授', 1050),
(8, '塾講師', 361)
;

-- SELECT name, salary FROM jobs;

-- SELECT height / 100, '2018-04-01', '吉田' FROM members;

-- SELECT name FROM members WHERE height >= 180;

-- SELECT name FROM members WHERE height >= 170 AND weight <=70;

-- SELECT name FROM members WHERE height >= 180 OR height <= 160;

-- SELECT COUNT(*) FROM members WHERE age >=50;

-- SELECT COUNT(*) AS '50歳以上の人' FROM members WHERE age >= 50;

-- SELECT height / 100 AS height_m, '2018-04-01' AS "測定日", '吉田' AS "測定者" FROM members;

-- SELECT COUNT(*) AS "30歳以上かつ170cm以上の人数" FROM members WHERE age >=30 AND height >=170;

-- SELECT name, age FROM members ORDER BY age;

-- SELECT name, age FROM members ORDER BY age DESC;

-- SELECT * FROM members ORDER BY id;

-- SELECT name AS'名前', age AS "年齢", height AS "身長" FROM members WHERE height >= 170 ORDER BY age DESC;

-- SELECT job_id, COUNT(*) FROM members GROUP BY job_id;

SELECT job_id, AVG(height) AS"平均身長" FROM members GROUP BY job_id;

-- SELECT job_id AS "職種", AVG(height) AS "平均身長" FROM members GROUP BY job_id HAVING AVG(height) > 170;

-- SELECT * FROM members INNER JOIN jobs ON jobs.id = members.job_id;

-- SELECT members.name, jobs.salary AS "年収（万）" FROM members INNER JOIN jobs ON jobs.id = members.job_id;

-- SELECT members.name, jobs.salary AS "年収" FROM members INNER JOIN jobs ON jobs.id = members.job_id WHERE jobs.salary >=1000 ORDER BY jobs.salary DESC;

// 演習問題　セクション１０

1. 体重が50kg以上かつ年収が500万円以上かつ年齢が40歳未満の人の名前を取得
   SELECT members.name FROM members INNER JOIN jobs ON jobs.id = members.job_id WHERE members.weight >= 50 AND jobs.salary => 500 AND members.age < 40;

2. BMIが22以下の人の名前、身長、体重、BMIをBMIが低い順に取得
   SELECT name AS "名前", height AS "身長", weight "体重", weight / POW(height,2) *10000 AS "BMI" FROM members WHERE weight / POW(height,2) *10000 <= 22 ORDER BY weight / POW(height,2)*10000;
   // 最後のだけBMIでもいいらしい。やめとこ🥺

INSERT

テーブルにレコードを追加するのに用いる命令文

基本

INSERT INTO テーブル名(カラム1, カラム2, ...)VALUES(値1, 値2,...);

例) membersテーブルのid11に吉田さんを追加する
INSERT INTO members(id,name,height,weight, age,job_id) VALUES(11,"吉田", 186.2,97.1,23,5);
SELECT * FROM members;

例) jobsテーブルに新しい職業を追加してみよう（id=9であればその他は自由）
INSERT INTO jobs(id,name,salary) VALUES(9, "女優", 8000);
SELECT * FROM jobs;

UPDATE

テーブル内のデータを更新する命令文

基本

UPDATE テーブル名　SET カラムと値の指定;

例) すべてのレコードのageを30にする
UPDATE members SET age =30;
SELECT * FROM members;

でもあんまり全員の値変えるってないよね🥺

特定の値を変えたい時はWHEREをつかう！！！

応用

// 基本構文
UPDATE テーブル名 SET カラム = 値 WHERE 条件式;
// さっきの例だと.....
UPDATE members SET age = 30 WHERE id =1;

練習) 佐藤さんの身長を172.1cmにする
UPDATE members SET height = 172.1 WHERE id=1;
SELECT * FROM members;

応用

// 今入ってる数に指定した数を増加させることも可能
例) membersの全員の年齢を１歳増やす
UPDATE members SET age = age + 1;
SELECT * FROM members;

例) 鈴木さんの身長を１増やし、体重を１０追加させる
UPDATE members SET height = height +1, weight = weight + 10 WHERE id =2;
SELECT * FROM members;

例) 塾講師の年収を100万増やす
UPDATE jobs SET salary = salary + 100 WHERE id=8;
SELECT * FROM jobs;

DELETE

テーブル内のレコードを削除する

基本

DELETE FROM テーブル名

例) membersテーブル削除
DELETE FROM members;

// 特定のレコードのみ削除
DELETE FROM テーブル名 WHERE 条件式;

例) 佐藤さん(id=1)の情報を消す
DELETE FROM members WHERE id =1;
SELECT * FROM members;

例) 伊藤さん(id=6)よりidが大きい人のレコード削除
DELETE FROM members WHERE id >6;
SELECT * FROM members;

例) 身長が180cm以上または体重が50kg以下のレコードを削除する
DELETE FROM members WHERE height >= 180 OR weight <= 50;
SELECT * FROM members;

INSERT UPDATE DELETEの演習コード

-- 定義部分
CREATE TABLE members
(
id INTEGER NOT NULL,
name CHAR(32),
height REAL,
weight REAL,
age INTEGER,
job_id INTEGER,
PRIMARY KEY (id)
)
;

CREATE TABLE jobs
(
id INTEGER NOT NULL,
name CHAR(32),
salary INTEGER,
PRIMARY KEY (id)
)
;

-- 入力部分
INSERT
INTO members(id, name, height, weight, age, job_id)
VALUES
(1, '佐藤', 170.2, 65.2, 60, 4),
(2, '鈴木', 151.5, 50.3, 53, 2),
(3, '高橋', 182.1, 85.1, 31, 8),
(4, '田中', 163.5, 70.6, 36, 3),
(5, '渡辺', 157.8, 55.8, 62, 7),
(6, '伊藤', 173.0, 65.3, 75, 1),
(7, '山本', 166.4, 49.1, 25, 5),
(8, '中村', 144.1, 56.9, 45, 7),
(9, '小林', 168.7, 90.1, 38, 3),
(10, '加藤', 178.6, 78.5, 26, 6)
;

INSERT
INTO jobs(id, name, salary)
VALUES
(1, '医師', 1232),
(2, '弁護士', 1028),
(3, 'SE', 515),
(4, '会計士', 1024),
(5, '薬剤師', 542),
(6, '保育士', 341),
(7, '大学教授', 1050),
(8, '塾講師', 361)
;

// INSERT 演習
-- INSERT INTO members(id,name,height,weight, age,job_id) VALUES(11,"吉田", 186.2,97.1,23,5);
-- SELECT * FROM members;
.
.
-- INSERT INTO jobs(id,name,salary) VALUES(9, "女優", 8000);
-- SELECT * FROM jobs;
.
.
.

// UPDATE 演習
-- UPDATE members SET age =30;
-- SELECT * FROM members;
.
-- UPDATE members SET age = 30 WHERE id =1;
-- SELECT * FROM members;
.
-- UPDATE members SET height = 172.1 WHERE id=1;
-- SELECT * FROM members;
.
-- UPDATE members SET age = age + 1;
-- SELECT * FROM members;
.
-- UPDATE members SET height = height +1, weight = weight + 10 WHERE id =2;
-- SELECT * FROM members;
.
-- UPDATE jobs SET salary = salary + 100 WHERE id=8;
-- SELECT * FROM jobs;
.
.
.
// DELETE

.
-- DELETE FROM members WHERE id =1;
-- SELECT * FROM members;
.
-- DELETE FROM members WHERE id >6;
-- SELECT * FROM members;
.
-- DELETE FROM members WHERE height >= 180 OR weight <= 50;
-- SELECT * FROM members;

---

量が多くなったから前半終了🥺🥺💗