방송대 데이터베이스시스템 - 5강. SQL (2)

---

1. 데이터 삽입, 수정, 삭제

2. 데이터 검색 (1)

---

1. 데이터 삽입, 수정, 삭제

데이터 조작 언어

DML : Data Manipulation Language

정의된 테이블에 레코드를 삽입, 수정, 삭제 및 검색하는데 사용되는 명령어의 집합

명령어의 종류

INSERT	테이블 스키마에 적합한 레코드를 삽입
UPDATE	테이블에서 조건을 만족하는 특정 레코드의 컬럼값을 수정
DELETE	테이블에 조건을 만족하는 특정 레코드를 삭제
SELECT	조건을 만족하는 레코드를 테이블에서 검색

---

INSERT문

테이블에 새로운 레코드를 삽입하는 명령문

- 테이블에 새로운 레코드를 삽입

- 모든 속성 또는 부분 속성에 대한 속성값을 삽입

값이 나열된 순서가 컬럼이 정의된 순서와 같은 경우	값이 나열된 순서가 컬럼이 정의된 순서와 다른 경우

컬럼값이 문자나 날짜인 경우 값의 양끝에 작은따옴표를 붙임

---

UPDATE문

조건을 만족하는 레코드의 특정 컬럼값을 수정

UPDATE문에서 WHERE 절을 사용하지 않으면 테이블에 존재하는 모든 레코드를 대상으로 지정된 컬럼의 값을 수정함.

→ MySQL은 SAFE UPDATES 모드를 활성화하여 이러한 SQL문을 실행을 거부함.

→ UPDATE문 실행 전 WHERE 절의 유무를 반드시 확인해야 함.

---

DELETE문

조건에 일치하는 레코드 집합을 테이블에서 삭제할 때 사용하는 명령어

DELETE문에서 WHERE 절이 생략되면 테이블의 모든 레코드가 삭제됨.

→ MySQL은 SAFE UPDATES 모드를 활성화하여 이러한 SQL문을 실행을 거부함.

만약 위 사례에서 '행정학개론'이라는 과목 레코드가 과목을 개설한 '행정학과' 교수의 교수번호를 참조하고 있으면 실행오류 발생함.

→ 교수 레코드를 삭제하기 전에 해당 교수번호를 참조하고 있는 과목 레코드부터 우선 삭제해야함.

→ DELETE문 오류 발생시 우선적으로 기본키-외래키 참조 관계 여부를 판단하는 것이 좋음.

---

SAFE UPDATES 모드

WHERE 절이 없는 UPDATE/DELETE 문은 테이블의 전체 레코드를 변경/삭제함.

의도하지 않은 데이터 변경/삭제 방지를 위해 MySQL은 SAFE UPDATES 모드를 지원함.

기본키가 아닌 컬럼을 대상으로 수정/삭제 조건을 명시할 경우 실행 여부를 결정함.

워크벤치 옵션	[Edit 메뉴] → [SQL Editor 탭]의 화면에서 콤보 박스를 선택/취소
SQL 쿼리패널 구문	· 비활성화: SET SQL_SAFE_UPDATES = 0 · 활성화: SET SQL_SAFE_UPDATES = 1

---

2. 데이터 검색 (1)

SELECT문 구문형식

한 개 이상의 테이블에서 주어진 조건에 만족하는 레코드를 출력하는 명령문

관계 대수의 셀렉션, 프로젝션, 조인, 카디션 프로덕트 연산자의 기능을 모두 포함하는 명령문

필수적 절인 SELECT 절과 부가적인 목적으로 사용할 수 있는 여러 절을 혼합하여 검색 기능을 구체화

SELECT 절	결과에 포함되는 컬럼을 지정  관계대수의 프로젝션 연산자와 같음
FROM 절	질의를 적용할 테이블을 지정
ON/WHERE 절	조인 조건/검색할 레코드 조건을 지정
GROUP BY 절	레코드를 그룹화하기 위한 그룹 조건을 지정 이를 위해 SELECT절에 집계함수 사용(COUNT, MIN, MAX, SUM, AVG)
HAVING 절	GROUP BY 절이 적용된 결과에 대한 조건을 지정 (GROUP BY가 존재해야 사용 가능)
ORDER BY 절	검색 결과의 정렬 기준을 지정

