SQLServer常用操作

创建数据库与表

-- 选择数据库
USE master
GO
-----------------------------------------------------------------------------------------------------------
-- 删除数据库
IF EXISTS(SELECT * FROM sysdatabases WHERE name=N'School')
DROP DATABASE School
GO
-- 创建数据库
CREATE DATABASE School
-- 配置主数据文件的选项
ON PRIMARY
(
    -- 数据文件的逻辑名称
    name = "School",
    -- 数据文件的实际保存路径
    filename = "d:\db\SqlServer\School\database.mdf",
    -- 文件的初始大小
    size = 10MB,
    -- 最大容量
    maxsize = 100MB,
    -- 超过默认值后自动以20%扩容
    filegrowth = 20%
)
-- 配置日志文件的选项
LOG ON
(
    name = "database.log",
    filename = "d:\db\SqlServer\School\database.ldf",
    size = 10MB,
    maxsize = 100MB,
    filegrowth = 15%
)
GO
-----------------------------------------------------------------------------------------------------------
-- 创建表 学生表
USE School   --将数据库上下文切换到当前创建的数据库
GO

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'Students')
DROP TABLE [Students]
GO

CREATE TABLE [Students]
(
    [StudentID] [INT] IDENTITY(1,1) NOT NULL,
    [StudentNO] [INT] NOT NULL,
    [UserName] [NVARCHAR](50) NOT NULL,
    [Password] [NVARCHAR](50) NOT NULL,
    [Email] [NVARCHAR](50) NOT NULL,
    [Phone] [NVARCHAR](50) NULL, 
    [IdCardNO] [NVARCHAR](50) NOT NULL, 
    [Gender] [BIT] NOT NULL,
    [Address] [NVARCHAR](255) NULL,
    [BornDate] [DATETIME] NOT NULL,
    [GradeNO] [INT] NOT NULL
)
GO

-- 创建表 科目表
USE School
GO

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'Subjects')
DROP TABLE [Subjects]
GO

CREATE TABLE [Subjects]
(
    [SubjectID] [INT] IDENTITY(1,1) NOT NULL,
    [SubjectNO] [INT] NOT NULL,
    [SubjectName] [NCHAR](50) NOT NULL,
    [ClassHour] [INT] NOT NULL,
    [GradeNO] [INT] NOT NULL
)
GO

-- 创建表 年级表
USE School
GO

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'Grades')
DROP TABLE [Grades]
GO

CREATE TABLE [Grades]
(
    [GradeID] [INT] IDENTITY(1,1) NOT NULL,
    [GradeNO] [INT] NOT NULL,
    [GradeName] [NVARCHAR](50) NOT NULL
)
GO

-- 创建表 成绩表
USE School
GO

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'Scores')
DROP TABLE [Scores]
GO

CREATE TABLE [Scores]
(
    [ScoreID] [INT] IDENTITY(1,1) NOT NULL,
    [StudentNO] [INT] NOT NULL,
    [SubjectNO] [INT] NOT NULL,
    [Score] [INT] NOT NULL,
    [ExamDate] [DATETIME] NOT NULL
)
GO
-----------------------------------------------------------------------------------------------------------
-- 添加约束 年级表
ALTER TABLE Grades 
ADD CONSTRAINT PK_GradeID PRIMARY KEY(GradeID)
GO 

ALTER TABLE Grades 
ADD CONSTRAINT UQ_GradeNO UNIQUE(GradeNO)
GO

-- 添加约束 学生表
-- 主键约束
ALTER TABLE Students 
ADD CONSTRAINT PK_StudentID PRIMARY KEY(StudentID)
GO

-- 唯一约束
ALTER TABLE Students 
ADD CONSTRAINT UQ_StudentNO UNIQUE(StudentNO)
GO 

ALTER TABLE Students 
ADD CONSTRAINT UQ_IdCardNO UNIQUE(IdCardNO)
GO    

ALTER TABLE Students 
ADD CONSTRAINT UQ_Email UNIQUE(Email)
GO

ALTER TABLE Students 
ADD CONSTRAINT UQ_Phone UNIQUE(Phone)
GO
-- 默认约束
ALTER TABLE Students 
ADD CONSTRAINT DF_Address DEFAULT('地址不详') FOR Address
GO

-- 检查约束
ALTER TABLE Students 
ADD CONSTRAINT CK_BornDate CHECK(BornDate>='1970-01-01')
GO
 
-- 非空约束
ALTER TABLE Students 
ADD CONSTRAINT CK_UserName CHECK(UserName IS NOT NULL)
GO

-- 外键约束
ALTER TABLE Students 
ADD CONSTRAINT FK_Students_GradeNO FOREIGN KEY(GradeNO) REFERENCES Grades(GradeNO)
GO


-- 添加约束 科目表
ALTER TABLE Subjects 
ADD CONSTRAINT PK_SubjectID PRIMARY KEY(SubjectID)
GO

ALTER TABLE Subjects 
ADD CONSTRAINT UQ_SubjectNO UNIQUE(SubjectNO)
GO

ALTER TABLE Subjects 
ADD CONSTRAINT CK_ClassHour CHECK(ClassHour>=0)
GO

ALTER TABLE Subjects 
ADD CONSTRAINT FK_Subjects_GradeNO FOREIGN KEY(GradeNO) REFERENCES Grades(GradeNO)
GO


-- 添加约束 成绩表
ALTER TABLE Scores 
ADD CONSTRAINT PK_ScoreID PRIMARY KEY(ScoreID)
GO 

ALTER TABLE Scores 
ADD CONSTRAINT DF_ExamDate DEFAULT(GETDATE()) FOR ExamDate
GO

ALTER TABLE Scores 
ADD CONSTRAINT CK_Score CHECK(Score BETWEEN 0 AND 100)
GO

ALTER TABLE Scores 
ADD CONSTRAINT FK_Scores_StudentNO FOREIGN KEY(StudentNO) REFERENCES Students(StudentNO)
GO

ALTER TABLE Scores 
ADD CONSTRAINT FK_Scores_SubjectNO FOREIGN KEY(SubjectNO) REFERENCES Subjects(SubjectNO)
GO

T-SQL操作