[MySQL] MySQL 문법

• show tables;
  → 모든 테이블의 종류 확인
  
  
• desc emp;
  → EMP테이블의 구조를 확인
  
  
• select * from emp;
  → 해당 테이블의 모든 필드를 출력
  → SELECT 필드이름 FROM 테이블이름
  
  
• select ENAME, 'a' as "aa" from emp;
  → 'a' as "aa" : 컬럼이름aa select ENAME, 'a' as aa from emp; select ENAME, 'a' as "aa" from emp;
  
  
• select distinct DEPTNO from emp;
  → distinct : 중복되는것을 없앰
  
  
• select empno, SAL, ENAME
  from emp
  where SAL < 1000;
  → sal이 1000보다 작은것 출력
  
  
• ORDER BY
  ORDER BY ENAME ;
  → (ASC)오름차순
  ORDER BY ENAME DESC;
  → (DESC)내림차순
  
  
• FORMAT(1000,0)
  → 1000단위 표시
  
  
• IF(A,B,C)
  → A 가 TRUE면 B 아니면 C
  IF(isnull(COMM), SAL0, SAL+COMM)
  → COMM이 NULL이면 SAL0 아니면 SAL+COMM
  
  
• IFNULL(COMM,0)
  → NULL값을 0으로 바꿈
  
  
• UNION 
  → 두 집합을 합친다 중복제거
  UNION ALL
  → 두 집합을 합치고 중복제거안함
  
  
• DATE_FORMAT
  DATE_FORMAT(BIRTHDAY,'%d')
  → BIRTHDAY에서 태어난 일
  WHERE date_format(BIRTHDAY, '%m') = 01;
  → 01월에 태어난 경우
  DATE_FORMAT(NOW(), '%Y-%m-%d-%H-%i-%s')
  → "YYYY-MM-DD-HH-mm-ss”
  
  
• WHERE
  WHERE SAL between 2000 and 3000; # 2000~3000
  WHERE ENAME >= 'S'; # 알파벳 순서대로 S보다 큰것
  WHERE DEPTNO IN(10,20); # 10, 20 있으면
  WHERE SAL >= 2000 and SAL <= 3000; # 2000 ~ 3000
  WHERE SAL LIKE '1%'; # 1로 시작하는 숫자
  WHERE COMM IS NULL; # NULL 값 있는것
  
  
• CASE
  CASE WHEN 1 THEN 'C' # 1가 1이면 'C'
             WHEN 2 THEN 'B' # 1이 2면 'B'
  ELSE 'ETC' # 아니면 ETC (생략가능)
  END "TEST" → 컬럼명
• GROUP BY DEPTNO
  → DEPTNO를 기준으로 그룹지어서
  → GROUP BY에 조건을 걸땐 WHERE 대신 HAVING 사용
• 문자 관련 함수
  LOWER # 소문자 EX) LOWER('ABCD') -> abcd
  UPPER # 대문자 EX) UPPER('abcd') -> ABCD
  LENGTH # 길이 EX) LENGTH('ab') -> 2
  CONCAT # 합치기 EX) CONCAT('A','B') -> AB
  SUBSTR #글자 자르기 EX) SUBSTR('ABC',1,2) -> AB
  INSTR # 찾기 EX) INSTR('AB#','#') -> 4
  LPAD # 글자 왼쪽 채우기 EX) LPAD('love',6,'') -> LOVE
  RPAD # 글자 오른쪽 채우기 EX) LPAD('love',6,'*') -> LOVE
  REPLACE # 치환 EX) REPLACE('AB','A','E') -> EB
  
  
• 숫자 관련 함수
  ROUND # 반올림 EX) ROUND(12.345,2) -> 12.35
  MOD # 나머지 EX) MOD(12,10) -> 2
  CEIL # 정수올림 EX) CEIL(12.345) -> 12
  FLOOR # 정수만 EX) FLOOR(12.345) -> 12
  POWER # 제곱 EX) POWER(3,2) -> 9
  
  
• GROUP 함수 COUNT()
  SUM()
  AVG()
  MAX()
  MIN()
  STDDEV() -- 표준편차
  VARIANCE() -- 분산
  
  
