Sql 문법 빠르게 훑어보기

SELECT * FROM 테이블명;

DESC 테이블명;

SELECT 열1, 열2 FROM 테이블명 WHERE 조건식;

값이 서로 다른 경우, '<>' ( WHERE no <>2;)

NULL 값을 검색할 경우, IS NULL (WHERE birthday IS NULL;)

=, <>, >, >=, <, <=

조건식1 AND 조건식2 (select * from sample24 where a<>0 and b<>0;
조건식1 OR 조건식2 (select * from sample24 where a<>0 or b<>0;
NOT 조건식 (select * from sample24 where not(a<>0 or b<>0);

AND는 OR에 비해 우선 순위가 높다.

패턴 매칭 : 열 LIKE 패턴

메타 문자 : % 문자열 _ 문자

select * from sample25 WHERE text LIKE '%SQL%'; 전방 일치, 중간 일치, 후방 일치

select * from sample25 WHERE text LIKE '%\%%'; \ 이스케이프 문자

문자열 상수 ' 의 이스케이프 'It"s' 또는 ''''

SELCT 열명 FROM 테이블명 ORDER BY 열명 DESC;

SELCT 열명 FROM 테이블명 ORDER BY 열명 ASC;

SELECT * FROM sample32 ORDER BY a ASC, b DESC;

MySQL의 경우 NULL 값을 가장 작은 값으로 취급해,
ASC(오름차순)에서는 가장 먼저, DESC(내림차순)에서는 가장 나중에 표시한다.

SELECT 열명 FROM 테이블명 LIMIT 행수 OFFSET 위치;

SELECT * FROM sample33 LIMIT 3 OFFSET 0;

SELECT * FROM sample33 WHERE ROWNUM <=3;

SELECT *, price*quantity AS amount FROM sample 34; 수치 연산 후 별명 붙이기

SELECT price*quantity "금액" FROM sample 34; AS 생략 가능, "한글"

더블 쿼트 "" -> 데이터베이스 객체 이름
싱글 쿼트 '' -> 문자열 상수

SELECT *, price*quantity AS amount FROM sample34 WHERE amount >= 2000;
WHERE -> SELECT 순으로 처리, SELECT 구에서 지정한 별명은 WHERE 구 안에서 사용할 수 없다.

SELECT *, price*quantity AS amount FROM sample34 ORDER BY amount DESC;
ORDER BY는 SELECT 보다 나중에 처리되기 때문에 별명을 사용할 수 있다.

SELECT amount, ROUND(amount,1) FROM sample341; 소수점 둘째 자리를 반올림

SELECT amount, ROUND(amount,-2) FROM sample341; 10 단위를 반올림

SELECT CONCAT(quantity, unit) FROM sample35; 문자열 결합

SUBSTRING('20140125001',1,4) 앞 4자리 추출

TRIM('ABC     ') 스페이스 제거

CHARACTER_LENGTH() 문자열 길이 반환

SELECT CURRENT_TIMESTAMP;

TO_DATE('2014/01/25', 'YYYY/MM/DD')

SELECT CURRENT_DATE + INTERVAL 1 DAY;

DATEDIFF('2014-02-28', '2014-01-01')

CASE WHEN 조건식1 THEN 식1
[WHEN 조건식2 THEN 식2...]
[ELSE 식3]
END

SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "a(null=0)" FROM sample37;

SELECT a, COALESCE(a, 0) FROM sample37;
a가 NULL이 아니면 NULL을 출력, 그렇지 않으면 0을 출력.

CASE 식1
WHEN 식2 THEN 식3
[WHEN 식4 THEN 식5 ...]
[ELSE 식6]
END

[검색 CASE]
SELECT a AS "코드",
CASE
  WHEN a = 1 THEN '남자'
  WHEN a = 2 THEN '여자'
  ELSE '미지정'
END AS "성별" FROM sample 37;

[단순 CASE]
SELECT a AS "코드",
CASE a
  WHEN 1 THEN '남자'
  WHEN 2 THEN '여자'
  ELSE '미지정'
END AS "성별" FROM sample37;

CASE 사용시, ELSE를 생략하면 ELSE NULL이 되는 것을 주의해야함.

CASE
  WHEN a = 1 THEN '남자'
  WHEN a = 2 THEN '여자'
  WHEN a IS NULL THEN '데이터 없음'
  ELSE '미지정'
END

INSERT INTO 테이블명 VALUES(값 1, 값2, ...)

INSERT INTO sample41 VALUES(1, 'ABC', '2014-01-25');

INSERT INTO sample41 (a, no) VALUES ('XYZ', 2);

INSERT INTO sample41 (no, a, b) VALUES (3, NULL, NULL);

INSERT INTO sample41 (no, d) VALUES (2, DEFAULT);

DELETE FROM 테이블명 WHERE 조건식

DELETE FROM sample41 WHERE no=3; no 열이 3인 행 삭제하기

UPDATE 테이블명 SET 열1 = 값1, 열2 = 값2, ... WHERE 조건식

UPDATE sample41 SET b = '2014-09-07' WHERE no=2;

UPDATE 명령에서는 WHERE 조건에 일치하는 모든 행이 갱신된다.

UPDATE sample41 SET no=no +1; 모든 행의 no 값에 1씩 더함.

UPDATE sample41 SET a='xxx', b='2014-01-01' WHERE no=2; 복수열 갱신, 순서 주의.

UPDATE sample41 SET a = NULL;

SELECT COUNT(*) FROM sample51; 행 개수 계산

SELECT COUNT(*) FROM sample51 WHERE name = 'A';

SELECT DISTINCT name FROM sample51; DISTINCT로 중복값 제거

SELECT COUNT(ALL name), COUNT(DISTINCT name) FROM sample51;

SUM([ALL|DISTINCT] 집합)
AVG([ALL|DISTINCT] 집합)
MIN([ALL|DISTINCT] 집합)
MAX([ALL|DISTINCT] 집합)

SELECT SUM(quantity) FROM sample51;

SELECT AVG(quantity), SUM(quantity)/COUNT(quantity) FROM sample51;

SELECT AVG(CASE WHEN quantity IS NULL THEN 0 ELSE quantity END) AS avgnull0 FROM sample51;

SELECT MIN(quantity), MAX(quantity), MIN(name), MAX(name) From sample51;

SELECT name, COUNT(name), SUM(quantity) FROM sample51 GROUP BY name;

내부처리 순서
WHERE 구 - > GROUP BY 구 -> SELECT 구 -> ORDER BY 구

WHERE 구에서는 집계함수를 쓸 수 없다. -> HAVING

SELECT name, COUNT(name) FROM sample51 GROUP BY name HAVING COUNT(name) =1;

내부처리 순서
WHERE 구 - > GROUP BY 구 -> HAVING구 -> SELECT 구 -> ORDER BY 구

SELECT MIN(no), name, SUM(quantity) FROM sample51 GROUP BY name;
GROUP BY에서 지정한 열 이외의 열은 집계함수를 사용하지 않은 채 SELECT 구에 지정할 수 없다.

SELECT no, quantity FROM sample51 GROUP BY no, quantity;

SELECT name, COUNT(name), SUM(quantity) FROM sample51 GROUP BY name ORDER BY SUM(quantity) DESC;
GROUP BY 구로 그룹화한 경우에도 ORDER BY 구를 사용해 정렬할 수 있다.

DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54); - Mysql에서는 못 씀.
DELETE FROM sample54 WHERE a = (SELECT a FROM (SELECT MIN(a) AS a FROM sample54) AS x);

클라이언트 변수
Set @a = (SELECT MIN(a) FROM sample54);
DELETE FROM sample54 WHERE a=@a;

SELECT 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'고 한다!

SELECT
  (SELECT COUNT(*) FROM sample51) AS sq1,
  (SELECT COUNT(*) FROM sample54) AS sq2;

Mysql에서는 FROM 생략 가능

SELECT
  (SELECT COUNT(*) FROM sample51) AS sq1,
  (SELECT COUNT(*) FROM sample54) AS sq2 FROM DUAL;

Oracle 문법, DUAL은 시스템 쪽에서 데이터베이스에 기본적으로 작성되는 테이블.

UPDATE sample54 SET a = (SELECT MAX(a) FROM sample54);

SELECT * FROM (SELECT * FROM sample54) sq;

SELECT * FROM (SELECT * FROM sample54) AS sq;

SELECT * FROM (SELECT * FROM (SELECT * FROM sample54) sq1) sq2;

SELECT * FROM (SELECT * FROM sample54 ORDER BY a DESC) sq WHERE ROWNUM <=2;

INSERT INTO sample541 VALUES (
  (SELECT COUNT(*) FROM sample51),
  (SELECT COUNT(*) FROM sample54)
);
SELECT * FROM sample541;

INSERT INTO sample541 SELECT 1, 2; VALUES 구 대신에 SELECT를 활용

INSERT INTO sample542 SELECT * FROM sample543; 열 구성이 같은 테이블끼리만

UPDATE sample551 SET a= '있음' WHERE
  EXISTS (SELECT * FROM sample 552 WHERE no2 = no); EXISTS는 참과 거짓을 반환

UPDATE sample551 SET a= '없음' WHERE
  NOT EXISTS (SELECT * FROM sample552 WHERE no2 = no);

SELECT * FROM sample551;

UPDATE sample551 SET a='있음' WHERE
  EXISTS (SELECT * FROM sample552 WHERE sample552.no2 = sample551.no); 열에 테이블명 붙이기

SELECT * FROM sample551 WHERE no IN (3,5);

SELECT * FROM sample551 WHERE no IN (SELECT no2 FROM sample 552);

CREATE TABLE 테이블명 (열 정의1, 열 정의2, ...)
DROP TABLE 테이블명
ALTER TABLE 테이블명 하부명령

CREATE TABLE sample62 (
  no INTEGER NOT NULL,
  a VARCHAR(30),
  b DATE);

DROP TABLE 테이블명;

TRUNCATE TABLE 테이블명; 모든 데이터 행 삭제.

ALTER TABLE sample62 ADD newcol INTEGER; 열 추가

ALTER TABLE sample62 MODIFY newcol VARCHAR(20); 열 속성 변경

ALTER TABLE sample62 CHANGE newcol c VARCHAR(20); 열 이름 변경

ALTER TABLE sample62 DROP c; 열 삭제

CREATE TABLE sample631 (
  a INTEGER NOT NULL,
  b INTEGER NOT NULL UNIQUE,
  c VARCHAR(30)
);
UNIQUE 중복값 불가 제약

CREATE TABLE sample632(
  no INTEGER NOT NULL,
  sub_no INTEGER NOT NULL,
  name VARCHAR(30),
  CONSTRAINT pkey_sample PRIMARY KEY (no, sub_no)
);
테이블 제약(PRIMARY KEY), 테이블 제약에 이름 붙이기(CONSTRAINT)
기본 키 제약: 열을 기본키로 지정해 유일한 값을 가지도록 하는 구조, NOT NULL이 설정되어있어야 함.

ALTER TABLE sample631 MODIFY c VARCHAR(30) NOT NULL; 열 제약 추가

ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY(a); 테이블 제약 추가

ALTER TABLE sample631 MODIFY c VARCHAR(30); 열 제약 삭제

ALTER TABLE sample631 DROP CONTRAINT pkey_sample631; 테이블 제약 삭제하기

ALTER TABLE sample631 DROP PRIMARY KEY; 기본키 제약 삭제하기

데이터 검색할 때 인덱스가 빠르다. 이진 트리 데이터 구조의 독립된 데이터베이스 객체로 작성되기 때문.

CREATE INDEX isample65 ON sample62(no); 인덱스 생성

DROP INDEX isample65 ON sample62; 인덱스 삭제

SELECT * FROM sample62 WHERE a = 'a';

EXPLAIN SQL 명령, sQL 명령 확인

CREATE INDEX isample65 on sample62(a);
EXPLAIN SELECT * FROM sample62 WHERE a = 'a';

EXPLAIN SELECT * FROM sample62 WHERE no > 10;
index를 a열로 지정하고 조건에서 a열을 사용하지 않는다면 index를 사용할 수 없다.

CREATE VIEW 뷰명 AS SELECT 명령
DROP VIEW 뷰명
뷰 작성과 삭제, FROM 구에 기술된 서브쿼리에 이름을 붙이고 데이터베이스 객체화하여 쓰기 쉽게 한 것을 뷰라고 함. 저장공간을 가지지 않음.

CREATE VIEW sample_view_67 AS SELECT * FROM sample54;
SELECT * FROM sample_view_67;

CREATE VIEW sample_view_672(n, v, v2) AS
  SELECT no, a, a*2 FROM sample54;
SELECT * FROM sample_view_672 WHERE n=1;
뷰에서 열지정하기

DROP VIEW sample_view_67;

SELECT * FROM sample71_a
UNION
SELECT * FROM sample71_b;

SELECT a FROM sample71_a
UNION
SELECT b FROM sample71_b
UNION
SELECT age FROM sample31;

SELECT a AS c FROM sample_a
UNION
SELECT b AS c FROM sample_b ORDER BY c;
UNION으로 SELECT 명령을 연결하는 경우, 가장 마지막 SELECT 명령에 대해서만 ORDER BY 구를 지정할 수 있따.
ORDER BY 구에 지정하는 열은 별명을 붙여 이름을 일치시킨다.

SELECT * FROM sample71_a
UNION ALL
SELECT * FROM sample71_b; UNION ALL을 적용해 중복 허용하기.

MySQL에서는 지원되지 않는 INTERSECT(교집합), EXCEPT/MINUS(차집합) 도 있다.

SELECT * FROM sample72_x, sample72_y;
FROM 구에 복수의 테이블을 지정하면 교차결합을 한다.

SELECT * FROM 상품, 재고수
  WHERE 상품.상품코드 = 재고수.상품코드;
이렇게 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것을 '내부결합'이라고 부른다.

SELECT 상품.상품명, 재고수.재고수 FROM 상품, 재고수
  WHERE 상품.상품코드 = 재고수.상품코드
    AND 상품.상품코드 = '식료품';

SELECT 상품.상품명, 재고수.재고수
  FROM 상품 INNER JOIN 재고수
    ON 상품.상품코드 = 재고수.상품코드
  WHERE 상품.상품분류 = '식료품'; INNER JOIN 사용하여 내부결합하기.

SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건

SELECT S.상품명, M.메이커명
  FROM 상품2 S INNER JOIN 메이커 M
    ON S.메이커코드 = M.메이커모드;
별명 사용하기.

SELECT S1.상품명, S2.상품명
  FROM 상품 S1 INNER JOIN 상품 S2
    ON S1.상품코드 = S2.상품코드; 자가결합

SELECT 상품3.상품명, 재고수.재고수
  FROM 상품3 LEFT JOIN 재고수
    ON 상품3.상품코드 = 재고수.상품코드
  WHERE 상품3.상품분류 = '식료품'; LEFT JOIN으로 외부결합하기 (RIGHT JOIN도 있다.)