MySQL简单建库建表操作

一、建库

create database selecttest character set utf8;
use selecttest;

---

二、建表

1.学生表（Student）

create table student(
	sno varchar(20)  primary key,
	sname varchar(20) not null,
	ssex varchar(10) not null,
	sbirthday datetime,
	class varchar(20)
)character set utf8;

2.教师表(Teacher)

create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(20) not null,
	tbirthday datetime,
	prof varchar(20) not null,
	depart varchar(20) not null
)character set utf8;

3.课程表（Course）

create table course(
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
)character set utf8;

4.成绩表（Score）

create table score(
	sno varchar(20) not null,
	cno varchar(20) not null,
	degree decimal,
	foreign key(sno) references student(sno),
	foreign key(cno) references course(cno), 
	primary key(sno,cno)
)character set utf8;

---

三、插入数据

1.添加学生信息

insert into student values('101','李华','男','1998-08-15','95001');
insert into student values('102','王怡','男','1997-08-25','95001');
insert into student values('103','刘健康','男','1997-04-18','95001');
insert into student values('201','田雨','女','1999-09-15','95002');
insert into student values('202','李亚吉','男','1995-07-14','95002');
insert into student values('104','马佳','女','1997-04-16','95001');
insert into student values('105','刘浪','男','1997-04-12','95001');
insert into student values('203','曾建','男','1998-09-06','95002');
insert into student values('204','王丽云','女','1998-07-14','95002');
insert into student values('106','卢玉石','女','1997-04-16','95001');
insert into student values('301','刘媛媛','女','1999-03-19','95003');
insert into student values('302','吴军','男','1997-06-12','95003');
insert into student values('303','肖庆','男','1998-02-26','95003');
insert into student values('304','王一云','女','1998-07-25','95003');
insert into student values('305','林风','女','1997-01-05','95003');

2.添加教师信息

insert into teacher values('001','李成','男','1977-04-18','副教授','计算机系');
insert into teacher values('002','张旭','男','1989-09-15','讲师','计算机系');
insert into teacher values('003','王萍','女','1975-07-14','副教授','电子工程系');
insert into teacher values('004','刘冰湖','女','1992-04-16','助教','电子工程系');

3.添加课程信息

insert into course values('3-105','计算机导论','001');
insert into course values('3-155','操作系统','002');
insert into course values('4-256','高等数学','003');
insert into course values('4-879','模拟电路','004');

4.添加成绩信息

insert into score values('101','3-105','89');
insert into score values('102','3-155','78');
insert into score values('103','4-879','92');
insert into score values('104','4-256','67');
insert into score values('105','3-155','78');
insert into score values('106','3-105','88');
insert into score values('201','4-256','82');
insert into score values('202','3-155','78');
insert into score values('203','4-879','92');
insert into score values('204','3-105','74');