Fuzzy Query
Using like is called a fuzzy query and is also a type of conditional query that supports% or underscore matching: (% is a special symbol, _is also a special symbol)
-
%: match any number of characters
-
_: Any character.
For example: Find names that contain O?
mysql> select ename from emp where ename like '%O%'; +-------+ | ename | +-------+ | JONES | | SCOTT | | FORD | +-------+
Find one whose name ends in T?
select ename from emp where ename like '%T';
Find names that start with K?
select ename from emp where ename like 'K%';
Find out that the second letter is A?
select ename from emp where ename like '_A%';
Find out that the third letter is R?
select ename from emp where ename like '__R%';
For example:
t_student Student table name field ---------------------- zhangsan lisi wangwu zhaoliu jack_son
Find''in the name Yes?
select name from t_student where name like '%_%'; //That's not possible, because the underline represents any character and will look up the entire table
Escape with slash
mysql> select name from t_student where name like '%\_%'; // \Escape character. +----------+ | name | +----------+ | jack_son | +----------+
sort
order by - default ascending order
Query all employee salaries, sort?
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 | | FORD | 3000.00 | | SCOTT | 3000.00 | | KING | 5000.00 | +--------+---------+
How to descend the order? order by...desc
Specify 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 | | 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 | +--------+---------+
Specify ascending order - order by...asc
select ename,sal from emp order by sal asc; +--------+---------+ | 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 | | FORD | 3000.00 | | SCOTT | 3000.00 | | KING | 5000.00 | +--------+---------+
Multiple Field Sorting
For example: Query the employee's name and salary, and ask for the salary to be in ascending order, if the salaries are the same, then in ascending order by name.
select ename,sal from emp order by sal asc, ename asc; // sal is first, dominant, and ename sorting is only considered when sals are equal. +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | JAMES | 950.00 | | ADAMS | 1100.00 | | MARTIN | 1250.00 | | WARD | 1250.00 | | MILLER | 1300.00 | | TURNER | 1500.00 | | ALLEN | 1600.00 | | CLARK | 2450.00 | | BLAKE | 2850.00 | | JONES | 2975.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | KING | 5000.00 | +--------+---------+
Understanding: You can also sort fields by their location
select ename,sal from emp order by 2; // 2 represents the second column. The second column is sal
Sort by column 2 sal of the query results. Only understand, not in development because it is not healthy. Once the column order is easily changed, 2 is discarded when the column order is modified.
Comprehensive exercise:
Find out information about employees whose salaries range from 1250 to 3000 and ask them to be in descending order.
select ename,sal from emp where sal between 1250 and 3000 order by sal desc; +--------+---------+ | ename | sal | +--------+---------+ | 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 | +--------+---------+
Note: Keyword order cannot be changed!!, The format is as follows:
select ... from ... where ... order by ...
The execution order of the above statements must be mastered:
-
Step 1:from
-
Step 2:
-
Step 3: select
-
Step 4: order by (Sorting is always last!)
Data Processing Functions
Data processing functions are also known as one-line processing functions
Features of one-line processing functions: one input corresponds to one output.
As opposed to single-line processing functions, multiline processing functions. (Multi-line processing function features: multiple inputs, corresponding to one output!)
What are the common one-line processing functions?
lower conversion to lowercase
mysql> select lower(ename) as ename from emp; +--------+ | ename | +--------+ | smith | | allen | | ward | | jones | | martin | | blake | | clark | | scott | | king | | turner | | adams | | james | | ford | | miller | +--------+
There are 14 inputs and 14 outputs. This is a feature of one-line processing functions.
A multiline processing function with 14 lines and one output, such as: Calculating the sum of employee salaries:
select sum(sal) from emp;
upper conversion to uppercase
mysql> select * from t_student; +----------+ | name | +----------+ | zhangsan | | lisi | | wangwu | | jack_son | +----------+ mysql> select upper(name) as name from t_student; +----------+ | name | +----------+ | ZHANGSAN | | LISI | | WANGWU | | JACK_SON | +----------+
Substr takes substring (substr (intercepted string, start subscript, intercepted length))
select substr(ename, 1, 1) as ename from emp;
Note: Start subscript starts from 1, no 0.
Find employee information whose first letter of employee name is A?
First: Fuzzy Query select ename from emp where ename like 'A%'; Second way: substr function select ename from emp where substr(ename,1,1) = 'A';
Capitalize the first letter?
select name from t_student; select upper(substr(name,1,1)) from t_student; select substr(name,2,length(name) - 1) from t_student; select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student; +----------+ | result | +----------+ | Zhangsan | | Lisi | | Wangwu | | Jack_son | +----------+
concat function for string stitching
select concat(empno,ename) from emp; +---------------------+ | concat(empno,ename) | +---------------------+ | 7369SMITH | | 7499ALLEN | | 7521WARD | | 7566JONES | | 7654MARTIN | | 7698BLAKE | | 7782CLARK | | 7788SCOTT | | 7839KING | | 7844TURNER | | 7876ADAMS | | 7900JAMES | | 7902FORD | | 7934MILLER | +---------------------+
Length takes length
select length(ename) enamelength from emp; +-------------+ | enamelength | +-------------+ | 5 | | 5 | | 4 | | 5 | | 6 | | 5 | | 5 | | 5 | | 4 | | 6 | | 5 | | 5 | | 4 | | 6 | +-------------+
trim to remove spaces
mysql> select * from emp where ename = ' KING';
Empty set (0.00 sec)
mysql> select * from emp where ename = trim(' KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
+-------+-------+-----------+------+------------+---------+------+--------+
case...when...then...when...then...else...end
When the employee's job is MANAGER, the salary is increased by 10%, when the job is SALESMAN, the salary is increased by 50%, other normal.
(Note: Do not modify the database, just show the query result as a pay increase)
select ename, job, sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp; +--------+-----------+---------+---------+ | ename | job | oldsal | newsal | +--------+-----------+---------+---------+ | SMITH | CLERK | 800.00 | 800.00 | | ALLEN | SALESMAN | 1600.00 | 2400.00 | | WARD | SALESMAN | 1250.00 | 1875.00 | | JONES | MANAGER | 2975.00 | 3272.50 | | MARTIN | SALESMAN | 1250.00 | 1875.00 | | BLAKE | MANAGER | 2850.00 | 3135.00 | | CLARK | MANAGER | 2450.00 | 2695.00 | | SCOTT | ANALYST | 3000.00 | 3000.00 | | KING | PRESIDENT | 5000.00 | 5000.00 | | TURNER | SALESMAN | 1500.00 | 2250.00 | | ADAMS | CLERK | 1100.00 | 1100.00 | | JAMES | CLERK | 950.00 | 950.00 | | FORD | ANALYST | 3000.00 | 3000.00 | | MILLER | CLERK | 1300.00 | 1300.00 | +--------+-----------+---------+---------+
round-off
select field from Table Name; select ename from emp; select 'abc' from emp; // select is followed by Literal/Literal Value, which aliases all records. Just remember what the database does mysql> select 'abc' as bieming from emp; +---------+ | bieming | +---------+ | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | | abc | +---------+
mysql> select abc from emp; ERROR 1054 (42S22): Unknown column 'abc' in 'field list' This will definitely make a mistake, because it will abc As the name of a field, go emp Find in Table abc Field gone.
select 1000 as num from emp; // 1000 is also treated as a literal quantity/literal value. +------+ | num | +------+ | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | | 1000 | +------+
Conclusion: select can be followed by a table's field name (which can be equated to a variable name) or literal quantity/literal value (data).
select 21000 as num from dept; +-------+ | num | +-------+ | 21000 | | 21000 | | 21000 | | 21000 | +-------+ mysql> select round(1236.567, 0) as result from emp; //Keep integer digits. +--------+ | result | +--------+ | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | | 1237 | +--------+ select round(1236.567, 1) as result from emp; //Keep 1 decimal select round(1236.567, 2) as result from emp; //Keep 2 decimals select round(1236.567, -1) as result from emp; // Keep to ten. +--------+ | result | +--------+ | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | | 1240 | +--------+ select round(1236.567, -2) as result from emp; +--------+ | result | +--------+ | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | | 1200 | +--------+
rand() generates random numbers
mysql> select round(rand()*100,0) from emp; // Random numbers within 100 +---------------------+ | round(rand()*100,0) | +---------------------+ | 76 | | 29 | | 15 | | 88 | | 95 | | 9 | | 63 | | 89 | | 54 | | 3 | | 54 | | 61 | | 42 | | 28 | +---------------------+
Ifnull is an empty handler. Empty, ifnull converts null to a specific value
In all databases, as long as NULL participates in mathematical operations, the end result is NULL.
In the following example, there is a null value in the column of com subsidies, so let's verify:
He counts all the values with NULL added up to NULL
mysql> select ename, sal + comm as salcomm from emp; +--------+---------+ | ename | salcomm | +--------+---------+ | SMITH | NULL | | ALLEN | 1900.00 | | WARD | 1750.00 | | JONES | NULL | | MARTIN | 2650.00 | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | TURNER | 1500.00 | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+---------+
Calculate the annual salary of each employee? Annual Pay= (Monthly Pay + Monthly Allowance) * 12
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 | +--------+----------+
Note: As long as NULL participates in the operation, the final result must be NULL. To avoid this, you need to use the ifnull function.
ifnull function usage:
Ifnull (data, which value is treated as), that is, if "data" is NULL, which value is treated as this data structure.
At this point, when the subsidy is NULL, treat as 0, and then see:
select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp; +--------+----------+ | ename | yearsal | +--------+----------+ | SMITH | 9600.00 | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | 35700.00 | | MARTIN | 31800.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 | +--------+----------+
Grouping Function (Multiline Processing Function)
The features of a multiline processing function are that multiple lines are input and one line is output.
There are five functions:
-
Count count
-
sum of sums
-
avg mean
-
max maximum
-
min minimum
Note: Grouping functions must be grouped before they can be used. If you do not group data, the whole table defaults to one group.
Find the maximum salary? mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+ Find the minimum wage? mysql> select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+ Calculate wages and: mysql> select sum(sal) from emp; +----------+ | sum(sal) | +----------+ | 29025.00 | +----------+ Calculate average wage: mysql> select avg(sal) from emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+ 14 Add up all the wages and divide by 14. Calculate the number of employees? mysql> select count(ename) from emp; +--------------+ | count(ename) | +--------------+ | 14 | +--------------+
What do you need to be aware of when using grouping functions?
First point: NULL must be NULL in mysql, but the grouping function automatically ignores NULL, so you don't need to process NULL ahead of time.
Second point: What is the difference between count(*) and count (specific field) in a grouping function?
mysql> select count(*) from emp; +----------+ | count(*) | +----------+ | 14 | +----------+ mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+
Summary:
-
Count (field specific): Represents the total number of elements that are not NULL under this field.
-
count(*): The total number of rows in a statistical table. (counts with one row of data are ++)
Because each row of records cannot be NULL, and one column in a row of data is not NULL, the row of data is valid.
Third point: Grouping functions cannot be used directly in where clauses.
Find out about employees who are above the minimum wage. select ename,sal from emp where sal > min(sal); //Surface OK, run it? ERROR 1111 (HY000): Invalid use of group function
So let's learn group by, and when we're done, we'll see that
Fourth point: All grouping functions can be combined.
select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp; +----------+----------+----------+-------------+----------+ | sum(sal) | min(sal) | max(sal) | avg(sal) | count(*) | +----------+----------+----------+-------------+----------+ | 29025.00 | 800.00 | 5000.00 | 2073.214286 | 14 | +----------+----------+----------+-------------+----------+
Grouped queries (very important: five stars*****)
What is a group query?
In practical applications, there may be a need to group and then manipulate each group of data.
We need to use group query at this time. How to do group query?
select ... from ... group by ...
For example:
Calculate wages and salaries for each department?
Calculate average salary per job?
Find the highest salary for each job?
...
Put all the previous keywords together to see their execution order?
select ... from ... where ... group by ... order by ...
The order of the above keywords cannot be reversed and needs to be remembered. What is the execution order?
- from
- where
- group by
- select
- order by
Why can't grouping functions be used directly behind where as a condition?
select ename,sal from emp where sal > min(sal);//Report errors.
Grouping functions must be grouped before they can be used.
When where is executed, there are no groups. So no grouping function can appear after where.
select sum(sal) from emp;
This is not grouped. Why can the sum() function be used? Because select executes after group by.
Case study: Find out the salary and salary for each job?
The way to achieve this is to divide jobs into groups and then sum up wages.
select job,sum(sal) from emp group by job; +-----------+----------+ | job | sum(sal) | +-----------+----------+ | ANALYST | 6000.00 | | CLERK | 4150.00 | | MANAGER | 8275.00 | | PRESIDENT | 5000.00 | | SALESMAN | 5600.00 | +-----------+----------+
Execution order of the above statement?
-
Query the data from the emp table first.
-
Group by job field.
-
Then sum(sal) each group of data
select ename,job,sum(sal) from emp group by job; +-------+-----------+----------+ | ename | job | sum(sal) | +-------+-----------+----------+ | SCOTT | ANALYST | 6000.00 | | SMITH | CLERK | 4150.00 | | JONES | MANAGER | 8275.00 | | KING | PRESIDENT | 5000.00 | | ALLEN | SALESMAN | 5600.00 | +-------+-----------+----------+
The above statement can be executed in mysql, but it doesn't make sense. The above statement executes an error in oracle.
oracle's syntax is stricter than mysql's. (The syntax of MySQL is relatively loose!)
Important conclusion: In a select statement, if there is a group by statement, the select can only be followed by: the field participating in the grouping, as well as the grouping function, all others cannot follow.
Find the highest salary for each department
What is the implementation idea? Grouped by department number to maximize each group.
select Add after ename Fields don't make sense, and oracle Errors will be reported. mysql> select ename,deptno,max(sal) from emp group by deptno; +-------+--------+----------+ | ename | deptno | max(sal) | +-------+--------+----------+ | CLARK | 10 | 5000.00 | | SMITH | 20 | 3000.00 | | ALLEN | 30 | 2850.00 | +-------+--------+----------+
mysql> select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+
Find the highest salary for "every department, different job"?
+--------+-----------+---------+--------+ | ename | job | sal | deptno | +--------+-----------+---------+--------+ | MILLER | CLERK | 1300.00 | 10 | | KING | PRESIDENT | 5000.00 | 10 | | CLARK | MANAGER | 2450.00 | 10 | | FORD | ANALYST | 3000.00 | 20 | | ADAMS | CLERK | 1100.00 | 20 | | SCOTT | ANALYST | 3000.00 | 20 | | JONES | MANAGER | 2975.00 | 20 | | SMITH | CLERK | 800.00 | 20 | | BLAKE | MANAGER | 2850.00 | 30 | | MARTIN | SALESMAN | 1250.00 | 30 | | ALLEN | SALESMAN | 1600.00 | 30 | | TURNER | SALESMAN | 1500.00 | 30 | | WARD | SALESMAN | 1250.00 | 30 | | JAMES | CLERK | 950.00 | 30 | +--------+-----------+---------+--------+
Tip: Two fields are combined into one field to see. (Joint grouping of two fields)
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 | +--------+-----------+----------+
Use having to further filter the data after grouping.
Note: having cannot be used alone, where cannot be replaced by having, and must be used in conjunction with group by.
Find the highest salary for each department and ask that it be shown that the highest salary is greater than 3000?
Step 1: Find the highest salary for each department Grouped by Department number, maximize each group. select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+ Step 2: Require maximum salary above 3000 select deptno,max(sal) from emp group by deptno having max(sal) > 3000; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+
Think about a question: Is the above sql statement inefficient to execute?
At a lower level, you can actually think about this: find out all the more than 3000 before grouping them.
select deptno,max(sal) from emp where sal > 3000 group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | +--------+----------+
Optimizing strategy:
Where and have, give priority to where, where can't be done, and have.
where can't????
Find out the average salary per department and ask to show that the average salary is above 2500.
Step 1: Find out the average salary per department select deptno,avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | | 30 | 1566.666667 | +--------+-------------+ Step 2: Require that the average salary is above 2500 select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | +--------+-------------+