1, Simple search

1. Fuzzy condition query

For search function
Example: find out which names contain the letter e

select *from emp where ename like '%e%';  

Exercise: query the employees whose names end with e

select *from emp where ename like '%e';

Query the employee whose last character in the name is e

select *from emp where ename like '%e_';

%Match any character > = 0
_ Match any character = 1
The above must be used in conjunction with like

2. Paging query

There is too much data in the query results, which can not be displayed at one time. It can be displayed in pages
Two known conditions are required: the current page number and the amount of data per page
Algorithm: query value at the beginning of each page = (current page number - 1) * amount of data per page
Syntax: select *from emp limit the value to start the query, and the amount of data per page;
Exercise: assuming that 5 pieces of data are displayed on each page, query the data of the first four pages
Page 1: select *from emp limit 0,5;
Page 2: select *from emp limit 5,5;
Page 3: select *from emp limit 10,5;
Page 4: select *from emp limit 15,5;
Note: the value to be queried and the amount of data on each page must be numeric and cannot be quoted

2, Complex query

1. Aggregate query / group query

Example: query the quantity of all employees

select count(*) from emp;

Exercise: using the number column to query the number of employees

select count(eid) from emp;(Primary key columns are recommended)

Exercise: using the department number column to query the number of employees

select count(deptId) from emp;

Exercise: query the total salary of male employees

select sum(salary) from emp where sex=1;

Exercise: find out the average salary of department No. 20

select avg(salary) from emp where deptId=20;

Exercise: query the maximum salary of female employees

select max(salary) from emp where sex=0;

Exercise: query the birthday of the oldest employee

select min(birthday) from emp;

Generally, grouping queries are only used to query aggregate functions and grouping conditions
Example: query the number of male and female employees and the total salary

select count(eid),sum(salary),sex from emp group by sex;

Exercise: find out the average wage, maximum wage and minimum wage of each department

select avg(salary),max(salary),min(salary),deptId from emp group by deptId;

Function: it is a function body that needs to provide several data and return results.
Aggregate function
count()/sum()/avg()/max()/min()
Sum of quantity average max min
year() gets the year in the date
Example: query the year of birth of all employees

select year(birthday) from emp;

Exercise: what are the employees born in 1993

select * from emp where year(birthday)=1993;

2. Sub query

Example: query the highest paid employee
Step 1: query the maximum salary value
Step 2: query the employee corresponding to the highest salary

select * from emp where salary=(select max(salary) from emp);

Subquery: a combination of multiple SQL commands that takes the result of one command as the query condition of another
Exercise: find out which employees are paid more than king

select *from emp where salary>(select salary from emp where ename='king');

Exercise: find out the employees born in the same year as tom

select *from emp where year(birthday)=(select year(birthday) from emp where ename='tom') and ename!='tom';

3. Multi table query

The columns to be queried are distributed in multiple tables, provided that there are associations between tables
Example: query the names of all employees and their department names

select emp.ename,dept.dname from emp,dept where emp.daptId=dept.did;

Inner connection

select ename,dname from emp inner join dept on deptId=did;Consistent with previous query results

All contents in the left table of the left outer connection are displayed

select ename,dname from emp left outer join dept on deptId=did;

All records in the left table are displayed. Which table is written first is the left table
Right outer connection

select ename,dname from emp right outer join dept on deptId=did;

All records in the table on the right are displayed. Which table is written later is the right table
The outer keyword in the left and right outer joins can be omitted
Full connection
full join ...on
All records in the left and right tables are displayed at the same time
mysql does not support full connection
union
union merges the same records
union all does not merge the same records

(select ename,dname from emp left outer join dept on deptId=did)
union
(select ename,dname from emp right outer join dept on deptId=did)

Full connection solution: combine the left external connection and the right external connection to merge the same records

Basic steps of learning a programming language

1. Understand the background knowledge: history, current situation, characteristics and application scenarios
2. Build a development environment and write hello world
3. Variables and constants
4. Data type
5. Operator
6. Logical structure
7. General applet
8. Functions and objects
9. Third party libraries and frameworks
10. Personal projects