子句可以对查询结果中指定的列进行升序或者是

作者: www.9159.com  发布:2019-11-21

分组函数GROUP BY

GROUP BY从语义上面来看意思是根据BY后面的字段名或者表达式进行分组,所谓的分组就是将SELECT出来的数据分成若干个组,相同的放一组),通常分组是为了做数据统计分析,所以常常配合聚合(统计)函数进行使用

常用的聚合(统计)函数有:

COUNT():返回SELECT语句检索到的行中非NULL值的数目,若找不到匹配的行,则COUNT() 返回 0,COUNT(*)则包含非NULL值

SUM(): 统计每组数据的总数,表中列值为NULL的行不参与计算,若找不到匹配的行,则返回NULL

AVG():统计每组数据的平均值,表中列值为NULL的行不参与计算,若找不到匹配的行,则返回 NULL

MAX():统计每组中的最大值,如果统计的列中只有NULL值,那么返回NULL

MIN():统计每组中的最小值,如果统计的列中只有NULL值,那么返回NULL

聚合函数的特点:只有一个返回值

mysql> SELECT name, AVG(score), SUM(score) FROM score GROUP BY name; -- 统计各科的平均成绩与总成绩
+------+------------+------------+
| name | AVG(score) | SUM(score) |
+------+------------+------------+
| 数学 | 94.33333   | 283.0      |
| 英语 | 95.33333   | 286.0      |
| 语文 | 92.66667   | 278.0      |
+------+------------+------------+
3 rows in set

分组会根据分组的字段进行默认排序,这里的排序指的是对每个组的结果集这个整体进行排序,而不是分组中每一条记录,实际上分组后每组也就一条记录了。

现在有个需求,想要对上面的结果再进行一次汇总,那么可能会考虑到用联合查询,不过MySQL中提供了WITH ROOLUP关键字就能轻松完成这件事情

mysql> SELECT name, AVG(score), SUM(score) FROM score GROUP BY name WITH ROLLUP;
+------+------------+------------+
| name | AVG(score) | SUM(score) |
+------+------------+------------+
| 数学 | 94.33333   | 283.0      |
| 英语 | 95.33333   | 286.0      |
| 语文 | 92.66667   | 278.0      |
| NULL | 94.11111   | 847.0      |
+------+------------+------------+
4 rows in set

与GROUP BY相比,在查询的最后一行多了对平均成绩与总成绩的汇总。对单个维度的汇总并不能体现出ROLLUP的优势,下面对id与name进行汇总统计

mysql> SELECT id, name, AVG(score), SUM(score) FROM score GROUP BY id, name WITH ROLLUP;
+------+------+------------+------------+
| id   | name | AVG(score) | SUM(score) |
+------+------+------------+------------+
|    1 | 数学 | 95         | 95.0       |
|    1 | 英语 | 98         | 98.0       |
|    1 | 语文 | 90         | 90.0       |
|    1 | NULL | 94.33333   | 283.0      |
|    2 | 数学 | 88         | 88.0       |
|    2 | 英语 | 90         | 90.0       |
|    2 | 语文 | 92         | 92.0       |
|    2 | NULL | 90         | 270.0      |
|    3 | 数学 | 100        | 100.0      |
|    3 | 英语 | 98         | 98.0       |
|    3 | 语文 | 96         | 96.0       |
|    3 | NULL | 98         | 294.0      |
| NULL | NULL | 94.11111   | 847.0      |
+------+------+------------+------------+
13 rows in set

其中(NULL, NULL)与GROUP BY  name WITH ROLLUP类似,表示对最后数据的汇总

(id, NULL)表示对学生进行分组后的聚合结果,这里表示对每个学生的成绩进行汇总

(id, name)表示对学生与科目进行分组后的聚合结果,这里表示对每个学生的各科成绩进行汇总

MySQL 扩展了 GROUP BY的用途,因此你可以使用SELECT 列表中不出现在GROUP BY语句中的列或运算。例如

mysql> SELECT id, name, AVG(score), SUM(score) FROM score GROUP BY id;
+----+------+------------+------------+
| id | name | AVG(score) | SUM(score) |
+----+------+------------+------------+
|  1 | 语文 | 94.33333   | 283.0      |
|  2 | 语文 | 90         | 270.0      |
|  3 | 语文 | 98         | 294.0      |
+----+------+------------+------------+
3 rows in set

从上面的结果可以看出分组函数的特点:返回值为该组中的第一条记录

在标准SQL中,你必须将 name添加到 GROUP BY子句中。假如你从GROUP BY部分省略的列在该组中不是唯一的,那么不要使用这个功能!你会得到非预测性结果。例如根据学生查询最高成绩时所对应课程名称为每组中第一条记录值,这并不是我们想要的

