在 MySQL 选择语句中将值从 1 更改为 Y?
mysqlmysqli database
您可以使用 MySQL 中的 IF() 将值从 1 更改为 Y。语法如下:
SELECT IF(yourColumnName,’Y’,yourColumnName) as anyVariableName FROM yourTableName;
为了理解上述语法，让我们创建一个表。创建表的查询如下:
mysql> create table changeValuefrom1toY -> ( -> Id int NOT NULL AUTO_INCREMENT, -> isValidAddress tinyint(1), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.76 sec)
现在您可以使用 insert 命令在表中插入一些记录。查询如下:
mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.22 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(0); Query OK, 1 row affected (0.16 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.19 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.15 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.16 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(0); Query OK, 1 row affected (0.12 sec) mysql> insert into changeValuefrom1toY(isValidAddress) values(1); Query OK, 1 row affected (0.49 sec)
使用 select 语句显示表中的所有记录。查询如下:
mysql> select *from changeValuefrom1toY;
输出结果如下:
+----+----------------+ | Id | isValidAddress | +----+----------------+ | 1 | 1 | | 2 | 0 | | 3 | 1 | | 4 | 1 | | 5 | 1 | | 6 | 0 | | 7 | 1 | +----+----------------+ 7 rows in set (0.00 sec)
这是将值从 1 更改为 Y 的查询。查询如下:
mysql> select if(isValidAddress,'Y',isValidAddress) as Answer from changeValuefrom1toY;
输出结果如下:
+--------+ | Answer | +--------+ | Y | | 0 | | Y | | Y | | Y | | 0 | | Y | +--------+ 7 rows in set (0.00 sec)
