MySQL addition, deletion, query and modification (basic)

    CRUD（ newly added 、 Inquire about 、 modify 、 Delete ） Operation in sql Very common in . After creating the table CRUD, Use idea Remote connection mysql Server to learn these grammars , You can also use mysql client .idea Remote connection mysql The method is as follows ：

Use idea It can be changed at any time sql command , It's easy to use . Using shortcut keys ctrl+ Enter to execute sql command .

newly added

insert into  Table name ( Field 1,  Field 2, ...)
values (value1, value2, value3);

First create a student table , And then insert the data .

#  Create student table 
create table student
(
    --  Student id
    id int,
    --  The student's name 
    name varchar(20)
);

If it's a character type , You must use single or double quotation marks . All of the above are full column inserts ,MySQL It also supports specified column insertion .

Inquire about

select  Columns to query , Columns to query 
from  Table name 
[where  Conditions met ] #  Optional 
[order by  Sort ] #  Optional 
[limit n][offset m]; #  Optional 

Full column query

Query all the columns of the student table just inserted , Use *.

Generally, it is not recommended to use * Full column query . The more columns , It means that a large amount of data needs to be transmitted , At the same time, it may affect the efficiency of indexing .

Specified column query

Query only some columns , such as name Column , You can put * Change it to name. Between multiple columns , separate .

When specifying column queries, you do not need to follow the order defined by the table .

The query field is an expression

For fields that are numeric , When querying, you can perform mathematical operations such as addition, subtraction, multiplication and division .

You can see ,id Added 1.

Alias

If you want to align the id + 1 names , have access to as, Use alias as the name of the column .

duplicate removal

If there are duplicate values in the data we insert , I want to get rid of it , You can use distinct Keyword de duplication of a column of data . Insert a “3 Wang Ma Zi ”.

Conditions of the query

    sometimes , Some data in the table needs to be filtered , This is the time , You can use where Filter keywords . For example, check the students whose names are not pockmarked Wang .

Some operation symbols of conditional query are as follows ：

Be careful ：

（1）like Fuzzy matching .% Usage of , Search for example ‘ king %’, Means to find any string starting with Wang , Queen face followed by zero characters , A character , Multiple characters are OK , But the efficiency of this search is not high ._ Usage of , Search for example ‘ king _’, You can only find those with one character after the king , No other number of characters ;

（2）where Conditions can be expressed as , You can also add brackets , But you can't use aliases ;

（3）and Priority over or, At the same time, you need to add （）.

（4） If this time , Use the view list operation , You will find two king pockmarks . Didn't you go heavy , actually , All operations of query are in A temporary table Operating on , No impact on data on disk . To modify the data on the disk , Modification operation is required .

Sort

Use order by Sort .asc In ascending order （ Default ）,desc For the descending order . Right now id Sort in descending order .

Paging query

    Sometimes we don't want to display too many query results , Then you can use it limit Paging queries . Query from 0 Subscript start .

such as , Query the first two data .

summary ：

（1） You can use one or more tables in a query statement , Use commas between tables (,) Division , And use WHERE Statement to set the query conditions . This kind of query is multi table query , We won't discuss here ;

（2）select Command can read one or more records ;

（3） You can use asterisks （*） To replace other fields ,select Statement will return the data of all columns of the table ;

（4） have access to WHERE Statement to contain any condition ;

（5） have access to LIMIT Property to set the number of records returned ;

（6） Can pass OFFSET Appoint SELECT Statement start query data offset . By default, the offset is 0;

（7） The changes made in the query are all operated on the temporary table , It has no effect on the data in the disk .

modify

Modify operation can modify the data on the disk .

update  Table name 
set  Modified Columns ( Multiple columns ,  separate )
[where...]
[order by...]
[limit ...];

Revise Zhao Liu's id by 4.

Delete

delete from  Table name 
[where ...]
[order by ...]
[limit ...];

Delete the data of Wang Mazi .

delete The operation is mainly aimed at deleting the data in the table , But this table will not be deleted , Instead, empty the contents of the table ;drop Operation on table , It will not only delete the data in the table , Also delete the table .

Deleting is dangerous , Be careful with .

About further addition, deletion, modification and query , We'll discuss it next time .