[Database] 이론 및 기본 문법(DDL, DML, DCL)

※ 이 글에서 다룬 기반기술, 프론트엔드, 백엔드, 데브옵스 등 풀스택 개발 지식은 모두 한화시스템 Beyond SW Camp 12기에서 배운 내용을 복습한 것입니다.

 

데이터베이스(Database) : 데이터를 표 형태로 저장한 파일

---

DDL (Data Definition Language)

데이터 정의 언어. 데이터베이스 구조를 정의하거나 수정하는 데 사용

• 주요 명령어: CREATE, ALTER, DROP, TRUNCATE, RENAME

CREATE

# 기본적인 문법
CREATE TABLE 테이블 (
	[속성명] [데이터타입] (크기), [PRIMARY KEY]
	[속성명] [데이터타입] (크기) [DEFAULT 기본값],
	[속성명] [데이터타입] (크기),
    FOREIGN KEY ([외래키]) REFERENCES [참조테이블]([속성])	
);


# 예시
CREATE TABLE board(
	idx			INT PRIMARY KEY AUTO_INCREMENT,
	title		VARCHAR(100),
	contents	VARCHAR(500),
	writer		INT,
	FOREIGN KEY (writer) REFERENCES user(idx)
);

 

ALTER

# 기본 문법
ALTER TABLE [테이블명] ADD [속성] [타입명];	# 속성 추가
ALTER TABLE [테이블명] RENAME COLUMN [속성명] TO [바꿀 속성명];	# 속성 이름 변경
ALTER TABLE [테이블명] MODIFY [속성명] [타입명];	# 속성 타입 변경
ALTER TABLE [테이블명] DROP [속성명];	# 속성 삭제


# 예시 코드
ALTER TABLE user ADD birth DATE;	# 속성 추가
ALTER TABLE user RENAME COLUMN birth TO yyyymmdd;	# 속성 이름 변경
ALTER TABLE user MODIFY yyyymmdd INT;	# 속성 타입 변경
ALTER TABLE user DROP yyyymmdd;	# 속성 삭제

 

DROP

# 기본 문법
DROP TABLE [테이블명];

# 예시
DROP TABLE user;

---

DML (Data Manipulation Language)

데이터 조작 언어. 데이터베이스의 데이터를 조회하거나 수정하는 데 사용

• 주요 명령어: SELECT, INSERT, UPDATE, DELETE

SELECT (조회)

# 기본 문법 # 모든 속성 보이게 하려면 *
SELECT [속성명] FROM [테이블명];

# 예시
SELECT * FROM user;
SELECT idx, name FROM user;

INSERT (삽입)

# 기본 문법
INSERT INTO [테이블명]([속성명1], [속성명2]) VALUES ([데이터1],[데이터2]);

# 예시
INSERT INTO user (name, id, password) VALUES ('test01','test01','qwer1234');

# 모든 속성을 다 넣으려면 속성명 생략 가능
INSERT INTO user VALUES (3,'test03','test03','qwer1234','abcd.jpg');

# 한 번에 2개도 가능
INSERT INTO user (name, id, password) VALUES ('test05','test05','qwer1234'), ('test06','test06','qwer1234');

 

UPDATE (수정)

# 기본 문법
UPDATE [테이블명] SET [속성명] = [데이터] WHERE 조건;

# 예시
UPDATE user SET profile_img='abcd.jpg' WHERE idx=1;

DELETE (삭제)

# 기본 문법
DELETE FROM [테이블명] WHERE 조건;

# 예시
DELETE FROM user WHERE idx=3;

---

DCL (Data Control Language)

데이터 제어 언어. 데이터베이스의 접근 권한을 제어하는 데 사용

• 주요 명령어: GRANT, REVOKE

---

 

# 기본 SELECT 문법
# SELECT 속성, 속성, 속성 FROM 테이블이름;

SELECT * FROM customers;
SELECT * FROM customers;
SELECT customerNumber, customerName, country FROM customers;

# 특정 조건에 맞는 데이터를 조회
# SELECT 속성, 속성, 속성 FROM 테이블이름 WHERE 조건;
# 글자를 비교할 때는 글자를 무조건 ''로 묶어준다.
# 숫자는 그냥 작성
SELECT customerNumber, country FROM customers 
	WHERE country ='USA';

# = 같은지 비교, > 큰지 비교, <작은지 비교, <=, >=, NOT 다른지 비교
SELECT customerNumber, country FROM customers 
	WHERE NOT country ='USA';

SELECT customerNumber, creditLimit FROM customers 
	WHERE creditLimit >= 10000;
    
# 조건 여러 개 같이 사용할 때
#AND		OR			XOR		참 : 1, 거짓 : 0
#0 0 0     0 0 0		0 0 0
#0 1 0     0 1 1		0 1 1
#1 0 0     1 0 1 		1 0 1
#1 1 1     1 1 1 		1 1 0
# country가 USA이고 creditLimit이 100000보다 큰 customers의
# customerNumber, country, creditLimit를 조회
SELECT customerNumber, creditLimit FROM customers 
	WHERE counry='USA' AND creditLimit >= 100000;
    
