Mysql的课后习题 40T

最新推荐文章于 2022-12-08 18:27:52 发布
Allen-1
最新推荐文章于 2022-12-08 18:27:52 发布
阅读量182
点赞数
分类专栏： Mysql 数据库文章标签： mysql
本文链接：https://blog.csdn.net/weixin_44548432/article/details/116544671
版权
Mysql 数据库专栏收录该内容
7 篇文章 0 订阅
订阅专栏
-- 数据库的优化
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫
描，如：
select id from t where num is null
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致
引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or Name = 'admin'
可以这样查询：
select id from t where num = 10
union all
select id from t where Name = 'admin'
5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择

CREATE TABLE STUDENT6
(
SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5)
);

show tables;

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL);

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL);

CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL);

INSERT INTO STUDENT6 (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES 
(108 ,'曾华' ,'男','1977-09-01',95033),
(105 ,'匡明' ,'男','1975-10-02',95031),
(107 ,'王丽' ,'女','1976-01-23',95033),
(101 ,'李军' ,'男','1976-02-20',95033),
(109 ,'王芳' ,'女','1975-02-10',95031),
(103 ,'陆君' ,'男','1974-06-03',95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES 
 ('3-105' ,'计算机导论',825),
 ('3-245' ,'操作系统' ,804),
 ('6-166' ,'数据电路' ,856),
 ('9-888' ,'高等数学' ,100);


INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES 
(103,'3-245',86),
(105,'3-245',75),
(109,'3-245',68),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'3-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-106',79),
(108,'6-166',81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)VALUES 
 (804,'李诚','男','1958-12-02','副教授','计算机系'),
 (856,'张旭','男','1969-03-12','讲师','电子工程系'),
 (825,'王萍','女','1972-05-05','助教','计算机系'),
 (831,'刘冰','女','1977-08-14','助教','电子工程系');

-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname,Ssex,Class from student6;
-- 2、 查询教师所有的单位即不重复的Depart列。
select distinct Depart from teacher;
-- 3、 查询Student表的所有记录。
select * from student6;
-- 4、 查询Score表中成绩在60到80之间的所有记录。
select * from score where degree between 60 and 80;
-- 5、 查询Score表中成绩为85，86或88的记录。
select * from score s where degree =85
union all 
select * from score s where degree =86
union all 
select * from score s where degree =88;

select * from Score where Degree in ('85','86','88');

-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student6 where CLASS='95031' or  SSEX='女';


-- 7、 以Class降序查询Student表的所有记录。
select * from student6 s  order by class desc;

-- 8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from Score order by Cno asc,degree desc;

-- 9、 查询“95031”班的学生人数。
select count(*) from student6 s where class='95031'; 


-- 10、查询Score表中的最高分的学生学号和课程号。

select sno,cno from score where degree =(select max(degree) from score);


-- 11、查询‘3-105’号课程的平均分。
select avg(DEGREE)  from  score s where CNO ='3-105';

-- 11.1 每门课程的平均分
select Cno,AVG(Degree) as 平均分 from Score group by Cno;
-- 11.2 以下写法存疑
-- select Cname from Course where Cno in (select Cno a from Score group by Cno) 
-- union 
-- select Cno,AVG(Degree) from Score group by Cno;

-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。  and count(CNO)>=5
select  avg(DEGREE) from score s where CNO like '3%' ; 

-- having的用法注意
select  avg(`DEGREE`) from score s where CNO like '3%' group by CNO having count(CNO)>=5; 

-- 13、查询最低分大于70，最高分小于90的Sno列。
select sno from score s where `DEGREE` between 70 and 90;


-- 14、查询所有学生的Sname、Cno和Degree列。
select  SNAME ,cno,degree  from student6 s LEFT join score s2 on s2.SNO =s.SNO ;

select Sname,Cno,Degree from student6 s6 join Score s on s6.Sno=s.Sno;

-- 15、查询所有学生的Sno、Cname和Degree列。
select SNO,Cname,degree  from course c ,score s where  c.CNO=s.CNO ;

select Sno,Cname,degree from Score join Course on Course.Cno=Score.Cno;

-- 16、查询所有学生的Sname、Cname和Degree列。
select Sname,Cname,degree from student6 s6 ,course c ,score s
where s6.SNO =s.SNO  and c.CNO =s.CNO ;

select student6.Sname,Cname,degree from student6 
join Score on student6.Sno=Score.Sno 
join Course on Course.Cno=Score.Cno;

-- 17、查询“95033”班所选课程的平均分。
select avg(`DEGREE`) from score s,student6 s6 
where s6.CLASS ='95033' and  s.SNO =s6.SNO ;

select AVG(Degree) from Score where Sno in (select Sno from student6 where Class='95033');



select * from grade g ;

show tables;

create table grade1(low int(3),upp int(3),rank char(1));

-- 18、假设使用如下命令建立了一个grade表：
create table grade1(low int(3),upp int(3),ranking char(1));

drop table grade1;

insert into grade1 values
(90,100,'A'),
(80,89,'B') ,
(70,79,'C') ,
(60,69,'D') ,
(0,59,'E');

--  现查询所有同学的Sno、Cno和rank列。

select s.SNO ,s.CNO ,g.ranking  from grade1 g ,score s where s.`DEGREE`>=low and s.`DEGREE` <=upp;

select Sno,Cno,Degree,ranking from grade1 join Score on Score.Degree between low and upp;

select Sno,Cno,Degree,ranking from Score,grade1 where Degree between low and upp;

-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。        学会拆解目标     step1: 成绩的所有同学的记录        step2:   查询选修“3-105”课程的成绩高于“109”号同学成绩
select * from student6 s6,score s2
where   s2.Cno='3-105'  and s6.SNO =s2.SNO and s2.`DEGREE` >(select `DEGREE` from score s where s.SNO='109'  and s.CNO ='3-105');

select * from student6,Score where Score.Cno='3-105' and student6.Sno=Score.Sno and Score.Degree>(select Degree from Score where Cno='3-105' and Sno='109');
-- 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。step1: 查询score中选学一门以上课程的成绩的记录        step2:   分数为非最高分成绩
-- 一门课程以上   
select * from score s,student6 s2 where s.SNO =s2.SNO and (select count(s.SNO) from score s,student6 s2 where s.SNO =s2.SNO ) >=2;

select * from score s,student6 s2 where s.SNO =s2.SNO  group by s2.SNO having count(s2.SNO)>2 ;


select * from score s where `DEGREE` <(select max(s2.`DEGREE`) from  score s2 where s2.CNO =s.CNO) and SNO in(select SNO from score s3 group by SNO having count(sno)>1);

--  为什么需要加上 s2.cno=s.cno  答：同一门科目中非最高分

select * from Score a where Degree <(select MAX(degree) from Score b where a.Cno=b.Cno) and Sno in(select Sno from Score group by Sno having count(*)>1);
-- 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。   需求的歧义,怎么处理？英文模式
 select * from student6 s6,score s where S6.SNO =S.SNO  and
 S.`DEGREE` >(select S2.`DEGREE` from score s2 where S2.SNO='109' and s2.CNO ='3-105');
 

-- 原版1： 
select * from student6 s6,score s,course c where s.CNO =c.CNO and S6.SNO =S.SNO and s.CNO ='3-105' and
 S.`DEGREE` >(select S2.`DEGREE` from score s2 where S2.SNO='109' and s2.CNO ='3-105');

 select S2.`DEGREE` from score s2,student6 s6 where S6.SNO =S2.SNO and  S6.SNO='109' and s2.CNO ='3-105';
-- 是否可以在原有的基础上进行优化

 select * from student6,Score where student6.Sno=Score.Sno and Score.Degree>(select Degree from Score where Cno='3-105' and Sno='109');
 
-- 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select Sno,Sname,Sbirthday from student6 s where  substring(SBIRTHDAY,1,4)=substring((select SBIRTHDAY from student6 s2 where sno ='108'),1,4);

select Sno,Sname,Sbirthday from student6 where year(student6.Sbirthday)=(select year(Sbirthday) from student6 where Sno='107');

select Sno,Sname,Sbirthday from student6 s6 where year(s6.SBIRTHDAY)=(select year(SBIRTHDAY) from student6 s6 where SNO='108');
-- 23、查询“张旭“教师任课的学生成绩。
  select  s.Sno,s.Cno,Degree from score s,teacher t,course c where t.TNO =c.TNO and c.CNO =S.CNO and t.TNAME ='张旭';

select Degree from Score,Teacher,Course where Teacher.Tname='张旭' and Teacher.Tno=Course.Tno and Course.Cno=Score.Cno;

select Sno,Cno,Degree from Score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname='张旭'));
 
