大白話之小白也能懂SQL（一）

03-07

首先什麼是SQL，這麼說吧，大家都用過excel，雖然excel很強大，但是很多地方還是不夠靈活，不夠友好，比如你有1000萬數據，你要放進excel裡面，你確實可以放，但是使用的時候很不舒服，去查詢數據，搜索你需要的數據都不是很方便，那這個時候excel的超級升級版資料庫就出現了，這玩意是什麼呢？其實你就把它看成是一個強化了的excel就好理解了，可能專業人士覺得不妥，但是不要太在意，大白話做比喻嘛，excel關於數據處理的功能它都有，而且更加強大，只不過有一些門檻，就是你要會資料庫的操作指令，也就是sql語句，關於數據可視化，你用資料庫搭配tableau這樣的商業軟體，你會發現excel太老土了，但是excel 仍然不能放棄，畢竟是完全免費的，畢竟該有的功能人家也都有，畢竟人家幾乎沒有什麼學習成本，現用現搜就可以了，用多了你就會了，但是sql這個東西，你還是要學一學sql語句的，所以學習成本還是會高一些的。tableau這樣的商業軟體也需要稍作學習，所以總的來說，excel不丟棄的基礎上學習資料庫，學習一些商業分析軟體那真是完美的搭配。在數據分析/處理中，用的比較多的是MYSQL這樣的傳統型資料庫，關於NOSQL不再此次文章中提及，像MONGODB這樣的，REDIS這樣的運用的更多的地方是開發工作中，當然了，你說你就要用mongo，那也行，而且人家mongo速度還更快，只是作為入門還是mysql之類的傳統資料庫吧，畢竟非互聯網公司應該基本上都是傳統型，互聯網公司有一大部分也是傳統型資料庫，所以先學mysql，之後再去看看mongoDB之類的吧

MYSQL的安裝看我前面的文章：

Python配置虛擬環境以及安裝/連接mysql(Linux-Ubuntu)；

MySQL小巧而強大的管理工具

下面就開始大白話sql語句。

1.建立一張表（對比excel新建表）

excel新建表其實很簡單，滑鼠點點就好了，mysql呢需要建表語句，但是並不是說就不能像excel那樣滑鼠點點，打打列名就好了，mysql也是可以的，比如用navicat這樣的mysql可視化管理工具，那就會非常方便，但是學習mysql，sql語句逃不掉的。

首先新建一個資料庫，建立新表語句在navicat中展示

然後進入這個新建的資料庫，右擊查詢按鈕選擇新建查詢會生成一個對話框讓你寫sql

然後就可以開始寫sql語句了。

首先新建一張表，怎麼建立呢？看語句

create table emp (id int not null auto_increment PRIMARY KEY,name VARCHAR (255)) DEFAULT CHARSET UTF8;

這句sql什麼意思？意思是說我要create一張表，這表名字叫emp，這個emp裡面有兩個列，第一個列是ID號，這個ID號裡面只能放int，也就是你只能放整數這個意思，並且你必須放，還不能空著，完了每增加一個行數據，這個ID都會自己增加，就是1，2，3，4，5.......這樣，並且設置這個ID為主鍵，什麼事主鍵呢？主要的作用主要確定該數據的唯一性。比如說ID=1,NAME=xxxx。我們要在資料庫中，找到這條數據可以使用select * from emp where id=1（select語句後面有詳細講解）這樣就可以把xxxx查找出來了。而這個xxxx，有可能出現同名，所以用ID來做主鍵，因為ID自動增加不可能重複。下面的name裡面只能放varchar類型，長度不能超過255，意思就是你放字元串可以，你放int就不可以，長度不能超過255，這個長度實際上你可以自行指定，這一點就比excel使用起來要更加有約束。下面最後括弧外面的意思是默認的字符集是utf8，這個一定要加上，為什麼呢，這個涉及到到編碼，這裡不細說了，google搜索一些編碼，或者你直接搜素utf8看看wiki什麼的就明白了，也不需要深扣他，要用到了或者出現字符集錯誤，亂碼之類的再去研究，會記得更牢固。

創建好了的emp表就是這樣的。

既然會建一張表，那就再建另外一張表格test

create table test (id int not null auto_increment PRIMARY KEY,name VARCHAR (255)) DEFAULT CHARSET UTF8;

現在兩張表空空蕩蕩什麼都沒有，下面就要往這個裡面放數據。