mysql> SELECT id, name, AVG(score), MAX(score) FROM score GROUP BY id;
+----+------+------------+------------+
| id | name | AVG(score) | MAX(score) |
+----+------+------------+------------+
|  1 | 语文 | 94.33333   | 98         |
|  2 | 语文 | 90         | 92         |
|  3 | 语文 | 98         | 100        |
+----+------+------------+------------+
3 rows in set

如果需要在一行中显示每个学生的各科成绩,可以用GROUP_CONCAT函数,该函数通常配合GROUP BY使用,如果没有GROUP BY,将返回列中的所有值

mysql> SELECT id, GROUP_CONCAT(score) FROM score GROUP BY id;
+----+---------------------+
| id | GROUP_CONCAT(score) |
+----+---------------------+
|  1 | 90.0,95.0,98.0      |
|  2 | 92.0,88.0,90.0      |
|  3 | 96.0,100.0,98.0     |
+----+---------------------+
3 rows in set

mysql> SELECT id, GROUP_CONCAT(score) FROM score;
+----+-----------------------------------------------+
| id | GROUP_CONCAT(score)                           |
+----+-----------------------------------------------+
|  1 | 90.0,95.0,98.0,92.0,88.0,90.0,96.0,100.0,98.0 |
+----+-----------------------------------------------+
1 row in set

语法格式中的offset和row_count都必须是非负的整数常数,offset指定返回的第一行的偏移量,row_count是返回的行数。例如,“LIMIT 5”表示返回SELECT语句的结果集中最前面5行,而“LIMIT3,5”则表示从第4行开始返回5行。

 

基本语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    字段列表 AS 字段别名
    [FROM 表名
    WHERE 条件表示式
    GROUP BY 字段名|表达式
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING 条件表达式]
    [ORDER BY 字段名|表达式
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]  

所有被使用的子句必须按语法说明中显示的顺序严格地排序。例如,一个HAVING子句必须位于GROUP BY子句之后,并位于ORDER BY子句之前。

ALL, DISTINCT和DISTINCTROW选项指定是否重复行应被返回,如果没有指定则默认值为ALL(返回所有匹配的行),DISTINCT和DISTINCTROW表示去重(如果是要删除重复的行,那么所有的字段都需要相同)

数据准备

CREATE TABLE IF NOT EXISTS score (
    id INT, -- 学生id
    name VARCHAR(10), -- 课程名称
    score NUMERIC(4, 1)); -- 分数

INSERT INTO score VALUES(1, '语文', 90);
INSERT INTO score VALUES(1, '数学', 95);
INSERT INTO score VALUES(1, '英语', 98);
INSERT INTO score VALUES(2, '语文', 92);
INSERT INTO score VALUES(2, '数学', 88);
INSERT INTO score VALUES(2, '英语', 90);
INSERT INTO score VALUES(3, '语文', 96);
INSERT INTO score VALUES(3, '数学', 100);
INSERT INTO score VALUES(3, '英语', 98);

字段别名:当数据进行查询出来的时候,有时候数据表的字段并不能符合我们的需求(多表查询的时候,可能会有同名的字段),这时候就需要对字段进行重命名

注意:在一个WHERE子句中使用列别名是不允许的,因为当执行WHERE子句时,列值可能还没有被确定。

mysql> SELECT name, score FROM score; -- 没有使用别名
+------+-------+
| name | score |
+------+-------+
| 语文 | 90    |
| 数学 | 95    |
| 英语 | 98    |
| 语文 | 92    |
| 数学 | 88    |
| 英语 | 90    |
| 语文 | 96    |
| 数学 | 100   |
| 英语 | 98    |
+------+-------+
9 rows in set

mysql> SELECT name AS '课程名称', score '分数' FROM score; -- 使用别名,score字段使用了AS关键字
+----------+------+
| 课程名称 | 分数 |
+----------+------+
| 语文     | 90   |
| 数学     | 95   |
| 英语     | 98   |
| 语文     | 92   |
| 数学     | 88   |
| 英语     | 90   |
| 语文     | 96   |
| 数学     | 100  |
| 英语     | 98   |
+----------+------+
9 rows in set

使用AS明确地指定列的别名,把它作为习惯,是一个良好的操作规范。

  1.  order by字句

ORDER BY salary DESC,dept ASC

过滤分组HAVING

HAVING是用来对分组后的数据进行数据筛选的,例如要查询平均成绩小于95的学生信息,使用having时,此时数据已经在内存中了。

mysql> SELECT id, AVG(score) FROM score GROUP BY id HAVING AVG(score) < 95;
+----+------------+
| id | AVG(score) |
+----+------------+
|  1 | 94.33333   |
|  2 | 90         |
+----+------------+
2 rows in set

用于范围比较的关键字有两个:between和in。

对分组结果进行排序 

排序ORDER BY

根据某个字段进行升序(默认)或者降序排序,依赖校对集

