Mysql add, delete, modify query records

Before explaining the query, I prepared a data table for everyone. This table stores the bank's balance and basic information about the user.

We defined a table structure named money.

The statement to create the table is as follows:

CREATE TABLEmoney(
idINT NOT NULL AUTO_INCREMENT ,
usernameVARCHAR(50) NOT NULL ,
balanceFLOAT NOT NULL ,
provinceVARCHAR(20) NOT NULL ,
ageTINYINT UNSIGNED NOT NULL ,
sexTINYINT NOT NULL ,
PRIMARY KEY (id(10))
) ENGINE = InnoDB CHARACTER SET utf8;

The table structure and data are displayed as follows:

##id username balance province age sex ##1 2 3 ##4 Jing Boran 810 Liaoning 27 1 Li Bingbing ##6 Jackie Chan 313 Shandong 63 1 7 Yang Mi 123 北京 30 0 456 ##9 Liu Yan 23.4 Hunan 36 0 Liaoning ##11 王峰 34.32 Beijing 44 1 43 Note: province refers to the province Basic query


Wang Baoqiang	120.02	上海	29	1
Fan Bingbing	260.23	Shandong	40	0
黄晓明	150.86	Shandong	40	1
					##5
20.15	Heilongjiang	43	0

				##8	Liu Shishi
Beijing	29	1
			##10	Zhao Benshan	3456
63	1
		##12	Guo Degang	212	天津
1			balance refers to the balance

Specify field query

##Basic syntax select * from Table; Example select * from money; Example description Query the money table All results in all fields

mysql> select id,username, balance from money;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 1 | Wang Baoqiang | 120.02 |
| 2 | Fan Bingbing | 260.23 |
| 3 | Huang Xiaoming | 150.86 |
| 4 | Jing Boran | 810 |
| 5 | Li Bingbing | 20.15 |
| 6 | Jackie Chan | 313 |
| 7 | Yang Mi | 123 |
| 8 | Liu Shishi | 456 |
| 9 | Liu Yan | 23.4 |
| 10 | Zhao Benshan | 3456 |
| 11 | Wang Feng | 34.3 2 |
| 12 | Guo Degang | 212 |
+----+-----------+---------+
12 rows in set (0.00 sec)

Query a single field for non-duplicate records distinct

##Basic syntax select field from table; Example select id,username, balance from money; Example description Query id,username, in the money table All results in the balance field

Category	Detailed explanation

Category	Detailed explanation
Basic syntax	select distinct field from table;
Example	select distinct age deptno from money;
Example description	Query all results with unique age in the money table

##mysql> select distinct age deptno from money;

+--------+
| deptno |
+--------+
| 29 |
| 40 |
| 27 |
| 43 |
| 63 |
| 30 |
| 36 |
| 44 |
+--------+
8 rows in set (0.00 sec )

Conditional query where

mysql> select * from money where age = 29;
+----+-----------+---------+----- -----+-----+-----+
| id | username | balance | province | age | sex |
+----+------- ----+---------+----------+-----+-----+
| 1 | Li Wenkai | 120.02 | Hubei | 29 | 1 |
| 8 | Liu Shishi | 456 | Beijing | 29 | 1 |
+----+----------+---------- +----------+-----+-----+
2 rows in set (0.00 sec)

Conditions that can be connected after where

Comparison operatorThe records that meet the conditions are listed in the result set. In the above example, the field after where is the ‘=’ of a field.

In addition, you can also use comparison operators such as >, <, >=, <=, !=;

Category Detailed explanation Basic syntax select field from table where where condition; Example select * from money where age = 29; Example description Query all results with age 29 in the money table

## Symbol Description ##> < >= <= ##!= is not equal to = is equal to Logical operators


is greater than
Less than
Greater than or equal to
Less than or equal to

You can also use or, and and other logical operators to perform multi-condition joint queries for multiple conditions

Symbol Description or or and and Let’s look at an example of multiple conditions:

Type Details Example select * from money where id <10 and Instructions Querying all fields requires that the id is less than 10 and province='Hubei' mysql> select * from money where id <10 and province='Hubei';

