1. Simple query statement (DQL)
Syntax format:
select field name 1, field name 2, field name 3 from table name;
mysql> select ename,empno from emp; +--------+-------+ | ename | empno | +--------+-------+ | SMITH | 7369 | | ALLEN | 7499 | | WARD | 7521 | | JONES | 7566 | | MARTIN | 7654 | | BLAKE | 7698 | | CLARK | 7782 | | SCOTT | 7788 | | KING | 7839 | | TURNER | 7844 | | ADAMS | 7876 | | JAMES | 7900 | | FORD | 7902 | | MILLER | 7934 | +--------+-------+ mysql> SELECT ENAME,EMPNO FROM EMP; +--------+-------+ | ENAME | EMPNO | +--------+-------+ | SMITH | 7369 | | ALLEN | 7499 | | WARD | 7521 | | JONES | 7566 | | MARTIN | 7654 | | BLAKE | 7698 | | CLARK | 7782 | | SCOTT | 7788 | | KING | 7839 | | TURNER | 7844 | | ADAMS | 7876 | | JAMES | 7900 | | FORD | 7902 | | MILLER | 7934 | +--------+-------+
Tips:
1. Any sql statement ends with ';'.
2. sql statements are not case sensitive and can be mixed.
Query employee's annual salary? (fields can participate in mathematical operations)
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 | +--------+----------+
Rename columns of query results?
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 | +--------+----------+
Chinese in alias?
mysql> select ename,sal * 12 as Annual salary from emp; (Error) mysql> select ename,sal * 12 as 'Annual salary' from emp; (Correct approach) +--------+----------+ | 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 | +--------+----------+
Note: single quotes are required for characters in standard sql statements. Although mysql supports double quotes, try not to use them.
Can the as keyword be omitted?
mysql> select empno,ename,sal*12 yearsal from emp; +-------+--------+----------+ | empno | ename | yearsal | +-------+--------+----------+ | 7369 | SMITH | 9600.00 | | 7499 | ALLEN | 19200.00 | | 7521 | WARD | 15000.00 | | 7566 | JONES | 35700.00 | | 7654 | MARTIN | 15000.00 | | 7698 | BLAKE | 34200.00 | | 7782 | CLARK | 29400.00 | | 7788 | SCOTT | 36000.00 | | 7839 | KING | 60000.00 | | 7844 | TURNER | 18000.00 | | 7876 | ADAMS | 13200.00 | | 7900 | JAMES | 11400.00 | | 7902 | FORD | 36000.00 | | 7934 | MILLER | 15600.00 | +-------+--------+----------+
Query all fields?
select * from emp; (not recommended in actual development, * is inefficient, it needs to be converted to fields first)
2. Condition query
Syntax format: select: Fields, fields from Table name where Conditions;
Execution order: first from, then where, and then select
Query the name of the employee whose job is equal to 5000?
mysql> select ename from emp where sal=5000; +-------+ | ename | +-------+ | KING | +-------+
Query the salary of SMiTH?
mysql> select sal from emp where ename='SMITH'; // String in single quotes +--------+ | sal | +--------+ | 800.00 | +--------+
Identify employees with wages of no less than 3000?
mysql> select ename,sal from emp where sal>=3000; +-------+---------+ | ename | sal | +-------+---------+ | SCOTT | 3000.00 | | KING | 5000.00 | | FORD | 3000.00 | +-------+---------+ Operator: < or! = not equal to between...and... Two values (closed range, must be small on the left and large on the right!!!) , equivalent to > = and<= Is null is null (is not null is not null) and Or or In contains, equivalent to multiple or (not in this range) Not not not, mainly used in and null like fuzzy query
Find out the wage is not equal to 3000?
select ename,sal from emp where sal <> 3000; select ename,sal from emp where sal != 3000;
Identify employees with salaries between 1100 and 3000, including 1100 and 3000?
select ename,sal from emp where sal >= 1100 and sal <= 3000; select eanme,sal from emp where sal between 1100 and 3000;
between... And... It must be small on the left and large on the right.
between... and... In addition to numbers, you can also use strings.
mysql> select ename from emp where ename between 'A' and 'C'; //(Note: initial left closed right open!!!) +-------+ | ename | +-------+ | ALLEN | | BLAKE | | ADAMS | +-------+
Find out who has an empty allowance?
In the database, NULL is not a value, which means nothing. It is empty.
Empty is not a value and cannot be measured by an equal sign.
is null or is not null must be used;
select ename,sal,comm from emp where comm is null;
Find out who doesn't have benefits?
select ename,sal,comm from emp where comm is null or comm = 0;
Combined with or: find out the Department employees whose salary is more than 1000 and whose department number is 20 or 30
select ename,sal,deptno from emp where sal > 1000 and deptno =20 or deptno = 30; // error select ename,sal,deptno from emp where sal > 1000 and (deptno =20 or deptno = 30); // Correct
Note: when the priority of the operator is uncertain, parentheses should be added.
Identify the employees whose jobs are MANAGER and SALESMAN?
select ename,job from emp where job='SALESMAN' or job='MANAGER';
select ename,job from emp where job in('SALESMAN','MANAGER');
select ename,job from emp where sal in (1000,5000); //Note: the salary here is 1000 or 5000, not 1000-5000
Fuzzy query like?
(in fuzzy query, you must master two special symbols, one is%, and the other is ﹖)
%Represents any number of characters, and UU represents any one character. (sort of regular)
Find the one with O in the name?
select ename from emp where ename like '%o%'; mysql> select ename from emp where ename like '%o%'; +-------+ | ename | +-------+ | JONES | | SCOTT | | FORD | +-------+
Find out if the second letter in the name is A?
mysql> select ename from emp where ename like '_A%'; +--------+ | ename | +--------+ | WARD | | MARTIN | | JAMES | +--------+
Find the underlined part of the name?
Escape characters are needed here\ select ename from emp where ename like '%\_%';
3. Sort (ascending, descending)
Find out the employee name and salary in ascending order?
select ename, sal from emp order by sal; mysql> select ename,sal from emp order by sal; //Default ascending order +--------+---------+ | 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 | +--------+---------+
Note: default ascending order. How to specify ascending or descending order? asc means ascending, desc means descending.
select ename,sal from emp order by sal; select ename,sal from emp order by sal asc; // Specified ascending order select ename,sal from emp order by sal desc; // Specified descending order
In descending order of salary, when the salary is the same, then in ascending order of name?
select ename,sal from emp order by sal desc, ename asc;
Note: the more advanced the field is, the more dominant it is. Only when the previous fields cannot be sorted will the latter fields be enabled.
select ename,sal from emp order by 1; // 1 means to sort by the first column, which means ename select ename,sal from emp order by 2; // 2 indicates sorting by the second column, which refers to sal
Find out the employees whose jobs are SALESMAN and arrange them in descending order of salary
select ename,job,sal from emp where job='SALESMAN' order by sal desc;
Here, execute from, then where, then select, and finally order by.
4. Grouping function
Count count
sum summation
avg average
max Max
min Min
Remember: all grouping functions operate on a set of data.
Find the total wage: select sum(sal) from emp; Find the maximum wage: select max(sal) from emp; Find out the average wage: select avg(sal) from emp; Find out the total number of people: select count(ename) from emp; Or: select count(*) from emp;
There are only five grouping functions in total. The grouping function has another name: multi line processing function.
Multi line processing function features: input multiple lines, the final output is 1 line.
Note: the grouping function automatically ignores NULL.
mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+ mysql> select sum(comm) from emp; //Four operations with NULL must be NULL, and sum is not NULL //The grouping function automatically ignores NULL and does not need to add additional conditional statements +-----------+ | sum(comm) | +-----------+ | 2200.00 | +-----------+
Identify employees who are paid above average?
select ename,sal from emp where sal > avg(sal); //ERROR 1111 (HY000): Invalid use of group function
Error message: invalid use of grouping function
Reason: there is a syntax rule in SQL statement. Grouping function cannot be directly used in where clause. why??
How to explain???
Because group by is executed after where. When an sql statement does not have group by, the data of the whole table will form a group, which can be understood as hiding group by
Execution sequence: select ... 5 from ... 1 where ... 2 First filter group by ... 3 Grouping having ... 4 Re filtration order by ... 6
So how to find out the employees whose wages are higher than the average?
Step 1: find out the average wage select avg(sal) from emp; +-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+ Step 2: employees above average wage select ename,sal from where sal > 2073.214286;
Can we solve it in one sentence? Subquery, SQL statement nesting
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 | +-------+---------+
What is the difference between count(*) and count (a specific field)?
count(*): not the number of data in a field, but the total number of records. (independent of a field)
count(comm): indicates the total number of non NULL data in comm field.
Grouping functions can also be combined:
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
5. Single line processing function
What is a single line handler?
Input one line, output one line.
Calculate the annual salary of each employee?
select ename,(sal+comm)*12 as yearsal from emp; mysql> select ename,(sal+comm)*12 as yearsal from emp; // But any data is far from NULL, and the result is NULL //Use the ifnull() function: select ename,(sal + ifnull(comm,0))*12 as yearsal from emp;
If () null handler?
ifnull(May be NULL What is the data treated as) select ename,ifnull(comm,0) as comm from emp;
6. group by and having
Group by: group by a field or some fields.
having: filter the grouped data again. It cannot be used alone. It must be used in combination with group by.
Case: find out the highest salary for each position.
select max(sal),job from emp group by job; +----------+-----------+ | max(sal) | job | +----------+-----------+ | 3000.00 | ANALYST | | 1300.00 | CLERK | | 2975.00 | MANAGER | | 5000.00 | PRESIDENT | | 1600.00 | SALESMAN | +----------+-----------+
Note: grouping function is generally used in combination with group by, which is why it is called grouping function.
And any grouping function is executed after the group by statement is executed.
When an sql statement does not have group by, the data of the whole table will form a group.
Note the following statement:
select ename,max(sal),job from emp group by job;
This statement will report syntax errors in oracle, but it can be executed in mysql, and the execution result of this statement is meaningless.
Remember a rule: for statements with group by, select can only be followed by grouping function and fields participating in grouping.
Can multiple fields be grouped together?
Case: find out the highest salary of different positions in each department
select deptno,job,max(sal) from emp group by deptno,job; // It can be seen that the basis of composition group is deptno+job
Find out the maximum salary of each department, and ask to display data with salary greater than 2900.
Step 1: find out the highest salary of each department mysql> select deptno,max(sal) from emp group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000.00 | | 20 | 3000.00 | | 30 | 2850.00 | +--------+----------+ Step 2: find out the 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 | +--------+----------+ //This method is inefficient because it finds out the max(sal) of all departments and finally discards the part. //It's better to get rid of the unused before finding the maximum value. In fact, it is more efficient to use where. It is recommended to use where as much as possible select deptno,max(sal) from emp where sal>2900 group by deptno; Find out the average salary of each department, and ask to display data with salary greater than 2000. mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 10 | 2916.666667 | | 20 | 2175.000000 | +--------+-------------+
7. Summary: how to write a complete DQL statement?
select ... 5 from ... 1 where ... 2 group by ... 3 having ... 4 order by ... 6
