[Database] SQL 기본 (2)

SQL 기본

---

실습 데이터베이스 - emloyees, testdb

1. 데이터 조회

1.4. GROUP BY

• GROUP BY 절은 여러 개의 값들을 하나의 그룹으로 묶어서 처리할 목적으로 사용하는 구문

SELECT gender, COUNT(*)
FROM employees
GROUP BY gender
ORDER BY gender;

<ex1-1>
-- GROUP BY 실습
-- usertbl 테이블에서 addr를 기준으로 그룹화하여
-- addr 기준으로 정렬 후 조회
# Distict와 결과가 동일해 보이지만 동작이 다름!

SELECT addr
FROM usertbl
GROUP BY addr
ORDER BY addr
;

usertbl 테이블에서 addr를 기준으로 그룹화하여 addr 기준으로 정렬 후 조회

---

-- 잘못된 연산
<ex1-2>
SELECT DISTINCT addr, COUNT(*)
FROM usertbl
ORDER BY addr
;

addr 조회와 count 연산의 행이 맞지 않기 때문에 결과값이 올바르지 못함

 

---

• 집계 함수를 GROUP BY 절과 함께 사용해 하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등을 계산 가능

SELECT SUM(salary) AS '급여의 합',
       AVG(salary) AS '급여의 평균',
       MIN(salary) AS '최소 급여',
       MAX(salary) AS '최대 급여',
       COUNT(*) AS '직원 수'
FROM employees
GROUP BY gender;

<ex1-1>
-- GROUP BY 함수를 사용
-- employee 테이블에서 부서별 급여의 함계를 조회 (부서별 오름차순 정렬)
SELECT dept_code,
		 sum(salary)
FROM employee
GROUP BY dept_code
ORDER BY dept_code
;

employee 테이블에서 부서별 급여의 합계 조회

---

<ex1-2>
-- employee 테이블에서 전체 사원의 급여의 합계를 조회
SELECT sum(salary)
FROM employee;

employee 테이블에서 전체 사원의 급여의 합계를 조회

---

<ex2>
-- 사용자 별로 구매한 물품의 개수의 합계를 조회
SELECT userID,
       SUM(amount)
FROM buytbl
GROUP BY userID
;

buytbl 테이블에서 사용자별로 구매한 물품의 개수 합계를 조회

 

---

<ex3-1>
-- 전체 구매자가 구매한 물품 개수의 평균
# AVG() 함수도 GROUP BY를 사용하지 않아도 가능
# ROUND() 함수를 사용하면 반올림 > ROUND(AVG(amount))
# FLOOR() 함수를 사용하면 내림 > FLOOR(AVG(amount))

SELECT AVG(amount)
FROM buytbl
;

buytbl 테이블에서 전체 구매자가 구매한 물품 개수의 평균 조회

 

---

<ex3-2>
-- 사용자별 평균 구매 개수
SELECT userID,
       AVG(amount)
FROM buytbl
GROUP BY userID
;

buytbl 테이블에서 사용자 별 평균 구매 개수 조회

---

<ex4-1>
-- 가장 큰 키와 작은 키를 조회
SELECT MAX(height),
       MIN(height)
FROM usertbl
;

usertbl에서 가장 큰 키와 작은 키를 조회

---

<ex4-2>
-- 가장 큰 키와 작은 키를 이름과 함께 조회
# 서브 쿼리가 먼저 실행이 되고 메인 쿼리가 실행됨
# 서브 쿼리에는 ;(세미콜론)을 붙여서는 안됨

SELECT NAME,
       height
from usertbl
WHERE height = (SELECT MAX(height) FROM  usertbl)
		OR height = (SELECT MIN(height) FROM  usertbl)
;

서브 쿼리를 사용하여 usertbl에서 가장 큰 키와 작은 키를 이름과 함께 조회

---

<ex5-1>
-- 전체 행의 개수를 카운트
SELECT COUNT(*)
FROM employee
;

employee 테이블의 전체 행의 개수를 카운트

---

<ex5-2>
-- 두 개의 sql문의 결과 값은 동일
-- 휴대폰이 있는 사용자의 수
SELECT COUNT(*)
FROM usertbl
WHERE mobile1 IS NOT NULL
;

-- mobile1 열에 값이 있는 행만 카운트
SELECT COUNT(mobile1)
FROM usertbl
;

usertbl 테이블에서 휴대폰 정보가 있는 사용자의 수 조회

1.5. HAVING

• HAVING 절은 집계 함수에 대해서 조건을 제한하는 구문이다.

SELECT gender, COUNT(*)
FROM employees
GROUP BY gender
HAVING COUNT(*) >= 5;

employees 테이블에서 gender 별로 그룹화하여 5명이 넘는 조건에 만족하는 데이터 조회

