D2. SQL 문법

A. 기본 문법

SELECT 열_이름
    FROM 테이블_이름
    WHERE 조건식
    GROUP BY 열_이름
    HAVING 조건식
    ORDER BY 열_이름
    LIMIT 숫자

순서: FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER

 

 

회원 테이블 만들기

USE market_db;
CREATE TABLE member -- 회원 테이블
( mem_id      CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    VARCHAR(10) NOT NULL, -- 이름
  mem_number  INT NOT NULL,  -- 인원수
  addr        CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1      CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2      CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height      SMALLINT,  -- 평균 키
  debut_date  DATE  -- 데뷔 일자
);

* CHAR: 고정형 - 8개가 들어오지 않아도 8글자 저장

* VARCHAR: 가변형 - 들어오는 글자만큼 저장, 저장 효율 good

 

구매 테이블 만들기

CREATE TABLE buy -- 구매 테이블
(  num 		INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
   mem_id  	CHAR(8) NOT NULL, -- 아이디(FK)
   prod_name 	CHAR(6) NOT NULL, --  제품이름
   group_name 	CHAR(4)  , -- 분류
   price     	INT  NOT NULL, -- 가격
   amount    	SMALLINT  NOT NULL, -- 수량
   FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);

 

 

---

데이터 조회

 

USE문

-- USE 사용
SELECT * FROM product;
USE shop_db;

-- USE 사용 안하려면
SELECT * FROM shop_db.product;

• shop_db 데이터베이스를 대상으로 할거야. 
• 이렇게 명시하지 않으면 모든 SQL문은 마지막으로 작업했던 DB에서 동작한다.

 

관계연산자, 논리연산자 사용

SELECT mem_name, height, mem_number
FROM member
WHERE height >= 165 AND mem_number > 6;

 

 

BETWEEN ~ AND: 범위값 구하기

SELECT mem_name, height, mem_number
FROM member
WHERE height BETWEEN 163 AND 165;

 

 

IN()

SELECT mem_name, addr
FROM member
WHERE addr IN('경기', '전남', '경남');

 

LIKE: 문자열의 일부 글자 검색

SELECT *
FROM member
WHERE mem_name LIKE '우%';

• 제일 앞 글자가 '우'이고 그 뒤는 무엇이든(%) 허용한다.
• 한 글자와 매치하기 위해서는 언더바(_)를 사용한다.

SELECT *
FROM member
WHERE mem_name LIKE '__핑크';

• 앞 두 글자는 상관 없고 뒤는 '핑크'인 회원을 검색한다.

 

서브 쿼리

SELECT mem_name, height FROM member
	WHERE height > (SELECT height FROM member WHERE mem_name = '에이핑크');

 

ORDER BY

• WHERE절과 함께 사용할 수 있다.
• 다만 WHERE절을 먼저 사용해줘야 한다.

SELECT mem_id, mem_name, debut_date
FROM member
WHERE height >= 164
ORDER BY height DESC, debut_date ASC;

 

정렬 

• ASC 오름차순
• DESC 내림차순 

 

LIMIT

• 출력하는 개수를 제한한다.
• 전체 중 앞에서 n개까지만 조회 

SELECT *
FROM member
LIMIT 3;

• LIMIT 시작, 개수 

SELECT mem_name, debut_date
FROM member
ORDER BY debut_date
LIMIT 3, 2;

• 3번째부터 2건

 

 

DISTINCT

• 중복된 결과를 제거

SELECT DISTINCT addr FROM member;

 

 

GROUP BY

• 그룹으로 묶어준다.
• 그룹을 묶어 집계한다.

 

집계 함수

• SUM()
• AVG()
• MIN()
• MAX()
• STDDEV() : 표준편차 
• VARIANCE() : 분산
• COUNT()
• COUNT(DISTINCT)

SELECT mem_id "회원 아이디", SUM(amount) "총 구매 개수"
FROM buy GROUP BY mem_id;

SELECT COUNT(*) FROM member;

• 집계함수는 WHERE절과 함께 사용할 수 없다.
• 대신 HAVING절을 사용한다.
• HAVING절은 반드시 GROUP BY 뒤에 나와야 한다.

