SQL 쿼리 작성순서, 실행순서와 함수정리

SQL 쿼리 작성 순서

1) SELECT

2) FROM

3) JOIN

4) ON

5) WHERE

6) GROUP BY

7) HAVING

8) ORDER BY

9) LIMIT

Join을 하는 경우 WHERE은 JOIN ~ ON 뒤에 사용되니 주의하기.

SQL 쿼리 실행 순서

1) FROM and JOIN

2) WHERE

3) GROUP BY

4) HAVING

5) SELECT

6) DISTINCT

7) ORDER BY

8) LIMIT / OFFSET

인덱스

• 찾아보기개념, 결과가 나오는 시간을 대폭 줄여준다.

CREATE INDEX idx_member_name ON member(member_name);

뷰

• 테이블과 거의 동일한 성격의 데이터베이스 개체
• 보안강화,

create view member_view
AS 
select * from member;

select*from member_view;

스토어드 프로시저

DELIMITER //
CREATE PROCEDURE myProc()
begin 	
	select * from member where member_name = '나훈아';
    select * from product where product_name = '삼각김밥';
end //
delimiter ;

call myProc();

select phone 
from customer 
where name ="김연아";

#DISTINCT
#중복제거 
select distinct publisher
from book;

#BETWEEN AND
#가격이 10000원이상 20000원이하 도서 검색
select *
from book
where price between 10000 and 20000;

#IN
#출판사 굿스포츠, 대한미디어인 도서 가져오기 

select*
from book 
where publisher In('굿스포츠','대한미디어');

#NOT IN 
#출판사 굿스포츠, 대한미디어가 아닌  도서 가져오기 

select*
from book 
where publisher not In('굿스포츠','대한미디어');

#LIKE
#도서이름에 축구가 포함된 출판사 

select bookname, publisher 
from book 
where bookname like '%축구%';

# % : 0개 이상의 문자
# _ : 1개의 문자

#ORDER BY
#정렬

select * 
from book 
order by bookname; 

#도서를 가격순으로 검색, 가격이 같으면 이름순 검색 
#asc 오름차순, desc 내림차순

select * 
from book 
order by price, bookname asc;
-- asc 생략가능 

#DESC
#도서를 가격 내림차순으로 정렬  

select *
from book 
order by price desc, publisher asc;

<aside> 💡 숫자형함수

Round (숫자, 남길 소수 자리수)

Ex) round(38.52353, 3)  => 38.524    Ex) round(38.52353, 1)  => 38.5

Ex) round(38.52353)  => 39

Truncate(숫자, ~이하로 버릴 자리) 반올림을 안하고 그냥 버림.

Ex) Truncate(38.5235, 3) =>  38.523

Ex) Truncate(38.5235) =>  38

Ex) Truncate(38.5235, -1)  =>  30

</aside>

집계함수

집계 함수 Count, max, min, sum, avg

특징 : Null 값은 제외하고 계산함. Where 절에서 사용할 수 없고, select 이나 having 에서만 사용

#SUM()
# book 테이블에 있는 도서의 총 금액  
# AS = 열에 이름 부여하는 별칭
select sum(price) as "합계"
from book;

#MIN 최소값, MAX 최대값, AVG 평균값
#price 가격기준으로 최대가격, 최소가격 출력 

#ROUND(값, 자리수) 괄호안에 실수 넣으면 소수점 반올림해서 표현

select min(price) as "최소값",
	   max(price) as "최대값",
	   round(avg(price),2) as "avg" -- 14450.00
       
from book;

#count
Count (*) => 전체 행 개수
Count(특정컬럼명) => 특정 컬럼을 기준으로 행을 세는데, 이때 특정 로우가 null 이면 그 행은 counting 되지 않는다.
즉 count(*) != count(나이컬럼)	(나이 컬럼이 널값을 가질 수 있다면..)
따라서 보통 기본키 속성이나 *로 count 함수를 사용.

날짜, 시간

-- 날짜, 시간 정보 저장하기 

# NOW() -> 현재 날짜, 시간 반환
#yyyy-mm-dd-hh:mm:ss 형태
select now() as 시간 ;

# curdate()날짜만 반환  
# yyyy-mm-dd
select curdate();

# curtime() 현재 시간만 반환 
select curtime();

#DATE()  전달받은 값에 해당하는 날짜정보 반환

#MONTH() 전달받은 값에서 '월' 값만 반환 0~12 값을 가짐 

