preamble
Previously we have explained DDL statements in SQL statements. Today we will continue with the DML statement of SQL.
DML is the Data Manipulation Language.
Used to add, delete, and change data operations on the tables in the library.。
1. Add data to the specified field(INSERT)
2. Modify data(UPDATE)
3. Delete data(DELETE)
I. Adding data (INSERT)
Let’s first look at the syntax for adding data:
1. Add data to the specified field
INSERT INTO table name(field1, field2, ...) VALUES(value1, value2, ...) VALUES(value 1, value 2, ...) ;
2. Add data to all fields
INSERT INTO table name VALUES(value 1, value 2, ...) VALUES(value 1, value 2, ...) ;
3.Batch add data
Grammar I:INSERT INTO table name(field1, field2, ...) VALUES(value1, value2, ...) ,(value1, value2, ...) ,(value1, value2, ...) ;
Grammar II:INSERT INTO table name VALUES(value 1, value 2, ...) ,(value1, value2, ...) ,(value1, value2, ...) ;)
The notes are as follows:
1. The order of fields specified when inserting data needs to be a one-to-one correspondence with the order of values.
2. String and date type data should be in quotes.
3. The size of the inserted data should be within the specified range of the field.
We now demonstrate with the diagram above.
Demonstration 1 (adding data to a specified field):insert into employee(id,worknum,name,gender,age,idcard,entrydate) Values (1, '1', 'Amy' and 'male', 21, '123456789012345678', '2020-01-01');
Results demo:
The important thing to note here is that the fields in the tableagecategorized astinyint unsignedThe range is 0 and a positive number, so if we give a negative number, we will get an error. We won’t demonstrate it here.
You can see here that the prompt age is out of range.
Demonstration 2 (adding data to all fields):insert into employee(id,worknum,name,gender,age,idcard,entrydate) Values (2, '1', 'Tom' and 'male', 20, '123456789012345679', '2021-01-01');
For a demonstration of the results, see:
Demonstration III (batch data addition)：insert into employee values(3,'2','Daming',' male ',22,'223456789012345679','2022-01-01'), (4, '3', 'Sam', 'woman', 21, '323456789012345679', '2023-01-01'), (5, '4', 'Simon' and 'male', 24, '423456789012345679', '2024-01-01');
Results demo:
II. Modification of data (UPDATE)
Modify the data syntax:
UPDATE table name SET field name 1=value 1,field name 2=value 2,... [WHERE condition].
Note: Here the modification statement can be made with or without a condition. If there is no condition, then all the data in the whole table will be modified.
Next we still have 3 examples to demonstrate.
Example 1：(Modify the data with ID 1 and change the name to ‘Samon;) update employee set name = 'Yellow' where id = 1; The results of the demo are as follows:
Example 2: (Modify data with ID 1, change name to James, change gender to female) update employee set name = 'James',gender = 'female' where id = 1; The results of the demo are as follows:
Example 3 (change the start date of all employees to 2005-04-06): update employee set entrydate = '2005-04-06'; See the results of the demo:
III. Deletion of data (DELETE)
Grammatical format:DELETE FROM table name [WHERE condition]。
Here’s something to keep in mind：The DELETE statement may or may not have a condition; if there is no condition, all data in the entire table is deleted.
The Delete statement cannot delete the value of a field (but you can use theUPDATE）。****
Example 1 (Deleting an employee whose gender is female):
sql delete from employee where gender = 'female ';The results are shown below, please see:
Example 2: (Delete all employees) delete from employee; Results demo:
IV. Summary of DML statements
DML statement is a data manipulation language, the main role of the database table is the operation of adding, deleting and changing, that is, adding, modifying and deleting.
Add data:INSERT INTO table name(field1,field2,...) VALUES(value1,value2,...) [,(value1,value2,...) ...] [,,(value1,value2,...) ...] ;
Modify the data:UPDATE table name SET field1=value1,field2=value2 [WHERE Condition];
Delete data:DELETE FROM table name [WHERE condition].
Well, the above is the DML statement add, delete and change operations.
