【Mac】PostgreSQL と MySQL (基礎)

• 【公式】PostgreSQL

• 【公式】MySQL

【インストール -> 接続】

PostgreSQL

**インストールから接続まで**

インストール

• 何も指定しなければ最新版

$ brew install postgresql

実行時下記のエラーが発生した場合

psql -l: error: could not connect to server: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

postgreSQLのアップグレードで解決

brew postgresql-upgrade-database

接続

# バージョン確認
$ psql -V

 # DB一覧
$ psql -l

# 接続
$ psql データベース名

MySQL

**インストールから接続まで**

インストール

• 何も指定しなければ最新版

brew install mysql

バージョンを指定する

$ brew search mysql # brew searchでインストールできるバージョンを確認
==> Formulae
automysqlbackup               mysql-client@5.7              mysql-search-replace
mysql ✔                       mysql-connector-c++           mysql@5.6
mysql++                       mysql-connector-c++@1.1       mysql@5.7
mysql-client                  mysql-sandbox                 mysqltuner
==> Casks
mysql-connector-python        mysql-utilities               navicat-for-mysql
mysql-shell                   mysqlworkbench                sqlpro-for-mysql

$ brew install mysql @5.6 # 5.6を指定

バージョン確認

brew info mysql

実行時下記のエラーが発生した場合

$ mysql.server start
ERROR! The server quit without updating PID file (/usr/local/var/mysql/USER-no-MacBook-Air.local.pid).

/usr/local/var/mysqlを削除し再インストールで解決

$ sudo rm -rf /usr/local/var/mysql
$ brew uninstall mysql
$ brew install mysql
$ mysql.server start
Starting MySQL
.. SUCCESS!  

rootでログイン

$ mysql -u root

---

【コマンド/クエリ/コメント】

**PostgreSQL**

「$」は外部で実行
「#」は内部で実行

動作	PostgreSQL
ヘルプ	$ psql --help # \h # \?
バージョン確認	$ psql --version $ psql -V
DB一覧	$ psql --list $ psql -l # \l
DB削除	$ drop データベース名
DB接続	$ psql データベース名
DB選択	# \c データベース名
選択中のDB	# select current_database();
切断	# \q #quit;
テーブル一覧	# \dt
テーブル詳細	# \d テーブル名
テーブル削除	# drop table テーブル名 viewで使用中だと削除できない
view一覧	# \dv
ユーザ一覧	# \du
ログインユーザー表示	# select session_user; # select current_user;
外部ファイル実行	# \i ファイル名
コメント	-- (行末まで) /* */ (複数行)
拡張表示(出力を縦表示)	# \x

**MySQL**

**「`$`」は外部で実行** **「`>`」は内部で実行**

| 動作 | MySQL |
|---|---|---|
| ヘルプ | $ mysql --help

> h; |
| バージョン確認 | $ mysql --version |
| DB一覧 | > show databases;

> show schemas

> show databases like '検索文字列' |
| DB作成 | > create database データベース名; |
| DB削除 | > drop database データベース名; |
| DB接続 | > mysql -u ユーザ名 -p
※ rootユーザの場合は-p不要 |
| DB選択 | > use データベース名 |
| 選択中のDB | > select database(); |
| 切断 | > \q

> quit;

> exit |
| テーブル一覧 | > show tables;

> show tables like '検索文字列'; |
| テーブル詳細 | > desc テーブル名; |
| テーブル削除 | > drop table テーブル名; |
| view一覧 | > show tables; |
| view削除 | > drop view名; |
| CREATE VIEW ステートメント表示 | > show create view view名; |
| ユーザ一覧 | > select Host, User from mysql.user; |
| ログインユーザー表示 | > select user();|
| 外部ファイル実行 | $ mysql -u ユーザ名 < ファイル名(パス)

> source ファイル名(パス)

> \. ファイル名(パス) |
| コメント | #(行末まで)

-- (行末まで)

/* */ (複数行) |
| 拡張表示(出力を縦表示) | > select * from テーブル名 \G;
※ クエリの末尾に\G
※mysql接続時に-Eオプションを指定 |
| DB存在確認 | > create database if not exists データベース名; |
| 文字コード確認 | > show variables like '%char%'; |
| 警告確認 | > show warnings; |
| 起動中のスレッド情報 | > show processlist; |
| コマンドキャンセル | > \c |
| ユーザ作成 | > create user ユーザ名@接続元 identified by 'パスワード'; |
| ユーザ削除 | > drop user ユーザ名@接続元; |
| 接続 | $ mysql -u ユーザ名 -p データベース名; |
| ログインユーザー表示 | > select user(); |
| 権限付与 | > grant 権限種類 on データベース名.対象のテーブル to ユーザ名@接続元; |

**権限レベル**

レベル	概要
グローバルレベル(G)	全てのデータベースに適用 mysql.user テーブルに格納される
データベースレベル(D)	特定のデータベース内の全てのオブジェクトに適用 mysql.db テーブル内に格納される
テーブルレベル(T)	特定のテーブル内の全てのカラムに適用 mysql.tables_priv テーブル内に格納れる
カラムレベル(C)	特定テーブル内の単一カラムに適用 mysql.columns_priv テーブル内に格納される
ルーチンレベル(R)	CREATE ROUTINE,ALTER ROUTINE,EXECUTE,GRANT OPTION権限はストアドルーチンに適用 グローバルとデータベースレベルで供与 CREATE ROUTINE以外は、各ルーチンに対してルーチンレベルで適用可能 mysql.procs_priv テーブル内に格納される
プロキシユーザー権限(P)	あるユーザーが別のユーザーの代理になることができる mysql.proxies_priv テーブル内に格納される

**権限一覧**

