MySQL 基礎コマンド

関連記事

• MySQL 関数と応用
• MySQL 正規化と整合性とINDEX
• MySQL CTE

データ型

整数

• TINYINT : -128 ~ +127　　　 例：TINYINT(30) 　( )は扱える最大の数
• INT : -21億 ~ +21億
• BIGINT : -922京 ~ +922京
• TINYINT UNSIGNED : 0 ~ +127
• INT UNSIGNED : 0 ~ +21億
• BIGINT UNSIGNED : 0 ~ +922京

実数

• DECIMAL : 固定小数点　　　例:DECIMAL(5, 2) 第一引数は全体の桁数、第二引数は少数以下の桁数
• FLOAT : 浮動小数点
• DOUBLE : 浮動小数点(高精度)

文字列

• CHAR : 0 ~ 255文字 　　　　例:CHAR(120) ( )は最大文字数
• VARCHAR : 0 ~ 65535文字
• TEXT : それ以上
• ENUM : 特定の文字列から一つ
• SET : 特定の文字列から複数

真偽値

• BOOL : TRUE / FALSE
• TINYINT(1) : 1 / 0

日時

• DATE : 日付
• TIME : 時間
• DATETIME : 日時

用語

-- +----+---------------+-------+  全体を`table`
-- | id | title         | likes |
-- +----+---------------+-------+  1 Kokoro 7 といった行のことを`recode`
-- |  1 | Kokoro        |     7 |
-- |  3 | Ningensikkaku |    14 |  id 1 3 や　title Kokoro Ningensikkaku といった列を`column`
-- +----+---------------+-------+

コメント

# comment
-- comment
/*
comment
*/

テーブルの作成

CREATE TABLE books (
  title VARCHAR(200),
  likes INT
);

テーブルの削除

DROP TABLE IF EXISTS books;

レコードの挿入

INSERT INTO books (title, likes) VALUES
  ('Kokoro', 7), 
  ('Rashomon', 10);

ENUM

特定の文字列から一つ

CREATE TABLE tests (
  subject ENUM('Chemistry', 'Economy', 'Mathematics'),
  score INT
);

INSERT INTO tests (subject, score) VALUES 
  ('Chemistry', 90),
  ('Mathematics', 68);
-- または
-- (1, 90),
-- (3, 68);

SET

特定の文字列から複数
SET( )によるINSERTは文字列で表記する方法と、整数で表記することできる。
整数表記の場合、先頭から2^0の1、2^1の2、2^2の3...

CREATE TABLE tests (
  subjects SET('Chemistry', 'Economy', 'Mathematics'),
  score INT
);

INSERT INTO tests (subjects, score) VALUES 
  -- ('Chemistry', 90),
  -- ('Economy', 58),
  -- ('Chemistry,Economy', 148),
  -- ('Mathematics', 72),
  -- ('Chemistry,Mathematics', 162),
  -- ('Economy,Mathematics', 130),
  -- ('Chemistry,Economy,Mathematics', 220);
-- または上記と同じように出力する場合下記のようになる
(1, 90),-- 第一引数は2^0で'Chemistry'を指定している
(2, 58),-- 2^1で'Economy'
(3, 148),-- この場合は、一つ目2^0と2つ目2^1を足した３で'Chemistry', 'Economy'になる
(4, 72),-- 2^2で'Mathematics'
(5, 162),-- 2^0と2^2を足した５で'Chemistry,Mathematics'
(6, 130),-- 2^1と2^2を足した６で'Economy,Mathematics'
(7, 220);-- 2^0と2^1と2^2を足した７で'Chemistry,Economy,Mathematics'

日時

CREATE TABLE tests (
  created DATETIME
);

INSERT INTO tests (created) VALUES
  ('2021-1-1 12:35:49'),
  ('2021-12-6'),-- 2020-10-12 00:00:00 
  (NOW());-- 現在日時

NULL

CREATE TABLE books (
  title VARCHAR(200),
  likes INT
-- likes INT NOT NULL <- NULLをエラーにしたい場合
-- likes INT DEFAULT 0 <- 指定のない場合の初期値設定
);

INSERT INTO books (title, likes) VALUES
  ('Kokoro', 7), 
  ('Rashomon', 10);

