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 .​

(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 , Table data insertion with the most frequent daily operations (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, learn more about the knowledge of the database and get the database free e-book. .

MySQL The foundation DML More related articles on sentence

  1. Detailed explanation MySQL Second articles —DML sentence

    DML sentence : DML Operation refers to the operation of table records in the database , It mainly includes the insertion of table records (insert). to update (update). Delete (delete) Hecha (select), It is the most frequently used operation by developers . The following will be followed by ...

  2. mysql The basic chapter - SELECT Statement explanation

    The basic chapter - SELECT Statement explanation         SELECT Statement explanation One . Introduction to the experiment SQL Most commonly used SELECT sentence , Used to select data in a table , In this section, we will learn in detail through a series of hands-on operations SELEC ...

  3. MySQL Basics :DCL Sentence summary

    SQL Language can be roughly divided into DCL.DDL.DML Three , This paper mainly introduces MySQL 5.7 edition DCL sentence . summary DCL(Data Control Language) sentence : Data control statement , Permission to control the direct of different data segments ...

  4. MYSQL Basic common sentences 200 strip

    database # View all databases SHOW DATABASES ; # Create a database CREATE DATABASE k; # Delete a database DROP DATABASE k; # Using this database US ...

  5. MySQL Basics ---- dynamic SQL sentence

    Respect for the original :   dynamic sql Sentence basic syntax  1 : Ordinary SQL Statements can be used Exec perform  eg: ...

  6. MySql Infrastructure and SQL Statement execution process

    01. mysql Infrastructure SQL How statements are executed To learn mysql Infrastructure for , From a sql Learn how statements are executed . Generally, we write a query statement similar to the following : select user,password ...

  7. MySQL Medium DML、DQL And subquery

    One .MySQL Medium DML sentence 1. Use insert Insert data record : INSERT INTO `myschool`.`student` (`studentNo`, `loginPwd`, `student ...

  8. JDBC The basic chapter (MYSQL)—— Use statement perform DML sentence (insert/update/delete)

    Be careful : Among them JdbcUtil Is my custom connection tool class : Code example link : package day02_statement; import java.sql.Connection; import java.s ...

  9. SQL Basics -----DML sentence

    I've already introduced SQL The foundation DDL( Database definition language ) sentence , This is the address ) This article mainly introduces DML sentence ( Database manipulation language ...

  10. MySQL Of DDL sentence 、DML Statements and DCL sentence

    background : In recent years , Open source databases are becoming popular . Because of its free use . Simple configuration . Good stability . Excellent performance, etc , Open source databases occupy a large market share in low-end applications , and MySQL It is the outstanding representative of open source database .MySQL database ...

Random recommendation

  1. all requires API level 3 (current min is 1) Problem solving

    A few times all requires API level 3 (current min is 1) Error of , It turns out that the solution is to right-click on the project folder , choice Android Tools->Clear Li ...

  2. openstack flow control

    G Version of the flow control , Can be in horizon Through to flavor Configure to achieve 1. Yes admin jurisdiction , Click on admin Enter the management interface : Click on Flavors, Select what you want to control flavor: Click on more, find View Ext ...

  3. centos compile swoole

    /usr/include/php/ext/pcre/php_pcre.h:45: error: expected '=', ',', ';', 'asm' or '__attribute__' bef ...

  4. View servers IP The main command of connection number ( Use... When the server is attacked )

    see TCP The total number of States netstat -n | awk '/^tcp/ {++S[$NF]} END {fo (a in S). print a, S[a]}' Press IP Look at the number of connections sort netsta ...

  5. STL Algorithm design philosophy - Dual function , Binary predicates and in set Application in

    demo Binary function objects #include <iostream> #include <cstdio> #include <vector> #include <a ...

  6. beta Summary of the defense

    Evaluation table Number Team name Project name Format Content PPT speech Defence Total score /100 1 Tianji Group Fingertip encryption 14 13 13 13 13 66 2 PMS Your eyes 14 14 17 14 15 ...

  7. &lt; Basics &gt; PHP Advanced abstract class (abstract)、 Interface (interface)、Trait( features )

    abstract class PHP 5 Support abstract classes and methods . Classes defined as abstract cannot be instantiated . Abstract methods can only be in abstract classes , Abstract classes can contain non abstract methods A method defined as an abstraction simply declares how it is called ( Parameters ), Its specific function implementation cannot be defined Following ...

  8. stay linux The structures, ftp The server 【 turn 】

    1 install vsftpd yum install vsftpd 2 To configure vsftpd open vsftpd file : vi /etc/vsftpd/vsftpd.conf It is recommended to back up the file before the initial modification 2.1 ...

  9. Django mark_safe

    no need mark_safe: use mark_safe: usage : from django.shortcuts import render from django.utils.safestring import ...

  10. 20155328 《Java Programming 》 Experiment three Agile development and XP practice Experimental report

    One . coding standard Programming standards include : Descriptive name . Clear expression . Straightforward control flow . Readable code and comments , And the importance of consistently using certain rules and idioms in pursuing these contents . Here is a program without the most basic indentation : publi ...