SQL總結（四）編輯類

1、資料庫

建立資料庫語法：

CREATE DATABASE database_name

1）建立測試庫

CREATE DATABASE TestDB

2）使用庫

USE TestDB

3）刪除庫

DROP DATABASE TestDB

2、表

1）建立使用者資訊表，包括ID、姓名、年齡、專業ID、所在城市

CREATE TABLE Students(
ID int,
Name nvarchar(20),
Age int,
MajorID int,
City nvarchar(50)
)

2）更新表

• 新增欄位

語法：

ALTER TABLE table_name
ADD column_name datatype

學生資訊表增加年紀欄位

ALTER TABLE Students ADD Grade int

• 修改欄位

語法：

ALTER TABLE table_name
ALTER COLUMN column_name datatype

例項：將Grade欄位，int改為nvarchar

ALTER TABLE Students ALTER COLUMN Grade nvarchar(20)

• 刪除欄位

語法：

ALTER TABLE table_name 
DROP COLUMN column_name

例項：刪除學生資訊表中的Grade欄位

ALTER TABLE Students DROP COLUMN Grade

3）刪除表

刪除學生資訊表：

DROP TABLE Students

3、約束(Constraints)

CHECK 約束用於限制列中的值的範圍。
如果對單個列定義 CHECK 約束，那麼該列只允許特定的值。
如果對一個表定義 CHECK 約束，那麼此約束會在特定的列中對值進行限制。

主要有以下幾種約束：

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

1）不可空（NOT NULL）

約束強制列不接受 NULL 值

2）唯一約束（UNIQUE）

UNIQUE 約束唯一標識資料庫表中的每條記錄

UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證.

建立表時指定

CREATE TABLE Students(
ID int primary key not null,
Name nvarchar(50) UNIQUE,
Age int,
City nvarchar(50),
MajorID int
)

但是這樣UNIQUE約束名是按規則預設的，我們無法確認，不便於後續修改。

需在建立時指定約束名稱，當然可以同時多多列建立唯一約束。建議建立表後新增約束。

（1）建立學生表時，指定ID和Name唯一約束

CREATE TABLE Students(
ID int not null,
Name nvarchar(50),
Age int,
City nvarchar(50),
MajorID int,
CONSTRAINT UK_Students_ID_And_Name UNIQUE (ID,Name)
)

（2）為學生的姓名新增唯一約束

ALTER TABLE Students ADD CONSTRAINT UK_Students_Name UNIQUE(Name) 

（3）刪除學生姓名唯一約束

ALTER TABLE Students DROP CONSTRAINT UK_Students_Name

3）主鍵（PRIMARY KEY）

PRIMARY KEY 約束唯一標識資料庫表中的每條記錄。

規則：

• 主鍵必須包含唯一的值。
• 主鍵列不能包含 NULL 值。
• 每個表都應該有一個主鍵，並且每個表只能有一個主鍵。

其使用方法與UNIQUE一樣，這裡只說幾個簡單例子

（1）指定學生ID為主鍵

ALTER TABLE Students ADD CONSTRAINT PK_Students_ID PRIMARY KEY(ID)

（2）刪除學生ID主鍵

ALTER TABLE Students DROP CONSTRAINT PK_Students_ID

（3）建立學生ID和姓名的聯合主鍵

注意：在建立主鍵時，一定要保證列有NOT NULL約束

ALTER TABLE Students ADD CONSTRAINT PK_Students_ID_AND_Name PRIMARY KEY(ID,Name)

4）外來鍵（FOREIGN KEY）

一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY。

如：學生資訊表中的MajorID即是專業資訊表Majors的外來鍵。

（1）學生資訊表中的MajorID新增外來鍵約束

ALTER TABLE Students ADD CONSTRAINT FK_Students_MajorID FOREIGN KEY(MajorID) REFERENCES Majors(ID)

（2）刪除外來鍵約束

ALTER TABLE Students DROP CONSTRAINT FK_Students_MajorID

5）校驗約束（CHECK）

