分组查询
分组查询是对数据按照某个或多个字段进行分组,在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%';
以上内容如有错误请批评指出。