13から続きます。

テーブルとビューの違いは?

テーブル

・実際のデータを保存

ビュー

・ビューの中にはセレクト文が保存される
・ビュー自体はデータを持たない
・order by句が使えない=並び替えできない
・ビューに対する更新は不可能ではないが制限があるデータを保存しないので記憶装置の容量を節約できる。
・よく使うセレクト文をビューにしておくことで使い回しができるが
パフォーマンス低下を招く場合がある

例：都道府県別のユーザー数を知りたい！まずはselect文から

select
p.name,
count(*)
from
users as u
inner join
prefectures as p
on u.prefecture_id = p.id
group by
prefecture_id;

これをviewにすることもできる。

続きを読む →

その12から続きます。

外部結合とは？

片方のテーブルの情報がすべて出力されるテーブルの結合

外部結合は欠落のあるデータを取り扱う結合

構文：外部結合 left outer join

select テーブル名1.列名,テーブル名2.列名・・・
from
テーブル名1
left outer join
テーブル名2
on テーブル名1.列名 = テーブル名2.列名；

left outer join / right outer join

left outer join・・・左側(from句で最初に書いたテーブル)をマスターとする。

right outer join・・・右側(from句で後に書いたテーブル)をマスターとする。

select
u.last_name,
u.id,
o.user_id,
o.id
from
users u
left outer join
orders as o
on u.id = o.user_id
order by u.id;

※ポイント

続きを読む →

その11から続きます。

1:内部結合(inner join)

例：ユーザー一覧を取得したい！都道府県IDで出力されてもよくわからないので都道府県名も表示したい！

select
 users.id,
 users.last_name,
 users.first_name,
 prefectures.name
from users
inner join
prefectures
on users.prefecture_id = prefectures.id;

※ポイント

まず名字と名前と県IDを取得
select id, last_name, first_name, prefecture_id from users;
次にprefecture_idと都道府県名(prefectures.nane)を結びつける(inner join on)

短く書くこともできる

select
 u.id,
 u.last_name,u.first_name, p.name
from
 users as u
inner join
 prefectures as p
 on u.prefecture_id = p.id;

さらに短く書くこともできる(asを省略)

select
 u.id,
 u.last_name,u.first_name, p.name
from
 users u
inner join
 prefectures p 
on u.prefecture_id = p.id;

さらにさらに短くもできる。(innerを省略)

続きを読む →

その９から続きます。

日付と時刻の演算

現在の日付 (current_date)

select current_date();

現在の時刻　(current_timestamp)

select current_timestamp();

n日後の日付

select current_date() + 3;

n日前の日付

select current_date() – 3;

x時間後の時刻

select current_timestamp() + interval 6 hour;

x時間前の時刻

select current_time() – interval 6 hour;

extract 日付や時刻の特定の部分 (年や月)までを取り出す

orders テーブルから注文日時(order_timeカラム)、
2017年01月のレコードを取得する。

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

ordersテーブルから
注文日時(order_timeカラムが)、2017年のレコードを取得する。

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

orders テーブルから注文日時(order_timeカラムが)、
1月のレコードを取得する。

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

その１０に続きます。

その10から続きます。

1:テーブルの正規化とは？

テーブルを分けて情報の重複をなくしていく作業のこと。

データの管理が容易になること、データ容量の削減ができることなどの

メリットが有るため、特別な意図がなければテーブルは正規化する。

2:テーブルの結合とは？

テーブル同士をある条件で結合することにより正規化なしの状態を作り出すこと

その9.5から続きます。

データの並べ替える方法(order by)

構文：order by 列名や式 並び順

並び順について

asc・・・昇順(ascending)※デフォルト並び順を指定しない場合昇順。

desc・・・降順(descending)

例1：商品一覧を価格が低い順に並べて作成したい！

select *from products order by price asc;

ascはなくても良い。取得するレコードの並び順が重要な場合はorder byを使って明示的に並び順を指定すること。

例2：商品一覧を価格が高い順に並べて作成したい！

select *from products order by price desc;

続きを読む →

その8から続きます。

1:対象行の行数を数えるcount集約関数

例：ECサイト(架空)のユーザーが何人いるか調べたい！

select count(*) from users;

※ポイント

count(*)とするとテーブルの行数をすべて取得できる。

女性ユーザーが何人いるのか知りたい！

select count(*) from users where users.gender = 2;

2:応用)2017年1月にアクセスした ユニークユーザー数（ec サイト登録ユーザのみ）を調べたい！

そもそもユニークアクセスユーザーとは？(ヒント)

1:決まった集計期間内にアクセスしたユーザーの数を表す数値

2:あるユーザーA が決まった期間内に10アクセスしたとしても1と数える。where句と集約関数 count (distinct expr)を組み合わせて使う。

3:count (distinct expr)の形で、count関数は重複を排除した形で個数を取得できる

続きを読む →

その７からつづきます。

1:集約関数とは？

SQLでテーブルの値を集計するために使う。

関数(function)とは、様々な計算をパッケージ化したもの

2:合計値を求めるsum集約関数

例：2017年1月の合計金額を調べたい！ER図よりordersテーブルから探す

続きを読む →

その６から続きます。

select文により取得したデータの利用方法

1：コンピュータープログラムから利用する。

RailsやPHPなど

2:CSVファイルに書き出し、表計算ソフトに取り込む、

エクセルやGoogle スプレッドシートなど。

CSVとは？

Comma-Separated Valuesの略

区切り文字であるコンマで区切ったテキストデータ

TSVというものもある

Tab-Separated Valuesの略のTSV

区切り文字であるタブで区切ったテキストデータ

続きを読む →

その５から続きます。

1:パターンマッチングによる絞り込み(like)

例：漢字の中から始まる苗字のユーザー一覧を取得したい！

select * from users where last_name like ‘中%’;

中を含むユーザー一覧がほしい

select * from users where last_name like '%中%'

子で終わる名前のユーザー一覧がほしい

select * from users where first_name like '%子';

子で終わる3文字の名前のユーザー一覧がほしい。

select * from users where first_name like '__子';

続きを読む →