文組生的轉職日記

備份 1. 進入SSMS操作介面 2. 在要備份的資料庫上點選右鍵_工作_備份 3. 選擇備份類型 (Express版僅有完整與差異備份，enterprise版才有多交易紀錄備份。) 4. 做差異備份前一定要做完整備份，因為差異備份是備份對於上次完整備份後，所做的改變部分備份。 5. 選擇媒體選項 (覆寫媒體選擇_附加至現有/覆寫所有現有備份) 6. 確認備份位置 7. 按確定讓系統執行備份 通常 -> 每周備份一次完整。每天備份一次差異。每小時做一次交易紀錄備份。 還原 1. 進入SSMS操作介面 2. 資料庫_右鍵 3. 選取表單上的還原資料庫 4. 選取來源<檔案位置/裝置> 5. 按下確定_開始還原 卸離資料庫 卸離資料庫後，可在SQL安裝資料夾中將資料庫檔案直接複製，並移動到另外一台電腦上。 1. 進入SSMS操作介面 2. 資料庫_右鍵_卸離 3. 點選確定即卸離完成 備註： 卸離後拷貝檔案需要卸離資料庫後才可備份檔案，備份速度為被複製檔案的大小差別。 附加資料庫 1. 進入SSMS操作介面 2. 資料庫_右鍵_附加 3. 選擇要附加的資料庫 4. 點選確定即完成附加

簡答題 請說明以下三者有何不同？完整備份、差異備份、卸離後拷貝檔案 完整備份： 將資料庫內的所有資料建立完整備份。因資料最完整，備份時間最長、檔案最大。 差異備份： 比對上一次資料庫的完整備份，只備份有變動的部分。 在使用差異備份時要先建立一次完整備份。 因只備份差異部分，備份時間較短，但若長時間未定期完整備份，備份檔案仍會越來越大。 卸離後拷貝檔案： 備份的資料完整度與完整備份相同，差異在於拷貝過程中需將資料庫卸離，造成資料庫需暫時性離線。 因為是直接拷貝檔案，速度會比備份還原快一些。 卸離的檔案若搬移到另一電腦使用，再重新附加資料庫時，可能會有使用者權限的問題。 基本語法練習 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...

基本介紹： 視觀表又稱檢視表，是一個虛擬的資料表。 視觀表本身並無儲存資料，只有定義資料，定義從那些資料表或檢視表挑出那些欄位或記錄。 雖然視觀表不是一個真正的資料表，是由SQL Command所產生。但是完全可以當成一般的資料表使用，可以新增、更新、刪除資料，但這些操作實際上是作用在其定義的來源資料表。 另一個角度來看，可以是將特定查詢的SQL Command以View的方式，儲存在資料庫裡。 在建立視觀表時，建議在名稱前方加上特定字串以供區別，如：VW_userinfo_taipei。 優點：隱藏/過濾原始資料，並簡化資料查詢。 達成邏輯資料獨立。 增加資料安全性。 簡化資料查詢。 簡化使用者觀點。 缺點：多一道建立過程，且沒有真正資料，所以有更多的操作限制。 執行效率差。 更多的操作限制。 增加管理的複雜度。 語法範例： 建立一個住在台北市民眾資料的視觀表 CREATE VIEW v_userinfo_taipei AS SELECT userinfo.uid, cname, address FROM userinfo, live, house WHERE userinfo.uid = live.uid AND live.hid = house.hid AND house.address LIKE = '台北市%' 注意！AS後面只能接一個SQL Command (巢狀/UNION ALL) 使用視觀表：與一般查詢資料表相同 --查詢住在台北市民眾資料的所有欄位 select * from v_userinfo_taipei --查詢住在台北市民眾資料共有幾筆 Select count(*) from v_userinfo_taipei 進階練習：查詢平均電話費大於300的費用 --先建立含有平均電話費的視觀表 create view vw_avg_fee as select tel, round(avg(fee),0) as avg_fee from bill group by tel --使用視觀表並篩選資料 select * from vw_avg_fee where avg_fee > 300 其他解法分享 (不使用VIEW的方...

資料操作語言 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...

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...

數值函數_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...