数据库SQL查询表的建立教程

建学生表

create table student

         (snochar(9) primary key,

         snamechar(20) unique,

ssex char(2),

sage smallint,

sdept char(20)

);

create table course

         (cnochar(4) primary key,

         cnamechar(40) no null,

         cpnochar(4),

         ccreditsmallint,

         foreignkey (cpno) references course(cno)

         );

create table sc

         (snochar(9),

         cnochar(4),

         gradesmallint,

         primarykey(sno, cno),

         foreignkey(sno) references student(sno),

         foreignkey(cno) references course(cno)

);

insert

into student(sno,sname,ssex,sage,sdept)

values('201215121','李勇','男',20,'cs')；

insert

into student (sno,sname,ssex,sage,sdept)

values ('201215122','刘晨','女',19,'cs');

insert

into student (sno,sname,ssex,sage,sdept)

values ('201215123','王敏','女',18,'ma');

insert

into student (sno,sname,ssex,sage,sdept)

values ('201215125','张立','男',19,'is');

insert

into course(cno,cname,cpno,ccredit)

values(‘2’,’数学’,’null’,’2’);

insert

into course(cno,cname,cpno,ccredit)

values(‘6’,’数据处理’,’null’,’2’);

insert

into course(cno,cname,cpno,ccredit)

values(‘4’,’操作’,’6’,’3’);

insert

into course (cno,cname,cpno,ccredit)

values('7','pscal语言','6','4');

insert

into course (cno,cname,cpno,ccredit)

values('5','数据结构','7','4');

insert

into course (cno,cname,cpno,ccredit)

values('1','','5','4');

insert

into course (cno,cname,cpno,ccredit)

values('3','信息系统','1','4');

insert

into sc(sno,cno,grade)

values(‘200215121’,’1’,’92’);

insert

into sc (sno,cno,grade)

values('200215121','2','85');

insert

into sc (sno,cno,grade)

values('200215121','3','88');

insert

into sc (sno,cno,grade)

values('200215122','2','90');

insert

into sc (sno,cno,grade)

values('200215122','3','80');