権限	レベル	意味
ALL [PRIVILEGES]	GDT	GRANT OPTION と PROXY 以外の全ての権限を設定
ALTER	GDT	テーブルの変更
ALTER ROUTINE	GDR	ストアドルーチンの変更・削除
CREATE	GDT	データベース、テーブルの作成
CREATE ROLE	G	ロールの作成
CREATE ROUTINE GD	ストアドルーチンの作成
CREATE TABLESPACE	G	テーブルスペースとロググループの作成、変更、削除
CREATE TEMPORARY TABLES	GD	CREATE TEMPORARY TABLE の使用
CREATE USER	G	CREATE USER 、 DROP USER 、 RENAME USER 、REVOKE ALL PRIVILEGES の使用
CREATE VIEW	GDT	ビューの作成と変更
DELETE	GDT	データベース、テーブル、ビューの削除
DROP	GDT	テーブルの削除
DROP ROLE	G	ロールの削除
EVENT	GD	イベントスケジューラがイベントの利用
EXECUTE	GDR	ストアドルーチンの起動
FILE	G	SELECT ... INTO OUTFILE と LOAD DATA INFILE の使用
GRANT OPTION	GDTRP	他のアカウントへの権限の付与とを削除
INDEX	GDT	インデックスの作成と削除
INSERT	GDTC	INSERTの使用
LOCK TABLES	GD	SELECT 権限を持つテーブル上の LOCK TABLES の使用
PROCESS	G	SHOW FULL PROCESSLIST の使用
PROXY	--	PROXYの使用
REFERENCES	GDTC	外部キーの作成
RELOAD	G	FLUSH の使用
REPLICATION CLIENT	G	スレーブとマスタの場所を問い合わせ
REPLICATION SLAVE	G	複製スレーブ (マスタからバイナリログイベントを読み込む為)
SELECT	GDTC	SELECT の使用
SHOW DATABASES	G	SHOW DATABASES の使用
SHOW VIEW	GDT	SHOW CREATE VIEWの使用
SHUTDOWN	G	mysqladmin shutdown の使用
SUPER	G	CHANGE MASTER TO 、 KILL 、 PURGE BINARY LOGS 、 SET GLOBAL 、 mysqladmin debug command の使用
TRIGGER	GDT	トリガの作成と削除
UPDATE	GDTC	UPDATE の使用
USAGE	G	「権限が無い」

SELECT権限ではグローバルレベル単位からカラム単位まで任意のレベルで設定可能。
すべてのデータベースに含まれるデータを取得できる権限を設定したり、特定のカラムだけデータを取得できるような権限を与えることができる。

サーバーとクライアントの文字コード

項目名	対象(client/server)	概要
character_set_client	client	クライアントがサーバにクエリを送信する際に使用
character_set_results	server	サーバがクライアントにクエリ結果を送る時に使用
character_set_connection	client	クライアントが送ったクエリをサーバが解析する際に使用
character_set_database	server	サーバのデフォルトキャラクタセット。LOAD DATA INFILE文はこの指定が使用される。mysql起動時にcharacter_set_serverの値が適用される。
character_set_server	server	サービス起動時のデフォルトキャラクタセット
character_set_system	-	ファイル名をこのキャラクタセットで扱う
character_sets_dir	-	キャラクタセットを扱う上で必須となるファイルを配置しているディレクトリ

拡張表示について

• 列が多く横長だと見にくい場合に便利
• 横か縦かの違い

**拡張表示なしの場合**

=# selsec * from users;

/* 抽出結果 */

 id |    name    | score | team  
----+------------+-------+-------
  1 | taguchi    |   5.5 | red
  2 | fkoji      |   8.3 | blue
  3 | dotinstall |   2.2 | red
  4 | sasaki     |     5 | green
  5 | sasaki     |   4.6 | red
  6 | kimura     |   4.7 | green
(6 rows)

**拡張表示ありの場合**

=# \x
Expanded display is on.
=# select * from users;

-[ RECORD 1 ]-----
id    | 1
name  | taguchi
score | 5.5
team  | red
-[ RECORD 2 ]-----
id    | 2
name  | fkoji
score | 8.3
team  | blue
-[ RECORD 3 ]-----
id    | 3
name  | dotinstall
score | 2.2
team  | red
.
.
.
=# \x
Expanded display is off.

---

【SQL評価順序】

**実行されるクエリの順序**

|順序| 句 | 動作 |
|:---:|---|---|---|
|先| FROM | 対象のテーブルを指定 |
|↓|ON| どの列で結合するかを指定 |
|| JOIN | 結合するテーブルを指定 |
|| WHERE | 対象のデータ絞り込み |
|| GROUP BY | 対象データでグループ化 |
|| 関数 | 集計 |
|| HAVING | グループ化した結果から絞り込み |
|| SELECT | 抽出する列を指定 |
|| DISTINCT | 抽出する列を指定 |
|| ORDER BY | 並べ替え |
|後| LIMIT | 抽出件数指定 |

**SQL文作成順序**

**

⑦   SELECT 抽出する列, 関数
⑧ DISTINCT 抽出する列
①     FROM 対象テーブル指定
②     JOIN 結合するテーブルを指定
③       ON 結合する列を指定
④    WHERE 対象データ絞り込み
⑤ GROUP BY グループ化
⑥   HAVING グループ結果から絞り込み
⑨ ORDER BY 並べ替え
⑩    LIMIT 抽出件数指定;

**【注意点】**

• HAVING句の条件によってSELECT句の指定が変わってくるためSELECT句の指定はHAVING句作成後
• LIMITが評価されるのは一番最後なので取得件数を少なくしても処理が速くなるわけではない。
• LIMITで取得件数を絞って速く動作するのは以下の場合に限る
  1. ORDER BYの指定が無い
  2. ORDER BYで指定されたカラムがインデックスに定義されている
