MySQL基礎を勉強しよう

学習教材

MySQL

MySQLとは

データベース(DB)管理システム。アプリのデータを表で管理している

データベース用語

table（テーブル）

record （レコード）

column　（カラム）

テーブルを作ろう

-- CREATE TABLE +　テーブル名
CREATE TABLE posts (message VARCHAR(140), likes INT);

-- DESC　→　テーブルの構造の確認
DESC posts;

-- テーブルの一覧を確認できる
SHOW TABLES;

テーブル名は複数形。カラムにはデータ型を指定する必要がある

※データベース内に同じ名前のテーブルがあるよのエラーを対処する過程で、postssテーブルとaaaaaテーブルも作成しちゃいました

テーブルの削除とレコードの挿入方法

-- IF EXISTS → postsが存在する時だけ削除する
DROP TABLE IF EXISTS posts;

-- レコードの挿入
INSERT INTO posts (message,likes) VALUES
  ('Thanks', 12),
  ('Arigato', 4);

-- SELECT * FROM + テーブル名 → 挿入したレコードの確認
SELECT * FROM posts;

データ型を見る

⭐️MySQLでよく使われるデータ型の解説

数値・文字列を扱おう

CREATE TABLE posts (
  message VARCHAR(140),
  -- いいね数はマイナスを無くし、扱える数値量を増やす
  likes INT UNSIGNED,
  -- DECIMAL(全体で４桁、小数点は２桁)
  mood DECIMAL(4, 2) UNSIGNED,
  -- 言語コードを指定文字数で保持する
  lang CHAR(2)
);

INSERT INTO posts (message, likes, mood,lang) VALUES
  ('Thanks', 12, 3.1234, 'EN'),
  ('Arigato', 4, 2.4567, 'JA');

ENUM型

カテゴリにない文字列を格納

-- 特定の文字列の中から1つだけ格納できる
  category ENUM('Gadget', 'Game', 'Business')

  INSERT INTO posts (message, likes, vagegory) VALUES
  ('Thanks', 12, 'Gadget'),
  ('Arigato', 4, 'Game'),
  -- カテゴリにないFashionを格納してみる
  ('Merci', 4, 'Fashion')

エラー

カテゴリにある文字列を格納

category ENUM('Gadget', 'Game', 'Business')

  INSERT INTO posts (message, likes, vagegory) VALUES
  ('Thanks', 12, 'Gadget'),
  ('Arigato', 4, 'Game'),
  ('Merci', 4, 'Business')

カテゴリの文字列を1から始まるindexでも格納できる

category ENUM('Gadget', 'Game', 'Business')

INSERT INTO posts (message, likes, category) VALUES
  ('Thanks', 12, 1),
  ('Arigato', 4, 2),
  ('Merci', 4, 3);

※特定の文字列から一つだけしか格納できない

SET型

categories SET('Gadget', 'Game', 'Business')

INSERT INTO posts (message, likes, categories) VALUES
  ('Thanks', 12, 'Gadget,Game'),
  ('Arigato', 4, 'Business'),
  ('Merci', 4, 'Business,Gadget');

SET型は内部的に値を数値で管理している

-- 左から 2^0 2^1 2^2...
categories SET('Gadget', 'Game', 'Business')

INSERT INTO posts (message, likes, categories) VALUES
  ('Thanks', 12, 3),
  ('Arigato', 4, 4),
  ('Merci', 4, 5);

真偽値・日時

-- 真偽値
is_draft BOOL,
-- 日時
created DATETIME

INSERT INTO posts (message, likes, is_draft,created) VALUES
('Thanks', 12, TRUE, '2020-10-11 15:32:05'),
-- 時間を省略すると00:00:00になる
('Arigato', 4, FALSE, '2020-10-12'),
-- NOW()で現在時刻を表示
('Merci', 4, 0, NOW());

NULLの扱い

INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Arigato', 4),
  ('Merci', 4);
  
  -- レコードの挿入は全てのカラムに値を設定しなくてもいい
  INSERT INTO posts (message) VALUES ('Gracisa');

値を設定していないとNULLという特殊な値になる

値が設定されていないとエラーにしたい場合

  -- 値が設定されていないとエラーにする設定
  likes INT NOT NULL

エラー文

値が設定されていないとデフォルト値を設定する

  -- 値が設定されていない場合、デフォルト値を０で設定
  likes INT DEFAULT 0

値に制限をつける

-- 重複した値を入れたらエラーにする設定
  message VARCHAR(140) UNIQUE,
  
  -- いいねの数を０以上１００未満に設定
  likes INT CHECK (likes >= 0 AND likes <= 100 )

  INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Arigato', 4),
  ('Merci', 154),
  ('Arigato', 4);

