create table Student
(
Sno varchar(50)primary key not null, --学号(主码)
Sname varchar(50) not null, --学生姓名
Ssex varchar(50)not null, --学生性别
Sbirthday datetime, --学生出生年月
Class varchar(50) --学生所在班级
)
go
truncate table Student --此句是清除表中所有内容 不是清除表
insert into Student values('108','曾华','男','1977-09-01','95033')
insert into Student values('105','匡明','男','1975-10-02','95031')
insert into student values('107','王丽','女','1976-01-23','95033')
insert into student values('101','李军','男','1976-02-20','95033')
insert into student values('109','王芳','女','1975-02-10','95031')
insert into student values('103','陆君','男','1974-06-03','95031')
select*from student
create table course
(
Cno varchar(50)primary key not null, --课程号(主码)
Cname varchar(50) not null, --课程名称
Tno varchar(50)references teacher(Tno)not null --教工编号(外码)
)
insert into course values('3-105','计算机导论','825')
insert into course values('3-245','操作系统','804')
insert into course values('6-166','数字电路','856')
insert into course values('9-888','高等数学','831')
select*from course
create table Score
(
Sno varchar(50)references student(Sno)not null , --学号(外码)
Cno varchar(50)references course(Cno)not null , --课程号(外码)
Degree Decimal(4,1) --成绩
primary key (Sno,Cno)
)
truncate table Score
insert into Score values('103','3-245', 86)
insert into Score values('105','3-245', 75)
insert into Score values('109','3-245', 68)
insert into Score values('103','3-105', 92)
insert into Score values('105','3-105', 88)
insert into Score values('109','3-105', 76)
insert into Score values('101','3-105', 64)
insert into Score values('107','3-105', 91)
insert into Score values('108','3-105', 78)
insert into Score values('101','6-166', 85)
insert into Score values('107','6-166', 79)
insert into Score values('108','6-166', 81)
select*from Score
create table Teacher
(
Tno varchar(50)primary key not null, --教工编号(主码)
Sname varchar(50) not null, --教工姓名
Ssex varchar(50)not null, --教工性别
Sbirthday datetime, --教工出生年月
Prof varchar(50)not null,
Depart Varchar(50) --教工所在部门
)
truncate table Teacher
insert into Teacher values('804','李诚','男','1958-12-02','副教授','计算机系')
insert into Teacher values('856','张旭','男','1969-03-12','讲师', '电子工程系')
insert into Teacher values('825','王萍','女','1972-05-05','助教', '计算机系')
insert into Teacher values('831','刘冰','女','1977-08-14','助教', '电子工程系')
select*from Teacher
受约束的表格叫外键表,约束的数据源叫主键表
创建表格时 先创建有主键的表 再创建有主键和外键的表 再创建有外键的表及是有逻辑的 删除则反之
要删除外键必须先删除主键 要删除主键必须先删除外键