2.1.4 常用的SQL語法（下篇）

在學習前，同樣需要準備好數(shù)據(jù)源，按照第2.1.3節(jié)介紹的方法導入數(shù)據(jù)，下載地址：http://pan.baidu.com/s/1boO8qMN。

1．修改客戶端字符集亂碼

MySQL會出現(xiàn)中文亂碼的原因主要有以下3個。

· 服務端設定編碼。

· 建表時編碼。

· 客戶端查詢數(shù)據(jù)編碼不匹配。

對于這個問題，可以執(zhí)行下面的命令，查看MySQL的默認編碼格式，如圖2-9所示。

圖2-9 MySQL默認的編碼格式

SHOW VARIABLES LIKE "%char%";

在使用MySQL時，不管是查詢數(shù)據(jù)，還是構(gòu)建表結(jié)構(gòu)，總會遇到中文字符顯示為亂碼的問題（標志是顯示很多問號）。

上述問題是由于客戶端編碼的不匹配造成的，甚至有時候直接通過客戶端創(chuàng)建表和insert初始化數(shù)據(jù)，最后查詢表數(shù)據(jù)會有很多問號。

如何匹配一致的編碼規(guī)則呢？主要以character_set_client和character_set_connection的編碼來確定。如果客戶端顯示亂碼，可以使用set names utf8/gbk設置默認的編碼格式。效果等同于同時設置以下3個參數(shù)的值，代碼如下。表數(shù)據(jù)的正常顯示，如圖2-10所示。

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

圖2-10 表數(shù)據(jù)的正常顯示

但是解決任何問題都需要從問題的源頭去處理，這樣才直接有效。所以為避免中文字符顯示為亂碼的問題，在創(chuàng)建數(shù)據(jù)庫和表時，要設置好參數(shù)。

創(chuàng)建數(shù)據(jù)庫的代碼如下。

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

