本記事と誰でもすぐわかるデータベース基礎の基礎~は【学習計画】十週間で知識ゼロからのデータ分析入門の第三週の学習内容です。今後この学習計画について更新していきます。
この前はデータベースの定義とRDBMSのMySQLのインストール方法について説明しましたが、今日はデータベースの操作とSQLについて説明します。
SQL(Structured Query Language)はRDMBSにおいて、データベースの定義や操作を行うためのデータベース言語です。
(出典: フリー百科事典『ウィキペディア(Wikipedia)』)
ウィキペディアの定義通り、リレーショナルデータベース、とその中のテーブル、データの参照、変更、削除、計算などを行うために、SQLが必要です。
Oracle、DB2、Sybase、SQLServer、MySQL、MS Accessなどのデータベースは、SQLの構文が違いますが、基本的には同じです。市場に多くのセルフサービスのBIツールがあって、ドラッグアンドドロップだけでデータ分析ができるにもかかわらず、データエンジニアまたはデータアナリストとして、少しでもSQLを理解しなければいけません。
市場にSQLに関する書籍やオンラインチュートリアルが増えているので、ここは、ただその学習の要点をまとめて共有したいです。
この記事は主に以下の方面から説明されています、ご参考までにどうぞ~
1.SQLクイック入門
2.データベースの基本操作
3.テーブルの基本操作
4.データ検索クエリ
###1.SQLクイック入門
知識ゼロからSQLを独学する際、よく2つの問題があります。
####問題①:
SQL SeverかMySQLを学ぶ前にデータベースのソフトをインストールする必要があります。初心者にとって、データベースソフトのインストールは時間がかかります。最初仕事のトレーニングを受けた時、2日間SQLをマスターすることが要求されました。結局、SQL Severのインストールにほぼ1日かかってしまいました。前の記事でそれほど複雑ではないMySQLのインストールを紹介しました。
####問題②:
最初SQL構文をたくさん暗記して、でも実際に使う時何もできません。私からいうと、練習しながら覚えることは一番効率的な方法です。 Excelの関数と同じように、その意味と使い方を大体理解した上で、実際使う時にW3cschools(オンライン学習サイト)で調べれば、だんだん身に着けるようになります。
####SQL学習
#####方法①:
MySQLをダウンロードしてインストールしてからビルトインのデータベースを利用して、W3cschoolsに従って練習します。(英語が苦手な方は言語で日本語を選択してください。)
#####方法②:
無料のオンラインコースを勉強します。二つのサイトをお勧めします。
このサイトは昔は英語と中国語のバージョンしかありませんが、今一部の内容に日本語のバージョンも利用できます。練習問題と段階的なクイズが用意されており、SQL文を入力してその実行結果を見ることができます。また、覚えない時、Referenceで調べられます。
Progate
SQL Tutorialと同じく、SQL文を入力してその実行結果を見ることができる一方、解説はとても詳しくわかりやすくて、練習問題を解く際、入力欄や実行結果と一緒に見本や解説も見ることができます。
###2.データベースの基本操作
①既存のデータベースを表示する
SHOW DATABASES;
②データベースを新規作成
CREATE DATABASE <database_name>;
③データベースを選択する
USE <database_name>;
④.sqlファイルからSQL文を参照する
SOURCE <.sql file path>;
⑤データベースを削除する
DROP DATABASE <database_name>;
###3.テーブル
①既存のデータベース内のテーブルを表示する
SHOW TABLES;
②テーブルを新規作成する
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
各列のデータタイプ(後述)を指定する必要があります。
- INTEGER 整数
- NUMERIC 任意の精度を持つ数字
- CHAR 固定長の文字列
- VARCHAR 可変長の文字列
- DATE 日付
③テーブル内の列の情報を表示する
DESCRIBE table_name;
④既存のテーブルにレコードを追加する
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
カラム名を省略する場合もあります。それで、値が順番にすべてのカラムに追加されます。
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
⑤既存のテーブル内のレコードを変更する
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
⑥テーブル内のレコードを削除する
DELETE FROM table_name
WHERE condition;
⑦テーブルを削除する
DROP TABLE table_name;
⑧既存のテーブルにカラムを追加、削除、または変更する
- カラムを追加する
ALTER TABLE table_name
ADD column_name datatype;
- カラムを削除する
ALTER TABLE table_name
DROP COLUMN column_name;
- カラムを変更する
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
⑨テーブル名を変更する
RENAME TABLE table_name1 to table_name2;
###4.データ検索クエリ
①SELECT
テーブルから指定するカラムのデータを選択する
SELECT column1, column2, ...
FROM table_name;
テーブルのすべてのデータを選択する
SELECT * FROM table_name;
②SELECT DISTINCT
重複値を含めないデータを選択する
SELECT DISTINCT column1, column2, ...
FROM table_name;
③WHERE
先、既存のテーブル内のレコードを変更する時、条件を特定するためのwhereを使いました。
where条件は通常以下のようになります。
- 文字列での比較(text)
- 数値での比較(numbers)
- AND、OR、NOTなどの論理演算
例:
SELECT product_name, product_type
FROM Product;
WHERE product_type = 'clothes';
④GROUP BY
GROUP BYを使うと特定のカラムをキーにした合計値や平均値などが表示される結果となります。一般的に集計関数(COUNT、MAX、MIN、SUM、AVG)と一緒に使用されます。
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s);
- COUNT:テーブル内のレコード数(行数)を計算する
- SUM:テーブルの数値列のデータの合計値を計算する
- AVG:テーブルの数値列のデータの平均値を計算する
- MAX:テーブル内の任意のカラムのデータの最大値を求める
- MIN:テーブル内の任意のカラムのデータの最小値を求める
GROUP BYとWHEREを一緒に使用した場合のSELECT文の実行順序:
FROM → WHERE → GROUP BY → SELECT
例:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
⑤Having
Havingは集計関数と一緒に使って、カラムを集計した結果を特定する条件です。HavingとWHEREを区別しなければいけません。
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
例:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
⑥ORDER BY
ORDER BYは取り出した結果をソートします。ASC(昇順)またはDESC(降順)を指定しないと、既定は昇順になります。
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
⑦BETWEEN
BETWEENはWHEREと一緒に、指定した範囲のレコードを検索するために使用されます。
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
⑧LIKE
LIKEで特定の文字列の検索を行うことができます。
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
LIKEと組み合わせで使用される2つのワイルドカードがあります。
- % —— 0文字以上の任意の文字列
- _ —— 任意の1文字
WHEREと一緒に使って、複数の値を指定することができます。
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
⑩JOIN
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
JOINは、二つ以上のテーブルの値を組み合わせるために使用されます(テーブル間に関連がある場合)。 下図は、4種類のJOINを示しています。
⑪ビュー
ビュー(View)は仮想的なテーブルのことです。 通常のSQLテーブルと同じように、レコードとカラムを含めています。 ビューは常にデータベース内の最新のデータを表示します。
- ビューを作成する
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
- ビューを削除する
DROP VIEW <view_name>;
⑫サブクエリ
既存のテーブルを加工した物からデータを取り出したい場合、サブクエリは役に立ちます。
例えば、2018秋と2019春に開かれるコースを検索する場合:
SELECT DISTINCT course_id FROM section
WHERE semester = 'Fall' AND year= 2018
AND course_id IN(
SELECT course_id
FROM section
WHERE semester = 'Spring' AND year= 2019
);
ツイッターでデータ分析・可視化、仕事のスキルについての面白い情報を発信しています。よかたっら覗いてください!
前はデータ分析職につきたい方のために、十週間の学習計画を紹介しました。ここはインフォグラフィックにまとめてみます!記事と合わせてお読みください!
— HaileeKana (@HaileeKana) July 12, 2020
記事のURL:https://t.co/1wBiLQiRzi @HaileeKana #note https://t.co/wWH0jMBwHb
###おまけに
【学習計画】十週間で知識ゼロからのデータ分析入門シリーズ、今迄の記事: