SQL数据库入门

如今大多数现代 Web 应用程序都与数据库交互，通常使用一种称为 sql 的语言。对我们来说幸运的是，这种语言很容易学习。在本文中，我们将从一些基本的 SQL 查询开始，并使用它们与 mysql 数据库进行交互。

你需要什么

SQL（结构化查询语言）是一种设计用于与关系数据库管理系统 (RDBMS) 交互的语言，例如 MySQL、Oracle 或 sqlite。要执行本文中的 SQL 查询，我建议您安装 MySQL。我还推荐 phpMyAdmin 作为 MySQL 的可视化界面。

以下应用程序使在本地计算机上安装 MySQL 和 phpMyAdmin 变得非常容易：

• 适用于 Windows 的WAMP

• 适用于 mac 的MAMP

我们将使用命令行进行查询。WAMP 已经带有一个 MySQL 控制台。

创建数据库

这是我们的第一个查询。我们将创建一个数据库来使用。

首先，打开您的 MySQL 控制台并登录。对于 WAMP，默认密码为空。对于 MAMP，密码应该是root默认的。

登录后，键入此查询并按回车键：

CREATE dataBASE my_first_db;

请注意，;在查询末尾添加了一个分号 ( )，就像在 javascript 中的代码行末尾一样。

此外，特殊词CREATE DATABASE与 SQL 中的所有特殊词一样不区分大小写。但为了可读性和惯例，我们将用大写字母书写它们。

列出所有数据库

以下查询用于获取您拥有的所有数据库的列表。

SHOW DATABASES;

删除数据库

您可以使用以下查询删除现有数据库。

DROP DATABASE my_first_db;

请谨慎处理此查询，因为它不会给您任何警告。如果你的数据库下有表和数据，它们会被立即删除。

在删除数据库之前备份数据库始终是一个好习惯。稍后，我们将了解如何对数据库进行完整备份。

选择数据库

您可以使用USE关键字来选择要操作的数据库。它是一条语句，末尾不需要分号。

USE my_first_db

它告诉 MySQL 为当前会话选择一个默认数据库。现在我们准备在这个数据库下创建表和做其他事情。

什么是数据库表？

您可以将数据库表想象成包含结构化数据的电子表格或 csv 文件，如以下屏幕截图所示。

就像在这个例子中一样，表有列名和数据行。使用 SQL 查询，我们可以创建这些表。我们还可以添加、读取、更新和删除数据。

创建表

通过这个查询，我们可以在数据库中创建表。不幸的是，MySQL 文档对新手来说不是很友好。这种查询的结构可能会非常复杂，但我们将从一个简单的开始。

以下查询将创建一个包含两列的表。

CREATE TABLE users (

    username VARCHAR(20),

    create_date DATE);

请注意，我们可以在多行中编写查询，甚至可以使用制表符进行缩进。

第一行很简单。我们只是创建一个名为的表users。接下来，在括号中，我们有一个用逗号分隔的表列列表。在每个列名之后，我们有一个数据类型，例如VARCHARor DATE。

数据类型是指该VARCHAR(20)列为字符串类型，最大长度为20个字符。这DATE也是一种专门用于存储日期的数据类型，格式为：YYYY-MM-DD.

主键

理想情况下，我们还应该包括一列user_id，这将是一个主键。在不深入细节的情况下，您可以将主键视为识别表中每一行的一种方式。它是标识任何行的唯一编号，不能为多行重复。

现在查询应该如下所示：

CREATE TABLE users (

    user_id INT AUTO_INCREMENT PRIMARY KEY,

    username VARCHAR(20),

    create_date DATE);

INT数据类型使它成为 32 位整数类型（即数字）。AUTO_INCREMENT每次我们添加新的数据行时，关键字都会自动生成一个新的 ID 号。它不是必需的，但它使它更方便。