mysql> SELECT * FROM score WHERE id = 1 ORDER BY score DESC; -- 查询学生1的成绩,并按照成绩由高到低进行排序
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 英语 | 98    |
|  1 | 数学 | 95    |
|  1 | 语文 | 90    |
+----+------+-------+
3 rows in set

SELECT子句的表达式中还可以包含所谓的聚合函数。聚合函数常常用于对一组值进行计算,然后返回单个值。除COUNT函数外,聚合函数都会忽略空值。聚合函数通常与GROUP BY子句一起使用,但是不能与where语句一起使用。如果SELECT语句中有一个GROUP BY子句,则这个聚合函数对所有列起作用,如果没有,则SELECT语句只产生一行作为结果。  www.2cto.com  

其中,TOP是表示限制结果集行数的关键字;数字n表示限制结果集行数;PRECENT关键字表示返回查询的结果集中前n%的行数,它是可选的。

数量限定LIMIT

两种使用方式

1、LIMIT row_count:row_count表示数量,如

mysql> SELECT * FROM score LIMIT 2; -- 查找列表前两条数据
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 语文 | 90    |
|  1 | 数学 | 95    |
+----+------+-------+
2 rows in set

2、LIMIT begin,offset:begin表示起始位置,offset表示数量

mysql> SELECT * FROM score LIMIT 2,3; -- 从第二条开始,取出三条数据,通常用于分页
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 英语 | 98    |
|  2 | 语文 | 92    |
|  2 | 数学 | 88    |
+----+------+-------+
3 rows in set

 

③ 计算差值的平方的总和;

 

条件过滤WHERE

在SELECT语句中,数据根据WHERE子句中指定的搜索条件来进行过滤,在搜索条件中用来判断条件的有比较运算符与逻辑运算符,其中

比较运算符有:>,<,>=,<=,!=,<>,like,between and,in/not in

逻辑运算符有:&&(and),||(or),!(not)

当SQL执行到WHERE子句时,会先从磁盘中根据搜索条件进行逐条判断,如果成立则保存到内存中,否则跳过。

注意:WHERE子句返回的结果只有0或者1(要么成立,要么不成立),其中0代表false,1代表true。

mysql> SELECT * FROM score WHERE id = 1; -- 查找id为1的学生信息
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 语文 | 90    |
|  1 | 数学 | 95    |
|  1 | 英语 | 98    |
+----+------+-------+
3 rows in set

mysql> SELECT * FROM score WHERE id = 1 OR id = 2; -- 查找id为1或者id为2的学生信息
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 语文 | 90    |
|  1 | 数学 | 95    |
|  1 | 英语 | 98    |
|  2 | 语文 | 92    |
|  2 | 数学 | 88    |
|  2 | 英语 | 90    |
+----+------+-------+
6 rows in set

mysql> SELECT * FROM score WHERE score BETWEEN 95 AND 98; -- 查找课程分数在95到98之间的学生信息
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | 数学 | 95    |
|  1 | 英语 | 98    |
|  3 | 语文 | 96    |
|  3 | 英语 | 98    |
+----+------+-------+
4 rows in set

 

SELECT teaID,teaName,dept,profession

其实在数据库最经常用的当属查询操作

AVG

 

逻辑与

-- 升序排序后的第4条到第6条记录

SELECT COUNT(备注)AS  '备注不为空的学生数目' FROM XS;

如果想要对分组后的结果限制查询条件,就需要使用HAVING子句。由于HAVING子句是用来限制分组后的查询结果,所以该子句需要放到GROUP BY子句的后面使用。其语法格式如下: 

说明:方差的计算按照以下几个步骤进行。

FROM T_teacher

  [into outfile 'name' export_options | INTO dumpfile 'name']

SELECT teaID,teaName,dept,profession,salary

说明:MySQL在后台执行如下表达式:(001|011)|111,结果为111。 其中BIN函数用于将结果转换为二进制位。

3.使用GROUP BY子句对表中数据进行分组

   [all | distinct | distinctrow ]

 

 

from (select * from t_teacher order by salary desc) temp

 

ORDER BY 列名1[ASC|DESC],列名2 [ASC|DESC] 

说明:

b.Oracle数据库中不支持类似于 MySQL 中的 LIMIT关键字来限制结果集行数,但是在 Oracle数据库中可以使用ROWNUM关键字限制结果集的行数。其语法格式如下: 

 

聚合函数只能出现在SELECT语句、GROUP BY子句以及HAVING子句中,WHERE子句中不能出现聚合函数。

注意:当给定列上只有空值或检索出的中间结果为空时,MAX和MIN函数的值也为空。

LIMIT 3

 

2.常用的聚合函数

●  high_priority:给予SELECT更高的优先权,使查询立刻执行,加快查询速度。

其中列名1表示要对该列进行分组,WITH ROLLUP关键字表示要对分组的结果进行统计。当然也可以对多个列进行分组,并统计分组后的结果。其语法格式如下: 

 

 

 

5.限制结果集行数

 

