mysql做题练习

mysql做题练习

题源:https://blog.csdn.net/qaz13177_58_/article/details/5575711/

文中作者使用的应该是SQL server. 与我使用的mysql有些区别.
例如:
1\mysql不像 SQL Server,写一段SQL,要go了才执行,默认 分号就执行了;
2\ 表student 学生生日Sbirthday 约束 为datetime,输入为1995-09-03,且不加引号,我直接复制输出出现错误ERROR 1292 (22007): Incorrect date value: ‘1963’ for column ‘sbirthday’ at row 1

经查询发现
题目：
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname, ssex, class from student;
出现错误: 不知道sname, ssex 之间是否有逗号.

2、 查询教师所有的单位即不重复的Depart列。

select distinct depart from teacher;
±-----------+
| depart |
±-----------+
| 计算机系 |
| 电子工程系 |
±-----------+
重点 distinct 是对行进行比较去重
3、 查询Student表的所有记录。
SELECT * FROM STUDENT;
4、 查询Score表中成绩在60到80之间的所有记录。
select * from score where degree between 60 and 80;
5、 查询Score表中成绩为85，86或88的记录。
select * from score where degree in(85,86,88);
6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where class = 95031 or ssex =“女”;
select * from student where class = ‘95031’ or ssex =“女”;
select * from student where class = 95031 or ssex =‘女’;
MySql里给varchar赋值要不要 单引号的一点总结
:https://blog.csdn.net/yanzi1225627/article/details/45041363/
MySQL中单引号和双引号的使用
:https://www.cnblogs.com/xuhaojun/p/9145581.html

7、 以Class降序查询Student表的所有记录。
select * from student order by class desc;
8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from score order by cno asc, degree desc;
select * from score order by cno, degree desc; #升序asc可以省略
9、 查询“95031”班的学生人数。
select count (class) from student where class = 95031;
#错误: count和 “(” 间多了空格
select count(class) from student where class = 95031;
select count(class) from student where class = ‘95031’;
select count(*) from student where class = 95031;

select count (*) from student where class = 95031;

10、查询Score表中的最高分的学生学号和课程号。
select sno, cno from student where =(select max(degree) from score
); #错误
select sno, cno from student where degree =(select max(degree) from score ); #错误 ERROR 1054 (42S22): Unknown column ‘cno’ in ‘field list’
select sno, cno ,degree from score where degree =(select max(degree) from score );
11、查询‘3-105’号课程的平均分。
select avg(*) from score where cno=‘3-105’; # 错误
select avg(degree) from score where cno=‘3-105’;
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

1. select cno,avg(degree) from score group by cno having count(*) > 5;
   #查询Score表中至少有5名学生选修的课程的平均分数。

select cno,avg(degree) from score where cno like ‘%3’ group by cno having count(*) > =5;

13、查询最低分大于70，最高分小于90的Sno列。
select sno from score where min(degree)>70 and max(degree)<90;
#错误 ERROR 1111 (HY000): Invalid use of group function

GROUP BY，顾名思义：根据…分组，在SQL中常根据指定字段分组（指定字段内容相同是为一组），然后针对组进行相关操作

WHERE和HAVING的区别在于：
where 子句的作用是对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。
读题:查询sno列,
以sno(学生号)进行分组,sno为字段,值为degree
且值满足最低分大于70，最高分小于90
select sno,group_concat(degree) from score group by sno ;
±----±---------------------+
| SNO | group_concat(degree) |
±----±---------------------+
| 101 | 64.0,85.0 |
| 103 | 86.0,92.0 |
| 105 | 75.0,88.0 |
| 107 | 91.0,79.0 |
| 108 | 78.0,81.0 |
| 109 | 68.0,76.0 |
±----±---------------------+

select sno,group_concat(degree) from score group by sno having min(degree)>70 and max(degree)<90;

14、查询所有学生的Sname、Cno和Degree列。
select * from student inner join score on sno.student = sno.score;
select * from student inner join score on student.sno = score.sno;

select student.sname, score.cno, score.degree from student inner join score on student.sno = score.sno;

15、查询所有学生的Sno、Cname和Degree列。
select score.sno, course.cname, score.degree from course inner join score on score.cno = course.cno;

select course.cname, score.sno, score.degree form score inner join course on score.cno = course.cno;

select course.cname, score.sno, score.degree from course inner join score on score.cno = course.cno;
select course.cname, score.sno, score.degree from course inner join score on course.cno = score.cno;
select a.cname, b.sno, b.degree from course as a inner join score as b on a.cno = b.cno;

15.SELECT A.CNAME, B.SNO,B.DEGREE FROM COURSE AS A JOIN SCORE AS B ON A.CNO=B.CNO ;
16、查询所有学生的Sname、Cname和Degree列。
select student.sname, course.cname, score.degree from student inner join course inner join score on student.sno = score.sno and score.cno = course.cno;
17、查询“95033”班所选课程的平均分。
原来up主答案
SELECT AVG(A.DEGREE) FROM SCORE A JOIN STUDENT B ON A.SNO = B.SNO WHERE B.CLASS=‘95033’;