• WHERE REGEXP_LIEK(A,B)
  WHERE REGEXP_LIKE(TEXT,'[a-z]'); -- 9 -- 알파벳 포함
  WHERE REGEXP_LIKE(TEXT,'[a-z] [0-9]'); -- 공백 포함
  WHERE REGEXP_LIKE(TEXT,'[a-z]{1}'); -- 9 -- 알파벳이 1개이상
  WHERE REGEXP_LIKE(TEXT,'[a-z]{4}'); -- 1 -- 알파벳이 4개이상 포함
  WHERE REGEXP_LIKE(TEXT,'[a-z][0-9]{1}'); -- 3
  WHERE REGEXP_LIKE(TEXT,'[a-z]$'); -- 9 -- 알파벳으로 끝나는것
  WHERE REGEXP_LIKE(TEXT,'^[0-9a-z]'); -- 10 -- 숫자나 알파벳으로 시작 (OR)
  WHERE REGEXP_LIKE(TEXT,'[^a-z]'); -- 11 -- 알파벳으로만 이루어진것 빠짐
  WHERE REGEXP_LIKE(TEXT,'^[^a-z]'); -- 4 -- 알파벳으로 시작하지 않음
  WHERE REGEXP_LIKE(TEXT,'^[^0-9]'); -- 10 -- 숫자로 시작하지않음
  WHERE REGEXP_LIKE(TEXT,'^[^0-9a-z]'); -- 2 -- 숫자나 알파벳으로 시작하지 않음
  WHERE not REGEXP_LIKE(TEXT,'[a-z]'); -- 3 -- 알파벳 있는것 빼고 출력
  
  
• REGEXP_REPLACE(A,B,C)
  REGEXP_REPLACE(TEXT, '[0-9]','[*]') --숫자가 로 바뀜
  REGEXP_REPLACE(TEXT, '[[:digit:]]','[]') --숫자가 로 바뀜
  REGEXP_REPLACE('aaa bb c d','( ){1}','') as "black2" -- 공백을 없앰(공백 1개짜리를 없앰) REGEXP_REPLACE('aaa bb c d','( ){2}','') as "black4" -- 2칸공백이 없어짐
  REGEXP_REPLACE('aaa bb c d','( ){2,}','') as "black6" -- 2칸이상의 공백이 없어짐
  REGEXP_REPLACE('aaa bbb','( ){2,}','') as "2" -- 2칸이상의 공백이 *로 바뀜
  
  
• LAG(), LEAD() OVER
  LAG(SAL,1) OVER(ORDER BY HIREDATE) AS "LAG" --밑으로 한칸씩 밀림 LAG(SAL)과 같음
  LAG(SAL,1,0) OVER(ORDER BY HIREDATE) AS "LAG" --NULL있으면 0
  LEAD(SAL,1) OVER(ORDER BY HIREDATE) AS "LEAD" --위로 한칸씩 밀림
  
  
• RANK() OVER
  RANK() OVER(ORDER BY SAL) AS "RANK_ASC" -- 랭크매김
  RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC ) AS "RANK" -- 부서별로 랭킹을 따로매김 DENSE_RANK() OVER(ORDER BY SAL) -- 중복이 있어도 바로 다음번호로 랭킹을 매김
  ROW_NUMBER() OVER(ORDER BY SAL) -- 중복이 있어도 순위를 순서대로 매김
  
  
• SUM() OVER
  SUM(P_TOTAL) OVER (ORDER BY P_TOTAL) -- P_TOTAL에 대한 누적합계
  SUM(P_TOTAL) OVER (PARTITION BY P_CODE ORDER BY P_TOTAL) -- 코드별 P_TOTAL에 대한 누적합계
  
  
• 주요 데이터 형식
  CHAR(n), VARCHAR(n), INT, BIGINT, FLOAT, DOUBLE, DECIMAL(m,d) 전체자릿수(m), 소수점 이하 자릿수(d), DATE, DATETIME, TEXT 대용량 글자, BLOB 사진,동영상,문서 파일
  
  
• 비교 연산자 ANY
  > ANY : 최소값보다 크면
  >= ANY : 최소값보다 크거나 같으면
  < ANY : 최대값보다 작으면
  <= ANY : 최대값보다 작거나 같으면
  = ANY : IN과 같은 기능
  = ANY : NOT IN과 같은 기능
  
  
