Interpretation of common SQL statements

Add data supplement :

Insert a query result into another table

create table student(name char(10),gender int);
insert into student values("jack",1);
insert into student values("rose",0);
create table student_man(name char(10),gender int);
insert into student_man select * from student where gender = 1;

be-all select keyword

Query a table ：     select * from Table name ;

Query the specified field ：select Field 1, Field 2, Field 3….from Table name ;

select distinct *  from table_name
	where 
	group by 
	having 
	order by
	limit a,b

 There must be : 
select 
	*  You can change it to any one or more field names   
	from
	table_name
# Be careful :  The order of keywords is fixed and cannot be changed at will 

where Conditions

where Conditions of the query ：select  Field 1, Field 2, Field 3 frome Table name where Conditional expression ;

select * from  table_name
where 
where  The back can be  

1. Comparison operator  
	>  <  >=  <=  =  != 
	
2. member operator 
	in  not in     There's a set

3. Logical operators  
	and or not 	
	not  Put it in front of the expression    and  and  or  Put it between two expressions  
4. Fuzzy query  
	like 
	%  Express   Any number of any character 
	_  Represents an arbitrary character 

 #  Please check   Small surname    Mathematics is less than  80  branch    also    English  > 20 branch     The man's   Math scores 
select math,name  from stu where math < 80 and english > 20 and name like " Small %"; 
select * from t_studect where id=1;
select * from t_student where age>22;

distinct Remove duplicate records

select distinct * from stu; 
#  Note that only if all fields in the query result are the same   It's a duplicate record 

Specified field

1. An asterisk indicates all fields

2. Manually specify the fields to query

3. It can also be four operations

4. Aggregate functions # Please check People who pass English average

select name,(math+english) / 2  average  from stu where english >= 60;

Take the alias

select name,math+english as  Total score  from stu where name = " zhaoyun ";

as  It can be omitted  

Statistical function

​ Also called aggregate function

​ Calculate a pile of data to get a result

Sum up     sum( Field name )
  The average  avg( Field name )
  Maximum  max( Field name )
  minimum value  min( Field name )
  Number     count( Field name )    
  The field name can use * Instead of   In addition, if the field is empty, it will be ignored

Can be used in   Location of field   Or after the group   
for example : Look up the average salary of everyone  
select avg(salary) from emp

Wrong cases : Check the name of the highest paid person  

select name,max(salary) from emp; 
	# The first one displayed by default name   because name There are many lines    and max(salary)  There is only one line      The number of rows in the two columns does not match 
	#  You shouldn't write that   Logic error 
select name from emp where salary = max(salary);
	#  Report errors   
	#  reason :  Pseudo code 
 for line in file:
       if salary = max(salary)  # 
    # analysis   where  Read a line that meets the conditions   ,max() First get all the data   To find the maximum ,
    # Here, because the reading is not completed, all cannot be   Find the maximum 
# Conclusion   where  Aggregate functions cannot be used later  

group by

group by Property name [having Conditional expression ][with rollup]

group It means grouping That is, a whole is divided into different parts according to a certain feature or basis

Why group Grouping is for statistics , For example, count how many men There are several women

grammar : select xxx from table_name group by Field name ;

 demand : Count how many people there are in each gender  
select sex,count(*) from emp group by sex;

 demand :  Query how many... Are there for each gender   And display the name 
select name,sex,count(*) from emp group by sex;

# mysql 5.6 Next    The result of the query is name Only the first... Under this group is displayed   
# 5.7 The above error is reported directly  ,5.6 You can also turn on this function manually   

#  We can use group_concat  Fields outside the group   Make a mosaic  , But it doesn't make sense 
#  If you want to query all the information under a certain personality   Use it directly where  that will do   

# Conclusion :  Only in group by  The following fields can appear in select Behind 

having

​ Used for filtration , But with the where The difference is ,having Use after grouping

Case study :

#  The average wage is greater than 500 Department information  
select dept,avg(salary) from emp  group by dept having avg(salary) > 5000;


# Inquire about   The number of people in the Department is less than 3 Of   Department name   Name of personnel   Number of personnel 

select dept,group_concat(name),count(*) from emp group by dept having count(*) < 3;

order

Sort by a field

 grammar :
select * from table_name order by  Field name ;
#  The default is ascending 

#  In descending order  
select * from table_name order by  Field name  desc;

#  Multiple fields    The first one is the same as the second one     asc  Expressing ascending order 
select * from table_name order by  Field name 1 desc, Field name 2 asc;


 Case study :
select * from  emp order by salary desc,id desc;

limit ( Mainly used for data paging )

Used to limit the number of records to display

limit Paging query ：select Field 1, Field 2,…from Table name limit initial position , Record number ;

 grammar 1:
select * from table_name limit  Number ;
 grammar 2:
select * from table_name limit  The starting position , Number ;


