SQL 자주 쓰는 쿼리

• Table RandomSample20ForEachPR에서 TUIMSimPrecision이라는 Column 삭제 

 ALTER TABLE aboutme.RandomSample20ForEachPR DROP COLUMN TUIMSimPrecision  

• Table RandomSample의 TopicCount라는 Column을 TestFlag로 이름 변경

 ALTER TABLE aboutme.RandomSample CHANGE TopicCount TestFlag INT  

• Table RandomSample20ForEach의 MUIMSim이라는 Column의 데이터속성 변경 

 ALTER TABLE aboutme.RandomSample20ForEach MODIFY COLUMN MUIMSim decimal(12,10)  

• SUM() and LIMIT  같이 쓸때

 SELECT SUM(SampleFlag) FROM   
      (SELECT SampleFlag FROM aboutme.RandomSample20ForEach WHERE TwitterURI = 'http://www.twitter.com/parklize' ORDER BY TUIMSim DESC LIMIT 10)   
      AS SuccessRate  

• INSERT INTO... SELECT FROM

 INSERT INTO table2  
 (column_name(s))  
 SELECT column_name(s)  
 FROM table1;  

• Subquery  limit 문구넣고 실행

 INSERT INTO aboutme.RandomSample50 (TwitterURI, LINK, TopicCount, CreateTime, TestFlag)   
 SELECT TwitterURI, LINK, TopicCount, CreateTime, 1 FROM aboutme.RandomSample  
 WHERE PositiveTestCases = '1' AND TwitterURI IN  
 (SELECT * FROM (SELECT DISTINCT TwitterURI FROM aboutme.RandomSample ORDER BY RAND() LIMIT 50) temp);  

• Substring

SELECT SUBSTRING_INDEX(UGCID2, '/', -1) FROM UGCListFor150URL  

http://www.twitter.com/parklize/status/562289797117800448->562289797117800448

• MAMP 에서 MySQL접속 및 sql 파일 실행하기

 
/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot  

MySql접속후 아래 명령으로 .sql파일 실행시킨다.

 mysql> source file_name  
 mysql> \. file_name  

• 현재 connection수 확인하고 kill하기

 SHOW STATUS WHERE `variable_name` = 'Threads_connected';  
 SHOW PROCESSLIST;  
 KILL 9690; // use id to kill the process  

• Get all "KILL" command for ending all processes over 200 secs.

 select concat('KILL ',id,';') from information_schema.processlist  
 where user='root' and time > 200;  

• update from select

 update RandomSampleUIM uim, (select distinct Topic, BabelID from _RandomSampleUIM) olduim set uim.`BabelID` = olduim.`BabelID` where uim.`Topic` = olduim.`Topic`;  

• my.conf 설정 (/etc/mysql/my.conf)
• max_connections: 연결가능한 connections 수를 설정
• Ubuntu MySQL start/stop

• /etc/init.d/mysql start/stop/restart