[Practice] 외래키, 조인, 집합 연산자, 서브 쿼리

1. 외래키

1) 테이블 생성

customer의 id와 ord의 name과 외래키 관계가 되도록 생성한다.

DROP DATABASE IF EXISTS foreignKey_test_db; 
CREATE DATABASE foreignKey_test_db; 
USE foreignKey_test_db;


DROP TABLE IF EXISTS customer; 
CREATE TABLE customer ( 
  c_id CHAR(32) PRIMARY KEY COMMENT '아이디', 
  c_name CHAR(32) NOT NULL COMMENT '이름', 
  c_age TINYINT UNSIGNED NOT NULL COMMENT '나이', 
  rank ENUM('GOLD','SILVER','BRONZE','NO HUMAN') NOT NULL DEFAULT 'NO HUMAN' COMMENT '등급' 
)ENGINE=InnoDB;


DROP TABLE IF EXISTS ord; 
CREATE TABLE ord ( 
  o_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '주문번호', 
  o_name CHAR(32) NOT NULL COMMENT '주문고객', 
  o_prod VARCHAR(50) NOT NULL COMMENT '상품명', 
  o_date DATETIME NOT NULL, 
  CONSTRAINT ord_fk_id 
    FOREIGN KEY (o_name) 
    REFERENCES customer (c_id) 
    ON DELETE RESTRICT 
    ON UPDATE RESTRICT 
)ENGINE=InnoDB;

2) 문제 발생 원인 확인

외래키를 사용했을 때, 값을 삽입하거나 변경하면서 발생하는 원인을 찾는다. (참조 컬럼에 없는 데이터는 외래키 컬럼에 입력이 불가능하다.)

- 실습 쿼리문

INSERT INTO customer(c_id, c_name, c_age) VALUES ('kim', '김영수', '28'); 
INSERT INTO customer(c_id, c_name, c_age) VALUES ('anzi', '안지환', '28'); 
INSERT INTO customer(c_id, c_name, c_age) VALUES ('ari', '아리수', '28');

INSERT INTO ord(o_name, o_prod, o_date) VALUES ('ari', '물', now()); 
INSERT INTO ord(o_name, o_prod, o_date) VALUES ('anzi', '축구공', now()); 

INSERT INTO ord(o_name, o_prod, o_date) VALUES ('ever', '컴퓨터', now());  // 문제 발생

UPDATE customer SET c_id='kill' WHERE c_id='kim'; 
UPDATE customer SET c_id='muri' WHERE c_id='ari'; // 문제 발생

 

 

 

2. 조인

1) 테이블 생성

DROP DATABASE IF EXISTS bns; 
CREATE DATABASE bns; 
USE bns; 

DROP TABLE IF EXISTS user; 
CREATE TABLE user ( 
  id VARCHAR(20) NOT NULL COMMENT '유저 아이디', 
  name VARCHAR(20) NOT NULL COMMENT '유저 이름', 
  level TINYINT UNSIGNED NOT NULL COMMENT '유저 레벨', 
  money INT UNSIGNED NOT NULL COMMENT '소지금', 
  choo VARCHAR(20) NULL COMMENT '추천인', 
  PRIMARY KEY(id), 
  FOREIGN KEY(choo) REFERENCES user(id)  
) COMMENT '캐릭터정보';  

DROP TABLE IF EXISTS item; 
CREATE TABLE item ( 
  name VARCHAR(50) NOT NULL COMMENT '아이템 이름', 
  cat VARCHAR(32) NOT NULL COMMENT '아이템 종류', 
  level TINYINT UNSIGNED NOT NULL COMMENT '착용 가능 레벨', 
  atk INT UNSIGNED NOT NULL COMMENT '공격력', 
  def INT UNSIGNED NOT NULL COMMENT '방어력', 
  PRIMARY KEY(name) 
) COMMENT '게임아이템';  

