MySQL中的分组查询、子查询（嵌套查询）

分组查询

分组查询是对数据按照某个或多个字段进行分组，在MySQL中使用group by关键字对数据进行分组。分组查询经常会与聚合函数一起使用。

分组查询关键字： group by 分组字段1[,分组字段2,...] [having 条件表达式]
having条件表达式：having是筛选group by后面的数据，having可以对普通字段进行筛选，也可以对聚合函数或者聚合函数的别名进行筛选。

注意：在分组查询中,select 后面的查询字段要么从group by后面的分组字段中选择，要么是聚合函数。

子查询

子查询是一个包含在另一个查询中的查询语句，子查询语句要用小括号括起来

子查询的结果会作为外部查询的基础，子查询是一种非常强大的语句形式，子查询可以嵌套多层，但是要注意不要把查询变得过于复杂。

常用操作
   and 与，表示需要同时满足条件  
   or  或，表示需要至少满足一个条件
   not  非，表示取反
   in  用于指定查询的范围
   any 用于比较子查询返回的多个值中的任何一个值是否满足条件

综合案例演示

#建表

CREATE TABLE emp(
	empno	 INT	 primary  key	 auto_increment,
	ename	 VARCHAR(50),
	job	 VARCHAR(50),
	mgr	 INT,
	hiredate	 DATE,
	sal	 DECIMAL(7,2),
	comm		 DECIMAL(7,2),
	deptno		 INT
);

#插入数据

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

#假如再多添加些数据：
INSERT INTO emp VALUES(9527,'SCOTT','ANALYST',7566,'1987-05-19',1000,NULL,10);
INSERT INTO emp VALUES(9999,'JONES','MANAGER',7839,'1981-04-02',2000,NULL,20);
INSERT INTO emp VALUES(8888,'MARTIN','CLERK',7698,'1982-09-28',1100,1400,30);

INSERT INTO emp VALUES(3456,'ALICE','SALESMAN',7698,'1982-09-28',1100,1400,30);

#执行后图表： 

问题：

-- 查询与SCOTT同一个部门的员工。
     有bug，只适合于名字只有一个SCOTT的情况
     select * from emp where deptno=(select deptno from emp where ename='SCOTT');
   适合不重名和重名的情况（都适合）：
  select * from emp where deptno in (select deptno from emp where ename='SCOTT');

-- 工资高于JONES的员工。
    有bug，只适合于名字只有一个JONES的情况
    select * from emp where sal>(select sal from emp where ename='JONES');
     
    查询记录，大于所有名字为JONES的员工的工资
  select * from emp where sal>all(select sal from emp where ename='JONES'); 
  查询记录，大于所有名字为JONES的任何一个员工的工资
  select * from emp where sal>any(select sal from emp where ename='JONES');
   
-- 工资高于30号部门所有人的员工信息。
  方式一：select * from emp where sal>all(select sal from emp where deptno=30);
   
 方式二：select * from emp where sal> (select max(sal) from emp where deptno=30);

 方式三：select * from emp where sal> (select max(sal) from emp group by deptno having deptno=30);

-- 查询工作和工资与MARTIN（马丁）完全相同的员工信息。
  有bug，只适合于名字只有一个MARTIN的情况
   select * from emp where job=(select job from emp where ename='MARTIN')
    and sal=(select sal from emp where ename='MARTIN');
 
 有逻辑bug，有可能查询出符合多个MARTIN的工作与工资交叉匹配的记录  
 select * from emp where job in (select job from emp where ename='MARTIN')
    and sal in (select sal from emp where ename='MARTIN');

 正确的SQL：
 select * from emp where (job,sal) in 
   (select job,sal from emp where ename='MARTIN');  

-- 查询佣金comm不为null的记录。
  select * from emp where comm is not null;
   

 #练习题1

#1.创建doctor(医生)表,id 是整型自增主键,name 是字符串,salary是工资
CREATE TABLE doctor(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20),
salary INT
);
#2.向表中插入多条记录
INSERT INTO doctor(NAME,salary)VALUES('张三丰',8000);
INSERT INTO doctor(NAME,salary)VALUES('张无忌',2500);
#3.更新工资为2500的记录的工资为5000
UPDATE doctor SET salary=5000 WHERE salary=2500;
#4.查询所有工资大于等于3000的记录
SELECT * FROM doctor WHERE salary>3000;
#5.删除名字为"张三丰"的记录.
DELETE FROM doctor WHERE NAME='张三丰';
#6.删除表
DROP TABLE doctor;

#练习题2

#创建数据表employee。
CREATE TABLE employee
(
e_no        INT NOT NULL PRIMARY KEY,
e_name      VARCHAR(100) NOT NULL,
e_gender    CHAR(2) NOT NULL,
dept_no    INT NOT NULL,
e_job       VARCHAR(100) NOT NULL,
e_salary   SMALLINT NOT NULL,
hireDate   DATE
);
#向employee表中插入数据，SQL语句如下：
INSERT INTO employee 
VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),
(1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),
(1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),
(1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),
(1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'), 
(1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),
(1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'),
(1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2003-05-12'),
(1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'),
(1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'1997-10-12'),
(1011, 'ADAMS', 'm',20, 'CLERK', 1100,'1999-10-05'),
(1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15');
SELECT * FROM employee;
#1.在employee表中，查询所有记录的e_no、e_name和e_salary字段值。
SELECT e_no,e_name,e_salary FROM employee;

#2.在employee表中，查询dept_no等于10和20的所有记录。
SELECT dept_no FROM employee WHERE dept_no IN(10,20);

#3.在employee表中，查询工资范围在800~2500之间的员工信息。
SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;

#4.在employee表中，查询部门编号为20的部门中的员工信息。
SELECT * FROM employee WHERE dept_no=20;
    
#5.在employee表中，查询每个部门最高工资的员工信息。
SELECT *,MAX(e_salary) AS 最高工资 FROM employee GROUP BY e_job;

#6.在employee表中，计算每个部门各有多少名员工。
SELECT dept_no AS 部门,COUNT(dept_no) AS 员工人数 FROM employee GROUP BY dept_no;

#7.在employee表中，计算不同类型职工的总工资数。
SELECT e_job AS 职工,SUM(e_salary) AS 总工资数 FROM employee GROUP BY e_job;

#8.在employee表中，计算不同部门的平均工资。
SELECT dept_no AS 部门,AVG(e_salary) AS 平均工资 FROM employee GROUP BY dept_no;

#9.在employee表中，查询工资低于1500的员工信息。
SELECT * FROM employee WHERE e_salary<1500;

#10.在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。
SELECT * FROM employee ORDER BY dept_no DESC,e_salary DESC;
     
#11.在employee表中，查询员工姓名以字母’A’或’S’开头的员工的信息。
SELECT * FROM employee WHERE e_name LIKE 'A%'OR e_name LIKE'S%';

以上内容如有错误请批评指出。