数据分析课程笔记 - 20 - HIVE 核心技能之窗口函数

大家好呀，这节课我们学习 Hive 核心技能中最难的部分——窗口函数。窗口函数我们之前在学 MySQL 的时候有学过一些，但是只学了三个排序的窗口函数。这节课我们会学习更多的窗口函数，包括累计计算、分区排序、切片排序以及偏移分析。

曲阳ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为成都创新互联公司的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：13518219792（备注：SSL证书合作）期待与您的合作！

在正式学习之前，我们需要先明确一下窗口函数和GROUP BY分组的区别。二者在功能上有相似之处，但是它们存在本质区别。

1. 分组会改变表的结构，而窗口函数不会改变表的结构。比如原表有10行数据，分成两组后只有两行，而窗口函数仍然返回十行数据。

2. 分组只能查询分组后的字段，包括分组字段（组名）和聚合函数字段。而窗口函数对查询字段没有限制，也就是可以查询原表的任意字段，再加上窗口函数新增的一列值。

好啦，现在让我们一起进入窗口函数的世界吧~

本节课主要内容：

1、累计计算窗口函数

（1）sum(…) over(……)

（2）avg(…) over(……)

（3）语法总结

2、分区排序窗口函数

（1）row_number()

（2）rank()

（3）dense_rank()

3、切片排序窗口函数

（1）ntile(n) over(……)

4、偏移分析窗口函数

5、重点练习

大家在做报表的时候，经常会遇到计算截止某月的累计数值，通常在EXCEL里可以通过函数来实现。

那么在HiveSQL里，该如何实现这种累计数值的计算呢？那就是利用窗口函数！

关于窗口函数的几点说明：

需求分析 ：既然要进行按月累计，我们就先要把2018年的每笔交易时间转换成月并按月分组聚合计算，得出一个2018年每月支付金额总合表，再基于这张表用窗口函数进行累计计算。

2018年每月支付金额总和表：

再用窗口函数进行月度累计：

年度进行汇总。

这个需求比需求1多了一个需求，那就是年度汇总。那我们只需要在上个需求的子查询中加一个 year 字段即可。

说明：

1、over 中的 partition by 起到了窗口内将数据分组的作用。事实上，加上partition by之后，可以理解为分成了多个窗口，并在每个窗口内进行累加计算或者分区。

如果不加 partition by a.year 的话，运行结果就是这样单纯按月份进行分组的：

2、order by 按照什么顺序进行累加，升序ASC、降序DESC，默认是升序。

大家看股票的时候，经常会看到这种K线图，里面经常用到的就是7日、30日移动平均的趋势图，那如何使用窗口函数来计算移动平均值呢？

需求分析 ：这个需求要求每个月近三个月的移动平均支付金额，这里我们要用到一个新知识点，在窗口函数 avg over 的 order by a.month 之后加一句 rows between 2 preceding and current row 来设定计算移动平均的范围，这个语句的含义就是包含本行及前两行。其他部分的写法跟前面的需求类似，先取出2018年每个月的支付金额总和，再用窗口函数求移动平均。

注意：

sum(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…)

avg(…A…) over(partition by …B… order by …C… rows between…D1… and …D2…)

A：需要被加工的字段名称

B：分组的字段名称

C：排序的字段名称

D：计算的行数范围

rows between unbounded preceding and current row

——包括本行和之前所有的行

rows between current row and unbounded following

——包括本行和之后所有的行

rows between 3 preceding and current row

——包括本行以内和前三行

rows between 3 preceding and 1 following

——从前三行到下一行(5行)

max(……) over(partition by …… order by …… rows between ……and ……)

min(……) over(partition by …… order by …… rows between ……and ……)

row_number() 、rank()、dense_rank()

用法：这三个函数的作用都是返回相应规则的排序序号

row_number() over(partition by …A… order by …B… )

rank() over(partition by …A… order by …B… )

dense_rank() over(partition by …A… order by …B… )

A：分组的字段名称

B：排序的字段名称

注意： 这3个函数的括号内是不加任何字段名称的！

row_number ：它会为查询出来的每一行记录生成一个序号，依次排序且不会重复。

rankdense_rank ：在各个分组内， rank() 是跳跃排序，有两个第一名时接下来就是第三名， dense_rank() 是连续排序，有两个第一名时仍然跟着第二名。

