[1주차] 24.01.12 금요일

• DDL(Data Definition Language) : 데이터 정의어 - create, alter, drop
• DML(Data Manipulation Language) : 데이터 조작어 - insert, update, delete
• DCL(Data Control Language) : 데이터 제어어 - grant, revoke
• TCL(Transaction Control Language) : 트랜잭션 제어어 - commit, rollback

SQL

 

함수란 하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리한 작은 서브 프로그램이다.

호출(call)하고, 실행에 대한 결과를 리턴(return)하는 방식으로 사용한다.

 

함수의 유형 : 단일행 함수(n개의 입력에 대한 n개의 출력), 복수행 함수(n개의 입력에 대한 1개의 출력)

 

-- 단일행함수 - 문자
SELECT	*
FROM	employee;

SELECT	emp_name,
	LENGTH(emp_name) -- select절에 함수 정의. 레코드에 변화 없으므로 당일행함수
FROM	employee;

SELECT	emp_name,
	LENGTH(emp_name),
	CHAR_LENGTH(emp_name)
FROM	employee;

SELECT	CHAR(65);

 

-- 문자열 연결함수(concat)
SELECT	concat(emp_name, '님')
FROM	employee;

-- 문자열(대문자, 소문자) - lower(), upper()

SELECT	LOWER('HELLO'), UPPER('hello');

-- LPAD(문자열, 길이, [원하는 문자]), RPAD() : 자릿수를 고정하여 빈 공간을 원하는 문자로 채우는 함수 -> 정렬에서 자주 사용

SELECT	email AS `원본데이터`,
	LENGTH(email) AS `원본길이`,
	LPAD(email, 20, '*') AS `우측정렬`,
	RPAD(email, 20, '*') AS `좌측정렬`,
	LENGTH(LPAD(email, 20, '*')) AS `중첩함수` -- 함수는 함수를 중첩할 수 있다
FROM	employee;

-- 인덱스를 이용해서 특정 위치의 문자를 찾을 수 있는 함수 : elt(인덱스번호, 문자열....)
-- instr('문자열', '부분문자열')

SELECT	ELT(2 , '1', '2', '3', '4'); -- 인덱스 번호는 1번부터 시작한다 e.g.) 1번인덱스의 값: 1, 2번인덱스의 값: 2 .....

SELECT	email,
	INSTR(email, '.')
FROM	employee;

SELECT	email,
	INSTR(email, 'c') -- 부분문자열의 대소문자를 구별하지 않는다. 
FROM	employee;

/*
SELECT	email,
	INSTR(email, 'c', -1) -- error. 역방향 검색을 지원하지 않는다.
FROM	employee;
*/

 

-- find_in_set(찾을 문자, 문자열 리스트) : 찾는 문자의 인덱스 반환

SELECT	FIND_IN_SET('둘', '하나,둘,셋,넷');

-- 문자열에서 특정 부분 추출 left(), right()

SELECT	LEFT('abcdefg', 3), RIGHT('abcdefg', 3);

-- 문자열 함수 LTRIM/RTRIM, TRIM
-- 제거(패턴을 제거하는 의미는 아님)
-- 기본 공백을 제거. 문자 사이에 껴있는 공백은 제거되지 않음
-- 고정길이 자료형에서 사용되지 않은 데이터공간을 제거해줄 때 주로 사용
-- 공백이 아닌 특정 문자를 제거
-- TRIM(BOTH, LEADING, TRAILING) 디폴트값은 BOTH

SELECT	LTRIM('    tech'), RTRIM('tech    '), TRIM('    tech    '); -- 공백제거 후 왼쪽정렬한 결과가 출력된다

SELECT	TRIM(BOTH '123' FROM '123tech123'),
	TRIM(LEADING '123' FROM '123tech123'),
	TRIM(TRAILING '123' FROM '123tech123'),
	TRIM(BOTH '213' FROM '123tech123'); -- TRIM()은 패턴을 제거하는 의미가 아니기 때문에 '213'을 하나의 문자로 보고 '123tech123'에는 없는 문자이므로 제거 x

-- 문자열 반복
SELECT	repeat('encore', 3);

-- 문자열 치환
SELECT	REPLACE('오늘은 즐거운 불금 즐겨보자', '즐겨보자', '놀아보자');

 

