1. Insert Data (Insert) Add a new record to the table, and 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 puts the string 'Some Data' into the MyTable mycolumn field. The name of the field to be inserted into the data is specified in the first parentheses, and the actual data is given in the second parentheses.
The complete sentence of the INSERT statement is as follows:
INSERT [INTO] {Table_Name | View_name} [(Column_List)] {Default Values |
VALUES_LIST | SELECT_STATEMENT}
If a table has multiple fields, you can insert data into all fields by using a comma with a comma with a comma. Assume that the table MyTable has three field first_column, second_column, and third_column. The following INSERT statement adds a full record of three fields:
INSERT MyTable (first_column, second_column, third_column)
VALUES ('Some Data', 'Some More Data', 'Yet More Data')
note
You can insert data into the text field using the INSERT statement. However, if you need to enter a long string, you should use the WRITETEXT statement.
What if you only specify two fields and data in the insert statement? In other words, you insert a new record to a table, but there is no data to provide data. In this case, there are four possibilities:
If this field has a default, this value will be used. For example, suppose you do not provide data to the field third_column when you insert a new record, and this field has a default value 'some value'. In this case, the value 'some value' is inserted when the new record is established.
If this field can be accepted, there is no default value, the null value is inserted.
If this field cannot be accepted, there is no default value, there is an error. You will receive an error message:
The Column In Table MyTable May Not Be Null.
Finally, if the field is a identification field, it will automatically generate a new value. When you insert a new record to a table with identity fields, as long as you ignore this field, the identification field will give yourself a new value.
Note: After inserting a new record in a table with identity field, you can use SQL variable @@ identity to access the value of the new recorded 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 a identification field, the value of this field is inserted into the another_first field of the table anothertable. This is because the variable @@ identity always saves the value of the last inserted identity field.
Field Another_First should have the same data type as the field first_column. However, field another_first cannot be identified by the field. The Another_First field is used to save the value of the field first_column. 2. Delete record
To remove one or more records from the table, you need to use the SQL DELETE statement. You can give the DELETE statement with a WHERE clause. The WHERE clause is used to select the record you want to delete. For example, the DELETE statement below deletes the value of the field first_column is equal to the record of 'Delete ME':
Delete mytable where first_column = 'deltet me'
The complete sentence of the delete statement is as follows:
Delete [from] {TABLE_NAME | View_name} [Where clause]
Any conditions that can be used in the SQL SELECT statement can be used in the WHERE clause of the delect statement. For example, the DELETE statement below deletes the value of the value of the first_column field is 'goodbye' or the second_column field of 'So long':
Delete mytable where first_column = 'goodby' or second_column = 'so long'
If you don't give the DELETE statement, all records in the table will be deleted. You should not have this idea. If you want to delete all the records in the table, you should use the Truncate Table statement told by Chapter 10.
note
Why use the TRUNCATE TABLE statement instead of the DELETE statement? When you use the TRUNCATE TABLE statement, the recorded deletion is not recorded. That is, this means that Truncate Table is much better than Delete.
3. Update record
To modify one or more records already existing in the table, you should use the SQL UPDATE statement. Like the Delete statement, the UPDATE statement can use the WHERE clause to select a specific record. Please see 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!'. For all selected records, the value of field first_column is set to 'updated!'.
Below is the full syntax of the UPDATE statement:
Update {Table_name | View_name} set [{table_name | View_name}]
{Column_List | Variable_List | Variable_and_column_list}
[, {colorn_list2 | variable_list2 | variable_and_column_list2} ...
[, {colorn_listn | variable_listn | variable_and_column_listn}]]]
[WHERE CLAUSE]
note
You can use the UPDATE statement for text type fields. However, if you need to update a long string, use the UpdateText statement. This part of the content is too advanced to this book, so it will not discuss it. For more information, please refer to the documentation of Microsoft SQL Sever.
If you don't provide a WHERE clause, all records in the table will be updated. Sometimes this is useful. For example, if you want to double the price of all books in Titles, you can use the UPDATE statement: You can also update multiple fields at the same time. For example, the following UPDATE statement is updated at the same time updating first_column, second_column, and third_column these three fields:
Update myTable set first_column = 'updated!'
SECOND_COLUMN = 'Updated!'
Third_column = 'updated!'
WHERE first_COLUMN = 'update me1'
skill
SQL ignores excess space in the statement. You can write the SQL statement into any format you easier to read.
Create records and tables with SELECT
You may have noticed that the INSERT statement is different from the DELETE statement and the Update statement. It only operates a record at a time. However, there is a way to make the INSERT statement to add multiple records 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 records from anothertable to myTable. Only the value of the field another_first in the table another_first is' Copy ME! 'Records were copied.
This form of INSERT statement is very useful when establishing a backup for recording in a table. Before deleting records in a table, you can copy them to another table with this method.
If you need to copy the entire table, you can use the SELECT INTO statement. For example, the following statement creates a new table called NewTable, which contains all the data of 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, just specify the field you want to copy in the list of fields. In addition, 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 is equal to the first_column field of the record of 'Copy ME!'.
SELECT First_Column InTo NewTable
From myTable
Where second_column = 'copy me!'
It is difficult to use SQL to modify the table that has been established. For example, if you add a field to a table, there is no easy way to remove it. Also, if you accidentally give the data type of a field, you will have no way to change it. However, using the SQL statement described in this section, you can bypass these two questions.
For example, suppose you want to delete a field from a table. Using the Select INTO statement, you can create a copy of the table, but do not include the fields to be deleted. This allows you to delete this field and retain data that you don't want to delete.
If you want to change the data type of a field, you can create a new table that contains the correct data type field. After you create the table, you can use the UPDATE statement and the SELECT statement to copy all the data in the original table to the new table. In this way, you can modify the structure of the table and save the original data.
