目录
需求说明
- 新加一个字段
age - 按照
id逆序（由大到小）排序递增设置age字段值
即：
id最大的那行的age字段值设置为1；id最小的那行的age字段值设置为最大值
最终实现效果
select * from tb_student; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 1 | Tom | 5 | | 2 | Jack | 4 | | 3 | Steve | 3 | | 4 | Yellow | 2 | | 5 | Green | 1 | +----+--------+-----+
环境准备
select version(); +-----------+ | version() | +-----------+ | 8.0.29 | +-----------+
初始化数据表
CREATE TABLE `tb_student` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ); INSERT INTO `tb_student`(`id`, `name`) VALUES (1, 'Tom'); INSERT INTO `tb_student`(`id`, `name`) VALUES (2, 'Jack'); INSERT INTO `tb_student`(`id`, `name`) VALUES (3, 'Steve'); INSERT INTO `tb_student`(`id`, `name`) VALUES (4, 'Yellow'); INSERT INTO `tb_student`(`id`, `name`) VALUES (5, 'Green');
查看数据
select * from tb_student; +----+--------+ | id | name | +----+--------+ | 1 | Tom | | 2 | Jack | | 3 | Steve | | 4 | Yellow | | 5 | Green | +----+--------+
需求实现
增加列
ALTER TABLE `tb_student` ADD COLUMN `age` int(0) NOT NULL DEFAULT 0 AFTER `name`; -- 此时的数据 select * from tb_student; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 1 | Tom | 0 | | 2 | Jack | 0 | | 3 | Steve | 0 | | 4 | Yellow | 0 | | 5 | Green | 0 | +----+--------+-----+
关键SQL语句
SET @rownum = 1; SELECT @rownum := @rownum + 1 AS num; +------+ | num | +------+ | 2 | +------+
递减更新age字段数据
SET @rownum = 0; UPDATE tb_student SET age = ( SELECT @rownum := @rownum + 1 AS num ) ORDER BY id desc;
更新结果
select * from tb_student; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 1 | Tom | 5 | | 2 | Jack | 4 | | 3 | Steve | 3 | | 4 | Yellow | 2 | | 5 | Green | 1 | +----+--------+-----+
