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,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENT (SNO,