实例练习：

再眼熟一下 user_trade 的表结构：

需求分析 ：先限定时间范围，然后根据 user_name 进行分组，接着选出 分组去重后的 user_name，并计算每个用户 goods_category 的数量（记得 distinct 去重），再然后就是用窗口函数对 goods_category 的数量进行排序，当然选择哪一种排序方法要看具体要求，这里我们可以三种方法都试一下看看结果：

注意 ：窗口函数中的 order by 字段不能用 select 中字段的重命名，因为二者是同时执行的。

需求分析 ： 先用窗口函数将2019年每个用户的支付总金额算出来并进行排序，再以此作为子查询，从中取出排名在第10、20、30名的用户名、支付总金额以及排名次序。企业一般会使用 dense_rank 进行排序，所以我们这里直接用 dense_rank。

2019年每个用户的支付总金额排名：

2019年支付金额排名在第10、20、30名的用户：

ntile(n) over(partition by …A… order by …B… )

n：切分的片数

A：分组的字段名称

B：排序的字段名称

需求分析 ：这个需求很简单，把需求5第一步的排序窗口函数变成切片即可。注意时间筛选条件变成2019年1月。

需求分析 ： 排名前10%，也就是一共分成10组，取第1组。那么我们先切片分组：

然后再取第一组：

说明：Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG和LEAD与left join、right join等自连接相比，效率更高，SQL语句更简洁。

lag(exp_str,offset,defval) over(partion by ……order by ……)

lead(exp_str,offset,defval) over(partion by ……order by ……)

lag() 函数示例：

lead() 函数示例：

需求分析： 先要从 user_trade 表中取出每个用户的支付时间，把每个用户放到一个窗口中，按照支付时间进行排序，取出偏移列： lead(dt,1,dt) over(partition by user_name order by dt)。接着基于该子查询，筛选出时间间隔大于100天的用户，并计算数量。

注意 ： 如果上面偏移分析函数写成 lead(dt,1,dt) 就不用加后面的 dt is not null 了，因为有默认值的话，间隔就是0，肯定是不满足条件的。

需求分析 ：

第一步 ：这个需求要用到 user_trade 和 user_info 两张表，前者取支付时间和金额，后者取城市和性别。先对这两张表基于 user_name 进行左连接，并取出相应字段，用窗口函数进行分组排序：

这一步的运行结果是这样的：

第二步 ：基于上述结果取出TOP3：

需求分析：

第一步 ：这个需求同样要用到两张表 user_refund 和 user_info。我们先把每个退款用户的退款金额和手机品牌取出来，并用窗口函数进行切片排序，25%就是分成4片：

注意 ：这里之所以要加 WHERE dt is not null 是因为 user_refund 是一个分区表，分区表要对分区字段进行限制，否则 hive 会报错。

第二步 ：选择前25%，也就是第一片：

最后补充一个从 hive 导出结果数据的命令：

以上就是这节课的全部内容了。做完整个练习，真的半条命都没了。窗口函数果然很难，不过掌握方法、多多练习，学会拆解需求，一步一步来做，就能明显降低难度。希望以后有机会能用到这么复杂的技能，哈哈~！

数据在ElasticSearch，需要对数据进行统计分析，怎么做

由于需要提升项目的搜索质量，最近研究了一下Elasticsearch，一款非常优秀的分布式搜索程序。最开始的一些笔记放到github，这里只是归纳总结一下。

首先，为什么要使用Elasticsearch？最开始的时候，我们的项目仅仅使用MySQL进行简单的搜索，然后一个不能索引的like语句，直接拉低MySQL的性能。后来，我们曾考虑过sphinx，并且sphinx也在之前的项目中成功实施过，但想想现在的数据量级，多台MySQL，以及搜索服务本身HA，还有后续扩容的问题，我们觉得sphinx并不是一个最优的选择。于是自然将目光放到了Elasticsearch上面。

根据官网自己的介绍，Elasticsearch是一个分布式搜索服务，提供Restful API，底层基于Lucene，采用多shard的方式保证数据安全，并且提供自动resharding的功能，加之github等大型的站点也采用 Elasticsearch作为其搜索服务，我们决定在项目中使用Elasticsearch。

