MySQL Basic Tutorial: Detailed Explanation of DML Statements

DML statements

DML (Data Manipulation Language) statement: data manipulation statement.

Purpose: Used to add, modify, delete and query database records, and check data integrity.

Common keywords: insert, update, delete, select, etc.

The object of DML operation is the data (records) of the library table.

It mainly includes insert, update, delete and query.

DML statements are the operations most frequently used by developers.

1. Insert records

Insert a record

insert into tablename (field1,field2,...,fieldn) values ​​(value1,value2,...,valuen);

Insert multiple records at one time

insert into tablename

 (field1,field2,...,fieldn)

values

 (value1,value2,...,valuen),

 (value1,value2,...,valuen);

2. Update records

Update single table data

update tablename set field1=value1,filed2=value2,...fieldn=valuen [where condition]

Update data in multiple tables simultaneously

update t1,t2,...,tn set t1.field1=expr1,tn.field=exprn [where condition];

Usually used to dynamically update the fields of another table based on the fields of one table

Note: The square brackets “[ ]” represent optional parameters, i.e., parameters that may or may not be present.

3. Simple query records

Simple conditional query. The symbol "*" represents querying all fields. If you only want to view certain columns, write the column names of the table.

select * from tablename [where condition];

select field1,field2,...,fieldn from tablename [where condition];

Aliases

Keywords for aliasing column or table names: AS or no keywords. like:

select field1 AS f1,field2 f2 from tablename;

select t2.field1 AS f1,t1.field2 f2 from table1 as t1,table2 t2;

If no alias is used, the column names of the query results are the original column names of the table;

If you use an alias, the column name of the query result will be the alias.

4. Delete records

Delete single table records

delete from tablename [where condition];

Deleting data from multiple tables at the same time

delete t1,t2,...,tn from t1,t2,...,tn [where condition];

Note: If no where condition is added, the entire table data will be deleted.

Single table "add, modify, query and delete" operation example:

Multi-table "delete" operation case:

Let’s look at another deletion case

Finally, let’s look at the deletion case

Conclusion of multi-table deletion:

If the condition is met, the corresponding data in the conditional table will be deleted, and if there is no condition, the entire table will be deleted;

If the condition is not met, none of them will be deleted.

I have a question: What happens if you delete multiple tables without giving a where condition?

Regarding the operation of updating data in multiple tables at the same time, you can explore it on your own. After all, learning with thinking is the most valuable.

5. Detailed explanation of query records (DQL statements)

DQL (Data Query Language) is database query language.

Database query statements are like playing with building blocks, piece by piece, and each square bracket "[]" represents a building block.

First, list the query syntax one by one in a progressive manner.

5.1. Query unique records

select distinct filed from tablename;

The distinct keyword is used to remove duplicates from the “query result set”. This will become clearer with an example.

5.2. Conditional Query

--Query all columns:

select * from tablename [where condition];

--Query the specified column:

select [field1,field2,...,fieldn] from tablename [where condition];

Commonly used conditions

Equal to: field = xxx

Greater than: field > xxx

Less than: field < xxx

Not equal to: field != xxx

Not equal to: field <> xxx

Equal to null: field is null

Not equal to null: field is not null

Use logical operators between multiple conditions: and and or

Fuzzy query "_": field like _x_xx_ The symbol "_" represents matching a single arbitrary character. You can add "_" next to the character you like.

Fuzzy query "%": field like %x%xx% The symbol "%" represents matching 0 to n characters. You can add "%" next to the characters you like.

In statement query: field in(value1,value2,...) query field and in array match, if not match, no data

Not in statement query: field not in(value1,value2,...) query field and in array do not match, if not match, there is data

For specific usage of other commonly used conditions, please refer to the commonly used conditions.

5.3. Aggregate Query

--The simplest aggregation query:
select [field1,field2,...,fieldn] fun_name from tablename group by field;
--Full syntax:
select [field1,field2,...,fieldn] fun_name from tablename
 [where condition]
 [group by field1,field2,...,fieldn [with rollup]]
 [having condition];

illustrate:

fun_name represents the aggregation function, commonly used ones are: sum(), record count(*), average value avg(), maximum value max(), minimum value min();

