mysql导出多个表数据为excel方法,substring函数查询

//查询sys_username以S.00655开头的用户
SELECT SUBSTRING(sys_username,3),sys_password FROM `tbl_sa_syslogin` where sys_username like 'S.%'; 

//修改S.00655用户的密码等于S码
update tbl_sa_syslogin set sys_password = SUBSTRING(sys_username,3) where sys_username like 'S.%'; */

//建立一个中间导出表
create table tbl_export_network(
    id int,
    sys_username VARCHAR(10),
    sys_password VARCHAR(10),
    network_name VARCHAR(50),
    network_name_old varchar(50),
    phone VARCHAR(20),
    telephone VARCHAR(20),
    network_type  VARCHAR(10)
);

//将关联查询的结果添加到中间导出表
insert into tbl_export_network 
SELECT u.id,u.sys_username,u.sys_password,nw.network_name,nw.network_name_old,nw.phone,nw.telephone,nw.network_type FROM `tbl_sa_syslogin` u ,
tbl_nw_networkinfo nw
where nw.f_syslogin_id =u.id and u.sys_username like 'S.%' order by network_type,f_pca_province_id,f_pca_city_id,f_pca_area_id

//查询中间导出表
select * from tbl_export_network;

//最后利用Navicat工具导出数据为excel，完成~!

 

转载于:https://www.cnblogs.com/simpledev/p/3657101.html