· ON 절 : 조인에 대한 조건

· WHERE 절 : 레코드에 대한 조건

· HAVING 절 : GROUP BY 절에 의한 그룹에 대한 조건문

---

단순질의문

레코드를 제한하지 않고 전체 테이블을 검색하는 SELECT문으로 WHERE 절이 없는 질의문

		교수 테이블의 전체 레코드를 출력
중복을 허용 O	중복을 허용 X

---

조건질의문

산술연산식, 함수 등을 사용하여 표현한 조건을 WHERE 절에 기술하여 조건을 만족하는 레코드만 검색하는 SELECT문

산술연산자, 비교연산자, 논리연산자

WHERE 절은 UPDATE, DELETE 문에서도 동일하게 적용

 

산술연산자

SELECT 절 또는 WHERE 절에 사용되어 컬럼값 또는 상수와의 산술 계산을 위한 연산자

DIV	컬럼명 DIV 상수(또는 컬럼명)	정수 나눗셈
/	컬럼명 / 상수(또는 컬럼명)	나눗셈 연산자
-	컬럼명 - 상수(또는 컬럼명)	뺄셈 연산자
%, MOD	컬럼명 % 상수(또는 컬럼명)	나머지 연산자
+	컬럼명 + 상수(또는 컬럼명)	덧셈 연산자
*	컬럼명 * 상수(또는 컬럼명)	곱셈 연산자

 

비교연산자

컬럼값과 상수 또는 컬럼값과 다른 컬럼값과의 크기를 비교하는 연산자

숫자, 문자, 날짜 및 시간에도 적용 가능함.

· 문자 : 사전순서 (ex. 성별 = '남')

· 날짜 및 시간 : 과거에서 미래 순으로 순서가 결정 (ex. 생년월일 < '2000-1-1')

=	컬럼_이름 = X	컬럼값과 X와 같은
<	컬럼_이름 < X	컬럼값이 X보다 작은
<=	컬럼_이름 <= X	컬럼값이 X보다 작거나 같은
>	컬럼_이름 > X	컬럼값이 X보다 큰
>=	컬럼_이름 >= X	컬럼값이 X보다 크거나 같은
!= or <>	컬럼_이름 <> X	컬럼값이 X와 같지 않은

 

논리연산자

두 개 이상의 조건이 기술되는 질의문에서 조건식 간의 관계를 정의하는 연산자

AND or &&	조건1 AND 조건2	조건1과 조건2를 동시에 만족하는
OR or ||	조건1 OR 조건2	조건1과 조건2를 적어도 하나 만족하는
NOT or !	NOT 조건1	조건1이 참이 아닌

 

데이터 정렬

ORDER BY 절을 사용

검색 결과를 특정 컬럼에 대해 오름차순(ASC) 또는 내림차순(DESC)으로 정렬

---

집합 연산

UNION(합집합), INTERSECT(교집합), EXCEPT(차집합)

집합 연산을 적용할 때에는 반드시 두 SELECT문의 결과 스키마가 동일해야함(컬럼개수, 데이터타입 동일).

MySQL에서는 INTERSECT와 EXCEPT 연산을 지원하지 않음.

• UNION : 여러 SELECT문의 결과를 하나로 통합, 중복 레코드 X

• UNION ALL : 여러 SELECT문의 결과를 하나로 통합, 중복 레코드 O

(SELECT 교수번호
	FROM 과목
    WHERE 과목명 = '컴퓨터의 이해')
UNION
(SELECT 교수번호
	FROM 과목
    WHERE 과목명 = '데이터베이스 시스템')

---

특수연산자