DROP TABLE IF EXISTS market; 
CREATE TABLE market ( 
  no INT UNSIGNED NOT NULL COMMENT '거래 번호', 
  seller VARCHAR(20) NOT NULL COMMENT '판매자', 
  item VARCHAR(50) NOT NULL COMMENT '판매 아이템', 
  price INT UNSIGNED NOT NULL COMMENT '판매 가격', 
  PRIMARY KEY(no), 
  FOREIGN KEY(seller) REFERENCES user(id), 
  FOREIGN KEY(item) REFERENCES item(name) 
) COMMENT '아이템거래'; 

2) 데이터 삽입

INSERT INTO user VALUES ('chulsu', '김철수', 70, 100000, NULL); 
INSERT INTO user VALUES ('jrich', '안지환', 7, 1000, NULL); 
INSERT INTO user VALUES ('ruina', '김나라', 47, 49000, 'chulsu'); 
INSERT INTO user VALUES ('jamie', '조아라', 29, 12000, 'ruina'); 
INSERT INTO user VALUES ('yoma', '아리수', 1, 500, NULL); 

INSERT INTO item VALUES ('곤륜검', 'sword', 50, 540, 0); 
INSERT INTO item VALUES ('염화검', 'sword', 36, 147, 0); 
INSERT INTO item VALUES ('요마검', 'sword', 20, 64, 0); 
INSERT INTO item VALUES ('풍뢰검', 'sword', 45, 263, 0); 
INSERT INTO item VALUES ('단검', 'sword', 1, 1, 0); 

INSERT INTO market VALUES (1, 'chulsu', '곤륜검', 75000); 
INSERT INTO market VALUES (2, 'chulsu', '풍뢰검', 40000); 
INSERT INTO market VALUES (3, 'ruina', '염화검', 36000); 
INSERT INTO market VALUES (4, 'yoma', '단검', 10);

3) 조인

순서	내용
1	user 테이블과 market 테이블 크로스 조인하여 모든 내용을 출력
2	market의 물품 정보와 물품을 판매하는 user의 정보를 출력
3	market에 물품 정보와 물품을 판매하는 user의 정보를 출력
4	'chulsu'가 판매하는 물품 정보와 user 정보 출력
5	market에서 판매되는 물품 중 '곤륜검'의 판매 정보를 출력
6	'곤륜검'을 판매하는 user의 정보 출력
7	세타 조인으로 '곤륜검'을 팔지 않는 user의 정보 출력

- 실습 쿼리문

/* user 테이블과 market 테이블 크로스 조인하여 모든 내용을 출력 */
SELECT * FROM user CROSS JOIN market;
SELECT * FROM user CROSS JOIN market CROSS JOIN item;
SELECT * FROM user JOIN market;

/* market의 물품 정보와 물품을 판매하는 user의 정보를 출력 */

SELECT * FROM market INNER JOIN user WHERE seller = id;

SELECT * FROM market JOIN user WHERE seller = id;

SELECT * FROM market JOIN user WHERE market.seller = user.id;

SELECT * FROM market m JOIN user u HWERE m.seller = u.id;

 

/* market에 물품 정보와 물품을 판매하는 user의 정보를 출력 */
SELECT m.item, m.price, u.name FROM market m JOIN user u WHERE m.seller = u.id;

 

/* 'chulsu'가 판매하는 물품 정보와 user 정보 출력 */
SELECT u.name, u.level, m.item, m.price FROM user u JOIN market m WHERE u.id = m.seller AND u.id = 'chulsu';

 

/* market에서 판매되는 물품 중 '곤륜검'의 판매 정보를 출력 */
SELECT m.item, m.price, i.level, i.atk, i.def, u.name FROM market m
  JOIN user u ON m.seller = u.id
  JOIN item i ON m.item = i.name
  WHERE m.item = '곤륜검';

 

/* '곤륜검'을 판매하는 user의 정보 출력 */
SELECT * FROM user u JOIN market m ON u.id = m.seller WHERE m.item = '곤륜검';

 

/* 세타 조인으로 '곤륜검'을 팔지 않는 user의 정보 출력 */
SELECT * FROM user u JOIN market m ON u.id = m.seller WHERE m.item <> '곤륜검'; 

 

 

 

3. 서브 쿼리, 비교 연산자, 상관/비상관 쿼리