• WHERE句での絞り込みかインデックスを作成しないと速くならない
• WHERE句はSELECT句より先に評価されるためSELECT句でASを使ってつけられた別名は使用できない
• クエリを速くするには、早い段階で取得するデータ量を少なくする

---

【テーブル操作】

テーブル作成

**PostgreSQL / MySQL**

書式

create table テーブル名 (カラム名 型（）, カラム名 型());

例

create table users(name varchar(255), age integer);

---

テーブル名変更

**PostgreSQL**

書式

alter table 現テーブル名 rename to 新テーブル名;

例

alter table users rename to employees;

**MySQL**

書式

rename table 現テーブル名 to 新テーブル名;

例

rename table users rename to employees;

---

テーブル削除

**PostgreSQL / MySQL**

書式

drop table テーブル名;

例

drop table users;

---

データ型

**PostgreSQL / MySQL**

型	PostgresSQL	MySQL
文字(固定長)	char(5)	char(5)
文字(可変長)	varchar(255) text	varchar(255) text
数値(整数)	integerまたはint	int (正の数と負の数) int unsigned (正の数のみにしその分扱える範囲を広く)
数値(小数点)	real	float double
数値(連番)	serial
真偽値	boolean(値: true, false, t, f)	boolean 別名 -> tinyint(1) 内部的には true -> 1 false -> 0 となる
日付(日付のみ)	date	date
日付(時間のみ)	time	time
日付(日付+時間)	timestamp	datetime 2020-01-01 12:00:00

PostgreSQLドキュメント: datatype

---

列挙型

**enum**

• 複数定義された中から1つ選択
• 内部で割り当てられている数値でinsertやupdateでも有効

MySQL

create table users (
  id int unsigned primary key auto_increment,
  ranks enum('gold', 'silver', 'bronze') -- 列挙型, 内部的には1, 2, 3と連番で数値が割り当てられている
);

insert into users (ranks) values ('silver');
insert into users (ranks) values ('gold');
insert into users (ranks) values ('bronze');
insert into users (ranks) values ('red'); -- NULLが入る
insert into users (ranks) values (1);
insert into users (ranks) values (2);
insert into users (ranks) values (3);
insert into users (ranks) values (4); -- NULLが入る

select * from users where ranks = 'gold';
+----+-------+-------+-------+
| id | name  | score | ranks |
+----+-------+-------+-------+
|  2 | fkoji |   8.2 | gold  |
+----+-------+-------+-------+
1 row in set (0.00 sec)

select * from users where ranks = 1;
+----+-------+-------+-------+
| id | name  | score | ranks |
+----+-------+-------+-------+
|  2 | fkoji |   8.2 | gold  |
+----+-------+-------+-------+
1 row in set (0.00 sec)

**set**

• 複数定義された中から1つ選択
• 内部的には1番目の要素には「2の0乗」の数値, 2番目の要素には「2の1乗」の数値, 3番目の要素には「2の2乗」の数値...が割り当てられる
  • 1番目と2番目の要素を指定したい場合は「1 + 2」で「３」を、3番目と4番目は「4 + 8」で12を指定する
• 内部で割り当てられている数値でinsertやupdateでも有効

MySQL

create table users (
  id int unsigned primary key auto_increment,
  coins set('gold', 'silver', 'bronze') -- [1番目-> 2の0乗(1), 2番目-> 2の1乗(2), 3番目-> 2の2乗(4)]
);

insert into users (coins) values ('gold');   -- (1)でもよい
insert into users (coins) values ('silver'); -- (2)でもよい
insert into users (coins) values ('bronze'); -- (4)でもよい
insert into users (coins) values ('gold,silver'); -- (3)でもよい 1 + 2
insert into users (coins) values ('gold,bronze'); -- (5)でもよい 1 + 4
insert into users (coins) values ('silver,bronze'); -- (6)でもよい 2 + 4
insert into users (coins) values ('silver,bronze,bronze'); -- (7)でもよい 1 + 2 + 4

