文組生的轉職日記

簡答題 請說明以下三者有何不同？完整備份、差異備份、卸離後拷貝檔案 完整備份： 將資料庫內的所有資料建立完整備份。因資料最完整，備份時間最長、檔案最大。 差異備份： 比對上一次資料庫的完整備份，只備份有變動的部分。 在使用差異備份時要先建立一次完整備份。 因只備份差異部分，備份時間較短，但若長時間未定期完整備份，備份檔案仍會越來越大。 卸離後拷貝檔案： 備份的資料完整度與完整備份相同，差異在於拷貝過程中需將資料庫卸離，造成資料庫需暫時性離線。 因為是直接拷貝檔案，速度會比備份還原快一些。 卸離的檔案若搬移到另一電腦使用，再重新附加資料庫時，可能會有使用者權限的問題。 基本語法練習 1. 列出姓李的姓名、住址、電話 select userinfo.uid as '身分證', cname as '姓名', address as '住址', tel as '電話' from userinfo Left Join live on userinfo.uid = live.uid left join house on live.hid = house.hid left join phone on house.hid = phone.hid where cname like '李%' 解題想法： 根據原始資料庫的設計，題目所需的姓名、住址、電話等資訊，分別儲存在Userinfo、House、Phone資料表中。因此，為了讓目標欄位能成功產出，必須先讓相關聯的資料表Join成一張大表，再帶入篩選條件--姓李的姓名。 使用語法： 1. Join--Left Join 2. Like 3. as 2. 台北市有多少棟房子 select count(address) as '台北市房屋數' from house where address like '台北市%' 解題想法： 這個題目是要篩選出台北市總共有多少棟房子。而在模擬的資料庫中，house資料表裡包含全 台灣的房屋地址。因此要將篩選的條件，設定在住址欄位中含有"台北市"的值，再利用 count() function...

如要建立博客來首頁的全站分類與購物車料庫系統，ERD該如何設計？ 筆記： 資料庫在建立時一定要考量到關聯性(一對一/一對多/多對多) 索引的建立 資料字典的建立 資料庫使用流程 for 後端工程師 於資料庫建置時就必須思考好，並建立提供給後端工程師使用的說明文件。 E.g ISBN, 作者, 書名, 出版社,…etc. 1. insert into ____ values() 2. insert into ____.... 3. select * from …. 4. insert into _____...etc. **資料庫會隨著前端功能變化改變而變動。 **資料庫確定後，後續軟體開發較易進行。 **前端需求與功能變化大，資料庫如何應對? 使用Json (字串格式) 一個資料表應對? E.g. { “UID” : “A01”, “cname” : “AAA”, “birthday”: “1999-01-01” } NoSQL [No Only SQL] (MySQL 8支援NoSQL格式) 當資料量成長至一定程度且穩定時，可選擇轉SQL或NoSQL。 **正統NoSQL: MongoDB

資料操作語言 DML INSERT INTO插入資料 插入一筆資料到資料表(不指定欄位) INSERT INTO userinfo VALUES ('A03','王大明') 注意：values後的資料順序要與資料表欄位相同，若不知道資料內容要補null，不可為空欄位。 插入資料到指定的欄位 /*將'宜蘭縣'插入到HOUSE資料表的ADDRESS欄位*/ INSERT INTO house(address) VALUES ('宜蘭縣') (house資料表HID為自動填入的流水號，所以指定欄位Address填入。) 將某資料表裡的資料複製到新的資料表 (通常是備份資料時用) /*將台北市民眾資料複製到另一個資料表*/ INSERT INTO new_table (uid, cname)   SELECT uid, cname   FROM v_userinfo_taipei 注意：插入資料前要確認目的地資料表已存在。 --把house表裡面的台北市資料插入到new_house資料表。 insert into new_house select * from house where address like '台北市%' 指定複製的欄位 /*把house表裡面的宜蘭縣資料插入到new_house資料表中的b欄位(指定複製的欄位要與新資料表欄位對應)*/ insert into new_house (b) select address from house where address like '宜蘭縣%' UPDATE 更新資料 更新所有資料 UPDATE userinfo SET cname = NULL 更新特定資料 --將 A03 的姓名改為孫小毛，身份證字號改為B01 UPDATE userinfo SET cname = '孫小毛',uid = 'B01' WHERE uid = 'A03' DELETE 刪除資料 刪除所有資料 /*刪除bill資料表內的所有資料*/ DELETE FROM bill /*另一種刪除方式*/ TRUNCAT...

