sql crud_SQL Server中的CRUD操作

sql crud

CRUD operations are foundation operations every database developer and administrator needs to understand. Let’s take a look at how they work with this guide.

CRUD操作是每个数据库开发人员和管理员都需要了解的基础操作。 让我们看看他们如何使用本指南。

介绍 (Introduction)

According to Wikipedia…

根据维基百科 …

• “In computer programming, create, read, update, and delete (CRUD) are the four basic functions of persistent storage. Alternate words are sometimes used when defining the four basic functions of CRUD, such as retrieve instead of read, modify instead of update, or destroy instead of delete. CRUD is also sometimes used to describe user interface conventions that facilitate viewing, searching, and changing information; often using computer-based forms and reports. The term was likely first popularized by James Martin in his 1983 book managing the Data-base Environment. The acronym may be extended to CRUDL to cover listing of large data sets which bring additional complexity such as pagination when the data sets are too large to hold easily in memory.”
• 在计算机编程中，创建，读取，更新和删除（CRUD）是持久性存储的四个基本功能。 定义CRUD的四个基本功能时，有时会使用替代词，例如检索而不是读取，修改而不是更新或销毁而不是删除。 CRUD有时也用于描述用户界面约定，以方便查看，搜索和更改信息。 通常使用基于计算机的表格和报告。 这个术语很可能是James Martin在1983年管理数据库环境的书中首次流行的。 首字母缩略词可以扩展到CRUDL，以涵盖大型数据集的列表，这会带来额外的复杂性，例如，当数据集太大而无法轻松保存在内存中时，将进行分页。

CRUD is an acronym that stands for Create, Read, Update, and Delete. 

CRUD是首字母缩写词，代表C reate， R ead， U pdate和D elete。

These are the four most basic operations that can be performed with most traditional database systems and they are the backbone for interacting with any database. 

这是大多数传统数据库系统可以执行的四个最基本的操作，它们是与任何数据库进行交互的基础。

入门 (Getting started)

Let’s get started to understand the concepts of CRUD operations in SQL Server

让我们开始了解SQL Server中的CRUD操作的概念

创造 (Create)

The first letter of CRUD, ‘C’, refers to CREATE aka add, insert. In this operation, it is expected to insert a new record using the SQL insert statement. SQL uses INSERT INTO statement to create new records within the table.

CRUD的首字母'C'是指CREATE aka add，insert。 在此操作中，期望使用SQL插入语句插入新记录。 SQL使用INSERT INTO语句在表中创建新记录。

Let us create a simple table named Demo for this example.

让我们为该示例创建一个名为Demo的简单表。

USE AdventureWorks2016;
GO
DROP TABLE IF EXISTS Demo;
CREATE TABLE dbo.Demo
(id   INT, 
name VARCHAR(100)
);

SQL Insert starts with the keyword INSERT INTO then specify the table name and the columns that we want to insert. The columns go inside of the parentheses and then we specify a VALUES clause.

SQL插入以关键字INSERT INTO开头，然后指定表名和我们要插入的列。 列放在括号内，然后我们指定一个VALUES子句。

INSERT INTO <tablename> (column1,column2,….)
 
VALUES (value1,value2,….)

We put in the table name demo after the insert into command. Now, supply the values to the listed columns id and name in the VALUES clause.

我们在insert into命令之后放入表名demo 。 现在，将值提供给VALUES子句中列出的列ID和名称。

INSERT INTO dbo.Demo
VALUES
(1, 
'Prashanth'
);

To insert multiple rows, follow the below syntax

要插入多行，请遵循以下语法

 
INSERT INTO <tablename> (column1,column2,….) 
VALUES(value1,value2,…. ),( value1,value2,…. ), (value1,value2,…. )…

In the following example, the multiple values are listed within in the parenthesis and each list is separated by a comma delimiter

在下面的示例中，多个值在括号内列出，并且每个列表用逗号定界符分隔

INSERT INTO dbo.Demo
(id, 
name
)
VALUES
(2, 
'Jayaram'
),
(3, 
'Pravitha'
);

To insert rows from SQL Union clause, follow the below syntax

要从SQL Union子句插入行，请遵循以下语法

INSERT INTO <tablename> (column1,column2,….) 
SELECT value1,value2,… 
UNION ALL 
SELECT value1, value2,…

In the following example, the multiple values are listed using SELECT statement and then these values combined and fed to the table using SQL UNION ALL set operator.

在下面的示例中，使用SELECT语句列出了多个值，然后使用SQL UNION ALL set运算符将这些值组合并馈送到表中。

