【程式學習之路:Day17】關聯式資料庫應用:資料庫原理 、SQL Command
∎ SQL Server 2017 Express 安裝筆記
▸ Step1:至微軟官網下載安裝 SQL Server 2017 Express 版本,限win10
下載SQL Server 2017 Express:https://goo.gl/MYXAzC
下載SQL Server 2012 Express:https://goo.gl/Q7bKbc
(若OS為win7,需改安裝SQL Server 2012版本)
.點選立即下載 。
.執行下載下來的SQLServer2017-SSEI-Expr.exe,
點選 「基本 」>「接受 」>「安裝 」 。
.SQL Server 2017 安裝完成畫面 。
▸ Step2:接著安裝 SQL Server SSMS
下載 SQL Server Management Studio 17.9.1: https://goo.gl/wECK8P
.執行下載下來的 SSMS-Setup-CHT.exe,點選安裝 。
.安裝過程 。
.SQL Server SSMS安裝完成畫面 。
▸step03:透過SSMS連進資料庫,即安裝完成!
.點選開始功能表,找到 「Microsoft SQL Server Management Studio 17 」將其啟動 。(可以將此應用程式拉到桌面方便應用)
.點選連線,成功進到資料庫即代表完成!
∎ 資料庫原理
一句話解釋:按照規定將資料存放到資料庫 。
▸ 資料庫模型
將所有雞蛋放在同一個籃子裡。
籃子裡只要有一筆資料損壞,整個資料庫都會損毀,
意味著,資料庫的備份無比重要!
資料庫的備份機制
每週做一次稱為完整備份
每天做一次稱為差異備份
每小時做一次稱為交易紀錄備份
但備份為非同步,備份過程一定會有資料損失
於是有另外一種機制:備援
資料庫的備援機制
備援資料庫的數量是相對的
可為一對一,或是一對多,視其維護層級的需求
最高等級備援方式:熱備援,建置成本相當高
小結:
備份一定有資料損失,
而備援不會。
▸資料類型
關聯式資料庫
在資料庫內部即可處理完成,所產生的資料即為使用者可辨別的資料,
缺點:處理分散式資料效率較差。
文本式資料庫
NoSQL:需要搭配前端語言處理,處理分散式資料相當得心應手。
▸資料庫管理系統
◦管理介面:
讓使用者透過操作介面,下 SQL Command指令,即可完成存取資料。
◦資料庫引擎:
實際資料的存取。
◦資料庫
▸ SQL Command種類
[主要談方法]
資料操作語言(DML, data manipulation language)
◦Insert Into: 插入資料
◦Update: 修改資料
◦Delete: 刪除資料
資料查詢語言(DQL, data query language)
◦Select: 查詢資料
[主要談權限]
資料控制語言(DCL, data control language)
◦Grant: 設定權限
◦Revoke: 取消權限
◦Commit: 確認交易完成
◦Rollback: 交易失敗回復原始狀態
▸關聯式資料庫組成
先有資料表再決定有甚麼欄位
實體(entity)-> Table
屬性(attribute) -> Field
屬性對應到資料庫的位置稱為欄位
關連性(relationship)-> 心中
關聯不存在於資料庫裡面,存在資料庫設計者的心裡。
▸案例:通訊錄
對資料庫來說02等於2,
關聯式資料庫談的是之間的關聯。
查詢:李大媽住哪?
李大媽:是搜尋條件,在姓名資料表裡面(位於哪個表哪個欄位)
要抓出彼此間的關聯性:屋號2號(把有關連的表挑出來)
住哪:是搜尋目標,在住址電話表裡面(問甚麼給甚麼資料)
進階:電話1111是誰?
逆向找到關聯性
▸ 索引(Index)
目的:讓搜尋條件維持正確性,搜尋更有效率,加快搜尋速度
相對的,需要更多的空間放置索引
思考,哪些資料需要建立索引?
◦查詢「條件」的欄位
◦關連線兩端的欄位
所有索引情況:2為基數的(欄位數)次方減1
▸ 主索引、主鍵、Primary Key、PK
目的:設成主索引的欄位其值,資料庫保證兩件事情:
◦不可重複 ◦不可空白不填
重點:SQL server每一張表一定要設定主索引!
原因:若資料重複時將無法做修改
特性:
一張表只有一張主索引
但主索引所在欄位不一定是單一欄位
可以是一個欄位,也可以是兩個欄位結合,稱為複合欄位主索引
▸參考索引、外來鍵、Foreign Key、FK
參考索引不是索引!
案例:數值的範圍要和被參考欄位值相同,若不符,資料庫可以拒絕存取
如何維護資料正確性 > 網頁上就要確保這件事情,若網頁有設定數值範圍機制,資料庫可自由添加
串接刪除
資料無法自由刪除,因彼此間互相參考機制,除非將相對參考的值做修改
(實務上會有上千萬筆資料,無法一一修改)
串接刪除為,相同關聯性的值全部刪除
▸ 數值型態
備註:
tinyint -128~127
int -32768~32767
bigint -32768的平方~32767的平方
文字
nchar 固定長度的字串資料
varchar 可變長度的字串資料
nvarchar 可變長度的字串資料
前面的n為unicode,所有語系都可以接收,
例如:nvarchar(10)可放五個中文字or十個英文字
▸ 正規化
一句話:規範資料如何存放
把一個資料表拆成兩個表,遵守越多正規化,資料越破碎
所以實務上不會遵守所有正規化,最多三個
第一正規化(1NF):每一個欄位內只能放一個值
第二正規化(2NF):談的是重複性,為了減少資料重複的情形
多對多的關係,一定會產生第三個資料表(一對一,一對多,多對多)
一對一關係:大多可合併為一張資料表
一對多:一個人可擁有多支電話,電話只專屬一人
多對多:一個人可買多棟房子,這棟房子可以住很多人(ex:書本,修課)
第三正規劃 (3NF):另外一個值,是因為另一個非主索引的值去算出來的
▸ 關連圖 Entity-Relationship (ER) Diagrams
▸資料字典
*標明星號:索引
小結:資料庫最重要的兩個文件:
1. 關連圖 Entity-Relationship (ER) Diagrams
2. 資料字典
∎ 上機實做
.新增資料庫
.新增資料表
.設計資料表屬性,編輯資料表資料
.讓資料表能夠修改欄位資料
設定方法:工具>選項>設計師>防止儲存需要資料表重建的變更
▸精準查詢(=)
select *
from UserInfo
where uid=’AAA'
select *
from UserInfo
where CName=’Howhow’
▸模糊查詢(lile)
select *
from UserInfo
where CName like’小%’
(備註:%小%:全文檢索)
▸Q:列出howhow和小美的資料
第一種寫法:
select *
from UserInfo
where CName =’阿明’ or CName = ‘Howhow’
(備註:不能寫and)
第二種寫法:
select *
from UserInfo
where CName in (‘阿明’,’Howhow’)
▸Q:列出howhow和小美以外的資料
第一種寫法:
select *
from UserInfo
where CName not in (‘阿明’,’Howhow’)
第二種寫法:
select *
from UserInfo
where CName <>’阿明’ or CName <> ‘Howhow’
▸Q:列出阿明的UID,Adress,tel
搜尋條件:阿明,在userInfo資料表
搜尋目標:UID,Adress,tel ,在UserInfo,House,Phone資料表
找出關連性:
select UserInfo.UID,CName,Address,TEL
from UserInfo,Live,House,Phone
where userinfo.UID=live.uid and
live.HID=House.HID and
House.HID=Phone.HID and
userinfo.CName=’阿明’
▸Q:列出小美的家裡電話
搜尋條件:小美 > UserInfo
搜尋目標:tel >Phone
第一種寫法:
select TEL
from UserInfo,Live,House,Phone
where userinfo.UID=live.uid and
live.HID=House.HID and
House.HID=Phone.HID and
userinfo.CName=’小美’
第二種寫法:
select TEL
from UserInfo,Live,Phone
where userinfo.UID=live.uid and
live.HID=Phone.HID and
userinfo.CName=’小美’
▸Q:列出所有電話號碼,且反向排序
select *
from phone
order by tel desc
注意:order by後面可以加多個欄位
select * f
rom phone
order by hid desc,tel desc
▸Q:查詢沒有打姓名的資料(null與空字串)
select *
from UserInfo
where CName=’’ or CName is null
▸避免資料庫出現null的情況
null若出現在前端網頁,會使應用程式Bug
select * , ISNULL(CName,’’)
from UserInfo
where CName=’’ or CName is null