One database every Day ----- day 3 data addition, modification, and deletion, database ----- Day

One database every Day ----- day 3 data addition, modification, and deletion, database ----- Day

Add, modify, and delete data from a database on a daily basis ----- Day 3

---- Reprinted with the source: coder-pig

This section introduces:

This section describes how to add, update, and delete data to a database.

1) add data to the table:

In the previous section, we deleted the T_Person table. First, we created a T_Person table. Next we will review it:

Create table T_Person (FId integer not null, FName VARCHAR (20), FAge INTEGER, FSex VARCHAR (10) primary key (FId )) Now, the table is created. Next, insert data! Use the following statement to insert an element:

Insert into T_Person (FName, FAge, FId, FSex) VALUES ('Tom ', 11, 1, 'man ')

After the statement is run, the insert is complete:

Because we set Fid as the primary key, if we insert a record with Fid = 1:

Insert into T_Person (FName, FAge, FId, FSex) VALUES ('jar', 12, 1, 'man ')
The following error is reported:

It also proves that the primary key mentioned above is unique and not empty!

Note the following when inserting records into a table:

1) The primary key cannot be blank. You can set the default value;

2) A non-empty field is set and cannot be blank.

3) The sequence of columns inserted can be arbitrary. Just like the above, FId does not have to be placed first!

4) if the foreign key is associated, the foreign key field needs to exist in the corresponding associated table when adding data, for example:

If the company id is added to the personnel table, the company id must exist in the company table. Otherwise, an error is reported !!!

2) Update (modify) The data in the table ① Update the values of a field in all records in the table:

For example, change all gender in the personnel table to 'Woman ', and change the age to 18.

You only need to use the following SQL statement

UPDATE T_PERSON SET FAge = '18', FSex = 'Woman'

Before modification:

After modification:

② If You Want To modify only a specific record:

Then you need to use the WHERE clause. For example, change Tom's Gender back to male and age to 25.

UPDATE T_PERSON SET FAge = '25', FSex = 'man' WHERE FName = 'Tom'

After running:

Okay. Updating table data is that simple! Of course, you can also add OR, NOT, and other Relational operators.

For more complex filtering logic, see the subsequent sections!

3) Delete table data: ① Directly clear all data in the table:

Delete from T_Person

In this way, you can directly delete all data in the T_Person table. In addition, it should be distinguished from the DROP of the previously learned Delete table:

DELETE: only deletes data and does not damage the table structure.

DROP: both data deletion and table structure Deletion

It can be visually understood as: DELETE only eats the rice in the bowl, and DROP not only eats the rice, but also throws the bowl !!

② Delete a specific record:

For example, to delete a table whose age is less than 20:

Delete from T_Person WHERE FAge <20

The records of FAge <20 in the table will be deleted!

The last two sentences are as follows:

This section describes how to add, delete, modify, and delete data in a table ~