sub query, index, view, 내장함수

date_format(날짜, 형식)
- 날짜를 지정한 형식으로 출력

<미션해석>
ex.1)5월에 주문된 제품들의 제품코드, 제품명을 조회(orders)
select * from orders o where month(orderdate)=05;
select * from products p;
select * from orderdetails od;

select p.productCode, p.productName -> 내가 확인하고 싶은 정보
from products p join orderdetails od -> products와 orderdetails을 조인
on p.productCode = od.productCode  -> 공통점 productCode
join orders o 
on o.orderNumber = od.orderNumber 
where month(orderdate)= '05';
- 5월의 정보가 'orders'에 있고(orderNumber)
  내가 보고 싶은 정보는 'products'에 있다

- 내가 알고 있는 정보는 
  'orders'의 정보 = orderNumber, orderDate, status 등

- 내가 알고 싶은 정보
  'products' = productCode, productName 등

- 'orderdetail' = orderNumber, orderCode, priceEach 등

-----------------------------
ex.2)상태(status)값이 'Shpped'인 것 중 주문일자가(orderDate)쿼리를 조회하는 날짜에
   발생한 주문정보를 조회(orders사용)
select * from orders 
where status = 'Shipped'
and dayofmonth(orderdate)=dayofmonth(now()); -1
/*and date_format(orderdate, '%d') = date_format(now(),'%d');*/ -2
- 1, 2은 동일

-----------------------------
ex.3)년도별 주문건수를 조회하시오(orders사용)
select date_format(orderDate, '%Y-%m')y, count(orderNumber)
from orders
group by y;

-----------------------------
ex.4)orderdetails에서 주문금액(quantityOrdered*priceEach)에 부가세 10%를 부가하여 
   표시하시오(단, 세후금액은 소수점 이하는 절삭)
select od.*, (quantityOrdered*priceEach) amt,
             (quantityOrdered*priceEach)*.1 tax,
             (quantityOrdered*priceEach*1.1) "before", - 소수점 이하 절삭 전
             floor(quantityOrdered*priceEach*1.1) "after" - 소수점 이하 절삭 후
from orderdetails od;
--------------------------------------개인정리--------------------------------------
<sub query>
- ()안에 기술된다
- 독립적인 실행이 되어야 한다
- select절, where절, from절에서 사용가능


<where절에서 사용하기>
- where amt = (select max(c_name)from t_name) -> subquery결과가 1건
- where amt > (select max(c_name)from t_name) -> subquery결과가 1건
- where amt in (select distinct c_name from t_name) -> subquery결과가 n건
- where amt not in (select distinct c_name from t_name) -> subquery결과가 n건
- where exists (sub query) -> subquery결과가 n건
- where not exists (sub query) -> subquery결과가 n건
- where amt > any(sub query) -> subquery결과가 n건
- where amt > all (sub query) -> subquery결과가 n건


*ANY(or)
k>any(100, 200,,300) 
-> 100보다 크거나 200보다 크거나 300보다 큰 값
즉, 100보다 크면 되므로 최솟값(100)을 반환한다


k<any(100, 200, 300)
-> 100보다 작거나 200보다 작거나 300보다 작은 값
즉, 300보다 작으면 되므로 최댓값(300)을 반환한다

*ALL(and)
k>all(100, 200, 300)
-> 100보다도 크고 200보다도 크고 300보다도 큰 값
즉, 300보다 커야하므로 최댓값(300)을 반환한다

k<all(100, 200, 300) 
-> 100보다도 작아야하고 200보다도 작아야하고 300보다도 작은 값
즉, 100보다 작아야하므로 최솟값(100)을 반환한다

--------------------------------------
ex)생산코드가 S10_1678인 제품의 생산라인의 상세 설명을 조회

[join 사용]
SELECT textDescription
FROM products p JOIN productlines pl
ON p.productline = pl.productline
WHERE productCode = 'S10_1678';


[sub query 사용]
SELECT textDescription
FROM productlines
WHERE productline = (SELECT productline FROM products
WHERE productCode = 'S10_1678');
- sub query안에 있는 내용은 1건만 출력된다

--------------------------------------
[ex.1]
select * from products where productCode = 'S10_1678';

[ex.2]
select * from products productlines where productLine = 'Motorcycles';

[1+2->join on절]
select p2.* from products p join productlines p2 
on p.productLine = p2.productLine 
where productCode = 'S10_1678';

