sql查询
I don’t use SQL very often and every time I need it, I find myself googling for the syntax of even the most basic operations. To help myself out, I have put together a cheat sheet of useful queries, all in one place. I hope you will find it useful, too.
我不经常使用SQL，每次需要它时，我都会搜寻甚至最基本操作的语法。 为了帮助自己，我将所有有用查询的备忘单放在一个地方。 我希望您也会发现它有用。
The queries are in Postgres, but the patterns are translatable to other SQL flavors. The notes are based on the great DataCamp courses, such as Introduction to SQL, Joining Data in SQL, and Introduction to Relational Databases in SQL, as well as on my own StackOverflow searches. Enjoy!
查询在Postgres中，但是模式可以转换为其他SQL风格。 这些说明基于出色的DataCamp课程，例如SQL入门，SQL中的联接数据和SQL中的关系数据库简介，以及我自己的StackOverflow搜索。 请享用！
符号的关键： (Key to symbols:)
- 🟠, 🔵, 🟢, etc. denote fields (i.e. variables, or columns) 🟠，🔵，🟢等表示字段(即变量或列)
🗂️ denotes the table nameWhen there is more than one table in a query, I refer to them as either 🗂️_1, 🗂️_2, etc., or left_🗂️ and right_🗂️, whichever’s more convenient.
🗂️表示表名当查询中有多个表时，我将它们称为🗂️_1，🗂️_2等，或将left_🗂️和right_🗂️命名，以较方便的为准。
收件人： (ToC:)
Creating, altering and removing tables, and filling them with data
1.创建，更改和删除表，并用数据填充它们 (1. Creating, altering and removing tables, and filling them with data)
# Creating table
CREATE TABLE 🗂️ (
# column_name data_type,
🔵 text,
🟠 numeric,
🟢 char(5)
);
# Adding a column
ALTER TABLE 🗂️
ADD COLUMN 🔵 text;
# Renaming a column
ALTER TABLE 🗂️
RENAME COLUMN 🔵 TO 🟠;
# Removing a column
ALTER TABLE 🗂️
DROP COLUMN 🔵;
# Fililng table with data from another table (distinct pairs of 🔵 and 🟠)
INSERT INTO 🗂️_1
SELECT DISTINCT 🔵, 🟠
FROM 🗂️_2;
# Fililng table manually
INSERT INTO 🗂️ (🔵, 🟠)
VALUES ("value_a", "value_B");
# Remove table
DROP TABLE 🗂️;2.概述表和列 (2. Overviewing tables & columns)
# get names of public tables
SELECT 🗂️
FROM information_schema.tables
WHERE table_schema = 'public';
# get column names of a specific table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 🗂️;3.选择列 (3. Selecting columns)
# columns 🟠 and 🔵
SELECT 🟠, 🔵 FROM 🗂️
# all columns
SELECT * FROM 🗂️
# unique values of 🟠
SELECT DISTINCT 🟠 FROM 🗂️
# number of rows
SELECT COUNT(*) FROM 🗂️
# number of non-missing values in 🟠
SELECT COUNT(🟠) FROM 🗂️
# number of unique non-missing values in 🟠
SELECT COUNT(DISTINCT 🟠) FROM 🗂️4.过滤行 (4. Filtering rows)
# <> means not equal to
# = single equation signs checks for equality
# filter rows based on column values
SELECT 🔵 FROM 🗂️ WHERE 🔵 = 'some string';
SELECT 🔵 FROM 🗂️ WHERE 🟠 > 123;
# number of rows for which 🟠 is smaller than 123
SELECT COUNT(*) FROM 🗂️ WHERE 🟠 < 123;
# multiple conditions with logical operators
SELECT 🟠 FROM 🗂️ WHERE 🔵 > 2005 AND 🔵 < 2015
SELECT 🟠 FROM 🗂️ WHERE 🔵 = 2005 OR 🔵 = 2015
# when combining OR with AND, parenthesis are needed
SELECT 🟠 FROM 🗂️ WHERE (
🔵 = 2005 OR 🔵 = 2015
) AND (
🟠 = 'some string' OR 🟠 = 'some other string'
);
# checking for ranges
# BETWEEN is inclusive, so the beginning and end values are included in the results
SELECT 🟠 FROM 🗂️ WHERE 🔵 BETWEEN 2005 AND 2015;
# filtering on many conditions
SELECT 🟠 FROM 🗂️ WHERE 🔵 IN (1, 3, 5);
# number of missings values in 🔵
SELECT COUNT(*) FROM 🗂️ WHERE 🔵 IS NULL;
# values 🟠 of for which 🔵 is observed
SELECT 🟠 FROM 🗂️ WHERE 🔵 IS NOT NULL;
# searching for text patterns
# wildcards (placeholders for characters):
# - The % wildcard will match zero, one, or many characters in text.
# - The _ wildcard will match a single character.
SELECT 🟠 FROM 🗂️ WHERE 🔵 LIKE 'start of some string%';
SELECT 🟠 FROM 🗂️ WHERE 🔵 LIKE 'one letter missing in searched stri_g';5.聚合，算术，别名，排序和分组 (5. Aggregations, arithmetics, aliasing, sorting & grouping)
# Aggregate functions
SELECT AVG(🟠) FROM 🗂️;
SELECT MIN(🟠) FROM 🗂️;
SELECT MAX(🟠) FROM 🗂️;
SELECT SUM(🟠) FROM 🗂️;
# Arithmetic operations
SELECT (4 * 3); # gives 12
SELECT (4 / 3); # gives 1, dividing int by int returns int
SELECT (4.0 / 3.0) AS result; # gives 1.333
# Aliasing (assigning a temporary name)
SELECT MAX(🔵) AS max_value FROM 🗂️;
# Sorting (in ascending order by default)
SELECT 🔵 FROM 🗂️ ORDER BY 🟠 DESC;
# Grouping
SELECT 🔵, count(*) FROM 🗂️ GROUP BY 🔵 ORDER BY count DESC;
# Filtering based on the result of an aggregate function
SELECT 🔵 FROM 🗂️ GROUP BY 🔵 HAVING COUNT(🟠) > 10;6.内部联接 (6. Inner joins)
# select all fields from inner join of tables
SELECT *
FROM left_🗂️
INNER JOIN right_🗂️
ON left_🗂️.🟠 = right_🗂️.🟠;
# if both tables share column names, they have to be aliased
# e.g. both tables have a columnc called 🟠
SELECT 🗂️_1.🟠 AS col_from_1, 🗂️_2.🟠 AS col_from_2
FROM 🗂️_1
INNER JOIN 🗂️_2
ON 🗂️_1.🔵 = 🗂️_2.🟢;
# using aliases for convenience
SELECT c1.🟠 AS col_from_1, c2.🟠 AS col_from_2
FROM 🗂️_1 AS c1
INNER JOIN 🗂️_2 AS c2
ON c1.🔵 = c2.🟢;
# combining multiple joins
SELECT a.🟠, b.🔵, c.🟢
FROM 🗂️_1 AS a
INNER JOIN 🗂️_2 AS b
ON a.🟠 = b.🔴
INNER JOIN 🗂️_3 AS c
ON a.🟠 = c.🟠 AND b.🔴 = c.🔴;
# inner join via USING (when the key field 🟢 is the same in both tables)
SELECT a.🟠 AS col_from_a, 🔴, b.🟠 AS col_from_b
FROM 🗂️_1 AS a
INNER JOIN 🗂️_2 AS b
USING (🟢);7.案例(长的if-else语句) (7. Cases (long if-else statements))
SELECT 🟠, 🔵,
CASE WHEN 🔵 > 10
THEN 'large'
WHEN 🔵 > 5
THEN 'medium'
ELSE 'small' END
AS 🟢
INTO 🗂️_2
FROM 🗂️_1;8.左右联接 (8. Left & right joins)
SELECT t1.🔵 AS 🔵, t2.🟢 AS 🟢, 🔴
FROM 🗂️_1 AS t1
LEFT JOIN 🗂️_2 AS t2
ON t1.🟠 = t2.🟠
ORDER BY 🔵 DESC;
# Writing a right join as a left join: the following two are equivalent
SELECT 🟠, 🔴
FROM 🗂️_1
LEFT JOIN 🗂️_2
ON 🗂️_1.🔵 = 🗂️_2.🟠
LEFT JOIN 🗂️_3
ON 🗂️_2.🟠 = 🗂️_3.🟢
ORDER BY 🟠;
SELECT 🟠, 🔴
FROM 🗂️_3
RIGHT JOIN 🗂️_2
ON 🗂️_3.🟢 = 🗂️_2.🟠
RIGHT JOIN 🗂️_1
ON 🗂️_2.🟠 = 🗂️_1.🔵
ORDER BY 🟠;9.设置理论条款 (9. Set theory clauses)
# Union - elements in one or both sets
SELECT *
FROM 🗂️_1
UNION
SELECT *
FROM 🗂️_2
# Union-all - elements in one or both sets, with the latter duplicated
SELECT 🟠, 🔵
FROM 🗂️_1
UNION ALL
SELECT 🟠, 🔵
FROM 🗂️_2
ORDER BY 🟠, 🔵;
# Intersect - elements in both sets
SELECT 🟠
FROM 🗂️_1
INTERSECT
SELECT 🟠
FROM 🗂️_2
# Except - set difference, elemets in one set but not the other
SELECT 🟠
FROM 🗂️_1
EXCEPT
SELECT 🔵
FROM 🗂️_210.完全和交叉连接 (10. Full & cross joins)
# Full join - all rows in both tables that match a condition
SELECT 🟠, 🔵
FROM 🗂️_1
FULL JOIN 🗂️_2
USING (🟠)
WHERE 🔵 = 'abc' OR 🔵 IS NULL
ORDER BY 🔵;
# Cross join - all possible combinations of two tables
SELECT t1.🟠 , t2.🔵
FROM 🗂️_1 AS t1
CROSS JOIN 🗂️_2 AS t211.半和反联接 (11. Semi & anti joins)
# use right table to detect which records from the left table to return
# Semi join
# (match records by the key field in the right table with those in the left,
# and returns only the rows in the left table that match that conditions)
SELECT DISTINCT 🟠
FROM 🗂️_1
WHERE 🟢 IN
(SELECT 🟢
FROM 🗂️_2
WHERE 🔴 = 'abc')
# Same results with an inner join
SELECT DISTINCT 🗂️_1.🟠 AS 🔵
FROM 🗂️_1
INNER JOIN 🗂️_2
ON 🗂️_1.🟢 = 🗂️_2.🟢
WHERE 🔴 = 'abc'
ORDER BY 🔵;
# Anti join
# (return those rows in the left table that do not
# match the condition on the right table)
SELECT t1.🟠, t1.🟢, 🔵
FROM 🗂️_1 AS t1
INNER JOIN 🗂️_2 AS t2
ON t1.🟠 = t2.🟠
WHERE 🔴 = 'abc'12.子查询 (12. Subqueries)
# Subquery inside a WHERE clause
SELECT *
FROM 🗂️
WHERE 🟠 >
3 * (SELECT AVG(🔵)
FROM 🗂️
WHERE 🟢 = 2) AND
🟢 = 3;
# Subquery inside a SELECT clause
SELECT 🟠,
(SELECT COUNT(*)
FROM 🗂️_1
WHERE 🗂️_1.🔵 = 🗂️_2.🔵) AS 🟢
FROM 🗂️_2
ORDER BY 🟢 DESC, 🟠
LIMIT 5;
# The above is exactly the same as the following
SELECT 🟠, COUNT(*) AS 🟢
FROM 🗂️_1
INNER JOIN 🗂️_1
ON 🗂️_1.🔵 = 🗂️_2.🔵
GROUP BY 🟠
ORDER BY 🟢 DESC, 🟠
LIMIT 5;
# Subquery inside a FROM clause
SELECT 🟠, subquery.🔵
FROM 🗂️_1, (SELECT 🟢, count(🔴) AS 🔵
FROM 🗂️_2
GROUP BY 🟢) AS subqueryThanks for reading! I hope you will find this basic cheat sheet useful. If you liked this post, try one of my other articles. Can’t choose? Pick one of these:
谢谢阅读！ 我希望您会发现此基本备忘单有用。 如果您喜欢这篇文章，请尝试我的其他文章之一 。 无法选择？ 选择以下之一：
翻译自: https://towardsdatascience.com/sql-queries-21958212e9e2
sql查询
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