select * from users where coins = 'gold,silver'; -- gold,silver 
select * from users where coins like '%gold%';
+----+--------------------+
| id | coins              |
+----+--------------------+
|  1 | gold               |
|  4 | gold,silver        |
|  5 | gold,bronze        |
|  7 | gold,silver,bronze |
+----+--------------------+
4 rows in set (0.00 sec)
select * from users where coins = 1; -- gold
select * from users where coins = 2; -- sliver
select * from users where coins = 3; -- gold,silver
select * from users where coins = 4; -- bronze
select * from users where coins = 5; -- gold,bronze
select * from users where coins = 6; -- silver,bronze
select * from users where coins = 7; -- gold,silver,bronze
select * from users where coins = 8; -- Empty```

---

制約

**PostgreSQL / MySQL**

制約	PostgresSQL	MySQL
入力必須	not null	not null
重複不可	unique	unique
入力チェック	check
初期値	defalut	defalut
主キー	primary key	primary key

postgreSQL

postgresql.sql

create table posts (
  id serial primary key,
  title varchar(255) not null,
  body text check(length(body) > 5),
  is_draft boolean detaul TRUE,
  created timestamp default statement_timestamp()
);

/*
制約
not null    入力必須
unique      重複不可
check
defalut     デフォルト値
primary key (not null + unique)テーブルに１つだけ設定可能
*/
/*
statement_timestamp() -> レコード挿入日時
*/

MySQL

mysql.sql

create table users (
  id int unsigned primary key auto_increment,
  name varchar(20) unique,
  score float default 0.0
);

---

抽出結果でテーブル作成

**PostgreSQL / MySQL**

書式

create table 作成するテーブル名 as 問い合わせ;

create table users_with_team as
select
  name,
  score,
  case 
    when score > 8.0 then 'Team-A'
    when score > 6.0 then 'Team-B'
    else 'Team-C'
  end as Team
from
  users;

desc users_with_team;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| score | float       | YES  |     | NULL    |       |
| Team  | varchar(6)  | NO   |     |         |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

---

テーブルコピー

**PostgreSQL**

likeでコピー

• テーブルの構造と制約を引き継ぐ
• レコードは引き継がない

書式

create table 作成するテーブル名 (like コピー元テーブル名 including all);

例

create table users_copy (like users including all);

 Column |          Type          | Collation | Nullable |              Default              
--------+------------------------+-----------+----------+-----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(255) |           | not null | 
 score  | real                   |           |          | 0
 team   | character varying(255) |           |          | 
Indexes:
    "users_copy_pkey" PRIMARY KEY, btree (id)

---

抽出結果でコピー

• テーブルの構造とレコードを引き継ぐ
• 制約は引き継がない

書式

create table 作成するテーブル名 as 問い合わせ;

例

create table users_copy as select * from users;

 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | integer                |           |          | 
 name   | character varying(255) |           |          | 
 score  | real                   |           |          | 
 team   | character varying(255) |           |          | 

**MySQL**

likeでコピー

• テーブルの構造だけコピー(レコードはコピーされない)
• likeを使う
• Primary keyやインデックスを引き継ぐ

書式

create table 作成するテーブル名 like コピー元テーブル名;

例

create table users_empty like users;
desc users_empty;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)  | YES  |     | NULL    |                |
| score | float        | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

---

抽出結果でコピー

• テーブルの構造とレコードをコピー
• 制約はコピーされない
  • primary key、インデックスは引き継がれない
  • aute increamentはdetaulf 0に置き換わる

書式

create table 作成するテーブル名 抽出問い合わせ;

例

create table users_copy select * from users;

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   |     | 0       |       |
| name  | varchar(20)  | YES  |     | NULL    |       |
| score | float        | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

---

【テーブルの構造を変更】

**PostgreSQL / MySQL**

インデックスについて

postgresql

/* フィールド追加 */
alter table テーブル名 add カラム名 型;
alter table users add fullname varchar(255);

/* フィールド削除 */
alter table テーブル名 drop カラム名;
alter table users drop カラム名;

/* フィールド名変更 */
alter table テーブル名 rename 元の名前 to 新しい名前;
alter table users rename name to myname;

/* フィールド型変更 */
alter table テーブル名 alter カラム名 type 型;
alter table users alter myname type varchar(32);

/ * インデックス追加 */
create index インデックス名 on テーブル名(カラム名);
create index team_index on users(team);

/ * インデックス削除 */
drop index インデックス名;
drop index team_index;

---

mysql

/*カラム追加*/
alter table テーブル名 add column カラム名 型 after カラム名;
alter table users add column email varchar(255) after name; /* カラムnameの後にカラムemailを追加 */

/*カラム削除*/
alter table テーブル名 drop column カラム名;
alter table users drop column score;

/*カラム変更*/
alter table テーブル名 change 旧カラム名 新カラム名 型 制約;
alter table users change name user_name varchar(80) default 'no-name';

/*テーブル名変更*/
alter table 旧テーブル名 rename 新テーブル名;
alter table users rename persons;

/* インデックス追加 */
alter table テーブル名 add index インデックス名 (インデックスを付与するカラム名);
alter table users add index index_socre (score);

/* インデックス削除 */ 
alter table テーブル名 drop index インデックス名;
alter table users drop index index_score;

/* インデックス確認 */
show index from テーブル名;
show index from users;

---

インデックスについて

• primary keyに指定されたカラムは自動的にインデックスされる。
• インデックスの追加は、検索のときは早くなるがレコードの挿入、更新時に新しいインデックスを作るので遅くなるためバランスを考える。

列の並び替え

PostgreSQLでは、コマンドレベルでの列の並び替えは不可能。
方法としては以下で対応可能。

1. 列を並び替えた状態のテーブルを新しく作成しする
2. データを更新し古いテーブルを削除

その他の方法

---

【INSERT】

**PostgreSQL**

書式

insert into テーブル名(カラム名, カラム名...) values (値, 値...), (値, 値...)...;

例

insert into users(name, age)
values (
  ('tanaka', 20),
  ('suzuki', 30),
  ('sato', 40)
);

**MySQL**

書式

insert into テーブル名(カラム名, カラム名...) values (値, 値...), (値, 値...)...;

例

insert into users(id, name, score)
values
  (1, 'tanaka', 20),
  (2, 'suzuki', 30),
  (3, 'sato', 40);

---

last_insert_id

**MySQL**

• 直前に挿入されたレコードのIDを調べる

前提

drop table if exists users;
drop table if exists posts;

create table users (
  id int unsigned primary key auto_increment,
  name varchar(255)
);

create table posts (
  id int unsigned primary key auto_increment,
  user_id int unsigned not null
);

alter table posts add constraint fk_posts foreign key (user_id) references users (id);

insert into users (name) values ("a");
insert into users (name) values ("b");
insert into users (name) values ("c");

insert into posts (user_id) values (1);
insert into posts (user_id) values (1);
insert into posts (user_id) values (3);

delete from users where id = 2;

insert into posts(user_id) values (last_insert_id()); -- user_id = 3のレコードが作成される

select * from users;
select * from posts;

alter table posts drop foreign key fk_posts;

+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | c    |
+----+------+
2 rows in set (0.00 sec)

+----+---------+
| id | user_id |
+----+---------+
|  1 |       1 |
|  2 |       1 |
|  3 |       3 |
|  4 |       3 |
+----+---------+
4 rows in set (0.01 sec)

挿入/更新時刻でレコードを作成

**PostgreSQL / MySQL**

• default current_timestampで現在時刻を挿入
• default current_time on update current_timestampでレコード更新時、更新時刻で更新する

drop table if exists users;

create table users (
  id int unsigned primary key auto_increment,
  name varchar(255),
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp on update current_timestamp
);

insert into users (name) values ('name1');
insert into users (name) values ('name2');
insert into users (name) values ('name3');

select * from users;

update users set name = 'update name' where id = 2;

do sleep(5);
select * from users;

+----+-------+---------------------+---------------------+
| id | name  | created_at          | updated_at          |
+----+-------+---------------------+---------------------+
|  1 | name1 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 |
|  2 | name2 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 |
|  3 | name3 | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 |
+----+-------+---------------------+---------------------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (5.01 sec)

.
.
.

+----+-------------+---------------------+---------------------+
| id | name        | created_at          | updated_at          |
+----+-------------+---------------------+---------------------+
|  1 | name1       | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 |
|  2 | update name | 2020-09-17 21:16:12 | 2020-09-17 21:16:17 | -- レコード更新時刻で更新される
|  3 | name3       | 2020-09-17 21:16:12 | 2020-09-17 21:16:12 |
+----+-------------+---------------------+---------------------+
3 rows in set (0.00 sec)

---

【SELECT】

**PostgreSQL / MySQL**

書式

select 抽出するカラム from テーブル名;

例

select * from users;
select name, score form users;

---

抽出時並び替え

**PostegreSQL / MySQL**

動作	書式
降順	order by カラム名 desc;
昇順	order by カラム名; order by カラム名 asc;
抽出するレコード数を操作	limit 抽出数 offset n番目から;

/* 昇順 */
select * from users order by score;
select * from users order by score asc;

/* 降順 */
select * from users order by score desc;

/* n番目からnつ */
select * from users limit 3 offset 3;
/* scoreトップランキング3 */
select * from users order by score limit 3;
/* scoreトップランキング4~6 */
select * from users order by score limit 3 offset 3;

/* scoreがnullのレコード */
select * from users where score is null;

/* scoreがnullではないレコード */
select * from users where score is not null;

---

抽出時使用されるインデックスを確認

explain 抽出文;

**PostgreSQL**

# explain select * from users where id > 1;

                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on users  (cost=0.00..10.88 rows=23 width=1040)
   Filter: (id > 1)
(2 rows)

**MySQL**

> explain select * from users where score > 5.0;

+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY     |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_score |            1 | score       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

---

【WHERE】

**PostgreSQL / MySQL**

書式

select カラム名 from テーブル名 where 条件;

例

select * from users where score > 1;
select * from users where score > 2.0 and team = 'red';
select * from users where team = 'red' or team = 'blue';

比較演算子

演算子	意味
>	~より大きい
<	~より小さい
>=	~以上
<=	〜以下
=	等しい
<>	等しくない
!=	等しくない

論理演算子

-- and
select * from users where score >= 3.0 and score <= 6.0 ;
select * from users where score between 3.0 and 6.0 ;

-- or
select * from users where name = 'taguchi' or name = 'fkoji';
select * from users where name in ('taguchi', 'fkoji');

like検索/ワイルドカード

PostgreSQL / MySQL

• デフォルト
  • PostgreSQLは大文字小文字を区別する
  • MySQLは大文字小文字を区別しない

ワイルドカード	意味
_	任意の1文字
%	0文字以上の任意の文字列
a%	aで始まる文字列
a%	aで終わる文字列
%a%	aを含む文字列

PostgreSQL: 大文字小文字を区別しない検索

select * from users where name Ilike 'A%'; -- 大文字のAで始まるレコードのみ抽出

MySQL: 大文字小文字を区別した検索

select * from users where name like binary 'A%'; -- 大文字のAで始まるレコードのみ抽出

---

【レコード集計】

四則演算

**PostgreSQL / MySQL**

演算子	意味
+	加算
-	減算
*	乗算
/	除算
%	剰余算

/* idが偶数のレコードを取得 */
select * from users where id  % 2 = 0;

/* idが偶数のレコードのscoreを1.2倍 */
update users set score = score * 1.2 where id % 2 = 0;

---

集計関数

**PostgreSQL / MySQL**

関数	動作
count	レコード数
distinct	データの種類
sum	合計
avg	平均
max	最大値
min	最小値

select count(score) from users;
select count(id) from users;
select count(*) from users;

select sum(score) from users;
select min(score) from users;
select max(score) from users;
select avg(score) from users;

select distinct team from users;

select count(distinct team) from users;  -- チームの種類数

---

group by

**PostgreSQL / MySQL**

select team, sum(score) from users group by team;

/* 抽出結果 */
 team  |    sum    
-------+-----------
 blue  |       8.3
 red   | 12.299999
 green |       9.7

---

HAVING句

• 集計後のデータに対しての絞り込み

**WHERE句とHAVING句の違い**

句	概要
WHERE句	SELECT句の結果から絞り込みを行う
HAVING句	group byで集計した結果から絞り込みを行う

/* チーム毎の合計が10.0以上のレコード */
select team, sum(score) from users group by team having sum(score) > 10.0;

/* whereで絞り込みを行った後のレコードに対してgroup byを行う */
select sum(score), team from users_with_team where id > 3 group by team;

日時の計算

• date_add()
• date_format()

**MySQL**

drop table if exists users;

create table users (
  id int unsigned primary key auto_increment,
  name varchar(255),
  created_at datetime default current_timestamp,
  updated_at datetime default current_timestamp on update current_timestamp
);

insert into users (name) values ('name1');
insert into users (name) values ('name2');
insert into users (name) values ('name3');

update users set created_at = '2019-09-17 10:00:00' where id = 2;    -- 日付を更新

select * from users;
+----+-------+---------------------+---------------------+
| id | name  | created_at          | updated_at          |
+----+-------+---------------------+---------------------+
|  1 | name1 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 |
|  2 | name2 | 2019-09-17 10:00:00 | 2020-09-17 21:35:37 |
|  3 | name3 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 |
+----+-------+---------------------+---------------------+
3 rows in set (0.00 sec)


select * from users where created_at > '2019-12-31';                 -- 条件に日付を指定
+----+-------+---------------------+---------------------+
| id | name  | created_at          | updated_at          |
+----+-------+---------------------+---------------------+
|  1 | name1 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 |
|  3 | name3 | 2020-09-17 21:35:37 | 2020-09-17 21:35:37 |
+----+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

select created_at, date_add(created_at, interval 21 day) from users; -- 21日後
+---------------------+---------------------------------------+
| created_at          | date_add(created_at, interval 21 day) |
+---------------------+---------------------------------------+
| 2020-09-17 21:35:37 | 2020-10-08 21:35:37                   |
| 2019-09-17 10:00:00 | 2019-10-08 10:00:00                   |
| 2020-09-17 21:35:37 | 2020-10-08 21:35:37                   |
+---------------------+---------------------------------------+
3 rows in set (0.00 sec)

select created_at, date_add(created_at, interval 3 week) from users; -- 3週間後
+---------------------+---------------------------------------+
| created_at          | date_add(created_at, interval 3 week) |
+---------------------+---------------------------------------+
| 2020-09-17 21:35:37 | 2020-10-08 21:35:37                   |
| 2019-09-17 10:00:00 | 2019-10-08 10:00:00                   |
| 2020-09-17 21:35:37 | 2020-10-08 21:35:37                   |
+---------------------+---------------------------------------+
3 rows in set (0.00 sec)

select created_at, date_format(created_at, '%W %M %Y') from users;   -- 抽出時のフォーマットを指定
+---------------------+-------------------------------------+
| created_at          | date_format(created_at, '%W %M %Y') |
+---------------------+-------------------------------------+
| 2020-09-17 21:35:37 | Thursday September 2020             |
| 2019-09-17 10:00:00 | Tuesday September 2019              |
| 2020-09-17 21:35:37 | Thursday September 2020             |
+---------------------+-------------------------------------+
3 rows in set (0.00 sec)

---

【関数】

**PostgreSQL**

length(長さ)

select name, length(name) from users;

/* 抽出結果 */
    name    | length 
------------+--------
 taguchi    |      7
 fkoji      |      5
 dotinstall |     10
 sasaki     |      6
 sasaki     |      6
 kimura     |      6
(6 rows)

---

concat(文字列連結)

select concat(name, '(', team, ')') as from users;

/* 結果 */
    concat     
-----------------
 taguchi(red)
 fkoji(blue)
 dotinstall(red)
 sasaki(green)
 sasaki(red)
 kimura(green)
(6 rows)

---

as(別名)

blogapp=# select concat(name, '(', team, ')') as nameteam from users;
    nameteam     
-----------------
 taguchi(red)
 fkoji(blue)
 dotinstall(red)
 sasaki(green)
 sasaki(red)
 kimura(green)
(6 rows)

---

substring(n文字目からm文字取り出す)

blogapp=# select substring(team, 1, 1) as teaminitiale from users;

/* 結果 */
 teaminitiale 
--------------
 r
 b
 r
 g
 r
 g
(6 rows)

---

random(ランダム抽出)

blogapp=# select * from users order by random() limit 1;

/* 結果 */
 id | name  | score | team 
----+-------+-------+------
  2 | fkoji |   8.3 | blue
(1 row)

---

**MySQL**

round()(小数点切り捨て)

select round(5.355); -- 5
select round(5.355, 1); -- 5.4 第2引数に表示する桁数を指定

---

floor()(小数点切り捨て)

select floor(5.833); -- 5
select floor(5.238); -- 6

---

rand()(乱数)

select rand(); -- 乱数
select * from users order by rand() limit 1; -- ランダムで一件取得

---

length()(長さ)

select length('Hello'); -- 5

---

substring()(文字列の一部を取得)

select substring('Hello', 2); -- ello
select substring('Hello', 2, 3); -- ell

---

upper()(大文字に変換)

select upper('Hello'); -- HELLO

---

lower()(小文字に変換)

select lower('Hello'); -- hello

---

concat()(文字列連結)

select concat('Hello', 'World'); -- HelloWorld

---

as(別名)

select length(name) as len, name from users order by len;

+------+------------+
| len  | name       |
+------+------------+
|    5 | fkoji      |
|    6 | Tanaka     |
|    6 | yamada     |
|    7 | taguchi    |
|    7 | tashiro    |
|   10 | dotinstall |
+------+------------+
6 rows in set (0.00 sec)

---

【条件分岐】

if文

**MySQL**

mysql.sql

drop table if exists users; -- usersテーブルがあればusersテーブルを削除

select
  name,
  score,
  if (score > 5.0, 'OK', 'NG') as result
from
  users;

+------------+-------+--------+
| name       | score | result |
+------------+-------+--------+
| taguchi    |   5.8 | OK     |
| fkoji      |   8.2 | OK     |
| dotinstall |   6.1 | OK     |
| Tanaka     |   4.2 | NG     |
| yamada     |  NULL | NG     |
| tashiro    |   7.9 | OK     |
+------------+-------+--------+
6 rows in set (0.00 sec)

---

case文

**MySQL**

whenに値を指定

select
  name,
  score,
  case floor(score) % 2
    when 0 then 'even'
    when 1 then 'odd'
    else null
  end as type
from
  users;

+------------+-------+------+
| name       | score | type |
+------------+-------+------+
| taguchi    |   5.8 | odd  |
| fkoji      |   8.2 | even |
| dotinstall |   6.1 | even |
| Tanaka     |   4.2 | even |
| yamada     |  NULL | NULL |
| tashiro    |   7.9 | odd  |
+------------+-------+------+
6 rows in set (0.00 sec)

whenに条件thenに返却値を指定

select
  name,
  score,
  case 
    when score > 8.0 then 'Team-A'
    when score > 6.0 then 'Team-B'
    else 'Team-C'
  end as Team
from
  users;

+------------+-------+--------+
| name       | score | Team   |
+------------+-------+--------+
| taguchi    |   5.8 | Team-C |
| fkoji      |   8.2 | Team-A |
| dotinstall |   6.1 | Team-B |
| Tanaka     |   4.2 | Team-C |
| yamada     |  NULL | Team-C |
| tashiro    |   7.9 | Team-B |
+------------+-------+--------+
6 rows in set (0.00 sec)

---

【UPDATE】

**PostgreSQL / MySQL**

update users set score = 5.8 where name = 'taguchi';

update users set score = score + 1 where team = 'red';

update users set name = 'sasaki, 'score = score + 1 where name = 'taguchi';

---

【DELETE】

**PostgreSQL / MySQL**

/* usersテーブル全件削除 */
delete from users;

/* 条件を満たすレコードを削除 */
delete from users where score < 4.0;
DELETE 1

---

【複数のテーブルを扱う】

**PostgreSQL / MySQL**

書式

select テーブルA.カラム名, テーブルB.カラム名 from テーブルA, テーブルB where テーブルサーバー全体のゲーム情報を削除すると退会.カラム名 = テーブルB.カラム名;
/* where句では、テーブルAとテーブルBのどのカラムが同一とするかを指定しテーブル同士の結びつきを作成する */

例

select users.name, posts.title from users, posts where users.id = posts.user_id;
/* テーブルusersの「id」とテーブルpostsの「user_id」が同一のものと指定しusersとpostsの結びつけている */

例

/* from句でテーブルに別名を付け文を短縮する */
/* from句が一番最初に実行されるので別名はfrom句で */
select u.name, p.title from users u, posts p where u.id = p.user_id;

/* andを使用し条件を足す */
select u.name, p.title from users u, posts p where u.id = p.user_id and u.id = 1;

---

【viewを使う】

view作成

**PostgreSQL / MySQL**

create view view名 as SELECT文;

create view top3 as select * from users order by score desc limit 3;

---

view使用

**PostgreSQL / MySQL**

select * from view名;

---

view削除

**PostgreSQL / MySQL**

drop view view名;

drop view taguchi_posts;

---

【トランザクション】

**PostgreSQL**

書式

begin;    -- 開始
/* 処理 */
rollback; -- 取り消し
commit;   -- 完了

**MySQL**

書式

start transaction; -- 開始
/* 処理 */
rollback;          -- 取り消し
commit;            -- 完了

---

【サブクエリ】

from句で抽出結果を使用する

**PostgreSQL / MySQL**

select sum(t.score), t.team
from
  (select id, name, score,           -- ここからサブクエリ
    case 
      when score > 8.0 then 'Team-A'
      when score > 6.0 then 'Team-B'
      else 'Team-C'
    end as Team
  from users) as t                   -- ここまで。
group by t.team;

+-------------------+--------+
| sum(t.score)      | Team   |
+-------------------+--------+
|                10 | Team-C |
| 8.199999809265137 | Team-A |
|                14 | Team-B |
+-------------------+--------+
3 rows in set (0.00 sec)

【テーブルの結合】

前提

select * from posts;

+----+---------+--------+
| id | title   | body   |
+----+---------+--------+
|  1 | title 1 | body 1 |
|  2 | title 2 | body 2 |
|  3 | title 3 | body 3 |
+----+---------+--------+

select * from comments;
+----+---------+---------------------------+
| id | post_id | body                      |
+----+---------+---------------------------+
|  1 |       1 | first comment for post 1  |
|  2 |       1 | second comment for post 1 |
|  3 |       3 | first comment for post 3  |
|  4 |       4 | first comment for post 4  |
+----+---------+---------------------------+

---

inner join

• テーブル同士共通のデータだけを取得する

**MySQL**

書式

select 取得するカラム from テーブルA inner join テーブルB on テーブルA.カラム名 = テーブルB.カラム名;
select 取得するカラム from テーブルA join テーブルB on テーブルA.カラム名 = テーブルB.カラム名; -- innerは省略可

例

select * from posts join comments on posts.id = comments.post_id;

+----+---------+--------+----+---------+---------------------------+
| id | title   | body   | id | post_id | body                      |
+----+---------+--------+----+---------+---------------------------+
|  1 | title 1 | body 1 |  1 |       1 | first comment for post 1  |
|  1 | title 1 | body 1 |  2 |       1 | second comment for post 1 |
|  3 | title 3 | body 3 |  3 |       3 | first comment for post 3  |
+----+---------+--------+----+---------+---------------------------+
3 rows in set (0.00 sec)

例

-- 取得するカラムを記述する際、from句で指定した軸になるテーブルのテーブル名は省略できる
select posts.id, title, body as posts_body, comments.body as commnets_body from posts join comments on posts.id = comments.post_id;

+----+---------+------------+---------------------------+
| id | title   | posts_body | commnets_body             |
+----+---------+------------+---------------------------+
|  1 | title 1 | body 1     | first comment for post 1  |
|  1 | title 1 | body 1     | second comment for post 1 |
|  3 | title 3 | body 3     | first comment for post 3  |
+----+---------+------------+---------------------------+```

