MySQL DML statement summary

DML operations refer to operations on table records in the database, mainly including inserting (insert), updating (update), deleting (delete) and querying (select) table records. They are the most frequently used operations by developers in daily life.

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 don't need to specify the field name, but the value must strictly correspond to the table field.

Format 3.

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

For nullable fields, non-null fields with default values, and auto-increment fields, they do not need to appear in the field list after INSERT. Just write the VALUE of the corresponding field name after VALUES:

Format 4

In MySQL, the INSERT statement 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

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: Regardless of whether it is a single table or multiple tables, all records in the table will be deleted without adding a where condition, so be careful when operating.

4. Query (select)

Basic syntax:

SELECT * FROM tablename [WHERE CONDITION];

1. To query unique 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 conditions after where can also use comparison operators such as >, <, >=, <=, and !=;

You can also use logical operators such as or and and to perform multi-condition joint queries between multiple conditions.

3. Sorting and Restrictions:

SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC],...fieldn [DESC|ASC]];

Among them, DESC and ASC are sorting keywords.
DESC means sorting by field in descending order (largest at the top - smallest at the bottom).
ASC means ascending order (smallest at the top - largest at the bottom).
If this keyword is not written, the default is ascending order.
ORDER BY can be followed by multiple different sort fields, and each sort field can have a different sort order.

If the values ​​of the sort fields are the same, the fields with the same values ​​are sorted according to the second sort field, and so on. If there is only one sort field, records with the same sort field will be sorted out of order.

SELECT ......[LIMIT offset_start,row_count];

For the sorted records, if you want to display only a part of them instead of all of them, you can use the LIMIT keyword to achieve this.

LIMIT is often used together with ORDER BY to display records in pages.

Note: limit is a syntax extended by MySQL after SQL92, and is not applicable to other databases.

4. Aggregation

In many cases, we need to perform some summary operations, such as counting the number of people in the entire company or the number of people in each department, which requires the use of SQL set operations.

SELECT [field1,field2,......fieldn] fun_name
FROM tablename
[WHERE where_contition]
[GROUP BY field1,field2,......fieldn
[WITH ROLLUP]]
[HAVING where_contition];

The parameters are described as follows:

1. fun_name indicates the set operation to be performed, that is, the aggregate function. Commonly used ones are sum (sum), count(*) (number of records), max (maximum value), and min (minimum value).
2. The GROUP BY keyword indicates the field to be classified and aggregated. For example, if you want to count the number of employees by department, the department should be written after group by.
3. WITH ROLLUP is an optional syntax that indicates whether to re-aggregate the results after classification aggregation.
4. The HAVING keyword indicates that the classified results are further filtered by conditions.

Note: The difference between having and where is that having is to filter the results after aggregation, while where is to filter the records before aggregation. If the logic allows, we should use where to filter the records first. This will greatly improve the efficiency of aggregation because the result set is reduced. Finally, we can see whether to use having for re-filtering based on the logic.

5. Table Join

Table joins are divided into inner joins and outer joins. The main differences between them are:
The inner join selects only the records that match each other in the two tables.
The outer join will select other unmatched records. We often use inner join.

Outer joins are divided into left joins and right joins, which are defined as follows:

1. Left join: includes all records in the left table, even if there is no matching record in the right table.
2. Right join: includes all records in the right table, even those that do not match the left table.
   

SELECT ename,deptname FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno;

6. Subqueries

In some cases, when we query, the condition we need is the result of another SELECT statement. At this time, we need to use a subquery.
The keywords used for subqueries mainly include in, not in, =, !=, exists, not exists, etc.

SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept);

Note: The conversion between subqueries and table joins is mainly used in two aspects:

MySQL versions prior to 4.1 do not support subqueries. Table joins are needed to implement subqueries. Table joins are used to optimize subqueries in many cases.

7. Record Joint

We often encounter such applications, after querying the data of two tables according to certain query conditions, the results are merged and displayed together. At this time, you need to use the union and union all keywords to implement this function. The specific syntax is as follows:

SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
......
UNION|UNION ALL
SELECT * FROM tn;

The main difference between UNION and UNION ALL is that UNION ALL directly merges the result sets together, while UNION performs a DISTINCT on the result of UNION ALL to remove duplicate records.

The above is a summary of the detailed explanation of MySQL DML statements introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!