INSERT INTO dbo.demo
       SELECT 4, 
              'Prarthana'
       UNION ALL
       SELECT 5, 
              'Ambika';

The output lists all the inserted rows from the above samples.

输出列出了上述示例中所有插入的行。

• • Notes:
    1. It is mandatory to insert at least all of the required columns, but you don’t have to update a column if those values are not required, or if there is a default value for that column
    2. A detailed explanation of SQL insert can be found in the following article: Overview of the SQL Insert statement
    3. SQL Insert statement only works against a single table unlike select which can work against multiple tables
    4. A detailed explanation of SQL Union clause can be found in the following article: SQL Union overview, usage and examples

• 笔记：
  1. 必须至少插入所有必需的列，但是如果不需要这些值或该列有默认值，则不必更新该列
  2. 有关SQL插入的详细说明，请参见以下文章： SQL插入语句概述
  3. SQL插入语句仅适用于单个表，而select则适用于多个表
  4. SQL Union子句的详细说明可以在下面的文章中找到： SQL Union概述，用法和示例

读 (Read)

The second letter of CRUD , ‘R’, refers to SELECT (data retrieval) operation. The word ‘read’ retrieves data or record-set from a listed table(s). SQL uses the SELECT command to retrieve the data. When it comes to executing queries, you can use SQL Server Management Studio or SQL Server Data Tools or sqlcmd, based on your preference.

CRUD的第二个字母“ R”表示SELECT（数据检索）操作。 单词“ read”从列出的表中检索数据或记录集。 SQL使用SELECT命令检索数据。 在执行查询时，您可以根据自己的喜好使用SQL Server Management Studio或SQL Server数据工具或sqlcmd。

For example, to read related data from the specified table, refer to the below syntax.

例如，要从指定的表中读取相关数据，请参考以下语法。

SELECT * FROM <TableName>

The SQL select statement allows you to query the tables. It allows you to retrieve specific data, one or more rows from one or more tables.

SQL select语句允许您查询表。 它允许您从一个或多个表中检索特定数据，一个或多个行。

The SQL SELECT statement in a vast majority of the time going to contain names of columns from the table(s) that you would like to get data from. Once you have column names, the table name is required in the FROM clause. Now, in a SELECT list, after every column of data, you’re going to need a comma. So you separate each column with a comma, except, no comma after the last column. We’re going to have the SELECT keyword, column name followed by a comma, column name, and the last column name, no comma, FROM clause followed by table name.

在大多数情况下，SQL SELECT语句将包含要从中获取数据的表中的列名称。 一旦有了列名，就需要FROM子句中的表名。 现在，在SELECT列表中，每列数据之后，您将需要一个逗号。 因此，您要用逗号分隔每列，但最后一列后不能有逗号。 我们将使用SELECT关键字，列名后跟一个逗号，列名，最后一个列名，没有逗号，FROM子句后跟表名。

In this case, one that will return every row in the Address table. And it will return just the AddressID, AddressLine1,AddressLine2,City, StateProvinceID and PostalCode columns.

在这种情况下，将返回“地址”表中的每一行。 并且它将仅返回AddressID，AddressLine1，AddressLine2，City，StateProvinceID和PostalCode列。

The SQL SELECT statement uses a wildcard character (*) or asterisk to populate all the columns of the table(s). It provides a way to not have to list every column table(s). That’s by using the asterisk or ‘*’.

SQL SELECT语句使用通配符（*）或星号填充表的所有列。 它提供了一种不必列出每个列表的方法。 那是使用星号或“ *”。

The output lists all the columns of the Address table. The following SQL going to give me all of the columns

输出列出“ 地址”表的所​​有列。 下面SQL将给我所有的列

USE [AdventureWorks2016];
GO
SELECT * FROM [Person].[Address];

Next, the FROM Clause is going to have at least a table or it is possible to have multiple tables using SQL Join.

接下来，FROM子句将至少具有一个表，或者使用SQL Join可以具有多个表。

In the following example, the Product and SalesOrderDetail tables are listed in the FROM Clause of the Select statement.

在下面的示例中，Product和SalesOrderDetail表在Select语句的FROM子句中列出。

SELECT *
FROM Production.Product AS p
     JOIN Sales.SalesOrderDetail AS s ON s.ProductID = p.ProductID;

• Notes: A detailed explanation of a few more SQL SELECT statement scenarios is discussed in the article: 注意：本文中讨论了更多SQL SELECT语句方案的详细解释： Overview of the SQL Order by clauseSQL Order by子句概述