• 비교 연산자 ALL
  > ALL : 최대값보다 크면
  >= ALL : 최대값보다 크거나 같으면
  < ALL : 최소값보다 작으면
  <= ALL : 최소값보다 작거나 같으면
  = ALL : 서브 쿼리의 결과가 1건이면 괜찮지만 여러 건이면 오류가 발생
  != ALL : 서브 쿼리의 결과가 1건이면 괜찮지만 여러 건이면 오류가 발생
• 테이블 만들기
  CREATE table NEW_TABLE
  (NO INT -- 컬럼명 NO에 데이터타입은 INT
  ,NAME VARCHAR(10)
  ,BIRTH DATE
  );
  
  
• 테이블복사(CREATE ㅁ AS ㅁ)
  CREATE TABLE DEPT3
  AS
  select * from dept2;
  → 모든 컬럼 복사
  
  CREATE TABLE DEPT4
  AS
  SELECT DCODE, DNAME → 특정 컬럼만 복사
  FROM DEPT2
  WHERE DCODE IN(1000,1001,1002);
  → 조건 설정가능
  
  
• 테이블 변경(ALTER)
  ALTER TABLE DEPT6 -- TABLE을 변경하겠다(ALTER)
  ADD(LOCATION VARCHAR(10)); → 컬럼 추가
  ADD(LOCATION2 VARCHAR(10) DEFAULT 'SEOUL'); → 디폴트값
  RENAME COLUMN LOCATION2 TO LOC; → 컬럼명 변경
  RENAME TABLE DEPT6 TO DEPT7; → 테이블 이름 변경
  MODIFY COLUMN LOC VARCHAR(20) → 컬럼 타입변경
  DROP TABLE DEPT7; → 특정테이블 삭제
  DROP COLUMN LOC; → 특정컬럼 삭제
  
  
• INSERT INTO DEPT2(DCODE,DNAME)VALUES(9000,'TEMP1');
  → 데이터 추가
  INSERT INTO DEPT2 VALUES(9003,'TEMP4',1006, 'TEMP AREA2');
  → 컬럼명 지워도 순서대로 입력됨(단, 컬럼갯수대로 다 적어야됨)
  
  
• DECIMAL(3, 2) → 총 3자리에 소수점 2자리 -9.99~9.99
  
  
• UPDATE DEPT3 → 데이터 변경하기
  SET AREA = '7777' → SET 변경할 컬럼
  WHERE DCODE = '1000'; → 조건 걸어서 특정부분만
  
  
• DELETE FROM DEPT2 → 데이터 삭제
  WHERE DCODE >= 9000 AND DCODE <= 9999;
  
  
• 제약 조건의 종류
  1. NOT NULL
  2. UNIQUE →유일한것 (NULL가능)
  3. PRIMARY KEY → NULL 불가능 AND 유일한것(1+2)
  4. FOREGN KEY → 다른테이블에 있는것
  
  
• *.JOIN
  테이블간에 연결
  둘중 하나를 Primary Key로 연결 하면 중복 없애기 편함
  
  
• 카티션 곱(Cartesian Product)
  SELECT * FROM EMP A, DEPT B
  ORDER BY A.DEPTNO, EMPNO;
  
  등가조인(EQUI JOIN) (=)
  SELECT *
  FROM EMP A, DEPT B
  WHERE A.DEPTNO = B.DEPTNO;
  
  등가조인(EQUI JOIN), ANSI JOIN(DB 특성 안탐)
  (INNER JOIN)
  SELECT *
  FROM EMP A JOIN DEPT B
  ON A.DEPTNO = B.DEPTNO;
  
  비등가 조인(Non-EQUI JOIN) (범위)
  SELECT CUST.GNAME AS "CUST_NAME"
               , FORMAT(CUST.POINT,0) AS "POINT"
               , GIFT.GNAME AS "GIFT_NAME"
     FROM CUSTOMER AS CUST , GIFT
   WHERE CUST.POINT BETWEEN GIFT.G_START AND GIFT.G_END
  ORDER BY CUST.POINT;
  
  LFET OUTER
  SELECT S.NAME
  , P.NAME
  FROM student S LEFT OUTER JOIN professor P
  ON S.profno = P.profno;