[MySQL] 기본 SQL 정리 (1) (DDL, DCL)

0. SQL

• Structured Query Language
• 관계형 DB 활용을 위한 표준 언어

📌 SQL 작성 규칙

SQL문법 : 대문자 작성 권장 테이블명
속성명 : 소문자 작성 권장
이름 : 의미가 잘 드러나게 작성, 여러 단어 혼합 시 “_” 사용
주석 : “--” (- 2개)
명령어 끝 : 세미콜론(;) 작성

• SQL을 크게 3가지로 구분 가능
  1. 데이터 정의어 (DDL, Data Definition Language) : 데이터 구조 정의
  2. 데이터 조작어 (DML, Data Manipulation Language) : 데이터 조회, 검색
  3. 데이터 제어어 (DCL, Data Control Language) : DB 접근 권한 관리

 

1. DDL (데이터 정의어)

• CREATE (DB 객체 생성)
• ALTER (DB 객체 변경)
• DROP (DB 객체 삭제)

 

1-1. 테이블 생성

CREATE TABLE 테이블명 (
	속성명 데이터 타입 [NULL/NOT NULL] [DEFAULT 기본값]
	[PRIMARY KEY (속성명 리스트)],  -- 속성 선언에 붙여도 됨 ex) 속성명 데이터 타입 PRIMARY KEY
	[UNIQUE (속성명 리스트)],  -- 속성 선언에 붙여도 됨 ex) 속성명 데이터 타입 UNIQUE
	[FOREIGN KEY (속성명 리스트) REFERENCES 참조테이블명 (참조속성명 리스트)]
);

• 테이블 이름 중복 허용 x
• NULL/NOT NULL : NULL 값 허용 여부
• DEFAULT : 값 입력 x 시 ⇒ 자동으로 입력될 기본값 정의 (기본값 정의 x 면 기본값이 NULL이 됨)
• PRIMARY KEY : 기본키 정의, NULL 값 허용 x!!
• UNIQUE : 유일성, 대체키(또는 후보키) 정의
• FOREIGN KEY : REFERENCES 와 같이 정의, 외래키 정의

 

• CHECK : 값 범위 제한 ⇒ 특정 값만 허용시킴! 제한에 벗어나면 에러 발생

CREATE TABLE 테이블명 (
	속성명 데이터 타입 CHECK (값 범위 제한 내용)
);

-- ex
CREATE TABLE customer (
	id VARCHAR(10) PRIMARY KEY,
	age INT CHECK (age >= 19)
);

• CONSTRAINT : 제약 조건의 이름 정의

CONSTRAINT 제약조건이름 제약조건(UNIQUE, CHECK ...) (적용할 속성명)

-- ex
CREATE TABLE customer (
	id VARCHAR(10),
	age INT,
	CONSTRAINT id_unique UNIQUE (id),
	CONSTRAINT age_check CHECK (age>=19)
);

📌 생성된 제약 조건 확인

SELECT * FROM information_schema.table_constraints;

 

✔️ 데이터베이스의 테이블 목록 확인
- SHOW TABLES;
테이블 생성 SQL 확인 (SQL 그대로 나옴)
- SHOW CREATE TABLE 테이블명;
테이블 구조 확인
- DESC 테이블명;

 

1-2. 테이블 수정

• 컬럼 추가
  ALTER TABLE 테이블명
  ADD [COLUMN] 컬럼명 데이터타입 제약조건

ALTER TABLE customer ADD [COLUMN] birthday DATE NOT NULL;

• 컬럼 수정
  ALTER TABLE 테이블명
  MODIFY [COLUMN] 컬럼명 데이터타입 제약조건

ALTER TABLE customer MODIFY [COLUMN] id VARCHAR(10) PRIMARY KEY;

• 컬럼명 변경
  ALTER TABLE 테이블명
  CHANGE [COLUMN] 기존컬럼명 새로운컬럼명 데이터타입 제약조건

