Table data operations include data insertion, modification, and deletion. 1. when inserting data into a table, pay attention to the following two points: first, user permission, only members of the sysadmin badge, database and database object owners and their authorized users have the permission to add data to the table. The second is the data format. for different data types, the data insertion format is different, operations on table data should be strictly followed, including data insertion, modification, and deletion.
I. Insert data
When adding data to a table, pay attention to the following two points: first, user permissions. only sysadmin packet members, database and database object owners and their authorized users have the permission to add data to the table; the second is the data format. for different data types, the format of the inserted data is different, and their respective format requirements should be strictly observed.
In the Transact-SQL language, INSERT statements to INSERT new data rows into tables or views. The syntax format of the INSERT statement is:
INSERT [INTO] table_source
{[Column_list]
VALUES ({DEFAULT | constant_expression }[,... N])
| DEFAULT VALUES
| Select_statement
| Execute_statement
}
}
The column_list parameter indicates a list of one or more columns in the newly inserted data row. it indicates that the INSERT statement only inserts data for the specified column. When inserting data to a table or a central table, you must use the column name list to indicate the column names. The values of other unspecified columns depend on their default values and null values. they have the following possible values:
(1) for timestamp columns or columns with the IDENTITY attribute, their column values are automatically assigned values after being calculated by SQL Server.
(2) if these columns have default values or are associated with default database objects, their values are the default values when new columns are inserted.
(3) when these columns have no default values, but they allow null values, the column value is empty.
(4) when these columns neither have default values nor allow null values, SQL Server will generate errors when executing the INSERT statement,
The insert operation fails.
When the column_list parameter is not specified, the data sequence provided for each column should strictly follow the defined sequence of each column in the table, the column_list parameter can be used to adjust the column sequence of data inserted into the table, as long as the data sequence provided by the VALUES clause is the same as the column sequence specified in the column_list parameter.
The VALUES clause provides data for the column specified by the column_list parameter in the newly inserted row, which can be provided in the form of a constant expression or use the DEFAULT keyword to insert its DEFAULT value to the column.
Default values indicates that the DEFAULT value is inserted into all columns in the table. For columns with the INDENTITY attribute or timestamp data type, the system automatically inserts the next appropriate value. If columns without default values are allowed to be null, SQL Server inserts null. Otherwise, an error message is returned.
Select_statement is a standard database query statement. it is another data insertion method provided by SQL Server for the INSERT statement. The INSERT statement inserts the result set data returned by the select_statement clause into the specified table. The number, type, and order of data in each row in the query statement result set must also be the same as the number, data type, and order of the specified columns in the table or column_list parameter.
The fourth data insertion method provided by SQL Server for the INSERT statement is to execute the system stored procedure, and its data comes from the result set generated after the execution of the procedure. The stored procedures can be stored procedures, system stored procedures, or extended stored procedures. they can be both local stored procedures and stored procedures on remote servers, you only need to have the execution permission for the user. For more information about stored procedures, see.
Table_source indicates the table or view operated by the INSERT statement when inserting data. Its syntax format can be simply written as follows:
{Table_name [[AS] table_alias]
| View_name [[AS] table_alias]
}
Table_name and view_name indicate the name of the table or view to be inserted. the table_alias parameter sets the alias for the table or view.
Aliases can be used for two reasons: first, when the table or View name is long, using aliases can simplify writing; second, in self-join or subquery, aliases can be used to differentiate a table or view.
When inserting data into a table, if the inserted data conflicts with the requirements of constraints or rules, or their data types are incompatible, the INSERT statement will fail to be executed. When you use the SELECT or EXECUTE clause to insert multiple rows of data to a table at a time, if any row of data is incorrect, the entire insert operation will fail, stop SQL Server from inserting all data rows.
Example 1: Use the value list (assuming that only the name, age, and sex fields are defined in the usertable table, and the name and sex fields are
Char type, age is int type ).
INSERT usertable
VALUES ('Zhang San', 'female ', 18)
Example 2: Use the column name list
INSERT usertable (age, name)
VALUES (18, 'Zhang San ')
Example 3: in the value list, you can also insert the variable value into the table. When using variables to provide data for a column, you should ensure that the data type of the variable is the same as that of the column, or you can automatically convert them to the same data type. For example:
Example 4: Insert the result set returned by the SELECT clause into the table. For example:
INSERT usertable (name, sex, age)
SELECT's 'name, sex, age
FROM usertable
WHERE name like 'sheet %'
II. modify data
The UPDATE statement in Transact-SQL is used to modify table data. the syntax format of this statement is:
UPDATE ()
SET (
Column_name = {expression | DEFAULT}
| @ Variable = expression
}[,... N]
[FROM
{
| (Select_statement) [AS] table_alias [,... M])]
}
[,... N]
]
[WHERE
| Current of ({[GLOBAL] cursor_name} | cursor_variable_name }}
]
Although a lot of data is written, the most common is the following format:
UPDATE table_name
SET column_name1 = variable1, column_name2 = variable2
WHERE search_conditions
The table_or_view parameter specifies the name of the table or view to be modified. Its format is the same as that in the INSERT statement.
The SET clause specifies the modified columns or variables in the table and their new values. Column_name is the modified column name, and @ variable is a declared local variable name. The modified values are provided by the expression, or the DEFAULT keyword is used to assign the DEFAULT value to the specified column.
The FROM clause extracts another table, which provides conditions for data modification in the UPDATE statement.
The search_conditions parameter in the WHERE clause describes the modification conditions of the UPDATE statement. it specifies which rows in the table or view need to be modified. If the WHERE clause is omitted, all rows in the specified table or view are modified !!!!
The current of clause in the WHERE clause indicates that the CURRENT position OF the cursor is modified. the cursor is specified by curror_name or cursor_variable_name.
UPDATE cannot modify the column values with the IDENTITY attribute column.
Example 1: change the gender of all persons in the usertable table to male'
UPDATE usertable
SET sex = 'male'
Example 2: change the gender of all persons whose gender is null to male'
UPDATE usertable
SET sex = 'male'
WHERE sex IS NULL
Example 3: change the names of all persons whose names are null to 'Zhang San', change the gender to 'female ', and change the age to 18.
UPDATE usertable
SET name = 'Zhang San', sex = 'female ', age = 18
WHERE name IS NULL
III. delete data
In Transact-SQL, the DELETE and TRUNCATE TABLE statements can both DELETE data in the TABLE. The syntax format of the DELETE statement is:
The structure of the DELETE statement is similar to that of the UPDATE statement, which also contains the FROM clause and the WHERE clause. The WHERE clause deletes data.
Except the specified conditions. If the WHERE clause is not used, the DELETE statement deletes all the data in one or more views. The FROM clause is an extension of the DELETE statement based on ANSI. it specifies the name of the table to be connected and provides functions similar to related subqueries.
The syntax format of the truncate table statement is:
Truncate table table_name
The truncate table statement deletes all data rows in the specified TABLE, but the TABLE structure and all its indexes are retained. the constraints, rules, defaults, and triggers defined for the TABLE are still valid. If the deleted table contains an IDENTITY column, the column is reset to its original base value. The DELETE statement without the WHERE clause can also DELETE all rows in the table, but it does not reset the IDENTITY column.
Compared with the DELETE statement, the truncate table statement is faster to DELETE. Because the DELETE statement records the DELETE operation to the log when each row is deleted, and the truncate table statement deletes the TABLE data by releasing the TABLE data page, it only performs transaction logging once on the release page. Therefore, after the truncate table statement is used to DELETE data, these rows cannot be restored, while the DELETE operation can be rolled back to restore the original data.
Because the truncate table statement does not have operation logs, it cannot activate the trigger, so the truncate table statement cannot delete a TABLE referenced by other tables through the foreign key constraint.
Example 1: use the DELETE statement to DELETE data rows whose name is 'zhangsan' in the usertable table
DELETE usertable
WHERE name = 'zhangsan'
Example 2: Use the FROM clause and WHERE clause to specify conditions and then delete data FROM the TB_update table.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
