sql 高级查询语句总结

数据库建表语句在最后

–这是一个学生成绩管理系统，创建数据库表语句在最后，表包括四张表tblStudent 学生信息表，tblScore 成绩表 ，tblteacher 教师信息表，tblcourse 课程表

–希望大家学会使用mysql数据库，以后工作中使用的数据库是由公司决定，有可能是mysql数据库也有可能是oracel 或者sqlserver数据库，
–但是所有数据库都是基于sql操作，每个数据库都有每个数据库的方言，学会基本的sql语句，接触新的数据库时候学习一下该数据库对比与
–其他数据库不同之处，这样可以快速上手一个新的数据库，更快的进入开发流程。
–今天希望大家学会使用Navicat for MySQL数据库管理工具，这个东西只是mysql数据库管理工具，使用这个工具的前提是先安装mysql数据库。
–以下题目为我个人总结，如果大家还有其他其他题目可以锻炼大家能力或者可以让大家学习到更多知识，欢迎大家提出，希望大家营造一个良好的班级氛围，
–让大家学习到更多知识，提高大家的能力。
–如果大家有任何不懂，可以在论坛中提问或者私聊我，论坛采用匿名制度，有任何问题都可以问，我会第一时间回复大家。
–本次练习对sql掌握要求很高，希望大家多多练习，大多数地方涉及到四表联查，但是和两表联查原理都是一样。

–1、查询“001”课程比“002”课程成绩高的所有学生的学号；
Select StuId From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);

【执行情况】
mysql> Select StuId From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);
+——-+
| StuId |
+——-+
| 1001 |
| 1009 |
| 1010 |
+——-+
3 rows in set

mysql>

使用自连接实现
mysql> select DISTINCT A.StuId from tblscore A,tblscore B
where A.CourseId=’001’ and B.CourseId=’002’ and A.Score>B.score and A.stuid=B.stuid;
+——-+
| StuId |
+——-+
| 1001 |
| 1009 |
| 1010 |
+——-+
3 rows in set

–2、查询平均成绩大于60分的同学的学号和平均成绩；
Select StuId,Avg(Score) as AvgScore From tblScore
Group By StuId
Having Avg(Score)>60;

【执行情况】
mysql> Select StuId,Avg(Score) as AvgScore From tblScore
Group By StuId
Having Avg(Score)>60;
+——-+——————-+
| StuId | AvgScore |
+——-+——————-+
| 1001 | 89.5 |
| 1002 | 69.83333333333333 |
| 1004 | 75.5 |
| 1006 | 71.8 |
| 1007 | 82 |
| 1008 | 68.4 |
| 1009 | 88.4 |
| 1010 | 91 |
+——-+——————-+
8 rows in set

–3、查询学习过‘001’课程的男生和女生人数各是多少？
mysql> select A.StuSex sex,count(*) as CountSex from tblstudent A,tblscore B
where B.courseid=’001’ and A.stuid=B.stuid group by A.StuSex ;
+—–+———-+
| sex | CountSex |
+—–+———-+
| 女 | 5 |
| 男 | 5 |
+—–+———-+
2 rows in set

–4、查询姓“李”的老师的个数；
Select Count(*) From tblTeacher Where TeaName like ‘李%’;
【执行情况】
mysql> Select Count(*) From tblTeacher Where TeaName like ‘李%’;
+———-+
| Count(*) |
+———-+
| 1 |
+———-+
1 row in set

–5、查询“张无忌”的所有学习课程名称和授课老师姓名；

【执行情况】
mysql> select a.stuid,a.stuname,c.CourseName,d.teaname from tblstudent a,tblscore b,tblcourse
c,tblteacher d where a.stuid=b.stuid and b.courseid=c.courseid
and c.teaid=d.teaid and a.stuname=’张无忌’;

+——-+———+————+———+
| stuid | stuname | CourseName | teaname |
+——-+———+————+———+
| 1000 | 张无忌 | 数据库 | 裘千尺 |
| 1000 | 张无忌 | Flash动漫 | 姚明 |
+——-+———+————+———+
2 rows in set

–6、查询每一门课程的课程名称，授课教师姓名，课程平均成绩；

