Excel是數(shù)據(jù)分析中最常用的工具 明未，利用Excel可以完成數(shù)據(jù)清洗槽华，預(yù)處理，以及最常見(jiàn)的數(shù)據(jù)分類亚隅，數(shù)據(jù)篩選硼莽，分類匯總，以及數(shù)據(jù)透視等操作煮纵，而這些操作用SQL一樣可以實(shí)現(xiàn)懂鸵。SQL不僅可以從數(shù)據(jù)庫(kù)中讀取數(shù)據(jù)，還能通過(guò)不同的SQL函數(shù)語(yǔ)句直接返回所需要的結(jié)果行疏，從而大大提高了自己在客戶端應(yīng)用程序中計(jì)算的效率匆光。
1 重復(fù)數(shù)據(jù)處理
查找重復(fù)記錄
SELECT * FROM user
Where (nick_name,password) in
(
SELECT nick_name,password
FROM user
group by nick_name,password
having count(nick_name)>1
);
查找去重記錄
查找id最大的記錄
SELECT * FROM user
WHERE id in
(SELECT max(id) FROM user
group by nick_name,password
having count(nick_name)>1
);
刪除重復(fù)記錄
只保留id值最小的記錄
DELETE c1
FROM customer c1,customer c2
WHERE c1.cust_email=c2.cust_email
AND c1.id>c2.id;
DELETE FROM user Where (nick_name,password) in
(SELECT nick_name,password FROM
(SELECT nick_name,password FROM user
group by nick_name,password
having count(nick_name)>1) as tmp1
)
and id not in
(SELECT id FROM
(SELECT min(id) id FROM user
group by nick_name,password
having count(nick_name)>1) as tmp2
);
插播一條，我自己是一名從事了多年開(kāi)發(fā)的Python老程序員酿联，辭職目前在做自己的Python私人定制課程终息，今年年初我花了一個(gè)月整理了一份最適合2019年學(xué)習(xí)的Python學(xué)習(xí)干貨，可以送給每一位喜歡Python的小伙伴贞让，想要獲取的可以來(lái)我的Python企鵝交流裙周崭，639+584+010，即可免費(fèi)獲取喳张。
2 缺失值處理
查找缺失值記錄
SELECT * FROM customer
WHERE cust_email IS NULL;
更新列填充空值
UPDATE sale set city = "未知"
WHERE city IS NULL;
UPDATE orderitems set
price_new=IFNULL(price_new,5.74);
查詢并填充空值列
SELECT AVG(price_new) FROM orderitems;
SELECT IFNULL(price_new,5.74) AS bus_ifnull
FROM orderitems;
3 計(jì)算列
更新表添加計(jì)算列
ALTER TABLE orderitems ADD price_new DECIMAL(8,2) NOT NULL;
UPDATE orderitems set price_new= item_price*count;
查詢計(jì)算列
SELECT item_price*count as sales FROM orderitems;
4 排序
多列排序
SELECT * FROM orderitems
ORDER BY price_new DESC,quantity;
查詢排名前幾的記錄
SELECT * FROM orderitems
ORDER BY price_new DESC LIMIT 5;
查詢第10大的值
SELECT DISTINCT price_new
FROM orderitems
ORDER BY price_new DESC LIMIT 9,1;
排名
數(shù)值相同的排名相同且排名連續(xù)
SELECT prod_price,
(SELECT COUNT(DISTINCT prod_price)
FROM products
WHERE prod_price>=a.prod_price
) AS rank
FROM products AS a
ORDER BY rank ;
5 字符串處理
字符串替換
UPDATE data1 SET city=REPLACE(city,'SH','shanghai');
SELECT city FROM data1;
按位置字符串截取
字符串截取可用于數(shù)據(jù)分列
MySQL 字符串截取函數(shù)：left(), right(), substring(), substring_index()
SELECT left('example.com', 3);
從字符串的第 4 個(gè)字符位置開(kāi)始取续镇，直到結(jié)束
SELECT substring('example.com', 4);
從字符串的第 4 個(gè)字符位置開(kāi)始取，只取 2 個(gè)字符
SELECT substring('example.com', 4, 2);
按關(guān)鍵字截取字符串
取第一個(gè)分隔符之前的所有字符销部，結(jié)果是www
SELECT substring_index('www.google.com','.',1);
取倒數(shù)第二個(gè)分隔符之后的所有字符摸航，結(jié)果是google.com;
SELECT substring_index('www.google.com','.',-2);
6 篩選
通過(guò)操作符實(shí)現(xiàn)高級(jí)篩選
使用 AND OR IN NOT 等操作符實(shí)現(xiàn)高級(jí)篩選過(guò)濾
SELECT prod_name制跟，prod_price FROM Products
WHERE vend_id IN('DLL01','BRS01');
SELECT prod_name FROM Products WHERE NOT vend_id='DLL01';
通配符篩選
常用通配符有% _ [] ^
SELECT * from customers WHERE country LIKE "CH%";
7 表聯(lián)結(jié)
SQL表連接可以實(shí)現(xiàn)類似于Excel中的Vlookup函數(shù)的功能
SELECT vend_id,prod_name,prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id=Products.vend_id;
SELECT prod_name,vend_name,prod_price,quantity
FROM OderItems,Products,Vendors
WHERE Products.vend_id=Vendors.vend_id
AND OrderItems.prod_id=Products.prod_id
AND order_num=20007;
自聯(lián)結(jié) 在一條SELECT語(yǔ)句中多次使用相同的表
SELECT c1.cust_od,c1.cust_name,c1.cust_contact
FROM Customers as c1,Customers as c2
WHERE c1.cust_name=c2.cust_name
AND c2.cust_contact='Jim Jones';
8 數(shù)據(jù)透視
數(shù)據(jù)分組可以實(shí)現(xiàn)Excel中數(shù)據(jù)透視表的功能
數(shù)據(jù)分組
group by 用于數(shù)據(jù)分組 having 用于分組后數(shù)據(jù)的過(guò)濾
SELECT order_num,COUNT(*) as items
FROM OrderItems
GROUP BY order_num HAVING COUNT(*)>=3;
交叉表
通過(guò)CASE WHEN函數(shù)實(shí)現(xiàn)
SELECT data1.city,
CASE WHEN colour = "A" THEN price END AS A,
CASE WHEN colour = "B" THEN price END AS B,
CASE WHEN colour = "C" THEN price END AS C,
CASE WHEN colour = "F" THEN price END AS F
FROM data1