对于Elasticsearch，如果要在项目中使用，需要解决如下问题：

索引，对于需要搜索的数据，如何建立合适的索引，还需要根据特定的语言使用不同的analyzer等。

搜索，Elasticsearch提供了非常强大的搜索功能，如何写出高效的搜索语句？

数据源，我们所有的数据是存放到MySQL的，MySQL是唯一数据源，如何将MySQL的数据导入到Elasticsearch？

对于1和2，因为我们的数据都是从MySQL生成，index的field是固定的，主要做的工作就是根据业务场景设计好对应的mapping以及search语句就可以了，当然实际不可能这么简单，需要我们不断的调优。

而对于3，则是需要一个工具将MySQL的数据导入Elasticsearch，因为我们对搜索实时性要求很高，所以需要将MySQL的增量数据实时导入，笔者唯一能想到的就是通过row based binlog来完成。而近段时间的工作，也就是实现一个MySQL增量同步到Elasticsearch的服务。

Lucene

Elasticsearch底层是基于Lucene的，Lucene是一款优秀的搜索lib，当然，笔者以前仍然没有接触使用过。:-)

Lucene关键概念：

Document：用来索引和搜索的主要数据源，包含一个或者多个Field，而这些Field则包含我们跟Lucene交互的数据。

Field：Document的一个组成部分，有两个部分组成，name和value。

Term：不可分割的单词，搜索最小单元。

Token：一个Term呈现方式，包含这个Term的内容，在文档中的起始位置，以及类型。

Lucene使用Inverted index来存储term在document中位置的映射关系。

譬如如下文档：

Elasticsearch Server 1.0 （document 1）

Mastring Elasticsearch （document 2）

Apache Solr 4 Cookbook （document 3）

使用inverted index存储，一个简单地映射关系：

Term

Count

Docuemnt

1.0 1

4 1

Apache 1

Cookbook 1

Elasticsearch 2 .

Mastering 1

Server 1

Solr 1

对于上面例子，我们首先通过分词算法将一个文档切分成一个一个的token，再得到该token与document的映射关系，并记录token出现的总次数。这样就得到了一个简单的inverted index。

Elasticsearch关键概念

要使用Elasticsearch，笔者认为，只需要理解几个基本概念就可以了。

在数据层面，主要有：

Index：Elasticsearch用来存储数据的逻辑区域，它类似于关系型数据库中的db概念。一个index可以在一个或者多个shard上面，同时一个shard也可能会有多个replicas。

Document：Elasticsearch里面存储的实体数据，类似于关系数据中一个table里面的一行数据。

document由多个field组成，不同的document里面同名的field一定具有相同的类型。document里面field可以重复出现，也就是一个field会有多个值，即multivalued。

Document type：为了查询需要，一个index可能会有多种document，也就是document type，但需要注意，不同document里面同名的field一定要是相同类型的。

Mapping：存储field的相关映射信息，不同document type会有不同的mapping。

对于熟悉MySQL的童鞋，我们只需要大概认为Index就是一个db，document就是一行数据，field就是table的column，mapping就是table的定义，而document type就是一个table就可以了。

Document type这个概念其实最开始也把笔者给弄糊涂了，其实它就是为了更好的查询，举个简单的例子，一个index，可能一部分数据我们想使用一种查询方式，而另一部分数据我们想使用另一种查询方式，于是就有了两种type了。不过这种情况应该在我们的项目中不会出现，所以通常一个index下面仅会有一个 type。

在服务层面，主要有：

Node: 一个server实例。

Cluster：多个node组成cluster。

Shard：数据分片，一个index可能会存在于多个shards，不同shards可能在不同nodes。

Replica：shard的备份，有一个primary shard，其余的叫做replica shards。

Elasticsearch之所以能动态resharding，主要在于它最开始就预先分配了多个shards（貌似是1024），然后以shard为单位进行数据迁移。这个做法其实在分布式领域非常的普遍，codis就是使用了1024个slot来进行数据迁移。

因为任意一个index都可配置多个replica，通过冗余备份的方式保证了数据的安全性，同时replica也能分担读压力，类似于MySQL中的slave。

Restful API

