code :
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 .

grammar :
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 .

grammar :

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

grammar :
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
where t_id=1);

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
where a.cou_id=b.cou_id);

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 .

Example :
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
sum(price)>3000;

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 .

Technology
©2019-2020 Toolsou All rights reserved,
One is called “ Asking for the train ” A small village Finally got the train SparkSQL Achieve partition overlay write Character recognition technology of vehicle license plate based on Neural Network c++ Memory Pointer out of bounds detection mechanism _CrtMemBlockHeade Change one's mind ! Tesla starts to deliver made in China to European market Model 3Qt study 7—— Modal and modeless dialog boxes JavaScript Medium Call and ApplyJAVA Convert a string to a numeric type k8s Entry to give up --k8s Key concepts Count the number of letters (java Language implementation )