-- 24、查询选修某课程的同学人数多于5人的教师姓名。
select distinct t.TNAME  from teacher t,score s,course c  where c.TNO=t.TNO and c.CNO =s.CNO and s.CNO =(select s.CNO from score s group by CNO having count(CNO)>5);

select Tname from Teacher where Tno in (select Tno from Course where Cno in (select Cno from Score group by Cno having COUNT(*)>5) );
-- 25、查询95033班和95031班全体学生的记录。
select * from student6 s where s.CLASS ='95033' or s.CLASS ='95031';


-- 26、查询存在有85分以上成绩的课程Cno.
select distinct CNO from score s where s.`DEGREE` >85;


-- 27、查询出“计算机系“教师所教课程绩表。
select  s.sno,s.Cno ,Degree from teacher t,course c ,score s where t.TNO =c.TNO and c.CNO =s.CNO and t.DEPART ='计算机系';

select sno,Cno ,Degree from Score where Cno in (select Cno from Course where Tno in (select tno from Teacher where Depart='计算机系'));
-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select distinct t.PROF, t.TNAME  from  teacher t ;

select Tname,Prof from Teacher a where Prof not in(select Prof from Teacher b where a.Depart!=b.Depart);
-- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。v
 select `DEGREE`,s.CNO,s.SNO  from score s where s.CNO='3-105' and `DEGREE`>any (select `DEGREE` from score where CNO='3-245')   order by `DEGREE` desc ;

