10-1. SQL - DDL/DML/DCL

* SQL이란?

Structured Query Language의 약자로서 데이터베이스로부터 데이터를 조회하고 삭제하는 등의 작업을 수행할 때 사용하는 언어이다.

* 테이블과 레코드

• RDBMS에서 데이터를 저장하는 장소를 테이블이라고 한다.
• 테이블을 데이터들을 저장하며 데이터들의 정보를 갖고 있다.
• 테이블의 구조와 관련된 정보를 테이블 스키마(Schema)라고 부른다.
• 테이블의 구조는 각각의 정보를 저장하는 컬럼(column)과 컬럼 타입 그리고 컬럼의 길이로 구성된다.
• 각 컬럼에 저장된 데이터 값을 레코드(record)라고 부른다.
• 레코드, 컬럼 그리고 테이블을 사용해서 데이터를 저장하고 조회하는 등의 작업을 수행하는 것을 데이터베이스 프로그래밍이라고 부른다.

*SQL 문법

- SQL문의 특징은 대/소문자를 구분하지 않는다.

 

#DDL(Data Definition Language)

- CREATE : 테이블이나 인덱스, 뷰등 데이터베이스 객체를 생성

- DROP : 생성된 데이터베이스 객체를 삭제 

- ALTER : 이미 생성된 데이터베이스 객체를 수정

- TRANCATE : 테이블이나 클러스터의 데이터를 통째로 삭제.  즉, 데이터만 삭제되고 테이블은 살아있다. 

 

#DML(Data Manipulation Language)

- SELECT : 테이블이나 뷰에 있는 데이터 조회

- INSERT : 데이터를 신규로 생성

- UPDATE : 이미 생성된 데이터를 수정

- DELETE : 데이터를 삭제

- COMMIT: 트랜잭션 처리, 변경된 데이터를 최종 적용

- ROLLBACK : 트랜잭션 처리, 변경된 데이터를 적용하지 않고 이전으로 되돌림

 

#DCL(Data Control Language)

 - GRANT : 사용자에게 특정 권한을 부여

- REVOKE : 사용자에게 부여된 권한을 회수

 

1. 데이터베이스 생성

- create database [db이름] default chaeacter set utf8;

default chaeacter set utf8은 이 db에 한글이 정상적으로 저장되도록 하겠다.

 

2. 사용자 계정 생성과 권한 부여

- 관리자 계정의 이름은 'root'입니다. 

 

a. 사용자 계정 생성

- create user '[계정명]' identified by '[암호]';

 

b. 권한 부여 

grant [권한 목록] on [데이터베이스이름].[대상] to '[계정]';

 

3. 테이블 생성 문법

- create table [테이블 이름] (

         [칼럼명1]  [칼럼 데이터 타입(byte)],

         [칼럼명2]  [칼럼 데이터 타입(byte)],

          .....

);

 

- 테이블 생성시 필요한 sql데이터 타입

 

A. mysql문법

- 문자형 칼럼일 경우 CHAR, VARCHAR

- 정수형 칼럼일 경우 INT

- 날짜형 칼럼일 경우 DATETIME

 

B. ORACLE문법

- 문자형 칼럼일 경우 VARCHAR2

- 정수형 칼럼일 경우 NUMBER

- 날짜형 칼럼일 경우 DATE

 

- 주요 키 (Primary key)

a. 테이블에 있는 레코드를 조회할 때 특정 값을 이용해서 조회한다면 좀 더 빠르게 레코드를 검색할 수 있다.

b. 주요 키로 지정된 칼럼은 하나의 테이블에 저장된 모든 레크드가 중복된 값없이 모두 다른 값을 갖는 레코드를 말한다.

c. 주요 회원 아이디나 게시판 글 번호 등이 주요 키로 지정된다.

 

4. INSERT 문

- insert문은 데이터를 저장하는 용도의 쿼리문이다.

-insert into [테이블명] ([칼럼1], [칼럼2], ... ) values ([값], [값], ...);

- 테이블명 뒤에 칼럼명을 명시하지 않으면 전체 칼럼에 대해 갑슬 지정해야한다.

 

---

실습

1. 데이터베이스 생성

데이터베이스 생성명령을 실행하면 스키마에서 새로고침 클릭시 DB가 생성됨을 확인할 수 있다. 

