Whether it is a newly graduated college students or have been in the field of software for many years of veteran, as long as the mention of DELETE data SQL Statement No one knows no one, then talk about the deletion of the SQL statement is showing off, oh ah, do not be ambitious:
The following two tables are available:
Table 1:
CREATE TABLE ' lm_r_user_info ' ( ' id ' CHAR ($) NOT null COMMENT ' user base information id ', ' Real_name ' VARCHAR () ' NOT null Commen T ' real name ', ' gender ' INT (one) default null COMMENT ' Gender ', ' birthday ' date DEFAULT null COMMENT ' Birth date ', ' nation ' in T (one) default null COMMENT ' Nation ', ' telephone ' varchar (a) default null COMMENT ' fixed phone ', ' mobile ' varchar (p) defaul T null COMMENT ' mobile phone ', ' email ' varchar (+) default null COMMENT ' email ', ' qq ' varchar ' default null COMMENT ' QQ Number ', ' WeChat ' varchar (+) default null COMMENT ', ' address ' varchar (+) default NULL COMMENT ' contact address ', ' Summary ' varchar (4000) default null COMMENT ' profile ', ' remark ' varchar (4000) default null COMMENT ' remarks ', PRIMARY K EY (' id '), KEY ' idx_user_info_realname ' (' real_name ') USING BTREE) engine=innodb DEFAULT Charset=utf8 comment= ' User base Information '
Table 2:
CREATE TABLE ' lm_r_user ' ( ' id ' char () NOT NULL COMMENT ' user ID ', ' user_info_id ' char () ' NOT null COMMENT ' user base information ID ', ' user_name ' varchar ($) DEFAULT null COMMENT ' user name ', ' password ' varchar ' not NULL COMMENT ' user password ', PRIMARY key (' id '), UNIQUE key ' user_idx_userinfoid ' (' user_info_id ') USING HASH) Engine=innodb DEFAULT Charset=utf8 comment= ' User Information '
Relationship between Table 1 and Table 2: Table 2 is associated with the user_info_id field and table 1.
Requirement: Delete user information with user name "Admin" and its basic information.
The stupidest way:
Based on the user name, obtain the corresponding user profile identification (that is, the corresponding user_info_id field in table 2) from table 2, and then execute the delete from Lm_r_user where user_name = 'admin ', then execute delete from Lm_r_user_info where id= ' query out the user information ID '
The flaw in this way: If you use this method to delete data, I'm afraid it will take three database connections, which virtually increases the pressure on the server side.
The most desirable way:
Delete U, ui from Lm_r_user_info UI inner join Lm_r_user u on u.user_info_id = ui.id where u.user_name = ' admin '
Advantages of this approach: only one data connection is required.
Talk about SQL statements that delete data