SQL Server DML (UPDATE, INSERT, DELETE) common usage (i)

　　

1. Introduction

T-SQL (Transact structured Query Language) is the standard SQL extension that is the primary language for program and SQL Server communication.

The T-SQL language consists mainly of the following parts:

• Data definition Language (DDL): Used to build databases, database objects, etc., such as CREATE TABLE, DROP table, and so on.
• Data Control Language (DCL): Controls access to the database, permissions, etc., such as Grant.
• Data manipulation Language (DML): Used to insert, modify, delete, and query data in the database, such as SELECT, Insert, UPDATE, delete, and so on.

The operations of data definition language and Data Control language implementations are typically done through Micosoft SQL Server Management Studio visualizations. The most commonly used data manipulation language in the program is the most common and basic usage of SELECT, INSERT, UPDATE, delete in the data manipulation language.

2. Common Concepts

Before you introduce data manipulation languages, learn about the specifications and formats commonly used in SQL Server, and the concepts that you might use in T-SQL statements.

2.1 Identifiers

servers, databases, and database objects (such as tables, views, columns, indexes, constraints, and so on) have identifiers, and the names of database objects are treated as identifiers for that object.

• L identifier Format

1. The initial letter must begin with a letter (defined in UNICODE 2.0), an underscore _, a symbol @, or a number sign #, followed by letters, numbers, _, @, $, #.
2. Identifiers that begin with @ represent local variables or parameters, start with # to represent temporary tables or variables, # #开头表示全局临时对象, and some Transact SQL functions start with @@ 开头

• L Identifier Classification

1. General identifier: Conforms to the identifier format specification, such as Age123,_we
2. Delimited identifiers: Identifiers contained within "" or [], with spaces in the middle of identifiers, such as [Hong Kong]

2.2 Object Naming conventions

The complete object name consists of a server name, a database name, a schema, and an object name, in which the. number is connected; You can omit the middle part for use. Express

Server.database.schema.object

Server: Schema.object

Schema.object

Object

2.3 Constants, variables, operators, and wildcard characters

• L Constants

1. Numeric constants contain integer constants, decimal constants, and floating-point number constants, such as 12,12.56,12.5e7
2. string constants, enclosed in single quotes, contain alphanumeric and special characters, such as! , @, #; If you include embedded quotes, you can use two single quotes to represent embedded single quotes. such as ' student '.
3. A constant value for date and time, date, time, and time interval is specified as a date and time constant. For example ' 1989-09-34 ', ' 04/04/1998 ', time and date vary according to the country's different writing methods.
4. Symbolic constants, special constant symbols represent different constant data values, current_date, and so on.

• L Variables

1. Local variables begin with @.
2. Global variables start with @@ 开头 internally define a number of global variables, such as @ @CONNECTIONS indicates the number of connections after the server has started.

• L operators and wildcard characters

1. Operators include arithmetic operators + 、-、 *,/,%, assignment operators =; comparison operators >, <, =, >=, <=, <>; logical operators all, and, any, between, EXISTS, in, like, not, Or, SOME; bitwise operators &, |, ^, ~; Join operator +.
2. wildcard character,%: contains 0 or more characters; _: any single character; []: Specifies the range or any single character in the collection; [^]: a single character that does not belong to the specified range or collection.

3.DML Data Manipulation language

DML data Manipulation language, which contains four T-SQL statements of SELECT, INSERT, UPDATE, Delete, the basic use of these four statements is described below through an example.

Suppose you have a table with the following table named Student:

　　

3.1 SELECT statement

The SELECT statement is used to query a data table or view, and is the most frequently used statement, because of its complex syntax, a large number of clauses, followed by a special chapter to illustrate its usage.

3.2 INSERT statement

The INSERT statement adds a new record to the table that can insert a new record into the table or insert a result set. The syntax is as follows (all of the following syntax formats are as follows, uppercase denotes keywords, [] denotes optional content, [,...] Indicates that the preceding paragraph can be repeated):

INSERT [into] Table_or_view_name (column_name[,...]) VALUES (expression) [,...]

To insert data into a table:

The results are as follows:

　　　　

3.3 UPDATE statement

Modify the data in the database. The syntax is as follows:

UPDATE Table_or_view_name [from {<table_source>}[,...]]

SET column_name = Expression | DEFAULT | NULL [,...]

WHERE search_condition

The age at which the name Wang Li in the modified table is 19:

The results are as follows:

　　　　

3.4 DELETE statement

Delete the records in the table with the following syntax:

DELETE [from <table_source>[,...]]

WHERE search_condition

Delete a record with the name Wang Li in the table:

The results are as follows:

4 Conclusion

The number of data manipulation languages is not many, simple structure, but frequently used, where the SELECT statement is the most used, but also the most complex, in detail see the following chapter, SQL Server Select query Statement basic usage.

SQL Server DML (UPDATE, INSERT, DELETE) common usage (i)