20250331_DB4

복습

sql join

색칠된 영역에 정보가 존재하는 값들만 출력(입력되지 않은 목록은 반영x)

left join, right join, inner join,union

step1
CREATE DATABASE day_4_3;

use day_4_3;
------------------------------------------------------------------------------------
*step2표 만들기
CREATE TABLE customer(customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
address varchar(100), 
phone VARCHAR(15),
age int
);

CREATE TABLE products(product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
product VARCHAR(30),
brand INT,
price BIGINT
);

step3 표 데이터 수정
------------------------------------------------------------------------------------
--insert into : 데이터 입력
INSERT INTO table_name(column1, column2, column3, …)
VALUES (value1, value2, value3, …);
값의 길이가 열의 개수와 일치하면 column1은 생략가능
INSERT INTO table_name 
VALUES (value1, value2, value3, …);
------------------------------------------------------------------------------------
--select : 데이터 읽기
SELECT column1, column2, …; 
FROM table_name
WHERE condition; (조건절 없이 사용하면 모든 값)
모든 열을 불러오려면 *사용
SELECT * FROM table_name;
------------------------------------------------------------------------------------
--delete : 데이터 삭제
표에 있는 모든 열을 삭제한다
DELETE FROM table_name;
표에 있는 특정 값들만
DELETE FROM table_name WHERE condition;
------------------------------------------------------------------------------------
--update : 데이터 수정
UPDATE table_name
SET column1 = value1, column2 = value2, … 
WHERE condition;
------------------------------------------------------------------------------------
where : 데이터 필터
where condition;
where A > B;
condition : 
<>,!=,|,^ : 같지 않다
between A and B : A값과 B값 사이
Like : 특정 문자열이 들어간 값
IN : 2개 이상의 조건 중 하나라도 만족하는 값
AND : 모든 조건을 만족하는 값
OR : 하나라도 만족하는 값
IS NULL/IS NOT NULL : 컬럼이 null인 값, null이 아닌 값들 불러오기
NOT : 조건이 아닌 것
------------------------------------------------------------------------------------
실습 문제
market테이블에서 작년에 물건을 올린 seller들 중 만원 이상의 물건을 올린 seller들을 구해라
select seller from market where reg_date < '2025.01.01 00:00:00' AND price >= 10000;

market테이블에서 '이'씨성을 가진 seller들의 물건 혹은 우로 끝나는 이름을 가진 seller들의 물건을 구해라;
select product form market where seller like '이%' or seller like '우%'

