本次多表查询操作如下数据：
学生信息表student
+-----+-----------+-----+-------+--------------+--------------------+
| Id | Name | Sex | Birth | Department | Address |
+-----+-----------+-----+-------+--------------+--------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-----+-----------+-----+-------+--------------+--------------------+
学生成绩表score
+----+--------+-----------+-------+
| Id | Stu_id | C_name | Grade |
+----+--------+-----------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 90 |
| 10 | 906 | 英语 | 85 |
+----+--------+-----------+-------+
多表查询1：
显示员工的工资等级
SELECT emp.ename AS '员工',salgrade.grade AS '工资等级'
FROM emp JOIN salgrade
ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal
ORDER BY salgrade.grade DESC;
显示每个部门的最高工资
SELECT dept.dname AS '部门',MAX(sal) AS '最高工资'
FROM emp JOIN dept
ON dept.deptno = emp.deptno
GROUP BY dept.deptno;
显示每个工作地点的员工数量
SELECT dept.loc AS '工作地点',COUNT(*) AS '员工数量'
FROM dept JOIN emp
ON dept.deptno = emp.deptno
GROUP BY dept.loc;
多表查询2
查询李四的考试科目（c_name）和考试成绩（grade）
SELECT C_name,Grade
FROM score
WHERE Stu_id IN (
SELECT Id
FROM student
WHERE Name='李四'
);
查询李四的考试科目和考试成绩
12.用连接的方式查询所有学生的信息和考试信息
SELECT *
FROM student JOIN score
ON student.Id = score.Stu_id;
连接方式查询所有学生的信息和考试信息
13.计算每个学生的总成绩
SELECT student.Name AS '姓名', SUM(score.grade) AS '总成绩'
FROM student JOIN score
ON student.Id = score.Stu_id
GROUP BY student.Id
ORDER BY SUM(score.grade) DESC;
计算每个学生的总成绩
14.计算每个考试科目的平均成绩
SELECT C_name AS '考试科目', AVG(Grade) AS '平均成绩'
FROM score
GROUP BY C_name
ORDER BY AVG(Grade) DESC;
计算每个考试科目的平均成绩
15.查询计算机成绩低于95的学生信息
SELECT student.Name , student.Sex, student.Birth, student.Department, student.Address, score.C_name, score.Grade
FROM student JOIN score
ON student.Id = score.Stu_id
WHERE score.C_name = '计算机' AND score.Grade < 95;
查询计算机成绩低于95的学生信息
16.查询同时参加计算机和英语考试的学生的信息
查询同时参加计算机和英语考试的学生id：
SELECT a.Stu_id
FROM score AS a JOIN score AS b
ON a.C_name != b.C_name AND a.Stu_id= b.Stu_id
WHERE (a.C_name, b.C_name) IN (('计算机','英语'),('英语','计算机'))
GROUP BY a.Stu_id;
查询同时参加计算机和英语考试的学生id
执行遇到错误：1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'empmgs.a.C_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
百度解决方案：mysql遇见contains nonaggregated column 'information_schema.PROFILING.SEQ'
再次执行：成功。
使用子查询完成查询同时参加计算机和英语考试的学生的信息：
SELECT Name , Sex, Birth, Department,Address
FROM student
WHERE Id IN (
SELECT a.Stu_id
FROM score AS a JOIN score AS b
ON a.C_name != b.C_name AND a.Stu_id= b.Stu_id
WHERE (a.C_name, b.C_name) IN (('计算机','英语'),('英语','计算机'))
GROUP BY a.Stu_id
);
查询同时参加计算机和英语考试的学生的信息
17.从student表和score表中查询出学生的学号，然后合并查询结果
SELECT student.Id, score.Stu_id
FROM student JOIN score
ON student.Id = score.Stu_id;
查询出学生的学号，然后合并查询结果
18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SELECT student.Name, student.Department, score.C_name, score.Grade
FROM student JOIN score
ON student.Id = score.Stu_id
WHERE student.Name LIKE '张%' OR student.Name LIKE '王%' ;
查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
SELECT student.Name, student.Department, score.C_name, score.Grade
FROM student JOIN score
ON student.Id = score.Stu_id
WHERE student.Address LIKE '湖南%' ;
查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
网友评论