維護SQL Server資料庫的一些常用SQL

維護SQL Server資料庫的一些常用SQL

1.如何建立資料庫

CREATE DATABASE student

2.如何删除資料庫

DROP DATABASE student

3.如何備份資料庫到磁盤檔案

BACKUP DATABASE student to disk='c:/1234.bak'

4.如何從磁盤檔案還原資料庫

RESTORE DATABASE studnet FROM DISK = 'c:/1234.bak'

5.怎樣建立表？

CREATE TABLE Students (

        ID int IDENTITY ( 1, 1), --自增字段,基數1,步長1

    StudentID char (4) NOT NULL ,

    Name char (10) NOT NULL ,

    Age int NULL ,

    Birthday datetime NULL,

        CONSTRAINT PK_Students PRIMARY KEY (StudentID)  --設定主鍵

)

CREATE TABLE Subjects (

        ID int IDENTITY ( 1, 1), --自增字段,基數1,步長1

    ClassID char (4) NOT NULL ,

    ClassName char (10) NOT NULL,

        CONSTRAINT PK_Subjects PRIMARY KEY (ClassID)    --設定主鍵

)

CREATE TABLE Scores (

        ID int IDENTITY ( 1, 1), --自增字段,基數1,步長1

    StudentID char (4) NOT NULL ,

    ClassID char (4) NOT NULL ,

    Score float NOT NULL,

        CONSTRAINT FK_Scores_Students FOREIGN KEY (StudentID) REFERENCES Students(StudentID), --設定外鍵

        CONSTRAINT FK_Scores_Subjects FOREIGN KEY (ClassID) REFERENCES Subjects(ClassID), --設定外鍵

        CONSTRAINT PK_Scores PRIMARY KEY (StudentID,ClassID) --設定主鍵

)

6.怎樣删除表？

DROP TABLE Students

7.怎樣建立視圖？

CREATE VIEW s_s_s

AS

SELECT Students.Name, Subjects.ClassName, Scores.Score

FROM Scores INNER JOIN

      Students ON Scores.StudentID = Students.StudentID INNER JOIN

      Subjects ON Scores.ClassID = Subjects.ClassID

8.怎樣删除視圖？

DROP VIEW s_s_s

9.如何建立存儲過程?

CREATE PROCEDURE GetStudent

@age INT,

@birthday DATETIME

AS

SELECT *

FROM students

WHERE Age = @age AND Birthday = @birthday

GO

10.如何删除存儲過程?

DROP PROCEDURE GetStudent

11.如何建立觸發器?

CREATE TRIGGER reminder

ON Students

FOR INSERT, UPDATE, DELETE

AS

   EXEC master..xp_sendmail 'MaryM',

      'Don''t forget to print a report for the distributors.'

GO

12.如何删除觸發器?

DROP TRIGGER reminder

13.如何建立索引?

CREATE UNIQUE INDEX IX_Students ON Students (Name)

14.如何删除索引?

DROP INDEX Students.IX_Students

15.怎樣給表添加字段？

ALTER TABLE Students ADD Address varchar (50) NULL

16.怎樣删除表中某個字段？

ALTER TABLE Students DROP COLUMN Address

17.如何設定列的辨別屬性?

沒找到辦法

18.如何去掉列的辨別屬性?

沒有找到好的方法,隻能是先添加一列,然後把辨別列的值更新到新加入的列,删除辨別列,再用與辨別列相同的名字類型添加一列,用前面加入的列更新該列.如果該辨別列是其他表的外鍵,還要先删除外鍵限制,很麻煩.誰有好的辦法,還請告訴我.

19.如何重設辨別列的辨別種子?

DBCC CHECKIDENT (Student, RESEED, 1)

20.怎樣給表加上主鍵？

ALTER TABLE Scores ADD CONSTRAINT PK_Scores PRIMARY KEY (StudentID,ClassID)

21.怎樣删除表的主鍵？

ALTER TABLE Scores DROP CONSTRAINT PK_Scores

22.怎樣給表添加一個外鍵？

ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Students FOREIGN KEY (StudentID) REFERENCES Students (StudentID) ON DELETE CASCADE

23.怎樣删除表的一個外鍵？

ALTER TABLE Scores DROP CONSTRAINT FK_Scores_Students

24.怎樣給字段加上CHECK限制？

ALTER TABLE Students ADD CONSTRAINT CK_Students CHECK (Age > 0)

25.怎樣去掉字段上的CHECK限制？

ALTER TABLE Students DROP CONSTRAINT CK_Students

26.怎樣給字段設定預設值？

ALTER TABLE Students ADD CONSTRAINT DF_Students_Age DEFAULT (18) FOR Age

27.怎樣移去字段的預設值？

ALTER TABLE Students DROP CONSTRAINT DF_Students_Age

28.修改字段的類型及非空限制

ALTER TABLE Students ALTER COLUMN Age char (10) null

ALTER TABLE Students ALTER COLUMN Age int not null