+----+-----------+---------+----------+-----+----- +

| id | username | balance | province | age | sex |
+----+-----------+---------+----------+-----+----- +
| 1 | Wang Baoqiang | 120.02 | Hubei | 29 | 1 |
+----+-----------+---------+----------+-----+----- +
1 row in set (0.00 sec)

Result set sorting

Keywords used in sorting:

Category Detailed explanation Basic syntax select field from table order by field sort keyword Example select id, username, balance from money order by balance desc; Example description Query the id, username, balance fields in the money table and sort them in descending order according to the balance

Keywords	Description
asc	Arrange in ascending order, from small to large (default)
desc	Arrange in descending order, from large to small

Use order by to sort the result set after the select comes out, where desc and asc are keywords in the sort order. desc means to sort by fields in descending order, and asc means to sort in ascending order. If no keyword is written, the default is to sort in ascending order.

mysql> select id,username, balance from money order by balance desc;
+----+-----------+-------- -+
| id | username | balance |
+----+-----------+---------+
| 10 | Zhao Benshan | 3456 |
| 4 | Jing Bairan | 810 |
| 8 | Liu Shishi | 456 |
| 6 | Jackie Chan | 313 |
| 2 | Fan Bingbing | 260.23 |
| 12 | Guo Degang | 212 |
| 3 | Huang Xiaoming | 150.86 |
| 7 | Yang Mi | 123 |
| 1 | Wang Baoqiang | 120.02 |
| 11 | Wang Feng | Liu Yan | 23.4 |
| 5 | Li Bingbing | 20.15 |
+----+-----------+---------+
12 rows in set (0.00 sec)

Multi-field sorting

order by can be followed by multiple different field sorting, and the order of different result sets of the sorting field is also different, if the values of the sorting fields are the same , then fields with the same value are sorted according to the second sorting field.

* Note: If the first field has already arranged the results. The second field sort field does not take effect. In this case, the second field is invalid.*

mysql> select id,username, balance from money order by balance desc,age asc;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 10 | Zhao Benshan | 3456 |
| 4 | Jing Boran | 810 |
| 8 | Liu Shishi | 456 |
| 6 | Jackie Chan | 313 |
| 2 | Fan Bingbing | 260.23 |
| 12 | Guo Degang | 212 |
| 3 | Huang Xiaoming | 150.86 |
| 7 | Yang Mi | 123 |
| 1 | Wang Baoqiang | 120.02 |
| 11 | Wang Feng | 34.32 |
| 9 | Liu Yan | 23.4 |
| 5 | Li Bingbing | 20.15 |
+----+-----------+---------+
12 rows in set (0.00 sec)

Result set limit

For queries or sorted result sets, if you want to display only part instead of all, use the limit keyword result set Quantitative restrictions.

Category Detailed explanation Basic syntax select field from table order by field 1 sort keyword,... ...Field n desc|asc; Example select id,username, balance from money order by balance desc,age asc; Example description Query the id, username, and balance fields in the money table, and sort them in descending order according to the balance. If the balance If they are all the same, then use age to sort in ascending order

mysql> select * from money limit 5;
+----+----------------+---------+------- ----+-----+-----+
| id | username | balance | province | age | sex |
+----+-------- ---+---------+-----------+-----+-----+
| 1 | Wang Baoqiang | 120.02 | Hubei | 29 | 1 |
| 2 | Fan Bingbing | 260.23 | Shandong | 40 | 0 |
| 3 | Huang Xiaoming | 150.86 | Shandong | 40 | 1 |
| 4 | Jing Boran | 810 | Liaoning | 27 | 1 |
| 5 | Li Bingbing | 20.15 | Heilongjiang | 43 | 0 |
+----+----------+----------+- ----------+-----+-----+
5 rows in set (0.00 sec)

Limit and sort the result set

## Example description Display the first five users

Category	Detailed explanation
Basic syntax	select field from table limit quantity;
Example	select id,username, balance from money limit 5;

##Basic syntax select field from table order by field keyword limit quantity Example select id,username, balance from money order by balance desc limit 5; Example description Sort by money, display the top five richest users ##mysql> select id,username, balance from money order by balance desc limit 5;

