sql初学者指南_2020年面向初学者的终极SQL指南。

sql初学者指南

SQL is a standard language for storing, manipulating, and retrieving data in databases. In this article, I’ll teach you the very basic fundamentals of the SQL language and hope you will be able to write your own database queries at the end.

SQL是用于在数据库中存储，处理和检索数据的标准语言。 在本文中，我将向您介绍SQL语言的基本知识，并希望您最终能够编写自己的数据库查询。

SQL是什么意思？ (What does SQL Mean?)

SQL stands for Structured Query Language and lets you access and manipulate databases.

SQL代表结构化查询语言，可让您访问和操作数据库。

句法 (Syntax)

Most of the actions you need to perform on a database are done with SQL statements. The following SQL statement selects all the records in the “Users” table:

您需要对数据库执行的大多数操作都是使用SQL语句完成的。 以下SQL语句选择“用户”表中的所有记录：

SELECT * FROM Users;

选择 (Select)

The select statement is used to retrieve data from a database. The requested data is returned in a results table.

select语句用于从数据库检索数据。 请求的数据将在结果表中返回。

SELECT column1 FROM table_name;

选择不同 (Select Distinct)

The Select Distinct statement is used to return only distinct (different) values.

Select Distinct语句用于仅返回不同的(不同的)值。

SELECT DISTINCT * FROM table_name;

Count

计数

The following SQL statement lists the number of different customer countries:

以下SQL语句列出了不同客户国家/地区的数量：

SELECT COUNT(DISTINCT Country) FROM Customers;

哪里 (Where)

The Where clause is used to filter records.

Where子句用于过滤记录。

SELECT column1
FROM table_name
WHERE condition;

For example:

例如：

SELECT * FROM Users
WHERE Country='Netherlands';

AND，OR和NOT运算符 (AND, OR and NOT Operators)

The Where clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition:

Where子句可以与AND，OR和NOT运算符结合使用。 AND和OR运算符用于根据多个条件过滤记录：

• The AND operator displays a record if all the conditions separated by AND are TRUE.
  如果用AND分隔的所有条件均为TRUE，则AND运算符将显示一条记录。
• The OR operator displays a record if any of the conditions separated by OR is TRUE.
  如果由OR分隔的任何条件为TRUE，则OR运算符将显示一条记录。

The NOT operator displays a record if the condition(s) is NOT TRUE.

如果条件不正确，则NOT运算符将显示一条记录。

AND

和

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR

要么

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT

不

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

订购依据 (Order By)

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

ORDER BY关键字用于按升序或降序对结果集进行排序。

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

The following SQL statement selects all users from the “Users” table, sorted DESCENDING by the “Country” column:

以下SQL语句从“用户”表中选择所有用户，并按“国家/地区”列按DESCENDING排序：

SELECT * FROM Users
ORDER BY Country DESC;

插 (Insert)

The Insert statement is used to insert new records in a table. It is possible to write the Insert statement in two ways.

Insert语句用于在表中插入新记录。 可以通过两种方式编写Insert语句。

First way

第一种方式

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Second way

第二种方式

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

更新资料 (Update)

The Update statement is used to modify the existing records in a table.

Update语句用于修改表中的现有记录。

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

删除 (Delete)

The Delete statement is used to delete existing records in a table.

Delete语句用于删除表中的现有记录。

DELETE FROM table_name WHERE condition;

Joins (Joins)

A Join clause is used to combine rows from two or more tables, based on a related column.

Join子句用于根据相关列组合来自两个或多个表的行。

Different Types of SQL Joins

不同类型SQL连接

Here are the different types of the Joins in SQL:

以下是SQL中Join的不同类型：

• (INNER) JOIN: Returns records that have matching values in both tables

  (INNER)JOIN ：返回两个表中具有匹配值的记录

• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

  LEFT(OUTER)JOIN ：返回左侧表中的所有记录，以及右侧表中的匹配记录

• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

  RIGHT(OUTER)JOIN ：从右表返回所有记录，并从左表返回匹配的记录

• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

  FULL(OUTER)JOIN ：当左表或右表中存在匹配项时，返回所有记录

Inner Join

内部联接

The Inner Join keyword selects records that have matching values in both tables.

内部联接关键字选择在两个表中具有匹配值的记录。

SELECT column_name(s)
FROM table1
INNER JOIN table2ON table1.column_name = table2.column_name;

Left Join

左加入

The Left Join keyword returns all records from the left table (table1), and the matched records from the right table (table2).

Left Join关键字返回左表(table1)中的所有记录，以及右表(table2)中的匹配记录。

SELECT column_name(s)
FROM table1
LEFT JOIN table2ON table1.column_name = table2.column_name;

Right Join

正确加入

The Right Join keyword returns all records from the right table (table2), and the matched records from the left table (table1).

Right Join关键字返回右表(table2)中的所有记录，并返回左表(table1)中的匹配记录。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2ON table1.column_name = table2.column_name;

Full Join

完全加入

The Full Outer Join keyword returns all records when there are a match in left (table1) or right (table2) table records.

当左(表1)或右(表2)表记录匹配时，“完全外部联接”关键字将返回所有记录。

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition;

组 (Group)

The group by statement groups rows with the same values into summary rows, like “find the number of users in each country.”

group by语句将具有相同值的行分组为摘要行，例如“查找每个国家/地区的用户数”。

SELECT column_name(s)FROM table_nameWHERE conditionGROUP BY column_name(s)ORDER BY column_name(s);

SELECT 列名(S)FROM TABLE_NAME WHERE 条件 GROUP BY 列名(多个)ORDER BY 列名(多个);

结论 (Conclusion)

After this article, I hope you have learned the basic fundamentals of SQL, and you can write your own creative queries to manage your database!

读完本文后，希望您已经学习了SQL的基本知识，并且可以编写自己的创意查询来管理数据库！

翻译自: https://levelup.gitconnected.com/the-ultimate-sql-guide-for-beginners-in-2020-4f13e45f371

sql初学者指南