个人最初想法是想用分组group by 实现,思路及过程如下:
想构成一个表如图
以class为进行分组, group_concat(degree)的值是对应班级所有的分数,因为class 和degree分属两个表.所以尝试关联查询把把两个表整合在一起
select * from student left join score on student.sno = score.sno;

然后通过上面生成的表 使用嵌套语句进行分组:
select class from(
select * from student left join score on student.sno = score.sno) group by class;
#出现错误ERROR 1248 (42000): Every derived table must have its own alias(每一个派生出来的表都必须有一个自己的别名)
修改为:
select class from(
select * from student left join score on student.sno = score.sno) as a group by class;
#ERROR 1060 (42S21): Duplicate column name ‘SNO’
(存在相同列名, student表与score表 sno 相同)
所以需对其中一个表的sno字段进行重命名,此处对score表

select a.sno, a.sname, a.ssex, a.sbirthday, a.class, b.sno as s_sno, b.cno, b.degree from student as a join score as b on a.sno=b.sno;

原本只要"select *" 就好 ,但是需要重命名,所以需要把所有列名打出通过as 重命名,(学艺不精,只能通过这麻烦的方法,有老铁会欢迎赐教).

然后通过嵌套进行分组
select class from(
select a.sno, a.sname, a.ssex, a.sbirthday, a.class, b.sno as s_sno, b.cno, b.degree from student as a join score as b on a.sno=b.sno) as c group by class;

select class, group_concat(degree) from(
select a.sno, a.sname, a.ssex, a.sbirthday, a.class, b.sno as s_sno, b.cno, b.degree from student as a join score as b on a.sno=b.sno) as c group by class;

select class, group_concat(degree) from(
select a.sno, a.sname, a.ssex, a.sbirthday, a.class, b.sno as s_sno, b.cno, b.degree from student as a join score as b on a.sno=b.sno) as c group by class having class=95033;

select class, avg(degree) from(
select a.sno, a.sname, a.ssex, a.sbirthday, a.class, b.sno as s_sno, b.cno, b.degree from student as a join score as b on a.sno=b.sno) as c group by class having class=95033;

18、假设使用如下命令建立了一个grade表：
create table grade(low number(3,0),upp number(3),ranking 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’);
现查询所有同学的Sno、Cno和rank列。

直接照着输入,无法创建表,百度了一下
试了改了一下仍不行,最后问题应该是出在number(x,x)上,mysql不存在number这种字符类型,改成decimal(3,0)或者numeric(3,0)
改成以下即可成功创建
CREATE TABLE grade
(low NUMERIC(3,0) NOT NULL,
upp NUMERIC(3) NOT NULL,
rank VARCHAR(1)NOT NULL);

原答案:
SELECT A.SNO,A.CNO, A.degree, B.RANK FROM SCORE A,GRADE B WHERE A.DEGREE BETWEEN B.LOW AND B.UPP ORDER BY RANK;
#此处疑惑:
1.此处是不是用了关联查询,为什么不见join, 而在score a 和grden之间加了逗号.
.2. 如果关联查询后面不是用on吗? 为什么是where??

试一下把逗号改成join ,结果是一样的,甚至把where改成on,结果也一样.

可参考一下https://blog.csdn.net/xingzhemoluo/article/details/39677891

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select * 
from student
join score
on student.sno = score.sno
where score.cno='3-105'
and score.degree >(
select degree 
from score 
where sno='109' and cno='3-105');

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。(搁置)
原up主答案:

SELECT * FROM score s WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE) GROUP BY SNO HAVING 
COUNT(SNO)>1 ORDER BY DEGREE ;

个人感觉有问题应按照执行顺序先执行SELECT MAX(DEGREE) FROM SCORE),这是从表score里查询所有成绩里最大成绩,而非选了一门课以上的同学 的成绩里最大成绩(也就是某同学多门课里最好成绩)
后面写着写着感觉这表述有问题,有几种理解,评论中也看到了相同观点…就按照第二种理解吧.
按照up主代码输出结果看来一下也不对,如图
思路是想用分组如上图第一个输出表中,去掉group_concat()列的最大值,但是始终想不出怎么实现筛选非最大值.

select * from (select sno from group by sno on count(cno)>1)score   #未完整

看到评论里有

确实,此处不是求最小值,如果2个分数相等,超过3个数据呢??
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select * from score where degree >(select degree from score where sno='109' and cno='3-105');

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

select sname, sno, sbirthday from student a where  year(sbirthday)=(select year(sbirthday) from student where sno='108');

但是这取来的就是108他自己,是否有问题?

select sname, sno, sbirthday from student a where sno!='108' and year(sbirthday)=(select year(sbirthday) from student where sno='108');

23、查询“张旭“教师任课的学生成绩。

select * from (course join teacher on course.tno = teacher.tno) join score on score.cno = course.cno and  where tname = '张旭';

select * from 
course join teacher join score
 on score.cno = course.cno 
 and course.tno = teacher.tno
 where tname='张旭';

24、查询选修某课程的同学人数多于5人的教师姓名。

select teacher.tname from (
score join course on score.cno=course.cno)
join teacher on teacher.tno=course.tno
group by score.cno having count(sno)>5;

