Data table data operations include insertion, modification, and deletion of data.
First, insert data
You should be aware of two points when adding data to a table: the first is user rights, only the sysadmin corner pack members, the database, and the database
such as the owner and his authorized user have the right to add data to the table, and the second is the data format, for different data types, insert the data
Format is also different, should strictly comply with their respective format requirements.
Inserts a new row of data into a table or view with an INSERT statement in a Transact-SQL language. 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
}
}
Where the column_list parameter is the list of one or more column names in the newly inserted data row, which shows that the INSERT statement inserts only the specified column
into the data. When inserting data into a table or viewport, you must use the column Name list to indicate this section of the column name. Columns for remaining unspecified columns
Values are based on their default and null attribute conditions, and they have the following possible values:
(1) for timestamp columns or with IDENTITY property columns, their column values are automatically assigned when computed by SQL Server.
(2) If these columns have default values or are associated with default database objects, the values of the new columns are the default values when they are inserted.
(3) When these columns do not have a default value set, but they allow null values, the column value is empty.
(4) When these columns have neither a default value setting nor a null value, SQL Server generates an error when executing the INSERT statement.
Causes the insert operation to fail.
When the column_list parameter is not specified, the data order provided for each column should be in strict accordance with the order in which the columns in the table are defined, using the
The column_list parameter adjusts the column order of the data inserted into the table as long as the values clause provides the order of data with the Column_
The column order specified in the list parameter is the same.
The VALUES clause provides data for the column specified by the column_list parameter in the newly inserted row, which can be presented as a constant expression
For, or use the default keyword description to insert its defaults into the column.
The default values note inserts the defaults into all columns in the table. For a indentity property or timestamp data type
column, the system will automatically insert the next appropriate value. For columns that do not have default values set, if they allow null values, SQL Server inserts
NULL, otherwise an error message is returned.
Select_statement is a standard database query statement that SQL Server provides for an INSERT statement with another data interpolation
Into the way. The INSERT statement inserts the result collection data returned by the select_statement clause into the specified table. Query statement result set
The number, data type, and order of data in each row must also be the number of columns specified in the column or column_list parameter defined in the table
The quantity, data type, and arrangement order are exactly the same.
The fourth type of data insertion that SQL Server provides for INSERT statements is through the execution of system stored procedures whose data comes from the process
The resulting collection of results after execution. The procedures that are executed can be stored procedures, system stored procedures, or extended stored procedures that can be
is a local stored procedure, and can be a stored procedure on a remote server, as long as the user has permission to execute them. About Stored
process see the corresponding content.
Table_source describes the table or view that is manipulated when the INSERT statement inserts data, and its syntax can be easily written as:
{table_name [[as] table_alias]
| View_name [[as] table_alias]
}
TABLE_NAME and view_name Describe the table or view name in which the data is inserted, and the Table_alias parameter sets the alias for the table or view.
There are two reasons to use aliases: First, when a table or view name is longer, using aliases simplifies writing; second, in the self-connection
or subqueries, you can distinguish the same table or view by using an alias.
When inserting data into a table, if the data being inserted conflicts with the requirements of a constraint or rule, or their data type is incompatible
Causes the INSERT statement to fail to execute. When you use a Select or EXECUTE clause to insert more than one row of data into a table, if the
Having any row of data is incorrect, it will cause the entire insert operation to fail, causing SQL Server to stop the insert operation of all data rows.
Example one, using a numeric list (assuming that only name, age, and sex fields are defined in the Usertable table, and that name, sex are all
Char type, age is int type).
INSERT usertable
VALUES (' John ', ' female ', 18)
Example of using Column Name list method
INSERT usertable (Age,name)
VALUES (18, ' John ')
For example, in a list of values, you can also insert the value of a variable into a table. When you use variables to provide data for a column, you should ensure that the variable's
Data types are the same as column data types, or they can be automatically converted to the same data type. For example:
DECLARE @name Char (16)
SET @name = ' John '
INSERT usertable
VALUES (@name, default,20)
In this example, this is the case in asp:
Dim name
Name= "John"
Sqlstr= "INSERT usertable VALUES (' &name&" ', ' female ', 20) "
......
For example, insert the result collection 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 ' Zhang% '
Ii. Modification of data
The UPDATE statement in Transact-SQL modifies the data in the table, which has the syntax format:
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 write a lot of, the most commonly used is the following format:
UPDATE table_name
SET Column_name1=variable1,column_name2=variable2
WHERE search_conditions
Where the Table_or_view parameter indicates the table or view name to be modified, in the same format as the parameter in the INSERT statement.
The SET clause indicates the columns or variables that were modified in the table, and their new values. COLUMN_NAME is the name of the column being modified, @variable
is a declared local variable name whose modified value is provided by the expression expression or by using the default keyword to
The default value is assigned to the specified column.
The FROM clause leads to another table, which provides a condition for the data modification operation of the UPDATE statement.
The search_conditions parameter in the WHERE clause describes the modification criteria for the UPDATE statement, which indicates which rows in the table or view
Need to be modified. When the WHERE clause is omitted, the description modifies all rows in the specified table or view!!!!
The current of description in the WHERE clause performs a modification at the present position of the cursor, which is changed by the Curror_name or the cursor
Quantity cursor_variable_name specified.
Update cannot modify column values that have an IDENTITY property column.
For example, change the gender of all persons in the usertable table to ' male '
UPDATE usertable
SET sex= ' man '
(ii) Change the gender of all persons who are gender-null to ' male '
UPDATE usertable
SET sex= ' man '
WHERE Sex is NULL
For example, change the name of all persons whose names are null to ' John ', change the gender to ' female ', and change the age to 18
UPDATE usertable
