mysq-DQL（二）

/*
SELECT
[ALL默认 | DISTINCT去重]
{* | table_name.* | field | table_name.filed AS 别名…}
FROM
table_name AS 别名,…
[INNER | RIGHT | LEFT JOIN table_name2 AS 别名]
[ON 关联条件]
[WHERE 条件]
[GROUP BY 分组字段,…]
[HAVING 分组的筛选条件]
[ORDER BY 排序字段,… ASC | DESC]
[LIMIT 起始的索引,每页显示的数据条数]
*/

– 1.掌握排序查询order by

练习1：将学生信息以成绩升序展示 ASC是升序 DESC是降序
SELECT
*
FROM
result
ORDER BY studentResult DESC;

练习2：将学生信息通过联表查询出来 并按成绩降序展示
SELECT
stu.studentName ‘姓名’,sub.subjectName ‘科目’,
r.studentResult ‘成绩’,r.examDate ‘考试时间’
FROM
result r,student stu,subject sub
WHERE
r.studentNo = stu.studentNo
AND r.subjectNo = sub.subjectNo
ORDER BY r.studentResult DESC

**练习3：**要求将上述内容 再添加时间降序
注意：如果涉及多个字段 每个字段必须进行排序指定
当多个字段时 会按照字段的顺序 进行优先排序
SELECT
stu.studentName ‘姓名’,sub.subjectName ‘科目’,
r.studentResult ‘成绩’,r.examDate ‘考试时间’
FROM
result r,student stu,subject sub
WHERE
r.studentNo = stu.studentNo
AND r.subjectNo = sub.subjectNo
ORDER BY r.studentResult DESC,r.examDate ASC

– 2.[掌握]Limit的使用(分页) 仅在MySQL中存在

LIMIT 数据起始索引,数据显示的条数

查询第一页的数据 每一页显示的数据条数为2条limit 0,2
SELECT
*
FROM
category
LIMIT 0,2;或
LIMIT 2;

查询第三页的数据 每一页显示的数据条数为2条
起始索引和页数的公式：(页码/当前页 - 1) * 每页显示条数

第一页：(1 - 1) * 2
第三页：(3 - 1) * 2
SELECT
*
FROM
category
LIMIT 4,2;

SQL查询语句中的 limit 与 offset 的区别：

• limit y 分句表示: 读取 y 条数据
• limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
• limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

比如分页获取数据：

第1页： 从第0个开始，获取20条数据

select * from testtable limit 0, 20; 
select * from testtable limit 20 offset 0;  
12

第2页： 从第20个开始，获取20条数据

select * from testtable limit 20, 20; 
select * from testtable limit 20 offset 20;  
12

第3页： 从第40个开始，获取20条数据

select * from testtable limit 40, 20;  
select * from testtable limit 20 offset 40;  

**练习：**查询学号、姓名、科目、成绩、时间 按成绩和时间排序
分页显示 每页显示1条
SELECT
r.studentNo ‘学号’,stu.studentName ‘姓名’,
sub.subjectName ‘科目’,r.studentResult ‘成绩’,
r.examDate ‘时间’
FROM
result r,student stu,subject sub
WHERE
r.studentNo = stu.studentNo
AND
r.subjectNo = sub.subjectNo
ORDER BY r.studentResult DESC,r.examDate DESC
LIMIT 2,1;

– 3.掌握MySQL子查询( 父查询 ) (嵌套查询)

一个查询语句中 嵌套(可以嵌套n层)另一个查询语句

笛卡尔积

SELECT
sub.subjectName,g.gradeName
FROM
subject sub,grade g
WHERE
sub.gradeId = g.gradeId;

采用子查询做连接查询 子查询可以放在查询语句的任何地方
SELECT
sub.subjectName ‘科目’,
(SELECT g.gradeName FROM grade g WHERE g.gradeId = sub.gradeId) ‘年级’
FROM
subject sub;

采用子查询作为多查询语句的联合

