LoginSignup
2
5

More than 1 year has passed since last update.

@koojiiiii(こじ)

MySQLについて

Last updated at Posted at 2021-11-15

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について[まとめ]

2
5
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
5