select `DEGREE` from score where CNO='3-245';

select 	Cno  from Score s where (select `DEGREE` from score b where cno='3-105' and B.SNO=S.SNO)>=(select `DEGREE` from score s1 where cno='3-245' and s1.SNO = s.SNO)  order by `DEGREE` desc ;

select Cno,Sno,Degree from Score a where (select Degree from Score b where Cno='3-105' and b.Sno=a.Sno)>=(select Degree from Score c where Cno='3-245' and c.Sno=a.Sno) order by Degree desc;

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

select `DEGREE`,s.CNO,s.SNO  from score s where s.CNO='3-105' and `DEGREE`>all (select `DEGREE` from score where CNO='3-245')   order by `DEGREE` desc ;


select Cno,Sno,Degree from Score a where (select Degree from Score b where Cno='3-105' and b.Sno=a.Sno)>(select Degree from Score c where Cno='3-245' and c.Sno=a.Sno);

-- 31、查询所有教师和同学的name、sex和birthday.
select distinct tname,tsex,tbirthday,sname,ssex,sbirthday from teacher t,student6 s6,course c ,score s
where s6.SNO =s.SNO and s.CNO =c.CNO and c.TNO =t.TNO ;

-- 通过取别名的方式查看 name,sex和birthday   1.union 和union all的区别以及联系，union 1.筛除重复2.排序  union all则正好相反。 
select tname name,tsex sex,tbirthday birthday from teacher t 
union  select SNAME name,SSEX sex,SBIRTHDAY birthday from student6 s;


-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.
select distinct tname,tsex,tbirthday,sname,ssex,sbirthday from teacher t,student6 s6,course c ,score s
where s6.SNO =s.SNO and s.CNO =c.CNO and c.TNO =t.TNO and TSEX='女' and SSEX='女';