-- 중요
-- substring(문자열, 시작위치, 길이) or (문자열 from 시작위치 for 길이)
-- substring_index(문자열, 구분자, 횟수)

SELECT	SUBSTRING('this is a encore', 6, 2),
	SUBSTRING('this is a encore' FROM 6 FOR 2);

SELECT	SUBSTRING_INDEX('www.encore.com', '.', 2), -- 두번째에 위치한 .을 기준으로 split한 데이터를 리턴
	SUBSTRING_INDEX('www.encore.com', '.', -1);

SELECT	*
FROM	employee;

-- 요구사항1) 메일 아이디만 추출
-- 요구사항2) 입사년도만 추출
-- 요구사항3) 주민번호 앞 6자리만 추출
-- 요구사항4) 입사일의 출력 포맷을 xxxx년xx월xx일 형식으로 출력

SELECT	SUBSTRING(email, 1, INSTR(email, '@') - 1)
FROM	employee;

SELECT	SUBSTRING(hire_date, 1, 4)
FROM	employee;

SELECT	SUBSTRING(emp_no, 1, 6)
FROM	employee;

SELECT	SUBSTRING_INDEX(emp_no, '-', 1)
FROM	employee;

SELECT	CONCAT(SUBSTRING(hire_date, 1, 4), '년', SUBSTRING(hire_date, 6, 2), '월', SUBSTRING(hire_date, 9, 2), '일')
FROM	employee;

 

-- 데이터 형식과 형변환
USE sqldb;

-- cast(표현식 as 데이터 형식 [길이] ), 묵시적 형변환
-- 다른 자료형으로 형변환을 시키는 경우 데이터의 길이도 함께 표시해줘야한다
SELECT	*
FROM	usertbl;

SELECT	*
FROM	buytbl;

-- 평균(avg) 구매 개수를 확인하고 싶다면?

SELECT	AVG(amount) AS `평균 구매 개수`
FROM	buytbl;

SELECT	CAST(AVG(amount) AS INT) AS `평균 구매 개수` -- 반올림한 결과 반환. int == integer == signed integer
FROM	buytbl;

-- 구매번호, 총 금액(cast를 사용하고), 구매액(cast를 사용하지 않고)
SELECT	num,
	CAST(price AS VARCHAR(10)) AS `금액`,
	CAST(amount AS VARCHAR(10)) AS `수량`,
	CONCAT(CAST(price AS VARCHAR(10)), ' * ', CAST(amount AS VARCHAR(10)), ' = ') AS `총 금액`,
	price * amount AS `구매액`
FROM	buytbl;

SELECT	'100' + '100'; -- 묵시적 형변환
SELECT	CAST('100' AS INT) + CAST('100' AS INT); -- 문자열을 정수로 형변환할때는 데이터의 길이를 지정하면 안된다

USE encore;

SELECT	*
FROM	employee;

-- 사원테이블에서 사원의 주민번호 앞 6자리와 뒤 7자리를 나누어서 더하기한 결과를 출력한다면?

SELECT	LEFT(emp_no, 6),
	RIGHT(emp_no, 7),
	LEFT(emp_no, 6) + RIGHT(emp_no, 7),
	CAST(LEFT(emp_no, 6) AS INT) + CAST(RIGHT(emp_no, 7) AS INT)
FROM	employee;

 

-- 숫자함수
SELECT	ABS(-100), -- absolute 절댓값
	CEILING(4.7), -- 올림
	FLOOR(3.2), -- 내림
	ROUND(5.5), -- 반올림
	TRUNCATE(123.1234567, 4); -- 절삭. 나머지는 잘라버림(반올림x). 자릿수가 양수면 실수자릿수, 음수면 정수자릿수

-- 날짜함수(Date)
SELECT	NOW(),
	SYSDATE(),
	CURDATE(),
	CURTIME();

-- 날짜 연산을 하고 싶다면? adddate(date, interval expr type), date_add()
-- 사원테이블에서 입사일을 기준으로 근무한지 20년이 되는 일자를 조회하고 싶다면?
SELECT	*
FROM	employee;

SELECT	ADDDATE(CURDATE(), 2),
	ADDDATE(CURDATE(), INTERVAL 2 DAY),
	ADDDATE(CURDATE(), INTERVAL 2 MONTH),
	ADDDATE(CURDATE(), INTERVAL 2 YEAR);
			