SELECT mem_id "회원 아이디", SUM(price*amount) "총 구매 금액"
FROM market_db.buy
GROUP BY mem_id
HAVING SUM(price*amount) > 1000;

 

 

 

 

---

데이터 입력

 

INSERT

INSERT INTO 테이블 [(열1, 열2, ...)] VALUES (값1, 값2, ...)

• 테이블 이름 다음에 나오는 열은 생략 가능
• 열 이름을 생략할 경우 VALUES 뒤에 나오는 값들의 순서 및 개수는 테이블을 정의할 때 열 순서 및 개수와 동일해야 한다.

USE market_db;
CREATE TABLE hongong1 (toy_id INT, toy_name CHAR(4), age INT);
INSERT INTO hongong1 VALUES (1, '우디', 25);

• 만약 age는 입력하고 싶지 않다면 테이블 이름 뒤에 입력할 열의 이름을 써줘야 한다. 

INSERT INTO hongong1 (toy_id, toy_name) VALUES (2, '버즈');

• age 열에는 NULL 값이 들어감

 

 

AUTO_INCREMENT : 자동으로 증가

• 열을 정의할 때 1부터 증가하는 값을 입력해준다.
• INSERT에서는 해당 열이 없다고 생각하고 입력한다.
• 주의: 지정한 열은 꼭 PRIMARY KEY로 지정!

CREATE TABLE hongong2 (
    toy_id INT AUTO_INCREMENT PRIMARY KEY, 
    toy_name CHAR(4),
    age INT);

• 자동증가하는 부분은 NULL 값으로 채워 놓으면 된다.

INSERT INTO hongong2 VALUES (NULL, '보핍', 25);
INSERT INTO hongong2 VALUES (NULL, '슬링키', 22);
INSERT INTO hongong2 VALUES (NULL, '렉스', 21);
SELECT * FROM hongong2;

• 계속 입력하다가 현재 어느 숫자까지 증가되었는지 확인이 필요할 때

SELECT LAST_INSERT_ID();

 

만약 AUTO_INCREMENT로 처음부터 입력되는 값을 100으로 지정하고, 다음 값은 3씩 증가하도록 하려면

ALTER TABLE hongong2 AUTO_INCREMENT=100;
SET @@auto_increment_increment=3;

 

 시스템 변수!

- MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수

- SHOW GLOBAL VARIABLES : 전체 시스템의 종류를 보여줘

- SELECT @@시스템변수 : 실행

여러 줄을 한 줄로 작성할 수도 있다.

INSERT INTO hongong2 VALUES (NULL, '보핍', 25), (NULL, '슬링키', 22), (NULL, '렉스', 21);

 

 

 

INSERT INTO ~ SELECT : 다른 테이블의 데이터를 한 번에 입력

• 주의: SELECT 문의 열 개수는 INSERT할 테이블의 열 개수와 같아야 한다.

INSERT INTO 테이블_이름 (열_이름1, 열_이름2, ...)
    SELECT문;

CREATE TABLE city_popul (city_name CHAR(35), population INT);

INSERT INTO city_popul
    SELECT Name, Population FROM world.city;

 

 

 

---

데이터 수정

 

UPDATE 문의 기본 문법

UPDATE 테이블_이름
    SET 열1=값1, 열2=값2, ...
    WHERE 조건;

 

city_popul 테이블의 도시이름(city_name) 중에서 'Seoul' -> '서울'로 변경

USE market_db;
UPDATE city_popul
SET city_name = '서울'
WHERE city_name = 'Seoul';
SELECT * FROM city_popul WHERE city_name = '서울';

 

도시이름(city_name)인 'New York'을 '뉴욕'으로 바꾸면서 동시에 인구(population)는 0으로 설정

UPDATE city_popul
SET city_name = '뉴욕', population = 0
WHERE city_name = 'New York';
SELECT * FROM city_popul WHERE city_name = '뉴욕';

 

주의: UPDATE 문에서 WHERE 절은 생략 가능하지만, 생략하면 테이블의 모든 행의 값이 변경된다. 

UPDATE city_popul
    SET population = population / 10000;
SELECT * FROM city_popul LIMIT 5;

 

 

 

---

데이터 삭제

 

DELETE

DELETE FROM 테이블 이름 WHERE 조건;