SQL
常用SQL
语法结构: SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT
sql
-- SELECT FROM
SELECT Company, OrderNumber FROM Orders;
-- ORDER BY
SELECT Company, OrderNumber FROM Orders ORDER BY Company;
SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber;
SELECT Company, OrderNumber FROM Orders ORDER BY Company IN ('HW'), Company, OrderNumber;
-- LIMIT
SELECT Company, OrderNumber FROM Orders LIMIT 1;
SELECT Company, OrderNumber FROM Orders LIMIT 1, 1;
-- FUNCTION
SELECT COUNT(*) AS NumberOfOrders FROM Orders;
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders;
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders;
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders;
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders;
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders;
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders;
-- GROUP BY / HAVING
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer;
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500;
高级查询
JOIN
sql
-- 引用两个表
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P;
-- JOIN
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName;
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName;
JOIN
JOIN: 如果表中有至少一个匹配, 则返回行
LEFT JOIN: 即使右表中没有匹配, 也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配, 也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配, 就返回行
窗口函数
语法: 窗口函数over([partition by 字段名] [order by 字段名 asc|desc])
over()中两个子句为可选项，partition by指定分区依据，order by指定排序依据
排序窗口函数语法
- rank()over([partition by 字段名] order by 字段名 asc|desc)
排序1,1,3,4 - dense_rank()over([partition by 字段名] order by 字段名 asc|desc)
排序1,1,2,3 - row_number()over([partition by 字段名] order by 字段名 asc|desc)
排序1,2,3,4
偏移分析函数语法
- lag(字段名,偏移量[,默认值])over([partition by 字段名] order by 字段名 asc|desc)
- lead(字段名,偏移量[,默认值])over([partition by 字段名] order by 字段名 asc|desc)
sql
select
yr
,party
,votes
,rank()over(partition by yr order by votes desc) as posn
from ge
where constituency = 'S14000021'
order by party,yr;
sql
select
name
,date_format(whn,'%Y-%m-%d') date
,confirmed 当天截至时间累计确诊人数
,lag(confirmed,1)over(partition by name order by whn) 昨天截至时间累计确诊人数
,(confirmed - lag(confirmed,1)over(partition by name order by whn)) 每天新增确诊人数
from covid
where name in ('France','Germany') and month(whn) = 1
order by whn;
子查询
sql
-- 单行子查询
SELECT Company, OrderNumber
FROM Orders
WHERE Company=(SELECT Company FROM CompanyS WHERE LOCATION='CQ');
-- 多行子查询
SELECT Company, OrderNumber
FROM Orders
WHERE Company IN (SELECT Company FROM CompanyS);
-- 多列子查询
SELECT Company, OrderNumber
FROM Orders
WHERE (Company, MaxNumber) IN (SELECT Company, MAX(MO) FROM CompanyS);
Excel/Tableau连接数据库
安装驱动
数据源导入数据