导入
聚合函数简介
了解SQL中的聚合函数。
1SELECT AGGREGATE_FUNCTION(Column1) 2FROM TableName;
1-- Products 表定义 2 3-- CREATE TABLE Products ( 4-- ProductID INT PRIMARY KEY AUTO_INCREMENT, 5-- ProductName VARCHAR(50) NOT NULL UNIQUE, 6-- CategoryID INT, 7-- Price DECIMAL(10, 2) NOT NULL, 8-- Stock INT NOT NULL, 9-- FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) 10-- ); 11-- ------------------------------------------------------------------------------ 12 13SELECT COUNT(*) AS TotalProducts 14FROM Products;
1-- Products 表定义 2 3-- CREATE TABLE Products ( 4-- ProductID INT PRIMARY KEY AUTO_INCREMENT, 5-- ProductName VARCHAR(50) NOT NULL UNIQUE, 6-- CategoryID INT, 7-- Price DECIMAL(10, 2) NOT NULL, 8-- Stock INT NOT NULL, 9-- FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) 10-- ); 11-- ------------------------------------------------------------------------------ 12 13SELECT SUM(Price) AS TotalPrice 14FROM Products;
1-- Products 表定义 2 3-- CREATE TABLE Products ( 4-- ProductID INT PRIMARY KEY AUTO_INCREMENT, 5-- ProductName VARCHAR(50) NOT NULL UNIQUE, 6-- CategoryID INT, 7-- Price DECIMAL(10, 2) NOT NULL, 8-- Stock INT NOT NULL, 9-- FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) 10-- ); 11-- ------------------------------------------------------------------------------ 12 13SELECT AVG(Price) AS AveragePrice 14FROM Products;
1-- Products 表定义 2 3-- CREATE TABLE Products ( 4-- ProductID INT PRIMARY KEY AUTO_INCREMENT, 5-- ProductName VARCHAR(50) NOT NULL UNIQUE, 6-- CategoryID INT, 7-- Price DECIMAL(10, 2) NOT NULL, 8-- Stock INT NOT NULL, 9-- FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) 10-- ); 11-- ------------------------------------------------------------------------------ 12 13SELECT MIN(Price) AS 'Cheapest Product' 14FROM Products;
1-- Products 表定义 2 3-- CREATE TABLE Products ( 4-- ProductID INT PRIMARY KEY AUTO_INCREMENT, 5-- ProductName VARCHAR(50) NOT NULL UNIQUE, 6-- CategoryID INT, 7-- Price DECIMAL(10, 2) NOT NULL, 8-- Stock INT NOT NULL, 9-- FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) 10-- ); 11-- ------------------------------------------------------------------------------ 12 13SELECT MAX(Price) AS 'Most Expensive Product' 14FROM Products;
1-- Products 表定义 2 3-- CREATE TABLE Products ( 4-- ProductID INT PRIMARY KEY AUTO_INCREMENT, 5-- ProductName VARCHAR(50) NOT NULL UNIQUE, 6-- CategoryID INT, 7-- Price DECIMAL(10, 2) NOT NULL, 8-- Stock INT NOT NULL, 9-- FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) 10-- ); 11-- ------------------------------------------------------------------------------ 12 13SELECT COUNT(*) AS TotalProducts, 14 AVG(Price) AS AveragePrice, 15 MAX(Stock) AS HighestStock 16FROM Products;
1SELECT Price, COUNT(*) AS TotalProducts 2FROM Products;
| TotalSales |
|---|
| 673.43 |
1-- Orders 表定义 2 3-- CREATE TABLE Orders ( 4-- OrderID INT PRIMARY KEY, 5-- CustomerID INT, 6-- OrderDate DATE, 7-- TotalAmount DECIMAL(10, 2), 8-- FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) 9-- ); 10-- ------------------------------------------------------------------------------ 11 12-- 请在此处编写代码
1SELECT SUM(TotalAmount) AS TotalSales 2FROM Orders;
1SELECT SUM(TotalAmount) AS TotalSales 2FROM Orders;
| TotalOrders | TotalRevenue | AverageOrderValue |
|---|---|---|
| 10 | 673.43 | 67.343000 |
1-- Orders 表定义 2 3-- CREATE TABLE Orders ( 4-- OrderID INT PRIMARY KEY, 5-- CustomerID INT, 6-- OrderDate DATE, 7-- TotalAmount DECIMAL(10, 2), 8-- FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) 9-- ); 10-- ------------------------------------------------------------------------------ 11 12-- 请在此处编写代码
1SELECT COUNT(*) AS TotalOrders, 2 SUM(TotalAmount) AS TotalRevenue, 3 AVG(TotalAmount) AS AverageOrderValue 4FROM Orders;
1SELECT COUNT(*) AS TotalOrders, 2 SUM(TotalAmount) AS TotalRevenue, 3 AVG(TotalAmount) AS AverageOrderValue 4FROM Orders;