此列不一定是整数，但它是最常用的类型。拥有 PRIMARY KEY 列也不是必需的，但强烈建议为了良好的数据库设计和性能。

让我们运行查询：

列出所有表

以下查询允许您获取数据库中当前的表列表。

SHOW TABLES;

显示表结构

要查看现有表的结构，您可以使用以下查询。

EXPLAIN users;

结果中列出了字段（又名列）及其属性。

删除表

就像DROP DATABASES命令一样，以下查询删除表及其内容，没有警告。

DROP TABLE users;

再次重申，任何DROP操作都要非常小心！

修改表

您可以使用查询修改表ALTER。该查询还可以具有相当复杂的结构，因为它可以对表执行大量更改。让我们看一些简单的例子。

确保重新创建我们刚刚删除的表，否则下面的查询显然不会工作！

添加列

ALTER TABLE users

    ADD email VARCHAR(100)

    AFTER username;

由于 SQL 的可读性，我认为这甚至不需要解释。您可以只使用ADD关键字在ALTER查询中添加新列。

删除列

同样，您可以使用DROP关键字删除查询中的现有列ALTER。

ALTER TABLE users

DROP email;

这也很简单。但请谨慎使用它，因为它会在没有警告的情况下永久删除数据。

再次添加该email列，因为我们稍后会用到它：

ALTER TABLE users    ADD email VARCHAR(100)    AFTER username;

修改列

有时您可能想要更改列的属性，这样您就不必删除并重新创建它。

让我们看看下面的查询。

ALTER TABLE users

    CHANGE username

    user_name VARCHAR(30);

它会将username列重命名为user_name并将类型从 更改VARCHAR(20)为VARCHAR(30)。像这样的更改不应干扰表中的任何现有数据。

向表中添加数据

让我们看看如何使用以下查询向表中添加一些数据。

INSERT INTO users VALUES(

    NULL,

    'johndoe',

    'john@doe.com',

    '2009-12-14');

如您所见，VALUES()包含字段值列表，以逗号分隔。字符串值用单引号括起来。并且这些值需要按照我们创建表时定义的列的顺序排列。

请注意，第一个值是NULL针对我们创建的主键字段的，即user_id。我们这样做是为了自动生成一个 id，因为该列设置为AUTO_INCREMENT. 第一次输入一行数据时，id为1，下一次插入的行为2，以此类推。

这样，您可以向users表中添加更多数据。

替代语法——SET

这是插入行的另一种语法。

INSERT INTO users SET

    user_name = 'tutsplus',

    email = 'tutsplus@gmail.com',

    create_date = '2009-12-15';

在上面的查询中，我们使用关键字SET而不是VALUES，并且后面没有括号。这比VALUES因为忘记或混淆列的顺序的危险更小。这里有几点需要注意：

• 可以省略一列。例如，我们没有为 赋值user_id，它将默认为AUTO_INCREMENT功能。如果省略一VARCHAR列，它将默认为空字符串（除非在创建表期间指定了不同的默认值）。

• 每一列都必须通过其名称进行引用。因此，与以前的语法不同，它们可以按任何顺序排列。

替代语法：命名值

这是另一种语法。在这种情况下，我们告诉INSERT语句我们正在为哪些列提供值，然后使用VALUES关键字传递这些值。

INSERT INTO users (email, user_name, create_date)

VALUES ('foo@bar.com','foobar','2009-12-16');

同样，由于每一列都是按名称引用的，因此它们可以按任何顺序排列。

LAST_INSERT_ID()功能_

您可以使用以下查询获取AUTO_INCREMENT当前会话中最后插入的行的 ID。

SELECT LAST_INSERT_ID();

NOW()功能_

我认为现在是演示如何在查询中使用 MySQL 函数的好时机。

该NOW()函数返回当前日期。因此，您可以使用它DATE在插入新行时自动将列设置为当前日期。

INSERT INTO users SET

    user_name = 'tutsplus',

    email = 'tutsplus@gmail.com',

    create_date = NOW();

