My SQL
집합 연산
SQL과 집합
-
데이터베이스에서는 테이블의 행이 요소에 해당한다. 행은 여러 개의 열로 구성되는 경우도 있으므로, 수치 상으로는 복수의 값이 존재한다. >> 집합의 요소라는 측면에서 보면 하나의 행이 곧 하나의 요소가 된다.
-
SELECT 명령을 실행하면 데이터베이스에 질의하며 그 결과 몇 개의 행이 반환된다. >> 이 반환된 결과 전체를 하나의 집합이라고 생각하면 된다.
합집합 UNION
집합을 서로 더한 것
-
중복된 요소는 하나로 표현
-
두 개의 SELECT 명령을 하나로 연계해 질의 결과를 얻을 수 있다.
- 각각의 SELECT 명령의 열의 내용은 서로 일치해야 한다.
-
SELECT 명령들은 UNION으로 묶을 때 나열 순서는 합집합의 결과에 영향을 주지 않는다.
CREATE TABLE sample71_a(a int);
INSERT INTO sample71_a VALUES(1);
INSERT INTO sample71_a VALUES(2);
INSERT INTO sample71_a VALUES(3);
CREATE TABLE sample71_b(b int);
INSERT INTO sample71_b VALUES(2);
INSERT INTO sample71_b VALUES(10);
INSERT INTO sample71_b VALUES(11);
SELECT * FROM sample71_a
UNION
SELECT * FROM sample71_b; # 두 개의 명령을 하나의 명령으로 합친다.
# 1, 2, 3, 10, 11-
A UNION B>> A, B는 SELECT명령으로 합친다. -
sample71_a와 sample71_b의 경우, 열 이름은 서로 다르지만 열 개수와 자료형이 서로 같기 때문에 일치한다고 할 수 있는 것
UNION으로 묶을 수 없는 구성
# 아래와 같은 열 구성이 다른 테이블을 UNION으로 묶을 수 없다.
SELECT * FROM sample71_a
UNION
SELECT * FROM sample71_b
UNION
SELECT * FROM sample31;
# 위처럼 * 를 쓰지않고, 열을 따로 지정하여 SELECT명령에서 집합의 요소가 될 데이터를 서로 맞춰주면 실행 가능
SELECT a FROM sample71_a
UNION
SELECT b FROM sample71_b
UNION
SELECT age FROM sample31; #age가 int로 자료형이 같아서 사용가UNION과 order by
- UNION으로 SELECT 명령을 결합해 합집합을 구하는 경우, 각 SELECT 명령에 ORDER BY를 지정해 정렬할 수는 없다.
- ORDER BY를 지정할 때는 마지막 쿼리 SELECT 명령에만 지정한다.
- ORDER BY를 쓸때는 정렬이 기준이되는 컬럼명이 서로 같아야 한다.
#에러 1
SELECT a FROM sample71_a ORDER BY a
UNION
SELECT b FROM sample71_b; # ERROR 첫 번째 SELECT 명령에 ORDER BY를 지정할 수 없다.
#에러 2
SELECT a FROM sample71_a
UNION
SELECT b FROM sample71_b ORDER BY b; # ERROR
#옳은 문법
SELECT a AS c FROM sample71_a
UNION
SELECT b AS c FROM sample71_b ORDER BY c;-
에러 1번은 합집합의 결과를 정렬하므로, 가장 마지막의 SELECT 명령에 ORDER BY를 지정해야 하는데 처음 SELECT명령에 지정해 에러가 났다.
-
2번은 ORDER BY를 지정할 수 있다고 해도 마지막의 SELECT 명령의 결과만 정렬하는 것이 아니고 합집합의 결과를 정렬하는 것이기 때문에 에러가 났다.
- 위 두 개의 SELECT명령에서 열 이름이 서로 다르기 때문에 에러가 난것이다. 이런 경우에는 별명을 붙여 정렬하면 된다.
-
UNION으로 SELECT 명령을 연결하는 경우,
가장 마지막 SELECT 명령에 대해서만 ORDER BY 구를 지정할 수 있다.
ORDER BY 구에 지정하는 열은 별명을 붙여 이름을 일치시킨다.
UNION ALL - 공통부분도 다 출력
-
중복을 제거하지 않고 모두를 반환하는 경우에는 ALL을 지정 >> UNION은 distinct를 사용할 수 없기에 기본 동작으로 깔려 있음
-
UNION은 기본 동작이 DISTINCT이고, 모든 결과를 얻고 싶을 때는 ALL을 추가적으로 지정한다.
- DISTINCT나 ALL로 중복제거 여부를 지정할 수 있다는 점은 똑같지만, UNION의 기본동작은 ALL이 아닌 DISTINCT라는 점이 다름
-
중복값이 없는 경우에는 UNION ALL을 사용하는 편이 좋은 성능을 보여준다.
두 개의 SELECT 명령에 UNION ALL을 적용해 합집합 구하기
SELECT * FROM sample71_a
UNION ALL
SELECT * FROM sample71_b;- 중복값이 없는 경우에는 UNION ALL을 사용하는 편이 좋은 성능을 보여준다.
예제용 db구축
c:\sql 폴더에 sample.dump를 복사
sql 폴더로 이동
mysql -u root -p < sample.dump
테이블 결합 JOIN - 곱집합/카티전곱
두 개의 집합을 곱하는 연산 방법으로 '곱집합' 또는 '카티전곱(Cartesian product)'이라고도 불린다.
- 가로(열) 방향으로 데이터가 늘어나는 결합이 된다. <> union은 세로로 결합
교차 결합(Cross Join)
- SELECT 명령에서는 FROM 구에 두 개의 테이블을 지정하면 이들은 곱집합으로 계산된다.
CREATE TABLE sample72_x(x char(4));
INSERT INTO sample72_x values('A');
INSERT INTO sample72_x values('B');
INSERT INTO sample72_x values('C');
CREATE TABLE sample72_y(y int);
INSERT INTO sample72_y values(1);
INSERT INTO sample72_y values(2);
INSERT INTO sample72_y values(3);
SELECT * FROM sample72_x;
# x A B C
SELECT * FROM sample72_y;
# y 1 2 3
#from구에 테이블 두개를 지정해 곱집합 구하기
SELECT * FROM sample72_x, sample72_y;
# x y
# A 1
# B 1
# C 1
# A 2
# B 2
# C 2
# A 3
# B 3
# C 3- FROM 구에 복수의 테이블을 지정하면 교차결합을 한다!
UNION 연결과 결합 연결의 차이
-
FROM 구로 테이블을 결합할 경우에는 가로 방향으로 더해지게 된다.
-
UNION으로 합집합을 구했을 경우에는 세로 방향으로 더해지게 된다.
내부 결합 Inner Join
결합 방법으로는 교차결합보다 내부결합이 자주 사용된다.
- 다른 테이블의 데이터를 참조해야 하는 경우, 참조할 테이블의 기본키와 동일한 이름과 자료형으로 열을 만들어서 행을 연결하는 경우가 많다.
# 상품 테이블 작성하기
CREATE TABLE 상품( # 한글로 놓는건 호환성 문제로 좋지 않다. 그저 학습적인 목적이라는 점
상품코드 CHAR(4) NOT NULL,
상품명 varchar(30),
메이커명 VARCHAR(30),
가격 INTEGER,
상품분류 VARCHAR(30),
PRIMARY KEY(상품코드)
);
# 재고관리 테이블 작성하기
CREATE TABLE 재고수(
상품코드 CHAR(4),
입고날짜 DATE,
재고수 INTEGER
);-
상품테이블의 상품명을 기본키로 지정하면 중복될 우려가 있으므로, 적합하지 않아 상품코드를 기본키로 이용하는 경우가 많다.
-
재고관리 테이블은 상품코드를 통해 상품 테이블과 연결할 수 있다. >> 재고수 테이블을 참조하는 다른 테이블이 있다면 기본키를 지정해두는 것이 좋다.
[실습하기]
# 크로스 조인
SELECT * FROM 상품;
SELECT * FROM 재고수;
# 이너조인
SELECT * FROM 상품, 재고수;-
상품코드가 같은 행을 검색하기 - Inner join
-
SELECT * FROM 상품, 재고수 WHERE 상품.상품코드 = 재고수.상품코드; -
교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것을 내부결합(Inner Join) 이라 부른다(결합 조건으로 보면 등결합 이라고도 부를 수 있다.)
-
-
검색할 행과 반환할 열 제한하기 - 옛날 방식의 Inner Join >> 잘 안쓰임 이제
SELECT 상품.상품명, 재고수.재고수 FROM 상품, 재고수 WHERE 상품.상품코드 = 재고수.상품코드 # 결합조건 AND 상품.상품분류 = '식료품'; # 검색 조건
-
where문의 첫번째 조건식은 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것 >> 결합조건
-
두번째 조건식은 결합 조건이 아닌 검색 조건이다.
조건식이 모두 참이어야 하므로 AND사용
- INNER JOIN으로 내부결합하기 - 새로나온 방식의 Inner join
특징
-
FROM 구에 테이블을 복수 지정해 가로 방향으로 테이블을 결합할 수 있다.
-
교차결합을 하면 곱집합으로 계산된다.
-
WHERE 조건을 지정해 곱집합에서 필요한 조합만 검색할 수 있다.
# 문법
SELECT * FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건
# 새로운 방식의 Inner Join >> 이 방식을 선호
SELECT 상품.상품명, 재고수.재고수
FROM 상품 INNER JOIN 재고수
ON 상품.상품코드 = 재고수.상품코드
WHERE 상품.상품분류 = '식료품';-
구식 방법에서는 쉼표(,)로 구분하여 테이블을 FROM 구에 지정했다. 새로운 형식에서는 테이블과 테이블 사이에 'INNER JOIN'이라는 키워드를 넣는다.
-
구식 방법에서는 WHERE 구에 결합조건을 지정하였지만 INNER JOIN에서는ON을 사용하여 결합조건을 지정한다.
INNER JOIN으로 두 개 테이블을 가로로 결합할 수 있다!
- 내부결합을 활용한 데이터 관리
- 메이커코드와 메이커명을 가지는 메이커 테이블을 작성해 데이터를 관리
CREATE TABLE 메이커(
메이커코드 CHAR(4) NOT NULL,
메이커명 VARCHAR(30),
PRIMARY KEY(메이커코드)
);
DESC 메이커;
DESC 상품2;
SELECT * FROM 메이커;
SELECT * FROM 상품2;
# 상품 테이블과 메이커 테이블을 내부 결합하기 - 테이블에 별명
SELECT S.상품명, M.메이커명
FROM 상품2 S INNER JOIN 메이커 M
ON S.메이커코드 = M.메이커코드;- 별명을 안줬으면 테이블이름을 넣고 . ~ 해야한다.
- SELECT 명령에서 복수의 테이블을 다룰 경우 어느 테이블의 열인지 정확하게 지정해야 한다.
- 테이블명을 매번 지정하는 것은 번거로운 일이므로 짧게 줄여 별명을 붙이는 경우가 많다.
외부키
- 메이커 테이블의 메이커코드는 기본키 <> 테이블의 메이커코드는 '외부키'라 불리는 것으로, 다른 테이블의 기본키를 참조하는 열이 외부키가 된다.
자기 결합(Self Join) - 테이블에 별명을 붙일 수 있는 기능을 이용해 같은 테이블끼리 결합하는 것을 말한다. >> 특별히 명령어가 정해져있지 ㅇ낳음
# 학습적인 목적의 예제
SELECT S1.상품명, S2.상품명
FROM 상품 S1 INNER JOIN 상품 S2
ON S1.상품코드 = S2.상품코드;- 자기결합에서는 결합의 좌우가 같은 테이블이 되기 때문에 이를 구별하기 위해서 반드시 별명을 붙여야 한다.
- 자기 자신의 기본키를 참조하는 열을 자기 자신이 가지는 데이터 구조로 되어 있을 경우에 자주 사용된다.
외부결합 Outer Join
교차결합으로 결합 조건을지정하여 검색한다는 기본적인 사고방식은 같다.
구분
- 내부결합
- 외부 결합 >> '어느 한 쪽에만 존재하는 데이터행을 어떻게 다룰지'를 변경할 수 있는 결합 방법이다
- Left Join/ Right Join 키워드 사용 <> Inner Join
외부결합으로 상품코드 0009인 상품도 결과에 포함하기
상품3 테이블에는 상품코드가 0009인 행을 새롭게 추가했다. 재고수 테이블에는 아직 이 상품에 대한 데이터가 없다. 이런 상태에서 곱집합을 구해도 0009 = 0009가 되는 행은 존재하지 않으므로 내부결합 결과에서는 상품코드가 0009인 상품이 제외된다.
# 내부결합에서는 상품코드가 0009인 상품이 제외된다.
SELECT 상품3.상품명, 재고수.재고수
FROM 상품3 INNER JOIN 재고수
ON 상품3.상품코드 = 재고수.상품코드
WHERE 상품3.상품분류 = '식료품';
# 외부결합으로 상품코드 0009인 상품도 결과에 포함하기
SELECT 상품3.상품명, 재고수.재고수
FROM 상품3 LEFT JOIN 재고수 # 재고수에는 없고, 상품 3에 원하는 데이터가 있으니 left Join 오른쪽에있으면 Rifht Join
ON 상품3.상품코드 = 재고수.상품코드
WHERE 상품3.상품분류 = '식료품';재고수 테이블에는 0009에 대한 데이터가 없으므로 값이 NULL로 표시되는 점에 주의
-
기준이 되는 상품 테이블을 JOIN의 왼쪽에 기술했으므로 LEFT JOIN이라 지정했다. 상품 테이블을 오른쪽에 지정하는 경우나 재고 테이블을 기준으로 삼고 싶은 경우에는 RIGHT JOIN을 사용해 외부결합을 시행한다.
LEFT JOIN, RIGHT JOIN으로 외부결합을 할 수 있다!
구식방법에서의 외부결합과 표준 SQL >> 참고적으로 알기만 하면 된다. 사용 x
-
구식 방법을 이용해도 내부결합은 가능하지만 외부결합은 할 수 없다. 그러므로 여기에서는 Oracle의 경우를 예로 들겠다. >> mysql은 비교적 최근에 나온 데이터베이스이므로
-
구식 방법에서는 where문으로 결합조건을 지정한다.
- 그냥 조건식을 지정하면 내부결합이 되어버리므로, 외부결합으로 진행하고 싶은 경우에는 특별한 연산자를 사용한다.
# Oracle에서 구식 외부결합으로 0009의 상품을 결과에 포함하기 Select 상품3.상품명, 재고수.재고수 FROM 상품3, 재고수 WHERE 상품3.상품코드 = 재고수.상품코드(+) AND 상품3.상품분류 = '식료품'; -
Oracle에서는 데이터가 존재하지 않을 수도 있는 테이블의 열에 (+)라는 특수한 기호를 붙여서 조건식을 지정한다.
문제
-
SELECT FROM sample71_a와 SELECT FROM sample71_b의 합집합을 구하시오.
select * from sample71_a union select * from sample71_b;
-
SELECT a FROM sample71_a, SELECT b FROM sample71_b 그리고 SELECT age FROM sample31;의 합집합을 구하시오.
select a from sample71_a union select b from sample71_b union select age from sample31;
-
sample71_a와 sample71_b를 오름차순 정렬하시오.
select a as c from sample71_a union select b as c from sample71_b order by c;
-
SELECT FROM sample71_a와 SELECT FROM sample71_b의 모든 요소를 출력하시오.
select * from sample71_a union all select * from sample71_b;
-
sample72_x와 sample72_y의 곱집합 구하기
select * from sample72_x join sample72_y;select * from sample72_x, sample72_y;# 위 보다 이게 더 간편
-
상품테이블의 상품코드와 재고수 테이블의 상품코드가 같은 상품테이블과 재고수 테이블의 모든 것을 출력하라.
select * from 상품, 재고수 where 상품.상품코드 = 재고수.상품코드;
-
상품테이블의 상품코드와 재고수 테이블의 상품코드가 같고 상품 테이블의 상품분류가 '식료품'인 상품명과 재고수를 출력하라.
select 상품.상품명, 재고수.재고수 from 상품, 재고수 where 상품.상품코드 = 재고수.상품코드 and 상품.상품분류 = '식료품';
-
상품테이블의 상품코드와 재고수 테이블의 상품코드가 같고 상품 테이블의 상품분류가 '식료품'인 상품명과 재고수를 출력하라.(6, 7번과는 다른 방법으로)
select 상품.상품명, 재고수.재고수 from 상품 inner join 재고수 on 상품.상품코드 = 재고수.상품코드 where 상품.상품분류 = '식료품';
-
상품2 테이블과 메이커 테이블로 부터 상품2 테이블의 메이커코드와 메이커테이블의 메이커코드가 같은 것의 상품명과 메이커명을 출력하라.
select 상품2.상품명, 메이커.메이커코드 from 상품2 inner join 메이커 on 상품2.메이커코드 = 메이커.메이커코드;select S.상품명, M.메이커명 from 상품2 S inner join 메이커 M on S.메이커코드 = M.메이커코드;>> 별칭사용
-
상품 테이블의 별명을 S1으로 하고 상품 테이블의 별명을 S2로 해서 S1의 상품명과 S2의 상품명이 같은 것들의 S1의 상품명과 S2의 상품명을 출력하라.
select s1.상품명, s2.상품명 from 상품 s1 inner join 상품 s2 on s1.상품명 = s2.상품명;
-
상품3 테이블과 재고수 테이블로 부터 상품3의 상품코드와 재고수의 상품코드가 같고 상품3의 상품분류가 '식료품'인 것들의 상품명과 재고수를 출력하시오. 상품3 테이블을 기준으로 외부결합하시오.
select 상품3.상품명, 재고수.재고수 from 상품3 left join 재고수 on 상품3.상품코드 = 재고수.상품코드 where 상품3.상품분류 = '식료품';select s3.상품명, j.재고수 from 상품3 s3 left join 재고수 j on s3.상품코드 = j.상품코드 where s3.상품분류 = '식료품';
관계형 모델 - 용어
관계형 모델을 기반으로 작성된 데이터베이스를 '관계형 데이터베이스'라고 한다.
- 관계형 모델
-
기본적인 요소는 릴레이션(Relation)이다. >> 릴레이션이라는 말 자체는 관계를 뜻하지만 관계형 모델에서는 테이블이라는 의미를 가진다.(테이블끼리의 관계가 아님)
- 관계형 모델의 릴레이션에는 몇 가지 '속성(attribute)'이 존재
- 속성 이름과 형 이름으로 구성
- 속성 : 열, 컬럼
- 행 : 튜플
- 관계형 모델의 릴레이션에는 몇 가지 '속성(attribute)'이 존재
-
릴레이션은 튜플의 집합이며, 릴레이션에 대한 연산이 집합에 대한 연산에 대응된다는 이론을 '관계대수'라고 한다.
관계대수의 기본규칙
- 하나 이상의 관계를 바탕으로 연산한다.
- 연산한 결과, 반환되는 것 또한 관계이다.
- 연산을 중첩 구조로 실행해도 상관없다.
- 관계형 모델과 SQL
합집합
- 합집합(union)은 릴레이션끼리의 덧셈 >> UNION
CREATE TABLE A(no int);
INSERT INTO A values(1);
INSERT INTO A values(2);
INSERT INTO A values(3);
CREATE TABLE B(no int);
INSERT INTO B values(2);
INSERT INTO B values(10);
INSERT INTO B values(11);
SELECT * FROM A UNION SELECT * FROM B;차집합
-
차집합(difference)은 릴레이션끼리의 뺄셈 >> EXCEPT
워크벤치에서는 에러라고 뜨지만 정상적으로 실행된다.
SELECT * FROM A EXCEPT SELECT * FROM B;
(
SELECT A.no FROM A LEFT JOIN B on A.no = B.no WHERE B.NO IS NULL;
)교집합
- 교집합은(intersection)은 릴레이션끼리의 공통부분(교집합)을 의미 >> INTERSECT
SELECT * FROM A INTERSECT SELECT * FROM B;
(
SELECT A.no FROM A JOIN B on A.no = B.no;
)EXCEPT와 INTERSECT는 PostgreSQL, MySQL에서만 활용 가능
곱집합
-
곱집합(cartesian product)은 릴레이션끼리의 대전표를 조합하는 연산
-
SQL에서는 FROM 구에 복수의 테이블을 지정한 경우 곱집합으로 계산된다.
-
CROSS JOIN으로 교차결합을 하면 곱집합을 구할 수 있다.
SELECT * FROM A, B; SELECT * FROM A CROSS JOIN B; # 곱집합
-
선택 selection
- 선택(selection)은 튜플의 추출을 말한다. = 제한이라고 불리기도 함
- 튜플은 SQL에서 행을 말하기 때문에 WHERE 구에 조건을 지정해 데이터를 검색하는 것에 해당된다.
CREATE TABLE C(no int, a char(4));
INSERT INTO C VALUES(1, 'A');
INSERT INTO C VALUES(2, 'B');
INSERT INTO C VALUES(3, 'C');
SELECT * FROM C WHERE no < 3;투영 projection
- 투영(projection)은 속성의 추출을 의미
- SQL에서 속성은 열을 말하기 때문에 SELECT 구에 결과로 반환할 열을 지정하는 것에 해당된다.
SELECT a FROM C;결합 join
-
결합(JOIN)은 릴레이션끼리 교차결합해 계산된 곱집합에서 결합조건을 만족하는 튜플을 추출하는 연산으로, 내부결합에 해당
관계대수에도 내부결합과 외부결합이 있다.
CREATE TABLE D(no int, b char(4));
INSERT INTO D VALUES(1, '가');
INSERT INTO D VALUES(2, '나');
INSERT INTO D VALUES(3, '다');
SELECT * FROM C INNER JOIN D ON C.no = D.no;-
집합연산
-
SQL에서는 UNION으로 합집합을 구할 수 있다.
데이터베이스의 지원 여부에 따라 차집합이나 교집합을 구할 수도 있다.
-
-
교차결합, 곱집합
-
FROM 구에 테이블을 복수로 지정하여 교차결합으로 곱집합을 구할 수 있다.
내부결합이나 외부결합의 기반이 된다.
-
-
내부결합
- 내부결합은 곱집합에서 필요한 행만 검색하도록 조건을 지정해 결합하는 것을 의미
- 이때 지정하는 조건을 결합조건이라 부름
- 내부결합은 곱집합에서 필요한 행만 검색하도록 조건을 지정해 결합하는 것을 의미
-
외부결합
- 내부결합으로 결합되지 않는 행을 강제적으로 결과에 포함하는 방법
-
관계형 모델
- 관계형 모델은 관계형 데이터베이스의 기반이 되는 이론적 개념으로, 사용되는 용어는 SQL과 일치하지 않는다
문제
- 릴레이션 : 테이블
- 속성 : 컬럼, 열
- 튜플 : 행
- A 테이블을 만들고 no int 요소를 가지게 만든후 A테이블에 순차적으로 1, 2, 3을 넣는다.
B 테이블을 만들고 다음 요소를 가지도록 만든다.no int >> B테이블에 순차적으로 2, 10, 11을 넣는다.
create table a(no int);
insert into a values(1), (2), (3);
create table b(no int);
insert into b values(2), (10), (11);- A 테이블과 B 테이블의 합집합을 구한다.
- A 테이블과 B 테이블의 차집합을 구한다.
- A 테이블과 B 테이블의 교집합을 구한다.
- A 테이블과 B 테이브 교집합을 구한다.
#합집합
select * from a union select * from b;
# 차집합
select * from a except select * from b; # 에러라고 뜨지만 멀쩡히 실행됌
# 교집합 - 공통적인 부분
select * from a intersect select * from b;
# 곱집합
select * from a , b;
select * from a cross join b;참조 foreign key () references 테이블명(속성)
CREATE TABLE 메이커2(
메이커코드 CHAR(4) NOT NULL,
메이커명 VARCHAR(30),
PRIMARY KEY(메이커코드)
);
CREATE TABLE 상품22(
상품코드 CHAR(4) NOT NULL,
상품명 varchar(30),
메이커코드 CHAR(4),
FOREIGN KEY(메이커코드) REFERENCES 메이커2(메이커코드)
);
INSERT INTO 메이커2 VALUES('M001', '나이키');
INSERT INTO 메이커2 VALUES('M002', '리복');
INSERT INTO 상품22 VALUES('P001', '축구화', 'M001');
INSERT INTO 상품22 VALUES('P002', '러닝화', 'M002');
INSERT INTO 상품22 VALUES('P003', '농구화', 'M003'); # Error1
SELECT * FROM 메이커2;
SELECT * FROM 상품22;
DELETE FROM 메이커2 WHERE 메이커코드='M001'; # Error2
UPDATE 메이커2 SET 메이커코드='M003' WHERE 메이커코드='M002'; # Error3
DROP TABLE 메이커2, 상품22;-
에러 1번 : 상품 22 테이블의 메이커코드 열은 메이커2 테이블의 메이커코드를 참조하는 외래 키 제약조건을 가지고 있다. >> P003상품의 메이커 코드인 M003은 메이커2 테이블에 존재하지 않기 때문에 에러가 난다.
-
에러 2번 : 참조 무결성 제약 조건 위반이다. 메이커2 테이블의 메이커코드 열은 상품22 테이블의 메이커코드를 참조하는 외래 키 제약조건을 가지고 있다. 따라서 메이커2 테이블에서 해당 메이커 코드를 삭제하려 하면, 외래 키 제약 조건에 위배되어 삭제할 수 없다.
-
에러 3번 : 참조 무결성 제약 조건 위반이다. 메이커2 테이블의 메이커코드 열은 상품 22 테이블의 메이커코드를 참조하는 외래키 제약조건을 가지고 있다. 따라서, 메이커2 테이블에서 해당 메이커 코드를 수정하려고 하면, 외래키 제약 조건에 위배되어 수정할 수 없다.
CREATE TABLE 메이커2(
메이커코드 CHAR(4) NOT NULL,
메이커명 VARCHAR(30),
PRIMARY KEY(메이커코드)
);
CREATE TABLE 상품22(
상품코드 CHAR(4) NOT NULL,
상품명 varchar(30),
메이커코드 CHAR(4),
FOREIGN KEY(메이커코드) REFERENCES 메이커2(메이커코드) ON DELETE CASCADE
);-
ON DELETE CASCADE
-
외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래 키가 포함되어 있는 모든 행도 삭제
INSERT INTO 메이커2 VALUES('M001', '나이키');
INSERT INTO 메이커2 VALUES('M002', '리복');
INSERT INTO 상품22 VALUES('P001', '축구화', 'M001');
INSERT INTO 상품22 VALUES('P002', '러닝화', 'M002');
SELECT * FROM 메이커2;
SELECT * FROM 상품22;
DELETE FROM 메이커2 WHERE 메이커코드='M001';
SELECT * FROM 메이커2;
SELECT * FROM 상품22;
UPDATE 메이커2 SET 메이커코드='M003' WHERE 메이커코드='M002'; # Error
DROP TABLE 메이커2, 상품22;
CREATE TABLE 메이커2(
메이커코드 CHAR(4) NOT NULL,
메이커명 VARCHAR(30),
PRIMARY KEY(메이커코드)
);
CREATE TABLE 상품22(
상품코드 CHAR(4) NOT NULL,
상품명 varchar(30),
메이커코드 CHAR(4),
FOREIGN KEY(메이커코드) REFERENCES 메이커2(메이커코드) ON UPDATE CASCADE
);-
ON UPDATE CASCADE
-
외래 키에서 참조하는 키 값이 포함된 행에서 키 값을 업데이트 하면 해당 외래 키를 구성하는 모든 값도 키에 지정된 새 값으로 업데이트되도록 지정
INSERT INTO 메이커2 VALUES('M001', '나이키');
INSERT INTO 메이커2 VALUES('M002', '리복');
INSERT INTO 상품22 VALUES('P001', '축구화', 'M001');
INSERT INTO 상품22 VALUES('P002', '러닝화', 'M002');
SELECT * FROM 메이커2;
SELECT * FROM 상품22;
UPDATE 메이커2 SET 메이커코드='M003' WHERE 메이커코드='M002';
SELECT * FROM 메이커2;
SELECT * FROM 상품22;
DELETE FROM 메이커2 WHERE 메이커코드='M001'; # Error
DROP TABLE 메이커2, 상품22;CREATE TABLE 메이커2(
메이커코드 CHAR(4) NOT NULL,
메이커명 VARCHAR(30),
PRIMARY KEY(메이커코드)
);
CREATE TABLE 상품22(
상품코드 CHAR(4) NOT NULL,
상품명 varchar(30),
메이커코드 CHAR(4),
FOREIGN KEY(메이커코드) REFERENCES 메이커2(메이커코드) ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO 메이커2 VALUES('M001', '나이키');
INSERT INTO 메이커2 VALUES('M002', '리복');
INSERT INTO 상품22 VALUES('P001', '축구화', 'M001');
INSERT INTO 상품22 VALUES('P002', '러닝화', 'M002');
SELECT * FROM 메이커2;
SELECT * FROM 상품22;
UPDATE 메이커2 SET 메이커코드='M003' WHERE 메이커코드='M002';
SELECT * FROM 메이커2;
SELECT * FROM 상품22;
DELETE FROM 메이커2 WHERE 메이커코드='M001';
SELECT * FROM 메이커2;
SELECT * FROM 상품22;
DROP TABLE 메이커2, 상품22;CREATE TABLE 메이커2(
메이커코드 CHAR(4) NOT NULL,
메이커명 VARCHAR(30),
PRIMARY KEY(메이커코드)
);
CREATE TABLE 상품22(
상품코드 CHAR(4) NOT NULL,
상품명 varchar(30),
메이커코드 CHAR(4)
);
INSERT INTO 메이커2 VALUES('M001', '나이키');
INSERT INTO 메이커2 VALUES('M002', '리복');
INSERT INTO 상품22 VALUES('P001', '축구화', 'M001');
INSERT INTO 상품22 VALUES('P002', '러닝화', 'M002');
ALTER TABLE 상품22 ADD CONSTRAINT FOREIGN KEY(메이커코드) REFERENCES 메이커2(메이커코드) ON DELETE CASCADE ON UPDATE CASCADE;
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = '상품22';
ALTER TABLE 상품22 DROP FOREIGN KEY 상품22_IBFK_1; # 상품22_IBFK_1은 검색해서 나온 제약키 이름
# >> 따로 제약키 이름을 명시하지 않으면 자동을 생성되서, 이 이름으로 삭제를 진행해야함
DROP TABLE 메이커2, 상품22;CREATE TABLE 메이커2(
메이커코드 CHAR(4) NOT NULL,
메이커명 VARCHAR(30),
PRIMARY KEY(메이커코드)
);
CREATE TABLE 상품22(
상품코드 CHAR(4) NOT NULL,
상품명 varchar(30),
메이커코드 CHAR(4)
);
INSERT INTO 메이커2 VALUES('M001', '나이키');
INSERT INTO 메이커2 VALUES('M002', '리복');
INSERT INTO 상품22 VALUES('P001', '축구화', 'M001');
INSERT INTO 상품22 VALUES('P002', '러닝화', 'M002');
( 보통 제약조건 이름은 테이블 이름, 컬럼 명, ~ 해서 사용한다.)
ALTER TABLE 상품22 ADD CONSTRAINT 상품22_메이커코드_fk FOREIGN KEY(메이커코드) REFERENCES 메이커2(메이커코드) ON DELETE CASCADE ON UPDATE CASCADE;- 제약 조건 이름 : 작명법 테이블이름컬럼이름제약조건
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = '상품22';
ALTER TABLE 상품22 DROP FOREIGN KEY 상품22_메이커코드_fk; # 상품22_메이커코드_fk 은 검색해서 나온 제약키 이름;
DROP TABLE 메이커2, 상품22;참조 문제
- 메이커2 테이블을 만든다.
메이커코드 CHAR(4) 널 허용안함 기본키
메이커명 VARCHAR(30)
상품 22 테이블을 만든다.
상품코드 CHAR(4) 널 허용안함
상품명 varchar(30)
메이커코드 char(4)
메이커코드는 메이커2테이블의 메이커코드를 참조한다.
create table 상품22(상품코드 char(4) not null, 상품명 varchar(30), 메이커코드 char(4),
foreign key(메이커코드) references 메이커2(메이커코드)); # 상품22 테이블 생성-
메이커2 테이블에 다음 내용을 넣는다.
'M001', '나이키'
'M002', '리복'insert into 메이커2 values('M001', '나이키'), ('M002', '리복');
-
상품22 테이블에 다음 내용을 넣는다.
'P001', '축구화', 'M001'
'P002', '러닝화', 'M002'insert into 상품22 values('P001', '축구화', 'M001'), ('P002', '러닝화', 'M002');
-
다음 내용을 상품22 테이블에 넣고 에러가 나는 것을 확인하자.
'P003', '농구화', 'M003'
왜 에러가 나는가?insert into 상품22 values('P003', '농구화', 'M003');# 추가하려는 데이터가 메이커2와 참조관계여서 - 넣기, 삭제하기, 바꾸기 불가능 다로 키워드 사용 해야함(후에 개념적으로 나옴)
-
메이커2, 상품22 테이블을 지우자.
drop table 메이커2, 상품22;
참조 관계 삭제 - on delete cascade
-
메이커2 테이블을 만든다.
메이커코드 CHAR(4) 널 허용안함 기본키
메이커명 VARCHAR(30) -
상품 22 테이블을 만든다.
상품코드 CHAR(4) 널 허용안함
상품명 varchar(30)
메이커코드 char(4)
메이커코드는 메이커2테이블의 메이커코드를 참조한다.
외래키를 만들 때 다음 옵션을 주자
(외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래 키가 포함되어 있는 모든 행도 삭제)
# 메이커2 테이블 생성
create table 메이커2(메이커코드 char(4) not null primary key, 메이커명 varchar(30));
# 상품22 테이블 생성
create table 상품22(상품코드 char(4) not null, 상품명 varchar(30), 메이커코드 char(4),
foreign key(메이커코드) references 메이커2(메이커코드) on delete cascade);-
메이커2 테이블에 다음 내용을 넣는다.
'M001', '나이키'
'M002', '리복'insert into 메이커2 values('M001', '나이키'), ('M002', '리복');
-
상품22 테이블에 다음 내용을 넣는다.
'P001', '축구화', 'M001'
'P002', '러닝화', 'M002'insert into 상품22 values('P001', '축구화', 'M001'), ('P002', '러닝화', 'M002');
-
메이커2 테이블에 메이커코드 M001을 삭제하자.
delete from 메이커2 where 메이커코드 = 'M001';
-
메이커2 테이블에 메이커코드 'M002'를 'M003'으로 바꾸자.
그리고 에러가 나는 것을 확인하자.
왜 에러가 나는가?update 메이커2 set 메이커코드 = 'M003' where 메이커코드 = 'M002';>> on delete cascade만설정해서 삭제만 가능하다.
-
메이커2, 상품22 테이블을 지우자.
drop TABLE 메이커2, 상품22;
참조 관계 변경, 삭제- on update cascade
-
메이커2 테이블을 만든다.
메이커코드 CHAR(4) 널 허용안함 기본키
메이커명 VARCHAR(30) -
상품 22 테이블을 만든다.
상품코드 CHAR(4) 널 허용안함
상품명 varchar(30)
메이커코드 char(4)
메이커코드는 메이커2테이블의 메이커코드를 참조한다.
외래키를 만들 때 다음 옵션을 주자
(외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래 키가 포함되어 있는 모든 행도 삭제
외래 키에서 참조하는 키 값이 포함된 행에서 키 값을 업데이트 하면 해당 외래 키를 구성하는 모든 값도 키에 지정된 새 값으로 업데이트되도록 지정)
# 메이커2 테이블 생성
create table 메이커2(메이커코드 char(4) not null primary key, 메이커명 varchar(30));상품22 테이블 생성
create table 상품22(상품코드 char(4) not null, 상품명 varchar(30), 메이커코드 char(4),
foreign key(메이커코드) references 메이커2(메이커코드) on update cascade on delete cascade);
-
메이커2 테이블에 다음 내용을 넣는다.
'M001', '나이키'
'M002', '리복'insert into 메이커2 values('M001', '나이키'), ('M002', '리복');
-
상품22 테이블에 다음 내용을 넣는다.
'P001', '축구화', 'M001'
'P002', '러닝화', 'M002'insert into 상품22 values('P001', '축구화', 'M001'), ( 'P002', '러닝화', 'M002');
-
메이커2 테이블에 메이커코드 M001을 삭제하자.
delete from 메이커2 where 메이커코드 = 'M001';
-
메이커2 테이블에 메이커코드 'M002'를 'M003'으로 바꾸자.
update 메이커2 set 메이커코드 = 'M003' where 메이커코드 = 'M002';
-
메이커2, 상품22 테이블을 지우자.
drop table 메이커2, 상품22;
- 상품22 테이블에 다음 제약 조건을 넣자.
메이커코드는 메이커2테이블의 메이커코드를 참조한다.
(외래 키에서 참조하는 키가 포함된 행을 삭제하려고 하면 해당 외래 키가 포함되어 있는 모든 행도 삭제
외래 키에서 참조하는 키 값이 포함된 행에서 키 값을 업데이트 하면 해당 외래 키를 구성하는 모든 값도 키에 지정된 새 값으로 업데이트되도록 지정)
- 제약 조건이름을 안주고서 만들어보고, 주고도 만들어 본다.
alter table 상품22 add constraint 상품22_메이커코드_cn foreign key(메이커코드) references 메이커2(메이커코드)on delete cascade on update cascade;
- 5에서 만든 제약조건을 검색해 보자.
select * from information_schema.table_constraints where table_name = '상품22';
- 5에서 만든 제약조건을 제거하자.
alter table 상품22 drop foreign key 상품22_메이커코드_cn;
스토어드 프로시저
MySQL에서 제공하는 프로그래밍 기능으로, 여러 개의 SQL문을 하나로 묶어서 편리하게 사용할 수 있다.
#스토어드 프로시저 생성
DELIMITER //
CREATE PROCEDURE myProc()
BEGIN
SELECT * FROM member WHERE member_name = '나훈아';
SELECT * FROM product WHERE product_name = '삼각김밥';
END //
DELIMITER ;
#스토어드 프로시저 호출
call myProc();
#스토어드 프로시저 삭제
drop procedure myProc();
#DB 상관없이 모든 프로시저 출력
show procedure status;
#특정 DB만 출력하기
show procedure status where Db = ' ';
show procedure status where Db = 'market_db';정보처리기사 sql기출
1, 2, 1, 2, 4, 3, 1, 3, 1, 3 >9번 그냥 결과만 보고 1번 골랐네..
jdbc설치
JDBC 연결
1. connection을 가져오기
-
eclips : JDBCTest 프로젝트생성 후 우클릭해서 bild path - configure build path - libraries - classpath - add external jars - 다운로드한(class파일에) - jars 파일 선택해서 apply
-
class새로 생성해서 해당 파일에
-
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class JDBCTest { public static void main(String[] args) { // db에서 만든 스키마 = project String url = "jdbc:mysql://localhost:3306/project?serverTimezone=UTC"; // serverTimezone=UTC 서버의 시간을 설정 UTC(Coordinated Universal Time:세계 협정시) // localhost라고 적은 부분은 현재 자신의 컴퓨터가 데이터베이스가 설치된 서버라는 의미의 루프백 IP주소이다. // 루프백 IP 주소로는 127.0.0.1도 사용할 수 있다. // 3306은 MySQL의 포트 번호이다. // mysql에 project 데이터베이스가 있어야 한다. String id = "root", pw = "1234"; try { Class.forName("com.mysql.cj.jdbc.Driver"); // 데이터베이스와 연결하는 드라이버 클래스를 찾아 로드한다. } catch (ClassNotFoundException e1) { System.out.println("드라이버 로드 실패"); } try (Connection conn = DriverManager.getConnection(url, id, pw)) { // close()자동 호출 // 연결을 관리하는 Connection 객체를 생성한다. System.out.println("Connection 객체 생성 성공"); } catch (SQLException e) { System.out.println("Connection 객체 생성 실패"); } } }
-
- sql : create database project; >> 스키마 명은 마음대로 대신 위 코드에서 해당 스키마명으로 변경해야함
2. 데이터 삽입 - executeUpdate()
create, insert, delete, update
eclipse
- 이클립스에 입력한 값이 sql 스키마에 들어가 데이터로 저장된다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Scanner;
import java.sql.Statement;
public class JDBCTest {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
// serverTimezone=UTC 서버의 시간을 설정 UTC(Coordinated Universal Time:세계 협정시)
// localhost라고 적은 부분은 현재 자신의 컴퓨터가 데이터베이스가 설치된 서버라는 의미의 루프백 IP주소이다.
// 루프백 IP 주소로는 127.0.0.1도 사용할 수 있다.
// 3306은 MySQL의 포트 번호이다.
String id = "root", pw = "1234", sql = null;
String name = null, title = null, contents = null;
int menu = 0;
int result = 0;
try { //데이터베이스와 연결하는 드라이버 클래스를 로드 - mysql에서 제공하는 jdbc드라이버 클래스
Class.forName("com.mysql.cj.jdbc.Driver");//안에 있는 문자열이
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
// 데이터베이스에 연결하기 위해 Connection객체를 생성한다.
try (Connection conn = DriverManager.getConnection(url, id, pw);
Statement stmt = conn.createStatement()) {
// Connection 객체인 conn을 사용해 sql문을 실행하기 위한 Statement객체를 생성한다.
//Statement객체를 사용해 sql문을 실행하고, db로부터 결과를 반환받을 수 있다. - JDBC의 핵심 인터페이스 중 한다 statement
Scanner sc = new Scanner(System.in);
// 질의를 처리하는 객체를 생성한다.
System.out.println("Connection 객체 생성 성공");
while (true) {
System.out.println("메뉴를 선택하세요.");
System.out.println("1. 쓰기");
System.out.println("2. 조회");
System.out.println("3. 종료");
System.out.print("선택 : ");
menu = sc.nextInt();
sc.nextLine();
switch (menu) {
case 1:
System.out.println("이름을 입력하세요.");
name = sc.nextLine();
System.out.println("제목을 입력하세요.");
title = sc.nextLine();
System.out.println("내용을 입력하세요.");
contents = sc.nextLine(); //작은따옴표 - 문자열은 ' ' >> sql의 문법대로 들어가게끔 작은 따옴표까지 쌍따옴표 안에 넣는다.
sql = "INSERT INTO board(name, title, contents) VALUES('" + name + "', '" + title + "' , '" + contents + "')";
//해당 내용을 아래 excuteUpdate()를 이용해 쿼리를 던지고, 적용된 줄 수의 값을 반환한다.
System.out.println(sql); // 디버깅 용도
result = stmt.executeUpdate(sql); //update명령을 통해, create/insert/delete/update실행 가능 >> 결과를 받아오는게 아니기때문에 select는 필요 x
//result에 적용된 줄 수를 반환한다. sql한줄 들어갔으니까 1이 들어갈것, 10줄이 들어가면 10을 반환
// 질의가 성공적으로 수행되면 삽입된 개수가 반환된다. 여기에서는 1이 반환.
if (result > 0)
System.out.println(result + " 데이터 삽입성공");
break;
case 3:
System.out.println("프로그램을 종료합니다.");
sc.close();
return;
}
}
} catch (SQLException e) {
System.out.println("Connection 객체 생성 실패");
}
}
}- sql : 이클립스에서 입력한 값을 저장한 테이블을 저장한다. 해당 테이블 명은 board >> 다른 이름으로 하고싶다면, sql의 문자열에서 변경시키면 된다.
CREATE TABLE BOARD(
no int AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
title varchar(200),
contents text,
wTime datetime default CURRENT_TIMESTAMP,
rCnt int default 0
);흐름
-
JDBC드라이버를 로드시키고 > connection 객체를 연결 > 가져온 connection객체를 통해 statement객체를 생성해 가져온다. > statement객체를 통해 쿼리를 날린다.
- 다 끝나면 리소스를 해제해야한다. > try~resource 이용
- int
mysql
- 확인해보면 board 테이블에 이클립스에서 입력한 데이터가 들어간 것을 확인할 수 있다. 해당 테이블의 데이터를 지우고 auto_increment를 초기화하려면 아래와 같이 하면 된다.
desc board;
select * from board;
DELETE from board where no = 1;
alter table board auto_increment = 0; #auto_increment를 0으로 초기화 3. 데이터 조회 - executeQuery
select의 역할 - ResultSet으로 반환된다.
eclipse- Statement객체 이용
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class JDBCTest {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
// serverTimezone=UTC 서버의 시간을 설정 UTC(Coordinated Universal Time:세계 협정시)
// localhost라고 적은 부분은 현재 자신의 컴퓨터가 데이터베이스가 설치된 서버라는 의미의 루프백 IP주소이다.
// 루프백 IP 주소로는 127.0.0.1도 사용할 수 있다.
// 3306은 MySQL의 포트 번호이다.
// project 는 데이터베이스 스키마 명이다.
String id = "root", pw = "1234";
String sql = null, name = null, title = null, contents = null;
int menu = 0, result = 0, no = 0, rCnt = 0;
String wTime = null;
try { // 데이터베이스와 연결하는 드라이버 클래스를 로드 - mysql에서 제공하는 jdbc드라이버 클래스
Class.forName("com.mysql.cj.jdbc.Driver");// 안에 있는 문자열이
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
// 데이터베이스에 연결하기 위해 Connection객체를 생성한다.
try (Connection conn = DriverManager.getConnection(url, id, pw); Statement stmt = conn.createStatement()) {
// Connection 객체인 conn을 사용해 sql문을 실행하기 위한 Statement객체를 생성한다.
// Statement객체를 사용해 sql문을 실행하고, db로부터 결과를 반환받을 수 있다. - JDBC의 핵심 인터페이스 중 한다
Scanner sc = new Scanner(System.in);
// 질의를 처리하는 객체를 생성한다.
System.out.println("Connection 객체 생성 성공");
while (true) {
System.out.println("메뉴를 선택하세요.");
System.out.println("1. 쓰기");
System.out.println("2. 조회");
System.out.println("3. 종료");
System.out.print("선택 : ");
menu = sc.nextInt();
sc.nextLine(); // 버퍼에 남아있는 엔터값을 지우기 위한 nextLine()
switch (menu) {
case 1:
System.out.println("이름을 입력하세요.");
name = sc.nextLine();
System.out.println("제목을 입력하세요.");
title = sc.nextLine();
System.out.println("내용을 입력하세요.");
contents = sc.nextLine(); // 작은따옴표 - 문자열은 ' ' >> sql의 문법대로 들어가게끔 작은 따옴표까지 쌍따옴표 안에 넣는다.
sql = "INSERT INTO board(name, title, contents) VALUES('" + name + "', '" + title + "' , '"
+ contents + "')";
// 해당 내용을 아래 excuteUpdate()를 이용해 쿼리를 던지고, 적용된 줄 수의 값을 반환한다.
System.out.println(sql); // 디버깅 용도
result = stmt.executeUpdate(sql); // executeUpdate() >> create/insert/delete/update실행 가능
// >> 결과를 받아오는게 아니기때문에 select는 필요 x
// result에 적용된 줄 수를 반환한다. sql한줄 들어갔으니까 1이 들어갈것, 10줄이 들어가면 10을 반환
// 질의가 성공적으로 수행되면 삽입된 개수가 반환된다. 여기에서는 1이 반환.
if (result > 0)
System.out.println(result + " 데이터 삽입성공");
break;
case 2:
sql = "select * from board order by no desc"; // no를 기준으로 내림차순 >> 게시판을 보면 최신 글 부터 보기 위함
try (ResultSet rs = stmt.executeQuery(sql)) {// executeQuery() >> 쿼리의 실행 결과를 가져와 resultSet객체를 반환한다.
// >> Select를 날리는 것과 같다.
// ResultSet을 반환하는 메소드는 executeQuery()밖에 없음
while (rs.next()) {
no = rs.getInt("no");
name = rs.getString("name");
title = rs.getString("title");
contents = rs.getString("contents");
wTime = rs.getString("wTime");
rCnt = rs.getInt("rCnt");
System.out.println("번호 " + no);
System.out.println("이름 " + name);
System.out.println("제목 " + title);
System.out.println("내용 " + contents);
System.out.println("시간 " + wTime);
System.out.println("조회수 " + rCnt);
}
} catch (SQLException e) {
e.printStackTrace();
}
break;
case 3:
System.out.println("프로그램을 종료합니다.");
sc.close();
return;
}
}
} catch (SQLException e) {
System.out.println("Connection 객체 생성 실패");
}
}
}데이터 조회 - PreparedStatement 이용
- AutoCloseable - Statement - PrepardeStatement : 순으로 상속 >> try~resource를 사용하려면 AutoCloseable을 상속받아야함
- PrepardeStatement는 Statement를 상속받으니 해당 메소드를 그대로 사용할 수 있다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class JDBCTest {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
// serverTimezone=UTC 서버의 시간을 설정 UTC(Coordinated Universal Time:세계 협정시)
// localhost라고 적은 부분은 현재 자신의 컴퓨터가 데이터베이스가 설치된 서버라는 의미의 루프백 IP주소이다.
// 루프백 IP 주소로는 127.0.0.1도 사용할 수 있다.
// 3306은 MySQL의 포트 번호이다.
String id = "root";
String pw = "1234";
Connection conn = null;
PreparedStatement pstmt = null;
String sql = null;
String name = null, title= null, contents = null;
Scanner sc = new Scanner(System.in, "euc-kr");
int menu = 0;
int result = 0;
ResultSet rs = null;
int no = 0, rCnt = 0;
String wTime = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// 데이터베이스와 연결하는 드라이버 클래스를 찾아 로드한다.
conn = DriverManager.getConnection(url, id, pw);
// 연결을 관리하는 Connection 객체를 생성한다.
System.out.println("Connection 객체 생성 성공");
sql = "INSERT INTO board(name, title, contents) VALUES(?, ?, ?)";
//아래에서 setString 응 이용해 값을 넣어주면 sql에서 자동으로 따옴표를 붙여서 입력한다.
pstmt = conn.prepareStatement(sql);
// 질의를 처리하는 객체를 생성한다.
while(true)
{
System.out.println("메뉴를 선택하세요.");
System.out.println("1. 쓰기");
System.out.println("2. 조회");
System.out.println("3. 종료");
System.out.print("선택 : ");
menu = sc.nextInt();
sc.nextLine();
switch(menu)
{
case 1:
System.out.println("이름을 입력하세요.");
name = sc.nextLine();
System.out.println("제목을 입력하세요.");
title = sc.nextLine();
System.out.println("내용을 입력하세요.");
contents = sc.nextLine();
pstmt.setString(1, name); //여기가 Statement와 다름 >> 이 숫자가 ?의 순번
pstmt.setString(2, title);
pstmt.setString(3, contents);
result = pstmt.executeUpdate();
// 질의가 성공적으로 수행되면 삽입된 개수가 반환된다. 여기에서는 1이 반환.
if(result > 0) System.out.println(result + " 데이터 삽입성공");
break;
case 2:
sql = "SELECT * FROM BOARD ORDER BY no desc";
rs = pstmt.executeQuery(sql);
// 질의 결과를 처리한다.
while(rs.next())
{
no = rs.getInt("no");
name = rs.getString("name");
title = rs.getString("title");
contents = rs.getString("contents");
wTime = rs.getString("wTime");
rCnt = rs.getInt("rCnt");
System.out.println("번호 " + no);
System.out.println("이름 " + name);
System.out.println("제목 " + title);
System.out.println("내용 " + contents);
System.out.println("시간 " + wTime);
System.out.println("조회수 " + rCnt);
System.out.println();
}
break;
case 3:
System.out.println("프로그램을 종료합니다.");
return;
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
System.out.println("Connection 객체 생성 실패");
} finally {
try {
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(conn != null) conn.close();
System.out.println("conn.close() 성공");
// Connection 객체를 닫는다.
if(sc != null) sc.close();
} catch (SQLException e) {
System.out.println("conn.close() 에러");
}
}
}
}
/*
CREATE TABLE BOARD(
no int AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
title varchar(200),
contents text,
wTime datetime default CURRENT_TIMESTAMP,
rCnt int default 0
);
*/- 만약 try~resource를 사용하지 않으면 try~catch에서 finally문을 이용해 하나씩 다 지정해서 close() 메소드를 이용해 해제해줘야한다.
- finally에서 다 해제해야하니까 try~안에서 선언하면 안되고 미리 클래스 변수로 선언해서 사용해야한다.
Statement / PreparedStatement 차이점
Statement와 PreparedStatement는 JDBC에서 쿼리를 실행하는 데 사용되는 두 가지 주요 인터페이스입니다. 이 두 인터페이스는 쿼리를 실행하는 방식과 목적에서 차이가 있습니다.
-
Statement:
-
Statement는 정적인 SQL 문장을 실행하는 데 사용됩니다. 즉, 쿼리가 실행될 때마다 SQL 문장이 매번 컴파일되고 실행됩니다. -
매번 실행할 때마다 SQL 문장이 컴파일되므로 동일한 쿼리를 반복적으로 실행할 때 성능 저하가 발생할 수 있습니다.
-
주로 정적인 쿼리를 사용할 때 유용합니다.
//Statement예시 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class StatementExample { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/mydatabase"; String user = "username"; String password = "password"; try (Connection conn = DriverManager.getConnection(url, user, password); Statement stmt = conn.createStatement()) { // 정적인 SQL 문장 실행 String sql = "INSERT INTO students (id, name, age) VALUES (1, 'John', 25)"; int rowsAffected = stmt.executeUpdate(sql); System.out.println(rowsAffected + "행이 삽입되었습니다."); } catch (SQLException e) { e.printStackTrace(); } } }
-
-
PreparedStatement:
-
PreparedStatement는 동적인 SQL 문장을 실행하는 데 사용됩니다. 미리 컴파일된 쿼리 템플릿을 가지고 있으며, 매번 실행될 때마다 새로운 매개변수 값을 전달하여 실행됩니다. -
쿼리를 한 번만 컴파일하고, 이후에는 컴파일된 쿼리를 재사용하여 실행하기 때문에 성능상의 이점이 있습니다.
-
주로 동적인 쿼리 또는 매개변수화된 쿼리를 사용할 때 유용합니다. 또한, SQL Injection 공격을 방지하는 데에도 도움이 됩니다.
//PreparedStatement예시 import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class PreparedStatementExample { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/mydatabase"; String user = "username"; String password = "password"; try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement pstmt = conn.prepareStatement("INSERT INTO students (id, name, age) VALUES (?, ?, ?)")) { // 동적인 SQL 문장 실행 pstmt.setInt(1, 2); // 첫 번째 매개변수에 값 설정 pstmt.setString(2, "Alice"); // 두 번째 매개변수에 값 설정 pstmt.setInt(3, 22); // 세 번째 매개변수에 값 설정 int rowsAffected = pstmt.executeUpdate(); System.out.println(rowsAffected + "행이 삽입되었습니다."); } catch (SQLException e) { e.printStackTrace(); } } }
-
- PreparedStatement에서 set메서드를 사용하여 값을 설정할 때 sql문장에서 물음표의 순서를 나타낸다. >>첫번째 ? : 1번째 매개변수, 두번째 ? : 2번째 매개변수, 순차적으로 명시해 매개변수를 설정해야 한다.
4. 데이터 수정, 삭제 - executeUpdate + 테이블 생성, 삭제, 목록 불러오기
테이블을 생성하고, 삭제할 때 result에 값이 반환되지 않는다, 그 이유는 executeUpdate, executeQuery 메서드는 영향을 받은 행의 수를 반환하는 것이기 때문이다.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.sql.Statement;
import java.util.Scanner;
public class JDBCTest {
public static void main(String[] args) {
String id = "root", pw = "1234", sql1 = null, sql2 = null, sql3 = null, sql4 = null, name = null, title = null,
contents = null, wTime = null, tableName = null;
int choice = 0, result = 0, no = 0, rCnt = 0;
Scanner sc = new Scanner(System.in);
String url = "jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
sql1 = "insert into board(name, title, contents) values(?, ?, ?)";
// sql2 = "select * from board order by no desc";
sql3 = "update board set contents = ? where no = ?";
sql4 = "delete from board where no = " + no;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (Connection conn = DriverManager.getConnection(url, id, pw);
Statement stmt = conn.createStatement();
PreparedStatement pstmt1 = conn.prepareStatement(sql1);
PreparedStatement pstmt3 = conn.prepareStatement(sql3)) {
while (true) {
System.out.println("메뉴를 선택하세요.");
System.out.println("1. 쓰기");
System.out.println("2. 조회");
System.out.println("3. 수정");
System.out.println("4. 삭제");
System.out.println("5. 종료");
System.out.println("6. 테이블 생성");
System.out.println("7. 테이블 삭제");
System.out.println("8. 테이블 목록 불러오기");
System.out.print("선택: ");
choice = sc.nextInt();
sc.nextLine();
switch (choice) {
case 1:
System.out.println("이름을 입력하세요.");
name = sc.nextLine();
System.out.println("제목을 입력하세요.");
title = sc.nextLine();
System.out.println("내용을 입력하세요.");
contents = sc.nextLine();
pstmt1.setString(1, name);//sql1에 들어갈 첫번째 ? 에 들어갈 값
pstmt1.setString(2, title); //sql1에 들어갈 두번째 ? 에 들어갈 값
pstmt1.setString(3, contents);
result = pstmt1.executeUpdate();
if (result > 0)
System.out.println(result + "데이터 삽입 성공");
break;
case 2:
sql2 = "select * from board order by no desc";
try (ResultSet rs = stmt.executeQuery(sql2)) {
while (rs.next()) {
no = rs.getInt("no");
name = rs.getString("name");
title = rs.getString("title");
contents = rs.getString("contents");
wTime = rs.getString("wTime");
rCnt = rs.getInt("rCnt");
System.out.println("번호: " + no);
System.out.println("이름: " + name);
System.out.println("제목: " + title);
System.out.println("내용: " + contents);
System.out.println("시간: " + wTime);
System.out.println("조회수: " + rCnt);
}
} catch (SQLException e) {
e.printStackTrace();
}
break;
case 3:
System.out.println("수정할 글 번호를 입력하세요.");
no = sc.nextInt();
sc.nextLine();
System.out.println("수정할 글 내용을 입력하세요.");
contents = sc.nextLine();
pstmt3.setString(1, contents);
pstmt3.setInt(2, no);
result = pstmt3.executeUpdate();
if (result > 0)
System.out.println(result + " 데이터 수정 성공");
break;
case 4:
System.out.println("삭제할 글 번호를 입력하세요.");
no = sc.nextInt();
sc.nextLine();
result = stmt.executeUpdate(sql4);
if (result > 0)
System.out.println(result + " 데이터 삭제 성공");
break;
case 5:
System.out.println("프로그램을 종료합니다.");
if (sc != null)
sc.close();
return;
case 6:
System.out.println("만드시려는 테이블 명을 입력해주세요.");
tableName = sc.nextLine();
sql2 = "create table " + tableName + " (no int)"; // 띄어쓰기 주의
try {
result = stmt.executeUpdate(sql2);
System.out.println("테이블 생성 성공");
} catch (SQLException e) {
System.out.println("테이블 생성 실패");
e.getMessage();
}
break;
case 7:
System.out.println("삭제하시려는 테이블 명을 입력해주세요");
tableName = sc.nextLine();
sql2 = "drop table " + tableName; // 띄어쓰기 주의
try {
result = stmt.executeUpdate(sql2);
System.out.println("테이블 삭제 성공");
} catch (SQLException e) {
System.out.println("테이블 생성 실패");
e.getMessage();
}
break;
case 8:
System.out.println("project DB의 테이블들을 불러오겠습니다.");
try (ResultSet rs = stmt.executeQuery(
"select table_name from information_schema.tables where table_schema = 'project'")) {
while (rs.next()) {
tableName = rs.getString("table_name");
System.out.println(tableName);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
} catch (SQLSyntaxErrorException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}