Detailed explanation of the examples of adding, deleting, searching and modifying operations of MySQL database table contents

1. Add table data

Syntax: INSERT [INTO] table_name [column , column...] VALUES (value_list) [ ,(value_list)];

mysql&gt; create table students (
    -&gt; id int unsigned primary key auto_increment,
    -&gt; number int not null unique comment 'Student number',
    -&gt; name varchar(20) not null,
    -&gt; telephone char(11) 
    -&gt; );
Query OK, 0 rows affected (0.05 sec)

1. Full column insertion and specified column insertion

When the number of value_list must be the same as the number of fields defined in the table, it will belong to the full column insertion and the part that specifies the inserted element can be omitted. Because we set the self-increment attribute, we don’t need to set the value of the id, but in this case, if it does not belong to the entire column, we must specify which fields are inserted.

//Insert the entire columninsert into students values(1, 202501, 'Zhang San', '15812345678');
//Specify column insertioninsert into students (number, name, telephone) values(202503, 'Wang Wu', '17712345678');

2. Multiple rows of data insertion

When inserting data, multiple pieces of data can also be inserted at the same time. The insertion of multiple rows of data also satisfies the rules of inserting all columns and inserting specified columns.

//Insert all columns and multiple rowsinsert into students values(4, 202504, 'Zhao Liu', '12312345678'), (5, 202505, 'Tian Qi', '12345656789');
//Specify column multiple rows to insertinsert into students (number, name, telephone) values(202506, 'Hello', '12312345678'), (202507, 'Ha ha', '12345656789');

3. Update and replace insertion

Update syntax: ON DUPLICATE KEY UPDATE

Replacement syntax: REPLACE

Because there are constraints for primary and unique keys in tables, if a unique key and primary key conflict occur when we insert, the insertion will fail. So if we want to insert, then we can use update or replacement statements to update the data to our newly inserted, or replace the entire replacement.

mysql&gt; insert into students (id, number, name) values(1, 202501, 'Bei Gu') on duplicate key update number=202510, name = 'Bei Gu';
Query OK, 2 rows affected (0.00 sec)
mysql&gt; select * from students;
+----+--------+--------+-------------+
| id | number | name   | telephone   |
+----+--------+--------+-------------+
|  1 | 202510 | Beigu   | 15812345678 |
|  2 | 202502 | Li Si   | 17712345678 |
|  3 | 202503 | Wang Wu   | 17712345678 |
|  4 | 202504 | Zhao Liu   | 12312345678 |
|  5 | 202505 | Tian Qi   | 12345656789 |
|  6 | 202506 | Hello   | 12312345678 |
|  7 | 202507 | Ha ha   | 12345656789 |
+----+--------+--------+-------------+
7 rows in set (0.00 sec)

The above code is an operation to insert otherwise update. First, insert insert is used to insert data. If the insert fails due to conflicts between the primary or unique keys, you can perform duplicate key update to update the data.

As shown in the figure, you can see that the return value has changed. If the inserted data has conflicts but the conflicting data is the same as the original data, it is equivalent to not doing any operation and returning 0; if there is no data conflict, directly insert and return 1; if there is a data conflict and the data update operation is performed, return 2.

mysql&gt; replace into students (number, name) value(202510, 'hello');
Query OK, 2 rows affected (0.01 sec)
mysql&gt; select * from students;
+----+---------+--------+-------------+
| id | number  | name   | telephone   |
+----+---------+--------+-------------+
|  2 |  202502 | Li Si   | 17712345678 |
|  3 |  202503 | Wang Wu   | 17712345678 |
|  4 |  202504 | Zhao Liu   | 12312345678 |
|  5 |  202505 | Tian Qi   | 12345656789 |
|  6 |  202506 | Hello   | 12312345678 |
|  7 |  202507 | Ha ha   | 12345656789 |
|  8 |  202510 | hello  | NULL        |
+----+---------+--------+-------------+
9 rows in set (0.00 sec)

The above code is to replace the code. His operation is to insert and return 1 if there is no conflict. If there is a large conflict, it will first delete the conflicting data, then reinsert and return 2.

2. View table data

1. Full column query and specified column query

SELECT [column， column] FROM table_name;

When column is not specified and set to *, it is a full column query, but it is generally not recommended to use a full column query, because the more columns are queried, it means that the larger the amount of data to be transmitted, which will affect efficiency. For the specified column query, the field name input does not need to be the same as when defined. The function of the select keyword is equivalent to printing. When we define what we print, it will display what we define, and in what order we define, it will display in what order we define.

2. Query expression fields

