对于以上的表结构，先分别插入五条数据：
INSERT INTO customer (name, gender, phone, address) VALUES
('张三', '男', '13912345678', '上海市浦东新区'),
('李四', '女', '13898765432', '北京市朝阳区'),
('王五', '男', '13788889999', '广东省深圳市'),
('赵六', '女', '13666668888', '江苏省南京市'),
('钱七', '男', '13555557777', '湖北省武汉市');
INSERT INTO car_model (brand, model, price) VALUES
('BMW', 'X5', 1200000),
('Audi', 'A4', 600000),
('Mercedes-Benz', 'S500', 1500000),
('Lexus', 'RX300', 550000),
('Honda', 'Civic', 200000);
INSERT INTO car (model_id, color, description) VALUES
(1, '白色', '全新未使用'),
(2, '黑色', '二手车，行驶里程10000公里'),
(3, '银色', '二手车，行驶里程5000公里'),
(4, '灰色', '全新未使用'),
(5, '红色', '全新未使用');
INSERT INTO order1 (customer_id, car_id, order_date, price) VALUES
(1, 1, '2021-01-01 10:00:00', 1000000),
(2, 2, '2021-01-02 11:00:00', 500000),
(3, 3, '2021-01-03 12:00:00', 1200000),
(4, 4, '2021-01-04 13:00:00', 450000),
(5, 5, '2021-01-05 14:00:00', 180000);
INSERT INTO employee (name, gender, phone, address, salary) VALUES
('张小明', '男', '13900001111', '上海市静安区', 8000),
('李小红', '女', '13811112222', '北京市海淀区', 10000),
('王小刚', '男', '13722223333', '广东省广州市', 12000),
('赵小丽', '女', '13633334444', '江苏省苏州市', 15000),
('钱小勇', '男', '13544445555', '湖北省襄阳市', 9000);
然后我们来进行一些多表连接查询：
1. 查询所有订单的详细信息，包括订单编号、顾客姓名、顾客电话、车型、颜色、价格和下单时间：
SELECT o.id, c.name, c.phone, cm.brand, cm.model, car.color, o.price, o.order_date
FROM order1 o
INNER JOIN customer c ON o.customer_id = c.id
INNER JOIN car ON o.car_id = car.model_id
INNER JOIN car_model cm ON car.model_id = cm.id;
2. 查询所有购买过 BMW X5 的顾客的姓名、电话和地址：
SELECT c.name, c.phone, c.address
FROM customer c
INNER JOIN order1 o ON c.id = o.customer_id
INNER JOIN car ON o.car_id = car.model_id
INNER JOIN car_model cm ON car.model_id = cm.id
WHERE cm.brand = 'BMW' AND cm.model = 'X5';
3. 查询所有女性员工的姓名、电话、地址和薪水：
SELECT name, phone, address, salary
FROM employee
WHERE gender = '女';
4. 查询所有下单时间在 2021 年 1 月 3 日之后的订单的顾客姓名、车型和价格：
SELECT c.name, cm.brand, cm.model, o.price
FROM order1 o
INNER JOIN customer c ON o.customer_id = c.id
INNER JOIN car ON o.car_id = car.model_id
INNER JOIN car_model cm ON car.model_id = cm.id
WHERE o.order_date >= '2021-01-03 00:00:00';
5. 查询所有车辆行驶里程不超过 5000 公里的二手车的品牌、型号、颜色和描述：
SELECT cm.brand, cm.model, car.color, car.description
FROM car
INNER JOIN car_model cm ON car.model_id = cm.id
WHERE car.description LIKE '%二手车%' AND car.description LIKE '%行驶里程%5000公里%';
1877
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
