Mysql个人总结----02 数据库习题（子查询和表关联）

切换数据库

use Teacher_Du;    

创建学生表

create table student (
	Sno varchar(20) not null comment '学号',
	Sname varchar(20) not null comment '学生姓名',
	Ssex varchar(20) not null comment '学生性别',
	Sbirthday datetime comment'学生出生年月',
	Class varchar(20) comment '学生所在班级'
);

创建课程表

create table Course(
	Cno varchar(20) not null comment '课程号',
	Cname varchar(20) not null comment '课程名称',
	Tno varchar(20) not null comment '教工编号'
);

创建分数表

create table Score(
	Sno varchar(20) not null comment '学号',
	Cno varchar(20) not null comment '课程号',
	Degree decimal(4,1) comment '成绩'
); 

创建教师表

create table Teacher(
	Tno varchar(20) not null comment '教工编号',
	Tname varchar(20) not null comment '教工姓名',
	Tsex varchar(20) not null comment '教工性别',
	Tbirthday datetime comment '教师出生年月',
	Prof varchar(20) not null comment '职称',
	Depart varchar(20) not null comment '教工所在部门'
);

向学生表插入数据

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,Sname,Ssex,Sbirthday,Class)values('103','陆君','男','1974-06-03','95031');

向课程表插入数据

insert into Course(Cno,Cname,Tno)values('3-105','计算机导论','825');
insert into Course(Cno,Cname,Tno)values('3-245','操作系统','