“This is the 18th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021”

1, the introduction of

MySQL is a relational database. When we use MySQL, we often map the attributes of objects into columns and store them in tables. Therefore, the query results are also individual attributes without any processing. If we want to query the returned result set in MySQL, and be able to combine the values of multiple fields (columns), or return them after a specific calculation, we can use the field calculation function provided by MySQL.

The following two types are often used in field calculation:

  • Field splices
  • Fields perform arithmetic calculations

2, the body

All of the field combinations implemented in MySQL can be done in the client, but it is faster to implement field combinations directly in the MySQL server than in the client.

2.1 Field Stitching

Prepare a user table and insert some data as follows:

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`;  CREATE TABLE 'user' (' id 'bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', 'name' varchar(255) CHARACTER SET utf8 COLLATE UTf8_general_ci NOT NULL COMMENT 'username ', 'nation' varchar(255) CHARACTER SET utf8 COLLATE UTf8_general_ci NULL DEFAULT NULL COMMENT 'nation ', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - the Records of the user -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- INSERT INTO ` user ` VALUES (1, 'plum', "Han"); INSERT INTO 'user' VALUES (2, 'zhang ',' Hui '); INSERT INTO 'user' VALUES (3, '03 ',' 03 '); INSERT INTO 'user' VALUES (4, '王 三 ', '王 三 '); SET FOREIGN_KEY_CHECKS = 1;Copy the code

Requirements:

Get the user’s name and ethnic group information

Statement:

Mysql > select concat(name, '(',nation, ') ') from user; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | concat (name, '(' the nation, ') ') | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | plum (han) | | zhang SAN (hui) | | li si (uygur) | | fifty (Mongolian) | +---------------------------------+Copy the code

Resolution:

The concat() function is used here, which can combine any number of elements. These elements can be table fields, fixed characters, etc. The elements are used, separated, and combined in the same order as the characters in concat() function.

What about the field name after the combination?

Concat () : concat(); concat() : concat(); If you want to specify the name of the field you want, just use an alias!

Mysql > select concat(name, '(',nation, ') as user_message from user; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | user_message | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | plum (han) | | zhang SAN (hui) | | li si (uygur) | | fifty (Mongolian) | +------------------+Copy the code

The alias uses as, followed by the name of the field you want to specify.

2.2 Fields perform arithmetic calculations

Combining fields is often more than simple string concatenation. It may involve arithmetic budgets between fields. In this case, we need to use arithmetic operators in MySQL. MySQL provides the addition, subtraction, multiplication and division operators as follows:

The operator instructions
+ add
– Reduction of
* take
/ In addition to

Prepare a product table and insert some data as follows:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称',
  `price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '产品价格',
  `number` int(11) NOT NULL COMMENT '产品数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, 'Apple iPhone 13 (A2634)', 6799.00, 22);
INSERT INTO `product` VALUES (2, 'HUAWEI P50 Pro', 6488.00, 88);
INSERT INTO `product` VALUES (3, 'MIX4', 4999.00, 30);
INSERT INTO `product` VALUES (4, 'OPPO Find X3', 3999.00, 15);
INSERT INTO `product` VALUES (5, 'vivo X70 Pro+', 5999.00, 27);

SET FOREIGN_KEY_CHECKS = 1;
Copy the code

Requirements:

Query the total value of products in stock

Statement:

mysql> select product_name, concat(price * number) as gross_value from product; +-------------------------+-------------+ | product_name | gross_value | +-------------------------+-------------+ | Apple iPhone 13 (A2634) 149578.00 | | | HUAWEI P50 Pro 570944.00 | | | MIX4 149970.00 | | | OPPO Find X3 59985.00 | | | Vivo X70 Pro + 161973.00 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

Operator order problem:

(* /) > (+ -). If you need to pay attention to the order of the combined operators, use () properly to restrict the execution order of the operators.

Example:

mysql> select concat(12 - 3 * 4); + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | concat (12-3 * 4) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 0 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00) sec) mysql> select concat((12 - 3) * 4); +----------------------+ | concat((12 - 3) * 4) | +----------------------+ | 36 | +----------------------+ 1 row in set (0.00 SEC)Copy the code

Note that MySQL does not raise an exception if the dividend is 0, but does return NULL. This is an exception handled internally by MySQL.

mysql> select concat(12 / 0); + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | concat (12/0) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | NULL | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set, 1 warning (0.00 SEC)Copy the code