SQL 기본 명령어

학습목표

SQL 기본 명령어 개념에 대해 학습합니다.

핵심키워드

데이터 정의어(DDL)

데이터 조작어(DML)

데이터 제어어(DCL)

트랜젝션 제어어(TCL)

---

Intro

DBA

• 데이터베이스 관리자로, 기업 내에서 데이터 베이스 관리

• 테이블 생성, 데이터 접근 권환 및 데이터 베이스 관리

• 데이터 정의어: 테이블 생성, 변경, 삭제

• 데이터 제어어: 데이터 접근 권한 부여, 제거

Data Analyst

• 데이터 분석을 통해 새로운 인사이트 도출

• 데이터 베이스 접근, 데이터 분석, 데이터 접근 권환 요청

• 데이터 조작어: 데이터 삽입, 조회, 수정, 삭제

• 트랜잭션 제어어: DML명령어 실행, 취소, 임시저장

데이터 정의어(DDL)

• 테이블 생성, 변경, 삭제 사용하는 명령어

• 열 추가, 열 데이터 타입 및 테이블명 변경, 테이블 삭제

• 테이블은 각 열마다 반드시 1가지 데이터 타입으로 정의

• 테이블은 각 열마다 제약 조건을 정의

  • PK(Primary Key)

    • 중복되어 나타날 수 없는 단일 값
    • NOT NULL: NULL(값이 없는 상태) 을 허용하지 않음

• Code

/* Practice 이름으로 데이터베이스 생성*/
CREATE DATABASE Practice;

/* Practice 데이터베이스 사용*/
USE Practice;


/***************테이블 생성(Create)***************/
/* 회원테이블 생성 */
CREATE TABLE 회원테이블 (
회원번호 INT PRIMARY KEY,
이름 VARCHAR(20),
가입일자 DATE NOT NULL,
수신동의 BIT
);

/* 기본키(PRIMARY KEY): 중복되어 나타날 수 없는 단일 값 + NOT NULL */
/* NOT NULL: NULL 허용하지 않음 */

/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;
  
  
/***************테이블 열 추가*******************/  
/* 성별 열 추가 */  
ALTER TABLE 회원테이블 ADD 성별 VARCHAR(2);
 
/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;
 
 
/***************테이블 열 데이터 타입 변경***************/  
/* 성별 열 타입 변경 */  
ALTER TABLE 회원테이블 MODIFY 성별 VARCHAR(20);
 
 
/***************테이블 열 이름 변경**************/  
/* 성별 -> 성 열 이름 변경 */  
ALTER TABLE 회원테이블 CHANGE 성별 성 VARCHAR(2);
 
 
/***************테이블명 변경**************/  
/* 테이블명 변경 */  
ALTER TABLE 회원테이블 RENAME 회원정보;
 
/* 회원테이블 조회 --> 이름이 변경되었기 때문에 조회되지 않음*/
SELECT  *
  FROM  회원테이블;
  
/* 회원정보 조회 */
SELECT  *
  FROM  회원정보;   
  
  
/***************테이블 삭제**************/  
/* 테이블 삭제 */  
DROP TABLE 회원정보;
 
/* 회원정보 조회 --> 삭제되었기 때문에 조회되지 않음*/
SELECT  *
  FROM  회원정보;

데이터 조작어(DML)

