sql delete同时删除多表相关联记录

sql delete同时删除多表相关联记录,sqlserver 支持级联更新和删除,oracle 只支持级联删除.

删除包含主键值的行的操作,该值由其它表的现有行中的外键列引用,在级联删除中,还删除其外键值引用删除的主键值的所有行,如:

1. createdatabasetemp
2. go
3. usetemp
4. go
6. createtableUserInfo
7. (
8. UserIdintidentity(1,1)primarykey,
9. UserNamevarchar(20),--用户名
10. passwordvarchar(20)notnull--密码
11. )
13. createtableUserDetails
14. (
15. idintidentity(1,1)primarykey,
16. namevarchar(50)notnull,--真实姓名
17. userIdint,
18. foreignkey(userId)referencesUserInfo(UserId)ondeletecascade
19. )
21. insertUserInfovalues('ly','jeff')
22. insertUserInfovalues('wzq','wzqwzq')
23. insertUserInfovalues('lg','lglg')
25. insertUserDetailsvalues('李四',1)
26. insertUserDetailsvalues('王五',2)
27. insertUserDetailsvalues('刘六',3)
28. --phpfensi.com
29. altertable表名
30. addconstraint外键名
31. foreignkey(字段名)references主表名(字段名)
32. ondeletecascade

语法:

1. ForeignKey
2. (column[,...n])
3. referencesreferenced_table_name[(ref_column[,...n])]
4. [ondeletecascade]
5. [onupdatecascade]

注释:

column:列名

referenced_table_name:外键参考的主键表名称

ref_name:外键要参考的表的主键列

on delete:删除级联

on update:更新级联

000,请您对文章做出评价)此时:Delete From UserInfo Where UserId = 1 就可删除UserInfo表和UserDetails表的UserId=1 的内容

看一下MySql数据库教程怎么操作多表删除呢,有时我们可以直接用delete 来删除.

delete 语法:

1. DELETE[LOW_PRIORITY][QUICK]FROMtable_name
2. [WHEREwhere_definition]
3. [ORDERBY...]
4. [LIMITrows]
6. or
8. DELETE[LOW_PRIORITY][QUICK]table_name[.*][,table_name[.*]...]
9. FROMtable-references
10. [WHEREwhere_definition]
11. --phpfensi.com
12. or
14. DELETE[LOW_PRIORITY][QUICK]
15. FROMtable_name[.*][,table_name[.*]...]
16. USINGtable-references
17. [WHEREwhere_definition]

示例代码为:

1.删除一个表中的数据

delete from department where name='Asset Management';

2.删除两个表中的数据

1. deleteemployee,employeeSkills
2. fromemployee,employeeSkills,department
3. whereemployee.employeeID=employeeSkills.employeeID
4. andemployee.departmentID=department.departmentID
5. anddepartment.name='Finance';

3.删除两个表中的数据,用using语法

1. deletefromemployee,employeeSkills
2. usingemployee,employeeSkills,department
3. whereemployee.employeeID=employeeSkills.employeeID
4. andemployee.departmentID=department.departmentID
5. anddepartment.name='Finance';