The above also says that select is a function of printing and displaying, and column from table_name is the specified printed content. So can the printed content be not in the table? Or can the printed field be an expression? Can fields printed use fields in the table as expressions as parameters?

mysql&gt; create table exam (
    -&gt; id int unsigned primary key auto_increment,
    -&gt; name varchar(20) not null,
    -&gt; chinese float default 0.0,
    -&gt; math float default 0.0,
    -&gt; english float default 0.0
    -&gt; );
Query OK, 0 rows affected (0.05 sec)
mysql&gt; insert into exam (name, chinese, math, english) values 
    -&gt; ('Zhang San', 67, 65, 86),
    -&gt; ('Li Si', 98, 56, 84),
    -&gt; ('Wang Wu', 76, 45, 97),
    -&gt; ('Zhao Liu', 99, 43, 91);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
//Show data not in tablemysql&gt; select 10;
+----+
| 10 |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
//Display expression datamysql&gt; select 10 + 20;
+---------+
| 10 + 20 |
+---------+
|      30 |
+---------+
1 row in set (0.00 sec)
//Display expression data with data in the table as parametersmysql&gt; select id + 10 from exam;
+---------+
| id + 10 |
+---------+
|      11 |
|      12 |
|      13 |
|      14 |
+---------+
4 rows in set (0.00 sec)

3. Alias the query result

Syntax: SELECT column [AS] alias_name [...] FROM table_name;

mysql&gt; select 10 + 20 as 'total';
+--------+
| total   |
+--------+
|     30 |
+--------+
1 row in set (0.00 sec)
mysql&gt; select id, name, chinese + math + english as 'Total Score' from exam;
+----+--------+--------+
| id | name   | Total points   |
+----+--------+--------+
|  1 | Zhang San   |    218 |
|  2 | Li Si   |    238 |
|  3 | Wang Wu   |    218 |
|  4 | Zhao Liu   |    233 |
+----+--------+--------+
4 rows in set (0.00 sec)

4. Result

Syntax: SELECT DISTINCT column FROM table_name;

condition

Comparison operator
Operators	illustrate
>, >=, <, <=	No special meaning, it's just a simple comparison
=	equal,
<=>	equal
!=, <>	Not equal to
BETWEEN x1 AND x2	Do range matching, if a value is between [x1, x2], then return true
IN (option, ...)	If it is one of the options, then return the ture
IS NULL	It's NULL
IS NOT NULL	Not NULL
LIKE	Fuzzy matching. % means any number of arbitrary characters; _ means any one character

Logical operators
Operators	illustrate
AND	Multiple conditions must be met before returning true
OR	Return true if any condition is met
NOT	When the condition is met, false is returned, which is equivalent to matching the content that is not the condition.

BETWEEN x1 AND x2

Here are some use cases for operators:

Basic comparison usage

mysql&gt; select * from exam;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | Zhang San   |      67 |   65 |      86 |
|  2 | Li Si   |      98 |   56 |      84 |
|  3 | Wang Wu   |      76 |   45 |      97 |
|  4 | Zhao Liu   |      99 |   43 |      91 |
+----+--------+---------+------+---------+
4 rows in set (0.00 sec)
mysql&gt; select id, name, math from exam where math &lt; 60;
+----+--------+------+
| id | name   | math |
+----+--------+------+
|  2 | Li Si   |   56 |
|  3 | Wang Wu   |   45 |
|  4 | Zhao Liu   |   43 |
+----+--------+------+
3 rows in set (0.00 sec)

Use of AND and BETWEEN AND

//Check the Chinese scores for students between 80 and 100mysql&gt; select id, name, chinese from exam where chinese &gt;= 80 and chinese &lt;= 100;
+----+--------+---------+
| id | name   | chinese |
+----+--------+---------+
|  2 | Li Si   |      98 |
|  4 | Zhao Liu   |      99 |
+----+--------+---------+
2 rows in set (0.00 sec)
mysql&gt; select id, name, chinese from exam where chinese between 80 and 100;
+----+--------+---------+
| id | name   | chinese |
+----+--------+---------+
|  2 | Li Si   |      98 |
|  4 | Zhao Liu   |      99 |
+----+--------+---------+
2 rows in set (0.00 sec)

OR and IN use

//Check students with English scores of 86 or 97mysql&gt; select id, name, english from exam where english=86 or english=97;
+----+--------+---------+
| id | name   | english |
+----+--------+---------+
|  1 | Zhang San   |      86 |
|  3 | Wang Wu   |      97 |
+----+--------+---------+
2 rows in set (0.00 sec)
mysql&gt; select id, name, english from exam where english in(86, 97);
+----+--------+---------+
| id | name   | english |
+----+--------+---------+
|  1 | Zhang San   |      86 |
|  3 | Wang Wu   |      97 |
+----+--------+---------+
2 rows in set (0.00 sec)

