先按照要求建立4个表
写出用CREATE TABLE语句对四个基本表的定义;
CREATE TABLE Student
(
SNo Char(7) UNIQUE NOT NULL,
Sname VarChar(20) NOT NULL,
Ssex Char(2) NOT NULL,
Sage Smallint,
Clno Char(5),
)
CREATE TABLE Course
(
Cno Char(1) UNIQUE NOT NULL,
Cname VarChar(20) NOT NULL,
Credit Smallint,
)
CREATE TABLE Class
(
Clno Char(5) UNIQUE NOT NULL,
Speciality VarChar(20) NOT NULL,
Inyear Char(20) NOT NULL,
Number Integer,
Monitor Char(7),
)
CREATE TABLE Grade
(
Sno Char(7) NOT NULL,
Cno Char(1) NOT NULL,
Gmark Decimal(4,1),
)
SQL语言的各项操作:
① 删除学生表中新增的属性Nation:
ALTER TABLEStudent
ADD
Nation Varchar(20)
② 删除学生表中新增的属性Nation:
ALTER TABLEStudent
DROP COLUMNNation
③ 向成绩表中插入记录(”2001110”,”3”,80):
INSERT INTOGrade(Sno,Cno,Gmark)
VALUES(2001110,3,80)
④ 修改学号为”2001110”的学生的成绩为70分:
UPDATE Grade
SET Gmark=70
WHERE Sno=2001110
⑤删除学号为”2001110”的学生的成绩记录:
DELETE FROMGrade
WHERE Sno=2001110
⑥为学生表创建一个名为IX_Class的索引,以班级号的升序排序:
CREATE CLUSTEREDINDEXIX_Class
ON Student(Clno)
⑦ 删除IX_Class索引:
DROP INDEXIX_Class
ON Student
新增约束:
ALTER TABLEStudent
ADD CONSTRAINTStudent_PrimPRIMARYKEY (Sno),
CONSTRAINT Student_sexCHECK(Ssex='男'OR Ssex='女'),
CONSTRAINT Student_sexdefaultDEFAULT'男'FORSsex,
CONSTRAINT Student_ageCHECK(Sage>=14AND Sage<=65),
CONSTRAINT Student_foreignFOREIGN KEY(Clno)REFERENCES Class(Clno)on update cascade on deletecascade
GO
ALTER TABLECourse
ADD CONSTRAINTCourse_PrimPRIMARYKEY (Cno),
CONSTRAINT Course_creditCHECK(CreditBETWEEN 1 AND 6)
GO
ALTER TABLEClass
ADD CONSTRAINTClass_PrimPRIMARYKEY (Clno),
CONSTRAINT Class_numberCHECK(Number>1AND Number<100),
CONSTRAINT Class_foreignFOREIGN KEY(Monitor)REFERENCES Student(Sno)
GO
ALTER TABLEGrade
ADD CONSTRAINTGrade_PrimPRIMARYKEY (Sno,Cno),
CONSTRAINT Class_sno_foreignFOREIGN KEY(Sno)REFERENCES Student(Sno)on update cascade on deletecascade,
CONSTRAINT Class_Cno_foreignFOREIGN KEY(Cno)REFERENCES Course(Cno)on update cascade on deletecascade,
CONSTRAINT Class_markCHECK(Gmark>0AND Gmark<100)
GO