美文网首页
SQL第7课:创建计算字段

SQL第7课:创建计算字段

作者: 周运来就是我 | 来源:发表于2018-08-29 22:29 被阅读6次
    • 字段(field):基本与column同意。通常表述为计算字段。
    • 拼接(concatenate):将值联结到一起构成单个值。
    • 别名(alias):一个字段或值的替换名。有时也叫 导出列。
    拼接字段
    mysql> select Concat(vend_name,'(',vend_country,')')
        -> from Vendors
        -> order by vend_name;
    +----------------------------------------+
    | Concat(vend_name,'(',vend_country,')') |
    +----------------------------------------+
    | Bear Emporium(USA)                     |
    | Bears R Us(USA)                        |
    | Doll House Inc.(USA)                   |
    | Fun and Games(England)                 |
    | Furball Inc.(USA)                      |
    | Jouets et ours(France)                 |
    +----------------------------------------+
    6 rows in set (0.00 sec)
    

    RTRIM() LTRIM() TRIM() 去掉字符串相应位置的空格。

    使用别名
    mysql> select Concat(vend_name,'(',vend_country,')')
        -> AS vend_title
        -> from Vendors
        -> order by vend_name;
    +------------------------+
    | vend_title             |
    +------------------------+
    | Bear Emporium(USA)     |
    | Bears R Us(USA)        |
    | Doll House Inc.(USA)   |
    | Fun and Games(England) |
    | Furball Inc.(USA)      |
    | Jouets et ours(France) |
    +------------------------+
    6 rows in set (0.00 sec)
    

    我拼接了一个字段,这个字段放在了哪个表中呢?

    执行计算字段
    mysql>  select prod_id,quantity,item_price
        -> from OrderItems
        -> where order_num = 20008;
    +---------+----------+------------+
    | prod_id | quantity | item_price |
    +---------+----------+------------+
    | RGAN01  |        5 |       4.99 |
    | BR03    |        5 |      11.99 |
    | BNBG01  |       10 |       3.49 |
    | BNBG02  |       10 |       3.49 |
    | BNBG03  |       10 |       3.49 |
    +---------+----------+------------+
    5 rows in set (0.00 sec)
    mysql> select prod_id,quantity,item_price,
        -> quantity*item_price AS expanded_price
        -> from OrderItems
        ->  where order_num = 20008;
    +---------+----------+------------+----------------+
    | prod_id | quantity | item_price | expanded_price |
    +---------+----------+------------+----------------+
    | RGAN01  |        5 |       4.99 |          24.95 |
    | BR03    |        5 |      11.99 |          59.95 |
    | BNBG01  |       10 |       3.49 |          34.90 |
    | BNBG02  |       10 |       3.49 |          34.90 |
    | BNBG03  |       10 |       3.49 |          34.90 |
    +---------+----------+------------+----------------+
    

    mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2018-03-10 09:51:19 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select trim('abc');
    +-------------+
    | trim('abc') |
    +-------------+
    | abc         |
    +-------------+
    1 row in set (0.00 sec)
    

    相关文章

      网友评论

          本文标题:SQL第7课:创建计算字段

          本文链接:https://www.haomeiwen.com/subject/qydufftx.html