LOAD DATA LOCAL INFILE 'c:\userlist.txt' INTO TABLE temp_user
FIELDS ESCAPED BY '\\' TERMINATED BY '\t' LINES TERMINATED BY '\r\n' (`username`, `userid`)
CREATE TABLE tc_userinfo(`username` VARCHAR(100),`ip` VARCHAR(100))
ENGINE = MYISAM DEFAULT CHARSET=utf8
SELECT * FROM tc_userinfo
ORDER BY papertimes DESC
1.关联userid
UPDATE tc_userinfo t ,ol_user u SET t.userid = u.userid WHERE t.username = u.username
/*truncate table ol_ip_tongchao*/
SELECT INET_ATON('123.15.57.50')
SELECT * FROM ol_ip_tongchao WHERE startip<2064595250 AND endip>2064595250
2.ip数据
LOAD DATA LOCAL INFILE 'c:\ip01.txt' INTO TABLE `servant_591up`.`ol_ip_tongchao`
FIELDS ESCAPED BY '\\' TERMINATED BY ',' LINES TERMINATED BY '\r\n' (`startip`, `endip`, `pos`,`detail`)
SELECT COUNT(*) FROM ol_ip_tongchao
TRUNCATE TABLE ol_ip_tongchao
SELECT * FROM ol_ip_tongchao LIMIT 100
SELECT INET_ATON('114.229.74.103')
UPDATE tc_userinfo t ,ol_ip_tongchao i SET t.diqu = i.pos WHERE INET_ATON(t.ip)>=i.startip AND INET_ATON(t.ip)<=i.endip
SELECT * FROM ol_ip_tongchao WHERE INET_ATON('114.229.74.103')
>startip AND INET_ATON('114.229.74.103')< endip
3.
UPDATE tc_userinfo t SET t.racetimes =
(SELECT COUNT(*) FROM gwy_raceresult WHERE userid = t.userid)
4.
UPDATE tc_userinfo t SET t.papertimes =
(SELECT COUNT(*) FROM ol_simulatelog WHERE userid = t.userid)
5.
UPDATE tc_userinfo t SET t.questiontimes =
(SELECT COUNT(*) FROM ol_answerlog WHERE userid = t.userid)
6.
EXPLAIN
UPDATE tc_userinfo t SET t.wrongtimes =
(SELECT COUNT(*) FROM ol_answerlog_tmp WHERE userid = t.userid AND IsCorrect = 0)
UPDATE tc_userinfo t , ol_answerlog l SET t.wrongtimes = 1
WHERE t.userid = l.userid AND l.IsCorrect = 0
CREATE TABLE ol_answerlog_tmp
SELECT * FROM ol_answerlog WHERE userid IN(SELECT userid FROM tc_userinfo)
7.
UPDATE tc_userinfo t SET t.rewrongtimes = ( SELECT
COUNT(*) FROM ol_tc_wrong WHERE userid = t.userid )
9.
UPDATE tc_userinfo t SET t.reviews = ( SELECT
COUNT(*) FROM ol_userreviews WHERE userid = t.userid AND Reviews IS NOT NULL AND Reviews<>'' )
SELECT * FROM ol_userreviews WHERE Reviews IS NOT NULL AND Reviews<>''LIMIT 100
