DML statement Change data

When we create a database and a table that stores the data, we need to add new data to the table, delete the unwanted data, and back up the data. The DML language is used to manipulate the data.

One: Insert inserts data

1, inserting a single row of data

Insert into table name (list name) values (value); Note the insertion value "" Here is not the same as null.

2, inserting multiple rows of data

Insert into new table name (list name) Select old list name from old table name; Note that the new table must be present at this time.

Second: Update data updates

1, update single-column data

Update table name set column name = numeric where column name 2 = value; Where is the clause, and if not, the data for the entire table is changed.

2. Update multi-column data

Update table name set column Name 1 = numeric, column Name 2 = value where Column name 3 = value;

3, delete data by update

Update table name set column name = "" Where column name = value;

Three: Delete data

1,delete from table name where (filter condition);

Filter conditions include:

where column name = value where column name < value where column name > numeric or column name < numeric between and

The 2,truncate table name is permanently deleted and executes faster than delete.

If there are self-growing sequences in the table, then deleting the self-growing sequence with delete will not happen with truncate.

Four: Select query data

1,select * from table name; query form.

Select list name from table name; Query row.

2,select table name. List name from table name; Query a column of data by adding a suffix.

3, table name as table alias; Alias table. Select Table alias. Column name from table name as table alias;

4,select distinct column name from table, excluding duplicate data.

5, but the condition is selected. Select Column name from table name where column name = value;

6, multi-criteria selection. Select Column name from table name where column name (range);

This involves a comparison operator (=,! =,>,>=,<,<>,<=) <> here! = Same as the effect.

7, execute scope query. Select Column name from table where between lower and upper bound; the lower and upper bounds are included here.

8, fuzzy query. Select Column Name 1 from table where column name 2 like mode; there are two types of patterns: 1,% contains 0 or more characters 2,_ any single character

9, processing null value data. Select Column Name 1 from table name where column name 2 is null;select column name 1 from table name where column name 2 is not null;

10, sort. Single column sorting, select column Name 1, column name 2, column name 3 from table name order by column name 2; default Ascending row. The data for column names 2 is sorted in ascending order, and the preceding columns are sorted by column name. Add desc Descending to the end of the row.

Multi-column sorting. Select Column Name 1, column name 2, column name 3 from table name order by column name 4, column name 5, column name 6, priority is determined according to the order of the subsequent columns, and when the previous data is the same, it is arranged in the order of column name 5

DML statement Change data