Modify table data using SQL statements

Enter data using INSERT statements

The basic syntax for INSERT statements is as follows:

The main parameters of the preceding format are described as follows:

  • TOP (expression) [PERCENT] : specifies the number or percentage of random rows to be inserted.
  • INTO: An optional keyword that can be used between inserts and target tables.
  • <object>: Is usually the name of a table or view.
  • Column_list: A list of one or more columns to which data is to be inserted. Column_ list must be enclosed in parentheses and separated by commas.
  • <OUTPUT Clause>: returns the insert row as part of the insert operation.
  • VALUES: Introduces a list of data VALUES to be inserted. For column_list or for each column in a table, there must be a data value.

For example:

INSERT INTO class(cname,sonsun) VALUES('software 01'.50)
INSERT INTO class VALUES('software 01'.50)
Copy the code

The first method lists the column names of the table in an order inconsistent with the table structure, and the values are added in the order specified for the columns.

The second method does not list the column names, and the values are added in the order corresponding to the columns of the table.

UPDATE the table data with the UPDATE statement

The basic syntax of an UPDATE statement is as follows:

The main parameters of the preceding format are described as follows:

  • TOP (expression) [PERCENT] : specifies the number or percentage of rows to be updated.
  • SET: Specifies the list of column or variable names to update.
  • Column_name: The column containing the data to be changed. Column_name must already exist in table_or_view_ name.
  • Expression: A variable, literal value, expression, or nested SELECT statement (in parentheses) that returns a single value. Expression The returned value replaces the existing value in the column_name or @variable.
  • DEFAULT: Specifies to replace the existing value in the column with the DEFAULT value defined by the column.

  • : Returns updated data or an expression based on updated data during an UPDATE operation.
  • FROM
    : Specifies that the table, view, or derived table source will be used to condition the update operation.
  • WHERE

    : Specifies conditions to qualify the rows to be updated and to specify conditions for the rows to be updated.

For example:

UPDATE class
SET sonsun = 60
WHERE cname = 'software 01'
Copy the code

The UPDATE statement only modifies one line because WHERE sets the condition

UPDATE class
SET sonsun = 80
Copy the code

The UPDATE statement does not set the WHERE condition, and the run time updates all sonsun columns in the table class to 80

DELETE data from a table using a DELETE statement

The basic syntax of a DELETE statement is as follows:

The main parameters of the preceding format are described as follows:

  • FROM: Optional keyword, used in DELETE keyword and target table_or_view_name.

  • : returns deleted rows or their expressions as part of a DELETE operation.
  • FROM
    : Specifies the additional FROM clause.
  • WHERE

    : Specifies the condition used to limit the number of rows to be deleted. If no WHERE clause is provided, DELETE deletes all rows in the table.

For example:

DELETE FROM class 
WHERE cname = 'software 01'
Copy the code

After the program is executed, all rows with the value of column Cname as software 01 are deleted.

Use the Truncate Table statement to delete data in the Table

The Truncate Table statement deletes all rows from a Table faster than DELETE.

Truncate Table statement format is as follows:

Truncate Table table_name
Copy the code

The Truncate Table statement is a fast way to delete all rows in a Table.