如何管理SQL数据库
SQL 备忘单
介绍
SQL 数据库随您添加、修改、删除和查询数据所需的所有命令一起安装。 这个备忘单风格的指南提供了一些最常用的 SQL 命令的快速参考。
如何使用本指南:
- 本指南采用备忘单格式,带有独立的命令行片段
- 跳转到与您尝试完成的任务相关的任何部分
- 当您在本指南的命令中看到
highlighted text
时,请记住,此文本应引用 您自己的 数据库中的列、表和数据。 - 在本指南中,给出的示例数据值都包含在撇号 (
'
) 中。 在 SQL 中,任何由字符串组成的数据值都必须用撇号括起来。 这对于数字数据不是必需的,但如果您确实包含撇号,它也不会导致任何问题。
请注意,虽然 SQL 被认为是一种标准,但大多数 SQL 数据库程序都有自己的专有扩展。 本指南使用 MySQL 作为示例关系数据库管理系统 (RDBMS),但给出的命令将适用于其他关系数据库程序,包括 PostgreSQL、MariaDB 和 SQLite。 在 RDBMS 之间存在显着差异的地方,我们包含了替代命令。
打开数据库提示(使用 Socket/Trust Authentication)
默认情况下,在 Ubuntu 18.04 上,root MySQL 用户可以使用以下命令在没有密码的情况下进行身份验证:
sudo mysql
要打开 PostgreSQL 提示符,请使用以下命令。 此示例将以 postgres 用户身份登录,这是包含的超级用户角色,但您可以将其替换为任何已创建的角色:
sudo -u postgres psql
打开数据库提示(使用密码验证)
如果您的 root MySQL 用户设置为使用密码进行身份验证,您可以使用以下命令进行验证:
mysql -u root -p
如果您已经为您的数据库设置了非 root 用户帐户,您还可以使用此方法以该用户身份登录:
mysql -u user -p
上面的命令会在你运行后提示你输入密码。 如果您想在命令中提供密码,请立即在 -p
选项后面加上您的密码,它们之间没有空格:
mysql -u root -ppassword
创建数据库
以下命令使用默认设置创建数据库。
CREATE DATABASE database_name;
如果您希望您的数据库使用不同于默认值的字符集和排序规则,您可以使用以下语法指定它们:
CREATE DATABASE database_name CHARACTER SET character_set COLLATE collation;
列出数据库
要查看 MySQL 或 MariaDB 安装中存在哪些数据库,请运行以下命令:
SHOW DATABASES;
在 PostgreSQL 中,您可以使用以下命令查看已创建的数据库:
\list
删除数据库
要删除数据库,包括其中保存的任何表和数据,请运行遵循以下结构的命令:
DROP DATABASE IF EXISTS database;
创建用户
要为您的数据库创建用户配置文件而不为其指定任何权限,请运行以下命令:
CREATE USER username IDENTIFIED BY 'password';
PostgreSQL 使用类似但略有不同的语法:
CREATE USER user WITH PASSWORD 'password';
如果您想创建一个新用户并在一个命令中授予他们权限,您可以通过发出 GRANT
语句来实现。 以下命令创建一个新用户并授予他们对 RDBMS 中每个数据库和表的完全权限:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
请注意前面的 GRANT
语句中的 PRIVILEGES
关键字。 在大多数 RDBMS 中,这个关键字是可选的,这个语句可以等效地写成:
GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
但请注意,当 Strict SQL 模式 开启时,授予此类权限需要使用 PRIVILEGES
关键字。
删除用户
使用以下语法删除数据库用户配置文件:
DROP USER IF EXISTS username;
请注意,默认情况下,此命令不会删除已删除用户创建的任何表,尝试访问此类表可能会导致错误。
选择数据库
在创建表之前,您首先必须告诉 RDBMS 您要在其中创建它的数据库。 在 MySQL 和 MariaDB 中,使用以下语法执行此操作:
USE database;
在 PostgreSQL 中,您必须使用以下命令来选择所需的数据库:
\connect database
创建表
以下命令结构创建一个名为 table
的新表,并包括两列,每列都有自己的特定数据类型:
CREATE TABLE table ( column_1 column_1_data_type, column_2 column_2_data_taype );
删除表
要完全删除一个表,包括其所有数据,请运行以下命令:
DROP TABLE IF EXISTS table
将数据插入表中
使用以下语法用一行数据填充表:
INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_A', 'data_B', 'data_C' );
您还可以使用单个命令填充包含多行数据的表,如下所示:
INSERT INTO table ( column_A, column_B, column_C ) VALUES ( 'data_1A', 'data_1B', 'data_1C' ), ( 'data_2A', 'data_2B', 'data_2C' ), ( 'data_3A', 'data_3B', 'data_3C' );
从表中删除数据
要从表中删除一行数据,请使用以下命令结构。 请注意,value
应该是您要删除的行中指定的 column
中保存的值:
DELETE FROM table WHERE column='value';
注意:如果在DELETE
语句中不包含WHERE
子句,如下例所示,会删除表中的所有数据,但不会列或表本身:
DELETE FROM table;
更改表中的数据
使用以下语法更新给定行中保存的数据。 请注意,命令末尾的 WHERE
子句告诉 SQL 要更新哪一行。 value
是 column_A
中保存的值,与您要更改的行对齐。
注意: 如果没有在UPDATE
语句中包含WHERE
子句,该命令将替换表格每一行中保存的数据。
UPDATE table SET column_1 = value_1, column_2 = value_2 WHERE column_A=value;
插入一列
以下命令语法将向表中添加一个新列:
ALTER TABLE table ADD COLUMN column data_type;
删除列
遵循此结构的命令将从表中删除一列:
ALTER TABLE table DROP COLUMN column;
执行基本查询
要查看表中单个列的所有数据,请使用以下语法:
SELECT column FROM table;
要从同一个表中查询多个列,请用逗号分隔列名:
SELECT column_1, column_2 FROM table;
您还可以通过用星号 (*
) 替换列名来查询表中的每一列。 在 SQL 中,星号充当占位符来表示“全部”:
SELECT * FROM table;
使用 WHERE 子句
您可以通过在 SELECT
语句中附加 WHERE
子句来缩小查询结果的范围,如下所示:
SELECT column FROM table WHERE conditions_that_apply;
例如,您可以使用如下语法查询单行中的所有数据。 请注意,value
应该是指定的 column
和要查询的行中的值:
SELECT * FROM table WHERE column = value;
使用比较运算符
WHERE
子句中的比较运算符定义应如何将指定列与值进行比较。 以下是一些常见的 SQL 比较运算符:
操作员 | 它能做什么 |
---|---|
=
|
相等性检验 |
!=
|
不平等检验 |
<
|
测试小于 |
>
|
测试大于 |
<=
|
测试小于或等于 |
>=
|
测试大于或等于 |
BETWEEN
|
测试一个值是否在给定范围内 |
IN
|
测试行的值是否包含在一组指定值中 |
EXISTS
|
在给定的条件下测试行是否存在 |
LIKE
|
测试一个值是否与指定的字符串匹配 |
IS NULL
|
测试 NULL 值
|
IS NOT NULL
|
测试除 NULL 以外的所有值
|
使用通配符
SQL 允许使用通配符。 如果您尝试在表中查找特定条目,但不确定该条目到底是什么,这些功能很有用。
星号 (*
) 是代表“全部”的占位符,这将查询表中的每一列:
SELECT * FROM table;
百分号 (%
) 表示零个或多个未知字符。
SELECT * FROM table WHERE column LIKE val%;
下划线 (_
) 用于表示单个未知字符:
SELECT * FROM table WHERE column LIKE v_lue;
计算列中的条目
COUNT
函数用于查找给定列中的条目数。 以下语法将返回 column
中保存的值的总数:
SELECT COUNT(column) FROM table;
您可以通过附加 WHERE
子句来缩小 COUNT
函数的结果,如下所示:
SELECT COUNT(column) FROM table WHERE column=value;
查找列中的平均值
AVG
函数用于查找特定列中保存的值的平均值(在本例中为平均值)。 请注意,AVG
函数仅适用于包含数值的列; 当用于保存字符串值的列时,它可能会返回错误或 0
:
SELECT AVG(column) FROM table;
查找列中的值的总和
SUM
函数用于求列中所有数值的总和:
SELECT SUM(column) FROM table;
与 AVG
函数一样,如果在包含字符串值的列上运行 SUM
函数,它可能会返回错误或仅返回 0
,具体取决于您的 RDBMS。
查找列中的最大值
要查找列中的最大数值或按字母顺序查找最后一个值,请使用 MAX
函数:
SELECT MAX(column) FROM table;
查找列中的最小值
要查找列中的最小数值或按字母顺序查找第一个值,请使用 MIN
函数:
SELECT MIN(column) FROM table;
使用 ORDER BY 子句对结果进行排序
ORDER BY
子句用于对查询结果进行排序。 以下查询语法返回 column_1
和 column_2
中的值,并按 column_1
中保存的值按升序或对于字符串值按字母顺序对结果进行排序:
SELECT column_1, column_2 FROM table ORDER BY column_1;
要执行相同的操作,但以降序或逆字母顺序对结果进行排序,请使用 DESC
附加查询:
SELECT column_1, column_2 FROM table ORDER BY column_1 DESC;
使用 GROUP BY 子句对结果进行排序
GROUP BY
子句类似于 ORDER BY
子句,但它用于对包含聚合函数的查询结果进行排序,例如 COUNT
、[X162X ]、MIN
或 SUM
。 就其本身而言,上一节中描述的聚合函数只会返回一个值。 但是,您可以通过包含 GROUP BY
子句来查看对列中的每个匹配值执行的聚合函数的结果。
以下语法将计算 column_2
中匹配值的数量,并按升序或字母顺序对它们进行分组:
SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2;
要执行相同的操作,但按降序或逆字母顺序对结果进行分组,请使用 DESC
附加查询:
SELECT COUNT(column_1), column_2 FROM table GROUP BY column_2 DESC;
使用 JOIN 子句查询多个表
JOIN
子句用于创建组合两个或多个表中的行的结果集:
SELECT table_1.column_1, table_2.column_2 FROM table_1 JOIN table_2 ON table_1.common_column=table_2.common_column;
这是 INNER JOIN
子句的示例。 INNER JOIN
将返回两个表中具有匹配值的所有记录,但不会显示任何不具有匹配值的记录。
可以使用 outer JOIN
子句从两个表之一返回所有记录,包括在另一个表中没有对应匹配的值。 外部 JOIN
子句写成 LEFT JOIN
或 RIGHT JOIN
。
LEFT JOIN
子句返回“左”表中的所有记录,仅返回“右”表中的匹配记录。 在外部 JOIN
子句的上下文中,左表是 FROM
子句中引用的表,右表是 JOIN
语句之后引用的任何其他表。 下面将显示来自 table_1
的每条记录,并且仅显示来自 table_2
的匹配值。 在 table_2
中不匹配的任何值将在结果集中显示为 NULL
:
SELECT table_1.column_1, table_2.column_2 FROM table_1 LEFT JOIN table_2 ON table_1.common_column=table_2.common_column;
RIGHT JOIN
子句的功能与 LEFT JOIN
相同,但它打印右侧表中的所有结果,仅打印左侧表中的匹配值:
SELECT table_1.column_1, table_2.column_2 FROM table_1 RIGHT JOIN table_2 ON table_1.common_column=table_2.common_column;
将多个 SELECT 语句与 UNION 子句组合
UNION
运算符可用于将两个(或多个)SELECT
语句的结果组合成一个结果集:
SELECT column_1 FROM table UNION SELECT column_2 FROM table;
此外,UNION
子句可以将两个(或更多)查询不同表的 SELECT
语句组合到同一个结果集中:
SELECT column FROM table_1 UNION SELECT column FROM table_2;
结论
本指南涵盖了 SQL 中用于管理数据库、用户和表以及查询这些表中的内容的一些更常见的命令。 但是,有许多子句和运算符的组合都会产生唯一的结果集。 如果您正在寻找更全面的 SQL 使用指南,我们鼓励您查看 Oracle 的数据库 SQL 参考 。
此外,如果您希望在本指南中看到常见的 SQL 命令,请在下面的评论中提问或提出建议。