CRUD operation of MySQL database tables

1. Insert

grammar：

INSERT [INTO] table_name
    [(column [, column] ...)] #Column Field    VALUES (value_list) [, (value_list)] ... #Column field content    
value_list: value, [, value] ...

Case：

-- Create a student table
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    sn INT NOT NULL UNIQUE COMMENT 'Student number',
    name VARCHAR(20) NOT NULL,
    qq VARCHAR(20)
);

1. Single row data - full column insert + specified column insert

• Specify column insertion: The contents of column fields and column fields must match one by one

insert into student (sn, name, qq) values (123, 'Zhang Fei', '12345');

• Insert the entire column: There are two ways to insert the entire column, one is to omit itvaluesThe field names on the left, all of them are specified

insert into students values (10, 124, 'Guan Yu', '13245');
insert into students (id, sn, name, qq) values (14, 125, 'Liu Bei', '14525');

Can be omittedinto

insert students (sn, name, qq) values (126, 'Zhuge Liang', '12525');

2. Multi-row data - Full column insert + Specify column insert

Specify column multi-row insertion

insert students (sn, name, qq) values (127, 'Cao Cao', '15256'), (128, 'Xu You', '23445');

Insert full columns and multiple rows

insert students values (20, 129, 'Sun Quan', '12256'), (21, 130, 'Lu Bu', '33445');

3. Insert otherwise update

The insertion failed because the value corresponding to the primary key or unique key already exists.

But I just want it to confirm whether it exists in the database first. If it does not exist, it will be inserted. If it exists, don't stop me and then execute the subsequent modification statement.

Selective synchronous update operation Syntax:

INSERT ... ON DUPLICATE KEY UPDATE
    column = value [, column = value] ...

If it does not exist, insert it. If there is a primary or unique key conflict, do not report an error. Then execute the subsequent modification statement.

insert into students values (14, 111, 'Zhou Yu', '56321') on duplicate key update sn=111, name='Zhou Yu', qq=56321;

Note that the updated value cannot conflict with other primary and unique keys, otherwise it cannot be updated.

• 0 row affected: There is conflicting data in the table, but the value of the conflicting data is equal to the value of update

• 1 row affected: There is no conflicting data in the table, the data is inserted

• 2 row affected: There is conflicting data in the table and the data has been updated

Get the number of affected rows of data through MySQL function

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

4. Replacement

If the primary or unique key does not conflict, then insert it directly. If the conflict occurs, thenDelete and insert（replace into）

mysql> insert into students values (22, 31,'Mike', '9856');
Query OK, 1 rows affected (0.00 sec)
mysql> select * from students;
+----+----+------+------+
| id | sn | name | qq   |
+----+----+------+------+
| 22 | 31 | Mike | 9856 |
+----+----+------+------+

mysql> REPLACE INTO students (sn, name, qq) values (31, 'Tom', '9856');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from students;
+----+----+------+------+
| id | sn | name | qq   |
+----+----+------+------+
| 23 | 31 | Tom  | 9856 |
+----+----+------+------+

• 1 row affected: There is no conflicting data in the table, the data is inserted
• 2 row affected: There is conflicting data in the table, delete it and reinsert it
• Here fromidYou can see that it was deleted and inserted becauseidIt's increased by itself, just now it was 22, now it's 23.

2. Retrieve

grammar:

