Shulou(Shulou.com)06/02 Report--

What this article shares to you is about how to understand the oncat () of the combined fields in MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. Introduction

MySQL is a relational database, when we use it, we tend to map the attributes of the object into columns and store them in the table, so the results of the query are 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) to return, or return after a specific calculation, we can use the field calculation feature provided by MySQL.

Two kinds of field calculations are often used:

Field splicing

Fields perform arithmetic calculations

2. Text

All the field combinations implemented in MySQL can be done in the client, but implementing field combinations directly in the MySQL server is faster than the client.

2.1 Field splicing

Prepare a user table and insert several pieces of data, as follows:

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0Mutual-Table structure for user-- DROP TABLE IF EXISTS `user` CREATE TABLE `user` (`id` bigint (20) NOT NULL AUTO_INCREMENT COMMENT 'primary key', `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 'nationality', PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic -Records of user-- INSERT INTO `user`VALUES (1, 'plum', 'Han'); INSERT INTO `user`VALUES (2, 'Zhang San', 'Hui'); INSERT INTO `user`VALUES (3,'Li Si', 'Uygur') INSERT INTO `user`VALUES (4, 'Wang Wu', 'Mongolian'); SET FOREIGN_KEY_CHECKS = 1

Demand:

Get the user's name and ethnic combination information

Statement:

Mysql > select concat (name,'(', nation,')) from user +-+ | concat (name,'(', nation) ')') | +-+ | Li Ziqi (Han nationality) | | Zhang San (Hui) | | Li Si (Uygur) | | Wang Wu (Mongolian) | +- -+

Parsing:

Here the use of the concat () function, the function can combine any number of elements, these elements can be table fields, fixed characters, etc., between the elements used, separated, combined in the same order as the characters in the concat () function.

About the field name after the combination?

The careful partner found that the combined field names use the entire function body of the concat () function, obviously this kind of display is not what we want! If you want to specify the field name you want, just use an alias!

Mysql > select concat (name,'(', nation,') as user_message from user;+-+ | user_message | +-+ | Li Ziqi (Han) | | Zhang San (Hui) | | Li Si (Uygur) | | Wang Wu (Mongolian) | +-+

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

2.2 Fields perform arithmetic calculations

Combining fields we often do not just simply concatenate strings, but may involve the arithmetic budget between fields, so we need to use the arithmetic operators in MySQL.

MySQL provides the addition, subtraction, multiplication and division operators as follows:

Operator description + add-minus * multiply /

Prepare a product table and insert several pieces of data, as follows:

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0Mutual-Table structure for product-- DROP TABLE IF EXISTS `product` CREATE TABLE `product` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'key', `product_ name` varchar 'product name', `price` decimal (10,2) UNSIGNED NOT NULL COMMENT 'product price', `number`int (11) NOT NULL COMMENT 'product quantity', 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

Demand:

Inquire about the total value of products currently 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 | +-+ |

Operator order problem:

Operators in MySQL also have an order, which is the same as that of ordinary operators, (* /) > (+ -). If you must pay attention to the order of operators when using combined operators, reasonable use of () can constrain the execution order of operators.

Example:

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

It is worth noting that in MySQL, when the divisor is 0, no exception is thrown, but NULL is returned, which is the exception handling of the operation within MySQL.

Mysql > select concat (12 / 0) +-+ | concat (12 / 0) | +-+ | NULL | +-+ 1 row in set. More than 1 warning (0.00 sec) is how to understand the oncat () of combined fields in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.