SQL DDL, DCL

yeoni·2024년 1월 12일

SQL

목록 보기

2/11

1. Database 관리

데이터베이스 목록 확인, 생성, 사용, 삭제

# 현재 database 목록 확인 
SHOW DATABASES;

# Database 이름을 지정하여 생성
CREATE DATABASE dbname;
CREATE DATABASE dbname DEFAULT CHARACTER SET utf8mb4;

# 해당 데이터베이스로 이동 (사용)
USE dbname;

# Database 삭제
DROP DATABASE dbname;

2. User 관리

조회, 생성, 삭제

사용자 정보는 mysql 에서 관리하므로 일단 mysql 데이터베이스로 이동후 조회

USE mysql;
# 조회
SELECT host, USER FROM USER;

# 현재 PC 에서만 접속 가능한 사용자를 비밀번호와 함께 생성
CREATE USER 'username'@'localhost' identified BY 'password';

# 외부에서 접속 가능한 사용자를 비밀번호와 함께 생성
CREATE USER 'username'@'%' identified BY 'password';

# 접근 범위에 따라 같은 이름의 사용자여도 별도로 삭제
DROP USER 'username'@'localhost'
DROP USER 'username'@'%'

권한 관리

권한 목록 확인, 부여, 삭제

CREATE DATABASE testdb;
CREATE USER 'test'@'LOCALHOST' identified BY '1234';

# 사용자 비밀번호 변경
SET PASSWORD FOR 'TEST'@LOCALHOST = 'TEST';

# 사용자에게 부여된 모든 권한 목록을 확인
SHOW GRANTS FOR 'test'@'LOCALHOST';

# 사용자에게 특정 데이터베이스의 모든 권한을 부여
GRANT ALL ON testdb.* TO 'test'@'LOCALHOST';

# 사용자에게 특정 데이터베이스의 모든 권한을 삭제 
REVOKE ALL ON testdb.* FROM 'test'@'LOCALHOST';

# 권한: CREATE, ALTER, DROP, INSERT, DELETE, UPDATE, SELECT 등
# 특정 권한 부여
GRANT SELECT, DELETE ON PRACTICE.회원테이블 TO 'TEST'@LOCALHOST;

# 특정 권한 제거
REVOKE DELETE ON PRACTICE.회원테이블 FROM 'TEST'@LOCALHOST;

# 수정내용이 적용이 되지 않은 경우 새로고침
FLUSH PRIVILEGES;

3. Table

Table 생성, 확인

임시 테이블도 생성이 가능하다 대신 서버 연결 종료시 자동으로 삭제된다.

CREATE DATABASE zerobase DEFAULT CHARACTER SET utf8mb4;

# Table 생성 문법
CREATE TABLE tablename(
	columnname datatype,
    columnname datatype,
    id int,
    name varchar(16),
);

# 임시 테이블 생성
CREATE TEMPORARY TABLE tablename(
	columnname datatype,
    columnname datatype,
    id int,
    name varchar(16),
); 

CREATE TABLE tablename1
SELECT A.*, B.column
FROM tablename2 AS A
INNER JOIN tablename3 AS B
ON A.column = B.column;


# Table 목록 확인
SHOW TABLES;

# Table 정보 확인 
DESC tablename;

Table 변경, 컬럼 추가, 컬럼 변경, 삭제

# Table 이름 변경
ALTER TABLE tablename RENAME newname;

# Table Column 추가
ALTER TABLE tablename ADD COLUMN columnname datatype;

# Table Column 변경 - datatype
ALTER TABLE tablename MODIFY COLUMN columnname datatype;

# Table Column 변경 - name
ALTER TABLE tablename CHANGE COLUMN columnname new_columnname new_datatype;
ALTER TABLE tablename RENAME COLUMN columnnamen to new_columnname;

# Table Column 삭제 
ALTER TABLE tablename DROP COLUMN columnname;

# Table 삭제
DROP TABLE tablename;

PRIMARY KEY(기본 키)

테이블의 각 레코드를 식별
중복되지 않은 고유값을 포함
NULL 값을 포함할 수 없음
테이블 당 하나의 기본키를 가짐

# 하나의 칼럼을 기본키로 설정하는 경우
CREATE TABLE person(
	pid int NOT NULL,
    name varchar(16),
    age int,
    sex char,
    PRIMARY KEY (pid)
);

# 여러개의 칼럼을 기본키로 설정하는 경우
CREATE TABLE animal(
	name varchar(16) NOT NULL,
    type varchar(16) NOT NULL,
    age int,
    PRIMARY KEY (name, type)
);

PRIMARY KEY 삭제(삭제하는 방법은 동일)

ALTER TABLE tablename DROP PRIMARY KEY;

기존 테이블에서 PRIMARY KEY 생성

ALTER TABLE tablename ADD PRIMARY KEY (column1, column2..);

# CONSTRAINT 생략해도 자동으로 생성
ALTER TABLE tablename ADD CONSTRAINT PK_table PRIMARY KEY (column1, column2..);

FOREIGN KEY (외래키)

한 테이블을 다른 테이블과 연결해주는 역할이며, 참조되는 테이블의 항목은 그 테이블의 기본키 (혹은 단일값)
1) CREATE TABLE 에서 FOREIGN KEY를 지정하는 경우

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 생략
CREATE TABLE job(
	jib int NOT NULL,
    name varchar(16),
    pid int,
    PRIMARY KEY (jid),
    FOREIGN KEY(pid) REFERENCES person(pid)
);

# 자동 생성된 CONSTRAINT 확인하는 방법
SHOW CREATE TABLE tablename;

2) 기존 table에서 FOREIGN KEY를 지정(ALTER TABLE 이용)

ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_table(REF_column);

3) FOREIGN KEY 삭제

ALTER TABLE tablename DROP FOREIGN KEY FK_constraint;

4) 예시 (경찰서 이름이 형식이 다르게 표시되는 각 테이블 이용)

# police_station.name 기본키로 설정
ALTER TABLE police_station ADD PRIMARY KEY (name);

# crime_status 테이블에 Foreign Key 로 사용할 Column 추가
ALTER TABLE crime_status ADD COLUMN reference VARCHAR(16);

# Foreign Key 생성
ALTER TABLE crime_status ADD FOREIGN KEY (reference) REFERENCES police_station(name);

# Foreign Key 값 Update
UPDATE crime_status c, police_station p
SET c.reference = p.name
WHERE p.name LIKE concat('서울', c.police_station, '경찰서');

# JOIN (Foreign Key 를 기준으로 두 테이블을 연관시켜 검색)
SELECT c.police_station, p.address
FROM crime_status c, police_station p
WHERE c.reference = p.name
GROUP BY c.police_station;