Category	Detailed explanation

+----+-----------+---------+

| id | username | balance |
+----+-----------+---------+
| 10 | Zhao Benshan | 3456 |
| 4 | Jing Boran | 810 |
| 8 | Liu Shishi | 456 |
| 6 | Jackie Chan | 313 |
| 2 | Fan Bingbing | 260.23 |
+----+----------+ ---------+
5 rows in set (0.00 sec)

Result set interval selection

Suppose I fetch 3 records starting from 0. I want to fetch 3 more records starting from the 3rd one. What should I do if I want to fetch 4 records starting from the 6th one?

At this time, you need to use the result set interval selection.

Note: The first record is 0.

mysql> select id,username, balance from money limit 0,3;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 1 | Wang Baoqiang | 120.02 |
| 2 | Fan Bingbing | 260.23 |
| 3 | Huang Xiaoming | 150.86 |
+----+-----------+---------+
3 rows in set (0.00 sec)

How about taking three more rows starting from the third row?

mysql> select id,username, balance from money limit 3,3;
+----+-----------+---------+
| id | username | balance |
+----+-----------+---------+
| 4 | Jing Boran | 810 |
| 5 | Li Bingbing | 20.15 |
| 6 | Jackie Chan | 313 |
+----+-----------+---------+
3 rows in set (0.00 sec)

Through the above idea, the display completes paging.
Each page displays 10 records, then:

The first page is limit 0,10
The second page is limit 10,10
The third page is limit 20,10

And so on... ...

Use of statistical functions

What if we want to know the total number of users?
How to query who is the richest person in the data table?
What if we want to know the average amount of money for a user?
What if we want to know the total amount for all users?

We have four most commonly used statistical functions:

Category Basic syntax Example Example description

Detailed explanation
select field from table limit offset, quantity
select id,username, balance from money limit 0,3;
Get three records starting from the first one

##avg Average

Function	Description
sum	Sum
count	Total statistics
max	Maximum value
min	Minimum value

Note: Of course you know that other mysql functions can also be used. However, in actual work, it is rarely used in many large and medium-sized projects in large companies, and they all have dedicated counting servers. Because the calculation amount of MySQL itself is very large, in order to reduce the pressure, we usually leave the actual calculation tasks to the business server or other servers to complete.

mysql> select count(id) from money;
+-----------+
| count(id) |
+-----------+
| 12 |
+-----------+
1 row in set (0.00 sec)

You can also give the field an alias! Use the as keyword.

mysql> select count(id) as zongshu from money;
+---------+
| zongshu |
+---------+
| 12 |
+---------+
1 row in set (0.00 sec)

Query average amount

mysql> select avg(balance) from money;
+--------------------+
| avg(balance) |
+--------------------+
| 498.24833393096924 |
+--------------------+
1 row in set (0.00 sec)

Query total amount

mysql> select sum(balance) from money;
+-------------------+
| sum(balance) |
+-------------------+
| 5978.980007171631 |
+-------------------+
1 row in set (0.00 sec)

Query the maximum amount

mysql> select max(balance) from money;
+-------------+
| max(balance) |
+-------------+
| 3456 |
+-------------+
1 row in set (0.00 sec)

Query the minimum amount

mysql> select min(balance) from money;
+--------------------+
| min(balance) |
+--------------------+
| 20.149999618530273 |
+--------------------+
1 row in set (0.00 sec)

Group group by

We use the provinces in the amount table to group the data. You will find after grouping the data. The same provinces will be removed. That is, a province is a group.

Category Detailed explanation Basic syntax select function (field) from table Example select count(id) from money Example Description Query the total number of ids in the money table

Category	Detailed explanation
Basic syntax	select * from table group by field
Example	select * from money group by province;
Example description	Group by region

