What are the DML data operation statements in MySQL

Xiaobian to share with you what DML data operation statements in MySQL, I believe most people still do not know how, so share this article for your reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

article directories

1. Insert

II. Update

Delete/truncate

IV. DML statement exercises

1. Insert

Method 1: insert into table name (column name,...) values(values 1,...);

Method 2: insert into table name set column name = value, column name = value...

Compare whether to support inserting multiple rows Whether to support subquery Method 1 √× Method 2 √× [Insertion Method 1]# 1. The type of inserted value should be consistent with or compatible with the type of column INSERT INTO beauty (id,NAME,sex,borndate,phone,photo,boyfriend_id)VALUE (13,'Shuangsheng',' Nv','1999-02- 15',' 138686666', NULL,2);# 2. The order of columns can be reversed Insert INTO beauty (NAME,id,sex,borndate,phone,photo,boyfriend_id)VALUE ('Xu Jie', 14,'Female','1999-02- 16','138686661', NULL,2);# 3. Column names can be omitted, and the order of columns is consistent with the order of columns in the table INSERT INTO beautyVALUE (16,'Kings',' Female','2000-02- 16',' 138686661', NULL,2);#4. Support subqueries INSERT INTO beauty(id,NAME,phone)SELECT id,boyname,'110'FROM boys WHERE id where > set

2. Modify the record syntax of multiple tables:

update Table 1 Alias

[inner| left| right] join Table 2 Alias

on Connection Conditions

set Column = new value, Column = new value,...

where filter conditions;

[Modify records in single table]#Modify the phone number of girl surnamed Zhou in beauty table as 158888888 UPDATE beautySET phone='15888888'WHERE NAME LIKE '% Zhou %';[Modify records in multiple tables]#Change the phone number of Zhang Wuji's girlfriend to 2333UPDATE boys b1JOIN beauty b2 ON b1.id =b2.boyfriend_idSET b2.phone ='2333'WHERE b1.boyName=' Zhang Wuji ';

Delete/truncate

delete

Single table delete:delete from table name where filter criteria

Multiple table deletion:

delete table 1 alias, table 2 alias

from Table 1 Alias

inner| left| right join Table 2 Alias on join condition

where filter conditions;

truncate

Clear single table: truncate table name;

Note: truncate delete has no return value, delete delete has return value.

Compare whether you can add where condition whether you can rollback whether there is a return value when there is a self-increasing column in the efficiency table delete√√ Generally delete with delete, then insert data, the value of self-increasing starts from breakpoint truncate×× slightly higher than delete delete with truncate delete, then insert data, the value of self-increasing starts from 1 [delete]# 1. Single table delete #Delete girl information ending with 9 FROM Delete beauty phone LIKE '%9';# 2. Delete multiple tables #Delete Huang Xiaoming's information and his girlfriend's information Delete b1,b2FROM beauty b1INNER JOIN boys b2ON b1.boyfriend_id=b2.idWHERE b2.boyName ='Huang Xiaoming ';[truncate]#Delete all data TRUNCATE TABLE boys in single table only;

IV. DML statement exercises

After learning how to add, delete and correct DML statements, try to complete the following exercises:

Answer:

1. Execute the following SQL statement

CREATE TABLE my_employees(

Id INT(10),

First_name VARCHAR(10),

Last_name VARCHAR(10),

Userid VARCHAR(10),

Salary DOUBLE(10,2)

);

CREATE TABLE users(

id INT,

userid VARCHAR(10),

department_id INT

);

2.

DESC my_employees

3.

Method 1:

INSERT INTO my_employees

VALUES(1,'patel','Ralph','Rpatel',895),

(2,'Dancs','Betty','Bdancs',860),

(3,'Biri','Ben','Bbiri',1100),

(4,'Newman','Chad','Cnewman',750),

(5,'Ropeburn','Audrey','Aropebur',1550);

TRUNCATE TABLE my_employees;

Method 1:

INSERT INTO my_employees

SELECT 1,'patel','Ralph','Rpatel',895 UNION

SELECT 2,'Dancs','Betty','Bdancs',860 UNION

SELECT 3,'Biri','Ben','Bbiri',1100 UNION

SELECT 4,'Newman','Chad','Cnewman',750 UNION

SELECT 5,'Ropeburn','Audrey','Aropebur',1550;

4.

INSERT INTO users

VALUE(1,'Rpatel',10),

(2,'Bdancs',10),

(3,'Bbiri',20),

(4,'Cnewman',30),

(5,'Aropebur',40);

5.

UPDATE my_employees

SET Last_name='drelxer'

WHERE id=3;

6.

UPDATE my_employees

SET salary=1000

WHERE salary