---

outer join

• 片方にしかレコードがない場合でも抽出する

**MySQL**

書式

/* 軸になる(左側)テーブルを基準にする */
select 取得するカラム名 from テーブルA left outer join テーブルB on テーブルA.結びつけるカラム名 = テーブルB.結びつけるカラム名;
select 取得するカラム名 from テーブルA left join テーブルB on テーブルA.結びつけるカラム名 = テーブルB.結びつけるカラム名;  -- outerは省略可

/* 結合する(右側)テーブルを基準にする */
select 取得するカラム名 from テーブルA right outer join テーブルB on テーブルA.結びつけるカラム名 = テーブルB.結びつけるカラム名;
select 取得するカラム名 from テーブルA right join テーブルB on テーブルA.結びつけるカラム名 = テーブルB.結びつけるカラム名; -- outerは省略可

例

/* 左側のpostsを軸に外部結合 */
/* commentsテーブルにはpost_id = 2のデータは存在しないためNULL */
select * from posts left join comments on posts.id = comments.post_id;

+----+---------+--------+------+---------+---------------------------+
| id | title   | body   | id   | post_id | body                      |
+----+---------+--------+------+---------+---------------------------+
|  1 | title 1 | body 1 |    1 |       1 | first comment for post 1  |
|  1 | title 1 | body 1 |    2 |       1 | second comment for post 1 |
|  2 | title 2 | body 2 | NULL |    NULL | NULL                      |
|  3 | title 3 | body 3 |    3 |       3 | first comment for post 3  |
+----+---------+--------+------+---------+---------------------------+
4 rows in set (0.01 sec)

