資料庫基本觀念

-

一、基本觀念

1、表(資料表)

(1)、索引(索引鍵)

  •     聚集索引(主索引)：只有一個
  •     非聚集索引(次索引)：可能多個

(2)、觸發器(觸發程序)：存在表架構下，做為資料表操作時(新、更新、刪除)自動觸發動作。

(3)、約束(條件約序)：只限於表中存在，當滿足條件時自動執行約束限制，例如性別欄位，只能輸入男或女。

2、數據庫關係圖(資料庫圖表)：表與表之間關係圖

3、視圖(檢視) ：視圖不存儲數據，而是預先寫好的查詢語句，目的控制用戶顯示數據及安全。

4、可編程性(可程式性)→存儲過程(預存程序)用途：

  1. 可減少存儲過程執行代碼之網路存取訊息流量
  2. 因有經過優化，減少存取每次運行時間
  3. 考量安全性，如穩藏數據庫訊息
  4. 存儲過程之間可以彼此調用

5、用戶與角色(登入與伺服器角色)

6、SQL Server 中的對象名

命令：　[ServerName.[DataBaseName.[SchemaName.]]] ObjectName
  • SchemaName：預設為dbo，參考資料庫名稱>安全性>結構描述
  • ObjectName ：可以是 Table、View、Function...等名稱

二、資料表連接

1、內部連接：

  • 顯示兩個表有匹配(相交)記錄的資料
  • select * from A inner join B on A.id = B.id

2、外部連接：分成

  • 左外連接 left join，顯示兩個表有(相交)記錄並顯示左邊的表沒匹配的記錄
  • 右外連接 right join，顯示兩個表有(相交)記錄並顯示右邊的表沒匹配的記錄
  • 全連接 full join，顯示兩個表有(相交)記錄並顯示左右邊的表沒匹配的記錄
PS：

  • 所謂左右邊，指語法設指定時前後關係，如 Form A left join B ，其中A就是左邊，B就是右邊。
  • 在使用連結，盡量使用內部連接，避免使用外部連結，可以避免代碼間的錯誤。
  • 當連結多個表，先執行第一段連結，連結結果再和第二段做連結，以此類推

3、交叉連結：

  • 就是兩個表相乘的和，如A表10筆記錄，B表20筆記錄，算10*20 = 200筆記錄
  • 語法：select * from A cross join B

三、聯合

  1. 將第二查詢結果附加在第一個查詢結果未尾，稱為聯合查詢
  2. JOIN 功能將垂直增加，增加更多列
  3. UNION 功能將水平增加，增加更多行
  4. 語法：
    SELECT row1,row2  FROM table1
    UNION [ALL]
    SELECT row1,row2  FROM table2
  5. 使用UNION 處理查詢時，要注意以下機個關鍵點：

  • 所有UNION查詢必須在SELECT清單中有相同的欄位數。
  • UNION 傳回的結果集的標題只能從第一個查詢中取得。
  • 查詢中對應列的資料類型必須隱式一致，當上下兩個欄位型態不同時，必須進行轉換，例CASE(ID AS varchar(10))。
  • 與其他非 UNION 查詢不同,UNION 查詢的預設回傳選項為DISTINCT,而非 ALL。

四、建立與修改資料表

CREATE TABLE

CREATE TABLE [database_n ame.[owner] Jtable_name)

