Inserting, updating, and deleting data tables using T-SQL statements

In the management of data, using SSMS for data maintenance has the advantage of visualization, convenience, but in bulk maintenance or repeated maintenance, using SSMS is inconvenient, but also error-prone, which requires the writing of SQL statements to maintain the database

SQL and T-SQL

SQL is the abbreviation for Structured Query language, the Structured Query language, which is the standard language for relational databases. Today, Oracle, Sybase, Informix, SQL Server, these large database management systems support SQL as the query language

T-SQL is a reinforced version of the language that supports basic functions such as flow control, variable descriptions, function functions, and so on, in addition to standard SQL commands.

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

DML (Data Manipulation Language): used to query, insert, delete, and modify data in a database, such as SELECT, INSERT, UPDATE, delete

DDL (data definition Language): used to build databases, database objects, and define their columns, most commands that start with create, such as Create, alter, drop

DCL (Data Control Language): used to control access permissions for database components, access rights, etc., such as GRANT, revoke

Inserting data (insert)

Use the INSERT statement to insert the database into the table with the following syntax:

Insert [into] table name [column Name] Values list

which

* Into is optional and can be omitted

* Table names are required, and table column names are optional, such as omitted, and the order of value lists is consistent with the order of the fields in the data table

* Multiple column names and value lists are separated by commas

For example , to insert a row of data into a student score table, you can use the following T-SQL statement

The execution of the SQL statement is typically done in the Query window, click the New Query button, select the database, and enter the SQL statement as shown in:

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/21/e6524d25b92af0298267d12b4f3e0fb4.png "title=" Yang Shufan 12.png "alt=" E6524d25b92af0298267d12b4f3e0fb4.png "/>

Click Yes in SSMs and the system checks the input T-SQL statement for syntax errors, then displays the results of the analysis, and if you click Execute, you can execute the T-SQL statement and display the result of the execution as shown in:

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/21/91e731f4f9129426fac11bd559f5c458.png "title=" Yang Shufan 13.png "alt=" 91e731f4f9129426fac11bd559f5c458.png "/>

When inserting data, you need to be aware of the following things:

(1) Every time you insert a whole row of data, it is not possible to insert only half a row or columns of data

(2) The number of data values must be the same as the number of columns, and the data type, precision, and scale of each value need not match the corresponding column

(3) For column of character type, single quotation marks must be used

(4) The inserted data item requires compliance with the CHECK constraint

Updating data (update)

Update the data in the table with the UPDATE statement in the following syntax format:

Update table name set column name = update value [where update condition]

which

* Set can be followed by updating values for multiple data columns, not limited to a

* Where is optional, used to restrict conditions, and if not limited, all data rows of the entire table will be updated

For example: change student grades Yangfan The CNT for the middle school student is 95,sql to 77, you can use the following T-SQL statement as shown in

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/21/c83e49a38c1a4f3696c241e11280ec71.png "title=" Yang Shufan 14.png "alt=" C83e49a38c1a4f3696c241e11280ec71.png "/>

Delete data

(1) Delete the data in the table using the DELETE statement

The delete syntax is formatted as follows

Delete from table name [where delete condition]

* If you don't use where, all the data in the table will be deleted

For example , to delete a record of student Zhang San, you can use the following T-SQL statement

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/21/7f6de80c653d0f5d8b833e82cea3df8b.png "title=" Yang Shufan 15.png "alt=" 7f6de80c653d0f5d8b833e82cea3df8b.png "/>

Note:Delete statements are used to delete entire records, not just individual fields, so field names cannot appear after delete

(2) Using the TRUNCATE TABLE statement to delete data from a table

The Truncate table statement is used to delete all rows in the table, functionally similar to the DELETE statement without a WHERE clause, with the Truncate table syntax in the following format:

TRUNCATE TABLE name

The difference between the Truncate table statement and the DELETE statement is as follows:

The ① Truncate Table statement can only empty the entire table data without a where, and the DELETE statement may delete some records by condition

The ② Truncate Table statement does not log transaction logs and cannot be recovered through the transaction log after deletion, and a transaction log is logged for each DELETE statement that deletes a row of records.

The ③ Truncate Table statement cannot be used with a table that has a foreign key constraint reference, in which case a DELETE statement is required

In summary, the Truncate table statement executes faster, and the DBA frequently uses this statement when a large number of data table jobs are in progress, but it is not possible to recover the data before executing it to ensure that it can be deleted.

This article is from the "Yang Shufan" blog, make sure to keep this source http://yangshufan.blog.51cto.com/13004230/1983937

Inserting, updating, and deleting data tables using T-SQL statements