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