SQL server學習筆記（一）資料庫的基本知識、基本操作（分離、離線、收縮、備份、還原、附加）和基本文法

標籤：檔案中   實值型別   等於   系統   磁碟   外鍵約束   uid   邏輯   區別   

在軟體測試中，資料庫是必備知識，假期閑裡偷忙，整理了一點學習筆記，共同探討。

閱讀目錄

基本知識

• 資料庫發展史
• 資料庫名詞
• SQL組成

基本操作

• 登入資料庫操作
• 資料庫遠端連線操作
• 資料庫分離操作
• 資料庫離線、聯機操作
• 資料庫收縮操作
• Database Backup、還原作業
• 資料庫使用權限設定

基本文法

• 建立資料庫
• 建立表
• 增
• 刪
• 改
• 查
• 添加約束
• 資料類型

基本知識

資料庫到底是有什麼作用？看應該就明白了。

就是應用程式發送請求操作時，到伺服器資料庫中查詢資料，然後再返回給應用程式。

資料庫起到的作用是儲存資料、檢索資料、產生新的資料，相對應的要求是：統一、安全、效能。

資料庫可以儲存大量資料，方便檢索和訪問，能保持資料資訊的一致、完整，共用和安全，通過組合分析，產生新的有用資訊。

資料庫發展史

萬物生長，如今使用的資料庫也不是一蹴而就，也是慢慢發展而來。可分為四個階段：

①萌芽階段—檔案系統，只是使用磁碟檔案來儲存資料

②初級階段—第一代資料庫Access，是網狀模型和層次模型資料庫

③中級階段—第二代資料庫SQL server、Mysql，是關係型資料庫和結構化查詢語言 (SQL)

④進階階段—新一代資料庫Oracle，是“關係-對象”型資料庫

當下最流行的資料庫是Oracle、SQL server、DB2

Oracle是“關係-對象”型資料庫，它的特點是產品免費、服務收費

SQL server是關係型資料庫，多版本，易用性好

DB2支援多作業系統、多種類型的硬體和裝置

資料庫名詞

DBA-----資料庫管理員

DB-------資料庫

DBMS--資料庫管理系統

系統資料庫：master、model、tempdb、msdb，不能隨意更改

資料檔案為.mdf或.ndf

記錄檔為.ldf

資料的預設路徑為：C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

SQL組成DML（資料操作語言）

　　——查詢、插入、刪除和修改資料庫中的資料；

　　——SELECT、INSERT、 UPDATE 、DELETE等；

DCL（資料控制語言）

　　——用來控制存取許可、存取許可權等；

　　——GRANT、REVOKE 等；

DDL（資料定義語言 (Data Definition Language)）

　　——用來建立資料庫、資料庫物件和定義其列

　　——CREATE TABLE 、DROP TABLE 等

TCL  （事務控制語句）

        變數說明、流程式控制制、功能函數

　　——定義變數、判斷、分支、迴圈結構等

　　——日期函數、數學函數、字元函數、系統函數等

資料庫基本操作登入資料庫

在安裝成功後，在開始菜單中會看到如下所示檔案夾：

點擊SQL server management studio，即可出現如下登入介面：

資料庫遠端連線設定

第一步，開啟外圍配置器設定

第二步，進行相應設定

第三步，進行串連操作

資料庫的分離操作

分離是指預設情況下資料庫在聯機狀態下我們不能對資料庫檔案進行任何複製刪除等操作，如果將資料庫分離的話就可以對資料檔案進行複製、剪下、刪除等操作了。一般想直接備份資料檔案，就先分離資料庫，之後把資料檔案複製到別的地方，再把資料檔案附加回去就可以了。

操作如下所示：

先將資料庫進行分離操作：

完成上述操作，資料庫便分離成功，然後就可以做相應的複製等操作了。

分離也可以使用代碼實現，如下

sp_detach_db demo

再將分離的資料庫附加到工作面板：

附加也可以使用代碼實現，如下：

sp_attach_db demo

