Keita N - 流行の科学技術を勝手に追いかけるブログ人工知能仮想現実ブロックチェーン

python:pandasの使い方　基本

pandasの使い方について、基本だけ載せておきます。

後日追記します。

# coding: utf-8

# In[24]:


#モジュールをインポートする
import numpy as np
import pandas as pd


# In[25]:


#１次元配列を作成する
s = pd.Series([1,2,np.nan])
"""
0    1.0
1    2.0
2    NaN
dtype: float64
"""
#取り出す
s[0]    #1.0

#合計
s.sum() #3.0


# In[26]:


#ディクショナリからデータフレームを作成する
df=pd.DataFrame({"A":[1,2],"B":[3,4]})
df


# In[27]:


#型を確認する
df.dtypes
"""
A    int64
B    int64
dtype: object
"""


# In[28]:


#numpy行列からデータフレームを作成する
df=pd.DataFrame(np.random.randn(6,4)) #６行４列

#indexを付ける
df=pd.DataFrame(np.random.randn(6,4),index=pd.date_range("20170101",periods=6))

#columnsを付ける
df=pd.DataFrame(np.random.randn(6,4),
                index=pd.date_range("20170101",periods=6),
                columns=["A","B","C","D"])
df


# In[29]:


#最初の一行を見る
df.head(1)


# In[30]:


#最後の３行を見る
df.tail(3)


# In[31]:


#indexを見る
df.index

#columnsを見る
df.columns

#valueを見る
df.values


# In[32]:


#discribe(count mean std mi max)を見る
df.describe()


# In[33]:


#転置する
df.T


# In[34]:


#ソートする　（columnを指定）
df.sort_values(by="B")

#indexでスライスする
df[0:3]
df["20170102":"20170104"]

#index指定で行を抽出
df.loc["20170101"]


# In[35]:


df.loc["20170102",["A","B"]]


# In[36]:


df.loc[:,["A","B"]]


# In[37]:


#行と列を座標で指定
df.iloc[0,0]


# In[38]:


#行と列を座標でスライス
df.iloc[0:2,0:2]


# In[39]:


df[df.A>0]


# In[40]:


df[df>0]


# In[41]:


df2=df.copy()


# In[42]:


df2["E"]=["one","twe","thee","four","five","six"]
df2


# In[43]:


df2[df2["E"].isin(["thee","five"])]


# In[44]:


s=pd.Series([1,2,3,4,5,6],index=pd.date_range("20170101",periods=6))
s


# In[45]:


df2["E"]=s
df2


# In[46]:


df.shift(1)


# In[47]:


df=pd.DataFrame(np.random.randn(2,2))
df


# In[48]:


pd.concat([df,df])


# In[49]:


df=pd.DataFrame(np.random.rand(8,4),columns=["A","B","C","D"])
df


# In[50]:


s=df.iloc[0]
s


# In[51]:


df.append(s)


# In[52]:


df.append(s,ignore_index=True)


# In[53]:


df=pd.DataFrame({"A":["foo","bar","foo","bar"],"B":np.random.randn(4)})
df


# In[54]:


#A列の中で同じものをまとめて足し算する
df.groupby("A").sum()


# In[55]:


#Yahooから株式情報を取得する
import pandas_datareader

df=pandas_datareader.data.DataReader("AAPL","yahoo","2014-01-01")
df

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

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

# coding: utf-8

# In[24]:

#モジュールをインポートする

import numpy as np

import pandas as pd

# In[25]:

#１次元配列を作成する

s = pd.Series([1,2,np.nan])

"""

0 1.0

1 2.0

2 NaN

dtype: float64

"""

#取り出す

s[0] #1.0

#合計

s.sum() #3.0

# In[26]:

#ディクショナリからデータフレームを作成する

df=pd.DataFrame({"A":[1,2],"B":[3,4]})

# In[27]:

#型を確認する

df.dtypes

"""

A int64

B int64

dtype: object

"""

# In[28]:

#numpy行列からデータフレームを作成する

df=pd.DataFrame(np.random.randn(6,4)) #６行４列

#indexを付ける

df=pd.DataFrame(np.random.randn(6,4),index=pd.date_range("20170101",periods=6))

#columnsを付ける

df=pd.DataFrame(np.random.randn(6,4),

index=pd.date_range("20170101",periods=6),

columns=["A","B","C","D"])

