Shulou(Shulou.com)06/01 Report--

When managing data, using SSMS for data maintenance has the advantages of visualization and convenience, but in batch maintenance or repeated maintenance, it is not convenient to use SSMS, and it is easy to make mistakes, so it is necessary to write SQL statements to maintain the database.

SQL and T-SQL

SQL is the abbreviation of Structured Query Language, that is, structured query language, which is the standard language of relational database. Today, large database management systems such as Oracle, Sybase, Informix and SQL Server all support SQL as a query language.

T-SQL is an enhanced version of SQL. In addition to standard SQL commands, it also supports basic functions similar to programming languages, such as flow control, variable description, function functions, etc.

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

DML (Data Manipulation Language, data manipulation language): used to query, insert, delete, and modify data in a database, such as select, insert, update, delete

DDL (Data Definition Language, data definition language): commands that create databases, database objects and define their columns, most of which begin with create, such as create, alter, drop

DCL (Data Control Language, data control language): used to control access permissions, access permissions, etc., of database components, such as grant, revoke

Insert data (INSERT)

Use the insert statement to insert the database into the table in the following syntax format:

Insert [into] Table name [column name] values values list

Among them

* into is optional and can be omitted

* the table name is required, and the column name of the table is optional. If omitted, the order of the values list is consistent with the order of the fields in the data table.

* multiple column names and values lists are separated by commas

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

The execution of the SQL statement is usually done in the query window. Click the "New query" button, select the database, and enter the SQL statement, as shown in the following figure:

Click "√" in SSMS, the system will check whether the entered T-SQL statement has syntax errors, and then the analysis result will be displayed. If it is correct, click execute, you can execute the T-SQL statement, and then display the execution result, as shown in the following figure:

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

(1) if you insert a whole row of data at a time, you cannot insert only half a row or several columns of data

(2) the number of data values must be the same as the number of columns, and the data type, precision and decimal places of each value do not need to match the corresponding columns.

(3) for character type columns, single quotation marks must be used.

(4) the inserted data items are required to meet the requirements of CHECK constraints.

Update data (UPDATE)

Use the update statement to update the data in the table in the following syntax format:

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

Among them

* set can be followed by update values of multiple data columns, not limited to one

* where is optional and is used to restrict conditions. If there are no restrictions, all data rows of the entire table will be updated.

For example, if you change the CNT of Yang Fan, a middle school student, to 95 SQL and 77, you can use the following T-SQL statement, as shown in the following figure

Delete data

(1) delete the data in the table using the delete statement

The format of delete syntax is as follows

Delete from table name [where deletion condition]

* if where is not used, all data in the table will be deleted

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

Note: the delete statement is used to delete the entire record, not just a single field, so the field name cannot appear after the delete.

(2) delete the data in the table using the Truncate table statement

The Truncate table statement is used to delete all rows in the table, which is functionally similar to the delete statement without a where clause. The Truncate table syntax format is as follows:

Truncate table table name

The differences between Truncate table statements and DELETE statements are as follows:

The ① Truncate table statement can only empty the entire table data without where, while the DELETE statement can delete some records according to conditions.

The ② Truncate table statement does not record the transaction log and cannot be recovered from the transaction log after deletion, while the DELETE statement records a transaction log for each row of records deleted.

③ Truncate table statements cannot be used for tables with foreign key constraint references, in which case you need to use a delete statement

To sum up, the execution speed of the Truncate table statement is faster. DBA often uses this statement in the case of a large number of data table jobs, but make sure that the data can be deleted before execution, otherwise it cannot be recovered.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.