2.插入多條數據

insert into emp (name) VALUE(章三),(李斯),(王五),(趙劉),(燕青),(小李),(姬霜),(姬軒),(小魚兒),(花無缺),(小辣椒),(惡人);insert into test (name) VALUE(章三),(李斯),(王五),(趙劉),(軒轅),(奔波爾霸),(霸波兒奔),(小鑽風),(小魚兒),(花無缺),(小辣椒),(惡人);

這個語句什麼意思就很簡單了，insert into插入數據進入test/emp表中的name列，value數據值是下面括弧的所有人名。

現在有了數據了，突然我又想加入年齡這個數據，但是沒有age這個列，那就要再加入一個age列

3.新增數據列

alter table emp add column age int after name;alter table test add column age int after name;

這個語句什麼意思呢，意思是alter改變表emp/test去增加一列，這個列的名字叫age，這一列只能放int，並且這一列要放在name這一列後面。

新增了數據列之後再增加數據.

replace into emp (id, name, age) VALUE(1,章三,12),(2,李斯,22),(3,王五,33),(4,趙劉,41),(5, 軒轅, 55),(6, 奔波爾霸, 16),(7,霸波兒奔,33),(8, 小鑽風,29),(9, 小魚兒, 12),(10,花無缺,26),(11, 小辣椒,54),(12, 惡人, 55);replace into test (id, name, age) VALUE(1,章三,12),(2,李斯,22),(3,王五,33),(4,趙劉,41),(5, 軒轅, 12),(6, 奔波爾霸, 9),(7,霸波兒奔,5),(8, 小鑽風,19),(9, 小魚兒, 12),(10,花無缺,26),(11, 小辣椒,54),(12, 惡人, 55);

現在你可能決定這個sql語句挺麻煩，但是等你全部弄好了，查詢的時候就很隨心所欲了，並且，現在是純sql語句操作，等你用python/scala去操作數據的插入和提取的時候會發現真實方便啊，當然了前提你要去學python/scala，哈哈，學習成本又高了呢！

以上的語句應該不需要用大白話解釋了，看英文也能看懂。

現在數據有了，下面做一些數據查詢的操作

4.select語句

查看一張表所有的數據

select * from emp

簡單不？這就是查看所有的數據

select name, age from amp;

這個肯定也能明白咯，就是光看name和age這兩列數據

select name, age from emp where id <10 and age < 20;

這句什麼意思？選擇name，age兩列數據，並且過濾id在10以內，年齡小與20的數據

select name, age from emp where age > 10 order by age desc;

這句sql說選擇name，age但是age要大於10歲，並且查詢出來的表格要按照年齡降序

SELECT id, name, age FROM emp WHERE age>10UNION ALLSELECT id, name, age FROM test

這個是指求這兩張表格的並集，並且要求年齡大與10，那麼下面肯定有交集

SELECT name, ageFROM test where exists ( select agefrom emp where emp.age = test.age) and age > 10order by age desc;

這個看似好像有點複雜，但是沒關係，大白話一句句說，這個意思就是說先選擇test表的2列數據，然後把這個test的數據進行過濾，怎麼過濾啊，where exists里再選擇emp的age列數據，再把emp的2列數據中age年齡和test數據中age年齡相等的提取出來，這些操作結束後得到一個新的表格，再把這個新的表格過濾and年齡要大於10歲，並且按照年齡大小降序排列展示。這樣子應該能明白了。全是大白話。

下面再來一個模糊搜索，找出兩個表名字中有霸字的數據

select * from emp where emp.name like %霸% union all select * from test where test.name like %霸% order by age

like你就理解為模糊搜索，只要包涵某個字元，你就給我找出來！

還有一個having，這個和where用法十分相似，但是又有一些不同之處，

select age, count(age) as count from emp group by age having count > 1 order by age desc

首先解釋一下這句sql的意思，就是說我選擇age這一列數據，在對這一列數據進行函數處理，就是count計數，把計數結果作為新的一列叫做count，然後按照age來分組，就是比如10歲在一個組，20在一個組這個意思，加上前面的count就是說10歲有幾個人，20歲有幾個人，然後在過濾，過濾條件是這個count計數必須大於1才行，最後按照age降序排列。

那麼問題就是為什麼用having不用where？這裡你要使用了where一定報錯，為什麼呢？看人家mysql自己咋說的唄！

