SQL 정리

용도는 그냥 한눈에 보게 정리하기 위해서 (MySQL 기준)

 

select 문만 정리해보려고 한다.

• 최대값
  • SELECT MAX(컬럼) FROM 테이블;
• 최소값
  •  SELECT MIN(컬럼) FROM 테이블;
• COUNT
  • SELECT COUNT(*) FROM 테이블; (이 때 NULL인 데이터는 제외하고 계산합니다.)
  • SELECT count(if(a=1, a, null)) FROM A; //조건
  • SELECT COUNT (DISTINCT COLUMN NAME) FROM TABLE NAME  //중복제거
  • SELECT  COUNT (DISTINCT NAME) as 'count' FROM ANIMAL_INS ;
• GROUP BY
  • SELECT * FROM   tableName  GROUP BY   tableName.user_idx
  •   select * from ( select id, name, hp from member order by date desc )a group by id //그루핑된 집합내에서 정렬필요할 때 
  • SELECT * FROM   tableName  GROUP BY  tableName.user_idx ORDER BY des
• 서브쿼리 

SELECT NAME, COUNT
FROM (
    SELECT NAME,COUNT(NAME) AS 'COUNT'
    FROM ANIMAL_INS
    GROUP BY NAME
) TE
WHERE COUNT>=2
;

 

-- 코드를 입력하세요
SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS 
GROUP BY NAME
HAVING COUNT>=2;

 

 

조인후 정렬

SELECT * FROM A RIGHT OUTER JOIN B ON A.칼럼=B.칼럼 ORDER BY A

조인후 정렬 성능이 더 좋음

SELECT * FROM (SELECT * FROM A ORDER BY 칼럼 DESC)

RIGHT OUTER JOIN (SELECT * FROM B ORDER BY 칼럼 DESC) ON A.칼럼 = B.칼럼

 

 SELECT (데이터검색하기)

SELECT [DISTINCT] 테이블명 {, 테이블명}*

FROM 필드명 {,필드명}*

[WHERE 검색조건]

[ORDER BY 필드명[ASC or DESC] {,필드명 [ASC or DESC]}*]

[GROUP BY 필드명 {, 필드명}*]

[HAVING  검색조건]

 

1. 일반적인 검색

SELECT name, id FROM userdb WHERE level = 'B'

SELCET * FROM userdb

SELECT name, id, email, sex, milage, level FROM userdb

 

2. 결과레코드의 중복제거

SELECT level FROM userdb

SELECT DISTINCT level FROM userdb

 

3. 조건검색

SELECT name, id, email FROM userdb WHERE milage > 3000 AND sex = 'M'

 

4. 검색결과의 정렬

SELECT name, id, milage FROM userdb WHERE milage >= 3000 ORDER BY milage DESC

 

5. 검색결과에 대한 산술계산 및 문자열처리

SELECT name, '님의 마일리지는 ', milage, '점입니다.' FROM userdb WHERE milage >= 3000 ORDER BY milage

SELECT name, id, email, milage+300 FROM userdbWHERE id = 'soony'

 

6. 그룹함수(group function)를 이용한 검색

SELECT count(*) FROM userdb WHERE sex = 'F'

SELECT avg(milage) FROM userdb WHERE sex='F'

 

7. GROUP BY를 이용한 검색

SELECT level, max(milage), min(milage), avg(milage) FROM userdb GROUP BY level

SELECT sex, max(milage), min(milage), avg(milage) FROM userdb GROUP BY sex

 

8. HAVING을 이용한 검색

SELECT sex, max(milage), min(milage), avg(milage) FROM userdb GROUP BY sex HAVING sex = 'F'

 

9. BETWEEN 연산자를 이용한 검색

SELECT name, email, mailage, sex FROM userdb WHERE sex = 'F' AND milage BETWEEN 3000 AND 4000

SELECT name, email, milage, sex FROM userdb WHERE sex = 'F' AND milage >= 3000 AND milage <= 4000

SELECT name, sex, milage FROM userdb WHERE milage NOT IN (3300,3500,3700)

 

10. LIKE를 사용한 검색

SELECT name FROM userdb WHERE name, LIKE '%현%'

SELECT name, id, FROM userdb WHERE id LIKE 'm%'

 

11. NULL값을 갖는 데이터검색

SELECT name, id FROM userdb WHERE email IS NULL

SELECT name, id FROM userdb WHERE email IS NOT NULL

[출처] [MySQL] 명령어 정리 (Select 정리 잘 되어있음)|작성자 참좋은날