current position:Home>DML statement of MySQL Foundation

DML statement of MySQL Foundation

2022-01-27 01:11:24 Yuanfuyue

DML sentence

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

purpose : Used to add 、 modify 、 Delete and query Database records , And check data integrity .

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

DML The object of operation is Data of library table ( Record ).

It mainly includes inserting (insert)、 to update (update)、 Delete (delete) And query (select).

DML Statement is the most frequently used operation by developers .

1. insert record

Insert a record

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

Insert multiple pieces of data at once

insert into tablename

2. Update record

Update single table data

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

Update the data of multiple tables at the same time

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

It is usually used to dynamically update the fields of another table according to the fields of one table

  Explain here : brackets “[ ]” Represents an optional parameter , Can have optional parameters .

3. Simple query records

Simple condition query . Symbol “*” Query all fields on behalf of , If you just want to see some of these columns , Then write the column name of the table .

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


     to Name or Table name Alias keywords :AS Or don't write keywords . Such as :

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

No alias , Then the column name of the query result is the original column name of the table ;

Take the alias , Then the column name of the query result is alias


4. Delete record

Delete a single table record

delete from tablename [where condition];

Delete the data of multiple tables at the same time

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

explain : No addition where Condition rule Delete full table data

Single table “ Add, modify, check and delete ” Operation cases :

Multiple tables “ Delete ” Operation cases :

   Look at another deletion case

  Finally, let's look at a deletion case

  Multi table deletion conclusion :

   Conditions established , Delete the corresponding data of the conditional table , If there are no conditions, the whole table will be deleted ;

   Conditions not established , Do not delete .

Leave a question : Multi table deletion does not give where What will the conditions be ?

About updating data of multiple tables at the same time , You can explore on your own , After all, learning with thinking is the most valuable .

5. Detailed explanation of query records (DQL sentence )

DQL(Data Query Language) Database query language .

Database query statements are like playing with building blocks , Piece by piece , Each bracket “[]” Represents a building block .

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

5.1. Query non duplicate records

select distinct filed from tablename;

distinct  The key word is right “ Query result set ” duplicate removal , Look at another example to see .

5.2. Conditions of the query

-- Query all columns :
select * from tablename [where condition];
-- Query specified column :
select [field1,field2,...,fieldn] from tablename [where condition];

Common conditions (condition)

 be equal to :field = xxx
 Greater than :field > xxx
 Less than :field < xxx
 It's not equal to :field != xxx
 It's not equal to :field <> xxx
 be equal to null:field is null
 It's not equal to null:field is not null
 Use logical operators between multiple conditions :and  and  or 
 Fuzzy query "_":field like _x_xx_      Symbol "_" Represents matching a single arbitrary character , You can put it next to the character you like "_"
 Fuzzy query "%":field like %x%xx%      Symbol "%" On behalf of the match 0~n Any character , You can put it next to the character you like "%"
in Statement query :field in(value1,value2,...)   Inquire about field  and  in  Array matches , If you don't deserve it, there's no data 
not in Statement query :field not in(value1,value2,...)    Inquire about field  and  in  Array doesn't match , If you don't deserve it, you have data 

How to use other common conditions , Refer to common conditions to operate .

5.3. Aggregate query

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

explain :

fun_name   Represents an aggregate function , Commonly used : Sum up sum()、 Record number count(*)、 Average avg()、 Maximum max()、 minimum value min();
[where condition]  It is the condition query mentioned above ;
[group by field1,field2,...,fieldn [with rollup]] 
  group by  Keyword means to field Fields are classified and aggregated ;
  with rollup  Keyword indicates whether to re summarize the results after classification and aggregation 
[having condition]  Filter the query result set after classification and aggregation by criteria 
where  and  having  The difference between :
  1. where It's grouping front Conditional filtering ;having It's grouping after Conditional filtering .

  2. where Yes, it is Original list name do filter ;having Yes, it is Query result set column name do filter .

     Preference where filter , This can reduce the result set , So as to improve the efficiency of classification and aggregation .

brackets "[]" Means optional , in other words group by Ahead [where condition]  not essential , hinder [with rollup] and [having condition] It is also dispensable .

group by  and distinct  The difference between :    group by  It's about designating  Column  Grouping ;distinct  It's a query  result  Deduplication .

 Join in where  and  having Example :

Column name is avg(stu_age) It looks a little strange , Give him an alias and it'll be clear at a glance :

5.4. Sort query

-- The simplest sort query :
select * from tablename order by field;
-- Complete grammar :
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  In descending order ( From big to small );ASC  Represents ascending order ( From small to large ),asc Sort by default . That means you just remember desc  That's all right. . It happened that desc  Is the keyword of query table design , And the grammar is very simple :desc tablename;

Single column sort :

Multi column sorting : Use symbols “,”  Just separate

5.5.limit Inquire about

   Also known as restricted query 、 Range queries 、 Paging query

-- The simplest limit Inquire about :offset_start Same as array subscript , from 0 Start counting 
select * from tablename limit offset_start,row_count;
-- Complete grammar : This article sql It 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: From 1 Bar start , Query two pieces of data

Example 2: From 2 Bar start , Query two pieces of data

= The above is the syntax of single table query =
= The following is about the syntax of two table joint query =

5.6. even surface Inquire about

Used when you need to associate multiple table data .  Just learn the left connection and the inner connection .

Left connection Select all records in the left table , Whether the right table matches it or not ; The left table is the main , Associated right table information

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

Internal connection : Select only 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. Subquery

Used when another query result is required as a query condition . The subquery uses “()” Cover up .

Such as : Ask the students “ Zhang San ” The achievement of

In some cases , Sub query can be transformed into linked table query . For example, the above example can be written as a linked table query :

5.8. Record Union

  Merge the query results of two or more tables into a result set output . The condition of merging is the combination of multiple tables Number of query result fields To be the same , Note that the number of query result fields is 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

The number of query result fields is different , False report

Fixed number of fields in query result set The order in which statements are executed

Prove whether the execution sequence is correct , It can also be regarded as a consolidation of the previous knowledge .​

(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

Only this and nothing more , On the table data with the most frequent daily operations Insert (insert)、 to update (update)、 Delete (delete) And query (select) The sentence is over . The most complicated query statement , Also known as DQL sentence , yes DML The key points in the sentence .

Pay attention to the official account and learn more about the knowledge and access to the database. Database free ebook .

copyright notice
author[Yuanfuyue],Please bring the original link to reprint, thank you.

Random recommended