MySQLについて



## SQL（Structured Query Language）

SQL があらかじめ用意している命令は大文字、自分で付けるテーブル名やカラム名などは小文字
にするのが慣習的

### テーブルの作成

~~~
CREATE TABLE posts (
  message VARCHAR(140), 
  likes INT
);
~~~

テーブル名は複数のレコードを管理するので、複数形にすることが多い
message は 140 文字までの文字列、 likes は整数という意味で integer のINT に設定している

#### テーブル構造と一覧の確認

* DESC テーブル名
  テーブル構造の確認

* SHOW TABLES;
  テーブル一覧の確認


~~~
DESC posts;
SHOW TABLES;
~~~

これで

~~~
~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| message | varchar(140) | YES  |     | NULL    |       |
| likes   | int(11)      | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
+-----------------+
| Tables_in_myapp |
+-----------------+
| posts           |
+-----------------+
~~~

このように表示される

### 再実行

~~~
CREATE TABLE posts (
  message VARCHAR(140), 
  likes INT
);

DESC posts;
SHOW TABLES;
~~~

このコードを再度実行しようとするとエラーになる
posts はもう存在しているので、新しく作れない

なので、

~~~
DROP TABLE IF EXISTS posts;
~~~
これを１行目に持ってくる
1.実行する度にまっさらな状態から始められるように、１行目でテーブルを削除
2.IF EXISTSをれることで、posts が存在する時だけ削除するという意味になる
  IF EXISTSをつけないと、postsが存在していなければエラーとなるから

### レコードの挿入

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

* データを挿入したいカラムの名前をカンマ区切りで渡し、 VALUES のあとに
  渡したい値そのものをカンマ区切りで渡す

* 文字列はシングルクォーテーションかダブルクォーテーションで囲む

### 挿入したレコードの確認

~~~
SELECT * FROM posts;
~~~

~~~
+---------+-------+
| message | likes |
+---------+-------+
| Thanks  |    12 |
| Arigato |     4 |
+---------+-------+
~~~

これでレコードが挿入されているのが確認できる


### コメント

コメントには３種類ある

~~~
-- comment
~~~

~~~
# comment
~~~

~~~
/*
comment
comment
*/
~~~

### データ型

* 整数

コード|領域
-|-
TINYINT|-128 ~ +127
INT|-21億 ~ +21億
BIGINT|-922京 ~ +922京
TINYINT UNSIGNED|0 ~ 255
INT UNSIGNED|0 ~ 42億
BIGINT UNSIGNED|0 ~ 1844京

* 実数

コード|意味
-|-
DECIMAL|固定小数点
FLOAT|浮動小数点
DOUBLE|浮動小数点（高精度）

* 文字列

コード|領域
-|-
CHAR|0 ~ 255文字
VARCHAR|0 ~ 65535文字
TEXT|それ以上
ENUM|特定の文字列から１つ
SET|特定の文字列から複数


* 真偽値

コード|意味
-|-
BOOL|TRUE / FALSE
TINYINT(1)|1 / 0

TRUE が 1 、 FLASE が 0 として管理されている
0 か 1 か、真か偽かといった二択の値を保持する時に使う
1 桁の TINYINT と同じ型(別名ということ)

### 数字、文字列を扱う

~~~
DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  message VARCHAR(140), 
  likes INT UNSIGNED,
  mood DECIMAL(4, 2) UNSIGNED,
  lang CHAR(2)
);

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

SELECT * FROM posts;
~~~
* 文字数があまり定まっていないようなものは VARCHAR を使ってあげる
* VARCHARは日本語英語関係なく丸かっこ内の文字数を使える



この部分を挿入した

~~~
mood DECIMAL(4, 2) UNSIGNED,
lang CHAR(2)
~~~

* DECIMAL の丸かっこの中では全体で４桁、その内小数点以下は２桁と指定している
* 固定長の文字列には CHAR を使う
  言語コードを 2 文字で保持したかったら、このように丸括弧の中に文字数を書いてあげれば OK

~~~
mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------+-------+------+------+
| message | likes | mood | lang |
+---------+-------+------+------+
| Thanks  |    12 | 7.83 | EN   |
| Arigato |     4 | 4.21 | JA   |
+---------+-------+------+------+
~~~
このように表示される

### ENUM

特定の文字列の中からひとつだけを格納できる

~~~
category ENUM('Gadget', 'Game', 'Business')
~~~
* categoryに対してこの３つのどれかを指定して、挿入できる
* 左からインデックス番号１、２、３で数字でも指定できる

INSERT部分でcategoryを追加するとこのように書くことができる

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