练习：查询高等数学成绩比贾思文高的学生信息
1.查询高等数学的对应编号
SELECT sub.subjectNo FROM subject sub WHERE sub.subjectName = ‘高等数学’;
2.查询贾思文的学生编号
SELECT stu.studentNo FROM student stu WHERE stu.studentName = ‘贾斯文’;
3.查询贾思文的成绩
SELECT r.studentResult FROM result r WHERE r.studentNo = 1003;

优化3 使用子查询
SELECT
r.studentResult
FROM
result r
WHERE
r.studentNo = (SELECT stu.studentNo FROM student stu WHERE stu.studentName = ‘贾斯文’);

组合信息(子查询)

SELECT
(SELECT stu.studentName FROM student stu WHERE r1.studentNo = stu.studentNo) ‘姓名’
FROM
result r1
WHERE
r1.studentResult > (
SELECT
r.studentResult
FROM
result r
WHERE
r.studentNo = (SELECT stu.studentNo FROM student stu WHERE stu.studentName = ‘贾斯文’)
)
AND
r1.subjectNo = (SELECT sub.subjectNo FROM subject sub WHERE sub.subjectName = ‘高等数学’);

– 4.掌握MySQL的常用函数

字符串函数

替换 REPLACE(str,from_str,to_str)

SELECT REPLACE(‘MySQL数据库’,‘MySQL’,‘Oracle’)

拼接 CONCAT(str1,str2,…)

SELECT CONCAT(‘MySQL’,‘数据’,‘库’)

截取 SUBSTR(str,pos) 位置从1开始 SUBSTR(str,pos,len) 从pos开始截取指定len

SELECT SUBSTR(‘这是MySQL数据库’,3)
SELECT SUBSTR(‘这是MySQL数据库’,3,5)

去除空格

SELECT LTRIM(’ hehe ‘)
SELECT RTRIM(’ hehe ')

首字母大写

SELECT CONCAT(UPPER(SUBSTR(‘hehe’,1,1)),SUBSTR(‘hehe’,2))

全部字母大写

SELECT UPPER(‘hehe’)

全部字母小写

SELECT LOWER(‘HH’)

时间函数

SELECT NOW()
SELECT DAYOFWEEK(NOW())
SELECT DAYOFMONTH(NOW())
SELECT DAYOFYEAR(NOW())
SELECT HOUR(NOW())
SELECT YEAR(NOW())
SELECT MONTH(NOW())

数学函数

四舍五入

SELECT ROUND(3.14)
SELECT ROUND(3.15,1)

向上取整

SELECT CEIL(3.4)

向下取整

SELECT FLOOR(3.5)

获取随机数

SELECT RAND();

获取随机整数

公式：(int)(Math.random() * (max - min)) + min

FLOOR(rand() * (max - min)) + min

[9,20)

select FLOOR(rand() * (20 - 9)) + 9

掌握常用聚合/统计函数

求最大值

SELECT MAX(studentResult) FROM result;

最小值

SELECT MIN(studentResult) FROM result;

求个数

SELECT COUNT(*) FROM result;
SELECT COUNT(1) FROM result;
SELECT COUNT(studentNo) FROM result;

求平均分

SELECT AVG(studentResult) FROM result;

求和

SELECT SUM(studentResult) FROM result;

– 5.掌握MySQL的分组查询group by

计算各科目的考试平均分

SELECT
subjectNo ‘科目编号’,
(SELECT sub.subjectName FROM subject sub WHERE sub.subjectNo = r.subjectNo) ‘科目名称’,
AVG(studentResult) ‘平均分’,
MAX(studentResult) ‘最高分’
FROM
result r
GROUP BY subjectNo
HAVING AVG(studentResult) > 60;

WHERE和HAVING的区别：
WHERE是对select from的基础查询进行条件筛选
HAVING是对group by分组后的数据进行筛选

WHERE必须放在from后 group by前
HAVING必须放在group by后

WHERE不能接聚合函数