1、SQL標準要求HAVING必須引用GROUP BY子句中的列或用於總計函數中的列。不過，MySQL支持對此工作性質的擴展，並允許HAVING涉及SELECT清單中的列和外部子查詢中的列。2、HAVING子句必須位於GROUP BY之後ORDER BY之前。3、如果HAVING子句引用了一個意義不明確的列，則會出現警告。在下面的語句中，col2意義不明確，因為它既作為別名使用，又作為列名使用：mysql> SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;標準SQL工作性質具有優先權，因此如果一個HAVING列名既被用於GROUP BY，又被用作輸出列清單中的起了別名的列，則優先權被給予GROUP BY列中的列。4、HAVING子句可以引用總計函數，而WHERE子句不能引用。【這應該是開發者在特定的情況下採用HAVING子句的最大原因】5、不要將HAVING用於應被用於WHERE子句的條目，這樣用可能不會出錯，但是mysql不推薦，既然它要求，我們遵循就可以了。

這裡提到了函數，那還有幾個常用函數，這裡除了可以用count你還可以換成sum求和函數，avg平均數函數，min最小值，max最大值，當然這個測試用的數據可能不大適合用所有函數來嘗試，但是這個意思應該好明白。

mysql去重複distinct

select DISTINCT name, age from emp where age is not null

這句表示選擇name和age列數據都不重複的數據，然後age列不能為NULL，就是不能是空數據

下面我們來聊一聊join這個語句，這個應該也是常用的語句，首先你應該看一下這張圖。

我覺得其實有這張圖可能都不需要再多說什麼了，但是，既然是大白話，那就要堅持用大白話來聊sql！

首先看left join這個語句

select * from emp a left join test b on a.age=b.age;

這句話什麼意思，對照圖中左上角第一個圖，就是說，我選取emp的所有數據，再選取test數據，再對比，看看test裡面有多少數據在emp裡面，怎麼看呢？以什麼標準看呢？這就是後面的on語句後面的，必須年齡相等的數據，就是說test裡面有多少數據年齡這一項和emp裡面是一樣的，如果一樣就拿出來。應該明白了吧。

再看看左上角第一個圖下面的那張圖，這個left join就有點區別了，看一句sql

select * from emp a left join test b on a.name=b.name where b.name is NULL

這句話意思是什麼，和上面的語句很相似，這句意思是說，把兩張表數據拿出來對比，找出emp這張表獨有的數據，怎麼個獨有法？就是說兩張表中name數據只出現在emp中的這部分數據，明白不？就是說a表是甲，已，丙，丁，b表是丙，戊，己，這個語句執行完了就是甲，已，丁。

既然有left join，那麼肯定有right join，其實和left join是一個道理，只是把上面兩個句子中的left改成right就可以了。

像下面這樣改寫

select * from test a right join emp b on a.name=b.name select * from test a right join emp b on a.name=b.name where a.name is NULL

然後再看一下中間的那張圖，就是inner join，先看語句

select * from emp a inner join test b on a.name=b.name

這句話什麼意思呢？很好理解，就是說啊，我選擇emp，test這兩張表，找出name都在這兩張表中的數據，其實就是求name的交集，對比圖看是不是很好理解！

還剩下最後下面兩個full outer join語句，其實說到這裡，再看這兩個語句，應該就已經明白這個套路了。

那麼還是來看看，首先第一個左邊那張圖

select * from emp a full outer join test b on a.name=b.name

正常來講這樣就可以了，但是！mysql不支持這麼寫，Oracle 、DB2、SQL Server、PostgreSQL 支持 Full JOIN，mysql不支持，但是mysql也有相應的語句應對，

就是可以通過 LEFT JOIN + UNION + RIGHT JOIN 的方式來實現。

left join，union，right join前面都說過了，這裡只要組合一下就可以實現full outer join

select * from emp a left join test b on a.id=b.id union select * from emp a right join test b on a.id=b.id；

仔細看這條語句，其實不就是求個並集嘛，是不是還挺簡單的。

舉一反三，最後一個肯定也就差不多啦

select * from emp a left join test b on a.id=b.id where b.id is nullunion select * from emp a right join test b on a.id=b.id where a.id is null;