~~~
mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------+-------+----------+
| message | likes | category |
+---------+-------+----------+
| Thanks  |    12 | Gadget   |
| Arigato |     4 | Game     |
| Merci   |     4 | Business |
+---------+-------+----------+
~~~

### SET型

ENUM型はひとつしか選べないが、複数選びたいときは SET 型を使う

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

* SET型の場合左から、2^0, 2^1, 2^2というように管理している
* 例えば、'Gadget,Game'というように挿入したい場合は、番号は2^0=1と2^1=2を足した３となる

~~~
INSERT INTO posts (message, likes, categories) VALUES 
 ('Tnanks', 12, 'Gadget,Game'),
 ('Arigato', 4, 'Business'),
 ('Merci', 4, 'Business,Gadget');
~~~
もしくは

~~~
INSERT INTO posts (message, likes, categories) VALUES 
  ('Tnanks', 12, 3),
  ('Arigato', 4, 4),
  ('Merci', 4, 5);
~~~
というように指定できる

~~~
mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------+-------+-----------------+
| message | likes | categories      |
+---------+-------+-----------------+
| Tnanks  |    12 | Gadget,Game     |
| Arigato |     4 | Business        |
| Merci   |     4 | Gadget,Business |
+---------+-------+-----------------+
~~~

### 真偽値や日時

~~~
DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  message VARCHAR(140), 
  likes INT,
  is_draft BOOL,
  created DATETIME
);

INSERT INTO posts (message, likes, is_draft, created) VALUES 
  ('Thanks', 12, TRUE, '2020-10-11 15:32:05'),
  ('Arigato', 4, FALSE, '2020-10-12'),
  ('Merci', 4, 0, NOW());  

SELECT * FROM posts;
~~~

この部分を挿入

* 時間の指定が無い場合は、00:00:00と表示される
* NOW()で現在の時間を表す

~~~
is_draft BOOL,
created DATETIME
~~~

~~~
 mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------+-------+----------+---------------------+
| message | likes | is_draft | created             |
+---------+-------+----------+---------------------+
| Thanks  |    12 |        1 | 2020-10-11 15:32:05 |
| Arigato |     4 |        0 | 2020-10-12 00:00:00 |
| Merci   |     4 |        0 | 2021-11-15 20:39:06 |
+---------+-------+----------+---------------------+
~~~

### NULLの扱い


likesカラムに何も値がなかった場合は、NULLが表示される

~~~
mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------+-------+
| message | likes |
+---------+-------+
| Thanks  |    12 |
| Arigato |     4 |
| Merci   |     4 |
| Gracias |  NULL |
+---------+-------+
~~~

* 値が設定されていなかったら、エラーではじきたいという場合

~~~
likes INT NOT NULL
~~~
こう書くと

~~~
mysql -h db -t -u dbuser -pdbpass myapp < main.sql
ERROR 1364 (HY000) at line 12: Field 'likes' doesn't have a default value
~~~
エラーになる

* デフォルトの値を表示させたい場合

値がなかった場合０を表示させたかったら

~~~
likes INT DEFAULT 0
~~~
と書いて

~~~
mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------+-------+
| message | likes |
+---------+-------+
| Thanks  |    12 |
| Arigato |     4 |
| Merci   |     4 |
| Gracias |     0 |
+---------+-------+
~~~
となる

### 値に制限をつける

~~~
message VARCHAR(140) UNIQUE, 
likes INT CHECK (likes >= 0 AND likes <= 200)
~~~

UNIQUEで文字列の重複、CHECKで値の数字が200以上になるとエラーになる

### 主キーの設定

特定のレコードを処理するために、レコードを一意に識別するカラムを設定するのが一般的

~~~
DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL,
  message VARCHAR(140), 
  likes INT,
  PRIMARY KEY (id)
);
~~~

* id INT NOT NULL,の部分でid という名前で NULL ではない整数の連番にする
* PRIMARY KEYでid をこのテーブルのプライマリーキーつまり、主キーに指定
* idが重複するとエラーがおこる

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

~~~
mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    12 |
|  2 | Arigato |     4 |
|  3 | Merci   |     4 |
+----+---------+-------+
~~~

またidの数字は自動で振ることもできる

~~~
id INT NOT NULL AUTO_INCREMENT,
~~~
* AUTO_INCREMENTを使うことで、値を挿入しなければ自動的に連番になる

### データ抽出

~~~
SELECT * FROM posts;
SELECT id, message FROM posts;

SELECT * FROM posts WHERE likes >= 10;
SELECT * FROM posts WHERE message = 'Danke';
SELECT * FROM posts WHERE message != 'Danke';
SELECT * FROM posts WHERE message <> 'Danke';
~~~

上から

* 全てのレコード
* idとmessageのみ
* likesが10以上
* messageのDankeのみ
* messageのDanke以外
* messageのDanke以外




