DQL标准语法结构:编写DQL一定要严格按照此语法的顺序来实现!
select [ALL | DISTINCT] ALL表示查询所有的1内容 DISTINCT去重
{* | 表名.* | 表名.字段名[AS 别名] []} 指定查询出的字段
from
表名[AS 别名] […]
[INNER | [LEFT | RIGHT] [OUTER] JOIN 另一张表名 [AS 别名] ON 关联条件]
[WHERE 条件]
[GROUP BY 分组字段[,…]]
[HAVING 给分组后的数据进行条件筛选]
[ORDER BY 排序字段[,…]]
[LIMIT [startIndex,]pageSize]
**## 1.掌握排序查询 order by**
order by 默认情况下为升序排序即ASC() 降序DESC
select
stu.stuName,sub.subjectName,r.result
from
result r,student stu,`subject` sub
where
r.stuId = stu.stuId AND r.subjectId = sub.subjectId
ORDER BY r.result DESC;
order by r.result DESC;
2.掌握限制查询(分页)limit
select * from student
limit 0,2;
页码和起始索引的计算公式:(页码 - 1) * 显示条数
startIndex = (currentPage - 1) * pageSize;
3.掌握MySQL子查询
select stuId from result
where
subjectId = (
select subjectId from `subject`
where subjectName = '高等数学'
) and result > 86;
4.掌握MySQL常用函数(略)
5.掌握分组查询 group by
注意事项:在分组查询的查询字段中 不要出现与分组查询无关的字段值
需求:计算各个年级的学生人数
select gradeId ,count(1) from student group by gradeId;