資料庫的離線、聯機操作

離線與聯機是相對操作，先要有離線才能執行聯機操作，與分離不同的是，離線後的資料庫只是不能被訪問了，但還在工作面板中，但分離後的資料庫，是不存在工作面板中的。當要再想使用該資料庫，離線狀態的資料庫只需要聯機即可，分離的資料庫需要附加才可以。

離線操作如下：

離線也可使用代碼實現，代碼如下：

alter database demo set offline

聯機操作如下：

聯機也可使用代碼實現，代碼如下：

alter database demo set online

資料庫收縮操作

收縮是把資料庫以及日誌的記憶體收縮

操作如下

收縮資料庫操作：

收縮記錄檔操作：

收縮檔案也可使用如下命令：

DBCC ShrinkFile(‘資料庫名‘,  targetsize);            /* 收縮資料庫檔案 */DBCC ShrinkFile(‘資料庫名_log‘,  targetsize);        /* 收縮記錄檔 */

需要注意的是：

Targetsize：單位為兆，必須為整數，DBCC SHRINKFILE 嘗試將檔案收縮到指定大小。

DBCC SHRINKFILE 不會將檔案收縮到小於“實際使用的空間”大小，例如“分配空間”為10M，“實際使用空間”為6M，當制定targetsize為1時，則將該檔案收縮到6M，不會將檔案收縮到1M。

收縮資料庫可使用如下命令：

DBCC SHRINKDATABASE(資料庫名，百分比)

此處的百分比：即“收縮後檔案中的最大可用空間”，取值範圍“大於等於0， 小於100%”，實際使用中設為0即可。

資料庫的備份、還原作業

備份操作是為了保證資料的完整性以及避免異常操作而導致資料無法挽回的操作，個人覺得是非常非常有必要的。

資料庫的備份分為如下幾種：

完全備份： 備份全部選中的檔案夾，並不依賴檔案的存檔屬性來確定備份那些檔案。（在備份過程中，任何現有的標記都被清除，每個檔案都被標記為已備份，換言之，清除存檔屬性）。完全備份也叫完整備份。

差異備份： 差異備份是針對完全備份：備份上一次的完全備份後發生變化的所有檔案。（差異備份過程中，只備份有標記的那些選中的檔案和檔案夾。它不清除標記，即：備份後不標記為已備份檔案，換言之，不清除存檔屬性）。

交易記錄備份： 在特定交易記錄備份之前執行的完整Database Backup和上次差異備份（如果有）。在完整Database Backup之後執行的所有交易記錄備份或在特定交易記錄備份之前執行的差異備份（如果您還原了差異備份）。如果你設定了復原模式為【簡單】，你將無法使用【交易記錄】備份。SQL Server 2000 和 SQL Server 2005： 建立交易記錄備份，您必須使用完整恢複或大容量日誌記錄恢複模型。

備份操作如下，此處選擇的是完整備份：

還原資料庫操作

使用權限設定

使用權限設定就是設定登入名稱和使用者名稱操作。

登入名稱與使用者名稱的區別：

1.登入名稱是指可以使用建立的登入名稱和密碼登入資料庫這個程式軟體，但不能開啟或展開使用者自己建立的資料庫；

2.使用者名稱是在登入名稱基礎上，為使用者自己建立的資料庫新加的可查看使用者，但要進行增查改刪操作仍然需要為該使用者添加使用許可權。使用者名稱可以與登入名稱相同，也可以不同。一個資料庫可以擁有多個使用者，一個使用者也可以訪問多個資料庫。

先以sa為登入名稱登入做如下操作：

建立登入名稱：

使用剛建立的登入名稱登入，去使用由sa使用者建立的資料庫，發現不可用：

建立使用者，分配許可權

添加好後就可以對sa建立的資料庫進行操作了

基本文法

說了這麼多，再來看資料庫文法怎麼操作，資料庫四大操作，就是增刪改查。在做這些操作的時候，首先得要有個資料庫，有張表，這樣才可以操作。把問題簡單化，想成平時的檔案室。資料庫就是檔案室，可以存放很多檔案，表就是檔案袋中的檔案。

