【MySQL】SQLのデータ操作(DML)の書き方まとめ

2022年5月16日データベース

SQLでとりわけデータ操作に関する書き方をまとめました。

テーブル定義に関しては、SQLのデータ定義(DDL)の書き方まとめを参考にしてみてください。

レコードを挿入する

insert into: 単一レコードを挿入する

insert into テーブル名(属性１, 属性２) values (値１, 値２);

insert into mst_shops(name, updated_by) values ('shop1, 'hoge');
show full columns from mst_prefs;

auto_incrementやデフォルト値があるものは、指定する必要はありません。
文字列のときは、"シングルクォーテーションで囲みます。(ダブルクオーテーションでも可)

insert into: 複数レコードを挿入する

insert into テーブル名(属性１, 属性２) values (値１, 値２),
(値１, 値２),
(値１, 値２),
(値１, 値２);

複数レコードの挿入には、valuesの後をカンマで続けていきます。

外部キー制約が入っている場合の注意点

外部キー制約が入っている場合、親テーブルから挿入する必要があります。
親テーブルにないものを子テーブルから挿入するとエラーになります。

特定のレコードを取得

select 取得したい属性 [as label] from テーブル名 [as alias]

取得したい属性を指定してselectすることで、特定のレコードを取得できます。

*: すべての属性を取得

-- *: すべての属性を取得
select count(*) from データベース名.テーブル名;

count(*): レコード件数を取得

-- count(*): レコード件数を取得
select count(*) from データベース名.テーブル名;
select count(1) from データベース名.テーブル名;

as: 列ラベルの変更

-- as: 列ラベルの変更
select id as "ID", name as "label" from データベース名.テーブル名;

asは省略しても大丈夫です。

distinct: 重複レコードを省いて選択

-- distinct: 重複レコードを省く
select distinct name "label" from データベース名.テーブル名;

distinctで重複を除くことができます。

count(distinct 属性): 重複行を省いた件数を取得

-- 重複行を省いた件数
select count(distinct name) from データベース名.テーブル名 as mp 

countと合わせることで、重複した行を省いた件数を取得できます。

テーブルのレコードを削除

【WHERE】条件句を使って、条件の指定して削除することが多いです。

delete from: 子テーブルの削除

delete from データベース名.テーブル名;

select *: レコードの確認

-- レコードの確認
select * from データベース名.テーブル名;

削除したとき、削除できているかを確認するには、select *を使います。

show create table: テーブル定義の確認

-- テーブル定義の確認
show create table データベース名.テーブル名;

テーブル定義の確認しときは、show create tableです。

外部キー制約が入っている場合の注意点

delete from データベース名.子テーブル名;
delete from データベース名.親テーブル名;

外部キー制約が入っている場合、子テーブルの方から削除する必要があります。
親テーブルから削除するとエラーになります。

WHERE条件句

WHERE条件句には、以下のような種類があります。

• = : 一致
• <>, != : 非一致
• >, >=, <, <= : 数値の比較
• A and B : A かつ B
• A or B : A または B
• () : 条件をくくる
• like : %で部分一致検索
• in : いずれかの値に一致
• not in : いずれの値にも一致しない
• between A and B: A から B の値
• is not null : null以外に一致
• is null : nullに一致

順番に見ていきます。

= : 一致

-- = : 一致
select * from データベース名.テーブル名
where 属性 = 1;

=で指定したものに一致するものを選択できます。

<>, != : 非一致

-- <>, != : 非一致
select * from データベース名.テーブル名
where 属性 != 1;
-- where 属性 <> 1;

<>もしくは!=で指定したもの以外を取得できます。

>, >=, <, <= : 数値の比較

-- >, >=, <, <= : 数値の比較
select * from データベース名.テーブル名
where 属性 >= 60;

A and B : A かつ B

-- A and B : A かつ B
select * from データベース名.テーブル名
where 属性1 = 1 and 属性2 = 1;

A or B : A または B

-- A or B : A または B
select * from データベース名.テーブル名
where 属性1 = 1 or 属性2 = 1;

() : 条件をくくる

-- () : 条件をくくる
select * from データベース名.テーブル名
where (属性 = 1 and 属性2 = 1)
or (属性1 = 2 and 属性2 = 2);

like : %で部分一致検索

-- like : %で部分一致検索
-- _とした場合には一文字に一致
select * from データベース名.テーブル名
where 属性 like '商%';

likeと%を使うことで、%部分がなんでもOKな部分一致でレコードを取得できます。

in () : いずれかの値に一致

-- in : いずれかの値に一致
select * from データベース名.テーブル名
where 属性 in ('商品A', '商品B');

inを使うことで、()でくくった値に合うものを取得できます。

not in () : いずれの値にも一致しない

-- not in () : いずれの値にも一致しない
select * from データベース名.テーブル名
where 属性 not in ('商品A', '商品B');

not inを使うことで、()でくくった値以外のものを取得できます。

between A and B: A から B の値

-- between A and B: A から B の値 
select * from データベース名.テーブル名
where 属性 between 30 and 100;

is not null : null以外に一致

-- is not null : null以外に一致
select * from データベース名.テーブル名
where 属性 is not null;

null以外の値を取得できます。

is null : nullに一致

-- is null : nullに一致
select * from データベース名.テーブル名
where 属性 is null;

nullの値の保持しているレコードを取得できます。

ソート順を指定

order by 属性 [acs|desc]

[asc]は、ascending 昇順で、デフォルト値です。
[desc]は、descending 降順です。

order by : 単一キーによるソート

-- 単一キーによるソート
select * from データベース名.テーブル名
order by 属性 desc;

属性を一つの場合、シンプルに上のようになります。

order by a, b:複数キーによるソート

-- 複数キーによるソート
select * from データベース名.テーブル名
order by 属性1 desc, 属性2 asc;

上のように、order byはカンマで続けることができます。

複数キーやWHERE条件句とともに使う

-- 条件付き
select * from データベース名.テーブル名
where 属性1 > 100
order by 属性1 desc, 属性2 asc;

上のような順番でWHERE条件句との併用も可能です。

取得レコード数の指定・制限する

limitを使うことでレコード数を制限して取得することが可能です。

limit : 取得レコード数の限定

-- 取得レコード数の限定
select * from データベース名.テーブル名
order by 属性1 desc,
属性2 asc
limit 4;

上の場合4件取得できます。

limit n1 offset n2 : オフセット付き

-- オフセット付き（0がデフォルト値）
-- オフセット, レコード数
select * from test_db.txn_stocks limit 1 offset 3;
select * from test_db.txn_stocks limit 3, 1;

上２つの書き方があります。
limitのあとにくる数値は取得したいレコード数です。
offsetのあとにくる数値は、上から除外死体レコードの数です。

レコードを更新する

update テーブル名 set 属性1 = 値1, where 条件

レコードの更新には、updateを使います。
そして、setで指定した属性に指定した値を代入できます。

更新するときの注意点

select文とupdate文の内容はほとんど同じになるので、
ミスを防ぐためにかならずselectで内容を確認してから更新しましょう。

-- 更新前の確認する
select * from テーブル名
where 条件;
-- 更新する
update テーブル名 set 属性1 = 値1
where 条件;

テーブルの結合

2つのテーブルを結合して両テーブルのレコードを返します。
代表的なものに以下2つがあります。

• 内部結合
• 外部結合

以下にそれぞれを説明します。

テーブルの内部結合（INNER JOIN）

内部結合は、2つのテーブルを結合した際に両テーブルに存在するレコードを返します。
両テーブルを円とすると、円の重なる部分を取り出すのが、内部結合です。

select * from テーブル1
inner join テーブル2
on テーブル1.値が一致する属性 = テーブル2.値が一致する属性;

上のようにすることで、テーブルを結合することができます。
内部結合は、テーブルの指定したフィールドの値が一致するデータしか抽出しません。

inner join … on : テーブルの内部結合

テーブルは以下のように別名をつけることも可能です。

use db;
select
mp.name "商品名", ms.name "ショップ名"
from
mst_products mp 
inner join mst_shops ms 
on mp.product_id = ms.id;

別名をつけるところは、fromのあとです。

select
mp.name "商品名", ms.name "ショップ名"
from
mst_products mp 
inner join mst_shops ms 
on mp.product_id = ms.id
where ms.id = 5;

条件文をつけたいときは、onのあとにwhere条件句を続けます。

where : テーブルの内部結合

use db;
select
mp.name "商品名", ms.name "ショップ名"
from
mst_products mp 
mst_shops ms 
where mp.product_id = ms.id;

whereを使ってもテーブルを結合することが可能です。
fromのあとに2つのテーブルを記載して、whereで紐付けるものを指定します。

複数のINNER JOINを使用する

inner join テーブル1 on 結合条件
inner join テーブル2 on 結合条件

複数のINNER JOINを使用する場合には上のように記述します。

テーブルの外部結合

外部結合は、以下の種類があります。

• 左外部結合
• 右外部結合
• フルの外部結合(MySQLでは未実装)

left join : 左外部結合

select * from 左テーブル
left join 右テーブル 
on 左テーブル.値が一致する属性 = 右テーブル.値が一致する属性;

左外部結合は、2つのテーブルを結合した際に、左側のテーブルに
存在するレコードを返します。
2つのテーブルの左のレコードと、左のレコードのデータとつながっている右のデータを返します。

right join : 右外部結合

select * from 右テーブル
right join 左テーブル 
on 右テーブル.値が一致する属性 = 左テーブル.値が一致する属性;

leftを逆にしただけです。

右外部結合は、2つのテーブルを結合した際に、右側のテーブルに
存在するレコードを返します。
2つのテーブルの右のレコードと、右のレコードのデータとつながっている左のデータを返します。

外部結合(Full Outer Join)

外部結合(Full Outer Join)は、条件に当てはまる2つのテーブルの
すべてのレコードを返します。
MySQLは、このFull Outer Joinは実装されていません。