Represents the main operation object of the database. Today we introduce the addition and deletion of tables.

• Increase: insert
• Modify:
• Delete: delete/truncate

1. Increased insert

_insert grammar is as follows

insert into tableName(colName name1, colName name2, ...) values(value1, value2, ...);

When inserting data, we can specify the column to be inserted, the column name to be inserted in parentheses after tableName, and the corresponding value in parentheses after values.

Note that the specified column corresponds to the subsequent data sequence one by one.

If you do not specify the column to insert, it implicitly indicates all inserts.

See the following example.

-- Create table
mysql> create table t1(id int, name  varchar(32));
Query OK, 0 rows affected (0.52 sec)

-- Appoint name Column insertion
mysql> insert into t1(name) values('xucc');
Query OK, 1 row affected (0.53 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
| NULL | xucc |
+------+------+
1 row in set (0.00 sec)

-- Appoint id Column insertion
mysql> insert into t1(id) values(1);
Query OK, 1 row affected (0.28 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
| NULL | xucc |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

-- towards id Column insertion name，Report errors
mysql> insert into t1(id) values('zhangsan');
ERROR 1366 (HY000): Incorrect integer value: 'zhangsan' for column 'id' at row 1

-- Do not specify columns, insert all
mysql> insert into t1 values(3, 'lisi');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
| NULL | xucc |
|    1 | NULL |
|    3 | lisi |
+------+------+
3 rows in set (0.00 sec)

_When inserting data, sometimes we insert the column where the primary key is located, assuming that the gradual corresponding value already exists, the insertion will fail.

-- id Listed as primary key
mysql> create table t2(id int primary key, name varchar(32), age int);
Query OK, 0 rows affected (0.35 sec)

mysql> insert into t2 values(1, 'xucc', 10);
Query OK, 1 row affected (0.10 sec)

-- Insert columns with primary key values and report errors
mysql> insert into t2 values(1, 'zhangsan', 11);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

_This is to use the following methods:

• a. Update operation

The grammar is as follows:

insert into tableName(colName1, ...) values(value1, ...) on duplicate key update colName1=value1, ... ;

When a primary key conflict occurs, the operation after the update keyword is executed.

mysql> insert into t2 values(1, 'zhangsan', 11) on duplicate key update id=1, name='zahngsan';
Query OK, 2 rows affected (0.09 sec)

mysql> select * from t2;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zahngsan |   10 |
+----+----------+------+
1 row in set (0.00 sec)

• b. Replacement operation

_Use replace to insert data. If there is a primary key conflict, replace directly. If there is no primary key conflict, insert normal data.

replace into tableName(colName1, ...) values(value1, ...);

_Use as follows:

mysql> replace into t2 values(1, 'lisi', 12);
Query OK, 2 rows affected (0.26 sec)

mysql> select * from t2;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | lisi |   12 |
+----+------+------+
1 row in set (0.00 sec)

2. update modification

Update is also called update. The grammar is as follows:

update tableName set colName1=value1[, ...] [where condition];

_uodate modifies the column after the set keyword to the value after the equal sign, which can have multiple columns. where is followed by the condition of update, that is, the restriction condition of the modified column. If there are no restrictions, all the data of the column in the table will be modified.

Before using update, we want to create a table.

mysql> create table t3(
    ->   id int,
    ->   name varchar(32),
    ->   price float
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> insert into t3 values(1, 'Apple', 5.3),(2, 'Banana', 4.1), (3, 'A mandarin orange', 7.12), (4, 'Mango', 2.2);
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|    1 | Apple |   5.3 |
|    2 | Banana |   4.1 |
|    3 | A mandarin orange |  7.12 |
|    4 | Mango |   2.2 |
+------+------+-------+
4 rows in set (0.01 sec)

_update is used as follows:

-- Increase the price of all fruits by 10
mysql> update t3 set price=price+10;
Query OK, 4 rows affected (0.09 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from t3;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|    1 | Apple |  15.3 |
|    2 | Banana |  14.1 |
|    3 | A mandarin orange | 17.12 |
|    4 | Mango |  12.2 |
+------+------+-------+
4 rows in set (0.00 sec)

-- Will all id Even-numbered fruits are priced at 20
mysql> update t3 set price=20 where id%2=0;
Query OK, 2 rows affected (0.58 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t3;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|    1 | Apple |  15.3 |
|    2 | Banana |    20 |
|    3 | A mandarin orange | 17.12 |
|    4 | Mango |    20 |
+------+------+-------+
4 rows in set (0.00 sec)

Limit can also be used in conjunction with update to limit the number of updates. The grammar is as follows:

update tableName set colName=value [...] [where condition] limit update number;

_Use as follows:

-- Put the first two rows of data together id Plus 10
mysql> update t3 set id=id+10 limit 2;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t3;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|   11 | Apple |  15.3 |
|   12 | Banana |    20 |
|    3 | A mandarin orange | 17.12 |
|    4 | Mango |    20 |
+------+------+-------+
4 rows in set (0.00 sec)

3. delete/truncate deletion

delete from tableName [where condition];

Similar to update, if no condition is specified, delete deletes the entire table, and vice versa, deletes the rows of the specified condition.

Before demonstrating, we should first make a copy of the t3 table to prevent data loss. After all, deletion is a high-risk operation in the database.

-- Create copy tmp，Structure and t3 equally
mysql> create table tmp like t3;
Query OK, 0 rows affected (0.53 sec)

-- take t3 All data insertion tmp in
mysql> insert into tmp select * from t3;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tmp;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|   11 | Apple |  15.3 |
|   12 | Banana |    20 |
|    3 | A mandarin orange | 17.12 |
|    4 | Mango |    20 |
+------+------+-------+
4 rows in set (0.02 sec)

Next, delete.

mysql> delete from tmp where id=11;
Query OK, 1 row affected (0.18 sec)

mysql> select * from tmp;
+------+------+-------+
| id   | name | price |
+------+------+-------+
|   12 | Banana |    20 |
|    3 | A mandarin orange | 17.12 |
|    4 | Mango |    20 |
+------+------+-------+
3 rows in set (0.00 sec)

-- Delete the entire table without specifying conditions
mysql> delete from tmp;
Query OK, 3 rows affected (0.09 sec)

mysql> select * from tmp;
Empty set (0.00 sec)

MySQL provides zero keyword truncate for deletion. Its grammar is as follows:

truncate table tableName

truncate deletion can not specify conditions, generally used for table deletion as a whole, faster than delete delete as a whole.

Truncate also deletes table records, such as self-growing data. Use delete to delete the self-growth data. The next time the self-growth data is added, it will increase from the deleted value, while truncate will delete it completely, and the next time it is added, it will start from the default value.

_truncate deletion will not return the number of deleted data, and delete will return the number of deleted data.

Note that delete or truncate only delete the table data, not delete the table structure, delete the table structure using drop.