SQL Structured Query Language 结构化查询语言

1.1 Manipulation 数据操纵

SELECT * FROM celebs; 提取celebs表中的全部数据

• 数据类型

integer 整数类型
text 文本字符串类型
date 日期类型
real 实数类型

• 创建表

CREATE TABLE table_name (
    column_1 data_type, 
    column_2 data_type, 
    column_3 data_type
  );

• 插入数据

INSERT INTO celebs (id, name, age)
VALUES (1, 'Justin Bieber', 21);

• 选取某一列

SELECT name FROM celebs;

• 更新数据

UPDATE celebs
SET age = 22
WHERE id = 1;

• 修改数据表

增加一列数据定义

ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;

• 删除数据

DELETE FROM celebs WHERE twitter_handle IS NULL;

1.2 Queries 数据查询

• 单表查询

SELECT name, imdb_rating FROM movies;

【DISINCT 操作符 保证查询结果为唯一值】

SELECT DISTINCT genre FROM movies;

【WHERE 操作符 过滤查询/条件查询】

SELECT * FROM movies WHERE imdb_rating > 8;

WHERE 条件运算符有
= 等于
!= 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于

【LIKE 操作符 模糊查询】

SELECT * FROM movies
WHERE name LIKE 'Se_en';

SELECT * FROM movies
WHERE name LIKE 'a%';

SELECT * FROM movies
WHERE name LIKE '%man%'

LIKE 通配符
_ 通配单个字符
% 通配零个或多个字符

A% 匹配所有开头为'A'的字符
%a 匹配所有结尾为'a'的字符

【BETWEEN ... AND ... 操作符 匹配范围内的记录】

SELECT * FROM movies
WHERE name BETWEEN 'A' AND 'J';

SELECT * FROM movies
WHERE year BETWEEN 1990 AND 2000
AND genre = 'comedy';

【AND & OR 操作符 逻辑过滤】

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录

SELECT * FROM movies
WHERE genre = 'comedy'
OR year < 1980;

【ORDER BY DESC/ASC 操作符 排序】

ASC 升序排序
DESC 降序排序

SELECT * FROM movies
ORDER BY imdb_rating DESC;

【LIMIT 操作符 限制记录数量】

SELECT * FROM movies
ORDER BY imdb_rating ASC
LIMIT 3;

1.3 Aggregate Functions 聚合函数

【COUNT(...) 计数函数】

SELECT COUNT(*) FROM fake_apps;

【GROUP BY 分组函数】

SELECT price, COUNT(*) FROM fake_apps
GROUP BY price;

SELECT price, COUNT(*) FROM fake_apps
WHERE downloads > 20000
GROUP BY price;

【SUM(...) 求和函数】

SELECT SUM(downloads) FROM fake_apps;

SELECT category, SUM(downloads) FROM fake_apps
GROUP BY category;

【MAX(...) 最大值函数】

SELECT MAX(downloads) FROM fake_apps;

SELECT name, category, MAX(downloads) FROM fake_apps
GROUP BY category;

【MIN(...) 最小值函数】

SELECT MIN(downloads) FROM fake_apps;

【AVG(...) 平均函数】

SELECT AVG(downloads) FROM fake_apps;

SELECT price, AVG(downloads) FROM fake_apps
GROUP BY price;

【ROUND(...) 舍入为指定小数位数函数】

SELECT price, ROUND(AVG(downloads), 2) FROM fake_apps
GROUP BY price;

1.4 Multiple Tables 多表

【PRIMARY KEY 主键】

定义为主键的一列不为NULL值，都是唯一值

CREATE TABLE artists(id INTEGER PRIMARY KEY, name TEXT)

【FOREIGN KEY 外键】

• 多表查询

SELECT albums.name, albums.year, artists.name FROM albums, artists;

【INNER JOIN 内连接】

SELECT
*
FROM
albums
JOIN artists ON
albums.artist_id = artists.id;

【LEFT JOIN 左连接】

SELECT
*
FROM
albums
LEFT JOIN artists ON
albums.artist_id = artists.id;

【AS 关键字 重命名列名】

