Learning goals

Ability to use inner joins for multi-table queries
Ability to use outer joins for multi-table queries
Ability to use subqueries for multi-table queries

Chapter 1 Relationships between tables

1.1 Relationships between tables

One-to-one relationship:
- In the actual development of the application is not much. Because one-to-one can create a table.
- Common examples: ID card form and employee information form
One-to-many relationship:
- Common examples: customers and orders, categories and goods, departments and employees, provinces and cities
Many-to-many relationships:
- Common examples: students and classes, users and characters, actors and movies, goods and orders

1.2 Foreign Key Constraints

We now have two tables “Category table” and “Commodity table”. To indicate which category an item belongs to, normally we will add a column to the commodity table for the cid information of the category, called: foreign key

In this case, “category table” is called: primary table, “CID” is called the primary key. ‘Commodity table products’ is called: from table, and category_id is called foreign key. We describe the primary foreign key relationship by the primary key of the primary table and the foreign key of the secondary table, which leads to the relationship between tables.

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.

Declare foreign key constraints

Grammar:

alter tableFrom the tableadd [constraint[foreign key name]foreignKey (foreign key field name from table)referencesPrimary table (primary key of primary table);Copy the code

[Foreign key Name] Used to delete foreign key constraints. You are advised to end with _fk

alter tableFrom the tabledrop foreignKey Specifies the name of the foreign keyCopy the code

Purpose of using foreign keys:
- Ensure data integrity
  - Consider adding data from tables
  - Consider primary key deletion data

The table structure is provided as follows:

Classification of #
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50));Table # goods
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2), # Shelves mark: 1 indicates shelves, 0 indicates removal
  category_id VARCHAR(32));Copy the code

Initialize data

# classification
INSERT INTO category(cid,cname) VALUES('c001'.'goods');
INSERT INTO category(cid,cname) VALUES('c002'.'dress');
INSERT INTO category(cid,cname) VALUES('c003'.'Cosmetics');
# goods
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001'.'lenovo'.5000.'1'.'c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002'.'海尔'.3000.'1'.'c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003'.'thor'.5000.'1'.'c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004'.'JACK JONES'.800.'1'.'c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005'.Jeanswest.200.'1'.'c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006'.'Playboy'.440.'1'.'c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007'.'the eu'.2000.'1'.'c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008'.Chanel.800.'1'.'c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009'.'Suitable herb'.200.'1'.'c003');
Copy the code

Chapter two: Actual practice of multi-table relation

2.1 Actual Combat 1: Province and city

Scheme 1: Multiple tables, one to many

Create a province table
create table province(
	pid int PRIMARY KEY,
	pname varchar(32), -- Name of province
	description varchar(100) Description -
);

Create city table
create table city (
	cid int PRIMARY KEY,
	cname varchar(32), -- City name
	description varchar(100), Description -
	province_id int.CONSTRAINT city_province_fk foreign key(province_id) references province(pid)
);
Copy the code

Scheme 2: a table, self-associated one-to-many

create table area (
	id int PRIMARY key AUTO_INCREMENT,
	`name` varchar(32),
	description varchar(100),
	parent_id int.CONSTRAINT area_area_fk FOREIGN KEY(parent_id) REFERENCES area(id)
);

INSERT into area values(null.'Liaoning'.'This is a province'.null);
INSERT into area values(null.'Dalian'.'This is a city'.1);
INSERT into area values(null.'Shenyang'.'This is a city'.1);
INSERT into area values(null.Hebei Province.'This is a province'.null);
INSERT into area values(null.'Shijiazhuang'.'This is a city'.4);
INSERT into area values(null.'Baoding'.'This is a city'.4);
Copy the code

2.2 Actual Combat 2: Users and Roles

Many-to-many relationships

- the user table
create table `user` (
	uid varchar(32) PRIMARY KEY,
	username varchar(32),
	`password` varchar(32));- character table
create table role (
	rid varchar(32) PRIMARY KEY,
	rname varchar(32));- the middle table
create table user_role(
	user_id varchar(32),
	role_id varchar(32),
	CONSTRAINT user_role_pk PRIMARY KEY(user_id,role_id),
	CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES `user`(uid),
	CONSTRAINT role_id_fk FOREIGN KEY(role_id) REFERENCES role(rid)
);

Copy the code

Chapter 3 Multi-table query

The table structure is provided as follows:

Classification of #
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50));Table # goods
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(1), # Shelves mark: 1 indicates shelves, 0 indicates removal
  category_id VARCHAR(32),
  CONSTRAINT products_category_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);
Copy the code

3.1 Initializing Data

# classification
INSERT INTO category(cid,cname) VALUES('c001'.'goods');
INSERT INTO category(cid,cname) VALUES('c002'.'dress');
INSERT INTO category(cid,cname) VALUES('c003'.'Cosmetics');
# goods
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001'.'lenovo'.5000.'1'.'c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002'.'海尔'.3000.'1'.'c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003'.'thor'.5000.'1'.'c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004'.'JACK JONES'.800.'1'.'c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005'.Jeanswest.200.'1'.'c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006'.'Playboy'.440.'1'.'c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007'.'the eu'.2000.'1'.'c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008'.Chanel.800.'1'.'c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009'.'Suitable herb'.200.'1'.'c003');
Copy the code

3.2 Multi-table query

Cross join query (not used in development – results in product of two tables)

Grammar:select * from A,B;

select * from category,products ORDER BY cid, pid;
Copy the code

Effect: Join each record in the category table with each record in the Products table

Common multi-table query methods:

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

Implicit inside connection: select * from A, B, where conditions;
Select * from A inner join B on;

-- Check which categories are already on the shelves
Implicit inner join
SELECT DISTINCT
	*
FROM
	category c,
	products p
WHERE
	c.cid = p.category_id;

-- Shows the inner connection
SELECT DISTINCT
	*
FROM
	category c INNER JOIN products p 
ON 
	c.cid = p.category_id;
Copy the code

The effect

Outer join query (use keyword outer join — outer can be omitted)

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;

#2. Select * from 'select' where 'select'
# left outer connect
INSERT INTO category(cid,cname) VALUES('c004'.'Luxury');

SELECT cname,COUNT(category_id) 
FROM category c LEFT OUTER JOIN products p 
ON c.cid = p.category_id 
GROUP BY cname;
Copy the code

The effect

3.3 the subquery

Subquery Overview

The subquery:

The result of a SELECT statement is part of the syntax of another SELECT statement (query condition, query result, table, etc.). Grammar:

select. Query field...from. The table..where. Query conditionsCopy the code

Subquery code demonstration

-- Sub-query, query "cosmetics" classified goods details
-- Internal connection mode
select 
	p.*
from 
	category c, products p
WHERE
	c.cid = p.category_id and c.cname = 'Cosmetics'

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

-- 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

The effect

Subquery exercise

# Query "cosmetics" and "home appliances" two categories of goods listed details
select
	*
from 
	products 
WHERE
	category_id in (select cid from category where cname='goods' or cname='Cosmetics'); 

-- select cid from category where cname=' cname 'or cname=' cname ';
-- select cid from category where cname in (' home ', 'cosmetics ');
Copy the code

The effect

Author: can read so far, doomed our this life predestined relationship! I have been engaged in Java education for more than 10 years. With the feelings of teaching and educating people, I am making more students avoid detours and changing the fate of millions of IT people.

Welcome to my B station, you can watch the accompanying video of this article ~~~Please pay attention to my official account for more information ~~~

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MySQL > select * from ‘SQL’

Learning goals

Chapter 1 Relationships between tables

1.1 Relationships between tables

1.2 Foreign Key Constraints

Chapter two: Actual practice of multi-table relation

2.1 Actual Combat 1: Province and city

2.2 Actual Combat 2: Users and Roles

Chapter 3 Multi-table query

3.1 Initializing Data

3.2 Multi-table query

Cross join query (not used in development – results in product of two tables)

Common multi-table query methods:

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

Outer join query (use keyword outer join — outer can be omitted)

3.3 the subquery

Subquery Overview

Subquery exercise

MySQL > select * from ‘SQL’

Learning goals

Chapter 1 Relationships between tables

1.1 Relationships between tables

1.2 Foreign Key Constraints

Chapter two: Actual practice of multi-table relation

2.1 Actual Combat 1: Province and city

2.2 Actual Combat 2: Users and Roles

Chapter 3 Multi-table query

3.1 Initializing Data

3.2 Multi-table query

Cross join query (not used in development – results in product of two tables)

Common multi-table query methods:

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

Outer join query (use keyword outer join — outer can be omitted)

3.3 the subquery

Subquery Overview

Subquery exercise

Related Posts

JAVA memory model and happens-before rule

Container technology (9) : Kubernetes Pod resource management and monitoring

TCP sticky packet solution