MySQL子查询
子查询： 在一个查询中又嵌套了其他的查询，那么嵌套的查询就被称为子查询，而外层的查询被称为父查询。子查询可以任意嵌套！可以出现在INSERT UPDATE DELETE WHERE等中…
需求： 查询在高等数学考试中，成绩比张三高的学生的姓名信息
如果不考虑连表查询
SELECT stuId FROM result WHERE subjectId = 1 AND result > 86;
1.查询高等数学的课程编号
SELECT subjectId FROM `subject` WHERE subjectName = '高等数学';
整合
SELECT stuId FROM result WHERE subjectId = ( SELECT subjectId FROM `subject` WHERE subjectName = '高等数学' ) AND result > 86;
2.查询张三的高数考试成绩
SELECT result FROM result WHERE stuId = (SELECT stuId FROM student WHERE stuName = '张三') AND subjectId = (SELECT subjectId FROM `subject` WHERE subjectName = '高等数学');
整合
SELECT r.stuId,stu.stuName FROM result r,student stu WHERE r.stuId = stu.stuId AND subjectId = ( SELECT subjectId FROM `subject` WHERE subjectName = '高等数学' ) AND result > ( SELECT result FROM result WHERE stuId = ( SELECT stuId FROM student WHERE stuName = '张三' ) AND subjectId = ( SELECT subjectId FROM `subject` WHERE subjectName = '高等数学' ) );
使用子查询解决连表查询
要求查询学生姓名、年级名称
原来
SELECT stu.stuName,g.gradeName FROM student stu,grade g WHERE stu.gradeId = g.gradeId;
子查询
SELECT stu.stuName, (SELECT g.gradeName FROM grade g WHERE g.gradeId = stu.gradeId) AS gradeName FROM student stu;
MySQL常用函数
常用的字符串函数
字符串拼接 CONCAT(str1,str2,…)
SELECT CONCAT('这是','MySQL','数据库');
字符串内容替换 REPLACE(str,from_str,to_str)
SELECT REPLACE('这是MySQL数据库','MySQL','Oracle');
去除左侧空格
SELECT LTRIM(' Hello World ');
去除右侧空格
SELECT RTRIM(' Hello World ');
获取字符串长度
SELECT LENGTH('Hello');
截取字符串SUBSTR(str,pos) 索引从1开始
SELECT SUBSTR('Hello World',5);
SUBSTR(str,pos,len)
SELECT SUBSTR('Hello World',5,3);
常用的数学函数
获取随机数 Math.random()
SELECT RAND();
向上取整
SELECT CEIL(20.4);
向下取整
SELECT FLOOR(20.7);
四舍五入
SELECT ROUND(20.5); SELECT ROUND(20.76,1)
常用的时间函数
获取当前时间
SELECT NOW(); SELECT CURRENT_DATE(); SELECT CURRENT_TIME();
获取各种时间信息的函数
SELECT MONTH(NOW()); SELECT YEAR(NOW()); SELECT DAY(NOW()); SELECT HOUR(NOW()); SELECT MINUTE(NOW()); SELECT SECOND(NOW());
日期转换
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
计算时间差额
SELECT DATEDIFF(NOW(),'2019-05-25');
常用的聚合/统计函数
求年级编号最大的
SELECT MAX(gradeId) FROM grade;
求年级编号最小的
SELECT MIN(gradeId) FROM grade;
求平均分
SELECT AVG(result) FROM result WHERE subjectId = 1;
求和
SELECT SUM(result) FROM result WHERE subjectId = 1;
计算学生总人数
SELECT COUNT(*) FROM student; SELECT COUNT(stuId) FROM student; SELECT COUNT(1) FROM student;
分组查询 GROUP BY
注意事项： 在分组查询的查询字段中 不要出现与分组查询中无关的字段值
需求： 计算各个年级的学生人数
SELECT gradeId,COUNT(1) FROM student GROUP BY gradeId;
如果我希望查看 各个年级的总人数 且要求查看对应的学生姓名列表
SELECT gradeId,COUNT(1),GROUP_CONCAT(stuName) FROM student GROUP BY gradeId;
计算各个年级各个性别的人数
SELECT gradeId,gender,COUNT(1) FROM student GROUP BY gradeId,gender;
需求： 查询年级的学生人数大于1人的年级名称
SELECT stu.gradeId,count(1),g.gradeName FROM student stu,grade g WHERE stu.gradeId = g.gradeId GROUP BY gradeId HAVING count(1) > 1;
HAVING和WHERE的区别？
相同点： 都可以用来进行条件判断 筛选行数
不同点：
- 位置不同： WHERE是在分组之前 HAVING是在分组之后
- 条件筛选不同： WHERE是给FROM查询到的数据进行条件筛选 而HAVING是对分组后的数据进行筛选