순서	내용
1	market에서 '곤륜검'을 판매하는 user의 정보를 출력
2	market에서 '곤륜검'이나 '풍뢰검'에 대한 정보를 출력
3	market에서 '곤륜검'이나 '풍뢰검'을 제외한 정보를 출력
4	market에서 '곤륜검'을 판매하는 user의 정보를 출력 (다중 행 반환)
5	market에서 가장 많은 물품을 판매하는 user의 정보를 출력 (가장 많은 물품을 판매하는 user는 한 명)
6	market에서 가장 많은 물품을 판매하는 user의 정보를 출력 (가장 많은 물품을 판매하는 user가 두 명 이상인 경우를 고려)
7	market에서 물품을 하나라도 구매 가능한 user의 정보를 출력
8	market에 어떤 물품이라도 구매 가능한 user의 정보를 출력
9	user 자신의 판매 물품 가격의 총합이 그 user의 재산보다 많은 user의 정보를 출력
10	market에 물품을 (판매/판매하지 않는) user를 검색

- 실습 쿼리문

/* market에서 곤륜검을 판매하는 user의 정보를 출력 */
SELECT * FROM user WHERE id = ( 
  SELECT seller FROM market WHERE item = '곤륜검'
);

/* market에서 '곤륜검'이나 '풍뢰검'에 대한 정보를 출력 */

SELECT * FROM market WHERE item = '곤륜검' OR item = '풍뢰검';

SELECT * FROM market WHERE item IN ('곤륜검', '풍뢰검');

 

/* market에서 '곤륜검'이나 '풍뢰검'을 제외한 정보를 출력 */

SELECT * FROM market WHERE item NOT IN ('곤륜검', '풍뢰검');

 

/* market에서 '곤륜검'을 판매하는 user의 정보를 출력 (다중 행 반환) */

SELECT * FROM user WHERE id IN (

  SELECT seller FROM market WHERE item = '곤륜검'

);

 

/* market에서 가장 많은 물품을 판매하는 user의 정보를 출력 (가장 많은 물품을 판매하는 user는 한 명) */

SELECT * FROM user WHERE id = (
  SELECT seller FROM market GROUP BY seller ORDER BY count(*) DESC LIMIT 1
);

 

/* market에서 가장 많은 물품을 판매하는 user의 정보를 출력 (1) - 가장 많은 물품을 판매하는 user가 두 명 이상인 경우를 고려, SELECT MAX(count(*)) FROM market GROUP BY seller; 는 불가능 */

SELECT * FROM user WHERE id IN (
  SELECT seller FROM market GROUP BY seller HAVING count(*) >= ALL (
    SELECT count(*) FROM market GROUP BY seller
  )
);

 

/* market에서 가장 많은 물품을 판매하는 user의 정보를 출력 (2) - 가장 많은 물품을 판매하는 user가 두 명 이상인 경우를 고려, SELECT MAX(count(*)) FROM market GROUP BY seller; 는 불가능 */

SELECT * FROM user WHERE id IN (
  SELECT seller FROM market GROUP BY seller HAVING count(*) = (
    SELECT MAX(mycount) FROM (

      SELECT count(*) mycount FROM market GROUP BY seller

    ) t
  )
);

 

/* market에서 물품을 하나라도 구매 가능한 user의 정보를 출력 */

SELECT * FROM user WHERE money >= ANY (SELECT price FROM market);

 

/* market에 어떤 물품이라도 구매 가능한 user의 정보를 출력 */

SELECT * FROM user WHERE money >= ALL (SELECT price FROM market);

 

/* user 자신의 판매 물품 가격의 총합이 그 user의 재산보다 많은 user의 정보를 출력 */

SELECT * FROM user WHERE money < (
  SELECT sum(price) FROM market WHERE seller = id
);

 

/* market에 물품을 (판매/판매하지 않는) user를 검색 */

SELECT * FROM user WHERE EXISTS (SELECT * FROM market WHERE seller = id);
SELECT * FROM user WHERE NOT EXISTS (SELECT * FROM market WHERE seller = id);