INSERT INTO books (title) VALUES ('Ningensikkaku');
-- 結果
--| Kokoro        |     7 |
--| Rashomon      |    10 |
--| Ningensikkaku |  NULL |

SELECT * FROM books;

CHECK

値に制限をかける

CREATE TABLE books (
-- 0 ~ 9999　以外はエラー
  likes INT CHECK (likes >= 0 AND likes <= 9999)
);

UNIQUE

重複を制限する

CREATE TABLE books (
  title VARCHAR(200)　UNIQUE,
  likes INT
);

INSERT INTO books (title, likes) VALUES
  ('Kokoro', 7), 
  ('Rashomon', 10),
  ('Kokoro', 13);-- 重複しているのでエラー

SELECT * FROM books;

PRIMARY KEY

CREATE TABLE books (
  PRIMARY KEY (id), -- PRIMARY KEY をidとして扱う
  id INT NOT NULL AUTO_INCREMENT, -- PRIMARY KEYを自動で割り当てる
  title VARCHAR(200), 
  likes INT
);

INSERT INTO books (title, likes) VALUES 
  ('Kokoro', 7),
  ('Rashomon', 10);

SELECT * FROM books;
-- 結果
-- +----+----------+-------+
-- | id | title    | likes |
-- +----+----------+-------+
-- |  1 | Kokoro   |     7 |
-- |  2 | Rashomon |    10 |
-- +----+----------+-------+

SELECT

WHEREでSELECTの指定

CREATE TABLE books (
  PRIMARY KEY (id),
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(200), 
  likes INT
);

INSERT INTO books (title, likes) VALUES 
  ('Kokoro', 7),
  ('Rashomon', 10),
  ('Ningensikkaku', 14);

SELECT id, title FROM books WHERE likes >= 10; -- likes10以上のidとtitle
SELECT * FROM books WHERE likes >= 10; -- likes10以上の全て
SELECT * FROM books WHERE title != 'Rashomon'; -- titleが'Rashomon'以外の全て
-- 結果
-- +----+---------------+
-- | id | title         |
-- +----+---------------+
-- |  2 | Rashomon      |
-- |  3 | Ningensikkaku |
-- +----+---------------+
-- +----+---------------+-------+
-- | id | title         | likes |
-- +----+---------------+-------+
-- |  2 | Rashomon      |    10 |
-- |  3 | Ningensikkaku |    14 |
-- +----+---------------+-------+
-- +----+---------------+-------+
-- | id | title         | likes |
-- +----+---------------+-------+
-- |  1 | Kokoro        |     7 |
-- |  3 | Ningensikkaku |    14 |
-- +----+---------------+-------+

-- AND なおかつ
SELECT * FROM books WHERE likes >= 8 AND <= 12; -- likes8以上かつ12以下の全て
SELECT * FROM books WHERE likes BETWEEN 8 AND 12; -- 上記と同意
SELECT * FROM books WHERE likes NOT BETWEEN 8 AND 12; -- 上記の反転

-- OR もしくは
SELECT * FROM books WHERE likes = 7 OR likes = 10; -- likesが7か10の全て
SELECT * FROM books WHERE likes IN (7, 100); -- 上記と同意
SELECT * FROM books WHERE likes NOT IN (7, 100); -- 上記の反転

-- WHEREで指定するとNULLが含まれなくなるので、含ませる場合は`IS NULL`をつける
SELECT * FROM books WHERE likes = 7 OR likes = 10 OR likes IS NULL; 

LIKE

下記で指定したものを取得。
% - 0以上の任意の文字列
_ - 任意の文字

SELECT * FROM books WHERE title LIKE '%a'; -- titleがaまたはAで始まるrecode
SELECT * FROM books WHERE title LIKE BINARY '%a'; -- titleがaで始まるrecode
SELECT * FROM books WHERE title LIKE 'a%'; -- titleがaまたはAで終わるrecode
SELECT * FROM books WHERE title LIKE '%a%'; -- titleにaまたはAを含むrecode
SELECT * FROM books WHERE title LIKE '__a%'; -- 先頭から3番目がaまたはAのrecode
SELECT * FROM books WHERE title LIKE '___a%'; -- 先頭から4番目がaまたはAのrecode
SELECT * FROM books WHERE title. NOT LIKE '___a%'; -- 上記の反転

