数据库中对表的一些基本操作

--建表
CREATE TABLE student
(
 stuNo NCHAR(6) NOT NULL,
 stuName NVARCHAR(20) NOT NULL,
 stuSex NCHAR(4) NOT NULL,
 stuAge SMALLINT NOT NULL,
 stuAddress NVARCHAR(50),
 stuScore FLOAT,
 stuSeat SMALLINT IDENTITY(1,1)
 
)

--修改表(增加列)
ALTER TABLE student
ADD stuSeat INT
GO

--删除表
DROP TABLE student
GO

--设置主键约束
ALTER TABLE student
ADD CONSTRAINT PK_stuNo PRIMARY KEY CLUSTERED(stuNo)
--设置唯一约束
ALTER TABLE student
ADD CONSTRAINT UN_stuName UNIQUE(stuName)

--添加检查约束
ALTER TABLE student
ADD CONSTRAINT CK_stuSex CHECK(stuSex='男' or stuSex='女')
ALTER TABLE student
ADD CONSTRAINT CK_stuAge CHECK(stuAge>16 and stuAge<30)
ALTER TABLE student
ADD CONSTRAINT CK_stuScore CHECK(stuScore>0 and stuScore<100)

--添加默认值
ALTER TABLE student
ADD CONSTRAINT DF_stuSex DEFAULT('男') FOR stuSex,
 CONSTRAINT DF_stuAddress DEFAULT('地址不详') FOR stuAddress,
 CONSTRAINT DF_stuScore DEFAULT(0) FOR stuScore

ALTER TABLE student
ADD CONSTRAINT CH_stuNo CHECK(stuNo like 'G211[0-9][0-9]')

------------------------------------------------------------
-----------------------------------------------------------

--建stuMarks表
CREATE TABLE stuMarks
(
 ExamNo CHAR(7) PRIMARY KEY,
 stuNo NCHAR(6) NOT NULL CHECK(stuNo like'G211[0-9][0-9]'),
 cSharpExam SMALLINT NOT NULL CHECK(cSharpExam between 0 and 100),
 SQLExam SMALLINT NOT NULL CHECK(SQLExam between 0 and 100)
)
--设置外键
ALTER TABLE stuMarks
ADD CONSTRAINT FK_stuMarks_student FOREIGN KEY(stuNo)
 REFERENCES student(stuNo)

--添加检查约束
ALTER TABLE stuMarks
ADD CONSTRAINT CH_cSharpExam CHECK(cSharpExam between 0 and 100),
 CONSTRAINT CH_SQLExam CHECK(SQLExam between 0 and 100)