mysql 数据操作 单表查询 concat()函数 定义显示格式
#定义显示格式
concat() 函数用于连接字符串
类似于python 格式化操作
print("姓名:%s" % name)
或者 用,拼接一个一个的变量
print("alex","mike","jack")
>>> print("alex","mike","jack") alex mike jack
mysql> select concat('姓名:',name) from employee; +------------------------+ | concat('姓名:',name) | +------------------------+ | 姓名:alex | | 姓名:yuanhao | | 姓名:liwenzhou | | 姓名:jingliyang | | 姓名:jinxin | | 姓名:成龙 | | 姓名:歪歪 | | 姓名:丫丫 | | 姓名:丁丁 | | 姓名:星星 | | 姓名:格格 | | 姓名:张野 | | 姓名:程咬金 | | 姓名:程咬银 | | 姓名:程咬铜 | | 姓名:程咬铁 | +------------------------+ 16 rows in set (0.00 sec)
加上性别 别名
mysql> select concat('姓名:',name,'性别:',sex) as info from employee; +--------------------------------+ | info | +--------------------------------+ | 姓名:alex性别:male | | 姓名:yuanhao性别:male | | 姓名:liwenzhou性别:male | | 姓名:jingliyang性别:female | | 姓名:jinxin性别:male | | 姓名:成龙性别:male | | 姓名:歪歪性别:female | | 姓名:丫丫性别:female | | 姓名:丁丁性别:female | | 姓名:星星性别:female | | 姓名:格格性别:female | | 姓名:张野性别:male | | 姓名:程咬金性别:male | | 姓名:程咬银性别:female | | 姓名:程咬铜性别:male | | 姓名:程咬铁性别:female | +--------------------------------+ 16 rows in set (0.00 sec)
在别名后面 加上薪资
mysql> select concat('姓名:',name,'性别:',sex) as info,salary from employee; +--------------------------------+------------+ | info | salary | +--------------------------------+------------+ | 姓名:alex性别:male | 1000000.31 | | 姓名:yuanhao性别:male | 3500.00 | | 姓名:liwenzhou性别:male | 2100.00 | | 姓名:jingliyang性别:female | 9000.00 | | 姓名:jinxin性别:male | 30000.00 | | 姓名:成龙性别:male | 10000.00 | | 姓名:歪歪性别:female | 3000.13 | | 姓名:丫丫性别:female | 2000.35 | | 姓名:丁丁性别:female | 1000.37 | | 姓名:星星性别:female | 3000.29 | | 姓名:格格性别:female | 4000.33 | | 姓名:张野性别:male | 10000.13 | | 姓名:程咬金性别:male | 20000.00 | | 姓名:程咬银性别:female | 19000.00 | | 姓名:程咬铜性别:male | 18000.00 | | 姓名:程咬铁性别:female | 17000.00 | +--------------------------------+------------+ 16 rows in set (0.00 sec)
用concat() 拼接
查询年薪
mysql> select concat('姓名:',name,'性别:',sex) as info ,concat('年薪:',salary*12) as Annual_salary from employee; +--------------------------------+--------------------+ | info | Annual_salary | +--------------------------------+--------------------+ | 姓名:alex性别:male | 年薪:12000003.72 | | 姓名:yuanhao性别:male | 年薪:42000.00 | | 姓名:liwenzhou性别:male | 年薪:25200.00 | | 姓名:jingliyang性别:female | 年薪:108000.00 | | 姓名:jinxin性别:male | 年薪:360000.00 | | 姓名:成龙性别:male | 年薪:120000.00 | | 姓名:歪歪性别:female | 年薪:36001.56 | | 姓名:丫丫性别:female | 年薪:24004.20 | | 姓名:丁丁性别:female | 年薪:12004.44 | | 姓名:星星性别:female | 年薪:36003.48 | | 姓名:格格性别:female | 年薪:48003.96 | | 姓名:张野性别:male | 年薪:120001.56 | | 姓名:程咬金性别:male | 年薪:240000.00 | | 姓名:程咬银性别:female | 年薪:228000.00 | | 姓名:程咬铜性别:male | 年薪:216000.00 | | 姓名:程咬铁性别:female | 年薪:204000.00 | +--------------------------------+--------------------+ 16 rows in set (0.00 sec)