表结构
本文使用的表结构如下：
以下是创建表的语句：
-- 厂商表
CREATE TABLE Manufacturers (Code INTEGER NOT NULL PRIMARY KEY, -- 编号，主键Name VARCHAR(255) NOT NULL, -- 名称
);-- 产品表
CREATE TABLE Products (Code INTEGER NOT NULL PRIMARY KEY, -- 编号，主键Name VARCHAR(255) NOT NULL, -- 名称Price DECIMAL NOT NULL, -- 价格Manufacturer INTEGER NOT NULL, -- 厂商编号FOREIGN KEY (Manufacturer) REFERENCES Manufacturers(Code) -- 外键，引用厂商表
);-- 测试数据
INSERT INTO Manufacturers(Code,Name)
VALUES (1,'Sony'),(2,'Creative Labs'),(3,'Hewlett-Packard'),(4,'Iomega'),(5,'Fujitsu'),(6,'Winchester');INSERT INTO Products(Code,Name,Price,Manufacturer)
VALUES (1,'Hard drive',240,5),(2,'Memory',120,6),(3,'ZIP drive',150,4),(4,'Floppy disk',5,6),(5,'Monitor',240,1),(6,'DVD drive',180,2),(7,'CD drive',90,2),(8,'Printer',270,3),(9,'Toner cartridge',66,3),(10,'DVD burner',180,2);
练习题
查看全部产品的名称
SELECT Name FROM Products;
查看全部产品的名称和价格
SELECT Name, Price FROM Products;
查看价格小于等于 200 的产品名称
SELECT Name FROM Products WHERE Price <= 200;
查看价格位于 60 到 120 之间的产品
/* 使用 AND 运算符*/
SELECT * FROM Products
WHERE Price >= 60 AND Price <= 120;/* 使用 BETWEEN 运算符*/
SELECT * FROM Products
WHERE Price BETWEEN 60 AND 120;
查看全部产品的名称和价格，价格以分为单位显示
SELECT Name, Price * 100 AS PriceCents FROM Products;
计算全部产品的平均价格
SELECT AVG(Price) FROM Products;
计算厂商编号为 2 的所有产品的平均价格
SELECT AVG(Price) FROM Products WHERE Manufacturer=2;
统计价格大于等于 180 的产品数量
SELECT COUNT(*) FROM Products WHERE Price >= 180;
查看价格大于等于 180 的产品名称和价格，按照价格降序、名称升序排序
SELECT Name, Price
FROM Products
WHERE Price >= 180
ORDER BY Price DESC, Name;
查看全部产品以及它们的厂商信息
SELECT * FROM Products, Manufacturers
WHERE Products.Manufacturer = Manufacturers.Code;
查看全部产品的名称、价格以及厂商的名称
SELECT Products.Name, Price, Manufacturers.Name
FROM Products
JOIN Manufacturers
ON Products.Manufacturer = Manufacturers.Code;
统计每个厂商的平均产品价格，显示厂商编号
SELECT AVG(Price), Manufacturer
FROM Products
GROUP BY Manufacturer;
统计每个厂商的平均产品价格，显示厂商名称
SELECT AVG(Price), Manufacturers.Name
FROM Products
JOIN Manufacturers
ON Products.Manufacturer = Manufacturers.Code
GROUP BY Manufacturers.Name;
查看平均产品价格大于等于 150 的厂商名称
SELECT AVG(Price), Manufacturers.Name
FROM Products
JOIN Manufacturers
ON Products.Manufacturer = Manufacturers.Code
GROUP BY Manufacturers.Name
HAVING AVG(Price) >= 150;
查看最便宜的产品名称和价格
SELECT Name, Price
FROM Products
ORDER BY price ASC
LIMIT 1;/* 如果存在多个最便宜的产品，嵌套子查询可以返回多条记录 */
SELECT Name, Price
FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products);
查看每个厂商及其最昂贵的产品名称和价格
SELECT A.Name, A.Price, F.Name
FROM Products A
JOIN Manufacturers F
ON A.Manufacturer = F.Code
AND A.Price =(SELECT MAX(A.Price)FROM Products AWHERE A.Manufacturer = F.Code);
查看平均产品价格大于 145 并且至少生产 2 种产品的厂商名称
SELECT m.Name, Avg(p.price) AS p_price, COUNT(p.Manufacturer) AS m_count
FROM Manufacturers m, Products p
WHERE p.Manufacturer = m.code
GROUP BY m.Name
HAVING Avg(p.price) >= 150 AND COUNT(p.Manufacturer) >= 2;
增加一个新的产品
新产品的信息如下：名称为 Loudspeakers，价格为 70，厂商编号为 2。
INSERT INTO Products( Code, Name , Price , Manufacturer)VALUES ( 11, 'Loudspeakers' , 70 , 2 );
将编号为 8 的产品更名为“Laser Printer”
UPDATE Products
SET Name = 'Laser Printer'
WHERE Code = 8;
将全部产品打九折
UPDATE Products
SET Price = Price - (Price * 0.1);
将价格大于等于 120 的产品打九折
UPDATE Products
SET Price = Price - (Price * 0.1)
WHERE Price >= 120;
