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 TABLE money
(
id
INT NOT NULL AUTO_INCREMENT ,
username
VARCHAR(50) NOT NULL ,
balance
FLOAT NOT NULL ,
province
VARCHAR(20) NOT NULL ,
age
TINYINT UNSIGNED NOT NULL ,
sex
TINYINT 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 Wang Baoqiang | 120.02 | 上海 | 29 | 1 | |
2Fan Bingbing | 260.23 | Shandong | 40 | 0 | |
3黄晓明 | 150.86 | Shandong | 40 | 1 | | ##4
Jing Boran | 810 | Liaoning | 27 | 1 | ##5 |
Li Bingbing20.15 | Heilongjiang | 43 | 0 | | ##6 | Jackie Chan
313 | Shandong | 63 | 1 | | 7 | Yang Mi
123 | 北京 | 30 | 0 | ##8 | Liu Shishi |
456 Beijing | 29 | 1 | | ##9 | Liu Yan | 23.4
Hunan | 36 | 0 | ##10 | Zhao Benshan | 3456 |
Liaoning 63 | 1 | | ##11 | 王峰 | 34.32 | Beijing
44 | 1 | ##12 | Guo Degang | 212 | 天津 |
43 1 | | | Note: balance refers to the balance | province refers to the province | Basic query |
Category
Detailed explanation
##Basic syntaxselect * from Table; | | Example
select * from money; | Example description | Query the money table All results in all fields
| Note: "*" is a regular expression, which means matching everything. The above query statement is equivalent to the following:
mysql> select * from money;
+- ---+-----------+----------+-----------+-----+-----+
| 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 |
| 6 | Jackie Chan | 313 | Shandong | 63 | 1 |
| 7 | Yang Mi | 123 | Beijing | 30 | 0 |
| 8 | Liu Shishi | 456 | Beijing | 29 | 1 |
| 9 | Liu Yan | 23.4 | Hunan | 36 | 0 |
| 10 | Zhao Benshan | 3456 | Liaoning | 63 | 1 |
| 11 | Wang Feng | 34.32 | Beijing | 44 | 1 |
| 12 | Guo Degang | 212 | Tianjin | 43 | 1 |
+----+-----------+----------+------ -----+-----+-----+
12 rows in set (0.00 sec)
Specify field query
Category | Detailed explanation |
##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 |
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
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 whereCategory | 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 |
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 >, <, >=, <=, !=;
## Symbol | Description |
##> is greater than | |
<Less than | |
>=Greater than or equal to | |
<= Less than or equal to | | ##!=
is not equal to | | =
is equal to | |
Logical operators
You can also use or, and and other logical operators to perform multi-condition joint queries for multiple conditions
SymbolDescription | |
oror | | and
and | |
Let’s look at an example of multiple conditions:
TypeDetails | |
Exampleselect * 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
CategoryDetailed explanation | |
Basic syntaxselect 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 used in sorting:
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 sortingorder 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. 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 |
* 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 limit quantity; |
Example | select id,username, balance from money limit 5; |
## Example description | Display the first five users |
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
Category | Detailed explanation |
---|
##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;
+----+-----------+---------+| 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.
CategoryDetailed explanation | |
Basic syntaxselect field from table limit offset, quantity | |
Exampleselect id,username, balance from money limit 0,3; | |
Example descriptionGet three records starting from the first one | |
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:
Function | Description |
---|
sum | Sum |
count | Total statistics |
max | Maximum value |
min | Minimum value |
##avg | Average |
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.
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 |
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 * 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.
Category | Detailed explanation |
---|
##Basic syntax | select * from table group by field with rollup |
##Exampleselect count(province),province from money group by province with rollup; | |
Example descriptionCount the number of groups again | |
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.
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.
CategoryDetailed explanation | |
Basic syntaxselect * from table group by field having conditions | |
Exampleselect count(province) as result,province from money group by province having result >2; | |
Example descriptionGroup regions and count the total, and display the grouped regions greater than 2 in the grouping results | |
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:
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)
Next Section<?php
echo "Hello Mysql";
?>
- Chapter1Why choose this course to learn PHP
- Why learn PHP?
- What is PHP
- You can learn even with z...
- Why can't some people lea...
- Chapter2PHP environment installation
- What is the development e...
- Windows environment insta...
- Linux environment install...
- Other development environ...
- Tool selection for writin...
- Chapter3php basic syntax
- PHP basic syntax
- Our first piece of PHP co...
- Variables in php - you wi...
- echo display command
- Learning php annotations
- Data types are not myster...
- PHP integer type is an in...
- PHP data type Boolean (ac...
- PHP data type string
- PHP data type floating po...
- PHP flow control if else ...
- PHP data type NULL type
- php data type array
- Resource type of php data...
- PHP data type viewing and...
- Automatic conversion and ...
- Object (will learn later)
- PHP constants and variabl...
- PHP constants and variabl...
- PHP constants and variabl...
- PHP constants and variabl...
- Variable references for P...
- PHP basic syntax arithmet...
- PHP basic syntax assignme...
- PHP basic syntax: self-in...
- PHP basic syntax comparis...
- Logical operations of php...
- PHP basic syntax bit oper...
- PHP basic syntax: ternary...
- Chapter4PHP process control
- Process control in PHP
- PHP process control if co...
- PHP flow control if state...
- Nested if...else...elseif...
- Multiple nesting of if st...
- Use of branch structure s...
- Use of loop statements in...
- while loop
- The difference between do...
- PHP flow control for loop...
- PHP flow control goto syn...
- Chapter5Basic function syntax of PHP
- Basic function syntax of ...
- PHP function basic syntax...
- PHP custom function callb...
- PHP custom function varia...
- PHP custom function anony...
- Internal function of php ...
- Variable scope of php cus...
- Reference to parameters o...
- PHP custom function recur...
- Static variables of php c...
- php uses system built-in ...
- php file contains functio...
- PHP math commonly used fu...
- PHP function to obtain pe...
- php date validation funct...
- PHP gets localized timest...
- PHP program execution tim...
- PHP string common functio...
- Chapter6PHP arrays and data structures
- PHP arrays and data struc...
- php array definition
- PHP array calculation
- php for loop traverses in...
- php foreach traverses as...
- PHP list, each function t...
- PHP commonly used array m...
- Common functions for php ...
- Chapter7Regular expressions in PHP
- Regular expressions in PH...
- Delimiter expressed by ph...
- Atoms in php regular expr...
- Metacharacters in php reg...
- Pattern modifiers in php ...
- Tips and commonly used re...
- PHP uses regular expressi...
- Chapter8php file system
- File system
- php read file
- php creates and modifies ...
- php creates temporary fil...
- php move, copy and delete...
- php detect file attribute...
- Common functions and cons...
- php file locking mechanis...
- php directory processing ...
- php file permission setti...
- php file path function
- PHP implements file guest...
- PHP implementation exampl...
- Chapter9PHP file upload
- PHP file upload
- When uploading files, you...
- Steps to upload php files
- Precautions for php file ...
- php completes file upload...
- php multiple file upload
- PHP file upload progress ...
- Chapter10PHP image processing
- PHP image processing
- PHP image processing gd2 ...
- PHP uses image processing...
- PHP development verificat...
- php image scaling and cro...
- PHP image watermark proce...
- Chapter11PHP error handling
- Error handling
- PHP error handling prohib...
- PHP error handling error ...
- PHP error handling error ...
- PHP error handling custom...
- Chapter12Getting started with MySQL
- Getting Started with MySQ...
- Mysql database introducti...
- Mysql entertainment expla...
- mysql database installati...
- Data statement operation ...
- Mysql connect to database
- Mysql database operation
- Mysql data table operatio...
- Mysql data field operatio...
- Mysql data type
- Mysql character set
- Mysql table engine
- Mysql index
- Mysql add, delete, modify...
- Mysql add, delete, modify...
- Mysql multi-table joint q...
- Mysql addition, deletion,...
- Mysql add, delete, modify...
- DCL statement
- Learn commonly used Engli...
- Chapter13PHP operates mysql database
- PHP operates mysql databa...
- PHP database connection s...
- PHP operates the database...
- PHP database operation: m...
- PHP database operation: p...
- PHP database operation: b...
- PHP database operation to...
- The ultimate solution to ...
- Chapter14php session management and control
- session overview
- Overview of Cookies for P...
- php session control Cooki...
- PHP session control using...
- php SESSION application e...
- Session management and co...
- Chapter15Making a thief program through cURL
- php curl usage methods an...
- php curl custom get metho...
- php curl uses post to sen...
- Making a thief program th...
- Chapter16Learn commonly used English words in PHP
- List of commonly used Eng...