本文共 6069 字,大约阅读时间需要 20 分钟。
存储在数据库表中的数据一般不是应用程序所需要的格式。们所需要的的可能是几个连起来的列。
比如:
我们需要直接从数据库中检索出转换、计算或格式化过
的数据
字段(field) 基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。
拼接(concatenate) 将值联结到一起构成单个值
MariaDB [test]> select concat(user,' (',age,')') from linux;+---------------------------+| concat(user,' (',age,')') |+---------------------------+| user1 (18) || user2 (23) || user3 (12) || user4 (22) || user5 (30) || a (22) || 1 (30) || user1 (18) || user11 (31) || user111 (41) || user1.1 (42) || (cay) (21) || (cays) (21) |+---------------------------+13 rows in set (0.04 sec)
SELECT语句连接以下4个元素:
他们之间用 ,隔开
(
和)
是我们用单引号引用进去的。 MariaDB [test]> insert into linux value('tt ','tttt','girl','24');Query OK, 1 row affected (0.05 sec) /插入带有空格的数据MariaDB [test]> select concat(user,' (',age,')') from linux order by user; +---------------------------+| concat(user,' (',age,')') |+---------------------------+| (cay) (21) || (cays) (21) || 1 (30) || a (22) || tt (24) | /当数据后面有多个空格时| user1 (18) || user1 (18) || user1.1 (42) || user11 (31) || user111 (41) || user2 (23) || user3 (12) || user4 (22) || user5 (30) |+---------------------------+14 rows in set (0.00 sec)MariaDB [test]> select concat(rtrim(user),' (',age,')') from linux order by user; +----------------------------------+| concat(rtrim(user),' (',age,')') |+----------------------------------+| (cay) (21) || (cays) (21) || 1 (30) || a (22) || tt (24) || user1 (18) || user1 (18) || user1.1 (42) || user11 (31) || user111 (41) || user2 (23) || user3 (12) || user4 (22) || user5 (30) |+----------------------------------+14 rows in set (0.00 sec)我们可以使用 RTrim() 函数去掉值右边的所有空格。对整个列生效
还支持LTrim()
(去掉串左边的空格)以及Trim()
(去掉串左右两边的空格)。
别名(alias)是一个字段或值的替换名。别名用 AS 关键字赋予。
MariaDB [test]> select concat(user,'(',age,')') from linux order by user DESC limit 5;+--------------------------+| concat(user,'(',age,')') | /注意这个名字,他是代表一个值+--------------------------+| user5(30) || user4(22) || user3(12) || user2(23) || user111(41) |+--------------------------+5 rows in set (0.02 sec)MariaDB [test]> select concat(user,'(',age,')') as a from linux order by user DESC limit 5;+-------------+| a | 、使用别名后就可以用 a 来代替这个合起来的字段+-------------+ | user5(30) || user4(22) || user3(12) || user2(23) || user111(41) |+-------------+5 rows in set (0.00 sec)MariaDB [test]> select user as U from linux limit 3;+-------+| U | /也可以用来替换一个字段+-------+| user1 || user2 || user3 |+-------+3 rows in set (0.00 sec)
别名的其他用途 别名还有其他用途。常见的用途包括:
算数计算配合 as 使用。
MariaDB [test]> select * from linux;+--------+----------+------+------+-----+-------+| user | passwd | sex | age | num | price |+--------+----------+------+------+-----+-------+| user2 | 222 | girl | 23 | 3 | 155 || user3 | 333 | boy | 12 | 1 | 78 || user4 | 444 | boy | 22 | | NULL || user5 | 555 | boy | 30 | | NULL || user1 | 111 | boy | 18 | 2 | 35 |+--------+----------+------+------+-----+-------+9 rows in set (0.00 sec)MariaDB [test]> select user, -> passwd, -> num*price as total_price /计算 -> from linux -> order by user;+--------+----------+-------------+| user | passwd | total_price |+--------+----------+-------------+| user1 | 111 | 70 || user2 | 222 | 465 || user3 | 333 | 78 || user4 | 444 | NULL || user5 | 555 | NULL |+--------+----------+-------------+9 rows in set (0.01 sec)
如果是比较复杂的数据的话,可以使用圆括号
来区分优先顺序
MariaDB [test]> select * from linux;+--------+----------+------+------+-----+-------+------------+| user | passwd | sex | age | num | price | math_price |+--------+----------+------+------+-----+-------+------------+| user2 | 222 | girl | 23 | 3 | 155 | 36 || user3 | 333 | boy | 12 | 1 | 78 | 66 || user4 | 444 | boy | 22 | | NULL | NULL || user5 | 555 | boy | 30 | | NULL | NULL || user1 | 111 | boy | 18 | 2 | 35 | 88 |+--------+----------+------+------+-----+-------+------------+9 rows in set (0.00 sec)MariaDB [test]> select user, -> passwd, -> num*(price+math_price) -> from linux -> order by user -> limit 3;+--------+----------+------------------------+| user | passwd | num*(price+math_price) |+--------+----------+------------------------+| user1 | 111 | 246 | 2*(35+88)=246| user2 | 222 | 573 | 3*(155+36)=573| user3 | 333 | 144 | 1*(78+66)=144+--------+----------+------------------------+
MariaDB [test]> select 3*2;+-----+| 3*2 |+-----+| 6 |+-----+1 row in set (0.00 sec)MariaDB [test]> select now();+---------------------+| now() |+---------------------+| 2020-08-04 18:59:34 |+---------------------+1 row in set (0.00 sec)MariaDB [test]> select Trim(' abc ');+----------------+| Trim(' abc ') |+----------------+| abc |+----------------+1 row in set (0.00 sec)
转载地址:http://guxli.baihongyu.com/