SELECT	DATE_ADD(hire_date, INTERVAL 20 YEAR)
FROM	employee;

-- 오늘 날짜를 기준으로 근속년수가 25년 이상인 사원의 정보를 검색한다면?
-- datediff(날짜, 날짜)
SELECT	*
FROM	employee
WHERE	DATEDIFF(CURDATE(), hire_date) >= 25 * 365;

SELECT	emp_name,
	hire_date,
	ROUND(DATEDIFF(CURDATE(), hire_date) / 365) AS `근속년수`
FROM	employee
WHERE	DATEDIFF(CURDATE(), hire_date) >= 25 * 365;
 
CREATE TABLE test_coupon_tbl(
	str_date DATE,
	end_date DATE
);

SELECT	*
FROM	test_coupon_tbl;

INSERT INTO test_coupon_tbl VALUES(NOW(), ADDDATE(NOW(), INTERVAL 60 DAY));

-- subdate(날짜, 차이), subtime()
SELECT	SUBDATE(CURDATE(), INTERVAL 30 DAY),
	SUBDATE(CURDATE(), INTERVAL 1 MONTH);

-- year(날짜), month(날짜), day(날짜)
-- hour(), minute(), second()

SELECT	cast(YEAR(hire_date) AS CHAR) '년',
	cast(MONTH(hire_date) AS CHAR) '월',
	cast(DAY(hire_date) AS CHAR) '일'
FROM	employee;

 

-- SQL 흐름을 제어하는 함수들 if ~ else

SELECT	if(100 > 200, '참', '거짓');

-- case 값 when 비교 then 참 else 거짓 end : ANSI 표준
-- case when 조건 then 결과 

SELECT	CASE 10
		when 1 then '일'
		when 10 then '십'
		ELSE '내가 원하는 값이 아님'
	END AS `구분`;

-- 부서번호가 50번인 사원의 이름, 주민번호, 성별을 출력한다면?
SELECT	emp_name,
	emp_no,
	if(SUBSTRING(emp_no, 8, 1) = '1', '남', '여') AS 'gender'
FROM	employee
WHERE	dept_id = '50';

-- 사원테이블에서 남자사원만 검색해보기
SELECT	*
FROM	employee
WHERE	SUBSTRING(emp_no, 8, 1) = '1';

-- 사원테이블에서 남자사원의 이름, 주민번호, 성별을 출력한다면?
SELECT	emp_name,
	emp_no,
	'남자' AS 'gender'
FROM	employee
WHERE	SUBSTRING(emp_no, 8, 1) = '1';

-- 사원테이블에서 직급이 J4인 사원의 사원번호, 이름, 사수번호를 검색해보자
-- 추가적인 요구사항으로 사수번호가 없는 사원의 mgr_id 컬럼에 '관리자'라는 값을 삽입한다면?

SELECT	emp_id,
	emp_name,
	case
		when mgr_id IS NOT NULL then mgr_id
		when null IS NULL then '관리자'
	END AS `관리자`
FROM 	employee
WHERE	JOB_ID = 'J4';


-- 사원의 급여등급을 나눠보고 싶다
-- 300만원 이하면 초급, 400만원 이하면 중급, 이상이면 고급
-- 사원번호, 사원이름, 급여, 급여등급을 출력한다면?

SELECT	emp_id,
	emp_name,
	salary,
	case
		when salary <= 3000000 then '초급'
		when salary <= 4000000 then '중급'
		else '고급'
	END AS '급여등급'
FROM	employee;

 

-- 복수행(그룹) 함수
-- 여러 행의 결과를 입력으로 하여 하나의 결과를 반환하는 함수. n개 입력에 대한 하나의 결과를 리턴하는 함수

-- 사원수 확인 : count()

SELECT	COUNT(*)
FROM	employee;

SELECT	COUNT(IFNULL(bonus_pct, 0))
FROM	employee;

-- 최저급여 확인 : min()
SELECT	MIN(salary), emp_name -- 잘못된 결과 출력
FROM	employee;

-- 급여총합, 평균
SELECT	SUM(salary),
	AVG(salary)
FROM	employee;