(1주차 5일) SQL 기본

MariaDB

> SQL 기본(2)

/*
* GROUP BY
* 여러 값을 그룹으로 묶어 처리(!=중복 제거)
* 집계 함수를 함께 사용하여 그룹 연산할 수 있다.
*/
SELECT addr, COUNT(*) FROM usertbl
GROUP BY addr
ORDER BY addr;

-- #그룹 연산 처리(SUM, AVG, MIN, MAX, COUNT)
SELECT dept_code, SUM(salary) FROM employee
GROUP BY dept_code
ORDER BY dept_code;

SELECT userID, SUM(amount), SUM(amount*price) FROM buytbl
GROUP BY userId;

SELECT userid, AVG(amount) FROM buytbl
GROUP BY userid;

-- 가장 큰 키와 작은 키의 회원 이름과 키를 출력
SELECT MAX(height), MIN(height) FROM usertbl;

SELECT NAME, height FROM usertbl
WHERE height IN (186, 166);

-- #서브쿼리 활용
SELECT NAME, height FROM usertbl
WHERE height = (SELECT MIN(height) FROM usertbl) 
	OR height = (SELECT MAX(height) FROM usertbl);

SELECT COUNT(mobile1) FROM usertbl
WHERE mobile1 IS NOT NULL;

/*
* HAVING
* 집계 함수에 대하여 조건을 제한
*/
SELECT userid, SUM(price*amount) FROM buytbl
GROUP BY userid
HAVING SUM(price*amount) >= 1000;

 

> 연습 문제(2)

-- employee 테이블에서 부서별 사원의 수, 보너스를 받는 사원의 수, 급여의 합,
-- 평균 급여, 최고 급여, 최저 급여를 조회(부서별 내림차순 정렬)
SELECT dept_code AS '부서 코드', 
		 COUNT(*) AS '부서별 사원의 수', 
		 COUNT(bonus) AS '보너스를 받는 사원의 수',
		 SUM(salary) AS '급여의 합', 
		 FLOOR(AVG(salary)) AS '평균 급여', 
		 MAX(salary) AS '최고 급여',
		 MIN(salary) AS '최저 급여' 
FROM employee
GROUP BY dept_code
ORDER BY dept_code DESC;

-- EMPLOYEE 테이블에서 부서별로 급여가 300만원 이상인 직원의 평균 급여를 조회
SELECT dept_code AS '부서 코드', 
		 AVG(salary) AS '평균 급여'
FROM employee
WHERE salary >= 3000000
GROUP BY dept_code;

-- EMPLOYEE 테이블에서 부서별 평균 급여가 300만원 이상인 부서의 부서 코드, 평균 급여를 조회
SELECT dept_code AS '부서 코드', 
		 AVG(salary) AS '평균 급여'
FROM employee
GROUP BY dept_code
HAVING AVG(salary) >= 3000000;

-- EMPLOYEE 테이블에서 직급별 총 급여의 합이 10,000,000 이상인 직급만 조회
SELECT job_code
FROM employee
GROUP BY job_code
HAVING SUM(salary) >= 10000000;

-- EMPLOYEE 테이블에서 부서별 보너스를 받는 사원이 없는 부서만 조회
SELECT dept_code AS '부서 코드'
FROM employee
GROUP BY dept_code
HAVING SUM(bonus) IS NULL;

 

> SQL문 순서 정리

• 작성 순서: SELECT → FROM(JOIN) → WHERE → GROUP BY → HAVING → ORDER BY(LIMIT)
• 실행 순서: FROM(JOIN) → WHERE → GROUP BY → HAVING → SELECT → ORDER BY(LIMIT)

 

> 데이터 변경

/*
* INSERT
* 테이블에 데이터 삽입
* 필드의 데이터 형식에 맞추어야 함
* 기본키 값이 반드시 입력되어야 함
*/
-- 테이블에 1행 삽입
INSERT INTO usertbl(userID, NAME, birthYear, addr)
VALUES ('hong123', '홍길동', 1995, '서울');

-- 필드를 지정하지 않는 경우, 모든 필드의 값을 지정해야 함
INSERT INTO usertbl
VALUES ('sung123', '성춘향', 1990, '강원', '010', '34567890', 160, CURDATE());

-- 테이블에 여러 행 삽입
INSERT INTO usertbl(userID, NAME, birthYear, addr)
VALUES ('lee123', '이몽룡', 1990, '강원'),
		 ('lim123', '임꺽정', 1988, '경기');
		 
-- 테이블 불러오기(키는 복사되지 않음)
-- WHERE 1 = 0: 검색 조건을 거짓으로 작성하여 테이블의 구조만 불러올 수 있다
CREATE TABLE emp_copy (
	SELECT * FROM employee
	WHERE 1 = 0
);

INSERT INTO emp_copy (
	SELECT * FROM employee
	WHERE dept_code = 'D9'
);

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'
);


/*
* UPDATE
* 기존 데이터의 값을 변경할 때 사용
*/
UPDATE usertbl SET NAME = '고길동'
WHERE userid = 'hong123';

-- 모든 사원의 급여를 기존 급여에서 10% 인상한 금액으로 변경
CREATE TABLE emp_salary (
	SELECT emp_id, emp_name, salary, bonus FROM employee
);

