SQL statement operation_ MySQL core query 2 (multi table query)

1 foreign key constraints

1.1 foreign keys

• The foreign key refers to the field in the slave table corresponding to the primary key of the primary table (that is, there is A column in data table A, which points to the primary key of another data table B, and this column in data table A is the foreign key)
  
• Using foreign key constraints can generate a corresponding relationship between two tables to ensure the integrity of the reference of the master-slave table
• Add / remove foreign keys are only available from tables
• Keywords: FOREIGN KEY
• Add foreign key

/*
Syntax format:
1. Add foreign keys when creating a new table
[CONSTRAINT] [Foreign key constraint name] foreign key REFERENCES primary table name (primary key field name)
2. Add foreign key to existing table
ALTER TABLE From the table ADD [CONSTRAINT] [FOREIGN KEY constraint name] FOREIGN KEY (FOREIGN KEY field name) REFERENCES main table (primary key field name);
*/
-- Create master table
create table department(
	id INT PRIMARY KEY auto_increment,
	dep_name VARCHAR(20),
	dep_location VARCHAR(80)
); 
-- Method 1: create employee,Add foreign key constraint
create table employee(
	eid INT PRIMARY KEY auto_increment,
	ename VARCHAR(20),
	sex CHAR(1),
	dept_id INT, -- The foreign key field type should be consistent with the primary key field type of the main table
	-- Add a foreign key constraint. The foreign key points to the primary key of the Department table
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
); 
-- Method 2: add foreign keys to existing tables
alter table emp3 add constraint emp3_dept_fk foreign key(dept_id) references department(id);

• Delete foreign key

-- Delete foreign key
alter table emp3 drop foreign key emp3_dept_fk;

• be careful
  • The foreign key type of the slave table must be consistent with the primary key type of the primary table, otherwise the creation fails
  • When adding data, you should first add the data in the main table
  • When deleting data, you should first delete the data in the slave table, and then delete the data in the main table

1.2 cascade deletion

• Delete the master table and delete the slave table at the same time
• Cascade delete: ON DELETE CASCADE
• Example

-- establish employee,Add cascade delete
create table employee(
	eid INT PRIMARY KEY auto_increment,
	ename VARCHAR(20),
	sex CHAR(1),
	dept_id INT, 
	CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
	-- Add cascade delete
	ON DELETE CASCADE
); 
-- Delete data with department number 2
delete from department where id = 2;

2 Cartesian product

• Cross join queries will not be used because Cartesian products will be generated
• Syntax format

SELECT Field name FROM  Table 1, Table 2;

• Cartesian product
  

3 internal connection

Match the data in the two tables according to the specified criteria. If the data is matched, it will be displayed. If it is not matched, it will not be displayed (the data in both the left and right tables will be displayed)

3.1 implicit inner connection

• Cartesian product + where conditional filtering
• Syntax format

SELECT Field name FROM Left table, Right table WHERE Connection conditions;

• Example

-- Query the category of Gree air conditioner
SELECT
	p.pname,
	c.cname 
FROM
	products p,
	category c 
WHERE
	p.category_id = c.cid AND p.pname = 'Gree air conditioner';

3.2 explicit inner connection

• Using inner join... on is an explicit inner join
• Syntax format

SELECT Field name FROM Left table [INNER] JOIN Right table ON condition
-- inner Can be omitted

• Example

-- Query all commodity information and corresponding classification information
SELECT * FROM products p INNER JOIN category c ON p.category_id = c.cid;
-- Query footwear classification,Name and price of goods with price greater than 500
SELECT
	c.cname,
	p.pname,
	p.price 
FROM
	products p
	INNER JOIN category c ON p.category_id = c.cid 
WHERE
	c.cname = 'Shoes and clothes' AND p.price > 500;

4 external connection

4.1 left outer connection

• Using LEFT OUTER JOIN, OUTER can be omitted
• characteristic
  • Take the left table as the benchmark to match the data in the right table. If the data is matched, the matched data will be displayed
  • If no match is found, the data in the left table is displayed normally, and the display on the right is null
    
• Syntax format

SELECT Field name FROM Left table LEFT [OUTER] JOIN Right table ON condition

• Example

-- Query all commodity information and corresponding classification information
SELECT * FROM category c LEFT JOIN products p ON c.cid = p.category_id ;
-- Query commodity quantity information under each category (group query)
SELECT
	c.cname,
	COUNT( p.pname) 
FROM
	category c
	LEFT JOIN products p ON c.cid = p.category_id 
GROUP BY
	c.cname;

4.2 right outer connection

• Using RIGHT OUTER JOIN, OUTER can be omitted
• characteristic
  • Take the right table as the benchmark to match the data in the left table. If it matches, the matched data will be displayed
  • If no match is found, the data in the right table is displayed normally, and the display on the left is null
    
• Syntax format

SELECT Field name FROM Left table RIGHT [OUTER] JOIN Right table ON condition

• Example

-- Query all commodity information and corresponding classification information
SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;

5 SQL JOINS summary

The content of this article comes from the actual training camp of lague education data analysis