# In[29]:

#最初の一行を見る

df.head(1)

# In[30]:

#最後の３行を見る

df.tail(3)

# In[31]:

#indexを見る

df.index

#columnsを見る

df.columns

#valueを見る

df.values

# In[32]:

#discribe(count mean std mi max)を見る

df.describe()

# In[33]:

#転置する

df.T

# In[34]:

#ソートする　（columnを指定）

df.sort_values(by="B")

#indexでスライスする

df[0:3]

df["20170102":"20170104"]

#index指定で行を抽出

df.loc["20170101"]

# In[35]:

df.loc["20170102",["A","B"]]

# In[36]:

df.loc[:,["A","B"]]

# In[37]:

#行と列を座標で指定

df.iloc[0,0]

# In[38]:

#行と列を座標でスライス

df.iloc[0:2,0:2]

# In[39]:

df[df.A>0]

# In[40]:

df[df>0]

# In[41]:

df2=df.copy()

# In[42]:

df2["E"]=["one","twe","thee","four","five","six"]

df2

# In[43]:

df2[df2["E"].isin(["thee","five"])]

# In[44]:

s=pd.Series([1,2,3,4,5,6],index=pd.date_range("20170101",periods=6))

# In[45]:

df2["E"]=s

df2

# In[46]:

df.shift(1)

# In[47]:

df=pd.DataFrame(np.random.randn(2,2))

# In[48]:

pd.concat([df,df])

# In[49]:

df=pd.DataFrame(np.random.rand(8,4),columns=["A","B","C","D"])

# In[50]:

s=df.iloc[0]

# In[51]:

df.append(s)

# In[52]:

df.append(s,ignore_index=True)

# In[53]:

df=pd.DataFrame({"A":["foo","bar","foo","bar"],"B":np.random.randn(4)})

# In[54]:

#A列の中で同じものをまとめて足し算する

df.groupby("A").sum()

# In[55]:

#Yahooから株式情報を取得する

import pandas_datareader

df=pandas_datareader.data.DataReader("AAPL","yahoo","2014-01-01")

Python ：listの操作

こんにちは、今日はlistの操作をやっておこうと思います。
numpyばかり使っているとlistの操作方法を忘れてしまう今日この頃です。

list　の操作　数値

#空list 作成
li=[]

#空リストに要素を追加
li.append(10) #[10]
li.append(20) #[20,20]

#listの後ろにlistを延長する
li.extend([1,2,3,4,5])#[10,20,1,2,3,4,5]

#一番後ろを引き抜いてきて、さらに元を削除する
li.pop()

#任意の要素を削除する
li.remove(3)

#小さいものから順番に並べる
li.sort()

#空list 作成

li=[]

#空リストに要素を追加

li.append(10) #[10]

li.append(20) #[20,20]

#listの後ろにlistを延長する

li.extend([1,2,3,4,5])#[10,20,1,2,3,4,5]

#一番後ろを引き抜いてきて、さらに元を削除する

li.pop()

#任意の要素を削除する

li.remove(3)

#小さいものから順番に並べる

li.sort()

list　の操作　文字列

こちらは、

li.strip　：改行を削除する
li.find　：該当する文字列の最初の位置を見つける
“__” in li　：該当する文字列が入っているか
li.replace　：該当する文字列を置換する
li.split(“○○”)　：区切り文字で分割する
“○”.li　：指定の文字で繋げる

くらいでしょうか

MySQL：データベース構造の操作 #18

#(22) データベース構造の操作
-- (1)データベースの追加　create databese
show databases;
create database book_store;
show databases;#確認

-- (2)テーブルの追加
use book_store;
show tables;#まだテーブルはなにもない
create table books(id int not null auto_increment primary key,title varchar(255) not null);
show tables;#booksテーブルが追加された
show columns from books;#テーブル構造を確認
select * from books;#中身はまだなにもない

-- (3)テーブルの列の追加 alter table add / price列
use book_store;
show columns from books;
alter table books add price int after id;
show columns from books;

-- (4)テーブルの列の変更 alter table change / booksテーブルのpriceをunit_priceへ変更
show columns from books;
alter table books change price unit_price int;
show columns from books;

