SQL, JDBC

터미널 인터페이스

라이브러리 데이터베이스

웹 스시템 데이터베이스

os : 운영체제

(Operating System)

 

 

SQL이란?

사람과 사람이 대화하기 위해서는 언어가

필요합니다.

DBMS에게 명령을 내릴 때도 특별한 명령이

필요합니다.

이 명령을 SQL이라고 합니다.

 

핵심개념

- create database

- grant

- flush privileges

- select

 

SQL (Structured Query Language)

- SQL은 데이터를 보다 쉽게 검색하고 추가, 삭제, 수정 같은 조작을 할 수 있도록 고안된 컴퓨터 언어이다.

- 관계형 데이터베이스에서 데이터를 조작하고 쿼리하는 표준 수단이다.

참고: http://www.ciokorea.com/print/35385   -->   역사를 접할 수 있는 사이트(어떤 배경으로 탄생하게 되었는지 알 수 있음)

 

★ DML (Data Mnipulation Language) : 데이터를 조작하기 위해 사용한다.

- INSERT(입력), UPDATE(수정), DELETE(삭제), SELECT(조회) 등이 여기에 해당한다.

★ DDL (Data Definition Language) : 데이터베이스의 스키마를 정의하거나 조작하기 위해 사용한다.

- CREATE, DROP, ALTER 등이 여기에 해당한다.

★ DCL ( Data Control Language) : 데이터를 제어하는 언어이다. 권한을 관리하고, 데이터의 보안, 무결성등을 정의한다.

- GRANT, REVOKE 등이 여기에 해당한다.

 

Database 생성하기

1. 콘솔에서 다음과 같이 명령을 실행합니다. MySQL 관리자 계정인 root로 데이터베이스 관리 시스템에 접속하겠다는 것이다.

이런 명령을 먼저 수행해야 한다.

     my sql - uroot -p     -->  root 계정으로 접속을 하겠다는 의미. - u 옵션을 주고 있다. user 가 root 이다. -p는 mysql 설치할 때, 부여한 pw를 입력하면 된다.

2. window 사용자는 설치시에 입력했던 암호를 입력한다.   맥사용자는 암호가 없으니 그냥 엔터를 입력하면 된다.

 

[ cmd 사용 ]

*  cmd ->  mysql -uroot -p  -> pw입력 -> 관리자 계정으로  MySQL에 접속했다면, 다음과 같은 명령으로 데이터베이스를 생성한다.

mysql> create database DB이름;  (직관적인 sql _ DB이름을 지정하겠다라는 명령어)

* 우리는 다음과 같은 명령을 실행하여 DB이름을 "connectdb"로 생성하도록 하겠다.

mysql> create database connectdb;

* Database를 생성했다면, 해당 데이터베이스를 사용하는 계정을 생성해야 한다.

또한, 해당 계정이 데이터베이스를 이용할 수 있는 권한을 줘야 한다. 아래와 같은 명령을 이용해서 사용자 생성과 권한을 줄 수 있다.

db이름 뒤의 * 는 모든 권한을 의미한다. @%는 어떤 클라이언트에서든 접근가능하다는 의미이고, @'localhost'는 해당 컴퓨터에서만 접근가능하다는 의미이다.

* flush privileges는 DBMS에게 적용을 하라는 의미입니다. 해당 명령을 반드시 실행해줘야 한다.

- grant all privileges on db이름.*to 계정이름@'%'identified by '암호';

- grant all privileges on db이름.*to 계정이름@'localhost'identified by '암호';

 

* 여러 문장을 한 줄에 연속으로 붙여서 구현이 가능하다. 순서대로 실행된다.

* 하나의 SQL은 여러 줄로 입력 가능하다.

--> 이렇게하면 조금 더 가독성이 좋아보일 수 있다.

 

 

 

 

JDBC란?

자바에서 데이터베이스를 사용하기 위한 절차에 대한 규약입니다.

 

★ eclipse에서 JDBC 등록하기.

java에서 데이터베이스를 사용하기 위해서는 드라이버를 설치해야 합니다.

