Difference Between Insert and Update and Alter

Insert vs Update vs Alter

插入，更新和ALTER是用于修改数据库的三个SQL（结构化查询语言）命令。插入语句用于将新行插入现有表。更新语句用于更新数据库中的现有记录。插入和更新是数据操纵语言（DML）语句。Alter SQL命令用于修改，删除或将列添加到数据库中的现有表中。Alter是数据定义语言（DDL）语句。

Insert

Insert is a SQL command used to insert a new row to an existing table. Insert is a DML statement. Commands that are used to manage data without altering the database schema are called DML statements. There are two ways that an Insert statement could be written.

One format specifies the names of the columns and the values that need to be inserted as follows.

插入tablename（column1name，column2name，…）
VALUES (value1, value2, …)

The second format does not specify the column names that the values should be inserted.

插入tablename
VALUES (value1, value2, …)

In the above examples, tableName is the name of the table that the rows should be inserted. The column1Name, column2Name, … are the names of the columns that the values value1, value2, … will be inserted.

Update

Update is a SQL command that is used to update existing records in a database. Update is considered as a DML statement. Following is the typical syntax of an update statement.

UPDATE tableName
SET column1Name=value1, column2Name=value2, …
columnXName = someValue

In the above example tableName should be replaced with the name of the table that you want to modify the records. The column1Name, column2Name in the SET clause are the names of the columns in the table in which the values of the record that needs to be modified. value1 and value2 are the new values that should be inserted in the record. WHERE clause specifies the set of records needs to be updated in the table. WHERE clause could also be omitted from the UPDATE statement. Then all the records in the table would be updated with the values provided in the SET clause.

What is Alter?

Alter is a SQL command that is used to modify, delete or add a column to an existing table in a database. Alter is considered as a DDL statement. Commands that are used to define the structure of a database (database schema) are called DDL statements. Following is the typical syntax of an alter statement that is used to add a column to an existing table.

ALTER TABLE tableName
ADD newColumnName dataTypeOfNewColumn

In here tableName is the name of the existing table that needs to be altered and newColumnName is the name given to the new column that is added to the table. dataTypeOfNewColumn provides the data type of the new column.

以下是typical syntax of an alter statement that is used to delete a column in an existing table.

ALTER TABLE tableName

DROP列列名称

In here, tableName is the name of the existing table that needs to be altered and the columnName is the name of the column that needs to be deleted. Some of the tables might not allow deleting columns from its tables.

以下是typical syntax of an alter statement that is used to change the data type of an existing column in a table.

ALTER TABLE tableName

ALTER COLUMN columnName newDataType

In here columnName is the name of the existing column in the table and the newDataType is the name of the new data type.

What is the difference between Inset, Update and Alter?

Insert command is used to insert a new row to an existing table, Update is a SQL command that is used to update existing records in a database, while alter is a SQL command that is used to modify, delete or add a column to an existing table in a database. Insert and Update are DML statement whereas, alter is a DDL statement. Alter command modifies the database schema, while insert and update statements only modifie records in a database or insert records in to a table, without modifying its structure.