# **資料庫workshop**

## 1.撰寫shell script，設計從台灣期交所網站抓取資料，寫入MySQL資料庫

#!/bin/bash
#設定today變數
today=`date +'%Y_%m_%d'`

#用today變數確保排程正常運作
url="https://www.taifex.com.tw/file/taifex/Dailydownload/DailydownloadCSV/Daily_$today.zip"

wget $url

zip_name=`echo $url | awk -F'/' '{print $NF}'`

unzip $zip_name

#避免重新寫入資料庫
if [ ! -e $zip_name ]; then
        echo 'zip file download fail.'
        exit
fi


#用sed參數更改副檔名
csv_name=`echo $zip_name | sed 's/zip/csv/g'`

echo $csv_name

#轉換編碼，寫入另一個檔案
iconv $csv_name -f BIG-5 -t UTF-8 > $csv_name"_1"

#找到有含TX的內容，awk -f 拆分欄位
cat $csv_name | grep ,TX | grep -v '/' | awk -F',' '{print $1 "," $4 "," $2 "," $3 "," $5 "," $6/2}' | sed 's/ //g' > future_tick.csv

mv $csv_name"_1" future_tick.csv

#匯入資料庫
mysqlimport -u root  -p123456 --local --fields-terminated-by="," --lines-terminated-by="\r\n"  finance /home/admin1/future_tick.csv

rm future_tick.csv

echo 'Done!'

## 2.更改檔案權限

chmod 755  auto_finance_file.sh

## 3.將腳本加入排程，並做紀錄

### crontab -e

# 排程時間設定
0 6 * * 1-5
#紀錄在download.log
/home/jack/download.sh >> /home/jack/download.log2>&1

## 4.在MySQL建立資料庫、表

create database finance ;

use finance；

CREATE TABLE future_tick (
  trade_date DATE NOT NULL,
  product_code VARCHAR(12) NOT NULL,
  expiration_month VARCHAR(12) NOT NULL,
  trade_time VARCHAR(12) NOT NULL,
  trade_price float(10, 2) NOT NULL,
  trade_qty INT NOT NULL,
  nearby_price float(10, 2),
  deferred_price float(10, 2),
  opening_auction VARCHAR(10)
);

## 5.建立日、夜盤的VIEW

#建立日盤的VIEW

CREATE VIEW future_day 
AS 
SELECT *
FROM future_tick
WHERE product_code='TX' and trade_time BETWEEN '084500' and '134500';




#建立夜盤的VIEW

CREATE VIEW future_night 
AS 
SELECT *
FROM future_tick
WHERE product_code='TX' and not trade_time BETWEEN '084500' and '134500';

## 6.建立平均成交價的VIEW

#建立平均成交價的VIEW
CREATE VIEW avg_price
AS 
SELECT trade_date,AVG(trade_price) as avg_price
FROM future_tick
where future_tick.product_code = 'TX'
group by trade_date;

## 7.給予其他使用者權限

#給予jack日盤權限
GRANT select
ON finance.future_day
TO 'jack'@'localhost' 
IDENTIFIED BY '123456';

#給予jack夜盤權限
GRANT select
ON finance.future_night
TO 'jack'@'localhost' 
IDENTIFIED BY '123456';

#給予jack成交價平均權限
GRANT select
ON finance.avg_price
TO 'jack'@'localhost' 
IDENTIFIED BY '123456';

## 8.用其他使用者的權限登入資料庫，SELECT VIEW