Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > SQL也疯狂：MySQL绘制简单几何图形(2013-04-12)

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

WBOY
Release： 2016-06-07 14:57:00
Original
1501 people have browsed it

用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);
    Copy after login
  1. 画横线 语句 
    SET @w := 24;  # 宽 <span>SELECT</span> repeat(<span>"*"</span>, @w) AS line;
    Copy after login
    结果 
    +--------------------------+
| line                     |
+--------------------------+
| ************************ |
+--------------------------+
    Copy after login
  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;
    Copy after login
    结果 
    +------+
| line |
+------+
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
| *    |
+------+
    Copy after login
  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;
    Copy after login
    结果 
    +--------------------------+
| line                     |
+--------------------------+
|  *                       |
|    *                     |
|      *                   |
|        *                 |
|          *               |
|            *             |
|              *           |
|                *         |
|                  *       |
|                    *     |
|                      *   |
|                        * |
+--------------------------+
    Copy after login
  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;
    Copy after login
  5. 结果 
    +-----------------------+
| X                     |
+-----------------------+
| *                   * |
|   *               *   |
|     *           *     |
|       *       *       |
|         *   *         |
|           *           |
|         *   *         |
|       *       *       |
|     *           *     |
|   *               *   |
| *                   * |
+-----------------------+
    Copy after login
  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;
    Copy after login

  8. 结果 
    +--------------------------+
| rect                     |
+--------------------------+
| ************************ |
| *                      * |
| *                      * |
| *                      * |
| *                      * |
| *                      * |
| *                      * |
| *                      * |
| *                      * |
| *                      * |
| *                      * |
| *                      * |
| *                      * |
| ************************ |
+--------------------------+
    Copy after login
  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;
    Copy after login
  10. 结果 
    +----------------------+
| rectXX               |
+----------------------+
| ******************** |
| **               * * |
| *  *           *   * |
| *    *       *     * |
| *      *   *       * |
| *        *         * |
| *      *   *       * |
| *    *       *     * |
| *  *           *   * |
| **               * * |
| ******************** |
+----------------------+
    Copy after login
  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;
    Copy after login
    结果 
    +---------------------+
| triangle            |
+---------------------+
|          *          |
|         * *         |
|        *   *        |
|       *     *       |
|      *       *      |
|     *         *     |
|    *           *    |
|   *             *   |
|  *               *  |
| ******************* |
+---------------------+
    Copy after login
  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;
    Copy after login
    结果 
    +---------------------------------+
| sin                             |
+---------------------------------+
|                     *           |
|                         *       |
|                            *    |
|                              *  |
|                               * |
|                              *  |
|                            *    |
|                         *       |
|                     *           |
|                *                |
|           *                     |
|       *                         |
|    *                            |
|  *                              |
| *                               |
|  *                              |
|    *                            |
|       *                         |
|           *                     |
|                *                |
|                     *           |
|                         *       |
|                            *    |
|                              *  |
|                               * |
|                              *  |
|                            *    |
|                         *       |
|                     *           |
+---------------------------------+
    Copy after login
  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;
    Copy after login
    结果 
    +-----------------------------------------------------+
| circle                                              |
+-----------------------------------------------------+
|                         * *                         |
|               *                     *               |
|            *                           *            |
|         *                                 *         |
|       *                                     *       |
|      *                                       *      |
|    *                                           *    |
|   *                                             *   |
|  *                                               *  |
|  *                                               *  |
| *                                                 * |
| *                                                 * |
| *                                                 * |
|  *                                               *  |
|  *                                               *  |
|   *                                             *   |
|    *                                           *    |
|      *                                       *      |
|       *                                     *       |
|         *                                 *         |
|            *                           *            |
|               *                     *               |
|                         * *                         |
+-----------------------------------------------------+
    Copy after login
  • UNION 后再用一个 SELECT 仅是包装一下，看起来像一个整体，不那么松散
  • 回头想想，上面用了 UNION 的都可以用 IF 来整合在一个语句里，不过会显得很臃肿
  • 亚丹
    seesea2517#gmail#com
    http://seesea.blog.chinaunix.net
    http://blog.csdn.net/nicenight
    http://my.oschina.net/seesea2517
<无> 
x
Copy after login
Related labels：
mysql sql Geometry Simple draw
source：php.cn
Previous article：自动更新日期列的值为最大日期的触发器 Next article：T-SQL生成SQL追踪信息
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
SQL Count() also requires 0 I have only one table and I want to count each entry of a tag that starts with 0. Now I tr...
From 2024-04-06 19:48:53
0
1
449
Calculate the sum of fields in another table using MySQL SQL query I have a schema like this: User table with attributes "user_id" and "userna...
From 2024-04-06 19:39:29
0
1
441
sql groupby not working, I want to group by code_branch -&gt;select('a.statusasstatus','b.nameascustomer_name','e.nameasbranch_name','c.nameas...
From 2024-04-06 19:24:49
0
2
488
Record query under partial value conditions - SQL skill sharing I have a table called zipcode which has a column called code. The Code column contains the...
From 2024-04-06 16:01:26
0
1
395
Node.js: Unable to store SQL query results in an array I'm trying to push the results of a SQL query to an array. However, it doesn't seem to wor...
From 2024-04-06 14:14:46
0
1
373
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template