SQL查询语句

学而时习之,不亦乐乎?
——孔子


MySQL安装(基于Windows)

MySQL 教程

廖雪峰SQL教程

W3school SQL教程


  • 数据:NBA技术统计球员薪酬数据,如果一名球员在同一个赛季辗转多只球队,就会产生多行记录。

  • 本文基于MySQL。

变量排序

SELECT salary17_18,Player FROM salary ORDER BY salary17_18 DESC;

删除 & 增添列

ALTER TABLE salary 
DROP COLUMN Tm
ADD COLUMN Team VARCHAR;

重编码新变量

ALTER TABLE salary 
ADD salary17_18 FLOAT;
UPDATE salary SET salary17_18 = season17_18/1000000
f1 Player Tm season17_18 salary17_18
1 Stephen Curry GSW 34682550 34.6825
2 LeBron James CLE 33285709 33.2857
3 Paul Millsap DEN 31269231 31.2692
4 Gordon Hayward BOS 29727900 29.7279
5 Blake Griffin DET 29512900 29.5129

按特定行删除重复值

  • DISTINCT按选中的列去重。得到高龄球员榜
SELECT DISTINCT Player,Age FROM seasons ORDER BY Age DESC;
Player Age
Kevin Willis 44
Robert Parish* 43
Dikembe Mutombo* 42
Kevin Willis 42
Robert Parish* 42
SELECT pubid,adfmt,event,COUNT(DISTINCT imei),COUNT(DISTINCT idfa) FROM table_numb 
WHERE pubid= '20' AND adfmt <> 'chip' AND event = 'ac'

按条件筛选数据

  • 使用WHERE子句保留2017年之后的数据
SELECT * FROM seasons WHERE Year>=2017
f1 Year Player Pos Age Tm G GS MP PER TS%
24096 2017 Alex Abrines SG 23 OKC 68 6 1055 10.1 0.56
24097 2017 Quincy Acy PF 26 TOT 38 1 558 11.8 0.565
24098 2017 Quincy Acy PF 26 DAL 6 0 48 -1.4 0.355
24099 2017 Quincy Acy PF 26 BRK 32 1 510 13.1 0.587
24100 2017 Steven Adams C 23 OKC 80 80 2389 16.5 0.589

数据合并

横向连接

ALTER TABLE salary
DROP COLUMN Tm;
SELECT * FROM salary
JOIN seasons
ON salary.Player=seasons.Player
  • MySQL多对一、一对多匹配可以自动复制。

纵向连接

  • UNION ALL允许重复值
  • UNION不允许重复值
SELECT Player FROM salary
UNION ALL
SELECT Player FROM seasons; 
ORDER BY Player

按条件删除行

  • 删除斯蒂芬·库里

  • LIMIT语句控制结果显示的行数

DELETE FROM salary WHERE Player='Stephen Curry';
SELECT * FROM salary LIMIT 5
f1 Player season17_18 salary17_18
2 LeBron James 33285709 33.2857
3 Paul Millsap 31269231 31.2692
4 Gordon Hayward 29727900 29.7279
5 Blake Griffin 29512900 29.5129
6 Kyle Lowry 28703704 28.7037

视图

  • 在SQL中,视图是基于SQL语句的结果集的可视化的表。
CREATE VIEW teamage AS
SELECT DISTINCT Year,Tm, AVG(Age)
FROM season
GROUP BY Tm
ORDER BY Year;
f1 Player season17_18 salary17_18
2 LeBron James 33285709 33.2857
3 Paul Millsap 31269231 31.2692
4 Gordon Hayward 29727900 29.7279
5 Blake Griffin 29512900 29.5129
6 Kyle Lowry 28703704 28.7037

从视图中查询

SELECT COUNT(DISTINCT Tm) FROM teamage;