在mysql库表散列中总结的SQL语句(ZT)

来源：互联网发布：淘宝编辑页面老是代码编辑：程序博客网时间：2024/05/21 06:20

mysql> select goodsId,goodsName from goods;

+----------------+--------------+
| goodsId        | goodsName    |
+----------------+--------------+
| 0B0ADABA002002 | 鍗у 绯诲垪 |
| 0B0ADABA001001 | 瀹㈠巺绯诲垪 |
| 0B0ADABA002003 | 鍗у 绯诲垪 |
| 0B0ADABA001002 | 瀹㈠巺绯诲垪 |
| 0B0ADABA002004 | 椁愬巺绯诲垪 |
| 0B0ADABA001003 | 鍗у 绯诲垪 |
| 0B0ADABA002005 | 鍗у 绯诲垪 |
| 0B0ADABA001004 | 鍗у 绯诲垪 |
| 0B0ADABA003001 | 瀹㈠巺绯诲垪 |
| 0B0ADABA002001 | 涔︽埧绯诲垪 |
+----------------+--------------+
10 rows in set (0.00 sec)

mysql> select goodsId,goodsName from goods where goodsId like '%002%';

+----------------+--------------+
| goodsId        | goodsName    |
+----------------+--------------+
| 0B0ADABA002002 | 鍗у 绯诲垪 |
| 0B0ADABA002003 | 鍗у 绯诲垪 |
| 0B0ADABA001002 | 瀹㈠巺绯诲垪 |
| 0B0ADABA002004 | 椁愬巺绯诲垪 |
| 0B0ADABA002005 | 鍗у 绯诲垪 |
| 0B0ADABA002001 | 涔︽埧绯诲垪 |
+----------------+--------------+
6 rows in set (0.00 sec)

mysql> select goodsId,goodsName from goods where goodsId like '________002%';
+----------------+--------------+
| goodsId        | goodsName    |
+----------------+--------------+
| 0B0ADABA002002 | 鍗у 绯诲垪 |
| 0B0ADABA002003 | 鍗у 绯诲垪 |
| 0B0ADABA002004 | 椁愬巺绯诲垪 |
| 0B0ADABA002005 | 鍗у 绯诲垪 |
| 0B0ADABA002001 | 涔︽埧绯诲垪 |
+----------------+--------------+
5 rows in set (0.00 sec)

mysql> select ASCII(goodsId) from goods;
+----------------+
| ASCII(goodsId) |
+----------------+
|             48 |
|             48 |
|             48 |
|             48 |
|             48 |
|             48 |
|             48 |
|             48 |
|             48 |
|             48 |
+----------------+
10 rows in set (0.01 sec)

mysql> SELECT CONCAT(2,'test');
+------------------+
| CONCAT(2,'test') |
+------------------+
| 2test            |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(38.8 AS CHAR);
+--------------------+
| CAST(38.8 AS CHAR) |
+--------------------+
| 38.8               |
+--------------------+
1 row in set (0.00 sec)

mysql> select SUBSTRING(goodsId,4,2) from goods;
+------------------------+
| SUBSTRING(goodsId,4,2) |
+------------------------+
| AD                     |
| AD                     |
| AD                     |
| AD                     |
| AD                     |
| AD                     |
| AD                     |
| AD                     |
| AD                     |
| AD                     |
+------------------------+
10 rows in set (0.00 sec)

mysql> select ASCII( SUBSTRING(goodsId,4,2)) from goods;
+--------------------------------+
| ASCII( SUBSTRING(goodsId,4,2)) |
+--------------------------------+
|                             65 |
|                             65 |
|                             65 |
|                             65 |
|                             65 |
|                             65 |
|                             65 |
|                             65 |
|                             65 |
|                             65 |
+--------------------------------+
10 rows in set (0.00 sec)

mysql> select ASCII( SUBSTRING(goodsId,4,2)),ASCII( SUBSTRING(goodsId,5,2)) from goods;
+--------------------------------+--------------------------------+
| ASCII( SUBSTRING(goodsId,4,2)) | ASCII( SUBSTRING(goodsId,5,2)) |
+--------------------------------+--------------------------------+
|                             65 |                             68 |
|                             65 |                             68 |
|                             65 |                             68 |
|                             65 |                             68 |
|                             65 |                             68 |
|                             65 |                             68 |
|                             65 |                             68 |
|                             65 |                             68 |
|                             65 |                             68 |
|                             65 |                             68 |
+--------------------------------+--------------------------------+
10 rows in set (0.00 sec)

mysql> select ASCII( SUBSTRING(goodsId,4,2))*100+ASCII( SUBSTRING(goodsId,5,2)) from goods;
+-------------------------------------------------------------------+
| ASCII( SUBSTRING(goodsId,4,2))*100+ASCII( SUBSTRING(goodsId,5,2)) |
+-------------------------------------------------------------------+
|                                                              6568 |
|                                                              6568 |
|                                                              6568 |
|                                                              6568 |
|                                                              6568 |
|                                                              6568 |
|                                                              6568 |
|                                                              6568 |
|                                                              6568 |
|                                                              6568 |
+-------------------------------------------------------------------+
10 rows in set (0.00 sec)