SQL 명령어

SQL 기본 명령어

• 데이터 정의어(Data Definition Language)  : 테이블 생성, 변경, 삭제
• 데이터 조작어(Data Manipulation Language) : 데이터 삽입, 조회, 수정 삭제
• 데이터 제어어(Data Control Language) : 데이터 접근 권한 부여, 제거
• 트랜젝션 제어어(Transaction Control Language) : 데이터 조작어 명령어 실행, 최소, 임시저장

 

데이터 정의어(DDL)

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

• 테이블은 각 열마다 반드시 1가지 데이터 타입으로 정의되어야 한다.
• 테이블은 각 열마다 제약 조건을 정의할 수 있다. (아래 나열된 조건보다 더 많음)
  • PK(Primary Key) : 중복되어 나타날 수 없는 단일 값, NOT NULL
  • NOT NULL : NULL 허용 X

 

• 테이블 생성
  • CREATE TABLE 테이블명
  • Ex.
    

    CREATE TABLE TempTable ( //테이블 명 TempTable, 해당 이름의 테이블 생성
    id INT PRIMARY KEY,
    name VARCHAR(20),
    signd DATE NOT NULL,
    status varchar(1)
    );​

    
    

    열 이름	데이터 타입	제약 조건
    id	int	pk
    name	varchar
    signd	date	not null
    status	bit

• 열 추가
  • ALTER TABLE 테이블명 ADD 추가할 열 이름 데이터타입
  • Ex.

    ALTER TABLE TempTable ADD age varchar(2); //TempTable 테이블에 age 열 추가

     

    열 이름	데이터 타입	제약 조건
    id	int	pk
    name	varchar
    signd	date	not null
    status	varchar
    age	varchar

• 열 데이터 타입, 이름 변경
  • ALTER TABLE 테이블명 MODIFY 바꾸고 싶은 열 이름 데이터타입
    ALTER TABLE 테이블명 CHANGE 바꾸고 싶은 열 이름 바꿀 이름 데이터타입
  • Ex.
    

    ALTER TABLE TempTable MODIFY age VARCHAR(5); // 열 데이터 타입 변경
    ALTER TABLE TempTable CHANGE age gender VARCHAR(2); // 열 이름 변경

    
    

    열 이름	데이터 타입	제약 조건
    id	int	pk
    name	varchar
    signd	date	not null
    status	varchar
    gender	varchar

• 테이블명 변경
  • ALTER TABLE 바꾸고자 하는 테이블명 RENAME 원하는 테이블 명
  • Ex.
    

    ALTER TABLE TempTable RENAME ReTable;​

• 테이블 삭제
  • DROP TABLE 테이블명
  • Ex.
    

    DROP TABLE ReTable;​

 

데이터 조작어(DML)

테이블안의 데이터들을 조작하는 명령어이다.

• 데이터 삽입
  • INSERT INTO 테이블명 VALUES (삽일할 데이터들); //열 순서대로 입력
  • Ex.
    

    INSERT INTO ReTable VALUES (1, '자몽', '2021-12-31', 'A' ,'1');​

  • 조건 위반
    • PRIMARY KEY 제약 조건 위반 : 이미 id(pk)에 1번이 있음
      INSERT INTO ReTable VALUES (1, 'ae자몽', '2021-12-31', 'A' ,'1'); 
    • NOT NULL 제약 조건 위반 : signd는 NOT NULL임
      INSERT INTO ReTable VALUES (2, 'ae자몽', NULL, 'A', '1'); 
    • 데이터 타입 조건 위반 : 2는 int형으로 DATE 타입이 아님
      INSERT INTO ReTable VALUES (2, 'ae자몽', 2, ,'A', '1');
• 데이터 조회
  • SELECT * FROM 테이블명
  • Ex.
    

    SELECT * FROM ReTable; //ReTable의 모든 열 조회​
    SELECT id FROM ReTable; //ReTable에서 id 열 조회
    SELECT id AS Number FROM ReTable; //ReTable에서 id를 Number란 이름으로 조회(출력시 id가 아닌 Number로 출력됨)

• 데이터 수정
  • UPDATE 테이블명 SET 변경하고자 하는 데이터
  • Ex.
    

    UPDATE ReTable SET status = 'D'; //ReTable의 status 열의 모든 값을 'D'로 변경​
    UPDATE ReTable SET status = 'A' WHERE name='자몽'; //ReTable에서 name이 '자몽'인 사람의 status만 'A'로 변경

• 데이터 삭제
  • DELETE FROM 테이블명
  • Ex.
    

    DELETE FROM ReTable WHERE name = 'ae자몽'; //ReTable에서 name이 'ae자몽'인 데이터(row) 삭제
    DELTE FROM ReTable; //ReTable 테이블은 남겨두고 안의 데이터들 삭제(모든 데이터 삭제)​

 