[1+2->sub query절]
select * from productlines
where productLine = (select productLine from products where productCode = 'S10_1678');

--------------------------------------
<from절에서 사용하기>
- sub query결과를 하나의 가상 테이블로 사용한다
- ...FROM (SELECT...) As alias_table_name

ex)코드별 구매단가의 합이 100이상인 자료 조회
SELECT a.* FROM(SELECT productCode, SUM(buyPrice) buy FROM products
GROUP BY productCode) a
WHERE a.buy>=100;
- a는 가상의 테이블
- SUM(buyPrice) buy -> 'buy'는 SUM(buyPrice)의 별칭


위의 코드를 having절로 표현
SELECT productCode, SUM(buyPrice) buy FROM products 
GROUP BY productCode HAVING buy>=100;

--------------------------------------
ex)employees에서 Bott(lastname)와 같은 직무를 갖고 있는 직원들의 사번, 이름, 이메일을 확인

[self join]
SELECT e2.employeeNumber, e2.lastName, e2.email, e2.jobTitle
from employees e1 join employees e2
on e1.jobTitle = e2.jobTitle
where e1.lastName = 'Bott';

[sub query]
SELECT employeeNumber, lastName, email, jobTitle
from employees
where jobTitle = (select jobTitle from employees where lastName='Bott')

--------------------------------------
ex)customers, payments사용하여, 고객번호(customerNumber)가 353인 
고객에게 지불될 amount보다 큰 고객들의
고객번호, 고객이름, 지불금액을 조회

[sub query & join on]
SELECT c.customerNumber, c.customerName, p.amount
FROM customers c join payments p
on c.customerNumber = p.customerNumber
where p.amount > all(SELECT amount from paymens where customerNumber=353)

--------------------------------------
INDEX(목록이 있을 때, '찾아보기' 기능)
- PK나 unique 제약조건을 설정하면 자동으로 index가 만들어진다

*index로 설정된 데이터는 전체 데이터의 5%내외 일 때 좋은 효율을 보인다고 알려져있다
*DML유형의 작업이 많은 경우 index의 rebuild로 인한 서버의 과부를 초래



<INDEX의 종류>
1. NON UNIQUE INDEX : 기본 인덱스
2. UNIQUE INDEX : 중복값이 없는 값을 갖는 인덱스
3. FULLTEXT INDEX : 컬럼내의 모든 텍스트 필드를 검색



<효율적인 INDEX>
- WHERE, ORDER BY절에 자주 사용되는 컬럼
- JOIN이 자주 사용되는 컬럼



<생성>
CREATE INDEX 인덱스명 ON 테이블명(컬럼들 [ASC | DESC])
CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼들 [ASC | DESC])
CREATE FULLTEXT INDEX 인덱스명 ON 테이블명(컬럼들)
- 컬럼들 항목에서 2개 이상의 컬럼을 지정한 경우 복합 INDEX로 처리된다


ALTER TABLE 테이블명 ADD INDEX 인덱스명(필드명)
ALTER TABLE 테이블명 ADD UNIQUE 인덱스명(필드명)
ALTER TABLE 테이블명 ADD FULLTEXT 인덱스명(필드명)
- 테이블에 INDEX를 추가할수도 있다


<INDEX지정사용>
SELECT 컬럼명들 FROM 테이블명
USE INDEX 인덱스명
WHERE ...



<INDEX확인>
- SHOW INDEX FROM 테이블명



<VIEW> 
- 가상테이블이며 보안이나 복잡한 쿼리를 단순하게 사용할 수 있도록 설정



<VIEW특징>
- 한 번 지정된 VIEW는 변경되지 않는다
- 삽입 및 수정, 삭제에 많은 제한 사항이 있다
- 자신만의 index를 가질 수 없다



<생성 및 삭제>
생성 : CREATE [OR REPLACE] VIEW 뷰명 AS SELECT ...
삭제 : DROP VIEW 뷰명


ex)복잡한 쿼리를 단순하게 만드는 VIEW
create view my_view as 
select c.customerNumber, c.customerName, p.amount
from customers c join payments p 
on c. customerNumber = p.customerNumber 
where p.amount > all(select amount from payments where customerNumber = 353)

select* from my_view where amount>=100000


ex)보안을 위한 VIEW
create view my_cus as
select customerName, city from customers;

select* from my_cus;





<내장함수>

