那些年我们一起做过的[分组查询]_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

小于

不等于

>= 大于等于

小于等于

!= 不等于（非SQL-92标准）

 

4：逻辑运算符

 

逻辑运算符用来对某个条件进行判断，以获得判断条件的真假，返回带有TRUE或FALSE值的布尔数据类型

 

运算符 说明

AMD 当且仅当两个布尔表达式都为TRUE时，返回TRUE

OR 当且仅当两个布尔表达式都为FALSE时，返回FALSE

NOT 对布尔表达式的值取反，有限级别最好

 

——————————————————————————————————————————————————————————————————————

 

使用T-SQL插入数据

 

1.使用INSERT插入数据（使用INSERT语句一行一行的插入数据是最常用的方法）

 

语法如下：

 

INSERT [INTO] 表名 [(列名列表)] VALUES(值类表);

 

 

其中

 

1.[INTO]是可选的，也可以省略。

 

2.表名是必须的。

 

3.表的列名是可选的，如果省略，将依次插入所有列。

 

4.多个列名和多个值列表用逗号分隔。

 

5.分号（;）是T-SQL语句终止符，分好不是必须的。

 

一次插入多行数据

 

1.通过INSERT SELECT语句向表中添加数据

 

例如，创建一张新表AddressList来存储本班的通讯信息，则可以从学生表中提取相关的数据插入建好的AddressList表中，语句如下：

 

INSERT INTO Addresslist(姓名，地址，电子邮件)

SELECT SName,SAdd热身赛，SEmail

FROM Students

注意：

 

查询的多的数据个数，顺序，数据类型等，必须与插入的项保持一致。

 

AddressList表必须预先创建好，并且具有姓名，地址和电子邮件三个列。

 

2.通过SELECT INTO语句将现有表中的数据添加到新表中

 

与上面的INSERT INTO 类似，SELECT INTO语句也是从一个表中选择一些数据插入新表中，所不同的是，这个新表是执行查询语句的时候创建的，不能够预先存在。

 

 例：

 

SELECT Students.Name,Students.SAddress,Students.SEMmail

INTO AddressList

FROM Students

 

 

 将创建新表的AddressList，把Students表中的SName，SAddress，SEmail作为AddressList表的新列，并且把查询到的数据全部插入新表中。

 

3.通过UNION关键字合并数据进行插入

 

UNION语句用于将两个不同的数据或查询结果组合成一个新的结果集。当然，不同的数据或查询结果，也要求数据个数，顺序，数据类型都一致，因此，当向表中多次插入数据的时候，可以使用SELECT...UNION来简化操作

 

 例：

 

INSERT Student(SName,SGrade,SSex)

SELECT '张三',7,1 UNION

SELECT '李四',4,0 UNION

SELECT '王五',2,0 UNION

SELECT '朱六',3,0 UNION

SELECT '王二麻子',7,1 UNION

 

 

 这样的效果其实与上面INSERT...SELECT的效果是一样的，只不过多行数据是后写的，然后用UNION合并组成多行数据记录，最后把这些多行数据记录一起插入

 

——————————————————————————————————————————————————————————————————————

 

使用T-SQL更新数据

 

语法：

 

UPDATE 表名 SET 列名 = 更新值 [WHERE 更新条件]

 

 

其中：

 

1.SET后面可以紧随多个”列名=更新值“，修改东哥数据列的值，不限一个，使用逗号分隔。

 

2.WHERE子句是可选的，用来限制更新数据的条件。若不限制，则整个表的数据行将被更新。

 

使用T-SQL删除数据

 

语法：

 

DELETE [FROM] 表名 [WHERE ]

 

 

 例：

 

在学生信息表中删除姓名为”张三“的数据

 

DELETE FROM Students

WHERE SName = '张三'

 

 

使用TTRUNCATE TABLE删除数据

 

TTRUNCATE TABLE用来删除表中说有行，功能上它类似于没有WHERE子句的DELETE语句。

 

 例如，要删除学生信息表中的所有记录行，可以使用以下语句

 

TRNCATE TABLE Students

但TRUNCATE TABLE 比DELETE 执行速度更快，使用的系统资源和事务日志资源更少，并且删除数据后表的标识列会重新开始编号。

 

 实际工作中，不建议使用TRUNCATE TABLE 语句，因为使用它删除的数据不能恢复还原。

 

友情提示：删除前问自己三遍是不是真的要删除，问下令删除的人三遍是否要删除，确定没有遗漏后执行操作。