mysql的多表查询和分组排序

1.分组查询

数据：

#创建数据
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a`  (
  `ID` int(10) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `class_id` int(10) NULL DEFAULT NULL,
  `score` int(10) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
#在表里面添加数据
INSERT INTO `a` VALUES (1, '张三', 1, 90);
INSERT INTO `a` VALUES (2, '李四', 1, 70);
INSERT INTO `a` VALUES (3, '王五', 1, 90);
INSERT INTO `a` VALUES (4, '孙悟空', 1, 80);
INSERT INTO `a` VALUES (5, '猪八戒', 2, 100);
INSERT INTO `a` VALUES (6, '唐三藏', 2, 80);
INSERT INTO `a` VALUES (7, '七煌', 2, 110);
INSERT INTO `a` VALUES (8, '綦江', 2, 80);
INSERT INTO `a` VALUES (9, '小何', 2, 80);
INSERT INTO `a` VALUES (10, '校长', 2, 60);
INSERT INTO `a` VALUES (11, '小梦', 1, 120);
INSERT INTO `a` VALUES (12, '小孟', 2, 150);

（1） order by：就是排序从高到底（在sql里面表示 desc）或者从底到高(在sql里面表示 asc)

下面两个例子：

查询成绩从高到底

select * from `a` order by score desc

查询成绩从底到高

select * from `a` order by score asc

2.聚合函数：

什么是聚合函数：聚合函数指的是对一组值执行计算并返回单一的值

聚合函数有哪些：

• COUNT()。用于统计记录的数量，可以计算整个表中的行数或指定列的非空值数量
• AVG()。计算指定列数值的平均值，忽略空值（NULL）
• SUM()。计算指定列数值的总和，包括空值（NULL）。
• MAX()。返回指定列中的最大值，适用于数值和字符类型。
• MIN()。返回指定列中的最小值，适用于数值和字符类型。

此外，还有一些其他的聚合函数，如STDEV()和STDEVP()（计算标准差）、VAR()和VARP()（计算方差）、FIRST()和LAST()（分别返回分组内的第一个和最后一个值），以及CORR()和COVAR_SAMP()（分别计算相关系数和协方差）。这些聚合函数可以与GROUP BY子句一起使用，以对每个分组进行计算，例子如下：

查询一共有几个人

select class_id,COUNT(*) from `a`

求一共平均数有多少

select avg(score) from `a`

求全部加起来是多少

select sum(score) from `a`

求最大值为多少

select MAX(score) from `a`

求最小值是多少

select MIN(score) from `a`

多表查询：

数据：

-- 创建emp表，并插入数据
create table emp(
	id int auto_increment comment 'ID' primary key,
	name varchar(50) not null comment '姓名',
	age int comment '年龄',
	job varchar(20) comment '职位',
	salary int comment '薪资',
	entrydate date comment '入职时间',
	managerid int comment '直属领导ID',
	dept_id int comment '部门ID'
)comment '员工表';
-- 添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES
	(1, '金庸', 66, '总裁',20000, '2010-01-01', null,5),
	(2, '张无忌', 20, '项目经理',12500, '2015-12-05', 1,1),
	(3, '杨逍', 33, '开发', 8400,'2010-11-03', 2,1),
	(4, '韦一笑', 48, '开发',11000, '2012-02-05', 2,1),
	(5, '常遇春', 43, '开发',10500, '2014-09-07', 3,1),
	(6, '小昭', 19, '程序员鼓励师',6600, '2014-10-12', 2,1),
	(7, '灭绝', 60, '财务总监',8500, '2012-09-12', 1,3),
	(8, '周芷若', 19, '会计',48000, '2016-06-02', 7,3),
	(9, '丁敏君', 23, '出纳',5250, '2019-05-13', 7,3),
	(10, '赵敏', 20, '市场部总监',12500, '2014-10-12', 1,2),
	(11, '鹿杖客', 56, '职员',3750, '2016-10-03', 10,2),
	(12, '鹤笔翁', 19, '职员',3750, '2017-05-09', 10,2),
	(13, '方东白', 19, '职员',5500, '2019-02-12', 10,2),
	(14, '张三丰', 88, '销售总监',14000, '2014-10-12', 1,4),
	(15, '俞莲舟', 38, '销售',4600, '2014-10-12', 14,4),
	(16, '宋远桥', 40, '销售',4600, '2014-10-12', 14,4),
	(17, '陈友谅', 42, null,2000, '2021-10-12', 1,null);

1.内连接

内连接：内连接是两张表的相交的中间部分的数据，内连接包括隐式内连接和显式内连接

隐式内连接:

隐式内连接的格式为：select * from 表1，表2 where 表1的id = 表2的外键

例子如下：

查询部门和人工信息

SELECT * from emp,dept where emp.dept_id= dept.id

 显式内连接:

显式内连接的格式为：SELECT * from 表1 INNER JOIN 表2 ON 表1的外键id = 表2的id

查询部门和人工信息

select * from emp As e INNER JOIN dept AS d ON e.dept_id = d.id

2.外连接：

 左外连接:

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
查询的是左表所有数据以及其交集部分。
 查询的是左表所有数据以及其交集部分。
 查询的是左表所有数据以及其交集部分。
 例子：

查询部门和人工信息:

select *  from emp as e LEFT JOIN dept as d ON e.dept_id = d.id

右外连接

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
 查询的是右表所有数据以及其交集部分。
 查询的是右表所有数据以及其交集部分。
查询的是右表所有数据以及其交集部分。
例子：

查询部门和人工信息:

select * from emp as e  RIGHT JOIN dept as d  ON e.dept_id = d.id

3.全连接

MySQL中的全连接（Full Join）是一种数据库查询，用于合并两个或多个表中的数据，无论这些表之间是否存在匹配的行。如果某些行在其中一个表中没有匹配的行，则结果中对应列将包含NULL值

--全连接的语法格式如下：

SELECT 列名

FROM 表1

FULL JOIN 表2

ON 表1.列名 = 表2.列名。

子查询

查询工资最高的员工信息

--分开写查询得到两个
select  MAX(salary) from emp 
select * from emp where salary = 48000
-- 上面的两个函数组合为一个查询
select * from emp where salary = (select MAX(salary) from emp)

查询员工工资小于平均工资的人

-- 分步写得到
select AVG(salary) from emp
select * from emp WHERE salary <10673.5294
-- 上面的两个查询可以组合为一个查询
select * from emp where salary < (select AVG(salary) from emp)

查询’财务部’和’市场部’所有的员工信息:

SELECT id from dept where name = '财务部' or name = '市场部'
select * from emp where dept_id = 2 or dept_id = 3 
-- 上面最后的一个查询的其他写法
select * FROM emp where dept_id in (2,3)
-- 上面的两个sql查询可以组合成一个查询
select *from emp where dept_id in (select id from dept where name = "财务部" or name = "市场部")

-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 根据可以得到是一个内连接
-- 首先查询在22014-12-12之后入职的

select * from emp where entrydate > '2014-12-12'
-- 隐式内连接子查询
select e.id ,e.name AS '姓名',d.name AS '部门名字',e.dept_id  ,e.age,e.salary,e.managerid,e.job,e.entrydate from dept as d ,(select * from emp where entrydate > '2014-12-12') as e where d.id = e.dept_id LIMIT 2,5
 
-- 显式内连接子查询
select  e.id ,e.name AS '姓名',d.name AS '部门名字',e.dept_id  ,e.age,e.salary,e.managerid,e.job,e.entrydate from dept as d INNER JOIN 
(select * from emp where entrydate > '2014-12-12') as e ON d.id = e.dept_id LIMIT 0,10