-- 創(chuàng)建訂單表 order（注意order是關(guān)鍵字，用反引號包裹）
CREATE TABLE `order` (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,  -- 關(guān)聯(lián)用戶表主鍵
    amount DECIMAL(10,2),  -- 訂單金額（保留2位小數(shù)）
    create_time DATETIME DEFAULT NOW()
);

-- 插入訂單測試數(shù)據(jù)
INSERT INTO `order` (user_id, amount) 
VALUES 
(1, 99.90),
(1, 199.90),
(2, 59.90),
(3, 299.90),
(4, 159.90),
(4, 89.90);

-- 1. 統(tǒng)計用戶總數(shù)
SELECT COUNT(*) AS user_total FROM user;
-- 2. 統(tǒng)計性別為男的用戶數(shù)
SELECT COUNT(*) AS male_total FROM user WHERE gender = 1;
-- 3. 統(tǒng)計訂單總數(shù)
SELECT COUNT(*) AS order_total FROM `order`;

-- 統(tǒng)計age字段非NULL的用戶數(shù)（如果有用戶age為NULL，會被排除）
SELECT COUNT(age) AS age_not_null FROM user;

-- 統(tǒng)計有訂單的不同用戶數(shù)（一個用戶可能有多個訂單，只算1次）
SELECT COUNT(DISTINCT user_id) AS user_with_order FROM `order`;

-- 1. 統(tǒng)計所有訂單的總金額
SELECT SUM(amount) AS total_amount FROM `order`;
-- 2. 統(tǒng)計用戶id=1的所有訂單金額總和
SELECT SUM(amount) AS user1_total FROM `order` WHERE user_id = 1;
-- 3. 非數(shù)值字段求和（返回0，無意義）
SELECT SUM(name) AS wrong_sum FROM user;

-- 1. 計算所有用戶的平均年齡
SELECT AVG(age) AS avg_age FROM user;
-- 2. 計算所有訂單的平均金額（保留2位小數(shù)，用ROUND函數(shù)）
SELECT ROUND(AVG(amount), 2) AS avg_amount FROM `order`;
-- 3. 計算性別為女的用戶平均年齡
SELECT AVG(age) AS female_avg_age FROM user WHERE gender = 2;

SELECT 
    COUNT(DISTINCT u.id) AS target_user,  -- 符合條件的用戶數(shù)
    ROUND(AVG(u.age), 2) AS avg_age,      -- 平均年齡
    SUM(o.amount) AS total_order_amount   -- 訂單總金額
FROM user u
LEFT JOIN `order` o ON u.id = o.user_id  -- 關(guān)聯(lián)用戶表和訂單表
WHERE u.age > 20 AND o.user_id IS NOT NULL;  -- 有訂單且年齡>20