当前位置：Gxlcms > mysql > SQL也疯狂：MySQL绘制简单几何图形(2013-04-12)

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

时间：2021-07-01 10:21:17 帮助过：65人阅读

用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);

画横线语句

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

结果

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

画竖线语句

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

结果

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

画斜线语句

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

结果

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

画XX 语句

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

    UNION ALL  SELECT concat(lpad("*", @y * @rate - 1, " "), repeat(" ", @w - 2 * @y * @rate - 2 + 1), IF(@w - 2 * @y * @rate - 2 + 1 <= 0, "",  "*")) AS X FROM number WHERE @y < @h and (@y := @y - 1) > 0
) AS Temp;

结果

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

画矩形语句

SET @x := 0;    # x 座标
SET @rate := 2; # x y 轴长比例，调整显示效果
SET @h := 12;   # 长
SET @w := 12;   # 宽  SELECT rect FROM ( SELECT repeat("*", @w * @rate) AS rect 

    UNION ALL  SELECT concat("*", repeat(" ", @w * @rate - 2), "*") FROM number WHERE @x < @h and @x := @x + 1

    UNION ALL SELECT repeat("*", @w * @rate)
) AS Temp;

结果

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

把XX用矩形框起来语句

SET @h := 12;           # 高
SET @w := 24;           # 宽
SET @y := 0;            # y 座标
SET @rate := @w / @h;   # x y 座标比例（斜率倒数）  SELECT rectXX FROM ( SELECT repeat("*", (@w - 2 * 2)) AS rectXX

    UNION ALL SELECT concat("*", lpad("*", @y * @rate - 1, " "), repeat(" ", (@w - 2 * 2) - 2 * @y * @rate - 2 + 1), IF((@w - 2 * 2) - 2 * @y * @rate - 2 + 1 <= 0, "",  "*"), repeat(" ", @y * @rate - 1), "*") FROM number WHERE @y < (@h - 2) / 2 and @y := @y + 1

    UNION ALL  SELECT concat("*", lpad("*", @y * @rate - 1, " "), repeat(" ", (@w - 2 * 2) - 2 * @y * @rate - 2 + 1), IF((@w - 2 * 2) - 2 * @y * @rate - 2 + 1 <= 0, "",  "*"), repeat(" ", @y * @rate - 1), "*") FROM number WHERE @y < (@h - 2) and (@y := @y - 1) > 0

    UNION ALL SELECT repeat("*", (@w - 2 * 2)) AS rect
) AS Temp;

结果

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

画等腰三角形语句

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 轴比例，调整显示效果 SELECT concat(repeat(" ", @rate * (@w / 2 - (@y - 1) * @k) - 1), "*", repeat(IF(@y = @h, "*", " "), @rate * (2 * (@y - 1) * @k) - 1), IF(@y = 1, "", "*")) AS triangle FROM number  WHERE (@y := @y + 1) AND @y <= @h;

结果

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

画正弦曲线语句

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

结果

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

画圆语句

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

<无>

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

人气教程排行