### 条件の組み合わせ

* AND なおかつ

~~~
SELECT * FROM posts WHERE likes >= 10 AND likes <= 20;
SELECT * FROM posts WHERE likes BETWEEN 10 AND 20;
SELECT * FROM posts WHERE likes NOT BETWEEN 10 AND 20;
~~~
上から
* likes が 10 以上、なおかつ 20 以下
* likes が 10 以上、なおかつ 20 以下
* likes が 10 以上、なおかつ 20 以下ではない


* OR  もしくは

~~~
SELECT * FROM posts WHERE likes = 4 OR likes = 12;
SELECT * FROM posts WHERE likes IN (4, 12);
SELECT * FROM posts WHERE likes NOT IN (4, 12);
~~~
上から
* likes が 4 もしくは 12 のレコード
* likes が 4 もしくは 12 のレコード
* likes が 4 もしくは 12 のレコードではない

### 文字列の抽出

~~~
%: 0文字以上の任意の文字
_: 任意の1文字
~~~

 % がその文字自体を表現する場合は、 \ をその前に付けてあげれば OK

~~~
SELECT * FROM posts WHERE message LIKE '%\%%';
~~~

~~~
 mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+-------------+-------+
| id | message     | likes |
+----+-------------+-------+
|  2 | thanks 100% |     4 |
+----+-------------+-------+
~~~

このように％が含まれた文字列を抽出できる

### 抽出結果を並び替える

~~~
SELECT * FROM posts ORDER BY likes 
~~~
* likes を小さい順で並び替える

~~~
SELECT * FROM posts ORDER BY likes DESC;
~~~
* 逆に並び替える場合はDESCをつける

~~~
SELECT * FROM posts ORDER BY likes DESC, message;
~~~
* likesの数が同じだった場合、アルファベット順に並び替えたかったら、
カンマ区切りでさらに messageをつける

~~~
SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3;
~~~
* さらにここから上位 3 件だけ抽出する場合はLIMITを使う

~~~
SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3 OFFSET 2;
SELECT * FROM posts ORDER BY likes DESC, message LIMIT 2, 3;
~~~
* 最初の 2 件を除外して、その後 3 件という抽出をしたい場合このどちらかを書く

### 数値の関数

~~~
SELECT 
  likes * 500 / 3 AS bonus,
  FLOOR(likes * 500 / 3) AS floor,
  CEIL(likes * 500 / 3) AS ceil,
  -- ROUND(likes * 500 / 3) AS round
  ROUND(likes * 500 / 3, 2) AS round
FROM 
  posts;
~~~

* ASはbonusという別名を設定している
* FLOORで端数切り捨て
* CEILで端数切り上げ
* ROUNDで四捨五入
* ROUNDで桁数を指定する場合、ROUND(likes * 500 / 3, 2)と書く
  これは小数点以下２桁

~~~
+-----------+-------+------+---------+
| bonus     | floor | ceil | round   |
+-----------+-------+------+---------+
| 2000.0000 |  2000 | 2000 | 2000.00 |
|  666.6667 |   666 |  667 |  666.67 |
|  666.6667 |   666 |  667 |  666.67 |
| 2500.0000 |  2500 | 2500 | 2500.00 |
| 1333.3333 |  1333 | 1334 | 1333.33 |
+-----------+-------+------+---------+
~~~

### 文字列の関数

* SUBSTRING()

~~~
SELECT message, SUBSTRING(message, 3) FROM posts;
SELECT message, SUBSTRING(message, 3, 2) FROM posts;
SELECT message, SUBSTRING(message, -2) FROM posts;
~~~

上から
3 文字目以降、 
3 文字目から 2 文字分、
末尾から 2 文字分を
それぞれ抽出している

* CONCAT()

文字列の連結

~~~
SELECT CONCAT(message, ' - ', likes) FROM posts;
~~~
これで

~~~
mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+-------------------------------+
| CONCAT(message, ' - ', likes) |
+-------------------------------+
| Thanks - 12                   |
| Merci - 4                     |
| Arigato - 4                   |
| Gracias - 15                  |
| Danke - 8                     |
+-------------------------------+
~~~
というように連結される

* LENGTH()

文字数を抽出できる

~~~
SELECT message, LENGTH(message) AS len FROM posts ORDER BY len;
~~~
とすると、

~~~
+---------+------+
| message | len  |
+---------+------+
| Merci   |    5 |
| Danke   |    5 |
| Thanks  |    6 |
| Arigato |    7 |
| Gracias |    7 |
+---------+------+
~~~

### 

~~~
SELECT created, YEAR(created) FROM posts;
SELECT created, MONTH(created) FROM posts;
SELECT created, DAY(created) FROM posts;
~~~
上から
年、
月、
日を抽出

