S2 第二章

--创建数据库
USE master
GO
IF EXISTS (SELECT * FROM sysdatabases WHERE name='S2226')
DROP DATABASE S2226
CREATE DATABASE S2226
on PRIMARY
(
NAME='S2226_data',
FILENAME='D:\S2226_data.mdf',
SIZE=5mb,
MAXSIZE=100mb,
FILEGROWTH=15%
)
LOG on
(
NAME='S2226_log',
FILENAME='E:\\S2226_log.ldf',
SIZE=5mb,
MAXSIZE=200mb,
FILEGROWTH=1mb
)


--创建表 标识列        列名 int identity(1,1),
USE S2226

CREATE TABLE Student    --创建学生信息表
(
Sid int NOT NULL,
Sname nvarchar(20) NOT NULL,
Sremark nvarchar(20) NOT NULL,
Sage int NOT NULL,
Cid int NOT NULL
)
CREATE TABLE Grade    --创建年级信息表
(
Cid int NOT NULL,
Cname nvarchar(20) NOT NULL
)

--添加数据
INSERT INTO Student (Sid,Sname,Sremark,Sage,Cid) VALUES (1,'刘','1',18,1)
INSERT INTO Student (Sid,Sname,Sremark,Sage,Cid) VALUES (2,'刘','2',18,2)
INSERT INTO Student (Sid,Sname,Sremark,Sage,Cid) VALUES (3,'刘','3',18,3)

INSERT INTO Grade (Cid,Cname) VALUES (1,'S1'),(2,'S2'),(3,'Y2')

--建立主键
ALTER TABLE Student 
ADD CONSTRAINT PK_Sid PRIMARY KEY (Sid)
ALTER TABLE Grade 
ADD CONSTRAINT PK_Cid PRIMARY KEY (Cid)

--建立外键
ALTER TABLE Student
ADD CONSTRAINT FK_Cid FOREIGN KEY(Cid)
REFERENCES Grade (Cid)

--删除外键
ALTER TABLE Student
DROP CONSTRAINT FK_Cid

--唯一约束
ALTER TABLE Student
ADD	CONSTRAINT  UQ_Sname UNIQUE (Sname)

--默认约束
ALTER TABLE Student
ADD CONSTRAINT DF_Sremark DEFAULT ('无备注') FOR Sremark

--约束检查
ALTER TABLE Student
ADD CONSTRAINT CK_Sage CHECK (Sage>='18')

--删除数据库，删除表，删除约束
--删除约束
ALTER TABLE Student
DROP CONSTRAINT DF_Sremark
--删除表
DROP TABLE Student
--删除数据库
DROP DATABASE S2226
GO