SQL基础知识

SQL基础知识

（一）基本语句：SELECT、FROM、WHERE、ORDER、LIMIT
一段SQL语言实例如下：

SELECT col1, col2
FROM table1
WHERE col3  > 5 AND col4 LIKE '%os%'
ORDER BY col5
LIMIT 10;

SELECT ：读取并显示数据。我们将这称为查询
DROP TABLE：是删除数据库中表的语句
CREATE TABLE：是一个在数据库中创建新表的语句

• 真实情境很少用到DROP和CREATE语句，需要很高的权限，往往数据管理员才可以

• 每个查询至少有一个 SELECT 和 FROM 语句，其中：
  SELECT 语句用于放置要显示数据的列
  FROM语句用于放置要从中提取数据的表

• SQL 查询忽略空格，因此可以根据需要在代码之间添加尽可能多的空格和空行，并且查询结果是相同的。

• 分号：
  根据 SQL 环境，查询结尾可能需要一个执行的分号。我们认为在每个语句的末尾添加一个分号是最好的做法，如果环境能够一次显示多个结果，那么这样做还可以一次运行多个命令。

最好的做法：

SELECT account_id
FROM orders;

(二)基本操作

• 查询：
  SELECT：用于提醒查询要返回哪些列。
  FROM：用于提醒查询在哪个表中查询。注意，这个表中需要有列。

SELECT *  
FROM orders;

练习
尝试编写自己的查询，以便为 orders 表中的所有订单选择 id、account_id 和 occurred_at 列

SELECT id, account_id, occurred_at
FROM orders

文本格式化最佳方法

• 大写命令（SELECT、FROM），小写查询中的其他内容是常见做法
• 表和变量名中不需要空格
• 通常在列名中使用下划线，避免使用空格
  在 SQL 中使用空格有点麻烦。 在 Postgres 中，如果列或表名称中有空格，就需要使用双引号括住这些列/表名称
  例如：FROM \”Table Name\”，而不是 FROM table_name）。在其他环境中，可能会使用方括号，例如：FROM [Table Name]
• 在查询中使用空格

SELECT account_id FROM orders

等价于

SELECT account_id
FROM orders

• SQL 不区分大小写
• 
  
  • LIMIT 数字：限制返回结果的行数

SELECT *
FROM demo.orders
ORDER BY occurred_at
LIMIT 10

默认升序排列（ascending)
末尾加DESC：降序排列
练习
编写一个查询，将响应限制在前 15 行，和包括 web_events 表中的 occurred_at、account_id 和 channel 字段

SELECT occurred_at, account_id, channel
FROM web_events
LIMIT 15;

• ORDER：按顺序返回查询结果

SELECT account_id,total_amt_usd
FROM orders
ORDER BY account_id,total_amt_usd DESC
-- id递增，且在id相同的前提下，total_amt_usd降序排列
SELECT account_id,total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC,account_id
-- total_amt_usd严格降序排列

练习
1、编写查询，返回 orders 表的前 10 个订单。包含 id、occurred_at 和 total_amt_usd

SELECT id, occurred_at, total_amt_usd
FROM orders
ORDER BY occurred_at
LIMIT 10;

2、编写一个查询，基于 total_amt_usd 返回前 5 个最高的 订单 (orders 表)。包括 id、account_id 和 total_amt_usd

SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC 
LIMIT 5;

3、编写一个查询，基于 total 返回前 20 个最低 订单 (orders 表)。包括 id、account_id 和 total

SELECT id, account_id, total
FROM orders
ORDER BY total
LIMIT 20;

4、编写一个查询，返回按从最新到最早排序的 订单 中的前 5 行，但需首先列出每个日期的最大 total_amt_usd

SELECT *
FROM orders
ORDER BY occurred_at DESC, total_amt_usd DESC
LIMIT 5;

5、编写一个查询，返回按从最早到最新排序的 订单 中的前 10 行，但需首先列出每个日期的最小 total_amt_usd

SELECT *
FROM orders
ORDER BY occurred_at, total_amt_usd
LIMIT 10;