mysql> select avg(b.score) as AvgScore,c.CourseName,d.teaname from tblstudent a,tblscore b,tblcourse
c,tblteacher d where a.stuid=b.stuid and b.courseid=c.courseid
and c.teaid=d.teaid group by b.courseid ;
+——————-+—————-+———-+
| AvgScore | CourseName | teaname |
+——————-+—————-+———-+
| 67.7 | 企业管理 | 叶平 |
| 79.14285714285714 | 马克思 | 赵志敬 |
| 71.375 | UML | 裘千仞 |
| 43.4 | 数据库 | 裘千尺 |
| 70 | 逻辑电路 | 裘千仞 |
| 56.4 | 英语 | 叶开 |
| 72.5 | 电子电路 | 独孤求败 |
| 92 | 毛泽东思想概论 | 孟星魂 |
| 24.5 | 西方哲学史 | 白展堂 |
| 79.25 | 线性代数 | 乔丹 |
| 53 | 计算机基础 | 吕轻侯 |
| 59.5 | AUTO CAD制图 | 花无缺 |
| 97 | 平面设计 | 佟湘玉 |
| 75 | Flash动漫 | 姚明 |
| 85 | Java开发 | 阿紫 |
| 76.66666666666667 | C#基础 | 叶平 |
+——————-+—————-+———-+
16 rows in set

mysql>

–7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId=’001’)>0
And
(Select Count(*) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId=’002’)>0;

【执行情况】
mysql> Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId=’001’)>0
And
(Select Count(*) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId=’002’)>0;
+——-+———+
| StuId | StuName |
+——-+———+
| 1001 | 周芷若 |
| 1002 | 杨过 |
| 1004 | 小龙女 |
| 1009 | 韦小宝 |
| 1010 | 康敏 |
| 1013 | 郭靖 |
+——-+———+
6 rows in set

方法二：
【执行情况】
mysql> SELECT C.stuid,C.stuName from tblStudent C where C.stuid in (SELECT A.stuid from
tblScore A where A.CourseId=’001’) and C.stuid
in(SELECT b.stuid from tblScore b where b.CourseId=’002’);
+——-+———+
| stuid | stuName |
+——-+———+
| 1001 | 周芷若 |
| 1002 | 杨过 |
| 1004 | 小龙女 |
| 1009 | 韦小宝 |
| 1010 | 康敏 |
| 1013 | 郭靖 |
+——-+———+
6 rows in set

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

Select StuId,StuName From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);

【执行情况】
mysql> Select StuId,StuName From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);
+——-+———+
| StuId | StuName |
+——-+———+
| 1001 | 周芷若 |
| 1009 | 韦小宝 |
| 1010 | 康敏 |
+——-+———+
3 rows in set

mysql>
–9、查询已经学完所有课程的同学的学号、姓名；
Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore sc Where st.StuId=sc.StuId)=
(Select Count(*) From tblCourse)
【执行情况】 无结果
mysql> Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore sc Where st.StuId=sc.StuId)=
(Select Count(*) From tblCourse);
Empty set
–10、查询课程补考过的学生学号，课程号；[同一门课程成绩存在两次代表补考]
Select AcourseId,Astuid From tblStudent st,
(Select Count(*) as CountNum,courseId as AcourseId,stuid as Astuid From tblScore sc group by sc.courseId,stuid) A where st.stuid=A.Astuid and A.CountNum>1;

mysql> Select AcourseId,Astuid From tblStudent st,
(Select Count(*) as CountNum,courseId as AcourseId,stuid as Astuid From tblScore sc group by sc.courseId,stuid) A where st.stuid=A.Astuid and A.CountNum>1;
+———–+——–+
| AcourseId | Astuid |
+———–+——–+
| 009 | 1011 |
| 016 | 1013 |
+———–+——–+
2 rows in set

mysql>

–11、查询所有同学的学号、姓名、选课数、总成绩；

mysql> Select StuId,StuName,
(Select Count(DISTINCT CourseId) From tblScore t1 Where t1.StuId=s1.StuId ) as SelCourses,
(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId ) as SumScore
From tblStudent s1;
+——-+———+————+———-+
| StuId | StuName | SelCourses | SumScore |
+——-+———+————+———-+
| 1000 | 张无忌 | 2 | 91 |
| 1001 | 周芷若 |