mysql练习题

练习来源于B站小伙伴的分析，下面是我对练习题的整理

准备工作

create database mysqltest;
show databases;
use mysqltest

create table student(
	s_no varchar(20) primary key comment '学生学号',
	s_name varchar(20) not null comment'学生姓名 不能为空',
	s_sex varchar(10) not null comment'学生性别',
	s_brithday datetime comment'学生生日',
	s_class varchar(20) comment'学生所在班级'
)ENGINE =INNODB CHARSET =UTF8;
drop table student
select * from student;

create table teacher(
	t_no varchar(20) primary key comment'教师编号',
	t_name varchar(20) not null comment'教师姓名',
	t_sex varchar(20) not null comment'教师性别',
	t_birthday datetime comment'教师生日',
	t_rof varchar(20) not null comment'教师职称',
	t_depart varchar(20) not null comment'教师所在的部门'
)ENGINE =INNODB CHARSET =UTF8;
drop table teacher;
select * from teacher;


create table course(
	c_no varchar(20) primary key comment'课程号',
	c_name varchar(20) not null comment'课程名称',
	t_no varchar(20) not null comment'教师编号 外键关联teacher表',
	foreign key(t_no) references teacher(t_no)
)ENGINE =INNODB CHARSET =UTF8;
drop table course;
select * from course;


create table score(
	s_no varchar(20) not null comment'成绩表的编号 依赖学生学号',
	c_no varchar(20) not null comment'课程号 依赖于课程表中的c_id',
	sc_degree decimal,
	foreign key(s_no) references student(s_no),
	foreign key(c_no) references course(c_no),
	primary key(s_no,c_no)
)ENGINE =INNODB CHARSET =UTF8;
drop table score
select * from score


#--学生表数据
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');
INSERT INTO student VALUES('110','张飞','男','1974-06-03','95038');



#--教师表数据
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-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','76');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');
INSERT INTO score VALUES('101','3-105','90');
INSERT INTO score VALUES('102','3-105','91');
INSERT INTO score VALUES('104','3-105','89');


#查看创建的表以及结构
show tables
describe course
describe score
describe student
describe teacher
select * from course;
select * from score;
select * from student;
select * from teacher;

下面开始练习

#1.查询student表中所有的记录
select * from student

#2.查询student表中所有记录的s_name,s_sex和s_class列
select s_name, s_sex,s_classfrom student

#3.查询教师所有的单位但是不重复的t_depart列
select distinct (t_depart) from teacher 

#4.查询score表中成绩在60-80之间所有的记录(sc_degree)
select * from score where  sc_degree between 60 and 80

#5.查询score表中成绩为85, 86, 或者88的记录(sc_degree)
select * from score where sc_degree in(85,86,88)

#6.查询student表中'95031'班或者性别为'女'的同学记录
select * from student where s_class='95031' or '女'

#7.以class降序查询student表中所有的记录
#注意：desc是降序   上高下低
select * from student order by s_class desc

#8.以c_no升序.sc_degree降序插叙score表中所有的数据
select * from score order by c_no asc,sc_degree desc

#9.查询'95031'班的学生人数
select count(*) from student where s_class='95031'

#10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)
select s_no,c_no from score where sc_degree=(select max(sc_degree) from score)

select s_no,c_no from score order by sc_degree desc limit 1

#11.查询每门课的平均成绩
select * from score;
select avg(sc_degree),c_no from score group by c_no

#12,查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
#着重了解一下
#其实就是在一张表上，先按学生分组，然后再查询课程数量大于2，且3开头的
select * from score;
select avg(sc_degree),c_no from score group by s_no having count(c_no)>2 and c_no like '3%'

#13.查询分数大于70但是小于90的s_no列:
select s_no from score where sc_degree>70 and sc_degree<90

#14.查询所有的学生 s_name , c_no, sc_degree列
select s_name,c_no,sc_degree from student s1,score s2 where s1.s_no=s2.s_no

#15.查询所有学生的s_no, c_name, sc_degree列
select s.s_no, c.c_name, s.sc_degree
from score s,course c
where s.c_no=c.c_no

#16.查询所有的学生 s_name , c_name, sc_degree列
select s_name , c_name, sc_degree from course c,score s1,student s2 where c.c_no=s1.c_no and s1.s_no =s2.s_no

