query

1. Query all data
   select * from emp;

2. Query the specified data

select column / function

select column_name1, column_name2, ... from table_name;

select mgr from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7698 |
| 7839 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
| 7566 |
+------+

1. Query to remove duplicate records

The default search will also find out the duplicates

select distinct column_name1, column_name2 from table_name;

distinct: Keywords used when removing duplicate records in query results, distinct Works on a single field.
select distinct mgr from emp;
+------+
| mgr  |
+------+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| NULL |
+------+

1. Queries using arithmetic operators

Operators such as + - * /% can be used

Given the monthly salary, ask for the annual salary
select sal*12 from emp;
The name after the column represents the alias of the
+----------+
| sal*12   |
+----------+
|  9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 36000.00 |
| 60000.00 |
| 36000.00 |
+----------+

1. Queries using field aliases

select column_name1 [as] othername1 from table_name;

[as] the representation enclosed in brackets is optional

select sal*12 from emp;
+----------+
| sal*12   |
+----------+
|  9600.00 |
| 19200.00 |
| 15000.00 |
| 35700.00 |
| 15000.00 |
| 34200.00 |
| 36000.00 |
| 60000.00 |
| 36000.00 |
+----------+

Note: if the field name contains spaces and some special characters, it needs to be enclosed in single quotes.
select sal*12 'yearsal&' from emp;

1. Query for setting data display format

In order to browse the information in the table conveniently, you can set the format of data display. You can use the concat() function.

Note: there are two ways to connect strings in Mysql: + and concat() functions

select concat('full name:',ename,'，Department:',depto) info from emp;
+-------------------------------+
| info                          |
+-------------------------------+
| full name: Smith，Department: 20         |
| full name: Allen，Department: 20         |
| full name: Ward，Department: 30          |
| full name: Jones，Department: 20         |
| full name: Maritn，Department: 30        |
| full name: Blake，Department: 30         |
| full name: Scott，Department: 10         |
| full name: King，Department: 10          |
| full name: Ford，Department: 20          |
+-------------------------------+
In this way, you can view the data intuitively and conveniently.

1. Sort query data

7.1 sorting by specified single field

select column_name1, column_name2 from table_name order by order_name [asc|desc];

asc stands for ascending order, desc stands for descending order, and the default is ascending order.

Ascending order
select ename, sal from emp order by sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| Smith  |  800.00 |
| Ward   | 1250.00 |
| Maritn | 1250.00 |
| Allen  | 1600.00 |
| Blake  | 2850.00 |
| Jones  | 2975.00 |
| Scott  | 3000.00 |
| Ford   | 3000.00 |
| King   | 5000.00 |
+--------+---------+

Descending order
select ename, sal from emp order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| King   | 5000.00 |
| Scott  | 3000.00 |
| Ford   | 3000.00 |
| Jones  | 2975.00 |
| Blake  | 2850.00 |
| Allen  | 1600.00 |
| Ward   | 1250.00 |
| Maritn | 1250.00 |
| Smith  |  800.00 |
+--------+---------+

Note: if the value of the specified sort field is Null, the value will be treated as the minimum value

7.2 sorting by specified multi field

select column_name1, column_name2 from table_name order by order_name1 [asc|desc], order_name2 [asc|desc];

select ename, hiredate, sal from emp order by sal,hiredate;
+--------+------------+---------+
| ename  | hiredate   | sal     |
+--------+------------+---------+
| Smith  | 1980-12-17 |  800.00 |
| Maritn | 1981-06-22 | 1250.00 |
| Ward   | 1981-02-22 | 1250.00 |
| Allen  | 1981-02-20 | 1600.00 |
| Blake  | 1981-09-22 | 2850.00 |
| Jones  | 1981-03-22 | 2975.00 |
| Ford   | 1981-03-22 | 3000.00 |
| Scott  | 1981-01-22 | 3000.00 |
| King   | 1981-04-22 | 5000.00 |
+--------+------------+---------+
here sal In ascending order, and hiredate It's in descending order.

1. Condition query

The previous are all the fields in the query table, but in our actual development process, we often query according to a certain condition to achieve a filtering effect.

select column_name1, column_name2 from table_name where where_condition;

where clause is used here to specify query conditions