(<column name>data type>

[DEFAULT <con stantexpression>]

[IDENTITY[(seed.increment) [NOT FOR REPLICATIONI]

[ROWGUIDCOL]

[COLLATE <Collation namey]

[NULL I NOT NULL ]

<column con straints>]

[column_name AS computed_column_expression]

[ table_con straint [...n])

[ON <filegroup>| DEFAULT)]

[ ColumnName dataType foreign key references TableName(ColumnName) ] //參加外部約束

[TEXTIMAGE_ON.<filegroup-DEFAULT]


ALTER TABLE


添加新列

ALTER TABLE <table name> ADD <column name> <data type>


删除列

ALTER TABLE <table name> DROP COLUMN<column name>


更改列的数据类型

ALTER TABLE <table name> ALTER COLUMN <column name>

<new data type>


添加包含约束的列

ALTER TABLE <table name> ADD <column name> <data type>

CONSTRAINT <constraint name> < constraint type> [check]

CONSTRAINT <constraint name> < constraint type> unique  //唯一約束

增加外部約束

ALTER TABLE <table name> ADD CONSTRAINT <constraint name>  Foreign Key (主要表欄位名稱)  References 被引用表名稱(欄位名稱)


在现有列中添加一个未经验证的CHECK约束

ALTER TABLE <table name> WITH NO CHECK

ADD CONSTRAINT <constraint name>

CHECK (check condition)



五、約束 

  1. 約束：是一種限制，通過表或列確保資料完整性規格的限制規則
  2. 約束的類型：
    • 域約束：針對個別的Col或多個Col做約束，在新增列或插入列發生時，執行約束限制，例如單價不能為負數或庫存量有預設值等約束。
    • 實體約束：每一ROW都有唯一值，都不會重複的資料
    • 參照完整性約束：母表與子表間某個Col之間的約束，例如產品內類別ID與類別內類別ID必須一致(做約束)
  3. 主鍵約束：Col的約束，值是唯一，並且不能null
  4. 唯一約束：Col的約束，值是唯一，但可以一次null 
    例如: 設定studentNO 約束是唯一值約束
    create table students
    (ID int identity not null primary key,
    studentNO char(6) null unique,
    name nvarchar(20))
  5. Check約束、DEFAULT約束、停用與啟用既有約束
    --檢查生日輸入值20~70之間
    --其中 DATEDIFF(單位,被減日期,當前日期)
    alter table people add constraint check_Birthday check (datediff(year, birthday, getdate()) between 20 and 70)
    --查詢性別只能輸入M與F
    alter table people add constraint check_sex check (sex in ('M','F')) 

    --建立資料表指定city欄位預設值為 '珠海'
    create table unit (unitID int not null primary key identity, 
    unitName nvarchar(50),
    city nvarchar(20) default '珠海')

    alter table people add JoiningDate date 

    --設定JoiningDate 預設值為當天
    alter table people add constraint default_JoiningDateForToday default getDate() for JoiningDate

    alter table people add Birthplace nvarchar(20)

    --設定約束，輸入Birthplace 必須含有'區'
    --charIndex(查找字元,被檢查欄位名)
    alter table people add constraint check_Birthplace_keyWord check (CharIndex('區',Birthplace)>0)


    --關閉已設定約束，關閉Birthplace 必須含有'區'約束
    alter table people nocheck constraint check_Birthplace_keyWord
    --恢復既有約束
    alter table people check constraint check_Birthplace_keyWord
  6. 約束與觸發器比較：
約束優點：
快速、可以引用其他列、在命令執行前發生、遵循ANSI標準。
約束缺點：
必須對每張表重新定義、不能引用其他表、不能綁定到資料型別。 

                 觸發器優點：

非常靈活、可以引用其他欄位或其他資料、表可以透過.NET 引用 SQL Server 以外的其他資訊。

                 觸發器缺點：

在執行指令之後發生、系統開銷很大。


六、子查詢

觀念：

子查詢是巢狀或內嵌在另一個查詢之內的 SELECT 陳述式。 巢狀查詢 (即子查詢) 稱為內部查詢。 包住巢狀查詢的查詢稱為外部查詢。


子查詢目的是將結果傳回給外部查詢。 結果的形式將決定子查詢是純量子查詢或多重值子查詢：

  • 純量子查詢傳回單一值。 外部查詢必須處理單一結果。
  • 多重值子查詢傳回的結果很像單一資料行的資料表。 外部查詢必須能夠處理多個值。

除了在純量子查詢和多重值子查詢之間選擇，子查詢可以是自足式子查詢，也可以與外部查詢相互關聯：

  • 自足式子查詢可以撰寫成獨立的查詢，而不相依於外部查詢。 當外部查詢執行時，自足式子查詢只處理一次，並將結果傳給該外部查詢。
  • 相互關聯的子查詢會參考外部查詢的一或多個資料行，因此相依於外部查詢。 相互關聯的子查詢與外部查詢不能分開執行。

若要撰寫純量子查詢，請考慮下列指導方針：

  • 若要表示查詢為子查詢，請以括弧括住。
  • Transact-SQL 支援多個層級的子查詢。 在本課程模組中，我們只考慮兩個層級的查詢 (一個內部查詢在一個外部查詢內)，但最多可支援 32 個層級。
  • 如果子查詢未傳回任何資料列 (空集合)，則子查詢的結果為 NULL。 如果您的情況有可能沒傳回任何資料列，請確保外部查詢除了處理其他預期結果，也能妥善處理 NULL。
  • 內部查詢通常只傳回單一資料行。 在子查詢中選取多個資料行幾乎註定錯誤。 唯一例外是以 EXISTS 關鍵字引進子查詢。

相互關聯的子查詢，請考慮下列指導方針：

  • 有別於自足式子查詢，使用相互關聯的子查詢時有一些特殊考慮：
    • 相互關聯的子查詢與外部查詢不能分開執行。 此限制使測試和偵錯變得複雜。
    • 不同於自足式子查詢只處理一次，相互關聯的子查詢會執行多次。 邏輯上，外部查詢會先執行，然後針對每個傳回的資料列，再處理內部查詢
  • 撰寫外部查詢接受來自內部查詢的適當傳回結果。 如果內部查詢是純量，您可以在 WHERE 子句中使用等式比較運算子，例如 =、<、> 和 <>。 如果內部查詢可能會傳回多個值，請使用 IN 述詞。 規劃處理 NULL 結果。
  • 識別外部查詢中的資料行，此資料行將由相互關聯子的查詢參考。 在外部查詢中，為資料行的來源資料表宣告別名。
  • 識別內部資料表中的資料行，此資料行將與外部資料表中的資料行比較。 建立來源資料表的別名，就像對外部查詢所做的一樣。
  • 撰寫內部查詢，根據外部查詢的輸入值，從來源取得值。 例如，在內部查詢的 WHERE 子句中使用外部資料行。

PS：

  • 您如何決定以 JOIN 或子查詢來撰寫涉及多個資料表的查詢？ 有時只是習慣問題而已。 大部分很容易轉換成 JOIN 的巢狀查詢，實際上會在內部轉換成 JOIN。 針對這類查詢，以何種方式撰寫查詢真的沒什麼差別。
  • 請謹記一項限制，在使用巢狀查詢時，傳回給用戶端的結果只能包含來自外部查詢的資料行。 因此，如果您需要兩個資料表都傳回資料行，則應該使用 JOIN 來撰寫查詢。
  • 最後，在某些情況下，內部查詢需要執行的作業，遠比範例中的簡單擷取複雜得多。 使用 JOIN 來重寫複雜的子查詢可能很困難。 許多 SQL 開發人員發現子查詢最適合複雜的處理，因為可讓您將處理分割成較小的步驟。

資料來源

七、MERGE 應用

用途，可以合併T-SQL MDL合併成一個組合，提高性能和減化事物的做法法


--找出各個項目在每個月銷售量

select * from ProductSaleDetails

select 

ProductName,

Year(SaleDate) SaleYear,

Month(SaleDate) as SaleMonth,

sum(SaleVoulume) as saleVoulume

from ProductSaleDetails

group by  ProductName ,Year(SaleDate),Month(SaleDate)

select * from [dbo].[ProductMonthReport]


--根據產品銷售明細，新增資料至產品銷售報表，如已有資料，則更新合併銷售量，否則準新增一筆資料

merge [dbo].[ProductMonthReport] as MR 

using (select 

ProductName,

Year(SaleDate) SaleYear,

Month(SaleDate) as SaleMonth,

sum(SaleVoulume) as SaleVoulume

from ProductSaleDetails

group by  ProductName ,Year(SaleDate),Month(SaleDate)) Details 

on MR.ProductName = Details.ProductName 

and MR.Year = Details.SaleYear 

and MR.Month = Details.SaleMonth

when matched then

update set SaleVolume = Details.SaleVoulume

when not matched then

insert (Year , Month , ProductName , SaleVolume) values (Details.SaleYear , Details.SaleMonth , Details.ProductName , Details.SaleVoulume)

output $action ,

inserted.Year,

inserted.Month,

inserted.ProductName,

inserted.SaleVolume,

deleted.Year,

deleted.Month,

deleted.ProductName,

deleted.SaleVolume;

八、維護索引

  • 問題：
    • 碎片
    • 確定碎片和頁拆分的可能性
  • 影響：
    • 產生浪費空間
    • 碎片太多，在查找資料，容易額外系統開銷


--查詢Address 資料表碎片與頁拆分狀況

declare @db_id int ,@object_id int

set @db_id = DB_ID('AdventureWorks2019')

set @object_id = OBJECT_ID('AdventureWorks2019.person.Address')

SELECT  database_id,object_id,index_id,partition_number,avg_fragmentation_in_percent,fragment_count FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(@db_id,@object_id,null,null,null) 


--重建索引

alter index [PK_Address_AddressID] on AdventureWorks2019.person.Address rebuild with (fillfactor=80)


九、視圖

  • 用途：
    • 減少數據庫複雜性
    • 向用戶穩藏特定欄位資訊
    • 特加額外索引，提高查詢性能

例如：

--建立一個視圖，可以看個人資訊，含全名、聯絡地址、聯絡電話

CREATE VIEW View_PersonIndividualInfo AS

SELECT (Per.FirstName + Per.LastName) AS FullName, per.PersonType, PP.PhoneNumber, Addr.AddressLine1, AddressLine2

FROM Person.BusinessEntity BE

JOIN person.Person Per ON Per.BusinessEntityID = BE.BusinessEntityID and  Per.PersonType='IN'

JOIN person.BusinessEntityAddress BEA ON BEA.BusinessEntityID = BE.BusinessEntityID

JOIN person.[Address] Addr ON Addr.AddressID = BEA.AddressTypeID

JOIN person.PersonPhone  PP ON pp.BusinessEntityID = BE.BusinessEntityID


--查詢建立view資訊

SELECT *  FROM View_PersonIndividualInfo


十、腳本的基礎知識

USE Accounting

--變數宣告與給值

DECLARE @var1 NVARCHAR(20) 

DECLARE @var2 int = 100

DECLARE @var3 int

DECLARE @var4 int

SET @var3 = 1000

SELECT　@var1=count(*) from people

set @var4 = (SELECT count(*) FROM people)


SELECT @var1 as var1, @var2 as var2, @var3 as var3, @var4 as var4


--系統預設變數

SELECT @@VERSION  --取得MSSQL版本

SELECT @@IDENTITY --取得自動編號最近號碼 + 1

SELECT @@SERVERNAME --取得MSSQL伺服器名稱

SELECT @@ROWCOUNT --返回最近一次執行SQL記錄受影響


Use Accounting

DECLARE @AddressCityName nvarchar(50) = '台南'

INSERT INTO Addressinfo (AddressCityName) VALUES (@AddressCityName)

DECLARE @Name nvarchar(20) ='陳大3'

DECLARE @StudentNO char(6) = 'A0009'

DECLARE @AddressInfoID int = @@IDENTITY

INSERT INTO students (studnetNO,[Name],AddressInfoID) VALUES (@Name,@StudentNO,@AddressInfoID) 

SELECT @@ROWCOUNT

select * from Addressinfo

select * from students

 




留言

張貼留言

這個網誌中的熱門文章

簽字筆 奇異筆 光碟筆 油漆筆 麥克筆 差別在哪

-
資料來源 ~ 簽字筆 ~ 簽字筆是指專門用于簽字的筆，有水性簽字筆和油性簽字筆 水性簽字筆一般用於纸張上，如果用與白板或者物品上很容易被擦拭掉 油性簽字筆一般用於永久性的記號，油性簽字筆很难難擦拭，但可以用酒精等物清洗 。 ~ 奇異筆 ~ 可於在光碟片、金屬、玻璃、塑膠及壓克力等任何物品表面，且防水沒有臭味。 奇異墨水為速乾性墨水，隨寫隨乾、色彩鮮明永不退色，耐水性强，用途廣泛，是目前世界上廣泛使用之筆記文具。 ~ 光碟筆 ~ 是由廠商另外推出的專門寫在 cd 上的商品 其實油性奇異筆跟光碟筆是大同小異的 ~ 油漆筆 ~ 需要補色，做記號的時候，都會用到油漆筆，平常有些地方油漆刷不到，或者掉漆了，需要修補的時候，也長會使用油漆筆，油漆筆最大的好處就是防水不掉色。 ~ 麥克筆 ~ 書寫或繪畫專用的繪圖彩色筆，一般擁有堅硬筆頭 馬克筆的顏料具有易揮發性，用於一次性的快速繪圖常使用於設計物品、廣告標語、海報繪製或其他美術創作等等 現在的麥克筆有分為水性和油性的墨水 水性的墨水就類似彩色筆，是不含油精成份的內容物 油性的墨水因為含有油精成份，故味道比較刺激，而且較容易揮發。
閱讀完整內容

DC 常用指令

-
一、檢查網域狀況工具  ( 資料來源 ) 常用的 AD 診斷工具與常用指令 查詢使用者密碼到期日 net user <username> /domain ex：net user test01 /domain 網域控制站診斷工具 (dcdiag.exe) – DCDiag 詳細資訊。除了錯誤及警告的資訊外，也會呈現成功測試結果的資訊 DCDiag /v   只顯示錯誤訊息 DCDiag /q 用於確認 DNS 狀況 DCDiag /test:dns   執行基本 DNS 測試 (包括網路連線能力、DNS 用戶端設定、服務可用性及區域存在性)。 DCDiag /DnsBasic 執行與複寫相關的工作 (repadmin.exe) – Repadmin 顯示複寫摘要的資料收集 Repadmin /replsummary  顯示每個對象複製夥伴和狀態 repadmin /showrepl 顯示特定域控制器的複制夥伴 repadmin /showrepl <ServerName> 僅顯示複製錯誤 repadmin /showrepl /errorsonly 顯示複製隊列，如果您的環境較小，則通常為零 Repadmin /Queue 如何強制Active Directory複製 這將執行拉複製，這意味著它會將更新從DC2拉到DC1 repadmin /syncall dc1 /AeD 在DC1上進行更改並希望將這些更改複製到其他DC repadmin /syncall dc1 /APeD 將結果導出到文本文件 repadmin /replsummary> c：\it\replsummary.txt 查找DC上次備份的時間 Repadmin /showbackup * 顯示尚未應答的呼叫 repadmin /showoutcalls * 列出拓撲信息 repadmin /bridgeheads * /verbose 站點間拓撲生成器報告 repadmin /istg * /verbose 使用 Netdom.exe 工具 ...
閱讀完整內容

如何在 Excel 2010 中將數值設定成以「千」或「百萬」為單

-
圖片
報表中以「千」或「百萬」為單位的數值格式，千萬不可以將數值除以 1000 或 1000000 之後，再將數字填進報表中，而應該使用自【 訂儲存格格式 】的方式來處理。以下圖為例，我們來將數值設定成以「千」或「百萬」為單位的格式。 1. 選取儲存格 B2:F7，插人點置於儲存格 B2，點按【 常用 】索引標籤\【 數值 】群組\【 文字方塊啟動器 】。 摺疊此圖像展開此圖像 按一下以展開此圖片 2. 在【 儲存格格式 】對話方塊的【 類別 】清單中，點按「自訂」；在【 類型 】文字方塊中輸入「 #,##0 ,」(後面加上一個逗號)，按下【 確定 】。 摺疊此圖像展開此圖像 3. 報表中的數值，呈現出以「千」為單位的格式(僅是外觀改變，數值的本質並沒有改變)。 摺疊此圖像展開此圖像 按一下以展開此圖片 4. 如果要設定以百萬為單位的數值格式，只要在[類型]文字方塊中輸入「 #,##0,, 」(後面加上兩個逗號)，按下【 確定 】。 摺疊此圖像展開此圖像 5. 此時的數值格式，就是以百萬為單位來呈現。 摺疊此圖像展開此圖像 按一下以展開此圖片 6. 萬為單位的格式，如果覺得不夠精確，此時，可以在[類型]文字方塊中輸入「 #,##0.0,, 」，來為數值加上一位小數點。 摺疊此圖像展開此圖像 7. 下圖即為加上一位小數之後，最後呈現出來的結果。 摺疊此圖像展開此圖像 按一下以展開此圖片 source:  http://support.microsoft.com/kb/2689115/zh-tw
閱讀完整內容