alien_cloud/alien-second/doc/二手委托人表创建.sql

-- ============================================
-- 二手委托人信息表 INSERT SQL 模板
-- ============================================

-- 方式1: 插入完整数据（推荐）
INSERT INTO second_entrust_user (
    entrust_trade_id, 
    entrust_trade_no, 
    entrust_user_phone, 
    entrust_user_name, 
    entrust_id_card, 
    entrust_id_card_img,
    delete_flag,
    created_time, 
    created_user_id, 
    updated_time, 
    updated_user_id
) VALUES (
    1,                              -- entrust_trade_id: 交易ID
    'TRADE202511210001',            -- entrust_trade_no: 交易编号
    '13800138000',                  -- entrust_user_phone: 委托人电话
    '张三',                          -- entrust_user_name: 委托人姓名
    '110101199001011234',           -- entrust_id_card: 委托人身份证
    'https://oss.example.com/id/123.jpg',  -- entrust_id_card_img: 委托人身份证照片
    0,                              -- delete_flag: 删除标记(0:未删除, 1:已删除)
    NOW(),                          -- created_time: 创建时间
    1,                              -- created_user_id: 创建人ID
    NOW(),                          -- updated_time: 修改时间
    1                               -- updated_user_id: 修改人ID
);

-- 方式2: 插入必填字段（让数据库自动填充其他字段）
INSERT INTO second_entrust_user (
    entrust_trade_id, 
    entrust_trade_no, 
    entrust_user_phone, 
    entrust_user_name,
    created_time
) VALUES (
    2,                              -- entrust_trade_id
    'TRADE202511210002',            -- entrust_trade_no
    '13900139000',                  -- entrust_user_phone
    '李四',                          -- entrust_user_name
    NOW()                           -- created_time
);

-- 方式3: 批量插入
INSERT INTO second_entrust_user (
    entrust_trade_id, 
    entrust_trade_no, 
    entrust_user_phone, 
    entrust_user_name, 
    entrust_id_card, 
    entrust_id_card_img,
    delete_flag,
    created_time
) VALUES 
(3, 'TRADE202511210003', '13700137000', '王五', '110101199001011235', 'https://oss.example.com/id/234.jpg', 0, NOW()),
(4, 'TRADE202511210004', '13600136000', '赵六', '110101199001011236', 'https://oss.example.com/id/345.jpg', 0, NOW()),
(5, 'TRADE202511210005', '13500135000', '孙七', '110101199001011237', 'https://oss.example.com/id/456.jpg', 0, NOW());

-- ============================================
-- 查询语句
-- ============================================

-- 查询所有未删除的委托人信息
SELECT * FROM second_entrust_user WHERE delete_flag = 0;

-- 根据交易ID查询
SELECT * FROM second_entrust_user WHERE entrust_trade_id = 1 AND delete_flag = 0;

-- 根据交易编号查询
SELECT * FROM second_entrust_user WHERE entrust_trade_no = 'TRADE202511210001' AND delete_flag = 0;

-- 根据委托人电话查询
SELECT * FROM second_entrust_user WHERE entrust_user_phone = '13800138000' AND delete_flag = 0;

-- 根据委托人姓名模糊查询
SELECT * FROM second_entrust_user WHERE entrust_user_name LIKE '%张%' AND delete_flag = 0;

-- 关联交易记录表查询
SELECT 
    seu.*,
    str.trade_no,
    str.transaction_time,
    str.transaction_location,
    str.trade_status
FROM second_entrust_user seu
LEFT JOIN second_trade_record str ON seu.entrust_trade_id = str.id
WHERE seu.delete_flag = 0;

-- ============================================
-- 更新语句
-- ============================================

-- 更新委托人信息
UPDATE second_entrust_user 
SET 
    entrust_user_phone = '13800138001',
    entrust_user_name = '张三丰',
    entrust_id_card = '110101199001011238',
    updated_time = NOW(),
    updated_user_id = 1
WHERE id = 1 AND delete_flag = 0;

-- ============================================
-- 删除语句（逻辑删除）
-- ============================================

-- 逻辑删除
UPDATE second_entrust_user 
SET 
    delete_flag = 1,
    updated_time = NOW(),
    updated_user_id = 1
WHERE id = 1;

-- 物理删除（谨慎使用）
-- DELETE FROM second_entrust_user WHERE id = 1;

-- ============================================
-- 统计查询
-- ============================================

-- 统计委托人总数
SELECT COUNT(*) as total FROM second_entrust_user WHERE delete_flag = 0;

-- 按日期统计委托人创建数量
SELECT 
    DATE(created_time) as create_date,
    COUNT(*) as count
FROM second_entrust_user
WHERE delete_flag = 0
GROUP BY DATE(created_time)
ORDER BY create_date DESC;

-- 查询最近创建的10条委托人信息
SELECT * FROM second_entrust_user 
WHERE delete_flag = 0 
ORDER BY created_time DESC 
LIMIT 10;