在mysql库表散列中总结的SQL语句(ZT)
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)
页:
[1]