[Day25] SQL 기초 & 기본 정리

SQL 기본 정리

SQL 데이터 타입

데이터 타입	설명
varchar	가변 길이 문자열 (최대 n 길이)
char	고정 길이 문자열
int	정수형 데이터
text	긴 문자열 데이터
date	날짜 (YYYY-MM-DD)
boolean	참/거짓 값

사용자 및 권한 설정

-- 사용자 생성
CREATE USER 'ureca'@'%' IDENTIFIED BY 'ureca';
-- 모든 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 'ureca'@'%' WITH GRANT OPTION;
-- 변경 사항 적용
FLUSH PRIVILEGES;

DB 생성 및 사용

-- 데이터베이스 생성
CREATE DATABASE ureca;

-- 데이터베이스 선택
USE ureca;

TABLE

테이블 생성

CREATE TABLE `ureca`.`member` (
  id VARCHAR(45) NOT NULL,
  pw VARCHAR(45) NOT NULL,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (id)
);

테이블 구조 변경

ALTER TABLE `ureca`.`member` 
ADD COLUMN `id` VARCHAR(45) NOT NULL,
ADD COLUMN `pw` VARCHAR(45) NOT NULL,
CHANGE COLUMN `name` `name` VARCHAR(50) NOT NULL,
CHANGE COLUMN `age` `age` INT NOT NULL,
ADD PRIMARY KEY (`id`);

테이블 삭제

DROP TABLE `ureca`.`member`; -- 완전 삭제 (rollback 불가)

데이터 (CRUD)

데이터 삽입 (INSERT)

INSERT INTO member(id, pw, name, age) VALUES
('a', 'a', '홍길동', 20),
('b', 'b', '이예은', 30),
('c', 'c', '전지현', 40);

데이터 조회 (SELECT)

SELECT * FROM member;

데이터 변경 (UPDATE)

update member
set name = '이예은'
where id = 'b';

데이터 삭제 (DELETE)

DELETE FROM member WHERE id = 'a';

데이터 삭제 방법

DELETE FROM member;      -- 데이터만 삭제 (ROLLBACK 가능)
TRUNCATE TABLE member;   -- 데이터만 삭제 (ROLLBACK 불가, AUTO_INCREMENT 초기화)

트랜잭션 관리

select * from member;
start transaction;
delete from member;
rollback; -- 변경 사항 취소
commit;   -- 변경 사항 저장

JDBC 사용법

• executeQuery(): SELECT문 실행, 결과로 ResultSet 반환
• executeUpdate(): INSERT, UPDATE, DELETE 실행, 변경된 행 개수 반환
• execute(): 모든 SQL 실행 가능 (SELECT이면 true, DML이면 false 반환)

JDBC를 이용한 데이터 조회 (select)

import java.sql.*;

