随笔- 72 文章- 0 评论- 91 阅读- 48885

开始稍微系统点的联系，难度不断增加，持续更新

 1 create database Sprise
 2 use Sprise
 3 go
 4 
 5 create table Student
 6 (
 7 Sno nvarchar(200) primary key not null,
 8 Sname nvarchar(200) not null,
 9 Ssex nvarchar(200) not null,
10 Sbirthday datetime ,
11 Class nvarchar(200) ,
12 
13 )
14 
15 go
16 
17 create table Course
18 (
19 Cno nvarchar(200) primary key not null,
20 Cname nvarchar(200) not null,
21 Tno nvarchar(200) not null,
22 )
23 
24 go
25 
26 create table Score
27 (
28 Sno nvarchar(200) not null,
29 Cno nvarchar(200) not null,
30 Degree decimal(4,1) ,
31 )
32 
33 go
34 
35 create table Teacher
36 (
37 Tno nvarchar(200) primary key not null,
38 Tname nvarchar(200) not null,
39 Tsex nvarchar(200) not null,
40 Tbirthday datetime ,
41 Prof nvarchar(200) ,
42 Depart nvarchar(200) not null,
43 )
44 
45 --select *from Teacher
46 
47 
48 insert into Student values('108','曾华','男','1997-09-01','95003')
49 insert into Student values('105','匡华','男','1975-10-12','95031')
50 insert into Student values('107','王丽','女','1976-10-23','95033')
51 insert into Student values('101','李军','男','1974-10-12','95033')
52 insert into Student values('109','王芳','女','1973-10-12','95031')
53 insert into Student values('103','陆君','男','1972-10-12','95031')
54 
55 go
56 
57 
58 insert into Course values('3-105','计算机导论','825')
59 insert into Course values('3-245','操作系统','804')
60 insert into Course values('6-166','数字电路','856')
61 insert into Course values('9-888','高等数学','831')
62 
63 go
64 
65 insert into Score values('103','3-245',86)
66 insert into Score values('105','3-245',75)
67 insert into Score values('101','3-245',90)
68 insert into Score values('105','3-105',77)
69 insert into Score values('103','3-105',88)
70 insert into Score values('107','9-888',72)
71 insert into Score values('107','3-245',66)
72 insert into Score values('109','3-245',71)
73 insert into Score values('103','9-888',89)
74 insert into Score values('108','3-245',96)
75 
76 go
77 
78 insert into Teacher values('804','李成','男','1958-12-20','副教授','计算机系')
79 insert into Teacher values('825','李三','男','1958-11-20','副教授','电子工程系')
80 insert into Teacher values('856','赵四','女','1958-09-20','副教授','计算机系')
81 insert into Teacher values('831','鼠标','男','1958-07-20','副教授','电子工程系')
82 
83 
84 
85 alter table Score add constraint S_Sc_Sno foreign key (Sno) references Student(Sno)
86 alter table Score add constraint C_Sc_Cno foreign key (Cno) references Course(Cno)
87 alter table Course add constraint T_C_Tno foreign key (Tno) references Teacher(Tno)