mysql-语句

最新推荐文章于 2021-01-19 18:31:25 发布

hn2002

最新推荐文章于 2021-01-19 18:31:25 发布

阅读量1.1k

点赞数

分类专栏：面试文章标签：数据库 c uml c# delete table

本文链接：https://blog.csdn.net/hn2002/article/details/6383020

版权

面试专栏收录该内容

8 篇文章 0 订阅

订阅专栏

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

问题

1、查询“001”课程比“002”课程成绩高的所有学生的学号; w

2、查询平均成绩大于60分的同学的学号和平均成绩;

3、查询所有同学的学号、姓名、选课数、总成绩;

4、查询姓“李”的老师的个数;

5、查询没学过“叶平”老师课的同学的学号、姓名; w

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; w

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; w

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; w

9、查询所有课程成绩小于60分的同学的学号、姓名; w

10、查询没有学全所有课的同学的学号、姓名; w

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名; 同上

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; w

14、查询和“1002”号的同学学习的课程数完全相同的其他同学学号和姓名; w

15、删除学习“叶平”老师课的SC表记录

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、课程编号为2，2号课的平均成绩; w

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,物理,历史,英语,有效课程数,有效平均分 w

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

// 无用

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序像这样的情况使用程序处理效率会更高

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）理由同上

21、查询不同老师所教不同课程平均分从高到低显示同上

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩同上

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 同上

// 无用

24、查询学生平均成绩及其名次

25、查询各科成绩前三名的记录:(不考虑成绩并列情况) // 无用

26、查询每门课程被选修的学生数

27、查询出只选修了一门课程的全部学生的学号和姓名

28、查询男生、女生人数

29、查询姓“张”的学生名单

30、查询同名同性别学生名单，并统计同名同性别人数 w

31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime) // 放

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 w

34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数 w

35、查询所有学生的选课情况，要求学生号，课程号，学生姓名，课程名

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数; w

37、查询不及格的课程，并按课程号从大到小排列 w

38、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名;

39、求选了课程的学生人数

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 w

41、查询各个课程及相应的选修人数

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 w

43、查询每门功成绩最好的前两名 w

44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列,若人数相同，按课程

号升序排列
45、检索至少选修两门课程的学生学号

46、查询全部学生都选修的课程的课程号和课程名

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 w

48、查询两门以上不及格课程的同学的学号及其平均成绩 w

49、检索“004”课程分数小于60，按分数降序排列的同学学号

50、删除“002”同学的“001”课程的成绩

答案

1、select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
from SC where C#='002') b
where a.score>b.score and a.s#=b.s#;

2、select S#,avg(score)
from sc
group by S# having avg(score) >60;

having 不能用于应被用于where字句的条目，select a from b where a>0不能这样，select a from b having a>0

3、

SELECT SC.S, Student.Sname, count( SC.C ) , SUM( SC.score )
FROM Student, SC
WHERE Student.S = SC.S
GROUP BY S

4、select count(distinct(Tname)) from Teacher where Tname like "李%"

distinct 检索出唯一的记录

5、select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');

6、select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');

exists求的是两者的交集,表示存在的意思

http://shenzhenchufa.blog.51cto.com/730213/269152

select Student.S,Student.Sname from Student,SC where Student.S=SC.S and SC.C=1 and exists(select * from SC t1 where t1.S = SC.S and t1.C = 2)

7、select S,Sname from Student where S in (select S from SC ,Course ,Teacher where SC.C=Course.C and Teacher.T=Course.T and Teacher.Tname='叶平' group by S having count(SC.C)=(select count(C) from Course,Teacher where Teacher.T=Course.T and Tname='叶平'));

下面的也是正确的

select Student.S,Student.Sname from Student,SC,Course,Teacher where Student.S = SC.S and SC.C=Course.C and Course.T = Teacher.T and Teacher.Tname = "叶平" group by S having count(-1) = (select count(-1) from Teacher,Course where Course.T = Teacher.T and Tname = "叶平")

8、select S,Sname from (select Student.S,Student.Sname,score,(select score from SC SC_2 where SC_2.S = Student.S and SC_2.C = 2)score2 from Student,SC where Student.S = SC.S and SC.C=1 )S_1 where score2<score

9、select S,Sname from Student where S not in (select distinct(Student.s) from Student,SC where Student.S=SC.S and score>60)

10、select Student.S,Student.Sname from Student,SC where Student.S = SC.S group by Student.S,Student.Sname having count(-1) < (select count(-1) from Course);

11 、select distinct(Student.S),Sname from Student,SC where Student.S = SC.S and SC.C in (select C from SC where S =1)

12 、select distinct(Student.S),Sname from Student,SC where Student.S = SC.S and SC.C in (select C from SC where S =1)

13、update SC set score = (select avg(SC2.score) from SC,(select SC.score,SC.C from SC,Teacher,Course where SC.C=Course.C and Course.T = Teacher.T and Teacher.Tname = '叶平')SC2 where SC.C = SC2.C) ？？

14、select S from SC where s != 2 group by s having count(*)= (select count(*) from sc where s='2')

15、delete from sc where C in (select C from Course,Teacher where Course.T = Teacher.T and Teacher.Tname = "叶平")

16、insert into SC (S,C,score) select S,2,(select avg(score) from SC where C=2) from Student where S not in (select S from SC where C=3)