如果要在資料庫中儲存時間，建議不要用datetime格式，請使用int資料型態。 原因如下： 1. 各家資料庫系統對於時間儲存有自己的格式。 2. 若為全球性的系統或軟體，資料庫儲存時間在轉換時區時，可能會因時區不同造成系統錯誤的問題。 因此，目前現行資料庫會建議使用int資料型態，儲存成epochtime/timestamp格式。 Epochtime其實只是一串整數。換算方式是以1970/1/1 00:00:00為基準，計算現在的格林威治時間與基準時間差了幾秒，然後把秒數轉成整數。 讓資料庫只儲存數字，數字至時間格式的轉換交由程式去做。 格式轉換網址： www.epochconverter.com

Trigger 觸發程序 功能：攔截資料表中發生的INSERT、DELETE、UPDATE事件。 (資料庫的檯面下交易，因為看不到它的運作。) 語法： Create Trigger tr_trigger_name --觸發程序的名稱通常前面會加個tr之類的字做識別 On userinfo --攔截的目標資料表 For INSERT, UPDATE, DELETE --看要攔截的事件是什麼，可以只寫一個。 AS //Trigger觸發後要做的事情寫這邊 老師的提醒： **Trigger的建立一定要寫文件做紀錄。 **Trigger也可設定成攔截A資料表中的事件，然後自動完成某件事情至B資料表。 **當For後面攔截三個事件，相對的AS後面執行的程式碼也會較複雜。初學可將三個事件拆成三個Tr寫。 範例：若Userinfo中新增一筆資料，將此事件自動記錄到log資料表中。 Create trigger tr_userinfo_log On userinfo For insert AS Declare @uid nvarchar(50) --設定 Declare @cname nvarchar(50) --停止計算SQL影響的資料列數 (系統預設會自動計算並顯示在SSMS訊息視窗) SET NOCOUNT ON Select @uid = uid, @cname = cname from INSERTED --從INSERTED資料表中提取新增或修改的那筆資料 /*INSERTED資料表是系統自動產生的表，裡面存放新增或修改的那一整筆資料。*/ Insert into log(body) values('資料表USERINFO中新增'+'@uid'+','+'@cname'+資料) --把那筆資料寫入資料表log裡 補充說明： Declare是T-SQL中的變數宣告。變數前面一律加@符號。(兩個@符號是全域變數，通常是系統變數。如：@@ERROR) 另外，Declare後面的資料型態要與對應的資料表內的資料型態相同，字串大小最少要和資料表內的資料型態同，不可小於該數字。  SET NOCOUNT ON：SQL預設在執行INSER...

變數形式 變數的形式主要分為兩大類 區域變數：使用者自訂的變數。 區域變數以@開頭。 如：@n 全域變數：屬於系統變數，以@@開頭。 例如：@@ERROR 變數宣告 變數的宣告方式，是在變數前方加上Declare單字，後方加上要指定給該變數的資料型態。 DECLARE @n int Declare @cname varchar(10) 註：T-SQL語言中，語法的大小寫並沒有差別。 變數的設定與顯示 設定變數有以下兩種方法： select @n = 100 set @a = 120 若要顯示變數的內容，則是如下方： select @n 將select的結果放入變數 /*將A01的姓名放入變數中*/ declare @cname varchar(20) select @cname = cname from userinfo where uid = 'A01' IF_判斷 IF @i > 10 BEGIN // 判斷式成立 END ELSE BEGIN // 判斷式不成立 END 註：如果 Begin End 中間只有單行程式碼，Begin End可省略不打。 IF @i > 10 // 判斷式成立 ELSE // 判斷式不成立 CASE_多個條件判斷 CASE語法主要是針對多個條件判斷需求所使用，但原則上多個條件判斷也可使用if...else。 declare @cname varchar(50) select @cname = cname from userinfo where uid = 'A03' print CASE @cname when '王力宏' then '姓王' when '周杰倫' then '姓周' else '不知姓什麼' End WHILE_迴圈 Declare @i int --迴圈要執行幾次的變數 Set @i = 0 --設初始值為零 while @i < 10 --當變數i小於10 BEGIN if @i = 5 --當變數i等於5 begin set@i=7  --設變數為...

