The SQL statement for data deletion and the SQL statement for data deletion

The SQL statement for data deletion and the SQL statement for data deletion

No matter whether you are a new college student or a veteran who has been fighting in the software field for many years, as long as you mention the SQL statements used to delete data, it is easy to say that the SQL statements used to delete data are sold out, do not go far:

There are two tables:

Table 1:

Create table 'lm _ r_user_info '('id' CHAR (36) not null comment 'user basic info id', 'real _ name' VARCHAR (30) not null comment 'real name', 'gender' INT (11) default null comment 'gender', 'birthday' date default null comment 'birthday ', 'nation' INT (11) default null comment' ethnic group ', 'telphone' VARCHAR (20) default null comment' landline phone, 'mobile' VARCHAR (20) default null comment 'mobile phone ', 'email' VARCHAR (100) default null comment' email ', 'qq' VARCHAR (20) default null comment 'qq No ', 'wheel' VARCHAR (100) default null comment', 'address' VARCHAR (100) default null comment 'Contact address', 'summary 'VARCHAR (4000) default null comment 'profile ', 'remark' VARCHAR (4000) default null comment' COMMENT', primary key ('id '), KEY 'idx _ user_info_realname' ('real _ name') using btree) ENGINE = innodb default charset = utf8 COMMENT = 'user basic information'

Table 2:

Create table 'lm _ r_user '('id' CHAR (36) not null comment 'user id', 'user _ info_id' CHAR (36) not null comment 'user basic information id', 'user _ name' VARCHAR (50) default null comment 'username', 'Password' VARCHAR (100) 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 table 1 through the user_info_id field.

Requirement: Delete the user information and basic information with the username "admin.

The most silly way:

According to the user name, first obtain the ID of the corresponding user basic information from table 2 (that is, the user_info_id field in Table 2), then execute delete from lm_r_user where user_name = 'admin ', run delete from lm_r_user_info where id = 'user information identified'

Disadvantages of this method: if this method is used to delete data, it may take three database connections, which increases the pressure on the server.

The most desirable method is:

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 method: only one data connection is required.

In fact, the second method is not easy to think of because you are familiar with the SQL statement "delete from table name" to delete data. This method can be replaced by another method: "delete alias. * from table name alias "(have you seen it ?), Some people may be confused when writing a blog --

1. This is silly, "delete alias. * Isn't the alias of the from table name "delete * from Table Name"? Why do I need an alias? hehahaha, if you think the alias here is redundant, so it can only prove how idiotic you are-"delete * from table name" cannot be executed, and there must be aliases (at least in the MySQL database ), however, this method ("delete * from table name") is considered correct in w3cschool.

2. Since the SQL statement for deleting data can be written as "delete alias. * from table name alias ", can I use such an SQL statement (" delete alias. column name from table name alias) delete one or more columns? Heh, don't be kidding. You must know that delete is used to delete the row in the data table.