More than 3 years have passed since last update.

MySQL メモランダム

MySQL

Posted at 2020-09-15

ドットインストールの学習メモです

#MySQLの始め方

ローカル開発環境で

[vagrant@localhost MySQL]$ mysql -u root

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

#データベースの操作
現在のユーザー表示,データベース一覧表示


mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

データベース作成、

mysql> create database mydb01;
Query OK, 1 row affected (0.00 sec)

mysql> create database mydb02;
Query OK, 1 row affected (0.00 sec)

mysql> create database mydb03;
Query OK, 1 row affected (0.00 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb01             |
| mydb02             |
| mydb03             |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

データベース削除


mysql> drop database mydb03;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb01             |
| mydb02             |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

現在のデータベース表示・変更


mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use mydb02;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb02     |
+------------+
1 row in set (0.00 sec)

大文字小文字の区別はない



mysql> SelEct DAtabase();
+------------+
| DAtabase() |
+------------+
| mydb02     |
+------------+
1 row in set (0.00 sec)

mysql>

#作業用ユーザーの設定

ルートユーザーで実行
create user dbuser01@localhost identified by 'password';
dbuser01というユーザーを作り、パスワードを設定。
grant all on mydb01.* to dbuser01@localhost;
dbuser01にmydb01の全テーブルに関するすべての権限を与える。

root userから dbuser01へ
パスワードとデータベースを指定してログイン。


mysql> \q
Bye
[vagrant@localhost MySQL]$ mysql -u dbuser01 -p mydb01;
Enter password:

ユーザーの変更と、データベースの指定を確認(mydb01のみ）

mysql> select user();
+--------------------+
| user()             |
+--------------------+
| dbuser01@localhost |
+--------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb01             |
+--------------------+
2 rows in set (0.00 sec)

ユーザーの削除
drop user dbuser@localhost;

#外部ファイルのコマンドの実行

ファイルを作成

drop database if exists myapp;
create database myapp;
grant all on myapp.* to myapp_user@localhost identified by 'matasaburou09';

ファイル名を指定して実行

[vagrant@localhost MySQL]$ mysql -u root < create_myapp.sql;
[vagrant@localhost MySQL]$ mysql -u myapp_user -p myapp;

rootユーザーでログインしてから、ファイルを指定して実行

mysql> source ./create_myapp.sql

もしくは
mysql> \. ./create_myapp.sql

#テーブルの操作

sql

drop table if exists users;
create table users(
	id int unsigned,
	name varchar(20),
	score float
);

（テーブルの削除）
テーブルの作成
id, name, score　のフィールドの作成

terminal

mysql> \. ./myapp.sql
Query OK, 0 rows affected (0.01 sec)

mysql> show tables
    -> ;
+-----------------+
| Tables_in_myapp |
+-----------------+
| users           |
+-----------------+
1 row in set (0.00 sec)

mysql> desc users;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |
| name  | varchar(20)      | YES  |     | NULL    |       |
| score | float            | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> drop table users;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

desc って　description　の略なんだって。初めて聞いた・・・。

#データ型について

number
int 整数
float 小数
double floatより精度が高い
int unsigned +の値しか使わない
string
char(4) 固定長のデータ 4けた
varchar(255)　255バイトまでの可変長文字列
text どれくらいの長さかわからない場合
date/time
date
time
datetime '2020-08-09 12:34:22'
true/false:
boolean -> tinyint(1)と同じ意味一桁の整数の方の別名
true -> 1 ↓以外
false -> 0 null

データ型

#テーブルデータの挿入

-- insert into users (id, name, score) values(1, 'tagichi', 5.8);
-- insert into users (id, name, score) values(2, 'fkoji', 8.2);
-- insert into users (id, name, score) values(3, 'dotinstall', 6.1);
-- insert into users (id, name, score) values(4, 'yamada', null);
insert into users (id, name, score) values
(1, 'tagichi', 5.8),
(2, 'fkoji', 8.2),
(3, 'dotinstall', 6.1),
(4, 'yamada', null);

select * from users;

#フィールドに制限をかけてみる

drop table if exists users;
create table users(
	id int unsigned primary key auto_increment,
	name varchar(20) unique,
	-- score float not null
	score float default 0.0
);
desc users;

primary key 主キー
auto_icrement 連番割り振り
unipue 重複を許さず

-- insert into users (id, name, score) values
-- (1, 'tagichi', 5.8),
-- (2, 'fkoji', 8.2),
-- (3, 'dotinstall', 6.1),
-- (4, 'yamada', null);
-- (5, 'tanaka');
-- (5, 'tanaka');
insert into users (name, score) values
('tagichi', 5.8),
('fkoji', 8.2),
('dotinstall', 6.1),
('yamada', null);
-- ('tanaka');
-- ('tanaka');

select * from users;

ここから更新された新しいMySQL講座

#日時・真偽値を使ってみよう

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 
  ('Tnanks', 12, TRUE, '2020-10-11 13:45:03'),
  ('Arigato', 4, FALSE, '2020-08-22'),
  ('Merci', 4, 0, NOW());

SELECT * FROM posts;

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------+-------+----------+---------------------+
| message | likes | is_draft | created             |
+---------+-------+----------+---------------------+
| Tnanks  |    12 |        1 | 2020-10-11 13:45:03 |
| Arigato |     4 |        0 | 2020-08-22 00:00:00 |
| Merci   |     4 |        0 | 2020-09-11 15:40:30 |
+---------+-------+----------+---------------------+
~ $

#SELECTを使ってデータを抽出

-- 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';

*　全て
FROM　テーブル
WHERE　条件

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

#条件の組み合わせ

10以上２０以下
10位上２０以下
10より下、20より上

-- AND 尚且つ
-- OR もしくは
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;

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

likesが4か12
likesが4か12
上記以外

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);

+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    12 |
|  2 | Arigato |     4 |
|  3 | Merci   |     4 |
+----+---------+-------+
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    12 |
|  2 | Arigato |     4 |
|  3 | Merci   |     4 |
+----+---------+-------+
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  4 | Gracias |    15 |
|  5 | Danke   |    23 |
+----+---------+-------+

#LIKEと％で文字列を抽出しよう

SELECT * FROM posts WHERE message = 'Gracias';
% 0文字以上の任意の文字
_: 任意の一文字


-- SELECT * FROM posts WHERE message LIKE 't%';
SELECT * FROM posts WHERE message LIKE BINARY 't%';
SELECT * FROM posts WHERE message LIKE '%su';
SELECT * FROM posts WHERE message LIKE '%i%';

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+-------------+-------+
| id | message     | likes |
+----+-------------+-------+
|  2 | thanks 100% |     4 |
+----+-------------+-------+
+----+-------------------+-------+
| id | message           | likes |
+----+-------------------+-------+
|  4 | Arigato_gozaimasu |    15 |
|  5 | Arigato! desu     |    23 |
+----+-------------------+-------+
+----+-------------------+-------+
| id | message           | likes |
+----+-------------------+-------+
|  3 | Gracias           |     4 |
|  4 | Arigato_gozaimasu |    15 |
|  5 | Arigato! desu     |    23 |
+----+-------------------+-------+

#LIKEと％で文字列を抽出しよう

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

SELECT * FROM posts WHERE message LIKE '__a%';
-- 3文字目がaの任意の文字
SELECT * FROM posts WHERE message NOT LIKE '__a%';
--それ以外の文字

SELECT * FROM posts WHERE message LIKE '%\%%';
-- ％の入ってる文字
SELECT * FROM posts WHERE message LIKE '%\_%';
-- _の入ってる文字

#NULLのレコードを抽出

SELECT * FROM posts WHERE likes != 12;
  -- nullは含まれない
SELECT * FROM posts WHERE likes != 12 OR likes IS NULL;
-- nullも含まれた
SELECT * FROM posts WHERE likes IS NOT NULL;
-- null以外

nullが入ってる時は条件に注意が必要

#抽出結果の並び替え

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;
-- 上記で上位3件のみ表示

SELECT * FROM posts ORDER BY likes DESC, message LIMIT 3 OFFSET 2;
-- 2件目まで除外してから3件分表示
SELECT * FROM posts ORDER BY likes DESC, message LIMIT 2, 3;
-- 同上

#数値の関数を見ていこう

-- + - * / %

SELECT likes * 500 / 3 FROM posts;
-- 1likes で500円分、全体の3分の１をもらえる計算
SELECT likes * 500 / 3 AS bonus FROM posts;
-- ASで名称をつける
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;
-- ()がついたものを関数という ex) round関数


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

