作者： 18屆 KDB

日期： 2020-10-2

SQLServer相關操作

創建資料庫
修改資料庫
洗掉資料庫
查看資料庫資訊
創建資料表
修改資料表
洗掉資料表
查詢資料表
添加資料
創建視圖
修改視圖
洗掉視圖
創建索引
修改索引
洗掉索引
查詢索引
更改索引
創建資料庫用戶賬號

創建資料庫

CREATE DATABASE 資料庫名稱
[ON
[FILEGROUP 檔案組名稱]
(NAME = 資料庫檔案邏輯名,
 FILENAME = '路徑 + 資料庫檔案名',
 SIZE = 資料檔案初始大小,
 MAXSIZE = 資料檔案最大容量,
 FILEGROWTH = 資料檔案自動增長容量,)]
[LOG ON
(NAME = 日志檔案邏輯名稱,
 FILENAME = '路徑 + 日志檔案名',
 SIZE = 日志檔案初始大小,
 MAXSIZE = 日志檔案最大容量,
 FILEGROWTH = 日志檔案自動增長容量)]
[COLLATE 資料庫校驗方式名稱]
[FOR ATTACH]

例：新建一個資料庫Teach，資料檔案的邏輯名稱為Teach_Data，資料檔案存放在E盤根目錄下，檔案名稱為Teach_Data.mdf；日志檔案的邏輯名稱為Teach_Log，日志檔案的物理地存放在E盤根目錄下，檔案名為TeachData.ldf

CREATE DATAVASE Teach
ON
(NAME = Teach_Data,
 FILENAME = 'E:\TeachData.mdf',
 SIZE = 10,
 MAXSIZE = 500,
 FILEGROWTH = 10)
LOG ON 
(NAME = Teach_Log,
FILENAME = 'E:\TeachData.ldf',
SIZE = 5,
MAXSIZE = 500,
FILEGROWTH = 5)

修改資料庫

ALTER DATABASE 資料庫名稱
ADD FILE(具體檔案格式) [,...n]
[TO FILEGROUP 檔案組名]
[ADD LOG FILE(具體檔案格式) [,...n]
|REMOVE FILE 檔案邏輯名稱
|MODIFY FILE(具體檔案格式)
|ADD FILEGROUP 檔案組名
|REMOVE FILEGROUP 檔案組名
|MODIFY FILEGROUP 檔案組名
{READ_ONLY|READ_WRITE,
 |DEFAULT,
 |NAME = 新建檔案組名}
]

例：修改Teach資料庫中的Teach_Data檔案增容方式為一次增加20MB

ALTER DATABASE Teach
MODIFY FILE
(NAME = Teach_Data,
 FILEGROWTH = 20)

例：用SQL命令修改資料庫Teach，增加一個次要資料檔案，邏輯名稱為Teach_Datanew，存放在E盤的根目錄下，檔案名為Teach_Datanew.ndf

ALTER DATABASE Teach
ADD FILE(
    NAME = Teach_Datanew,
    FILENAME = 'E:\Teach_Datanew.ndf',
   SIZE = 100,
   MAXSIZE = 200,
   FILEGROWTH = 10)

例：使用SQL命令，從Teah資料庫中洗掉次要資料檔案

ALTER DATABASE Teach
REMOVE FILE Teach_Datanew

洗掉資料庫

DROP DATABASE 資料庫名稱[,...n]

例：洗掉資料庫Teach

DROP DATABASE Teach

查看資料庫資訊

Sp_helpdb[[@dbname=]'name']
Sp_helpfile[[@filename=]'name']
Sp_helpfilegroup[[@filegroupname=]'name']

例：用系統存盤程序顯示資料庫結構

EXEC Sp_helpdb AdventureWorks2012

例：用系統存盤程序顯示檔案資訊

EXEC Sp_helpfile Address

例：用系統存盤程序顯示檔案組資訊

USE AdventureWork2012
EXEC Sp_helpfilegroup

創建資料表

CREATE TABLE <表名>
(<列定義>[{, <列定義>|<表約束>}])

例：建立一個學生表S

CREATE TABLE S
(SNo CHAR(6),
 SN VARCHAR(10),
 Sex NCHAR(1) DEFAULT '男',
 Age INT,
 Dept NVARCHAR(20))

修改資料表

ALTER TABLE <表名>
ADD <列定義> | <完整性約束定義>

ALTER TABLE <表名>
ALTER COLUMN <列名> <資料型別>
[NULL | NOT NULL]

ALTER TABLE <表明>
DROP CONSTERAINT <約束名>

