SQL也疯狂：MySQL绘制简单几何图形(2013-04-12)

用SQL SELECT 语句而非存储过程来画基本几何图形，以加深对 SELECT 语句的理解。因为 SELECT 语句隐含了一个或多个循环，看上去只是一个语句，其实包含了一个完整的程序所包含的顺序、分支、循环，因此不必用存储过程就能实现一般的程序的功能 需要有一个表

• 用SQL SELECT 语句而非存储过程来画基本几何图形，以加深对 SELECT 语句的理解。因为 SELECT 语句隐含了一个或多个循环，看上去只是一个语句，其实包含了一个完整的程序所包含的顺序、分支、循环，因此不必用存储过程就能实现一般的程序的功能

• 需要有一个表有 100 行以上的数据，数据是什么没关系，因为下面的语句已经避开使用数据库记录中的数据了。如果没有现成的就使用如下代码生成一个吧： 语句

  DELIMITER ;;
  
  DROP PROCEDURE IF EXISTS test_num;;
  
  CREATE PROCEDURE test_num(MAX_COUNT INTEGER)
  BEGIN
  	DECLARE i INTEGER;
  	SET i = 0;
  
  	DROP TABLE IF EXISTS number;
  	CREATE TABLE number(num INT(10), PRIMARY KEY (num));
  
  	WHILE i < MAX_COUNT DO
  		INSERT INTO number(num) VALUES (i);
  		SET i = i + 1;
  	END WHILE;
  END;;
  
  DELIMITER ;
  
  CALL test_num(100);
  

1. 画横线 语句

   SET @w := 24;  # 宽 <span>SELECT</span> repeat(<span>"*"</span>, @w) AS line;
   

   结果

   +--------------------------+
   | line                     |
   +--------------------------+
   | ************************ |
   +--------------------------+
   

2. 画竖线 语句

   SET @h := 12;  # 高
   SET @y := 0;   # y 座标 <span>SELECT</span> <span>"*"</span> AS line <span>FROM</span> number <span>WHERE</span> @y < @h and @y := @y + 1;
   

   结果

   +------+
   | line |
   +------+
   | *    |
   | *    |
   | *    |
   | *    |
   | *    |
   | *    |
   | *    |
   | *    |
   | *    |
   | *    |
   | *    |
   | *    |
   +------+
   

3. 画斜线 语句

   SET @h := 12;   # 高
   SET @y := 0;    # y 座标
   SET @rate := 2; # x y 座标比例（斜率倒数） <span>SELECT</span> lpad(<span>"*"</span>, @y * @rate, <span>" "</span>) AS line <span>FROM</span> number <span>WHERE</span> @y < @h and @y := @y + 1;
   

   结果

   +--------------------------+
   | line                     |
   +--------------------------+
   |  *                       |
   |    *                     |
   |      *                   |
   |        *                 |
   |          *               |
   |            *             |
   |              *           |
   |                *         |
   |                  *       |
   |                    *     |
   |                      *   |
   |                        * |
   +--------------------------+
   

4. 画XX 语句

   SET @h := 12;           # 高
   SET @w := 24;           # 宽
   SET @y := 0;            # y 座标
   SET @rate := @w / @h;   # x y 座标比例（斜率倒数） <span> SELECT</span> X <span>FROM</span> ( <span> SELECT</span> concat(lpad(<span>"*"</span>, @y * @rate - 1, <span>" "</span>), repeat(<span>" "</span>, @w - 2 * @y * @rate - 2 + 1), IF(@w - 2 * @y * @rate - 2 + 1 <= 0, <span>"",  "</span>*")) AS X <span>FROM</span> number <span>WHERE</span> @y < @h / 2 and @y := @y + 1
   
       UNION ALL <span> SELECT</span> concat(lpad(<span>"*"</span>, @y * @rate - 1, <span>" "</span>), repeat(<span>" "</span>, @w - 2 * @y * @rate - 2 + 1), IF(@w - 2 * @y * @rate - 2 + 1 <= 0, <span>"",  "</span>*")) AS X <span>FROM</span> number <span>WHERE</span> @y < @h and (@y := @y - 1) > 0
   ) AS Temp;
   