# country가 USA이거나 creditLimit이 100000보다 큰 customers의
# customerNumber, country, creditLimit를 조회
SELECT customerNumber, creditLimit FROM customers 
	WHERE counry='USA' OR creditLimit >= 100000;
    
# 글자 중에 일부를 포함한 경우 조회
SELECT customerNumber FROM customers
	WHERE country LIKE 'USA';

# 글자 중에 일부를 포함한 경우 조회
SELECT customerNumber FROM customers
	WHERE country LIKE '%a%';
    
# 정렬, ASC 오름차순, DESC 내림차순
SELECT customerNumber, country FROM customers
	ORDER BY country ASC;
    
# 특정 수 만큼 조회
# LIMIT 데이터의 수 OFFSET 몇 번째부터
SELECT customerNumber, country FROM customers
	LIMIT 3 OFFSET 0;
# 위에꺼랑 같은 뜻. LIMIT 몇 번쨰부터, 데이터 수
SELECT customerNumber, country FROM customers
	LIMIT 0, 3;
    
# JOIN
SELECT * FROM customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber;

# 회원 테이블과 주문 테이블에서 주문 상태가 Disputed인
# 회원의 회원 번호와 이름과 주문 번호, 주문 상태를 조회
SELECT customers.customerNumber, customerName, orderNumber,status 
FROM customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
WHERE status = 'Disputed';

SELECT customers.customerNumber, customerName, orderNumber,status 
FROM customers
LEFT JOIN orders ON customers.customerNumber = orders.customerNumber
WHERE status = 'Disputed';

#-----------------------------SELECT 실습-----------------------------------
# 런던에서 주문한 고객들의 주문 번호와 고객 이름 조회
SELECT orderNumber, customerName 
FROM orders
INNER JOIN customers ON customers.customerNumber = orders.customerNumber
WHERE city = 'London';

# 주문한 제품의 수량 35개 이상인 주문의 주문 번호와 고객 이름
SELECT orders.orderNumber, customerName FROM orders 
INNER JOIN customers ON orders.customerNumber = customers.customerNumber
INNER JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber
WHERE quantityOrdered >= 35;

# territory가 APAC 근무하는 직원이 담당하는 고객 이름과 고객 번호 조회
SELECT customerName, customerNumber FROM customers
INNER JOIN employees ON employees.employeeNumber = customers.salesRepEmployeeNumber
INNER JOIN offices ON employees.officeCode = offices.officeCode
WHERE territory = 'APAC';

# 주문 상태가 Shipped인 주문에 포함된 제품의 이름과 주문한 고객의 이름 조회
SELECT productName, customerName
from customers INNER JOIN orders ON customers.customerNumber = orders.customerNumber
INNER JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber
INNER JOIN products ON orderdetails.productCode = products.productCode
WHERE status = 'shipped';


# 내장 함수 
# 집계 함수
SELECT * FROM orderdetails;	

# 모든 주문 수
SELECT COUNT(orderNumber) FROM orders;

# 모든 고객의 수
SELECT COUNT(customerNumber) FROM customers;

# 도시 별 고객의 수
SELECT city, COUNT(customerNumber) FROM customers GROUP BY city;

# 중복 제거 DISTINCT

#--------------------------내장 함수 실습----------------------------
# 도시별로 주문한 고객들의 수
SELECT city, COUNT(DISTINCT customers.customerNumber) FROM customers 
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
GROUP BY city;

# 주문한 제품별 제품의 이름과 고객의 수
SELECT productName, COUNT(DISTINCT customers.customerNumber) FROM orders
INNER JOIN customers ON orders.customerNumber = customers.customerNumber
INNER JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber
INNER JOIN products ON orderdetails.productCode = products.productCode
GROUP BY products.productCode;

# territory별 근무하는 직원이 담당하는 고객의 수
SELECT COUNT(customerNumber) FROM employees
INNER JOIN customers ON employees.employeeNumber = customers.salesRepEmployeeNumber
INNER JOIN offices ON employees.officeCode = offices.officeCode
GROUP BY territory;

# territory별(territory가 30 이상인 것 중) 근무하는 직원이 담당하는 고객의 수
# GROUP BY의 조건절은 HAVING
# 집계함수로 뽑은 경과에 조건을 비교하고 싶을 때는 HAVING 사용
SELECT COUNT(customerNumber) FROM employees
INNER JOIN customers ON employees.employeeNumber = customers.salesRepEmployeeNumber
INNER JOIN offices ON employees.officeCode = offices.officeCode
GROUP BY territory
HAVING COUNT(customerNumber)>30;