【数据库】复习练习SQL建表添数据

建表

create table Student(S# int not null primary key ,Sname nvarchar(20),Sage int,Ssex varchar(4))

create table Course(C# int not null primary key, Canme nvarchar(10),T# int)

Create table SC(S# int not null,C# int not null,score int) //这看题直接建，发现没主键

create table Teacher(T# int not null primary key,Tname nvarchar(10))

创建外键Course_Teacher

alter table Course

add constraint Course_Teacher

foreign key (T#)

references Teacher(T#)

alter table SC

add constraint SC_Course

foreign key (C#)

references Course(C#)

插入数据

先插入老师

insert into Teacher(T#,Tname) values('1','叶平');

insert into Teacher(T#,Tname) values(2,'张阔');

insert into Teacher(T#,Tname) values(3,'王超');

insert into Teacher(T#,Tname) values(4,'任川');

insert into Teacher(T#,Tname) values(5,'李安');

Course

insert into Course(C#,Canme,T#) values(001,'数据库',1);

insert into Course(C#,Canme,T#) values(002,'企业管理',4);

insert into Course(C#,Canme,T#) values(003,'英语',2);

insert into Course(C#,Canme,T#) values(004,'语文',3);

insert into Course(C#,Canme,T#) values(006,'C语言',1);

insert into Student(S#,Sname,Sage,Ssex) values(1,'张三',15,'男')

insert into Student(S#,Sname,Sage,Ssex) values(2,'张燕',15,'女')

insert into Student(S#,Sname,Sage,Ssex) values(3,'高飞',15,'男')

insert into Student(S#,Sname,Sage,Ssex) values(4,'孙文',15,'男')

insert into Student(S#,Sname,Sage,Ssex) values(5,'杨帆',15,'男')

insert into Student(S#,Sname,Sage,Ssex) values(6,'莫醒',15,'女')

insert into SC(S#,C#,score) values(1,1,68);

insert into SC(S#,C#,score) values(1,2,79);

insert into SC(S#,C#,score) values(1,3,91);

insert into SC(S#,C#,score) values(1,4,67);

insert into SC(S#,C#,score) values(1,5,88);

insert into SC(S#,C#,score) values(2,1,98);

insert into SC(S#,C#,score) values(2,2,63);

insert into SC(S#,C#,score) values(2,3,76);

insert into SC(S#,C#,score) values(2,4,61);

insert into SC(S#,C#,score) values(2,5,84);

insert into SC(S#,C#,score) values(3,1,62);

insert into SC(S#,C#,score) values(3,2,58);

insert into SC(S#,C#,score) values(3,3,36);

insert into SC(S#,C#,score) values(3,4,38);

insert into SC(S#,C#,score) values(4,1,58);

insert into SC(S#,C#,score) values(4,2,63);

insert into SC(S#,C#,score) values(4,4,74);

insert into SC(S#,C#,score) values(4,5,55);

insert into SC(S#,C#,score) values(5,1,43);

insert into SC(S#,C#,score) values(5,2,58);

insert into SC(S#,C#,score) values(5,3,47);

insert into SC(S#,C#,score) values(5,4,55);

insert into SC(S#,C#,score) values(5,5,35);

insert into SC(S#,C#,score) values(6,1,38);

insert into SC(S#,C#,score) values(6,3,68);

insert into SC(S#,C#,score) values(6,4,77);

insert into SC(S#,C#,score) values(6,5,58);