SELECT
    [DISTINCT] {* | {column [, column] ...}
    [FROM table_name] # Filter from which table    [WHERE ...] # Filter criteria    [ORDER BY column [ASC | DESC], ...] # Sort filter results    LIMIT ... # Limited number of filtered items

distinct: Deduplicate content

• *: Full column query
• column, column…: Specify column query

Case：

-- Create a table structure
CREATE TABLE exam_result (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL COMMENT 'Class name',
    chinese float DEFAULT 0.0 COMMENT 'Chinese score',
    math float DEFAULT 0.0 COMMENT 'Math Score',
    english float DEFAULT 0.0 COMMENT 'English score'
);
 
-- Insert test data
INSERT INTO exam_result (name, chinese, math, english) VALUES
    ('Tang Sanzang', 67, 98, 56),
    ('Sun Wukong', 87, 78, 77),
    ('Pig Woneng', 88, 98, 90),
    ('Cao Mengde', 82, 84, 67),
    ('Liu Xuande', 55, 85, 45),
    ('Sun Quan', 70, 73, 78),
    ('Song Gongming', 75, 65, 30);

1. select column

Full column queryNormally not recommended*The more columns are conducted for full-column query, the larger the amount of data to be transmitted; it may affect the use of indexes.

select * from exam_result;

Specify column queryThe order of specifying columns does not need to be defined in the order of the table.

select id, name, chinese from exam_result;

Query fields are expressions selectVery special, you can followselectThe built-in clauses, filter conditions, etc. can also be followed by expressions

• For query resultsSpecify an aliasgrammar:

SELECT column [AS] alias_name [...] FROM table_name; # Here you can do not bring as

• The result is deduplicated

select distinct math from exam_result;

2. where conditions

• Just now, I filtered the overall information of the table, but there must be filtering conditions when doing the query.
• The filtering by condition affects the number of entries or rows displayed in the future. In the past, all rows were taken out according to the number of positions.
• If a column wants to take those rowswhereThe conditions are determined.

whereIt is a filter clause, which can be followed by a specific comparison operator to decide how we should filter.

whereIt's a bit like the if statement in C/C++, which is judged based on the following conditions.

📚 Comparison operator：

💡 Things to note

• =When it is carried out on both sidesnullValue comparison cannot participate in the operation
• If you want to judgenullIs it equal to use<=>
• But this is not the case. I usually prefer to use it.IS NULLTo determine whether a value is null

📚 Logical operators:

Specific cases

• Based on the above table building, only instructions will be written below, and the final result is omitted to save space.

① Names and English scores of students who fail in English ( < 60 )

select name, english from exam_result where english < 60;

**② **Students with Chinese scores of [80, 90] and Chinese scoresuseANDMake conditional connections

select name, chinese from exam_result where chinese >= 80 and chinese <= 90;

useBETWEEN … AND …condition

select name, chinese from exam_result where chinese between 80 and 90;

③ Students with math scores of 58 or 59 or 98 or 99 and math scoresuseORMake a conditional connection, and satisfy any one is true

select name, math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;

useINThe condition, if any of the () is true

select name, math from exam_result where math in (58, 59, 98, 99);

• Classmates surnamed Sun and classmate SunSometimes the match does not give the specific value a more detailed field meaning, but may only give a fuzzy search keyword. Just like above. Anyway, if the conditions are not given, then a vague condition is given.
• We can useLIKEFuzzy matching%Match any multiple (including 0) characters.MySQLYou can use ‘ ’ or “ ” to represent strings

select name from exam_result where name like 'Sun%';

_Match a strict arbitrary character:

select name from exam_result where name like 'Sun_';

④ Students with better Chinese scores than English scores

select name, chinese, english from exam_result where chinese > english;

⑤ Students with a total score of less than 200

mysql> select name, math + chinese + english total from exam_result where math + chinese + english < 200;
+-----------+-------+
| name      | total |
+-----------+-------+
| Liu Xuande    |   185 |
| Song Gongming    |   170 |
+-----------+-------+

# But writing this will cause problemsmysql> select name, math + chinese + english as total from exam_result where total < 200;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'

Why does it report an error in unknown column total? Haven’t we renamed it? Isn’t this total already available? Why don’t you know total here?

The explanation is as follows:

• It's very simple, we must execute it firstfrom, in executionwhere, then executeselect
• After filtering, execute,From 1, take 2, go to 3, filter
• **Reason: ** Only add up to the smaller than operation 2 and then print it, saving space
• So renames cannot be used in where

⑥ Chinese score > 80 students who are not surnamed Sun

ANDandNOTUse of

select name, chinese from exam_result where chinese>80 and name not like 'Sun%';

⑦ Sun, otherwise the total score is required > 200 and Chinese score < Mathematics score and English score > 80

Either it is Sun or the following requirements have to be met. There are only two conditions in total. In mysql, if there are many conditions, you can use () to enclose this condition to represent a unit

select name, chinese, math, english, chinese + math + english total 
from exam_result 
where name like 'Sun_' or (chinese + math + english > 200 and chinese < math and english > 80);

3. Result sorting – order by

grammar：

SELECT ... FROM table_name [WHERE ...]
    ORDER BY column [ASC|DESC], [...];

• ASC is ascending (from small to large) (ascending)
• DESC is descending (from large to small) (descding)
• Default is ASC

Note: The order returned is undefined without the ORDER BY clause. Never rely on this order.

【Case】

• Operation based on the table created above

Yi students and math scores are displayed in ascending order of math scores

select name, math from exam_result order by math asc;

• Notice:NULLConsidered as smaller than any value, appearing at the top ascending order

② Check the grades of students in each subject, display them in descending order of mathematics, ascending order of English, and ascending order of Chinese.

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

③ Check students and total scores, from high to low

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

• Hey, I have a question here ❓ Why can I use alias to sort in order by, but there is no way to use alias after where?

Therefore, we can draw a conclusion: whether alias can be used depends entirely on the execution order of the clauses in the current SQL!

• If you want to sort the data in the table structure, you must have data first!
• Some people may say that it is not a table structure that has data naturally, so they can directly sort out the table structure data, and then select the rows, but we will definitely not be willing to waste time processing data sorting.
• Because you don’t filter, there must be a large number of data when sorting, but it doesn’t need to be sorted. Sorting these data is a waste of space and time. MySQL is not that stupid!
• So I'll filter it firstselectReorderorder by

4. Filter pagination – limit

What is pagination?

• If the amount of data in a table is too large, if there are a lot of queries in the column at this time, it will be inconvenient to view and analyze
• Sometimes we don't want to show so much at once, so we can do the resultsLIMITPagination.

limitThere is no filtering function itself, just follow the number followed by it and follow the result to be displayed according toStarting locationandStep length, show us multiple records.

grammar：

-- The starting subscript is 0
 
-- from s start，filter n Results
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
 
-- from 0 start，filter n Results
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
 
-- from s start，filter n Results，More clearer than the second usage，Recommended to use
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

suggestion:When querying unknown tables, it is best to add oneLIMIT 1, to avoid the database being stuck due to the large amount of data in the table and querying the entire table data.

• By default, start with 0 subscript and filter multiple records.
• limit Pagination readingThe execution order is at the end
• You can also specify that the step size followed by the subscript start filtering. fromSpecify the location to start, read multiple records in succession.

as follows：

mysql> select id, name, math from exam_result order by id limit 3 offset 0;
+----+-----------+------+
| id | name      | math |
+----+-----------+------+
|  1 | Tang Sanzang    |   98 |
|  2 | Sun Wukong    |   78 |
|  3 | Pig Wuneng    |   98 |
+----+-----------+------+

mysql> select id, name, math from exam_result order by id limit 3 offset 6;
+----+-----------+------+
| id | name      | math |
+----+-----------+------+
|  7 | Song Gongming    |   65 |
+----+-----------+------+

• LIMITThe filtering lines are followed by.OFFSETThe following starts from that line.
• limitYou can paginate it. For example, if there is too much data, you can read it in this way.

Summary of keyword execution order

• **from > on> join > where > group by > with > having > select (** with renaming)> distinct > order by > limit

3. Update | Delete | Insert query results

3.1 Update

grammar：

UPDATE table_name SET column = expr [, column = expr ...]
    [WHERE ...] [ORDER BY ...] [LIMIT ...]

• setThe value to be reset is followed by, which can be multiple columns.
• Usually inupdateWhen    , the corresponding one must be usedwhereThe clause performs conditional filtering, and if not, it will update all the columns specified in this table, which is unreasonable.

[Case]: Based on the exam_result table created above

Update column values ​​for the query results： set

① Change Sun Wukong's math score to 80 points

update exam_result set math=80 where name='Sun Wukong';

② Change Cao Mengde's math score to 60 points and Chinese score to 70 points

update exam_result set math=60, chinese=70 where name='Cao Mengde';

③ Add 30 points to the math scores of the top three students in the total score

• The updated value is changed based on the original value.
• Note that the syntax of math += 30 is not supported according to the update.

④ Update the Chinese scores of all students to 2 times the original

update exam_result set chinese=chinese*2;

Note: Use the statements that update the entire table with caution!

3.2 Delete

grammar:

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

• Generally, you will delete the conditions
• Without adding conditions, the content of the entire table is deleted, but the table structure is still there.
• Delete table structure drop

For example, delete the previous scores of Sun Wukong in exam_result, as follows:

delete from exam_result where name='Sun Wukong';

Let's have another test,Test tableas follows:

-- Prepare the test form
create table for_delete(id int primary key auto_increment, name varchar(20));
 
 
-- Insert test data
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

-- Query table data
select * from for_delete;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+

Now we can see that the id has been set to increase itself. Three records have been inserted. If you insert a record, the id will be 4.

But now we delete the table without inserting it, as follows:

-- Delete table
delete from for_delete;

mysql> show create table for_delete \G;
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

-- Insert new data
insert into for_delete(name) values('E');

mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
|  4 | E    |
+----+------+

When a new record is inserted, this id is 4, and the self-growth has become the next id value.

• illustratedelete from Clear the tableThe way will not beSelf-value-added setting 0

There is another way to clear the table.Truncate table. In effect anddeleteIt's exactly the same, but there are differences in details and principles.

Truncate table

grammar：

TRUNCATE [TABLE] table_name

Notice: Use this operation with caution

• Only operate on the entire table, not likeDELETEAlso operated for some data;
• In fact, MySQL does not operate on data, soDELETEFaster, butTRUNCATEWhen deleting data, it does not go through real things, so it cannot be rolled back.
• Will resetAUTO_INCREMENTItem

【Case】:

mysql> select * from for_delete; #Before operation+----+------+
| id | name |
+----+------+
|  4 | E    |
+----+------+

-- insert
insert into for_delete(name) values('A'), ('B'), ('C');

-- truncate operate
mysql> truncate for_delete;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into for_delete(name) values('E');
Query OK, 1 row affected (0.01 sec)

mysql> select * from for_delete; #After operation+----+------+
| id | name |
+----+------+
|  1 | E    |
+----+------+

• You can see that the table structure is still there, but the content has been cleared. But it is worth noting here that the self-growth was 5 before, but nowtruncataThe self-growth is gone after clearing the table.
• Then insert a new record and the id becomes 1. Self-growth has become 2
• in other wordstruncateWill reset the self-growth. anddeleteNot.

In fact,TRUNCATEandDELETEThere are still some differences.TRUNCATEThe operation is to directly clear the data in the table, and this operation does not pass the transaction. andDELETEand other SQL operations will be wrapped inTransactions, and then processed by MySQL.

The impact of transactions

TransactionsThe use or not will affect the way MySQL records operation information. MySQL uses its ownLog systemTo record data operations information, mainly including the following typeslog：

• : Stores optimized historical SQL statements.
• : Used to recover data when MySQL encounters a downtime.
• : Used to store rollback segment information.

The role of logs

• The historical SQL statements are recorded and support the master-slave synchronization mechanism of MySQL. When a database has completed certain operations, it can be done throughSynchronize these SQLs to another database to keep the data of the two databases consistent (master-slave synchronization). It should be noted that by default,yesclosureof.
• When MySQL encounters a failureProvide data recovery function

Persistence method

The persistence method refers to a method that can quickly recover database data after a system crash.

There are usually two ways to write data to disk as files

• Record history SQL statement
• Record the data itself

Features of Truncate

becauseTRUNCATEDon't record your own operationslog, and it is not used asTransactionspart, so it simply clears the data in the table, the result of doing so isTRUNCATEThe execution speed is relatively fast.

• TRUNCATEBecause of its non-transactional and non-logging characteristics,Execution speedAdvantages
• But it is not as good as in data recovery and consistencyDELETE

3.3 Insert query results

grammar：

INSERT INTO table_name [(column [, column ...])] SELECT ...

We can insert it whenever we want, delete it whenever we want, modify it whenever we want, and check it when we want. In fact, we can also combine select and insert. You can filter the data from other tables and insert it into another table.

Let's implement the following small experiment:

Delete duplicate records in the table, only one duplicate data can be, now we have a table below:

mysql> select * from duplicate_t;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    1 | a    |
|    2 | b    |
+------+------+

My approach is

• Create an empty table with the same structure as the original tableno_duplicate_t，

• Filter out the results after deduplication from the original table and insert them intono_duplicate_tIsn't this table just non-repeat?

• Then forduplicate_tRename,no_duplicate_tChange the name toduplicate_t. In the end, it's doneduplicate_tGo to the heavy

mysql> create table no_duplicate_t like duplicate_t; # Create the exact same table, use likemysql> insert into no_duplicate_t select distinct * from duplicate_t; # If you insert all columns, you don't need to specify the columns.
-- Rename
mysql> rename table duplicate_t to old_duplicate_t;
mysql> rename table no_duplicate_t to duplicate_t;

mysql> select * from duplicate_t; # Query the final result+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+

❓There is a detailed question here, why did it finally passrenameProcessing in the way?

If you want to upload a file tolinuxOn, for example, the upload time of this file is 1G, it may be 10 minutes. We want to put the file upload number in a directory and require it to be placed in an atomic manner.

• Therefore, generally we cannot upload the file directly to the corresponding directory, because the upload process is definitely not atomic when writing, it is too slow.
• So we upload this file to the temporary target, upload all the files and then upload them.moveGo to that directory.
• directmoveThis action is actually atomic, and it is the same thing to rename a file.

So we finally passedrenameThe method is to simply wait for everything to be ready, then put it in, update, take effect, etc.! Compared to other lengthy moves, this move is very light.

This is the end of this article about CRUD operations of MySQL database tables. For more related contents of CRUD operations of MySQL tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!