更新资料 (Update)

The third letter of CRUD, ‘U’, refers to Update operation. Using the Update keyword, SQL brings a change to an existing record(s) of the table.

CRUD的第三个字母“ U”表示更新操作。 使用Update关键字，SQL可以对表的现有记录进行更改。

• Overview of SQL Update to learn more about SQL update.SQL更新概述，以了解有关SQL更新的更多信息。

When performing an update, you’ll need to define the target table and the columns that need to update along with the associated values, and you may also need to know which rows need to be updated. In general, you want to limit the number of rows in order to avoid lock escalation and concurrency issues.

执行更新时，您需要定义目标表和需要更新的列以及相关的值，并且您可能还需要知道哪些行需要更新。 通常，您希望限制行数，以避免锁升级和并发问题。

The basic syntax for an update:

更新的基本语法：

UPDATE <TableName>
SET Column1=Value1, Column2=Value2,…
WHERE <Expression>

The UPDATE keyword is followed by the name of the table or view to be updated, and then the set keyword followed by the column name and the value to be set, be it an expression, default, keyword, or null value. If you’re looking to specify which rows are modified using a search condition, the syntax is as follows: Everything is the same as the previous example, only this time you’ll see the “where” clause followed by an expression.

UPDATE关键字后跟要更新的表或视图的名称，然后set关键字后跟列名称和要设置的值，可以是表达式，默认值，关键字或null值。 如果要指定使用搜索条件修改的行，则语法如下：一切与前面的示例相同，仅这次，您将看到“ where”子句后跟一个表达式。

• Overview of the SQL update statementSQL更新语句概述

删除 (Delete)

The last letter of the CRUD operation is ‘D’ and it refers to removing a record from a table. SQL uses the SQL DELETE command to delete the record(s) from the table.

CRUD操作的最后一个字母为“ D”，它表示从表中删除记录。 SQL使用SQL DELETE命令从表中删除记录。

• Overview of SQL Delete to learn more about SQL delete operation.“ SQL删除概述”以了解有关SQL删除操作的更多信息。

For example, to delete related data from the specified table, refer to the below syntax

例如，要从指定的表中删除相关数据，请参考以下语法

DELETE FROM <TableName>
WHERE <Expression>

When writing a DELETE statement, you’ll define the target table and also which rows you need to delete from the table. The syntax in its simplest form is the DELETE keyword followed by the table name. In some case without a WHERE clause in the query deletes ALL existing rows from the table. To apply a condition clause to the SQL DELETE statement, use the WHERE clause followed by the expression(s).

在编写DELETE语句时，您将定义目标表以及需要从表中删除的行。 最简单形式的语法是DELETE关键字，后跟表名。 在某些情况下，查询中没有WHERE子句会从表中删除所有现有行。 要将条件子句应用于SQL DELETE语句，请使用WHERE子句，后跟表达式。

• Note:注意： The detailed explanation of SQL delete can be found in the following article: 有关SQL删除的详细说明，请参见以下文章： Overview of the SQL delete statementSQL delete语句概述

摘要 (Summary)

So, thus far, we’ve discussed a lot about CRUD operations. It is a termed as the foundation of SQL operations in any database products. We also discussed how to implement CRUD Operations in SQL Server.

因此，到目前为止，我们已经讨论了很多有关CRUD操作的内容。 它被称为任何数据库产品中SQL操作的基础。 我们还讨论了如何在SQL Server中实现CRUD操作。

I would recommend reading SQL Insert, SQL Delete, and SQL Update articles which part of CRUD operations. Implementing the Create, Update, Delete, and insert operations are reasonably simple because they are very similar operations.

我建议阅读CRUD操作的SQL插入 ， SQL删除和SQL更新文章。 实现Create，Update，Delete和Insert操作非常简单，因为它们是非常相似的操作。

The most efficient way to implement CRUD operations in SQL is through stored procedures. You can refer to the article Creating and using CRUD stored procedures for further reading.

在SQL中实现CRUD操作的最有效方法是通过存储过程。 您可以参考文章创建和使用CRUD存储过程以进一步阅读。

Thanks so much for taking the time to read this article. I hope you found it simple and valuable. Feel free leave the comment below.

非常感谢您抽出宝贵的时间阅读本文。 希望您发现它简单而有价值。 随意留下下面的评论。

翻译自: https://www.sqlshack.com/crud-operations-in-sql-server/

sql crud