真知棒系列之 SQL

真知棒系列主要是将平时开发过程中后知后觉的知识点进行了部分的规整。真知,棒!

删除没有在 B 中的 A ID

1
delete from A where ID not in (select distinct ID from B)

####查询过期的数据

1
select count(*) from pi_BasicInfo where pi_bid not in (select pi_bid from pi_memInfo) and upTime<(unix_timestamp()-2*3600) and isAutoTest = 'no' order by uptime desc;

内存使用量 top 5 的 pageID

1
select distinct pageValue,memValue from pi_memInfo,pi_pageInfo where pi_memInfo.pi_bid in (select pi_bid from pi_BasicInfo where sessionid in (select * from (select sessionid from pi_BasicInfo where isautotest = 'yes' order by uptime desc limit 1) temp_tab)) and pi_memInfo.pi_bid = pi_pageInfo.pi_bid order by cast(memValue as DECIMAL(12,2)) desc limit 5;

cpu 使用量 top 5 的 pageID

1
select distinct pageValue,cpuValue from pi_cpuInfo,pi_pageInfo where pi_cpuInfo.pi_bid in (select pi_bid from pi_BasicInfo where sessionid in (select * from (select sessionid from pi_BasicInfo where isautotest = 'yes' order by uptime desc limit 1) temp_tab)) and pi_cpuInfo.pi_bid = pi_pageInfo.pi_bid order by cast(cpuValue as DECIMAL(12,2)) desc limit 5;

fps 值介于 0 ~ 30 次数最多的 pageID

1
select pageValue,count(fpsValue) from pi_fpsInfo,pi_pageInfo where pi_fpsInfo.pi_bid = pi_pageInfo.pi_bid and fpsValue > 0 and fpsValue < 30 and pi_fpsInfo.pi_bid in (select pi_bid from pi_BasicInfo where sessionid in (select * from (select sessionid from pi_BasicInfo where isautotest = 'yes' order by uptime desc limit 1) temp_tab)) group by pageValue order by count(fpsValue) desc limit 5;

导出某数据库某表的表结构

1
mysqldump -u root -p HX_PerformanceInfo -d pi_pageInfo > /data/www/html/ios/PI/page.sql

寻找表内不以 KB 结尾的字段

1
select * from pi_trafficInfo where trafficStat not like '% KB';

将表内的‘字节’字段修改为以 KB 为单位

1
update pi_trafficInfo set trafficStat = concat(SUBSTRING_INDEX(SUBSTRING_INDEX(trafficStat, ' ', 1), ' ', -1)/1024,' KB') where trafficStat like '%字节';

创建具有外键的表

1
2
3
4
5
6
7
8
CREATE TABLE `pi_httpInfo` (
`pi_hid` int(11) NOT NULL AUTO_INCREMENT,
`timeStamp` varchar(255) DEFAULT NULL,
`url` varchar(255) DEFAULT NULL,
`pi_bid` int(11) DEFAULT NULL,
PRIMARY KEY (`pi_hid`),
KEY `pi_httpInfo_pi_BasicInfo_pi_bid_fk` (`pi_bid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
文章目录
  1. 1. 删除没有在 B 中的 A ID
  2. 2. 内存使用量 top 5 的 pageID
  3. 3. cpu 使用量 top 5 的 pageID
  4. 4. fps 值介于 0 ~ 30 次数最多的 pageID
  5. 5. 导出某数据库某表的表结构
  6. 6. 寻找表内不以 KB 结尾的字段
  7. 7. 将表内的‘字节’字段修改为以 KB 为单位
  8. 8. 创建具有外键的表