#17.查询班级是'95031'班学生每门课的平均分
#因为是查询每门课的平均分，所以在算平均分之前还需要按课程分组一下
select avg(sc_degree) from score 
where s_no in (select s_no from student where s_class='95031')
group by c_no

#18.查询选修"3-105"课程的成绩高于'109'号同学'3-105'成绩 的所有同学的记录
#着重看一下，为什么我做不对
#首先我们查询需要比较的同学的成绩，然后发现学生表里没有成绩，我们就把成绩表和学生表关联，最后我们查询关联后表里成绩大于目标同学的就可以了
select * from student s1,score s2 
where s2.s_no=s1.s_no 
and c_no='3-105'
and sc_degree>(
	select sc_degree from score where s_no='109' and c_no='3-105'
)

#19.查询成绩高于学号为'109',课程号为'3-105'的成绩的所有记录
select * from score
where sc_degree>(
	select sc_degree from score where s_no='109' and c_no='3-105'
)

#20.查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday
#先查询出符合条件的年份
select s_no,s_name,s_brithday
from student 
where year(s_brithday) in(
	select year(s_brithday) from student where s_no in ('108','101')
)

#21.查询 张旭 教师任课的学生的成绩
#错误想法：希望建立三表连接，发现某些没有用到的字段就没有信息
#正确做法：1.查询张旭老师t_no 2.查询课程表里张旭老师对应c_no 3.查询学生里对应c_no
select t_no from teacher where t_name='张旭'

select c_no from course where t_no=(
	select t_no from teacher where t_name='张旭'
)

select * from score s1,student s2 
where s1.s_no=s2.s_no
and c_no =(
	select c_no from course where t_no =(
		select t_no from teacher where t_name='张旭'
	)
)


#另一种做法
select * 
from student 
where s_no IN (
	SELECT s_no FROM score WHERE c_no = (
		SELECT c_no FROM course WHERE t_no = (
			SELECT t_no FROM teacher WHERE t_name='张旭'
			)
	 )
);

#总结我的问题，如果使用了多表查询可能会出现查询过多信息的问题，
#现在我们使用一层一层的，虽然繁琐，但信息比较受限制，不过问题不大
#注意如果可以保证查询到的是唯一的就大胆使用=


#22.查询选修课程的同学人数多余 5 人的教师姓名
#在成绩表里按课程编号分组，分组后计算不同学生数量>5    --------注意非常巧妙
#先查询出选项课程>5的课程号
select c_no from score group by c_no having count(s_no)>5
#再按课程号找出老师的编号
select t_no from course where c_no =(
	select c_no from score group by c_no having count(s_no)>5
)
#通过老师编号查找出老师名字
select t_name from teacher where t_no=(
	select t_no from course where c_no =(
		select c_no from score group by c_no having count(s_no)>5
	)
)


#23.查询95033班和95031班全体学生的记录
select * from student where s_class in('95033','95031')


#24.查询存在85分以上成绩的课程c_no
select c_no from score where sc_degree>85

#25.查出所有'计算机系' 教师所教课程的成绩表
select t_no from teacher where t_depart='计算机系'

select c_no from course where t_no in(select t_no from teacher where t_depart='计算机系')

select * from score where c_no in(select c_no from course where t_no in(select t_no from teacher where t_depart='计算机系')
)

#26.查询'计算机系'与'电子工程系' 不同职称的教师的name和rof
#目标:查询两个专业里与另一个表里不重复的职称
#判断是同一个表里的，然后发现最好在一个专业里去查询，然后交叉的判断会好一点
#首先查询职称
select t_rof from teacher where  t_depart='电子工程系'
select * from teacher where t_depart='计算机系' and t_rof not in(select t_rof from teacher where  t_depart='电子工程系')

select t_rof from teacher where  t_depart='计算机系'
select * from teacher where t_depart='电子工程系' and t_rof not in(select t_rof from teacher where  t_depart='计算机系')


#最后把他们union
select * from teacher where t_depart='计算机系' and t_rof not in(select t_rof from teacher where  t_depart='电子工程系')
union
select * from teacher where t_depart='电子工程系' and t_rof not in(select t_rof from teacher where  t_depart='计算机系')


#27查询选修编号为"3-105"课程且成绩至少高于选修编号为'3-245'同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序
select min(sc_degree) from score where c_no='3-245'

