MySql（七）子查询与虚表

一、子查询

子查询在主查询前执行一次

主查询使用子查询的结果

子查询要用括号括起来

将子查询放在比较运算符的右边

子查询中不要加ORDER BY子句

对单行子查询使用单行运算符  （如：=、>、>=、<、<=、<>）

对多行子查询使用多行运算符  

>ANY 指大于最小值

<ANY指小于最大值

>ALL 指大于最大值

<ALL指小于最小值

二、虚表

#查询高于自己部门平均工资的员工名字,部门号,工资,平均工资*** 虚表

SELECT emp.ename,emp.deptno,emp.sal, AVG(emp.sal)

FROM emp,(SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ) b

WHERE emp.deptno=b.deptno AND sal>b.avgsal

------------------------示例-----------------

#1.使用标准SQL嵌套子查询和连接查询两种方法查询选修课程名称为'gs'的学生学号和姓名

SELECT stuid,stuname FROM students            #子查询

WHERE stuid IN (SELECT stuid FROM grade WHERE couid = (SELECT couid FROM course WHERE couname = 'gs'))

SELECT s.stuid,s.stuname FROM students s,grade g,course c

WHERE couname = 'gs'                 #多表查询

AND c.couid = g.couid

AND g.stuid = s.stuid

#2 使用标准SQL嵌套语句查询选修课程编号为‘2’的学生姓名和所属班级

SELECT stuname,class FROM students

WHERE stuid IN (SELECT stuid FROM grade WHERE couid = 2)

#3.使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属班级  *******

SELECT stuname,class FROM students

WHERE stuid IN (SELECT stuid FROM grade GROUP BY stuid HAVING COUNT(stuid) = (SELECT COUNT(couid) FROM course))

#4.查询选修了课程的学生人数

SELECT COUNT(DISTINCT stuid) FROM grade

#5.查询选修课程超过3门的学员学号和所属班级 ****

SELECT stuid,class FROM students

WHERE stuid IN (SELECT stuid FROM  grade

GROUP BY stuid

HAVING COUNT(couid) > 3)#得到选修课程超过3的学号