Shulou(Shulou.com)05/31 Report--

This article will explain in detail the sample analysis of DML statements in MySQL. Xiaobian thinks it is quite practical, so share it with you as a reference. I hope you can gain something after reading this article.

DML operations refer to operations on table records in the database, mainly including insert, update, delete and select of table records, which are the most frequently used operations by developers on a daily basis.

1. insert

Format 1.

INSERT INTO emp(ename,hiredate,sal,deptno) VALUES('zzx1','2000-01-01','2000',1);

Format 2.

INSERT INTO emp VALUES('lisa','2003-02-01','3000',2);

You do not need to specify a field name, but the value must correspond exactly to the table field.

Format 3.

INSERT INTO emp(ename,sal) VALUES('dony',1000);

For fields that contain nullable fields, fields that are not empty but contain default values, and self-increasing fields, they do not need to appear in the field list after INSERT. Only the VALUE of the corresponding field name is written after VALUES:

format 4

In MySQL, INSERT statements can also insert multiple records at once:

INSERT INTO tablename(field1,field2,...... fieldn)VALUES(record1_value1,record1_value2,...... record1_valuen),(record2_value1,record2_value2,...... record2_valuen),...... (recordn_value1,recordn_value2,...... recordn_valuen);

2. update (update)

format 1

UPDATE emp SET sal=4000 WHERE ename='lisa';

format 2

UPDATE t1,t2,... tn SET t1.field1=expr1,t2.field2=expr2,... tn.fieldn=exprn [WHERE CONDITION];

Update data in multiple tables simultaneously

3. delete

format 1

DELETE FROM tablename [WHERE CONDITION];

format 2

DELETE t1,t2,... tn FROM t1,t2,... tn [WHERE CONDITION];

Note: No matter whether it is a single table or multiple tables, all records of the table will be deleted without adding the where condition, so be careful when operating.

4. query (select)

Basic syntax:

SELECT * FROM tablename [WHERE CONDITION];

1. To query non-duplicate records, you can use the distinct keyword:

SELECT DISTINCT deptno FROM emp;

2. Conditional query:

SELECT * FROM emp WHERE deptno=1;

In addition to "=", the condition after where can also be used>,=,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.