Use of LIKE

//Inquiry of students with surname Zhang and Wangmysql&gt; select id, name from exam where name like 'open%' or name like 'king%';
+----+--------+
| id | name   |
+----+--------+
|  1 | Zhang San   |
|  3 | Wang Wu   |
+----+--------+
2 rows in set (0.00 sec)
mysql&gt; insert into exam values(5, 'Zhang Wenqiang', 98, 90, 79);
Query OK, 1 row affected (0.01 sec)
//Check the name as 2 characters and the classmate who still has sex with Zhangmysql&gt; select id, name from exam where name like 'open_';
+----+--------+
| id | name   |
+----+--------+
|  1 | Zhang San   |
+----+--------+
1 row in set (0.00 sec)

Where and expressions are used in a mixed manner

//Students with total score greater than 230mysql&gt; select id, name, chinese + math + english as 'Total Score' from exam where chinese + math + english &gt; 230;
+----+-----------+--------+
| id | name      | Total points   |
+----+-----------+--------+
|  2 | Li Si      |    238 |
|  4 | Zhao Liu      |    233 |
|  5 | Tian Qi      |    231 |
|  6 | Zhang Wenqiang    |    267 |
+----+-----------+--------+
4 rows in set (0.00 sec)

AND and NOT

//The Chinese score is greater than 90, not a classmate who is not named Limysql&gt; select name, chinese from exam where chinese &gt; 90 and name not like 'plum_';
+-----------+---------+
| name      | chinese |
+-----------+---------+
| Zhao Liu      |      99 |
| Tian Qi      |      92 |
| Zhang Wenqiang    |      98 |
+-----------+---------+
3 rows in set (0.00 sec)

The difference between = and <=>

Both are to determine whether the two values are equal, but the first one is non-safe. If you compare NULL with any value, NULL will be returned, because NULL represents an unknown value in MySQL, so the result is also unknown when comparing. <=> can handle comparison of NULL values, and NULL will be treated as a value. If it is all NULL, it will return 1, not 0.

6. Result sorting

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

Use case: select * from tset_table order by xxxx;

The operation is to display the selected display data in ascending or descending order according to the value of the column data. ASC is the default value, indicating ascending order, and DESC represents descending order. For NULL, it is considered a value smaller than any data. You can also define multi-segment sorting rules. If the two values are equal, the first sorting rule cannot be sorted and will continue to sort according to the second sorting rule.

mysql&gt; insert into exam values(7, 'Ha ha', 98, 77, 79);
Query OK, 1 row affected (0.01 sec)
mysql&gt; insert into exam values(8, 'Wang Qiang', NULL, 79, 59);
Query OK, 1 row affected (0.01 sec)
//Chinese language is arranged in descending order, mathematics is arranged in ascending ordermysql&gt; select * from exam order by chinese desc, math;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | Zhao Liu      |      99 |   43 |      91 |
|  2 | Li Si      |      98 |   56 |      84 |
|  7 | Ha ha      |      98 |   77 |      79 |
|  6 | Zhang Wenqiang    |      98 |   90 |      79 |
|  5 | Tian Qi      |      92 |   60 |      79 |
|  3 | Wang Wu      |      76 |   45 |      97 |
|  1 | Zhang San      |      67 |   65 |      86 |
|  8 | Wang Qiang      |    NULL |   79 |      59 |
+----+-----------+---------+------+---------+
8 rows in set (0.00 sec)
//Sort the total score, the column name can be used in order bymysql&gt; select id, name, chinese + math + english as Total points from exam order by Total points desc;
+----+-----------+--------+
| id | name      | Total points   |
+----+-----------+--------+
|  6 | Zhang Wenqiang    |    267 |
|  7 | Ha ha      |    254 |
|  2 | Li Si      |    238 |
|  4 | Zhao Liu      |    233 |
|  5 | Tian Qi      |    231 |
|  1 | Zhang San      |    218 |
|  3 | Wang Wu      |    218 |
|  8 | Wang Qiang      |   NULL |
+----+-----------+--------+
8 rows in set (0.00 sec)

7. Filter the page results

//Filter n results from 0

SELECT ... FROM table_name [...] LIMIT n;

//Filter n results from s

SELECT ... FROM table_name [...] LIMIT s, n;

SELECT ... FROM table_name [...] LIMIT n OFFSET S;

When querying and displaying unknown tables, it is best to add LIMIT to avoid excessive amount of large data in the table and querying the entire table will cause the database to be stuck. If the query data is not enough n, it will not have any impact.