-- (3)テーブルの列の削除 alter tabel drop / unit_price
show columns from books;
alter table books drop unit_price;
show columns from books;

-- (4)テーブルの削除 drop table / booksテーブル
show tables;
drop table books;
show tables;#確認

-- (5)データベースの削除 drop database / book_sore
show databases;
drop database book_store;
show databases;#確認

#(22) データベース構造の操作

-- (1)データベースの追加　create databese

show databases;

create database book_store;

show databases;#確認

-- (2)テーブルの追加

use book_store;

show tables;#まだテーブルはなにもない

create table books(id int not null auto_increment primary key,title varchar(255) not null);

show tables;#booksテーブルが追加された

show columns from books;#テーブル構造を確認

select * from books;#中身はまだなにもない

-- (3)テーブルの列の追加 alter table add / price列

use book_store;

show columns from books;

alter table books add price int after id;

show columns from books;

-- (4)テーブルの列の変更 alter table change / booksテーブルのpriceをunit_priceへ変更

show columns from books;

alter table books change price unit_price int;

show columns from books;

-- (3)テーブルの列の削除 alter tabel drop / unit_price

show columns from books;

alter table books drop unit_price;

show columns from books;

-- (4)テーブルの削除 drop table / booksテーブル

show tables;

drop table books;

show tables;#確認

-- (5)データベースの削除 drop database / book_sore

show databases;

drop database book_store;

show databases;#確認

MySQL：データの更新 #17

#(21)データの更新
-- (1)新規の行を追加 insert into
insert into products(name,price) values('新商品A',1000);
select * from products;#追加されたか確認する

-- (2)新規の行を追加　列リストを省略した書き方（非推奨）
insert products values(1002,'新商品B',2000);
select * from products;

-- (3)行を複数追加する
insert into
  products(name,price)
values
  ('新商品C',3000),
  ('新商品D',4000),
  ('新商品E',5000);
select * from products;

-- (4)データのアップデート　update　商品価格を10%ディスカウント
select * from products;
set sql_safe_updates=0;#リミッターの解除
update products set price=price*0.9;

-- (5)特定の条件に合致するデータのみをアップデートする
select * from products where id=3;
update products set name='SQL入門', price=1000 where id =3;
select * from products where id=3;#確認

-- (6)更新条件にサブクエリを使用する
#累計販売数が１０を超えている商品は価格を５％UPする
#まずは条件に入るリストを作成する。あとでこの部分がサブクエリとなる
select
  product_id,
  sum(product_qty)
from
  order_details
group by
  product_id
having
 sum(product_qty)>=10;
 
#アップデートする
update
  products
set
  price=price*1.05
where
  id in
  (
	select
	  product_id
	  -- sum(product_qty)
	from
	  order_details
	group by
	  product_id
	having
	 sum(product_qty)>=10
	);
select * from products;

-- (7)行の削除 delete
select * from products_categories;
delete from products_categories;
select * from products_categories;#確認

-- (8)条件を指定して　行を削除
select * from products where id=1001;
delete from products where id=1001;#注意：where句を忘れると全部消える
select * from products where id=1001;#確認

-- (9)削除条件に　サブクエリを使う
#一個も売れていない商品を消す
select
  product_id
from
  order_details
group by
  product_id;

#消す
delete 
from
  products
where
  id not in
  (
	select
	  product_id
	from
	  order_details
	group by
	  product_id
  );
  
  select * from products;#確認

#(21)データの更新

-- (1)新規の行を追加 insert into

insert into products(name,price) values('新商品A',1000);

select * from products;#追加されたか確認する

-- (2)新規の行を追加　列リストを省略した書き方（非推奨）

insert products values(1002,'新商品B',2000);

select * from products;

-- (3)行を複数追加する

insert into

products(name,price)

values

('新商品C',3000),

('新商品D',4000),

('新商品E',5000);

select * from products;

-- (4)データのアップデート　update　商品価格を10%ディスカウント

select * from products;

set sql_safe_updates=0;#リミッターの解除

update products set price=price*0.9;

-- (5)特定の条件に合致するデータのみをアップデートする

select * from products where id=3;

update products set name='SQL入門', price=1000 where id =3;

select * from products where id=3;#確認

-- (6)更新条件にサブクエリを使用する

#累計販売数が１０を超えている商品は価格を５％UPする