select c_no,s_no,sc_degree from score 
where c_no='3-105'
and sc_degree >(select min(sc_degree) from score where c_no='3-245')
order by sc_degree desc

#28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和sc_degree
select max(sc_degree) from score where c_no='3-245'

select c_no,s_no,sc_degree
from score
where c_no='3-105'
and sc_degree>(select max(sc_degree) from score where c_no='3-245')


#29. 查询所有教师和同学的 name ,sex, birthday
select * from teacher
union
select * from student
#上面语法不通过，列不同，所以错误

select *
from teacher t,course c,student st,score sc
where t.t_no=c.t_no and c.c_no=sc.c_no and st.s_no=sc.s_no
#上面推论并不对，如果多表查询我们只能查询到同学以及对应老师信息表


#注意：题目要求查询所有教师同学的信息，即教师同学在同一列里
#按照以前方法并不容易实现，现在我们使用union完成
#只要字段相似都可以使用union----所以我们要使用别名
select s_name as name,s_sex as sex,s_brithday as brithday
from student
union
select t_name as name,t_sex as sex,t_birthday as brithday
from teacher


#30.查询所有'女'教师和'女'学生的name,sex,birthday
select s_name name,s_sex sex,s_brithday birthday 
from student 
where s_sex='女'
union
select t_name name,t_sex sex,t_birthday birthday
from teacher
where t_sex='女'

#31.查询成绩比该课程平均成绩低的同学的成绩表
#注意：这题的思路：首先我们的表看成两张表，第一张里的成绩和第二张表里查询出的平均成绩比较，注意他们之间的联系是共有的字段
#其实就是自连接
#在同一张表里有用到的数据以及字段不同时，我们就需要使用自连接
#下面是正确做法
#自连接
SELECT * FROM score AS sc1 
WHERE sc1.sc_degree < (
	SELECT  AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no
);

#32.查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到)
#题目就是有分数表关联的老师有哪些
select t_no from course
select * from teacher where t_no in(select t_no from course)


#33.查出至少有2名男生的班号
#先查出男生（这样查询出来的都是男生的），再分组，然后我们就可以按学号算数量了
#总结：where对表一次筛选，having对已经筛选过的表按组二次筛选
select * from student 
where s_sex='男'
group by s_class
having count(s_no)>2

#34.查询student 表中 不姓"王"的同学的记录
select * from student where s_name not like '王%'

#35. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)
#求年龄的公式：year(now())-year(出生日期)
select s_name,YEAR(NOW())-YEAR(s_brithday) as age from student 

#36. 查询student中最大和最小的 s_birthday的值
select max(s_brithday),min(s_brithday) from student 

#37.以班级号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by s_class desc,s_brithday desc 

#38.查询"男"教师 及其所上的课
select t_no from teacher where t_sex='男'
select * from course where t_no in(select t_no from teacher where t_sex='男')

#39.查询最高分同学的s_no c_no 和 sc_degree;
select max(sc_degree) from score
select s_no,c_no,sc_degree from score where sc_degree=(select max(sc_degree) from score)

#40. 查询和"李军"同性别的所有同学的s_name
select s_name,s_sex from student where s_sex='男'

#41.查询和"李军"同性别并且同班的所有同学的s_name
select s_name,s_sex from student where s_sex='男' group by s_class

#42. 查询所有选修'计算机导论'课程的'男'同学的成绩表
select c_no from course where c_name='计算机导论'
select s_no from student where s_sex='男' 

select * from score 
where c_no in(select c_no from course where c_name='计算机导论')
and s_no in(select s_no from student where s_sex='男' )

#43. 假设使用了以下命令建立了一个grade表
#CREATE TABLE grade(
#    low INT(3),
#    upp INT(3),
#    grade CHAR(1)
#);
#INSERT INTO grade VALUES(90,100,'A');
#INSERT INTO grade VALUES(80,89,'B');
#INSERT INTO grade VALUES(70,79,'c');
#INSERT INTO grade VALUES(60,69,'D');
#INSERT INTO grade VALUES(0,59,'E');
select * from grade

#查询所有同学的s_no , c_no 和grade列
######总结字段我们也是可以使用的
SELECT s_no, c_no , grade 
from score, grade 
where sc_degree between low and upp;