2.1.4 常用的SQL语法（下篇）_轻松学大数据挖掘：算法、场景与数据产品-QQ阅读男生玄幻网

上QQ阅读APP看本书，新人免费读10天

设备和账号都新为新人

2.1.4 常用的SQL语法（下篇）

在学习前，同样需要准备好数据源，按照第2.1.3节介绍的方法导入数据，下载地址：http://pan.baidu.com/s/1boO8qMN。

1．修改客户端字符集乱码

MySQL会出现中文乱码的原因主要有以下3个。

· 服务端设定编码。

· 建表时编码。

· 客户端查询数据编码不匹配。

对于这个问题，可以执行下面的命令，查看MySQL的默认编码格式，如图2-9所示。

图2-9 MySQL默认的编码格式

SHOW VARIABLES LIKE "%char%";

在使用MySQL时，不管是查询数据，还是构建表结构，总会遇到中文字符显示为乱码的问题（标志是显示很多问号）。

上述问题是由于客户端编码的不匹配造成的，甚至有时候直接通过客户端创建表和insert初始化数据，最后查询表数据会有很多问号。

如何匹配一致的编码规则呢？主要以character_set_client和character_set_connection的编码来确定。如果客户端显示乱码，可以使用set names utf8/gbk设置默认的编码格式。效果等同于同时设置以下3个参数的值，代码如下。表数据的正常显示，如图2-10所示。

      SET character_set_client='utf8';
      SET character_set_connection='utf8';
      SET character_set_results='utf8';

图2-10 表数据的正常显示

但是解决任何问题都需要从问题的源头去处理，这样才直接有效。所以为避免中文字符显示为乱码的问题，在创建数据库和表时，要设置好参数。

创建数据库的代码如下。

      CREATE DATABASE ‘test’
      CHARACTER SET 'utf8'
      COLLATE 'utf8_general_ci';