public class Test {
	public static void main(String[] args) {
		// JDBC 6단계
		try {
			// 1.Driver 등록
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			// 2. Connection
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/ureca", "ureca", "ureca");
			
			// 3. Statement 생성
			Statement stmt = con.createStatement();
			
			// 4. SQL 전
			ResultSet rs = stmt.executeQuery("select * from member");
			// executeQuery : ResultSet이 return 
			
			// 5. 결과 얻기
			while(rs.next()) {
				String id = rs.getString("id");
				String pw = rs.getString("pw");
				String name = rs.getString("name");
				int age = rs.getInt("age");
				System.out.println(id + " " + pw + " " + name + " " + age);
			}
			
			// 6. 자원 닫기
			rs.close();
			stmt.close();
			con.close();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

JDBC를 이용한 데이터 삽입 (insert)

import java.sql.*;

public class InsertTest {
	public static void main(String[] args) {
		// JDBC 6단계
		try {
			// 1.Driver 등록
			Class.forName("com.mysql.cj.jdbc.Driver");
			
			// 2. Connection
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/ureca", "ureca", "ureca");
			// 3. Statement 생성
			// Statement stmt = con.createStatement();
			PreparedStatement stmt = con.prepareStatement("insert into member(id, pw, name, age) values(?,?,?,?)");
			
			// 4. SQL 전
			stmt.setString(1, args[0]);
			stmt.setString(2, args[1]);
			stmt.setString(3, args[2]);
			stmt.setInt(4, Integer.parseInt(args[3]));
			
			int i = stmt.executeUpdate();
			
			// 5. 결과 얻기
			System.out.println(i + "행이 변경되었습니다.");
			
			// 6. 자원 닫기
			stmt.close();
			con.close();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

---

PAIR STUDY

import java.awt.*;
import java.awt.event.*;
import java.sql.*;

public class MyWorkbench {
	static Connection con;
	
	public static void main(String[] args) throws Exception {
		Frame f = new Frame();
		TextArea ta = new TextArea();
		Panel p = new Panel();
		TextField tf = new TextField(25);
		
		f.add(ta);
		f.add(p, BorderLayout.SOUTH);
		p.add(tf);
		
		dbSet();
		
		tf.addActionListener(e -> {
			String sql = tf.getText().trim();
			
			try {
				PreparedStatement stmt = con.prepareStatement(sql);
				
				if(sql.startsWith("select")) {
					ResultSet rs = stmt.executeQuery();

				     while (rs.next()) {
				      String id = rs.getString("id");
				      String pw = rs.getString("pw");
				      String name = rs.getString("name");
				      int age = rs.getInt("age");
				      ta.append(id + ":" + pw + ":" + name + ":" + age + "\n");
				     }
				} else {
					int i = stmt.executeUpdate();
					ta.append(i + "행이 변경되었습니다." + "\n");
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		});

		f.addWindowListener(new WindowAdapter() {
			@Override
			public void windowClosing(WindowEvent e) {
				System.exit(0);
			}
		});
		f.setSize(500, 400);
		f.setVisible(true);
	}

	private static void dbSet() throws Exception {
		// 1.Driver 등록
		Class.forName("com.mysql.cj.jdbc.Driver");
		// 2. Connection
		con = DriverManager.getConnection("jdbc:mysql://localhost/ureca", "ureca", "ureca");
	}
}

결과

✅ 테이블의 값을 변경한 경우 (insert, delete 등)

✅ 전체 테이블 데이터 보기 (select)

🔹 강사님의 답안

import java.awt.*;
import java.awt.event.*;
import java.sql.*;


public class MyWorkbench {
	static Connection con;

	public static void main(String[] args) throws Exception {
		Frame f=new Frame();
		TextArea ta=new TextArea();
		Panel p=new Panel();
		TextField tf=new TextField(35);
		
		f.add(ta);
		f.add(p, BorderLayout.SOUTH);
		p.add(tf);
		
		dbSet();
		
		tf.addActionListener(e->{
			String sql=tf.getText().trim();
			try {
				PreparedStatement stmt=con.prepareStatement(sql);a
				
				if(stmt.execute()) {
					ResultSetMetaData rsmd=stmt.getMetaData();
					ResultSet rs=stmt.getResultSet();
					int colCnt=rsmd.getColumnCount();
					
					StringBuilder sb=new StringBuilder();
					while(rs.next()) {
						for (int i = 1; i <= colCnt; i++) {
							sb.append(rs.getString(i)).append(" ");
						}
						sb.append("\n");
					}
					ta.append(sb.toString()+"\n");
				}else {
					int i=stmt.getUpdateCount();
					ta.append(i+"행이 변경되었습니다\n\n");
				}
				tf.setText("");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
		});
		
		f.addWindowListener(new WindowAdapter() {
			@Override
			public void windowClosing(WindowEvent e) {
				System.exit(0);
			}
		});
		f.setSize(500, 400);
		f.setVisible(true);
	}

	private static void dbSet() throws Exception {
		//1. Driver 등록
		Class.forName("com.mysql.cj.jdbc.Driver");
		//2. Connection
		 con=DriverManager.getConnection("jdbc:mysql://localhost/ureca", "ureca", "ureca");	
	}
}

---

END