mariaDB SQL 문법 연습

1 별칭사용하기
별칭을 사용하는 이유 : 긴 단어를 줄여서 사용하는데 좋음

sql에서 별칭을 사용할 수 있는 곳
컬럼명 대신 별칭 사용 : 컬럼 명 뒤에 as 별칭  예시 ) Select mem_id as id
테이블 명 대신 별칭 사용 :테이블 명 뒤에 바로 별칭을 작성 예시 ) from member mem

테이블 별칭을 언제 사용하느냐? 이름이 길때, join할때, 첫음절을 대문자로

<처리순서>
1select 
2from
3테이블 메모리에 올리기
4where조건
5and 조건 또는 or 조건
6조회할 칼럼
7정렬

like '문자' : 형식이 일치하는지  '_a%' 두번째 글자가 a인 경우, '%a%'a가 포함된경우
beetween A and B : A 이상 B 이하의 범위 조건
날짜 타입 또는 숫자 타입에 대한 범위 연산 조건에 사용됨

날짜 형식( 포멧형식)
0000-00-00 , 0000.00.00, 0000/00/00, 00000000


                
SELECT LOWER(mem_id) AS '소문자로' , UPPER (mem_id) AS '대문자로'
FROM member                ;

SELECT
  '왼쪽공백',
  LTRIM(' 왼쪽공백 제거 '),
  '오른쪽 공백         ',
  RTRIM('오른쪽공백제거  '),
  '   좌우공백   ',
  TRIM(' 좌우공백제거 ');
  
  -- 특정 자리 수까지의 문자 추출하기 
  -- mysql OR maria db용
  SELECT LEFT(prod_id,4) , RIGHT (prod_id,6)
  FROM prod;
  

문자열 내 특정 위치값 추출하기
select substring('java program', 7,3),
substring('java program', 1,3)

sql 표준 : in , like, between
회사에서 만든 것 : concat , substring

in 함수를 이용해서 서브쿼리를 사용할 경우 규칙 : 다하나의 컬럼에 여러개의 행을 조회 할 수 있음.
단일 컬럼에 다중행 
비교연산자를 이용하는 서브쿼리의 특징: 하나의 컬럼에 하나의 값만 조회 가능해야 한다.
단일 컬럼에 단일 행이라고 합니다.

 

 

<-- 수업예제 -->

SELECT *
FROM MEMBER;

SELECT * FROM member;

SELECT mem_name FROM member WHERE mem_id= 'a001';

SELECT * FROM lprod WHERE lprod_gu = 'P201';

UPDATE lprod SET lprod_nm = "향수" WHERE lprod_gu='P201';

DELETE FROM lprod WHERE lprod_gu='P201';

SELECT prod_id, prod_name FROM prod WHERE prod_sale>= 1000;

SELECT PROD_ID, prod_name FROM prod WHERE prod_price>=1000 AND prod_sale >=2000;

SELECT * FROM member;

SELECT mem_id AS id, 
		 mem_name AS name,
		 mem_mileage AS POINT,
		 SUM(mem_mileage) AS sum
		 FROM member
		 WHERE mem_mileage>=10
		 GROUP BY mem_id,NAME, point with ROLLUP;
		 
SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE mem_mileage >=10
-- AND mem_name = "김은대"
ORDER BY mem_id DESC;

SELECT mem_id as id, mem_name AS name, mem_mileage AS point
FROM member
WHERE mem_mileage >=10
ORDER BY point DESC;

SELECT mem_id, mem_name, mem_regno1
FROM member WHERE mem_regno1 >= 760101;


SELECT prod_id,
		prod_name,
		prod_lgu,
		prod_sale 
FROM prod
WHERE prod_lgu="P201" AND prod_sale=170000
ORDER BY prod_name;


SELECT prod_name, prod_sale
FROM prod
WHERE prod_sale=150000 OR
		prod_sale=170000 OR 
		prod_sale=330000
;
SELECT prod_name, prod_sale
FROM prod
WHERE prod_sale in(150000,170000,330000) ;

SELECT prod_name, prod_id
FROM prod 
WHERE prod_name LIKE '_성%';


SELECT mem_id, mem_name, mem_add1, mem_mileage
FROM member
WHERE mem_add1 LIKE '서울%' AND
mem_mileage >=1000;

SELECT mem_name, mem_bir
FROM member memeber
WHERE mem_bir LIKE '1975%';

SELECT mem_name, mem_bir
FROM member memeber
-- WHERE mem_bir BETWEEN '1975-01-01' AND '1975-12-31'
-- WHERE mem_bir BETWEEN '1975-01-01' AND '19751231'
-- WHERE mem_bir BETWEEN '1975-01-01' AND '1975/12/31'
-- WHERE mem_bir BETWEEN '1975-01-01' AND '1975.12.31'
WHERE mem_bir BETWEEN '19750101' AND '19751231';

