MySQL은 인스턴스 내부에 여러 데이터베이스를 둘 수 있다. 그리고 하나의 데이터베이스 내부에 여러 테이블, 뷰 등을 둘 수 있다.
🎈MySQL 접속 명령어
워크벤치(GUI)를 사용하는 방법도 있지만
cmd(CLI)에서 다음을 통해서 MySQL에 접속할 수 있다.
mysql -u 유저명 -p이후 패스워드 입력
🎈데이터베이스 명령어
SHOW 데이터베이스 목록 보기
SHOW DATABASES;명명 규칙
- 영어로 된 이름을 권장
- 30자 이내의 제한 글자 수
- snake case (공백 위치에
_)가 권장됨 - 예약어 키워드는 사용 불가능
CREATE 데이터베이스 생성
CREATE DATABASE 데이터베이스명;USE 데이터베이스 사용
USE DATABASE 데이터베이스명;테이블 명령어 사용 전에 사용할 데이터를 지정해주어야 한다.
🎈테이블 명령어
SHOW 테이블 목록 보기
SHOW TABLES;DESC 테이블 구조 보기
DESC 테이블명;테이블 상태 확인
CHECK TABLE 테이블명;📌CREATE 테이블 생성
CREATE TABLE 테이블명(
컬럼명1 데이터타입 NOT NULL,
컬럼명2 데이터타입,
컬럼명3 데이터타입
PRIMARY KEY(컬럼명1)
FOREIGN KEY(컬럼명2) REFERENCES 테이블명2(테이블2_컬럼명4)
);NULL이어서는 안되는 컬럼 뒤에 NOT NULL을 붙여서 명시할 수 있다.
제약 조건들에 이름을 줄 때는 CONSTRAINT 제약조건이름 제약조건으로 제약 조건을 설정할 수 있다.
PK
PK는 NOT NULL 설정하지 않아도 자동으로 NOT NULL 설정 됨.
PRIMARY KEY로 지정하는 경우에는 해당 컬럼 뒤에 PRIMARY KEY를 붙이거나, 마지막 줄에 PRIMARY KEY(컬럼명)으로 지정 가능.
DESC로 확인하면 해당 컬럼의 Key는 PRI로 설정됨.
만약 PRIMARY KEY에 이름을 주고 싶다면,
CONSTRAINT PRIMARY KEY 키이름 (컬럼명)으로 PK에 이름 부여 가능.
SHOW KEYS FROM 테이블명; -- 테이블의 키 이름 확인
SHOW INDEX FROM 테이블명;
-- 위 두 명령어는 같은 기능을 함위 명령을 사용해 테이블의 키 이름들을 확인할 수 있음
FK
FOREIGN KEY 지정 시 REFERENCES 키워드 사용. DESC로 확인하면 해당 컬럼의 Key는 MUL로 설정됨.(다중 사용 컬럼)
- 외래 테이블 : FOREIGN KEY가 선언된 테이블
- 기준 테이블 : FOREIGN KEY가 참조하고 있는 테이블
FOREIGN KEY에 추가적인 제약조건을 주는 경우
ON UPDATE CASCADE;
ON DELETE CASCADE;FOREIGN KEY 생성 시 바로 뒷 줄에 같이 작성.
- ON UPDATE CASCADE : 기준 테이블에서 UPDATE 시 외래 테이블도 자동으로 UPDATE
- ON DELETE CASCADE : 기준 테이블에서 DELETE 시 외래 테이블도 자동 DELETE
별도로 지정하지 않는다면 ON UPDATE NO ACTION, ON DELETE NO ACTION이 기본 설정 값이다.
UNIQUE
PK와 비슷하지만 다음과 같은 차이점을 가진다.
- PRIMARY KEY : 행을 고유하게 식별할 수 있는 유일한 값. NULL을 허용하지 않는다.
- UNIQUE : 특정 컬럼의 고유성을 보장. NULL을 허용
즉, 어떤 컬럼에 UNIQUE 제약 조건을 거는 경우에 그 컬럼은 대체키(Alternate Key)이다.
만약 UNIQUE에 NOT NULL 조건이 걸려있다고 하더라도 테이블의 기본키로 간주되지는 않는다.
외래키 제약조건 확인
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.referential_constraints
WHERE CONSTRAINT_SCHEMA = DB명;위 명령어를 사용해 해당 DB에 걸려있는 외래키 제약조건을 확인할 수 있다.
외래키 제약조건 비활성화
수정, 삭제 시 외래키 제약조건을 강제로 무시하는 경우,
SET FOREIGN_KEY_CHECKS=0; -- 외래키 제약조건 비활성화
-- 수정 삭제 작업...
SET FOREIGN_KEY_CHECKS=0; -- 외래키 제약조건 활성화주의 : 데이터 무결성 문제 발생 가능성이 있음.
이보다 ON DELETE CASCADE, ON UPDATE CASCADE를 사용하는 것을 권장.
INSERT 데이터 삽입
INSERT INTO 테이블명(컬럼명1, 컬럼명2, ...)
VALUES(값1, 값2, ...);테이블 내의 모든 컬럼에 데이터를 삽입하는 경우, 컬럼명을 명시해줄 필요가 없다.
DROP 테이블 삭제
테이블 자체를 삭제. 테이블을 비롯한 내부 데이터까지 모두 삭제됨.
DROP TABLE 테이블명;주의 : 제약조건이 걸려있는 경우(FK) 삭제되지 않는다. 제약조건 먼저 삭제 후 테이블 삭제 필요.
DELETE 데이터(행) 삭제
DELETE FROM 테이블명
WHERE 조건;WHERE문을 작성하지 않는 경우, 테이블 내부의 모든 데이터(행)이 삭제된다.
TRUNCATE 모든 데이터 삭제
TRUNCATE TABLE 테이블명;테이블의 구조를 남기지만 모든 데이터를 삭제.
DELETE에 조건을 주지 않는 것과 같은 기능을 하지만, 성능이 더 좋다.
UPDATE 데이터 수정
UPDATE 테이블명
SET 컬럼1 = 값1, 컬럼2 = 값2, ...
WHERE 조건;WHERE문을 작성하지 않는 경우, 테이블 내부의 모든 데이터가 변경된다.
CREATE INDEX ON 인덱스
인덱스를 사용하면 조회 성능을 높일 수 있다.
CREATE INDEX 인덱스명
ON 테이블명(컬럼명1, 컬럼명2, ...);INDEX 앞에 UNIQUE 옵션을 주어서 중복값을 비허용할 수 있다.
📌ALTER 테이블 변경
테이블 이름 변경
ALTER TABLE 기존테이블명
RENAME 새테이블명;컬럼 추가
ALTER TABLE 테이블명
ADD 컬럼명;기본적으로 가장 뒤에 추가됨.
맨 앞에 추가하는 경우에는 마지막에 FIRST를 붙여주기
특정 컬럼 뒤에 추가하는 경우에는 마지막에 AFTER 컬럼명을 붙여주기
PK 추가
ALTER TABLE 테이블명
ADD PRIMARY KEY (컬럼명);PK 삭제
ALTER TABLE 테이블명 DROP PRIMARY KEY;단, 이 명령을 실행하려면 해당 PK가 다른 FK와 연결되지 않은 상태여야 한다.
외래키 제약조건 확인 명령으로 해당 테이블에 걸려있는 제약조건명을 확인할 수 있다. 그리고 해당 제약조건명을 사용해 FK를 삭제한 후, PK를 삭제할 수 있다.
FK 삭제
ALTER TABLE 테이블명 DROP FOREIGN KEY 제약조건명;CONSTRAINT + PK 추가
ALTER TABLE 테이블명
ADD CONSTRAINT 키이름 PRIMARY KEY (컬럼명);컬럼 삭제
ALTER TABLE 테이블명
DROP 컬럼명;컬럼명 변경
ALTER TABLE 테이블명
RENAME COLUMN 기존컬럼명 TO 새컬럼명컬럼 데이터 타입 변경
ALTER TABLE 테이블명
MODIFY 컬럼명 데이터타입;컬럼명 + 데이터 타입 변경
ALTER TABLE 테이블명
CHANGE 기존컬럼명 새컬럼명 데이터타입📌SELECT 데이터 조회
SELECT 컬럼명1, 컬럼명2, ...
FROM 테이블명
WHERE 조건;SELECT 뒤에 컬럼명을 명시하는 대신 *를 사용하게 되면 테이블의 모든 컬럼을 가져오게 됨.
컬럼명에 공백이 존재하는 경우 백틱 ``으로 감싼다.
WHERE문은 선택적.
비교 연산자
SELECT *
FROM userTBL
WHERE height >= 160;>, <, >=, <=, !=, = 를 사용해 값을 비교할 수 있다.
BETWEEN
SELECT *
FROM userTBL
WHERE height BETWEEN 160 AND 180;BETWEEN 과 AND를 사용해서 특정한 범위 내에 값이 속하는지 확인할 수 있다.
IN
SELECT *
FROM userTBL
WHERE addr IN ('서울', '부산', '전주');IN 을 사용하여 괄호 내의 값들 중 하나라도 속하는 것이 있는지 확인할 수 있다.
서브쿼리
SELECT *
FROM userTBL
WHERE height >= (SELECT height FROM userTBL WHERE height = 'John');서브쿼리란 where절에서 특정 조건을 만족하는 컬럼을 추출하기 위해 사용되는 쿼리를 의미한다. 괄호로 묶여있는 것이 특징이다.
만약 서브쿼리의 결과가 2행 이상이라면 위의 쿼리는 오작동하게 된다.
그러한 경우 서브쿼리 앞에 ANY 키워드를 붙여 서브쿼리로부터 나온 결과 중 어느 것이라도 상관 없음을 명시한다.
SELECT *
FROM userTBL
WHERE height >= ANY(SELECT height FROM userTBL WHERE addr = '전주');=> 전주에 사는 사람들 중 한 사람의 키라도 넘어서는 모든 사람들의 데이터를 가져옴
=> 즉, 전주에 사는 사람들 중 제일 작은 사람의 키보다 큰 모든 사람들의 데이터를 가져옴
ORDER BY 정렬
SELECT *
FROM 테이블명1
ORDER BY 컬럼1;ORDER BY는 옵션을 주지 않으면 기본적으로 ASC(오름차순) 정렬된다.
컬럼1을 기준으로 오름차순 정렬된 데이터를 가져옴.
만약 내림차순 정렬을 하고 싶다면 기준 컬럼 뒤에 DESC 옵션을 주면 된다.
또한 컬럼1을 기준으로 먼저 정렬을 한 후, 컬럼2를 기준으로 정렬을 하고 싶다면 기준이 되는 컬럼들을 ,로 구분하면 된다.
SELECT *
FROM 테이블명1
ORDER BY 컬럼1 DESC, 컬럼2 ASC;=> 컬럼1을 기준으로 내림차순 정렬을 한 후, 컬럼1에서 같은 값을 가지는 행이 있을 경우 컬럼2를 기준으로 오름차순 정렬
ORDER BY 절은 대용량 데이터에서 성능을 상당히 떨어뜨릴 수 있으므로 되도록 사용하지 않는다.
범위를 주어서 페이징과 함께 사용하는 것이 바람직하다.
LIMIT, OFFSET
SELECT *
FROM 테이블명1
ORDER BY 컬럼1 DESC, 컬럼2 ASC LIMIT 10 OFFSET 0;LIMIT은 읽어오는 행의 최대 갯수를 의미하며, OFFSET은 말 그대로 오프셋이다.
OFFSET을 0으로 주는 경우는 첫번째 행부터 읽게 된다.
OFFSET이 10이라면 11번째 행부터 읽게 된다.
DISTINCT
DISTINCT를 사용해 중복을 제거할 수 있다.
같은 내용을 가지는 행이 2행 이상 조회될 경우, 중복을 제거한 데이터를 보여준다.
SELECT DISTINCT name
FROM userTBL;=> 중복이 제거된 모든 이름을 가져옴
📌Aggregation
Aggregation 이란 집계 함수를 의미하며, 특정 컬럼, 표현, 위치에 맞추어 데이터를 집계한다.
- SUM() 합계
- AVG() 평균
- MIN() 최소
- MAX() 최대
- COUNT() 행의 갯수 -> NULL 값은 제외됨
- STDEV() 표준편차
- VAR_SAMP() 분산
GROUP BY, HAVING과 함께 사용된다.
GROUP BY
SELECT userID, SUM(amount)
FROM buyTBL
GROUP BY userID;=> userID로 그룹화하여 amount를 모두 합산
AS 문을 사용해 집계 결과의 별칭을 줄 수 있음(AS는 생략 가능)
SELECT userID, SUM(amount) '총 구매 수량'
FROM buyTBL
GROUP BY userID;HAVING
집계 함수와 관련하여 만들어지는 컬럼에 대해서는 WHERE이 아닌 HAVING을 사용해 조건을 줘야 한다.
SELECT userID, SUM(amount) '총 구매 수량'
FROM buyTBL
GROUP BY userID
HAVING SUM(amount) > 5;HAVING으로 조건을 줄 때는 집계 함수의 별칭을 사용해 조건을 줄 수 없으므로 원래 이름으로 줘야함.
HAVING은 반드시 GROUP BY 뒤에 와야 한다.
(ORDER BY에서는 집계 함수에 대해서 조건을 줄 수 있음)
ROLLUP
그룹별 부분 합계와 총 합계를 계산하기 위해서 ROLLUP을 사용할 수 있다.
SELECT num, groupName, SUM(amount) '총 구매 수량'
FROM buyTBL
GROUP BY groupName, num
WITH ROLLUP;GROUP BY에서 지정된 컬럼의 순서가 중요하다.
ROLLUP을 사용하게 되면 먼저 groupName을 기준으로 그룹화를 하고 num을 기준으로 그룹화를 하여 부분 합계와 총 합계를 계산한다.
부분 합계 혹은 총 합계의 컬럼 값은 NULL이 들어가게 된다.
예시
| num | groupName | 총 구매 금액 |
|---|---|---|
| 4 | 간식 | 500 |
| 7 | 간식 | 2000 |
| NULL | 간식 | 2500 |
| 1 | 미용 | 1000 |
| NULL | 미용 | 1000 |
| 9 | 식대 | 900 |
| NULL | 식대 | 900 |
| 8 | 음료 | 1000 |
| NULL | 음료 | 1000 |
| NULL | NULL | 5400 |
groupName, num을 기준으로 그루핑을 하여, 그룹 내의 항목들을 보여준다. 그리고 각 그룹의 마지막 행에는 num 컬럼에 NULL 값이 들어가고 부분 합계가 계산된다.
그리고 가장 마지막 줄에는 num과 groupName 컬럼에 모두 NULL 값이 들어가고 총 합계가 계산된다.
🎈뷰 명령어
CREATE AS 뷰 생성
뷰는 테이블을 참조하는 논리 테이블로 제한된 역할을 가진 사용자에게 제한된 데이터를 보여주기 위해 주로 사용됨.
AS 를 사용하여 생성하는 뷰를 명시
CREATE VIEW 뷰이름
AS
SELECT 컬럼명1, 컬럼명2, ...
FROM 테이블명
WHERE 조건;SELECT 뷰 조회
SELECT 컬럼명1, 컬럼명2, ...
FROM 뷰이름
WHERE 조건;DROP 뷰 삭제
DROP VIEW 뷰이름;뷰 주의사항
- 뷰에서 삽입, 삭제를 진행하는 경우, 뷰가 테이블을 참조하고 있기 때문에 테이블 내의 정보도 삽입, 삭제가 이루어짐.
- 뷰가 참조하는 테이블을 삭제하게 되면 경고 없이 삭제됨. 그리고 뷰를 조회하면 에러 발생
생성된 뷰 정보 보기
SHOW CREATE VIEW 뷰이름;뷰의 상태 확인
CHECK TABLE 뷰이름;테이블의 상태 확인 명령어와 같은 명령어로 뷰 상태 확인 가능.
- 정상 상태 :
Msg_type값이status - 에러 상태 :
Msg_type값이error
Msg_text를 확인
🎈백업 명령어
데이터베이스를 실수로 삭제하는 경우를 대비하여 백업을 해두고 이를 사용해 복원할 수 있다.
cmd에서 해당 명령어 입력
백업
mysqldump -u 유저명 -p 데이터베이스명 > 경로\파일명.sql이후 패스워드 입력
복원
백업 파일을 사용해서 복원
mysql -u 유저명 -p 데이터베이스명 < 경로\파일명.sql이후 패스워드 입력
백업 파일 내용대로 데이터베이스가 복원된다.
