《深入解析：水果销售数据库操作与查询技巧》
《深入解析：水果销售数据库操作与查询技巧》
·
文章目录
一、数据库结构与数据源插入
本节将介绍如何构建一个水果销售数据库，包括创建数据库、数据表，并插入初始数据。
1.1 创建数据库与表
CREATE DATABASE fruitsales;
USE fruitsales;
CREATE TABLE fruits(
f_id char(10) NOT NULL PRIMARY KEY,
s_id INT NOT NULL,
f_name char(255) NOT NULL,
f_price decimal(8,2)
);
1.2 插入数据
INSERT INTO fruits (f_id, s_id, f_name, f_price)
VALUES('a1', 101,'apple',5.2),
('b1',101,'blackberry', 10.2),
('bs1',102,'orange', 11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana', 10.3),
('t2',102,'grape', 5.3),
('o2',103,'coconut', 9.2),
('c0',101,'cherry', 3.2),
('a2',103, 'apricot',2.2),
('l2',104,'lemon', 6.4),
('b2',104,'berry', 7.6),
('m1',106,'mango', 15.6),
('m2',105,'xbabay', 2.6),
('t4',107,'xbababa', 3.6),
('m3',105,'xxtt', 11.6),
('b5',107,'xxxx', 3.6);
CREATE TABLE customers(
c_id int NOT NULL AUTO_INCREMENT,
c_name char(50) NOT NULL,
c_address char(50) NULL,
c_city char(50) NULL,
c_zip char(10) NULL,
c_contact char(50) NULL,
c_email char(255) NULL,
PRIMARY KEY (c_id)
);
INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email)
VALUES(10001, 'RedHook', '200 Street ', 'Tianjin', '300000', 'LiMing', 'LMing@163.com'),
(10002, 'Stars', '333 Fromage Lane','Dalian', '116000', 'Zhangbo','Jerry@hotmail.com'),
(10003, 'Netbhood', '1 Sunny Place', 'Qingdao', '266000','LuoCong', NULL),
(10004, 'JOTO', '829 Riverside Drive', 'Haikou','570000', 'YangShan', 'sam@hotmail.com');
CREATE TABLE orderitems(
o_num int NOT NULL,
o_item int NOT NULL,
f_id char(10) NOT NULL,
quantity int NOT NULL,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (o_num,o_item)
) ;
INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)
VALUES(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 5, 14.99);
CREATE TABLE suppliers(
s_id int NOT NULL AUTO_INCREMENT,
s_name char(50) NOT NULL,
s_city char(50) NULL,
s_zip char(10) NULL,
s_call CHAR(50) NOT NULL,
PRIMARY KEY (s_id)
) ;
INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');
CREATE TABLE orders(
o_num int NOT NULL AUTO_INCREMENT,
o_date datetime NOT NULL,
c_id int NOT NULL,
PRIMARY KEY (o_num)
) ;
INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2021-09-01', 10001),
(30002, '2021-09-12', 10003),
(30003, '2021-09-30', 10004),
(30004, '2021-10-03', 10005),
(30005, '2021-10-08', 10001);
二、基础数据查询
2.1 查询客户信息
- 查询客户表customers中的客户编号c_id、客户名c_name和地址c_address信息。
SELECT c_id,c_name,c_address FROM customers;
- 在customers表中查询每个客户的c_id、c_name、c_email，输出的列名为客户编号、客户姓名、邮箱。
SELECT c_id AS '客户编号',c_name '客户姓名',c_email '邮箱'
FROM customers;
2.2 查询供应商信息
- 查询供应商表suppliers中供应商编号s_id为101的供应商名称s_name和供应商电话s_call。
SELECT s_name,s_call FROM suppliers
WHERE s_id=101;
- 查询fruits表中s_id为102的供应商所供应的orange和banana水果的名称及价格信息。
SELECT f_name,f_price FROM fruits
WHERE s_id=102 AND (f_name='orange' OR f_name='banana');
- 查询orders表中订购日期o_date为2021年9月份的订单编号o_num信息。
SELECT o_num FROM orders
WHERE o_date BETWEEN '2021-09-01' AND '2021-9-30';
- 查询suppliers表中供应商名以Inc.结尾或供应商名第3个字母为M的供应商名称及所在城市的信息。
SELECT s_name,s_city FROM suppliers
WHERE s_name LIKE '%Inc.' OR s_name LIKE '__M%';
- 查询customers表中e_mail值为空的客户编号和客户姓名信息。
SELECT c_id,c_name FROM customers
WHERE c_email = NULL;
SELECT c_id,c_name FROM customers
WHERE c_email IS NULL;
- 查询fruits表中s_id为101、102和103，且f_price不小于10元的供应商编号、水果名称和价格的信息。
SELECT s_id,f_name,f_price FROM fruits
WHERE s_id IN(101,102,103) AND f_price>=10;
- 以s_id的降序、s_id相同以f_price升序，显示fruits表中s_id为101和102的s_id、f_id和f_price的信息。
SELECT s_id,f_id,f_price FROM fruits
WHERE s_id IN(101,102)
ORDER BY s_id DESC,f_price;
- 显示fruits表中水果价格最高的3种水果的信息。
SELECT * FROM fruits
ORDER BY f_price DESC
LIMIT 3;
三、查询优化与技巧
探讨如何优化查询语句，以及一些实用的查询技巧。
3.1 使用LIMIT子句
- 使用LIMIT子句，显示customers表中第2-3条记录。
SELECT * FROM customers
LIMIT 1,2;
- 统计customers表中客户的总人数和有电子邮箱的客户人数。
SELECT COUNT(*) '总人数',COUNT(c_email) '邮箱人数'
FROM customers;
四、高级查询技巧
展示如何使用更复杂的SQL查询语句，包括使用聚合函数、连接查询等。
4.1 使用聚合函数
- 在fruits表中，查询每个供应商水果价格的平均值、最高值和最低值。
SELECT s_id,AVG(f_price) '平均价格',MAX(f_price) '最高价格',
MIN(f_price) '最低价格'
FROM fruits
GROUP BY s_id;
- 查询orderitems表中每份订单总金额，并按总金额的降序排列。
SELECT o_num,SUM(quantity*item_price) '总金额'
FROM orderitems
GROUP BY o_num
ORDER BY '总金额' DESC;
- 查询orderitems中每个订单中订购数量相同的订单数
SELECT o_num,quantity,count(*) 订单数 FROM orderitems
GROUP BY o_num,quantity;
- 显示水果种类大于2种的供应商编号和提供的水果种类数。
SELECT s_id,COUNT(*) 种类数 FROM fruits
GROUP BY s_id
HAVING COUNT(*)>2;
- 查询s_id为101的供应商名称s_name及所供应水果的f_id、f_name和f_price，查询结果按f_price的降序排列。
SELECT s_name,f_id,f_name,f_price FROM fruits f,suppliers AS s
WHERE f.s_id = s.s_id AND s.s_id = 101
ORDER BY f_price DESC;
4.2 连接查询
- 通过customers和orders表，查询所有客户的订单信息，包括没有下订单的的客户。
SELECT c.c_id,c_name,o_num,o_date
FROM customers c LEFT JOIN orders o
ON c.c_id = o.c_id;
4.3 使用子查询
- 查询供应商’ACME’供应的水果编号、名称及价格。
SELECT f_id,f_name,f_price FROM fruits
WHERE s_id = (SELECT s_id FROM suppliers
WHERE s_name='ACME')
- 查询供应商’ACME’供应且大于水果平均价格的水果编号、名称及价格。
SELECT f_id,f_name,f_price FROM fruits
WHERE s_id = (SELECT s_id FROM suppliers
WHERE s_name='ACME')
AND f_price > (SELECT AVG(f_price) FROM fruits);
五、案例分析
- 通过具体案例，展示如何综合运用SQL知识解决实际问题。
5.1 客户订单详情查询
- 查询客户10001所下订单的详细信息。
SELECT * FROM orderitems
WHERE o_num IN (SELECT o_num FROM orders
WHERE c_id=10001);
- 查询fruits表中f_price高于供应商101供应的全部水果价格的f_id和f_price信息。
SELECT f_id,f_price FROM fruits
WHERE f_price >ALL (SELECT f_price FROM fruits
WHERE s_id = 101);
查询已下订单的客户编号、姓名、地址和电子邮箱。
SELECT c_id,c_name,c_address,c_email FROM customers
WHERE EXISTS (SELECT * FROM orders
WHERE customers.c_id = orders.c_id);
假设orderitems表已按o_num和quantity分别建立了索引。现要查询o_num为30001和30002的订单信息，和quantity大于等于10的订单信息。为了提高查询速度，需要使用两列上的索引，所以分别进行条件查询，然后合并查询结果。
SELECT * FROM orderitems WHERE o_num IN(30001,30002)
UNION
SELECT * FROM orderitems WHERE quantity >= 10;
对合并后的查询结果排序。
SELECT o_num,o_item,quantity 数量 FROM orderitems
WHERE o_num IN(30001,30002)
UNION
SELECT o_num,o_item,quantity FROM orderitems
WHERE quantity >= 10
ORDER BY 数量;
创建suppliers_bak1表，同时在s_name字段上创建普通索引name_idx1，要求按降序排列。
CREATE TABLE suppliers_bak1(
s_id INT ,
s_name CHAR(50),
s_city CHAR(50),
s_zip CHAR(10),
s_call CHAR(50),
INDEX name_idx1(s_name DESC)
);
创建suppliers_bak2表，同时在s_id字段上创建唯一索引id_uq_idx2。
CREATE TABLE suppliers_bak2(
s_id INT ,
s_name CHAR(50),
s_city CHAR(50),
s_zip CHAR(10),
s_call CHAR(50),
UNIQUE INDEX id_uq_idx2(s_id)
);
为suppliers_bak1表按s_id和s_name建立唯一索引，索引名为in_uq_idx3。
CREATE UNIQUE INDEX in_uq_idx3
ON suppliers_bak1(s_id,s_name);
– 查看表suppliers_bak2的索引信息。
SHOW INDEX FROM suppliers_bak2;
在水果销售过程中，经常要查看每个客户所下订单的次数和消费的总金额，则可以将这个复杂查询建成一个视图，再通过查询该视图完成查询操作。
CREATE VIEW tj AS
SELECT c.c_id 客户编号,COUNT(*) 下单次数
,SUM(quantity*item_price) 总金额
FROM customers c,orders o,orderitems oi
WHERE c.c_id = o.c_id AND o.o_num = oi.o_num
GROUP BY c.c_id;
SELECT * FROM tj;
修改例3-65建立的视图v_fruit，取消约束条件检查。
CREATE OR REPLACE VIEW v_fruit
AS
SELECT f_id,s_id,f_name,f_price FROM fruits
WHERE f_price >= 10;
修改视图v_fruit，查询供应商101供应的水果名称和价格。
ALTER VIEW v_fruit(水果名称,水果价格)
AS
SELECT f_name,f_price FROM fruits
WHERE s_id=101;
根据fruits表创建视图，查询每个供应商供应水果的数量。
CREATE VIEW v_tj
AS
SELECT s_id 供应商编号,COUNT(*) 供应水里数量
FROM fruits
GROUP BY s_id;
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
12
0- 0
已为社区贡献3条内容
所有评论(0)