MySQL之DQL语句，子查询以及常用函数练习

DQL语句，子查询以及常用函数练习

#查询 '数据库结构-1' 的所有考试结果（学号、科目、成绩），并按成绩降序排列
#方法一：使用连接查询
SELECT studentno, r.studentname, studentresult
FROM result r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY studentresult DESC;
#方法二：使用子查询
#子查询的结果是由里到外的
SELECT studentno, r.studentname, studentresult
FROM result 
WHERE subjectno=(SELECT subjectno FROM `subject` WHERE subjectname='数据库结构-1') #一个值可以用 =， 多个值符合的话应该用 IN
ORDER BY studentresult DESC;

#查询课程为 ‘高等数学-2’ 且分数不小于80分的学生的学号和姓名
#方法一：使用连接查询
SELECT r.srudentno, studentname
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-2' AND studentresult>=80;
#方法二：使用连接查询 + 子查询(将需求拆分、细化，逐步完善sql语句)
#1.分数不小于80分的学生的学号和姓名
SELECT r.studentno, studentname
FROM student s 
INNER JOIN result r
ON s.studentno=r.studentno
WHERE studentresult>=80
#2.在上边sql语句基础上，添加需求：查询课程为 ‘高等数学-2’  
AND subjectno=(SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2');
#方法三：完全使用子查询（分步写简单sql语句，然后将其嵌套起来）
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE studentresult>=80 AND subjectno(
SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2'));

#查询 ‘C语言-1’ 的前五名的学生的成绩信息：学号、姓名、分数
SELECT r.studentno, studentname, studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON s.subjectno=sub.subjectno
WHERE subjectname='C语言-1'
ORDER BY studentresult DESC
LIMIT 0,5;

#使用子查询实现：查询张三同学所在的年级名称
SELECT gradename FROM grade WHERE gradeid=(
SELECT gradeid FROM student WHERE studentname='张三');

#常用MySQL函数

#数学函数
SELECT ABS(-8);   #绝对值
SELECT CEILING(9.8);   #大于等于此数的最小的整数
SELECT FLOOR(9.8);   #小于等于此数的最大的整数
SELECT RAND();    #返回0~1之间的随机小数
SELECT RAND(10);    #以某个数作为种子，返回一个固定的随机数
SELECT SIGN(-100);   #符号函数，正数返回1，负数返回-1,0返回0

#字符串函数
SELECT CHAR_LENGTH('好好学习天天向上');   #返回字符串中包含的字符数
SELECT CONCAT('好','好','学','习');   #合并字符串（参数可以有多个）
SELECT INSERT('好好学习天天向上',3,2,'睡觉');   #替换字符串，从某个位置开始，替换某个长度 （如果起始位置超过原字符串长度，则返回原字符串）
SELECT LOWER('ASDFG');   #变成小写
SELECT UPPER('zcxVBnm');   #变成大写
SELECT LEFT('好好学习天天向上',4);   #从左边开始截取某个长度的字符串
SELECT RIGHT('好好学习天天向上',4);   #从右边开始截取某个长度的字符串
SELECT REPLACE('好好学习天天向上','学习','睡觉');   #替换
SELECT SUBSTR('好好学习天天向上',1,4);   #截取，从哪开始截多长
SELECT REVERSE('好好学习天天向上');   #反转字符串

#日期和时间函数
#获取当前日期
SELECT CURRENT_DATE();   
SELECT CURDATE();
#获取当前的日期和时间
SELECT NOW();
SELECT LOCALTIME();
SELECT SYSDATE();
#分别获取日期中的某个部分
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

#系统信息函数
SELECT VERSION();
SELECT USER();

#练习：查询姓张的同学，改成姓李
SELECT REPLACE(studentname,'张','李') AS 新名字 FROM student
WHERE studentname LIKE '张%';


#聚合函数
SELECT COUNT(*) FROM student;   #不推荐写*，效率低，count为非空查询
SELECT COUNT(1) FROM student;   #有时候会写1
SELECT COUNT(studentname) FROM student;   #推荐写直接写列名 非空列

SELECT SUM(studentresult) AS 总和 FROM result;
SELECT AVG(studentresult) AS 平均分 FROM result;
SELECT MAX(studentresult) AS 最高分 FROM result;
SELECT MIN(studentresult) AS 最低分 FROM result;


#综合练习
#查询不同课程的平均分，最高分，最低分（根据不同课程进行分组）
SELECT subjectname, AVG(studentresult) AS 平均分, MAX(studentresult) AS 最高分, MIN(studentresult) AS 最低分
FROM result r INNER JOIN `subject` sub
ON r.subjeactno=sub.subject
GROUP BY r.subjectno   #按什么分组
HAVING 平均分>80   #过滤分组后查询的次要内容
ORDER BY 平均分 DESC   #降序排列
LIMIT 0,5;   #要前5个