例：在S表中增加一個班號列和住址列

ALTER TABLE S
ADD
Class_No VARCHAR(6),
Address NVARCHAR(20)

例：在SC表中增加完整性約束定義，使Score在0~100之間

ALTER TABLE SC
ADD 
CONSTRAINT Score_Chk CHECK(Score BETWEEN 0 AND 100)

例：把S表中的SN列加寬到12個字符

ALTER TABLE S
ALTER COLUMN
SN NVARCHAR(12)

例：洗掉S表中的主鍵

ALTER TABLE S
DROP CONSTRAINT S_Prim

洗掉資料表

DROP TABLE <表名>

查詢資料表

SELECT [ALL|DISTINCT][TOP N [PERCENT][WITH TIES]]
<列名> [AS 別名1][{, <列名>[AS 別名2]}]
FROM <表名> [[AS]表別名]
[WHERE <檢測條件>]
[GROUP BY <列名1>[HAVING <條件運算式>]]
[ORDER BY <列名2>[ASC|DESC]]

例：查詢學生的全部資訊

SELECT * FROM S

例：查詢選修了課程的學生學號

SELECT DISTINCT SNo FROM SC

例：查詢"劉偉"老師所講授的課程，要求列出教師號、教師姓名和課程號

SELECT T.TNo, TN, CNo 
FROM T, TC
WHERE (T.TNo = TC.TNo) AND (TN = '劉偉')

例：查詢所有選課學生的學號、姓名、選課名稱及成績

SELECT S.SNo, SN, CN, Sore
FROM S, C, SC
WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo

例：查詢所有學生的學號、姓名、選課名稱及成績

SELECT S.SNo, SN, CN, Score
FROM S
LEFT OUTER JOIN SC
ON S.SNo = SC.SNo
LEFT OUTER JOIN C
ON C.CNo = SC.CNo

例：對學生表S和課程表C進行交叉查詢

SELECT * FROM S CROSS JOIN C

例：查詢與"劉偉"老師職稱相同的教師

SELECT TNo, TN FROM T 
WHERE Prof = ( SELECT Prof
               FROM T
               WHERE TN = '劉偉')

例：用含有EXISTS的陳述句查詢講授課程號為C5的教師姓名

SELECT TN
FROM T
WHERE EXISTS ( SELECT * 
               FROM TC
               WHERE TNo = T.TNo AND CNo = 'C5')

例：查詢沒有講授課程號為C5的教師姓名

SELECT TN
FROM T
WHERE (NOT EXISTS (SELECT * FROM TC 
                   WHERE TNo = T.TNo = T.TNo AND CNo = 'C5'))

例：從SC資料表中查詢出學號為S1同學的學號和總分，再從SC資料表中查詢出學號為S5的同學的學號和總分，然后將兩個查詢結果合并成一個結果集

SELECT SNo AS 學號, SUM(Score) AS 總分
FROM SC
WHERE (SNo = 'S1')
GROUP BY SNo
UNION
SELECT SNo AS 學號, SUM(Score) AS 總分
FROM SC
WHERE (SNo = 'S5')
GROUP BY SNo

例：從SC資料表中查詢出所有同學的學號和總分，并將查詢結果存放到一個新的資料表Cal_Table中

SELECT SNo AS 學號, SUM(Score) AS 總分
INTO Cal_Table
FROM SC
GROUP BY SNo

添加資料

INSERT INTO <表名>[(<列名1>[, <列名2>])] 子查詢

例：在SC表中添加一條選課記錄(‘S7’, ‘C1’)

INSERT INTO SC(SNO， CNo) VALUES('S7', 'C1')

例：求出各系主任教師的平均工資，把結果存放在新AvgSal中

CREATE TABLE AvgSal
(Department VARCHAR(20),
 Average SMALLINT)

更新資料

UPDATE <表名> SET <列名> = <運算式> [, <列名> = <運算式>]...
[WHERE <條件>]

例：把劉偉老師轉到資訊系

UPDATE T
SET Dept = '資訊'
WHERE SN = '劉偉'

例：將所有學生的年齡增加1歲

UPDATE S SET Age = Age + 1

例：把教師表中工資小于或等于1000元的講師的工資提高20%

UPDATE T SET Sal = 1.2 * Sal
Where (Prof = '講師')
AND Sal <= 1000

創建視圖

CREATE VIEW view_name[(column[,...n])] AS select_statement

例：創建一個計算機系教師情況的視圖Sub_T

