数据库系统概论(第五版)79页提供的三个表,为了我们上机操作容易,下面创建这三个表
学生-课程数据库中包含以下三个表
学生表:Student(Sno,Sname,Ssex,Sage,Sdept)课程表:Course(Cno,Cname,Cpno,Ccredit)学生选课表:SC(Sno,Cno,Grade)
关系的主码加粗表示,各个表中的数据实例:
Student
Course
SC
1、建表语句
Sno为Student表的主键,Cno为Course表的主键,SC表中外键Sno,Cno分别是Student表和Course表的主键
create table Student (Sno char(9) primary key,Sname char(20),Ssex char(2),Sage int,Sdept char(10))charset=utf8;create table Course (Cno char(2) primary key,Cname char(20),Cpno char(2),Ccredit integer)charset=utf8;create table SC (Sno char(9),Cno char(2),Grade float,foreign key(Sno) references Student(Sno),foreign key(Cno) references Course(Cno))charset=utf8;
2、插入数据
insert into Student values('15121','李勇','男',20,'CS');insert into Student values('15122','刘晨','女',19,'CS');insert into Student values('15123','王敏','女',18,'MA');insert into Student values('15125','张立','男',19,'IS');insert into Course values('1','数据库','5',4);insert into Course values('2','数学','',2);insert into Course values('3','信息系统','1',4);insert into Course values('4','操作系统','6',3);insert into Course values('5','数据结构','7',4);insert into Course values('6','数据处理','',2);insert into Course values('7','PASCAL语言','6',4);insert into SC values('15121','1',92);insert into SC values('15121','2',85);insert into SC values('15121','3',88);insert into SC values('15122','2',90);insert into SC values('15122','3',80);
3、查询表
select * from Student;select * from Course;select * from SC;