GROUP BY, CONSTRAINT, PRIMARY KEY, FOREIGN KEY

GROUP BY

• 그룹화하여 데이터를 조회한다.

• 특정 컬럼을 그룹화 할때는 GROUP BY

• 특정 컬럼을 그룹화한 결과에 조건을 걸 때는 HAVING

• WHERE과 HAVING을 혼동하는 경우가 있는데 WHERE는 그룹화 하기 전이고, HAVING은 그룹화 한 후에 사용하는 조건이다.

✔ type을 그룹화 하여 name의 갯수를 조회

SELECT type, COUNT(name) AS cnt
FROM abcde
GROUP BY type;

-> type컬럼과 name컬럼을 선택한 것 임

✔ type이 2 이상인 type을 그룹화 하여 name의 갯수를 조회

SELECT type, COUNT(name) AS cnt
FROM abcde
WHERE type > 1 GROUP BY type;

✔ type을 그룹화하여 name의 갯수를 가져온 후, 그 중에서 갯수가 2개 이상인 데이터를 조회

SELECT type, COUNT(name) AS cnt
FROM abcde
GROUP BY type HAVING cnt >= 2;

✔ type이 1 초과인 type을 그룹화하여 name의 갯수를 가져온 후, 그 중에 갯수가 2개 이상인 데이터 조회

SELECT type, COUNT(name) AS cnt
FROM abcde
WHERE type > 1
GROUP BY type HAVING cnt >= 2;

cf) 예제 테이블

✔ 공간을 2개 이상 가진 사람들의 정보를 ID순으로 조회

SELECT * FROM PLACES
WHERE HOST_ID IN
(
SELECT HOST_ID FROM PLACES
GROUP BY HOST_ID
HAVING COUNT(HOST_ID) >= 2
)
ORDER BY ID
;

---

CONSTRAINT(제약조건)

• 데이터의 무결성을 지키기 위해 제약조건에 따라 올바른 데이터만 입력받고, 규칙에 어긋나는 데이터는 입력 및 변경이 되지 않도록 하는 기능이다.

• 제약조건은 CREATE문으로 테이블을 생성할 때나 ALTER문으로 변경할때도 설정 가능하다.

• MySQL에서 사용할 수 있는 제약조건은 다음과 같다.

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • DEFAULT

NOT NULL

• NOT NULL 제약조건을 설정하면 해당 필드는 무조건 데이터를 가지고 있어야 한다.

✔ id 컬럼에 NOT NULL 설정

CREATE TABLE test
(
id int NOT NULL,
name varchar(8)
);

UNIQUE

• UNIQUE 제약 조건을 설정하면 해당 필드는 서로 다른 값을 가져야 한다.

• 중복된 값을 저장할 수 없다.

✔ id 컬럼에 UNIQUE 설정

CREATE TABLE test
(
id int UNOQUE,
name varchar(8)
);

PRIMARY KEY(기본키)

• 테이블의 각 레코드를 식별한다.(빠르게 찾을 수 있는 역할)

• 중복되지 않는 고유값을 포함한다.

• NULL값을 포함할 수는 없다.

• NOT NULL과 UNIQUE 제약조건의 특징을 모두 가진다.

• 테이블당 하나의 기본키를 갖는다.

✔ 하나의 칼럼을 기본키로 설정하는 경우 (table 생성시)

CREATE TABLE person
(
pid int NOT NULL,
name varchar(16),
age int,
sex char,
PRIMARY KEY (pid)
);

✔ 하나의 칼럼을 기본키로 설정하는 경우 (이미 생성된 table에 추가시)

ALTER TABLE person
ADD PRIMARY KEY (pid);

✔ 하나의 칼럼이 기본키로 설정된 경우 삭제 방법

ALTER TABLE person
DROP PRIMARY KEY;

✔ 여러개의 칼럼을 기본키로 설정하는 경우 (한 테이블에 pk가 여러개이면 묶어서 하나의 pk로 봐야한다)(예를들어 이름으로만 구별이 안될때 생년월일까지 따지는 경우)

CREATE TABLE animal
(
name varchar(16) NOT NULL,
type varchar(16) NOT NULL,
age int,
PRIMARY KEY (name, type)
);

✔ 여러개의 칼럼을 기본키로 설정하는 경우

ALTER TABLE animal
ADD CONSTRAINT PK_animal PRIMARY KEY (name, type);


-> CONSTRAINT 제약조건별칭

✔ 여러개의 칼럼이 기본키로 설정된 경우 삭제 방법

ALTER TABLE animal
DROP PRIMARY KEY;

---

FOREIGN KEY(외래키)

• 한 테이블을 다른 테이블과 연결해주는 역할이며, 부모 테이블의 PRIMARY KEY 또는 UNIQUE를 외래키로 지정할 수 있다.

• 외래키가 설정된 테이블에 레코드를 입력하면 부모 테이블의 내용을 참조해서 레코드가 입력된다.

• 외래키 값은 NULL 이거나 부모 테이블의 기본키 값과 동일 해야한다.

• 외래키로 지정할 두 테이블의 필드는 같은 데이터 타입이어야 한다.

• 외래키 조건을 설정할 때 부모 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약 조건이 설정되어 있어야 한다.

• 부모 테이블의 기본키, 고유키가 여러개의 칼럼으로 이루어져 있다면 부모 테이블이 가진 기본키, 고유키 칼럼을 원하는 개수 만큼 묶어서 외래키로 지정할 수 있다.

✔ CREATE TABLE에서 FOREIGN KEY를 지정하는 경우, CONSTRAINT를 사용

CREATE TABLE orders
(
oid int NOT NULL,
order_no varchar(16),
pid int,
PRIMARY KEY (oid),
CONSTRAINT FK_person FOREIGN KEY (pid) REFERENCES person(pid)
);


-> CONSTRAINT 제약조건별칭 FOREIGN KEY (자식테이블컬럼명) REFERENCES 부모테이블명(부모테이블기본키명)

✔ CREATE TABLE에서 FOREIGN KEY를 지정하는 경우, CONSTRAINT를 생략할 수 있다.

CREATE TABLE job
(
jid int NOT NULL,
name varchar(16),
pid int,
PRIMARY KEY (jid),
FOREIGN KEY (pid) REFERENCES person(pid)
);

✔ 자동 생성된(위에서 생략된) CONSTRAINT를 확인하는 방법(테이블 생성 쿼리 명세 확인)

SHOW CREATE TABLE job;


-> SHOW CREATE TABLE job\G 는 더이쁘게 출력해줌

✔ FOREIGN KEY 삭제 방법

ALTER TABLE orders
DROP FOREIGN KEY FK_person;

-> DROP FOREIGN KEY 외래키별칭

✔ TABLE이 생성된 이후에도 ALTER TABLE을 통해 FOREIGN KEY를 지정할 수 있다.

ALTER TABLE orders
ADD FOREIGN KEY (pid) REFERENCES person(pid);

DEFAULT

• DEFAULT 제약 조건은 해당 필드의 기본값을 설정할 수 있게 해준다.

• 만약 레코드를 입력할 때 값을 전달하지 않으면 자동적으로 기본 설정값을 저장한다.

✔ name 컬럼에 DEFAULT 설정

CREATE TABLE test
(
id int,
name varchar(8) DEFAULT 'Anonymous'
);