1、标准书写样式 (只能按照循序写)
SELECT
....
FROM
....
WHERE (条件)
....
GROUP BY (分组)
....HAVING (筛选)
....
ORDER BY (排序)
....
2、sql执行循序
①、FROM
②、WHERE
③、GROUP BY
④、HAVING
⑤、SELECT
⑥、ORDER BY
3、注意事项:
- sql语句中能用WHERE或者HAVING都能达到结果的,要用尽量WHERE;
- 在使用链接表时能用JOIN链接要用JOIN链接
- HAVING 必须跟在分组函数后
--例子
--1、查询班级下的年龄大于20岁的学生
-- -- sql语句中能用WHERE或者HAVING都能达到结果的,要用尽量WHERE;
SELECT * FROM `user` WHERE age>20 GROUP BY classid
SELECT * FROM `user` GROUP BY classid HAVING age>20
--2、查询年级中大于最小年级的学生
SELECT * FROM `user` u1 WHERE u1.age > (SELECT MIN(u2.age) FROM `user` u2)
SELECT *FROM `user` u1 JOIN (SELECT MIN(nu.age) nage FROM `user` nu) u2 ON u1.age> u2.nage
4、例子
-- 查询每个班级下的平均年龄大于20岁的学生,并且班级不是3,倒叙排序
SELECT
classid,
AVG( age )
FROM
`user`
WHERE
classid != 3
GROUP BY
classid
HAVING
AVG( age ) > 20
ORDER BY
age DESC
建表语句:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字',
`age` int(3) NULL DEFAULT NULL COMMENT '年龄',
`grade` double(5, 2) NULL DEFAULT NULL COMMENT '分数',
`classid` int(11) NULL DEFAULT NULL COMMENT '班级id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', 35, 24.00, 3);
INSERT INTO `user` VALUES (3, '赵六', 23, 23.82, 2);
INSERT INTO `user` VALUES (5, '李小', 34, 23.16, 3);
INSERT INTO `user` VALUES (6, '赵四', 45, 34.00, 2);
INSERT INTO `user` VALUES (7, '小红', 12, 23.98, 1);
INSERT INTO `user` VALUES (8, '赵六', 23, 99.90, 2);
INSERT INTO `user` VALUES (9, 'DAXIE', 32, 34.00, 2);
INSERT INTO `user` VALUES (10, 'xiaoxie', 67, 89.00, 3);
SET FOREIGN_KEY_CHECKS = 1;