MySQL 基礎コマンド

###関連記事

- [MySQL 関数と応用](https://qiita.com/zgoovw_/items/9d64cc22b66696ceb1af)
- [MySQL 正規化と整合性とINDEX](https://qiita.com/zgoovw_/items/542b98ca1d2b74ff84d4)
- [MySQL CTE](https://qiita.com/zgoovw_/items/baa2d062cd0ef7203dab)

#データ型

###整数

- 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 : 日時

##用語

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

##コメント

```sql
# comment
-- comment
/*
comment
*/
```

#テーブルの作成

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

#テーブルの削除

```sql
DROP TABLE IF EXISTS books;
```

##レコードの挿入

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

##ENUM
特定の文字列から一つ

```sql
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`... 

```sql
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'
```

##日時

```sql
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

```sql
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
値に制限をかける

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

##UNIQUE
重複を制限する

```sql
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

```sql
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の指定

```sql
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以上の任意の文字列
`_` - 任意の文字

```sql
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
並び順指定

```sql
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名指定

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

##文字列の関数
###SUBSTRING
文字列の中の任意の文字を取得

```sql
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の連結

```sql
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マニュアル](https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_date-format)

```sql
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の更新

```sql
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は上書きされない。

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

#テーブルの削除

```sql
TRUNCATE TABLE books;
```

#テーブルの設計変更

```sql
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に変更