WHERE：添加查找限制条件，常用到符号>、<、>=、<=、!=、=
练习
1、从 订单 表提取出大于或等于 1000 的 gloss_amt_usd 美元数额的前五行数据（包含所有列）

SELECT *
FROM orders
WHERE gloss_amt_usd >= 1000
LIMIT 5;

2、从 订单 表提取出小于 500 的 total_amt_usd美元数额的前十行数据（包含所有列）

SELECT *
FROM orders
WHERE total_amt_usd < 500
LIMIT 10;

同时，WHERE 语句也可以与非数字数据一起使用。我们可以使用 = 和 ！= 运算符。 还需要确保在文本数据中使用引号
注：单引号或双引号都可以 - 如果原始文本中有引号，就一定要注意

通常将 WHERE 与非数字数据字段一起使用时，我们会使用 LIKE、NOT 或 IN 运算符
练习
过滤账户（accounts ）表格，从该表格中筛选出 Exxon Mobil 的 name、website 和 primary point of contact (primary_poc)

SELECT name, website, primary_poc
FROM accounts
WHERE name = 'Exxon Mobil';

• LIMIT：限制返回的行数

SELECT *
FROM orders
WHERE count_id=4251
ORDER BY occurred_at
LIMIT 100

其中，WHERE要放在FROM之后，ORDER BY或LIMIT之前
支持非值查找，用单引号括起来

派生列：将现有的列组合，生成的新列称为派生列。
常见运算：+、-、*、/

运算顺序
PEMDAS 规则：http://www.purplemath.com/modules/orderops.htm

• 合并几列创建一个新的列
  derived column(派生列)：将现有列进行组合

SELECT account_id,
       occurred_at,
       standard_qty,
       gloss_qty,
       poster_qty,
       gloss_qty + poster_qty AS nonstandard_qty
FROM orders

可以添加新的列，名字为：nonstandard_qty
命名惯例：无代码和空格
练习
1、创建一个用 standard_amt_usd 除以 standard_qty 的列，查找每个订单中标准纸的单价。将结果限制到前 10 个订单，并包含 id 和 account_id 字段

SELECT id, account_id, standard_amt_usd/standard_qty AS unit_price
FROM orders
LIMIT 10;

2、编写一个查询，查找每个订单海报纸的收入百分比。 只需使用以 _usd 结尾的列。 （在这个查询中试一下不使用总列）。包含 id 和 account_id 字段

SELECT id, account_id,
poster_amt_usd/(standard_amt_usd + gloss_amt_usd + poster_amt_usd) AS post_per
FROM orders;

逻辑运算的简介：LIKE、IN、NOT、AND & BETWEEN、OR
LIKE
可用于进行类似于使用 WHERE 和 = 的运算，但是这用于你可能 不 知道自己想准确查找哪些内容的情况。
IN：用于执行类似于使用 WHERE 和 = 的运算，但用于多个条件的情况。
NOT：这与IN 和LIKE 一起使用，用于选择 NOT LIKE 或NOT IN某个条件的所有行。
AND & BETWEEN：可用于组合所有组合条件必须为真的运算。
OR：可用于组合至少一个组合条件必须为真的运算

详解：
LIKE： 寻找包含某个字段的信息
需要使用通配符(wildcard),下面的通配符是%

SELECT *
FROM web_events_full
WHERE referer_url LIKE '%google%';

%代表任意数量的字符
练习
1、所有以 ‘C’ 开头公司名。

SELECT name
FROM accounts
WHERE name LIKE 'C%';

2、名称中包含字符串 ‘one’ 的所有公司名。

SELECT name
FROM accounts
WHERE name LIKE '%one%';

3、所有以 ‘s’ 结尾的公司名。

SELECT name
FROM accounts
WHERE name LIKE '%s';

IN：限制查找的字段范围

SELECT *
FROM accounts
WHERE name IN('Walmart','Apple')

单引号（‘’）将字符串括起来，值可以直接输入

SELECT *
FROM accounts
WHERE account_id IN(1001,1021)