Elasticsearch提供了Restful API，使用json格式，这使得它非常利于与外部交互，虽然Elasticsearch的客户端很多，但笔者仍然很容易的就写出了一个简易客户端用于项目中，再次印证了Elasticsearch的使用真心很容易。

Restful的接口很简单，一个url表示一个特定的资源，譬如/blog/article/1，就表示一个index为blog，type为aritcle，id为1的document。

而我们使用http标准method来操作这些资源，POST新增，PUT更新，GET获取，DELETE删除，HEAD判断是否存在。

这里，友情推荐httpie，一个非常强大的http工具，个人感觉比curl还用，几乎是命令行调试Elasticsearch的绝配。

一些使用httpie的例子:

# create

http POST :9200/blog/article/1 title="hello elasticsearch" tags:='["elasticsearch"]'

# get

http GET :9200/blog/article/1

# update

http PUT :9200/blog/article/1 title="hello elasticsearch" tags:='["elasticsearch", "hello"]'

# delete

http DELETE :9200/blog/article/1

# exists

http HEAD :9200/blog/article/1

索引和搜索

虽然Elasticsearch能自动判断field类型并建立合适的索引，但笔者仍然推荐自己设置相关索引规则，这样才能更好为后续的搜索服务。

我们通过定制mapping的方式来设置不同field的索引规则。

而对于搜索，Elasticsearch提供了太多的搜索选项，就不一一概述了。

索引和搜索是Elasticsearch非常重要的两个方面，直接关系到产品的搜索体验，但笔者现阶段也仅仅是大概了解了一点，后续在详细介绍。

同步MySQL数据

Elasticsearch是很强大，但要建立在有足量数据情况下面。我们的数据都在MySQL上面，所以如何将MySQL的数据导入Elasticsearch就是笔者最近研究的东西了。

虽然现在有一些实现，譬如elasticsearch-river-jdbc，或者elasticsearch-river-mysql，但笔者并不打算使用。

elasticsearch-river-jdbc的功能是很强大，但并没有很好的支持增量数据更新的问题，它需要对应的表只增不减，而这个几乎在项目中是不可能办到的。

elasticsearch-river-mysql倒是做的很不错，采用了python-mysql-replication来通过binlog获取变更的数据，进行增量更新，但它貌似处理MySQL dump数据导入的问题，不过这个笔者真的好好确认一下？话说，python-mysql-replication笔者还提交过pull解决了minimal row image的问题，所以对elasticsearch-river-mysql这个项目很有好感。只是笔者决定自己写一个出来。

为什么笔者决定自己写一个，不是因为笔者喜欢造轮子，主要原因在于对于这种MySQL syncer服务（增量获取MySQL数据更新到相关系统），我们不光可以用到Elasticsearch上面，而且还能用到其他服务，譬如cache上面。所以笔者其实想实现的是一个通用MySQL syncer组件，只是现在主要关注Elasticsearch罢了。

项目代码在这里go-mysql-elasticsearch，现已完成第一阶段开发，内部对接测试中。

go-mysql-elasticsearch的原理很简单，首先使用mysqldump获取当前MySQL的数据，然后在通过此时binlog的name和position获取增量数据。

一些限制：

binlog一定要变成row-based format格式，其实我们并不需要担心这种格式的binlog占用太多的硬盘空间，MySQL 5.6之后GTID模式都推荐使用row-based format了，而且通常我们都会把控SQL语句质量，不允许一次性更改过多行数据的。

需要同步的table最好是innodb引擎，这样mysqldump的时候才不会阻碍写操作。

需要同步的table一定要有主键，好吧，如果一个table没有主键，笔者真心会怀疑设计这个table的同学编程水平了。多列主键也是不推荐的，笔者现阶段不打算支持。

一定别动态更改需要同步的table结构，Elasticsearch只能支持动态增加field，并不支持动态删除和更改field。通常来说，如果涉及到alter table，很多时候已经证明前期设计的不合理以及对于未来扩展的预估不足了。

更详细的说明，等到笔者完成了go-mysql-elasticsearch的开发，并通过生产环境中测试了，再进行补充。

总结

最近一周，笔者花了不少时间在Elasticsearch上面，现在算是基本入门了。其实笔者觉得，对于一门不懂的技术，找一份靠谱的资料（官方文档或者入门书籍），蛋疼的对着资料敲一遍代码，不懂的再问google，最后在将其用到实际项目，这门技术就算是初步掌握了，当然精通还得在下点功夫。

