MySQL自动化生成HTML页面(导出数据)极限SQL编程

日客户要求表内的数据依据某种分组生成HTML页面进行展示，一般处理这种需求直接上编程工具就好了，从数据库里读取数据，根据规则生成字符串，最后将字符串写出到文件。由于需求比较急，作为数据库编程系列文章，如果能用SQL实现首选还是SQL，这样处理既直接又快速，不过针对SQL要真的有耐心和信心写完，调试更是崩溃。由于要写出文件到硬盘，最后还是选择MySQL作为数据库工具，Navicat作为开发工具。

有两张表计划表、市县表，二者依靠市县编码（sxbm）进行等值连接，计划表内含有各个学校投放在各个市县的专业代号（zydh），专业名称（zymc）、招生备注(bz)、学制(xz)、要求的学历(xl)、计划数(jh)等字段组成的计划信息，院校编码(yxbm)为学校的两位数编码，院校代号(yxdh)为院校编码(yxbm)+市县编码(sxbm)组成的四位数编码，院校代号其实可以区分出学校在哪个市县的投档的专业计划。要求以学校为单位创建HTML页面，页面首先要以市县作为表格分割，然后根据专业代号排序。具体实现过程如下：

创建计划表：

CREATE TABLE `zzjh2019v` (
`YXDH` varchar(9) COMMENT '学校代号',
`YXMC` varchar(54) COMMENT '学校名称',
`ZYDH` varchar(2) COMMENT '专业代号',
`ZYMC` varchar(28) COMMENT '专业名称',
`XZ` varchar(3) COMMENT '学制',
`XL` varchar(4) COMMENT '学历',
`JH` varchar(6) COMMENT '招生计划数',
`BZ` varchar(200) COMMENT '备注',
`yxbm` char(2) COMMENT '学校编码',
`sxbm` char(2) COMMENT '市县编码'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

创建市县编码表：

CREATE TABLE `sx` (
`sxbm` char(2) COMMENT '市县编码',
`sxmc` varchar(20) COMMENT '市县名称'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

纠结了很久这个东西怎么写，最后采取游标、拼接字符串、字符串聚合，动态SQL，写文件等一些列操作完成需求，创建的存储过程如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `splitjh`()
BEGIN
declare done INT DEFAULT 0;
declare pyxbm char(2);
declare psxmc varchar(10);
declare pyxmc varchar(50);
declare pjhall int;
declare pjhrows TEXT;
declare yxjh cursor
for
select yxbm,yxmc,sum(jh) jhall from zzjh2019v a,sx b where a.sxbm=b.sxbm group by yxbm,yxmc order by yxbm;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
open yxjh;
fetch yxjh into pyxbm,pyxmc,pjhall;
while done !=1 do
select group_concat(jhrow separator '') into pjhrows from
(select concat('<tr class="subtitle"><td>',yxdh,'</td><td>',yxmc,'在 <span><font color="red">',b.sxmc,'</font></span> 招生计划如下</td><td>',sum(jh),'</td><td></td><td></td></tr>',group_concat('<tr class="jhrow"><td>',zydh,'</td><td>',zymc,'(',bz,')</td><td>',jh,'</td><td>',xz,'</td><td>',xl,'</td></tr>' order by zydh separator '')) jhrow
from zzjh2019v a,sx b where yxbm=pyxbm and a.sxbm=b.sxbm group by yxdh order by yxdh,zydh) jhs;
set @pfilename = concat('''d:/32/1/1/jh11',pyxbm,'.html''');
set @sql =concat('select concat(''<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><link rel="stylesheet" type="text/css" href="zsjh.css" ><title>3+2计划</title></head><body><h3></h3><table><tr class="subtitle"><th>代号</th><th>专业及名称备注</th><th>人数</th><th>学制</th><th>学历</th></tr>'',''',pjhrows,''',''</body></html>'') from dual into outfile ',@pfilename);
prepare execsql from @sql;
execute execsql;
DEALLOCATE PREPARE execsql;
fetch yxjh into pyxbm,pyxmc,pjhall;
end while;
close yxjh;
END;

首先看效果，执行过程

call splitjh();

在磁盘形成的HTML文件效果如下图（数据有一定的敏感性，进行了遮挡处理）：

文件展示页面

生成的文件列表如下图：

生成的文件列表

这里一共有87所学校，所以生成了87的文件，添加CSS样式文件，让表格呈现如前图所示。

技术点

1）MySQL的游标，以及循环读取游标的方法，涉及的语句如下：

declare yxjh cursor
for
select yxbm,yxmc,sum(jh) jhall from zzjh2019v a,sx b where a.sxbm=b.sxbm group by yxbm,yxmc order by yxbm;#游标定义
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;#游标循环条件，注意此句一定要定义在游标之后，才起作用
open yxjh;#打开游标
fetch yxjh into pyxbm,pyxmc,pjhall;#将游标行内容赋值给变量。

2）执行动态SQL，由于MySQL into outfile 后接的文件名不能为变量，所以必须使用动态SQL的方法，涉及的语句如下：

prepare execsql from @sql;#从一个变量准备一个动态sql，注意execsql不用提前定义
execute execsql;#执行准备好的语句
DEALLOCATE PREPARE execsql;#销毁语句

综上就是使用MySQL数据库，并借用MySQL写文件的方式将数据从数据库内按照需求导出文件，为何不用navicat导出呢？因为无法达到要求，又是聚合、又是格式，所以只能自己编写过程通过SQL语句拼接字符串的方式来实现。没有太多的技术难度，主要是想法和调试难度。后续在此基础上又开发了以市县为单位创建HTML文件，各招生学校作为分割的过程。本案例是实际需求催生出来的做法，在遇到这样的需求前你是先想到SQL还是先想到开发工具呢？从实际效果看使用SQL这种方式更加灵活。这样的SQL实现的字符串拼接是不是有点极限呢？

y_web.py(更新)

.昨日回顾

服务器软件：Apache、IIS

浏览器是一个翻译官，是一个解释器。浏览器可以翻译有HTML、CSS、JavaScript。

服务器端脚本程序：PHP、 .net、JSP、c#等

.php文件要由PHP脚本引擎来进行处理。

网站前台(客户端) 网站后台(服务器端) 数据库

HTML(结构) PHP MySQL

CSS JSP Oracle

JavaScript .net SqlServer

HTML结构：主要来说明，哪个地方是标题，哪个地方是段落，哪个地方是表格，哪个地方是表单。

CSS表现(格式)：主要功能是，给相对应的HTML标记定义各种各样的格式。

JavaScript行为：主要用于与计算机网页进行交互(交流)。

块元素和行内元素

块元素：宽度单独占一个通栏宽，块元素的前台的其它元素，都要另起一行来排。

比如：<p> <pre> <hr> <h1>……<h6> <ul> <ol> <li>

DIV和SPAN就是一个容器，容器中可以存放任何可以存放在<body>中的内容或标记。

行内元素：行内元素没有宽度和高度的概念，也就是说通过CSS无法给行内元素增加宽和高。多个行内元素会放在同一行，不会另起一行来排版。

2.<marquee></marquee>滚动字幕标记：块元素

Direction：滚动的方向，取值：left、right、up、down

Behavior：滚动的方式，取值：scroll(滚动)、 alternate(弹动)、 slide(滚动一次)

Width：滚动的区域宽度，取值：px或%

Height；滚动的区域高度

bgColor：背景颜色，如：bgcolor=“#FF0000” bgcolor=“rgb(255,0,0)”

scrollAmount：滚动步长值，一步走多远，值越大，走的越快

scrollDelay：两次滚动的间隔时间，以毫秒为单位 1秒=1000毫秒

3.计算机进制

进制，就是一种进位方法。

10进制：有10个基本数，0、1、2、3、4、5、6、7、8、9，运算规则是：逢十进一

例如：9 10 19 20 29 30

2进制：有2个基本数，0、 1，运算规则是：逢二进一。比如：(10)2 11 100 101 110

8进制：有8个基本数，0、1、2、3、4、5、6、7，运算规则是：逢八进一。比如：(17)8 20 21 26 27 30

因为二进制太长，不方便记忆，因此小型机引入了八进制，八进制可以很好的反应二进制。

因此，一个八进制数，用3位二进制数来表示。

基R=2^3

16进制：有16个基本数，0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F，运算规则是：逢16进一

比如：EF F0 F1 F2 FE FF (100)16

16进制的一位对应4位二进制。基R=2^4

10进制	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
2进制	1	10	11	100	101	110	111	1000	1001	1010	1011	1100
8进制	1	2	3	4	5	6	7	10	11	12	13	14	15	16	17	20
16进制	1	2	3	4	5	6	7	8	9	A	B	C	D	E	F	10	11	12	13

十进制转成二进制

分为整数部分和小数部分，分别转换。比如：将(10.125)10转成二进制？ 1010.001

（1）整数部分转成二进制：除2取余法。每次将整数除以2，取余数，一直除下去，直到整数部分(商)为0。最后读数时，从下往上读取。

第N次	整数10	商	余数
第1次	10/2	5	0
第2次	5/2	2	1
第3次	2/2	1	0
第4次	1/2	0	1	最后结果为：1010

（2）小数部分转成二进制：乘2除整法。每次将小数部分乘以2，取整数，一直乘下去，直到小数部分为0。最后读数时，从上往下读取。

第N次	小数0.125	积	整数
第1次	0.125*2	0.25	0
第2次	0.25*2	0.5	0
第3次	0.5*2	1.0	1
				最后结果为：001

二进制转成十进制

不分整数部分和小数部分，按权相加法。换句话说：二进制上的每一位，乘以所在位权(也就是2的几次方)，最后将各个位的乘相加，就能得到十进制。

1010.001 = 1*2^3 + 0*2^2 + 1*2^1 + 0*2^0 + 0*2^-1 + 0*2^-2 + 1*2^-3 = 8+2+ 1/8 = 10+0.125 = 10.125

4.计算机编码介绍(字符集)

计算机只能二进制，比如：a-z、A-Z、0-9、标点符号，计算机都不能直接识别。每个人都可以来约定一套字符编码（比如：a用二进制100表示，b用二进制101表示,c用二进制的110表示等）

计算机是由美国人发明的，美国标准化局同样制定了一套统一的编码，叫ASCII码。

ASCII码

ASCII编码用7位或8位(1个字节)二进制来表示，共可以表示128或256个字符。包括：大小写字母、数字、标点符号、图形符号等。

5.ANSI编码

每个国家都想让计算机能显示或处理本国的语言，都对ASCII码进行了扩展。

在中国(简体字)，ANSI编码是指GB2312或GBK。

在台湾(繁体字)，ANSI编码是指BIG5

在日本，ANSI编码是指JIS。

……

注意：ANSI编码是跟随操作一齐安装的，也就是，安装了中文操作系统后，它的默认编码就是GB2312。

6.GB2312编码

GB2312是中国标准化局，在1980年制定的一套显示简体中文的编码。

GB2312是用2个字节来显示。共可以表示2^16=256*256=65536个字符。

GB2312共保存了6763个常用汉字，一级汉字3700个。

GBK编码

对GB2312进行了扩充，可以显示人名、古汉语中的一些罕见字等。

GBK也是用2个字节来表示。共存储了2.3万个左右的中文字符。

GBK的范围，要比GB2312大的多。

BIG5编码

BIG5用于台湾、香港、澳门地区的繁体中文编码。

Unicode编码

用4个字节表示字符，共可以表示42个亿个字符。

Unicode编码可以显示世界上所有国家的语言。

缺点：文件庞大，效率不高，不利于推广。

UTF-8统一格式转换

它会自动根据不同的字符，来选择不同编码长度。

7.<meta>标记

<head>标记中的内容在浏览器中，是不可见的。

主要用两个常用的属性：

http-equiv：主要设置一些控制信息，属性值是固定的。比如：content-type

name：一般用于设置网页描述信息，像关键字、网页描述、网页版权、作者等，属性值是固定的。比如：keywords

content：是具体的参数或内容，与http-equiv和name属性进行对应。

（1）设置网页的字符集或编码

（2）设置网页刷新或跳转

注意：刷新功能相当于浏览器中的刷新按钮

（3）设置网页关键字(SEO优化)，给搜索引擎(搜索机器人)使用

（4）设置网页描述信息(SEO优化)

8.XHTML

XHTML是可扩展超文本标注语言，XHTML是更纯净的HTML，语法更严格。

XHTML的目的是为了取代HTML4.01。

XHTML是W3C的一个标准或规范。

编写XHTML的规范，与HTML编写有何不同？

1）所有的标记都必须被关闭。比如：<br>转换<br /> <hr>转换后 <hr />

2）XHTML要求所有的标记名称必须小写。比如：<meta><table><img><font>

3）XHTML要求所有的属性名必须小写，属性值必须加引号。

4）XHTML要求所有的属性必须有值。

<hr noshade=“noshade” />

<input type=“radio” checked=“checked” />

5）所有的标记要顺序嵌套，不能交叉嵌套；

6）XHTML文件，必须要有DTD文档类型定义；

DTD(Document Type Define)文档类型定义

（1）严格型Strict

要求HTML代中，不能存在任何的表现，用CSS去代替。比如：<font>、bgcolor、background等

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

（2）过渡型transitional

可以使用任何表现的标记或属性。

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

（3）框架型Frameset

制作框架网页时使用的一种类型，框架主要用于网站的后台管理。

框架技术：是将一个浏览器窗口划分成不同区域，每个区域(窗口)都可以显示一个独立的网页。（HTML第四天单独讲框架，现在作个了解）

9.W3C简介

W3C是万维网联盟，是一个国际性的非盈利性组织，是WEB领域最具权威性的组织。已经制定多达200多项的标准。

比如：XHTML、CSS、ECMAScript(JavaScript)、DOM、 XML等。

10.超级链接

超级链接的功能：就是点击某个链接，可以跳转到目标页面或文件。

超级链接的分类：

（1）按内容分类：文本链接、图片链接、多媒体等

（2）按URL分类：相对URL、绝对URL、锚点

URL，指网址或路径

URL(Uniform Resource Locator，统一资源定位器)，也就是指互联网地址。

http://www.sina.com.cn/about/index.html

第一个部分：协议，指访问什么类型的服务器，如：http://、FTP://、news://

第二个部分：主机名或主机的IP地址。如：www.sina.com.cn

第三个部分：文件夹名，如：about

第四个部分：文件名，如：index.html

11.<a></a>超级链接的标记

格式：<a 属性=“属性值”>新浪网</a>

常用的属性：

Href：指链接的目标文件或网址。比如：href=“http://www.toutiao.com”

Target：指目标文件的打开方式。

_blank：在新窗口中，来打开目标文件；

_self：在当前窗口中，来打开目标文件；

_top：在最顶层窗口中，来打开目标文件(在框架中讲)；

_parent：在父窗口中，来打开目标文件(在框架中讲)

Name：设置锚点的名称。锚点是指：链接到同一个页面的不同部分

举例：<a href=http://www.toutiao.com target=“_blank”>头条号</a>

12.相对路径和绝对路径

绝对URL：它总是以file:///协议开头，去访问目标文件。

如果将当前文件移动到其它地方，这个绝对URL路径不需要修改。

绝对URL，只需要确定目标文件的路径即可，与当前文件位置无关。

例如：<a href=“file:///E:/itcast/20140510/lesson/day2/4.html”>本地绝对路径</a>

相对URL：要确定当前文件与目标文件的关系。

关系分为三种：

平级关系：直接写目标文件的文件名即可。

下级关系：也就是目标文件所在的文件夹与当前文件是平级关系，当前文件与目标文件是上下级关系。

上级关系：目标文件位于上一层级。

../代表上一层文件夹

../../代表上二层文件夹

../../images/01.jpg

提示：在制作网站时，一般使用的都是相对路径。

（3）下载链接：如果链接到的文件，网页不能直接执行的话，都会出现下载提示。

浏览器可以直接执行的文件：.gif、.jpg、.png、html、.htm、.mp3、.swf等

（4）邮箱链接：mailto:beijing2000@126.com

13.锚点链接：指链接到一个网页的不同地方

第一步：定义一个锚点或标记 <a name=“top”></a>

第二步：链接到锚点或标记 <a href=“#top”>返回顶部</a>

<a href=“intro.html#bottom”>链接到intro.html的底部</a>

14.<img>图片标记，单边标记

格式：<img 属性＝“属性值” />

常用的属性：

Src：指图片的URL，可以是相对路径，也可以是绝对路径。

Width：指图片的宽度，单位px或%

Height：指图片的高度

Align：图片的水平对齐方式，取值：left center right ，left或right可以实现图文混排

Alt：如果图片不存在时，显示的提示信息

Hspace：图片左右的距离

Vspace：图片上下的距离

举例：<img src=“images/01.gif” width=“400” height=“300” alt=“宝宝图片” />

如果保证图片在调整过程中不变形（等比例缩放）：只需要设置width和height其中一个即可，另一个会自动缩放。

在线咨询

上一篇：轻松实现HTML表格拖拽和排序，实现复杂交互式数据表变得更简单
下一篇：手把手教你深入CSS实现一个粒子动效的按钮

您的项目需求

*请认真填写需求信息，我们会在24小时内与您取得联系。

整合营销服务商