#文字列の関数〜文字列抽出、文字列の連結、文字列の長さ（日本語対応）〜

SELECT message, SUBSTRING(message, 3) FROM posts;
-- 3文字目以降
SELECT message, SUBSTRING(message, 3, 2) FROM posts;
-- 3文字目以降2文字分
SELECT message, SUBSTRING(message, -2) FROM posts;
-- 末尾から2文字分

SELECT CONCAT(message, ' _ ', likes) FROM posts;
-- 文字列の連結。メッセージといいねの数をハイフンでつなぐ

SELECT message, LENGTH(message) AS len FROM posts;
-- メッセージと文字の長さ。しかしありがとうは15文字扱い。
SELECT message, CHAR_LENGTH(message) AS len FROM posts;
-- 日本語にも対応 LENGTHのみ問題になる SUBSTRINGは問題ない。

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+-----------------+-----------------------+
| message         | SUBSTRING(message, 3) |
+-----------------+-----------------------+
| Thanks          | anks                  |
| Merci           | rci                   |
| Arigato         | igato                 |
| ありがとう      | がとう                |
| Gracias         | acias                 |
| Danke           | nke                   |
+-----------------+-----------------------+
+-----------------+--------------------------+
| message         | SUBSTRING(message, 3, 2) |
+-----------------+--------------------------+
| Thanks          | an                       |
| Merci           | rc                       |
| Arigato         | ig                       |
| ありがとう      | がと                     |
| Gracias         | ac                       |
| Danke           | nk                       |
+-----------------+--------------------------+
+-----------------+------------------------+
| message         | SUBSTRING(message, -2) |
+-----------------+------------------------+
| Thanks          | ks                     |
| Merci           | ci                     |
| Arigato         | to                     |
| ありがとう      | とう                   |
| Gracias         | as                     |
| Danke           | ke                     |
+-----------------+------------------------+
+-------------------------------+
| CONCAT(message, ' _ ', likes) |
+-------------------------------+
| Thanks _ 12                   |
| Merci _ 4                     |
| Arigato _ 4                   |
| ありがとう _ 4                |
| Gracias _ 15                  |
| Danke _ 8                     |
+-------------------------------+
+-----------------+------+
| message         | len  |
+-----------------+------+
| Thanks          |    6 |
| Merci           |    5 |
| Arigato         |    7 |
| ありがとう      |   15 |
| Gracias         |    7 |
| Danke           |    5 |
+-----------------+------+
+-----------------+------+
| message         | len  |
+-----------------+------+
| Thanks          |    6 |
| Merci           |    5 |
| Arigato         |    7 |
| ありがとう      |    5 |
| Gracias         |    7 |
| Danke           |    5 |
+-----------------+------+

