ホームページ >データベース >mysql チュートリアル >SQL也疯狂：MySQL绘制简单几何图形(2013-04-12)

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

WBOYオリジナル: 2016-06-07 14:57:001488ブラウズ

用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;  # 宽 <span>SELECT</span> repeat(<span>"*"</span>, @w) AS line;

结果

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

画竖线语句

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 |
+------+
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
+------+

画斜线语句

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                     |
+--------------------------+
|  *                       |
|    *                     |
|      *                   |
|        *                 |
|          *               |
|            *             |
|              *           |
|                *         |
|                  *       |
|                    *     |
|                      *   |
|                        * |
+--------------------------+

画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;

结果

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

画矩形语句

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;

结果

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

把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;

结果

+----------------------+
| 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 轴比例，调整显示效果 <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            |
+---------------------+
|          *          |
|         * *         |
|        *   *        |
|       *     *       |
|      *       *      |
|     *         *     |
|    *           *    |
|   *             *   |
|  *               *  |
| ******************* |
+---------------------+

画正弦曲线语句

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                             |
+---------------------------------+
|                     *           |
|                         *       |
|                            *    |
|                              *  |
|                               * |
|                              *  |
|                            *    |
|                         *       |
|                     *           |
|                *                |
|           *                     |
|       *                         |
|    *                            |
|  *                              |
| *                               |
|  *                              |
|    *                            |
|       *                         |
|           *                     |
|                *                |
|                     *           |
|                         *       |
|                            *    |
|                              *  |
|                               * |
|                              *  |
|                            *    |
|                         *       |
|                     *           |
+---------------------------------+

画圆语句

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

声明：

この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

前の記事：自动更新日期列的值为最大日期的触发器次の記事：T-SQL生成SQL追踪信息

続きを見る

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

関連記事