insert into Student(Sid,Sname,sex,birthday,entrydate,plandscape,nativeplace)
values('2017071102','好生','男','1996-5-3','2017-9-1','团员','广州顺德')
select * from student
insert into Course(Cid,Cname,category,credit)
values('101','SQLServer','考试',3)
insert into Course(Cid,Cname,category,credit)
values('103','Java','考试',3)
select * from course
alter table T
add email nvarchar(30) check(email like '%@%.%')
ALTER TABLE T ADD constraint UK_T_tel unique(tel)
create table CF
(
Sid char(10) not null,
Cid char(3) not null,
charges money,
academicyear char(20),
semester int
)
alter table cf add constraint pk_cf primary key (Sid,Cid)
insert into cf values('2010071102','102',30,'2012-2013',1)
insert into cf values('2010071102','102',40,'2012-2013',1)
再向CF表插入第二条语句是失败,因为违反了 PRIMARY KEY 约束 'pk_cf'。
alter table cf add constraint ck_charges check (charges>0 and charges<100)
insert into cf values('2010071102','101',140,'2012-2013',1)
第二条语句失败,因为收费字段为140,与 CHECK 约束"ck_charges"冲突。
alter table cf add constraint DF_semester default 1 for semester
insert into cf(Sid,Cid,charges,semester) values ('2010071103','104',40,'2012-2013')
可以向CF表插入成功,因为在学期字段设置了默认约束,如果设置了具体值就按设置数据存放(如第1句学期字段为2)
,如果没有设置具体的值就默认值存放(如第2句学期字段为1)。
alter table cf add constraint FK_CF_S foreign key(Sid) references S(Sid)
alter table cf add constraint FK_CF_C foreign key(Cid) references C(Cid)
insert into cf (Sid,Cid,charges,semester) values('2010071102','123',30,'2012-2013')
不能插入,因为不存在'123'号课程,和外键约束发生冲突。
update student set Sid='2010071115' where Sid='2010071102'
在默认状态下,不能成功,因为在CF表中或CS表中引用了'2010071102'该学生,所以不能修改。
delete from S where Sid='2010071115'
不行,因为外键。
alter table cf add paymentdate datetime default getdate()
数据表
create database jxgl
go
use jxgl
go
create table T(
Tid nchar(5) primary key,
name nchar(10) not null,
sex nchar(2) default '男',
workingtime datetime,
plandscape nchar(20),
degree nchar(10),
title nvarchar(6),
department nvarchar(20),
tel nchar(11)
)
create table S(
Sid nchar(10) primary key,
sname nchar(10),
sex nchar(2) default '男',
birthday Date,
age as datediff(yy,birthday,getdate()),
entrydate date,
plandscape nchar(10) default '团员',
nativeplace nvarchar(20),
class as substring(Sid,7,2)
)
create table C(
Cid nchar(3) primary key,
cname nvarchar(20) Not null,
category nchar(4) Not null check(category in('考试','考查')) default '考试',
credit int,
Tid nchar(5) foreign key references T(Tid) on update cascade
)
create table SC(
Sid nchar(10) foreign key references S(Sid) on update cascade on delete cascade,
Cid nchar(3),
score int check(score between 0 and 100),
primary key(Sid,Cid),
foreign key(Cid) references C(Cid) on update cascade on delete cascade
)
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95011', N'赵西明 ', N'男 ', CAST(0x0000768300000000 AS DateTime), N'群众 ', N'硕士 ', N'副教授', N'软件', N'13733152369')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95012', N'李小平 ', N'男 ', CAST(0x0000775E00000000 AS DateTime), N'党员 ', N'硕士 ', N'教授', N'计算机系', N'13733152370')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95013', N'张爽 ', N'男 ', CAST(0x00007BC700000000 AS DateTime), N'群众 ', N'本科 ', N'副教授', N'计算机系', N'13733152371')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95014', N'李丽宁 ', N'女 ', CAST(0x00007BC700000000 AS DateTime), N'党员 ', N'硕士 ', N'副教授', N'计算机系', N'13733152372')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95015', N'张金明 ', N'男 ', CAST(0x0000812C00000000 AS DateTime), N'群众 ', N'硕士 ', N'讲师', N'计算机系', N'13733152373')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95016', N'李燕 ', N'女 ', CAST(0x000082C800000000 AS DateTime), N'党员 ', N'硕士 ', N'讲师', N'计算机系', N'13733152374')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95017', N'宛平 ', N'女 ', CAST(0x0000840B00000000 AS DateTime), N'群众 ', N'博士 ', N'副教授', N'软件', N'13733152375')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95018', N'陈江川 ', N'男 ', CAST(0x0000883900000000 AS DateTime), N'群众 ', N'博士 ', N'讲师', N'软件', N'13733152376')
INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95019', N'郭新 ', N'男 ', CAST(0x00008CAA00000000 AS DateTime), N'党员 ', N'博士 ', N'讲师', N'软件', N'13733152377')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071101', N'张佳 ', N'女 ', CAST(0x3F1D0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'江西南昌')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071102', N'好生 ', N'男 ', CAST(0xCD1E0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'广州顺德')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071203', N'徐克 ', N'男 ', CAST(0xBF210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'江西南昌')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071204', N'叶飞 ', N'女 ', CAST(0xD1210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'党员 ', N'上海')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071205', N'任伟 ', N'男 ', CAST(0x7B220B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'北京顺义')
INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'2017071206', N'江贺 ', N'男 ', CAST(0x02200B00 AS Date), CAST(0x3D3D0B00 AS Date), N'党员 ', N'福建厦门')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'101', N'计算机基础', N'考试 ', 2, N'95011')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'102', N'C语言', N'考试 ', 3, N'95012')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'103', N'计算机组成原理', N'考试 ', 3, N'95012')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'104', N'汇编语言', N'考试 ', 3, N'95014')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'105', N'Java语言', N'考查 ', 2, N'95015')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'106', N'操作系统', N'考试 ', 3, N'95016')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'107', N'数据结构', N'考试 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'108', N'编译原理', N'考试 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'109', N'网络基础', N'考试 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'110', N'数据库原理', N'考试 ', 3, N'95017')
INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'120', N'SQLServer', N'考查 ', 2, N'95018')
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'101', 90)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'102', 70)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'103', 48)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071101', N'105', 80)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'102', 90)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'104', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'106', 68)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'107', 85)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'109', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'110', 65)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071102', N'120', 48)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'102', 65)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'104', 82)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'105', 79)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'107', 55)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'110', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071203', N'120', 67)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'101', 86)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'102', 86)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'104', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'105', 84)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'106', 95)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'108', 91)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071204', N'110', 82)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'101', 63)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'102', 84)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071205', N'103', 77)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'107', 58)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'108', 74)
INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'2017071206', N'109', 74)