#日時の関数　〜 DATE_FORMAT, INTERVAL, NOW() ~

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;
-- わかりやすく表示

SELECT 
   created,
   
   DATE_ADD(created, INTERVAL 7 DAY) AS next
FROM
  posts;
-- 一週間後を表示

SELECT 
   created,
   NOW(),
   DATEDIFF(created, NOW()) AS diff
FROM
  posts;
-- 現在日時との差を表す

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------------------+---------------+
| created             | YEAR(created) |
+---------------------+---------------+
| 2020-05-01 00:00:00 |          2020 |
| 2020-05-03 00:00:00 |          2020 |
| 2020-06-14 00:00:00 |          2020 |
| 2020-07-04 00:00:00 |          2020 |
| 2020-08-22 00:00:00 |          2020 |
+---------------------+---------------+
+---------------------+----------------+
| created             | MONTH(created) |
+---------------------+----------------+
| 2020-05-01 00:00:00 |              5 |
| 2020-05-03 00:00:00 |              5 |
| 2020-06-14 00:00:00 |              6 |
| 2020-07-04 00:00:00 |              7 |
| 2020-08-22 00:00:00 |              8 |
+---------------------+----------------+
+---------------------+--------------+
| created             | DAY(created) |
+---------------------+--------------+
| 2020-05-01 00:00:00 |            1 |
| 2020-05-03 00:00:00 |            3 |
| 2020-06-14 00:00:00 |           14 |
| 2020-07-04 00:00:00 |            4 |
| 2020-08-22 00:00:00 |           22 |
+---------------------+--------------+
+---------------------+----------------------------+
| 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 |
+---------------------+----------------------------+
+---------------------+---------------------+
| 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 |
+---------------------+---------------------+
+---------------------+---------------------+------+
| created             | NOW()               | next |
+---------------------+---------------------+------+
| 2020-05-01 00:00:00 | 2020-09-15 08:45:03 | -137 |
| 2020-05-03 00:00:00 | 2020-09-15 08:45:03 | -135 |
| 2020-06-14 00:00:00 | 2020-09-15 08:45:03 |  -93 |
| 2020-07-04 00:00:00 | 2020-09-15 08:45:03 |  -73 |
| 2020-08-22 00:00:00 | 2020-09-15 08:45:03 |  -24 |
+---------------------+---------------------+------+
~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+---------------------+---------------+
| created             | YEAR(created) |
+---------------------+---------------+
| 2020-05-01 00:00:00 |          2020 |
| 2020-05-03 00:00:00 |          2020 |
| 2020-06-14 00:00:00 |          2020 |
| 2020-07-04 00:00:00 |          2020 |
| 2020-08-22 00:00:00 |          2020 |
+---------------------+---------------+
+---------------------+----------------+
| created             | MONTH(created) |
+---------------------+----------------+
| 2020-05-01 00:00:00 |              5 |
| 2020-05-03 00:00:00 |              5 |
| 2020-06-14 00:00:00 |              6 |
| 2020-07-04 00:00:00 |              7 |
| 2020-08-22 00:00:00 |              8 |
+---------------------+----------------+
+---------------------+--------------+
| created             | DAY(created) |
+---------------------+--------------+
| 2020-05-01 00:00:00 |            1 |
| 2020-05-03 00:00:00 |            3 |
| 2020-06-14 00:00:00 |           14 |
| 2020-07-04 00:00:00 |            4 |
| 2020-08-22 00:00:00 |           22 |
+---------------------+--------------+
+---------------------+----------------------------+
| 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 |
+---------------------+----------------------------+
+---------------------+---------------------+
| 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 |
+---------------------+---------------------+
+---------------------+---------------------+------+
| created             | NOW()               | diff |
+---------------------+---------------------+------+
| 2020-05-01 00:00:00 | 2020-09-15 08:45:47 | -137 |
| 2020-05-03 00:00:00 | 2020-09-15 08:45:47 | -135 |
| 2020-06-14 00:00:00 | 2020-09-15 08:45:47 |  -93 |
| 2020-07-04 00:00:00 | 2020-09-15 08:45:47 |  -73 |
| 2020-08-22 00:00:00 | 2020-09-15 08:45:47 |  -24 |
+---------------------+---------------------+------+