CREATE VIEW Sub_T
AS SELECT TNo, TN, Prof
FROM T WHERE Dept = '計算機'

例：創建一學生情況視圖S_SC_C

CREATE VIEW S_SC_C(SNo, SN, CN, Score)
AS SELECT S.SNo, SN, CN, Scroe
FROM S, C, SC WHERE S.SNo = SC.SNo
AND SC.CNo = C.CNo

例：創建一個學生平均成績的視圖S_Avg

CREATE VIEW S_Avg(SNo, Avg)
AS SELECT SNo, Avg(Score) FROM SC GROUP BY SNo

修改視圖

ALTER VIEW <視圖名>[(<視圖串列>)] AS <子查詢>

例：修改學生情況視圖S_SC_C

ALTER VIEW S_SC_C(SN, CN, Score)
AS SELECT SN, CN, Score
FROM S, C, SC
WHERE S.SNo = SC.SNo AND SC.CNo = C.CNo

洗掉視圖

DROP VIEW <視圖名>

例：洗掉計算機系教師情況的視圖Sub_T

DROP VIEW Sub_T

創建索引

CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX index_name
ON table_or_view_name(column_name[ASC|DESC][,...n])
[WITH <index_option>[,...n]]
[ON {filegroup_name|"default"}]

例：為表SC在SNo和CNo上建立唯一索引

CREATE UNIQUE INDEX SCI ON SC(SNo, CNo)

例：為教師表T在TN上建立聚集索引

CREATE CLUSTER INDEX TI ON T(TN)

修改索引

ALTER INDEX {index_name | ALL}
ON table_or_view_name
{REBUILD
  [[PARTITION = ALL]
    [WITH(<rebuild_index_option[,...n]>)]
    |[PARTITION = partition_number
      [WITH(<single_partition_rebuild_index_option>
       [,...n])
    ]]]
 |DISABLE
 |REORGANIZE
  [PARTITION = partition_number]
  [WITH(LOB_COMPACTION = {ON | OFF})]
 |SET(<set_index_option>[,...n])
}[;]

REBUILD:洗掉索引并且重新生成索引
PARTITION:指定只重新生成或重新組織索引的一個磁區
DISABLE:將索引標記為禁用，從而不能由資料庫引擎使用
REORGANIZE:重新組織索引

洗掉索引

DROP INDEX <table or view name><index name>
DROP INDEX <index name> ON <table or view name>

查詢索引

Sp_helpindex [@objname=]'name'

例：查詢表SC的索引

EXEC Sp_helpindex SC

更改索引

Sp_rename'資料表名.原索引名','新索引名'

例：更改T表中的索引T1名稱為T_Index

EXEC Sp_rename 'T.T1','T_Index'

創建資料庫用戶賬號

CREATE USER user_name
[{FOR|FROM}
 {LOGIN login_name
  |CERTIFICATE cert_name
  |ASYMMETRIC KEY asym_key_name}
 |WITHOUT LOGIN]

規則

例：創建學生年齡規則

CREATE RULE age_rule
AS @age >= 18 AND @age <= 50

系結規則

Sp_bindrule[@rulename=]'rule'.[@objname=]'object_name'[,'futureonly']

例：系結規則age_rule到S表的欄位Age

EXEC Sp_bindrule'age_rule'.'S.Age'

解除系結

Sp_unbindrule[@objname=]'object_name'[,'futureonly']

例：解除已系結到S表的欄位Age的規則age_rule

EXEC Sp_unbindrule 'S.Age'

洗掉命令

DROP RULE{rule_name}[,...n]

例：洗掉age_rule規則

DROP RULE age_rule

默認創建

CREATE DEFAULT default_name AS constant_expression

例：創建生日日期默認birthday_defa

CREATE DEFAULT birthday_defa AS '1987-1-1'

查看默認
例查看默認birthd_defa

EXEC Sp_helptext birthday_defa

系結與解除默認

Sp_bindefault[@defname=]'default'
[@objname=]'object_name'[,'futureonly']

Sp_unbindefault[@objname=]'object_name'[,'futureonly']

例：系結默認birthd_defa到資料表S的Birthday列

EXEC Sp_bindefault 'birthday_defa,' 'S.Birthday'

例：解除默認birthd_defa與表S的Birthday列的系結

EXEC Sp_unbindefault 'S.Birthday'

洗掉默認

DROP DEFAULT{default_name}[,..n]

例：洗掉學生生日默認birthd_defa

DROP DEFAULT birthday_defa

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/151968.html

標籤：其他

上一篇：mysql學習知識點大全

下一篇：小白快速建站