主キーを設定しよう

テーブルは特定のレコードを処理するために、そのレコードを一位に識別するためのカラムを設定するのが一般的

-- idという名前でNULLではない番号の連番にする
  id INT NOT NULL,

-- idをPRIMARYKEY(主キー)にすることができる
  PRIMARY KEY (id)

主キーにしておくとidの値をうっかり入れ忘れたり、値が重複していた時にエラーにしてくれるメリットがある

連番(id)を自動で振ってみよう

id INT NOT NULL AUTO_INCREMENT,

INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Arigato', 4),
  ('Merci', 4);

※自動的に連番にしてくれるので連番にしたくない時は使えない

SELECTでデータを抽出

INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Arigato', 4),
  ('Merci', 4),
  ('Gracias', 15),
  ('Danke', 23);

-- 全てのレコードを抽出してね文
SELECT * FROM posts;

-- idとmessageだけ抽出してね文
SELECT id, message FROM posts;

-- 条件にあるレコードだけを抽出したい文
SELECT * FROM posts WHERE likes >= 10;

条件分岐

〜より大きい（>）
〜以上（>=）　
〜より小さい（<）　
〜以下（<=）　
〜と等しい（＝）　
〜と等しくない（！＝ or <>）　

INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Arigato', 4),
  ('Merci', 4),
  ('Gracias', 15),
  ('Danke', 23);

-- メッセージが'Danke'のを抽出しなさい
SELECT * FROM posts WHERE message = 'Danke';

-- メッセージが'Danke'じゃないものを抽出しなさい
SELECT * FROM posts WHERE message != 'Danke';
SELECT * FROM posts WHERE message <> 'Danke';

条件を組み合わせよう

AND なおかつ

-- AND なおかつ

-- likesが10以上なおかつ20以下のレコードを抽出
SELECT * FROM posts WHERE likes >= 10 AND likes <= 20;

-- '〜以上なおかつ〜以下'という条件に限る特殊な書き方
SELECT * FROM posts WHERE likes BETWEEN 10 AND 20;

-- BETWEENの条件を反転させる書き方
SELECT * FROM posts WHERE likes NOT BETWEEN 10 AND 20;

OR もしくは

-- OR もしくは

--likesが4もしくは12のレコードを抽出したい
SELECT * FROM posts WHERE likes = 4 OR likes = 12;

--'=で判定する条件をORで繋ぐ'場合の特殊な書き方
SELECT * FROM posts WHERE likes IN ( 4, 12 );

--INを反転されたい場合
SELECT * FROM posts WHERE likes NOT IN ( 4, 12 );

LIKEと％で文字列を抽出

--文字列の抽出

--完全一致なら'='でOK
SELECT * FROM posts WHERE message = 'Gracias';

-- % : 0文字以上の任意の文字

-- % を使えば前方一致
--'T'から始まるメッセージだけを抽出する
SELECT * FROM posts WHERE message LIKE 't%';

--大文字小文字を区別するには'BINARY'を使う
SELECT * FROM posts WHERE message LIKE BINARY 't%';

-- 'su'で終わるという条件
SELECT * FROM posts WHERE message LIKE '%su';
-- 'i'を含むという条件
SELECT * FROM posts WHERE message LIKE '%i%';

-- _ : 任意の1文字

--任意の1文字が二つ続いてその次がaで、そのあとが2文字以上の任意の文字
SELECT * FROM posts WHERE message LIKE '__a%';

3文字目が'a'の2文字以上のレコード

--反転させる
SELECT * FROM posts WHERE message NOT LIKE '__a%';

3文字目が'a'以外のレコード

--'%'が入ったレコードを抽出
SELECT * FROM posts WHERE message LIKE '%\%%';

--'_'が入ったレコードを抽出
SELECT * FROM posts WHERE message LIKE '%\_%';

NULLのレコードを抽出

一旦全部のレコードを抽出

INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Arigato', 4),
  ('Merci', NULL),
  ('Gracias', 15),
  ('Danke', NULL);

SELECt * FROM posts;

likesが12以外のレコードを抽出

SELECT * FROM posts WHERE likes != 12;

likesが12の文字とNULLの文字が表示されていない

NULLも含ませたい

SELECT * FROM posts WHERE likes != 12 OR likes IS NULL;

NULL以外のレコードの抽出

SELECT * FROM posts WHERE likes IS NOT NULL;

抽出結果の並び替え

-- likesを小さい順に並び替える
SELECT * FROM posts ORDER BY likes;
  
-- 大きい順
SELECT * FROM posts ORDER BY likes DESC;

