如何删除特定用户名的所有数据? [英] How to delete all the data of a specific username?

问题描述

我想从数据库的所有表中删除 UserName 的所有数据.
实际上，我在所有表中都将 UserName 用作 FK .
现在我想从数据库中删除特定的用户.

我尝试过的事情:

删除s.UserName，p.Name，p.Father_Name，s.Email，p.DOB，p.Gender，p.Nationality，p.Domicile，p.CNIC，p.Mobile，p.Address等. SSC_OM，E.SSC_TM，E.SSC_EB，e.HSSC_OM，e.HSSC_TM，e.HSSC_EB，d.Choices_1，d.Choices_2，d.Choices_3
从注册s
内部联接Pers_D p ON p.UserName = s.UserName
在e.UserName = p.UserName上的INNER JOIN Edu_D e
d.UserName = e.UserName上的INNER JOIN Dep_S d
在[d] .UserName之类的'％Abuzar％'

解决方案

0x01AA的含义是，当您在注册之间建立外键关系时表UserName列以及Pers_D，Edu_D和Dep_s Username条目，您可以在DELETE CASCADE上添加一个约束: SQL Server:外部具有级联删除的键 [ ^ ]
然后发生的是，当您从主表中删除行时-注册-系统将自动为您从依赖表中删除所有行:

  Delete   FROM 注册 WHERE  UserName =  @ UserName  

将自动为您完成此操作.

并且不要将LIKE与DELETE一起使用！ (甚至是UPDATE-在可能的情况下，请保持SELECT的状态)这是非常危险的.
假设您要删除用户"old"，并且您说:

  Delete   FROM 注册 WHERE 用户名喜欢 ' ％old％' 

用户"old"将离开-但"bold"，"oldsmobile"和..."Stack Holder"也将离开.
像是危险的-在使用时要当心！

我还建议将UserName用作键字段是一个坏主意-使用ID值代替它可以节省空间，节省时间并允许用户更改其名称而无需更新所有相关记录.

从注册中删除，其中UserName = @ UserName

I want to delete all the data of a UserName from all the table from database.
Actually I have UserName used in all the tables as FK.
Now i want to delete a specific User from my database.

What I have tried:

DELETE s.UserName, p.Name,p.Father_Name,s.Email,p.DOB,p.Gender,p.Nationality,p.Domicile,p.CNIC,p.Mobile,p.Address, e.SSC_OM,E.SSC_TM,E.SSC_EB,e.HSSC_OM,e.HSSC_TM,e.HSSC_EB, d.Choices_1,d.Choices_2,d.Choices_3
FROM Signup s
INNER JOIN Pers_D p ON p.UserName = s.UserName
INNER JOIN Edu_D e on e.UserName = p.UserName
INNER JOIN Dep_S d on d.UserName = e.UserName
WHERE [d].UserName LIKE ''%Abuzar%''

解决方案

What 0x01AA meant was that when you establish the Foreign Key relationship between the Signup table UserName column and the Pers_D, Edu_D, and Dep_s Username entries you can add a Constraint ON DELETE CASCADE: SQL Server: Foreign Keys with cascade delete[^]
What happens then is that when you delete the row from the master table - Signup - the system will automatically remove all teh rows from dependant tables for you:

DELETE FROM Signup WHERE UserName = @UserName

Will do it automatically for you.

And don''t use LIKE with DELETE! (Or even UPDATE - keep LIKE for SELECT where possible) it''s very dangerous.
Suppose you want to DELETE user "old" and you say:

DELETE FROM Signup WHERE UserName LIKE '%old%'

The user "old" will go - but so will "bold", "oldsmobile" and ... "Stack Holder".
LIKE is dangerous - be very careful where you use it!

I would also suggest that using the UserName as a key field is a bad idea - use an ID value instead and it saves space, saves time, and allow the user to change his name without you having to update all related records.

delete from Signup where UserName=@UserName

这篇关于如何删除特定用户名的所有数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！