[혼공S] 2주차_CH3

Ch3. SQL 기본 문법

3.1 기본 중에 기본 SELECT~FROM~WHERE

• SELECT 열이름 FROM 테이블 이름 WHERE 조건식
• 하이픈 2개 연속(--)하고 한 칸 띄기 : 주석처리하는 방법
• 열이름에 띄어쓰기 있으면 언더바로 대체 또는 큰따옴표 사용
• 전각문자는 alt + = 키를 통해 기본문자로 바꿔줄 것

---

🚩실습용 데이터 구축

1) 데이터 베이스 만들기

• 한빛미디어 사이트의 혼공자료실에서 market_db.sql파일을 다운로드한 후 SQL에서 market_db 데이터베이스를 열어서 member1, buy2 테이블을 확인
  

• 위와 같은 데이터베이스와 테이블을 직접 코딩해서 만들어보기
  (1) 기존 market_db 삭제하고 market_db만들기

  DROP DATABASE IF EXISTS market_db;
  CREATE DATABASE market_db;

(2) memeber 테이블 형태만 만들기

USE market_db;
CREATE TABLE member -- 회원 테이블(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), -- 연락처의 국번
phone2 CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
height SMALLINT, -- 평균키
debut_date DATE -- 데뷔 일자
);

(3) buy 테이블 형태만 만들기

USE market_db;
CREATE TABLE buy -- 구매 테이블(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)
);

• AUTO_INCREMENT : 자동으로 숫자를 1,2,3,...과 같이 입력해줌.

(4) 데이터 입력 후 확인

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);

※ CHAR, VARCHAR, DATE형은 작은따옴표로 값을 묶어줌.
buy 테이블의 첫 번째 열인 num은 자동으로 입력되므로 그 자리에는 NULL이라고 적어줌.

SELECT * FROM member;
SELECT * FROM buy;

---

기본조회하기 SELECT~FROM

USE market_db;
SELECT * FROM member;

데이터베이스를 USE문으로 지정 후 member테이블의 모든 열 추출

SELECT * FROM member.market_db;

USE문 이용하지 않았을때 테이블.데이터베이스이름 으로도 가능.

SELECT addr 주소, debut_date "데뷔 일자", mem_name FROM member;

member테이블에서 일부 열을 추출하는데 별칭(alias)를 지정해줌.
별칭에 공백이 있으면 큰따옴표로 묶어줄 것!

특정한 조건만 조회하기 : SELECT~FROM~WHERE

• SELECT 열이름 FROM 테이블이름 WHERE 조건식;

• 이름이 블랙핑크인 결과만 출력

SELECT * FROM member WHERE mem_name = '블랙핑크';

• 키가 165보다 크고, 인운수가 6명초과인 회원의 이름, 평균키, 인원수 출력

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

• 평균키가 163~165인 회원의 이름과 평균키 출력

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

👉 숫자의 범위를 조건식에서 사용할땐 BETWEEN~AND 쓸 것.

• 주소가 경기/전남/경남 중 한 곳에 사는 회원을 출력

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

👉 여러 문자 중 하나에 포함되는지를 알아볼 땐 IN() 쓸 것.

• 첫 글자가 '우'로 시작하는 회원을 출력

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

👉 문자열의 일부 글자를 검색하려면 LIKE 쓸 것.

• 이름의 앞 두 글자는 상관없고 뒤가 '핑크'인 회원을 출력

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

👉 한 글자랑 매치하기 위해서는 언더바(_) 쓸 것.

🔸서브쿼리(하위쿼리) : SELECT 안에 또다른 SELECT가 들어 갈때, 두 개의 SELECT문을 하나로!

ex) 이름이 '에이핑크'인 회원의 평균 키보다 큰 회원을 출력

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

3.2 좀 더 깊게 알아보는 SELECT문

💡SELECT문 기본 공식 (순서중요!)

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

✔️ ORDER BY : 결과가 출력되는 순서를 조절. 오름차순은 ASC(기본), 내림차순은 DESC

• 날짜 오름차순은 옛날부터 현재까지로 출력

SELECT mem_id, mem_name, debut_date FROM member ORDER BY debut_date;

• 날짜 내림차순은 현재부터 과거로 출력

SELECT mem_id, mem_name, debut_date FROM member ORDER BY debut_date DESC;

• 평균키가 큰 순서대로 정렬하되, 평균키가 같으면 데뷔 일자가 빠른 순서로 정렬

SELECT mem_id, mem_name, height, debut_date FROM MEMBER ORDER BY height, debut_date DESC;

주의) ORDER BY height and debut_date DESC는 다른 결과 나옴..

• 평균키가 큰 순으로 정렬하되, 3번째부터 2건만 조회

  SELECT * FROM member ORDER BY height DESC LIMIT 3, 2;