#まずは条件に入るリストを作成する。あとでこの部分がサブクエリとなる

select

product_id,

sum(product_qty)

from

order_details

group by

product_id

having

sum(product_qty)>=10;

#アップデートする

update

products

set

price=price*1.05

where

id in

(

select

product_id

-- sum(product_qty)

from

order_details

group by

product_id

having

sum(product_qty)>=10

);

select * from products;

-- (7)行の削除 delete

select * from products_categories;

delete from products_categories;

select * from products_categories;#確認

-- (8)条件を指定して　行を削除

select * from products where id=1001;

delete from products where id=1001;#注意：where句を忘れると全部消える

select * from products where id=1001;#確認

-- (9)削除条件に　サブクエリを使う

#一個も売れていない商品を消す

select

product_id

from

order_details

group by

product_id;

#消す

delete

from

products

where

id not in

(

select

product_id

from

order_details

group by

product_id

);

select * from products;#確認

MySQL：条件分岐 #16

#(19)条件分岐 case式
-- (1)ランク分け
select
  u.id as user_id,
  count(*) as num, #count(user_id)でも同じ
    case
      when count(*) >= 5 then 'A'
      when count(*) >= 2 then 'B'
      else 'C'
    end as user_rank
from
  users as u
inner join
  orders as o
on u.id=o.user_id
group by user_id
order by user_rank;

-- (2)個数がnullのときゼロに置き換える
select
  p.id,
  p.name,
  -- sum(od.product_qty) as num
  case
    when sum(od.product_qty) is null then 0
    else sum(od.product_qty)
  end as num
from
  products as p
left outer join
  order_details as od
on p.id=od.product_id
group by p.id;

-- (3)ランク分け
select
  p.id as product_id,
  p.name as name,
  sum(od.product_qty) as num,
  case
    when sum(od.product_qty) >= 20 then 'A'
    when sum(od.product_qty) >= 10 then 'B'
    else 'C'
  end as rank
from
  products as p
left outer join
  order_details as od
  on  p.id=od.product_id
group by product_id
order by rank;

#(20)実践問題
-- (1)平均客単価
select
  -- round(sum(amount)/count(id),0) as average
  round(avg(amount),0) as average
from
  orders;
  
-- (2)月別の平均客単価 date_format関数を使用
select
  date_format(order_time,'%Y%m') as order_year_month,
  round(avg(amount),0) as average_costomer_spend
from
  orders
group by date_format(order_time,'%Y%m')
order by order_year_month;

-- (3) 都道府県別の平均客単価
select
  pf.id as prefecture_id,
  pf.name as prefecture_name,
  round(avg(o.amount),0) as average_coutomer_spend
from
  orders as o
left outer join
  users as u
  on o.user_id=u.id
left outer join
  prefectures as pf
  on u.prefecture_id=pf.id
group by u.prefecture_id
order by prefecture_id;

-- (4) 都道府県別および月別の平均客単価
select
  pf.id,
  pf.name,
  date_format(o.order_time,'%Y%m') as order_year_month,
  round(avg(o.amount),0) as average_costomer_spend
from
  orders as o
left outer join
  users as u
  on o.user_id=u.id
left outer join
  prefectures as pf
  on u.prefecture_id=pf.id
group by u.prefecture_id , order_year_month
order by u.prefecture_id , order_year_month ;

100

101

#(19)条件分岐 case式

-- (1)ランク分け

select

u.id as user_id,

count(*) as num, #count(user_id)でも同じ

case

when count(*) >= 5 then 'A'

when count(*) >= 2 then 'B'

else 'C'

end as user_rank

from

users as u

inner join

orders as o

on u.id=o.user_id

group by user_id

order by user_rank;

-- (2)個数がnullのときゼロに置き換える

select

p.id,

p.name,

-- sum(od.product_qty) as num

case

when sum(od.product_qty) is null then 0

else sum(od.product_qty)

end as num

from

products as p

left outer join

order_details as od

on p.id=od.product_id

group by p.id;

-- (3)ランク分け

select

p.id as product_id,

p.name as name,

sum(od.product_qty) as num,

case

when sum(od.product_qty) >= 20 then 'A'

when sum(od.product_qty) >= 10 then 'B'

else 'C'

end as rank

from

products as p

left outer join

order_details as od

on p.id=od.product_id

