MySQL Table addition, deletion, modification and query （ Basics ）

• CRUD;create( newly added ),retrieve( retrieval ),update( to update ),delete( Delete ). ‘

Configure the default character set

Insert a record ：insert into student values(20,“haoshaokang”);

Be careful ： Chinese strings cannot be inserted here at the beginning , because mysql The default character set for databases and servers is Latin , So we can change the character set to utf8, One way is to set the character set when creating the database , such as ：create database java104 character set utf8;

View the default character set commands ：show variables like “character%”;

You can see that the default character set of the database and server are Latin

But there is another way to do it once and for all ： Modify the configuration file , Set the default character set of database and server to utf8. The profile name is ：(my.ini) route ：C:\ProgramData\MySQL\MySQL Server 5.7( This computer path ).

• Back up the configuration file first
• Modify the configuration file

• Save configuration file

• Restart the server
  

• Be careful ： The previously created database still uses the previous character set , Only the newly created character set will be used utf8.

After changing the configuration file , Let's check the default character set again ：

so , The database and server default character sets have become utf8;

The new data

insert into student values (22,“ Hao Shaokang ”); ( Insert a record )

insert into student values (22,“ Hao Shaokang ”),(12,“ Li Si ”); ( Insert multiple records ).

insert into student(name) values(“ Hao Shaokang ”),(“ Li Si ”); ( Insert multiple records in the specified column );

• stay MySQL Insert one record at a time , Inserting multiple records at a time is much slower than inserting multiple records at a time .
• Because every time the client sends a request to the server , Server resolution SQL After the statement , Start the operation , And return the operation result to the client .
• One by one, there will be multiple requests / Respond to . Just insert once, and there is only one request / Respond to .
• And the insertion operation requires certain traversal operations in the database , Find the right place . Insert more than once , The server needs to do many preparations .

Query data

Full column query

*select * from student; （ Full column query ）（ Represent wildcard ）

• however select * Operation may be risky . Because if there is a lot of data in the database （ Hundreds of millions ）, The server needs to find data from the disk , Then return to the client through the network , In this process, it is possible to put the disk IO Full , Or eat up the network bandwidth . If your server is an online server , Other users may have a bad experience .
• In case of execution select * operation , Sure ctrl+c Cancel operation .

Specified column query

select Name , Name from student; （ Specified column query ）

• select All operation results are presented in a temporary table , It will not change the data in the database .

Query with expression

select id ,name,chinese+math+english from exam_result;

select id,name,math+10 from exam_result;

Alias query

select id,name,chinese+math+english as total from exam_result;

select id,name,chinese+math+english total from exam_result;

（ Is there any as Fine ）

Go to check again

select distinct math from exam_result; （math Remove the weight in the column ）

select dintinct math,english from exam_result; （math and english It's all the same before going heavy ）

select distinct * from exam_result; （ Only when all the columns are the same can the weight be removed ）

Sort query

select * from exam_result order by math; ( The default is ascending sort )

select * from exam_result order by math desc; ( null )

select * from exam_result orde by math desc,chinese;

• Specify multi column sorting , If the data in the first column is equal , Then sort by the second column .

select name,chinese+math+english as total from exam_result order by total desc;

• Specify alias sorting .

NULL When operating with other data types , The result is NULL

Conditions of the query

Basic query

• Filter the data in the table according to the conditions , And return to the temporary table .

select * from exam_result where math > 60;

select * from exam_result where chinese > english;

select name,chinese+math+english as total from exam_result where chinese+math+english < 200;

• where Alias cannot be used after .

select * from exam_result where math > 60 and english > 70; （and Stands for logic and ）

select * from exam_result where math > 60 or english > 70; （or For logic or ）

• and Has a higher priority than or

Interval query

select * from exam_result where math between 60 and 80; （ Screening mathematics in 60 To 80 Between the records ）（ Closed interval ）

select * from exam_result where math in (60,78,63); （ Screening mathematics is 60,78,63 One of the records ）

Fuzzy query

Use wildcards ,% Replace any character ,_ Represents an arbitrary character

select * from exam_result where name like “ Grandchildren %”; （ Names beginning with sun ）

select * from exam_result where name like “% Grandchildren ” （ The name ending with sun ）

select from exam_result where name like “% Grandchildren %” （ Include sun's name ）*

select * from exam_result where name like “ Grandchildren _”;

NULL Query for

select * from exam_result where math is not NULL;

select * from exam_result where math is NULL;

• is and is not Can only be used to query NULL.
• Use = and NULL Can't match correctly ,NULL=NULL The result is NULL
• Use <=> You can talk to NULL Right match ,NULL<=>NULL The result is true

Paging query

Let the query result , Take out only part of it , So as to reduce the cost , Speed up query

select * from exam_result limit 3 offset 3; （ Limit the query of three records , Offset from the first record 3 Position start ）.

select * from exam_result limit 3;（ From the first record ）.

• Query operation can also add conditions [where] [order by] [limit]

Modifying data

update exam_result set math = 30 where name = “ The Monkey King ”;

update exam_result set math = 50,chinese = 70 where name = “ Cao mengde ”;

update exam_result set math = math-30 order by chinese+math+english limit 3; ( The last three students in the total score minus 30)

update exam_result set chinese = chinese - 10; （ All students' Chinese minus 10）

• Conditions can also be added to the modification operation [where] [order by] [limit]

Delete data

delete from exam_result where name = “ The Monkey King ”;

delete from exam_result order by math limit 3; （ Delete the record of the last three students in Mathematics ）.

delete from exam_result; （ Delete the entire table data ）

• Delete operation can also set conditions [where] [order by] [limit]