FROM T_teacher

例:前面那个求1900年出生的学生的第三种做法;

使用GROUP BY子句对数据表中的某一列进行分组时,会对指定分组的列中不同的值都计算出一个统计结果。其语法格式如下: 

    FROM XS  WHERE 总学分>50;

 

 

 

例1:将XSCJ数据库中各专业名输出。

SELECT teaID,teaName,dept,profession

 

SELECT profession,MAX(salary)

 

SELECT teaID,teaName,dept,profession,salary

 

ORDER BY salary

   [SQL_cache | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

COUNT函数是用来计算数据表中的总行数,SUM函数是用来计算数据表中某一列的属性值的总和。 

  WHERE 出生日期 NOT BETWEEN '1990-01-01'and '1990-12-31';

c.MySQL数据库和Oracle数据库中使用LIMIT关键字和ROWNUM的方法限制结果集行数,在Microsoft SQL Server数据库中需要使用TOP关键字。其语法格式如下: 

expression [ NOT ] between expression1 andexpression2

排在使用ORDER BY子句进行排序操作时,除了可以使用列名对指定列进行序,也可以使用该列在选择列表中的位置的序号对其进行排序。 

 

在使用GROUP BY子句对对指定列进行分组时,有时可能会遇到指定列中含有NULL值的情况。此时,GROUP BY子句会将该列中所有的NULL值归为一组。 

 

 

 

 

 

SELECT teaID,teaName,dept,profession,salary

(6)BIT_AND、BIT_OR和BIT_XOR

很多时候,对数据表中数据进行分组后,还希望对分组的结果进行排序操作。如果想对使用了GROUP BY子句的分组结果进行排序的话,就需要使用ORDER BY子句。 

 

… 

SELECT关键词的后面可以使用很多的选项:

 

 

FROM 表名 

 

 

注意:expression1的值不能大于expression2的值。

 

STDDEV可以缩写为STD,这对结果没有影响。

GROUP BY列名1,列名2… 

 

其中列名1表示需要对该列进行排序操作。关键字ASC和DESC是可选的。如果ORDER BY 子句后面不写ASC或者是DESC,则默认执行的是升序操作。 

  count(distinct 字段名):去掉重复后再统计字段中有值的记录个数(也不包括null)

SELECT dept,COUNT(profession)

 

 

 

order by salary

 

COUNT、SUM和AVG函数中可以使用DISTINCT关键字去除指定列中的重复项。使用DISTINCT关键字后只是对不同行的值进行统计。 

●  straight_join:用于促使MySQL优化器把表联合在一起,加快查询速度。

SELECT dept,profession,MAX(salary)

   [order by{col_name | expr | position}   [ASC| DESC] , ...]             /*ORDER BY子句*/

GROUP BY ROLLUP (列名1,列名2…)

逻辑异或

对SELECT语句中的非选择列进行排序 

  count(字段名):统计字段中有值的记录个数(不包括null);

ORDER BY teaID

 

 

 

 

 

select teaName,salary

 

 

    FROM XS ORDER BY 学号 LIMIT 5;

4.使用ROLLUP关键字统计数据

 

FROM T_teacher

 

单列分组 

例如,根据学生所学的专业对XS表中的所有行分组,结果是每个专业的学生成为一组。group by子句的语法格式如下:group by {col_name | expr| position} [ASC | DESC], ... [WITH ROLLUP]

GROUP BY dept

例1:求学生的总人数。

 

(4)variance和stddev(STD)函数

ORDER BY salary DESC

 

FROM T_teacher

 

FROM T_teacher

语法格式为:GROUP_CONCAT({[ALL|DISTINCT] expression }| * )

 

 

ORDER BY 5 ASC

   FROM XS GROUP BY 专业名,性别 with rollup;

GROUP BY dept

求组中值的平均值

SELECT dept,profession,MAX(salary)

语法格式: having 选择条件;

使用SQL语句执行查询操作时,我们可能发现查询出的数据结果的排序是无序的。为了更好的观察数据表中的查询结果,开发人员或者用户经常要对查询的数据进行排序操作,这就需要使用ORDER BY子句。在数据库的实际应用中,有时需要对查询的数据进行统计和分组操作,这就需要了解SQL语句的聚合函数和GROUP BY子句的使用。有些时候开发人员或者用户还希望对分组后的结果做进一步的统计,在SQL语句中提供了ROLLUP这样一个关键字用来对数据进行统计。最后还将介绍主要数据库中如何限制结果集的行数。

使用having子句的目的与where子句类似,having与where的区别:WHERE子句是用来在FROM子句之后选择行,where是对原始记录进行挑选;而HAVING子句用来在GROUP BY子句后选择行,对分组后的记录进行挑选。

使用HAVING子句子限制分组后的查询结果 

 

 

例:求学号081101的学生所学课程的总成绩。

GROUP BY列名1 HAVING 条件表达式 

SELECT * FROM XS

其中列名1表示需要对该列进行分组操作。 

 

 

   [straight_join]

按照GROUP BY后分得的各组中的最高工资给组排序;而如下语句则是按照每组的第一行的工资给组排序。 

SQL标准要求HAVING必须引用GROUP BY子句中的列或用于聚合函数中的列。不过,MySQL支持对此工作性质的扩展,并允许HAVING引用SELECT清单中的列和外部子查询中的列。

指定表中的一列进行排序 

 

FROM T_teacher 

 

数据的排序与分组语句

 

 

 

FROM T_teacher 

    FROM XS_KC   WHERE 学号 ='081101';

 

 

WHERE dept ='计算机系'

 

GROUP BY profession

 

GROUP BY 列名1 ,列名2 WITH ROLLUP 

 

GROUP BY列名1 

 

 

SELECT 专业名 FROM XS GROUP BY 专业名;

GROUP BY子句中处理NULL值 

 

MAX和MIN函数中的列或者表达式可以是数字型、字符型或者是日期类型的值。如果MAX和MIN函数中的列或者表达式是字符型的,则按照首字母从A到Z的顺序排序,如果首字母相同,则比较字符串中第二个字母的大小,以此类推。汉字则是按照其汉语拼音的全拼来排序。

SELECT 学号,姓名,专业名,出生日期  FROM XS

 

 

HAVING MAX(salary)>3000

   select_expr, ...

 

MySQL支持一个特殊的聚合函数group_concat函数。该函数返回来自一个组指定列的所有非NULL值,这些值一个接着一个放置,中间用逗号隔开,并表示为一个长长的字符串。这个字符串的长度是有限制的,标准值是1024。

 

 

在实际应用中,有时不仅需要得到分组后的统计结果,还希望对分组的统计结果做进一步的计算,例如通过对教师信息表(T_teacher)中的院校和教师职称进行分组,得到分组后教师的工资,还希望对每一个院系中的教师的工资做一个阶段性的统计,希望得到各个院校中不同职称的教师的工资的加和(相当于小计),还希望得到所有院校不同职称教师工资的总和(相当于总计)。这个时候仅仅使用GROUP BY子句是无法做到的,此时就需要使用ROLLUP关键字。 

返回给定表达式中所有值的标准差

ORDER BY子句除了可以指定单列进行排序操作,也可以指定数据表中的多个列进行排序操作。如果要指定数据表中的多个列进行排序操作,则指定排序的列与列之间需要用逗号隔开。其语法规则如下: 

MIN

a.在MySQL和Microsoft SQL Server数据库中需要使用WITH ROLLUP。其语法格式如下:

 

 

所有被使用的子句必须按语法说明中显示的顺序严格地排序。例如,一个HAVING子句必须位于GROUP BY子句之后,并位于ORDER BY子句之前。

ORDER BY 列名1 [ASC|DESC] 

 

其中LIMIT是关键字,数字n表示要限制结果集行数。

 

LIMIT 3,3

 

使用GROUP BY子句对数据表中的多个列进行分组时,会对指定分组的多个列中不同的值都计算出一个统计结果。其语法格式如下: 

 

ROLLUP关键字使用时需要放到GROUP BY关键字的后面。ROLLUP关键字在不同的数据库中的使用方式上稍有不同。 

1.语法格式:

其中列名1和列名2表示需要对指定的数据列进行排序操作。列名1和列名2之间用逗号进行分割。关键字ASC和DESC是可选的。如果ORDER BY 子句后面不写ASC或者DESC,则默认执行的是升序操作。首先,根据ORDER BY中指定的第一列进行排序;然后,再根据ORDER BY子句中指定的第二列的升序或者降序方式进行排序。 

  www.2cto.com  

FROM T_teacher 

   [limt {[offset,] row_count | row_count OFFSET offset}]                /*LIMIT子句*/

除了COUNT(*)之外,其他的几个函数在计算时都忽略表达式中的空值(NULL行)。 

  1.  limit字句

HAVING MAX(salary)>3000

 

 

逻辑或

SELECT TOP n [PRECENT] 列名1,列名2 … 

  www.2cto.com  

FROM T_teacher

●   SQL_BIG_RESULT:可以与GROUPBY或DISTINCT同时使用,来告知MySQL优化器结果集合有很多行。在这种情况下,MySQL会优先进行分类,不优先使用临时表。  www.2cto.com  

其中ROWNUM关键字表示对符合条件结果的序列号,它的起始值总是从1开始的。数字n表示要限制的结果集的行数。当然,这里的比较运算符除了可以使用(<)小于以外,还可以使用(<=)小于等于。 

  例2: 求XSCJ数据库中各专业的学生数。

其中列名1和列名2表示需要对指定列进行分组操作。列名1和列名2之间用逗号进行分割。 

 

LIMIT n 

●  sql_small_result:可以与GROUP BY或DISTINCT同时使用,来告知MySQL优化器结果集合是较小的。在此情况下,MySQL使用快速临时表来储存生成的表,不使用分类。

 

 

GROUP BY dept,profession

 

 

●  HIGH_PRIORITY,STRAIGHT_JOIN和以SQL_为开头的选项都是MySQL相对于标准SQL的扩展,这些选项在多数情况下可以选择不使用。

ORDER BY子句中也可以对没有在SELECT语句中出现的选择列进行排序操作。 

 

FROM T_teacher

其含义是对结果集中的重复行只选择一个,保证行的唯一性。

 

例: 查找XS表中学号最靠前的5位学生的信息。

group by dept

 

 

例: 查询XSCJ数据库中总学分尚不定的学生情况。

 

例:查询XS表中专业名为“计算机”、“通信工程”或“无线电”的学生的情况。

 

说明:IN关键字最主要的作用是表达子查询。

Select同时包含数据列和聚合函数时,必须使用Group By。 

函  数  名

 

LIMIT子句是SELECT语句的最后一个子句,主要用于限制被SELECT语句返回的行数。  语法格式:LIMIT {[offset,]row_count | row_count OFFSET offset}

 

   使用带rollup操作符的GROUP BY子句: 指定在结果集内不仅包含由 GROUP BY 提供的正常行,还包含汇总行。

WHERE age>30

 

 

说明:  www.2cto.com  

 

 

ORDER BY salary ASC

 

ORDER BY MAX(salary) DESC

   WHERE 专业名 IN  ('计算机', '通信工程', '无线电');

FROM T_teacher 

BIT_AND

b.在Oracle数据库中,ROLLUP关键字需要紧跟在GROUP BY关键字的后面,然后再写需要分组的字段。其语法格式如下: 

 

使用SQL语句执行查询操作时,我们可能发现查询出的数据结果的排序是无序的。为了更好的观察数据表中的查询结果...

例: 将通信工程专业的学生按出生日期先后排序。

 

 

FROM T_teacher

 

多列分组 

BIT_OR

指定表中列的位置序号进行排序 

 

其中列名1表示需要对该列进行分组操作。HAVING子句后的条件表达式是用来筛选分组后的结果。在HAVING子句中经常使用聚合函数对分组后的结果进行筛选。 

使用了ROLLUP操作符后,将对GROUP BY子句中所指定的各列产生汇总行,产生的规则是:按列的排列的逆序依次进行汇总。如本例根据专业名和性别将XS表分为4组,使用ROLLUP后,先对性别字段产生了汇总行(针对专业名相同的行),然后对专业名与性别均不同的值产生了汇总行。所产生的汇总行中对应具有不同列值的字段值将置为NULL。

GROUP BY 列名1 WITH ROLLUP

返回给定表达式中所有值的方差

 

(5)group_concat函数

-- 升序排序后的前3条记录

 

如果要得到每个分组中的工资的最大值对应的行(而不是分组的第一行),可以使用子查询等方法,详细以后讨论。 

 

1.使用ORDER BY子句对数据记录进行排序

语法格式为:COUNT ( { [ ALL | DISTINCT ] expression } | * )

SELECT MAX(salary),MIN(salary)

 

SUM和AVG函数中的表达式只能是数字类型的值。 

聚合函数中最经常使用的是COUNT()函数,用于统计组中满足条件的行数或总行数,返回SELECT语句检索到的行中非NULL值的数目,若找不到匹配的行,则返回0。

聚合函数也被称为分组函数或者统计函数,主要用于对得到的一组数据进行统计计算,例如求和、求平均值等,常用的聚合函数包括COUNT、MAX、MIN、SUM和AVG五个。 

 

GROUP BY dept

④ 用总和除以(列中的)值得结果。

通过ORDER BY 子句可以对查询结果中指定的列进行升序或者是降序操作,这取决于ORDER BY子句后的关键字,如果ORDER BY子句后面的关键字是ASC,则对查询的结果执行升序操作;如果ORDER BY子句后面的关键字是DESC,则对查询的结果执行降序操作。其语法规则如下 

 

 

返回由属于一组的列值连接组合而成的结果

SELECT dept,profession,MAX(salary)

 

 

●  SQL_cache:告知MySQL把查询结果存储在查询缓存中。对于使用UNION的查询或子查询,本选项会影响查询中的所有SELECT。

 

 

 

 

注意:GROUP BY下才有HAVING,HAVING用于组;而WHERE是针对SELECT的(针对于表或者视图),WHERE用于分布前。 

SELECT  学号, 姓名, 专业名, 出生日期  FROM XS

 

说明:ORDER BY子句后可以是一个列、一个表达式或一个正整数。正整数表示按结果表中该位置上的列排序。关键字asc表示升序排列,desc表示降序排列,系统默认值为ASC。

 

作者 tianyazaiheruan

SELECT SUM(salary),COUNT(salary),AVG(salary)

VARIANCE / STDDEV ( [ ALL | DISTINCT ]expression )

WHERE ROWNUM<n 

SUM和AVG分别用于求表达式中所有值项的总和与平均值,语法格式为:SUM / AVG ( [ ALL | DISTINCT ] expression )其中,expression是常量、列、函数或表达式,其数据类型只能是数值型数据。

指定表中的多列进行排序 

SELECT COUNT(总学分) AS  '总学分50分以上的人数'

WHERE dept ='计算机系'

 

 

 

 

 

 

 

a.在MySQL数据库中限制结果集行数可以使用LIMIT关键字,它可以用来限制查询出来的数据结果的个数。通过使用LIMIT关键字可以让开发人员或者用户得到其中想要的部分的结果。如果要使用LIMIT限制结果集行数,可以使用下面的语法格式。 

SUM

有些时候,开发人员或者用户并不希望将查询结果的数据列中的数据全部显示出来,而是只希望显示其中的几行,尤其是在需要分页的操作中。例如,一个数据表最后查询出了100条记录,而开发人员或者用户只关心其中前10条记录的值,这就需要对查询结果中的数据记录的行数进行限制。在不同的数据库中限制结果集行数的方法也不尽相同。 

     FROM XS_KC  WHERE 课程号 ='101';

 

 

值得注意的是初始行的偏移量为0而不是1。

   [sql_small_result] [sql_big_result] [sql_buffer_result]

  www.2cto.com  

SELECT MAX(成绩), MIN(成绩)

 

 

 

 

 

 

  1. having子句(使用分组后在进行挑选)

语句后面的记录加不加单引号都一样...

  1. 消除结果集中的重复行

 

MySQL中纯数值的记录在程序中可以不加单引号;如果字符型的记录中只有数字则也可以不加单引号;as 语句后面的记录加不加单引号都一样。

    WHERE 专业名 = '通信工程' ORDER BY 出生日期;

 

 

 

求最大值

 

 

求组中项数,返回int类型整数

 

  count(*):统计记录的条数(包括null);

   [having where_definition]          /* HAVING 子句*/

为了与PostgreSQL兼容,MySQL也支持LIMIT row_count OFFSET offset语法。所以将上面例子中的LIMIT子句换成“limit 5  offset 3”,结果一样。

 

 

 

 

MAX和MIN分别用于求表达式中所有值项的最大值与最小值,语法格式为:MAX / MIN ( [ ALL | DISTINCT ] expression )其中,expression是常量、列、函数或表达式,其数据类型可以是数字、字符和时间日期类型。  www.2cto.com  

当不使用NOT时,若表达式expression的值为空值,返回TRUE,否则返回FALSE;当使用NOT时,结果刚好相反。

(3)SUM函数和AVG函数

返回表达式中所有值的和

 

 

① 计算相关列的平均值;

 

 

 

 

 

 

本例即查找总学分为空的学生,结果为空。

例: 查找XSCJ数据库中平均成绩在85分以上的学生的学号和平均成绩。

 

 

注意:当对空值排序时,ORDER BY子句将空值作为最小值对待,按升序排列的话将空值放在最上方,降序放在最下方。

SELECT 学号, AVG(成绩) AS '平均成绩'

 

BIT_XOR

 

 

 

例2. 统计备注不为空的学生数目。

 

 

 

使用IN关键字可以指定一个值表,值表中列出所有可能的值,当与值表中的任一个匹配时,即返回TRUE,否则返回FALSE。使用IN关键字指定值表的格式为:expression IN ( expression [,…n])

 

 

  1.       group by子句主要用于根据字段对行分组。

 

 

 

select

例:求选修101课程的学生的最高分和最低分。

 

 

 

 

 

当需要判定一个表达式的值是否为空值时,使用IS NULL关键字,格式为: expression IS [ NOT ] NULL

当不使用NOT时,若表达式expression的值在表达式expression1与expression2之间(包括这两个值,相反,使用not时,不包括这两个值),则返回TRUE,否则返回FALSE;使用NOT时,返回值刚好相反。

SELECT BIN(BIT_OR(bin_value)) FROM BITS;

 

COUNT

② 求列中的每一个值和平均值的差;

 

 

 

 

 

 

 

 

例: 在XSCJ数据库上产生一个结果集,包括每个专业的男生人数、女生人数、总人数,以及学生总人数。

 

说明:如果记录特别多可以用limit语句分页!

 

   [high_priority]  www.2cto.com  

 例3: 统计总学分在50分以上的人数。

从这个基本语法可以看出,最简单的SELECT语句是SELECTselect_expr,利用这个最简单的SELECT语句,可以进行MySQL所支持的任何运算,例如,SELECT 1+1,它将返回2。

 

FROM XS GROUP BY 专业名;

 

 

VARIANCE

   WHERE 专业名 ='计算机'  OR 专业名 = '通信工程'  OR 专业名 = '无线电';

 

  1.  聚合函数(aggregationfunction)(分组:分类汇总)
  1.   范围比较

variance和stddev函数分别用于计算特定的表达式中的所有值的方差和标准差。语法格式:

●  SELECT…INTO OUTFILE 'name':这个语句可以将表中的行导出到一个文件中,这个文件被创建在服务器主机中,name为文件名。

 

STD或STDDEV

 

   [from  table_reference [ ,table_reference] …]                       /*FROM子句*/

例: 有一个表BITS,其中有一列bin_value上有3个integer值:1、3、7,获取在该列上执行BIT_OR的结果,使用如下语句:  www.2cto.com  

SELECT 专业名,COUNT(*) AS '学生数' 

对表只选择其某些列时,可能会出现重复行。例如,若对XSCJ数据库的XS表只选择专业名和总学分,则出现多行重复的情况。可以使用distinct或distinctrow关键字消除结果集中的重复行,其格式是:select  distinct|distinctrow 字段名1 [ , 字段名2…]

 

(2)MAX和MIN

 

SELECT 学号, 姓名, 专业名, 性别, 出生日期, 总学分

 

 

 

 

SELECT * FROM XS

当要查询的条件是某个值的范围时,可以使用between关键字。BETWEEN关键字指出查询范围,格式为:

    FROM XS_KC GROUP BY 学号 HAVING AVG(成绩) >=85;

条件的定义和WHERE子句中的条件类似,不过HAVING子句中的条件可以包含聚合函数,而WHERE子句中则不可以。

求最小值

 

 

 

 

●   ALL | distinct | DISTINCTROW:这几个选项指定是否重复行应被返回。如果这些选项没有被给定,则默认值为ALL(所有的匹配行被返回)。DISTINCT和 DISTINCTROW是同义词,用于消除结果集合中的重复行。

 

[group by{col_name| expr | position} [asc|desc], ... [with rollup]]      /*GROUP BY子句*/

    WHERE 总学分 IS NULL;

 

与二进制运算符|(或)、&(与)和^(异或)相对应的聚合函数也存在,分别是BIT_OR 、BIT_AND、BIT_XOR。例如,函数BIT_OR在一列中的所有值上执行一个二进制OR。

 

 

SELECT COUNT(*) AS '学生总数' FROMXS;

 

说    明

 

 

 

 

●  SQL_buffer_RESULT:促使结果被放入一个临时表中。这可以帮助MySQL提前解开表锁定,在需要花费较长时间的情况下,也可以帮助把结果集合发送到客户端中。

 

●  SQL_CALC_FOUND_ROWS:告知MySQL计算有多少行应位于结果集合中,不考虑任何LIMIT子句。

该语句与下列语句等价:

SELECT 专业名, 性别, COUNT(*) AS '人数'

●  SQL_NO_CACHE:告知MySQL不要把查询结果存储在查询缓存中。

 

group by子句后通常包含列名或表达式。MySQL对GROUP BY子句进行了扩展,可以在列的后面指定ASC(升序)或DESC(降序)。GROUP BY可以根据一个或多个列进行分组,也可以根据表达式进行分组,经常和聚合函数一起使用。

 

 在一条SELECT语句中,如果不使用order by子句,结果中行的顺序是不可预料的。使用ORDER BY子句后可以保证结果中的行按一定顺序排列。  语法格式:ORDER BY {col_name | expr | position} [ASC | DESC] , ...

例:对XSCJ数据库的XS表只选择专业名和总学分,消除结果集中的重复行。   select distinct 专业名,总学分 from xs;

 

 

注意:这里COUNT(备注)计算时备注为NULL的行被忽略

GROUP_CONCAT  www.2cto.com  

 

语法格式为:  BIT_AND |BIT_OR | BIT_XOR( { [ ALL | DISTINCT ] expression } | * )

       如果是按多个字段排序,先按第一个字段排,当第一个字段的值相同时,再按第二个排;如果第一个字段都不相同,则第二个字段用不着;order by语句在limit语句之前。

STDDEV函数用于计算标准差。标准差等于方差的平均根。所以,stddev(…)和sqrt(VARIANCE(…))这两个表达式是相等的。

SELECT * FROM XS

(1)COUNT函数

 

SELECT SUM(成绩) AS '课程总成绩'

MAX

其中,expression是一个表达式,其数据类型是除BLOB或TEXT之外的任何类型。ALL表示对所有值进行运算,DISTINCT表示去除重复值,默认为ALL。使用COUNT(*)时将返回检索行的总数目,不论其是否包含 NULL值。

 

 

 

  1.       空值比较

[wherewhere_definition]        /*WHERE子句*/

 

本文由9159.com发布于www.9159.com,转载请注明出处:子句可以对查询结果中指定的列进行升序或者是

关键词: