sql(테이블만들기, SELET문..)

제이·2023년 5월 23일
Databasesql
0

데이터베이스

목록 보기
1/2

sql 까는 방법


1.sql 깐 다음에 HeidiSQL_11.2_64_Portable의 heidisql.exe를 실행
2.신규버튼 누르기
3.세션이름바꿔주기
4.암호 1234입력하기

sql 기본 설명

  • 데이터베이스 안에는 테이블형태로 저장됨.
  • 키워드는 대문자로. 내가만든 건 소문자로. 근데 대소문 크게 구분없음.
  • 문장의 끝에는 세미콜론.
  • 문자를 ' '로 표현.
  • '--' : 한줄주석, '/* */' :여러줄 주석

sql 코드 설명

  • num INT PRIMARY KEY : 정수형태이고 인마가 이 테이블의 주키입니다는 뜻.

  • DESC 테이블명; : 스키마. 테이블구조 확인하는 거.(아래설명)

  • DROP TABLE 테이블명; : 테이블 삭제.

  • CHAR(10): 10은 렝스가 아니고, 바이트이다. 고정길이 문자열
    VARCHAR(10) : 가변길이 문자열(바캐릭터라고 읽음.)

  • DECIMAL : 실수 나타낼때(데시말)
    DECIMAL(5,2) 5: 전체자리수, 2 : 실수로 표현할 자리수(소수이하의 자리)

  • SELECT * FROM 테이블명 : 전체 레코드 확인, 내가 넣은 코드를 보려면 이걸 부분실행해야한다.

스키마 설명(DESC 테이블명)

테이블안의 int(11)은 값을 설정하는게 아니라 그냥 기본이 11자리라는 뜻. int에 '1'을 넣었는다면, 11자리로 끊어서 앞에는 0으로 10개채우고 1을 보여준다는 뜻.
null이 no되어 있는 건 무조건 값을 넣어야 된다는 뜻.
dfault : 기본값은 다 null.

CHAR와 VARCHAR 설명

  • CHAR(5): 무조건 5바이트의 크기를 잡아먹는다. 만약 abc를 넣으면 abcOO이렇게 자리 차지한다.
    따라서, 자주 바뀌는 값들은 여기에 쓴다. 글자제목 같은 거. 닉네임 같은거.
  • VARCHAR(5) : 만약 abc를 넣으면 5칸중에 3칸만 사용한다. 모든 칸을 잡아먹지 않는다.
    따라서, 변경할 수 없는 항목이나 자리수 정해진거, 잘안바꾸는 항목들에 사용 - 아이디. 공간적인 이득은 취하지만, 변경에 대해서 취약하다.
  • 값이 바뀌면, abcd로 바뀌게 되면, CHAR는 그냥 바로 값을 이어서 적을 수 있지만, VARCHAR는 abc뒤에 들어온 애들을 한칸씩 다 밀어내야 한다. 반대로 ab만 넣으면 한칸씩 땡겨와야한다.

DECIMAL 설명

반올림되었다.
"23.456"은 되는데, "1234.56"은 안된다.
5,2면 정수분은 3자리를 넣을 수 있다는 것이니까. 총 3자리까지 들어갈 수 있다. 근데 이 정수분의 수가 넘어가면 무조건 에러다. 근데 실수분은 2자리 넘어가면 반올림이 된다. 그 자리수에 맞춰서.
"1.23456"을 넣으면 1.23이라는 값이 나온다. -> 경고는 뜰 수 있으나 에러는 아니다.
"12345"하면 에러 뜬다. 정수부 자릿수가 넘어갔다.

영향 받은 행: 1 몇개의 레코드가 영향을 받았는지. 한줄 넣어서 1이 나옴.
잡아넣는거, 수정하는 거, 삭제하는거 => 행이 바뀐다.
3개를 잡아넣엇으면 3이 바뀜.
우리는 root계정을 쓰고 있어서 다 삭제하고 수정하고 하는데, 일하러 가면 다른 계정을 줘서 테이블 삭제같은 거 못하게 해놨을 것이다.

<sql코드>

--한줄주석
/*
여러줄 주석
*/

DROP TABLE a_first;

CREATE TABLE a_first(	
	num			INT			PRIMARY KEY,
	my_value1	CHAR(10)		NOT NULL,
	my_value2	VARCHAR(10),
	my_value3	DECIMAL(5,2)

);
--테이블 구조(스키마) 확인
DESC a_first;