먼저 MySQL 홈페이지에서 드라이버를 설치합니다. (link -> https://dev.mysql.com/downloads/connector/j/#downloads)

 

1. jar file 준비하기.

설치가 완료되면, mysql-connector-java-버전-bin.jar 파일이 생성되었을 것입니다.

여러 클래스들을 모아 놓은 파일을 jar 파일이라고 하는데, 이 파일이 있어야 MySQL을 사용할 수 있습니다.

 

2. eclipse에서 jar파일 연동

* eclipse에서 새로운 프로젝트를 생성

* 프로젝트 폴더를 우클릭 -> Properties -> Java Build Path 항목 클릭

* Libraries -> Add Library...를 클릭 -> User Library클릭 -> User Libraries 클릭

* New 클릭하여 임의 이름을 작성합니다.

* 생성된 Library를 클릭하고 Add External JARs 클릭

* 처음에 설치한 jar 파일 mysql-connector-java-버전-bin.jar 파일을 찾아서 클릭

 

==> 그러면 프로젝트 폴더 아래에 라이브러리가 추가될텐데, 여기까지 진행했으면 JDBC를 사용할 준비가 된 것입니다.

 

3. JDBC 기본적인 사용

JDBC에서 인터페이스를 제공하기 때문에, 어떤 DB를 사용하든 개발자가 JDBC를 사용하는 방법은 변하지 않습니다.

이것이 인터페이스의 가장 큰 장점입니다.

 

4. JDBC를 사용하는 방법은 다음과 같습니다.

* import java.sql.*;

* 드라이버를 load

* mysql 연결을 위한 Connection 객체 생성

* Statement 객체를 생성하여 질의 수행

* 질의 결과가 있다면, ResultSet 객체를 생성하여 결과 저장

* 추가 로직 실행 후, JDBC 연결 과정에서 필요했던 객체들을 close

------------------------------------------

Connection 객체를 자동완성으로 import 할 때는 com.mysql.connection이 아닌

java 표준인 java.sql.Connection 클래스를 import해야 한다.

 

* import java.sql.Connection;

* import java.sql.DriverManger;

* import java.sql.SQLExceprion;

 

 

1. 먼저 mysql-connector-java-버전-bin.jar 파일이 라이브러리에 추가되어 있어야 합니다.

2. 다음으로 Class.forName() 메서드를 호출하여, mysql에서 제공하는 Driver 클래스를 JVM method area에 로딩시킵니다.

3. 그러면 여러 Driver 객체를 다루는 DriverManger의 static 메서드를 사용할 수 있게 됩니다.

4. DriverManager 캘래스의 static 메서드인 getConnection() 메서드를 호출해서, mysql에 연결하기 위한 커넥션 정보(url, user, password)를 입력합니다.

5. getConnection() 메서드 수행 결과로 Connection 객체를 반환하는데, 이 객체를 통해 쿼리를 날리는 statement를 작성할 수 있습니다.

6. SELECT 쿼리에서는 createStatement(), INSERT에서는 prepareStatement()를 호출합니다.

 

★ 이 프로그램이 정상적으로 실행이 되려면, mysql에 dev라는 database가 있어야 하며, 비밀번호가 dev인 dev 유저가 있어야 합니다.

 

1. SELECT 쿼리를 수행하기 전에, 머넞 Connection 객체를 얻어야 합니다.

2. 이어서 Connection 객체의 createStatement() 메서드를 호출하여, 쿼리를 실행할 수 있는 Statement객체 (stmt)를 얻습니다.

3. 해당 쿼리에는 동적으로 할당되는 값이 없으므로 createStatement() 메서드를 호출합니다.

4. 다음으로 쿼리를 작성하면 되는데, 쿼리를 작성할 때 주의 할 것들이 있습니다.

5. 작성한 쿼리(sql)를 stmt.executQuery() 메서드의 인자로 전달하여 호출하면 쿼리가 실행됩니다.

6. 그 결과로 ResultSet 객체(rs)가 반환 되며, 이 객체에는 SELECT 쿼리 결과의 레코드들이 담겨있습니다.

 

* 결과를 조회하기 위해서 반복분 while을 사용하기도 하지만, 일반적으로는 VO 객체를 사용합니다.

VO 객체란 테이블의 칼럼들을 멤버 변수로 갖는 객체를 의미합니다.

 

* pstmt = conn.prepareStatement(sql);   --> 데이터를 추가할 것임을 알리는 것.

 

 

1. Insert 쿼리를 수행할 때도 먼저 Connection 객체를 얻어야 합니다.

2. INSERT는 일반적으로 동적으로 값이 할당되므로 createStatement()를 호출하지 않고, 쿼리를 준비하는

statement라는 의미로 prepareStatement() 메서드를 호출합니다.

3. 그러면 PreparedStatement 객체(pstmt)를 반환하는데, pstmt.setString() 메서드를 통해 동적으로 값을 할당할 수 있습니다.

4. 값이 할당되면 pstmt.executeUpdate() 메서드를 실행하여 INSERT 쿼리를 실행할 수 있습니다.

5. 반환 값은 영향을 미친 row의 개수입니다.

 

★ 쿼리를 수행할 때 동적으로 할당해야 하는 값이 있으면 PreparedStatement 객체를 사용하고,

동적으로 할당할 필요가 없으면 statement 객체를 사용합니다.

 

★ 쿼리의 결과가 있으면 executeQuery() 메서드를 호출하여 ResultSet 객체에 담고, 쿼리의 결과가 없으면 executeUpdate() 메서드를 호출하여 int형 변수에 결과 값을 할당합니다.

 

JSP/Servlet, Spring으로 넘어가면 중복되는 코드를 없애는 리팩토링 작업과 데이터들을 효과적으로 다룰 수 있도록 DVO, VO 클래스들을 생성하여 DB를 다루게 됩니다.

 

 

★ database : 데이터가 저장되는 테이블들을 모아놓은 하나의 논리적 공간.

 

1. database 생성

# DREATE database 데이터베이스명;

 

2. database 사용 선언

어떤 database를 사용할 것인지 선업합니다.

# USE 데이터베이스명;

 

3. database 목록조회

# SHOW databases;

 

4. database 삭제

# DROP database 데이터베이스명;

 

5. 테이블 추가 : 테이블을 생성하는 명령어의 기본구조는 CREATE TABLE 테이블명 ( 컬럼, 컬럼, 컬럼); 입니다.

--> 컬럼을 명시하는 괄호() 안에는 필드명/ 자료형/ 제약조건의 형식을 갖습니다.

필드명은 자신이 직접 작성해주면 되며, 일반적으로 snake case를 사용합니다.

 

[ 자료형의 종류 ]

INT : 정수

DOUBLE : 실수

VARCHAR(정수) : 문자열

DATE : YYYY-MM-DD format

TIME : HH:MM:SS format.

ENUM : enum으로 명시된 값만 입력될 수 있음

 

[ 제약 조건의 종류 ]

NOT NULL : 반드시 입력해야 하는 컬럼

AUTO_INCREMENT : 자동으로 숫자가 증가하도록 만듦 (주로 PK 컬럼에서 사용합니다.)

PRIMARY KEY : 기본 키로 지정

DEFAULT : 기본 값을 설정

UNIQUE : 중복되지 않도록 합니다.

 

[ 테이블 조회 ]

1) DESC  : desc는 스키마(테이블의 컬럼명과 컬럽들의 제약조건 정보)를 볼 수 있는 명령어입니다.

