Part I multi table query

Complete syntax of single table query:
Select distinct field name
from 'table name'
where 'filter conditions before grouping'
group by 'field name'
having 'filter conditions after grouping'
order by 'default ascending sort ase|desc'
limit display number

Execution order of keywords:
**
1,from
2,where
3,group by
4,having
5,select
6,distinct
7,order by
8,limit
**
Prepare an emp table before you start:

Insert code here create table emp(
  id int not null unique auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', #Mostly men
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #One department, one room
  depart_id int
);

#insert record
#Three departments: teaching, sales and operation
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','The first handsome image of Zhangjiang',7300.33,401,1), #Here is the Teaching Department
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tank','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('nick','male',18,'19000301','teacher',30000,401,1),
('sean','male',48,'20101111','teacher',10000,401,1),

('Crooked','female',48,'20150311','sale',3000.13,402,2),#Here is the Sales Department
('Ya ya','female',38,'20101101','sale',2000.35,402,2),
('Tintin','female',18,'20110312','sale',1000.37,402,2),
('Stars','female',18,'20160513','sale',3000.29,402,2),
('sound made by a hen','female',28,'20170127','sale',4000.33,402,2),

('Zhang Ye','male',28,'20160311','operation',10000.13,403,3), #Here is the operation Department
('Cheng bite Jin','male',18,'19970312','operation',20000,403,3),
('Cheng Chi Yin','female',18,'20130311','operation',19000,403,3),
('Cheng bite Cu','male',18,'20150411','operation',18000,403,3),
('Cheng Ting tie','female',18,'20140512','operation',17000,403,3)
;

I. where: filter conditions

# 1. Query data with id greater than or equal to 3 and less than or equal to 6
select id,name from emp where id >= 3 and id <= 6;
select *  from emp where id between 3 and 6;  

# 2. Query data with salary of 20000, 18000 or 17000
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);  # Abbreviation

# 3. Query the name and salary of the employee whose name contains the letter o
# When you first contact mysql query, it is recommended that you spell out your sql statements according to the priority order of the query
"""
//First check which table from emp
//Then according to what conditions to check where name like '% o%'
//select name,salary
"""
select name,salary from emp where name like '%o%';

# 4. Query the name and salary of an employee consisting of four characters
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;

# 5. Query data with id less than 3 or greater than 6
select *  from emp where id not between 3 and 6;

# 6. Query the data whose salary is not in the range of 20000180017000
select * from emp where salary not in (20000,18000,17000);

# 7. Query the employee name and position name whose position description is empty. For null, the equal sign cannot be used, only is can be used
select name,post from emp where post_comment = NULL;  # Query is empty!
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

group by

# Data grouping application scenario: average salary of each department, gender ratio, etc

# 1. Group by Department
select * from emp group by post;  # After grouping, the first data of each group is taken out
select id,name,sex from emp group by post;  # Verification
"""
//Setting SQL mode to only full group by means that in the future, only the basis for grouping can be obtained,
//You should not get the value of a single element in a group. In that case, grouping is meaningless, because not grouping is the random access to the information of a single element
"""
set global sql_mode="strict_trans_tables,only_full_group_by";
# Relink client
select * from emp group by post;  # Report errors
select id,name,sex from emp group by post;  # Report errors
select post from emp group by post;  # Get department information
# Emphasis: as long as you group, you can no longer "directly" find a single data information, only get the group name


# 2. Get the maximum wage of each department  
# Statistics of data within a group by group
# Maximum wage per department
select post,max(salary) from emp group by post;
# Minimum wage per department
select post,min(salary) from emp group by post;
# Average wage per department
select post,avg(salary) from emp group by post;
# Total wages per department
select post,sum(salary) from emp group by post;
# Number of people in each department
select post,count(id) from emp group by post;

# 3. Query the Department names after grouping and the names of all students in each department
# Group ABCD concat (used after grouping) can be used not only to display fields but also to splice strings in addition to grouping
select post,group_concat(name) from emp group by post;

select post,group_concat(name,"_SB") from emp group by post;

select post,group_concat(name,": ",salary) from emp group by post;

select post,group_concat(salary) from emp group by post;


# 4. Supplement concat (when not grouped) to splice strings for better display effect
select name as Full name,salary as salary from emp;
select concat("NAME: ",name) as Full name,concat("SAL: ",salary) as salary from emp;

# Supplementary as syntax can be used to alias fields or tables
select emp.id,emp.name from emp as t1; # Error reported because the table name has been changed to t1 by you
select t1.id,t1.name from emp as t1;

# Four operations of query
# Query everyone's annual salary
select name,salary*12 as annual_salary from emp;
select name,salary*12 annual_salary from emp;  # as can be omitted

Three, having

select Query field 1,Query field 2,... from Table name
		where Filter condition
		group by group by

# The grammar says so, but the order of execution is different
from
where
group by
select

The syntax format of having is the same as where, except that having is a filter after grouping. That is to say, where can't use aggregate function, but having can!

IV. distinct: de duplication

# De duplication of repeated display data
select distinct post from emp;

Five.
order by

select * from emp order by salary asc; #Default ascending
select * from emp order by salary desc; #Descending order

select * from emp order by age desc; #Descending order

#First in descending order of age, and then in ascending order of salary when young
select * from emp order by age desc,salary asc; 

# The average wages of employees aged over 10 in each department are counted, and the departments with average wages greater than 1000 are retained, and then the average wages are sorted
select post,avg(salary) from emp
    where age > 10
    group by post
    having avg(salary) > 1000
    order by avg(salary)
    ;

Six, limit

# Limit the number of display items
select * from emp limit 3;
# Query the details of the highest paid person
select * from emp order by salary desc limit 1;

# Paging display
select * from emp limit 0,5;  # The first parameter indicates the starting position, and the second parameter indicates the number of entries, not the index position
select * from emp limit 5,5;

7. Regular query

select * from emp where name regexp '^j.*(n|y)$';

Part II multi table query

Preparations, creating tables

#Building tables
create table dep(
id int,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#insert data
insert into dep values
(200,'technology'),
(201,'Human resources'),
(202,'Sale'),
(203,'Operate');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',38,201),
('nick','female',28,202),
('owen','male',18,200),
('jerry','female',18,204)
;

# Why do we need to divide tables at the beginning? It's just for the convenience of management. There are many tables on the hard disk, but when it comes to memory, we should put them together into one more table for query