创建表结构的代码如下。

      CREATE TABLE `input_user_base` (
        `id` bigint(30) NOT NULL AUTO_INCREMENT COMMENT ’自增ID',
        `mid` bigint(20) DEFAULT NULL COMMENT ’用户id',
        `sex` varchar(50) DEFAULT NULL COMMENT ’性别’,
        `age` int(10) DEFAULT NULL COMMENT ’年龄’,
        `degree` varchar(50) DEFAULT NULL COMMENT ’学位’,
        `active_area` varchar(255) DEFAULT NULL COMMENT ’活跃区域’,
        `member_grade` int(11) DEFAULT NULL COMMENT ’会员等级’,
        `late_inv_time` varchar(255) DEFAULT NULL COMMENT ’最近登录时间’,
        `active` varchar(255) DEFAULT NULL COMMENT ’活跃度’,
        `city_change` varchar(255) DEFAULT NULL COMMENT ’城市变化’,
        `create_ymd` varchar(50) DEFAULT NULL COMMENT ’创建时间’,
        PRIMARY KEY (`id`),
        INDEX `mid_index` (`mid`)
      ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8

设置好后，就不会出现显示乱码的问题了。

2．如何理解索引

索引是针对数据所建立的目录，它可以加快查询速度，但降低了增、删、改操作的速度。

创建索引的原则主要有以下3个。

· 不要过度创建索引。

· 在查询最频繁的列上增加索引。

· 如果构建索引，这一列尽量是离散值，而不要是过于连续的区间。

索引主要有以下3个类型。

· 普通索引：index的作用仅仅是加快查询速度。

· 唯一索引：unique index行上的值不能重复。

· 主键索引：primary key不能重复。

需要注意的是，主键必唯一，但是唯一索引不一定是主键。在一张表上，只能有一个主键，但是可以用一个或多个唯一索引。

查看一张表上所有索引的代码如下。

   Show index from input_user_base;

查询结果如图2-11所示。

图2-11 表input_user_base的查询结果

3．学会模糊查询

在数据库及Hive的数据仓库查询中，肯定会有对中文字符的查询，如用户的省份区域。对于这类字符的查询，经常使用模糊查询。模糊查询主要细分为以下两种。

· %通配任意字符。

· _通配单个字符。

select * from input_user_base where active_area like '%省%'；查询结果如图2-12所示。

图2-12 模糊查询的通配前后任意字符

select * from input_user_base where active_area like '%市’；查询结果如图2-13所示。select * from input_user_base where active_area like '_市’；查询结果如图2-14所示。

图2-13 模糊查询的通配前任意字符

图2-14 模糊查询的通配单个字符

4．理解count的使用

count的主要功能是计数。我们要分析的不仅仅是这一点，而是关于count(*)、count(1)和count(列名)三者的区别。

select count(*), count(1), count(age) from input_user_base；查询结果如图2-15所示。

图2-15 input_user_base的查询结果

对于myisam引擎的表是没有区别的（这种引擎内部有一个计数器在维护着行数），而Innodb引擎用count(*)直接读行数，无论表的列中包含的是空值（NULL）还是非空值。使用count(age)对特定列中具有值的行进行计数，忽略NULL值。

5．理解union和union all的区别

union用于合并两个或多个SELECT语句的结果集。要注意以下3点。

· SELECT语句必须拥有相同数量的列。

· 列也必须拥有相似的数据类型。

· 每条SELECT语句中的列的顺序必须相同。

    SELECT mid, sex, age FROM input_user_base
    UNION
    SELECT mid, sex, age FROM input2

查询结果如图2-16所示。

图2-16 去重后的合并结果

注：UNION操作符选取不同的值。如果允许重复的值，使用UNION ALL。

    SELECT mid, sex, age FROM input_user_base
    UNION ALL
    SELECT mid, sex, age FROM input2

另外，union all后结果集可以进行排序，代码如下。

    SELECT mid, sex, age FROM input_user_base
    UNION ALL
    SELECT mid, sex, age FROM input2 order by mid

查询结果如图2-17所示。注意order by是针对合并后的结果集进行的排序。

图2-17 排序后的结果

6．理解order by的使用

对于MySQL排序而言，在很多数据查询的场景，当最终结果集出来后，仍然可以进行排序，从而得到更想要的输出，代码如下。

      select mid, sex, age from input_user_base order by mid

默认是采取增序排列，如果想按降序排列，可修改order by mid desc。对于多字段排序也很容易，代码如下。

      SELECT MID, age, sex  FROM input_user_base  ORDER BY MID DESC, age ASC
  LIMIT 10

这里的LIMIT在语句的最后，起到限制条目的作用。对于多字段排序，它会在第一个字段排序的基础上，进行二次排序，甚至是三次排序（在学习MapRedcue时有实战操作二次排序的机会）。

7．理解Having的使用

在SQL中增加Having子句的原因是由于Where无法与聚合函数一起使用，代码如下。

      select mid, count(member_grade) as num from input_user_base group by
  mid having(num)>=1

查询结果如图2-18所示。

图2-18 对用户会员等级计数筛选

8．理解Join系列（左连接、内连接和外连接）

      LEFT JOIN

顾名思义，是从左表那里返回所有的行，不考虑右表是否存在相同的key，记录行数以左表为准，右表没对应上的默认为NULL。

      SELECT s1.mid, s1.age, s1.sex, s2.mid, s2.age, s2.sex
      FROM input2 AS s1
      LEFT JOIN input_user_base AS s2 ON (s1.mid=s2.mid)

两张表是以mid作为关联key，数据查询以input2为准。左边的这张表在查询后，数据不会发生任何变化（数据和数值）。而右边这张表input_user_base，会根据mid对应input2表的mid值，如图2-19所示。

图2-19 简单的样式

上面介绍的是最简单的场景，左表和右表的key键mid都只是一对一的关系。大家可以思考下面三个场景，同样是在上述左表关联中。

（1）如果input1中的同一个mid出现多个行，而input_user_base中mid都是唯一值，这种查询最后的数据总量如何呢？如图2-20所示。

图2-20 场景1的表关联

（2）如果input2中的同一个mid出现唯一值，而input_user_base中mid出现多个行，这种查询最后的数据总量又如何呢？如图2-21所示。

图2-21 场景2的表关联

（3）如果input3中的同一个mid出现多个行，而input_user_base中mid也出现多个行，这种查询最后的数据总量又如何呢？如图2-22所示。

图2-22 场景3的表关联

对于INNER JOIN、RIGHT JOIN和FULL JOIN都是同一个道理。

注：在某些数据库中，左连接又称LEFT OUTER JOIN。一般在大数据生态圈的Hive中使用，都会默认为LEFT OUTER JOIN。

学习SQL的内容，是为了让更多初学者以及入门者快速掌握SQL的实际应用场景，以及对数据思维能力的培养。这里提供了一个业务实践题目，读者可以下载进行练习。

业务实践题目地址：http://pan.baidu.com/s/1c2ERllQ（密码：74a8）。

参考答案地址：http://pan.baidu.com/s/1hrDMSYC（密码：txij）。

在进入大数据生态圈之前，所有应该掌握的基本知识，不管是Python系列，还是SQL系列，都是为了让大家能够培养基本编程能力、动手能力、简单数据分析能力和数据处理能力。接下来就介绍Python的一些入门知识。

本周热推：

Computer Vision Projects with OpenCV and Python 3 Creating Concrete5 Themes Augmented Reality for Android Application Development Reporting with Microsoft SQL Server 2012 CORS Essentials