SQL小抄--快速而简单的SQL命令参考实例

今天你吸猫了么
526 阅读7分钟

这张SQL小抄的目的是为了让你快速而容易地找到那条在你舌尖上的SQL命令，或者看到一个普通查询的例子。

请注意：这份小抄使用的是PostgreSQL的方言。我们也包括了一些CockroachDB特有的命令（标有星号），其中CockroachDB提供了Postgres中不存在的有用功能。然而，这个小抄中的大部分命令都可以在大多数SQL中使用。

入门

创建和管理数据库

创建数据库

创建一个新的数据库。

CREATE DATABASE bank;

DROP DATABASE

删除一个数据库和它的所有内容。

DROP DATABASE bank;

显示数据库 *

显示你的集群中的所有数据库。

SHOW DATABASES;

IF EXISTS 可以用来防止在我们（例如）试图删除一个不存在的数据库时出现错误。 ，也可以与许多其他SQL语句一起使用，并与其他操作符结合。IF EXISTS

例子

DROP DATABASE IF EXISTS bank;CREATE DATABASE IF NOT EXISTS bank;

DROP DATABASE … CASCADE 可以用来删除所有依赖数据库被删除的对象。 可以用来防止 命令的执行，除非数据库是空的。DROP DATABASE … RESTRICT DROP DATABASE

举例说明

DROP DATABASE bank CASCADE;DROP DATABASE bank RESTRICT;

创建表和模式

CREATE TABLE

在数据库中创建一个新的表。

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING,
        name STRING,
        address STRING,
        credit_card STRING,
        dl STRING
);

对于表中的每一条记录，你必须至少指定一个行名（例如：city ）和一个数据类型（例如：STRING ）。但是你也可以用CREATE TABLE 语句做更多的事情，例如。

定义一个主键列。

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING,
        name STRING
);

定义一个多列主键。

CREATE TABLE users (
        id UUID,
        city STRING,
        name STRING,
        PRIMARY KEY (city, id)
);

定义一个引用数据库中另一个表的外键。

(在本例中，引用名为locations 的表中名为city 的列）。

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING REFERENCES locations(city),
        name STRING
);

创建一个基于列的索引。

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING,
        name STRING,
        INDEX (name)
);

定义一个列的默认值。

(在这种情况下，使用CockroachDB的gen_random_uuid() 函数来生成一个随机的UUID作为默认值）。

CREATE TABLE users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        city STRING,
        name STRING
);

不允许NULL 列的值。

CREATE TABLE users (
        id UUID NOT NULL,
        city STRING,
        name STRING
);

创建一个带有计算列的表。

CREATE TABLE users (
        id UUID NOT NULL,
        city STRING,
        name STRING,
        name_and_city STRING AS (CONCAT(name, ' ', city)) STORED
);

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING REFERENCES locations(city) ON DELETE CASCADE,
        name STRING
);

使用查询的结果创建一个新的表。

CREATE TABLE users_ny(user_id, name, city) 
AS SELECT * FROM users 
WHERE city = 'new york';

管理SQL表

ALTER TABLE

对一个表应用模式的改变。

ALTER TABLE bank ADD COLUMN active BOOL;

ALTER TABLE 与子命令一起使用，例如。

ADD COLUMN

添加一个列。

ALTER TABLE bank ADD COLUMN active BOOL;

DROP COLUMN

删除一个列。

ALTER TABLE bank DROP COLUMN active;

ALTER COLUMN

改变列的约束，数据类型等。

ALTER TABLE bank ALTER account_balance TYPE FLOAT;

重命名列

重命名一个列。

ALTER TABLE bank RENAME COLUMN account_balance TO balance;

重命名为

重命名一个表。

ALTER TABLE bank RENAME TO users;

删除表

删除一个表。

DROP TABLE bank;

DROP TABLE … CASCADE 可以用来删除所有依赖被删除表的对象（约束，视图等）。

DROP TABLE … RESTRICT 可以用来阻止DROP TABLE命令的执行，除非该表是空的。

请谨慎使用DROP TABLE 语句!

管理SQL约束

ADD CONSTRAINT

为一个列添加一个键、检查或唯一约束。

ALTER TABLE users ADD CONSTRAINT id_name_unique UNIQUE (id, name);

删除约束

从一个列中删除一个约束条件。

ALTER TABLE users DROP CONSTRAINT id_name_unique;

ALTER COLUMN

添加或删除DEFAULT 和NOT NULL 约束，改变数据类型。

ALTER TABLE subscriptions ALTER COLUMN newsletter SET NOT NULL;

插入数据

插入到...值

向表中插入具有指定值的行。

INSERT INTO users (name, city) VALUES('Alice', 'New York');

插入到...选择

从查询的结果中向表中插入行。

INSERT INTO drivers (id, city, name, address)
    SELECT id, city, name, address FROM users
    WHERE name IN ('Anita Atkinson', 'Devin Jordan');

处理你的数据

修改数据

更新表中的行