• 데이터 삽입(제약조건 위반, 문자 밀 날짜형('')

• 조회: 모든 열(* ),특정 열 이름 변경(AS)

• 수정: 특정 조건(WHERE)

• 삭제: 특정 조건(WHERE)

/* Practice 데이터베이스 사용*/
USE Practice;

/***************테이블 생성(Create)***************/
/* 회원테이블 생성 */
CREATE TABLE 회원테이블 (
회원번호 INT PRIMARY KEY,
이름 VARCHAR(20),
가입일자 DATE NOT NULL,
수신동의 BIT
);


/***************데이터 삽입*******************/  
INSERT INTO 회원테이블 VALUES (1001, '홍길동', '2020-01-02', 1);
INSERT INTO 회원테이블 VALUES (1002, '이순신', '2020-01-03', 0);
INSERT INTO 회원테이블 VALUES (1003, '장영실', '2020-01-04', 1);
INSERT INTO 회원테이블 VALUES (1004, '유관순', '2020-01-05', 0);

/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;


/***************조건 위반*******************/
/* PRIMARY KEY 제약 조건 위반 */
INSERT INTO 회원테이블 VALUES (1004, '장보고', '2020-01-06', 0);

/* NOT NULL 제약 조건 위반 */
INSERT INTO 회원테이블 VALUES (1005, '장보고', NULL, 0);

/* 데이터 타입 조건 위반 */
INSERT INTO 회원테이블 VALUES (1005, '장보고', 1, 0);

 
/***************데이터 조회***************/  
/* 모든 열 조회 */  
SELECT  *  
  FROM  회원테이블;
 
/* 특정 열 조회 */  
SELECT  회원번호,
		이름
  FROM  회원테이블;

/* 특정 열 이름 변경하여 조회 */  
SELECT  회원번호,
		이름 AS 성명
  FROM  회원테이블;


/***************데이터 수정*******************/
/* 모든 데이터 수정 */
UPDATE 회원테이블
   SET 수신동의 = 0;
  
  
/* 회원테이블 조회 */  
SELECT  *
  FROM  회원테이블;

/* 특정 조건 데이터 수정 */ 
UPDATE 회원테이블
   SET 수신동의 = 1
 WHERE 이름 = '홍길동';
 
/* 회원테이블 조회 */  
SELECT  *
  FROM  회원테이블;
  
  
/***************데이터 삭제*******************/  
/* 특정 데이터 삭제 */ 
DELETE 
  FROM 회원테이블
 WHERE 이름 = '홍길동';
 
/* 회원테이블 조회 */  
SELECT  *
  FROM  회원테이블;
  
/* 모든 데이터 삭제 */ 
DELETE 
  FROM 회원테이블;
 
/* 회원테이블 조회 */  
SELECT  *
  FROM  회원테이블;
  

데이터 제어어(DCL)

• 데이터 접근 권한 부여, 제거

• 사용자 확인: USE MYSQL

• 사용자 추가 및 삭제: CREATE, DROP

• 권한 부여 및 삭제: CRANT, REVOKE

/***************사용자 확인***************/
/* MYSQL 데이터베이스 사용 , 사용자 추가*/
USE MYSQL;

/* 사용자 확인 */
SELECT  *
  FROM  USER;
  
/***************사용자 추가***************/

/* 사용자 아이디 및 비밀번호 생성 */
CREATE USER 'TEST'@LOCALHOST IDENTIFIED BY 'TEST';

/* 사용자 확인 */
SELECT  *
  FROM  USER;
  
/* 사용자 비밀번호 변경 */
SET PASSWORD FOR 'TEST'@LOCALHOST = '1234';

  
/***************권한 부여 및 제거***************/ 
/** 권한: CREATE, ALTER, DROP, INSERT, DELETE, UPDATE, SELECT 등  **/

/* 특정 권한 부여 */
GRANT SELECT, DELETE ON PRACTICE.회원테이블 TO 'TEST'@LOCALHOST;

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

/* 모든 권한 부여 */
GRANT ALL ON Practice.회원테이블 TO 'TEST'@LOCALHOST;

/* 모든 권한 제거 */
REVOKE ALL ON Practice.회원테이블 FROM 'TEST'@LOCALHOST;


/***************사용자 삭제***************/ 

/* 사용자 삭제 */
DROP USER 'TEST'@LOCALHOST;

/* 사용자 확인 */
SELECT  *
  FROM  USER;

트랜잭션 제어어(TCL)

• 데이터 조작어 명령어 실행, 취소, 임시저장

• 트랜잭션은 분할할 수 없는 최소 단위이고 논리적인 작업 단위

  • 실행(COMMIT): 모든 작업을 최종 실행
  • 취소(ROLLBACK): 모든 작업을 되돌림

• 임시 저장(SAVEPOINT): 임시 저장은 ROLLBACK 저장점을 지정하는 명령어

  • 원하는 지점으로 갈 수 있다.(게임과 유사)

/* Practice 데이터베이스 사용*/
USE Practice;

/***************테이블 생성(Create)***************/
/* (회원테이블 존재할 시, 회원테이블 삭제) */
DROP TABLE 회원테이블;

/* 회원테이블 생성 */
CREATE TABLE 회원테이블 (
회원번호 INT PRIMARY KEY,
이름 VARCHAR(20),
가입일자 DATE NOT NULL,
수신동의 BIT
);

/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;


/***************BEGIN + 취소(ROLLBACK)*******************/  
/* 트랜젝션 시작 */
BEGIN;

/* 데이터 삽입 */
INSERT INTO 회원테이블 VALUES (1001, '홍길동', '2020-01-02', 1);

/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;

/* 취소 */
ROLLBACK;


/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;


/***************BEGIN + 실행(COMMIT)*******************/  
/* 트랜젝션 시작 */
BEGIN;

/* 데이터 삽입 */
INSERT INTO 회원테이블 VALUES (1005, '장보고', '2020-01-06', 1);

/* 실행 */
COMMIT;

/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;


/***************임시 저장(SAVEPOINT)*******************/ 
/* (회원테이블에 데이터 존재할 시, 데이터 모두 삭제) */
DELETE FROM 회원테이블;

/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;

/* 트랜젝션 시작 */
BEGIN;

/* 데이터 삽입 */
INSERT INTO 회원테이블 VALUES (1005, '장보고', '2020-01-06', 1);

/* SAVEPOINT 지정 */
SAVEPOINT S1;

/* 1005 회원 이름 수정 */
UPDATE  회원테이블
   SET  이름 = '이순신';
 
/* SAVEPOINT 지정 */
SAVEPOINT S2;

/* 1005 회원 데이터 삭제 */
DELETE 
  FROM  회원테이블;
 
/* SAVEPOINT 지정 */
SAVEPOINT S3;

/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;

/* SAVEPOINT S2 저장점으로 ROLLBACK */
ROLLBACK TO S2;
 
/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;

/* 실행 */
COMMIT;

/* 회원테이블 조회 */
SELECT  *  FROM  회원테이블;