mysql创建学生表、课程表、分数表、教师表

DROP TABLE IF EXISTS `db_school`.`Student`;create table Student(Sno varchar(20),Sname varchar(50),primary key (Sno)) ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `db_school`.`Course`;create

_昨叶何草

14794人浏览 · 2021-03-17 11:43:04

_昨叶何草 · 2021-03-17 11:43:04 发布

https://blog.csdn.net/Yan456jie/article/details/52557140

DROP TABLE IF EXISTS `db_school`.`Student`;
create table Student
(
	Sno varchar(20),
	Sname varchar(50),	
	primary key (Sno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
DROP TABLE IF EXISTS `db_school`.`Course`;
create table Course
(
	Cno varchar(20),
	Cname varchar(50),	
	Tno varchar(20),
	primary key (Cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
DROP TABLE IF EXISTS `db_school`.`SC`;
create table SC
(
	Sno varchar(20),
	Cno varchar(20),	
	score int,
	primary key (Sno,Cno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
DROP TABLE IF EXISTS `db_school`.`Teacher`;
create table Teacher
(
	Tno varchar(20),
	Tname varchar(50),	
	primary key (Tno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `Student`(Sno,Sname) VALUES ('001','陈一');
INSERT INTO `Student`(Sno,Sname) VALUES ('002','郭二');
INSERT INTO `Student`(Sno,Sname) VALUES ('003','张三');
INSERT INTO `Student`(Sno,Sname) VALUES ('004','李四');
INSERT INTO `Student`(Sno,Sname) VALUES ('005','王五');
 
 
INSERT INTO `Teacher`(Tno,Tname) VALUES ('001','张老师');
INSERT INTO `Teacher`(Tno,Tname) VALUES ('002','王老师');
INSERT INTO `Teacher`(Tno,Tname) VALUES ('003','钱老师');
INSERT INTO `Teacher`(Tno,Tname) VALUES ('004','刘老师');
INSERT INTO `Teacher`(Tno,Tname) VALUES ('005','胡老师');
 
 
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('001','语文','张老师');
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('002','数学','王老师');
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('003','英语','钱老师');
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('004','物理','刘老师');
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('005','政治','胡老师');
 
 
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','001',50);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','002',60);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','003',70);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','004',80);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','005',90);
 
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','001',90);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','002',80);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','003',70);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','004',60);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','005',50);
 
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','001',81);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','002',82);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','003',83);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','004',84);
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','005',85);