调出账户里的1001号和1021号的信息

练习
使用 客户 表查找 Walmart、Target 和 Nordstrom 的name (客户名称), primary_poc (主要零售店), and sales_rep_id (销售代表 id)

练习：
编写一个查询，查找每个订单海报纸的收入百分比。 只需使用以 _usd 结尾的列。 （在这个查询中试一下不使用总列）。包含 id 和 account_id 字段

SELECT id,account_id,poster_amt_usd/(standard_amt_usd+gloss_amt_usd+poster_amt_usd) AS post_per
FROM orders
LIMIT 10

• NOT：查找条件取反

SELECT sales_rep_id,name
FROM accounts
WHERE sales_rep_id NOT IN(321500,321570)
ORDER BY sales_rep_id

• AND和BETWEEN：条件的取与

SELECT *
FROM orders
WHERE occurred_at >= '2016-04-01' AND occurred_at <= '2016-10-01'
ORDER BY occurred_at DESC

练习
使用客户表查找所有不以 ‘C’ 开始但以 ‘s’ 结尾的公司名

select name
FROM accounts
WHERE name NOT LIKE 'C%' and name LIKE '%s'

练习
使用 web_events 表查找通过 organic 或 adwords 联系，并在 2016 年的任何时间开通帐户的个人全部信息，并按照从最新到最旧的顺序排列。

SELECT *
FROM web_events
WHERE channel IN('organic','adwords') AND occurred_at BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY occurred_at DESC
--BETWEEN 一般情况下不包括端点值，假设时间是 00:00:00（即午夜），这就是我们将右边的时间点设置为 '2017-01-01' 的原因了

SELECT account_id,
       occurred_at,
       standard_qty,
       gloss_qty,
       poster_qty
FROM orders
WHERE (standard_qty = 0 OR gloss_qty = 0 OR poster_qty = 0)
AND occurred at >= '2016-10-01'

练习
查找以 ‘C’ 或 ‘W’ 开头的所有公司名 (company names)，主要联系人 (primary contact) 包含 ‘ana’ 或 ‘Ana’，但不包含 ‘eana

SELECT *
FROM accounts
WHERE name LIKE 'C%' OR name LIKE 'W%' 
      AND(primary_poc LIKE '%ana%' OR primary_poc LIKE '%Ana%')
      AND primary_poc NOT LIKE '%eana%'

SQL命令概述

语句	使用方法	其他详细信息
SELECT	SELECT Col1,Col2,…	提供你需要的列
FROM	FROM Table	提供列所在的表格
LIMIT	LIMIT	10
ORDER BY	ORDER BY Col	根据列命令表格。与 DESC 一起使用
WHERE	WHERE Col > 5	用于过滤结果的一个条件语句
LIKE	WHERE Col LIKE ‘%me%’	仅提取出列文本中具有 ‘me’ 的行
IN	WHERE Col IN (‘Y’, ‘N’)	仅过滤行对应的列为 ‘Y’ 或 ‘N’
NOT	WHERE Col NOT IN (‘Y’, “N’)	NOT 经常与 LIKE 和 IN 一起使用
AND	WHERE Col1 > 5 AND Col2 < 3	过滤两个或多个条件必须为真的行
OR	WHERE Col1 > 5 OR Col2 < 3	过滤一个条件必须为真的行
BETWEEN	WHERE Col BETWEEN 3 AND 5	一般情况下，语法比使用 AND 简单一些

其他

• 不区分大小写
• 关键词顺序很重要

SELECT col1, col2
FROM table1
WHERE col3  > 5 AND col4 LIKE '%os%'
ORDER BY col5
LIMIT 10;

移动平均值
移动平均值用于将数据线性化，以便更容易观察长期趋势，也不会因日常波动而错乱。
例如，假设你想可视化服装零售店的销售趋势。从每天的数据开始，而图表看起来太不稳定，因为更多的人在周末购物，所以销售额会在周末飙升。
使用移动平均值可使每日波动平滑一些，也可以观察长期趋势。