codegyeon

WHERE

SELECT 문에서 데이터를 필터링하는 데 사용.

 단일 행에 대한 조건을 지정.

having

GROUP BY 문에서 그룹화된 결과를 필터링.그룹화된 결과 중에서 조건을 만족하는 그룹만 선택.

즉, WHERE 절은 전체 테이블의 레코드를 필터링하고,      HAVING 절은 그룹화된 결과에 대한 필터링을 수행

 차이를 알고 있어야 sql 이 수월할것 같다.

--시간 관련

NOW(): 현재 날짜와 시간을 반환합니다.

CURDATE(): 현재 날짜를 반환합니다.

CURTIME(): 현재 시간을 반환합니다.

DATE(): 날짜 부분만 추출합니다.

TIME(): 시간 부분만 추출합니다.

YEAR(): 연도를 추출합니다.

MONTH(): 월을 추출합니다.

DAY(): 일을 추출합니다.

HOUR(): 시간을 추출합니다.

MINUTE(): 분을 추출합니다.

SECOND(): 초를 추출합니다.

DATE_FORMAT(): 날짜를 특정 형식으로 포맷팅합니다.

DATE_ADD() : 날짜, 시간 더하기
DATE_SUB() : 날짜, 시간 빼기
DATEDIFF() : 두 기간 사이의 일수 계산
TIMEDIFF() : 두 기간 사이의 시간 계산
PERIOD_DIFF() : 두 기간 사이의 개월 수 계산
TIMESTAMPDIFF() : 두 기간 사이의 시간 계산

--계산관련

ABS(): 절댓값을 반환합니다.

ROUND(): 반올림 값을 반환합니다.

CEIL(): 올림 값을 반환합니다.

FLOOR(): 내림 값을 반환합니다.

MOD(): 나머지 값을 반환합니다.

RAND(): 0과 1 사이의 랜덤한 값을 반환합니다.

POWER(): 제곱 값을 반환합니다.

SQRT(): 제곱근 값을 반환합니다.

EXP(): e의 지수 값을 반환합니다.

LOG(): 로그 값을 반환합니다.

SIN(): 사인 값을 반환합니다.

COS(): 코사인 값을 반환합니다.

TAN(): 탄젠트 값을 반환합니다.

PI(): 원주율인 π 값을 반환합니다.

--where 관련

LIKE: 패턴 매칭을 수행하여 특정 패턴과 일치하는 값을 선택합니다. 와일드카드 문자인 '%'와 '_'를 사용할 수 있습니다.

IN: 특정 값 목록 중 하나와 일치하는 값을 선택합니다.

NOT IN: 특정 값 목록과 일치하지 않는 값을 선택합니다.

BETWEEN: 특정 범위 내에 있는 값을 선택합니다.

NOT BETWEEN: 특정 범위에 포함되지 않은 값을 선택합니다.

IS NULL: NULL인 값을 선택합니다.

IS NOT NULL: NULL이 아닌 값을 선택합니다.

EXISTS: 서브쿼리의 결과가 존재하는지 여부에 따라 값을 선택합니다.

NOT EXISTS: 서브쿼리의 결과가 존재하지 않는지 여부에 따라 값을 선택합니다.

Subquery 는 SQL 쿼리 내에서 다른 쿼리를 사용하는 것을 말하며,

주 쿼리의 일부로 사용된다. 또, 주 쿼리의 결과에 영향을 미치는 보조적인 데이터를 가져오거나 조건을 필터링하는 데 사용됨.

주로 

WHERE 

SELECT

FROM
에 사용된다.

연습!!

---- WHERE

SELECT * FROM point_users pu
WHERE `point` > (
	SELECT AVG(pu.`point`)  FROM point_users pu 
)

SELECT AVG(pp.`point`)  FROM point_users pp
WHERE user_id IN (
	SELECT user_id FROM users  WHERE name = '이**'
)

---- SELET

SELECT c.checkin_id,
	   c.course_id,
	   c.user_id,
	   c.likes,
   	   (
		SELECT AVG(likes)  FROM checkins
		WHERE course_id = c.course_id  
	   ) as course_avg
  FROM checkins c 




---- FROM

SELECT a.course_id,a.cnt_checkins,b.cnt_total, a.cnt_checkins/b.cnt_total as ratio,c.title  
FROM 
(
	SELECT c.course_id ,COUNT(DISTINCT c.user_id)as cnt_checkins   FROM checkins c 
	group by c.course_id 
)a
INNER JOIN 
(
	SELECT o.course_id,COUNT(user_id) as cnt_total  FROM orders o 
	group by o.course_id 
)b on a.course_id = b.course_id
INNER JOIN courses c on a.course_id = c.course_id



---- with 절