5. 结果

   +-----------------------+
   | X                     |
   +-----------------------+
   | *                   * |
   |   *               *   |
   |     *           *     |
   |       *       *       |
   |         *   *         |
   |           *           |
   |         *   *         |
   |       *       *       |
   |     *           *     |
   |   *               *   |
   | *                   * |
   +-----------------------+
   

6. 画矩形 语句

   SET @x := 0;    # x 座标
   SET @rate := 2; # x y 轴长比例，调整显示效果
   SET @h := 12;   # 长
   SET @w := 12;   # 宽 <span> SELECT</span> rect <span>FROM</span> ( <span>SELECT</span> repeat(<span>"*"</span>, @w * @rate) AS rect 
   
       UNION ALL <span> SELECT</span> concat(<span>"*"</span>, repeat(<span>" "</span>, @w * @rate - 2), <span>"*"</span>) <span>FROM</span> number <span>WHERE</span> @x < @h and @x := @x + 1
   
       UNION ALL <span>SELECT</span> repeat(<span>"*"</span>, @w * @rate)
   ) AS Temp;
   

7. 
   
8. 结果

   +--------------------------+
   | rect                     |
   +--------------------------+
   | ************************ |
   | *                      * |
   | *                      * |
   | *                      * |
   | *                      * |
   | *                      * |
   | *                      * |
   | *                      * |
   | *                      * |
   | *                      * |
   | *                      * |
   | *                      * |
   | *                      * |
   | ************************ |
   +--------------------------+
   

9. 把XX用矩形框起来 语句

   SET @h := 12;           # 高
   SET @w := 24;           # 宽
   SET @y := 0;            # y 座标
   SET @rate := @w / @h;   # x y 座标比例（斜率倒数） <span> SELECT</span> rectXX <span>FROM</span> ( <span>SELECT</span> repeat(<span>"*"</span>, (@w - 2 * 2)) AS rectXX
   
       UNION ALL <span>SELECT</span> concat(<span>"*"</span>, lpad(<span>"*"</span>, @y * @rate - 1, <span>" "</span>), repeat(<span>" "</span>, (@w - 2 * 2) - 2 * @y * @rate - 2 + 1), IF((@w - 2 * 2) - 2 * @y * @rate - 2 + 1 <= 0, <span>"",  "</span>*<span>"), repeat("</span> <span>", @y * @rate - 1), "</span>*") <span>FROM</span> number <span>WHERE</span> @y < (@h - 2) / 2 and @y := @y + 1
   
       UNION ALL <span> SELECT</span> concat(<span>"*"</span>, lpad(<span>"*"</span>, @y * @rate - 1, <span>" "</span>), repeat(<span>" "</span>, (@w - 2 * 2) - 2 * @y * @rate - 2 + 1), IF((@w - 2 * 2) - 2 * @y * @rate - 2 + 1 <= 0, <span>"",  "</span>*<span>"), repeat("</span> <span>", @y * @rate - 1), "</span>*") <span>FROM</span> number <span>WHERE</span> @y < (@h - 2) and (@y := @y - 1) > 0
   
       UNION ALL <span>SELECT</span> repeat(<span>"*"</span>, (@w - 2 * 2)) AS rect
   ) AS Temp;
   

10. 结果

    +----------------------+
    | rectXX               |
    +----------------------+
    | ******************** |
    | **               * * |
    | *  *           *   * |
    | *    *       *     * |
    | *      *   *       * |
    | *        *         * |
    | *      *   *       * |
    | *    *       *     * |
    | *  *           *   * |
    | **               * * |
    | ******************** |
    +----------------------+
    

11. 画等腰三角形 语句

    SET @h := 10;       # 高
    SET @w := 10;       # 底
    SET @x := 0;        # x 座标
    SET @y := 0;        # y 座标
    SET @k := @w/@h/2;  # 1/2底高比例，即边的斜率的倒数
    SET @rate := 2;     # x y 轴比例，调整显示效果 <span>SELECT</span> concat(repeat(<span>" "</span>, @rate * (@w / 2 - (@y - 1) * @k) - 1), <span>"*"</span>, repeat(IF(@y = @h, <span>"*"</span>, <span>" "</span>), @rate * (2 * (@y - 1) * @k) - 1), IF(@y = 1, <span>"", "</span>*")) AS triangle <span>FROM</span> number <span> WHERE</span> (@y := @y + 1) AND @y <= @h;
    

    结果

    +---------------------+
    | triangle            |
    +---------------------+
    |          *          |
    |         * *         |
    |        *   *        |
    |       *     *       |
    |      *       *      |
    |     *         *     |
    |    *           *    |
    |   *             *   |
    |  *               *  |
    | ******************* |
    +---------------------+
    