There are many ways to implement conditional query. You can use comparison operators, between and, in, is null, like, and, or, etc. in the where clause to specify query conditions.

8.1 queries using comparison operators

Usually use >, <, > =, < =, =, < > is not equal to= Not equal to

1. query emp No. in table deptno Information for all employees with a value of 30 in the field.
select * from emp where deptno=30;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7521 | Ward   | salesman | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 |     30 |
|  7698 | Blake  | manager  | 7839 | 1981-09-22 | 2850.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+


2. use<=

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | Smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | Allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     20 |
|  7521 | Ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | Jones  | manager   | 7839 | 1981-03-22 | 2975.00 |    NULL |     20 |
|  7654 | Maritn | salesman  | 7698 | 1981-06-22 | 1250.00 | 1400.00 |     30 |
|  7698 | Blake  | manager   | 7839 | 1981-09-22 | 2850.00 |    NULL |     30 |
|  7782 | Scott  | analyst   | 7566 | 1981-01-22 | 3000.00 |    NULL |     10 |
|  7839 | King   | president | NULL | 1981-04-22 | 5000.00 |    NULL |     10 |
|  7902 | Ford   | analyst   | 7566 | 1981-03-22 | 3000.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+

Note: when using the comparison operator, = is the condition for comparing strings (case insensitive)

select * from emp where ename='smith';
select * from emp where ename='SMITH';
These two are the same.

8.2 range query using [not] between... and

To realize the conditional query to judge whether the value of a field is within the specified range

select column_name1,column_name2 from table_name where column_name [not] between value and value2;

1. select * from emp where sal between 1500 and 3000;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 |     20 |
|  7566 | Jones | manager  | 7839 | 1981-03-22 | 2975.00 |   NULL |     20 |
|  7698 | Blake | manager  | 7839 | 1981-09-22 | 2850.00 |   NULL |     30 |
|  7782 | Scott | analyst  | 7566 | 1981-01-22 | 3000.00 |   NULL |     10 |
|  7902 | Ford  | analyst  | 7566 | 1981-03-22 | 3000.00 |   NULL |     20 |
+-------+-------+----------+------+------------+---------+--------+--------+
Note: it includes 1500 and 3000 values.

2. select * from emp where sal not between 1500 and 3000;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | Smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7521 | Ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | Maritn | salesman  | 7698 | 1981-06-22 | 1250.00 | 1400.00 |     30 |
|  7839 | King   | president | NULL | 1981-04-22 | 5000.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

8.3 specified set query using [not] in

You can not only use between and to indicate whether the value of a field is within the specified range, but also use the in keyword to determine whether the value of a field is within a specified set.

select column_name1,column_name2 from table_name where column_name [not] in (value1, value2, ...);

