数据库学不会是一种什么感受？Mysql查询练习

create database selectTest;
use selectTest;
show tables;
#学生表
#student
#学号
#姓名
#性别
#出生年月日
#所在班级
create table student (
	sno varchar(20) primary key,
    sname varchar(20) not null,
    ssex varchar(20) not null,
    sbirthday datetime,
    class varchar(20) 
	);
    
    
#教师表
#Teacher
#教师编号
#教师姓名
#教师性别
#出生年月日
#职称
#所在部门
SET FOREIGN_KEY_CHECKS = 0;
drop table 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
);

#课程表
#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)
 );


#成绩表
#Score
#学号
#课程号
#成绩
drop table 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)
);

# 数据表中添加数据
#学生表
insert into student values ('101','曾华','男','1977-09-01','95033');
insert into student values ('102','匡明','男','1975-10-02','95031');
insert into student values ('103','王丽','女','1976-01-23','95033');
insert into student values ('104','李军','男','1976-02-20','95033');
insert into student values ('105','王芳','女','1975-02-10','95031');
insert into student values ('106','陆君','男','1974-06-03','95031');
insert into student values ('107','王尼玛','男','1976-02-20','95033');
insert into student values ('108','张全蛋','男','1975-02-10','95031');
insert into student values ('109','赵铁柱','男','1974-06-03','95031');
select * from student;
#添加教师表
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','助教','电子工程系');
select * from teacher;
#课程表
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');
select * from course;
# 成绩表
insert into score values ('103','3-245','86');
insert into score values ('105','3-245','75');
insert into score values ('109','3-245','68');
insert into score values ('103','3-105','92');
insert into score values ('105','3-105','88');
insert into score values ('109','3-105','78');
insert into score values ('103','6-166','85');
insert into score values ('105','6-166','79');
insert into score values ('109','6-166','81');
select * from score;
use selectTest;
#	1.查询student表所有记录。
	select * from student;
#	2.查询student表中 sname，ssex和class列
	select sname,ssex,class from student;
#	3.查询教师所有的单位即不重复的depart列
	# distinct 排除重复
    select distinct dapart from teacher;
    select distinct depart from teacher;
    select * from teacher;
#	4.查询score表成绩在60-80之间的所有记录
	# 操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
	select * from score where degree between 60 and 80;
    select * from score where degree between 60 and 80; #方法1
    #使用运算符比较
	select * from score where degree > 60 and degree < 80; #方法2
#	5.查询score表中成绩为85,86或88的记录
	# 表示 或者关系的查询 in
	select * from score where degree in(85,86,88);
    select * from score where degree in(85,86,88);
#	6.查询student表中‘95031’班或者性别为‘女’的同学记录
	# or 表示或者
    select * from student where class='95031' or ssex='女';
	select * from student where class='95031' or ssex='女';
#	7.以class降序查询student表的所有记录
	#降序 desc
	select * from student order by class desc;
    select * from student order by class desc;
#	8.以cno升序，degree降序查询score表的所有记录
	#升序 asc 系统默认排序是升序
    select * from score order by cno asc,degree desc;
# 	9.查询‘95031’ 班的学生人数
	#统计函数 count（）
    select count(*) from student where class='95031';
#	10.查询score 表中的最高分的学生学号和课程号。（子查询或者排序）
	# MAX 函数返回一列中的最大值。
	select sno,cno,degree from score where degree=(select max(degree) from score);
	select * from score;
    #第一步 找到最高分 
    select max(degree) from score;
    #第二步 找最高分的sno 和 cno
    select sno,cno from score where degree=(select max(degree) from score); 
    # sno 和 cno查询2条最高分数据
    # limlit 第一个数字表示从多少开始
    # 第二个数字表示查询多少条
    select sno,cno,degree from score order by degree desc limit 1,2;
#	11.查询每门课的平均成绩
	select * from course;
    select avg(degree) from score where cno='3-105';
#	在一个sql语句中写
	# group by 表示分组
	select cno,avg(degree) from score group by cno;
#	12.查询score表中至少有2名学生选修的并以3开头的课程的平均分数
	select cno,avg(degree),count(*) from score group by cno
    having count(cno)>=2 and cno like '3%';
#	13.查询分数大于70，小于90的sno列
	select cno,sno,degree from score where
    degree>70 and degree<90;
    select cno,sno,degree from score
    where degree between 70 and 90;
#	14.查询所有学生的 sname,cno 和 degree 列
	#多表查询
	select sname  from student;
	select sno,cno,degree from score;
    select sname,cno,degree from student,score
    where student.sno=score.sno;
#	15.查询所有学生的sno,cname和degree列
	select * from course;
    select cname,sno,degree from course,score
    where course.cno=score.cno;
#	16.查询所有学生的sname,cnam和degree列
	# sname - student
    # cname - course
    # degree - score
    select sname,cname,degree from student,course,score
    where student.sno=score.sno
    and course.cno=score.cno;
	select sname,cname,degree,student.sno,course.cno from student,course,score
    where student.sno=score.sno
    and course.cno=score.cno;
	select sname,cname,degree,student.sno,student.sno as stu_sno,course.cno,course.cno as cou_cno from student,course,score
    where student.sno=score.sno
    and course.cno=score.cno;    
#	17.查询'95031'班学生每门课的平均分
	select * from student;
	select * from student where class='95031';
    select sno from student where class='95031';
    select	* from score where sno in (select sno from student where class='95031');
    select cno,avg(degree) from score where sno in
    (select sno from student where class='95031')
    group by cno;
#	18.查询选修'3-105'课程的成绩高于'109'号同学'3-105'成绩的所有同学的记录
	select degree from score where sno='109' and cno='3-105';
    select * from score where cno='3-105' and 
    degree>(select degree from score where sno='109' and cno='3-105');