如何管理SQL数据库

介绍

安装SQL数据库时,需要添加,修改,删除和查询数据所需的所有命令。这个备忘单样式指南提供了一些最常用的SQL命令的快速参考。

如何使用本指南:

  • 本指南采用备忘单格式,包含自包含的命令行代码段
  • 跳转到与您要完成的任务相关的任何部分
  • 当您在本指南的命令中看到highlighted text时,请记住,此文本应引用您自己的数据库中的列,表和数据。
  • 在本指南中,给出的示例数据值都包含在撇号(')中。在SQL中,必须在撇号中包装由字符串组成的任何数据值。数字数据不需要这样做,但如果包含撇号,也不会导致任何问题。

请注意,虽然SQL被认为是标准,但大多数SQL数据库程序都有自己的专有扩展。本指南使用MySQL作为示例关系数据库管理系统(RDBMS),但给出的命令将与其他关系数据库程序一起使用,包括PostgreSQL,MariaDB和SQLite。在RDBMS之间存在显着差异的地方,我们已经包含了替代命令。

要完成本教程,您需要具备一台已经设置好可以使用sudo命令的非root账号的Ubuntu服务器,并且已开启防火墙。没有服务器的同学可以在这里购买,不过我个人更推荐您使用免费的腾讯云开发者实验室进行试验,学会安装后再购买服务器

打开数据库提示符(使用套接字/信任身份验证)

默认情况下,在Ubuntu 18.04上, MySQL用户可以使用以下命令在没有密码的情况下进行身份验证:

sudo mysql

要打开PostgreSQL提示符,请使用以下命令。此示例将以postgres用户身份登录,该用户是包含的超级用户角色,但您可以将其替换为任何已创建的角色:

sudo -u postgres psql

打开数据库提示符(使用密码身份验证)

如果您的 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';

删除用户

使用以下语法删除数据库用户配置文件:

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要更新哪一行。column_A中保持的value值与您要更改的行对齐。

注意:如果您未在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子句

您可以通过附加带有WHERE子句的SELECT语句来缩小查询结果的范围,如下所示:

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允许使用通配符。如果您尝试在表中查找特定条目,但不确定该条目是什么,则这些条目很有用。

Asterisks(*)是表示“all”的占位符,它将查询表中的每一列:

SELECT * FROM table;

百分号(%)表示零个或多个未知字符。

SELECT * FROM table WHERE column LIKE val%;

Underscores(_)用于表示单个未知字符:

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子句,但它是用来包括聚合函数例如查询的结果进行排序COUNTMAXMIN,或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子句用于创建组合来自两个或多个表的行的结果集。如在本示例这样,如果每个两个表中存在具有相同名称和数据类型的列,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将返回两个表中具有匹配值的所有记录,但不会显示任何没有匹配值的记录。

通过使用外部 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数据库的相关教程,请前往腾讯云+社区学习更多知识。


参考文献:《How To Manage an SQL Database》

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java大联盟

Java面试手册:数据库 ④

12830
来自专栏吾爱乐享

软件测试之学习mysql的增删改(数据库重点)

14120
来自专栏JavaEdge

MySQL必知必会分页whereupdatelimit字符串截取order by排序ength和char_lengthreplace函数1 键2 数据库事务的ACID3 视图4 删除连接

408140
来自专栏互联网技术栈

MySQL EXPLAIN详解

MySQL EXPLAIN命令是查询性能优化不可缺少的一部分,该文主要讲解explain命令的使用及相关参数说明。

17390
来自专栏数据分析

SQL Server 性能优化之——T-SQL 临时表、表变量、UNION

这次看一下临时表,表变量和Union命令方面是否可以被优化呢? 一、临时表和表变量 很多数据库开发者使用临时表和表变量将代码分解成小块代码来简化复杂的逻辑。但是...

57540
来自专栏JMCui

MongoDB系列四(索引).

一、索引简介     再来老生常谈一番,什么是索引呢?数据库索引与书籍的索引类似。有了索引就不需要翻整本书,数据库可以直接在索引中查找,在索引中找到条目以后,就...

32650
来自专栏禅林阆苑

mysql学习总结04 — SQL数据操作

mysql 中 SELECT 命令类似于其他编程语言的 print 或 write,可用来显示字符串、数字、数学表达式的结果等

29030
来自专栏大数据和云计算技术

MongoDB系列6:MongoDB索引的介绍

1、前言 和关系型数据库一样,MongoDB的索引可以提高查询执行效率。索引就好比书中的目录,可以快速定位书中某一页。适当的索引查询,优化器可以快速地返回结果集...

532100
来自专栏Danny的专栏

《高性能 MySQL》读书笔记

1、隔离级别有四种: READ UNCOMMITTED(未提交读),同事务中某个语句的修改,即使没有提交,对其他事务也是可见的。这个也叫脏读。 READ...

21820
来自专栏Kevin-ZhangCG

SQL优化总结之二

20320

扫码关注云+社区

领取腾讯云代金券