例

/* 左側のcommentsを軸に外部結合 */
/* postsテーブルにはid = 4のデータは存在しないためNULL */
select * from posts right join comments on posts.id = comments.post_id;

+------+---------+--------+----+---------+---------------------------+
| id   | title   | body   | id | post_id | body                      |
+------+---------+--------+----+---------+---------------------------+
|    1 | title 1 | body 1 |  1 |       1 | first comment for post 1  |
|    1 | title 1 | body 1 |  2 |       1 | second comment for post 1 |
|    3 | title 3 | body 3 |  3 |       3 | first comment for post 3  |
| NULL | NULL    | NULL   |  4 |       4 | first comment for post 4  |
+------+---------+--------+----+---------+---------------------------+
4 rows in set (0.00 sec)

【外部キー制約】

外部キー制約を追加

• 外部キー制約を付けるカラムは、紐づくカラムと同じ型にする必要がある
• 紐づくレコードが存在しない場合レコードを挿入できない
• カラムに外部キー制約が付いている場合deleteで削除できなくなる

**MySQL**

**前提**

create table users (
  id int unsigned primary key auto_increment,
  name varchar(255)
);

create table posts (
  id int unsigned primary key auto_increment,
  user_id int unsigned not null
);

書式

/* カラムAはテーブルAに存在するカラムBと関連づけるカラム */
/* テーブルBは関連づけるテーブル名 */
/* カラムBは関連づけるカラム名 */
alter table テーブルA add constraint キー名 foreign key (カラムA) references テーブルB (カラムB);