UPDATE users SET address = '201 E Randolph St' WHERE id = '851eb851-eb85-4000-8000-00000000001a';

注意：如果没有WHERE 语句，UPDATE 将更新所有行的指定列或列的值。

插入一条新行，或者在检测到与现有行冲突时执行不同的操作（即 "upsert"）。

INSERT INTO employees (id, name, email) 
VALUES (2, ‘Dennis’, ‘dennisp@weyland.corp’)
ON CONFLICT (id) DO UPDATE;

UPSERT*

将一条记录上移到数据库中。

UPSERT INTO employees (id, name, email) VALUES (6, ‘Lambert’, ‘lambert@weyland.corp`);

注意：默认情况下，CockroachDB使用主键列的值来决定是否有冲突（即，是否应该更新现有的行或插入新的行）。更多关于SQL中插入的信息可以在我们的博客上找到。

DELETE FROM

删除一条或数条特定的记录。

DELETE FROM promo_codes WHERE code = 'HAPPY50';

查询数据

select ... from ...

返回表格中特定列的值。

SELECT id, city, name FROM users;

select * from ...

返回表中所有列的值。

SELECT * FROM users;

LIMIT

限制查询所返回的行数。

SELECT * FROM users LIMIT 5;

OFFSET

在返回查询结果之前，跳过前n条记录。

SELECT * FROM users LIMIT 5 OFFSET 5;

WHERE

根据一个或多个条件对查询结果进行过滤。

SELECT * FROM vehicles WHERE city = 'seattle' AND status = 'available';

GROUP BY

根据一个列对查询结果进行分组。

SELECT city FROM rides
WHERE city IN ('new york', 'chicago', 'seattle') GROUP BY city;

HAVING

根据运行聚合函数的结果过滤查询。

SELECT city, AVG(revenue) as avg FROM rides GROUP BY city
HAVING AVG(revenue) BETWEEN 50 AND 60;

连接表

SQL中的连接语句看起来像这样。

SELECT table1.column1, table2.column1, table2.column2
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column1;

注意，"左 "指的是在你的SQL语句中首先列出的表，所以在上面的例子中，table1 是左表。该查询的输出是一个包含column1 和column2 的表，用于查询table1 和table2 中所有与column1 匹配的记录。

连接的类型。

连接类型描述
内联(INNER JOIN)返回在两个表中具有匹配值的记录。
LEFT JOIN返回左表的所有记录和右表的匹配值。
RIGHT JOIN返回右表的所有记录和左表的匹配值。
全额联接返回所有在任一表中具有匹配值的记录。
联合将表 "堆叠 "起来，首先返回左表的记录，然后是右表的记录。

聚合数据

选定的聚合函数。

函数使用它来...
COUNT()计算行的数量。
AVG()对某一列中的数值进行平均。
SUM()将某一列中的数值相加。
MIN()返回一列中的最低值。
MAX()返回一列中的最高值。

例子

SELECT AVG(balance) FROM accounts WHERE balance > 0;

这个SQL查询将返回表accounts 中balance 列的平均值，不包括余额为零的行。

逻辑运算符

选定的逻辑运算符。

操作符使用它来...
ALL如果子查询中的所有值都满足指定的条件，返回TRUE。
和返回以AND分隔的条件为真的记录。
ANY如果子查询中的任何值符合指定的条件，则返回TRUE。
BETWEEN返回在两个指定值之间的查询记录。
EXISTS如果在子查询中存在任何记录，则返回TRUE。
输入在WHERE查询中指定多个值，不必在每个值之间使用OR。
LIKE返回符合指定模式的记录。
不返回不符合指定条件的记录。
或返回任何以OR分隔的条件为真的记录。

管理索引

创建索引

使用一个或多个列为一个表创建一个索引。

CREATE INDEX ON table1 (column1, column2);

改变索引...重命名为

重命名一个索引。

ALTER INDEX usersname_idx RENAME TO users_name_idx;

DROP INDEX

删除一个索引。

DROP INDEX users_name_idx;

管理/操作

数据库访问

创建角色

创建一个SQL用户角色（用户组）。

CREATE ROLE basic_user;

DROP ROLE

删除一个SQL用户角色。

DROP ROLE basic_user;

创建用户

创建一个新的用户。

CREATE USER alex;

DROP USER

删除一个用户。

DROP USER alex;

授予

给予一个用户或一个角色权限。

GRANT DELETE ON TABLE rides TO alex;

REVOKE

撤销一个用户或角色的数据库权限。

REVOKE ALL ON DATABASE defaultdb FROM alex;

SHOW ROLES*

列出一个集群中所有数据库的角色。

SHOW ROLES;

显示用户

列出一个集群中所有数据库的用户。

SHOW USERS;

显示授权

查看授予一个用户或角色的权限。

SHOW GRANTS FOR alex;

性能调查

解读

查看一个查询的查询计划，而不执行它。

EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;

解说分析

执行一个查询，并生成一个带有执行统计数据的物理查询计划。

EXPLAIN ANALYZE SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;
avatar
文章
阅读
粉丝