SELECT
albums.name AS 'Album',
albums.year,
artists.name AS 'Artist'
FROM
albums
JOIN artists ON
albums.artist_id = artists.id
WHERE
albums.year > 1980;

1.5 Subqueries 子查询

• non-correlated subquery 非关联子查询

【嵌套查询】

SELECT *
FROM flights
WHERE origin in (
SELECT code
FROM airports
WHERE elevation < 2000);

SELECT a.dep_month,
   a.dep_day_of_week,
   AVG(a.flight_distance) AS average_distance
FROM (
    SELECT dep_month,
          dep_day_of_week,
           dep_date,
           sum(distance) AS flight_distance
      FROM flights
     GROUP BY 1,2,3
   ) a
GROUP BY 1,2
ORDER BY 1,2;

• correlated subquery 关联子查询

【外部查询取出一行后，再对内部查询中的每一行内进行运算，TRUE时候返回查询结果】

SELECT id
FROM flights AS f
WHERE distance < (
SELECT AVG(distance)
FROM flights
WHERE carrier = f.carrier
);

1.6 Set Operations

【UNION 并集】

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL会列出所有的值，即不去重

【INTERSECT 交集】

SELECT category FROM new_products
INTERSECT
SELECT category FROM legacy_products;

【EXCEPT 差集】

SELECT category FROM new_products
EXCEPT
SELECT category FROM legacy_products;

1.7 Conditional Aggregates 条件聚合

IS NULL 值为空
IS NOT NULL 值不为空

【CASE 控制结构】

SELECT
CASE
    WHEN elevation < 250 THEN 'Low'
    WHEN elevation BETWEEN 250 AND 1749 THEN 'Medium'
    WHEN elevation >= 1750 THEN 'High'
    ELSE 'Unknown'
END AS elevation_tier
, COUNT(*)
FROM airports
GROUP BY 1;

【COUNT(CASE WHEN)】

SELECT    state, 
COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) as count_high_elevation_aiports 
FROM airports 
GROUP BY state;

【SUM(CASE WHEN)】

SELECT origin, sum(distance) as total_flight_distance, sum(CASE WHEN carrier = 'DL' THEN distance ELSE 0 END) as total_delta_flight_distance 
FROM flights 
GROUP BY origin;

SELECT origin, 100.0*(sum(CASE WHEN carrier = 'UN' THEN distance ELSE 0 END)/sum(distance)) as percentage_flight_distance_from_united 
FROM flights 
GROUP BY origin;

SELECT state,
100.0 * sum(CASE WHEN elevation >= 2000 THEN 1 ELSE 0 END) / count(*)  as percentage_high_elevation_airports
FROM airports
GROUP BY state;

1.8 Date, Number, and String Functions 日期，数字和字符串函数

【Dates 时间函数】

SELECT DATETIME(manufacture_time)
FROM baked_goods;

SELECT DATE(manufacture_time), count(*) as count_baked_goods
FROM baked_goods
GROUP BY DATE(manufacture_time);

SELECT TIME(manufacture_time), count(*) as count_baked_goods
FROM baked_goods
GROUP BY TIME(manufacture_time);

在当前时间进行时间的增加，下述例子在当前时间上增加了1天2小时30分钟

SELECT DATETIME(manufacture_time, '+2 hours', '30 minutes', '1 day') as inspection_time
FROM baked_goods;

【Numbers 数字函数】

SELECT ROUND(ingredients_cost, 4) as rounded_cost
FROM baked_goods;

SELECT (number1 + number2) 返回两个数字的和
SELECT CAST(number1 AS REAL) / number3 CAST()函数用于将某种数据类型的表达式显式转换为另一种数据类型
SELECT ROUND(number, precision) 四舍五入数字，precision为精度

返回两列数据中同行的最小值

SELECT id, MIN(cook_time, cool_down_time)
FROM baked_goods;

【|| 字符串连接运算】

SELECT string1 || ' ' || string2; 利用"||"连接string1和一个空格和string2

【REPLACE() 字符串替换运算】

REPLACE(string,from_string,to_string) 将string列中的"from_string"字符串替换为"to_string"字符串