student學生表數據：

teacher老師表數據：

course課程表數據：

score成績表數據：

1.查詢平均成績大于60分的同學的學生編號和學生姓名和平均成績
select st.StuId,st.StuName as st_name,avg(sc.StuScore) from score as sc,student as st where st.StuId = sc.StuId group by st.StuId having avg(sc.StuScore) > 60;
2.查詢所有學生的學號、姓名、選課數、總成績
select st.StuId,st.StuName,count(sc.StuId),sum(sc.Stuscore) from student as st,score as sc where st.StuId = sc.StuId group by sc.StuId;
3.查詢姓“張”的老師的個數
select count(TeaId) from Teacher where TeaName like ‘張%’;
4.查詢學過“張三”老師所教的所有課的同學的學號、姓名
select Student.StuId,Student.StuName from student where StuId in (select score.StuId from score,course,teacher where score.CourseId = course.CourseId and course.TeaId = teacher.TeaId and Teacher.TeaName = ‘張三’);
5.查詢學過編號為“0001”的課程并且也學過編號為“0002”的課程的學生的學號、姓名
select StuId,StuName from Student where StuId in (select StuId from score where CourseId=’0001’) and StuId in (select StuId from score where CourseId=’0002’)
6.查詢所有課程成績小于等于60分的學生的學號、姓名
select Student.StuId,Student.StuName from student where Student.StuId not in (select Student.StuId from student,score where Student.StuId = Score.StuId and StuScore > 60);
7.查詢沒有學全所有課的學生的學號、姓名
select Student.StuId,Student.StuName from student,score where Student.StuId = Score.StuId group by Student.StuId,Student.StuName having count(courseId) < (select count(courseId) from course);
8.查詢至少有一門課與學號為“0001”的學生所學課程相同的學生的學號和姓名
select Student.StuId,Student.StuName from student,score where Student.StuId = Score.StuId and CourseId in (select courseId from score where StuId = ‘0001’);
9.查詢每門課程被選修的學生數
select Course.courseName,count(Score.courseId) from Score,Course where Score.courseId = Course.courseId group by Score.courseId;
10.查詢出只選修了一門課程的全部學生的學號和姓名
select Student.stuId,Student.stuName from Student,Score where Score.stuId = Student.stuId group by Score.stuId having count(Score.courseId) = 1;
11.查詢平均成績大于85的所有學生的學號、姓名和平均成績
select Student.stuId,Student.stuName,avg(Score.stuScore) from Student,Score where Student.stuId = Score.stuId group by Student.stuId having avg(Score.stuScore) > 85;
12.查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列
select courseId,avg(stuScore) from score group by courseId order by avg(stuScore),courseId desc;
13.查詢課程名稱為“語文”且分數低于60的學生姓名和分數
select Student.stuName,Score.stuScore from Student,Course,Score where Course.courseName = ‘語文’ and Course.courseId = Score.courseId and Score.stuId = Student.stuId and Score.stuScore < 60;
14.查詢任何一門課程成績在70分以上的學生學號、姓名、課程號和分數
select distinct Student.stuId,Student.stuName,Score.courseId,Score.stuScore from Student,Score where Score.stuScore>=70 and Score.stuId = Student.stuId;
15.統計每門課程的學生選修人數，要求輸出課程號和選修人數，查詢結果按人數降序排序，若人數相同，按課程號降序排序
select courseId,count() from score group by courseId order by count() desc,courseId desc;
16.查詢沒學過“張三”老師講授的任一門課程的學生姓名
select stuName from Student where stuId not in(select stuId from Course,Teacher,Score where Teacher.teaName = ‘張三’ and Teacher.teaId = Course.teaId and Course.courseId = Score.courseId);
MongoDB應用
1 創(chuàng)建一年級的第三個班grade_1_3，隨機添加 3 名學生，字段有，name,age，sex,hobby
db.grade_1_3.insert(
[{name:“張三”,age:18,sex:“男”,hobby:[“喝酒”,“音樂”,“電影”]},
{name:“李四”,age:12,sex:“男”,hobby:[“喝酒”,“音樂”,“電影”]},
{name:“王蘭”,age:13,sex:“女”,hobby:[“化妝”,“畫畫”,“觀察”]},
{name:“王五”,age:14,sex:“男”,hobby:[“燙頭”,“打架”,“好人”]},
{name:“趙花”,age:15,sex:“女”,hobby:[“繡花”,“學習”,“鋼琴”]},
{name:“趙六”,age:16,sex:“男”,hobby:[“抽煙”,“拉架”,“勸架”]},
{name:“錢八”,age:14,sex:“男”,hobby:[“電腦”,“游戲”,“吃雞”]},
{name:“錢多”,age:15,sex:“男”,hobby:[“掙錢”,“研究”,“象棋”]},
{name:“周靜”,age:17,sex:“女”,hobby:[“學習”,“跳舞”,“唱歌”]},
{name:“吳小”,age:15,sex:“男”,hobby:[“旅游”,“追星”,“娛樂”]}])
2查看一年級二班grade_1_3中的所有學生
db.grade_1_3.find().pretty()
3查看一年級二班grade_1_3中所有年齡14 歲的學生
db.grade_1_3.find({age:14})
4.查看一年級二班grade_1_3中所有年齡大于7歲并且小于 14 歲的學生
db.grade_1_3.find({age:{$gt:7,$lt:14}})
5.查看一年級二班grade_1_3所有年齡是 14 歲或 6 歲的學生
db.grade_1_3.find({KaTeX parse error: Expected 'EOF', got '}' at position 23: …e:14},{age:16}]}?) 6.查看一年級二班grad…set:{age:8,hobby:[“跳舞”,“畫畫”]}})

總結

以上是生活随笔為你收集整理的MYSQL经典题型和一部分mongodb题的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。