<ex1>
-- 총 구매액이 1000 이상인 회원의 아이디, 구매 금액을 조회
# 집계 함수의 결과를 WHERE 절에서 조건으로 사용할 수 없음
# GROUP BY 절 전에 WHERE절이 실행이 되기 때문에 오류가 발생
# GROUP BY 절에서 조건을 주고 싶을때는 HAVING절을 사용
# HAVING 절은 반드시 GRUOP BY 다음에 작성

SELECT userID, SUM(price*amount)
FROM buytbl
GROUP BY userID
HAVING SUM(price*amount) >= 1000
;

buytbl 테이블에서 총 구매액이 1000 이상인 회원의 아이디, 구매 금액을 조회

2. 데이터 변경

2.1. INSERT

• INSERT는 테이블에 데이터를 삽입하는 구문

INSERT INTO employees(name, gender, age) VALUES('홍길동', '남자', 20);

<ex1-1>
-- INSERT 실습
-- usertbl 테이블에 한 개의 행을 삽입
-- 열의 데이터 타입과 insert할 데이터 타입의 순서가 맞지 않으면 에러가 발생
# 특정 컬럼만 넣고 싶다면 테이블명(컬럼명1, 컬럼명2, 컬럼명3, -)형태로 작성
# 모든 컬럼에 데이터를 넣고 싶으면 테이블명만 작성

INSERT INTO usertbl(userID, NAME, birthYear, addr)
VALUES ('hong123', '홍길동', 1994, '서울')

select * from usertbl를 통해서 insert한 데이터가 저장되어있는지 확인

---

<ex1-2>
-- 기본 키(default value) 열에 NULL 값이 입력될 수 없어서 에러가 발생
INSERT INTO usertbl(NAME, birthYear, addr)
VALUES ('이몽룡', 1990, '강원')
;

에러 발생 내용

---

<ex1-3>
-- 테이블명만 있는 형식은 모든 열의 값을 넣는다는 의미
-- 모든 열에 값을 지정하지 않아서 에러가 발생
INSERT INTO usertbl
VALUES ('sung123', '성춘향', 1990, '강원')
;

오류 발생 내용

---

<ex2>
-- usertbl 테이블에 한 개의 행 삽입
-- CURDATE() 함수는 현재 날짜 데이터를 출력하는 함수
INSERT INTO usertbl
VALUES ('sung123', '성춘향', 1990, '강원', '010', '34567890', 160, CURDATE())
;

select * from usertbl를 통해서 insert한 데이터가 저장되어있는지 확인

---

• 여러 개의 행을 한 번에 삽입하려면 아래와 같이 작성

INSERT INTO employees VALUES ('홍길동', '남자', ...), ('성춘향', '여자', ...);

<ex1>
-- usertbl 테이블에 여러 개의 행 삽입
# 기본키값을 기준으로 오름차순 정렬

INSERT INTO usertbl(userid, NAME, birthYear, addr)
VALUES ('lee123', '이몽룡', 1990, '강원'),
		 ('lim123', '임꺽정', 1988, '경기')
;

2개의 데이터를 insert 했기 때문에 영향 받은 행이 2개

select * from usertbl를 통해서 insert한 데이터가 저장되어있는지 확인

---

• 다른 테이블의 데이터를 가져와서 대량으로 입력하려면 아래와 같이 작성

INSERT INTO employees_copy 
  SELECT * 
  FROM employees;

<ex1-1>
-- emp_copy 테이블 생성하여 employee 테이블 복사
# 키는 복사 되지 않음
# WHERE 절의 1 = 0은 모든 행이 거짓이기 때문에 데이터는 입력이 되지 않고 구조만 복사됨

CREATE TABLE emp_copy(
	SELECT *
	FROM employee
	WHERE 1=0
)
;

emp_copy 테이블이 생성

WHERE 1=0 의 조건으로 구조만 복사됨

---

<ex1-2>
-- employee 테이블에서 dept_code가 D9인 데이터만 삽입
-- SELECT 결과를 테이블 삽입
INSERT INTO emp_copy(
	SELECT *
	FROM employee
	WHERE dept_code = 'D9')
;

 

select * from usertbl를 통해서 insert한 데이터가 저장되어있는지 확인

---

<ex2>
-- employee 테이블에서 dept_code가 D2인 데이터의
-- emp_id, emp_name, emp_no, dept_code 데이터만 insert

INSERT INTO emp_copy(emp_id, emp_name, emp_no, dept_code)(
	SELECT emp_id,
		emp_name,
		emp_no,
		dept_code
	FROM employee
	WHERE dept_code = 'D2')
;

select * from usertbl를 통해서 insert한 데이터가 저장되어있는지 확인

2.2. UPDATE

• UPDATE는 기존에 입력되어 있는 값을 변경할 때 사용하는 구문

