sqlを勉強し始めて(SELECT,INSERT,UPDATE,DELETE,CREATE文,left join,inner join,group by,order by,distinct,サブクエリを覚えたぐらい)の頃の自分が、その当時に次のステップとして知っておきたかったSQLのいろいろを書いていきます。
注意
私自身sqlについて勉強中のものですので、間違いや、もっといい書き方があるなどがあると思いますが、
私がsqlを勉強し始めた頃にこれを知っていれば、sqlってこんなに面白いものなんだともっと早く知ることができたのにと思い、投稿させていただきました。m(__)m
間違いなどあれば指摘いただければと思います。
テーブル・カラム名とかは大分適当ですが、やりたいことが伝わる内容には気をつけましたのでよろしくお願いいたします。
基本的にMySQL 5.6で書いて行きます。
MySQLでサポートしていない書き方などは別のsqlを使う感じです。
バージョン確認
select version();
テーブルバックアップ
create table sample_table_bk as select * from sample_table
データが0件の場合だけINSERT
ググるとやり方がいろいろあるようですが、個人的には、これが一番見た目わかりやすいと思いました。
CREATE TABLE IF NOT EXISTS works(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(50)
)
INSERT INTO works(
title
)
SELECT title FROM (
SELECT 'MyWork' title UNION ALL
SELECT 'Todos' UNION ALL
SELECT 'Shopping'
) tmp
WHERE (select count(*) from works) = 0
TRUNCATEで全データ削除
delete文だとオートコミットでなかった場合、commit;が必要ですが、
truncateだとcommit;しなくても反映してくれる
以下2つは同じ意味です。
TRUNCATE TABLE sample_table;
DELETE FROM sample_table;
COMMIT;
トリガーを設定する
参考
覚えると難しいことができそうですが、私がやってみたのは以下の簡単な例です。
例えばMySQLですと、CREATE文でON UPDATEを指定すると、更新時に一緒に値を更新してくれますが、
CREATE TABLE IF NOT EXISTS test_table (
id INT(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(20) NOT NULL,
createdate DATETIME DEFAULT CURRENT_TIMESTAMP,
updatedate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) DEFAULT CHARSET=utf8mb4;
SQLiteですと、このようなON UPDATEの機能はありませんので、トリガー使えばできる。
sqlite
CREATE TABLE IF NOT EXISTS sample_table (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name VARCHAR NOT NULL,
createdate DATE DEFAULT (DATETIME('now','localtime')),
updatedate DATE DEFAULT (DATETIME('now','localtime'))
);
sqlite
CREATE TRIGGER IF NOT EXISTS updatedate AFTER UPDATE on sample_table
BEGIN
UPDATE sample_table SET updatedate = DATETIME('now','localtime') WHERE id = old.id;
END;
横つなぎにしてくれる集計関数
参考
select 地方,GROUP_CONCAT(都道府県)
from japan
group by 地方;
select 地方,GROUP_CONCAT(都道府県 ORDER BY シーケンス DESC SEPARATOR':')
from japan
group by 地方;
select GROUP_CONCAT(DISTINCT 地方)
from japan;
information_schemaからテーブル名・カラム名一覧取得
MySQLですと、information_schemaでテーブルのいろいろな情報が取れますね
SELECT *
FROM information_schema.tables
WHERE table_schema = database()
SELECT *
FROM information_schema.columns
WHERE table_schema = database()
AND table_name = 'hogehoge'
ORDER BY ordinal_position
最大値/最小値に対する全カラムを取得
- 支店ごとの来客数最大に対する他のカラムも取得
やり方はいろいろあるとのことです。
以下3つとも同じ結果になる
SELECT *
FROM guest
WHERE ((支店,来客数) IN (
SELECT 支店,MAX(来客数)
FROM guest
GROUP BY 支店
))
SELECT guest.*
FROM guest
INNER JOIN (
SELECT 支店,MAX(来客数) 来客数
FROM guest
GROUP BY 支店
) tmp
USING(支店,来客数)
row_number()関数などでも同じ結果を取得できる
※MySQL5.6では使えないので、postgresqlを使用
postgresql
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY 支店 ORDER BY 来客数 DESC) NUM
FROM guest
) a
WHERE NUM = 1
ファンクションを作成する
ほとんど使ったことがないため、とりあえずfizzbuzzを作りました
CREATE FUNCTION fizzbuzz (i INT)
RETURNS VARCHAR(20) DETERMINISTIC
RETURN case
when i % 3 = 0 and i % 5 = 0 then 'fizzbuzz'
when (i % 3 = 0) then 'fizz'
when (i % 5 = 0) then 'buzz'
else i
end
select num,fizzbuzz(num) from (
select 1 num
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
union all select 14
union all select 15
) a
縦持ちのデータを横持ちにする
※やり方はいろいろ人によります
select a.syohin_mei
,l.size size_l
,m.size size_m
,s.size size_s
from (select distinct fuku_1.syohin_mei from fuku_1) a
left join (select syohin_mei,size
from fuku_1
where size='L') l
on a.syohin_mei=l.syohin_mei
left join (select syohin_mei,size
from fuku_1
where size='M') m
on a.syohin_mei=m.syohin_mei
left join (select syohin_mei,size
from fuku_1
where size='S') s
on a.syohin_mei=s.syohin_mei
横持ちのデータを縦持ちにする
※やり方はいろいろ人によります
select * from (
(select syohin_mei,'L' size
from fuku_2
where size_l=1)
union all
(select syohin_mei,'M'
from fuku_2
where size_m=1)
union all
(select syohin_mei,'S'
from fuku_2
where size_s=1)
) a
order by syohin_mei desc,size
WITH句で重複条件などをまとめる
with句を使用することで、条件を使い回す、長いsqlだと見やすくなる、と行ったメリットがあります。
※with句はMySQL5.6ではサポートしていないため、postgresql 9.2を使用しています。
MySQLだとwith句の代わりにVIEWを作成するなどします。
出力内容
キャンペーン対象の商品について、先月と今月の売り上げを比較したデータを出力する
出力項目:ショップID、商品コード、先月トータル売上、今月トータル売上、売上アップ/ダウン
抽出条件
- syohin_mstテーブルのcampaign_flgが1
または、
- 以下のショップID・商品コードの組み合わせの場合は、campaign_flgに関係なく抽出
| ショップID shop_id | 商品コード code | 備考 |
|---|---|---|
| 11001 | 01S | |
| 11001 | 01M | |
| 11003 | 01S | ※先月トータル売上には適用しない |
| 11003 | 01M | ※先月トータル売上には適用しない |
| 11005 | 01X | ※先月トータル売上には適用しない |
postgresql
WITH campaign_code AS (
(select '11001' shop_id, '01S' code, 1 sengetsu_flg union
select '11001','01M',1 union
select '11003','01S',0 union
select '11003','01M',0 union
select '11005','01X',0) union
SELECT shop_id,code,1
FROM syohin_mst
WHERE campaign_flg=1
),
先月売上金額 AS (
SELECT A.shop_id,A.code,SUM(B.amount) total
FROM sales A
LEFT JOIN syohin_mst B
ON A.shop_id=B.shop_id AND A.code=B.code
WHERE TO_CHAR(A.sales_date,'yyyymm') = TO_CHAR(now()+'-1 months','yyyymm')
AND ((A.shop_id,A.code)IN(SELECT DISTINCT shop_id,code FROM campaign_code WHERE sengetsu_flg=1))
GROUP BY A.shop_id,A.code
),
今月売上金額 AS (
SELECT A.shop_id,A.code ,SUM(B.amount) total
FROM sales A
LEFT JOIN syohin_mst B
ON A.shop_id=B.shop_id AND A.code=B.code
WHERE TO_CHAR(A.sales_date,'yyyymm') = TO_CHAR(now(),'yyyymm')
AND ((A.shop_id,A.code)IN(SELECT DISTINCT shop_id,code FROM campaign_code))
GROUP BY A.shop_id,A.code
)
SELECT A.shop_id
,A.code
,COALESCE(B.total,0) 先月
,COALESCE(C.total,0) 今月
,CASE WHEN COALESCE(B.total,0) > COALESCE(C.total,0) THEN '売上ダウン'
WHEN COALESCE(B.total,0) < COALESCE(C.total,0) THEN '売上アップ'
ELSE '同'
END
FROM (SELECT DISTINCT shop_id,code FROM campaign_code) A
LEFT JOIN 先月売上金額 B ON A.shop_id=B.shop_id AND A.code=B.code
LEFT JOIN 今月売上金額 C ON A.shop_id=C.shop_id AND A.code=C.code
ORDER BY A.shop_id,A.code
こういう、ちょっとややこしい条件になってくると、with句を使うことで見やすくなりました
ビューでselect文を簡潔にする
- 全テーブルに削除フラグのカラムがあり、全テーブルレコードを論理削除している
- それに加えて、memberテーブルからははstatusが
'加入中'のデータのみを常に持ってくる - といった条件でテーブルを5つ結合する
普通にsqlを書くと以下のようなselect文になる
select *
from cust c
left join member m
on c.cust_id=m.cust_id
and m.delete_flg=0
and m.status='加入中'
left join food_order o
on c.cust_id=o.cust_id
and o.delete_flg=0
left join food f
on o.food_id=f.food_id
and f.delete_flg=0
left join food_category fc
on f.food_cat_id=fc.food_cat_id
and fc.delete_flg=0
where c.delete_flg=0
order by c.cust_id,fc.food_cat_id,f.food_id;
-- またはサブクエリにする
select *
from cust c
left join (select * from member where delete_flg=0 and status='加入中') m
on c.cust_id=m.cust_id
left join (select * from food_order where delete_flg=0) o
on c.cust_id=o.cust_id
left join (select * from food where delete_flg=0) f
on o.food_id=f.food_id
left join (select * from food_category where delete_flg=0) fc
on f.food_cat_id=fc.food_cat_id
where c.delete_flg=0
order by c.cust_id,fc.food_cat_id,f.food_id;
好みの問題になりますが、自分的には上の書き方よりは、ビューにして簡潔にした方がわかりやすいかなと思います。
-- ビューから取得
select *
from customer_view c
left join member_view m on c.cust_id=m.cust_id
left join food_order_view o on c.cust_id=o.cust_id
left join food_view f on o.food_id=f.food_id
left join food_category_view fc on f.food_cat_id=fc.food_cat_id
order by c.cust_id,fc.food_cat_id,f.food_id;
csvを加工して取り込み
気象庁のホームページから、全国最高気温情報のcsvを持ってきて、それを取り込んで見る
※加工と言っても、日付変換とcase文使ってるぐらいです
LOAD DATA LOCAL INFILE 'c:/high.csv' -- Cドライブ直下にcsvを置いた場合
INTO TABLE high
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(
@field1
,@field2
,@field3
,@field4
,@field5
,@field6
,@field7
,@field8
,@field9
,@field10
,@field11
,@field12
,@field13
,@field14
,@field15
,@field16
,@field17
,@field18
,@field19
,@field20
,@field21
,@field22
,@field23
,@field24
,@field25
,@field26
,@field27
,@field28
,@field29
,@field30
,@field31
,@field32
,@field33
,@field34
,@field35
,@field36
,@field37
)
SET
`観測所番号` = @field1
,`都道府県` = case when @field2 like '北海道%' then '北海道' else @field2 end
,`地点` = @field3
,`国際地点番号` = @field4
,`現在時刻` = STR_TO_DATE(CONCAT(@field5,@field6,@field7,@field8,@field9),'%Y%m%d%H%i')
,`今日の最高気温` = @field10
,`今日の最高気温の品質情報` = @field11
,`今日の最高気温起時` = STR_TO_DATE(CONCAT(@field12,@field13),'%d%H%i')
,`今日の最高気温起時の品質情報` = @field14
,`平年差` = @field15
,`前日差` = @field16
,`該当旬（月）` = @field17
,`該当旬（旬）` = @field18
,`極値更新` = @field19
,`10年未満での極値更新` = @field20
,`今季最高` = @field21
,`今年の最高気温（昨日まで）` = @field22
,`今年の最高気温（昨日まで）の品質情報` = @field23
,`今年の最高気温（昨日まで）を観測した起日` = STR_TO_DATE(CONCAT(@field24,@field25,@field26),'%Y%m%d')
,`昨日までの観測史上1位の値` = @field27
,`昨日までの観測史上1位の値の品質情報` = @field28
,`昨日までの観測史上1位の値を観測した起日` = STR_TO_DATE(CONCAT(@field29,@field30,@field31),'%Y%m%d')
,`昨日までの1月の1位の値` = @field32
,`昨日までの1月の1位の値の品質情報` = @field33
,`昨日までの1月の1位の値の起日` = STR_TO_DATE(CONCAT(@field34,@field35,@field36),'%Y%m%d')
,`統計開始年` = @field37;
csv出力
公式に書いてあること、そのままです
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
INDEXを作成してみる
先ほど作った最高気温を保存しているhighテーブルにindexを作成して見ます。
※本来ならもっと大量のデータに対してINDEXを作成するものでしょうが、
今回は私がSQLを勉強し始めた頃に知っておきたかったことという内容で書いてますので、とりあえず作成だけ実施してみました
ALTER TABLE high ADD INDEX 都道府県_idx(都道府県)
EXPLAINを先頭につけると、indexが使用されたことがわかる
explain select *
from high
where 都道府県='沖縄県'
TRANSACTION、COMMIT、ROLLBACK
TRANSACTIONを開始し、COMMITして決定するか、ROLLBACKで前の状態に戻すかをできる便利な機能
この話はよくデッドロックの問題と一緒に話されていることが多いので、
実際にデッドロックを起こしてみました。
CREATE TABLE `tests` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO tests VALUES(null,'111'),(null,'222');
デッドロック手順
セッション①：START TRANSACTION
↓
セッション②：START TRANSACTION
↓
セッション①：update tests set col = 'aaa' where id =1
↓
セッション②：update tests set col = 'bbb' where id =2
↓
セッション①：update tests set col = 'ccc' where id =2
実行中のまま動かなくなるデッドロックがおきました
表結合で私がわかりやすいと思った説明
※そもそも表結合がどういう動きか感覚が掴めなかった頃の自分がわかりやすいと思った説明です。
なので、sqlが内部的にこうやって表結合している、という説明ではありません
まず結合キーを書かず、表を2つselectした場合どうなるかを確認
select *
from animal,bunrui
テーブルanimal(レコード3行)とbunrui(レコード5行)が倍掛けになって15行結果が来る
内部結合(INNER JOIN)
そこに対して、結合キーを指定する
select *
from animal,bunrui
where animal.bunrui_id=bunrui.id;
-- または
select *
from animal
inner join bunrui on animal.bunrui_id=bunrui.id;
同一レコードごとに色分けするとこのような感じです。
倍掛けで増えたレコードの中で、結合キーが一致するものだけが選択される
外部結合(LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)
- LEFT JOIN(LEFT OUTER JOIN)
select *
from animal
left join bunrui on animal.bunrui_id=bunrui.id;
外部結合する際は、結合キーで条件を指定する場合と、
WHEREで条件を指定する場合では、結果が変わってしまうため注意。
select *
from animal
left join bunrui
on animal.bunrui_id=bunrui.id
and bunrui.name='両生類';
select *
from animal
left join bunrui
on animal.bunrui_id=bunrui.id
where bunrui.name='両生類';
複数テーブル結合
select *
from syubetsu
left join bunrui on syubetsu.id=bunrui.syubetsu_id
left join animal on bunrui.id=animal.bunrui_id
- RIGHT JOIN(RIGHT OUTER JOIN)
select *
from animal
right join bunrui on animal.bunrui_id=bunrui.id;
- FULL OUTER JOIN
LEFT JOINとRIGHT JOINを同時にやっているようなことのようです
※MySQLではFULL OUTER JOINをサポートしていないため、postgresqlで実行しています
postgresql
select *
from animal
full outer join bunrui on animal.bunrui_id=bunrui.id;
最後まで読んでいただいてありがとうございました
SQLサンプル
001
-- DROP TABLE IF EXISTS japan;
CREATE TABLE IF NOT EXISTS japan (
地方 VARCHAR(20),
都道府県 VARCHAR(20) unique,
シーケンス TINYINT(1) unsigned
) DEFAULT CHARSET=utf8mb4;
INSERT INTO japan(地方,都道府県,シーケンス) VALUES
('北海道','北海道',1),
('東北','青森県',1),
('東北','岩手県',2),
('東北','宮城県',3),
('東北','秋田県',4),
('東北','山形県',5),
('東北','福島県',6),
('関東','茨城県',1),
('関東','栃木県',2),
('関東','群馬県',3),
('関東','埼玉県',4),
('関東','千葉県',5),
('関東','東京都',6),
('関東','神奈川県',7),
('中部','新潟県',1),
('中部','富山県',2),
('中部','石川県',3),
('中部','福井県',4),
('中部','山梨県',5),
('中部','長野県',6),
('中部','岐阜県',7),
('中部','静岡県',8),
('中部','愛知県',9),
('近畿','三重県',1),
('近畿','滋賀県',2),
('近畿','京都府',3),
('近畿','大阪府',4),
('近畿','兵庫県',5),
('近畿','奈良県',6),
('近畿','和歌山県',7),
('中国','鳥取県',1),
('中国','島根県',2),
('中国','岡山県',3),
('中国','広島県',4),
('中国','山口県',5),
('四国','徳島県',1),
('四国','香川県',2),
('四国','愛媛県',3),
('四国','高知県',4),
('九州','福岡県',1),
('九州','佐賀県',2),
('九州','長崎県',3),
('九州','熊本県',4),
('九州','大分県',5),
('九州','宮崎県',6),
('九州','鹿児島県',7),
('九州','沖縄県',8);
commit;
002
-- DROP TABLE IF EXISTS guest;
CREATE TABLE IF NOT EXISTS guest (
支店 VARCHAR(10),
来客数 INT(5),
日付 DATETIME,
売り上げ合計 INT(10),
現場責任者 VARCHAR(10)
);
INSERT INTO guest VALUES
('札幌',20,NOW(),100000,'佐藤'),
('札幌',25,ADDDATE(NOW(),-1),130000,'佐藤'),
('札幌',18,ADDDATE(NOW(),-2),90000,'鈴木'),
('東京',52,NOW(),350000,'田中'),
('東京',53,ADDDATE(NOW(),-1),370000,'斉藤'),
('東京',37,ADDDATE(NOW(),-2),250000,'田中'),
('大阪',37,NOW(),210000,'村田'),
('大阪',25,ADDDATE(NOW(),-1),170000,'村田'),
('大阪',30,ADDDATE(NOW(),-2),190000,'村田');
commit;
003
-- DROP TABLE IF EXISTS fuku_1;
CREATE TABLE IF NOT EXISTS fuku_1 (
syohin_mei VARCHAR(30),
size VARCHAR(6),
PRIMARY KEY(syohin_mei,size)
);
INSERT INTO fuku_1 VALUES
('上着','L'),
('上着','M'),
('上着','S'),
('シャツ','L'),
('シャツ','M'),
('ズボン','M'),
('ズボン','S');
commit;
004
-- DROP TABLE IF EXISTS fuku_2;
CREATE TABLE IF NOT EXISTS fuku_2 (
syohin_mei VARCHAR(30),
size_l TINYINT(1),
size_m TINYINT(1),
size_s TINYINT(1)
);
INSERT INTO fuku_2 VALUES
('上着',1,1,0),
('シャツ',1,1,1),
('ズボン',0,0,1);
commit;
005
postgresql
-- DROP TABLE IF EXISTS syohin_mst;
-- DROP TABLE IF EXISTS sales;
-- 商品マスタ
CREATE TABLE IF NOT EXISTS syohin_mst (
shop_id VARCHAR(10),
code VARCHAR(100),
name VARCHAR(100),
amount INT,
campaign_flg SMALLINT,
PRIMARY KEY(shop_id,code)
);
-- 1個の売り上げに対し、1レコードずつ保存されていくテーブル
CREATE TABLE IF NOT EXISTS sales (
shop_id VARCHAR(10),
code VARCHAR(100),
sales_date TIMESTAMP
);
INSERT INTO syohin_mst(shop_id,code,name,amount,campaign_flg) VALUES
('11001','01S','消しゴム小',250,0),
('11001','01M','消しゴム中',300,0),
('11001','01L','消しゴム大',300,0),
('11001','02A','えんぴつ1本',100,0),
('11001','02B','えんぴつ5本',500,0),
('11001','03A','ボールペン',110,1),
('11002','01S','消しゴム小',300,1),
('11002','01M','消しゴム中',350,1),
('11002','01L','消しゴム大',400,1),
('11002','02A','えんぴつ1本',100,0),
('11002','02B','えんぴつ5本',500,0),
('11003','01S','消しゴム小',300,0),
('11003','01M','消しゴム中',350,1),
('11003','01L','消しゴム大',400,0),
('11003','02A','えんぴつ1本',100,0),
('11003','02B','えんぴつ5本',500,0),
('11004','01M','消しゴム中',350,1),
('11004','02A','えんぴつ1本',100,1),
('11004','03A','ボールペン',120,0),
('11005','01S','消しゴム小',250,0),
('11005','01M','消しゴム中',300,0),
('11005','01L','消しゴム大',350,0),
('11005','02A','えんぴつ1本',80,1),
('11005','02B','えんぴつ5本',400,1),
('11005','03A','ボールペン',110,1),
('11005','01X','ねり消しゴム',500,0),
('11006','01S','消しゴム小',260,1),
('11006','01M','消しゴム中',310,1),
('11006','01L','消しゴム大',360,1),
('11006','02A','えんぴつ1本',100,1),
('11006','02B','えんぴつ5本',500,1),
('11007','01S','消しゴム小',260,0),
('11007','01M','消しゴム中',310,0),
('11007','01L','消しゴム大',360,0),
('11007','02A','えんぴつ1本',110,1),
('11007','02B','えんぴつ5本',510,1);
INSERT INTO sales(shop_id,code,sales_date) VALUES
('11001','01S',now()+'-2 months'),
('11001','01S',now()+'-1 months'),
('11001','01S',now()),
('11001','01S',now()+'-1 months'),
('11001','01S',now()),
('11001','01S',now()),
('11001','01S',now()),
('11001','01S',now()),
('11001','01S',now()),
('11001','01M',now()),
('11001','01M',now()),
('11001','01M',now()),
('11001','01L',now()+'-2 months'),
('11001','02A',now()+'-2 months'),
('11001','02A',now()+'-2 months'),
('11001','02A',now()+'-1 months'),
('11001','02A',now()),
('11001','02B',now()+'-1 months'),
('11001','02B',now()+'-1 months'),
('11001','02B',now()+'-1 months'),
('11001','02B',now()),
('11001','03A',now()+'-2 months'),
('11001','03A',now()+'-1 months'),
('11002','01S',now()+'-2 months'),
('11002','01S',now()+'-1 months'),
('11002','01S',now()),
('11002','01M',now()+'-2 months'),
('11002','01M',now()+'-1 months'),
('11002','01M',now()+'-1 months'),
('11002','01M',now()),
('11002','01L',now()+'-2 months'),
('11002','01L',now()+'-1 months'),
('11002','01L',now()+'-1 months'),
('11002','01L',now()),
('11002','01L',now()),
('11002','02A',now()+'-2 months'),
('11002','02A',now()+'-1 months'),
('11002','02A',now()),
('11002','02B',now()+'-2 months'),
('11002','02B',now()+'-1 months'),
('11002','02B',now()),
('11003','01S',now()+'-1 months'),
('11003','01S',now()),
('11003','01S',now()),
('11003','01M',now()+'-2 months'),
('11003','01M',now()+'-1 months'),
('11003','01M',now()+'-1 months'),
('11003','01M',now()+'-1 months'),
('11003','01M',now()),
('11003','01L',now()+'-2 months'),
('11003','01L',now()+'-1 months'),
('11003','01L',now()),
('11003','02A',now()+'-2 months'),
('11003','02A',now()+'-1 months'),
('11003','02A',now()),
('11003','02B',now()+'-2 months'),
('11003','02B',now()+'-1 months'),
('11003','02B',now()),
('11004','01M',now()+'-2 months'),
('11004','01M',now()+'-1 months'),
('11004','01M',now()+'-1 months'),
('11004','01M',now()+'-1 months'),
('11004','01M',now()),
('11004','02A',now()+'-2 months'),
('11004','02A',now()),
('11004','03A',now()+'-1 months'),
('11004','03A',now()+'-1 months'),
('11004','03A',now()+'-1 months'),
('11004','03A',now()+'-1 months'),
('11004','03A',now()+'-1 months'),
('11005','01S',now()+'-2 months'),
('11005','01S',now()+'-1 months'),
('11005','01S',now()),
('11005','01S',now()),
('11005','01S',now()),
('11005','01M',now()+'-2 months'),
('11005','01M',now()+'-1 months'),
('11005','01M',now()),
('11005','01L',now()+'-2 months'),
('11005','01L',now()+'-1 months'),
('11005','01L',now()+'-1 months'),
('11005','01L',now()+'-1 months'),
('11005','01L',now()),
('11005','01L',now()),
('11005','01L',now()),
('11005','01L',now()),
('11005','02A',now()+'-1 months'),
('11005','02A',now()),
('11005','02B',now()+'-2 months'),
('11005','02B',now()+'-1 months'),
('11005','02B',now()),
('11005','03A',now()+'-2 months'),
('11005','03A',now()+'-2 months'),
('11005','03A',now()+'-2 months'),
('11005','03A',now()+'-1 months'),
('11005','03A',now()+'-1 months'),
('11005','03A',now()),
('11005','03A',now()),
('11005','03A',now()),
('11005','01X',now()+'-2 months'),
('11005','01X',now()+'-1 months'),
('11005','01X',now()),
('11006','01S',now()+'-2 months'),
('11006','01S',now()+'-1 months'),
('11006','01S',now()),
('11006','01S',now()),
('11006','01M',now()+'-1 months'),
('11006','01M',now()),
('11006','01L',now()+'-2 months'),
('11006','01L',now()+'-1 months'),
('11006','01L',now()),
('11006','02A',now()+'-2 months'),
('11006','02A',now()+'-1 months'),
('11006','02A',now()),
('11006','02B',now()+'-1 months'),
('11006','02B',now()),
('11006','02B',now()),
('11006','02B',now()),
('11007','01S',now()+'-1 months'),
('11007','01S',now()),
('11007','01S',now()),
('11007','01M',now()+'-2 months'),
('11007','01M',now()+'-1 months'),
('11007','01M',now()),
('11007','01L',now()+'-2 months'),
('11007','01L',now()+'-1 months'),
('11007','01L',now()),
('11007','02A',now()+'-2 months'),
('11007','02A',now()+'-1 months'),
('11007','02A',now()),
('11007','02A',now()),
('11007','02A',now()),
('11007','02A',now()),
('11007','02A',now()),
('11007','02B',now());
commit;
006
-- DROP TABLE IF EXISTS cust;
-- DROP TABLE IF EXISTS member;
-- DROP TABLE IF EXISTS food;
-- DROP TABLE IF EXISTS food_category;
-- DROP TABLE IF EXISTS drink;
-- 来店したお客さん
CREATE TABLE IF NOT EXISTS cust(
cust_id INT(11),
name VARCHAR(20),
delete_flg TINYINT(1) DEFAULT 0
);
-- 会員登録したお客さん
CREATE TABLE IF NOT EXISTS member(
member_id INT(11),
cust_id INT(11),
status VARCHAR(10),
delete_flg TINYINT(1) DEFAULT 0
);
-- 注文した料理
CREATE TABLE IF NOT EXISTS food_order(
cust_id INT(11),
food_id INT(11),
delete_flg TINYINT(1) DEFAULT 0
);
-- 料理一覧
CREATE TABLE IF NOT EXISTS food(
food_id INT(11),
food_cat_id INT(11),
name VARCHAR(20),
delete_flg TINYINT(1) DEFAULT 0
);
-- 料理のカテゴリー
CREATE TABLE IF NOT EXISTS food_category(
food_cat_id INT(11),
name VARCHAR(20),
delete_flg TINYINT(1) DEFAULT 0
);
CREATE OR REPLACE VIEW customer_view(
cust_id,
name
) AS
SELECT cust_id,name
FROM cust
WHERE delete_flg=0;
CREATE OR REPLACE VIEW member_view(
member_id,
cust_id,
status
) AS
SELECT member_id,cust_id,status
FROM member
WHERE delete_flg=0
AND status='加入中';
CREATE OR REPLACE VIEW food_order_view(
cust_id,
food_id
) AS
SELECT cust_id,food_id
FROM food_order
WHERE delete_flg=0;
CREATE OR REPLACE VIEW food_view(
food_id,
food_cat_id,
name
) AS
SELECT food_id,food_cat_id,name
FROM food
WHERE delete_flg=0;
CREATE OR REPLACE VIEW food_category_view(
food_cat_id,
name
) AS
SELECT food_cat_id,name
FROM food_category
WHERE delete_flg=0;
INSERT INTO cust VALUES
(1,'佐藤',0),
(2,'鈴木',0),
(3,'田中',1),
(4,'木村',1),
(5,'村田',0);
INSERT INTO member VALUES
(1,1,'加入中',0),
(2,2,'加入中',0),
(3,3,'登録ミス',1),
(4,3,'登録ミス',1),
(5,3,'加入中',0),
(6,5,'退会済',0);
INSERT INTO food_order VALUES
(1,1,0),
(1,9,0),
(1,6,1),
(2,7,0),
(2,7,0),
(3,2,0),
(3,3,1),
(4,5,0),
(5,10,0),
(5,8,0),
(5,7,0),
(5,1,0),
(5,5,1),
(5,6,1);
INSERT INTO food VALUES
(1,1,'枝豆',0),
(2,1,'サラダ',0),
(3,1,'唐揚げ',0),
(4,1,'鍋',0),
(5,1,'寿司',0),
(6,2,'水',0),
(7,2,'ビール',0),
(8,2,'ハイボール',0),
(9,2,'焼酎',0),
(10,2,'ワイン',0);
INSERT INTO food_category VALUES
(1,'食べ物',0),
(2,'飲み物',0);
commit;
007
-- DROP TABLE IF EXISTS high;
CREATE TABLE IF NOT EXISTS high(
`観測所番号` INT(11) NOT NULL PRIMARY KEY
,`都道府県` VARCHAR(50)
,`地点` VARCHAR(50)
,`国際地点番号` INT(11)
,`現在時刻` DATETIME
,`今日の最高気温` FLOAT
,`今日の最高気温の品質情報` INT(2)
,`今日の最高気温起時` TIME
,`今日の最高気温起時の品質情報` INT(2)
,`平年差` FLOAT
,`前日差` FLOAT
,`該当旬（月）` INT(2)
,`該当旬（旬）` INT(2)
,`極値更新` INT(2)
,`10年未満での極値更新` INT(2)
,`今季最高` INT(2)
,`今年の最高気温（昨日まで）` FLOAT
,`今年の最高気温（昨日まで）の品質情報` INT(2)
,`今年の最高気温（昨日まで）を観測した起日` DATE
,`昨日までの観測史上1位の値` FLOAT
,`昨日までの観測史上1位の値の品質情報` INT(2)
,`昨日までの観測史上1位の値を観測した起日` DATE
,`昨日までの1月の1位の値` FLOAT
,`昨日までの1月の1位の値の品質情報` INT(2)
,`昨日までの1月の1位の値の起日` DATE
,`統計開始年` INT(11)
) DEFAULT CHARSET=utf8;
008
-- DROP TABLE IF EXISTS animal;
-- DROP TABLE IF EXISTS bunrui;
-- DROP TABLE IF EXISTS syubetsu;
-- 動物
CREATE TABLE IF NOT EXISTS animal(
id INT(11),
bunrui_id INT(11),
name VARCHAR(20)
);
-- 分類
CREATE TABLE IF NOT EXISTS bunrui(
id INT(11),
syubetsu_id INT(11),
name VARCHAR(20)
);
-- 種別
CREATE TABLE IF NOT EXISTS syubetsu(
id INT(11),
name VARCHAR(20)
);
INSERT INTO animal VALUES
(1,1,'犬'),
(2,3,'カエル'),
(3,10,'イワシ');
INSERT INTO bunrui VALUES
(1,1,'哺乳類'),
(2,1,'鳥類'),
(3,1,'両生類'),
(4,1,'爬虫類'),
(5,2,'節足動物');
INSERT INTO syubetsu VALUES
(1,'脊椎動物'),
(2,'無脊椎動物');
commit;