--アルファベット順
SELECT * FROM posts ORDER BY likes DESC,  message;
  
--上位三件だけ表示
SELECT * FROM posts ORDER BY likes DESC,message LIMIT 3;

数値の関数

-- 1いいねで五百円の報酬、もらえるのは3分の１のみ
SELECT likes * 500 /3 FROM posts;

--表示名の変更
SELECT likes * 500 /3 AS bonus FROM posts;

--数値の端数を処理
SELECT 
  likes * 500 /3 AS bonus 
  -- 端数を切り捨てる
  FLOOR (likes * 500 /3)AS floor,
  -- 端数を切り上げたい
  CEIL(likes * 500 /3)AS ceil,
  -- 四捨五入
  ROUND(likes * 500 /3)AS round,
FROM
  posts;

文字列の関数

文字列の加工をする関数

  -- 文字列の一部を切り出す関数
  
  -- 3文字目以降を切り出す
  SELECT message, SUBSTRING(message, 3) FROM posts;
  -- 3文字目から2文字分を切り出す
  SELECT message, SUBSTRING(message, 3, 2) FROM posts;
  -- 末尾から〇〇文字分〜
  SELECT message, SUBSTRING(message, -2) FROM posts;

  -- messageとlikesを'-'で連結したい
  SELECT CONCAT(message, ' - ', likes) FROM posts;

  -- 文字数をlength関数で求める
  SELECT message, LENGTH(message) AS len FROM posts;
  -- ※length関数は日本語に使うとバグる
  
  -- 日本語が含まれるなら
  SELECT message, CHAR_LENGTH(message) AS len FROM posts;

日時の関数

  -- createdから年と月を引き出す
  SELECT created, YEAR(created) FROM posts;
  SELECT created, MONTH(created) FROM posts;
  SELECT created, DAY(created) FROM posts;

  -- 好きなフォーマットで抽出したい
  SELECT 
    created,
    DATE_FORMAT(created, '%M %D %Y %W') AS date
  FROM
    posts;

日付の計算

  -- createdから7日後を表示したい
  SELECT 
    created,
    DATE_ADD(created, INTERVAL 7 DAY) AS next
  FROM
    posts;

  -- 現在の日付よりどれくらい前から知りたい場合
  SELECT 
    created,
    NOW(),
    DATEDIFF(created,NOW()) AS diff
  FROM
    posts;

レコードの更新

  -- レコード（元データ）を更新する
  -- likesが１０以上のだけ＋5する条件を付ける
  UPDATE posts SET likes = likes + 5 WHERE likes >= 10;
  SELECT * FROM posts;

-- 複数のカラムの値を一気に更新
UPDATE
  posts
SET
  likes = likes + 5,
  -- 全て大文字にする関数
  message = UPPER(message)
WHERE
  likes >= 10;
SELECT * FROM posts;

レコードの削除

-- 全て削除する
DELETE FROM posts;
  
-- likesが１０以下を全て削除する(通常は条件を付ける)
DELETE FROM posts WHERE likes < 10;

-- 連番を最初から振り直すにはテーブルごと削除して再作成する
TRUNCATE TABLE posts;

作成・更新日時を自動で設定

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140),
  likes INT,
  -- レコードの作成日時や更新日時を自動設定する
  
  -- レコードが挿入された時点の日時
  created DATETIME DEFAULT NOW(),
  
  -- レコードが更新された時、その時点の日時を自動で更新する
  updated DATETIME DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id)
);

INSERT INTO posts (message, likes) VALUES
  ('Thanks', 12),
  ('Merci', 4),
  ('Arigato', 4),
  ('Gracias', 15),
  ('Danke', 8);
  
SELECT id, created, updated FROM posts;

-- SLEEPという命令を使って3秒待つ
SELECT SLEEP (3);

-- idが1のレコードだけ更新
UPDATE posts SET likes = 100 WHERE id = 1;

SELECT id, created, updated FROM posts;

テーブルの設計を変更する

-- 作成したテーブルを後から変更する場合
-- authorというカラムを後から追加したい場合
ALTER TABLE posts ADD author VARCHAR(255);
DESC posts;

-- authorをidの後に追加したい場合
ALTER TABLE posts ADD author VARCHAR(255) AFTER id;
DESC posts;

-- 最初に入れる
ALTER TABLE posts ADD author VARCHAR(255) FIRST;

-- 後からカラムを削除
ALTER TABLE posts DROP message;

-- likesをpointsに変更したい(カラムの変更)
ALTER TABLE posts CHANGE likes points INT;

-- テーブルの名前を変更
DROP TABLE IF EXISTS messages;
ALTER TABLE posts RENAME messages;
SHOW TABLES;