那麼到這裡其實常用的可能也就這些，還有很多的語法就需要去看教程，其實掌握到這個程度，可以應付很多數據的處理，剩下的其實在日常使用的時候查就可以了，就像excel那樣，隨用隨查，因為你不需要把每一個語句都如同背書一樣背下來，運用之中去查，去用，google大法會讓你輕鬆很多，這裡再給你一個網站去查各種語句：SQL教程_w3cschool

這個網站的語句和教程夠你應付所有的數據處理工作了。

下面再來看看一些複雜點的SQL語句。

NO.1

create table users (user_id int not null auto_increment PRIMARY KEY,username VARCHAR (255)) DEFAULT CHARSET UTF8;insert into users (username) VALUE(John Doe ),(Jane Don),(Alice Jones),(Lisa Romero);create table training_details (user_training_id int not null auto_increment PRIMARY KEY,user_id int (10),training_id int (10),training_date VARCHAR (200)) DEFAULT CHARSET UTF8;insert into training_details (user_id,training_id,training_date) VALUE(1,1,"2015-08-02"),(3,1,"2015-08-03"),(2,3,"2015-08-04"),(2,1,"2015-08-03"),(5,2,"2015-08-04"),(4,1,"2015-08-02"),(2,4,"2015-08-05"),(2,1,"2015-08-02"),(3,3,"2015-08-03"),(1,2,"2015-08-02"),(1,1,"2015-08-02");

首先創建兩張表，並且分別插入數據待使用，如果文章仔細看到這裡，這個創建語句應該完全能看得懂了！

查詢要求：從users和training_details選取user_id,username,training_id,training_date並且對用戶training次數計數，列名取為count，按照training_date降序組成新的表以查看user的training情況

首先分析，從users和training_details選取user_id,username,training_id,training_date並且對用戶training次數計數，列名取為count

光這句要求我覺得還是好寫出來的

SELECT u.user_id, username, training_id, training_date, count( user_training_id ) AS count FROM users u LEFT JOIN training_details t ON t.user_id = u.user_id這一句是不是就是表示了上面那句要求，先把要求的user_id,username,training_id,training_date放進select中，然後利用函數count處理user_training_id來得到計數次數，並且取名為count，又因為這是兩張表，同時取兩張表的數據你必須join兩張表才可以，這就相當於從join後的整張表裡面取數據，join的時候注意過濾條件，就是兩張表對應的用戶user的ID要一一對應，這是必須的，不然用戶就對不上號了

上面的寫完了還是不行的，不完整，還要繼續寫，接著分析，

這裡還缺一個句沒有寫就是分組，為什麼呢，你選出來的數據不分組就會出現異常，你會發現count次數不對，而且數據只有一條，這就是你不分組的結果，顯然是個錯誤的結果，因為查詢要求是以查看user的training情況，那麼就必須按照

u.user_id,

username,

training_id,

training_date

這幾項分組，然後count計數就可以準確的對應不同的用戶展示count次數，也就是trainning次數所以上面的語句要加上一段寫成這樣。

SELECT u.user_id, username, training_id, training_date, count( user_training_id ) AS count FROM users u JOIN training_details t ON t.user_id = u.user_id GROUP BY u.user_id, username, training_id, training_date

再加上按照training_date降序組成新的表以查看user的training情況，那就簡單了。

注意這裡要用HAVING不是WHERE，前面有詳細講到

結果：

NO.2

create table Customer (CustomerID int not null auto_increment PRIMARY KEY,CustomerName VARCHAR (200),weight float (11)) DEFAULT CHARSET UTF8;insert into Customer (CustomerName,weight) VALUE(John Doe ,100.34),(Jane Don,55.88),(Alice Jones,69.091),(Lisa Romero,44);

創建一張表格

查詢 Customer的CustomerName欄位並且用分號鏈接成一行輸出

SELECT group_concat(CustomerName separator ;) as DATA From Customer;

這個其實沒什麼可說的，就是用函數來處理了，

結果：

把體重按照小數點分割為kg和g

select weight, floor(weight) as kg, substr(floor(weight) - TRUNCATE(weight,3), 4) as gms from Customer;

這個其實也沒啥好說，就是函數處理，都是很基本的sql語句，只是套用了函數。

結果：

NO.3

create table Employee (Emp_Id int not null PRIMARY KEY,Emp_name VARCHAR (200),Salary int (10),Manager_Id int (10)) DEFAULT CHARSET UTF8;insert into Employee (Emp_Id,Emp_name,Salary,Manager_Id) VALUE(10,Anil,50000,18),(11,Vikas,75000,16),(12,Nisha,40000,18),(13,Nidhi,60000,17),(14,Priya,80000,18),(15,Mohit,45000,18),(16,Rajesh,90000,null),(17,Raman,55000,16),(18,Santosh,65000,17);