建立資料庫

建資料庫和建表操作可手動建立，也可以使用代碼建立，先看手動建立：

手動建立資料庫操作如下：

完成上述兩步就已經建立好一個資料庫了。

在使用代碼操作的時候，需要先做如下所示操作，並串連：

使用代碼建立資料庫，實現如下：

create database demouse demo

還有一種建立資料庫文法，就是指定資料庫檔案參數和記錄檔參數，文法如下：

create  database  資料庫名on [primary] (  <資料檔案參數> [，…n]  [<檔案組參數>] ) [log on]  (  <記錄檔參數> [，…n] )

舉例如下：

CREATE DATABASE stuDB   ON  PRIMARY  --預設就屬於PRIMARY主檔案組，可省略( NAME=‘stuDB_data‘,  --主要資料檔案的邏輯名 FILENAME=‘D:\project\stuDB_data.mdf‘,  --主要資料檔案的實體名稱 SIZE=5mb,  --主要資料檔案初始大小 MAXSIZE=100mb,  --主要資料檔案增長的最大值 FILEGROWTH=15%   --主要資料檔案的增長率)LOG ON(  NAME=‘stuDB_log‘,  FILENAME=‘D:\project\stuDB_log.ldf‘,  SIZE=2mb,  FILEGROWTH=1MB)GO 

若一次性要建立多個資料庫或多個記錄檔，在小括弧外加個,號即可

建立表

手動建立表，操作如下：

 

儲存輸入表名即可

使用代碼建立表如下：

 

代碼為：

create table demo(nname nchar(10),age tinyint,sex int)

資料庫和表已經建立好了

首先來看增

插入一條資料：

方法一：安照列名依次給出資料值，不能交換順序，資料類型不能出錯

insert into demovalues(‘zhang‘,25,1)

方法二：

insert into demo(nname,age,sex)values(‘san‘,18,0)

方法三：交換順序，數值按照列名給就行

insert into demo(nname,sex,age)values(‘san‘,1,22)

方法四：沒給數值的列顯示null

insert into demo(nname)values(‘li‘)

插入多條資料：使用select，union是指串連的意思

insert into demo(nname,age,sex)若不寫列名，則預設插入所有列資料select ‘wang‘,28,0 unionselect ‘wu‘,30,1

插入一列

alter table demoadd sex int

複製demo表中資料到demo1表中：

insert into demo1(nname,nage,asex)select nname,age,sexfrom demo

注意事項

1:每次插入一行資料，不可能只插入半行或者幾列資料，因此，插入的資料是否有效將按照整行的完整性的要求來檢驗；

2:每個資料值的資料類型、精度和小數位元必須與相應的列匹配；

3:不能為識別欄位指定值，因為它的數字是自動成長的；

4:如果在設計表的時候就指定了某列不允許為空白，則必須插入資料；

5:插入的資料項目，要求符合檢查約束的要求

6:具有預設值的列，可以使用default（預設）關鍵字來代替插入的數值

其次來看刪

刪除某一行：

delete from demowhere nname=‘li‘

刪除某一列：

alter table demodrop column sex

刪除整張表：

delete from demo --不加where條件就是刪除整個表

清空整張表：

truncate table demo

注意：刪除整張表操作和清空整張表操作對比

　　刪除是刪除了所有，包括表結構

　　清空只是清空了表中資料，但表結構還在，清空速度快些

刪除資料庫

drop database new

還可以先查詢是否有該資料庫，再做刪除操作，代碼如下：

if exists(select * from sys.databases where name=‘new‘)drop database new

做了一個if判斷，sys.databases是指整個系統資料庫，刪除資料庫的前提條件是，要刪除的這個資料庫沒有被使用

然後來看改

改某一列資料

update demosetage=‘28‘where nname=‘zhang‘

set是設定要改的資料

改多列資料：