LIMIT 시작, 개수 로 몇 건 추출 / LIMIT 3 = LIMIT 0, 3

• addr에서 중복된 데이터는 1개만 남기고 제거 : SELECT DISTINCT 열이름

  SELECT DISTINCT addr FROM member;

✔️ GROUP BY : 데이터를 그룹으로 묶어줌. 집계함수와 주로 같이 이용.

함수명	설명
SUM()	합계
AVG()	평균
MIN()	최소값
MAX()	최대값
COUNT(*)	모든 행의 개수 세어줌.
COUNT(열이름)	열이름 값이 NULL인 것을 제외한 행의 개수 세어줌.

• 각 회원별로 구매한 개수를 합쳐서 출력

  SELECT mem_id, SUM(amount) FROM buy GROUP BY mem_id;

별칭을 이용하여 좀 더 보기 편하게 한다면,

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

• 회원이 구매한 금액의 총합 출력

  SELECT mem_id, SUM(amount), SUM(amount * price) FROM buy GROUP BY mem_id;

• 총 구매액이 1000이상인 회원만 추출

  SELECT mem_id, SUM(amount price) FROM buy GROUP BY mem_id HAVING SUM(amount price) > 1000;

HAVING : 집계함수에 대한 조건을 제한하며, GROUP BY뒤에 나와야 함!

• 총 구매액이 1000이상인 회원만 나오게 하되 큰 사용자부터 나타나게 출력

  SELECT mem_id, SUM(amount price) FROM buy GROUP BY mem_id HAVING SUM(amount price) > 1000 ORDER BY SUM(amount * price) DESC;

🎈 p.138 확인문제
2. 다음 보기 중에서 각 문항의 빈칸에 들어갈 것을 고르세요.
답 :
(1) ORDER BY
(2) LIMIT
(3) DISTINCT

3.3 데이터 변경을 위한 SQL문

✔️ INSERT : 테이블에 데이터를 삽입하는 명령
INSERT INTO 테이블 [(열1, 열2, ...)] VALUES (값1, 값2, ...)
주의) 열은 생략가능 / 열 순서 바꿔서 데이터 입력 가능

• toy_id랑 toy_name만 입력

  USE market_db;
  CREATE TABLE hongong1 (toy_id INT, toy_name CHAR(4), age INT);
  INSERT INTO hongong1 (toy_id, toy_name) VALUES (2, '버즈');

• 자동으로 증가하는 AUTO_INCREMENT
  주의) Primary Key로 지정된 열만 가능

• toy_id열은 자동증가로 설정

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

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

• 처음부터 입력되는 값을 1000으로 지정하고, 3씩 증가하도록 설정
  🚩이 경우에는 @@auto_increment_increment라는 시스템 변수를 변경해줘야함!!

CREATE TABLE hongong3(
toy_id INT AUTO_INCREMENT PRIMARY KEY,
toy_name CHAR(4),
age INT);
ALTER TABLE hongong3 AUTO_INCREMENT = 1000; -- 1000부터 시작하도록 테이블 변경
SET @@auto_increment_increment = 3; -- 증가값을 3으로 지정

여기서, 시스템 변수 : MySQL에서 자체적으로 가지고 있는 설정값이 저장된 변수로, @@이 앞에 붙고, "SELECT @@시스템변수"로 확인 가능

• 다른 테이블의 데이터를 한 번에 입력하는 INSERT INTO ~ SELECT
  cf) DESC 테이블 이름 : 테이블 구조 확인

  DESC world.city;

(1) 도시 이름, 인구를 새 테이블로 옮기기 위해 테이블부터 만들기

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

(2) 만들어진 테이블에 도시 이름, 인구 데이터 옮기기

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

✔️ UPDATE : 데이터 수정

• UPDATE 테이블_이름 SET 열1=값1, 열2=값2,... WHERE 조건;
  주의) MySQL Workbench 설정 변경해야함!!!!!!

• New York을 뉴욕으로, 인구를 0으로 수정

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

  주의) 만약, WHERE가 없다면 테이블의 모든 행의 값이 변경됨. -> 모든 행의 인구단위나 가격단위를 맞추기 위해 일괄적으로 값을 변경해야할 땐 유용!

  ✔️ DELETE : 테이블의 행 데이터를 삭제해야하는 경우

• DELETE FROM 테이블이름 WHERE 조건식;

• New로 시작하는 도시 중 상위 5개만 삭제

  DELETE FROM city_popul WHERE city_name LIKE 'New%' LIMIT 5;

하지만, 대용량 테이블을 삭제해야 할 경우,
테이블까지 삭제 하려면 : DROP TABLE 테이블이름;
테이블은 남기고 삭제 하려면 : TRUNCATE TABLE 테이블이름;