ALTER TABLE customer CHANGE [COLUMN] name korean_name VARCHAR(10) NOT NULL;

• 컬럼 삭제
  ALTER TABLE 테이블명 DROP [COLUMN] 컬럼명

ALTER TABLE customer DROP [COLUMN] address;

• 테이블명 변경
  ALTER TABLE 기존테이블명 RENAME [TO] 새로운테이블명

ALTER TABLE customer RENAME member;

• 제약 조건 추가
  ALTER TABLE 테이블명
  ADD CONSTRAINT 제약조건이름 제약조건 (속성명)

ALTER TABLE customer
ADD CONSTRAINT address_unique UNIQUE (address);

ALTER TABLE customer
ADD CONSTRAINT customer_chk_2 CHECK (age >= 19 AND name = '김민준');

• DEFAULT 제약 조건 수정
  ALTER TABLE 테이블명
  ALTER 속성 SET DEFAULT 새로운기본값

ALTER TABLE customer ALTER address SET DEFAULT '주소 없음';

 

1-3. 테이블 삭제

DROP TABLE 테이블명;

• 삭제하려는 테이블이 참조하는 테이블이 있다면 삭제가 거절됨!
  ⇒ 외래키 관계를 먼저 해제 후 삭제 해야함

 

2. DCL

• 데이터 제어문 : 사용자 계정 생성, 삭제, 권한 부여 및 철회
• CREATE USER (사용자 계정 생성)
• DROP USER (사용자 계정 삭제)
• GRANT (권한 부여)
• REVOKE (권한 철회)

 

2-1. 사용자 계정 생성

• 사용자 계정, 비밀번호, 권한 스키마 등에 관한 시스템 데이터 → ‘mysql’ DB에 저장됨!

📌 사용자_계정 = 아이디@접속_호스트 (접속 호스트 : 서버로 접속하는 IP 주소)

CREATE USER 사용자_계정 IDENTIFIED BY '비밀번호';

-- ex
-- % : 어느 컴퓨터에서나 자유롭게 접속 가능
CREATE USER 'user1'@'%' IDENTIFIED BY 'password1234';

-- localhost : 서버가 구동중인 로컬호스트에서만 접속 가능
CREATE USER 'user2'@'localhost' IDENTIFIED BY '1234';

✔️ 생성된 계정 정보 확인
- SELECT host, user FROM mysql.user;

 

2-2. 사용자 계정 삭제

DROP USER 사용자_계정

-- ex
DROP USER 'user1'@'%';
DROP USER 'user2'@'localhost';

 

2-3. 권한 부여

- 권한 내용

• SELECT, INSERT, UPDATE, DELETE 
• REFERENCES : 외래키 선언 권한
• USAGE : 도메인 사용 권한
• ALL PRIVILEGES : 모든 권한

- 권한 대상

• DB명.객체이름(테이블명)

📌 WITH GRANT OPTION : 권한을 부여받은 사용자가 다른 사용자에게 부여받은 권한 부여 가능!

GRANT 권한_내용 ON 권한_대상 TO 사용자_계정 [WITH GRANT OPTION];

-- ex
GRANT ALL ON *.* TO 'manager'@'localhost' WITH GRANT OPTION;
GRANT INSERT, UPDATE, DELETE ON univDB.student TO 'user1'@'localhost';

✔️ 현재 접속 사용자의 권한 표시
- SHOW GRANTS;

 

2-4. 권한 철회

GRANT 권한_내용 ON 권한_대상 FROM 사용자_계정 [RESTRICT | CASCADE];

-- ex
GRANT ALL ON *.* FROM 'manager'@'localhost';
GRANT DELETE ON univDB.* FROM 'user1'@'localhost';

• RESTRICT : 취소하려는 권한으로 인해 다른 권한이 함께 취소되어야 하는 경우 → 취소 자체가 수행 X !
• CASCADE : 취소하려는 권한으로 인해 다른 권한이 함께 취소되어야 하는 경우 → 그 권한도 같이 취소됨 !