group by product_id

order by rank;

#(20)実践問題

-- (1)平均客単価

select

-- round(sum(amount)/count(id),0) as average

round(avg(amount),0) as average

from

orders;

-- (2)月別の平均客単価 date_format関数を使用

select

date_format(order_time,'%Y%m') as order_year_month,

round(avg(amount),0) as average_costomer_spend

from

orders

group by date_format(order_time,'%Y%m')

order by order_year_month;

-- (3) 都道府県別の平均客単価

select

pf.id as prefecture_id,

pf.name as prefecture_name,

round(avg(o.amount),0) as average_coutomer_spend

from

orders as o

left outer join

users as u

on o.user_id=u.id

left outer join

prefectures as pf

on u.prefecture_id=pf.id

group by u.prefecture_id

order by prefecture_id;

-- (4) 都道府県別および月別の平均客単価

select

pf.id,

pf.name,

date_format(o.order_time,'%Y%m') as order_year_month,

round(avg(o.amount),0) as average_costomer_spend

from

orders as o

left outer join

users as u

on o.user_id=u.id

left outer join

prefectures as pf

on u.prefecture_id=pf.id

group by u.prefecture_id , order_year_month

order by u.prefecture_id , order_year_month ;

MySQL：サブクエリ #15

#(18)サブクエリ
-- (1)2017/12 に商品を購入していないユーザーを抽出したい

-- ユーザーリスト
select
  u.id,
  u.last_name,
  u.email
from
  users u
where id not in( # 該当するidは除く
	-- サブクエリ：12月購入のユーザーidのリスト　
	select
	  user_id
	from 
	  orders o
	where
	  o.order_time>='2017-12-00 00:00:00'
	  and o.order_time<'2018-01-00 00:00:00');

-- (2)サブクエリ　例：2017/12に商品を購入したユーザーを抽出
select
  id,
  last_name,
  email
from
  users
where id in(
	select
	  id
	from
	  orders
	where
	  order_time>='2017-12-01 00:00:00'
	  and order_time<'2018-01-01 00:00:00');

-- (3)全商品の平均単価より高い商品を取得
#     スカラーサブクエリ　単一の行を返すサブクエリ
select
  *
from
  products
where price>=(
-- スカラーサブクエリ
	select
	  avg(price)
	from
	  products)
order by
  price desc, id asc;

#(18)サブクエリ

-- (1)2017/12 に商品を購入していないユーザーを抽出したい

-- ユーザーリスト

select

u.id,

u.last_name,

u.email

from

users u

where id not in( # 該当するidは除く

-- サブクエリ：12月購入のユーザーidのリスト　

select

user_id

from

orders o

where

o.order_time>='2017-12-00 00:00:00'

and o.order_time<'2018-01-00 00:00:00');

-- (2)サブクエリ　例：2017/12に商品を購入したユーザーを抽出

select

id,

last_name,

from

users

where id in(

select

from

orders

where

order_time>='2017-12-01 00:00:00'

and order_time<'2018-01-01 00:00:00');

-- (3)全商品の平均単価より高い商品を取得

# スカラーサブクエリ　単一の行を返すサブクエリ

select

from

products

where price>=(

-- スカラーサブクエリ

select

avg(price)

from

products)

order by

price desc, id asc;

MySQL：ビュー #14

#(17)ビュー create view
#order byは使用できない
-- 都道府県別のユーザー数
create view prefecture_user_counts(name,count)#追加
as#追加
select
  p.name name,
  count(*) count #group by してからselect
from
  users u
inner join
  prefectures p
on u.prefecture_id=p.id
group by u.prefecture_id;

-- ここでいったん refresh all
-- 作成したビューを使用してみる
select
  name,
  count
from
  prefecture_user_counts;

-- (2)insert delete update に制約がある
#下記の場合を満たす場合のみ可能になる
# select句にdistinctが含まれていない
# from句に含まれるテーブルが一つだけ
# group by句を使用していない
# having句を使用していない

-- (3)ビューの削除　drop view
drop view prefecture_user_counts;

#(17)ビュー create view

#order byは使用できない

-- 都道府県別のユーザー数

create view prefecture_user_counts(name,count)#追加

as#追加

select

p.name name,

count(*) count #group by してからselect

from

users u

inner join

prefectures p

on u.prefecture_id=p.id

