DML database Operation language

DML language

Data Manipulation Language:

Inserting: Insert

Modified: Update

Remove: Delete

#一, insert statement

#方式一: Classic Insertion

/*

Grammar:

Insert into table name (column name,...) VALUES (value 1,...);

*/

SELECT * from Beauty;

#1. The type of the inserted value to be consistent or compatible with the type of the column

INSERT into Beauty (id,name,sex,borndate,phone,photo,boyfriend_id)

VALUES (13, ' Tang Yixin ', ' female ', ' 1990-4-23 ', ' 1898888888 ', null,2);

#2. A column that is not nullable must have a value inserted. How can a nullable column be inserted into a value?

#方式一:

INSERT into Beauty (id,name,sex,borndate,phone,photo,boyfriend_id)

VALUES (13, ' Tang Yixin ', ' female ', ' 1990-4-23 ', ' 1898888888 ', null,2);

#方式二:

INSERT into Beauty (id,name,sex,phone)

VALUES (15, ' Donna ', ' female ', ' 1388888888 ');

#3. Whether the order of the columns can be swapped

INSERT into Beauty (name,sex,id,phone)

VALUES (' Ziyi ', ' female ', 16, ' 110 ');

#4. The number of columns and values must be the same

INSERT into Beauty (name,sex,id,phone)

VALUES (' Guan Xiaotong ', ' female ', 17, ' 110 ');

#5. You can omit column names, all columns by default, and the order of columns is consistent with the order of the columns in the table

INSERT into Beauty

VALUES (18, ' Zhang Fei ', ' Male ', NULL, ' 119 ', null,null);

#方式二:

/*

Grammar:

Insert INTO table name

Set column name = value, column name = value,...

*/

INSERT into Beauty

SET id=19,name= ' Liu Tao ', phone= ' 999 ';

#两种方式大pk ★

#1, mode one support inserting multiple lines, mode two does not support

INSERT into Beauty

VALUES (23, ' Tang Yixin 1 ', ' female ', ' 1990-4-23 ', ' 1898888888 ', null,2)

, (24, ' Tang Yixin 2 ', ' female ', ' 1990-4-23 ', ' 1898888888 ', null,2)

, (25, ' Tang Yixin 3 ', ' female ', ' 1990-4-23 ', ' 1898888888 ', null,2);

#2, mode one support subquery, mode two does not support

INSERT into Beauty (id,name,phone)

SELECT 26, ' Victoria ', ' 11809866 ';

INSERT into Beauty (id,name,phone)

SELECT id,boyname, ' 1234567 '

From Boys WHERE id<3;

#二, modify statements

/*

1. Modify the record of the single table ★

Grammar:

Update table name

Set column = new value, column = new value,...

where filter conditions;

2. Modify multi-Table records "Supplement"

Grammar:

SQL92 Syntax:

Update table 1 aliases, table 2 aliases

Set column = value,...

where join condition

and filter conditions;

SQL99 Syntax:

Update Table 1 aliases

Inner|left|right Join table 2 aliases

On connection condition

Set column = value,...

where filter conditions;

*/

#1. Modify records for a single table

#案例1: Modify the name of Don's goddess in the beauty table for 13899888899

UPDATE beauty SET phone = ' 13899888899 '

WHERE NAME like ' Don% ';

#案例2: Modify the ID of the boys table to be 2, the name is Zhang Fei, charm value 10

UPDATE Boys SET boyname= ' Zhang Fei ', usercp=10

WHERE id=2;

#2. Modifying records for multiple tables

#案例 1: Change Mowgli's girlfriend's cell phone number to 114

UPDATE Boys Bo

INNER JOIN Beauty B on Bo. ' id ' =b. ' boyfriend_id '

SET b. ' Phone ' = ' 119 ', Bo. ' USERCP ' =1000

WHERE bo. ' Boyname ' = ' Zhang Mowgli ';

#案例2: The girl who has no boyfriend is numbered number 2nd.

UPDATE Boys Bo

Right JOIN beauty B on Bo. ' id ' =b. ' boyfriend_id '

SET B. ' boyfriend_id ' =2

WHERE bo. ' id ' is NULL;

SELECT * from Boys;

#三, DELETE statements

/*

Mode one: Delete

Grammar:

1, the deletion of the single table "★"

Delete from table name where filter criteria

2. Deletion of multiple tables "supplement"

SQL92 Syntax:

Delete alias for table 1, alias for table 2

From table 1 aliases, table 2 aliases

where join condition

and filter conditions;

SQL99 Syntax:

Delete alias for table 1, alias for table 2

From table 1 aliases

Inner|left|right Join table 2 alias on join condition

where filter conditions;

Way two: Truncate

Syntax: TRUNCATE TABLE name;

*/

#方式一: Delete

#1. Deletion of a single table

#案例: Delete the goddess message with phone number ending in 9

DELETE from Beauty WHERE The phone like '%9 ';

SELECT * from Beauty;

#2. Deletion of multiple tables

#案例: Delete The information of Mowgli's girlfriend

DELETE b

From Beauty b

INNER JOIN boys Bo on B. ' boyfriend_id ' = bo. ' ID '

WHERE bo. ' Boyname ' = ' Zhang Mowgli ';

#案例: Delete Huang Xiaoming's information and his girlfriend's information

DELETE B,bo

From Beauty b

INNER JOIN boys Bo on B. ' boyfriend_id ' =bo. ' id '

WHERE bo. ' Boyname ' = ' Huang Xiaoming ';

#方式二: Truncate statements

#案例: Remove the >100 information from the glamour value

TRUNCATE TABLE Boys;

#delete PK Truncate "face question ★"

/*

1.delete can add a where condition, truncate cannot be added

2.truncate Delete, lost efficiency

3. If the table you want to delete has a self-growing column,

If you delete it with delete, and then insert the data, the value of the self-growing column starts at the breakpoint,

When truncate is deleted, the data is inserted, and the value of the self-growth column starts at 1.

4.truncate Delete no return value, delete delete has return value

5.truncate Delete cannot be rolled back, delete delete can be rolled back.

*/

SELECT * from Boys;

DELETE from Boys;

TRUNCATE TABLE Boys;

INSERT into Boys (BOYNAME,USERCP)

VALUES (' Zhang Fei ', 100), (' Liu Bei ', 100), (' Cloud Long ', 100);

DML database Operation language