25、查询95033班和95031班全体学生的记录。

select * from student where class='95033' or class= '95031';

26、查询存在有85分以上成绩的课程Cno.
select distinct cno from score where degree>85;
27、查询出“计算机系“教师所教课程的成绩表。

select score.sno, score.cno,  score.degree from (
teacher join course on teacher.tno=course.tno) 
join score on score.cno=course.cno
where depart='计算机系';

select sno,Cno ,Degree from
Score where Cno in (
select Cno from
Course where Tno in (
select tno from 
Teacher where Depart='计算机系'))
#1.先查询出计算机系教师tno;.2.再从课程表里查cno,where tno=1查询的结果;3.再从score表查询,where cno=2查询结果.....2个子查询

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

原UP答案:elect tname,prof from teacher where depart=‘计算机系’ and prof not in (select prof from teacher where depart=‘电子工程系’);
该答案是不完整的,如下图
A代表计算机系所拥有的Prof, B代表电子工程系拥有的Prof
题意要求筛选出A+C,原答案只能得出A.

知识点参考https://www.runoob.com/mysql/mysql-union-operation.html

#解1
select tname,prof from teacher 
where 
depart='计算机系' and prof not in (
select prof from 
teacher where depart='电子工程系')
union
select tname,prof from 
teacher where depart='电子工程系' and prof not in (
select prof from 
teacher where depart='计算机系');

解二 *************重点看一下

select * from 
teacher  where depart='计算机系' or depart='电子工程系' 
group by prof having count(*)=1;

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

select * from 
Score where Cno='3-105' 
and Degree >any(
select Degree from 
Score where Cno='3-245') 
order by degree desc;

原up答案不对,没有仅筛选出’3-105’的成绩

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

select * from 
Score where Cno='3-105' 
and Degree >all(
select Degree from 
Score where Cno='3-245') 
order by degree desc;

31、查询所有教师和同学的name、sex和birthday.

select tname,tsex, tbirthday from teacher; 

32、查询所有“女”教师和“女”同学的name、sex和birthday.

select distinct Sname as name,Ssex as sex,Sbirthday as birthday from student where Ssex='女'
union
select distinct Tname as name,Tsex as sex,Tbirthday as birthday from Teacher where Tsex='女';

33、查询成绩比该课程平均成绩低的同学的成绩表。

select Sno,Cno,Degree from 
Score a where a.Degree<(
select AVG(Degree) from
Score b where a.Cno=b.Cno)

有点不能理解改写法…
参考https://zhidao.baidu.com/question/1432184552453540379.html

34、查询所有任课教师的Tname和Depart.
这个题如何判断某老师是否在任教呢?
1.course表中有其tno
2.score表中对应的cno,能对应到表course,再对应到表teacher
(以2理解的参考https://www.cnblogs.com/The-second/p/5979912.html)

以下答题以第一种理解

#解一
select a.tname, a.depart from 
course as b left join teacher as a 
on a.tno=b.tno;
#左连接查询,以course表为左表,该表中有的全显示,右表teacher中有而左表无 以null代替.

#解二
select a.tname, a.depart from 
teacher as a join course as b 
on a.tno=b.tno;

**#解三,改解法和33题有点像**
select tname,depart from teacher a where exists
(select * from course b where a.tno=b.tno);
#解四
SELECT TNAME,DEPART FROM 
TEACHER WHERE TNO IN (SELECT TNO FROM COURSE);

35 查询所有未讲课的教师的Tname和Depart.
select tname, depart from teacher left join course on course.tno=teacher.tno where isnull(course.tno);

select tname, depart from 
teacher left join course 
on course.tno=teacher.tno 
where isnull(course.tno);

解二 注意此法

select tname,depart from 
teacher a where not exists
(select * from course b where a.tno=b.tno);

解三

select tname, depart from 
teacher where tno not in(select tno from course);

36、查询至少有2名男生的班号。

select class from student where ssex='男'group by class having count(*)>=2;

37、查询Student表中不姓“王”的同学记录。

select * from student where sname not like'王%';

38、查询Student表中每个学生的姓名和年龄。
select sname, year(now())-year(sbirthday) from student;
select sname, (year(now())-year(sbirthday))as age
from student;
39、查询Student表中最大和最小的Sbirthday日期值。

select MAX(Sbirthday) as 最大,MIN(Sbirthday) as 最小 from student;

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

select * from student order by Class desc,Sbirthday asc;

41、查询“男”教师及其所上的课程。

select course.cname, teacher.tname from course join teacher on course.tno=teacher.tno and teacher.tsex='男';

42、查询最高分同学的Sno、Cno和Degree列。

select * from score where degree=(select max(degree) from  score);

43、查询和“李军”同性别的所有同学的Sname.

select sname from student where ssex=(select ssex from student where sname='李军') and sname!='李军';

44、查询和“李军”同性别并同班的同学Sname.

select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军')and Sname not in ('李军') ;

45、查询所有选修“计算机导论”课程的“男”同学的成绩表

select a.sno,c.degree from student as a join course as b join score as c on a.sno=c.sno and b.cno=c.cno and cname='计算机导论' where ssex='男';