#  Query the first three  
select * from  emp limit 3;

#  Start with number three   Inquire about 3 strip    3-5
select * from  emp limit 2,3;


#   Be careful : The starting position   from 0 Start 

#  Classic usage scenarios : Pagination display   
1. Number of entries per page    a  = 3
2. Specify the current number of pages    b = 2
3. Calculate the starting position    c = (b-1) * a      


select * from emp limit 0,3;
select * from emp limit 3,3;
select * from emp limit 6,3;


# django  Provides off the shelf paging components    But it first queries all the data   Drop it on the list   Then take out the data     In this way, if the amount of data is too large, there may be problems  

Subquery

​ Take the result of one query statement as the condition or data source of another query statement

​ When we can't find the desired data at one time, we need to use sub query

in Keyword subquery

​ When the inner layer queries ( In brackets ) When there are multiple results , Out of commission = Must be in , In addition, an external subquery must contain only one column of data

​ demand : Specify a department name , Obtain the information of all employees under the changed department

1. Query out   Average age   Greater than 25 Department number of 

select  dept_id from emp  group by  dept_id  having avg(age) > 25;



2. Then query the name of the department according to the number   

select name from  dept where id in (select  dept_id from emp  group by  dept_id  having avg(age) > 25);

 The idea of subquery :
1. To analyze   Find the final data   What are the steps  
2. Write the corresponding... According to the steps sql sentence 
3. Put the... Of the previous step sql The statement is dropped to the next sql Statement as a condition  

exists Keyword subquery

When the inner layer queries When there is a result The outer layer will execute

  Case study :
 select* from dept where exists (select * from dept where id = 1);
 #  Because the inner query produces results   therefore   An outer query was executed dept All data for  

Multi-table query

Cartesian product query

select * from   table1,table2,......

#  The result of Cartesian product query will have a lot of wrong data, that is , Data association error !
 Add filter conditions   From the foreign key value of the table   be equal to   The primary key value of the main table 

#  And duplicate field information will be generated    For example, in employees   Department number    and   Department table id Field  
 stay select  After that, specify the name of the field to query  

 Case study :
select  dept.name  department  ,dept.id  Department number ,emp.name  full name ,emp.id  Employee number ,sex from emp ,dept where dept.id = dept_id;

Internal connection query :

It's essentially a Cartesian product query

 grammar :
select * from  table1 inner join table2;
 Case study :
select * from  emp inner join dept where dept_id = dept.id;

inner It can be omitted 
select * from  emp join dept where dept_id = dept.id;

Left outer connection query

The table on the left should be fully displayed whether it can be matched or not

The one on the right shows only the records on the matching

 demand :  To query the information of all employees and their departments  
select * from emp left join dept on dept_id = dept.id;
 Be careful :  Cannot use... In external join query where  keyword   You have to use on Specifically for table correspondence    

Right outer connection query

The table on the right should be fully displayed whether it can be matched or not

The one on the left shows only the records on the match

 demand :  To query all departments and their corresponding employee information  
select * from emp right join dept on dept_id = dept.id;

All external connection query

Whether the match is successful or not The data of both tables should be displayed

 

 demand : Query the correspondence between all employees and all departments   
select * from emp full join dept on dept_id = dept.id;

 Be careful :mysql External connection not supported  


 We can   The result of the left outer connection query    and   The result of the right outer connection query   Make a merger   
select * from emp left join dept on dept_id = dept.id
union
select * from emp right join dept on dept_id = dept.id;

 
union Usage of :
select * from emp
union 
select * from emp;

# union Duplicate records will be automatically removed   
# union all  Don't repeat  


select sex,name from emp 
union 
select * from dept;
#  Be careful   union  Two query results must be guaranteed   Same number of columns    It is generally used when the structures of multiple results are completely consistent 

summary : External connection query What is found is a record without corresponding relationship , But such data is inherently problematic , Therefore, the most commonly used is inner connection query

Inner connection means Only records that match successfully are displayed

External connection If there is no match, it should also be realized

Multi table query case :

create table stu(id int primary key auto_increment,name char(10));

create table tea(id int primary key auto_increment,name char(10));

create table tsr(id int primary key auto_increment,t_id int,s_id int,
foreign key(s_id) references stu(id),
foreign key(t_id) references tea(id));

insert into stu values(null," Zhang San "),(null," Li Si ");
insert into tea values(null,"egon"),(null,"wer");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);


#egon Who did the teacher teach ? 
select tea.name,stu.name from tea join tsr join stu
on 
tea.id = t_id and stu.id = s_id
where tea.name = "egon";


#  Sub query implementation  
select * from stu where id in (select s_id from tsr where t_id = (select id from tea where name = "egon"));

Subquery

Multi-table query

The cartesian product Internal connection External connection

Internal connections are most commonly used

Usually a requirement You can use a continuous watch It's fine too Subquery