sqlite 記帳資料庫
sqlite 記帳資料庫
嗯,我用很久了...
首先是 sqlite-manager http://code.google.com/p/sqlite-manager/
如果有用 firefox 的話
那就裝一下 http://sqlite-manager.googlecode.com/files/sqlitemanager-0.5.17.xpi 吧
如果沒有
先抓 XULRunner https://developer.mozilla.org/en/XULRunner_1.9.2_Release_Notes
http://releases.mozilla.org/pub/mozilla.org/xulrunner/releases/1.9.2.8/runtimes/xulrunner-1.9.2.8.en-US.win32.zip
然後再抓 sqlite-manager
http://sqlite-manager.googlecode.com/files/sqlitemanager-xr-0.5.17-es-ES.zip
個別解壓縮
在 .\xulrunner-1.9.2.8.en-US.win32\xulrunner\ 資料夾中會有 xulrunner.exe 執行檔
右鍵建立捷徑
右鍵選捷徑內容
在捷徑標籤的目標欄位,加上 sqlite-manager 的 application.ini 位置
像是如果我的 application.ini 是在下面這一行的這個地方的話
C:\Documents and Settings\kfgg\My Documents\sqlitemanager-xr-0.5.17-es-ES\application.ini
那我的 xulrunner.exe 的捷徑目標欄就填
"C:\Documents and Settings\kfgg\My Documents\xulrunner-1.9.2.8.en-US.win32\xulrunner\xulrunner.exe" "C:\Documents and Settings\kfgg\My Documents\sqlitemanager-xr-0.5.17-es-ES\application.ini"
加雙引號的原因是因為路徑中有空白的原故
接下來,點兩下捷徑就會跑出 sqlite-manager 視窗了
先選擇 Database -> New Database 建立新資料庫,填檔名、選位置
接下來匯入 sql,選 Database -> Import -> 點 SQL 標籤 -> Select File 選擇 sql 檔 -> UTF-8 -> OK
匯入完成
下面貼一下我用的帳單資料庫結構 sql,檔案記得存成 UTF-8
--------------------------------------------------
DROP TABLE IF EXISTS "main_class";
CREATE TABLE main_class (
id INT UNSIGNED NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY(id));
INSERT INTO "main_class" VALUES(1,'INCOME');
INSERT INTO "main_class" VALUES(2,'HOME EXPENSES');
INSERT INTO "main_class" VALUES(3,'TRANSPORTATION');
INSERT INTO "main_class" VALUES(4,'HEALTH');
INSERT INTO "main_class" VALUES(5,'CHARITY_GIFTS');
INSERT INTO "main_class" VALUES(6,'DAILY LIVING');
INSERT INTO "main_class" VALUES(7,'ENTERTAINMENT');
INSERT INTO "main_class" VALUES(8,'SAVINGS');
INSERT INTO "main_class" VALUES(9,'OBLIGATIONS');
INSERT INTO "main_class" VALUES(10,'SUBSCRIPTIONS');
INSERT INTO "main_class" VALUES(11,'MISCELLANEOUS');
INSERT INTO "main_class" VALUES(12,'SMART CARD');
DROP TABLE IF EXISTS "one_expenditure";
CREATE TABLE one_expenditure (
year INT UNSIGNED NOT NULL,
month INT UNSIGNED NOT NULL,
day INT UNSIGNED NOT NULL,
main_class_id INT UNSIGNED NOT NULL,
sub_class_id INT UNSIGNED NOT NULL,
amount INT NOT NULL,
detail text);
DROP TABLE IF EXISTS "sub_class";
CREATE TABLE sub_class (
main_class_id INT UNSIGNED NOT NULL,
id INT UNSIGNED NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY(main_class_id, id));
INSERT INTO "sub_class" VALUES(1,1,'Wages_Tips');
INSERT INTO "sub_class" VALUES(1,2,'Interest Income');
INSERT INTO "sub_class" VALUES(1,3,'Dividends');
INSERT INTO "sub_class" VALUES(1,4,'Gifts Received');
INSERT INTO "sub_class" VALUES(1,5,'Refunds_Reinbursements');
INSERT INTO "sub_class" VALUES(1,6,'Transfer From Savings');
INSERT INTO "sub_class" VALUES(1,7,'MISCELLANEOUS');
INSERT INTO "sub_class" VALUES(2,1,'Mortgage_Rent');
INSERT INTO "sub_class" VALUES(2,2,'Home_Rental Insurance');
INSERT INTO "sub_class" VALUES(2,3,'Electricity');
INSERT INTO "sub_class" VALUES(2,4,'Gas_Oil');
INSERT INTO "sub_class" VALUES(2,5,'Water_Sewer_Trash');
INSERT INTO "sub_class" VALUES(2,6,'Phone');
INSERT INTO "sub_class" VALUES(2,7,'Cable_Satellite');
INSERT INTO "sub_class" VALUES(2,8,'Internet');
INSERT INTO "sub_class" VALUES(2,9,'Furnishings_Appliances');
INSERT INTO "sub_class" VALUES(2,10,'Lawn_Garden');
INSERT INTO "sub_class" VALUES(2,11,'Maintenance_Supplies');
INSERT INTO "sub_class" VALUES(2,12,'Improvements');
INSERT INTO "sub_class" VALUES(2,13,'Other');
INSERT INTO "sub_class" VALUES(3,1,'Vehicle Payments');
INSERT INTO "sub_class" VALUES(3,2,'Auto Insurance');
INSERT INTO "sub_class" VALUES(3,3,'Fuel');
INSERT INTO "sub_class" VALUES(3,4,'Bus_Taxi_Train Fare');
INSERT INTO "sub_class" VALUES(3,5,'Repairs');
INSERT INTO "sub_class" VALUES(3,6,'Registration_License');
INSERT INTO "sub_class" VALUES(3,7,'Other');
INSERT INTO "sub_class" VALUES(4,1,'Health Insurance');
INSERT INTO "sub_class" VALUES(4,2,'Doctor_Dentist');
INSERT INTO "sub_class" VALUES(4,3,'Medicine_Drugs');
INSERT INTO "sub_class" VALUES(4,4,'Health Club Dues');
INSERT INTO "sub_class" VALUES(4,5,'Life Insurance');
INSERT INTO "sub_class" VALUES(4,6,'Veterinarian_Pet Care');
INSERT INTO "sub_class" VALUES(4,7,'Other');
INSERT INTO "sub_class" VALUES(5,1,'Gifts Given');
INSERT INTO "sub_class" VALUES(5,2,'Charitable Donations');
INSERT INTO "sub_class" VALUES(5,3,'Religious Donations');
INSERT INTO "sub_class" VALUES(5,4,'Other');
INSERT INTO "sub_class" VALUES(6,1,'Groceries');
INSERT INTO "sub_class" VALUES(6,2,'Personal Supplies');
INSERT INTO "sub_class" VALUES(6,3,'Clothing');
INSERT INTO "sub_class" VALUES(6,4,'Cleaning');
INSERT INTO "sub_class" VALUES(6,5,'Education_Lessons');
INSERT INTO "sub_class" VALUES(6,6,'Dining_Eating Out');
INSERT INTO "sub_class" VALUES(6,7,'Salon_Barber');
INSERT INTO "sub_class" VALUES(6,8,'Pet Food');
INSERT INTO "sub_class" VALUES(6,9,'Other');
INSERT INTO "sub_class" VALUES(7,1,'Videos_DVDs');
INSERT INTO "sub_class" VALUES(7,2,'Music');
INSERT INTO "sub_class" VALUES(7,3,'Games');
INSERT INTO "sub_class" VALUES(7,4,'Rentals');
INSERT INTO "sub_class" VALUES(7,5,'Movies_Theater');
INSERT INTO "sub_class" VALUES(7,6,'Concerts_Plays');
INSERT INTO "sub_class" VALUES(7,7,'Books');
INSERT INTO "sub_class" VALUES(7,8,'Hobbies');
INSERT INTO "sub_class" VALUES(7,9,'Film_Photos');
INSERT INTO "sub_class" VALUES(7,10,'Sports');
INSERT INTO "sub_class" VALUES(7,11,'Outdoor Recreation');
INSERT INTO "sub_class" VALUES(7,12,'Toys_Gadgets');
INSERT INTO "sub_class" VALUES(7,13,'Vacation_Travel');
INSERT INTO "sub_class" VALUES(7,14,'Other');
INSERT INTO "sub_class" VALUES(8,1,'Emergency Fund');
INSERT INTO "sub_class" VALUES(8,2,'Transfer to Savings');
INSERT INTO "sub_class" VALUES(8,3,'Retirement');
INSERT INTO "sub_class" VALUES(8,4,'Investments');
INSERT INTO "sub_class" VALUES(8,5,'Education');
INSERT INTO "sub_class" VALUES(8,6,'Smart Card Prepaid');
INSERT INTO "sub_class" VALUES(8,7,'Other');
INSERT INTO "sub_class" VALUES(9,1,'Student Loan');
INSERT INTO "sub_class" VALUES(9,2,'Other Loan');
INSERT INTO "sub_class" VALUES(9,3,'Credit Card Debt');
INSERT INTO "sub_class" VALUES(9,4,'Alimony_Child Support');
INSERT INTO "sub_class" VALUES(9,5,'Federal Taxes');
INSERT INTO "sub_class" VALUES(9,6,'State_Local Taxes');
INSERT INTO "sub_class" VALUES(9,7,'Other');
INSERT INTO "sub_class" VALUES(10,1,'Newspaper');
INSERT INTO "sub_class" VALUES(10,2,'Magazines');
INSERT INTO "sub_class" VALUES(10,3,'Dues_Memberships');
INSERT INTO "sub_class" VALUES(10,4,'Other');
INSERT INTO "sub_class" VALUES(11,1,'Bank Fees');
INSERT INTO "sub_class" VALUES(11,2,'Postage');
INSERT INTO "sub_class" VALUES(11,3,'Other');
INSERT INTO "sub_class" VALUES(12,1,'Bus_Taxi_Train Fare');
INSERT INTO "sub_class" VALUES(12,2,'Groceries');
INSERT INTO "sub_class" VALUES(12,3,'Dining_Eating Out');
CREATE VIEW "列出主類別每個月的總支出" AS SELECT main_class.name as m_name,
one_expenditure.year as year,
one_expenditure.month as month,
total(one_expenditure.amount) as total_main_class_expenditure,
total(one_expenditure.amount) / total_expenditure_per_month * 100 as total_expenditure_percentage
from one_expenditure, main_class,
(SELECT year as t_year,
month as t_month,
count(one_day_sum) as day_count,
avg(one_day_sum) as average_expenditure_a_day_per_month,
sum(one_day_sum) as total_expenditure_per_month
from
(SELECT one_expenditure.year as year,
one_expenditure.month as month,
one_expenditure.day,
sum(one_expenditure.amount) as one_day_sum
from one_expenditure
where main_class_id != 1 and main_class_id != 12
group by year, month, day)
group by year, month
order by year, month)
where main_class_id = main_class.id and year = t_year and month = t_month
group by year, month, main_class.id
order by year, month, main_class.id;
CREATE VIEW "列出副類別每個月每天的平均支出" AS SELECT m_name,
s_name,
year,
month,
count(one_day_sum) as day_count,
avg(one_day_sum) as average_expenditure_a_day_per_month
from
(SELECT main_class.name as m_name,
sub_class.name as s_name,
one_expenditure.year as year,
one_expenditure.month as month,
one_expenditure.day,
sum(one_expenditure.amount) as one_day_sum
from one_expenditure, main_class, sub_class
where one_expenditure.main_class_id = main_class.id and
one_expenditure.sub_class_id = sub_class.id and
sub_class.main_class_id = main_class.id
group by year, month, day, main_class.id, sub_class.id)
group by year, month, m_name, s_name
order by year, month, m_name, s_name;
CREATE VIEW "列出副類別每個月每筆的平均支出" AS SELECT main_class.name as m_name,
sub_class.name as s_name,
one_expenditure.year as year,
one_expenditure.month as month,
count(one_expenditure.amount) as item_count,
avg(one_expenditure.amount) as average_expenditure_an_item_per_month
from one_expenditure, main_class, sub_class
where one_expenditure.main_class_id = main_class.id and
one_expenditure.sub_class_id = sub_class.id and
sub_class.main_class_id = main_class.id
group by year, month, main_class.id, sub_class.id
order by year, month, main_class.id, sub_class.id;
CREATE VIEW "列出副類別每個月的總支出" AS SELECT main_class.name as m_name,
sub_class.name as s_name,
one_expenditure.year as year,
one_expenditure.month as month,
total(one_expenditure.amount) as total_sub_class_expenditure,
total(one_expenditure.amount) / total_expenditure_per_month * 100 as total_expenditure_percentage
from one_expenditure, main_class, sub_class,
(SELECT year as t_year,
month as t_month,
count(one_day_sum) as day_count,
avg(one_day_sum) as average_expenditure_a_day_per_month,
sum(one_day_sum) as total_expenditure_per_month
from
(SELECT one_expenditure.year as year,
one_expenditure.month as month,
one_expenditure.day,
sum(one_expenditure.amount) as one_day_sum
from one_expenditure
where main_class_id != 1 and main_class_id != 12
group by year, month, day)
group by year, month
order by year, month)
where one_expenditure.main_class_id = main_class.id and
one_expenditure.sub_class_id = sub_class.id and
sub_class.main_class_id = main_class.id and
year = t_year and month = t_month
group by year, month, main_class.id, sub_class.id
order by year, month, main_class.id, sub_class.id;
CREATE VIEW "列出每個月的總支出與每天平均支出" AS SELECT year,
month,
count(one_day_sum) as day_count,
