那些年我们一起做过的[分组查询]_MySQL

分组查询

在SQL Server中使用的分组查询是ORDER BY子句，使用ORDER BY子句要同聚合函数配合使用才能完成分组查询，在SELECT查询的字段中如果字段没有使用聚合函数就必须出现在ORDER BY子句中（即SELECT后边的字段名要么出现在聚合函数中，要么在ORDER BY子句中使用）

使用group by进行分组查询

在使用group by关键字时，在select列表中可以指定的项目是有限制的，select语句中仅许以下几项：

*被分组的列

*为每个分组返回一个值得表达式，例如用一个列名作为参数的聚合函数

注意:group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面

HAVING子句与WHERE子句的区别

HAVING子句和WHERE子句的相似之处在于，它也定义搜索条件。但与WHERE子句不同，HAVING子句与组有关，而不是与单个的行有关。

1、如果指定了GROUP BY子句，那么HAVING子句定义的搜索条件将作用于这个GROUP BY子句创建的那些组。

2、如果指定WHERE子句，而没有指定GROUP BY子句，那么HAVING子句定义的搜索条件将作用于WHERE子句的输出，并把这个输出看作是一个组。

3、如果既没有指定GROUP BY子句也没有指定WHERE子句，那么HAVING子句定义的搜索条件将作用于FROM子句的输出，并把这个输出看作是一个组。

4、在SELECT语句中，WHERE和HAVING子句的执行顺序不同。上面SELECT语句的执行步骤可知，WHERE子句只能接收来自FROM子句的输入，而HAVING子句则可以接收来自GROUP BY子句、WHERE子句和FROM子句的输入。

S-TQL语句执行顺序

select 5:投影 ，映射为对应的列

from 1:定位到表

where 2:进行分组前的第一次筛选

group by 3:分组

having 4:对分组后的数据进行第二次筛选

order by 6:对映射出的结果进行排序

注意:where后面不能加聚合函数

三个顺序：

01where：对表中记录进行筛选（分组前）

02group by 分组依据

03having 对分组后的数据进行筛选

见到having,之前必须有group by,因为having是对分组后的数据进行筛选

三者使用顺序不可颠倒

eg:

myschool数据库中有四张表,分别为student(学生表)result(成绩表)subject(科目表)grade(年级表)

01查询每个年级的总学时数,并按照升序排列

题目是每个年级的总学时数,每个年级肯定做为group by的分组依据,总学时则利用sum()函数

至于order by后也可以跟SUM(classhour).写总学时数的目的是为了更详细的理解语句的执行顺序

select gradeid as 年级编号,SUM(classhour) as 总学时数

from Subject

group by GradeId

order by 总学时数

02查询每个参加考试的学员的平均分

每个参加考试的学员平均分,学员对应学员编号肯定为group by的分组依据,平均分利用avg()函数计算出来

select studentno as 学员编号,AVG(studentresult) as 平均分

from Result

group by StudentNo

--03查询每门课程的平均分,并按照降序排列

每门课的平均分,按降序排列,group by的分租依据是subjectid(课程编号)平均分则利用avg()函数计算出来

利用order by 平均分 desc 来进行降序排列

select subjectid as 课程编号 , SUM(studentresult) as 平均分

from Result

group by SubjectId

order by 平均分 desc

04查询每个学生参加的所有考试的总分,并按照降序排列

每个学生所有考试总分,按降序排列,group by的分组依据是studentno(学生编号),总分利用sum()函数进行计算

最后利用order by总分desc来进行降序排序

select studentno as 学生编号 ,SUM(StudentResult) as 总分

from Result

group by StudentNo

order by 总分 desc

05查询每学期学时数超过50的课程数

每学期学时超过50的课程数,group by的分组依据是gradeid(年级编号),现有一个限定条件学时不超过50,这里用where来限定他classhour>50

select * from Subject

select gradeid as 年级编号,COUNT(classhour) as 课程数

from Subject

where classhour>50

group by GradeId

06查询每学期学生的平均年龄

