MySQL Query Example
Posted 2011. 10. 20. 10:26마지막 두 문자 추출
SELECT RIGHT(location , 2) FROM my_contacts;
콤마 앞의 모든 문자 추출
SELECT SUBSTRING_INDEX(location , ’ I ’ I1) FROM my_contacts;
대문자 / 소문자 변환
UPPER(string), LOWER(string)
문자 역순
REVERSE(string)
문자열 앞/뒤 공백제거
LTRIM(string) RTRIM(string)
문자수 반환
LENGTH(string)
최대값 반환
MAX(string)
합계 반환
SUM(string)
평균 반환
AVR(string)
interests 문자열의 앞에서 interest1의 값 길이 +2 만큼 제거.
SUBSTR(interests, LENGTH(interest1)+2);
상관서브쿼리(1)
SELECT mc.first_name, mc.last_name
FROM my_contact AS mc
WHERE
3 = (SELECT COUNT(*) FROM contact_interest WHERE contact_id = mc.contact_id);
상관서브쿼리(2)
SELECT mc.first_name firstname, mc.last_name lastname, mc.email email
FROM my_contact mc
WHERE NOT EXISTS
(SELECT * FROM job_current jc WHERE mc.contact_id = jc.contact_id);
IN
SELECT * FROM T1 where x in (select y from T2)
is typically processed as :
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
EXIST
select * from t1 WHERE EXISTS ( select null from t2 where y=x)
This is processed more like :
for x in (select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then OUTPUT THE RECORD
end if
end loop
- Filed under : DEV/DB