--값입력
INSERT INTO a_first (num,my_value1,my_value2,my_value3)
	VALUES(1, 'first', 'choonsik', 3.14);

INSERT INTO a_first VALUES (2, 'second', NULL, NULL);

INSERT INTO a_first (my_value3, num, my_value2, my_value1)
	VALUES (0.9, 3, 'iu', 'third');

INSERT INTO a_first (num, my_value1, my_value2)
	VALUES (4, 'forth', 'foo');
/*
INSERT INTO a_first (num,my_value1,my_value2,my_value3)
	VALUES(5, 'fifth', 'bar', 23.456);

INSERT INTO a_first (num,my_value1,my_value2,my_value3)
	VALUES(6, 'sixth', 'bar', 1234.56);

-- 실수부 자리수 2를 초과한 경우 3번째 자리에서 반올림한다.
INSERT INTO a_first (num,my_value1,my_value2,my_value3)
	VALUES(7, 'a', 'bar', 1.123456);
-- 정수부 자리수가 넘어갔음.
INSERT INTO a_first (num,my_value1,my_value2,my_value3)
	VALUES(8, 'ac', 'bar', 12345);
*/

INSERT INTO a_first(num, my_value1,my_value2,my_value3)
VALUES
(5, 'e', 'muzi', 100.23);
(6, 'f', 'neo', 22.11);
(7, 'g', 'ryan', 7.23);
(8, 'h', 'con', 9.99);

--삭제
DELETE FROM a_first WHERE num = 2;a_first

-- 수정
-- UPDATE a_first SET my_value3 = 15.0;
-- where안하면 전부 다 바껴버림!!!!
UPDATE a_first SET my_value3 = 15.0 WHERE num = 4;
UPDATE a_first SET my_value3 = 10.04, my_value1='a' WHERE num= 1;

--
UPDATE a_first SET my_value3 = 11, my_value1 = 'f' WHERE num = 1;


--전체 레코드 확인
SELECT * FROM a_first;

SELECT * FROM a_first WHERE num = 2;
SELECT my_value2 FROM a_first WHERE my_value3>=10 AND my_value3<=100;

-- null은 =로 검사하면 안된다. 
SELECT num, my_value2 FROM a_first WHERE my_value3 IS NULL;
SELECT num, my_value2 FROM a_first WHERE my_value3 IS NOT NULL;

--오름차순, 내림차순
SELECT * FROM a_first ORDER BY my_value3 ASC;
SELECT * FROM a_first ORDER BY my_value3 DESC;

-- my_value3이 5이상 100이하인 레코드
SELECT my_value2 FROM a_first WHERE my_value3 BETWEEN 5 AND 100;

--my_value1이 'f'가 아닌 레코드
SELECT * FROM a_first WHERE my_value1 <> 'f';
SELECT * FROM a_first WHERE NOT my_value1 ='f';

--my_value2가 'iu' 또는 'foo'인 레코드 구하기
SELECT * FROM a_first WHERE  my_value2 ='iu' OR my_value2='foo';
SELECT * FROM a_first WHERE  my_value2 IN ('iu','foo');

--my_value2에 'o'가 들어간 레코드를 모두 구하라.
/*
	와일드 카드
	% : 0~무한대
	_ : 1글자
*/
SELECT * FROM a_first WHERE my_value2 LIKE '%o%'
SELECT * FROM a_first WHERE my_value2 LIKE 'c%'