3월에 등록된 물품 중 천원과 십 만원 사이이고 두 글자인 물건들이거나, 이십 만원 이상의 레코드를 찾으시오.
select product from market where reg_date between '2023-03-01 00:00:00' and '2023-03-31 23:59:59' and ((price between 1000 and 200000)

2월에 올라온 물건들 중 10개 미만의 제품들은 모두 2개씩 팔린 걸 업데이트 하시오.
update market set amount - 2 where reg_date between '2023-02-01 00:00:00' and '2023-02-28 23:59:59' and amount < 10;

홍씨의 성을 가진 seller의 reg_date이 잘못 입력되어서 2022.02.22 22:22:22 으로 업데이트 하시오.
update market set reg_date = '2022-02-22 22:22:22' where seller like '홍%';

올해에 올라온 물건들 중 삼십만원 이상의 물건은 5%을 세금을 추가해서 Table을 업데이트 하시오.
update market set price = price * 1.05 where reg_date > '2023-01-01 00:00:00' and price >= 300000;
-------------------------------------------------------------------------------------
테이블 스키마

ALTER TABLE table_name
ADD column_name datatype;  --  새 column 추가
-------	       
DROP column_name;   --  column 삭제
-------     
RENAME column_name to new_name;  -- column 이름 변경
-------	        
MODIFY COLUMN column_name new_datatype;  --  column datatype 변경
-------
예제 1) cash (BIGINT)추가
ALTER TABLE account ADD cash BIGINT;
예제 2) manner_point datatype 교체
ALTER TABLE account MODIFY COLUMN manner_point FLOAT;
-------------------------------------------------------------------------------------
외래 키(상속 개념)
테이블을 새로 만들 때:
CREATE TABLE table_name(
	column1 datatype,
	column2 datatype,
		…
	FOREIGN KEY (column#) REFERENCES parent_table(key)
);

이미 만들어진 테이블을 수정할 때:
ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES parent_table(key);

On Delete
Cascade	부모 데이터 삭제 시 자식 데이터도 삭제
Set null	부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Null로 업데이트
Set default	부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트
Restrict	자식 테이블이 참조하고 있을 경우, 데이터 삭제 불가
No Action	Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.
On Update
Cascade	부모 데이터 업데이트 시 자식 데이터도 업데이트
Set null	부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Null로 업데이트
Set default	부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트
Restrict	자식 테이블이 참조하고 있을 경우, 업데이트 불가
No Action	Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.

---

쿼리 구문 안에서 동작하지 않으면 오류발생?

내장 함수

구분	함수
숫자 함수	ABS, CEIL, COS, EXP, FLOOR, LN, LOG, MOD, POWER, RAND, ROUND, SIGN, TRUNCATE
문자 함수 (문자 반환)	CHAR, CONCAT, LEFT, RIGHT, LOWER, UPPER, LPAD, RPAD, LTRIM, RTRIM, REPLACE, REVERSE, RIGHT, SUBSTR, TRIM
문자 함수 (숫자 반환)	ASCII, INSTR, LENGTH
날짜·시간 함수	ADDDATE, CURRENT_DATE, DATE, DATEDIFF, DAYNAME, LAST_DAY, SYSDATE, TIME
변환 함수	CAST, CONVERT, DATE_FORMAT, STR_TO_DATE
정보 함수	DATABASE, SCHEMA, ROW_COUNT, USER, VERSION
NULL 관련 함수	COALESCE, ISNULL, IFNULL, NULLIF
집계 함수	AVG, COUNT, MAX, MIN, STD, STDDEV, SUM
윈도우 함수 (혹은 분석 함수)	CUME_DIST, DENSE_RANK, FIRST_VALUE, LAST_VALUE, LEAD, NTILE, RANK, ROW_NUMBER

숫자함수

함수	설명	함수	설명
ABS(숫자)	숫자의 절댓값을 반환 ABS(-4.5) => 4.5	SIN | COS | TAN(숫자)	숫자의 삼각함수 값을 계산 TAN(1.75) => -5.52037992250933
CEIL(숫자)	숫자보다 크거나 같은 최소의 정수 반환 CEIL(4.1) => 5	EXP(숫자)	숫자의 e의 거듭제곰을 반환 EXP(1) => 2.718281828459045
FLOOR(숫자)	숫자보다 작거나 같은 최대의 정수 반환 FLOOR(4.1) => 4	LN(숫자)	숫자의 자연로그 값을 반환 LN(2) => 0.6931471805599453
ROUND(숫자, m)	숫자의 반올림 반환, m은 반올림 기준 자릿수 ROUND(5.36, 1) => 5.40	MOD(숫자1, 숫자2)	숫자1을 숫자2로 나누어 나머지를 반환 MOD(18,4) => 2
LOG(n, 숫자)	숫자의 자연로그 값을 반환 LOG(10) => 2.30259	RAND()	0과 1 사이의 무작위 숫자를 반환 (0 ≤ x < 1) RAND() => 0.6062292536778648
POWER(숫자, n)	숫자의 n제곱 값을 반환 POWER(2, 3) => 8	TRUNCATE(숫자, m)	숫자를 m소수점 자리에서 잘라서 버림 (m Default 값은 0) TRUNCATE(135.375, 2) => 135.37
SQRT(숫자)	숫자의 제곱근 값을 반환(숫자는 양수) SQRT(9.0) => 3.0	SIGN(숫자)	숫자가 음수면 -1, 0이면 0, 양수면 1을 반환 SIGN(3.45) => 1

문자함수

반환 구분	함수	설명
문자값 반환 함수 s : 문자열 c : 문자 n : 정수 k : 정수	CONCAT(s1,s2)	두 문자열을 연결, CONCAT('마당', '서점') => '마당 서점'
	LOWER(s) | UPPER(s)	대상 문자열을 모두 소문자 | 대문자로 변환 LOWER('MR. SCOTT') => 'mr. scott'|   UPPER('mr. scott') => 'MR. SCOTT'
	LPAD(s,n,c) | RPAD(s,n,c)	대상 문자열의 왼쪽 | 오른쪽부터 지정한 자리수까지 지정한 문자로 채움 LPAD('Page 1', 10, '*') => '****Page 1
	REPLACE(s1,s2,s3)	대상 문자열의 지정한 문자를 원하는 문자로 변경 REPLACE('JACK & JUE', 'J', 'BL') => 'BLACK & BLUE'
	CHAR(ASCII)	대상 아스키 코드 값을 알파벳 문자를 반환 CHAR(68) => 'D'
	SUBSTR(s,n,k)	대상 문자열의 지정된 자리에서부터 지정된 길이만큼 잘라서 반환 SUBSTR('ABCDEFG', 3, 4) => 'CDEF'
	TRIM(c FROM s)	대상 문자열의 양쪽에서 지정된 문자를 삭제(문자열만 넣으면 기본값으로 공백 제거) TRIM('='FROM '==BROWNING==') => 'BROWNING'
	LTRIM(s) | RTRIM(s)	대상 문자열의 왼쪽 | 오른쪽에서 공백 삭제
	LEFT(s, n) | RIGHT(s, n)	n 문자 수만큼 대상 문자열의 왼쪽 | 오른쪽을 반환 LEFT('SQL tutorial', 3) => 'SQL'
	REVERSE(s)	대상 문자열을 역분으로 반환 REVERSE('SQL') => 'LQS'
숫자값 반환 함수	ASCII(c)	대상 알파벳 문자의 아스키 코드 값을 반환 ASCII('D') => 68
	LENGTH(s)	대상 문자열의 Byte 반환, 알파벳 1byte, 한글 3byte (UTF8) LENGTH('CANDIDE') => 7
	CHAR_LENGTH(s)	문자열의 문자 수를 반환, CHAR_LENGTH('데이터') => 3
	INSTR(s1, s2)	주어진 문자열 s1에서 s2문자열이 처음 나온 위치값(index)을 반환

날짜함수

함수	반환형	설명
STR_TO_DATE(string, format)	DATE	문자열(STRING) 데이터를 날자형(DATE)으로 반환 STR_TO_DATE('2019-02-14', '%Y-%m-%d') => 2019-02-14
DATE_FORMAT(date, format)	STRING	날짜형(DATE) 데이터를 문자열(VARCHAR)로 반환 DATE_FORMAT('2019-02-14', '%Y-%m-%d') => '2019-02-14'
ADDDATE(date, interval)	DATE	DATE 형의 날짜에서 INTERVAL 지정한 시간만큼 더함 ADDDATE('2019-02-14', INTERVAL 10 DAY) => 2019-02-24
DATE(date)	DATE	DATE 형의 날짜 부분을 반환 SELECT DATE('2003-12-31 01:02:03'); => 2003-12-31
DATEDIFF(date1, date2)	INTEGER	DATE 형의 date1 – date2 날짜 차이를 반환 SELECT DATEDIFF('2019-02-14', '2019-02-04') => 10
(시험문제)SYSDATE	DATE	DBMS 시스템상의 오늘 날짜를 반환하는 함수 SYSDATE() => 2018-06-30 21:47:01

인자	설명
%w	요일 순서(0~6, Sunday=0)
%W	요일(Sunday~Saturday)
%a	요일의 약자(Sun~Sat)
%d	1달 중 날짜(00~31)
%j	1년 중 날짜(001~366)
%h	12시간(01~12)
%H	24시간(00~23)
%i	분(0~59)
%m	월 순서(01~12, January=01)
%b	월 이름 약어(Jan~Dec)
%M	월 이름(January~December)
%s	초(0~59)
%Y	4자리 연도
%y	4자리 연도의 마지막 2 자리

실습

기본코드

CREATE DATABASE day_5;

USE day_5;

CREATE TABLE customer(customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(10),
address VARCHAR(100), 
phone VARCHAR(15),
age INT
);

INSERT INTO customer(name, address, phone, age)
	VALUES ('손흥민', '영국 런던', '010-5001-0001', 30);
INSERT INTO customer(name, address, phone, age)
	VALUES ('유재석', '대한민국 서울', '010-6001-0001', -50);
INSERT INTO customer(name, address, phone, age)
	VALUES ('BTS 진', '대한민국 강원도', '010-7001-0001', 30);
INSERT INTO customer(name, address, phone, age)
	VALUES ('김하성', '미국 샌디에고', '010-8001-0001', 27);
INSERT INTO customer(name, address, age) VALUES ('김영진', '대한민국 서울', -25);

CREATE TABLE products(product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product VARCHAR(100),
brand VARCHAR(100),
price INT
);

INSERT INTO products(product, brand, price) VALUES ('양말', '아디다스', 7000);
INSERT INTO products(product, brand, price) VALUES ('긴바지', '무신사', 13000);
INSERT INTO products(product, brand, price) VALUES ('농구공', '나이키', 22000);
INSERT INTO products(product, brand, price) VALUES ('축구화', '나이키', 35000);
INSERT INTO products(product, brand, price) VALUES ('모자', '아디다스', 8000);
INSERT INTO products(product, brand, price) VALUES ('티셔츠', '아디다스', 6000);
INSERT INTO products(product, brand, price) VALUES ('와이셔츠', '스파오', 20000);
INSERT INTO products(product, brand, price) VALUES ('후드티', '스파오', 13000);
INSERT INTO products(product, brand, price) VALUES ('팬티', '캘빈클라인', 7500);
INSERT INTO products(product, brand, price) VALUES ('보온병', '노스페이스', 13000);

CREATE TABLE orders(order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
product_id INT,
sale_price INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (1, 1, 6000, '2023-03-01');
INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (1, 3, 21000, '2022-12-03');
INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (2, 5, 8000, '2022-12-03');
INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (3, 6, 6000, '2023-03-04');
INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (4, 7, 20000, '2023-03-05');
INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (1, 2, 12000, '2023-03-11');
INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (4, 8, 13000, '2023-02-26');
INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (3, 10, 12000, '2023-01-08');
INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (2, 10, 7000, '2023-03-09');
INSERT INTO orders(customer_id, product_id, sale_price, order_date) VALUES (3, 8, 13000, '2023-02-10');

실습1

--제시문--
customer 테이블에 다음 password 칼럼을 추가해라
password 칼럼은 VARCHAR(20)의 datatype,
FLOOR, RAND 를 이용한 랜덤한 수 지정


USE day_5;
/*SET SQL_SAFE_UPDATES = 0;*/

ALTER TABLE customer;
ADD password VARCHAR(20);

UPDATE customer
SET password = FLOOR(RAND() * 10000);

/*에러
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
where문이 들어가지 않으면 전체를 수정할 수 있는 우려가 있기에 자동으로 차단(safe update mode)
*/

실습2

--제시문--
customer 테이블에 음수로 잘못 입력된 나이를 수정해라

UPDATE customer
SET age = ABS(age);

실습3

--제시문--
orders 테이블에서 각 주문의 order_date를 년, 월, 일로 각각 나누어라 (문자 함수 이용)
그리고 orders 테이블을 customer과 합하여 주문 고객, 주문 년도, 월, 일을 구해라


SELECT name '주문 고객', SUBSTR(order_date, 1, 4) '주문 년도',
SUBSTR(order_date, 6, 2) '주문 월', SUBSTR(order_date, 9, 2) '주문 일'
FROM orders, customer
WHERE orders.customer_id = customer.customer_id;

실습4

--제시문--
orders 테이블에서 각 주문의 order_date를 yy-mm-dd 형식으로 표현해라 (날짜 함수 이용)

SELECT order_id, order_date, DATE_FORMAT(order_date, '%y-%m-%d') 'yy-mm-dd'
FROM orders;

실습5

--제시문--
주문한 후 10일 후에 배달이 된다면 각 주문이 언제 도착하는지 구해라
※HINT: ADDDATE 함수 이용하기

SELECT order_id, order_date, ADDDATE(order_date, INTERVAL 10 DAY) '배달일'
FROM orders;

NULL 함수

이론
NULL함수
IFNULL(속성, 값) : 속성 값이 NULL이면 값으로 대치하여 출력
NULL 값을 확인하는 방법 – IS NULL, IS NOT NULL
NULL 값을 찾을 때는 '='연산자가 아닌 ‘IS NULL'을 사용, 
NULL이 아닌 값을 찾을 때는 '＜＞’ 연산자가 아닌 'IS NOT NULL'을 사용

- 실습 -
customer 테이블의 전체를 불러오는데 NULL인 값이 있으면 '[속성] 없음'으로 표기해라

SELECT customer_id '고객 번호',
IFNULL(name, '이름 없음') '이름',
IFNULL(address, '주소 없음') '주소',
IFNULL(phone, '전화번호 없음') '전화번호'
FROM customer;
-----------------------------------------------------------------------------

 

---

부속질의(Subquery)

쿼리의 중첩 (괄호 내부 쿼리)

main query > subqyery

주질의(외부질의) > 부속질의(내부질의)

다른 테이블에서 필요한 값만 가져옴

명칭	위치	영문 및 동의어	설명
스칼라 부속질의	SELECT 절	scalar subquery	SELECT 절에서 사용되며 단일 값을 반환하기 때문에 스칼라 부속질의라고 함.
인라인 뷰	FROM 절	inline view, table subquery	FROM 절에서 결과를 뷰(view) 형태로 반환하기 때문에 인라인 뷰라고 함.
중첩질의	WHERE 절	nested subquery, predicate subquery	WHERE 절에 술어와 같이 사용되며 결과를 한정시키기 위해 사용됨. 상관 혹은 비상관 형태.

 

술어	연산자	반환 행	반환 열	상관
비교	=, ＞, ＜, ＞=, ＜=, ＜＞	단일	단일	가능
집합	IN, NOT IN	다중	단일	가능
한정(quantified)	ALL, SOME(ANY)	다중	단일	가능
존재	EXISTS, NOT EXISTS	다중	다중	필수

 

평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오

SELECT order_id, sale_price
FROM orders
WHERE sale_price <= (SELECT AVG(sale_price)
FROM orders);
--------------------------------------------------------------
각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 보이시오

SELECT 	order_id, customer_id, sale_price
FROM orders od
WHERE sale_price > (SELECT AVG(sale_price)
FROM orders so
WHERE od.customer_id=so.customer_id);

서술형 70

기본기능 + 내장함수

객관식 30

정보처리 산업기사 필기

 몇 문제 정도? 필기구로 샤프도 괜찮은지?