Statement inserts data
To add a new record to the table, you want to use the SQL INSERT statement. Here is an example of how to use this statement:
INSERT mytable (MyColumn) VALUES (' Some data ')
This statement inserts the string ' Some data ' into the MyColumn field of the table MyTable. The name of the field that will be inserted into the data is specified in the first parenthesis, and the actual data is given in the second parenthesis.
The complete syntax for the INSERT statement is as follows:
INSERT [into] {table_name|view_name} [(column_list)] {DEFAULT VALUES |
Values_list | Select_statement}
If a table has more than one field, you can insert data into all fields by separating the field name and field values by commas. Suppose the table mytable has three fields First_column,second_column, and Third_column. The following INSERT statement adds a full record of three fields that have values:
INSERT MyTable (First_column,second_column,third_column)
VALUES (' Some data ', ' Some more data ', ' yet more data ')
Attention
You can use the INSERT statement to insert data into a text-type field. However, if you need to enter a long string, you should use the WRITETEXT statement. This part is too advanced for this book, so it is not discussed. To learn more, refer to the Microsoft SQL Sever documentation.
What happens if you specify only two fields and data in an INSERT statement? In other words, you insert a new record into a table, but one field doesn't provide the data. In this case, there are the following four possible kinds of possibilities:
If the field has a default value, the value is used. For example, suppose you insert a new record without providing data to the field Third_column, and the field has a default value of ' some value '. In this case, the value ' some value ' is inserted when the new record is established.
If the field can accept null values and has no default value, it is inserted with a null value.
If the field cannot accept null values, and there is no default value, an error occurs. You will receive the error message:
The column in table mytable May is null.
Finally, if the field is an identity field, it automatically produces a new value. When you insert a new record into a table with a marked literacy segment, the Identification field assigns a new value to the field as long as it is ignored.
Attention
After inserting a new record into a table with a marked literacy segment, you can use the SQL variable @ @identity to access the new record
The value of the identity field. Consider the following SQL statement:
INSERT mytable (first_column) VALUES (' some value ')
INSERT anothertable (Another_first,another_second)
VALUES (@ @identity, ' some value ')
If the table mytable has an identity field, the value of the field is inserted into the Another_first field of the table anothertable. This is because the variable @ @identity always holds the value of the last time you inserted the identity field.
Field Another_first should have the same data type as the field First_column. However, field Another_first cannot be a field that should be identified. The Another_first field is used to hold the value of the field First_column.
Delete a record
To delete one or more records from a table, you need to use the SQL DELETE statement. You can provide a WHERE clause to the DELETE statement. The WHERE clause is used to select the record to delete. For example, the following DELETE statement deletes only the record for the First_column value of the field equals ' Delete Me ':
DELETE mytable WHERE first_column= ' Deltet Me '
The complete syntax for the DELETE statement is as follows:
DELETE [from] {table_name|view_name} [WHERE clause]
Any condition that can be used in an SQL SELECT statement can be used in the WHERE clause of the Delect statement. For example, the following DELETE statement deletes only those records in which the value of the First_column field is ' So long ' in the ' Goodbye ' or second_column field:
DELETE mytable WHERE first_column= ' goodby ' OR second_column= ' so long '
If you do not provide a WHERE clause to the DELETE statement, all records in the table will be deleted. You should not have such an idea. If you want to delete all records in the table, you should use the TRUNCATE TABLE statement in chapter tenth.
Attention
Why use the TRUNCATE TABLE statement instead of the DELETE statement? When you use the TRUNCATE TABLE statement, the deletion of the record is not recorded. In other words, this means that truncate TABLE is much faster than delete.
Update records
To modify one or more records that already exist in a table, use the SQL UPDATE statement. As with the DELETE statement, the UPDATE statement can use the WHERE clause to choose to update a particular record. Take a look at this example:
UPDATE mytable SET first_column= ' updated! ' WHERE second_column= ' Update me! '
This UPDATE statement updates the value of all Second_column fields to ' Update me! ' 's record. For all selected records, the value of the field First_column is set to ' updated! '.
The following is the complete syntax for the UPDATE statement:
UPDATE {table_name|view_name} SET [{table_name|view_name}]
{Column_list|variable_list|variable_and_column_list}
[, {Column_list2|variable_list2|variable_and_column_list2} ...
[, {COLUMN_LISTN|VARIABLE_LISTN|VARIABLE_AND_COLUMN_LISTN}]]
[WHERE clause]
Attention
You can use the UPDATE statement on text fields. However, if you need to update a very long string, you should use the UPDATETEXT statement. This part is too advanced for this book, so it is not discussed. To learn more, refer to the Microsoft SQL Sever documentation.
If you do not provide a WHERE clause, all records in the table are updated. Sometimes this is useful. For example, if you want to double the price of all the books in the table titles, you can use the following UPDATE statement:
You can also update multiple fields at the same time. For example, the following UPDATE statement updates both First_column,second_column, and third_column three fields:
UPDATE mytable SET first_column= ' updated! '
Second_column= ' updated! '
Third_column= ' updated! '
WHERE first_column= ' Update Me1 '
Skills
SQL ignores extra spaces in the statement. You can write the SQL statement in any format that is easiest for you to read.
Create records and tables with select
You may have noticed that the INSERT statement is a little different from the DELETE statement and the UPDATE statement, and it only operates one record at a time. However, there is a way to make the INSERT statement add more than one record at a time. To do this, you need to combine the INSERT statement with the SELECT statement, like this:
INSERT MyTable (First_column,second_column)
SELECT Another_first,another_second
From anothertable
WHERE another_first= ' Copy me! '
This statement is recorded from the anothertable copy to the mytable. Only the value of field another_first in table anothertable is ' copy me! ' Records before being copied.
This form of INSERT statement is useful when a backup is established for a record in a table. Before you delete records from a table, you can copy them to another table in this way.
If you need to copy the entire table, you can use the SELECT INTO statement. For example, the following statement creates a new table named NewTable that contains all the data for the table mytable:
SELECT * into newtable from mytable
You can also specify that only specific fields are used to create this new table. To do this, simply specify the field you want to copy in the field list. Alternatively, you can use the WHERE clause to limit the records copied to the new table. The following example only copies the value of the field second_columnd equal to ' copy me! ' The First_column field of the record.
SELECT First_column into newtable
From MyTable
WHERE second_column= ' Copy me! '
It is difficult to modify an already established table using SQL. For example, if you add a field to a table, there is no easy way to get rid of it. In addition, if you accidentally give a field a data type wrong, you will have no way to change it. However, using the SQL statements that are described in this section, you can bypass these two issues.
For example, suppose you want to delete a field from a table. Using Sele
