Customers form :
Orders form :
Internal connection :
Internal connection is based on cross connection
Only the data rows in the join table that match the join criteria are listed , Records that do not match will not be listed .
Implicit grammar ： select * from customers,orders where customers.id=orders.customer_id;
Explicit grammar ： select * from customers as c inner join orders as o on c.id=o.customer_id;
External links ：
The outer link is based on a table , Other table information is spliced , If you have it, put it together , If not shown null; The outer link is divided into left outer link and right outer link .
Left external connection : The table on the left of the keyword is used as the base table for splicing .
select * from customers as c left join orders as o on c.id=o.customer_id;
Right outer connection : Take the table to the right of the keyword as the base table
select * from orders as o right join customer as c on c.id=o.customer_id;
Subquery ： Include nested subqueries , Related subquery .
nested subqueries ：
The execution of internal queries is independent of external queries , Internal queries are executed only once , After execution, the result is used as the condition of external query （ The subquery statements in nested subquery can be taken out and run separately .）
for example ： query id by 1 The students who have been taught by our teachers .
select * from students where id in（select student_id from teacher_student
Related subquery ：
The execution of the internal query depends on the data of the external query , Every time the external query is executed , The internal query is also executed once . Each time, the external query is executed first , Get a tuple from the external query table , Passes the data in the current tuple to the internal query , The internal query is then executed . Based on the results of the internal query execution , Determines whether the current tuple satisfies the where condition , If so, the current tuple is the record that meets the requirements , Otherwise, it does not meet the requirements . then , The external query continues to fetch the next tuple data , Do the above , Until all tuples are processed .
for example ： The score of a student whose score is greater than average in each subject .
select * from score as a where a.score>(select avg(b.score) from score as b
Common functions ：
1,count()： Find the total number of rows that satisfy the conditions of the column .
for example ： Count the number of people in a class .
select count(id) from class one;
2,sum()： Summation .
for example ： Find the average score of a class .
select sum（math）/count(*) from student ;
3,avg()： Average .
for example ： Find the average score of Mathematics
select avg(math) from student;
4,max()\min(): Maximum value , minimum value .
for example ： Find the maximum and minimum of mathematics achievement .
select max（math）,min（math） from student;
group by ： grouping
GROUP BY The real purpose of clauses is to work with various aggregate functions . It is used to group the query data .
The meaning of grouping is ： Treat multiple records with the same value as a group of records , Finally, only one record is output . Group functions ignore null values .
create table t_order(id int primary key,product varchar(20),price float(8,2));
insert into t_order values(1,‘xiaomi’, 1000);
insert into t_order values(2,‘xiaomi’,1100);
insert into t_order values(3,‘huawei’,2200);
insert into t_order values(4,‘apple’,8200);
for example ：
(1) Categorize each item in the order .
select product sum(price) from t_order group by product;
(2) The total price of the query product is greater than 3000 Products of .
select product ,sum（price） from t_order group by product having
having \ where The difference between
①,where and having They are all used for conditional restrictions ,
②,WHERE It's in groups (group by) Before condition filtering ,
③,HAVING Clauses are grouped in groups (group by) After that, conditional filtering is carried out ,
④,WHERE Aggregate functions cannot be used in clause ,HAVING Clause can use aggregate functions .
⑤,HAVING Clause is used to filter the grouped results
having sum(price)>2000 amount to Take it The column name is sum(price) Go to the inquiry .