[where condition] is the conditional query mentioned above;

[group by field1,field2,...,fieldn [with rollup]]

The group by keyword indicates classification and aggregation of the field field;

The with rollup keyword indicates whether to re-aggregate the results after classification aggregation.

[having condition] Filter the query result set after classification aggregation by condition

The difference between where and having:

1. where is the conditional filtering before grouping; having is the conditional filtering after grouping.
2. where is to use the original table column name as conditional filtering; having is to use the query result set column name as conditional filtering.

Prioritize where condition filtering, which can reduce the result set and thus improve the efficiency of classification aggregation.

The brackets "[]" represent optional meanings, which means that the [where condition] before group by is optional, and the [with rollup] and [having condition] after it are also optional.

The difference between group by and distinct: group by groups the specified columns; distinct deduplicates the query results.

Example of adding where and having:

The column name avg(stu_age) looks a bit strange, so it will be clear at a glance if we give it another name:

5.4. Sorting Query

--The simplest sort query:
select * from tablename order by field;
--Full syntax:
select [field1,field2,...,fieldn] fun_name from tablename
 [where condition]
 [group by field1,field2,...,fieldn [with rollup]]
 [having condition]
 [order by field1 [desc|asc], field2 [desc|asc],...,fieldn [desc|asc]];
DESC stands for descending order (from large to small); ASC stands for ascending order (from small to large), and asc is the default sorting. In other words, you only need to remember desc. It happens that desc is the keyword for query table design, and the syntax is very simple: desc tablename;

Sort by single column:

Multi-column sorting: Use the symbol "," to separate them

5.5. Limit query

Also known as restriction query, range query, paging query

--The simplest limit query: offset_start is the same as the array subscript, starting from 0. select * from tablename limit offset_start,row_count;
--Complete syntax: This SQL is the complete version of single table query select [field1,field2,...,fieldn] fun_name from tablename
 [where condition]
 [group by field1,field2,...,fieldn [with rollup]]
 [having condition]
 [order by field1 [desc|asc], field2 [desc|asc],...,fieldn [desc|asc]]
 [limit offset_start,row_count];

Example 1: Starting from item 1, query two pieces of data

Example 2: Starting from item 2, query two pieces of data

=========================

= The above is the syntax for single table query=

= The following is the syntax for joining two tables =

=========================

5.6. Join table query

Used when you need to associate data from multiple tables. Just learn left join and inner join.

Left join: select all records in the left table, regardless of whether the right table matches it; the left table is the master, and the right table information is associated

select * from t1 left join t2 on t1.field1=t2.field2;

Inner join: only select the records that match each other in the two tables, if there is no match, the result is empty

select * from t1 inner join t2 on t1.field1=t2.field2;

select * from t1,t2 where t1.field1=t2.field2;

5.7. Subqueries

Used when another query result is needed as a query condition. Subqueries are enclosed in "()".

For example: query the grades of student "Zhang San"

In some cases, a subquery can be converted into a join-table query. The above example can be written as a join table query:

5.8. Record Union

Combine the query results of two or more tables into a result set for output. The condition for merging is that the number of query result fields of multiple tables must be the same. Note that it is the number of query result fields, not the number of table fields.

select f1,f2,...,fn from t1

union/union all

select f1,f2,...,fn from t2

...

union/union all

select f1,f2,...,fn from tn

If the number of query result fields is not equal, an error will be reported

Fixed number of query result set fields

5.9. Execution order of select statements

Proving by yourself whether the execution order is correct can also be regarded as a consolidation of the knowledge learned previously.​

(7) SELECT

(8) DISTINCT <select_list>

(1) FROM <left_table>

(3) <join_type> JOIN <right_table>

(2) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list>

(6) HAVING <having_condition>

(9) ORDER BY <order_by_condition>

(10) LIMIT <limit_number>​

6. Summary

So far, we have finished talking about the insert, update, delete and query statements of table data, which are the most frequently used in daily operations. The most complex query statement, also known as DQL statement, is the focus of DML statements.

This is the end of this article about the DML statements in the MySQL Basics Tutorial. For more information about the DML statements in the MySQL Basics, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!