創(chuàng)建表結(jié)構(gòu)的代碼如下。

      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 ’活躍區(qū)域’,
        `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 ’創(chuàng)建時間’,
        PRIMARY KEY (`id`),
        INDEX `mid_index` (`mid`)
      ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8

設置好后，就不會出現(xiàn)顯示亂碼的問題了。

2．如何理解索引

索引是針對數(shù)據(jù)所建立的目錄，它可以加快查詢速度，但降低了增、刪、改操作的速度。

創(chuàng)建索引的原則主要有以下3個。

· 不要過度創(chuàng)建索引。

· 在查詢最頻繁的列上增加索引。

· 如果構(gòu)建索引，這一列盡量是離散值，而不要是過于連續(xù)的區(qū)間。

索引主要有以下3個類型。

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

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

· 主鍵索引：primary key不能重復。

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

查看一張表上所有索引的代碼如下。

   Show index from input_user_base;

查詢結(jié)果如圖2-11所示。

圖2-11 表input_user_base的查詢結(jié)果

3．學會模糊查詢

在數(shù)據(jù)庫及Hive的數(shù)據(jù)倉庫查詢中，肯定會有對中文字符的查詢，如用戶的省份區(qū)域。對于這類字符的查詢，經(jīng)常使用模糊查詢。模糊查詢主要細分為以下兩種。

· %通配任意字符。

· _通配單個字符。

select * from input_user_base where active_area like '%省%'；查詢結(jié)果如圖2-12所示。

圖2-12 模糊查詢的通配前后任意字符

select * from input_user_base where active_area like '%市’；查詢結(jié)果如圖2-13所示。select * from input_user_base where active_area like '_市’；查詢結(jié)果如圖2-14所示。

圖2-13 模糊查詢的通配前任意字符

圖2-14 模糊查詢的通配單個字符

4．理解count的使用

count的主要功能是計數(shù)。我們要分析的不僅僅是這一點，而是關于count(*)、count(1)和count(列名)三者的區(qū)別。

select count(*), count(1), count(age) from input_user_base；查詢結(jié)果如圖2-15所示。

圖2-15 input_user_base的查詢結(jié)果

對于myisam引擎的表是沒有區(qū)別的（這種引擎內(nèi)部有一個計數(shù)器在維護著行數(shù)），而Innodb引擎用count(*)直接讀行數(shù)，無論表的列中包含的是空值（NULL）還是非空值。使用count(age)對特定列中具有值的行進行計數(shù)，忽略NULL值。

5．理解union和union all的區(qū)別

union用于合并兩個或多個SELECT語句的結(jié)果集。要注意以下3點。

· SELECT語句必須擁有相同數(shù)量的列。

· 列也必須擁有相似的數(shù)據(jù)類型。

· 每條SELECT語句中的列的順序必須相同。

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

查詢結(jié)果如圖2-16所示。

圖2-16 去重后的合并結(jié)果

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

另外，union all后結(jié)果集可以進行排序，代碼如下。

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

查詢結(jié)果如圖2-17所示。注意order by是針對合并后的結(jié)果集進行的排序。

圖2-17 排序后的結(jié)果

6．理解order by的使用

對于MySQL排序而言，在很多數(shù)據(jù)查詢的場景，當最終結(jié)果集出來后，仍然可以進行排序，從而得到更想要的輸出，代碼如下。

      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時有實戰(zhàn)操作二次排序的機會）。

7．理解Having的使用

在SQL中增加Having子句的原因是由于Where無法與聚合函數(shù)一起使用，代碼如下。

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

查詢結(jié)果如圖2-18所示。

圖2-18 對用戶會員等級計數(shù)篩選

8．理解Join系列（左連接、內(nèi)連接和外連接）

      LEFT JOIN

顧名思義，是從左表那里返回所有的行，不考慮右表是否存在相同的key，記錄行數(shù)以左表為準，右表沒對應上的默認為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作為關聯(lián)key，數(shù)據(jù)查詢以input2為準。左邊的這張表在查詢后，數(shù)據(jù)不會發(fā)生任何變化（數(shù)據(jù)和數(shù)值）。而右邊這張表input_user_base，會根據(jù)mid對應input2表的mid值，如圖2-19所示。

圖2-19 簡單的樣式

上面介紹的是最簡單的場景，左表和右表的key鍵mid都只是一對一的關系。大家可以思考下面三個場景，同樣是在上述左表關聯(lián)中。

（1）如果input1中的同一個mid出現(xiàn)多個行，而input_user_base中mid都是唯一值，這種查詢最后的數(shù)據(jù)總量如何呢？如圖2-20所示。

圖2-20 場景1的表關聯(lián)

（2）如果input2中的同一個mid出現(xiàn)唯一值，而input_user_base中mid出現(xiàn)多個行，這種查詢最后的數(shù)據(jù)總量又如何呢？如圖2-21所示。

圖2-21 場景2的表關聯(lián)

（3）如果input3中的同一個mid出現(xiàn)多個行，而input_user_base中mid也出現(xiàn)多個行，這種查詢最后的數(shù)據(jù)總量又如何呢？如圖2-22所示。

圖2-22 場景3的表關聯(lián)

對于INNER JOIN、RIGHT JOIN和FULL JOIN都是同一個道理。

學習SQL的內(nèi)容，是為了讓更多初學者以及入門者快速掌握SQL的實際應用場景，以及對數(shù)據(jù)思維能力的培養(yǎng)。這里提供了一個業(yè)務實踐題目，讀者可以下載進行練習。

業(yè)務實踐題目地址：http://pan.baidu.com/s/1c2ERllQ（密碼：74a8）。

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

在進入大數(shù)據(jù)生態(tài)圈之前，所有應該掌握的基本知識，不管是Python系列，還是SQL系列，都是為了讓大家能夠培養(yǎng)基本編程能力、動手能力、簡單數(shù)據(jù)分析能力和數(shù)據(jù)處理能力。接下來就介紹Python的一些入門知識。