--my_value2가 3글자인데 o로 끝나는 거.
SELECT * FROM a_first WHERE my_value2 LIKE '__o'
  • INSERT INTO 테이블명 VALUES :테이블에 내가 값을 잡아 넣겠다.
  • DELTE FROM 테이블 명 : 삭제
  • UPDATE 테이블명 SET 컬럼명=수정할 값 WHHRE 조건 : 수정. where안하면 전부 다 바껴버리기 때문에 where 꼭 넣기!
  • SELECT * FROM 테이블명 : 전체 레코드 확인, 내가 넣은 코드를 보려면 이걸 부분실행해야한다.
  • null은 =로 검사하면 안된다
    SELECT num, my_value2 FROM a_first WHERE my_value3 IS NULL;
    SELECT num, my_value2 FROM a_first WHERE my_value3 IS NOT NULL;
  • 오름차순, 내림차순
    SELECT FROM a_first ORDER BY my_value3 ASC;
    SELECT     FROM a_first ORDER BY my_value3 DESC;
  • my_value3이 5이상 100이하인 레코드
    SELECT my_value2 FROM a_first WHERE my_value3 BETWEEN 5 AND 100;
  • my_value1이 'f'가 아닌 레코드
    SELECT FROM a_first WHERE my_value1 <> 'f';
    SELECT     FROM a_first WHERE NOT my_value1 ='f';
    .
  • my_value2가 'iu' 또는 'foo'인 레코드 구하기
    SELECT FROM a_first WHERE my_value2 ='iu' OR my_value2='foo';
    SELECT     FROM a_first WHERE my_value2 IN ('iu','foo');

와일드카드

  • % : 0~무한대
  • _ : 1글자
    .
  • my_value2에 'o'가 들어간 레코드를 모두 구하라.
    SELECT * FROM a_first WHERE my_value2 LIKE '%o%'
    .
  • my_value2에 'c'로 시작하는 레코드를 모두 구하라.
    SELECT * FROM a_first WHERE my_value2 LIKE 'c%'
    .
  • my_value2가 3글자인데 o로 끝나는 거.
    SELECT * FROM a_first WHERE my_value2 LIKE '__o'

  • DML - 데이터 조작 : insert, select, delete, update(우리가 해야하는거!) - crud만 하면 된다.

  • DCL - 데이터 제어
    commit : 내가 이때까지 했던 작업들 적용해
    rollback:지금까지 처리했던 거 취소해,
    grant :권한설정하는거,
    revoke :권한을 취소하는 거

  • DDL - 데이터 정의 : create, drop, alter

이클립스에 sql적용

https://mvnrepository.com/ -> sql관련 다운 거 다 받을 수 있다.
여기에 가서 'connector' 이라고 검색한 후에
MySQL Connector Java 클릭 후에 5.1.49을 클릭후 jar파일을 다운 받는다.

이클립스로 돌아와서..
톰캣안에 있는 libraries(java resource)에 넣을 수도 있고,
05.23에서 있는 WEB-INF안에 있는 lib에 넣을 수도 있는데,
05.23에 넣으면 코드에 필요한 것을 넣는 것이다.
배포할때는 라이브러리 폴더 안에 있는 애들만 나간다.

  • 실행 : 드래그 + alt + x

<이클립스에서 sql코드 작성>

--실행 : 드래그 + alt + x
DROP TABLE a_second;

CREATE TABLE a_second (
	id			INT			PRIMARY KEY 		AUTO_INCREMENT,
	user_name	VARCHAR(20)	NOT NULL,	
	user_age	INT			NOT NULL,	
	user_job	VARCHAR(20)	NOT NULL,	
	user_tel	CHAR(11)	NOT NULL
);

INSERT INTO a_second (user_name, user_age, user_job, user_tel)
VALUES 
('smith', 30, 'singer', '01033334444'),
('jane', 22, 'dancer', '01012345678'),
('tomas', 45, 'student', '01088887777'),
('sam', 26, 'waiter', '01055556666');

SELECT * FROM a_second;

--20세 이상 30세 이하인 사람 중 직업이 'dancer'인 사람의 이름과 나이 전화번호를 구하라
SELECT user_name, user_age, user_tel FROM a_second WHERE (user_job ='dancer')AND (user_age BETWEEN 20 AND 30);

--이름에 'a'가 들어가거나 's'가 들어가는 사람의 모든 정보를 구하라. 단 나이로 내림차순 정렬한다.
SELECT * FROM a_second WHERE user_name LIKE '%a%'OR user_name LIKE '%s%' ORDER BY user_age DESC;

--tomas의 나이를 38로 변경하시오. 키가 아니기때문에 where을 select문으로 날려주고 맞는지 확인하고 여기에 넣기. 
UPDATE a_second SET user_age = 38 WHERE user_name ='tomas';
SELECT * FROM a_second WHERE user_name ='tomas';

  • AUTO_INCREMENT : 우리가 관리안하고 알아서 자동으로 번호를 발급해준다. mysql에서만 되는 거. 주키에만 사용하가능하고, 한 테이블에 하나씩만 가능하다.

  • JDBC(java database connectivity)

  1. 드라이버로드 : 딱한번만 하면 된다.
    그래서 생성자에 넣어놓는다.
    다음에 데이터베이스 하면 2~4번이 반복되는 것이다.
  2. 연결
  3. 질의
  4. 자원해제

