MySQL数据库实训题_mysql实训

2018年7月11日笔记

1.新建数据库

设有一个数据库，包括四个表：学生表(student)、课程表(course)、成绩表(score)以及教师信息表(teacher)。用SQL语句创建四个表并完成相关题目。

创建学生表

create table student(

sno varchar(20) not null primary key,

sname varchar(20) not null,

ssex varchar(20) not null,

sbirthday datetime,

class varchar(20)

) engine = innodb default charset = utf8;

创建教师表

create table teacher(

tno varchar(20) not null primary key,

tname varchar(20) not null,

tsex varchar(20) not null,

tbirthday datetime,

prof varchar(20),

depart varchar(20) not null

)engine = innodb default charset = utf8;

创建课程表

create table course(

cno varchar(20) not null primary key,

cname varchar(20) not null,

tno varchar(20) not null,

foreign key(tno) references teacher(tno)

) engine = innodb default charset = utf8;

创建成绩表

create table score(

sno varchar(20) not null,

foreign key(sno) references student(sno),

cno varchar(20) not null,

foreign key(cno) references course(cno)

degree decimal,

) engine = innodb default charset = utf8;

2.插入数据库

插入学生表数据

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHD