SQL資料庫的基礎知識及使用！

• 2019 年 10 月 31 日
• 筆記

作者 | 寂寞黑鯊

來源 | CSDN

1. 約束作用

資料庫的約束：避免垃圾數據的產生，禁止非法的數據加入資料庫中，保證資料庫的結構良好

資料庫中的數據在C#中就是一個對象，一條記錄存儲的是一個對象的屬性（例如：姓名，學號，班級等屬性），存儲到資料庫中就是一列列的欄位

2. 約束的類型

實體完整性約束：保證存儲的記錄在資料庫中唯一。常見約束類型：

• a.主鍵約束約束（primary key）
• b.唯一鍵約束（unique）等

域完整性約束：對欄位進行約束。常見約束類型有：

• a.數據類型約束（int或者char（2））等約束）
• b.非空約束（not null）
• c.默認約束（default）
• d.檢查約束（check）等

引用完整性約束：保證資料庫中的多張數據表數據的一致性和完整性。常見約束類型：外鍵約束（foreign key）

3. 外鍵約束的使用

外鍵約束的使用：當一張表依賴於另外一張表的某個或某些欄位時使用，創建外鍵約束時，先建被引用的表（主鍵表），再建有外鍵約束的表（外鍵表）

刪除表中的數據時，如果當前表（主鍵表）被其他表引用，刪除主鍵表中的數據時有兩種方法：第一種：則應該先刪除引用的表（外鍵表）中的數據，再刪當前表（主鍵表）中的數據，例如：A表（主鍵表）中的a1欄位被B表（外鍵表）中的a1欄位引用，這時如果要刪除A表中的a1時，要先刪除B中的a1再刪A中的a1；第二種：通過級聯的方式刪除，但不提倡使用。

建議：數據表中的主鍵值不能隨便修改。

4. 創建資料庫、數據表和表的約束的示例程式碼

--指向當前要使用的資料庫  use master  go  --判斷當前資料庫是否存在  if exists (select * from sysdatabases where name='SMDB')  drop database SMDB --刪除資料庫  go  --創建資料庫  create database SMDB  on primary  (  --資料庫文件的邏輯名      name='SMDB_data',      --資料庫物理文件名（絕對路徑）      filename='D:DBSMDB_data.mdf',      --資料庫文件初始大小      size=10MB,      --數據文件增長量      filegrowth=1MB  )  --創建日誌文件  log on  (      name='SMDB_log',      filename='D:DBSMDB_log.ldf',      size=2MB,      filegrowth=1MB  )  go  --創建學員資訊數據表  use SMDB  go  if exists (select * from sysobjects where name='Students')  drop table Students  go  create table Students  (      StudentId int identity(100000,1) ,      StudentName varchar(20) not null,      Gender char(2)  not null,      Birthday smalldatetime  not null,      StudentIdNo numeric(18,0) not null,--身份證號      StuImage text null,--學員照片      Age int not null,      PhoneNumber varchar(50),      StudentAddress varchar(500),      ClassId int not null  --班級外鍵  )  go  --創建班級表  if exists(select * from sysobjects where name='StudentClass')  drop table StudentClass  go  create table StudentClass  (  ClassId int primary key,      ClassName varchar(20) not null  )  go  --創建成績表  if exists(select * from sysobjects where name='ScoreList')  drop table ScoreList  go  create table ScoreList  (      Id int identity(1,1) primary key,      StudentId int not null,      CSharp int null,      SQLServerDB int null,      UpdateTime smalldatetime not null  )  go  --創建管理員用戶表  if exists(select * from sysobjects where name='Admins')  drop table Admins  create table Admins  (  LoginId int identity(1000,1) primary key,      LoginPwd varchar(20) not null,      AdminName varchar(20) not null  )  go  --創建數據表的各種約束  use SMDB  go  --創建「主鍵」約束primary key  if exists(select * from sysobjects where name='pk_StudentId')  alter table Students drop constraint pk_StudentId      alter table Students  add constraint pk_StudentId primary key (StudentId)      --創建檢查約束check  if exists(select * from sysobjects where name='ck_Age')  alter table Students drop constraint ck_Age  alter table Students  add constraint ck_Age check (Age between 18 and 35)      --創建唯一約束unique  if exists(select * from sysobjects where name='uq_StudentIdNo')  alter table Students drop constraint uq_StudentIdNo  alter table Students  add constraint uq_StudentIdNo unique (StudentIdNo)          --創建身份證的長度檢查約束  if exists(select * from sysobjects where name='ck_StudentIdNo')  alter table Students drop constraint ck_StudentIdNo  alter table Students  add constraint ck_StudentIdNo check (len(StudentIdNo)=18)      --創建默認約束  if exists(select * from sysobjects where name='df_StudentAddress')  alter table Students drop constraint df_StudentAddress  alter table Students  add constraint df_StudentAddress default ('地址不詳' ) for StudentAddress      if exists(select * from sysobjects where name='df_UpdateTime')  alter table ScoreList drop constraint df_UpdateTime  alter table ScoreList  add constraint df_UpdateTime default (getdate() ) for UpdateTime          --創建外鍵約束  if exists(select * from sysobjects where name='fk_classId')  alter table Students drop constraint fk_classId  alter table Students  add constraint fk_classId foreign key (ClassId) references StudentClass(ClassId)      if exists(select * from sysobjects where name='fk_StudentId')  alter table ScoreList drop constraint fk_StudentId  alter table ScoreList  add constraint fk_StudentId foreign key(StudentId) references Students(StudentId)