group by u.prefecture_id;

-- ここでいったん refresh all

-- 作成したビューを使用してみる

select

name,

count

from

prefecture_user_counts;

-- (2)insert delete update に制約がある

#下記の場合を満たす場合のみ可能になる

# select句にdistinctが含まれていない

# from句に含まれるテーブルが一つだけ

# group by句を使用していない

# having句を使用していない

-- (3)ビューの削除　drop view

drop view prefecture_user_counts;

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の順序だけが異なることに注意

MySQL：関数と演算子 #12

#(15)関数と演算子
-- (1)基本演算
select 10+30;
select 10-3;
select 10*3;
select 10/3;
select 10%3;
-- (2)nullを含む演算
select 10+null; #null
select 10-null; #null
select 10*null; #null
select 10/null; #null
select 10%null; #null
-- (3)abs関数
select abs(10); #10
select abs(-10);#10
select abs(0);  #0
-- (4)round関数
select id,name,price*1.08 from products;#小数点込みで出力される
select id,name,round(price*1.08,0) from products;#少数第一位で四捨五入
select round(10.555,0);#11
select round(10.555,1);#10.6
select round(10.555,2);#10.56
-- (5)文字列の連結
select concat(last_name,' ',first_name,'さん') from users;
select concat(last_name,'さん'),email from users where gender=2;
-- (6)日付と時刻
select current_date();#2019-01-03
select current_timestamp();#2019-01-03 21:56:45
select current_date()+3;#20190106
select current_date()-3;#20190100
select current_time()+interval 6 hour;#27:58:11
select current_time()-interval 6 hour;#15:58:45

select * from orders where extract(year_month from order_time)=201701;
select * from orders where extract(year from order_time)=2017;
select * from orders where extract(month from order_time)=1;

#(15)関数と演算子

-- (1)基本演算

select 10+30;

select 10-3;

select 10*3;

select 10/3;

select 10%3;

-- (2)nullを含む演算

select 10+null; #null

select 10-null; #null

select 10*null; #null

select 10/null; #null

select 10%null; #null

-- (3)abs関数

select abs(10); #10

select abs(-10);#10

select abs(0); #0

-- (4)round関数

select id,name,price*1.08 from products;#小数点込みで出力される

select id,name,round(price*1.08,0) from products;#少数第一位で四捨五入

select round(10.555,0);#11

select round(10.555,1);#10.6

select round(10.555,2);#10.56

-- (5)文字列の連結

select concat(last_name,' ',first_name,'さん') from users;

select concat(last_name,'さん'),email from users where gender=2;

-- (6)日付と時刻

select current_date();#2019-01-03

select current_timestamp();#2019-01-03 21:56:45

select current_date()+3;#20190106

select current_date()-3;#20190100

select current_time()+interval 6 hour;#27:58:11

select current_time()-interval 6 hour;#15:58:45

select * from orders where extract(year_month from order_time)=201701;

select * from orders where extract(year from order_time)=2017;

select * from orders where extract(month from order_time)=1;

MySQL：order by 句 #11

#(14)データの並び替え order by
-- (1)price を降順(desc)で並び替える
select * from products order by price desc;
-- (2)price を昇順(asc)で並び替える
select * from products order by price asc; #デフォルトがascなので省略可能
-- (3)priceを降順(desc)で並べて、価格が同じときは登録順(id順)で並び替える
select * from products order by price desc , id asc;
-- (4)ユーザーを生年月日が古い順に並べる。一緒の場合は都道府県idで昇順に並べる。
select * from users order by birthday asc , prefecture_id asc;

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

#(14)データの並び替え order by

-- (1)price を降順(desc)で並び替える

select * from products order by price desc;

-- (2)price を昇順(asc)で並び替える

select * from products order by price asc; #デフォルトがascなので省略可能

-- (3)priceを降順(desc)で並べて、価格が同じときは登録順(id順)で並び替える

select * from products order by price desc , id asc;

-- (4)ユーザーを生年月日が古い順に並べる。一緒の場合は都道府県idで昇順に並べる。

select * from users order by birthday asc , prefecture_id asc;

#記述順序：select>from>where>group by>having>order by>limit

#実行順序：from>where>group by>select>having>orderby>limit

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

2024年4月
月	火	水	木	金	土	日
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