DML language

Data manipulation language

• Inserting: insert ing
• Modify: update
• Delete: delete

1, Insert statement

1. Mode 1 (common)

Syntax: insert into table name (field 1,...) values (value 1,...);

characteristic:

• The type of the inserted value should be consistent or compatible with the type of the column
• The order of columns can be reversed
• The number of columns and values must be consistent
• Column names can be omitted, and all columns are selected by default
• Non nullable columns must have values inserted. Nullable columns have two ways to insert nulls
  • Write column name and null for assignment
  • Do not write column names and do not assign values

#1. The type of the inserted value should be consistent or compatible with the type of the column
INSERT INTO jobs(`job_id`,`job_title`,`min_salary`,`max_salary`) 
VALUES('ABC','anything',1000,2000);

#2. The sequence of columns can be changed
INSERT INTO jobs(`job_id`,`min_salary`,`max_salary`,`job_title`) 
VALUES('DEF',1000,2000,'anything');

#3. The number of columns and values must be consistent
INSERT INTO jobs(`job_id`,`min_salary`,`max_salary`,`job_title`) 
VALUES('DEF',2000,'anything'); # report errors

#4. Column names can be omitted. All columns are defaulted, and the order of columns is consistent with that of columns in the table
INSERT INTO jobs VALUES('GHI','anything',1000,2000);

#5. Non nullable columns must insert values. Nullable columns have two ways to insert nulls
#Method 1: write column name and write null for assignment
INSERT INTO jobs(`job_id`,`job_title`,`min_salary`,`max_salary`)
VALUES('jkl',NULL,NULL,NULL);

#Method 2: do not write column name or assign value
INSERT INTO jobs(`job_id`)
VALUES('mno');

2. Mode II

Syntax: insert into table name set column name = value, column name = value

INSERT INTO `jobs`
SET `job_id`= 'OPQ',`min_salary` = 1000;

3. Comparison of the two methods

• Method 1 supports inserting multiple rows, while method 2 does not

INSERT INTO `jobs`
VALUES('ABC','anything',1000,2000)
,('DEF',1000,2000,'anything')
,('jkl',NULL,NULL,NULL);

• Method 1 supports sub query, while method 2 does not

INSERT INTO `jobs`(`job_id`,`min_salary`,`max_salary`,`job_title`) 
SELECT 'ABC','anything',1000,2000; # Replace values with the result of select

2, Modify statement

Modify the record of the table

1. Modification sheet record

• Syntax: update table name set column = new value, column = new value,... where filter criteria;

UPDATE `jobs` SET `min_salary` = 1500
WHERE `job_id`='ABC';

2. Modify multi table records

Sometimes you need a join table to modify the contents of another table according to the current table contents

• sql92 syntax: update table 1 alias, table 2 alias, set column = value,... where connection condition and filter condition;

• sql99 syntax: update table 1 alias inner|left|right join table 2 alias on connection condition set column = value,... where filter condition;

# Modify job_ The salary of employees whose title is Accounting Manager is 20000, which was originally 12000
# There is no job in the employees table_ The job table only has the attribute title
UPDATE `employees` e LEFT JOIN `jobs` j ON e.`job_id`=j.`job_id`
SET e.salary = 20000
WHERE j.job_title = 'Accounting Manager';

3, Delete statement

1. Method 1: delete

Single table deletion

• Syntax: delete from table name where filter criteria

  DELETE FROM jobs WHERE `job_id`='OPQ' OR `job_id`='mno';
  

Multi table deletion

• sql99 syntax:

  delete Aliases for table 1,Aliases for table 2
  from Table 1 aliases
  inner|left|right join Table 2 aliases on Connection conditions
  where Screening conditions;
  

2. Mode 2: truncate

• Syntax: truncate table name

  truncate table jobs
  

truncate can only delete the entire table and cannot add a where condition

3. Comparison of the two methods

• The where condition can be added to delete, but not to truncate
• delete is inefficient and truncate is efficient because truncate has no where condition
• If there is a self growing column in the table to be deleted, if you use delete to delete and then insert data, the value of the self growing column starts from the breakpoint, and after truncate is deleted, insert data again, and the value of the self growing column starts from 1.
• If delete has a return value, it will tell you how many rows are affected. If truncate is deleted, it has no return value. Although all rows are deleted, it does not tell you how many rows are affected
• delete deletion can be rolled back, while truncate deletion cannot be rolled back