MySQL 数据库

-- 创建示例数据 CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50), price DECIMAL(10,2) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, product_id INT, quantity INT, order_date DATE ); INSERT INTO products VALUES (1, 'iPhone 15', '手机', 6999.00), (2, 'MacBook Pro', '电脑', 12999.00), (3, 'iPad Air', '平板', 4799.00); INSERT INTO orders VALUES (101, 1, 2, '2024-01-10'), (102, 2, 1, '2024-01-11'), (103, 1, 3, '2024-01-12'), (104, 3, 5, '2024-01-13'); -- 标量子查询：返回单个值 -- 查询价格高于平均价格的产品 SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products); -- 在SELECT中使用子查询 SELECT product_name, price, (SELECT AVG(price) FROM products) AS avg_price, price - (SELECT AVG(price) FROM products) AS price_diff FROM products; -- 在HAVING中使用子查询 -- 查询总销量高于平均销量的产品 SELECT p.product_name, SUM(o.quantity) AS total_quantity FROM products p JOIN orders o ON p.product_id = o.product_id GROUP BY p.product_id, p.product_name HAVING SUM(o.quantity) > (SELECT AVG(total_qty) FROM ( SELECT SUM(quantity) AS total_qty FROM orders GROUP BY product_id ) AS product_sales);

-- IN子查询 -- 查询有订单的产品 SELECT product_name, price FROM products WHERE product_id IN (SELECT DISTINCT product_id FROM orders); -- NOT IN子查询 -- 查询没有订单的产品 SELECT product_name, price FROM products WHERE product_id NOT IN (SELECT DISTINCT product_id FROM orders); -- 注意：NOT IN需要处理NULL值问题 SELECT product_name FROM products WHERE product_id NOT IN ( SELECT product_id FROM orders WHERE product_id IS NOT NULL ); -- EXISTS子查询 -- 查询有订单的产品（使用EXISTS） SELECT product_name, price FROM products p WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.product_id = p.product_id ); -- NOT EXISTS子查询 -- 查询没有订单的产品 SELECT product_name, price FROM products p WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.product_id = p.product_id ); -- EXISTS性能通常优于IN，特别是在数据量大时

-- 创建简单视图 CREATE VIEW vw_expensive_products AS SELECT product_id, product_name, price FROM products WHERE price > 5000; -- 使用视图 SELECT * FROM vw_expensive_products ORDER BY price DESC; -- 创建连接视图 CREATE VIEW vw_order_details AS SELECT o.order_id, o.order_date, o.quantity, p.product_name, p.category, p.price, o.quantity * p.price AS total_amount FROM orders o JOIN products p ON o.product_id = p.product_id; -- 使用连接视图 SELECT category, COUNT(*) AS order_count, SUM(total_amount) AS category_total FROM vw_order_details GROUP BY category; -- 创建带聚合的视图 CREATE VIEW vw_product_sales AS SELECT p.product_id, p.product_name, p.category, COUNT(o.order_id) AS order_count, COALESCE(SUM(o.quantity), 0) AS total_quantity, COALESCE(SUM(o.quantity * p.price), 0) AS total_revenue FROM products p LEFT JOIN orders o ON p.product_id = o.product_id GROUP BY p.product_id, p.product_name, p.category; -- 使用聚合视图 SELECT * FROM vw_product_sales WHERE total_revenue > 10000 ORDER BY total_revenue DESC; -- 查看视图定义 SHOW CREATE VIEW vw_order_details; -- 查看所有视图 SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';

-- 修改视图（使用CREATE OR REPLACE） CREATE OR REPLACE VIEW vw_expensive_products AS SELECT product_id, product_name, price, category FROM products WHERE price > 5000 ORDER BY price DESC; -- 修改视图（使用ALTER VIEW） ALTER VIEW vw_expensive_products AS SELECT product_id, product_name, price, category FROM products WHERE price > 6000 -- 修改条件 ORDER BY price DESC; -- 删除视图 DROP VIEW IF EXISTS vw_expensive_products; -- 带检查选项的视图 CREATE VIEW vw_high_sales AS SELECT product_id, product_name, price FROM products WHERE price > 8000 WITH CHECK OPTION; -- 通过视图更新数据（需满足条件） -- 这个更新会成功，因为价格9000 > 8000 UPDATE vw_high_sales SET price = 9000 WHERE product_id = 2; -- 这个更新会失败，因为价格7000 < 8000 -- UPDATE vw_high_sales SET price = 7000 WHERE product_id = 2; -- 视图的优点： -- 1. 简化复杂查询 -- 2. 增强数据安全性 -- 3. 提供逻辑数据独立性 -- 4. 提高代码重用性