SQL基础四（例子）

最新推荐文章于 2024-03-17 12:21:20 发布

转载最新推荐文章于 2024-03-17 12:21:20 发布 · 67 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/tcheng/p/6060778.html

本文档详细介绍了如何使用SQL创建学生、课程及成绩表，并演示了如何将具体的学生姓名和成绩数据插入到这些表中。

------------------------------------------------
--分别创建student/course/score表

Create table student
(
mid char(10) not null primary key,
mname char(50) not null,
)

Create table course
(
fid char(10) not null primary key,
fname char(50) not null,
)

Create table score
(
Sid int identity(1,1) primary key,
fid char(10) not null,
mid char(10) not null,
score int,
foreign key(mid) references student(mid),
foreign key(fid) references course(fid)
)
------------------------------------------------------
--
--往studnt表插入数据
insert into student(mid,mname) values('M001','张三')
insert into student(mid,mname) values('M002','李四')
insert into student(mid,mname) values('M003','王五')
insert into student(mid,mname) values('M004','顺六')
-------------------------------------------------------
--
--往scourse表插入数据
insert into course(fid,fname) values('F001','语文')
insert into course(fid,fname) values('F002','数学')
insert into course(fid,fname) values('F003','英语')
insert into course(fid,fname) values('F004','历史')
-------------------------------------------------------
--
--往score表插入数据
insert into score(fid,mid,score) values('F001','M001',78)
insert into score(fid,mid,score) values('F002','M001',78)
insert into score(fid,mid,score) values('F003','M001',78)
insert into score(fid,mid,score) values('F004','M001',78)

insert into score(fid,mid,score) values('F001','M002',58)
insert into score(fid,mid,score) values('F002','M002',58)
insert into score(fid,mid,score) values('F003','M002',68)
insert into score(fid,mid,score) values('F004','M002',98)

insert into score(fid,mid,score) values('F001','M003',74)
insert into score(fid,mid,score) values('F002','M003',74)
insert into score(fid,mid,score) values('F003','M003',71)
insert into score(fid,mid,score) values('F004','M003',73)

insert into score(fid,mid,score) values('F001','M004',79)
insert into score(fid,mid,score) values('F002','M004',77)
insert into score(fid,mid,score) values('F003','M004',1)
insert into score(fid,mid,score) values('F004','M004',22)


-------------------------