[前半](My)SQLの概念・基本操作・演習までやってみた。[Paiza]
つ、ついに、、避け続けたSQLに関して、、、、、
今回こそ聞かれたら答えられるくらい、、、理解できるかな、、、、🥺🥺
でもSQLの前にさらっとDBについておさらい🤕
🌟Paizaで練習するのが楽ちん👇👇
DB[補足等]
DBはデータの集まり
DBの利用は直接ユーザーがデータにアクセスするわけではなく、データの要求や取り出しを整理する働きがあるDBMSによって行われる。
DBの種類
- 🌟リレーショナルデータベース(主流)
- 階層型データベース
- オブジェク指向データベース
- キー・バリュー型データストア
など、、他にもいろいろな種類がある!
RDS(リレーショナルデータベース)とは??🙁
Excelのように列(カラム)と行(レコード)からなるテーブル(表)形式で複数データを関連付け、使えるようにしたデータベースのこと
そしてやっとここでSQLとの関連が、、、👇👇
SQLとは
SQLとはRDBMSを操作する言語(げ、言語やったんか、、、、)
よく聞くMySQLはその1種類!(オープンソースやから有名なんね)
PostgreSQLとかもオープンソースで有名
オープンソース:無償(または最低限の必要なコスト)で公開されたプログラムなどのソースコードで、自由に利用や、改変、再配布ができるという特徴がある。
どのRDBMSに対しても使える標準SQLとRDBMS毎に使える特有の方言があるらしい~
つまり標準SQLの文は、MySQLだけでなく、PostgreSQLや他のSQLベースのデータベースシステムでも使える文法です。
コラム:SQL/Pythoon/Rの使い分けの目安
データ分析にはSQL以外にもRやPythoonなどがある。(
<使い分けの目安>
- SQL:データ分析に用いろデータをDBから用意するまで
- R:アドホック(その場限りの)な分析の場合に使用
- Pythoon:処理をシステム化したり、機械学習を用いた分析の場合
SQLがDBからデータを持ってきてデータ分析しやすい形に整えて
R(現状確認など)やPythoon(処理をシステム化して繰り返し使いたい時など)で分析していく
という流れ。にゃるへそ😺
ここから下の例でつかう例
-- 定義部分
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文に書くカラム名には定数や式も書くことが出来る。
身長の単位をm(÷100)で表示・身長測定日・測定者を表示したい場合
SELECT height / 100, '2018-04-01', '吉田' FROM members;
// 文字列や日付を書く場合シングルクォーテーション('')で囲う🥺
WHERE句
特定の条件を満たすレコード(行)のみ取得したい時に使用する。
SELECT カラム FROM テーブル WHERE 条件式;
例) 身長が180cm以上の人の名前を取り出したい
SELECT name FROM members WHERE height >= 180;
複数の条件式を書く場合は 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数字
だけ出力され何の数かわかりにくい。
そこでカラム名に別名を付けることが出来る🙃
⇒ カラム名に別名を付けるには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;
例) 職種ごとの平均身長を取得しよう
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 テーブル1 INNER JOIN テーブル2 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;
// 演習問題 セクション10
-
体重が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; -
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;
でもあんまり全員の値変えるってないよね🥺
// 今入ってる数に指定した数を増加させることも可能
例) membersの全員の年齢を1歳増やす
UPDATE members SET age = age + 1;
SELECT * FROM members;
例) 鈴木さんの身長を1増やし、体重を10追加させる
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;
量が多くなったから前半終了🥺🥺💗
Discussion