如何管理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 要更新哪一行。 valuecolumn_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_1column_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 ]、MINSUM。 就其本身而言,上一节中描述的聚合函数只会返回一个值。 但是,您可以通过包含 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 JOINRIGHT 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 命令,请在下面的评论中提问或提出建议。