<숫자 관련 함수>
- abs(n) : n의 절대값 출력
- ceiling(n) or ceil(n) : n의 절상값
- floor(n) : n의 절삭값
- round(n, p) : p가 양수이면 소숫점 이하, 음수이면 소숫점 이상에서 반올림
- truncate(n, p) : p의 자리에서 버림
- pow(x, y) or power(x, y) : x의 y승
- mod(x, y) : x를 y로 나눈 나머지
- greatest(n1, n2, n3...) : 가장 큰 수
- least(n1, n2, n3...) : 가장 작은 수




<문자 관련 함수>
 - ascII(str) : str의 아스키 코드값
- concat(str1, str2, str3, …) : 문자열 연결
- insert(str, star t, length, newStr) : str문자열에서 star t에서 length길이 만큼의 문자열을 new Str로 
  바꾼다
 ex)select insert('abcdef', 2, 1, '1234567'); → a1234567cdef 

- replace(str, oldStr, newStr) : str에서 oldStr의 문자열을 newStr로 수정
ex)select replace('abcdef', 'bc', '123'); → a123def

- instr(str, fi ndStr) : str에서 fi ndStr을 찾아 위치 반환
ex)select instr('abcdef', 'cd'); → 3

- left (str, length) str에서 왼쪽부터 length 만큼 추출
ex) select left('abcdef', 3); → abc

- right(str, length) : str에서 오른쪽부터 length 만큼 추출
ex)select right('abcdef', 3); → def

- mid(str, start, length) or substring(str, start, length) : str에서 start위치에서 length 만큼 추출
ex)select mid('abcdef', 2,3), substring('abcdef', 2,3); → def def

- ltrim(str), rtrim(str), trim(str) : 왼쪽, 오른쪽 양쪽 공백 제거
- lcase(str) or lower(str) : 모두 소문자로
- ucase(str) or upper(str) : 모두 대문자로
- reverse(str) : str을 반대로 나열
- format(숫자, 소수점 자리수) : 천단위 소숫점 표시
ex)select format(1234567.12345,3); → 1,234,567.123


[미션]
1. 5월에 주문된 제품들의 제품코드, 제품명을 조회(orders)
2. 상태(status)값이 'Shpped'인 것 중 주문일자가(orderDate)쿼리를 조회하는 날짜에
   발생한 주문정보를 조회(orders사용)
3. 년도별 주문건수를 조회하시오(orders사용)
4. orderdetails에서 주문금액(quantityOrdered*priceEach)에 부가세 10%를 부가하여 
   표시하시오(단, 세후금액은 소수점 이하는 절삭)


<논리 함수>
- if(논리식, 참일때, 거짓일때)
ex)select if(10<4, 't', 'f'); -> f

- ifnull(v1, v2) : v1이 null이면 v2를 반환 아니면, v1을 반환
ex)select ifnull(null, '1'); -> 1
    select ifnull('a', '1'); -> a




<집계 함수>
- count(fn) : null값이 아닌 row갯수
- sum(fn) : 합계
- avg(fn) : 평균
- max(fn) : 최댓값
- min(fn) : 최솟값




<날짜 함수>
- now() or sysdate() or current_timestamp() : 년월일 시분초
- curdate() or current_date() or date(날짜) : 년월일
- curtime() or current_time() or time(날짜) : 시분초
- date_add(날짜, interval 기준값) : 날짜를 더한다
- date_sub(날짜, interval 기준값) : 날짜를 뺀다
- year(날짜), month(날짜), dayofmonth(날짜)
ex)select year(now(), month(now()), dayofmonth(now()); -> 2021 1 1

- monthname(날짜), dayname(날짜) 
- dayofweek(날짜), weekday(날짜) : weekday -> 0 : 월요일, dayofweek -> 1 : 일요일
- dayofyear(날짜) : 일년 중 오늘이 며칠째날
- week(날짜) : 몇번 째 주
- date_format(날짜, 형식)




<date functions>
- now() : 쿼리 블럭에서 최초로 시작되는 시간
- sysdate() : 쿼리가 시작되는 시점의 시간




<date_add(start_date, interval expr unit)
- unit : hour, day, month, minute_second, day_hour, second_microsecond, hour_minute
ex)select date_add(now(), interval 100day)




<date_format(date, format)>
- format문자열 : %y, %m, %d, (%h, %H), %i, %s, %W
ex)select date_format(now(), '%y-%m-%d %h : %i : %s (%W)');