UPDATE emp_salary
SET salary = salary * 1.1;

/*
* DELETE
* 행 단위로 데이터를 삭제할 때 사용
* WHERE절 생략 가능하며, 전체 행이 삭제됨
*/
-- SELECT *
DELETE FROM usertbl
WHERE userid = 'hong123';

-- SELECT * 
DELETE FROM usertbl
WHERE height IS NULL LIMIT 2;

DELETE FROM emp_copy;

-- #TRUNCATE TABLE: 테이블의 모든 데이터 삭제
TRUNCATE TABLE emp_salary;

-- #DROP TABLE: 테이블 삭제
DROP TABLE emp_copy;
DROP TABLE emp_salary;

/*
* 조건부 데이터 입력, 변경
*/

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

-- #DUPLICATE KEY: 기본키가 중복되지 않으면 INSERT, 중복되면 UPDATE 실행
INSERT INTO usertbl(userid, NAME, birthYear, addr)
VALUES ('BBK', '바보킴', 1999, '인천')
ON DUPLICATE KEY UPDATE NAME='바보킴', addr='인천';

> SQL 언어 구분

• DDL(데이터 정의어): CREATE, ALTER, DROP, RENAME, TRUNCATE
• DML(데이터 조작어): SELECT, INSERT, UPDATE, DELETE
• DCL(데이터 제어어): GRANT, REVOKE
• TCL(트랜젝션 제어어): COMMIT, ROLLBACK, SAVEPOINT

> 함수 실습(1)

-- 형 변환: 숫자 데이터를 문자 데이터로 형 변환
SELECT 123456789;
SELECT CAST(123456789 AS CHAR);
SELECT CONVERT(123456789, CHAR);
SELECT FORMAT(12345.678, 2);
SELECT FORMAT(12345.678, 0);

-- buytbl에서 평균 구매 개수를 조회
SELECT CAST(AVG(amount) as INT) FROM buytbl;

SELECT CAST('1000000' AS INT);
SELECT CONVERT('1000000', INT);
SELECT CONVERT(REPLACE('1,000,000', ',', ''), INT);

-- 아래 쿼리가 정상적으로 연산되도록 쿼리문을 작성
SELECT CONVERT(REPLACE('1,000,000', ',', ''), INT) 
	  - CONVERT(REPLACE('500,000', ',', ''), INT);

-- employee 테이블에서 emp_id를 숫자 형식으로 변환하여 조회
SELECT cast(emp_id AS INT), emp_name
FROM employee;

SELECT CONVERT('2024/05/17', DATE);
SELECT CONVERT('2024%05%17', DATE);

SELECT CAST(20240517 AS DATE);
SELECT CAST(20240517154238 AS DATETIME);
SELECT CAST(223859 AS TIME);

-- 묵시적 형변환
SELECT '100' + '200';
SELECT CONCAT('100', '200');
SELECT CONCAT(100, '200');

-- IF, IFNULL, NULLIF
SELECT IF(100 > 200, '참', '거짓');

SELECT userid, SUM(amount), IF(SUM(amount) >= 10, 'VIP 고객', '일반 고객') FROM buytbl
GROUP BY userid
ORDER BY userid;

SELECT IFNULL(NULL, '값이 없음'), IFNULL(100, '값이 없음');
SELECT NVL(NULL, '값이 없음'), NVL(100, '값이 없음');

-- buytbl 테이블에서 모든 데이터 출력
-- 단, groupName 값이 NULL인 경우 '없음'으로 표시
SELECT num, userID, prodName, IFNULL(groupName, '없음'), price, amount FROM buytbl;

-- employee 테이블에서 보너스를 0.1로 동결하여 직원명, 보너스율
-- 동결된 보너스율, 보너스가 포함된 연봉 출력
SELECT emp_name, IFNULL(bonus, 0), NVL2(bonus, 0.1, 0), 
		 (salary + (salary * NVL2(bonus, 0.1, 0))) * 12
FROM employee;

SELECT NULLIF('123', '123');
SELECT NULLIF('123', '456');

-- CASE문
SELECT CASE 10
			WHEN 1 THEN '일'
			WHEN 5 THEN '오'
			WHEN 10 THEN '십'
			ELSE '알 수 없음'
		 END AS '결과';
		 
SELECT emp_name, salary, 
		 CASE WHEN salary > 5000000 THEN '1등급'
				WHEN salary > 3500000 THEN '2등급'
				WHEN salary > 2000000 THEN '3등급'
				ELSE '4등급'
			END AS 'grade'
FROM employee
ORDER BY salary DESC;

-- ASCII(), CHAR()
SELECT ASCII('a'), CHAR(97);

-- BIT_LENGTH(), CHAR_LENGTH(), LENGTH() 함수
-- MariaDB는 기본적으로 UTF-8 코드를 사용하므로 영문은 1Byte, 한글은 3Byte를 할당함
SELECT BIT_LENGTH('abc'), CHAR_LENGTH('abc'), LENGTH('abc');
SELECT BIT_LENGTH('가나다'), CHAR_LENGTH('가나다'), LENGTH('가나다');