Logo
Section 1 / 4

连接表

了解 SQL 中不同类型的连接。

导入

连接表

了解 SQL 中不同类型的连接。

1SELECT Column1, ...
2FROM Table1
3INNER JOIN Table2
4ON Table1.ColumnName = Table2.ColumnName;
1-- Customers 表定义
2-- CREATE TABLE Customers (
3--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
4--     CustomerName VARCHAR(50) NOT NULL,
5--     Email VARCHAR(50),
6--     Phone VARCHAR(15),
7--     Address VARCHAR(100)
8-- );
9
10-- Orders 表定义
11-- CREATE TABLE Orders (
12--     OrderID INT PRIMARY KEY AUTO_INCREMENT,
13--     CustomerID INT,
14--     OrderDate DATE NOT NULL,
15--     TotalAmount DECIMAL(10, 2) NOT NULL,
16--     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
17-- );
18-- ------------------------------------------------------------------------------
19SELECT o.OrderID, c.CustomerName, o.OrderDate, o.TotalAmount
20FROM Orders o
21INNER JOIN Customers c ON o.CustomerID = c.CustomerID;
1SELECT Column1, ...
2FROM Table1
3LEFT JOIN Table2
4ON Table1.ColumnName = Table2.ColumnName;
1-- Customers 表定义
2-- CREATE TABLE Customers (
3--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
4--     CustomerName VARCHAR(50) NOT NULL,
5--     Email VARCHAR(50),
6--     Phone VARCHAR(15),
7--     Address VARCHAR(100)
8-- );
9
10-- Orders 表定义
11-- CREATE TABLE Orders (
12--     OrderID INT PRIMARY KEY AUTO_INCREMENT,
13--     CustomerID INT,
14--     OrderDate DATE NOT NULL,
15--     TotalAmount DECIMAL(10, 2) NOT NULL,
16--     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
17-- );
18-- ------------------------------------------------------------------------------
19
20SELECT o.OrderID, c.CustomerName, o.OrderDate, o.TotalAmount
21FROM Orders o
22LEFT JOIN Customers c ON o.CustomerID = c.CustomerID;
1SELECT Column1, ...
2FROM Table1
3RIGHT JOIN Table2
4ON Table1.ColumnName = Table2.ColumnName;
1-- Customers 表定义
2-- CREATE TABLE Customers (
3--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
4--     CustomerName VARCHAR(50) NOT NULL,
5--     Email VARCHAR(50),
6--     Phone VARCHAR(15),
7--     Address VARCHAR(100)
8-- );
9
10-- Orders 表定义
11-- CREATE TABLE Orders (
12--     OrderID INT PRIMARY KEY AUTO_INCREMENT,
13--     CustomerID INT,
14--     OrderDate DATE NOT NULL,
15--     TotalAmount DECIMAL(10, 2) NOT NULL,
16--     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
17-- );
18-- ------------------------------------------------------------------------------
19
20SELECT c.CustomerName, o.OrderID, o.OrderDate, o.TotalAmount 
21FROM Orders o
22RIGHT JOIN Customers c ON o.CustomerID = c.CustomerID;
1-- Customers 表定义
2-- CREATE TABLE Customers (
3--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
4--     CustomerName VARCHAR(50) NOT NULL,
5--     Email VARCHAR(50),
6--     Phone VARCHAR(15),
7--     Address VARCHAR(100)
8-- );
9
10-- Orders 表定义
11-- CREATE TABLE Orders (
12--     OrderID INT PRIMARY KEY AUTO_INCREMENT,
13--     CustomerID INT,
14--     OrderDate DATE NOT NULL,
15--     TotalAmount DECIMAL(10, 2) NOT NULL,
16--     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
17-- );
18-- ------------------------------------------------------------------------------
19
20SELECT c.CustomerName, o.OrderID, o.OrderDate, o.TotalAmount
21FROM Orders o
22FULL JOIN Customers c ON o.CustomerID = c.CustomerID;
1-- Orders 表定义
2-- CREATE TABLE Orders (
3--     OrderID INT PRIMARY KEY AUTO_INCREMENT,
4--     CustomerID INT,
5--     OrderDate DATE NOT NULL,
6--     TotalAmount DECIMAL(10, 2) NOT NULL,
7--     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
8-- );
9
10-- Customers 表定义
11-- CREATE TABLE Customers (
12--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
13--     CustomerName VARCHAR(50) NOT NULL,
14--     Email VARCHAR(50),
15--     Phone VARCHAR(15),
16--     Address VARCHAR(100)
17-- );
18
19-- Order_Details 表定义
20-- CREATE TABLE Order_Details (
21--     OrderItemID INT PRIMARY KEY AUTO_INCREMENT,
22--     OrderID INT,
23--     ProductID INT,
24--     Quantity INT,
25--     TotalItemPrice DECIMAL(10, 2) NOT NULL,
26--     FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
27--     FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
28-- );
29
30-- Products 表定义
31-- CREATE TABLE Products (
32--     ProductID INT PRIMARY KEY AUTO_INCREMENT,
33--     ProductName VARCHAR(50) NOT NULL UNIQUE,
34--     CategoryID INT,
35--     Price DECIMAL(10, 2) NOT NULL,
36--     Stock INT NOT NULL,
37--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
38-- );
39-- ------------------------------------------------------------------------------
40
41SELECT o.OrderID, c.CustomerName, p.ProductName, od.Quantity, o.OrderDate
42FROM Orders o
43INNER JOIN Customers c ON o.CustomerID = c.CustomerID
44INNER JOIN Order_Details od ON o.OrderID = od.OrderID
45INNER JOIN Products p ON od.ProductID = p.ProductID;