CHECK 約束用於限制列中的值的範圍。

（1) 設定學生的名稱長度不能少於2

ALTER TABLE Students ADD CONSTRAINT CK_Students_Name CHECK(LEN(Name)>=2)

（2）刪除該約束

ALTER TABLE Students DROP CONSTRAINT CK_Students_Name

（3）多列約束，對姓名和年齡新增約束

ALTER TABLE Students ADD CONSTRAINT CK_Students_Name_AND_Age CHECK(LEN(Name)>=2 AND Age>=18)

6）預設值（DEFAULT）

DEFAULT 約束用於向列中插入預設值。

此約束可以直接在建立表時執行。一般改動較小。

（1）建表時指定預設值，指定性別的預設值為0（0代表男，1代表女）

--學生資訊表
CREATE TABLE Students(
ID int primary key not null,
Name nvarchar(50),
Age int,
Sex bit DEFAULT(0),
City nvarchar(50),
MajorID int
)

（2）單獨語句增加Sex預設值約束

ALTER TABLE Students ADD CONSTRAINT DF_Students_Sex DEFAULT(1) FOR Sex

（3）刪除預設值約束

ALTER TABLE Students DROP CONSTRAINT DF_Students_Sex

（4）修改預設值約束，暫時沒找到

建議刪除後重建。

4、索引

索引類似於書記的目錄

索引可以加快查詢速度，當然過多的索引會影響資料的更新效率，建議索引建立在那些變化不大且經常查詢的列。

1）建立索引

CREATE INDEX 語句用於在表中建立索引。

CREATE UNIQUE INDEX是建立唯一索引，意味著其值唯一。

（1）在學生表的ID上建立索引

CREATE INDEX Index_Student_ID
ON Students (ID)

（2）在學生表的ID上建立唯一索引

CREATE UNIQUE INDEX Index_Student_ID
ON Students (ID)

（3）在學生ID降序建立索引

CREATE UNIQUE INDEX Index_Student_ID
ON Students (ID DESC)

（4）建立多個列聯合索引

CREATE INDEX Index_Student_ID_AND_NAME
ON Students(ID,Name)

2）刪除索引

語法：

DROP Index TABLE_NAME.INDEX_NAME

如：刪除學生表的ID索引

DROP Index Students.Index_Student_ID

5、檢視(VIEW)

概念: 是基於 SQL 語句的結果集的視覺化的表。

檢視包含行和列，就像一個真實的表。檢視中的欄位就是來自一個或多個資料庫中的真實的表中的欄位。我們可以向檢視新增 SQL 函式、WHERE 以及 JOIN 語句，我們也可以提交資料，就像這些來自於某個單一的表。

1）建立檢視，查詢所有成年的學生資訊

CREATE VIEW V_Adult_Students AS
SELECT ID,Name,Age,City FROM Students WHERE Age>=18

2）刪除檢視

DROP VIEW V_Adult_Students

3）更新檢視，暫時沒有直接方法，建議刪除重建。

對於記錄

1、插入

語法：

INSERT INTO 表名稱 VALUES (值1, 值2,....)

指定所要插入資料的列（推薦）：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

例項：增加學生資訊

INSERT INTO Students VALUES(2,'Lucy',17,'BeiJing','10')

建議使用，指定列。

INSERT INTO Students(ID,Name,Age,City) VALUES(1,'Tom',19,'BeiJing')

2、更新

Update 語句用於修改表中的資料。

語法：

UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

例項：

更新Lucy的年齡

UPDATE Students SET Age=18 WHERE Name='Lucy'

更新多列的值：

UPDATE Students SET Age=18,City='ShangHai' WHERE Name='Lucy'

3、刪除

DELETE 語句用於刪除表中的行。

語法：

DELETE FROM 表名稱 WHERE 列名稱 = 值

例項：

1）刪除Lucy的學生資訊

DELETE FROM Students WHERE Name='Lucy'

2）刪除所有學生資訊

DELETE FROM Students

轉載自：SQL總結（四）編輯類