现在笔者只是觉得Elasticsearch很美好，上线之后铁定会有坑的，那时候只能慢慢填了。话说，笔者是不是要学习下java了，省的到时候看不懂代码就惨了。:-)

-

关于spl中SELECT使用

楼上的言语太过简单了！数据表都已经创建起来了，假设我们已经插入了许多的数据，我们就可以用自己喜欢的方式对数据表里面的信息进行检索和显示了，比如说：可以象下面这样把整个数据表内的内容都显示出来

select * from president;

也可以只选取某一个数据行里的某一个数据列

select birth from president where last_name=’Eisenhower’;

select语句的通用形式如下：

select 你要的信息

from 数据表(一个或多个)

where 满足的条件

select语句有几个子句，他们的各种搭配能帮你查出最感兴趣的信息，这些子句可以很简单，也可以很复杂,看看作者是如何详细讲解的

１， 用各种操作符来设定检索条件

要想让select语句只把满足特定条件的记录检索出来，就必须给它加上where字句来设置数据行的检索条件。只有这样，才能有选择地把数据列的取值满足特定要求的那些数据行挑选出来。可以针对任何类型的值进行查找，比如说，对数值进行搜索

select * from score where score95; //显示所有分数在９５分以上的信息

也可以针对字符串值进行查找

select last_name,first_name from president where last_name=’Tom’; //找出所有姓tom的总统

还可以对不同类型的值进行组合查找

select last_name,first_name,birth,state from president

where birth’1950-1-1’ and (state=’VA’ or state=’BA’);

//找出1950年前出生于VA州或ＢＡ州的总统

可见 where子句中可以使用的是算术操作符（+-*/%）,比较操作符(=)以及逻辑运算符，我们应该熟练理解这些操作符的含义（都很简单）

２， NULL 值的特别处理

这是一种不属于任何类型的值。它通常用来表示“没有数据”“数据未知”“数据缺失”“数据超出取值范围”“与本数据列无关”“与本数据列的其它值不同”等多种含义。在许多情况下，NULL 值是非常有用的。

我们的各种操作符是不能对NULL 值进行处理的，如果相对NULL 值进行查找，用的是 is null 或 is not null 来进行判断，举例如下：

select last_name,first_name,birth,state from president

where death is null; //找出所有没死的总统

在某些情况下，NULL 值是很有用的类型，大家慢慢就会理解的。

３， 查询结果进行排序

一般说来，如果创建了一个数据表并向里面插入了一些记录，当发出一条select * from name命令的时候，数据记录在查询结果中的先后顺序通常与它们被插入时的先后顺序一样．这当然符合我们的思维习惯．但这只是一种＂想当然＂的假设而已，事实上，但记录被删除时，数据库中会产生一些空的区域，MYSQL会用新的记录来填补这些区域，也就是说，这个时候本假设就不正确了．因此我们必须记住一点，从服务器返回的记录行的先后顺序是没有任何保证的！如果想要按照一定的顺序，就必须使用order by 子句来设置这个顺序．

select last_name,first_name,birth,state from president

order by last_name; //让总统们的名字按字母顺序排列

还可以设置排列的升序降序

select last_name,first_name from president

order by state DESC,last_name ASC;

//先按照出生地的降序排列，同出生地的按照姓氏的升序排列

注意：如果结果中含有NULL 值，默认情况下他们总是出现在查询结果的开头。

４， 限制查询结果中数据行个数

这个简单，只要用limit 子句就可以了，看两个例子：

select last_name,first_name,birth,state from president

order by birth limit 5; //只想看前５个

order by birth limit 10,5; //返回从第11个记录开始的5个记录（跳过了10个）

小技巧：从president表中随机找出一个总统来玩：

select last_name,first_name,birth,state from president

order by rand() limit 1; //这是用了表达式求值的方法，在哪里都管用

５， 对输出列进行求值和命名

为了提高效率，MYSQL还可以把表达式的计算结果当作输出列的值。表达式可以很简单，也可以很复杂。例如：下面这个查询有两个输出列，前一个输出列对应一个非常简单的表达式（一个常数），而后一个输出列则对应着一个使用了多个算术运算符和两个函数调用的复杂表达式。

