mysql查询练习数据准备

创建学生表，教师表，课程表和成绩表

首先来学生表

create table students(
    sno varchar(20) primary key,  #学号
    sname varchar(20) not null,   #名字
    ssex varchar(10) not null,    #性别
    sbirthday datetime,           #出生日期
    class varchar(20)             #班级
)character set = utf8;
最后加上character set = utf8可以插入中文（如果本身就可以插入中文的话就没必要加了）

教师表

create table teacher(
    tno varchar(20) primary key,   #教师号
    tname varchar(20) not null,    #名字
    tsex varchar(10) not null,     #性别
    tbirthday datetime,            #出生年月
    prof varchar(20),              #职位
    depart varchar(20)             #所在部门
)character set = utf8;

课程表

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;

成绩表

create table score(
    sno varchar(20) not null,                    #学生号
    cno varchar(20) not null,                    #课程号
    degree decimal,                              #成绩
    foreign key(sno) references students(sno),   #学生号外键关联学生表中的学号
    foreign key(cno) references course(cno),     #课程号外键关联课程表中的课程号
    primary key(sno,cno)                         #联合主键
)character set = utf8;

现在各个表建好之后就是往表里插入数据：

往学生表加数据：
insert into students values('101','曾华','男','1977-09-01','95033');
insert into students values('102','匡明','男','1975-10-02','95031');
insert into students values('103','王丽','女','1976-01-23','95033');
insert into students values('104','李军','男','1975-02-10','95033');
insert into students values('105','王芳','女','1974-06-03','95031');
insert into students values('106','陆君','男','1974-06-03','95031');
insert into students values('107','王尼玛','男','1976-02-20','95033');
insert into students values('108','张全蛋','男','1975-02-10','95031');
insert into students values('109','赵铁柱','男','1974-06-03','95031');


教师表加数据：
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');

课程表加数据：
insert into course values('3-105','计算机导论','825');  
insert into course values('3-245','操作系统','804');  
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831'); 


成绩表加数据：
insert into score values('103','3-105','92');   
insert into score values('103','3-245','86');   
insert into score values('103','6-166','85');   
insert into score values('105','3-105','88');   
insert into score values('105','3-245','75');   
insert into score values('105','6-166','79');   
insert into score values('109','3-105','76');   
insert into score values('109','3-245','68');   
insert into score values('109','6-166','81');