:: GO치의 에브리데이 일기장::

mySQL 데이터타입

char(크기) 문자열저장
vachar(크기) 문자열저장
text 문자열저장

int(크기) 정수저장
//0.24495677233429397

float 실수저장(0.244957)

bouble 실수저장(0.244956772334294)

boolean 논리값 저장(1,0)

package com.ohj;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MainClass5 {
	
	public static void main(String[] args) {
		String driver_name="com.mysql.jdbc.Driver";
		String db_url="jdbc:mysql://localhost:3306/testdb";
		
		//ERR connect:Unknown database 'testdb6' 데이터베이스명을 잘 못 되었을 경우
	
		String dc_id="root";
		String db_pw="920424";
	
		//ERR connect:Access denied for user 'root'@'localhost' (using password: YES)비밀번호나 아이디를 잘 못 입력했을 경우
	
		Connection conn = null;//java.sql 접속 결과값을 저장받는 곳(추후 자원해제 시 필요)​
		try {
			Class.forName(driver_name); //1번째 해당 드라이버 로딩해주기
			System.out.println(" mySQL Driver Load 성공");
			
			conn=DriverManager.getConnection(db_url,dc_id,db_pw);//2번째 드라이버메니저로 접속주소, 아이디, 비밀번호를 적어 로그인
			System.out.println("DB 접속 성공");//mysql에서 로그인해서 데이터베이스를 만들어 준다.
			
			Statement stmt= conn.createStatement();//java.sql 임포트 시킨다. db명령을 실행할 객체
	
			//삽입 
			/*String insert = "insert into test values('abc','123')";//실행할 db 명령
			int n=stmt.executeUpdate(insert);//db명령을 실제 실행하는 메소드
			//실행한 내용을 정수로 반환.
			System.out.println(n+"개 를 삽입했습니다.");
			
			//삭제 
			String delete = "delete from test";
			int k = stmt.executeUpdate(delete);
			System.out.println(k+"개를 삭제 했습니다.");*/
			/*//id가 ghi인 pw 값을 111로 수정
			
			
			//update테이블명 set 변경필드='변경값' where 검색필드 = '검색값'
			
			String update = "update test set pw ='111' where id='ghi'";
			int m = stmt.executeUpdate(update);
			System.out.println(m+"개가 수정되었습니다.");*/
		
		
				
			/*//테이블 데이터 조회(선택)
			//selsct*from 테이블명
			
			String select = "select*from test";
			
			ResultSet rs = stmt.executeQuery(select);//executeQuery() 반환타입 ResultSet
			
			rs.next();//다음 줄로 내려가라 내부포인터를 다음줄로 변경 / 다음 줄로 갈 수 있으면 true
			
			System.out.println(rs.getString("id"));//re.get데이터타입("필드명")
			
			rs.next();//다음 줄로 내려가라
			
			System.out.println(rs.getString("pw"));*/
	
			String select = "select*from test";
			ResultSet rs = stmt.executeQuery(select);//executeQuery()
			
				while(rs.next()){
				System.out.println(rs.getString("id"+"\t"));
				System.out.println(rs.getString("pw"));
				}
			} catch (ClassNotFoundException e) {
				System.out.println("ERR mySQL Driver Load:"+e.getMessage());
			} catch (SQLException e) {
				System.out.println("ERR connect:"+e.getMessage());
			
				}
	}//main(String[] args) end
}//class MainClass5 end

//select 필드명리스트 from 테이블명
//insert into 테이블명 (필드명리스트) values(데이터리스트)
//delete from 테이블명

//select * from test <= test 테이블의 모든 데이터 조회(선택)
// select id from test <== test 테이블의 아이디 값만 조회
//select pw from test => test 테이블의 비번만 조회
//select id,pw from test=>test 테이블 아이디와 비번을 조회

//insert into test (id) values('ccc')<= test테이블의 아이디 필드에만 ccc문자열을 삽입
//insert into test (pw) values('111')<= test테이블의 pw 필드에만 ccc문자열을 삽입
//insert into test (id,pw) values('dd','11')<= test테이블의 아이디,비번 필드 dd,11문자열을 삽입
//insert into test (pw,id) values('22','ee')<= test테이블의 아이디,비번 필드 '22','ee'문자열을 삽입
//insert into test values('22','ee')<= test테이블 생성시 생성된 필드 순서대로 값들을 삽입

//delete from test <- 모든 테이블 데이터 삭제

//select 필드명리스트 from 테이블명
//insert into 테이블명 (필드명리스트) values(데이터리스트)
//delete from 테이블명

//select * from test <= test 테이블의 모든 데이터 조회(선택)
// select id from test <== test 테이블의 아이디 값만 조회
//select pw from test => test 테이블의 비번만 조회
//select id,pw from test=>test 테이블 아이디와 비번을 조회

//insert into test (id) values('ccc')<= test테이블의 아이디 필드에만 ccc문자열을 삽입
//insert into test (pw) values('111')<= test테이블의 pw 필드에만 ccc문자열을 삽입
//insert into test (id,pw) values('dd','11')<= test테이블의 아이디,비번 필드 dd,11문자열을 삽입
//insert into test (pw,id) values('22','ee')<= test테이블의 아이디,비번 필드 '22','ee'문자열을 삽입
//insert into test values('22','ee')<= test테이블 생성시 생성된 필드 순서대로 값들을 삽입

//delete from test <- 모든 테이블 데이터 삭제

	
	1. JDBC 드라이버 로드
		1-1.Class.forName("com.mysql.jdbc.Driver");
		1-2. 예외처리 : ClassNotfoundException