CustomerNameOrderIDTotalAmount
John DoeNULLNULL
Jane SmithNULLNULL
Alice Johnson7156.24
. . .. . .. . .
Olivia Thompson61590.69
James Walker10751.50
Grace Taylor42550.50
. . .. . .. . .
Emma WatsonNULLNULL
1-- Orders 表定义
2-- CREATE TABLE Orders (
3--     OrderID INT PRIMARY KEY AUTO_INCREMENT,
4--     CustomerID INT,
5--     OrderDate DATE NOT NULL,
6--     TotalAmount DECIMAL(10, 2) NOT NULL,
7--     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
8-- );
9
10-- Customers 表定义
11-- CREATE TABLE Customers (
12--     CustomerID INT PRIMARY KEY AUTO_INCREMENT,
13--     CustomerName VARCHAR(50) NOT NULL,
14--     Email VARCHAR(50),
15--     Phone VARCHAR(15),
16--     Address VARCHAR(100)
17-- );
18-- ------------------------------------------------------------------------------
19
20-- 请在此处编写你的代码
1SELECT Customers.CustomerName, Orders.OrderID, Orders.TotalAmount
2FROM Customers
3LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
1SELECT Customers.CustomerName, Orders.OrderID, Orders.TotalAmount
2FROM Customers
3LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

ProductNameCategoryName
SmartphoneElectronics
Dumbbell SetFitness
LaptopElectronics
Smart Outdoor LightOutdoor
. . .. . .
Camping ChairOutdoor
Hiking BackpackOutdoor
SmartwatchElectronics
Smart Fitness BandFitness
1-- Categories 表定义
2-- CREATE TABLE Categories (
3--     CategoryID INT PRIMARY KEY AUTO_INCREMENT,
4--     CategoryName VARCHAR(50) NOT NULL UNIQUE
5-- );
6
7-- Products 表定义
8-- CREATE TABLE Products (
9--     ProductID INT PRIMARY KEY AUTO_INCREMENT,
10--     ProductName VARCHAR(50) NOT NULL UNIQUE,
11--     CategoryID INT,
12--     Price DECIMAL(10, 2) NOT NULL,
13--     Stock INT NOT NULL,
14--     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
15-- );
16-- ------------------------------------------------------------------------------
17
18-- 请在此处编写你的代码
1SELECT p.ProductName, c.CategoryName
2FROM Products p
3LEFT JOIN Categories c ON p.CategoryID = c.CategoryID;
1SELECT p.ProductName, c.CategoryName
2FROM Products p
3LEFT JOIN Categories c ON p.CategoryID = c.CategoryID;

SupplierNameProductName
TechSolutionsSmartphone
TechSolutionsLaptop
TechSolutionsSmart TV
. . .. . .
SportyGearSmart Outdoor Light
SportyGearTent
. . .. . .
TechNexusBluetooth Speaker
TechNexusHiking Backpack
GourmetKitchenNotebook
1-- Suppliers 表定义
2-- CREATE TABLE Suppliers (
3--     SupplierID INT PRIMARY KEY AUTO_INCREMENT,
4--     SupplierName VARCHAR(50) NOT NULL,
5--     Email VARCHAR(50),
6--     Phone VARCHAR(15),
7--     Address VARCHAR(100)
8-- );
9
10-- Product_Suppliers 表定义
11-- CREATE TABLE Product_Suppliers (
12--     ProductID INT,
13--     SupplierID INT,
14--     FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
15--     FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
16-- );
17-- ------------------------------------------------------------------------------
18
19-- 请在此处编写你的代码
1SELECT s.SupplierName, p.ProductName
2FROM Suppliers s 
3LEFT JOIN Product_Suppliers ps ON s.SupplierID = ps.SupplierID
4LEFT JOIN Products p ON ps.ProductID = p.ProductID;
1SELECT s.SupplierName, p.ProductName
2FROM Suppliers s 
3LEFT JOIN Product_Suppliers ps ON s.SupplierID = ps.SupplierID
4LEFT JOIN Products p ON ps.ProductID = p.ProductID;

聚合函数与分组

子查询