How does mysql query the last record
Editor to share with you how mysql query the last record, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!
Mysql query the last record of the method: 1, view the current database of the table and table structure; 2, according to the self-growth of ID, sort the ID field in reverse order and view the first row; 3, according to the current insert statement and use the function last_insert_id () to view the last record.
MySQL queries the last record
I. Environment and data preparation
1. View tables in the current database
Mysql > show tables;+-+ | Tables_in_test | +-+ | my_insert1 | | my_insert2 | +-+
two。 View the my_ insert1 table structure
Mysql > show create table my_insert1\ Gbomber * 1. Row * * Table: my_insert1Create Table: CREATE TABLE `my_ insert1` (`name` varchar (10) CHARACTER SET latin1 DEFAULT NULL, `password` varchar (32) CHARACTER SET latin1 DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
3. View the my_ insert2 table structure
Mysql > show create table my_insert2\ G * * 1. Row * * Table: my_insert2Create Table: CREATE TABLE `my_ insert2` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (10) CHARACTER SET latin1 DEFAULT NULL, `password` varchar (32) CHARACTER SET latin1 DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
4. Insert records into tables my_insert1 and my_ insert2 tables
Mysql > insert into my_insert1 (name,password) values ('Huang Feihong', password (123456)), ('Bruce Lee', password (123456)); mysql > insert into my_insert2 (id,name,password) values (null,' Huang Feihong', password (123456)), (null,' Bruce Lee', password (123456))
5. View the records of the table
Mysql > select * from my_insert1 +-+-+ | name | password | +-+-+ | Huang Feihong | * 6BB4837EB74329105EE4568DDA7DC67 | | Bruce Lee | * 6BB4837EB74329105EE4568DDA7DC67 | +-+-- + mysql > select * from my_insert2 +-+ | id | name | password | +-+ -+ | 1 | Huang Feihong | * 6BB4837EB74329105EE4568DDA7DC67 | | 2 | Bruce Lee | * 6BB4837EB74329105EE4568DDA7DC67 | | 3 | Jet Li | * 6BB4837EB74329105EE4568DDA7DC67 | +-+-- +
Second, when there is no ID self-growth field in the table and there is a way for ID self-growth to view the last record
1. Because of my_insert1, there is no ID self-growth. Check how many records there are in the current table.
Mysql > select count (*) from my_insert1;+-+ | count (*) | +-+ | 2 | +-+ 1 row in set (0.00 sec)
two。 View the second row record of the current table
Mysql > select * from my_insert1 limit 1 +-+-- + | name | password | +-+-+ | Bruce Lee | * 6BB4837EB74329105EE4568DDA7DC67 | +-+-- -- + 1 row in set (0.00 sec)
3. According to the self-growth of ID, use a subquery to view the maximum value of the ID field of the table
Mysql > select * from my_insert2 where id= (select max (id) from my_insert2) +-+ | id | name | password | +-+ -+ | 3 | Jet Li | * 6BB4837EB74329105EE4568DDA7DC67 | +-+-- +
4. According to the self-growth of ID, sort the ID field in reverse order and view the first line
Mysql > select * from my_insert2 order by id desc limit 1 +-+ | id | name | password | +-+ -+ | 3 | Jet Li | * 6BB4837EB74329105EE4568DDA7DC67 | +-+-- +
5. You can view the last record by using the function last_insert_id () based on the current insert statement
Mysql > insert into my_insert2 (id,name,password) values (null,' Huo Yuanjia', password ('123456')); Query OK, 1 row affected, 1 warning (0.00 sec) mysql > select * from my_insert2 where id= (select last_insert_id ()) +-+ | id | name | password | +-+ -+ | 4 | Huo Yuanjia | * 6BB4837EB74329105EE4568DDA7DC67 | +-+-- + 1 row in set (0.00 sec) are all the contents of the article "how to query the Last record by mysql" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