​

	2. 데이터베이스 연결

		2-1. String db_url="jdbc:mysql://localhost:3306/DB명"//127.0.0.1 //포트번호 :3306
	
		String dc_id="root";
		String db_pw="설치시 비밀번호";
		Connection con = unll;
		con = DriverManager.getConnection(db_url,dc_id,db_pw);

		2-2. 예외처리 : SQLException

	3. SQL문실행

		3-1. Statement stmt = null;
		
		stmt= conn.createDtatement();
		String sql="insert into test_table values('abc','1234');
		int i = stmt.executeUpdate(sql);
		
		3-2. 예외처리: SQLException
		
		​

4 조회결과처리

​

5.데이터베이스와 연결해제

​

​

Enter password: ******

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.1.60-community MySQL Community Server (GPL)

​

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

​

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

​

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

​

mysql> create database testdb;

Query OK, 1 row affected (0.00 sec)

​

mysql> create database testdb;

ERROR 1007 (HY000): Can't create database 'testdb'; database exists

mysql> show datadases;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'datadases' at line 1

mysql> show databases

->

->

->

-> show databases;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'show databases' at line 5

mysql>

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

| testdb |

+--------------------+

4 rows in set (0.00 sec)

​

mysql> use testdb;

Database changed

mysql> create table test (id varchar(10),pw varchar(10));

Query OK, 0 rows affected (0.05 sec)

​

mysql> dsc test;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dsc test' at line 1

mysql> desc test;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | varchar(10) | YES | | NULL | |

| pw | varchar(10) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

​

mysql> insert into test (id,pw) values('abc','123');

Query OK, 1 row affected (0.03 sec)

​

mysql> insert into test (id,pw) values('def','456')'

'>

'> insert into test (id,pw) values('def','456');

'> insert into test (id,pw) values('def','456')'

->

-> insert into test (id,pw) values('def','456')'

'> insert into test (id,pw) values('def','456')'

-> insert into test (id,pw) values('def','456')'

'> i

'>

'>

'> insert into test (id,pw) values('def','456');

'> insert into test (id,pw) values('def','456');

'>

'> insert into test (id,pw) values('abc','123');

'>

'>

'>

'> '

-> insert into test (id,pw) values('def','456');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''

​

insert into test (id,pw) values('def','456');

insert into test (id,pw) values' at line 1

mysql> insert into test (id,pw) values('def','456');

Query OK, 1 row affected (0.02 sec)

​

mysql> select*from test;

+------+------+

| id | pw |

+------+------+

| abc | 123 |

| def | 456 |

+------+------+

2 rows in set (0.00 sec)

​

mysql> select*id from test;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id from test' at line 1

mysql> select id from test;

+------+

| id |

+------+

| abc |

| def |

+------+

2 rows in set (0.00 sec)

​

mysql> delete from test;

Query OK, 2 rows affected (0.05 sec)

​

mysql> select*id from test;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id from test' at line 1

mysql> select id from test;

Empty set (0.00 sec)

​

mysql> select id from test;

+------+

| id |

+------+

| abc |

+------+

1 row in set (0.01 sec)

​

mysql> select*from test;

+------+------+

| id | pw |

+------+------+

| abc | 123 |

+------+------+

1 row in set (0.00 sec)

​

mysql>

mysql> select*from test;

Empty set (0.00 sec)

​

mysql> insert into test (id,pw) values('adc','123');

Query OK, 1 row affected (0.05 sec)

​

mysql> insert into test (id,pw) values('def','456');

Query OK, 1 row affected (0.03 sec)

​

mysql> insert into test (id,pw) values('ghi','789');

Query OK, 1 row affected (0.02 sec)

​

mysql> select*from test;

+------+------+

| id | pw |

+------+------+

| adc | 123 |

| def | 456 |

| ghi | 789 |

+------+------+

3 rows in set (0.00 sec)

​

mysql> select pw from test where id='abc';

Empty set (0.00 sec)

​

mysql> select pw from test where id='abc';

Empty set (0.00 sec)

​

mysql> delete from test;

Query OK, 3 rows affected (0.02 sec)

​

mysql> insert into test (id,pw) values('abc','123');

Query OK, 1 row affected (0.03 sec)

​

mysql> insert into test (id,pw) values('def','456');

Query OK, 1 row affected (0.03 sec)

​

mysql> insert into test (id,pw) values('ghi','789');

Query OK, 1 row affected (0.03 sec)

​

mysql> select pw from test where id='abc';

+------+

| pw |

+------+

| 123 |

+------+

1 row in set (0.00 sec)

​

mysql> select pw from test where id='456';

Empty set (0.00 sec)

​

mysql> select pw from test where id='456';

Empty set (0.00 sec)

​

mysql> delete from test where id='abc';

Query OK, 1 row affected (0.03 sec)

​

mysql> select*from test;

+------+------+

| id | pw |

+------+------+

| def | 456 |

| ghi | 789 |

+------+------+

2 rows in set (0.00 sec)

​

mysql> update test set pw='111' where id='jdf';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 0

​

mysql> select*from test;

+------+------+

| id | pw |

+------+------+

| def | 456 |

| ghi | 789 |

+------+------+

2 rows in set (0.00 sec)

​

mysql> update test set pw='111' where id='def';

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

​

mysql> select*from test;

+------+------+

| id | pw |

+------+------+

| def | 111 |

| ghi | 789 |

+------+------+

2 rows in set (0.00 sec)

​

mysql> insert into test (id,pw) values('ghi','789');

Query OK, 1 row affected (0.03 sec)

​

mysql> select*from test;

+------+------+

| id | pw |

+------+------+

| def | 111 |

| ghi | 111 |

| ghi | 789 |

+------+------+

3 rows in set (0.00 sec)