• WHERE 절은 생략이 가능하지만 WHERE 절을 생략하면 테이블 전체의 행이 변경

UPDATE employees
SET name = '임꺽정',
    age = 35
WHERE emp_no = 30000;

 

<ex1>
-- userID 가 hong123인 회원의 이름을 고길동으로 변경
UPDATE usertbl
SET NAME = "고길동"
WHERE userID = "hong123"
;

데이터가 변경이 되었는지 SELECT문을 통해 확인

---

<ex2>
-- 테스트 테이블 생성
-- employee 테이블에서 emp_id, emp_NAME, salary, bonus의 컬럼만
-- 선택하여 emp_salary 테이블 생성후 삽입
CREATE TABLE emp_salary(
	SELECT emp_id,
			 emp_NAME,
			 salary,
			 bonus
	FROM employee
);

emp_salary 테이블 생성

emp_salary의 데이터

<ex2>
-- 모든 사원의 급여를 기존 급여에서 10프로 인상한 금액을 변경
UPDATE emp_salary
SET salary = salary * 1.1;

where절이 없어서 경고 메시지 출력 ("예" 클릭)

모든 행의 salary 열의 데이터가 update

 

2.3. DELETE

• DELETE는 행 단위로 데이터를 삭제할 때 사용하는 구문
• WHERE 절은 생략이 가능하지만 WHERE 절을 생략하면 테이블 전체의 행이 삭제
• 만약 조건을 만족하는 결과 중에서 상위 몇 건만 삭제하려면 LIMIT 구문과 함께 사용

-- 조건을 만족하는 3개의 행 삭제
DELETE FROM employees WHERE address LIKE '서울%' LIMIT 3;

-- 테이블 삭제
DROP TABLE employees;

-- 테이블의 모든 데이터 삭제
TRUNCATE TABLE employees;

<ex 1>
-- userID가 hong123인 회원을 삭제
-- SELECT * # DELETE는 데이터를 삭제하는 구문이기 때문에 항상 주의가 필요
DELETE 
FROM usertbl
WHERE userID = "hong123"

SELECT 구문을 사용하여 데이터가 삭제 되었는지 확인

---

 

<ex2>
-- height가 NULL인 회원들 중 상위 2명 삭제
-- SELECT *
# HeidiSQL 에서 쿼리문이 아닌 툴을 통해서도 삭제가 가능

DELETE
FROM usertbl
WHERE height IS NULL
LIMIT 2
;

SELECT 구문을 사용하여 데이터가 삭제 되었는지 확인

---

<ex3>
-- emp_copy 테이블 전체 데이터 delete
# WHERE 절을 작성하지 않으면 모든 행이 삭제됨

DELETE FROM emp_copy;

emp_copy 테이블의 데이터가 모두 삭제된 것을 확인

---

<ex4>
-- 테이블 삭제
# DROP TABLE [테이블 명]
DROP TABLE emp_copy;
DROP TABLE emp_salary;

emp_copy와 emp_salary 테이블이 삭제됨

2.4. 조건부 데이터 입력, 변경

• 기본적으로 기본 키(PK)가 중복된 경우 데이터가 입력되지 않음
• 기본 키(PK)가 중복되더라도 오류를 발생하지 않고 무시하고 넘어가려면 아래와 같이 작성

INSERT IGNORE INTO employees VALUES(30000, '홍길동', 30);

 

<ex1-1>
-- 기본 키의 값이 중복으로 에러가 발생하는 경우

INSERT INTO usertbl(userid, NAME, birthYear, addr)
VALUES ('BBK', '바보킴', 1999, "인천")
;

primary 키 중 동일한 것이 있기 때문에 오류 발생

---

<ex1-2>
-- 기본 키 중복으로 인한 에러가 발생하지 않고 경고만 출력
INSERT IGNORE INTO usertbl(userid, NAME, birthYear, addr)
VALUES ('BBK', '바보킴', 1999, "인천")
;

primary 키 중 동일한 것에 대한 경고 출력

---

• 기본 키(PK)가 중복되지 않으면 INSERT를 실행하고, 기본 키(PK)가 중복되면 UPDATE를 실행하려면 아래와 같이 작성

INSERT INTO employees VALUES(30000, '임꺽정', 35)
  ON DUPLICATE KEY UPDATE name = '임꺽정', age = 35;

<ex2>
-- userID가 BBK인 회원이 없으면 INSERT를 수행하고
-- userID가 BBK인 회원이 있으면 UPDATE를 수행

INSERT INTO usertbl(userid, NAME, birthYear, addr)
VALUES ('BBK', '바보킴', 1999, "인천")
ON DUPLICATE KEY UPDATE NAME = "바보킴", addr = "인천"
;

BBK라는 userID가 존재했기 때문에 name을 바보킴, addr를 인천으로 UPDATE