请注意，我们收到了来自 MySQL 的警告，但这没什么大不了的。原因是NOW()实际上也返回时间信息。

但是create_date我们创建的列只包含DATE时间，没有时间，所以返回的数据被截断了。我们可以改用该CURDATE()函数，它只返回日期，但无论哪种方式，最后存储的数据都是相同的。

从表中读取数据

显然，我们添加的数据除非我们可以读取它，否则将毫无用处。这就是SELECT查询的来源。

这是SELECT从表中读取的最简单的可能查询：

SELECT * FROM USERS;

在本例中，星号 ( *) 表示我们要求从表中获取所有列。如果您只需要特定的列，查询将如下所示：

SELECT user_name, email FROM users;

WHERE条款_

通常情况下，我们只对某些行感兴趣，而不是全部。例如，假设我们想要tutsplus用户的电子邮件地址。

要检索它，您可以使用以下查询。

SELECT email FROM users WHERE user_name = 'tutsplus';

把它想象成一个IF声明。该WHERE子句允许您在查询中为要查找的结果设置条件。

请注意，对于相等条件，仅使用一个等号 ( )，而不是您可能在编程中习惯使用=的双等号 ( )。==

您也可以使用其他比较条件：

SELECT * FROM users WHERE user_id <= 2;

SELECT * FROM users WHERE create_date != '2009-12-14;

和运算AND符OR可用于组合条件：

SELECT * FROM users WHERE user_name = 'tutsplus' OR user_id = 1;

请注意，数值不必在引号内。

IN接线员_

该IN运算符对于匹配多个值很有用。

SELECT * FROM users WHERE

create_date IN ('2009-12-15', '2009-12-16');

LIKE接线员_

LIKE运算符允许您进行通配符搜索。

SELECT * FROM users WHERE

email LIKE '%tuts%';

百分号 ( %) 用作通配符。

ORDER BY条款_

如果希望结果按特定顺序返回，可以使用ORDER BY子句。

SELECT * FROM users ORDER BY create_date; 

SELECT * FROM users ORDER BY user_name DESC;

默认顺序是ASC（即升序）。您可以DESC按降序添加以对其进行排序。

该LIMIT...OFFSET 条款

借助LIMITandOFFSET子句，您可以限制返回结果的数量。

SELECT * FROM users LIMIT 2; 

SELECT * FROM users LIMIT 1 OFFSET 2; 

SELECT * FROM users LIMIT 2,1;

该LIMIT 2子句仅获得前 2 个结果。在前 2 个结果之后，LIMIT 1 OFFSET 2得到 1 个结果。意思是一样的LIMIT 2, 1，但注意第一个数字是偏移量，第二个数字是限制。

GROUP BY条款_

GROUP BY当您想要基于任何特定列聚合数据时，该子句非常有用。假设在我们的用户表中，它有country_id一列存储用户所在的***/地区。现在，如果你想得到基于***的所有用户的数量，你可以使用该GROUP BY子句来实现它。

查询看起来像这样。

SELECT country_id, count(*) as total_users 

FROM users

GROUP BY country_id;

JOIN条款_

该JOIN子句帮助您从两个不同的表中选择公共数据。理想情况下，两个表都应包含一个对这两个表通用的列。

在我们的示例中，我们有users包含user_id列的表。现在，假设我们有另一个名为 的表user_details，其中包含user_id、user_company_name和user_ssn_no列。该user_details表用于存储有关用户的额外信息。在本例中，该user_id列在两个表中，因此您可以使用该JOIN子句通过单个查询检索所有用户信息。

让我们看看下面的查询。

SELECT * 

FROM users 

INNER JOIN user_details 

ON users.user_id = user_details.user_id;

它会在一个查询中为您提供所有用户的所有详细信息。有不同类型的联接，例如LEFT JOIN、RIGHT JOIN和INNER JOIN。是最INNER JOIN常用的联接之一。

