Things to be determined before inquiry

• Query several tables
• Link condition of query
• Fields used in query
• Query criteria

1, Cross connect query

The results of multi table query and cross join query will produce Cartesian product, which can not be used

The result of the query is to multiply the data of the two tables

select * from category,products; --Cross connect query

2, Internal link query

Features: match the contents of two tables by specifying conditions, and those that do not match will not be displayed

Implicit inner connection

Syntax format: select field from left table, right table where link condition

Explicit inner join

Syntax format: select field from left table [inner] join right table on link condition

inner can be omitted

1# query all commodity information and corresponding classification information

--Implicit connection query
select * from products,category where category_id = cid; 

--Explicit inner connection query
select * from products inner join category on category_id = cid;

2# query the commodity name, price and commodity classification information in the commodity table

--Implicit inner connection
select
    p.pname,
    p.price,
    c.cname
from products p,category c
where p.category_id=c.cid;

--Explicit inner join
select 
    p.pname,
    p.price,
    c.cname
from product p inner join category c on p.category_id = c.cid;

3# query under which category does the grid air conditioner belong

--Implicit inner connection
select 
    p.pname,
    c.cname
from products p,category c
where p.category_id=c.cid AND p.pname='Gree air conditioner';

--Show internal connections
select 
    p.pname,
    c.cname
from products p inner join category c on p.category_id=c.cid;

4# query the name and price of goods with a price greater than 500 under the classification of shoes and clothing

--Implicit inner connection
select
    p.pname,
    p.price,
    c.cname 
from products p,category c 
where p.category_id = c.cid and p.price>500 and c.cname='Shoes and clothes';

--Explicit inner join
select
    p.pname,
    p.price,
    c.cname 
from product p 
inner join category c on p.category_id=c.cid
where p.price>500 and c.cname='Shoes and clothes';

3, External link query

Left outer connection

Keywords: left [outer] join (outer can be omitted)

Syntax format: select field name from left table left join right table on link condition

Features: Based on the left table, the data matching the right table will be displayed normally if it can be matched

If there is no match, the data in the left table is displayed normally and the data in the right table is null

Right outer connection

Keyword: right [outer] join (outer can be omitted)

Syntax format: select field name from left table right join right table on link condition

Features: Based on the right table, the data matching the left table will be displayed normally if it can be matched

If there is no match, the data in the right table is displayed normally and the data in the left table is null

1# query the number of goods under each category

--Left outer link
select 
    c.cname,
    count(p.pid)
from category c left join products p on c.cid = p.category_id
group by c.cid;

4, subQuery

The result of a select statement is part of another select statement

Subquery features: subqueries must be placed in parentheses

Sub query is used as the condition of parent query (more often)

Sub query classification:

where type sub query: use the results of the sub query as the comparison criteria of the parent query

from sub query: use the results of the sub query as a table

exists type sub query: the result of the query is single column and multiple rows. The result of the sub query can be used as the condition in the in function of the parent query

Syntax format: select field name from table name where field in (subquery);

Sub query summary:

If the sub query is a field (single column), the condition is made after where

If it is multiple fields (multiple columns), it will be used as a table (alias)

1# query the information of the commodity with the highest price

--Sub query as query criteria
select * from product where price=(select max(price) from products); --Subquery

2# query the commodity name and price under the category of cosmetics

--Sub query as query criteria
select 
    pname,
    price 
from products 
where category_id=(select cid from category where cname='Cosmetics');

3# query the goods whose price is less than the average price

--Sub query as query criteria
select * from products where price<(select avg(price) from products);

4# query the information of commodities with price greater than 500 in commodities, including commodity name, commodity price and classification attribute of commodities

--Mode 1
select 
    p.pname,
    p.price,
    c.cname
from products p 
inner join category c on p.category_id = c.cid
where p.price>500;

--Method 2: use the sub query results as a table
select 
    p.pname,
    p.price,
    c.cname
from products p
inner join (select * from category) c on p.category_id = c.cid
where p.price>500;

--Method 3: use the sub query results as a table
select
    p.pname,
    p.price,
    c.cname
from products p
inner join (select cid,cname from category) c on p.category_id = c.cid
where p.price>500;

5# query the classification of goods with price less than 2000

--Take the result of the child query as the result of the parent query in Conditional use in function
select * from category 
where cid in (select distinct category_id from products where price<2000);

6# query all commodity information of household appliances and shoes and clothing

select * from products
where category_id in (select cid from category where cname in ('household electrical appliances','Shoes and clothes'));

The following are the tables and data to be used

--Create a file named db2 Database of
create database db2 character set utf8;

--Create classification table
create table category(
    cid int primary key auto_crement,
    cname varchar(50)
);

--Create item table
create table products(
    pid int primary key auto_crement,
    pname varchar(50),
    price decimal(21,6),
    flag varchar(2),
    cid int
);

--Insert classification data
INSERT INTO category(cname) VALUES('household electrical appliances');
INSERT INTO category(cname) VALUES('Shoes and clothes');
INSERT INTO category(cname) VALUES('Cosmetics');
INSERT INTO category(cname) VALUES('automobile');

--Insert item data
INSERT INTO products(pname,price,flag,category_id) VALUES('Millet TV',5000,'1',1);
INSERT INTO products(pname,price,flag,category_id) VALUES('Gree air conditioner',3000,'1',1);
INSERT INTO products(pname,price,flag,category_id) VALUES('Midea refrigerator',4500,'1',1);

INSERT INTO products(pname,price,flag,category_id) VALUES('Basketball shoes',800,'1',2);
INSERT INTO products(pname,price,flag,category_id) VALUES('Sports pants',4500,'1',2);
INSERT INTO products(pname,price,flag,category_id) VALUES('T Shirt',300,'1',2);
INSERT INTO products(pname,price,flag,category_id) VALUES('pizex',2000,'1',2);

INSERT INTO products(pname,price,flag,category_id) VALUES('GHb ',800,'1',3);
INSERT INTO products(pname,price,flag,category_id) VALUES('Dabao',200,'1',3);