算术操作符包括:+(加)、-(减)、*(乘)、/(除)、-(取反)、MOD(取模)。这些操作符可以作用在数值列上。

    如“从多个表里查询数据” 页面的示例查询出客户购买的每个商品的数量和价格,其中数量乘以价格就是每类商品的支付总额。所以 select_list可以增加一列 t3.ol_quantity * t4.i_price item_sum_price 。

    1. SELECT t1.c_first, t1.c_last, t1.c_credit, t2.o_ol_cnt, t2.o_entry_d, t3.ol_number, t3.ol_quantity, t4.i_name, t4.i_price, t3.ol_quantity * t4.i_price item_sum_price
    2. FROM cust t1
    3. JOIN ordr t2 ON (t1.c_id=t2.o_id AND t1.c_w_id=t2.o_w_id AND t1.c_d_id=t2.o_d_id)
    4. JOIN ordl t3 ON (t2.o_id=t3.ol_o_id AND t2.o_w_id=t3.ol_w_id AND t2.o_d_id=t3.ol_d_id)
    5. JOIN item t4 ON (t4.i_id=t3.ol_i_id )
    6. WHERE t1.c_w_id=2 AND t1.c_d_id=5 and t1.c_last LIKE 'CALLY%'
    7. ORDER BY t1.c_id, t2.o_id, t3.ol_number;

    查询结果如下:

    1. +------------------+----------------+----------+----------+------------+-----------+-------------+--------------------------+---------+----------------+
    2. | c_first | c_last | c_credit | o_ol_cnt | o_entry_d | ol_number | ol_quantity | i_name | i_price | item_sum_price |
    3. +------------------+----------------+----------+----------+------------+-----------+-------------+--------------------------+---------+----------------+
    4. | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 1 | 5 | FJT8fkxaUh2aUbI | 79.95 | 399.75 |
    5. | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 2 | 5 | kiMk43vd9HidvmwG8x | 58.59 | 292.95 |
    6. | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 3 | 5 | JnJEOLUCjunrKkt4Z1pL | 85.26 | 426.30 |
    7. | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 4 | 5 | CrFVAZW3OhyekdDNc2rPH | 22.30 | 111.50 |
    8. | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 5 | 5 | fJpsyG11EjWIceJWaB | 41.39 | 206.95 |
    9. | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 6 | 5 | shseF8WI1VSPbWfswSsIuNC | 30.04 | 150.20 |
    10. | wPS9EgAgztLRvSuZ | CALLYABLEOUGHT | GC | 7 | 2020-02-15 | 7 | 5 | prjdpUDOxRvAn5WiMVoT85B1 | 18.55 | 92.75 |
    11. +------------------+----------------+----------+----------+------------+-----------+-------------+--------------------------+---------+----------------+
    12. 7 rows in set (0.01 sec)