MySQL2-阿里云开发者社区

MySQL1:https://developer.aliyun.com/article/1580063

7.8 分组查询

语法：SELECT列名FROM表名WHERE条件GROUP BY分组依据(列)；

关键字	说明
GROUP BY	分组依据，必须在WHERE之后生效

7.8.1查询各部门的总人数

#思路：
#1.按照部门编号进行分组(分组依据是department.id)
#2.再针对各部门的人数进行统计(count)
SELECT department.id,COUNT(employee_id)
FROM t_employees
GROUP BY department.id;

7.8.2查询各部门的平均工资

#思路：
#1.按照部门编号进行分组(分组依据department.id) o 
#2.针对每个部门进行平均工资统计(avg)。
SELECT department_id , AVG(salary)
FROM t_employees
GROUP BY department_id

7.8.3查询各个部门、各个岗位的人数

#思路：
#1.按照部门编号进行分组(分组依据department一id)。
#2.按照岗位名称进行分组(分组依据job.id)。
#3.针对每个部门中的各个岗位进行人数统计(count) o
SELECT department_id , job_id , COUNT(employee_id) FROM t_employees
GROUP BY department_id , job_id;

7.8.4常见问题

#查询各个部门id、总人数、first_name
SELECT department.id , COUNT(*) , first_name
FROM t_employees
GROUP BY department_id; #error

注：分组查询中，select显示的列只能是分组依据列，或者聚合函数列，不能出现其他列。

7.9 分组过滤查询

语法：SELECT列名FROM表名WHERE条件GROUP BY分组列HAVING过滤规则

关键字	说明
HAVING过滤规则	过滤规则定义对分组后的数据进行过滤

7.9.1统计部门的最高工资

#统计60、70、9。号部门的最高工资
#思路：
#1).确定分组依据(department_id)
#2).对分组后的数据，过滤出部门编号是60、70、9。信息
#3). max()函数处理

SELECT department.id , MAX(salary)
FROM t.employees
GROUP BY department_id
HAVING department.id in (60,70,90)

# group确定分组依据department-id
#having过滤出60 70 90部门
#select查看部门编号和max函数。

7.10 限定查询

SELECT列名FROM表名LIMIT起始行，查询行数

关键字	说明
LIMIT offset_start, row_count	限定查询结果的起始行和总行数

7.10.1查询前5行记录

#查询表中前五名员工的所有信息
SELECT * FROM t_employees LIMIT 0,5;

注意：起始行是从0开始，代表了第一行。第二个参数代表的是从指定行开始查询几行

7.10.2查询范围记录

#查询表中从第四条幵始，查询10行
SELECT * FROM t_employees LIMIT 3,10;

7.10.3 LIMIT典型应用

分页查询：一页显示10条，一共查询三页

#思路：第一页是从。开始，显示10条
SELECT * FROM LIMIT 0,10；

#第二页是从第10条开始，显示条
SELECT * FROM LIMIT 10,10;

#第三页是从20条开始，显示1。条
SELECT * FROM LIMIT 20,10;

经验：在分页应用场景中，起始行是変化的，但是一页显示的条数是不变的

7.11 查询总结

7.11.1 SQL语句编写顺序

SELECT列名FROM表名WHERE条件GROUP BY分组HAVING过滤条件ORDER BY排序列（asc|desc） LIMIT起始行，总条数

7.11.2 SQL语句执行顺序

1.FROM :指定数据来源表
2.WHERE :对查询数据做第一次过滤
3 .GROUP BY :分组
4.HAVING :对分组后的数据第二次过滤
5.SELECT :查询各字段的值
6.ORDER BY :排序
7.LIMIT :限定查询结果

7.12 子查询(作为条件判断)

SELECT列名FROM表名Where条件**（子查询结果）**

7.12.1查询工资大于Bruce的员工信息

#1.先查询到Bruce的工资（一行一列）
SELECT SALARY FROM t_employees WHERE FIRST_NAME = * Bruce* ;#工资是 6000
#2.查询工资大于Bruce的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
#3.将1、2两条语句整合
SELECT * FROM t_employees WHERE SALARY > （SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce'）;

注意：将子查询”一行一列“的结果作为外部查询的条件，做第二次查询

子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断

7.13 子查询(作为枚举查询条件)

SELECT列名FROM表名Where列名in（子查询结果）;

7.13.1查询与名为’King，同一部门的员工信息

#思路：
#1.先查询'King'所在的部门编号（多行单列）
SELECT department_id
FROM t.employees

WHERE last_name = ’King"; //部门编号：80、90
#2.再查询8。、9。号部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM t.employees
WHERE department_id in (80,90);

#3.SQL：合并
SELECT employee_id , first_name , salary , department.id
FROM t.employees
WHERE department_id in （SELECT department.id cf rom t.employees WHERE last_name = ' King' ） ; #N行一列

将子查询”多行一列“的结果作为外部查询的枚举查询条件，做第二次查询

7.13.2工资高于60部门所有人的信息

#1.查询60部门所有人的工资（多行多列）
SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;

#2.查询高于60部门所有人的工资的员工信息（高于所有）
select * from t_employees where SALARY > ALL（select SALARY from t_employees WHERE DEPARTMENT_ID=60）;

#。查询高于60部门的工资的员工信息（高于部分）
select * from t.employees where SALARY > ANY（select SALARY from t_employees WHERE DEPARTMENT_ID=60）;

