查询语句。。

基础

SELECT username AS '姓名', phone AS '手机号' FROM user;

CREATE INDEX idx_product_name ON product(product_name);

EXPLAIN SELECT * FROM product WHERE product_name LIKE '水%';

SELECT user_id, COUNT(order_id) AS '订单数量'
FROM `order`
GROUP BY user_id
ORDER BY `订单数量` DESC;

复杂

SELECT 
    u.user_id, 
    u.username, 
    u.email, 
    u.phone, 
    uw.wallet_id, 
    uw.balance 
FROM 
    user u 
JOIN 
    user_wallet uw ON u.user_id = uw.user_id; 


SELECT 
    pt.type_name 
FROM 
    product p 
JOIN 
    product_type pt ON p.type_id = pt.type_id 
		JOIN 
    (SELECT 
         product_id,
         COUNT(order_id) AS order_count 
     FROM 
         order_info 
     GROUP BY 
         product_id 
     ORDER BY 
         order_count DESC 
     LIMIT 1) oi ON p.product_id = oi.product_id;


SELECT 
    u.*, 
    uw.wallet_id, 
    uw.balance 
FROM 
    user u 
JOIN 
    (SELECT 
         user_id,
         SUM(total_price) AS total_spent
     FROM 
         `order` 
     GROUP BY 
         user_id 
     ORDER BY 
         total_spent DESC 
     LIMIT 1) o ON u.user_id = o.user_id
JOIN 
    user_wallet uw ON u.user_id = uw.user_id;