MySQL第二章-相关查询、分组、排序、模糊、分页、连接、函数

知识点一

查询关键字：select

查询语法结构：select  列名from表名where条件  group by(分组)     order by(排序)

聚合函数

Count（*） 统计记录条数  sum（studentresult）统计某一列累加总和,要求列中的数据是数值类型

Max()求最大值，min（）求最小值  avg() 求平均值

聚合函数的特点，针对数据进行统计，返回一个结果值。

排序

排序方式有升序(asc)和降序(desc)，默认情况下是升序。

/*查询所有学员信息按年级升序，再按生日降序*/

select * from student order by gid,birthday desc;

多条件排序原理是在按第一个条件排序后的基础上，再按第二个条件来排序。

分组

分组就是结合聚合函数来对数据进行分组统计. 关键字：group by

根据分组的列把数据进行分组，然后再分别进行统计。

/*统计男女学员各有多少*/

select sex,count(*)  from student group by sex

/*统计每个年级各有多少学员*/

SELECT gid,count(*) from student group by gid;

/*统计每门科目的考试平均分*/

select subjectno,avg(studentresult) from result group by subjectno;

Having:在分组之后的结果中再一次进行数据的筛选。此关键字必须要结合分组来使用。

/*查询哪些学员在科目一参加了补考*/

select studentno, count(*) from result where subjectno  = 1

group by studentno having count(*)>1

模糊查询

模糊查询就是指查询条件不确定的情况。

/*模糊查询

 %: 0-N任意字符

 _: 一个任意字符

 []:区间指定内的一个字符

*/

/*查询姓张的学员  like */

select * from student where name like '张_'

/*查询考试成绩在 70 - 90 之间的数据  between */

select * from result where studentResult>=70 and studentResult<=90;

select * from result where studentResult between 70 and 90;

/*查询没有邮箱的学员信息 is null 查询列值为 null 的数据*/

select * from student where email is null;

/*查询广州和深圳的学员信息  in */

select * from student where address in ('广州','深圳')

分页

只按要求显示部分行数据.

/*实现分页需要的数据有哪一些： 总记录数、每页大小、总页数 */

/*分页 第一个数表示: 跳过多少条，第二个数表示：提取多少条*/

select * from student limit 0,3

连接查询

连接查询：内连接，外连接（左连接，右连接），交叉连接

/*两种方式都是内连接:特点，两个表的前后顺序调换结果没有影响

建议用inner join ... on ...方式，性能好一些

*/

select s.studentno,name,examDate,studentResult from  student s

inner join result r on s.studentNo = r.studentNo;

select s.studentno,name,examDate,studentResult

from result r,student s where s.studentNo = r.studentNo

外连接：左外连接

左外连接：左边的表是主表，右边的表是子表，主表的数据全部显示，子表的数据来填充，如果子表中没有相关的数据填充，则显示NULL。

select * from student s left join result r

on s.studentNo = r.studentNo

/*查询没有参加考试的学员信息*/

select * from student  s left join result r

on s.studentNo = r.studentNo where r.studentNo is null

子查询

/*只有一个条件：'一年级' 要求查询所有一年级的学员信息

分析：取一年级的编号，再把编号作为查询条件在学员表中进行查询

子查询,括号里面的查询会先执行，取得一个结果作为外面查询的条件。

子查询语句可以出现在查询语句的任意地方。*/

select * from student where gid =

(select id from grade where gradename = '一年级');

select * from student s join grade g

on g.id= s.gid where g.gradeName = '一年级'

注意：表连接能实现的功能，子查询都能实现，但子查询实现的功能，表连接不一定能实现。

      当查询结果只是作为查询条件时，选择用子查询，

      当查询结果数据来自于多张表，选择用表连接。

      子查询使用 =、>=、< 、<= 、!=  要保证子查询的返回结果只有一个值，

      如果有多个值返回，则用只能用 in

/*查询参加考试的学员信息  */

select * from student where studentno not in

(select studentno from result where subjectno = 1)

函数

字符串函数、数学函数、日期函数、聚合函数、系统函数

日期函数

总结

作业

/*把密码把所有的 o 变成 0 把所有的 l 变成 1 一句SQL实现*/

update card set password = replace( replace(password,'o','0') ,'l','1')

1. 查询20岁以下的学员信息

2. 查询Java课程的考试成绩

3. 查询女学员信息，包括（学号、姓名、年龄、性别）

4. 查询课时超过60的科目信息

5. 查询班级中姓张的单名的学员信息

6. 查询考试成绩在70-80之间的数据

7. 查询广州、北京的学员信息

8. 查询没有邮箱地址的学员信息     is null       =’’。

9. 查询Java考试后前3名的考试成绩。

10. 分页查询第3页的 学员信息 每页3条。

11. /*查询本月生日的学员信息*/。

12. 查询本周的开卡信息。

13. 统计班级学员的平均年龄。

统计所有科目的总课时数。