MySQLの復習@7日目

複数のテーブルから抽出

(select [col] from [tab1]) union
(select [col] from [tab2]);

# 3つ以上の union も可能
(select [col] from [tab1]) union
(select [col] from [tab2]) union
(select [col] from [tab3]);

# 条件付き抽出
(select [col] from [tab1] where...) union
(select [col] from [tab2] where...);

複数の抽出対象に同じレコードがあるときは，レコードの重複を許してフィルタリングしたほうが効率が良い（重複をとりのぞく処理をしないため）．

(select ...) union all (select ...);

内部結合

複数のテーブルのレコードを結合したレコードを表示できる．

# join は from tab join の tab を修飾するイメージ．
select [tab.col] from [tab1] join [tab2] on ...
# 重複を許さない
select distinct [tab.col] from [tab1] join [tab2] on ...

# テーブルのカラムか明記するために tab.col と書く必要あり
select tb1.name, tb2.age from tb1
join tb2 on tb1.name == tb2.name;

# テーブルのエイリアス利用するとスッキリ書ける
# 実際のデータベース名は長いので，簡単に書くために
select x.name, y.age from tb1 as x
join tb2 as y on x.name == y.name;

# on の代わりに using を使える
# 両方のテーブルにある同名のカラムをきーにするときのみ
select x.name, y.age from tb1 as x join tb2 as y using(name);

# 条件を加える
select [tab].[col] from [tab1] join [tab2] using([col]) where ...

# 複数の join
select [col] from [tab1]
join [tab2] using([col])
join [tab3] using([col])...

外部結合

# 左外部結合（左=tab1の全てのレコードを表示）
select [col] from [tab1] left join [tab2] on ...;

# 右外部結合（左=tab2の全てのレコードを表示）
select [col] from [tab1] right join [tab2] on ...;

自己結合

select [col] from [tab] as [x] join [tab] as [y];

サブクエリ

select ... in ([sub-query]);

# 最大の age をもつレコードを抽出
select * from tab where age = (select max(age) from tab);
select * from tab where age in (select max(age) from tab);

# 平均以上 age をもつレコードを返す
select * from tab where age >= (select avg(age) from tab);

# カラムを返すサブクエリを in で使う
select * from tab1 where name in
(select name from tab2 age >= 20);

# exists & not exists
# tab2 に name が存在するときのみ，
# tab1 のレコードを取り出す．
select * from tab1 where name
exists (select * from tab2 tab1.name = tab2.name);

select * from tab1 where name
not exists (select * from tab2 tab1.name = tab2.name);

ランク付け

# 自己結合を使って
select x.name, x.age, count(*) as rank
from tab as x
join tab as y where x.age <= y.age
group by x.name order by rank

# サブクエリを使って
# （ランク付けされた新しいテーブルを作る）
create table sort_age like memeber;
alter table sort_age add rank int auto_increment primary key;
insert into sort_age (select name, age, rank)
(select name, age from member order by age desc);

忘れてたこと

条件に一致するレコードの削除

delete from [tab] where [condition];