#DAY() 전달받은 값에서 '일'값만 반환 0~31 값을 가짐 

#HOUR() 전달받은 값에서 '시간'값만 반환 0~23 사이 값을 가짐 

#minute() '분' 0~59

#second() '초' 0~59

select Date('2016-02-19 12:34:56'),
month('2016-02-19 12:34:56'),
DAY('2016-02-19 12:34:56'),
HOUR('2016-02-19 12:34:56'),
minute('2016-02-19 12:34:56'),
second('2016-02-19 12:34:56'),
monthname('2016-02-19 12:34:56'), 
dayname('2016-02-19 12:34:56'); -- 요일 

-- 연도별 , 월별, 주별 일수 

#dayofweek 해당 주에서 몇번째날인지 반환 

#일요일 = 1 토요일 = 7
select dayofweek('2021-05-23'),

# 해당월에서 몇번째 날인지 반환 0~31
dayofmonth('2021-05-23'),

#해당 연도에서 몇번째 날인지 반환 1~366
dayofyear('2021-05-23');

#DATEDIFF 두 개의 날짜 사이의 일수 차이를 계산하는 데 사용
datediff(end_date, start_date)

문자열함수

- 문자열 함수
select length('1234567');

- 문자열 결합
전달 받은 문자열을 모두 결합하여 하나의 문자열로 반환한다.
    
    문자열 중간에 null 이 하나라도 존재하면 결과 null 반환
    
    select Concat(A, B , C) => ABC  문자열 연결
    select concat('My',null,'No');
    

- 문자열 반복
select repeat("이것이",3);

- 문자열 뒤집기
select reverse("mysql");

- 문자열 검색
문자열이 특정 문자열에서 처음으로 나타나는 위치를 찾아서 해당위치 반환, 만약 값이 없으면 0반환
문자열 첫 인덱스 1부터 시작

- 중복되는 값이 있을 때 자리수를 지정
    
    select locate('abc', 'abcde');
    select locate('abc', 'abcdeadf', 4);
    
- 문자열 추출
LEFT RIGHT 함수는 전달받은 문자열의 왼쪽부터 개수만큼 문자반환
    
    select left("Mysql php html java",10);
    select right("Mysql php html java",10);
    
- 문자열 추출 (특정부분을 가져오기)
substring(문자열, 시작번호, 길이)

select substring('가나다라마바사',3,2);

- 문자열 구분자로 끊어서 가져오기
문자열에 음수도 집어넣으면 오른쪽부터 거꾸로 문자열 반환
select substring_index("[www.google.com](<http://ww.google.com/>)",'.',3);

- 대소문자 변경
select lower('ABCD'); --문자열 소문자로
select upper('ABBcccc'); --문자열 대문자로

- 문자열 대체
select replace('Mysql','My','Ms'); -- my를 ,ms 로 바꿔라

- 문자열 다듬기
특정 문자제거를 하거나 앞이나 뒤
기본설정 -> 양끝에 공백제거
select trim(' !!!!!mysql php html java!!!!! ');

- leading : 전달받은 문자 앞쪽제거
select trim(leading '!' from '!!!!!mysql php html java!!!!!');

- trailing : 전달받은 문자 뒤쪽 제거
select trim(trailing '!' from '!!!!!mysql php html java!!!!!');

- 데이터베이스끼리 관계성을 주기위해서 key 설정
기본키 or 대표키 (primary key)
ex)주민등록증 주민번호;
설정할 때 중복이 되면 안됨 (unique)
외래키 외부키 (foregin key) 테이블끼리 연결해서 사용하는내용

DATE_FORMAT(HIRE_YMD,'%Y-%m-%d') -- 2022-03-12

1) IFNULL(?, ?)

1. 컬럼이 NULL이면 0으로 치환하여 반환

SELECT IFNULL(컬럼명, 0) FROM TEST ;

2. 컬럼이 NULL이면 -- 으로 치환하여 반환

SELECT IFNULL(컬럼명, '--') FROM TEST ;

2) IF()

1.  컬럼이 NULL일 경우 1을, NULL이 아닐때는 2를 return한다.

IF(컬럼명 IS NULL, '1', '2') FROM 테이블명

3) NULLIF(?, ?)

1.  (전자== 후자) 의 결과가 false면 전자의 값을 return 하고,  true이면 NULL을 return 한다.

SELECT NULLIF(1, 1) ;

• -> null 을 리턴한다.

SELECT NULLIF(1, 2) ;

• -> 1을 리턴한다.