学习

JOIN 类型	作用	结果
`LEFT JOIN`（左外连接）	保留左表 (`car`) 的全部行，右表 (`carsales`) 若匹配不到则填 `NULL`	因而未售出的车辆仍会出现，`salesDate` 为 `NULL`

Week-1f-SQL-revision-VSCode-DBeaver · Jimmy Huang's Blog

维度	`NOT IN`	`NOT EXISTS`
NULL 安全性	⚠️ 遇到 `NULL` 可能返回 0 行	✅ 安全
典型执行方式	子查询结果集去重 → 哈希/集合比对	反连接 (anti-join)；行行索引探测
推荐程度	小表且确定无 NULL 时可用	通用写法，官方文档/社区最推荐
可读性	简单直观	略多两行但专业、易维护

片段	说明
`SELECT make`	报告行所属的品牌（分组键）。
`COUNT(DISTINCT model) AS model_cnt`	统计该品牌不同型号的数量。 `DISTINCT` 先对同品牌内的 `model` 去重，再计数。
`FROM car`	数据来源仍是 `car` 表，因而“含售出与未售”。
`GROUP BY make`	将所有行按品牌分桶。
`HAVING COUNT(DISTINCT model) >= 2`	过滤分组：只保留“车型数≥2”的桶。与 `WHERE` 不同，它在聚合之后起作用。
`ORDER BY model_cnt DESC`	车型多的品牌排前；并自动以 `make` 升序作为次排序键。

sql

-- 清场：先删子表再删父表
BEGIN EXECUTE IMMEDIATE 'DROP TABLE carsales'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE car';      EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE customer'; EXCEPTION WHEN OTHERS THEN NULL; END;
/

-- 父表先建
CREATE TABLE Car (
  carID            NUMBER(5)     PRIMARY KEY,
  make             VARCHAR2(20)  NOT NULL,
  model            VARCHAR2(30),
  year             NUMBER(4),
  colour           VARCHAR2(25),
  advertisedPrice  NUMBER(10)
);

CREATE TABLE Customer (
  customerID NUMBER(5)    PRIMARY KEY,
  cName      VARCHAR2(20) NOT NULL,
  cPhone     VARCHAR2(10),
  address    VARCHAR2(50)
);

-- 最后建子表（外键才找得到父表）
CREATE TABLE CarSales (
  carID           NUMBER(5)   NOT NULL,
  customerID      NUMBER(5)   NOT NULL,
  salesDate       DATE        NOT NULL,
  purchasedPrice  NUMBER(10)  NOT NULL,
  stampDuty       NUMBER(8,2) NOT NULL,
  PRIMARY KEY (carID, customerID),
  FOREIGN KEY (carID)      REFERENCES Car(carID),
  FOREIGN KEY (customerID) REFERENCES Customer(customerID)
);

sql

-- CAR
INSERT ALL
INTO Car VALUES (1,'Holden','Cruze',2015,'Black',25780)
INTO Car VALUES (2,'BMW','520d',2016,'Grey',98800)
INTO Car VALUES (3,'Audi','A5',2016,'Black',68200)
INTO Car VALUES (4,'Holden','Commodore',2008,'Grey',12650)
INTO Car VALUES (5,'Honda','Civic',2012,'Red',13488)
INTO Car VALUES (6,'Jeep','Grand Cherokee',2006,'Black',21999)
INTO Car VALUES (7,'Lexus','RX350',2009,'Red',33500)
INTO Car VALUES (8,'Mazda','3Series',2011,'Blue',13999)
INTO Car VALUES (9,'Nissan','Dualis',2010,'White',18888)
INTO Car VALUES (10,'Volkwagen','Golf',2015,'White',39888)
INTO Car VALUES (11,'Mercedes Benz','C200',2006,'Blue',16995)
INTO Car VALUES (12,'Subaru','Outback',2014,NULL,33950)
INTO Car VALUES (13,'Honda','City',2010,'Grey',7490)
INTO Car VALUES (14,'Mini','Cooper',2009,'Black',19750)
INTO Car VALUES (15,'Toyota','Corolla',2013,'White',23888)
SELECT * FROM dual;

-- CUSTOMER
INSERT ALL
INTO Customer VALUES (1,'Jones','0411111111','1 King St')
INTO Customer VALUES (2,'Lily','0422222222','2 Queen St')
INTO Customer VALUES (3,'Rex','0433333333','3 Duke St')
INTO Customer VALUES (4,'Amy','0444444444','4 Prince St')
INTO Customer VALUES (5,'Tom','0455555555','5 Victoria Rd')
SELECT * FROM dual;

-- CARSALES
INSERT ALL
INTO CarSales VALUES (1,4,  DATE '2015-02-04', 25780,  824.96)
INTO CarSales VALUES (8,3,  DATE '2015-12-12', 13999,  559.96)
INTO CarSales VALUES (10,1, DATE '2016-06-14', 39888, 1276.42)
INTO CarSales VALUES (2,2,  DATE '2016-05-18', 98800, 5631.60)
INTO CarSales VALUES (4,2,  DATE '2016-07-13', 12650,  506.00)
SELECT * FROM dual;

COMMIT;

sql

SELECT make, model, year, colour
FROM car
WHERE year >= 2014 AND colour IN ('Black','White')
ORDER BY year DESC, make;           
-- 预期 3 行：2016(Audi A5 Black)、2015(Holden Cruze Black)、2015(Volkwagen Golf White)

sql

SELECT cs.salesDate, c.make, c.model,
       cs.purchasedPrice, cs.stampDuty,
       (cs.purchasedPrice + cs.stampDuty) AS total_price
FROM carsales cs
JOIN car c ON c.carID = cs.carID
ORDER BY total_price DESC;          -- 预期 5 行，金额从大到小（BMW 520d 应该第一）

sql

SELECT carID, customerID, salesDate
FROM carsales
WHERE salesDate BETWEEN DATE '2015-01-01' AND DATE '2015-12-31'
ORDER BY salesDate;                  -- 预期 2 行（2015-02-04 与 2015-12-12）

sql

SELECT c.carID, c.make, c.model, c.advertisedPrice
FROM car c
WHERE c.carID NOT IN (SELECT carID FROM carsales)
ORDER BY c.carID;                    -- 预期 10 行（除去 1,2,4,8,10）

sql

-- 看看当前颜色
SELECT carID, make, model, colour FROM car WHERE carID = 13;

-- 修改为 Black
UPDATE car SET colour = 'Black' WHERE carID = 13;

-- 验证
SELECT carID, make, model, colour FROM car WHERE carID = 13;

-- 还原（不想保留就回滚）
ROLLBACK;

-- 再验：应恢复为 Grey
SELECT carID, make, model, colour FROM car WHERE carID = 13;

sql

-- 估算会影响多少行
SELECT COUNT(*) FROM car WHERE year <= 2010;

SAVEPOINT before_raise;

-- 老车（<=2010）标价上调 5%
UPDATE car
SET advertisedPrice = ROUND(advertisedPrice * 1.05)
WHERE year <= 2010;

-- 验证一个样本
SELECT carID, year, advertisedPrice FROM car WHERE year <= 2010 ORDER BY carID FETCH FIRST 5 ROWS ONLY;

-- 反悔：回到保存点（撤销这次批量调价）
ROLLBACK TO before_raise;

-- 确认已恢复
SELECT carID, year, advertisedPrice FROM car WHERE year <= 2010 ORDER BY carID FETCH FIRST 5 ROWS ONLY;

sql

-- carID=1 在销售表里出现过吗？
SELECT * FROM carsales WHERE carID = 1;

-- 试图删除这辆车（预期 ORA-02292: integrity constraint violated）
DELETE FROM car WHERE carID = 1;

sql

-- 多少辆未售出？
SELECT COUNT(*) AS unsold
FROM car c
WHERE NOT EXISTS (SELECT 1 FROM carsales cs WHERE cs.carID = c.carID);

-- 删除未售出的“极早年份车”（示例：<=2009）
DELETE FROM car c
WHERE c.year <= 2009
  AND NOT EXISTS (SELECT 1 FROM carsales cs WHERE cs.carID = c.carID);

-- 验证删除了几行
SELECT COUNT(*) FROM car;

-- 还原数据
ROLLBACK;

-- 再验：行数应回到 15
SELECT COUNT(*) FROM car;

sql

-- 选一辆未售出的车看看（示例：carID=15 是否未售出）
SELECT carID, make, model FROM car
WHERE carID = 15
  AND NOT EXISTS (SELECT 1 FROM carsales WHERE carID = 15);

-- 插入销售（若上面查到有结果）
INSERT INTO carsales (carID, customerID, salesDate, purchasedPrice, stampDuty)
VALUES (15, 5, DATE '2016-11-01', 23888, 716.64);

-- 验证
SELECT * FROM carsales WHERE carID = 15;

COMMIT;  -- 想保留就提交；不想保留就 ROLLBACK

javascript

/* 3.1-a  顾客姓名 + 购买车辆 + 购买价（总 5 行，BMW 520d 最高） */
SELECT ct.cName,
       c.make || ' ' || c.model  AS car,
       cs.purchasedPrice
FROM   carsales cs
JOIN   car       c  ON c.carID = cs.carID
JOIN   customer  ct ON ct.customerID = cs.customerID
ORDER  BY cs.purchasedPrice DESC;

javascript

/* 3.1-b  为每笔销售算“实际落地价”=购买价+印花税 */
SELECT ct.cName,
       c.make, c.model,
       cs.purchasedPrice,
       cs.stampDuty,
       cs.purchasedPrice + cs.stampDuty  AS total_price
FROM   carsales cs
JOIN   customer ct ON ct.customerID = cs.customerID
JOIN   car      c  ON c.carID       = cs.carID
ORDER  BY total_price DESC;

javascript

/* 3.2  列出所有车辆及其销售日期；未售出的显示 NULL */
SELECT c.carID, c.make, c.model,
       cs.salesDate
FROM   car c
LEFT   JOIN carsales cs ON cs.carID = c.carID
ORDER  BY c.carID;
-- ✅ 应看到 15 行；其中 10 行 salesDate 为 NULL（未售出）

javascript

/* 3.3-a  未售出车辆 (NOT IN) */
SELECT carID, make, model
FROM   car
WHERE  carID NOT IN (SELECT carID FROM carsales)
ORDER  BY carID;

/* 3.3-b  同题，用 NOT EXISTS（推荐写法） */
SELECT c.carID, c.make, c.model
FROM   car c
WHERE  NOT EXISTS (SELECT 1 FROM carsales cs WHERE cs.carID = c.carID)
ORDER  BY c.carID;
-- ✅ 两条语句都应返回 10 行且结果一致

javascript

/* 3.4  给每个品牌算平均标价，并仅显示
         标价高于该品牌平均值的车型 */
SELECT c.make,
       c.model,
       c.advertisedPrice AS price,
       (SELECT AVG(advertisedPrice)
          FROM car c2
         WHERE c2.make = c.make)     AS avg_make_price
FROM   car c
WHERE  c.advertisedPrice >
       (SELECT AVG(advertisedPrice)
          FROM car c2
         WHERE c2.make = c.make)
ORDER  BY c.make, price DESC;

javascript

/* 3.5-a  每个品牌有多少辆车？（含未售出） */
SELECT make,
       COUNT(*)          AS car_cnt,
       MIN(year)         AS oldest,
       MAX(year)         AS newest
FROM   car
GROUP  BY make
ORDER  BY car_cnt DESC, make;

javascript

/* 3.5-b  只想看“车型数 ≥2” 的品牌 */
SELECT make,
       COUNT(DISTINCT model) AS model_cnt
FROM   car
GROUP  BY make
HAVING COUNT(DISTINCT model) >= 2
ORDER  BY model_cnt DESC;

javascript

car 表（部分）
┌────┬─────┬────────┐
│ ID │ make│ model  │
├────┼─────┼────────┤
│ 1  │ BMW │ 520d   │
│ 2  │ BMW │ X3     │
│ 3  │ BMW │ X3     │
│ 4  │ Audi│ A4     │
│ 5  │ Audi│ A6     │
│ 6  │ Audi│ A6     │
│ 7  │ VW  │ Golf   │
└────┴─────┴────────┘
→ GROUP BY make
→ COUNT(DISTINCT model)
→ HAVING ≥2
结果
┌─────┬──────────┐
│make │ model_cnt│
├─────┼──────────┤
│Audi │    2     │
│BMW  │    2     │
└─────┴──────────┘

javascript

/* 3.6  统计各顾客的总消费（购买价+印花税），按高→低 */
SELECT ct.cName,
       SUM(cs.purchasedPrice + cs.stampDuty) AS total_spent
FROM   carsales cs
JOIN   customer ct ON ct.customerID = cs.customerID
GROUP  BY ct.cName
ORDER  BY total_spent DESC;

javascript

/* 4.1-a  每个品牌按标价由高到低，给车型编号 ROW_NUMBER */
SELECT make,
       model,
       advertisedPrice AS price,
       ROW_NUMBER() OVER (PARTITION BY make ORDER BY advertisedPrice DESC) AS rn
FROM   car
ORDER  BY make, rn;

学习

学习

学习

Week-1f-SQL-revision-VSCode-DBeaver

本页目录

学习

Week-1f-SQL-revision-VSCode-DBeaver

本页目录

Connection to the ORACLE database

VS Code

Docker启动Oracle XE

Create tables

Insert into

Simple query retrieval

1. 投影 & 别名

2. 去重

3. 过滤+排序

4. 模糊匹配

5. 连接 + 计算列 + 排序

6. 日期范围

7. 子查询：未售出车辆

Updating and deleting records

1. 单行 UPDATE + ROLLBACK

2. 批量 UPDATE + SAVEPOINT

3. 外键约束下的 DELETE（预期报错）

4. 安全删除：只删“未售出”的车，然后回滚

5. 插入一条真实销售记录并保留

Joining multiple tables

1. INNER JOIN

2. LEFT OUTER JOIN

3. NOT IN vs NOT EXISTS（反向子查询）

4. 相关子查询

Aggregate functions and group by

1. GROUP BY + 聚合

2. JOIN 后分组统计

窗口函数（ROW_NUMBER / RANK / SUM OVER）