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 (field1,field2,...,fieldn) values (value1,value2,...,valuen), (value1,value2,...,valuen);
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];
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 ：
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;
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
5.9.select 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>
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 .