범위 포함 여부, 부분 일치 여부, 포함 여부 등 관계형 데이터베이스에서만 사용되도록 고안된 연산자

BETWEEN	컬럼명 BETWEEN V1 AND V2	컬럼값이 V1 ~ V2 사이에 존재하는지 검사
LIKE	컬럼명 LIKE V1%	V1으로 시작하는 문자열 검사
	컬럼명 LIKE %V1	V1으로 끝나는 문자열 검사
	컬럼명 LIKE %V1%	V1이 문자열 내부에 존재하는지 검사
	컬럼명 LIKE V1_	V1뒤에 한 문자만 일치하는지 검사
IN	컬럼명 IN (V1, ..., Vn)	컬럼값이 V1, V2, ..., Vn 중 하나와 일치하는지 검사

 

기타 SELECT문

별칭(alias)	SQL문 내부에서 테이블이나 컬럼의 이름을 임시로 변경 AS는 생략 가능함.	변경전 이름 AS 변경후 이름 ex. 주소 AS 홈페이지주소
NULL	공백 문자열 (X), 숫자값 0(X) 아직 알 수 없는 값 (미정) '=' 대신 IS를 사용함.	ex. WHERE 이수학점 IS NULL; ex. WHERE 이수학점 IS NOT NULL;

---

함수

특정 목적을 수행하도록 사전에 정의된 연산 및 기능을 수행한 후 결과값을 반환하는 명령어 집합

상용 DBMS는 검색결과가 사용자에게 여러 형태로 사용되도록 여러 데이터 타입에 대한 다양한 함수를 제공 (MySQL 기준)

 

※ 함수의 세부적인 기능에 대해서는 출제하지 않음.

 

숫자함수

삼각함수, 상수, 올림과 버림, 난수 등의 숫자 데이터 타입에 적용할 수 있는 계산을 위한 함수

ABS()	ABS(X)	X의 절대값을 반환	ABS(-5) → 5
SIN(), COS() 등	SIN(X) 등	X의 삼각함수 값을 반환	SIN(O) → 0
CEILING() FLOOR()	CEILING(X) FLOOR(X)	X보다 크지 않은 최소의 정수 X보다 작지 않은 최대의 정수	CEILING(3.7) → 4 FLOOR(3.7) → 3
LN()	LN(X)	자연로그 InX의 값을 반환
PI()	PI()	원주율 값을 반환
POWER()	POWER(X, Y)	거듭제곱 X의 y제곱 값을 반환
RAND()	RAND()	0과 1 사이의 임의의 값을 반환
ROUND()	ROUND(X, Y)	X의 Y번째 소수점 위치의 수를 반올림한 값을 반환	ROUND(3.14159, 2) → 3.14
SQRT()	SQRT(X)	X의 제곱근의 값을 반환	SQRT(25) → 5
TRUNCATE()	TRUNCATE(X, Y)	X의 Y번째 소수점 이하를 버림한 값을 반환	TRUNCATE(3.14159, 2) → 3.14

 

반지름이 8.7인 원의 넓이와 (3^4) * e^(-2)의 절댓값을 출력하라.

SELECT 
    (PI() * POWER(8.7, 2)) AS 원의_넓이,
    ABS(POWER(3, 4) * EXP(-2)) AS 계산값_절댓값
FROM table;

 

모든 학생이 입학에서 졸업까지 8학기가 걸린다고 가정할 때, 각 학과의 학생이 매 학기마다 평균적으로 이수해야 하는 학점을 계산하여 학과이름과 함께 출력하라. 단, 소수점 이하의 값을 올림한다.

SELECT
    학과이름,
    CEILING(졸업학점 / 8.0) AS 평균이수학점
FROM 학과;

---

문자함수

문자열 조작 및 문자 형식 변환 등의 문자와 관련된 다양한 연산을 지원하는 함수