#レコードの更新〜　UPDATE　SET、　UPPER

-- SELECT likes + 10 FROM posts;
-- UPDATE posts SET likes = likes + 5 WHERE likes >= 10 ;
-- likes10以上にlikesを5プラスする
UPDATE 
  posts 
SET 
  likes = likes + 5,
  message = UPPER(message)
WHERE
  likes >= 10;
-- 上記に加えて、更新したものを大文字にする。
SELECT * FROM posts;


~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | Thanks  |    17 |
|  2 | Merci   |     4 |
|  3 | Arigato |     4 |
|  4 | Gracias |    20 |
|  5 | Danke   |     8 |
+----+---------+-------+
~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
|  1 | THANKS  |    17 |
|  2 | Merci   |     4 |
|  3 | Arigato |     4 |
|  4 | GRACIAS |    20 |
|  5 | Danke   |     8 |
+----+---------+-------+

#レコードの削除 ~ DELETE FROM, TRUNCATE TABLE ~

-- DELETE FROM posts WHERE likes <= 10;
-- likesが１０以下のものを削除
TRUNCATE TABLE posts;
-- 連番ごと削除できる。

INSERT INTO posts (message, likes) VALUES('Xie Xie', 10);
-- 連番は一度使われたものは使われない。

SELECT * FROM posts;