Select 17,format(sqrt(3*3+4*4),0))

输出：17 5

再看这个命令：把两个输出列合并成一个

select concat(first_namem,’ ‘,last_name),concat(city,’,’,state) from president;

如果合并之后输出列的标题过长，则可以给其一个别名，如：

select concat(first_namem,’ ‘,last_name) as name,

concat(city,’,’,state) as birth place

from president;这样就比较美观了。

６， 和日期有关的问题

首先记住：在MYSQL中，年份是放到最前面的！我们通常对日期进行下列操作：

按日期进行排序

查找某个日期或日期范围

提取日期中的年，元，日各个部分

计算两个日期的间隔

用一个日期求出另外一个日期

看例子：

select * from event where date=’2002-10-01’ //看看这天有何考试信息？

select last_name,first_name,birth,state from president

where death’1900-01-01’ and death’2000-01-01’; //看看上个世纪死了几个？

三个函数year,month,dayofmonth可以分别分离出日期中的年月日来。

select last_name,first_name,birth from president

where month(birth)=3; //谁生在3月 ？

where month(birth)=7 and dayofmonth(birth) =6; //谁生在7月6日？(汤姆克鲁斯？)

函数to_days可以把日期转换为天数。

select last_name,first_name,birth to_days(death)-to_days(birth) as age from president

可以看看这帮家伙都活了多少天！你自己把它改为年吧。

日期值的减法运算还能帮我们计算出现在距离某个特定日期还有多长的时间，这正是我们用来找到需要在近期内缴纳会费的会员的办法：

select last_name,first_name,expiration from member