ASCII()	ASCII(X)	X의 맨 왼쪽 문자의 ASCII 코드 값 반환
CHAR_LENGTH()	CHAR_LENGTH(X)	X의 글자수를 반환 (한글도 1글자로 계산)
CONCAT()	CONCAT(X, Y)	X와 Y 문자열을 결합
LOWER()	LOWER(X)	X의 문자를 모두 소문자로 변환
UPPER()	UPPER(X)	X의 문자를 모두 대문자로 변환
SUBSTRING()	SUBSTRING(X, A, B)	X의 A번째 문자부터 B개의 문자열을 반환
INSERT()	INSERT(S1, x, y, S2)	문자열 S1의 x번째 문자부터 y길이만큼 문자열 S2로 대체한 값을 반환
REPLACE()	REPLACE(S1, S2, S3)	문자열 S1 내의 S2를 S3으로 대체한 값을 반환
TRIM()/LTRIM()/RTRIM()	TRIM(X)/LTRIM(X)/RTRIM(X)	양쪽/왼쪽/오른쪽 공백 문자를 제거하고 반환

 

학생의 학생번호, 학생이름, 성별, 생년월일을 출력하라. 단, 학생번호는 앞 6자리만 출력하고 성별 뒤에는 '성'을 붙여라.

SELECT SUBSTRING(학생번호, 1, 6), 학생이름, CONCAT(성별, '성')
FROM 학생

 

이름과 성을 컬럼으로 가지고 있는 Name 테이블에서 이름의 첫 글자와 성을 결합한 새로운 Full_Name 열을 생성하라.

SELECT CONCAT(SUBSTRING(이름, 1, 1), 성) AS Full_Name
FROM Name;

 

문자열 'Hello World!'의 6번째 위치에 문자열 'beautiful '을 삽입하라.

SELECT 
	INSERT('Hello World!', 6, LENGTH('beautiful '), 'beautiful ') AS 결과;

→ Hello beautiful World!

 

문자열 'Hello World!'에서 'World'를 'Universe'로 대체하라.

SELECT REPLACE('Hello World!', 'World', 'Universe') AS 결과;

→ Hello Universe!

---

날짜 및 시간함수

날짜 및 시간 데이터 타입에 적용되어 산술 연산 및 시간 형 변환 등의 조작을 위한 함수

ADDDATE()	ADDDATE(X, INTERVAL Y 단위)	X에 Y 단위의 시간을 더한 날짜를 반환
ADDTIME()	ADDTIME(X, Y)	X와 Y 시각을 더한 날짜를 반환
CURDATE(), CURRENT_DATE()	CURDATE(), CURRENT_DATE()	현재 날짜를 반환
CURTIME(), CURRENT_TIME()	CURTIME(), CURRENT_TIME()	현재 시간을 반환
DATE()	DATE(X)	X에서 날짜(년, 월, 일)을 반환
YEAR()	YEAR(X)	X에서 년 값을 반환

 

학생의 학생이름과 생년월일을 출력하라. 단, 생년월일은 태어난 달에 '월'을 붙여서 출력한다.

SELECT 학생이름, CONCAT(MONTH(생년월일), '월')
	FROM 학생

 

2022년 9월 15일 14시 30분 30초를 기준으로 한 달(30일) 이내에 수강신청된 수강 데이터의 학생번호, 과목코드, 경과시간을 출력하라. 단, 경과시간은 내림차순으로 수강 레코드를 정렬하여 출력한다.

SELECT 
    학생번호,
    과목코드,
    TIMESTAMPDIFF(DAY, 신청시각, '2022-9-5 14:30:30') AS 경과시간
FROM 수강
WHERE TIMESTAMPDIFF(DAY, 신청시각, '2022-9-5 14:30:30')
	BETWEEN 0 AND 30
ORDER BY 경과시간 DESC

SELECT 
    학생번호,
    과목코드,
    DATEDIFF('2022-09-05 14:30:30', 신청시각) AS 경과시간
FROM 수강
WHERE DATEDIFF('2022-09-05 14:30:30', 신청시각) BETWEEN 0 AND 30
ORDER BY 경과시간 DESC;