* DATE_FORMAT()

好きなフォーマットで抽出できる

~~~
SELECT 
  created, 
  DATE_FORMAT(created, '%M %D %Y, %W') AS date
FROM 
  posts;
~~~
とすると、

~~~
+---------------------+----------------------------+
| created             | date                       |
+---------------------+----------------------------+
| 2020-05-01 00:00:00 | May 1st 2020, Friday       |
| 2020-05-03 00:00:00 | May 3rd 2020, Sunday       |
| 2020-06-14 00:00:00 | June 14th 2020, Sunday     |
| 2020-07-04 00:00:00 | July 4th 2020, Saturday    |
| 2020-08-22 00:00:00 | August 22nd 2020, Saturday |
+---------------------+----------------------------+
~~~
となる

* DATE_ADD()
日付の計算

~~~
SELECT 
  created, 
  DATE_ADD(created, INTERVAL 7 DAY) AS next 
FROM 
  posts;
~~~

created から 7 日後を抽出


~~~
+---------------------+---------------------+
| created             | next                |
+---------------------+---------------------+
| 2020-05-01 00:00:00 | 2020-05-08 00:00:00 |
| 2020-05-03 00:00:00 | 2020-05-10 00:00:00 |
| 2020-06-14 00:00:00 | 2020-06-21 00:00:00 |
| 2020-07-04 00:00:00 | 2020-07-11 00:00:00 |
| 2020-08-22 00:00:00 | 2020-08-29 00:00:00 |
+---------------------+---------------------+
~~~


* DATEDIFF()
現在の日付よりどれくらい前かを計算

~~~
SELECT 
  created, 
  NOW(), 
  DATEDIFF(created, NOW()) AS diff 
FROM 
  posts;
~~~

~~~
+---------------------+---------------------+------+
| created             | NOW()               | diff |
+---------------------+---------------------+------+
| 2020-05-01 00:00:00 | 2021-11-15 22:20:14 | -563 |
| 2020-05-03 00:00:00 | 2021-11-15 22:20:14 | -561 |
| 2020-06-14 00:00:00 | 2021-11-15 22:20:14 | -519 |
| 2020-07-04 00:00:00 | 2021-11-15 22:20:14 | -499 |
| 2020-08-22 00:00:00 | 2021-11-15 22:20:14 | -450 |
+---------------------+---------------------+------+
~~~

### レコードの更新

* UPDATE テーブル名 SET
データ自体を更新

~~~
UPDATE posts SET likes = likes + 5 WHERE likes >= 10;
~~~
likes が 10 以上のものについて likes を 5 増やしている


また、複数のカラムの値を一気に更新するには

~~~
UPDATE 
  posts 
SET 
  likes = likes + 5, 
  message = UPPER(message) 
WHERE 
  likes >= 10;

SELECT * FROM posts;
~~~

likes が 10 以上のレコードについてmessageを全て大文字にする処理を行なっている

* 複数のカラムを更新するにはカンマ区切りにする
* UPPER()は全て大文字にする関数

### レコードの削除

~~~
DELETE FROM posts WHERE likes < 10;
~~~
likes が 10 より小さいものを削除している
なお、レコードを削除したあとにデータを挿入した場合に、
連番がどうなるかというと
例えば、1 から 5 まであったデータを一部でも全部でも消去すると
連番は６からになる

* TRUNCATE TABLE テーブル名
連番を最初から振り直したい場合に使う
テーブルごといったん削除して再作成する

~~~
TRUNCATE TABLE posts;
~~~
これで連番もリセットされる

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

* DEFAULT NOW()
レコードが挿入された時点での日時を設定


* DEFAULT NOW() ON UPDATE NOW()
レコードが更新された時にその時点での日時を自動で更新

### テーブルの設計を変更

例えば、

~~~
ALTER TABLE posts ADD username VARCHAR(255);
~~~
usernameというカラムを追加するときに場所を指定する場合、

~~~
ALTER TABLE posts ADD username VARCHAR(255) AFTER id;
ALTER TABLE posts ADD username VARCHAR(255) FIRST;
~~~

上から

* idの後にusername
* 最初に挿入される

### カラムの削除

~~~
ALTER TABLE posts DROP message;
~~~
これでmessageが削除される

### カラムの変更

~~~
ALTER TABLE posts CHANGE likes points INT;
~~~
likes を points の整数型に変更している

### テーブル名の変更

~~~
ALTER TABLE posts RENAME messages;
~~~
messagesという名前のテーブルになった

>参考
>>ドットインストール　MySQL入門　基礎編
>[SQLについて[まとめ]](https://qiita.com/koojiiiii/items/17ff796eb58f7590fa84)