12. 画正弦曲线 语句

    SET @x := 0;        # x 座标
    SET @offset := 15;  # y 偏移
    SET @am := 15;      # 振幅
    SET @rate := 10;    # x y 轴比例（影响波长），调整显示效果
    SET @len := 30;     # 长度 <span> SELECT</span> lpad(<span>"*"</span>, round(@am * sin(@x * 3.14 / @rate) + @offset) + 1, ' ') AS 'sin' <span>FROM</span> number <span>WHERE</span> (@x := @x + 1) < @len;
    

    结果

    +---------------------------------+
    | sin                             |
    +---------------------------------+
    |                     *           |
    |                         *       |
    |                            *    |
    |                              *  |
    |                               * |
    |                              *  |
    |                            *    |
    |                         *       |
    |                     *           |
    |                *                |
    |           *                     |
    |       *                         |
    |    *                            |
    |  *                              |
    | *                               |
    |  *                              |
    |    *                            |
    |       *                         |
    |           *                     |
    |                *                |
    |                     *           |
    |                         *       |
    |                            *    |
    |                              *  |
    |                               * |
    |                              *  |
    |                            *    |
    |                         *       |
    |                     *           |
    +---------------------------------+
    

13. 画圆 语句

    SET @r := 12;           # 半径
    SET @d := 2 * (@r + 1); # 图形范围，即直径（避免减到0，偏移 1）
    SET @x := 0;            # x 座标
    SET @y := @r + 1;       # y 座标
    SET @rate := 2;         # x y 轴长比例，调整显示效果 <span> SELECT</span> circle <span>FROM</span> ( <span>SELECT</span> @x := round(@rate * sqrt(pow(@r, 2) - pow(@y, 2))) + 1, concat(lpad(<span>"*"</span>, @d - @x, <span>" "</span>), lpad(<span>"*"</span>, 2 * @x, <span>" "</span>)) AS circle <span>FROM</span> number <span>WHERE</span> (@y := @y - 1) > 0 AND @y <= @r
        UNION ALL <span>SELECT</span> @x := round(@rate * sqrt(pow(@r, 2) - pow(@y, 2))) + 1, concat(lpad(<span>"*"</span>, @d - @x, <span>" "</span>), lpad(<span>"*"</span>, 2 * @x, <span>" "</span>)) AS circle <span>FROM</span> number <span>WHERE</span> (@y := @y + 1) > 0 AND @y <= @r
    ) AS Temp;
    

    结果

    +-----------------------------------------------------+
    | circle                                              |
    +-----------------------------------------------------+
    |                         * *                         |
    |               *                     *               |
    |            *                           *            |
    |         *                                 *         |
    |       *                                     *       |
    |      *                                       *      |
    |    *                                           *    |
    |   *                                             *   |
    |  *                                               *  |
    |  *                                               *  |
    | *                                                 * |
    | *                                                 * |
    | *                                                 * |
    |  *                                               *  |
    |  *                                               *  |
    |   *                                             *   |
    |    *                                           *    |
    |      *                                       *      |
    |       *                                     *       |
    |         *                                 *         |
    |            *                           *            |
    |               *                     *               |
    |                         * *                         |
    +-----------------------------------------------------+
    

• UNION 后再用一个 SELECT 仅是包装一下，看起来像一个整体，不那么松散
• 回头想想，上面用了 UNION 的都可以用 IF 来整合在一个语句里，不过会显得很臃肿

• 亚丹
  seesea2517#gmail#com
  http://seesea.blog.chinaunix.net
  http://blog.csdn.net/nicenight
  http://my.oschina.net/seesea2517

<无>

x