1.如何創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE student

2.如何刪除數(shù)據(jù)庫

DROP DATABASE student

3.如何備份數(shù)據(jù)庫到磁盤文件

BACKUP DATABASE student to disk=´c:\1234.bak´

4.如何從磁盤文件還原數(shù)據(jù)庫

RESTORE DATABASE studnet FROM DISK = ´c:\1234.bak´

5.怎樣創(chuàng)建表？

CREATE TABLE Students (
    ID int IDENTITY ( 1, 1), --自增字段,基數(shù)1,步長1
    StudentID char (4) NOT NULL ,
    Name char (10) NOT NULL ,
    Age int NULL ,
    Birthday datetime NULL,
    CONSTRAINT PK_Students PRIMARY KEY (StudentID)  --設(shè)置主鍵
)

CREATE TABLE Subjects (
    ID int IDENTITY ( 1, 1), --自增字段,基數(shù)1,步長1
    ClassID char (4) NOT NULL ,
    ClassName char (10) NOT NULL,
    CONSTRAINT PK_Subjects PRIMARY KEY (ClassID)    --設(shè)置主鍵
)

CREATE TABLE Scores (
    ID int IDENTITY ( 1, 1), --自增字段,基數(shù)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), --設(shè)置外鍵
    CONSTRAINT FK_Scores_Subjects FOREIGN KEY (ClassID) REFERENCES Subjects(ClassID), --設(shè)置外鍵
    CONSTRAINT PK_Scores PRIMARY KEY (StudentID,ClassID) --設(shè)置主鍵
)

6.怎樣刪除表？

DROP TABLE Students

7.怎樣創(chuàng)建視圖？

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.如何創(chuàng)建存儲過程?

CREATE PROCEDURE GetStudent
@age INT,
@birthday DATETIME
AS
SELECT *
FROM students
WHERE Age = @age AND Birthday = @birthday
GO

10.如何刪除存儲過程?

DROP PROCEDURE GetStudent

11.如何創(chuàng)建觸發(fā)器?

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.如何刪除觸發(fā)器?

DROP TRIGGER reminder

13.如何創(chuàng)建索引?

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.如何設(shè)置列的標識屬性?

沒找到辦法

18.如何去掉列的標識屬性?

沒有找到好的方法,只能是先添加一列,然后把標識列的值更新到新加入的列,刪除標識列,再用與標識列相同的名字類型添加一列,用前面加入的列更新該列.如果該標識列是其他表的外鍵,還要先刪除外鍵約束,很麻煩.誰有好的辦法,還請告訴我.

19.如何重設(shè)標識列的標識種子?

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.怎樣給字段設(shè)置默認值？

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