九九乘法表
delimiter $$
create procedure lala
( )
begin
declare a int
;
declare b int
;
declare c varchar
(1000
);
set a
=1
;
while a
<=9
do
set c
='';
set b
=1
;
while b
<=a
do
set c
=concat
(c,
' ',a*b
);
set b
=b+1
;
end
while;
select c
;
set a
=a+1
;
end
while;
end $$
delimiter
;
call lala
();
将九九乘法表数据输入到表中
create table 九九乘法表
(AB varchar
(1000
));
delimiter $$
create procedure lalala
( )
begin
declare a int
;
declare b int
;
declare c varchar
(1000
);
set a
=1
;
while a
<=9
do
set b
=1
;
set c
='';
while b
<=a
do //建立内循环语句
set c
=concat
(c,
' ',a,
'*',b,
'=',a*b
); //连接字符函数 可以自行定义
set b
=b+1
;
end
while;
insert into 九九乘法表 values
(c
);
set a
=a+1
;
end
while;
end $$
delimiter
;
call lalala
();
select * from lalala
;
mysql
> select * from 九九乘法表
-
> ;
+----------------------------------------------------------------+
| AB
|
+----------------------------------------------------------------+
| 1*1
=1
|
| 2*1
=2 2*2
=4
|
| 3*1
=3 3*2
=6 3*3
=9
|
| 4*1
=4 4*2
=8 4*3
=12 4*4
=16
|
| 5*1
=5 5*2
=10 5*3
=15 5*4
=20 5*5
=25
|
| 6*1
=6 6*2
=12 6*3
=18 6*4
=24 6*5
=30 6*6
=36
|
| 7*1
=7 7*2
=14 7*3
=21 7*4
=28 7*5
=35 7*6
=42 7*7
=49
|
| 8*1
=8 8*2
=16 8*3
=24 8*4
=32 8*5
=40 8*6
=48 8*7
=56 8*8
=64
|
| 9*1
=9 9*2
=18 9*3
=27 9*4
=36 9*5
=45 9*6
=54 9*7
=63 9*8
=72 9*9
=81
|
+----------------------------------------------------------------+
9 rows
in set (0.01 sec
)
将九九乘法表导入到windows系统，并生成xls文件
第一步 在配置文件中开放权限
[mysqld
]
socket
= /usr/local/mysql/mysql.sock
log-bin
= ejzwn
secure-file-priv
="" '//插入这一行权限是不限制文件保留在任意地方'
server-id
= 1
systemctl restart mysqld
第二步 创建一个存放目录并赋予权限
[root@localhost ~
]
[root@localhost ~
]
[root@localhost ~
]
drwxrwxrwx 2 root root 6 10月 20 12:17 xxx
第三步 去Mysql中将表导入目录中
mysql -uroot -pabc123
use school
;
select * from 九九乘法表 into outfile
'/opt/xxx/zz.xls'; //查询后传入到相关的目录中取名字为zz.xls
Query OK, 9 rows affected
(0.00 sec
)
cat zz.xls
'//可以看看文件有没有九九乘法表'
将 /opt/xxx 中的文件拖到桌面就可以了
