MySql012——检索数据：创建计算字段（拼接字段、使用别名、执行算术计算）

红太狼 2023-10-14 18:52 2阅读 0赞

## 准备工作1：在study库中创建表vendors，并插入数据 ##

> 说明：vendors表包含供应商名和位置信息。

use `study`;
    
    CREATE TABLE vendors
    (
      vend_id      int      NOT NULL AUTO_INCREMENT,
      vend_name    char(50) NOT NULL ,
      vend_address char(50) NULL ,
      vend_city    char(50) NULL ,
      vend_state   char(5)  NULL ,
      vend_zip     char(10) NULL ,
      vend_country char(50) NULL ,
      PRIMARY KEY (vend_id)
    ) ENGINE=InnoDB;
    
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
    INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
    VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

![在这里插入图片描述][552f4d04ad994014a61ca25ff0a6a51d.png]

## 一、拼接字段 ##

为了说明如何使用计算字段，举一个创建由两列组成的标题的简单例子：

vendors表包含`供应商名`和`位置`信息。假如需要在供应商的名字中按照name(location)这样的格式列出供应商的位置。而表中数据存储在两个列vend\_name和vend\_ country中。此外，需要用括号将vend\_country括起来，这些东西都没有明确存储在数据库表中。我们来看看怎样编写返回供应商名和位置的SELECT语句。

解决办法是把两个列拼接起来。在MySQL的SELECT语句中，可使用`Concat()函数`来拼接两个列。

SELECT 
        CONCAT(vend_name, '(', vend_country, ')')
    FROM
        study.vendors
    ORDER BY vend_name;

![在这里插入图片描述][acf7ba2794074b0dbd0c3a87c2f7f1e1.png]

## 二、使用别名 ##

从上面的结果可以看出，结果是查出来了，但是列名却不是我们想要的`简短见名知意`效果；

为了解决这个问题，SQL支持列别名。`别名（alias）`是一个字段或值的替换名。别名用`AS关键字`赋予。例如上面语句可以修改成：

SELECT 
        CONCAT(vend_name, '(', vend_country, ')') AS `vend_message`
    FROM
        study.vendors
    ORDER BY vend_name;

其中，AS后面的名称是我们自定义的，你也可以根据需要换成其他的别名，SQL执行结果如下：  
![在这里插入图片描述][ee65048e21824590a6ec9f7350093240.png]

## 准备工作2：在study库中创建表orderitems，并插入数据 ##

> 说明：orders表包含收到的所有订单

use `study`;
    
    CREATE TABLE orderitems
    (
      order_num  int          NOT NULL ,
      order_item int          NOT NULL ,
      prod_id    char(10)     NOT NULL ,
      quantity   int          NOT NULL ,
      item_price decimal(8,2) NOT NULL ,
      PRIMARY KEY (order_num, order_item)
    ) ENGINE=InnoDB;
    
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 1, 'ANV01', 10, 5.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 2, 'ANV02', 3, 9.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 3, 'TNT2', 5, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20005, 4, 'FB', 1, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20006, 1, 'JP2000', 1, 55);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20007, 1, 'TNT2', 100, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20008, 1, 'FC', 50, 2.50);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 1, 'FB', 1, 10);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 2, 'OL1', 1, 8.99);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 3, 'SLING', 1, 4.49);
    INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
    VALUES(20009, 4, 'ANV03', 1, 14.99);

![在这里插入图片描述][16148aa8f38a43e38cec848e8ef54df0.png]

## 三、执行算术计算 ##

首先，我们查询订单号20005中的所有物品，结果如下：

SELECT 
       order_num, prod_id, quantity, item_price
    FROM
        orderitems
    WHERE
        order_num = 20005;

![在这里插入图片描述][d61ca3015cb94775aa990763f1ee1988.png]  
这样我们就查询到订单号20005中的所有物品信息，其中包括订单号、产品ID、数量、单价

### 3.1、MySQL算术操作符 ###

操作符			           说明
    --------------------------------------
         + 					    加
         - 						减
         * 						乘
         / 					    除

但是，我还想知道每一种产品总共花了多少钱，怎么做呢？这样我们就需要使用数量乘以单价。

SELECT 
        order_num,
        prod_id,
        quantity,
        item_price,
        item_price * item_price AS expanded_price -- 做运算同时取别名
    FROM
        orderitems
    WHERE
        order_num = 20005;

![在这里插入图片描述][53fc29cbe21147e7925a3a238a6096bb.png]

[552f4d04ad994014a61ca25ff0a6a51d.png]: https://img-blog.csdnimg.cn/552f4d04ad994014a61ca25ff0a6a51d.png
[acf7ba2794074b0dbd0c3a87c2f7f1e1.png]: https://img-blog.csdnimg.cn/acf7ba2794074b0dbd0c3a87c2f7f1e1.png
[ee65048e21824590a6ec9f7350093240.png]: https://img-blog.csdnimg.cn/ee65048e21824590a6ec9f7350093240.png
[16148aa8f38a43e38cec848e8ef54df0.png]: https://img-blog.csdnimg.cn/16148aa8f38a43e38cec848e8ef54df0.png
[d61ca3015cb94775aa990763f1ee1988.png]: https://img-blog.csdnimg.cn/d61ca3015cb94775aa990763f1ee1988.png
[53fc29cbe21147e7925a3a238a6096bb.png]: https://img-blog.csdnimg.cn/53fc29cbe21147e7925a3a238a6096bb.png