MySQL：結合 #13

#(16)テーブルの結合
#    基本は正規化、パフォーマンスに問題がある場合は非正規化
#    外部キーと主キーで結合する
-- (1)内部結合　inner join
select
  users.id,
  last_name,
  first_name,
  prefectures.name
from #テーブル名1
  users
inner join #テーブル名2
  prefectures
on users.prefecture_id=prefectures.id;#結合する列を指定する　テーブル１の外部キー=テーブル２の主キー
-- (2)(1)を省略してみる as join
select
  u.id,
  last_name,
  first_name,
  p.name
from #テーブル名1
  users as u #さらに as 自体も省略可能
join #テーブル名2 # 区別するために基本は省略せずにinner joinと書くこと
  prefectures as p #さらに as 自体も省略可能
on u.prefecture_id=p.id;
-- (3)絞り込む
select
  u.id,
  u.last_name,
  u.first_name,
  p.name
from
  users u
inner join
  prefectures p
on
  u.prefecture_id=p.id
where
  u.gender=2;

-- (4)inner join 例
select
  o.id,
  o.order_time,
  o.amount,
  u.id,
  u.last_name,
  u.first_name,
  u.prefecture_id
from
  orders o
inner join
  users u
on
  o.user_id=u.id
where
  u.prefecture_id=13
  and o.order_time>='2017-01-01 00:00:00'
  and o.order_time<'2017-02-01 00:00:00'
order by
  o.id;

-- (5)外部結合 left outer join / right outer join
select
 u.last_name last_name,
 u.id user_id,
 o.user_id order_user_id,
 o.id order_id
from
  users u
-- inner join #結果：nullデータは存在しない
left outer join#結果：idをすべて出力するのでnullデータも存在する
  orders o
on u.id=o.user_id
order by u.id;

-- (6)外部結合　例 注文された商品の個数を出力
select
  p.id,
  p.name,
  sum(od.product_qty) num
from
  products p
left outer join
  order_details od
on
  p.id=od.product_id
group by p.id;

-- (7)４つのテーブルを結合
select
  o.id order_id,
  o.user_id user_id,
  u.last_name,
  u.first_name,
  o.amount amount,
  o.order_time order_time,
  od.product_price,
  p.price product_price,
  p.name product_name 
from orders o
inner join
  order_details od
on o.id=od.order_id
inner join
  products p
on od.product_id=p.id
inner join
  users u
on o.user_id=u.id;

-- (8)多数 vs 多数の　結合
#中間テーブルが必要：products_categoriesテーブル
#productsテーブルとcategoriesテーブルをつなぐ 
select
 p.id product_id,
 p.name product_name,
 c.name
from
  products p
inner join
  products_categories pc
  on p.id=pc.product_id
inner join
  categories c
  on pc.category_id=c.id
where
  p.id=3;

-- (9)テーブルの足し算 union / union all
-- 列数をそれぞれ合わせること。それぞれのデータ型を一致させること。

select
  u.email user_email,
  u.last_name,
  u.first_name,
  u.gender
from
  users u

union#ここのセミコロンをunionに変更するだけ

select
  adu.email user_email,
  adu.last_name,
  adu.first_name,
  adu.gender
from
  admin_users adu;

-- (10)order byは　最後に一度だけしか使用できない
select
  u.email user_email,
  u.last_name,
  u.first_name,
  u.gender
from
  users u
where gender=1

union#ここのセミコロンをunionに変更するだけ　重複データは削除される
-- union all 重複したデータも削除されない

select
  adu.email user_email,
  adu.last_name,
  adu.first_name,
  adu.gender
from
  admin_users adu
where gender=2

order by gender;

#記述順序：select>from>結合処理>where>group by>having>order by>limit
#実行順序：from>結合処理>where>group by>select>having>orderby>limit
#selectの順序だけが異なることに注意

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

#(16)テーブルの結合

# 基本は正規化、パフォーマンスに問題がある場合は非正規化

# 外部キーと主キーで結合する

-- (1)内部結合　inner join

select

users.id,

last_name,

first_name,

prefectures.name

from #テーブル名1

users

inner join #テーブル名2

prefectures

on users.prefecture_id=prefectures.id;#結合する列を指定する　テーブル１の外部キー=テーブル２の主キー

-- (2)(1)を省略してみる as join

select

u.id,

last_name,

first_name,

p.name

from #テーブル名1

users as u #さらに as 自体も省略可能

join #テーブル名2 # 区別するために基本は省略せずにinner joinと書くこと

prefectures as p #さらに as 自体も省略可能

on u.prefecture_id=p.id;

-- (3)絞り込む

select

u.id,

u.last_name,

u.first_name,

p.name

from

users u

inner join

prefectures p

u.prefecture_id=p.id

where

u.gender=2;

-- (4)inner join 例

select

o.id,

o.order_time,

o.amount,

u.id,

u.last_name,

u.first_name,

u.prefecture_id

from

orders o

inner join

users u

o.user_id=u.id

where

u.prefecture_id=13

and o.order_time>='2017-01-01 00:00:00'

and o.order_time<'2017-02-01 00:00:00'

order by

o.id;

-- (5)外部結合 left outer join / right outer join

select

u.last_name last_name,

u.id user_id,

o.user_id order_user_id,

o.id order_id

from

users u

-- inner join #結果：nullデータは存在しない

left outer join#結果：idをすべて出力するのでnullデータも存在する

orders o

on u.id=o.user_id

order by u.id;

-- (6)外部結合　例注文された商品の個数を出力

select

p.id,

p.name,

sum(od.product_qty) num

from

products p

left outer join

order_details od

p.id=od.product_id

group by p.id;

-- (7)４つのテーブルを結合

select

o.id order_id,

o.user_id user_id,

u.last_name,

u.first_name,

o.amount amount,

o.order_time order_time,

od.product_price,

p.price product_price,

p.name product_name

from orders o

inner join

order_details od

on o.id=od.order_id

inner join

products p

on od.product_id=p.id

inner join

users u

on o.user_id=u.id;

-- (8)多数 vs 多数の　結合

#中間テーブルが必要：products_categoriesテーブル

#productsテーブルとcategoriesテーブルをつなぐ

select

p.id product_id,

p.name product_name,

c.name

from

products p

inner join

products_categories pc

on p.id=pc.product_id

inner join

categories c

on pc.category_id=c.id

where

p.id=3;

-- (9)テーブルの足し算 union / union all

-- 列数をそれぞれ合わせること。それぞれのデータ型を一致させること。

select

u.email user_email,

u.last_name,

u.first_name,

u.gender

from

users u

union#ここのセミコロンをunionに変更するだけ

select

adu.email user_email,

adu.last_name,

adu.first_name,

adu.gender

from

admin_users adu;

-- (10)order byは　最後に一度だけしか使用できない

select

u.email user_email,

u.last_name,

u.first_name,

u.gender

from

users u

where gender=1

union#ここのセミコロンをunionに変更するだけ　重複データは削除される

-- union all 重複したデータも削除されない

select

adu.email user_email,

adu.last_name,

adu.first_name,

adu.gender

from

admin_users adu

where gender=2

order by gender;

#記述順序：select>from>結合処理>where>group by>having>order by>limit

#実行順序：from>結合処理>where>group by>select>having>orderby>limit

#selectの順序だけが異なることに注意

この記事を書いた人
最新の記事

Keita N

最新記事 by Keita N (全て見る)

2024年11月
月	火	水	木	金	土	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30