However, in MySQL, there is no keyword rowNum. If my table does not have a self incremented primary key id, or the primary key I use is an unordered UUID. And I have a requirement: how to delete the data in rows 6 to 10?
In daily development, I wonder if you have ever encountered a time when the query condition is "line"? In fact, there are many scenarios that will be used.
For example, some time ago, there was an application information table in our business. This table was directly imported by our colleague Xiao Liu with a csv file. People from other companies will manually analyze these data. There was no self incrementing primary key when importing. Xiao Liu didn't expect to have left a hidden danger to himself.
There are 20w rows of data in this table, of which items 8w - 15w are redundant and cannot be re exported, because colleagues in other companies may be dealing with it. How to delete the redundant data?
In order not to be invited by the leader to climb the mountain, he came to me for help.
Xiao Liu: "brother ha, help, help, MySQL doesn't have a self increasing primary key. How can I delete the data of [8w,15w]?"
Chen haha: "ah? MySQL doesn't have rowNum, so what? I won't, won't ~"
Xiao Liu: "a bunch! Brother ha, come on"
Chen haha: "Oh, I'm in a hurry for this job. Let's talk about it in the afternoon."
Xiao Liu: "tonight, Wangjing Xiaoyao, don't invite me to be your son!"
Chen ha ha: "what strings are all brothers. What I say is like I'm afraid you'll brag that B won't invite you. Come on, come on, let's see how to deal with it."
Xiao Liu: "
In fact, there is no rownum pseudo column in MySQL, but we can write our own function definitions. Online data is inconvenient. Let me take some test data as examples (15), as follows
mysql> select * from t_student; +------------+-----+-----+-------+----------+-----------+ | NAME | SEX | AGE | CLASS | GRADE | HOBBY | +------------+-----+-----+-------+----------+-----------+ | Ha ha Chen | male | 15 | 18 class | 9 Grade 1 | surf the internet | | Hu Yapeng | male | 15 | 18 class | 9 Grade 1 | delicious food | | Xu Linan | female | 14 | 18 class | 9 Grade 1 | read | | Chen zining | female | 15 | 18 class | 9 Grade 1 | watch movie | | Liu Xiaoli | female | 14 | 18 class | 9 Grade 1 | Jin Xiche | | Chen ha ha 1 | male | 15 | 18 class | 9 Grade 2 | surf the internet | | Hu Yapeng 1 | male | 15 | 18 class | 9 Grade 2 | delicious food | | Xu Linan 1 | female | 14 | 18 class | 9 Grade 2 | read | | Chen zining 1 | female | 15 | 18 class | 9 Grade 2 | watch movie | | Liu Xiaoli 1 | female | 14 | 18 class | 9 Grade 1 | Jin Xiche | | Chen ha ha 2 | male | 15 | 18 class | 9 Grade 2 | surf the internet | | Hu Yapeng 2 | male | 15 | 18 class | 9 Grade 2 | delicious food | | Xu Linan 2 | female | 14 | 18 class | 9 Grade 2 | read | | Chen zining 2 | female | 15 | 18 class | 9 Grade 2 | watch movie | | Liu Xiaoli 2 | female | 14 | 18 class | 9 Grade 1 | Jin Xiche | +------------+-----+-----+-------+----------+-----------+ 15 rows in set (0.00 sec)
How to query the row values of these data? The SQL is as follows:
-- Query data rows without self incrementing primary key( rownum)，Line number select @rownum:=@rownum+1 AS rownum,`NAME`,`SEX`,`CLASS`,`GRADE` from t_student ,(SELECT @rownum:=0) r;
The query results are as follows:
mysql> select @rownum:=@rownum+1 AS rownum,`NAME`,`SEX`,`CLASS`,`GRADE` from t_student ,(SELECT @rownum:=0) r; +--------+------------+-----+-------+----------+ | rownum | NAME | SEX | CLASS | GRADE | +--------+------------+-----+-------+----------+ | 1 | Ha ha Chen | male | 18 class | 9 Grade 1 | | 2 | Hu Yapeng | male | 18 class | 9 Grade 1 | | 3 | Xu Linan | female | 18 class | 9 Grade 1 | | 4 | Chen zining | female | 18 class | 9 Grade 1 | | 5 | Liu Xiaoli | female | 18 class | 9 Grade 1 | | 6 | Chen ha ha 1 | male | 18 class | 9 Grade 2 | | 7 | Hu Yapeng 1 | male | 18 class | 9 Grade 2 | | 8 | Xu Linan 1 | female | 18 class | 9 Grade 2 | | 9 | Chen zining 1 | female | 18 class | 9 Grade 2 | | 10 | Liu Xiaoli 1 | female | 18 class | 9 Grade 1 | | 11 | Chen ha ha 2 | male | 18 class | 9 Grade 2 | | 12 | Hu Yapeng 2 | male | 18 class | 9 Grade 2 | | 13 | Xu Linan 2 | female | 18 class | 9 Grade 2 | | 14 | Chen zining 2 | female | 18 class | 9 Grade 2 | | 15 | Liu Xiaoli 2 | female | 18 class | 9 Grade 1 | +--------+------------+-----+-------+----------+ 15 rows in set (0.00 sec)
You can see the row number queried. How can I delete the rownum row found above? In fact, this idea is not feasible, because the above rownum is a display value, and there is no other practical effect.
But, I can find the unique key set to be deleted by finding the unique columns in the table (such as UUID, MD5, package NAME and ID card ID) and querying rownum through the range criteria. For example, if the only key in this table is "NAME", I can delete it through sub query by finding the unique key "NAME" in row [6,10] (here, there is "1" after the person NAME) Delete). The SQL is as follows:
Let's take a look at the data in row [6,10]. The SQL is as follows:
-- Query the data in rows 6 to 10. SELECT * from (select @rownum:=@rownum+1 AS rownum,`NAME`,`SEX`,`CLASS`,`GRADE` from t_student ,(SELECT @rownum:=0) r) t where t.rownum between 6 and 10;
Return result:
mysql> SELECT * from (select @rownum:=@rownum+1 AS rownum,`NAME`,`SEX`,`CLASS`,`GRADE` from t_student ,(SELECT @rownum:=0) r) t
-> where t.rownum between 6 and 10;
# last
There are also ways to brush questions for the interview. It is suggested that it is best to carry out according to the topic, and then from basic to advanced, from shallow to deep, the effect will be better. Of course, I also put all these contents in one pdf The document is divided into the following topics:
* Java Basic part
* Algorithm and programming
* Database part
* Popular frameworks and new technologies( Spring+SpringCloud+SpringCloudAlibaba)
Of course, this interview document is more than that. In fact, it looks like JVM,Design mode ZK,MQ,The interview contents of other parts such as data structure are involved, because the length of the article is not fully described here.
**As a programmer, phased learning is essential and needs to maintain a certain continuity. This time, I systematically reviewed some key knowledge points at this stage, which not only consolidated my foundation, but also improved the breadth and depth of my knowledge.**
Finally, let me remind you that if you want to learn, but you have no dry learning materials, all the above materials can be shared with you free of charge, as long as you give more support
Of course, the document is more than that. It's actually like JVM,Design mode ZK,MQ,The interview contents of other parts such as data structure are involved, because the length of the article is not fully described here.
**As a programmer, phased learning is essential and needs to maintain a certain continuity. This time, I systematically reviewed some key knowledge points at this stage, which not only consolidated my foundation, but also improved the breadth and depth of my knowledge.**
Finally, let me remind you that if you want to learn, but you have no dry learning materials, all the above materials can be shared with you free of charge, as long as you give more support
**["Like the article, follow me, and then click here for free download "](https://gitee.com/vip204888/java-p7)**