17、

SELECT S AS 学生ID ,

( SELECT score FROM SC , Course WHERE SC . S = t . S AND Course . C = SC . C AND Course . Cname = '物理' ) AS 物理 ,

( SELECT score FROM SC , Course WHERE SC . S = t . S AND Course . C = SC . C AND Course . Cname = '历史' ) AS 历史 ,

( SELECT score FROM SC , Course WHERE SC . S = t . S AND Course . C = SC . C AND Course . Cname = '英语' ) AS 英语 ,

count ( - 1 ) AS 有效课程 , round(AVG ( t . score ),2) AS 有效平均分
FROM SC AS t
GROUP BY S
ORDER BY AVG ( t . score ) desc

18 、

select C as 课程ID,

(select max(score) from SC where SC.C = t.C) as 最高分,

(select min(score) from SC where SC.C = t.C) as 最低分

from SC as t

group by C

24、select round(avg(score),2) as avgs from sc group by S order by avgs

26、select count(-1) from SC group by C

27、select Student.S,Sname from Student,SC where Student.S = SC.S group by SC.S,Sname having count(-1) <2

一般的都是会把select之后的列都放在group by 后面

28、select count(-1) as 人数,Ssex from Student group by Ssex

29、select Sname from student where Sname like "张%";

30、select Sname,count(-1) from Student group by Sname,Ssex having count(-1) >2

32、select C,avg(score) as avgs from SC group by C order by avgs,C desc

33、select Student.S,Sname,avg(score) from Student,SC where Student.S = SC.S group by Student.S,Sname having avg(score) > 85

34、select Sname,ifnull(score,0) from Student,SC,Course where Course.C = SC.C and Student.S = SC.S and Cname = '物理' and score<60 ifnull函数score,如果不是Null则返回score，如果为null返回0

35、select SC.S,SC.C,Sname,Cname from Student,SC,Course where Student.S = SC.S and SC.C= Course.C

36、select distinct(Sname),Cname,score from Student,SC,Course where Student.S = SC.S and SC.C = Couse.C and score >70

37、select distinct(C) from SC where score < 60 order by C desc

38、select Studnet.S,Sname from Student,SC where C = 3 and score>80 and SC.S = Student .S

39、select count(distinct(S)) from sc;

40、select ST.Sname,score from Student ST,SC ,Course,Teacher where ST.S = SC.S and SC.C = Course.C and Course.T = Teacher.T and Teacher.Tname = '叶平' and score = (select max(score) from SC where C = Course.C)

select ST.Sname,max(score) from Student ST,SC ,Course,Teacher where ST.S = SC.S and SC.C = Course.C and Course.T = Teacher.T and Teacher.Tname = '叶平'

41、select C,count(-1) from SC group by C

42、select distinct(A.S),B.score,A.C from SC A ,SC B where A.Score=B.Score and A.C <>B.C 没做出

43、select C,score from SC t1 where 2 > (select count(*) from SC where C = t1.C and score > t1.score ) order by C,score desc

44、select C as 课程号,count(-1) as 人数 from SC group by C having count(-1) >= 3 order by count(-1) desc , C
45、select S from SC group by S having count(-1) >2

46、

select C,Cname from Course where C in (select C from SC group by C having count(S) = (select count(-1) from Student))

select SC.C,Course.Cname from SC,Course where SC.C = Course.C group by SC.C having count(-1) = (select count(-1) from Student)

47、

select Sname from Student where S not in (select S from Course,Teacher,SC where Course.T=Teacher.T and SC.C= Course.C and Tname='叶平')

下面的是不正确的

select Sname from Student,SC where Student.S = SC.S and SC.C not in (select SC.C from SC,Course,Teacher where SC.C=Course.C and Course.T=Teacher.T and Teacher.Tname = "叶平")

48、这个可以从两个方面来考虑首先解决的是查找平均分的问题，然后再找约束条件就是两门以上不及格课程的同学

select S,avg(score) from SC group by S 这个是符合第一个条件，

select S from SC where score< 60 group by S having count(-1)>2 这是符合第二个条件

组合就是

select S ,avg(score) from SC where S in (select S from SC where score<60 group by S having count(-1) >2) group by S

这样分析起来比较简单，将复杂的问题简单化

49、select S from SC where C = 4 and scors < 60 order by scors desc

50、delete from SC where S = 002 and C = 001

其他
1 排重语句

http://blog.csdn.net/softwave/archive/2009/02/14/3890576.aspx

http://topic.csdn.net/u/20100604/14/af50e4cb-ddf7-443e-abbf-d6ccc1e99d41.html

总结

1 group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by后面。

比如 select name,sum(point) from table_name
这样sql语句会报错，必须写成:
select name,sum(point) from table_name GROUP BY name
就是select后面name没有使用聚合函数所以必须出现在group by 后面

2 聚合函数

http://database.51cto.com/art/201010/229363.htm

3 把 HAVING 加入 SQL 的原因是，WHERE 无法应用于合计函数，而如果没有 HAVING，就无法测试结果条件。
select name,sum(point)
from table_name GROUP BY name
HAVING sum(point)>1000

having通常和group by联合使用

出处 http://www.cnblogs.com/buzaixian/archive/2009/09/21/1571365.html