SQL文の練習
事前準備
PostgreSQLは後付け
コマンド変な場合は以下をご確認ください
データベースの確認
code:console
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
postgres=# \l
ちょっと補足
SQL文には大文字小文字の区別はないです。今回は小文字で統一して書いています。
世の例では大文字が多いかもしれません。
データベースの作成
create database データベース名;
code:console
mysql> create database sample;
Query OK, 1 row affected (0.00 sec)
データベースへの接続
mysql
use データベース名;
connect データベース名;
postgres
\c データベース名;
code:console
mysql> use sample;
Database changed
postgres=# \c sample
※文字数が少ないので私はuseを使う場合が多い。
バックスラッシュ打ち方 : option + ¥
テーブルの確認
code:console
mysql> show tables;
Empty set (0.00 sec)
sample=# \d
Did not find any relations.
※テーブルは存在しない、という意味。
テーブルの作成
実はここが一番難しい、、かも。
構文
code:syntax
create table テーブル名 (
)
// mysql例
create table book (
no integer(4) primary key,
name varchar(10)
);
// postgres例
create table book (
no integer primary key,
name varchar(10)
);
※「,」は最後の列名にはつけてはいけない。
code:console
mysql> create table book (
-> no integer(4) primary key,
-> name varchar(10)
-> );
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_sample |
+------------------+
| book |
+------------------+
1 row in set (0.01 sec)
sample=# \d
※「primary key」は主キーと呼ばれるもの
意味等は別テキストで。
DDLの実行
data definition language
データを格納するための構造を定義するためのもの（言語）
SQLにおけるDDLには、データベースやテーブル、ビューなどの作成を行うCREATE文や、削除するDROP文、変更を加えるALTER文、データを全削除するTRUNCATE文などが含まれる。
SQL文の練習用のサンプルデータを実行します。
以下のSQL文の実行結果は一部省略している。
DDLを更新する可能性があるから。
select文
基本) select 列名 from テーブル名;
code:console
mysql> select no, name, sal from emp;
+------+------------+--------+
| no | name | sal |
+------+------------+--------+
| 1001 | 田中太郎 | 300000 |
| 2002 | 佐藤一郎 | 250000 |
| 2003 | 鈴木花子 | 230000 |
| 3001 | 中村瞳 | 100000 |
| 3002 | 加藤さくら | 150000 |
| 3003 | 渡辺二郎 | 150000 |
| 3004 | 山口葵 | 120000 |
| 4001 | 林三郎 | 80000 |
| 4002 | 山田史郎 | 60000 |
| 4003 | 斎藤吾郎 | 65000 |
| 4004 | 山本晴彦 | 55000 |
| 4005 | 松本綾 | 77000 |
| 4006 | 吉田舞 | 40000 |
+------+------------+--------+
13 rows in set (0.00 sec)
列名を「*」にすると全ての列のデータを表示できる。
とりあえず中身を知りたいときによく使う。
code:console
mysql> select * from emp;
mysql> select * from emp_pos;
キーワード distinct
code:console
mysql> select distinct hiredate from emp;
グループ関数
件数の取得
count関数
code:console
mysql> select count(*) from emp;
// テーブル全体の行数を知る場合「*」でもよいがprimary keyを指定してもよい
mysql> select count(no) from emp;
// 指定した列名で「NULL」は数えない
mysql> select count(quitdate) from emp;
他のグループ関数
table:group_function
min 最小値を求める
max 最大値を求める
sum 合計値を求める
avg 平均値を求める
最小値の取得
min関数
code:console
mysql> select min(sal) from emp;
算術演算子
table:operator
+ 加算
- 減算
* 乗算
/ 除算
% 剰余
一か月分の営業日全て出勤したと仮定した場合の日当
code:console
mysql> select sal / 20 from emp;
その他の関数
別名をつける
キーワード as
code:console
mysql> select hiredate as "入社日" from emp;
select文 + 検索条件
select 列名 from テーブル名 where 条件;
給料(sal)が100000以上のレコードを表示する。
code:console
mysql> select * from emp where sal >= 100000;
table:条件演算子
= 同じ
< より小さい
より大きい
!=, <> 同じでない
is null nullである
is not null nullでない
ポイントはnullだけ条件の書き方が違うところ
退職日(quitdate)がnullのレコードを表示する。
code:console
mysql> select * from emp where quitdate is null;
メモ）
select * from emp where quitdate notnull;
複数条件を組み合わせるときは
AND, &&（論理積・かつ）や
OR, ||（論理和・または）
を利用する。
給料(sal)が100000以上かつ退職日(quitdate)がnullのレコードを表示する。
code:console
mysql> select * from emp where sal >= 100000 and quitdate is null;
「入社日が2010-04-01以前または入社日が2018-04-01以後」なおかつ「退職日(quitdate)がnull」
code:console
mysql> select * from emp where hiredate <= '2010-04-01' or hiredate >= '2018-04-01' and quitdate is null;
mysql> select * from emp where (hiredate <= '2010-04-01' or hiredate >= '2018-04-01') and quitdate is null;
上記二つの違い・題意と合うのはどちらか
※ANDとORならANDの方が優先される。
between演算子
給料(sal)が80000以上かつ200000以下のレコードを表示する。
code:console
mysql> select * from emp where sal between 80000 and 200000;
日付）
select * from emp where hiredate between '2010-04-01' and '2013-04-01';
in演算子
役職(posno)が社員(2)とアルバイト(4)のレコードを表示する。
code:console
mysql> select * from emp where posno in (2, 4);
余談) not in だと処理が遅くなるので、使わない方が良い。
like演算子
文字列のパターン・マッチングに使用します。
以下のメタ・キャラクタ（特殊記号）を利用可能
%...ゼロ以上の任意の文字にマッチ
_...任意の1文字にマッチ
名前の頭文字が「山」のレコードを表示する。
名前の頭文字が「山」で3文字のレコードを表示する。
code:console
mysql> select * from emp where name like '山%';
mysql> select * from emp where name like '山__';
ソート
select 列名 from テーブル名 【where 条件】 order by 列名 【desc | asc 】;
「order by 列名」だけ書くと、デフォルトは昇順（asc）
役職(posno)降順に並び変えてレコードを表示する。
code:console
mysql> select * from emp order by posno desc
複数指定することも可能。
先に役職(posno)降順で並び変え、給与(sal)を昇順に並び変える
code:console
mysql> select * from emp order by posno desc, sal;
グループ化と集計
select 列名, グループ関数(列名) 【, グループ関数(列名)】 from テーブル名 group by 列名;
役職（posno）ごとの給料の最大値
code:console
mysql> select posno, max(sal) from emp group by posno;
基本的に「group by」で指定した列名は表示でも指定する。
逆に「group by」に指定していない列名を表示させると変。
以下、給料が最大値の人の名前が表示されるわけではない。
code:console
mysql> select name, posno, max(sal) from emp group by posno;
複数、列を指定することも可能 例としては微妙
code:console
mysql> select posno, hiredate, max(sal) from emp group by posno, hiredate;
having句
グループに対して条件を指定する場合はhaving句を使う。
select 列名, グループ関数(列名) 【, グループ関数(列名)】 from テーブル名 group by 列名 having 条件;
役割ごとの給料が最大200000より大きい役割を表示する。
code:console
mysql> select posno, max(sal) from emp group by posno having max(sal) > 200000;
副問い合わせ（サブクエリ）
select文を複数使って、1つのSQL文を作るイメージ
例えば、平均給料よりも給料が高い従業員を表示する、といった場合
code:console
// 確認
mysql> select avg(sal) from emp;
mysql> select * from emp where sal > (select avg(sal) from emp);
where句（条件）の中のselect文を「副問い合わせ（サブクエリ）」と呼ぶ。
複数回select文を実行しなくても、一度に結果を表示できる。
結合
取得したいデータが複数のテーブルに分割されている場合に実行する。
結合には二種類ある。
結合条件に指定した値が共通で含まれるデータのみ取り出す場合に使用
外部結合
結合条件に指定した値が共通で含まれていないデータも取り出す場合に使用
準備
code:console
insert into emp(no, name, hiredate, sal, quitdate, posno) values(5001, '外道新', '2021-11-1', 60000, null, 5);
データ確認
code:console
mysql> select * from emp;
+------+------------+------------+--------+------------+-------+
| no | name | hiredate | sal | quitdate | posno |
+------+------------+------------+--------+------------+-------+
| 1001 | 田中太郎 | 2010-04-01 | 300000 | NULL | 1 |
| 2002 | 佐藤一郎 | 2010-04-01 | 250000 | NULL | 2 |
| 2003 | 鈴木花子 | 2017-10-01 | 230000 | NULL | 2 |
| 3001 | 中村瞳 | 2010-04-01 | 100000 | 2016-10-31 | 3 |
| 3002 | 加藤さくら | 2010-04-01 | 150000 | NULL | 3 |
| 3003 | 渡辺二郎 | 2014-04-01 | 150000 | NULL | 3 |
| 3004 | 山口葵 | 2016-04-01 | 120000 | NULL | 3 |
| 4001 | 林三郎 | 2015-04-01 | 80000 | 2018-03-01 | 4 |
| 4002 | 山田史郎 | 2016-04-01 | 60000 | NULL | 4 |
| 4003 | 斎藤吾郎 | 2017-04-01 | 65000 | NULL | 4 |
| 4004 | 山本晴彦 | 2018-04-01 | 55000 | NULL | 4 |
| 4005 | 松本綾 | 2018-06-01 | 77000 | NULL | 4 |
| 4006 | 吉田舞 | 2020-04-01 | 40000 | NULL | 4 |
| 5001 | 外道新 | 2021-11-01 | 60000 | NULL | 5 |
+------+------------+------------+--------+------------+-------+
14 rows in set (0.00 sec)
mysql> select * from emp_pos;
+----+------------+
| no | name |
+----+------------+
| 1 | 店長 |
| 2 | 社員 |
| 3 | パート |
| 4 | アルバイト |
+----+------------+
4 rows in set (0.00 sec)
内部結合の確認
select 列名 from テーブル名1 inner join テーブル名2 on 結合条件;
code:console
mysql> select emp.name, emp_pos.name from emp inner join emp_pos on emp.posno = emp_pos.no;
+------------+------------+
| name | name |
+------------+------------+
| 田中太郎 | 店長 |
| 佐藤一郎 | 社員 |
| 鈴木花子 | 社員 |
| 中村瞳 | パート |
| 加藤さくら | パート |
| 渡辺二郎 | パート |
| 山口葵 | パート |
| 林三郎 | アルバイト |
| 山田史郎 | アルバイト |
| 斎藤吾郎 | アルバイト |
| 山本晴彦 | アルバイト |
| 松本綾 | アルバイト |
| 吉田舞 | アルバイト |
+------------+------------+
13 rows in set (0.01 sec)
ポイント
それぞれのテーブルで同じ列名がある場合は「テーブル名.列名」で区別する。
「結合条件」は「テーブル1の(条件となる)列名 = テーブル2の列名」
先ほど追加した「外道新」は表示されていない。
empテーブルでは「posno=5」を追加したが、emp_posテーブルでは「posno=5」がないため
外部結合の確認
select 列名 from テーブル名1 left (right) join テーブル名2 on 結合条件;
code:console
mysql> select emp.name, emp_pos.name from emp left outer join emp_pos on emp.posno = emp_pos.no;
+------------+------------+
| name | name |
+------------+------------+
| 田中太郎 | 店長 |
| 佐藤一郎 | 社員 |
| 鈴木花子 | 社員 |
| 中村瞳 | パート |
| 加藤さくら | パート |
| 渡辺二郎 | パート |
| 山口葵 | パート |
| 林三郎 | アルバイト |
| 山田史郎 | アルバイト |
| 斎藤吾郎 | アルバイト |
| 山本晴彦 | アルバイト |
| 松本綾 | アルバイト |
| 吉田舞 | アルバイト |
| 外道新 | NULL |
+------------+------------+
14 rows in set (0.00 sec)
mysql> select emp.name, emp_pos.name from emp right outer join emp_pos on emp.posno = emp_pos.no;
+------------+------------+
| name | name |
+------------+------------+
| 田中太郎 | 店長 |
| 鈴木花子 | 社員 |
| 佐藤一郎 | 社員 |
| 山口葵 | パート |
| 渡辺二郎 | パート |
| 加藤さくら | パート |
| 中村瞳 | パート |
| 吉田舞 | アルバイト |
| 松本綾 | アルバイト |
| 山本晴彦 | アルバイト |
| 斎藤吾郎 | アルバイト |
| 山田史郎 | アルバイト |
| 林三郎 | アルバイト |
+------------+------------+
13 rows in set (0.00 sec)
ポイント
left (right)は基準となるテーブルを指定している。
leftは左側、rightは右側に書いたテーブルが基準となる。
基準となるテーブルのデータは全て表示されて、データがない列は「NULL」となる。
left rightの二種類があるが、私の経験ではleftしか使ったことはない。
leftに書き方を統一してテーブルの書く順番を調整する形
update文
テーブルのレコードを更新する。
update テーブル名 set 列名1 = 値1 【, 列名2 = 値2】 where 条件;
山田史郎さんの給料(sal)を80000に変更する。
斎藤吾郎さんの給料(sal)を100000、役割(posno)を社員(2)に変更する。
code:console
mysql> update emp set sal = 80000 where no = 4002;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update emp set sal = 100000, posno = 2 where no = 4003;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
where句を指定し忘れないことがすごく大事
実際にupdate文を実行するときは、前もってテキストエディタで用意しておくのがおすすめ
insert文
テーブルにレコードを挿入する。
insert into テーブル名 ( 列名 【, 列名】) values ( 値 【, 値】);
code:console
mysql> insert into emp(no, name, hiredate, sal, quitdate, posno) values(4007, '追田武', '2021-12-1', 60000, null, 4);
Query OK, 1 row affected (0.01 sec)
値を指定しなかった場合、（「デフォルト制約」がない限り）基本的には「NULL」が入る。
実際にinsert文を実行するときは、前もってテキストエディタで用意しておくのがおすすめ
delete文
テーブルのレコードを削除する。
delete from テーブル名 where 条件;
code:console
mysql> delete from emp where no = 4007;
Query OK, 1 row affected (0.00 sec)
where句を指定し忘れないことがすごく大事
実際にdelete文を実行するときは、前もってテキストエディタで用意しておくのがおすすめ
やっちゃいけないことをやってみよう！
where句をつけずにupdateする。
code:console
$ update emp set sal = 90000;
$ delete from emp;
DBeaverだと怒ってくれるレベル。
一番抑えたいところ
update（delete文）に程よく恐れるべし。
updateは手元で下書きする。
まとめ
めも
4文字
検索っぽく