otaku-tokyo

 

SQL

SQLとは

データベースを扱うための言語

データを取得する

SELECT * FROM purchases;

特定のデータを取得する

データ型には「テキストデータ」「日付データ」「数値データ」が存在し、数値データはクォーテーションで囲まない

WHERE NOT カラム名=”食費”;

WHERE カラム名 <=”2020-07-01″;(7月1日以前)

WHERE カラム名 >=1000;

※7月1日以前は7月1日も含む

”プリン”を含むデータを取得する

WHERE name = “プリン”;だとプリンは取得出来ても、焼きプリンは取得できないので次のようにする。(ワイルドカード)

WHERE name LIKE “%プリン”;

プリン、焼きプリンだけでなくプリンアラモードも取得したいなら次のようにする。

WHERE name LIKE “%プリン%”;

空のデータ

=ではなくISを用いる。NOTの場所に注意すること

WHERE price IS NULL;

WHERE price IS NOT NULL;

倫理演算子

WHERE name = “” OR name=””;

WHERE name = “” AND name=””;(かつ)

データの並び替え

ORDER BY price DESC;(降順/大きい順)

ORDER BY price ASC;(昇順/小さい順)

データの件数

LIMITはクエリの最後に記述すること

LIMIT 5;(取得するデータの件数)

重複データの除去

SELECT DISTINCT(name) FROM purchases;

集計関数

SELECT name, price * 1.10 FROM purchases;

SELECT SUM(price) FROM purchases;

SELECT AVG(price) FROM purchases;(平均)

SELECT MAX(price) FROM purchases;

SELECT MIN(price) FROM purchases;

カラム名を指定するとpriceの値はNULLのデータはCOUNTされない。

SELECT COUNT(price) FROM purchases;

NULLのデータも数える場合は*を利用する。

SELECT COUNT(*) FROM purchases;

GROUP BY

SELECTで使えるのは集計関数とGROUP BYに指定しているカラム名のみ

SELECT SUM(price), purchased_at FROM purchases GROUP BY purchased_at;

次のようには使えない。

×SELECT price,purchased_at FROM purchases GROUP BY purchased_at;

GROUP BYには複数のカラムを指定可能。また、WHEREを使う場合はGROUP BYより前に書くこと

SELECT SUM(price),purchased_at, character_name

FROM purchases

WHERE category =”食費”

GROUP BY purchased_at, character_name;

グループ化されたデータの中から更に絞りたい場合はGROUP BYのあとにHAVINGを用いる

SELECT SUM(price),purchased_at

FROM purchases

GROUP BY purchased_at

HAVING SUM(price) > 1000;

サブクエリ

()内にセミコロンは不要

SELECT name

FROM players

WHERE goals > (

SELECT goals FROM players WHERE name = “ウィル”

);

取得したカラムの名前を指定

SELECT SUM(goals) AS “チームの合計得点” FROM players;

複数のテーブルを結合する

JOINとONを使う。SELECTやWHERE、ONなどではplayers.nameのようにテーブル名を指定できる。

ONはGROUP BYの前で使うこと

SELECT *

FROM players(テーブルA)

JOIN countries (テーブルB)

ON players.country_id = countries.id (結合条件)

(GROUP BY ~);

テーブルを結合する際、NULLのレコードは実行結果に表示されないので表示したい場合はJOIN ONではなくLEFT JOIN ONとすること

3つ以上のテーブルを結合する場合はつぎのようにする

SELECT * FROM players

JOIN countries ON players.country_id=countries.id

JOIN teams ON players.previous_team_id=teams.id;

レコードを挿入する

INSERT INTO students (name,course) VALUES(“Kate”,”Java”);

データを更新する

WHEREで指定しないと全データが更新されてしまうので注意

実行後にデータを戻すことはできない

UPDATE students

SET name = “Jordan”, course = “HTML”

WHERE id = 6;

データを消去する

WHEREで指定しないと全データが更新されてしまうので注意

実行後にデータを戻すことはできない

DELETE FROM students WHERE id = 7;

MySQLの開発環境を用意する

コマンドプロンプトで次のコマンドを実行しMySQLがインストールされているか確認する。

mysql –version

インストールされていない場合は公式サイトからダウンロードする。64bit版を使っていても32bit版をダウンロードすること。

ダウンロードが完了したら以下画像の手順に従ってインストールを進める。

MySQLをインストールしたら、コマンドプロンプトからMySQLコマンドを使えるようにする。

検索バーに「環境変数」と入力して「システム環境変数の編集」を開く。

上段にある「Path」を選択し「編集」ボタンをクリック

以下の部分を「MySQL Server 5.7」と書き直して、編集ウィンドウと環境変数ウィンドウを閉じる。

変更を適用させるためにコマンドプロンプトを一度閉じ、再度コマンドプロンプトを管理者として実行から開きなおす。以下のコマンドを実行し環境変数が変更されたことを確認する。

mysql –version

以下のコマンドでMySQLを起動させる。起動に成功した場合は「MySQL57サービスは正常に開始されました。」と表示、既に起動している場合は「要求したサービスはすでに開始されています。」と表示される。

net start mysql57

起動出来たらrootユーザー(=管理者)としてログイン

mysql –user=root –password

コマンド実行後Enter password:と表示されるのでパスワードを入力しEnterを押すと以下のような画面になる。

ログアウトしてからMySQLを停止させる。

exit;
net stop mysql57

「MySQL57サービスは正常に停止されました。」と表示されればOK。

データベースを作成する

まず、次のコマンドでMySQLを起動してログイン

net start mysql57
mysql –user=root –password

そして、データベースを作成

CREATE DATABASE progate(データベース名);

データベースの一覧を見て先ほど作ったデータベースが追加されていたらデータベースの作成は完了

SHOW database;

※(information_schema,mysql,performance_schema,sys)以外にprogateが作成されていればOK

テーブルの作成

まず、データベースを選択

USE progate;

次に、データベース内に存在するテーブルの一覧を確認

SHOW tables;

テーブルが存在しないので「Empty set(0.00 sec)」と表示される。そして、テーブルを作成

CREATE TABLE users (id INT AUTO_INCREMENT, name TEXT, PRIMARY KEY (id)) DEFAULT CHARSET=utf8;

※usersは作成するテーブルの名前

※id INT AUTO_INCREMENTはカラム名、データタイプ、オプションの順に指定

※PRIMARY KEY (id)でプライマリーキーをidに設定。プライマリーキーに設定されたカラムには重複する値を挿入できなくなる。プライマリーキーのカラムには必ずauto_incrementも必要。

※DEFAULT CHARSET=utf8は文字コードをutf8に変更する

もう一度SHOW tables;し先ほど作ったテーブルを確認

カラムを追加・変更・削除する

ALTER TABLE users(テーブル名) ADD COLUMN stock(カラム名) INT(データ型);

※INTは整数

ALTER TABLE users CHANGE COLUMN price(前のカラム名) cost(新しいカラム名) INT(新しいデータ型);

削除はこれ👇

ALTER TABLE users DROP COLUMN category;

テーブル情報を確認する

データ型やプライマリーキーを確認する場合は次のコマンドを実行

DESCRIBE users(テーブル名);

テーブルの削除

DROP TABLE users(削除するテーブル名);

削除後にもう一度SHOW tables;クエリを実行するとテーブルがなくなっているはず。

データベースの削除

DROP DATABASE progate(削除するデータベース名);

削除後にSHOW database;で確認する