ORDER BY

並び順指定

SELECT * FROM posts ORDER BY title; -- titleのアルファベット順
SELECT * FROM posts ORDER BY likes; -- likesの小さい順
SELECT * FROM posts ORDER BY likes DESC; -- likesの大きい順
SELECT * FROM posts ORDER BY likes LIMIT 3 -- likesの小さい順に3つだけ
SELECT * FROM posts ORDER BY likes LIMIT 3 OFFSET 2 -- likesの小さい順で0から数えて2つ目から３つだけ
SELECT * FROM posts ORDER BY likes LIMIT 3, 2 -- likesの小さい順に先頭から3つ除外して2つ取得

数値の関数

ASでcolumn名指定

SELECT 
  FLOOR(likes / 3) AS floor, -- 端数の切り捨て
  CEIL(likes / 3) AS ceil, -- 端数の切り上げ
  ROUND(likes / 3) AS round -- 四捨五入
  ROUND(likes / 3, 2) AS round -- 小数点以下の桁数の指定

文字列の関数

SUBSTRING

文字列の中の任意の文字を取得

SELECT title, SUBSTRING(title, 2) FROM books; -- titleの2文字以降を取得
SELECT title, SUBSTRING(title, 2, 3) FROM books; -- title2文字目以降、3文字取得
SELECT title, SUBSTRING(title, -4) FROM books; -- 末尾から4文字取得

CONCAT

columnの連結

SELECT CONCAT(title, likes) FROM books; -- titleとlikesの連結
SELECT title, LENGTH(title) AS len FROM books ORDER BY len; -- タイトルの文字数
SELECT title, CHAR_LENGTH(title) AS len FROM books ORDER BY len; -- 日本語の場合はCHAR_LENGTH

日時の関数

DATE_FORMATマニュアル

CREATE TABLE foo (
  created DATETIME DEFAULT NOW(), -- 指定がない場合は現在の日時
-- updated DATETIME ON UPDATE NOW()　　自動で日時を更新
);

INSERT INTO foo (created) VALUES
 ('2021-01-01');

SELECT created, YEAR(created) FROM foo; -- 2021
SELECT created, MONTH(created) FROM foo; -- 1
SELECT created, DAY(created) FROM foo; -- 1
SELECT created, DATE_FORMAT(created, '%M %D %Y, %W') FROM foo; -- January 1st 2021, Friday 
SELECT created, DATE_ADD(created, INTERVAL 3 DAY) FROM foo; -- 2021-01-04 00:00:00
SELECT created, DATEDIFF(created, '2021-1-11') FROM foo; -- -10 二つの日時の差を計算する

recodeの更新

CREATE TABLE books (
  PRIMARY KEY (id),
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(200), 
  likes INT
);

INSERT INTO books (title, likes) VALUES 
  ('Kokoro', 7),
  ('Rashomon', 10),
  ('Ningensikkaku', 14);

-- likesが10以上のrecodeのlikesを+5してtitleを大文字に変換
UPDATE 
  books 
SET 
  likes = likes + 5,
  title = UPPER(title)
WHERE 
  likes >= 10;

SELECT * FROM books;

recodeの削除

recodeを削除して新しいrecodeを挿入してもPRIMARY KEYは上書きされない。

DELETE FROM books WHERE likes < 10; -- likesが10未満のrecodeを削除

テーブルの削除

TRUNCATE TABLE books;

テーブルの設計変更

CREATE TABLE books (
  PRIMARY KEY (id),
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(200), 
  likes INT
);

ALTER TABLE books ADD author CHAR(50); -- カラムの最後にauthorを追加
ALTER TABLE books ADD author CHAR(50) AFTER id; --idの後にauthorを追加
ALTER TABLE books ADD author CHAR(50) FIRST; -- カラムの先頭にauthorを追加
ALTER TABLE books DROPS title; -- titleを削除
ALTER TABLE books CHANGE likes price INT; -- likesをpliceに変更
ALTER TABLE books RENAME foo; -- テーブル名をfooに変更