每学期学生的平均年龄,group by的分组依据是studentno(学生编号),平均年龄用datediff()进行处理得出年龄,用avg()在进行处理,得出平均年龄.

select studentno as 学生编号,AVG(DATEDIFF(yy,birthday,GETDATE())) as 平均年龄

from Student

group by StudentNo

07查询北京地区的每学期学生人数

北京地区的每学期学生人数.group by的分组依据是gradeid ,学生人数用count(studentno)得出学生人数,最后有个限定条件,只查询北京地区的,用where进行限定,address like '%北京%'%代表任意长度的字符.

select gradeid as 年级编号,COUNT(studentno) as 学生人数

from Student

where Address like '%北京%'

group by GradeId

08查询参加考试的学生中平均分及格的学生记录,并按照成绩降序排列

参加考试的学生中平均分及格的学生记录,按降序排列,group by 分组依据是studentno,平均分用av(studentresult)进行计算出平均分,having进行判定avg(studentresult)>60是否及格,在用order by 平均分及格 desc进行降序排列.

select Studentno as 学生编号,AVG(Studentresult) as 平均分及格

from Result

group by StudentNo

having AVG(Studentresult)>=60

order by 平均分及格 desc

09查询考试日期为2009年9月9号的课程的及格平均分

考试日期为2009年9月9号的课程的及格平均分,group by 的分组依据是课程编号,限定条件是考试日期为2009年9月9号

where ExamDate>='2009-9-9' and ExamDate=60.

select subjectid,AVG(studentresult) as 及格平均分

from Result

where ExamDate>='2009-9-9' and ExamDate

group by SubjectId

having AVG(StudentResult)>=60

10查询至少一次考试不及格的学生学号,不及格次数

考试不及格的学生学号,不及格次数,先限定条件不及格的学生成绩,在按照学生学号进行分组,group by studentno

select studentno as 学生编号,COUNT(1) as 次数

from Result

where StudentResult

group by StudentNo

学生数据库中数据的增加、修改和删除

目标：

1：使用T-SQL向表中插入数据

2：使用T-SQL更新表中数据

3：使用T-SQL删除表中数据

首先我们来简单的介绍一下SQL及它的作用是什么

在进行数据库管理时，如果每次创建数据库、表或者从数据库中读取数据，都需要手动在SQL Server Management Studio中进行的话，不但管理不方便，而且存储在数据库中的数据也根本无法提供给程序使用。所以，数据库也需要一套指令集，能够识别指令、执行相应的操作为程序提供数据，目前表中的指令集及时SQL语言。

SQL语言是真的数据库而言的一门语言，它可以创建数据库、数据表，可以针对数据库的数据进行增、删、改、查等操作，可以常见视图、存储过程，可以赋予用户权限等。

SQL中的运算符

运算符是一种符号，是用来进行列间或者变量之间的比较和数学运算的。在SQL中，常用的运算符有算数运算符、赋值运算符、比较运算符和逻辑运算符。

1：算数运算符

算数运算符包括：+（加），—（减），*（乘），/（除），%（模）五个。算数运算符用来在两个数或表达式上执行数学运算，这两个表达式可以是任意两个数字数据类型的表达式

运算符 说明

+ 加运算，求两个数或表达式相加的和

— 减运算，求两个数或表达式相减的差

* 乘运算，求两个数或表达式相乘的积

/ 除运算，求两个数或表达式相除的商，例如，5/5的值为1,5.7/3的值为1.900000

% 取模运算，求两个数或表达式相除的余数，例如，5%3的值为2

2：赋值运算符

SQL有一个赋值运算符，即“=”（等号），用于将一个数或变量或表达式赋值给另一个变量

运算符 说明

= 吧一个数或变量或表达式赋值给另一个变量，例如：Name=‘张三’

3:比较运算符

比较运算符用来判断两个表达式的大小关系，除text、ntext或Image数据类型的表达式外，比较运算符几乎可以用于其他所有的表达式。

运算符 说明

= 等于，例如：age=23

> 大于，例如：price>100