데이터 제어어(DCL)

데이터 제어어는 권한을 제어하는 명령어로 데이터에 대한 접근 권한을 부여 및 제거할 수 있다.

데이터베이스 관리자(DBA)가 특정 사용자(User)에게 데이터 접근 권한을 부여 및 제거할 때 사용된다.

• 권한 부여/제거
  • GRANT 권한 부여할 명령어들 ON 데이터베이스.테이블명 TO 권한을 부여할 사용자
    REVOKE 권한 제거할 명령어들 ON 데이터베이스.테이블명 FROM 권한을 제거할 사용자 
  • Ex.
    

    CREATE USER 'TEMP'@LOCALHOST IDENTIFIED BY 'TEMP'; //사용자 아이디 및 비밀번호(IDENTIFIED BY 뒤) 생성
    //생성 후 SELECT * FROM USER; 해보면 TEMP가 추가된 것을 확인할 수 있음
    
    SET PASSWORD FOR 'TEMP'@LOCALHOST = '0000'; //사용자 비밀번호 변경
    
    /* 특정 권한 부여 */
    /* TEMP에게 DBPRACTICE 데이터베이스의 ReTable 테이블에 대한 SELECT, DELETE 권한 부여 */
    GRANT SELECT, DELETE ON DBPRACTICE.ReTable TO 'TEMP'@LOCALHOST;​
    
    /* 특정 권한 제거 */
    /* TEMP에게 주었던 권한 중 SELECT 권한을 제거 */
    REVOKE SELECT ON DBPRACTICE.ReTable FROM 'TEMP'@LOCALHOST;
    
    /* 모든 권한 부여 */
    GRANT ALL ON DBPRACTICE.ReTable TO 'TEMP'@LOCALHOST; 
    
    /* 모든 권한 제거 */
    REVOKE ALL ON DBPRACTICE.ReTable FROM 'TEMP'@LOCALHOST;

• 사용자 삭제
  • DROP USER 삭제할 사용자 이름
  • Ex.
    

    DROP USER 'TEMP'@LOCALHOST;
    
    SELECT * FROM USER; //삭제 후 제거된 것을 확인 가능​

 

트랜젝션 제어어(TCL)

TCL은 데이터 조작어(DML) 명령어를 실행, 취소, 임시저장할 때 사용하는 명령어이다.

 

트랜젝션(Tranaction)

트랜젝션은 분할할 수 없는 최소 단위이며 논리적인 작업 단위이다.

출처 - boostcourse, 기초 데이터 분석을 위한 핵심 SQL

 

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

  

  출처 - boostcourse, 기초 데이터 분석을 위한 핵심 SQL

  • Ex.
      spring boot에서 쿼리 작성을 할 때 commit을 하지 않아도 항상 최신 데이터로 처리되어있는 것을 확인했었는데, commit을 왜 할까? https://kimsg.tistory.com/258
  • /* BEGIN + ROLLBACK */ BEGIN; /* 트랜젝션 시작 */ INSERT INTO ReTable VALUES (3, 'JAAMONG', '2021-12-31', 'A', 1); //데이터 삽입 SELECT * FROM ReTable; // 삽입 확인 ROLLBACK; //취소 : 데이터 삽입 취소 SELECT * FROM ReTable; // 삽입 취소 확인 /* 트랜젝션 종료 */ /* BEGIN + COMMIT */ BEGIN; /* 트랜젝션 시작 */ INSERT INTO ReTable VALUES (3, 'JAAMONG', '2021-12-31', 'A', 1); //데이터 삽입 COMMIT; //실행 SELECT * FROM ReTable; // 삽입 확인 /* 트랜젝션 종료 */​
• 임시 저장(SAVEPOINT)
  • ROLLBACK 저장점을 지정하는 명령어
  • 원하는 지점으로 ROLLBACK 할 수 있다.
    

    

  • Ex.
    

    BEGIN; /* 트랜젝션 시작 */
    
    INSERT INTO ReTable VALUES ( , , ...); //대충 데이터 삽입했다고 하기
    SAVEPOINT SP1; //SAVEPOINT 지정, SP1 : savepoint임
    
    UPDATE ReTable SET name = '절대로뒤를돌아보지말어';
    SAVEPOINT SP2;
    
    DELETE FROM ReTable;
    SAVEPOINT SP3;
    
    SELECT * FROM ReTable; /* 반영됐는지 조회 */
    
    /* SAVEPOINT SP2로 ROLLBACK */
    ROLLBACK TO SP2;
    
    SELECT * FROM ReTable; 
    /* 조회하면 SP2로 돌아간 것을 확인할 수 있음, 조회 시 name '절대로뒤를돌아보지말어'가 존재할 것임 */​