mysql> select * from money group by province;
+----+-----------+---------+------ -----+-----+-----+
| id | username | balance | province | age | sex |
+----+------- ----+---------+-----------+-----+-----+
| 7 | Yang Mi | 123 | Beijing | 30 | 0 |
| 12 | Guo Degang | 212 | Tianjin | 43 | 1 |
| 2 | Fan Bingbing | 260.23 | Shandong | 40 | 0 |
| 1 | Wang Baoqiang | 120.02 | Hubei | 29 | 1 |
| 9 | Liu Yan | 23.4 | Hunan | 36 | 0 |
| 4 | Jing Boran | 810 | Liaoning | 27 | 1 |
| 5 | Li Bingbing | 20.15 | Black Dragon Jiang | 43 | 0 |
+----+-----------+----------+-----------+-----+ -----+

Statistical grouping (category) total number:

mysql> select deptno, count(1) from emp group by deptno;
+----- ---+----------+
| deptno | count(1) |
+--------+----------+
| 1 | 1 1 |
| 2 | 5 |
| 3 | 1 1 |
| 5 | 4 |
+--------+----- -----+
4 rows in set (0.04 sec)

Count the number of provinces and then display them in groups

mysql> select count(province),province from money group by province;
+------------------+----------+
| count(province) | province |
+------------------+----------+
| 3 | Beijing |
| | | Tianjin |
| 3 | Shandong |
| 1 1 Hubei |
| 1 1 | Hunan |
| 2 | Liaoning |
| Heilongjiang |
+------------------+----------+
7 rows in set (0.00 sec)

Statistics based on grouping

with rollup is rarely used. This knowledge point is set to the understanding level.

Its main function is to count the grouped data and then perform a total count.

##Basic syntax select * from table group by field with rollup ##Example Example description On the basis of the above, count the total number. In the result of the following example, there is one more at the end. 12 NULL.

Category	Detailed explanation

select count(province),province from money group by province with rollup;
Count the number of groups again

mysql> select count(province),province from money group by province with rollup;

+------------------+----------+
| count(province) | province |
+------------------+----------+
| 3 | Beijing |
| | | Tianjin |
| 3 | Shandong |
| 1 1 Hubei |
| 1 1 | Hunan |
| 2 | Liaoning |
| Heilongjiang |
| 12 | NULL |
+------------------+----------+
8 rows in set (0.00 sec)

The results are then filtered having

The having clause is similar to where but also different. They are both statements that set conditions.

having is the filtering group and where is the filtering record.

mysql> select count(province) as result ,province from money group by having province result >2;
+--------+----------+
| result | province |
+--------+----------+
| 3 | Beijing |
| 3 | Shandong |
+--------+----------+
2 rows in set (0.00 sec)

Use SQL as a whole

We have only used certain statements in the above statements, and have not used them as a whole.

We will now integrate the statements and use them together once. The syntax structure used with the overall SQL statement is as follows:

SELECT
[Field 1 [as alias 1], [Function (Field 2),]...Field n]
FROM table name
[WHERE where condition]
[GROUP BY field]
[HAVING where_continition]
[order condition]
[limit condition]

Note: [] can be used to represent optional in the above statement.

The final syntax summary is as follows:

Category Basic syntax Example Example description

Detailed explanation
select * from table group by field having conditions
select count(province) as result,province from money group by province having result >2;
Group regions and count the total, and display the grouped regions greater than 2 in the grouping results

Keywords	Description
select	Selected columns
from	Table
where	Query conditions
group by	Group attribute having group filter conditions
order by	Sort attribute
limit	Starting record position, take the number of records

us Perform an overall use and query the money table fields: id, username, balance, province. It is required that id>1 and the balance be greater than 50. Use regions for grouping. We use the user ID to perform descending order, and only 3 items are allowed to be displayed.

Finally write the SQL statement as follows, and the query results are as follows:

mysql> select id,username,balance,province from money where id > 1 and balance > 50 group by province order by id desc limit 3;
+----+-----------+---------+----------+
| id | username | balance | province |
+----+-----------+---------+----------+
| 12 | Guo Degang | 212 | Tianjin |
| 7 | Yang Mi | 123 | Beijing |
| 4 | Jing Boran | 810 | Liaoning |
+----+-----------+---------+----------+
3 rows in set (0.00 sec)

Continuing Learning