维护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