이클립스 코드 적용(book코드)

book.sql

CREATE TABLE book (
	b_num			INT				PRIMARY KEY			AUTO_INCREMENT,
	b_title			CHAR(200)		NOT NULL,		
	b_writer		CHAR(30)		NOT NULL,		
	b_password		VARCHAR(10)		NOT NULL,		
	b_price			INT				NOT NULL,
	b_publisher		VARCHAR(30)		NOT NULL,
	b_comment		CHAR(200)		NOT NULL,
);

--전체보기
SELECT * FROM book ORDER BY b_num DESC;

--책 추가하기
INSERT INTO book (b_title, b_writer, b_password, b_price, b_publisher, b_comment)
	VALUES (?,?,?,?,?,?);
	
--  책 삭제
DELETE FROM book WHERE b_num =?;

BookDao.java

package kr.ac.green;

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

/*
 * JDBC(java database connectivity)
 * 
 * 1.드라이버로드	
 * 2.연결	
 * 3.질의
 * 4.자원해제
 * */

public class BookDao {
	private static final BookDao INSTANCE = new BookDao();
	
	private BookDao() {
		//1.드라이버로드
		try {
			Class.forName("com.mysql.jdbc.Driver");	//mysql드라이버
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static BookDao getInstance() {
		return INSTANCE;
	}
	
	//2.연결
	public Connection connect() {
		//java.Connection
		Connection con = null;
		try {
			//jdbc:mysql://localhost:3306/text mysql의 url인듯...
		con = DriverManager.getConnection("jdbc:mysql://localhost:3306/text", "root", "1234");
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	
	//4.자원해제
	public void disconnect(Connection con) {
		try {
			con.close();
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public int insertBook(Connection con, Book book) {
		int result = 0;
		//데이터베이스에 질의를 날리는 거. - statement
		Statement stmt = null;
		//import : java.sql.Statement
		
		try {
			//executeUpdate : insert, update, delete => int리턴 :3개 지워지면 3이 리턴되는 것이다. ㅌ
			//executeQuery : select
			stmt = con.createStatement();
			/*
			String sql = "INSERT INTO book (b_title, b_writer, b_password, b_price, b_publisher, b_comment)"
					+ "VALUES ('"+book.getTitle() +"','"+book.getWriter() +"','"+book.getPassword() +"',"+book.getPrice() +",'"+book.getPublisher() +"','"+book.getComment() +"')";
			*/
			String sql = "INSERT INTO book (b_title, b_writer, b_password, b_price, b_publisher, b_comment) VALUES ('%s','%s','%s','%d','%s','%s')";
			sql = String.format(sql, book.getTitle(), book.getWriter(), book.getPassword(), book.getPrice(), book.getPublisher(), book.getComment());
			result = stmt.executeUpdate(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				//자원해제 -  데이터베이스도 max세션이 있어서 자원해제를 안하면 계속 세션이 남아있다. 엑세스디나이가 떠서 안된다. 무조건 필수로 해줘야 한다. 
				stmt.close();
			}catch(Exception e) {
				
			}
		}
		return result;
		
	}
}

TestDao.java

package kr.ac.green.text;

import java.sql.Connection;	//이걸로 import해야한다!

import kr.ac.green.Book;
import kr.ac.green.BookDao;

public class TestDao {
	public static void main(String[] args) {
		BookDao dao = BookDao.getInstance();
		
		Connection con = dao.connect();
		
		int result = dao.insertBook(con, new Book("power java", "inkuk", "1234", 28000, "infinity books", "wow!!"));
		
		dao.disconnect(con);
		System.out.println(result); //1이 나오면 제대로 작동.
	}
}

먜)
자바에서 sql문 실행하기 위한것 : Statement객체
쿼리를 삽입할 때는 Statement객체를 사용하는 것 같음.

Java에서 데이터베이스와의 연결을 나타내는 인터페이스 : Connection객체

profile
제이
Hello :)
이전 포스트

데이터베이스(key,무결성,정규화)

0개의 댓글