例

alter table posts add constraint fk_posts foreign key (user_id) references users (id);

---

外部キー制約を削除

**MySQL**

書式

alter table テーブル名 drop foreign key キー名;

例

alter table posts drop foreign key fk_posts;

【トリガー】

• トリガー起動時に実行されるSQL文において、old.カラム名とNEW.カラム名を使って参照可能。
• トリガー操作がINSERTの場合はNEW.カラム名で新しく追加されたデータの値を参照できる。
• トリガー操作がDELETEの場合は OLD.カラム名で削除されたデータの値を参照できる。
• トリガー操作操作がUPDATEの場合はOLD.カラム名とNEW.カラム名の両方が使用でき更新される前の値と更新された後の値を参照できる。

トリガー作成

**MySQL**

書式

/* トリガーが起動する操作は{ insert | update | delete }の3種類 */
/* { before | after }でトリガーの発火を操作前か操作後に設定 */
create trigger トリガー名 { before | after } { insert | update | delete } on テーブルA for each row insert into テーブルB (カラム名) values (値);

例

create trigger posts_insert_trigger after insert on posts for each row insert into logs (msg) values ('post added!');

例２

drop table if exists users;

create table users (
  id int unsigned primary key auto_increment,
  name varchar(255)
);

drop table if exists logs;

