把查询效果中全部行中的指定列进行相加
留意：列的数据必须是数值型，不能是字符型，日期型等等，如果对非数值型计算，会报告诫！
select sum(指定列) from 表名;
示例：全部弟子语文成绩的总和
NULL值不到场运算
1.3avg函数：求平均值
select avg(指定列/表达式) as 别名 from 表名;
示例：全部弟子语文成绩的总和 的平均值
示例：全部语文，英语，数学三门成绩总和的平均分
1.4max函数，min函数：求最大值，最小值
select max(指定列) as 别名，min(指定列) as 别名 from 表名;
示例：语文最高分，英语最低分
注释：同一列可以用差别的聚合函数
2.分组查询：group by子句
select 中使用group by子句可以对指定列进行分组查询。需要满足：使用group by子句进行分组长训时，select 指定的字段必须是“分组依据字段（需要分组的列）”，其他列想出现，必须包罗在聚合函数中
#相干代码练习
create table emp (
id bigint primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary decimal(10, 2) not null
);
insert into emp values (null, '马云', '老板', 1500000.00);
insert into emp values (null, '马化腾', '老板', 1800000.00);
insert into emp values (null, 'a哥', '讲师', 10000.00);
insert into emp values (null, 'b哥', '讲师', 12000.00);
insert into emp values (null, 'c姐', '学管', 9000.00);
insert into emp values (null, 'd姐', '学管', 8000.00);
insert into emp values (null, '猪悟能', '游戏脚色', 700.5);
insert into emp values (null, '沙和尚', '游戏脚色', 333.3);
语法：select 分组的列名，聚合函数(指定列),... from 表名 group by 分组的列;
示例：计算差别脚色工资的平均值
留意：round(数值，小数点位数)
示例：round(avg(salary),2)
留意：group by之后可以跟order by子句
3.having 关键字
group by子句进行分组以后，需要对分组效果再进行条件过滤时，不能使用where语句，而是用having语句 where是对表每一行的真实数据进行过滤，where在from之后 having是对分组后，计算出来的效果进行过滤的，having在group by之后
示例：每种脚色的平均工资大于1万小于10万
4.团结查询（MySQL中重点内容）
#相干代码练习
CREATE TABLE `class` (
`class_id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '计算机系2019级1班');
INSERT INTO `class` VALUES (2, '中文系2019级3班');
INSERT INTO `class` VALUES (3, '自动化2019级5班');
-- ----------------------------
-- Table structure for course
-- ----------------------------
CREATE TABLE `course` (
`course_id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'Java');
INSERT INTO `course` VALUES (2, '中国传统文化');
INSERT INTO `course` VALUES (3, '计算机原理');
INSERT INTO `course` VALUES (4, '语文');
INSERT INTO `course` VALUES (5, '高阶数学');
INSERT INTO `course` VALUES (6, '英文');
-- ----------------------------
-- Table structure for student
-- ----------------------------
CREATE TABLE `student` (
`student_id` bigint NOT NULL AUTO_INCREMENT,
`sn` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`mail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`class_id` bigint NULL DEFAULT NULL,
PRIMARY KEY (`student_id`) USING BTREE,
UNIQUE INDEX `sn`(`sn` ASC) USING BTREE,
INDEX `class_id`(`class_id` ASC) USING BTREE,
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '09982', '黑旋风李逵', 'xuanfeng@qq.com', 1);
INSERT INTO `student` VALUES (2, '00835', '菩提老祖', NULL, 1);
INSERT INTO `student` VALUES (3, '00391', '白素贞', NULL, 1);
INSERT INTO `student` VALUES (4, '00031', '许仙', 'xuxian@qq.com', 1);
INSERT INTO `student` VALUES (5, '00054', '不想毕业', NULL, 1);
INSERT INTO `student` VALUES (6, '51234', '好好语言', 'say@qq.com', 2);
INSERT INTO `student` VALUES (7, '83223', 'tellme', NULL, 2);
INSERT INTO `student` VALUES (8, '09527', '老外学中文', 'foreigner@qq.com', 2);
-- ----------------------------
-- Table structure for score
-- ----------------------------
CREATE TABLE `score` (
`score_id` bigint NOT NULL AUTO_INCREMENT,
`student_id` bigint NULL DEFAULT NULL,
`course_id` bigint NULL DEFAULT NULL,
`score` decimal(5, 2) NULL DEFAULT NULL,
PRIMARY KEY (`score_id`) USING BTREE,
INDEX `student_id`(`student_id` ASC) USING BTREE,
INDEX `course_id`(`course_id` ASC) USING BTREE,
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 70.50);
INSERT INTO `score` VALUES (2, 1, 3, 98.50);
INSERT INTO `score` VALUES (3, 1, 5, 33.00);
INSERT INTO `score` VALUES (4, 1, 6, 98.00);
INSERT INTO `score` VALUES (5, 2, 1, 60.00);
INSERT INTO `score` VALUES (6, 2, 5, 59.50);
INSERT INTO `score` VALUES (7, 3, 1, 33.00);
INSERT INTO `score` VALUES (8, 3, 3, 68.00);
INSERT INTO `score` VALUES (9, 3, 5, 99.00);
INSERT INTO `score` VALUES (10, 4, 1, 67.00);
INSERT INTO `score` VALUES (11, 4, 3, 23.00);
INSERT INTO `score` VALUES (12, 4, 5, 56.00);
INSERT INTO `score` VALUES (13, 4, 6, 72.00);
INSERT INTO `score` VALUES (14, 5, 1, 81.00);
INSERT INTO `score` VALUES (15, 5, 5, 37.00);
INSERT INTO `score` VALUES (16, 6, 2, 56.00);
INSERT INTO `score` VALUES (17, 6, 4, 43.00);
INSERT INTO `score` VALUES (18, 6, 6, 79.00);
INSERT INTO `score` VALUES (19, 7, 2, 80.00);
INSERT INTO `score` VALUES (20, 7, 6, 92.00);
4.1内毗连
语法：
#标准写法
select 列名 from 表名1 别名1 [inner] join 表2 别名2 on 毗连条件 and 其他条件;
#个人习惯写法
select 列名 from 表1 别名1,表2 别名2 where 毗连条件 and 其他条件
注释：习惯哪种用哪种！
示例：查询“许仙”同学的成绩（分步骤做这道题）
1.起首确定哪几张表到场查询：成绩表和弟子表；
select *from student,score;
2.根据表与表之间的主外键关系，确定过滤条件
student_id作为主外键关联字段
select *from student,score where student.student_id=score.student_id;
3.确定过滤条件
在where中添加student.name='许仙'的过滤条件
select *from student,score where student.student_id=score.student_id and student.`name`='许仙';
4.精简信息
只需要姓名和分数
select student.`name`,score.score from student,score where student.student_id=score.student_id and student.`name`='许仙';
注释：团结查询详细步骤 1.确定查询中涉及到有那些表。2.对目的表取笛卡尔积。3.确定毗连条件。4.确定对整个效果集的过滤条件。5.精减查询字段
示例：查询全部同学的总成绩和个人信息
select st.student_id,st.`name`,sum(sc.score) as 总分 from student st,score sc where st.student_id=sc.student_id group by sc.student_id;
4.2外毗连
外毗连分为左外毗连和右外毗连。如果团结查询，左侧的表完全显示就是左外毗连，右侧的表全部显示就是右外毗连
语法：
select*from 表1 left(right) join 表2 on 毗连条件;
示例：查询没有考试的同学
select*from student st left join score sc on st.student_id=sc.student_id where sc.score_id is NULL;
3.毗连条件就是student_id雷同
select*from score sc1,score sc2 where sc1.student_id=sc2.student_id;
4.观察效果集，确定过滤条件
1 是Java ，3是计算机原理
要么sc1.course_id=1 and sc2.course_id=3
要么sc1.course_id=3 and sc2.course_id=1
5.加入条件
select*from score sc1,score sc2 where sc1.student_id=sc2.student_id and sc1.course_id=3 and sc2.course_id=1 and sc1.score>sc2.score ;
5.子查询（嵌套查询）
5.1单行子查询
示例：查询与“不想毕业”的同班同学
select *from student where class_id=(select class_id FROM student where `name`='不想毕业');
5.2多行子查询
示例：查询语文和英文成绩信息 （使用到in关键词）
select *from score where course_id in (select course_id from course where `name`='语文' or `name`='英文');
6.exists关键字
语法：select*from 表名 where exists (查询语句);
exists 后面括号中的查询语句，如果有效果正常返回，则执行外层语句；如果返回空，则不执行
相当于if语句的判断条件，有效果返回true，没效果返回false
1.正常返回，因为学号有1的同学
2.返回为空，因为学号没有100的同学
7.合并查询
关键词 union ，union all
语法：select *from 表名1 union/ union all select *from 表名2; union 会去重，union all不会去重
在单表查询推荐使用 or，多表查询不能用or ，就必须用union来毗连
