DML 简介

代码示例

1INSERT INTO table_name (column1, column2, column3, ...)
2VALUES (value1, value2, value3, ...);

代码示例

1INSERT INTO table_name
2VALUES (value1, value2, value3, ...);

1-- 向 Categories 表中添加新类别
2INSERT INTO Categories (CategoryName)
3VALUES ('杂货');
4
5-- 为验证插入结果，我们可以查询数据
6SELECT * FROM Categories WHERE CategoryName = '杂货';

1-- 首先，让我们找到 '杂货' 的 CategoryID
2-- 在此示例中，假设在上一次插入后其值为 9。
3-- 如果你正在运行此代码，可能需要查询它：
4-- SELECT CategoryID FROM Categories WHERE CategoryName = '杂货';
5-- 假设 '杂货' 的 CategoryID 为 9。
6
7-- 向 Products 表中添加新产品
8INSERT INTO Products (ProductName, CategoryID, Price, Stock, LastRestockDate, MonthlySales)
9VALUES ('有机苹果', 9, 2.99, 150, '2025-03-15', 0);
10
11-- 验证插入结果
12SELECT ProductName, CategoryID, Price, Stock, LastRestockDate, MonthlySales 
13FROM Products WHERE ProductName = '有机苹果';

1-- 向 Customers 表中添加新客户
2INSERT INTO Customers (CustomerName, Email, Phone, Address, CustomerTier)
3VALUES ('Nina Blue', 'ninablue@example.com', '555-1234', '10 Green Hill Rd', 'New');
4
5-- 验证插入结果
6SELECT * FROM Customers WHERE Email = 'ninablue@example.com';

代码示例

1UPDATE table_name
2SET    column1 = value1, column2 = value2, ...
3WHERE  condition;

1-- 更新 'Laptop'（ProductID 1）的库存
2UPDATE Products
3SET    Stock = Stock + 20, LastRestockDate = '2025-03-20' -- 更新库存和补货日期
4WHERE  ProductID = 1;
5
6-- 验证更新结果
7SELECT ProductID, ProductName, Stock, LastRestockDate FROM Products WHERE ProductID = 1;

1-- 更新 John Doe（CustomerID 1）的邮箱和客户等级
2UPDATE Customers
3SET    Email = 'john.doe.new@example.com', CustomerTier = 'VIP', LoyaltyPoints = LoyaltyPoints + 100
4WHERE  CustomerID = 1;
5
6-- 验证更新结果
7SELECT CustomerID, CustomerName, Email, CustomerTier, LoyaltyPoints FROM Customers WHERE CustomerID = 1;

1-- 将 '电子产品' 类别下所有产品的价格上调 5%
2UPDATE Products
3SET    Price = Price * 1.05
4WHERE  CategoryID = 1;
5
6-- 验证部分电子产品的更新结果
7SELECT ProductName, Price FROM Products WHERE CategoryID = 1;

代码示例

1DELETE FROM table_name
2WHERE condition;

1-- 从 Products 表中删除特定产品
2DELETE FROM Products
3WHERE ProductID = 17;

1-- 从 Customers 表中删除客户
2DELETE FROM Customers
3WHERE CustomerID = 26 AND Email = 'ninablue@example.com'; -- 添加邮箱以增加安全性

1-- 删除旧的已取消订单
2DELETE FROM Orders
3WHERE DeliveryStatus = 'Cancelled' AND OrderDate < '2025-01-01';