Detailed description of replace statement usage in Sqlite3

Because of my ignorance, and because of the fact that I was rarely involved in the development of the database module in the previous project, I realized that the Sqlite database also supports the replace statement a few days ago. This article mainly explains the replace statement behavior in Sqlite, which is also a learning note. In addition, replace statements are similar to update statements, but there are many differences.

Because of my ignorance, and because of the fact that I was rarely involved in the development of the database module in the previous project, I realized that the Sqlite database also supports the replace statement a few days ago. This article mainly explains the replace statement behavior in Sqlite, which is also a learning note. In addition, replace statements are similar to update statements, but there are many differences.

Because of my ignorance, and because of the fact that I was rarely involved in the development of the database module in the previous project, I realized that the Sqlite database also supports the replace statement a few days ago. This article mainly explains the replace statement behavior in Sqlite, which is also a learning note. In addition, replace statements are similar to update statements, but there are many differences. This article also compares the replace statement and update statement in Sqlite.

In this example, use the following database table:

(Figure 1)

The name of the table is student, which stores student information. The data type of all fields is TEXT. The id and name are used as the composite primary keys. The email field has a unique constraint. The table creation statement is as follows:

?

1

2

3

4

5

6

7

8

9

10

Create table if not exists student (

"Id" TEXT,

"Name" text not null,

"Sex" TEXT,

"Email" text unique,

"Fenshu" text check (fenshu> 0 ),

"Tecid" text references teacher (id ),

"Class" TEXT,

Primary key (id, name)

)

Replace statement Behavior

1. The replace statement deletes an existing record and inserts a new record to replace the original record.

To verify this conclusion, open the Sqlite command line and execute the following statement to replace the record with id 2.

?

1

2

Sqlite> replace into student (id, name, sex, email, fenshu, tecid, class) values

('2', 'lisi', '* F', '1970 @ qq.com', '80', '2', '1 ');

After this statement is executed, the data in the student table is changed:

(Figure 2)

Comparison and we can see that in medium, the record with id 2 is the first record in the Table. After the above replace statement is executed, the record with id 2 is located at the end of the whole table. This statement deletes the original record with id 2 and inserts a new record with id 2.

2. replace is generally used to replace all columns of a record. If no column is specified in the replace statement, the value of this column is left blank after replace.

Next we will perform an experiment with the record id 2 and execute the following statement:

?

1

2

Sqlite> replace into student (id, name, sex, email, fenshu, tecid) values ('2 ',

'Lisi', '* F', '2017 @ qq.com', '80', '2 ');

This statement replaces the record with id 2 and name lisi, but the class column is not specified when the column is specified. After the execution is complete, the data in the table is as follows:

(Figure 3)

Comparison and we can see that the class field of the record with id 2 and name lisi has no value.

3. replace determines which record is replaced based on the primary key.

In this table, specify the id and name as the composite primary key. When the preceding two statements are executed, the id is 2 and the name is lisi in values. After the execution, the result is also id 2, and the record whose name is lisi is replaced. This shows that the replace statement determines which record is replaced based on the value of the primary key.

4 The replace statement cannot locate the record to be replaced based on the where clause

Run the following statement:

?

1

2

Sqlite> replace into student (id, name, sex, email, fenshu, tecid) values ('2 ',

'Lisi', '* F', '2017 @ qq.com', '80', '2') where id = '2 ';

The following error is reported:

?

1

Error: near "where": syntax error

5. If there is no record to be replaced when executing the replace statement, a new record will be inserted.

In the student table, we make the id and name a composite primary key. Replace the record with id 100 and name a with replace statement. We can see that there is a record with name a In the table, but the id of this record is 7 instead of 100. That is to say, the record with id 100 and name a does not exist.

Execute the following statement:

?

1

2

Sqlite> replace into student (id, name, sex, email, fenshu, tecid, class) values

('200', 'A', '* F', '1970 @ qq.com', '80', '2', '1 ');

After the execution is complete, the data in the table is as follows:

(Figure 4)

We can see that a new record is inserted in the table.

6 if a field in the newly inserted or replaced record conflicts with another record in the table, the other record will be deleted.

The above step 2 also illustrates this problem. After inserting a new record with id 100 and name a, the record with id 2 and name lisi is deleted. Why? As we said at the beginning, the email field in the table has a unique constraint. The email of the record with id 2 is the same as the email in the record with the newly inserted id 100, both are 123456@qq.com. This results in a violation of the unique constraint. Therefore, before inserting a record with id 100, the record with id 2 is deleted.

Verify it again. Now we replace the record with id 5, name lisi3, and email it with the 2@163.com. The e-mail field of the record with id 5 in the table is also the 2@163.com, which causes a violation of the unique constraint.

Execute the following statement:

?

1

2

Sqlite> replace into student (id, name, sex, email, fenshu, tecid, class) values

('5', 'lisi3 ', 'F', '2 @ 163.com', '80', '2', '1 ');

After this statement is executed, the data in the table is as follows:

(Figure 5)

Comparison and found that the record with id 5 was replaced, and set the email of this record as 2@163.com, which conflicts with the original record with id 6, therefore, the record with id 6 is deleted, and there is no record with id 6 in it.

Comparison between replace and update statements

It should be familiar with update statements because they are often used. Next we will compare the actions of the update and replace statements, which are simply stated and will not be described by specific instances.

The update statement uses the where clause to locate the updated record;

The update statement can update one record at a time or multiple records, as long as these records are combined with the requirements of the where clause;

Update only updates the field value on the original record, does not delete the original record, and then inserts a new record;

If some fields are not specified in the update statement, these fields remain the original values without being left blank;