數值函數_COUNT() 資料數量 當我們需要了解資料表中含有幾筆資料時，可使用count()函數進行計算。 範例：列出資料表中有多少筆資料 select count(*)/* 括號中放入要計算的欄位 */ from userinfo  /* 從userinfo資料表 */ 範例：列出userinfo資料表中有多少個姓王的資料 select count(cname) /* 括號中放入要計算的欄位 */ from userinfo /* 從userinfo資料表 */ where cname like '王%' select count(*) /* 括號中放入要計算的欄位, 這裡星號代表所有欄位 */ from userinfo /* 從userinfo資料表 */ where cname like '王%' 數值函數_avg() 平均值 當我們需要計算資料表內某欄位的平均值，則可使用avg()。 例如，查詢每一支電話的平均費用 select tel, avg(fee) from bill group by tel 數值函數_sum() 加總 計算資料表某欄位內的加總。 範例：查詢每一支電話費的總額 select tel, sum(fee) from bill group by tel 數值函數_round() 取小數點後幾位 當欄位值(或運算的回傳值)有小數點時，可利用round函數取特定小數點後位數。 SELECT ROUND(235.415, 2) 數值函數_Max() 查詢欄位最大值。 /*查詢每支電話的最高電話費 */ SELECT tel, max(fee) from bill group by tel 數值函數_Max() 查詢欄位最小值。 /*查詢每支電話的最低電話費 */ SELECT tel, min(fee) from bill group by tel 數值函數_Floor() 取整數。 SELECT floor(235.415) Having  運算值條件 Having 是針對函數產生的值設定條件，因為where無法針對函數產生的值下條件。 通常是使用在一個...