注意：当子查询结果集形式为多行单列时可以使用ANY或ALL关键字

7.14子查询(作为一张表)

SELECT列名FROM **（子查询的结果集）**WHERE条件；

7.14.1查询员工表中工资排名前5名的员工信息

#思路：
#1.先对所有员工的薪资进行排序（排序后的临时表）
select employee_id , first_name , salary
from t^employees
order by salary desc

#2.再查询临时表中前5行员工信息
select employee_id , first_name , salary
from （临时表）
limit 0,5;

#SQL：合并
select employee_id , first_name , salary
from （select employee_id , first_name , salary from t.employees order by salary desc） as temp limit 0,5;

将子查询”多行多列“的结果作为外部查询的一张表，做第二次查询。

注意：子查询作为临时表，为其赋予一个临时表名

7.15合并查询(了解)

• SELECT* FROM 表名 1 UNION SELECT * FROM 表名 2

• SELECT* FROM 表名 1 UNION ALL SELECT* FROM 表名 2 7.15.1合并两张表的结果（去除重复记录）

7.15.1合并两张表的结果(去除重复记录)

#合并两张表的结果，去除重复记录
SELECT * FROM tl UNION SELECT * FROM t2；

注意：合并结果的两张表，列数必须相同，列的数据类型可以不同

7.15.2合并两张表的结果(保留重复记录)

#合并两张表的结果，不去除重复记录（显示所有）

SELECT * FROM tl UNION ALL SELECT * FROM t2;

经验：使用UNION合并结果集，会去除掉两张表中重复的数据

7.16 表连接查询

SELECT列名FROM表1 连接方式表2 ON连接条件

7.16.1 内连接查询(INNER JOIN ON)

#1 .查询所有有部门的员工信息（不包括没有部门的员工） SQL标准
SELECT * FROM t.employees INNER JOIN t_jobs ON t_employees.J0B_ID = t_jobs.JOB_ID


#2.查询所有有部门的员工信息(不包括没有部门的员工)
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID

经验：在MySql中，第二种方式也可以作为内连接查询，但是不符合SQL标准

**而第一种属于SQL标准，与其他关系型数据库通用**

7.16.2三表连接查询

#查询所有员工工号、名字、部门名称、部门所在国家ID SELECT * FROM t_employees e
INNER JOIN t.departments d
on e.department.id = d.department_id
INNER JOIN t.locations 1
ON d.location.id = 1.location_id

7.16.3 左外连接(LEFT JOIN ON)

#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中，部门名称以NULL填充）
SELECT e.employee_id , e.first一name , e.salary , d.department_name FROM t_employees e 
LEFT JOIN t.departments d
ON e.department_id = d.department_id;

注意：左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果

匹配不到，则返回NULL值填充

7.16.4 右外连接(RIGHT JOIN ON)

#查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以NULL填充） SELECT e.employee.id , e.first_name , e.salary , d.department.name FROM t_employees e 
RIGHT JOIN t_departments d
ON e.department.id = d.department_id;

注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果

匹配不到，则返回NULL值填充

8 DML操作【重点】

8.1 新增(INSERT)

INSERT INTO 表名**(列 1,列 2,列 3…) VALUES(值 1,值 2,值 3…)；**

8.1.1添加一条信息

#添加一条工作岗位信息
INSERT INTO t_jobs(JOB_ID,JOB-TITLE,MIN_SALARY,MAX_SALARY) VALUES('JAVA.Le',1JAVA_Lecturer',2500,9000);

#添加一条员工信息
INSERT INTO 't.employees'
(EMPLOYEE.ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE.DATE,JOB.ID,SALARY,COMMISSION.PCT,MANAGER_ID,DEPARTMENKID )
VALUES
('194','Samuel ' ,'McCain','SMCCAIN', '650.501.3876', '1998-07-01', 'SKCLERK', '3200', NULL, '123', '50');

注意：表名后的列名和VALUES里的值要一一对应(个数、顺序、类型)

8.2 修改(UPDATE)

UPDATE表名SET列1=新值1 ,列2 =新值2, …WHERE条件；

8.2.1修改一条信息

#修改编号为1。。的员工的工资为25000
UPDATE t_employees SET SALARY = 25000 WHERE EMPLOYEE_ID = ' 100' ;


#修改编号为135的员工信息岗位编号为ST_MAN,工资为350。
UPDATE t.employees SET JOB_ID=ST_MAN,SALARY = 3500 WHERE EMPLOYEE_ID = '135':

注意：SET后多个列名二值，绝大多数情况下都要加WHERE条件，指定修改，否则为整表更新

8.3 删除(DELETE)

DELETE FROM 表名 WHERE 条件；

8.3.1删除一条信息

#删除编号为135的员工
DELETE FROM t.employees WHERE EMPLOYEE_ID=1135'；


#刪除姓Peter,并且名为Hall的员工
DELETE FROM t.employees WHERE FIRST_NAME = 1 Peter' AND LAST_NAME=* Hall';

注意：删除时，如若不加WHERE条件，删除的是整张表的数据

8.4清空整表数据(TRUNCATE)

TRUNCATE TABLE 表名；

8.4.1清空整张表

#清空t^countries整张表
TRUNCATE TABLE t_countries；

注意：与DELETE不加WHERE删除整表数据不同，TRUNCATE是把表销毁，再按照原表的格式创建一张新表

MySQL3:https://developer.aliyun.com/article/1580092

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL2