MYSQL删除一条记录

Question

我正在进行一些修订，以下是要做的:

删除属于“A Wilks”的Beetle

person (driver-id, name, address)
car (license, model, year)
accident (report-number, date, location)
owns (driver-id, license) 
participated (driver-id, car, report-number, damage-amount)

这是我想到的:

DELETE FROM car
WHERE model = 'beetle'

然而，我知道这是错误的，因为它将删除所有甲壳虫汽车，而不仅仅是A·威尔克斯拥有的那辆。

- Callum

---

你能编写一个(SELECT)查询以展示所有车主为'A'且车型为'beetle'的汽车吗？如果可以，将该查询语句改为一个DELETE语句会很容易。 - ypercubeᵀᴹ

Answer 1

DELETE FROM car
WHERE model = 'beetle'
AND license = (
    SELECT o.license from owns o INNER JOIN person p
    ON o.driver-id = p.driver-id
    WHERE p.name = 'A Wilks'
)

- Achrome

Answer 2

DELETE FROM car
WHERE model = 'Beetle' AND license IN (
  SELECT license
  FROM owns
  INNER JOIN Person ON owns.driver_id = person.driver_id
  WHERE person_name = 'A Wilks'

如果你确定只有一个属于"A Wilks"的Beetle，你可以在查询的第二行中使用license =代替license IN。

请考虑坐下来用MySQL尝试一些查询；这样你会更好地理解这些材料 :)

- Ed Gibbs

Answer 3

DELETE FROM car 
USING car 
INNER JOIN owns 
INNER JOIN person
WHERE car.license=owns.license 
AND car.model='Beetle'
AND owns.driver-id=person.driver-id 
AND person.name='A Wilks';

http://dev.mysql.com/doc/refman/5.0/en///delete.html

- philippinedev

Answer 4

Philippinedev 给出的答案很好，我想指出为什么它比使用子查询的答案更好。

他的答案使用了连接语法（最佳性能和控制）：

DELETE FROM car 
USING car 
INNER JOIN owns 
INNER JOIN person
WHERE car.license=owns.license 
AND car.model='Beetle'
AND owns.driver-id=person.driver-id 
AND person.name='A Wilks';

他正在使用执行带有连接表的查询语法，并从DELETE FROM子句中删除任何匹配记录的语法。另一种方法使用子查询格式（易于编码，性能较差）：

DELETE FROM car
WHERE model = 'beetle'
AND license = (
    SELECT o.license from owns o INNER JOIN person p
    ON o.driver-id = p.driver-id
    WHERE p.name = 'A Wilks'
)

子查询在性能上比直接连接更昂贵。如果这是在一个真实的车辆管理部门系统上运行的查询（有数百万条记录），您会希望尽一切可能避免使用子查询，而改用直接连接。

- phil_scott_a_person

Answer 5

从车辆中删除 WHERE model ='beetle' and licence=(select licence owns INNER JOIN Person ON owns.driver_id = person.driver_id WHERE person_name = 'A Wilks')

- puneeth

Answer 6

我建议你给车辆添加所有者，然后进行如下操作：

DELETE FROM car
WHERE model = 'beetle' and owner = 'ownername'

除非您在car中包含owner，否则这将无法运行。

- pattyd

---

你不能在SQL中使用两个WHERE语句。所以你应该删除其中一个。 - greedybuddha

好的，谢谢！我注意到另一个人有两个WHERE，所以我以为我做错了......结果证明我是正确的！谢谢@greedybuddha！ - pattyd