with table1 as (
	SELECT c.course_id ,COUNT(DISTINCT c.user_id)as cnt_checkins   FROM checkins c 
	group by c.course_id 
), table2 as (
	SELECT o.course_id,COUNT(user_id) as cnt_total  FROM orders o 
	group by o.course_id 
)
SELECT a.course_id,a.cnt_checkins,b.cnt_total, a.cnt_checkins/b.cnt_total as ratio,c.title  FROM table1 a
INNER JOIN table2 b on a.course_id = b.course_id
INNER JOIN courses c on a.course_id = c.course_id

* 추가 문법

SUBSTRING (필드, 범위, 범위) : 해당 필드에서 범위를 정해 원하는 값만 얻을 수 있다.

SUBSTRING_INDEX (필드, 기준 , 인덱스) : 해당 필드에서 문자열로 기준을 세워 몇번째 인덱스를 가져올수 있다.

CASE : when then 으로 조건을 걸수 있다.

JOIN 은 둘 이상의 테이블간에 관계를 맺고 , 특정 기준에 따라 행을 결합하는 작업을 수행하는데 사용되는 기능이다.

JOIN 을 이용하여 여러 테이블에서 필요한 데이터를 연결하여 하나의 결과 집합으로 가져올 수 있다. 

JOIN 의 종류

1. INNER JOIN : 두 테이블 간의 교집합을 반환합니다. 일치하는 행만 반환된다.

예)
SELECT * FROM post p 
INNER join users u on o.user_id =p.user_id

2,3 행만 조회

2. LEFT JOIN : 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환한다.

오른쪽 테이블에 일치하는 행이 없는 경우 NULL 값으로 채워진 결과가 반환된다.

예)
SELECT * FROM post p 
LEFT join users u on o.user_id =p.user_id

1,2,3,4 행 전부 조회

3. RIGHT JOIN : (LEFT JOIN 의 반대)

((--근데 왼쪽 테이블이 오른쪽과 1:N 관계일 경우 NULL 값으로 채워질 일은 거의 없을것같다.

예를 들어 글쓴이가 없는 글 이라던지?))

** UNION : 두개 이상의 SELECT 문을 결합하여 하나의 결과 집합으로 반환하는 연산

데이터를 범주에 따라 묶어서 통계치를 구하고 , 정렬한다.

Group by  

group by 는 특정 컬럼 기준으로 데이터를 그룹화하는 역할을 한다.

이를 이용하여 그룹별로 데이터를 집계하거나 필터링을 할 수 있다.

예를 들어 user 의 성씨가  들어있는 데이터가 있을때 group by 를 사용하면

1. 김**						김**
2. 이**						이**
3. 황**						황**
4. 정**			->			정**	
5. 이**						장**
6. 장**
7. 이**
8. 장**

로 그룹화가 되고,

SUM, COUNT, AVG, MIN, MAX 등을 이용하여 원하는 정보를 얻을 수 있다.

예)

COUNT 를 이용한다면,   김** 1명, 이** 3명, ...  

SELECT name, count(*) from users
group by name

Order by

Order by 는 데이터를 정렬하는 역할을 한다.

이를 이용하여 필요한 데이터를 순서대로 정렬하여 가독성을 높이는데 유용하다.

Order by 에는 ASC(오름차순), DESC(내림차순) 이 있으며 Order by 의 기본값은 오름차순이다.

예)

SELECT name, count(*) from users
group by name
order by count(*)

위와 같이 실행한다면 김 씨와, 정 씨와 , 황 씨가 먼저 표기 되며 그다음 장 씨, 이 씨 순으로 표기 된다.

뒤에 DESC 를 쓴다면 역순으로 표기된다.

where와 함께 써보기

SELECT payment_method,COUNT(*)  FROM users
WHERE age = 30
group by name
ORDER by COUNT(*) DESC

나이가 30인 사람중에 성씨 를 내림차순으로 조회

select 필드명 from + 테이블명  -> 테이블에서 해당 필드에 있는 값을 전부 찾아라. 

Where 절 : Select 쿼리문으로 가져올 데이터의 조건을 걸어준다

예)

// 문자열 찾기
WHERE [필드명] = '[찾으려는값]'

//대소 비교로 찾기
WHERE [필드명] > 5000

AND : 그리고

OR : 또는

BETWEEN 값 and 값  :  사이의 값 찾기 (숫자, 날짜)

IN (값) : 필드 안의 값과 일치하는 것만 보기 

LIKE '값'  :  패턴으로 값 찾기

LIMIT 값 : 조회 숫자 조정

DISTINCT(필드) : 해당 필드에 값들이 무엇이 있는지 검색(중복x)

COUNT(필드) : 해당 필드의 값이 몇개인지 찾기