create table logs (
  id int unsigned primary key auto_increment,
  msg text
);

drop trigger if exists users_update_logs;

delimiter // -- 文の区切り文字を変更
create trigger users_update_logs after update on users for each row
  begin
    insert into logs (msg) values ('users updated');
    insert into logs (msg) values (concat(old.name, '->', new.name)); -- old, newは関数
  end;
//  -- 文の終端
delimiter ; -- 文の区切り文字を戻す

insert into users (name) values ('name1');
insert into users (name) values ('name2');
insert into users (name) values ('name3');

update users set name = 'new' where id = 2; -- トリガー発火

select * from users;
select * from logs;

+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | new   |
|  3 | name3 |
+----+-------+
3 rows in set (0.00 sec)

+----+---------------+
| id | msg           |
+----+---------------+
|  1 | users updated |
|  2 | name2->new    |
+----+---------------+
2 rows in set (0.00 sec)

---

トリガー一覧

**MySQL**

/* \Gは拡張表示オプション */
show triggers \G;

---

トリガー削除

**MySQL**

書式

drop trigger トリガー名;

例

drop trigger posts_insert_trigger;

---

【バックアップ / リストア】

**MySQL**

• mysqldump(バックアップ作成)
• \. ./バックアップファイル名(リストア)

dump

$ mysqldump -u ユーザ名 -p データベース名 > ファイル名

リストア

$ \. ./バックアップファイル名