如何将MySQL表中的所有记录从大写转换为小写？
使用UPDATE命令和LOWER()方法将MySQL表中的所有记录从大写转换为小写。
让我们首先创建一个表-
mysql> create table DemoTable ( Id varchar(100), StudentFirstName varchar(20), StudentLastName varchar(20), StudentCountryName varchar(10) );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('STU-101','John','Smith','US');
mysql> insert into DemoTable values('STU-102','John','Doe','UK');
mysql> insert into DemoTable values('STU-103','David','Miller','AUS');以下是使用select语句显示表中所有记录的查询-
mysql> select *from DemoTable;
这将产生以下输出-
+---------+------------------+-----------------+--------------------+ | Id | StudentFirstName | StudentLastName | StudentCountryName | +---------+------------------+-----------------+--------------------+ | STU-101 | John | Smith | US | | STU-102 | John | Doe | UK | | STU-103 | David | Miller | AUS | +---------+------------------+-----------------+--------------------+ 3 rows in set (0.00 sec)
这是通过一次调用更改MySQL表中每个字段大小写的查询。
mysql> update DemoTable set Id=lower(Id), StudentFirstName=lower(StudentFirstName), StudentLastName=lower(StudentLastName), StudentCountryName=lower(StudentCountryName); Rows matched: 3 Changed: 3 Warnings: 0
使用select语句显示表中的所有记录，以检查上述查询中所做的更改-
mysql> select *from DemoTable;
这将产生以下输出-
+---------+------------------+-----------------+--------------------+ | Id | StudentFirstName | StudentLastName | StudentCountryName | +---------+------------------+-----------------+--------------------+ | stu-101 | john | smith | us | | stu-102 | john | doe | uk | | stu-103 | david | miller | aus | +---------+------------------+-----------------+--------------------+ 3 rows in set (0.00 sec)