Query statement (DQL)
1.5. 1 simple query statement
Syntax format:
select field name 1, field name 2, field name 3 From table name;
Tips:
-
Any sql statement that ends with a semicolon
-
sql statements are not case sensitive
mysql> select Empno,ENamE from -> emp; +-------+--------+ | Empno | ENamE | +-------+--------+ | 7369 | SMITH | | 7499 | ALLEN | | 7521 | WARD | | 7566 | JONES | | 7654 | MARTIN | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7839 | KING | | 7844 | TURNER | | 7876 | ADAMS | | 7900 | JAMES | | 7902 | FORD | | 7934 | MILLER | +-------+--------+ 14 rows in set (0.00 sec)Query employee's annual salary?
select ename, sal*12 from emp;
mysql> select ename, sal * 12 from emp; +--------+----------+ | ename | sal*12 | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ 14 rows in set (0.00 sec)
Fields can participate in operations, and the query field results can be renamed:
select ename, sal * 12 as yearSal from emp; Statement renames the displayed new field
mysql> select ename,sal*12 as yearSal from emp; +--------+----------+ | ename | yearSal | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ 14 rows in set (0.00 sec)
Is there Chinese in the alias?
Select ename, Sal * 12 as annual salary from emp# error
select ename, sal * 12 as' annual salary 'from emp;
mysql> select ename, sal * 12 as'Annual salary'from emp; +--------+----------+ | ename | Annual salary | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ 14 rows in set (0.00 sec)
The standard string is a single quote identification string. It is recommended to use single quotation marks instead of double quotation marks when writing strings in the database, so that it can run not only in MySQL, but also in other databases.
as keyword can be omitted:
mysql> select ename,sal * 12 yearSal from emp; +--------+----------+ | ename | yearSal | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 19200.00 | | WARD | 15000.00 | | JONES | 35700.00 | | MARTIN | 15000.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | SCOTT | 36000.00 | | KING | 60000.00 | | TURNER | 18000.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | FORD | 36000.00 | | MILLER | 15600.00 | +--------+----------+ 14 rows in set (0.00 sec)
Query all fields?
select * from table name;
*The efficiency of is low and is not recommended in practical development.
1.5. 2 condition query
Syntax format:
select field 1, field 2, field 3 From table name where condition;
Execution order: first from, then where, and finally select
- Query the name of an employee whose salary is 5000:
select ename from emp where sal=5000;
mysql> select ename from emp where sal=5000; +-------+ | ename | +-------+ | KING | +-------+ 1 row in set (0.00 sec)
- Query SMITH's salary:
mysql> select sal from emp where ename = 'SMITH'; +--------+ | sal | +--------+ | 800.00 | +--------+ 1 row in set (0.00 sec)
(write single quotation marks for string query)
- Query employees with salary greater than 3000:
mysql> select ename, sal from emp where sal>=3000; +-------+---------+ | ename | sal | +-------+---------+ | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+ 3 rows in set (0.01 sec)
- Query employees whose salary is not equal to 3000:
mysql> select ename,sal from emp where sal <> 3000; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | MILLER | 1300.00 | +--------+---------+ 12 rows in set (0.00 sec) mysql> select ename, sal from emp where sal != 3000; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | MILLER | 1300.00 | +--------+---------+ 12 rows in set (0.00 sec)
- Query salary within a certain range:
mysql> select ename,sal from emp where sal >= 1100 and sal <= 3000; +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ 11 rows in set (0.01 sec)
mysql> select ename,sal from emp where sal between 1100 and 3000; +--------+---------+ | ename | sal | +--------+---------+ | ALLEN | 1600.00 | | WARD | 1250.00 | | JONES | 2975.00 | | MARTIN | 1250.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ 11 rows in set (0.01 sec)
between and is a closed interval [10003000], the order cannot be exchanged, otherwise no data can be queried
between... and... Can also be applied to Strings:
The description is marked with an initial letter.
mysql> select ename from emp where ename between 'A' and 'B'; +-------+ | ename | +-------+ | ALLEN | | ADAMS | +-------+ 2 rows in set (0.01 sec)
- Find out who doesn't have an allowance
In the database, NULL is not a value, representing nothing. It is empty. It cannot be measured with an equal sign. You must use is null or is not null.
mysql> select ENAME, SAL from EMP where COMM is null; +--------+---------+ | ENAME | SAL | +--------+---------+ | SMITH | 800.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ 10 rows in set (0.00 sec)
- Identify employees whose jobs are MANAGER and SALESMAN:
mysql> select ENAME, JOB from EMP where JOB = 'SALSMAN' or JOB = 'MANAGER'; +-------+---------+ | ENAME | JOB | +-------+---------+ | JONES | MANAGER | | BLAKE | MANAGER | | CLARK | MANAGER | +-------+---------+ 3 rows in set (0.00 sec)
Note that the characters should be enclosed in single quotes. In addition, conditions should be written before and after the or connection.
-
Joint use of and and or: find out employees whose salary is greater than 1000 and department number is 20 or 30
-
mysql> select ENAME,SAL,DEPTNO from EMP where SAL > 1000 and DEPTNO = 20 or DEPTNO = 30; +--------+---------+--------+ | ENAME | SAL | DEPTNO | +--------+---------+--------+ | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | JONES | 2975.00 | 20 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | SCOTT | 3000.00 | 20 | | TURNER | 1500.00 | 30 | | ADAMS | 1100.00 | 20 | | JAMES | 950.00 | 30 | | FORD | 3000.00 | 20 | +--------+---------+--------+ 10 rows in set (0.00 sec)
mysql> select ENAME,SAL,DEPTNO from EMP where SAL > 1000 and (DEPTNO = 20 or DEPTNO = 30); +--------+---------+--------+ | ENAME | SAL | DEPTNO | +--------+---------+--------+ | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | JONES | 2975.00 | 20 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | SCOTT | 3000.00 | 20 | | TURNER | 1500.00 | 30 | | ADAMS | 1100.00 | 20 | | FORD | 3000.00 | 20 | +--------+---------+--------+ 9 rows in set (0.00 sec)
The first is wrong, the second is correct, and the priority is greater than or
When the priority is uncertain, pay attention to writing parentheses
-
in equals or: find out the employees whose jobs are MANAGER and SALSMAN:
-
mysql> select ename , sal, job from emp where job = 'SALESMAN' or job = 'MANAGER'; +--------+---------+----------+ | ename | sal | job | +--------+---------+----------+ | ALLEN | 1600.00 | SALESMAN | | WARD | 1250.00 | SALESMAN | | JONES | 2975.00 | MANAGER | | MARTIN | 1250.00 | SALESMAN | | BLAKE | 2850.00 | MANAGER | | CLARK | 2450.00 | MANAGER | | TURNER | 1500.00 | SALESMAN | +--------+---------+----------+ 7 rows in set (0.00 sec) mysql> select ename, sal, job from emp where job in ('SALESMAN','MANAGER'); +--------+---------+----------+ | ename | sal | job | +--------+---------+----------+ | ALLEN | 1600.00 | SALESMAN | | WARD | 1250.00 | SALESMAN | | JONES | 2975.00 | MANAGER | | MARTIN | 1250.00 | SALESMAN | | BLAKE | 2850.00 | MANAGER | | CLARK | 2450.00 | MANAGER | | TURNER | 1500.00 | SALESMAN | +--------+---------+----------+ 7 rows in set (0.01 sec)Note that this is not an interval, but a specific value. For example, the following example:
Each after in is a value, not an interval
mysql> select ENAME,SAL,JOB from EMP where SAL in (800,5000); +-------+---------+-----------+ | ENAME | SAL | JOB | +-------+---------+-----------+ | SMITH | 800.00 | CLERK | | KING | 5000.00 | PRESIDENT | +-------+---------+-----------+ 2 rows in set (0.00 sec)
-
In addition, in has a negative: not in, which means that the conditions not listed are found:
mysql> select ENAME,SAL,JOB from EMP where SAL not in (800,5000); +--------+---------+----------+ | ENAME | SAL | JOB | +--------+---------+----------+ | ALLEN | 1600.00 | SALESMAN | | WARD | 1250.00 | SALESMAN | | JONES | 2975.00 | MANAGER | | MARTIN | 1250.00 | SALESMAN | | BLAKE | 2850.00 | MANAGER | | CLARK | 2450.00 | MANAGER | | SCOTT | 3000.00 | ANALYST | | TURNER | 1500.00 | SALESMAN | | ADAMS | 1100.00 | CLERK | | JAMES | 950.00 | CLERK | | FORD | 3000.00 | ANALYST | | MILLER | 1300.00 | CLERK | +--------+---------+----------+ 12 rows in set (0.00 sec)
-
1.5. 3 fuzzy query like
Find the o in the name:
In fuzzy query, we must master two special symbols: one is%, the other is_
%Represents any number of characters_ Represents any character. Then find out the one with o in the name. You should use the following statement:
select ENAME from EMP where ename like '%O%';
mysql> select ENAME from EMP where ename like '%O%'; +-------+ | ENAME | +-------+ | JONES | | SCOTT | | FORD | +-------+ 3 rows in set (0.00 sec)
Find out if the second letter in the name is A:
mysql> select ENAME from EMP where ename like '_A%'; +--------+ | ENAME | +--------+ | WARD | | MARTIN | | JAMES | +--------+ 3 rows in set (0.00 sec)
Find underlined names: use escape characters\
Find out if the last letter in the name is t:
mysql> select ENAME from EMP where ename like '%t'; +-------+ | ENAME | +-------+ | SCOTT | +-------+ 1 row in set (0.00 sec)
1.5. 4 sorting (ascending, descending)
Find out the employee name and salary in ascending order of salary:
The keyword is order by; The default is in ascending order.
mysql> select ENAME, SAL from EMP order by SAL; +--------+---------+ | ENAME | SAL | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+ 14 rows in set (0.00 sec)
- How to specify ascending or descending order
- asc: ascending order
- desc: descending order
mysql> 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 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+ 14 rows in set (0.00 sec)
It is arranged in descending order of salary. When the salary is the same, it is arranged in ascending order of Name:
mysql> select ENAME, SAL from EMP order by SAL desc,ENAME asc; +--------+---------+ | ENAME | SAL | +--------+---------+ | KING | 5000.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | MARTIN | 1250.00 | | WARD | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+ 14 rows in set (0.00 sec)
Multi field sorting: the higher the field, the more dominant it is. Only when the previous field cannot be sorted, the latter field will play a sorting role.
In the order of columns: 1 represents the first column and 2 represents the second column.
mysql> select ENAME, SAL from EMP order by 2; +--------+---------+ | ENAME | SAL | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+ 14 rows in set (0.00 sec) mysql> select ENAME, SAL from EMP order by 1; +--------+---------+ | ENAME | SAL | +--------+---------+ | ADAMS | 1100.00 | | ALLEN | 1600.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | FORD | 3000.00 | | JAMES | 950.00 | | JONES | 2975.00 | | KING | 5000.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | SCOTT | 3000.00 | | SMITH | 800.00 | | TURNER | 1500.00 | | WARD | 1250.00 | +--------+---------+ 14 rows in set (0.00 sec)
mysql> select * from EMP order by 6; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec)
However, it is not recommended to use the column number for sorting. If the column order is changed, this writing method will fail.
Find out the employees whose jobs are Salman and arrange them in descending order of salary:
mysql> select ENAME,SAL from EMP where JOB = 'SALESMAN' order by SAL desc; +--------+---------+ | ENAME | SAL | +--------+---------+ | ALLEN | 1600.00 | | TURNER | 1500.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | +--------+---------+ 4 rows in set (0.00 sec)
Execution order of the above statements:
select
* 3
from
tablename 1
where
conditions 2
order by
... 4
Note: order by is the last execution. See the following example for verification:
mysql> select ENAME,SAL as salary from EMP order by salary; +--------+---------+ | ENAME | salary | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+ 14 rows in set (0.00 sec)
We use aliases for sorting. Aliases can be used as queries, indicating that order by is the last to be executed.
1.5. 5 grouping function
{ c o u n t meter number s u m seek and a v g seek flat all value m a x most large value m i n most Small value \begin{cases} count\quad count \ \ sum\quad Sum \ \ avg\quad average \ \ max\quad maximum \ \ min\quad minimum \ \ end{cases} ⎩⎪⎪⎪⎪⎪⎨⎪⎪⎪⎪⎪⎧ count sum avg average max Max min Min min Min
All grouping functions operate on a certain set of data.
Total salary: select sum(sal) from emp;
mysql> select sum(sal) from emp; +----------+ | sum(sal) | +----------+ | 29025.00 | +----------+ 1 row in set (0.01 sec)
Find the maximum salary: select max(sal) from emp;
mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+ 1 row in set (0.01 sec)
Find out the minimum wage: select min(sal) from emp;
mysql> select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+ 1 row in set (0.00 sec)
Find the average salary: select avg(sal) from emp;
mysql> select avg(sal) from emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+ 1 row in set (0.00 sec)
Find the total number of people: select count(ename) from emp; Or select count(*) from emp;
mysql> select count(ename) from emp; +--------------+ | count(ename) | +--------------+ | 14 | +--------------+ 1 row in set (0.00 sec) mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec)
There are five grouping functions, all of which process a group of data. Also known as multiline processing function
Characteristics of multi line processing function: there are multiple lines of input, but only one line of output.
The grouping function automatically ignores NULL. The following NULL salary is not counted.
mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec) mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | 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 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec)
- Identify employees whose wages are above average:
mysql> select ename, sal from emp where sal>=avg(sal); ERROR 1111 (HY000): Invalid use of group function
Invalid use of grouping function.
There is a rule in sql statements: grouping functions cannot be written directly to where.
group by is executed after where, and the grouping function can only be executed after the grouping is completed, let alone the grouping function.
- Statement execution order:
select ... 5 from ... 1 where ... 2 group by ... 3 having ... 4 order by ... 6
According to this execution order, find out the sql statement of employees whose salary is higher than the average salary:
mysql> select avg(sal) from emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+ 1 row in set (0.00 sec) mysql> select ename, sal from emp where sal >= 2073.214286; +-------+---------+ | ename | sal | +-------+---------+ | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+ 6 rows in set (0.01 sec)
Splice two query statements:
mysql> select ename, sal from emp where sal>= (select avg(sal) from emp); +-------+---------+ | ename | sal | +-------+---------+ | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+ 6 rows in set (0.00 sec)
Using a select statement nested within a select statement is called a subquery.
- What is the difference between count(*) and count (specific field)?
count(*): counts the total number of records, which has nothing to do with a field
count(comm): indicates the total number of non null data in the comm field.
- Combination of grouping functions
mysql> select count(*),min(sal),max(sal),avg(sal) from emp; +----------+----------+----------+-------------+ | count(*) | min(sal) | max(sal) | avg(sal) | +----------+----------+----------+-------------+ | 14 | 800.00 | 5000.00 | 2073.214286 | +----------+----------+----------+-------------+ 1 row in set (0.00 sec)
1.5. 6 single line processing function
What is a single line handler?
Input one line, output one line
Example: calculate the annual salary of each employee
mysql> select ename , (sal+comm)*12 as yearsal from emp; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | NULL | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | NULL | | MARTIN | 31800.00 | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | TURNER | 18000.00 | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+----------+ 14 rows in set (0.00 sec)
If the allowance is NULL, the final result is NULL: the database stipulates that if there is NULL in the calculation expression, the result must be NULL.
It should be noted that the expression here should not be confused with the grouping function. The grouping function will actively ignore null. As follows:
mysql> select sum(comm) from emp; +-----------+ | sum(comm) | +-----------+ | 2200.00 | +-----------+ 1 row in set (0.00 sec)
The grouping function does not need to be added as select sum(comm) from emp where comm is not null;
When using the expression select ename, (SAL + comm) * 12 as yearsal from EMP; When calculating the annual salary, you need to use the null value processing function: ifnull(arg1, arg2), which belongs to the single line processing function.
The format is: ifnull (it may be a control function, which is treated as)
mysql> select ename, ifnull(comm, 0) from emp; +--------+-----------------+ | ename | ifnull(comm, 0) | +--------+-----------------+ | SMITH | 0.00 | | ALLEN | 300.00 | | WARD | 500.00 | | JONES | 0.00 | | MARTIN | 1400.00 | | BLAKE | 0.00 | | CLARK | 0.00 | | SCOTT | 0.00 | | KING | 0.00 | | TURNER | 0.00 | | ADAMS | 0.00 | | JAMES | 0.00 | | FORD | 0.00 | | MILLER | 0.00 | +--------+-----------------+ 14 rows in set (0.00 sec)
- Calculate salary using ifnull()
mysql> select ename, (sal + ifnull(comm, 0)) as yearsal from emp; +--------+---------+ | ename | yearsal | +--------+---------+ | SMITH | 800.00 | | ALLEN | 1900.00 | | WARD | 1750.00 | | JONES | 2975.00 | | MARTIN | 2650.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | SCOTT | 3000.00 | | KING | 5000.00 | | TURNER | 1500.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | FORD | 3000.00 | | MILLER | 1300.00 | +--------+---------+ 14 rows in set (0.00 sec)
1.5.7 group by and having
Group by: group by a field or some fields
having: filter the grouped data again
Case: find out the highest salary for each job.
mysql> select max(sal) from emp group by job; +----------+ | max(sal) | +----------+ | 3000.00 | | 1300.00 | | 2975.00 | | 5000.00 | | 1600.00 | +----------+ 5 rows in set (0.00 sec) mysql> select job, max(sal) from emp group by job; +-----------+----------+ | job | max(sal) | +-----------+----------+ | ANALYST | 3000.00 | | CLERK | 1300.00 | | MANAGER | 2975.00 | | PRESIDENT | 5000.00 | | SALESMAN | 1600.00 | +-----------+----------+ 5 rows in set (0.00 sec)
Note: grouping functions are generally used in combination with group by. This is why it is called a grouping function.
And any grouping function (avg, min, max, sum) is executed after group by.
When an sql statement does not have group by, the data of the whole table will form a group.
Case: find out the highest salary for each job:
mysql> select job, max(sal) from emp group by job; +-----------+----------+ | job | max(sal) | +-----------+----------+ | ANALYST | 3000.00 | | CLERK | 1300.00 | | MANAGER | 2975.00 | | PRESIDENT | 5000.00 | | SALESMAN | 1600.00 | +-----------+----------+ 5 rows in set (0.00 sec)
Note: select ename, max(sal) from emp group by job; In mysql, there are query results, but they are meaningless. Errors and syntax errors will be reported in Oracle database. The syntax of Oracle is more rigorous than that of MySQL
Remember a rule: when there is group by in a statement, select can only be followed by the fields and grouping functions involved in grouping.
Average salary per job:
mysql> select job, avg(sal) from emp group by job; +-----------+-------------+ | job | avg(sal) | +-----------+-------------+ | ANALYST | 3000.000000 | | CLERK | 1037.500000 | | MANAGER | 2758.333333 | | PRESIDENT | 5000.000000 | | SALESMAN | 1400.000000 | +-----------+-------------+ 5 rows in set (0.00 sec)
Can multiple fields be grouped together?
Case list: find out the maximum salary of each position in different departments.
mysql> select deptno, job, max(sal) from emp group by deptno,job; +--------+-----------+----------+ | deptno | job | max(sal) | +--------+-----------+----------+ | 10 | CLERK | 1300.00 | | 10 | MANAGER | 2450.00 | | 10 | PRESIDENT | 5000.00 | | 20 | ANALYST | 3000.00 | | 20 | CLERK | 1100.00 | | 20 | MANAGER | 2975.00 | | 30 | CLERK | 950.00 | | 30 | MANAGER | 2850.00 | | 30 | SALESMAN | 1600.00 | +--------+-----------+----------+ 9 rows in set (0.01 sec)
Case: find out the maximum salary of each department and display the data with salary greater than 2900.
mysql> select deptno, max(sal) from emp group by deptno having max(sal)>2900; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | +--------+----------+ 2 rows in set (0.00 sec)
However, this method is inefficient. It is recommended to use where to filter before grouping. This method is more efficient.
mysql> select deptno, max(sal) from emp where sal>2900 group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | +--------+----------+ 2 rows in set (0.00 sec)
Case: find out the average salary of each department and display the data whose salary is greater than the average salary.
mysql> select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | +--------+-------------+ 2 rows in set (0.00 sec)
Using the having statement can be easily written, but using the where statement cannot filter. (sub query is not allowed, and grouping function cannot be used after where)
having is used to filter the data after grouping, and where is used to filter the data before grouping.
1.5. 8 summarize DQL statements
The complete statement sequence and execution sequence are as follows:
select ... 5 from ... 1 where ... 2 group by ...3 having ... 4 order by ...6