HAVING条款_

HAVING从句一般与从句一起使用GROUP BY。您可以将其视为WHERE聚合列（如COUNT、SUM等）的子句。所以基本上，当您要根据聚合列过滤结果时，您需要使用该HAVING子句。

在前面的部分中，我们讨论了如何找到按country_id列分组的总用户数。我们可以扩展相同的示例并过滤拥有两个以上用户的***/地区。

让我们看看下面的例子。

SELECT country_id, count(*) as total_users  

FROM users 

GROUP BY country_id

HAVING total_users >= 2;

更新表中的数据

以下查询用于更新表中的数据。

UPDATE users SET

email = 'changed@gmail.com',

user_name = 'barfoo'

WHERE user_name = 'foobar';

大多数时候，它与WHERE子句一起使用，因为您只想更新特定的行。如果WHERE未提供子句，所有行都将更新为相同的更改！UPDATE所以在使用查询时必须小心。

UPDATE没有子句的查询WHERE会将相同的数据写入表中的每条记录——永久删除以前的数据。这个查询要非常小心！

您还可以使用LIMIT子句来限制要更新的行数。

UPDATE users SET

create_date = '2009-12-01'

WHERE create_date = '2009-12-14' LIMIT 1;

从表中删除数据

就像 一样UPDATE，下面的查询通常也与WHERE子句一起使用。

DELETE FROM users WHERE user_name = 'batman';

DELETE没有子句的查询WHERE将永久删除表中的每条记录。这个查询要非常小心！

截断表

要删除表格的全部内容，您可以这样做：

DELETE FROM users;

但通常使用它会更有效率TRUNCATE。

该TRUNCATE子句还重置了AUTO_INCREMENT数字，因此新行将再次从头开始。但这不会发生在DELETE查询中，并且计数器不断上升。

转义字符串值和特殊词

字符串值

某些字符需要转义，否则会出现问题。

让我们看看下面的查询。

INSERT INTO users SET user_name = 'O\'Reilly';

反斜杠 ( \) 用于转义。这确保字符（例如，'本例中的字符）被视为字符串的一部分，而忽略其在 SQL 中的特殊含义。 

出于安全原因，这也非常重要。任何进入数据库的用户输入都需要正确转义。在 PHP 中，您可以使用mysql_real_escape_string()函数或使用准备好的语句，因为它们会自动转义。

特殊词

由于 MySQL 有许多特殊词，例如SELECTor UPDATE，您可以通过在表名和列名两边加上引号来防止冲突。但这些不是常规引述；您需要使用反引号 ( `) 字符。

假设您想添加一个delete出于某种原因命名的列：

如您所见，当我们尝试在没有反引号字符的情况下运行查询时，MySQL 给了我们一个警告。当我们使用反引号字符时，它使我们能够成功运行查询。

如何备份数据库

在本节中，我们将快速了解如何在命令行控制台的帮助下备份 MySQL 数据库。

让我们看看下面的命令。

$mysqldump -h {HOSTNAME} -u {USERNAME} -p {DATABASENAME} > backup.sql

您需要根据需要替换占位符。{HOSTNAME}应该替换为您的服务器名称，如果localhost您在本地工作的话。{USERNAME}应替换为您的 MySQL 用户名。{DATABASENAME}应替换为您要备份的 MySQL 数据库名称。当您按Enter 键运行上述命令时，它会要求输入密码，因为我们已经提供了-p参数。你只需要输入你的 MySQL 密码，它就会在backup.sql文件中备份整个数据库。

如何恢复备份

如果没有恢复的方法，备份就没有多大用处！

看看下面的命令。

$mysql -h {HOSTNAME} -u {USERNAME} -p {DATABASENAME} < backup.sql

这将恢复数据库。

结论

希望在本文中，我能够让您相信 SQL 非常强大且易于学习。