群組_GROUP BY 將相同的資料合併在一起。 SELECT tel, sum(fee) /* sum(fee)會是一個計算值 ((根據每一群組做加總 */ FROM bill GROUP BY tel /*要群組的欄位 ((在bill資料表中，針對tel欄位的相同值綁在一起。*/ **group by 產出的表中出現的欄位，除了計算的值之外，要設定在group by後面的欄位才可放。(如下方的例子) /* 先根據電話group一次，之後再用地址group一次。 */ Select tel, sum(fee), address From bill, house Where bill.hid = house.hid Group by tel, address 別名_as 暫時性的替換名稱，資料表或欄位都可以。別名除了單一英文字外，其餘需用單引號夾住。 備註：Oracle不可打as；MS-SQL必須打as Select * From userinfo as a, live as b, house as c Where a.uid = b.uid and b.hid = c.uid SELECT a.uid AS '身份證字號', cname AS '姓名', address AS '住址', tel AS '電話' FROM userinfo AS a, live AS b, house AS c, phone AS d WHERE a.uid = b.uid AND b.hid = c.hid AND c.hid = d.hid ORDER BY a.uid 不重複的資料_DISTINCT 拿掉重複的資料，使產出的資料表中不重複。 /*列出所有的姓氏*/ SELECT DISTINCT left(cname, 1)  讓重複的姓氏(第一個字)拿掉 FROM userinfo 綜合練習_DISTINCT, count(), 巢狀查詢 列出每個姓氏有幾筆資料 SELECT lastname, count(*) AS n FROM ( SELECT left(cname, 1) as lastname FROM user...

dateadd() 時間加減 dateadd(單位, 多少單位, 欄位/func()) 範例： select dateadd(day, 5, getdate()) /*現在時間加五天*/ select dateadd(day, -20, getdate())  /*現在時間減20天*/ select getdate() /*取得現在時間*/ select dateadd(day, 1, ‘2018/2/28 0:0:0’)  /*確認是否有2/29*/ 註：每家資料庫的function都不同<oracle/MSSQL/Access都不同> datediff() 計算兩個時間的差距 語法：DATEDIFF ( datepart , startdate , enddate ) 範例：計算table1裡的dd欄位，其欄位值裡的時間與現在時間差多少天。 select datediff(day, getdate(), dd) from table1 進階練習：計算userinfo裡的會員年齡。 --userinfo 每個人的年齡 --Wrong Ans_有BUG (用年份去算會造成多算一歲) SELECT userinfo.uid as '身分證', cname as '姓名', datediff(year,userinfo.bday,getdate()) as '年齡' FROM userinfo --Wrong Ans2_有BUG (超過一定數字後的差異不見了) SELECT userinfo.uid as 'ID No.', cname as 'Name', datediff(year,userinfo.bday,getdate())/365 as 'Age' FROM userinfo --正解_使用天數除以365.25計算年齡 SELECT userinfo.uid as ‘ID No.’, cname as ‘Name’, bday as Birthday, floor(datediff(day,userinfo.bday,getdate())/365.25 )  + 1 as...

語法基本架構 select uid, cname from userinfo where cname = ‘王大明’ 從上方的範例我們可以觀察出，在 select 後方需要鍵入欲查詢的資料欄位，也就是最終查詢完成時我們想要看到的資料表格。而 from 後方鍵入從哪一個資料表中查詢 where 後方輸入查詢條件 例一：查詢userinfo資料表中的所有欄位 select * from userinfo 例二：查詢userinfo資料表中的特定欄位 select cname, birthday from userinfo 除了單純地查詢欄位外，透過給定條件、function的使用，可使查詢更精確。 LIKE 查詢含有特定字元的欄位 select * from userinfo where cname like = '李%' 1. 使用%符號區隔表示要查詢的關鍵字。 2. LIKE是模糊查詢，屬於全文檢索指令。 3. 欲查詢的關鍵字需以單引號 ' 夾住。 4. %符號的位置決定關鍵字查詢的方式。(請注意看以下範例！) select * from userinfo where cname like = '李%' /*搜尋李開頭的值*/ select * from userinfo where cname like = '%王' /*搜尋王結尾的值*/ select * from userinfo where cname like = '%王%' /*搜尋任何含有王關鍵字的值*/ 額外補充：特殊符號" _ "底線的用法 在SQL查詢中可用底線代表中英文的一個空值，並結合關鍵字做查詢。但是基本上很少用，因為一個底線只對應到一個字的關鍵字。 例如：王_ _ (王大明會出現/王磊則不會) AND/OR 連結查詢條件 如同其他程式語言，在SQL語法中也可使用AND或OR來代表相對應的條件。 例如，當我們要查詢資料庫中，姓李與姓黃的人名時，便可使用OR來連接查詢條件。 select * from userinfo where cname like '李%' OR cname like '黃%...

--------------課程筆記----------- 定義介紹 如果車庫用鋼筋水泥蓋，那資料庫就是用資料建 資料庫 是資料存取的規則 目前主流關聯式資料庫供應商(由小到大排列) SQLite FREE! Access 圖像化 非資訊相關皆可操作 易上手 10~20 users MS-SQL 適合中小企 個人使用免費 Oracle 價格百萬、市面上最貴 銀行業/航空業 可同時萬人上線 災難防護性佳 MySQL 開源 全球第二大 效率高 No-SQL 文本資料庫 違反Relational Database規則 檔案單位從T計算 分散式資料庫 GOOGLE/FACEBOOK 大數據!? 各家資料庫副檔名 SQL Server .mdf Oracle .dbf Access .mdb SQLite .sqlite 資料庫備援方式 冷備援  (Cold site) 完整備份(可能的頻率: 每周) 差異備份(可能的頻率: 每天) 備份與先前一次備份的差異部分 交易紀錄備份(每30mins/60mins) 只備份指令 熱備援  (Hot site) 分主要系統與備份系統 同時運行與寫入，緊急情況時可由主系統切換至備份系統 E.g. 某電信有6套系統，並採異地備援 因等於同時設立多套一樣的系統，建置成本高。 資料庫模型 階層式 網路式 物件導向式 關聯式 <目前主流!> 資料庫架構 管理系統/介面 DBMS (管理系統與使用者介面) 引擎 (資料庫與部分的管理系統) SQL Command (各家廠商有80%都相同) 資料定義語言（DFL, data definition language）  Create: 建立資料庫物件 Alter: 變更資料庫物件 Drop: 刪除資料庫物件 資料操作語言（DML, data manipulation language) 只有這三個可以修改資料 Insert Into: 插入資料 Update: 修改資料 Delete: 刪除資料 資料查詢語言（DQL, data query langua...