Mysql table alias usage

Mysql table alias can be used to solve the nested query problem. The following describes how to use Mysql table alias. If you are interested in this aspect, take a look.

MySQL has been used for a long time and has been plagued by the absence of nested queries in MySQL. Although MySQL 4.1 supports this feature, all my PHP programs use the MySQL function library instead of the MySQLi function library, so 4.1 of the features are not used.

Previously, when nested queries were needed, they were generally converted into join queries for two tables. However, in a recent program, it was found that the nested query is performed twice on the same table, so it cannot be directly converted into join queries for the two tables. It is too troublesome to use PHP instead of MySQL to do such a thing.) So I always wanted to find a simple alternative. Perhaps it was a burst of inspiration. Maybe I recently went through the MySQL manual every day to have a better understanding of MySQL queries. Today I suddenly thought that I could use the Mysql table alias to solve this problem.

I used to think that the Mysql table alias is only used to make the SQL statement brief and has no other use. I discovered it after experiment today, A table can be used as multiple tables with different aliases. Here is an example of my program:

My program has a category table. In order to implement unlimited multi-level classification, I Have A parent_id field in the category table. This field stores the parent category id of the category represented by this record, if this field is null, it indicates the top-level category. Now I need to delete a category. This category may have subcategories. Therefore, after this category is deleted, the parent_id of its subcategories needs to be changed, instead of pointing to a non-existent category, I want to delete this category and change its parent category to its parent category. In this case, I didn't know what to do before. Now, use the following statement:

 
 

  
  
lock tables `categories` write, `categories` as `c1` write,   `categories` as `c2` write;   
  
  
 
  
  
update `categories` as `c1`, `categories` as `c2` set `c1`.`parent_id` = `c2`.`parent_id` where `c1`.`parent_id` = `c2`.`cat_id` and `c2`.`cat_id` = $cat_id;   
  
  
 
  
  
delete from `categories` where `cat_id` = $cat_id;   
  
  
 
  
  
unlock tables;  
  
  
 
 
 

Note that when locking a table, you must lock both the table to be operated and the Mysql table alias; otherwise, the following statements may fail.
 

Provides you with an in-depth understanding of Mysql external locks

Advantages of Mysql Merge table

Method for randomly obtaining Mysql Data

Back up data based on Mysql tables

How to clear a table in Mysql