5. 插入測試數據

一些使用經驗：

• 插入數據時，先插主鍵表再插外鍵表，否則會出錯
• 先把表結構和約束創建完再添加數據，這樣可以有效的避免出錯

use SMDB  go      --插入班級數據  insert into StudentClass(ClassId,ClassName) values(1,'網監1區')  insert into StudentClass(ClassId,ClassName) values(2,'網監2區')  insert into StudentClass(ClassId,ClassName) values(3,'資訊安全')  insert into StudentClass(ClassId,ClassName) values(4,'電腦科學與技術')      --插入學員資訊  insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)           values('蘇家輝','男','1989-08-07',22,120223198908071111,'022-22222222','天津市南開區紅磡公寓5-5-102',1),   ('小美女','女','1989-05-06',22,120223198905062426,'022-33333333','天津市河北區王串場58號',2),           ('小帥哥','男','1990-02-07',21,120223199002078915,'022-44444444','天津市紅橋區丁字沽曙光路79號',4)        --插入成績資訊  insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100000,60,78),(100001,55,88),(100002,90,58)      --插入管理員資訊  insert into Admins (LoginPwd,AdminName) values(123456,'楊家貴')  insert into Admins (LoginPwd,AdminName) values(123456,'陳皮')  insert into Admins (LoginPwd,AdminName) values(123456,'劉懿蟬')

7. 因為添加約束出現的問題解決

若後期，想要在表中添加約束，但加不進去，這是因為一旦創建了約束，資料庫系統就要對執行約束，因為已經存在了垃圾數據，執行約束沒有通過，所以添加不成功。

解決這種問題的辦法：找到垃圾數據然後對垃圾數據進行修改或者刪除沒用的數據，然後再添加約束

找到垃圾數據的辦法：子查詢 not in進行查找

8.identity的使用

如果資料庫已經創建並且已經做了部署，但是我們希望identity從頭開始，可以使用truncate，但是使用前有個前提那就是必須沒有引用關係，如果有引用關係，先刪除引用關係

9. 資料庫查詢及對NULL的處理

以後在資料庫中添加數據時，盡量不適用null空值，因為在程式中容易出錯，可以使用空字元串代替

在數據表中查找null值：使用is null方法

對於null 值，可以把null替換掉，或者把null數據插入一個臨時表中，在臨時表中做數據檢索

10. 簡單的幾個查詢

• select top 3 from 表 ，查詢表 中的前三條記錄（在程式的分頁中使用）
• select top 20 percent from 表 ，查詢表中20%的數據並顯示
• order by 排序asc升序 desc 降序
• select 名字，班級名稱 from 學生表 inner join 班級表 on 班級表.classid=學生表 .classid inner join …on…，需要特別 注意的是：使用連接查詢時，相同的欄位在兩個表中出現，需要在前面加上表明，例如：班級表.classid=學生表 .classid

常用模糊查詢：

• like配合%通配符使用，示例：楊%——查詢楊開頭的所有數據
• between….and…，示例：between 70 and 100——查詢70到100的數據

常用函數：

• AVG——求平均值；max——求最大值；min——求最小值；sum——求和；count（*）——求記錄數目