select  tname name,tsex sex,TBIRTHDAY birthday  from teacher t where TSEX='女' union 
select  s.SNAME name,s.SSEX sex,s.SBIRTHDAY birthday  from student6 s where SSEX ='女' ;


-- 33、查询成绩比该课程平均成绩低的同学的成绩表。
select Sno,Cno,`DEGREE` from score s2 where   s2.`DEGREE`<(select  avg(s.`DEGREE`)  from score s where s.CNO =s2.CNO  group by CNO);

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

-- 34、查询所有任课教师的Tname和Depart.
select t.TNAME ,t.DEPART from teacher t where t.PROF <>'助教'; 


select Tname,Depart from Teacher where Tname in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno);


select TNAME,DEPART  from teacher t where TNO  in (select tno from course where CNO in (select distinct cno from score s2 ) );

select Tname,Depart from Teacher where tno in (select tno from course where Cno in (select distinct Cno from Score));

-- 根据表格内部的信息去做选择

-- 35 查询所有未讲课的教师的Tname和Depart.
select t.TNAME ,t.DEPART from teacher t where TNAME not in (select distinct TNAME from  teacher t2,course c,score s where t2.TNO=c.TNO and s.CNO=c.CNO); 

select Tname,Depart from Teacher where Tname not in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno);

select Tname,Depart from Teacher where tno not in (select tno from course where Cno in (select distinct Cno from Score));

-- 36、查询至少有2名男生的班号。
select distinct s.CLASS from student6 s where (select count(SSEX)  from student6 s2)>=2 order by CLASS ; 

select class from student6 s where SSEX ='男' group by CLASS  having count(*)>1; 

select Class FROM student6  where Ssex='男' group by Class having COUNT(*)>1;

-- 37、查询Student表中不姓“王”的同学记录。
select * from  student6 s  where s.SNAME not like '王%';

-- 38、查询Student表中每个学生的姓名和年龄。
select sname,(sysdate()-sbirthday)/(60*60*365*24) from student6 s6 ;

select Sname,YEAR(sysdate())-year(Sbirthday) from student6;-- 学会使用year当前年分获取函数

select sysdate()-'2021-05-06' from student6 s2 ;

-- 39、查询Student表中最大和最小的Sbirthday日期值。
select min(SBIRTHDAY)  岁数max,max(SBIRTHDAY) 岁数min from student6 s6; 


-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
 select * from student6 s order by CLASS desc ,SBIRTHDAY asc ;

-- 41、查询“男”教师及其所上的课程。
select t.TNAME ,c.CNAME from teacher t,course c where t.TSEX ='男' and t.TNO = c.TNO ;

-- 42、查询最高分同学的Sno、Cno和Degree列。
select Sno,Cno,degree from score s where `DEGREE` =(select max(`DEGREE`) from score s2 order by CNO)  ;

-- select  Sno,Cno,degree from  score order by `DEGREE` desc limit 0,1;

-- select max(`DEGREE`) from score s2 order by CNO;

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

select SNAME from student6 s where SSEX=(select SSEX from  student6 s where SNAME ='李军') and SNAME not in('李军');

select SSEX from  student6 s where SNAME ='李军';



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

select SNAME from student6 s where SSEX=(select SSEX from  student6 s where SNAME ='李军') and CLASS =(select CLASS from  student6 s where SNAME ='李军') and SNAME not in ('李军');


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

select s.SNO,degree from score s,course c,student6 s6 where c.CNO=s.CNO and c.CNAME ='计算机导论' and s6.SSEX='男' and s6.SNO =s.SNO ;

select Sno,Degree from Score where Sno in (select Sno from student6 where Ssex='男') and Cno in (select Cno from Course where Cname='计算机导论');
https://www.huaweicloud.com/articles/164825a1f16ff45e612010b2d13beb0e.html 答案出处
SQL 的整体学习，后期最重要的还是看懂题目要求，前期就是基本用法多用不同的方法实现，孰能生巧。