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 FO