SQL기초
SQL - DML, DDL, DCL, TCL
토리쟁이
2024. 2. 6. 23:36
이번 포스팅에서는, SQL을 기능/목적에 따라 크게 나눈 DML, DDL, DCL, TCL에 대해 알아보고 각각의 명령어에 대해 공부해 보려고 한다. (MySQL 기준)
데이터 정의어(DDL, Data Definition Language)
- 테이블이나 관계의 구조를 생성하는데 사용하는 명령어
- CREATE
- 데이터베이스와 테이블을 생성하는 명령어
- 테이블 이름, 열 이름, 데이터 형식 등을 지정
- 기본키, 외래키 정의
-
CREATE DATABASE sesac DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
- CHARACTER SET: 문자들과 그 문자들을 코드화한 인코딩들의 조합
- COLLATION: 정해져 있는 인코딩을 기반으로 문자열을 비교/정렬하기 위해 정의된 규칙들의 집합
- utf8은 이모지 불가능 / utf8mb4는 이모지 사용 가능
- CREATE TABLE 테이블명 (속성1 데이터타입 제약조건, 속성2 데이터타입 제약조건, ...);
- 데이터 타입
- 숫자형: TINYINT, SMALLINT, INT, BIGINT, FLOAT
- 문자형: CHAR(N), VARCHAR(N), TEXT, MEDIUMTEXT
- 날짜형: DATA, TIME, DATETIME
- 제약조건
- NOT NULL: NULL 허용 X
- AUTO_INCREMENT: 1씩 자동 숫자 증가
- PRIMARY KEY: 기본키(중복X, NULL X)
- DEFAULT: 기본값 지정
- UNIQUE: 중복허용X, NULL 허용O, 한 테이블에 여러 개 가능
- 데이터 타입
- ALTER
- 생성된 테이블의 속성과 속성에 대한 제약 및 기본키, 외래키 등 변경
- ALTER TABLE 테이블명 ADD 속성이름 데이터타입; -- 속성 새로 추가
- ALTER TABLE 테이블명 DROP COLUMN 속성이름; -- 기존 속성 삭제
- ALTER TABLE 테이블명 MODIFY 속성이름 데이터타입; -- 기존 속성 수정
- DROP
- 생성된 테이블 삭제
- 테이블의 구조와 데이터가 모두 삭제됨
- DROP TABLE 테이블명;
-- DB 생성
CREATE DATABASE sesac DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; -- utf8은 이모지 불가능
CREATE DATABASE dobong CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- utf8mb4 는 utf8보다 더많은 문자 지원(이모지 저장 가능)
-- 1. 테이블 생성
/*
CREATE TABLE products(
속성1 값형식 제약조건,
속성2 값형식 제약조건
)
*/
-- 제약조건
-- NOT NULL : NULL 허용X
-- AUTO_INCREMENT: 자동 숫자 증가
-- PRIMARY KEY: 기본키(중복허용X, NULL값 허용X)
-- DEFAULT: 기본값
-- UNIQUE: 중복허용X, NULL값 허용o, 한 테이블에 여러 개 가능
CREATE TABLE products(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
model_model VARCHAR(15) NOT NULL,
series VARCHAR(30) NOT NULL
);
-- 테이블 목록 확인
SHOW TABLES;
-- 테이블 구조 확인
DESC products; -- 어떤 컬럼이 있는지 확인
-- 테이블 변경(수정) ALTER
-- 1. 컬럼 추가
ALTER TABLE products ADD new_column VARCHAR(20);
--2. 특정 컬럼 수정
ALTER TABLE products MODIFY new_column INT; -- VARCHAR -> INT 수정
--3. 특정 컬럼 삭제
ALTER TABLE products DROP new_column;
-- 테이블 삭제
/*
DROP는 테이블 자체 삭제
TRUNCATE는 테이블은 유지되고 안의 데이터들이 모두 삭제되는 것
*/
DROP TABLE products;
TRUNCATE TABLE products;
데이터 조작어(DML, Data Manipulation Language)
- 데이터베이스의 내부 데이터를 관리하기 위한 언어
- SELECT: 데이터 검색(조회)
- SELECT 속성이름 FROM 테이블명 WHERE 조건
- INSERT: 데이터 추가
- INSERT INTO 테이블명(필드1, 필드2, ...) VALUES(값1, 값2, ...);
- 단, 필드를 명시하지 않은 경우엔 모든 컬럼에 값을 순서대로 추가해야 함
- UPDATE: 데이터 수정
- UPDATE 테이블명 SET 필드1=값1 WHERE 조건
- DELETE: 데이터 삭제
- DELETE FROM 테이블명 WHERE 조건
- 데이터 조작을 위한 WHERE 조건
- 비교 연산자: =, >, <=, <, <=
- 부정 연산자: !=, ^=, <>, NOT 칼럼명 =~ → 4개 모두 ~와 같지 않다를 의미
- 범위: BETWEEN a AND b - a와 b사이 (a, b 포함)
- 집합: IN(list) - 리스트에 있는 값 중에서 어느 하나라도 일치하면 참
- 패턴: LIKE- 비교 문자열과 형태가 일치하면 사용
- %: 0개 이상의 어떤 문자
- _: 1개의 단일 문자
- NULL: IS NULL- NULL이면 참.
- 복합 조건: AND, OR, NOT
- 데이터 정렬 ORDER BY
- ASC: 오름차순(기본값)
- DESC: 내림차순
- 중복 제거 DISTINCT
- 출력 개수 제한 LIMIT
- 그룹화
- GROUP BY: 속성 이름끼리 그룹으로 묶는 역할
- HAVING: GROUP BY절의 결과를 나타내는 그룹을 제한 (조건)
- 집계 함수
- SUM(), AVG(), MAX(), MIN(), COUNT(), COUNT(DISTINCT) - 중복 제외 행 개수
-- 1. 데이터 추가(CREATE) >> INSERT INTO
-- INSERT INTO 테이블이름(컬럼1, 컬럼2, ...) VALUES(값1, 값2, ...);
INSERT INTO user (name, age, address) VALUES('김민정', 20, '서울특별시 마포구');
-- 2. 데이터 수정
-- UPDATE 테이블명 SET 데이터 어떻게 수정할 것인지 WHERE 조건 어떤 데이터를
UPDATE user SET name='김민지' WHERE id=1;
-- 3. 데이터 삭제
-- delete from 테이블명 where 삭제조건;
-- delete from 테이블명; 전체 삭제
-- TRUNCATE와의 차이점은, 디스크 공간 차지가 다름
DELETE from user where id=1; -- where 이후 조건에 따른 데이터 삭제
DELETE from user; -- 전체 데이터 삭제
-- 4. 데이터 조회(READ) SELECT ~ FROM
-- *: 전체
SELECT * FROM user;
SELECT name FROM user; -- name 컬럼만 조회
SELECT name, age FROM user; -- name, age 컬럼 조회
-- WHERE 조건 적용
SELECT * FROM user where age >=25;
SELECT * FROM user where id =3;
SELECT name FROM user where id =3;
SELECT id, age FROM user where name='이지은';
-- ORDER BY: 데이터 정렬
-- desc: 내림차순
-- asc: 오름차순(default)
SELECT * FROM user ORDER BY age;
SELECT * FROM user WHERE id>6 ORDER BY age;
-- LIKE: 문자열 패턴 조회(where와 함께 쓰임)
-- '서울'로 시작하는 주소 찾기
SELECT * FROM user WHERE address LIKE '서울%';
-- 이름의 마지막 글자가 '희'인 사람
SELECT * FROM user WHERE name LIKE '%희';
SELECT * FROM user WHERE name LIKE '__희'; -- 모든 사람의 글자가 3글자라는 가정하에-
-- 주소에 광역시가 들어가는 데이터
SELECT * FROM user WHERE address LIKE '%광역시%';
-- 이름에 희가 들어가는 사람 이름 컬럼만 조회, age 기준 내림차순 정렬
SELECT name FROM user WHERE name LIKE '%희%' ORDER BY age DESC;
-- LIMIT: 데이터의 개수 제한
SELECT * FROM user LIMIT 3;
SELECT * FROM user WHERE address LIKE '서울%' LIMIT 2;
SELECT * FROM user WHERE address LIKE '서울%' ORDER BY age LIMIT 2;
-- BETWEEN A AND B: A와 B의 사이값 조회(A, B 포함)
SELECT * FROM user WHERE age BETWEEN 25 AND 30;
-- IN(리스트): 리스트의 요소와 일치하면 참
SELECT * FROM user WHERE age IN(20, 21, 22, 23) -- BETWEEN 20 AND 23;
-- IS NULL / IS NOT NULL
INSERT INTO user (name, age) VALUES('서현승',28);
SELECT * FROM user WHERE address IS NULL;
SELECT name, address FROM user WHERE address IS NOT NULL;
-- 논리 연산자: AND, OR, NOT
-- 주소가 NULL이 아니면서 age가 25보다 큰 전체 속성 검색
SELECT * FROM user WHERE address IS NOT NULL AND age >25;
SELECT * FROM user WHERE address IS NOT NULL OR age >25;
-- 이씨 이면서 나이가 22살인 사람의 이름 검색
SELECT name FROM user WHERE name LIKE '이%' AND age=22;
-- DISTINCT: 중복 튜플 제거
SELECT age FROM user;
SELECT DISTINCT age FROM user;
데이터 제어어(DCL, Data Control Language)
- 데이터베이스의 데이터에 접근하는 것을 제한할 수 있는 권한 부여/박탈
- GRANT: 권한 부여
- GRANT ~ ON ~ TO~
- REVOKE: 권한 박탈
- REVOKE ~ ON ~ FROM
트랜잭션 제어어(TCL, Transaction Control Language)
- 데이터 조작어(DML)의 실행/취소/임시 저장시 사용하는 명령어
- COMMIT: 모든 작업 최종 실행 즉, DB에 최종 반영하는 연산
- ROLLBACK: 모든 작업 되돌리기 즉, 모든 변경 작업 취소 후 이전 상태로 되돌리는 연산
- SAVEPOINT: 임시저장/ rollback 저장점을 지정하여 특정 지점까지 ROLLBACK이 가능
- SAVEPOINT 포인터명;
- SAVEPOINT 여러 개 생성 가능
- 주의) SAVEPOINT 후 COMMIT 연산 수행시, COMMIT 수행 전에 만든 SAVEPOINT들이 모두 사라짐