앞으로 여러 DB파일을 생성할껀데 jsp_practice를 디폴드값으로 지정하고 싶다면 jsp_practice오른쪽 버튼클릭 >set as defalt   schema 클릭 (디폴트로 지정되면 색이 찐해진다.)

 

2 - a. db사용자 계정 생성.

2 - b. 사용자에게 권한 부여

3. 테이블 생성

4. 데이터 저장 INSERT

 

5. 데이터 수정 update

6. 데이터 삭제 DELETE

수정 삭제는 조건을 잘 지정해줘야한다.

7. 데이터 조회 SELECT 부터는 따로 정리하지 않고 쿼리만 나열하겠다. 

# 데이터베이스 생성 명령
# 워크벤치에서 sql문 실행 명령: 컨드롤 + Enter
CREATE DATABASE jsp_practice default character set utf8;

# 관리자 계정명 : root // 비밀번호 : mysql
# DB 사용자 계정 생성.
CREATE USER 'jsp' identified by 'jsp';

# 사용자에게 권한 부여
GRANT ALL PRIVILEGES ON jsp_practice.* to 'jsp';

# 테이블 생성 명령
# primary key - 칼럼의 주요키를 뜻하며 중복 데이터를 방지함.
# not null - 데이터 null값이 들어오는 것을 방지.
CREATE TABLE member(
	id varchar(20) primary key,
    pw varchar(20) not null,
    name varchar(30) not null,
    email varchar(80)
);

select * from member;

#데이터 저장 INSERT
INSERT INTO member (id, pw, name)
VALUES ('abc1234', '1234', '콩수빈');

#칼럼을 생략하겠다는건 모든 칼럼에 넣겠다는거고, 개수와 순서에 맞게 적어줘야한다.
INSERT INTO member
VALUES ('def456', '4433', '이순신', 'aaa@bbb.com');


INSERT INTO member VALUES ('fef1010', '4433', '기성용', 'aaa@never.com');
INSERT INTO member VALUES ('gfdg101', '1234', '이성계', 'dfdg@never.com');
INSERT INTO member VALUES ('sdfs201', '3424', '이민의', 'sdsd@never.com');
INSERT INTO member VALUES ('dhrr301', '3534', '이설용', 'gfd@never.com');
INSERT INTO member VALUES ('qwer401', '4567', '강하나', 'fff@never.com');

# 데이터 수정 UPDATE
UPDATE member
SET name = '신사임당',
email = 'shin012@daum.net'
WHERE id= 'abc1234'; 

# 데이터 삭제 DELETE
DELETE FROM member WHERE id = 'def456';

# 데이터 조회 SELECT
SELECT id FROM member; 
SELECT * FROM member;
SELECT email FROM member WHERE id = 'abc1234';

# ID가 qwer401인 회원의 비밀번호를 조회
SELECT pw FROM member WHERE id= 'qwer401';

SELECT * FROM member WHERE name LIKE '이%';

# 이메일이 naver인 사람의 이름정보를 조회하세요.
SELECT name FROM member WHERE email LIKE '%never%';

# 정렬 데이터 정렬
SELECT * FROM member ORDER BY name ASC;
SELECT * FROM member ORDER BY name DESC;
SELECT * FROM member ORDER BY name ASC, id DESC;

# 이름이 '이'로 시작하는 회원의 모든 컬럼을 ID오름차로 정렬하세요.
SELECT * FROM member WHERE name LIKE '이%' ORDER BY id ASC;

---

* 트랜잭션

지금까지 한 INSERT는 바로 DB에 반영이 되었다. (오토COMMIT이 되어있다.)

하지만 실무에선 이렇게 해선 안된다. 무조건 트랜잭션(COMMIT, ROLLBACK)을 거쳐야한다.

수동 COMMIT하는 법을 배워보자. 그리고 이러한 COMMIT, ROLLBACK은 DML에만 적용된다. 

 

수동 COMMIT 끄는법

auto commit 체크 풀고 mysql 껐다가 다시 실행시키면 된다.

트랜잭션 처리

위 순서대로 실행시 첫번째 INSERT만 남고 나머지 두 데이터는 ROLLBACK으로 사라진다.

TRUNCATE문

TRUNCATE는 DDL문이여서 ROLLBACK이 안된다.

테이블 컬럼 수정

테이블 칼럼이 adress 생성되고 email은 삭제됬음을 확인할 수 있다.