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:
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;
# 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
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;
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) ;
# 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