創建一張員工表格

查詢要求，計算Emp_Id和Manager_Id相等的數據的平均值，再按照emp_id和emp_name分組

，這張表仔細看看，其實第一列第二列就是員工id和員工姓名，第三行就是薪水，第四行什麼意思呢？而且還只有16，17，18，其實是說員工id為16，17，18的是領導，其他人分別受這三個人管理，也就是這麼個關係，看張圖

這張圖看明白沒，查詢要求就是要你查rajesh，Ramans，Santosh這三級領導的下屬的平均工資，這下應該很明白查詢要求想要你幹啥了，直接看sql語句

select b.emp_id as "Manager_Id", b.emp_name as "Manager", avg(a.salary) as "Average_Salary_Under_Manager"from Employee a, Employee bwhere a.manager_id = b.emp_idgroup by b.emp_id, b.emp_nameorder by b.emp_id;

乍一看好像沒看明白，一段一段來，首先

select b.emp_id as "Manager_Id", b.emp_name as "Manager",

好理解吧，這個b其實是給這張表起的別稱，後面的Employee b就是呼應這裡的b，

就是把員工id和名字找出來，並且起別名為領導id和領導姓名，因為要從員工裡面找出領導嘛，

avg(a.salary) as "Average_Salary_Under_Manager"

這句的avg是求平均數的函數，求誰的平均數？求員工薪水的平均數，這個時候又冒出來一個a，怎麼回事？和上面的b一個鳥樣，別稱呀，下面的Employee a與之呼應，你肯定會問mysql還能這麼操作？對，就是有這種騷操作！別問為什麼別鑽牛角尖，just do it！把這個平均數也取別稱"Average_Salary_Under_Manager"意思就是xx領導下的員工平均薪水。

然後from Employee a, Employee b不用說了吧！

下面按照emp_id和emp_name分組還有排序我覺得不用廢話了，所以整個sql應該都能看明白了吧！

結果：

NO.4

create table A (id int not null auto_increment PRIMARY KEY,city VARCHAR (255)) DEFAULT CHARSET UTF8;insert A (city) VALUE(上海),(廣州),(上海),(北京),(上海),(北京),(廣州)create table B (id int not null auto_increment PRIMARY KEY,created VARCHAR (255)) DEFAULT CHARSET UTF8;insert B (created) VALUE(2018/3/2),(2018/3/1),(2015/6/9),(2018/3/1),(2018/3/1),(2015/3/21),(2017/1/8)

創建一張用戶註冊表，不同城市用戶註冊情況，

查詢要求，表A是用戶的註冊時間表，表B是用戶所在地，寫出各地區每月新註冊用戶數的查詢SQL

廢話不說，直接上sql

SELECT id, city, COUNT(city) AS new_register FROM ( SELECT A.id,city,created FROM A LEFT JOIN B ON B.id=A.id WHERE date_format(created, %Y/%m) = date_format(curdate() , %Y/%m)) a GROUP BY city ORDER BY new_register DESC;

首先選擇id，city,以及city的計數，因為每增加一個註冊，對應這個註冊的城市就會出現一次，所以作為註冊數的計量是可以的，把這個計數取別稱為new_register，後面from是重點，這裡from一個處理過的表格，怎麼處理呢？

從A表和B表join後的新表中選擇id，city，created，並且過濾條件是A和B的id要一致，因為要保證是同一個用戶id，兩張表id相同才表示是同一個註冊用戶，下面的where是什麼呢？因為查詢要求是要每個月的，那就必須獲取當天日期，mysql提供了這樣的處理函數，curdate()函數可以獲取當天日期，比如2018-3-3，然後加上date_format處理函數把2018-3-3處理成年月形式也就是2018/3，同樣的把用戶的註冊時間也通過同樣的方法處理為一致的格式，這樣只要年份月份相等就說明這個數據是本月註冊的，然後後面group bu，order by都是套路了，最後加上desc降序，搞定！

-----------------------------------------------------------------------

至此大白話之小白也能懂SQL（一）就到這裡吧！如果過程中有什麼錯誤，請指出，我及時更正！

-------------------------------------

更新大白話之小白也能懂SQL（二）