| 删除多表关联数据 |
|
[ 2008-7-17 14:08:00 | By: 追忆逝水华年 ]
|
|
2008.04.15 以下的内容只是针对MySql数据库 1. sql语句删除两个或多个关联中的数据 以前因为对数据库中的数据只是进行简单的操作,通常只是删除一个表中的数据(因为表与表中的关联性比较差,没有外键这样的约束),就像这样 
delete from tableA a
where
1
=
1
and a.id
=
1
;
delete后面不用指定表的名称,直到最近要删除两个表中的数据,表A与表B,表B中的内容是附属于表A的,也就是说如果表A中的某一条记录删除了,表B中的记录也要删除,因为如果不删除B中的记录,那就会形成垃圾数据, 表A中的一项为itemid,表B中也一个Column也为itemid,是以外键的形式指向表A. 当我这样写的时候就报错了:
delete from A a,B b
where
1
=
1
and a.itemid
=
b.itemidand a.id
<
5
提示你where后的语句有问题(当然问题不在where语句),当from后面的表涉及到多个的时候,这个语句就要有点变化了
这样delete a,bfrom A a,B b
where
1
=
1
and a.itemid
=
b.itemidand a.id
<
5
或者是这样delete from a,b
using
A a,B b
where
1
=
1
and a.itemid
=
b.itemidand a.id
<
5
from 中的表的名称没有先后顺序的要求,以下为<Sams - Mysql Tutorial(2003).chm>中的说明:
delete 语法:
DELETE
[
LOW_PRIORITY
]
[
QUICK
]
FROM
table_name
[
WHERE where_definition
]
[
ORDER BY ...
]
[
LIMIT rows
]
or
DELETE
[
LOW_PRIORITY
]
[
QUICK
]
table_name
[
.*
]
[
, table_name[.*
]
...]
FROM
table
-
references
[
WHERE where_definition
]
or
DELETE
[
LOW_PRIORITY
]
[
QUICK
]
FROM
table_name
[
.*
]
[
, table_name[.*
]
...] USING
table
-
references
[
WHERE where_definition
]
示例代码为:
1
. 删除一个表中的数据
delete
from
department
where
name
=
'
Asset Management
'
;
2
. 删除两个表中的数据
delete
employee, employeeSkills
from
employee, employeeSkills, department
where
employee.employeeID
=
employeeSkills.employeeID
and
employee.departmentID
=
department.departmentID
and
department.name
=
'
Finance
'
;
3
. 删除两个表中的数据,用using语法
delete
from
employee, employeeSkillsusing employee, employeeSkills, department
where
employee.employeeID
=
employeeSkills.employeeID
and
employee.departmentID
=
department.departmentID
and
department.name
=
'
Finance
'
;
|
3269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