1. select * from emp where ename in ('King, 'SMITH');
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | Smith | clerk     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7839 | King  | president | NULL | 1981-04-22 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
Will find in The data inside.

2. select * from emp where ename not in ('King, 'SMITH');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | Allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     20 |
|  7521 | Ward   | salesman | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | Jones  | manager  | 7839 | 1981-03-22 | 2975.00 |    NULL |     20 |
|  7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 |     30 |
|  7698 | Blake  | manager  | 7839 | 1981-09-22 | 2850.00 |    NULL |     30 |
|  7782 | Scott  | analyst  | 7566 | 1981-01-22 | 3000.00 |    NULL |     10 |
|  7902 | Ford   | analyst  | 7566 | 1981-03-22 | 3000.00 |    NULL |     20 |
+-------+--------+----------+------+------------+---------+---------+--------+

8.4 null value query using is [not] null

The value of '0' or 'where' in a clause can be used to determine whether it is null or not.

select column_name1, column_name2,... from table_name where column_name is [not] null;

1. select * from emp where comm is null;
+-------+-------+-----------+------+------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | Smith | clerk     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | Jones | manager   | 7839 | 1981-03-22 | 2975.00 | NULL |     20 |
|  7698 | Blake | manager   | 7839 | 1981-09-22 | 2850.00 | NULL |     30 |
|  7782 | Scott | analyst   | 7566 | 1981-01-22 | 3000.00 | NULL |     10 |
|  7839 | King  | president | NULL | 1981-04-22 | 5000.00 | NULL |     10 |
|  7902 | Ford  | analyst   | 7566 | 1981-03-22 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

2. select * from emp where comm is not null;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | Allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     20 |
|  7521 | Ward   | salesman | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

8.5 fuzzy query using [not] like

In where, the budget symbol = can be used to specify the search, but in our actual development, we need to search all letters beginning with S according to S, which will use fuzzy query to query this kind of data.

slect column_name1, column_name2 from table_name where column_name [not] like value;

• Fuzzy query using% wildcard (multiple characters of the query, equivalent to the role of *, matching [0,])

1. select * from emp where ename like 'S%';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | Smith | clerk   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7782 | Scott | analyst | 7566 | 1981-01-22 | 3000.00 | NULL |     10 |
+-------+-------+---------+------+------------+---------+------+--------+
Query all to S Initial letter

2.  select * from emp where ename like '%S';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
Create a search to find all S Ending character

3. select * from emp where ename like '%S%';
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | Smith | clerk   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | Jones | manager | 7839 | 1981-03-22 | 2975.00 | NULL |     20 |
|  7782 | Scott | analyst | 7566 | 1981-01-22 | 3000.00 | NULL |     10 |
+-------+-------+---------+------+------------+---------+------+--------+
Query all letters that contain S Yes, no matter the beginning, the end, or the middle will be matched.

• Use "" Wildcard fuzzy query (matching is one, [0,1]);

1. select * from emp where ename like '_L%';
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 |     20 |
|  7698 | Blake | manager  | 7839 | 1981-09-22 | 2850.00 |   NULL |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+

2. select * from emp where ename like '%L_';
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7499 | Alle | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 |     20 |
|  7698 | Bla | manager  | 7839 | 1981-09-22 | 2850.00 |   NULL |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+

3. select * from emp where ename like '_L_';
The match is three characters

Use not like '% S%';

select * from emp where ename not like '%S%';
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | Allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     20 |
|  7521 | Ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | Maritn | salesman  | 7698 | 1981-06-22 | 1250.00 | 1400.00 |     30 |
|  7698 | Blake  | manager   | 7839 | 1981-09-22 | 2850.00 |    NULL |     30 |
|  7839 | King   | president | NULL | 1981-04-22 | 5000.00 |    NULL |     10 |
|  7902 | Ford   | analyst   | 7566 | 1981-03-22 | 3000.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+

Note: if you want to use the string% and_ In other words, it is to use the translation characters, '', '%' To translate.

8.6 multi condition query using and

All the above have described that there is only one query condition, but sometimes only specifying one query condition can not meet the needs of users. You need to use and to query with multiple conditions.

select column_name1, column_name2 from table_name where where_condition and where_conditon2 and where_condition3;

select * from emp where deptno=20 and job='clerk';
+-------+-------+-------+------+------------+--------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal    | comm | deptno |
+-------+-------+-------+------+------------+--------+------+--------+
|  7369 | Smith | clerk | 7902 | 1980-12-17 | 800.00 | NULL |     20 |
+-------+-------+-------+------+------------+--------+------+--------+

8.7 multi condition query using or

There is also a keyword in MySql that can connect multiple query conditions. This keyword is' or '. Connecting multiple query criteria with or means that records can be queried as long as one of the conditions is met.

select column_name1, column_name2, from table_name where where_condition 1 or where_condition 2 [or where_condition 3]

select * from emp where deptno=20 or job='clerk';
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7369 | Smith | clerk    | 7902 | 1980-12-17 |  800.00 |   NULL |     20 |
|  7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 |     20 |
|  7566 | Jones | manager  | 7839 | 1981-03-22 | 2975.00 |   NULL |     20 |
|  7902 | Ford  | analyst  | 7566 | 1981-03-22 | 3000.00 |   NULL |     20 |
+-------+-------+----------+------+------------+---------+--------+--------+

be careful:

1. and and or Both; Logical operators.
2. and to && Equivalent.
3. or And || Equivalent.

1. Restricted query

In the actual development process, the amount of queries in the table is very large. If too much data is queried, it is obviously not practical to display all the data. At this time, we can limit the number of records displayed in the query results through the provided by MySql.

The keyword used to restrict the query is limit. After this keyword, you can specify the initial position of the display record in the query result and the number of rows displayed by the record.

select column_name1, column_name2 from table_name where where_condition limit [start_index], row_count;

Where, start_index is optional, indicating the initial position of the display record, row_count indicates the number of rows displayed by the record.

9.1 restricted query without specifying initial location

select * from emp where sal < 3000 limit 3;
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm   | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
|  7369 | Smith | clerk    | 7902 | 1980-12-17 |  800.00 |   NULL |     20 |
|  7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 |     20 |
|  7521 | Ward  | salesman | 7698 | 1981-02-22 | 1250.00 | 500.00 |     30 |
+-------+-------+----------+------+------------+---------+--------+--------+

start_ The default value of the index parameter is 0. The first three pieces of data are queried. Originally, there were many pieces of data. Because the limit limits the number of pieces, three pieces are queried.

9.2 restricted query of specified initialization location

select * from emp where sal < 3000 limit 3, 3;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7566 | Jones  | manager  | 7839 | 1981-03-22 | 2975.00 |    NULL |     20 |
|  7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 |     30 |
|  7698 | Blake  | manager  | 7839 | 1981-09-22 | 2850.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

limit 3 stands for start_index starts the query from the fourth record, and the query results are determined by the parameter row_count.

be careful:

1. If limit Restrictions if there are 11 records and 13 restrictions, Only 11 pieces of data will be displayed.
2. limit Keywords are usually used for pagination display. The first page does not need to specify the initial position of the display record, and it needs to be specified from the second page.
3. limit Keywords are usually associated with order by The clauses are sorted together, and then the results are sorted together.

1. Function query

MySql provides a large number of functions to simplify users' operations on the database, such as string processing, date operation and numerical operation. The use of functions can not only improve the ability of query, but also provide convenience for data conversion and processing.

The function only processes the data in the query result and does not change the value of the data table in the database. It provides two categories: single line function and multi line function.

10.1 query using single line function

The single line function calculates each piece of data and returns the result to the user.

1. String function.

(1). concat(str1, str2, strn); // Splice into a new string

(2). Insert (str, index, n newstr) / / replace the string str with the string newstr n characters starting from the index position

(3). length / / get the length of the string str

(4). lower(str) / / convert to lowercase

(5). upper(str) / / convert to uppercase

(6). left(str, n); // Get the leftmost n characters of STR

(7). replace(str, oldstr, newstr); // Replace all substrings oldstr in the string STR with the string newstr

(8). substring(str, index, n); // Gets the n characters starting from the index position of the string str

1. concat
select concat('full name:',ename,',department:',deptno) info from emp where deptno = 10;
+------------------------+
| info                   |
+------------------------+
| full name:Scott,department:10     |
| full name:King,department:10      |
+------------------------+

2. length()Use of functions
 If the query condition is based on the length of characters, it will be used length()Function.
select * from emp where length(ename)=6;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7654 | Maritn | salesman | 7698 | 1981-06-22 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

3. use lower() and upper() Function query
select ename, lower(ename), upper(ename) from emp where deptno = 10;
+-------+--------------+--------------+
| ename | lower(ename) | upper(ename) |
+-------+--------------+--------------+
| Scott | scott        | SCOTT        |
| King  | king         | KING         |
+-------+--------------+--------------+

4. replace()Use of functions
select ename, job, replace(job, 'clerk', 'staff') newjob from emp where job='clerk';
+-------+-------+--------+
| ename | job   | newjob |
+-------+-------+--------+
| Smith | clerk | staff  |
+-------+-------+--------+
It can be seen that the successful winning string clerk Replaced with staff

5. substring() Use of functions
select ename, substring(ename, 1, 3) subname, job from emp where job = 'clerk';
+-------+---------+-------+
| ename | subname | job   |
+-------+---------+-------+
| Smith | Smi     | clerk |
+-------+---------+-------+
Note: the following table of the first character is 1, not 0, so 1-3 Intercepted is Smi

1. Numerical function

A numerical function is a function used to handle numerical operations.

(1). abs(num) / / returns the absolute value of num

(2). ceil / / round up

(3). floor(num); // Round down

(4). mod(num1, num2); // Returns the remainder of num1/num2 (modulo)

(5). pi(); // Returns the value of PI

(6). pow(num. n) / power(num, n); // Returns the n-th power of num

(7). rand(num); Returns a random number between 0 and 1

(8). round(num, n); // The value rounded to the nearest N decimal places

(9). truncate(num, n); // The value is returned to the decimal place of num.

1. Date and time function

1. Interval type function

1. Process function

(1). if(condition, t, f); // If the condition is true, it returns t; otherwise, it returns F

(2). ifnull(value1, value2); // If value1 is not null, value1 is returned; otherwise, Value2 is returned

(3). nullif(value1, value2); // If value1 equals Value2, null is returned; otherwise, value1 is returned

(4). case value when [value] then result1[when value2 then result2 [else result] end / / if value is equal to value1, result is returned; otherwise, result is returned

1. if() function
select ename, deptno, sal, if(sal >= 3000, 'high','low') sal_level from emp where deptno= 20;
+-------+--------+---------+-----------+
| ename | deptno | sal     | sal_level |
+-------+--------+---------+-----------+
| Smith |     20 |  800.00 | low       |
| Allen |     20 | 1600.00 | low       |
| Jones |     20 | 2975.00 | low       |
| Ford  |     20 | 3000.00 | high      |
+-------+--------+---------+-----------+

2. ifnull() function
2.1 select ename, deptno,comm, job, sal*12 + comm year_income from emp where deptno=30;
+--------+--------+---------+----------+-------------+
| ename  | deptno | comm    | job      | year_income |
+--------+--------+---------+----------+-------------+
| Ward   |     30 |  500.00 | salesman |    15500.00 |
| Maritn |     30 | 1400.00 | salesman |    16400.00 |
| Blake  |     30 |    NULL | manager  |        NULL |
+--------+--------+---------+----------+-------------+
be careful: Blake Your annual salary does not return a number, but null， that is because null + Digital return null As a result, null Not equal to null character or 0

2.2 Solution use ifnull()function
select ename, deptno,comm, job, sal*12 + ifnull(comm, 0) year_income from emp where deptno=30;
+--------+--------+---------+----------+-------------+
| ename  | deptno | comm    | job      | year_income |
+--------+--------+---------+----------+-------------+
| Ward   |     30 |  500.00 | salesman |    15500.00 |
| Maritn |     30 | 1400.00 | salesman |    16400.00 |
| Blake  |     30 |    NULL | manager  |    34200.00 |
+--------+--------+---------+----------+-------------+
We will find that if used ifnull， It can be solved perfectly null Problems.

3. nullif() function
select nullif(1,1), nullif(1,2) from emp;
+-------------+-------------+
| nullif(1,1) | nullif(1,2) |
+-------------+-------------+
|        NULL |           1 |
As you can see, if the two parameters are equal, the return is null， If the two numbers are different, the value of the first parameter is returned.

4. case..when..then..() function
 Front use if Function to realize the classification of salary height, using case..when..then..()Can also be achieved.
select ename, deptno, sal, case sal>=3000 when true then 'high' else 'low' end sal_level from emp where deptno= 20; 
+-------+--------+---------+-----------+
| ename | deptno | sal     | sal_level |
+-------+--------+---------+-----------+
| Smith |     20 |  800.00 | low       |
| Allen |     20 | 1600.00 | low       |
| Jones |     20 | 2975.00 | low       |
| Ford  |     20 | 3000.00 | high      |
+-------+--------+---------+-----------+

1. Other functions

10.2 queries using multiline functions

Multiline function is to perform operations on a group of data, and only one result is returned for this group of data (multiline giant deer). It is also called grouping function and aggregation function

In the actual development, multi line functions are mostly statistical functions. Statistical functions are needed to count the number of people, statistical average, etc.

(1). count() / / the number of records in the statistics table

(2). sum() / / calculate the sum of the specified field values

(3). avg() / / calculate the average value of the specified field value

(4). max() / / the maximum value in the statistics field value

(5). min() / / the minimum value in the statistics field value

1. count() function
1.1 count(*) Returns the total number of records in the table
1.2 count(exp)  Return expression exp Number of records with non empty value
1.3 count(distinct(exp)); return exp Number of records with non duplicate and non empty values.
select count(*), count(comm),count(distinct(mgr)), count(distinct(ifnull(mgr, 0))) from emp;
+----------+-------------+----------------------+---------------------------------+
| count(*) | count(comm) | count(distinct(mgr)) | count(distinct(ifnull(mgr, 0))) |
+----------+-------------+----------------------+---------------------------------+
|        9 |           3 |                    4 |                               5 |
+----------+-------------+----------------------+---------------------------------+

2. sum(), avg() function
sum(exp); Return sum
sum(distinct(exp)) Returns the sum of non repeating expressions
avf() and sum () Function usage is the same

select sum(sal),avg(sal), sum(distinct sal), avg(distinct sal) from emp;
+----------+-------------+-------------------+-------------------+
| sum(sal) | avg(sal)    | sum(distinct sal) | avg(distinct sal) |
+----------+-------------+-------------------+-------------------+
| 21725.00 | 2413.888889 |          17475.00 |       2496.428571 |
+----------+-------------+-------------------+-------------------+


3. max() and min() function
select max(sal), min(sal) from emp;
+----------+----------+
| max(sal) | min(sal) |
+----------+----------+
|  5000.00 |   800.00 |
+----------+----------+
Note: when calculating the minimum value, null It will not be calculated and will be ignored. 

1. Grouping query

In the actual development process, we usually group all data records in the table according to different categories, and then calculate the grouped data.

We can use the group by clause to specify which field or fields to group, and specify records with the same field value as a group.

The query operation after grouping using the group by clause is relatively complex, which is divided into three stages.

1. use group by Simple grouping query.
2. use group by Grouping query for statistical function.
3. use group by to having Group query for

Single field grouping
select sum(sal) from emp group by deptno;
+----------+
| sum(sal) |
+----------+
|  8000.00 |
|  8375.00 |
|  5350.00 |
+----------+
Grouping can only be used together with aggregation function.

Multi field grouping
select sum(sal) from emp group by deptno,job;
+----------+
| sum(sal) |
+----------+
|  3000.00 |
|  5000.00 |
|  3000.00 |
|   800.00 |
|  2975.00 |
|  1600.00 |
|  2850.00 |
|  2500.00 |
+----------+

Grouping with statistical functions

select deptno, count(*), avg(sal), max(sal) from emp group by deptno;
+--------+----------+-------------+----------+
| deptno | count(*) | avg(sal)    | max(sal) |
+--------+----------+-------------+----------+
|     10 |        2 | 4000.000000 |  5000.00 |
|     20 |        4 | 2093.750000 |  3000.00 |
|     30 |        3 | 1783.333333 |  2850.00 |
+--------+----------+-------------+----------+

group by with where

select deptno, count(*), avg(sal), max(sal) from emp where deptno<>10 group by deptno;
+--------+----------+-------------+----------+
| deptno | count(*) | avg(sal)    | max(sal) |
+--------+----------+-------------+----------+
|     20 |        4 | 2093.750000 |  3000.00 |
|     30 |        3 | 1783.333333 |  2850.00 |
+--------+----------+-------------+----------+

Group query using group by and group concat()

select deptno, count(*), group_concat(ename) enames from emp group by deptno;
+--------+----------+------------------------+
| deptno | count(*) | enames                 |
+--------+----------+------------------------+
|     10 |        2 | Scott,King             |
|     20 |        4 | Smith,Allen,Jones,Ford |
|     30 |        3 | Ward,Maritn,Blake      |
+--------+----------+------------------------+

Group query using group by and having

Note: the where statement cannot use statistical functions, that is, it cannot use the where clause to implement conditional restrictions on grouping, which is caused by the order of execution. MySql provides a special grouping restriction, having clause

select deptno,count(*), avg(sal) from emp group by deptno having avg(sal)>= 2000; 
+--------+----------+-------------+
| deptno | count(*) | avg(sal)    |
+--------+----------+-------------+
|     10 |        2 | 4000.000000 |
|     20 |        4 | 2093.750000 |
+--------+----------+-------------+

be careful:

1. MySql The execution process of each clause in is from first to last: from -> where -> group by -> having -> select -> order by;
2. where Filter row, having Filter groups, having Support all where Operation.
3. having Clause appears group by Clause, and where Clause to appear in group by Before clause.