1.卸载mysql
https://blog.csdn.net/cxy_Summer/article/details/70142322
重装后cmd中不能直接运行mysql 解决方法:将mysql.exe所在路径添加至环境变量里的path中即可。
2.任务栏上的图标显示成白色恢复方法:(1.win+R
(2.>输入:%APPDATA%MicrosoftInternet ExplorerQuick LaunchUser PinnedTaskBar
(3.将问题软件的快捷方式拷贝进TaskBar中,将该软件固定到任务栏,再取消即可恢复。
3. 复制表的结构like
CREATE TABLE IF NOT EXISTS new_tb (LIKE old_tb);
10.11sql语句缓存
①复合内联:
select * from infotb,dictionary where infotb.village=dictionary.village and infotb.town=dictionary.town and infotb.districtid=dictionary.districtid;
②模糊内联:
select * from infotb,dictionary where infotb.village like dictionary.village and infotb.town like dictionary.town and infotb.districtid like dictionary.districtid;
③union(链接多个select并去重)
select town from infotb union select village from dictionary;
④insert into与select结合使用:
a.复制数据:insert into 空表名 select * from 老表名;(要求空表与老表字段结构完全一样)
b.分内容插入数据:insert into 空表名 (字段1,字段2,字段3)select 字段1,字段2,字段3 from 老表名;
例子:
insert into newtb (district,districtid,town,townid,village,villageid) select infotb.district,infotb.districtid,infotb.town,infotb.townid,infotb.village,dictionary.villageid from infotb,dictionary where infotb.village like dictionary.village and infotb.town like dictionary.town and infotb.districtid like dictionary.districtid;
⑤去除测试数据:
insert into to_stat_rev
select * from to_stat_rev1
where event_desc not like '%测试%'
and lower(event_desc) not like '%test%'
and lower(event_desc) not like '%teat%'
and event_src_name not like '%8890%'
and event_src_id in(1,5,100,1002)
and create_time >='2019-08-25'
and street_id is not null ;
⑥去除冗余字段生成简化表:
a.建立22个字段的简化表to_stat_tev
CREATE TABLE `to_stat_rev` ( `rec_id` bigint(20) NOT NULL DEFAULT '0', `create_time` datetime NOT NULL, `event_desc` varchar(2000) DEFAULT NULL, `event_src_id` int(11) DEFAULT NULL, `event_src_name` varchar(40) DEFAULT NULL, `rec_type_id` int(11) DEFAULT NULL, `rec_type_name` varchar(40) DEFAULT NULL, `event_type_id` int(11) DEFAULT NULL, `event_type_name` varchar(20) DEFAULT NULL, `main_type_id` int(11) DEFAULT NULL, `main_type_name` varchar(40) DEFAULT NULL, `sub_type_id` int(11) DEFAULT NULL, `sub_type_name` varchar(80) DEFAULT NULL, `district_id` int(11) DEFAULT NULL, `district_name` varchar(40) DEFAULT NULL, `street_id` int(11) DEFAULT NULL, `street_name` varchar(40) DEFAULT NULL, `community_id` int(11) DEFAULT NULL, `community_name` varchar(40) DEFAULT NULL, `event_state_id` int(11) DEFAULT NULL, `event_state_name` varchar(100) DEFAULT NULL, `archive_time` datetime DEFAULT NULL, PRIMARY KEY (`rec_id`,`create_time`) USING BTREE, KEY `idx_stat_info_19` (`archive_time`) USING BTREE, KEY `idx_stat_info_20` (`create_time`) USING BTREE, KEY `idx_stat_info_21` (`event_state_id`) USING BTREE, KEY `idx_stat_info_101` (`sub_type_id`,`district_id`) USING BTREE, KEY `idx_stat_info_103` (`create_time`,`district_id`) USING BTREE, KEY `idx_stat_info_104` (`create_time`,`event_state_id`) USING BTREE, KEY `idx_stat_info_105` (`create_time`,`rec_type_id`) USING BTREE, KEY `idx_stat_info_106` (`district_id`) USING BTREE, KEY `idx_stat_info_107` (`street_id`) USING BTREE, KEY `idx_stat_info_108` (`community_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
b.从大表中拷贝数据
insert into to_stat_rev (rec_id,create_time,event_desc,event_src_id,event_src_name,rec_type_id,rec_type_name,
event_type_id,event_type_name,main_type_id,main_type_name,sub_type_id,sub_type_name,
district_id,district_name,street_id,street_name,community_id,community_name,event_state_id,event_state_name,archive_time) select rec_id,create_time,event_desc,event_src_id,event_src_name,rec_type_id,rec_type_name,
event_type_id,event_type_name,main_type_id,main_type_name,sub_type_id,sub_type_name,district_id,
district_name,street_id,street_name,community_id,community_name,event_state_id,event_state_name,archive_time from to_stat_info;
`rec_id` bigint(20) NOT NULL DEFAULT '0',
`create_time` datetime NOT NULL,
`event_desc` varchar(2000) DEFAULT NULL,
`event_src_id` int(11) DEFAULT NULL,
`event_src_name` varchar(40) DEFAULT NULL,
`rec_type_id` int(11) DEFAULT NULL,
`rec_type_name` varchar(40) DEFAULT NULL,
`event_type_id` int(11) DEFAULT NULL,
`event_type_name` varchar(20) DEFAULT NULL,
`main_type_id` int(11) DEFAULT NULL,
`main_type_name` varchar(40) DEFAULT NULL,
`sub_type_id` int(11) DEFAULT NULL,
`sub_type_name` varchar(80) DEFAULT NULL,
`district_id` int(11) DEFAULT NULL,
`district_name` varchar(40) DEFAULT NULL,
`street_id` int(11) DEFAULT NULL,
`street_name` varchar(40) DEFAULT NULL,
`community_id` int(11) DEFAULT NULL,
`community_name` varchar(40) DEFAULT NULL,
`event_state_id` int(11) DEFAULT NULL,
`event_state_name` varchar(100) DEFAULT NULL,
`archive_time` datetime DEFAULT NULL,
PRIMARY KEY (`rec_id`,`create_time`) USING BTREE,
KEY `idx_stat_info_19` (`archive_time`) USING BTREE,
KEY `idx_stat_info_20` (`create_time`) USING BTREE,
KEY `idx_stat_info_21` (`event_state_id`) USING BTREE,
KEY `idx_stat_info_101` (`sub_type_id`,`district_id`) USING BTREE,
KEY `idx_stat_info_103` (`create_time`,`district_id`) USING BTREE,
KEY `idx_stat_info_104` (`create_time`,`event_state_id`) USING BTREE,
KEY `idx_stat_info_105` (`create_time`,`rec_type_id`) USING BTREE,
KEY `idx_stat_info_106` (`district_id`) USING BTREE,
KEY `idx_stat_info_107` (`street_id`) USING BTREE,
KEY `idx_stat_info_108` (`community_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;