多表关联查询

	create table student (
    no INT(10) ,
    name VARCHAR(20),
    sex VARCHAR(20) ,
    birthday datetime not null,
    class int
    );
    
    create table score (
    no INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
    cno VARCHAR(20) ,
    degree INT(10)
    );
	
Insert into score(no,cno,degree) values(5001，’3-105’，69);
Insert into score(no,cno,degree) values(5001，’5-102’，55);
Insert into score(no,cno,degree) values(503，’4-108’，85);
Insert into score(no,cno,degree) values(5004，’3-105’，77);
Insert into score(no,cno,degree) values(5005，’3-245’，100);
Insert into score(no,cno,degree) values(5006，’3-105’，53);
Insert into score(no,cno,degree) values(5003，’4-109’，45);
Insert into score(no,cno,degree) values(5008，’3-105’，98);
Insert into score(no,cno,degree) values(5004，’4-109’，68);
Insert into score(no,cno,degree) values(5010，’3-105’，88);
Insert into score(no,cno,degree) values(5003，’3-105’，98);
Insert into score(no,cno,degree) values(5005，’4-109’，68);
Insert into score(no,cno,degree) values(5002，’3-105’，88);
Insert into score(no,cno,degree) values(107，’3-105’，98);
Insert into score(no,cno,degree) values(108, ’4-109’，68);
Insert into score(no,cno,degree) values(109，’3-105’，88);
Insert into score(no,cno,degree) values(109，’4-109’，80);
Insert into score(no,cno,degree) values(107，’3-111’，88);
Insert into score(no,cno,degree) values(5003，’3-111’，80);


Insert into student(no,name,sex,birthday,class) values(5001，’李白’,’男’，’1987-7-22 00:00:00’，95001);
Insert into student(no,name,sex,birthday,class) values(5002，’夏洛特’,’女’，’1987-11-15 00:00:00’，95002);
Insert into student(no,name,sex,birthday,class) values(5003，’王昭君’,’女’，’1987-10-05 00:00:00’，95001);
Insert into student(no,name,sex,birthday,class) values(5004，’李信’,’男’，’1987-9-25 00:00:00’，95003);
Insert into student(no,name,sex,birthday,class) values(5005，’百里守约’,’男’，’1987-7-17 00:00:00’，95004);
Insert into student(no,name,sex,birthday,class) values(5006，’虞姬’,’女’，’1987-6-18 00:00:00’，95005);
Insert into student(no,name,sex,birthday,class) values(5007，’孙尚香’,’女’，’1987-8-29 00:00:00’，95006);
Insert into student(no,name,sex,birthday,class) values(5008，’马可波罗’,’男’，’1987-6-15 00:00:00’，95007);
Insert into student(no,name,sex,birthday,class) values(5009，’牛魔’,’女’，’1987-6-23 00:00:00’，95008);
Insert into student(no,name,sex,birthday,class) values(5010，’张飞’,’女’，’1987-6-24 00:00:00’，95002);
Insert into student(no,name,sex,birthday,class) values(108，’廉颇’,’男’，’1987-6-15 00:00:00’，95007);
Insert into student(no,name,sex,birthday,class) values(109，’韩信’,’男’，’1987-6-23 00:00:00’，95008);
Insert into student(no,name,sex,birthday,class) values(107，’云中君’,’男’，’1987-6-24 00:00:00’，95001);

#以class降序输出student的所有记录（student表全部属性）
select * from student ORDER BY class DESC;
#列出student表中所有记录的name、sex和class列
select name,sex,class from student;
#输出student中不姓王的同学的姓名。
SELECT * from student WHERE name not like '王%';
#输出成绩为85或86或88或在60-80之间的记录（no,cno,degree）
select * from score where degree=85 or degree=86 or degree=88 or degree BETWEEN 60 and 80;
#输出班级为95001或性别为‘女’ 的同学（student表全部属性）
select * from student where class=95001 OR sex='女';
#以cno升序、degree降序输出score的所有记录。（score表全部属性）
select * from score order by cno,degree DESC;
#输出男生人数及这些男生分布在多少个班级中 
select COUNT(*),count(DISTINCT class) FROM student where sex='男';
#列出存在有85分以上成绩的课程编号。
select distinct cno from score where degree>85;
#输出95001班级的学生人数
select COUNT(*) from student where class=95001;
#输出‘3-105’号课程的平均分
select AVG(degree) from score where cno="3-105";
#输出student中最大和最小的birthday日期值
select MIN(birthday),MAX(birthday) FROM student;
#显示95001和95004班全体学生的全部个人信息（不包括选课）。（student表全部属性）
select * from student where class=95001 OR class=95004;