update demosetnname=‘zhao‘,age=‘13‘,sex=‘0‘where nname=‘li‘

最後就是查詢

查詢表中所有資料，*代表所有列：

select * from demo

查詢表中某一行所有資料：

select * from demowhere nname=‘li‘

查詢表中某一行某一列的資料：

select agefrom demowhere nname=‘li‘

查詢出來的結果列用as取別名：

select age as ‘年齡‘from demowhere nname=‘li‘

查詢出來的結果列用=取別名：

select wname = nnamefrom demo

注意：wname是新列名，nname是表中已存在的列名

添加約束

約束目的：保證資料的完整性和有效性

約束分為如下幾種：

1.主鍵約束：唯一非空（特點） primary key PK_

　　一個表裡面只能存在一個主鍵

　　允許存在把兩列作為一個主鍵

2.預設約束：預設值 default key DK_

　　當不填資料時預設採用設定好的值

3.唯一約束：資料不重複，允許一行資料為空白 Unique Key 唯一允許一個為空白

4.檢查約束：用來檢查填入的資料是否滿足要求 Check Key

5.非空約束：不可為空 not null

6.外鍵約束：外鍵指的是，表A中的id為主鍵，但在表B中id不是主鍵，表A的id就是表B的id外鍵。

外鍵文法：

alter table 表名add constraint FK_欄位名--"FK"為外鍵的縮寫foreign key (欄位名) references 關聯的表名(關聯的欄位名) --注意‘關聯的表名‘和‘關聯的欄位名‘

添加約束有三種方式，分別是建表中（在列名中添加和不在列名中添加），建表後

方法一：建表中在列名中添約束，文法為：列名 資料類型 約束條件，代碼如下：

create table dome5( sid  smallint primary key, sname nchar(10) not null default ‘zhangsan‘, saddre nvarchar(20), sage tinyint unique key, semail  char(20) check (semail like ‘%@%.%‘), createtime datetime  default getdate())

方法二：建表中不在列名中添加約束，文法為：constraint    約束名    約束文法，代碼如下：

create table stdent2(    sid int,    sname nchar(15) not null default ‘zhangsan‘,    saddress nvarchar(30),    sage tinyint,    semail char(20),    stime datetime default getdate(),    constraint PK_sid primary key(sid),    constraint CK_sage check (sage>10),    constraint CK_semail check (semail like ‘%@%.%‘))

需要注意的是：預設約束只能在 列名  資料類型   後面添加，意思就是預設約束要使用在方法一添加

方法三：建完表後再添加約束，代碼如下：

alter table stuadd constraint ck_ssex check (ssex=0 or ssex=1)

alter是修改表結構

資料類型

資料類型如下表所示：

分類	備忘和說明	資料類型	說明
位元據類型	儲存非字元和文本的資料	Image	可用來儲存映像
文本資料類型	字元資料包括任意字母、符號或數字字元的組合	Char	固定長度的非 Unicode 字元資料，效率高
		Varchar	可變長度非 Unicode 資料，節省空間的
		Nchar	固定長度的 Unicode 資料
		Nvarchar	可變長度 Unicode 資料
		Text	儲存長文本資訊
		Ntext	儲存可變長度的長文本
日期和時間	日期和時間在單引號內輸入	Datetime	日期和時間
數字資料	該資料僅包含數字，包括正數、負數以及分數	Bigint、int、smallint、tinyint	整數
		float、real	數字
貨幣資料類型	用於十進位貨幣值	Money
數實值型別	例：stuid numeric(18,0)，代表18位元字，小數位元為0，屬於浮點類型	numeric (18,0)
識別欄位	identity（起始值，遞增量）	identity(1,1)	自動遞增
Bit資料類型	表示是/否的資料	Bit	儲存布林值資料型別

 

本文僅代表作者觀點，系作者@溫一壺清酒發表。轉載請註明出處：http://www.cnblogs.com/hong-fithing/

SQL server學習筆記（一）資料庫的基本知識、基本操作（分離、離線、收縮、備份、還原、附加）和基本文法