SELECT mem_name, mem_mileage
FROM member 
WHERE mem_mileage BETWEEN 10 AND 10000 AND 
mem_name LIKE '김%';

SELECT mem_name, mem_bir
FROM member 
WHERE mem_bir NOT BETWEEN '1975-01-01' AND '1975-12-31';

SELECT mem_name ,CONCAT(mem_id, '-', MEM_REGNO2)AS MEM_REGNO,
                CONCAT(mem_add1,' ',mem_add2) AS MEM_ADD
                FROM member;
                
SELECT LOWER(mem_id) AS '소문자로' , UPPER (mem_id) AS '대문자로'
FROM member                ;

SELECT
  '왼쪽공백',
  LTRIM('		왼쪽공백 제거 '),
  '오른쪽 공백         ',
  RTRIM('오른쪽공백제거 		'),
  '   좌우공백   ',
  TRIM('	좌우공백제거	');
  
  -- 특정 자리 수까지의 문자 추출하기 
  -- mysql OR maria db용
  SELECT LEFT(prod_id,4) , RIGHT (prod_id,6)
  FROM prod;
  
  
  
-- p101 상품분류에 대해 새로운 상품을 등록하려고 합니다
-- p101000006이 현재 마지막 번호입니다
-- 마지막 번호에 1 증가 시키고자 합니다

SELECT LEFT(max(prod_id),8)      
FROM prod 
WHERE prod_id LIKE 'P101%'
 ORDER BY prod_id DESC ;
 
SELECT *
FROM member ;
 
  
  
SELECT prod_id, prod_name
FROM prod
WHERE SUBSTRING(prod_name,4,2)='칼라';

SELECT *
FROM member
WHERE mem_name LIKE '김%' AND
	SUBSTRING(mem_add1,1,2) IN ('서울', '대전') AND 
	mem_memorial LIKE '%결혼%';
	
SELECT mem_id, mem_mileage
FROM member 
WHERE mem_mileage >= (SELECT mem_mileage FROM member where mem_id='a001') ;
 

SELECT mem_id, mem_name
FROM member 
WHERE mem_id NOT IN(SELECT cart_member FROM cart);

SELECT mem_id, mem_name
FROM member 
LEFT OUTER JOIN cart ON member.mem_id =cart.cart_member
WHERE cart.cart_member IS NULL ;


SELECT lprod_gu, lprod_nm
FROM lprod
WHERE lprod_gu NOT IN(SELECT prod_lgu FROM prod);

SELECT mem_name 
FROM member
WHERE mem_id IN (SELECT cart_member FROM cart);


SELECT cart_member,
 (SELECT mem_name FROM member WHERE cart_member=mem_id ) AS mem_name,
cart_no,
cart_prod,
cart_qty 
FROM cart;

SELECT * FROM lprod ;

SELECT prod_name,
prod_sale,
prod_lgu,
(SELECT lprod_nm FROM lprod WHERE prod_lgu = lprod_gu) AS lprod_nm
FROM prod  ;

SELECT *
FROM cart;

SELECT mem_id
FROM member
WHERE mem_add1 LIKE '서울%' OR mem_add1 LIKE '대전%';


SELECT 
cart_no,
cart_prod,
cart_qty,
(SELECT prod_name FROM prod WHERE prod_id= cart_prod) AS prod_name,
(SELECT lprod_nm FROM lprod WHERE lprod_gu=(
SELECT prod_lgu FROM prod WHERE prod_id= cart_prod)) AS lprod_gu
FROM cart
WHERE cart_member IN (SELECT mem_id FROM member WHERE mem_add1 LIKE '서울%' OR mem_add1 LIKE '대전%');


SELECT *
from prod;

SELECT prod_buyer
FROM prod ;


SELECT buyer_id
FROM buyer
WHERE buyer_add1 LIKE '%서울%' OR buyer_add1 LIKE '%대전%' OR buyer_add1 LIKE '%광주%';

SELECT *
FROM cart
WHERE cart_qty>=5 AND
   (SELECT lprod_nm FROM lprod WHERE lprod_gu= SUBSTRING(cart_prod,1,4)) LIKE '%컴퓨터%' 
--	and (select prod_buyer FROM prod WHERE prod_id = cart_prod)
	
;

SELECT prod_id , prod_name, prod_sale, (SELECT buyer_name FROM buyer WHERE buyer_id = prod_buyer) AS buyer_name
FROM prod
WHERE prod_id IN (SELECT cart_prod FROM cart
where cart_member IN (SELECT mem_id FROM member WHERE mem_mileage >=100) AND cart_qty>=5)
AND prod_lgu IN (SELECT lprod_gu FROM lprod WHERE lprod_nm LIKE '%컴퓨터%');
AND prod_buyer IN (SELECT buyer_id FROM buyer WHERE SUBSTRING(buyer_add1,1,2) IN ('서울', '대전','광주') );

SELECT buyer_id FROM buyer WHERE SUBSTRING(buyer_add1,1,2) IN ('서울', '대전','광주')
;