where (to_days(expiration)-to_days(curdate())60; //有些人60天内需要花钱了！

７， 模式匹配

有些情况下，模糊查询是很必要的，我们使用like和not like加上一个带通配符的字符串就可以了。共有两个通配符”_”(单个字符)和””（多个字符）

select concat(first_namem,’ ‘,last_name) as name,

where last_name like ‘W%’; //找到以Ｗ或w开头的人

where last_name like ‘％W%’; //找到名字里面Ｗ或w开头的人

８， 设置和使用SQL变量

MYSQL 3.23.6以上的版本可以使用查询结果来设置变量，我们就能够方面的把一些结果保存起来以供他用。变量的命名规格是：@name, 赋值语法是 @name:=value ( pascal?) 使用起来也简单：

select @birth:=birth from president

where last_name =’adsltiger’; //执行完成后我们就就会有一个@birth变量可用

用一下试试：

select concat(first_namem,’ ‘,last_name) as name from president

where birth@birth order by birth; //看看那些人比我大！

＊9， 生成统计信息

单纯依靠手工来生成统计信息是一项既艰苦又耗时还容易出错的工作，如果我们能熟练掌握用数据库来生成各种统计信息的技巧，他就会成为很有威力的信息处理工具。作者在这里用了许多篇幅讲这个主题，为了便于大家理解，我分解开来论述：

9.1 找出一组数据中到底有多少种不同的值是一项比较常见的统计工作，而关键字distinct就可以把查询结果中的重复数据清除掉。如

select distinct state from president //看看美国总统们都来自那些州？(重复的不计)

9.2用count()函数来统计相关记录的个数，注意其使用方法：count(*)计算所有的，NULL也要；count(数据列名称) NULL值不计算在内。

select count(*) from president;

9.3如果我们想知道班级内的男女生数目？该如何查询呢？最简单的方法是

select count(*) from student where sex=’f’;

select count(*) from student where sex=’m

但是如果使用count函数结合group by关键字，一行命令就搞定了

select sex,count(*) f rom student group by sex;

我们可以看到，与反复使用彼此类似的查询来分别统计某数据列不同取值出现次数的做法相比， 把count(*)和group by字句相结合使用有许多优点，主要表现在：

在开始统计自前，不必知道被统计的数据列里面有多少种不同的取值

因为只用了一个查询命令，我们可以对输出做排序的处理

select state,count(*) as count from president

group by state order by count desc limt4; //看看出生总统最多的前四个州是哪几个？

9.4除了count()，我们还用其他一些统计函数，如求出最小值的min()，求最大值的max()，求和的sum()，求平均值的avg()，在实际工作中，这些函数时经常用到的！

*10, 从多个表提取信息

我们目前的例子都是从一个表里面提取信息，但数据库的真正威力还在于用“关系”来综合多个数据表里面的记录，这种操作称之为“关联”或“结合”我们可以看到，select需要给出多个数据表里面的信息（不可重复）；from需要知道从哪几个表里面做事；where则对几个表之间的关联信息作出详细的描述。

首先我们要学习最可靠的数据列引用方式：数据表名.数据列名。这样在查询中就一定不会混淆这个数据列到底在哪一个表里。

例子１:查询某一天内的学生们的考试成绩，用学号列出。

select scroe.student_id,event_date,score.score.event.type

from event,score

where event.date=’2003-09-12’

and event.event_id=score.event_id

首先，利用event数据表把日期映射到一个考试事件编号，在利用这个编号把score表内相匹配的考试分数找出来。关联两个表，一个查询搞定。

例子2:查询某一天内的学生们的考试成绩，用姓名列出。

select student.name event.name,score.score,event.type

form event,score,student

where event.date=’2003-09-12’

and event.event_id= score.event_id

and scroe.student_id=student.student_id;

关联三个表，一个查询搞定。

例子３:查询一下缺席学生的名字，学号，缺席次数

select student.student_id,student_name

count(absence.date) as absences

from student,absence

where student.student_id=absence.student_id //关联条件

group by student.student_id;

简单的关联操作就介绍到这里。事实上，对于关联的知识我们需要学的很多很多，比如说，我们怀疑某一个数据表内不存在和我们相关的数据，把么在关联查询的时候如何处理这个表呢？这就涉及到内联接，外联接，左联接，右联接的许多新概念了。不知道大家还有没有信心向下看我的笔记？在本书第四章里面，对关联进行了十分详细的论述，看来“在SQL里面，干粗活的是select”的说法再对不过了。

我们了解了select命令的如此之多的用法，感到了它的灵活性，许多字句的组合能够形成一个非常“精妙”的ＳＱＬ语句，在基础没有打好之前，我等初学者目前还没有必要去钻研那些技巧性很高的东西，“一定程度的创造性是必要的，但太专业或充满技巧的代码则是各种 bug的发源地，同时也是若干个不眠之夜的前奏”

注意:Group by 里面没有的字段,在Select字段列表里就必须加上聚合函数(sum,max,min,count...),不然那句SQL就是错的

怎样把accesss数据库转换为mysql数据库，有什么软件吗？

mysql笔记：数据导入导出 mysql自带有mysqldump命令，可以把数据库做备份用。 MySQL有很多可以导入数据的方法，然而这些只是数据传输中的一半，另外的一般是从MySQL数据库中导出数据。有许多的原因我们需要导出数据。一个重要的原因是用于备份数据库。数据的造价常常是昂贵的，需要谨慎处理它们。经常地备份可以帮助防止宝贵数据地丢失；另外一个原因是，也许您希望导出数据来共享。 在这个信息技术不断成长的世界中，共享数据变得越来越常见。 比方说Macmillan USA维护护着一个将要出版的书籍的大型数据库。这个数据库在许多书店之间共享，这样他们就知道哪些书将会很快出版。医院越来越走向采用无纸病历记录，这样这些病历可以随时跟着你。世界变得越来越小，信息也被共享得越来越多。有很多中导出数据得方法，它们都跟导入数据很相似。因为，毕竟，这些都只是一种透视得方式。从数据库导出的数据就是从另一端导入的数据。这里我们并不讨论其他的数据库各种各样的导出数据的方法，您将学会如何用MySQL来实现数据导出。 注意:mysqldump比直接拷贝数据库文件夹速度要慢 但,直接复制文件夹不能100%转移到其它机子上用使用mysqldump工具，基本用法是：shell mysqldump [OPTIONS] database [tables]如果你不给定任何表，整个数据库将被导出。通过执行mysqldump --help，你能得到你mysqldump的版本支持的选项表。注意，如果你运行mysqldump没有--quick或--opt选项，mysqldump将在导出结果前装载整个结果集到内存中，如果你正在导出一个大的数据库，这将可能是一个问题。

名称栏目：mysql笔记怎么写 mysql写法
本文网址：http://azwzsj.com/article/dooidph.html