mysql&gt; select * from exam limit 3;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | Zhang San   |      67 |   65 |      86 |
|  2 | Li Si   |      98 |   56 |      84 |
|  3 | Wang Wu   |      76 |   45 |      97 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)
mysql&gt; select * from exam limit 3, 3;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  4 | Zhao Liu      |      99 |   43 |      91 |
|  5 | Tian Qi      |      92 |   60 |      79 |
|  6 | Zhang Wenqiang    |      98 |   90 |      79 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql&gt; select * from exam limit 6, 3;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  7 | Ha ha   |      98 |   77 |      79 |
|  8 | Wang Qiang   |    NULL |   79 |      59 |
+----+--------+---------+------+---------+
2 rows in set (0.00 sec)
mysql&gt;

8. Insert the result of the query

mysql&gt; create table exam_zhang (
    -&gt; id int unsigned primary key auto_increment,
    -&gt; name varchar(20) not null,
    -&gt; chinese float default 0.0,
    -&gt; math float default 0.0,
    -&gt; english float default 0.0
    -&gt; );
Query OK, 0 rows affected (0.05 sec)
mysql&gt; insert into exam_zhang select distinct * from exam where name like 'open%';
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql&gt; select * from exam_zhang;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | Zhang San      |     100 |  130 |     116 |
|  6 | Zhang Wenqiang    |      98 |  120 |     109 |
+----+-----------+---------+------+---------+
2 rows in set (0.01 sec)

by clause

Use this clause to query the specified columns in grouping. When using grouping queries, the columns displayed in select must appear in the group by clause, or be an aggregate function.

mysql&gt; create table student (
    -&gt; class_id int not null,
    -&gt; name varchar(10) not null,
    -&gt; score float default 0.0
    -&gt; );
Query OK, 0 rows affected (0.03 sec)
mysql&gt; insert into student values  
    -&gt; (1, 'Zhang San', 98.7),
    -&gt; (1, 'Li Si', 97.2),
    -&gt; (1, 'Wang Wu', 88.6),
    -&gt; (2, 'Zhao Liu', 79.4),
    -&gt; (2, 'Tian Qi', 99.9),
    -&gt; (2, 'Wang Qiang', 50.4);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
// Check the highest scores for each classmysql&gt; select class_id, max(score) from student group by class_id;
+----------+------------+
| class_id | max(score) |
+----------+------------+
|        1 |       98.7 |
|        2 |       99.9 |
+----------+------------+
2 rows in set (0.00 sec)
//It must be an aggregate function, or a column that appears in group bymysql&gt; select class_id, name, max(score) from student group by class_id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column '' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

3. Modify table data

Syntax: UPDATE table_name SET column=xx [, column=xx, ...] [WHERE ...];

// Provide 30 points in English scores with the highest total scoremysql&gt; select name, english, math+chinese+english as Total points from exam order by Total points desc limit 3;
+-----------+---------+--------+
| name      | english | Total points   |
+-----------+---------+--------+
| Zhang San      |      86 |    286 |
| Zhang Wenqiang    |      79 |    267 |
| Ha ha      |      79 |    254 |
+-----------+---------+--------+
3 rows in set (0.00 sec)
mysql&gt; update exam set english = english + 30 order by math+english+chinese desc limit 3;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql&gt; select name, english, math+chinese+english as Total points from exam order by Total points desc limit 3;
+-----------+---------+--------+
| name      | english | Total points   |
+-----------+---------+--------+
| Zhang San      |     116 |    316 |
| Zhang Wenqiang    |     109 |    297 |
| Ha ha      |     109 |    284 |
+-----------+---------+-------
//Check changes - This is not recommendedmysql&gt; update exam set math = math + 30;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8  Changed: 8  Warnings: 0

4. Delete and truncate table data

Delete syntax: DELETE FROM table_name [....];

If no range selection conditions are added, it is equivalent to deleting the entire table data.

Truncated syntax: TRUNCATE [TABLE] table_name;

For deletion, it is to delete a single or multiple data in the form, while truncation is to operate on the entire table, which will clear the entire table data. In this way, the operation of clearing the table by freeing the storage space of the table can be realized. The DELETE statement needs to delete records row by row, and the deletion operation of each row will be recorded in the log. So it will be much faster than DELETE.

If there are self-increment columns in the table, the operation resets the value of the self-increment column to the initial value (usually 1). The DELETE statement will not reset the value of the self-increment column. There are also the characteristics of not rolling back and not punishing triggers, which will be discussed later.

This is the article about the addition, deletion, search and modification operations of MySQL database table content. For more related contents of mysql database table, please search for my previous articles or continue to browse the related articles below. I hope everyone will support me in the future!