# DESC 테이블명;

 

2)  SHOW : database의 모든 테이블 조회

# SHOW tables;

 

[ 테이블 삭제 ]

1) DROP  : drop은 테이블을 통째로 삭제하는 명령어입니다.

# DROP table 테이블명;

 

2) TRUNCATE : 이와 달리 truncate는 테이블 정의는 남겨놓고 테이블에 존재하는 모든 데이터를 삭제하는 명령어입니다.

#TRUNCATE table 테이블명;

 

[ 테이블 정의 수정 ]

테이블을 정의한 뒤 Alter 명령어를 통해 스키마를 수정할 수 있습니다.

만약 테이블이 비어있다면 DROP을 한 후 다시 생성하면 되지만, 이미 데이터가 있을 경우에는 곤란하므로 이럴 경우 유용합니다.

 

1) 컬럼 추가

테이블에 새로운 컬럼을 추가하는 명령어의 기본구조는 ALTER TABLE 테이블명 ADD 컬럼명 데이터타입; 입니다.

아래는 products 테이블에 문자열 타입의 newColumn 컬럼을 추가하는 예제입니다.

기존의 데이터들은 newColumn 컬럼이 없었는데 갑자기 생긴 것이므로 newCoulumn의 값으로 NULL이 할당됩니다.

# ALTER TABLE products ADD newColumn varchar(20); 

 

2) 컬럼 타입 수정

테이블에 새로운 컬럼을 추가하는 명령어의 기본구조는 ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입;   입니다.

아래는 varchar(20)이였던 newColumn 컬럼의 자료형을 Integer로 수정하는 예제입니다.

자료형이 변경되었으므로 기존의 데이터들도 자료형이 바뀌는데, 바뀔 수 없다면 에러가 발생합니다.

# ALTER TABLE products MODIFY newColumn Integer;

 

~~~~~~~~~ ~~ ~ ~