SQL增删改查


MySQL安装(基于Windows)

MySQL 教程

廖雪峰SQL教程

W3school SQL教程


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

  • 本文基于MySQL。此外建议尝试使用SQLite。SQLite是长期以来被低估的数据库:它开源、体积小、和Python与R等编程语言的交互性好。它不需要设置数据驱动。而且SQLite数据的储存只使用一个数据库文件,适用于多表管理。但是不同的SQL数据库语法略有区别。

  • SQLite文件见这里

变量排序

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;

空值的处理

来一道简单的Leecode题

某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。

Customers 表:

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表:

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格,你的查询应返回:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+
SELECT L1.Name AS Customers
FROM Customers AS L1
LEFT JOIN Orders ON Orders.CustomerId = L1.Id
WHERE Orders.CustomerId IS NULL;

只能采用IS NULL或IS NOT NULL,而不能采用=, <, <>, !=这些操作符来判断NULL

多表联结

遇事不决,多表联结 ———— 沃茨基·硕德

来道力扣题:

选出所有 bonus < 1000 的员工的 name 及其 bonus。

Employee 表单

+-------+--------+-----------+--------+
| empId |  name  | supervisor| salary |
+-------+--------+-----------+--------+
|   1   | John   |  3        | 1000   |
|   2   | Dan    |  3        | 2000   |
|   3   | Brad   |  null     | 4000   |
|   4   | Thomas |  3        | 4000   |
+-------+--------+-----------+--------+

empId 是这张表单的主关键字 Bonus 表单

+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+

empId 是这张表单的主关键字 输出示例:

+-------+-------+
| name  | bonus |
+-------+-------+
| John  | null  |
| Dan   | 500   |
| Brad  | null  |
+-------+-------+

答案:

# Write your MySQL query statement below
SELECT name, bonus
FROM Employee
LEFT JOIN Bonus ON Employee.empId =  Bonus.empId
WHERE Bonus.bonus < 1000 OR Bonus.bonus IS NULL;

FROM子查询

FROM子查询把内层的查询结果当成临时表,供外层sql再次查询。查询结果集可以当成表看待。临时表要使用一个别名。

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+

(player_id, event_date) 是这个表的两个主键 这个表显示的是某些游戏玩家的游戏活动情况 每一行是在某天使用某个设备登出之前登录并玩多个游戏(可能为0)的玩家的记录 请编写一个 SQL 查询,描述每一个玩家首次登陆的设备名称

查询结果格式在以下示例中:

Activity table:

+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-05-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result table:

+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 1         |
+-----------+-----------+

解答:

/* # 原答案
SELECT player_id, device_id
FROM Activity 
WHERE event_date in(SELECT min(event_date) FROM Activity GROUP BY  player_id);
*/

SELECT player_id, device_id
FROM Activity 
WHERE (player_id, event_date) IN (SELECT player_id, min(event_date) FROM Activity GROUP BY  player_id);

条件判断的运用

一个小学生 Tim 的作业是判断三条线段是否能形成一个三角形。

然而,这个作业非常繁重,因为有几百组线段需要判断。

假设表 triangle 保存了所有三条线段的三元组 x, y, z ,你能帮 Tim 写一个查询语句,来判断每个三元组是否可以组成一个三角形吗?

| x  | y  | z  |
|----|----|----|
| 13 | 15 | 30 |
| 10 | 20 | 15 |

对于如上样例数据,你的查询语句应该返回如下结果:

| x  | y  | z  | triangle |
|----|----|----|----------|
| 13 | 15 | 30 | No       |
| 10 | 20 | 15 | Yes      |
SELECT x, y, z, (CASE WHEN x + y > z 
AND x + z > y 
AND z + y > x 
THEN 'Yes' ELSE 'No' END) AS triangle
FROM triangle;

SELECT x, y, z, IF (x+y>z 
AND x+z>y 
AND y+z>x, 'Yes', 'No') AS triangle 
FROM triangle;

IF 用法类似于R语言的ifelse()函数。