A foreign key

One-to-many, many-to-one, and one-to-one relationships exist between tables. Relationships between tables are maintained by primary keys and foreign keys. Foreign keys can be constrained by the database or by application logic.

# category tablecreate table category(
	cid int primary key auto_increment,
	cname varchar(32)); Table # goodscreate table product(
	pid int primary key auto_increment,
	pname varchar(32),
	price int,
	category_id int
);
Copy the code

Suppose we have two tables ** “category table” and “commodity table” **. To indicate which category the item belongs to, we would normally add a column to the commodity table for the cid information of the category. This column is called: foreign key

  • Foreign key features:

    • The secondary foreign key value is a reference to the primary key of the primary table.
    • The foreign key type of the secondary table must be the same as the primary key type of the primary table.

  • SQL code declares and removes foreign key constraints

# the statement:alter tableFrom the tableadd [constraint[foreign key name]foreignKey (foreign key field name from table)referencesPrimary table (primary key of primary table); # An example # Add foreign keysalter table product 
add constraint fk_product_category foreign key (category_id) referencescategory(cid); # delete foreign keyalter tableFrom the tabledrop foreignKey Specifies the name of the foreign keyCopy the code

Multi-table query

Initial data is as follows:

Table 1: the category



Table 2: products

select * from A,B;
Copy the code

Run the code

select * from category,products
Copy the code

The result is the Cartesian product:

That is, each column in Table 1 is combined with all the columns in Table 2 respectively, resulting in 3∗9=27 3*9=27 3∗9=27



The following figure shows seven usages related to LEFT JOIN, RIGHT JOIN, INNER JOIN, and OUTER JOIN.

In the connection

Inner join query (use the keyword inner join – inner can be omitted)

  • Implicit inner join:Select * from A,B where A;
  • Display internal connection:Select * from A inner join B on;
Implicit inner join
SELECT * FROM category c,products p WHERE c.cid = p.category_id;
-- Shows the inner connection
SELECT * FROM category c INNER JOIN products p on c.cid = p.category_id;
Copy the code

Outer join

  • Left outer join: left outer join

    • Select * from A left outer join B on;

  • Right outer join: right outer join

    • Select * from A right outer join B on;

The subquery

Subquery: the result of a SELECT statement as part of the syntax of another SELECT statement (query condition, query result, table, etc.)

-- Subquery type 1 (used as query condition value)
select 
	* 
from
	products p
where
	p.category_id = (SELECT cid from category where cname='Cosmetics') -- 'c003'


-- Subquery type 2 (used as a table)
select 
	p.*
FROM
	products p, (select * from category where cname='Cosmetics') c
WHERE
	p.category_id = c.cid;
Copy the code