DELETE テーブル名　WHERE　条件
IDの連番は削除できないので、 TRUNCATE TABLE テーブル名　を使う。

#作成・更新日時を自動で設定　~ created, updated DATETIME DEFAULT NOW(), UPDATE~ SET , ~


DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140), 
  likes INT,
  created DATETIME DEFAULT NOW(),
  -- フィールドを追加。レコードが挿入された時点をNOW()で記録。
  updated DATETIME DEFAULT NOW() ON UPDATE 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;
-- データを表示
SELECT SLEEP(3);
-- 3秒待つ
UPDATE posts SET likes = 100 WHERE id = 1;
-- id が1のlikesを100にする。
SELECT id, created, updated FROM posts;
-- もう一度表示。
SELECT * FROM posts;

~ $ mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------------------+---------------------+
| id | created             | updated             |
+----+---------------------+---------------------+
|  1 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  2 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  3 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  4 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  5 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
+----+---------------------+---------------------+
+----------+
| SLEEP(3) |
+----------+
|        0 |
+----------+
+----+---------------------+---------------------+
| id | created             | updated             |
+----+---------------------+---------------------+
|  1 | 2020-09-15 09:42:52 | 2020-09-15 09:42:56 |
|  2 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  3 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  4 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  5 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
+----+---------------------+---------------------+
+----+---------+-------+---------------------+---------------------+
| id | message | likes | created             | updated             |
+----+---------+-------+---------------------+---------------------+
|  1 | Thanks  |   100 | 2020-09-15 09:42:52 | 2020-09-15 09:42:56 |
|  2 | Merci   |     4 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  3 | Arigato |     4 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  4 | Gracias |    15 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
|  5 | Danke   |     8 | 2020-09-15 09:42:52 | 2020-09-15 09:42:52 |
+----+---------+-------+---------------------+---------------------+

#テーブルの設計の変更 ~ ALTER TABLE , ADD (AFTER,FIRST), CHANGE, RENAME ~

- ALTER TABLE posts ADD author VARCHAR (255);
-- ALTER TABLE posts ADD author VARCHAR (255) AFTER id;
-- idの後にauthor挿入
ALTER TABLE posts ADD author VARCHAR (255) FIRST;
-- BEFOREはない！最初に入れるFIRSTだけ！  

ALTER TABLE posts DROP message;
-- メッセージの削除
ALTER TABLE posts CHANGE likes points INT;
-- フィールド名をlikes を pointsに変更。 changeは前のデータが消えることもあるので注意！
DROP TABLE IF EXISTS messages;
-- messagesがすでにあったら削除
ALTER TABLE posts RENAME messages;
-- テーブル名をpostsからmessagesに変更！
SHOW TABLES;

-- DESC posts;


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

何やらなんやらで時間がかかってしまったので最初の部分忘れてる・・・。
まあ先に進もう。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up