1, select basic syntax
The select statement is used to select data from the database. The results are stored in a result table called a result set.
Quotation marks and semicolons in the select query statement are English symbols, and the end flag of the query statement is semicolon.
Take dept table as an example to demonstrate:
Description: deptno (department number), dname (Department name), loc (location)
+--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
1. Query single column data: select column name from table name;
select deptno from dept;
2. Query multi column data: select column name 1, column name 2... from table name;
select deptno,dname from dept;
3. Query all data: select column name 1, column name 2... from table name; (list all column names)
Or select * from dept;
select deptno,dname,loc from dept; select * from dept;
4. Alias the queried column: select column name 1 as alias 1, column name 2 as alias 2... from table name;
select deptno as id,dname as name from dept;
If it is written as: select column name 1, column name 2... as alias 1 from table name; (change the last column name in the select statement to an alias)
select deptno,dname as id from dept;
(aliasing can be omitted. If the alias contains spaces or Chinese, enclose the alias in single quotation marks. Aliasing will not change the column name of the original table)
select deptno as 'd id' from dept; select deptno as 'number' from dept;
5. Mathematical operation of column name parameters: (query employee's annual salary)
select ename,sal*12 as year_salary from emp;
2, select condition query
Take emp table (employee table) as an example to demonstrate:
explain:
empno (employee number), ename (employee name), job (job), mgr (superior leader number), hiredate (entry date), sal (monthly salary), comm (allowance), deptno (department number)
+-------+--------+-----------+------+------------+---------+---------+--------+ | 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 | +-------+--------+-----------+------+------------+---------+---------+--------+
Syntax format: select column name 1, column name 2... from table name where condition;
1. = equal to:
select ename from emp where sal = 800;//Query employee ename with sal=800 select sal from emp where job='SALESMAN';//The query job is the employee sal of SALESMAN
2. < > not equal to:
select sal from emp where deptno<>20;//Query sal of employees whose deptno is not equal to 20 select sal from emp where job<>'SALESMAN';//Query employee sal whose job is not SALESMAN
3. < less than:
select ename,job from emp where sal<1000;//Query employee names and jobs with Sal < 1000
4. > greater than:
select ename,job from emp where sal>1000;//Query employee names and jobs with Sal > 1000
5. > = greater than or equal to:
select ename,job from emp where sal>=1000;//Query employee ename and job with Sal > = 1000
6.between a and b: (closed interval, a < B required)
select ename,job from emp where sal between 1000 and 1500;//Query employee names and jobs with sal between 1000 and 1500 select ename,job from emp where sal>=1000 and sal<=1500;//Query employee names and jobs with sal between 1000 and 1500
7.null is not null:
select ename,job from emp where comm is null;//Query employee ename and job whose comm is null
8.and:
select * from emp where job='manager' and sal>2500;//Query the original information that the job is a manager and the sal is greater than 2500
9.or:
select * from emp where job='manager' or job='PRESIDENT';//Query the information of employees whose job is manager or present select * from emp where sal>2000 and (deptno=10 or deptno=20);//Query employee information whose sal is greater than 2000 and deptno is 10 or 20
10.in contains, equivalent to multiple or:
select * from emp where sal in (800,5000);//Query the employee information of sal 800 and 5000
11.not in does not include:
select * from emp where sal not in(800,5000);//Query employees whose sal is not 800 and 5000
12.like fuzzy query:
① % matches any character:
select * from emp where ename like '%o%';//Query employee information contained o in ename select * from emp where ename like '%r';//Query employee information ending with r in ename select * from emp where ename like '%\_%';//Query ename contains_ Employee information (has special meaning and must be translated with \)
② _ Match one character:
select * from emp where ename like '_a%';//Query the employee information of ename whose second letter is a select * from emp where substr(ename,2,1)='a';//Query the employee information of ename whose second letter is a
3, order by sort
Order by column name: used to sort the specified columns. The default is ascending (asc). It can be specified as descending: order by field name desc or ascending: order by field name desc.
1.asc ascending: (default)
select * from emp order by sal asc;//Sort the queried employee information in sal ascending order select * from emp order by sal;//Sort the queried employee information in sal ascending order
2.desc descending order:
select * from emp order by sal desc;//Sort the queried employee information in sal descending order
Advanced: multiple field sorting (secondary sorting)
select ename,sal from emp order by sal asc,ename asc;//Query all the original names of ename,sal, and sal in ascending order. When the SAL is the same, the name will be in ascending order
Sort by the field position of the select query column: (understand: not recommended, robustness is too weak)
select ename,sal from emp order by 2;//Query ename and sal, and the results are sorted by sal (ascending by default)
Comprehensive example: query the names and wages of employees whose wages are between 1500 and 2500, and output them in ascending order of wages. When the wages are the same, they are in ascending order of names.
select ename,sal from emp where sal between 1500 and 2500 order by sal asc,ename asc; //Execution order: from --- > where --- > select --- > order by
4, Data processing function (single line processing function)
Single line processing function: one input corresponds to one output
Multi line processing function: multiple inputs correspond to one output (such as sum)
1.lower to lowercase:
select lower(ename) as ename from emp;//Turn the queried data field to lowercase
2.upper to capital:
select upper (ename) as ename from emp;//Turn the queried data field to lowercase
3.substr substring: (STR (intercepted string, starting subscript, intercepted length))
select substr(ename,1,2) from emp;//Query the first two letters of ename select * from emp where substr(ename,2,1)='a';//Query the employee information of ename whose second letter is a
Initial capital:
select concat(upper(substr(name ,1,1.)) , substr(name ,2 , length (nane) - 1)) as result from t student;
4. Length (field name) takes the length:
select length(ename) as name_len from emp;//Output the length of each name in the ename field
5. Trim (string) to remove spaces:
select * from emp where ename=trim(' KING');//Query employee information whose name is KING
6.round (number, decimal places reserved): round
select round(123.564) as result from dept;//Rounding output 124 select round(123.564,-2) as result from dept;//Rounding output 100 select round(123.564,-1) as result from dept;//Rounding output 120 select round(123.564,1) as result from dept;//Keep one decimal place and output 123.6 select round(123.564,2) as result from dept;//Keep two decimal places and output 123.56
select 'hello' as result from dept;//Output hello with the same number of rows in dept (the default column name is also hello) selecet 1000 as result from emp;//Output 1000 of the same number of rows in emp (the default column name is also 1000)
7.rand() random number:
select rand() from dept;//Random number of production dept equivalent lines (in the range of 0-1)
8. Ifnull (field, replacement value) converts null to a specific value:
In the database, all data values are calculated with null, and the result is null;
(in the database, null is not a value, but represents null, so the result of operation with it is still null)
select ename,sal+comm as sum_sal from emp;//When null participates in the operation, the result is still null
Use ifnull() correctly:
//If (field, 0): if the field is null, treat null as 0 (0 can be replaced by other numbers or letters) select ename,(sal + ifnull(comm,0)) as year_Sal from emp;//Query employee's annual salary and ename
9.case...when...then...when...then...else...end
//When the employee's position is MANAGER, the salary will be increased by 10%. When the position is SALESMAN, the salary will be increased by 50%. Others are normal. (Note: the database will not be modified, but the query result will be displayed as salary 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;
5, Grouping function (multiline processing function)
Multi line processing function features: input multiple lines and output one line of results.
The grouping function can only be used after grouping. Otherwise, the whole table is used as a group by default.
1.count:
select count(ename) as count from emp;//Count the number of employees in the emp table
2.sum:
select sum(sal) as sum from emp;//Count the sum of sal in emp table
3.avg average:
select avg(sal) as avg_sal from emp;//Count the average value of sal in the emp table
4.max:
select max(sal) as max_sal from emp;//Count the maximum sal in the emp table
5.min for minimum value:
select min(sal) as min_sal from emp;//Count the minimum value of sal in the emp table
Precautions for using grouping functions:
① When the grouping function is used, null is automatically processed, and manual processing with ifnul() is not required:
select sum(comm) as comm from emp;//Query the sum of comm s
select count(comm) as count from emp;//Count the number of comms (comm is null and not counted)
② count(*) is different from count (field name). The former counts the number of all row records; the latter counts the number of non null fields in the column. (because it is impossible for each column in a record to be null)
select count(*) from emp;//Count the number of all line records
select count(comm) as count from emp;
③ Grouping functions cannot be used directly in the where condition:
(ERROR 1111 (HY000): Invalid use of group function)
select ename,sal from emp where sal > min(sal);//ERROR 1111 (HY000): Invalid use of group function
④ All grouping functions can be used in one statement:
select count(*),sum(sal),max(sal),min(sal),avg(sal) from emp;
6, group by group query
Syntax format: select field 1, field 2... from table name group by column name
Put all the keywords together: select... from... where... group by... having... order by
Keyword execution order: from - > where - > group by - > having - > Select - > order by
First, query data from a table, then filter out valuable data through where conditions, and group these valuable data. After regrouping, you can use having to continue filtering. select to query. Last sort output!
Key conclusions:
① In a select statement, if there is a group by statement, the select can only be followed by the fields participating in the grouping and the grouping function, and the others cannot be followed (meaningless).
② In group query, group by can be used with multiple column names (multi-level grouping of multiple data). If there are filter criteria used after group by, you must use havIng statement to filter.
1. Query the total salary of each position:
select job,sum(sal) as sum_sal from emp group by job;
2. Query the maximum salary in each department:
select deptno,max(sal) from emp group by deptno;
3. Query the salary of each department and different jobs:
select deptno,job,max(sal) from emp group by deptno,job order by deptno;//Here, the order by sorting operation can only be placed after group by, and the order cannot be reversed
4. Query the maximum salary of each department and output the department number with the maximum salary greater than 3000:
select deptno,max(sal) from emp group by deptno having max(sal)>3000;//When there are many database data, the efficiency is relatively low
Optimization strategy: where and having. Give priority to where. Where can't be completed, and then choose having.
select deptno,max(sal) from emp where sal>3000 group by deptno;//It is more efficient to use where to filter conditions before grouping
Advanced exercise of comprehensive query:
1. Query the average salary of each department and output the department number and average salary whose average salary is greater than 2000
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;//You can only use having for conditional filtering here
5. Query the average salary of each position. If the average salary is greater than 1500, it is required to be sorted in descending order according to the average salary except for the MANAGER position.
select job,avg(sal) as avg_sal from emp where job<>'manager' group by job having avg_sal > 1500 order by avg_sal desc;
