# chapter03_06
# 집합(set)
# 집합 (순서X, 중복X, 추가 및 삭제 O)


# 선언

a = set()
print(a)
b = set([1,2,3,4])
c = set([1,4,5,6])
d = set([1,2,'pen','cap','plate'])
e = {'foo','bar','baz','foo','qux'} #key가없이 원소만 나열한다면 SET
f = {42, 'foo', (1,2,3), 3.14159}

# 출력
print ('a-',type(a),a, 2 in a )
print ('b-',type(b),b, 2 in b )
print ('c-',type(c),c)
print ('d-',type(d),d)
print ('e-',type(e),e)
print ('f-',type(f),f, 2 in f )


# 튜플 변환 (Set>>tuple)
print()
t=tuple(b)
print('t-',t)
print(type(t))
print('t-', t[0], t[1:3])


# 리스트 변환
l = list(c)
l2 = list(e)
print('l-', l, type(l))
print('l2-', l2, type(l2))


# 길이
print(len(a))
print(len(b))
print(len(c))
print(len(d))
print(len(e))
print(len(f)) #갯수/ 중복xxx


# 집합 자료형 활용
# 교집합.
s1 = set([1,2,3,4,5,6])
s2 = set([4,5,6,7,8,9])
print('s1 & s2 :' ,s1 & s2)
print('s1 & s2 :' ,s1.intersection(s2))
# 합집합.
print('s1 | s2 : ', s1|s2)
print('s1 | s2 : ', s1.union(s2))
# 차집합
print('s1 - s2 : ', s1 - s2)
print('s1 - s2 : ', s1.difference(s2))
# 중복원소가 있는지 알려주는 함수
print('s1 & s2 ', s1.isdisjoint(s2)) #False 일떄 교집함이 있다는뜻!! (dis)
# 부분 집합 확인
print('subset : ', s1.issubset(s2))
print('superset : ',s1.issuperset(s2))


# 데이터 추가/삭제.
s1 = set([1,2,3,4])
s1.add(5)
print(s1)
s1.remove(2)
print(s1)
# remove로 없는 원소를 삭제하려고 하면 Key error 라는 에러가 나타난다
s1.discard(3)
print(s1)
s1.discard(7) # 없는 원소를 삭제하려고 해도 에러X (예외발생X)
s1.clear() #전부삭제(리스트도마찬가지임)
print(s1)

# 챕터03_5
# 파이썬 딕셔너리
# 범용적으로 가장 많이 사용
# 딕셔너리 자료형 (순서X, 키 중복X, 수정O, 삭제O)


# 선언 / a= (튜플), [리스트], {딕셔너리}
ex = {'key': 'value'}
a = {'name' : 'kim', 'phone' : '01011111111', 'birth' : '870514'}
b = {0 : 'hello python'}
c = {'arr' : [1,2,3,4,5]}
d = {
    'Name' : 'Niceman',
    'City' : 'Seoul',
    'Age'  : 20,
    'Grage': 'A',
    'status' : True
}

# 자주쓰이진 않지만 리스트안에 튜플 형태로 선언하기도 한다. (불편..)
e = dict([
    ('Name', 'Niceman'),
    ('City', 'Seoul'),
    ('Age', 20),
    ('Grage', 'A'),
    ('status', True)
])

f=dict(
    name='niceman',
    city='Seoul',
    age = 20,
    grade = 'A',
    status = True
)


# 타입
print('>>>>>>>')
print('a  - ', type(a), a)
print('b  - ', type(b), b)
print('c  - ', type(c), c)
print('d  - ', type(d), d)
print('e  - ', type(e), e)
print('f  - ', type(f), f)
print()

# 출력
print('>>>>>>>')
print('a  - ', a['name'])           # 키가 존재 X > 에러발생
print('a  - ', a.get('name'))       # 키가 존재 X > NONE으로 처리
print('b  - ', b[0])
print('b  - ', b.get(0))
print('f  - ', f.get('city'))
print('f  - ', f.get('age'))
print()

# 딕셔너리 추가
print('>>>>>>>')
a['address'] = 'seoul'
a['name'] = 'jin'  # 원래 있던 값(name)을 추가하면 수정해버린다 (kim>jin)
a['rank'] = [1,2,3]
print('a  - ', a)
print()

# 딕셔너리 길이 확인
print('>>>>>>>')
print('a  - ', len(a)) #키의 갯수
print('b  - ', len(b))
print('c  - ', len(c))
print('d  - ', len(d))
print('e  - ', len(e))
print('f  - ', len(f))
print()


# dict_keys, dict_values, dict_items  : 반복문(__iter__)에서 사용가능
print('>>>>>>>')
# .keys() 키값들만 가져온다.
print('a  - ',  a.keys())
print('c  - ',  c.keys())
print('d  - ',  d.keys())
print('e  - ',  e.keys())
print('e  - ',  list(e.keys()))
print('a  - ',  list(a.keys()))
#.values 밸류값만 가져온다.
print('c  - ',  c.values())
print('d  - ',  d.values())
print('e  - ',  e.values())
#.itmes 키와 밸류값을 가져온다.
print('c  - ',  c.items())
print('d  - ',  d.items())
print('e  - ',  e.items())
print('e  - ',  list(e.items()))
print('d  - ',  list(d.items()))
print()
print('>>>>>>>')
print('a  - ', a.pop('name'))
print('a  - ', a)
print('c  - ', c.pop('arr'))
print('c  - ', c)
print()
print('f  - ', f.popitem())
print('f  - ', f)
print('f  - ', f.popitem())
print('f  - ', f)
print('f  - ', f.popitem())
print('f  - ', f)
print('f  - ', f.popitem())
print('f  - ', f)
print('f  - ', f.popitem())
print('f  - ', f)
print()
print('a  - ', 'birth' in a ) # birth라는 키가 a에 있는가.
print('a  - ', 'city' in b)


#수정.
print('>>>>>>>')

a['test']='test_dict'
print('a  - ', a)

a['test']='test_dict2'
print('a  - ', a)

a.update(test='test_dict3')
print('a  - ', a)

temp = {'test' : 'test_dict4'}
a.update(temp)
print('a  - ', a)

#chapter03_04
# 파이썬 튜플
# 리스트와 튜플 비교
# 튜플 자료형( 순서와 중복은 가능하지만, 수정과 삭제가 불가능(del/remove)_리뮤테이블

# 선언

t1 = ()
t2 = (1, 2)
t3 =(1,)  # 1개는 끝이 , 로 끝나야 tuple로 인식한다 (1)=int
t4  = 1, 2, 3



a=()
b=(1,)
c=(11,12,13,14)
d=(100, 1000, 'Ace', 'Base', 'Captain')
e=(100, 1000, ('Ace', 'Base', 'Captain'))

# 인덱싱
print('>>>>>>>>>')
print('d - ' ,d[1])
print('d - ' ,d[0]+d[1]+d[1])
print('d - ', d[-1])
print('e - ', e[-1]) #튜플반환
print('e - ', e[-1][1])

print('chane list - ', list(e[-1][1]))


# 수정xxx
# d[0] = 1500

# 슬라이싱
print('>>>>>>>>>')
print('d - ', d[0:3])
print('d - ', d[2:])
print('e - ', e[2][1:3])

# 튜플 연산
print('>>>>>>>>>')
print('c+ d - ', c+d)
print('c * 3 - ', c*3)

# 튜플 함수
print('>>>>>>>>>')
a = ( 5, 2, 3, 1, 4)
print('a - ',a)
print('a - ', a.index(3)) #3의 위치
print ('a - ', a.count(2)) # 2의 갯수

# 팩킹 & 언팩킹

# 팩킹 ( 하나로 묶는다 )

t = ('foo','bar','baz','qux')
print(t)


# 언팩킹1
(x1, x2, x3, x4) = t #()없어도 가능, 하지만 언팩킹을 알려주기위해 ()를 쓰는편,
print(type(x1), type(x2), type(x3), type(x4) ) #str
print(x1, x2, x3, x4)


# 팩킹&언팩킹1

t2 = 1, 2, 3 #괄호가 없어도 튜플임
t3 = 4, 3
x1, x2, x3 = t2
x4, x5, x6 = 4, 5, 6

print(t2)
print(t3)
print(x1, x2, x3)
print(x4, x5, x6)

package dept;

public class Dept {
	
	// VO : 데이터를 저장하는 기능만 가지는 클래스, read Only
	// DTO : 데이터틀 저장하는 기능을 가지는 클래스, Write 가능
	
	public Dept(int deptno, String dname, String loc) {
		super();
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}
	
	private int deptno;
	private String dname;
	private String loc;
	
	
	
	
	@Override
	public String toString() {
		return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
	}

	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public String getLoc() {
		return loc;
	}
	public void setLoc(String loc) {
		this.loc = loc;
	}
	
}

package dept;

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

public class ConnectionProvider {

	public static Connection getConnection() throws SQLException {
		
		Connection conn = null;

         // 2. DB 연결 localhost == 127.0.0.1
         String jdbcUrl = "jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
         String user = "hyo";
         String password = "admin";
         conn = DriverManager.getConnection(jdbcUrl, user, password);

         return conn;
	}
	
}

package dept;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

// DAO : Data Access Object -> 데이터베이스 처리하는 기능만 가지는 클래스
public class DeptDao {

	private static DeptDao dao = new DeptDao();
	
	private DeptDao() {
	}
	
	public static DeptDao getInstance() {
		return dao;
	}
	
	

	// 부서 입력
	int insertDept(Dept dept, Connection conn) {

		int resultCnt = 0;

		// DB 연결 : Connection
		try {
			// Statement
			// SQL : Insert into
			String sql = "insert into dept values(?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dept.getDeptno());
			pstmt.setString(2, dept.getDname());
			pstmt.setString(3, dept.getLoc());

			// sql 실행
			resultCnt = pstmt.executeUpdate();

			pstmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		// 결과
		return resultCnt;

	}

	// 부서 정보를 수정 : Dept
	int updateDept(Dept dept, Connection conn) {

		int resultCnt = 0;

		// DB 연결 : Connection
		try {
			// Statement
			// SQL : Update
			String sql = "update dept set dname=?, loc=? where deptno=? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(3, dept.getDeptno());
			pstmt.setString(1, dept.getDname());
			pstmt.setString(2, dept.getLoc());

			// sql 실행
			resultCnt = pstmt.executeUpdate();

			pstmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		// 결과
		return resultCnt;

	}

	// 부서정보 삭제
	int deleteDept(int deptno, Connection conn) {

		int resultCnt = 0;

		// DB 연결 : Connection
		try {
			// Statement
			// SQL : delete
			String sql = "delete from dept where deptno=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, deptno);

			// sql 실행
			resultCnt = pstmt.executeUpdate();

			pstmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		// 결과
		return resultCnt;
	}

	// 부서의 전체 리스트
	List<Dept> listDept(Connection conn) {

		List<Dept> list = new ArrayList<Dept>();

		Statement stmt = null;

		try {
			stmt = conn.createStatement();

			String sql = "select * from dept order by deptno";

			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				// Dept d = new Dept(rs.getInt(1), rs.getString(2), rs.getString(3));
				// list.add(d);
				list.add(new Dept(rs.getInt(1), rs.getString(2), rs.getString(3)));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	// 부서 하나 검색
	Dept searchDept(int deptno, Connection conn) {
		
		Dept dept = null;

		Statement stmt = null;

		try {
			stmt = conn.createStatement();

			String sql = "select * from dept where deptno="+deptno;

			ResultSet rs = stmt.executeQuery(sql);

			if(rs.next()) {
				dept = new Dept(rs.getInt(1), rs.getString(2), rs.getString(3));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return dept;

	}

}

package dept;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;

public class DeptManager {

	// 사용자 입력
	Scanner sc = new Scanner(System.in);

	DeptDao dao = DeptDao.getInstance();

	// 부서입력 메소드 : 사용자에게 입력 받은 데이터를 DAO를 이용해서 DB 입력
	void insertDept() {

		// 부서정보를 받아서 인스턴스를 생성하고 dao.insertDept()

		System.out.println("부서정보를 입력합니다.");
		System.out.println("부서번호를 입력해주세요.");
		String deptno = sc.nextLine();
		System.out.println("부서이름을 입력해주세요.");
		String dname = sc.nextLine();
		System.out.println("지역 이름을 입력해주세요.");
		String loc = sc.nextLine();

		Dept dept = new Dept(Integer.parseInt(deptno), dname, loc);

		Connection conn = null;

		int resultCnt = 0;

		try {
			conn = ConnectionProvider.getConnection();
			
			resultCnt = dao.insertDept(dept, conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(resultCnt>0) {
			System.out.println("입력되었습니다.");
		} else {
			System.out.println("입력이 실패했습니다.");
		}
	}


	// 부서 정보 수정 메소드 : 사용자에게 입력 받고 데이터를 DAO를 이용해서 수정
	void editDept() {
		
		System.out.println("수정을 원하시는 부서번호를 입력해주세요.");
		String deptno = sc.nextLine();
		System.out.println("새로운 부서 이름을 입력해주세요. ");
		String dname = sc.nextLine();
		System.out.println("새로운 지역 이름을 입력해주세요.");
		String loc = sc.nextLine();
		
		Dept dept = new Dept(Integer.parseInt(deptno), dname, loc);
		

		Connection conn = null;
		
		int resultCnt = 0;

		try {
			conn = ConnectionProvider.getConnection();
			
			resultCnt = dao.updateDept(dept, conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(resultCnt>0) {
			System.out.println("수정되었습니다.");
		} else {
			System.out.println("수정이 실패했습니다.");
		}
		
		
	}

	
	// 부서 정보를 삭제
	void delDept() {
		
		System.out.println("삭제를 원하시는 부서번호를 입력해주세요.");
		String deptno = sc.nextLine();

		Connection conn = null;
		
		int resultCnt = 0;

		try {
			conn = ConnectionProvider.getConnection();
			
			resultCnt = dao.deleteDept(Integer.parseInt(deptno), conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(resultCnt>0) {
			System.out.println("삭제되었습니다.");
		} else {
			System.out.println("지우려는 정보가 존재하지 않습니다.");
		}
		
		
	}
	
	void listDept() {
		
		Connection conn = null;
				
		List<Dept> list = null;

		try {
			conn = ConnectionProvider.getConnection();
			
			list = dao.listDept(conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(list.isEmpty()) {
			System.out.println("저장된 정보가 없습니다.");
		} else {
			
			for(Dept d : list) {
				//System.out.println(d);
				System.out.printf("%5s", d.getDeptno()+"\t" );
				System.out.printf("%12s", d.getDname()+"\t" );
				System.out.printf("%12s", d.getLoc()+"\n" );
			}
			
			
		}
		
	}
	
	void searchDept() {
		
		System.out.println("부서를 검색합니다.");
		System.out.println("찾으시는 부서의 번호를 입력해주세요.");
		String deptno = sc.nextLine();
		

		Connection conn = null;

		Dept dept = null;

		try {
			conn = ConnectionProvider.getConnection();
			
			dept = dao.searchDept(Integer.parseInt(deptno), conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(dept!=null) {
			System.out.println(dept);
		} else {
			System.out.println("찾으시는 정보가 존재하지 않습니다.");
		}
		
		
	}


}

package dept;

import java.util.Scanner;

public class DeptMain {

	public static void main(String[] args) {

		DeptManager manager = new DeptManager();

		Scanner sc = new Scanner(System.in);

		// 데이터 베이스 드라이버 로드
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");

			while(true) {

				System.out.println("부서관리 메뉴를 입력해주세요.");
				System.out.println("1. 입력, 2. 수정, 3.삭제, 4. 전체리스트, 5. 검색, 6. 종료");
				System.out.println("__________________________________________________");

				String select = sc.nextLine();

				switch (select.charAt(0)) {
				case '1':
					manager.insertDept();
					break;
				case '2':
					manager.editDept();
					break;
				case '3':
					manager.delDept();
					break;
				case '4':
					manager.listDept();
					break;
				case '5':
					manager.searchDept();
					break;
				case '6':
					System.out.println("프로그램을 종료합니다.");
					return;
				}
			}
			

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}

package jdbc;

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

public class JDBCMysqlStatementTest {

	public static void main(String[] args) {
		Scanner sc= new Scanner(System.in);
		Connection conn = null;
		
		try {
			// 1. 드라이버 로드
	         Class.forName("com.mysql.cj.jdbc.Driver");
	         System.out.println("Driver Load!");
			
			// 2. DB 연결     localhost == 127.0.0.1
	         String jdbcUrl ="jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
	         String user = "hyo";
	         String password = "admin";
	         
	         conn = DriverManager.getConnection(jdbcUrl, user, password);
	         System.out.println("데이터베이스에 접속했습니다!");
			
			// 3. statement 인스턴스생성
			
			Statement stmt = conn.createStatement();
			
			System.out.println("부서 이름을 입력해주세요.");
			String userDname= sc.nextLine();
			System.out.println("부서의 위치를 입력해주세요.");
			String userLoc = sc.nextLine();
			
			
			// 입력 : insert
			
			String sqlInsert = "insert into dept (deptno, dname, loc) values (80, '"+userDname+"','"+userLoc+"')";
				
			
			int resultCnt = stmt.executeUpdate(sqlInsert); // sql문을 실행하고 횟수반환
			if(resultCnt>0) {
				System.out.println("정상적인 입력");
			}
			
			// 4. sql 실행 : 부서리스트 출력
			String sql="select * from dept order by deptno";
			ResultSet rs = stmt.executeQuery(sql);
			
			// 5. ResultSet을 이용해서 결과 출력
			while(rs.next()) { 			// rs.next(); // boolean Type
	              int deptno = rs.getInt("deptno");
	              String dname = rs.getString("dname");
	              String loc = rs.getString(3);
	              
	              // System.out.println(deptno+"\t"+dname+"\t"+loc);
	              System.out.printf("%5s",deptno+"\t");
	              System.out.printf("%12s",dname+"\t");
	              System.out.printf("%12s",loc+"\n");

			}
			
			rs.close();
			stmt.close();
			conn.close();		
			
		} catch (ClassNotFoundException e) {
			System.out.println("Driver 로드 실패");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		 
		
		
		
	}

}​

package jdbc;

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

public class JDBCMysqlConnectionTest {

   public static void main(String[] args) {

      Connection conn = null;
      
      try {
         // 1. 드라이버 로드
         Class.forName("com.mysql.cj.jdbc.Driver");
         System.out.println("Driver Load!");
         
         // 2. DB 연결       localhost == 127.0.0.1
         String jdbcUrl ="jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
         String user = "hyo";
         String password = "admin";
         
         conn = DriverManager.getConnection(jdbcUrl, user, password);
         System.out.println("데이터베이스에 접속했습니다.");
         
         // ....
         
         
         conn.close();
         
   
      } catch (ClassNotFoundException e) {
         System.out.println("Driver 로드 실패");
         e.printStackTrace();
      } catch (SQLException e) {
         e.printStackTrace();
      }
      
      
      
      
      
   }

}

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCMysqlPreparedTest {

	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		Connection conn = null;

		try {
			// 1. 드라이버 로드
			Class.forName("com.mysql.cj.jdbc.Driver");
			System.out.println("Driver Load!");

			// 2. DB 연결 localhost == 127.0.0.1
			String jdbcUrl = "jdbc:mysql://localhost:3306/project?serverTimezone=UTC";
			String user = "hyo";
			String password = "admin";

			conn = DriverManager.getConnection(jdbcUrl, user, password);
			System.out.println("데이터베이스에 접속했습니다.");

			// Statement stmt = conn.createStatement();

			System.out.println("부서 번호 입력해주세요.");
			int userDeptno = sc.nextInt();
			sc.nextLine();
			System.out.println("부서 이름을 입력해주세요.");
			String userDname = sc.nextLine();

			System.out.println("부서의 위치를 입력해주세요.");
			String userLoc = sc.nextLine();

			// PreparedStatement 인스턴스 생성
			String sqlInsert = "insert into dept values (?, ?, ?)";

			PreparedStatement pstmt = conn.prepareStatement(sqlInsert);
			pstmt.setInt(1, userDeptno);
			pstmt.setString(2, userDname);
			pstmt.setString(3, userLoc);

			int resultCnt = pstmt.executeUpdate();

			if (resultCnt > 0) {
				System.out.println("정상적으로 입력되었음.");
			} else {
				System.out.println("데이터 입력이 되지 않았음.");
			}

			// 부서 리스트 출력
			String sqlList = "select * from dept order by loc";
			pstmt = conn.prepareStatement(sqlList);
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				System.out.print(rs.getInt(1) + ", ");
				System.out.print(rs.getString(2) + ", ");
				System.out.print(rs.getString(3) + "\n");
			}

			rs.close();
			pstmt.close();
			conn.close();

		} catch (ClassNotFoundException e) {
			System.out.println("Driver 로드 실패");

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}

1) 커넥트 파일 위치 알아두기 

- 헛갈리지 않게 따로 복사해서 보관해두는 것도 좋다.

2) 라이브러리 불러오기  Build Path - Configure Build Path

3 Add Extenal Jars

4-1) Mysql

4-2) Oracle sql

1) 계정생성

create user `tester`@`localhost` identified by 'test123';
drop user tester;

create user `tester`@`localhost` identified by 'test123';
drop user `tester`@`localhost`;

2) 스키마생성

3) 스키마권한주기 add entry (project 아까만든거)

grant option : 권한을 주는 권한

4) 커넥션 생성

5) 테이블 생성 (tables 우클릭)

AI ) 시퀀스가 없고 대신 이게 있음 auto incremental

테이블 만들때 utf8 general_ci(디폴트)로 설정해서 알아서 되어있음

옆에 아이콘들 누르면 정보보기 / 수정 / 현재 데이터 조회할 수 있다.

6) 입력하기

SELECT `member`.`idx`,
    `member`.`userid`,
    `member`.`password`,
    `member`.`username`,
    `member`.`regdate`
FROM `project`.`member`;


INSERT INTO `project`.`member`
(`userid`,`password`,`username`)
VALUES
('king',1111,'king');


UPDATE `project`.`member`
SET
`userid` = 'hyosoen'
WHERE `idx` = 1;

DELETE FROM `project`.`member` WHERE `idx`=1;

rollback;
commit;

dev.mysql.com/downloads/file/?id=499589

 [excute 누름]

[mysql 기본포트 3306]

[admin/admin]

[add 눌러서 모자란거설치]

package Homework;

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

public class Jdbc_Homework1 {

	public static void main(String[] args) {

			// 2020.11.18
		
		Connection conn = null;
		
		try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("Oracle Driver Load !!!");

			
			String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
			String user = "scott";
			String password = "tiger";
			
			conn = DriverManager.getConnection(jdbcUrl, user, password);
			System.out.println("데이터베이스에 접속했습니다.");
			System.out.println("");
			
			
			// 인스턴스 생성하기
			PreparedStatement pstmt = null;
			
////////////// 1) EMP 테이블에 새로운 사원 정보를 입력하는 프로그램을 작성해보자.
			// emp : empno, ename, job, mgr, hiredate, sal, comm, deptno
			String sqlQ1 = "insert into emp values (seq_emp_empno.nextval,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sqlQ1);
			pstmt.setString(1,"hyoseon");
			pstmt.setString(2,"gamer");
			pstmt.setInt(3,7839);
			pstmt.setString(4,"20/11/18");
			pstmt.setInt(5,10000);
			pstmt.setInt(6,2000);
			pstmt.setInt(7,30);
			
			int resultCnt = pstmt.executeUpdate();			
			if (resultCnt>0) {
				System.out.println("");
				System.out.println("-------------------- 문제 1번) 정상입력");
				System.out.println("-------------------- 새로운 사원정보 입력 / ename : hyoseon");
				System.out.println("");
				resultCnt=0;
			}else {
				System.out.println("입력이 되지 않았음");
			}
			
			
			// 부서 리스트 출력
//////////////2) EMP 테이블의 모든 데이터를 출력하는 프로그램을 작성해보자.
			
			
			System.out.println("");
			System.out.println("-------------------- 문제 2번) 모든데이터출력");
			System.out.println("");
			String sqlList="select * from emp order by empno";
			pstmt=conn.prepareStatement(sqlList);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				System.out.print(rs.getInt(1)+"\t");
				System.out.print(rs.getString(2)+"\t");
				System.out.print(rs.getString(3)+"\t");
				System.out.print(rs.getInt(4)+"\t");
				System.out.print(rs.getString(5)+"\t");
				System.out.print(rs.getInt(6)+"\t");
				System.out.print(rs.getInt(7)+"\t");
				System.out.print(rs.getInt(8)+"\t");
				System.out.println("");
				
			}
////////////// 3) EMP 테이블에 서 “SCOTT” 사원의 급여(sal) 정보를 1000으로 바꾸는 프로그램을 작성해보자.
			String sqlQ3 = "update emp set sal=1000 where ename='SCOTT'";
			Statement stmt = conn.createStatement();
			resultCnt = stmt.executeUpdate(sqlQ3);
			if (resultCnt>0) {
				System.out.println("");
				System.out.println("-------------------- 문제 3번) 정상입력");
				System.out.println("-------------------- 스캇 월급 1000으로 수정!");
				System.out.println("");
			}else {
				System.out.println("입력이 되지 않았음");
			}
////////////// 4)EMP 테이블에 서 “SCOTT” 이름으로 검색한 결과를 출력하는 프로그램을 작성해보자.
			System.out.println("");
			System.out.println("-------------------- 문제 4번) Scott 검색 데이터 출력");
			
			String sqlQ4 = "select * from emp where ename='SCOTT'";
			
			rs = stmt.executeQuery(sqlQ4);
			if(!rs.next()) {
				System.out.println("검색결과 X");
			}else {
				do {
					System.out.print(rs.getInt(1)+"\t");
					System.out.print(rs.getString(2)+"\t");
					System.out.print(rs.getString(3)+"\t");
					System.out.print(rs.getInt(4)+"\t");
					System.out.print(rs.getString(5)+"\t");
					System.out.print(rs.getInt(6)+"\t");
					System.out.print(rs.getInt(7)+"\t");
					System.out.print(rs.getInt(8)+"\t");
					System.out.println("");
				}while(rs.next());
				}
			
			
					

//////////////5.모든 사원정보를 출력하되 부서정보를 함께 출력하는 프로그램을 작성해보자.
			System.out.println("");
			System.out.println("-------------------- 문제 5번) 사원정보 + 부서정보 함께 출력");
			System.out.println("");
			String sqlList2="select * from emp, dept where emp.deptno=dept.deptno";
			pstmt=conn.prepareStatement(sqlList2);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				System.out.print(rs.getInt(1)+"\t");
				System.out.print(rs.getString(2)+"\t");
				System.out.print(rs.getString(3)+"\t");
				System.out.print(rs.getInt(4)+"\t");
				System.out.print(rs.getString(5)+"\t");
				System.out.print(rs.getInt(6)+"\t");
				System.out.print(rs.getInt(7)+"\t");
				System.out.print(rs.getInt(8)+"\t");
				System.out.print(rs.getInt(9)+"\t");
				System.out.print(rs.getString(10)+"\t");
				System.out.print(rs.getString(11)+"\t");
				System.out.println("");
				
			}
			
			
		} catch (ClassNotFoundException e) {
	         System.out.println("Driver 로드 실패");
	      } catch (SQLException e) {
	         e.printStackTrace();
	      }
		
		
	}

}

STATEMENT

package jdbc;

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

public class JDBCOracleStatementTest {

	public static void main(String[] args) {
		Scanner sc= new Scanner(System.in);
		Connection conn = null;
		
		try {
			// 1. 드라이버 로드
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("Oracle Driver Load !!!");
			
			// 2. DB 연결     localhost == 127.0.0.1
			String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
			String user = "scott";
			String password = "tiger";
			
			conn = DriverManager.getConnection(jdbcUrl, user, password);
			System.out.println("데이터베이스에 접속했습니다.");
			
			// 3. statement 인스턴스생성
			
			Statement stmt = conn.createStatement();
			
			System.out.println("부서 이름을 입력해주세요.");
			String userDname= sc.nextLine();
			System.out.println("부서의 위치를 입력해주세요.");
			String userLoc = sc.nextLine();
			
			
			// 입력 : insert
			
			String sqlInsert = "insert into dept values (SEQ_DEPT_DEPTNO.NEXTVAL,'"+userDname+"','"+userLoc+"')";
				
			
			int resultCnt = stmt.executeUpdate(sqlInsert); // sql문을 실행하고 횟수반환
			if(resultCnt>0) {
				System.out.println("정상적인 입력");
			}
			
			// 4. sql 실행 : 부서리스트 출력
			String sql="select * from dept order by deptno";
			ResultSet rs = stmt.executeQuery(sql);
			
			// 5. ResultSet을 이용해서 결과 출력
			while(rs.next()) { 			// rs.next(); // boolean Type
				int deptno = rs.getInt("deptno");
				String dname = rs.getString("dname");
				String loc = rs.getString(3);
				System.out.println(deptno+", "+ dname+ ", "+loc);
				
			}
			
			rs.close();
			stmt.close();
			conn.close();		
			
		} catch (ClassNotFoundException e) {
			System.out.println("Driver 로드 실패");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		 
		
		
		
	}

}

PREPAREDSTATEMENT

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCOraclePreparedTest {

	public static void main(String[] args) {
		Scanner sc= new Scanner(System.in);
		Connection conn = null;
		
		try {
			// 1. 드라이버 로드
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("Oracle Driver Load !!!");
			
			// 2. DB 연결     localhost == 127.0.0.1
			String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:orcl";
			String user = "scott";
			String password = "tiger";
			
			conn = DriverManager.getConnection(jdbcUrl, user, password);
			System.out.println("데이터베이스에 접속했습니다.");
			
		
			//Statement stmt = conn.createStatement();
			
			System.out.println("부서 이름을 입력해주세요.");
			String userDname= sc.nextLine();
			System.out.println("부서의 위치를 입력해주세요.");
			String userLoc = sc.nextLine();
			
			
			// PreparedStatement 인스턴스 생성
			String sqlInsert = "insert into dept values (Seq_dept_deptno.nextval, ?, ?)";
			
			PreparedStatement pstmt = conn.prepareStatement(sqlInsert);
			pstmt.setString(1, userDname);
			pstmt.setString(2, userLoc);
			
			int resultCnt = pstmt.executeUpdate();
			
			if(resultCnt>0) {
				System.out.println("정상적으로 입력되었음.");
			}else {
				System.out.println("데이터 입력이 되지 않았음.");
			}
			
			// 부서 리스트 출력
			String sqlList ="select * from dept order by loc";
			pstmt = conn.prepareStatement(sqlList);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()) {
				System.out.print(rs.getInt(1)+", ");
				System.out.print(rs.getString(2)+", ");
				System.out.print(rs.getString(3)+"\n");
			}
			
			rs.close();
			pstmt.close();
			conn.close();
			
		} catch (ClassNotFoundException e) {
			System.out.println("Driver 로드 실패");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		 
		
		
		
	}

}

PREPAREDSTATEMENT 2) 검색

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class JDBCOraclePreparedTest2 {

   public static void main(String[] args) {

      Scanner sc = new Scanner(System.in);
      
      Connection conn = null;
      
      try {
         // 1. 드라이버 로드
         Class.forName("oracle.jdbc.driver.OracleDriver");
         System.out.println("Oracle Driver Load!");
         
         // 2. DB 연결       localhost == 127.0.0.1
         String jdbcUrl ="jdbc:oracle:thin:@localhost:1521:orcl";
         String user = "scott";
         String password = "tiger";
         
         conn = DriverManager.getConnection(jdbcUrl, user, password);
         System.out.println("데이터베이스에 접속했습니다.");
         
         
         // 3. statement 인스턴스생성
         PreparedStatement pstmt = null;

         
         System.out.println("검색하고 싶은 부서 이름을 입력하세요.");
         String searchDname = sc.nextLine();
         
         // 부서 정보 리스트 
         String sqlSelect = "select * from dept where dname=? order by loc";
         
         pstmt = conn.prepareStatement(sqlSelect);
         pstmt.setString(1, searchDname);
         
         ResultSet rs = pstmt.executeQuery();
         
         if(!rs.next()) {
            System.out.println("검색 결과 X");
         } else {
            do  {
               System.out.print(rs.getInt(1)+"\t");
               System.out.print(rs.getNString(2)+"\t");
               System.out.print(rs.getNString(3)+"\n");
            } while(rs.next());
         }
         
         
         rs.close();
         pstmt.close();
         conn.close();
         
         
         
      } catch (ClassNotFoundException e) {
         System.out.println("Driver 로드 실패");
      } catch (SQLException e) {
         e.printStackTrace();
      }
      
      
      
      
      
   }

}

1. cmd 들어가기

2. sqlplus

3. (기본이 SYSTEM) (기본 설정암호)

​

4. create user 'user이름' identified by 'user비밀번호'; //유저 생성

5. alter user 'user이름' account unlock; //만들었을 때 user account에 lock이 걸려있을지 모르므로 unlock시켜줌

6. grant resource, connect to 'user이름'

grant dba to 'user이름' //권한 설정

이렇게만 하면 db에 user를 만들 수 있음

// 계정생성
create user [USERNAME] identified by [PASSWORD];
// 계정비밀번호 변경
ALTER USER [USERNAME] IDENTIFIED BY [NEW PASSWORD];

// 권한할당

GRANT CREATE 권한명 TO 계정;
----------------------------------------------------
시스템 권한 종류
create user : 데이터 베이스 유저 생성 권한
select any table : 모든 유저의 테이블 조회 권한
create any table : 모든 유저의 테이블 생성 권한
create session : 테이터베이스 접속 권한
create table : 테이블 생성 권한
create view : 뷰 생성 권한
create proced user : 프로시저 생성 권한
create sequence : 시퀀스 생성 권한
sysdba : 테이버베이스를 관리하는 최고 권한
sysoper : 테이터베이스를 관리하는 권한
----------------------------------------------------

// 권한제거
revoke 권한명 (시스템 명령 또는 SQL 명령) ON 테이블명 FROM 계정;

// 계정삭제
DROP USER 계정명 CASCADE;

// 현재연결된계정보기
show user

사용자명 입력: system
비밀번호 입력:

다음에 접속됨:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user [hyoseon] identified by [1234];
create user [hyoseon] identified by [1234]
            *
1행에 오류:
ORA-01935: 누락된 사용자 혹은 롤 이름


SQL> create user hyoseon identified by 1234;

사용자가 생성되었습니다.

SQL> grant create session to hyoseon
  2  ;

grant create session to 유저이름;

select * from ALL_users;    // 모든 계정에 대한 정보 확인
select * from DBA_users;    // 모든 계정에 대한 정보 확인
select username from ALL_users;    // 모든 계정에 대한 이름 확인
select username from DBA_users;    // 모든 계정에 대한 이름 확인

사용자 계정을 만들어서 사용할 때 기본 세팅 명령어

-- 사용자 계정 만들기
CREATE user 계정명 IDENTIFIED by 비밀번호;
-- 권한 부여
grant create session, create table, create sequence, create view to 계정명;
-- 사용자 계정에 테이블 공간 설정
alter user 계정명 default tablespace users;
-- 테이블 공간에 쿼터 할당
alter user 계정명 quota unlimited on users;

1. 시퀀스의 현재 값을 확인

SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TB_ZZTRACE_SQ01';

2. 시퀀스의 INCREMENT 를 현재 값만큼 빼도록 설정 (아래는 현재값이 999999 일 경우)

ALTER SEQUENCE TB_ZZTRACE_SQ01 INCREMENT BY -999999;

3. 시퀀스에서 다음 값을 가져 온다

SELECT TB_ZZTRACE_SQ01.NEXTVAL FROM DUAL;

4. 현재 값을 확인 해보면 -999999 만큼 증가 했다

SELECT TB_ZZTRACE_SQ01.CURRVAL FROM DUAL;

5. 시퀀스의 INCREMENT 를 1로 설정 한다

ALTER SEQUENCE TB_ZZTRACE_SQ01 INCREMENT BY 1;

6. 시퀀스가 1부터 다시 시작 한다.

create table phoneInfo_basic (
        idx             number(6)       primary key,
        fr_name         varchar2(20)    not null,
        fr_phonenumber  varchar2(20)    not null,
        fr_email        varchar2(20)    default '-',
        fr_address      varchar2(20)    default '-',
        fr_regdate      DATE            default sysdate
);

create table phoneInfo_univ (
        idx             number(6)       primary key,
        fr_u_major      varchar2(20)    default 'N',
        fr_u_year       number(1)       check (5>=fr_u_year AND fr_u_year>=1),
        fr_ref          number(7)       references phoneinfo_basic(idx)
);


create table phoneinfo_com(
        idx             number(6)       primary key,
        fr_c_company    varchar2(20)    default 'N',
        fr_ref          number(6)       references phoneinfo_basic(idx)
);



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

create table fr_type(
    friendty      varchar2(5)    unique
);

insert into fr_type values ('univ');
insert into fr_type values ('com');
insert into fr_type values ('cafe');


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

create table phonebook(
    pidx            number(6)        primary key,
    friendtype      varchar2(5)      references fr_type(friendty)
);

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

인덱스의 장점

- 검색 속도가 빨라진다.
- 시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킨다.

인덱스의 단점
- 인덱스를 위한 추가적인 공간이 필요하다.
- 인덱스를 생성하는데 시간이 걸린다.
- 데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우에는 오히려 성능이 저하된다.

Primary Key / unique

기본 키나 유일 키는 데이터 무결성을 확인하기 위해하기 위해서 수시로 데이터를 검색하기 때문에

빠른 조회를 목적으로 오라클에서 내부적으로 해당 컬럼에 인덱스를 자동으로 생성한다.

-- index : 검색을 빠르게 하기 위한 객체
-- create index index_name on targer_table_name (column_name)

-- index 확인을 하는 user_ind_columns
desc user_ind_columns;

select index_name, table_name, column_name
from user_ind_columns;
-- primary key 또는 unieq 속성은 자동으로 index가 생성이 된다.



insert into emp10 select * from emp10;
-- emp10 테이블에 500만개의 행을 만들어준 상태임
insert into emp10 (empno, ename) values (1111, 'COOL');

select empno, ename from emp10 where ename='COOL';  -- 2.765초

-- ename을 인덱스로 정의
create index index_emp10_ename
on emp10 (ename);
select empno, ename from emp10 where ename='COOL';  -- 1.26초로 단축

-- 인덱스 삭제
-- DROP INDEX index_name;

인덱스 확인방법

: 인덱스 확인

SELECT * FROM USER_INDEXES

WHERE TABLE_NAME='테이블명 ' ;

: 키가 되는 컬럼 알아보기

ELECT * FROM USER_IND_COLUMNS

WHERE TABLE_NAME='테이블명 ' ;

: 해당 인덱스의 컬럼만 알아보기

SELECT * FROM USER_IND_COLUMNS

WHERE INDEX_NAME='인덱스명';

create index index_test01 on test01(ename);
insert into test01 select * from test01;

--인덱스 확인 방법
SELECT * FROM USER_INDEXES
WHERE TABLE_NAME='TEST01';

-- 그 키가 되는 컬럼을 알아보는 방법
SELECT * FROM USER_IND_COLUMNS
WHERE TABLE_NAME='TEST01';

-- 해당 인덱스의 컬럼만 보고싶다면
SELECT * FROM USER_IND_COLUMNS
WHERE INDEX_NAME='INDEX_TEST01';

-- 내가 조회할땐 어쩐지 대문자만됐다;

출처 : coding-factory.tistory.com/419

인덱스(Index)란?

인덱스는 데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이며 일종의 색인기술입니다. 테이블에 index를 생성하게 되면 index Table을 생성해 관리합니다. 인덱스는 테이블에 있는 하나이상의 컬럼으로 만들 수 있습니다. 가장 일반적인 B-tree 인덱스는 인덱스 키(인덱스로 만들 테이블의 컬럼 값)와 이 키에 해당하는 컬럼 값을 가진 테이블의 로우가 저장된 주소 값으로 구성됩니다.인덱스(Index) 사용 예시 

인덱스 생성

--문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)

--예제
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

--예제 컬럼 중복 X
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

위와같이 쿼리문을 작성하면 INDEX를 생성할 수 있습니다. UNIQUE 키워드를 붙이면 컬럼값에 중복값을 허용하지 않는다는 뜻입니다.

인덱스 조회

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';

인덱스를 생성하면 USER_INDEXES 시스템 뷰에서 조회할 수 있습니다.

방금 CUSTOMERS 테이블에 만들었던 EX_INDEX가 첫번째 ROW에 있군요.

인덱스 삭제

--문법
DROP INDEX [인덱스 명]
--예제
DROP INDEX EX_INDEX;

인덱스는 조회성능을 극대화하기 위해 만든 객체인데 너무 많이 만들면 insert, delete, update시에 부하가 발생해 전체적인 데이터베이스 성능을 저하합니다. 고로 안쓰는 인덱스는 삭제시키는것이 좋습니다.

▶인덱스(Index) 리빌드(Rebuild)사용 예시 

인덱스를 리빌드하는 이유

인덱스 파일은 생성 후 insert, update, delete등을 반복하다보면 성능이 저하됩니다. 생성된 인덱스는 트리구조를 가집니다. 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어집니다. 이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는것이 좋습니다.

인덱스 리빌드 할 대상 조회쿼리

SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
       DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF
FROM   USER_INDEXES I
WHERE   I.BLEVEL > 4
ORDER BY I.BLEVEL DESC

해당쿼리는 index 트리의 깊이가 4이상인 index를 조회하는 쿼리입니다. 해당 쿼리문을 실행하여 검색되는 index가 있다면 리빌딩을 하는것이 좋습니다. 

인덱스 리빌드

--문법
ALTER INDEX [인덱스명] REBUILD;
--예제
ALTER INDEX EX_INDEX REBUILD;

위의 쿼리를 실행시키면 인덱스가 리빌드 됩니다. 이렇게 일일히 리빌드 시키기 귀찮으신 분들은 USER_INDEXES에 있는 인덱스를 조회하여 인덱스 리빌드 쿼리를 만들어 한번에 실행시키시면 간편합니다.

전체 인덱스 리빌드 쿼리문 만들기

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; 'FROM USER_INDEXES;

인덱스(Index)를 남발하지 말아야 하는 이유

개발을 진행할때에 대개 개발서버와 운영서버를 나누어서 관리합니다. 대부분 개발서버에서 개발을 할때에는 적은량의 데이터를 가지고 로직검사를 하며 로직검사에 통과한 코드들이 운영서버에 업데이트가 되죠. 하지만 개발서버에는 잘 동작하던 로직들이 운영서버의 많은량의 데이터들을 처리하다보면 성능이슈가 많이 발생합니다. 그 성능이슈의 주요원인은 바로 데이터베이스에 있습니다. 데이터베이스 관리자는 성능문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성입니다. 그렇게 하여 인덱스를 하나 만들었다고 합시다. 그리고 또 다른 SQL에서문에서 성능이슈가 발생하여 또 인덱스를 만들었다고 합시다. 이렇게 문제가 발생할때마다 인덱스를 생성하면서 인덱스가 쌓여가는것은 결코 좋지않습니다. 인덱스를 만드는것은 하나의 쿼리문을 빠르게는 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래합니다. 그렇기에 인덱스를 생성하는것 보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야합니다. 인덱스생성은 꼭 마지막 수단으로 강구해야 할 문제입니다.

시퀀스는 테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기이므로

시퀀스를 기본 키로 사용하게 되면 사용자의 부담을 줄일 수 있다.

-- Sequence : 숫자 자동 생성기
-- 시작값, 증가값, 최대값, 최소값, 반환 여부.

-- dept 테이블의 deptno가 pk로 되어있다. [deptno = 10, 20, 30..]
-- detpno에 사용할 시퀀스를 생성해보자.
------------ 시퀀스 삭제 : drop sequence 시퀀스이름
drop sequence seq_dept_deptno;

-- 시퀀스 생성
create sequence seq_dept_deptno
MINVALUE 10 -- 최소값 10
MAXVALUE 90
start with 10 -- 10부터 시작
INCREMENT BY 10 -- 10씩 증가
;

-- emp 테이블에 사용할 수 있는 시퀀스 만들어보기
create sequence sqe_emp_empno
MINVALUE 0
start with 0
increment by 1

CURRVAL / NEXTVAL

-- sequence 객체로 숫자를 생성, 현재 숫자를 읽어오는 명령
-- 숫자생성 : nextval > 새로운 숫자를 생성하고 숫자를 반환
-- currval: 현재 숫자 반환

-- 현재 숫자를 확인
select seq_dept_deptno.nextval
from dual;


-- 현재 숫자
select seq_dept_deptno.currval
from dual;


insert into dept01 values (seq_dept_deptno.nextval , 'test', 'test');
-- 계속하면 (90이넘으면 / 현재 maxvalue 90임)
-- ORA-08004: 시퀀스 SEQ_DEPT_DEPTNO.NEXTVAL exceeds MAXVALUE은 사례로 될 수 없습니다.
select * from dept01;

- 뷰의 개념

* 뷰(View)는 한마디로 물리적인 테이블을 근거한 논리적인 가상 테이블이라고 정의할 수 있습니다. 
* 가상이란 단어는 실질적으로 데이터를 저장하고 있지 않는다.

* 사용자는 마치 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있다.
* 뷰는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문입니다.
* 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 합니다.
* 사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용하게 됩니다.

* 뷰는 데이터를 물리적으로 저장하고 있지 않는다.

- CREATE OR RELPACE VIEW

그냥 CREATE VIEW를 통해 만들어진 뷰의 구조를 바꾸려면 뷰를 삭제하고 다시 만들어야 되는 반면,

CREATE OR REPLACE VIEW는 새로운 뷰를 만들 수 있을 뿐만 아니라,

기존에 뷰가 존재하더라도 삭제하지 않고 새로운 구조의 뷰로 변경(REPLACE)할 수 있다.
그래서 대부분 뷰를 만들 때는 CREATE VIEW 대신 CREATE OR REPLACE VIEW를 사용한다.

View 테이블에 insert를 사용해서 추가를 하면,

기존 테이블에도 추가가 된다. 자바로 생각하면 부모테이블(?) 에도 추가가 된다.

create table test01
as
select * from emp;


create or REPLACE view view_test01
as 
select empno, ename, job
from test01;

select * from view_test01;

insert into view_test01 VALUES (1111, 'PPO', 'SEON');
select * from view_test01;
select * from test01;

-- 전 사원에 대한 정보 출력 (사원정보, 부서정보)
select *
from emp, dept
where emp.deptno=dept.deptno
;

-- deptno가 두개라서 하나만 보이게 하려고 다써줌 ㅠ
-- 두개 테이블을 join한 것은 insert가 불가능
create or replace view view_emp_dept
as
select empno, ename, job, mgr, hiredate, sal, comm, emp.deptno, dname, loc
from emp, dept where emp.deptno=dept.deptno;

select * from view_emp_dept;
select * from view_emp_dept where deptno=10;



-- view 정보 확인 : user_views 테이블을 통해 확인이 가능
-- 로그인한 사용자의 view 인스턴스의 정보
select * from user_views;
select view_name, text from user_views;


-- view_emp30을 통해 insert
insert into view_emp30 values (9999, 'tester', 40);

desc emp;
select * from emp;
rollback;

-- view의 삭제 : 객체를 삭제하는 방식과 동일
-- drop

drop view view_emp_dept;

ROWNUM / 로우넘

-- view_hire : 입사일 기준으로 오름차순으로 정렬된 결과를 가상테이블로 정의

select empno, ename, hiredate from emp order by hiredate;

create or replace view view_hiredate
as
select empno, ename, hiredate from emp order by hiredate
;

select rownum, empno, ename, hiredate from view_hiredate;

select * from view_hiredate where rownum<=5;


-- 인라인 뷰를 이용해서 가장 최근에 입사한 사원 5명을 추출
select *
from (select * from emp order by hiredate desc)
where rownum<=5
;

select * from emp order by hiredate desc

- COMMIT 명령어

Transaction(INSERT, UPDATE, DELETE) 작업 내용을 실제 DB에 저장합니다.
이전 데이터가 완전히 UPDATE 됩니다.
모든 사용자가 변경된 데이터의 결과를 볼 수 있습니다.

- ROLLBACK 명령어

Transaction(INSERT, UPDATE, DELETE) 작업 내용을 취소합니다.
이전 COMMIT한 곳 까지만 복구합니다.

-- 트랜잭션(Transaction)
-- 여러개의 sql을 하나의 단위로 처리하는 것
-- 트랜잭션이 가지는 모든 작업이 모두 정상처리 되어야 트랜잭션이 완료되었다고 하는것

truncate table emp10;
insert into emp10
select * from emp;
commit;
 
select * from emp10;
select * from dept01;

-- 부서 테이블에 데이터 하나를 저장 - 정상
-- 사원 테이블에 새로운 사원을 입력 - 오류

insert into dept01 values (50, 'RD', 'SEOUL');
insert into emp10(empno, ename, job, sal) values ('ten', 'TEN', 'MANAGER', 2500);
rollback; -- 오류가 발생해서 초기화 (마지막저장단계 commit단계로 이동)

-- 새로운 트랜잭션 시작
insert into dept01 values (50, 'RD', 'SEOUL');
insert into emp10(empno, ename, job, sal) values (7777, 'TEN', 'MANAGER', 2500);
select * from emp10;
select * from dept01;
commit;

-- 새로운 트랜잭션 시작
update emp10
set mgr=(select empno from emp10 where ename='KING');
-- 잘못처리된 작업이라고 하면, rollback;
rollback;

- COMMIT 명령어과 ROLLBACK 명령어의 장점

데이터 무결성이 보장됩니다.
영구적인 변경 전에 데이터의 변경 사항을 확인할 수 있습니다.
논리적으로 연관된 작업을 그룹화할 수 있습니다.

- 자동 커밋
- DDL 문에는 CREATE, ALTER, DROP, RENAME, TRUNCATE 등이 있습니다.
- 이러한 DDL문은 자동으로 커밋(AUTO COMMIT)이 발생됩니다.

# Chapter03_03.py
# 리스트
# 자료구조에서 중요

# 리스트 자료형 (순서, 중복, 삭제 가능)



### 선언
a = []
b = list()
c = [ 70, 75, 80, 75] # print(len(c))=4
d = [1000, 10000, 'Ace', 'Base', 'Captine'] # 서로 다른 자료형도 가능
e = [1000, 10000, ['Ace', 'Base', 'Captine']] # 리스트 in 리스트
f = [21.42, 'foobar', 3, 4, False, 3.14159]



# 인덱싱
print('>>>>>>>>>')
print(' d - ', type(d), d)
print(' d - ', d[1])
print(' d - ', d[0] + d[1] + d[1])
print(' d - ', d[-1]) # 맨 오른쪽
print(' e - ', e[-1][1])
print(' e - ', list(e[-1][1])) # 문자열을 리스트 형태로 형변환 가능


# 슬라이싱
print('>>>>>>>>>')
print('d - ', d[0:3])
print('d - ', d[2:])
print('e - ', e[-1][1:3])


# 리스트 연산 (리스트+리스트=리스트)
print('>>>>>>>>>')
print('c + d =', c+d)
print('c * 3 =', c*3) #리스트*정수= 리스트, 리스트연산의 결과 = 리스트
# print("'Test' + c[0] = ", 'test'+c[0]) #type error
print("'Test' + c[0] = ", 'test'+str(c[0]))


# 값 비교
print('>>>>>>>>>')
print(c == c[:3] + c[3:])
print(c)
print(c[:3])
print(c[3:])

# 같은 identity(id)
print('>>>>>>>>>')
temp = c
print(temp, c)
print(id(temp))
print(id(c))    # 파이썬이 속도와 쾌적한 환경을 제공하기 위해 리스트도 같은값 = 같은id

# 리스트 수정, 삭제
print('>>>>>>>>>')
c[0] = 4
print('c - ', c)
print(c[1:2])  #c[1]
c[1:2] = ['a', 'b', 'c']
print('c - ', c)
c[1:2] = [['a', 'b', 'c']] # = c[1]=['a', 'b', 'c']
print('c - ', c)
c[1:3] = []
print('c - ', c)

# 제거/삭제
print('>>>>>>>>>')
del c[2]
print('c - ', c)


# 리스트 함수
print('>>>>>>>>>')
a = [5, 2, 3, 1, 4]
print('a - ', a)
#a[5] = 10 에러, 추가시엔 append 함수!
a.append(10)
print('a - ', a)
a.sort() # 파이썬 리스트 오름차순 정렬
print('a - ', a)
a.reverse()  #역순으로 정렬 (내림차순 X)
print('a - ', a)
print('a - ', a.index(4), a[4]) # 인덱스로 가져오기
a.insert(2,7) #0,1,2번째(숫자론세번째)  자리에 7을 넣고 나머지는 뒤로 민다 (삽입)
print('a - ', a)
a.remove(1) #del a[6]와 같다. 인덱스 제거가 아닌 값으로 제거.
print('a - ', a)
print('a - ', a.pop())
#기존의 리스트에서 마지막에 있던 값을 꺼내오고 그 값을 삭제
# LAST IN FIRST OUT
# 접시를 쌓아놓고 맨 위에 쌓인(마지막에 놓은) 것을 먼저 쓰는 것을 생각하면 이해가 쉽다.
# ex) 웹 브라우저에서 뒤로가기를 누르면 바로 전 페이지로 가는 것과 같다
print('a - ', a)
print('a - ', a.count(4)) # 리스트 안에 값 '4'가 몇개인지, 있는지 없는지 확인할때 (=값 0)


ex=[8,9]
a.extend(ex) #뒤에 다른 리스트를 붙여줌
print('a - ', a)

#### 삭제 : remove, del, pop

# 반복문을 이용한 pop

while  a:
    data =a.pop()
    print(data)

-- dept01 테이블 생성 (구조만가져올것)
drop table dept01;
create table dept01
as
select * from dept where 1=2
;
desc dept01;
select * from dept01;

insert into dept01 (deptno, dname, loc) values (10, '마케팅', '서울');
insert into dept01 values (40, '디자인', '보스턴');

-- null값의 입력
insert into dept01 (deptno, dname) values (30, 'QC');
insert into dept01 values (30, 'QC', null);

-- 서브쿼리를 이용해서 데이터 입력
drop table dept02;
create table dept02 as select * from dept where 1=2;

select * from dept02;

insert into dept02
select * from dept;

-- 데이터의 수정
-- update 테이블이름 set 칼럼이름 = value, ..., where 행을 찾기 위한 조건

-- 1. 모든 사원의 부서를 30번으로 수정하자.
drop table emp10;
create table emp10 as select * from emp;
update emp10 set deptno=30;
select * from emp10;

-- 모든 사원의 급여를 10% 인상시키는 업데이트문
update emp10
set sal=sal*1.1;

-- 모든 사원이 입사일을 오늘로 수정
update emp10
set hiredate=sysdate;

-- 급여가 3000 이상인 사원만 급여를 10프로 인상
update emp10
set sal=sal*1.1
where sal>=3000;

-- 1987년에 입사한 사원의 입사일을 오늘로 수정

update emp10
set hiredate=sysdate
where substr(hiredate,1,2)=87;

-- 전체 데이터를 삭제하고 다시 emp넣기 리셋 [하도 이거저거해서 리셋]
truncate table emp10;
insert into emp10
select * from emp;


-- SCOTT 사원의 부서는 20번으로 직급은 manager로 수정
update emp10
set deptno=20, job='MANAGER'
where ename='SCOTT';

-- SCOTT 사원의 입사일자는 오늘로,
-- 급여를 50 으로 커미션을 4000 으로 수정합시다.

update emp10
set hiredate=sysdate, comm=4000, sal=50
where ename='SCOTT';

--- 서브쿼리를 이용한 업데이트
-- 10번 부서의 지역명을 40번 부서의 지역명으로 변경
update dept01
set loc=(select loc from dept01 where deptno=40)
where dept=10;

update dept01
--set dname=(select dname from dept01 where deptno=40),
--    loc=(select loc from dept01 where deptno=40)
set (dname, loc) = (select dname, loc from dept01 where deptno=40)
where deptno=20;


--dept01 테이블의 모든 데이터(데이터만)를 삭제
select * from dept01;
delete from dept01;

-- emp10
select * from emp10;

-- emp10 테이블의 데이터중 부서번호가 10번인 부서의 사원들을 삭제
delete from emp10
where deptno=10;

-- 부서명이 sales인 사원을 모두 삭제해봅시다.
delete from emp10
where deptno=(select deptno from dept where dname='SALES');

--dept01 테이블의 모든 데이터(데이터만)를 삭제
select * from dept01;
delete from dept01;

-- 무결성제약 조건에 위배됩니다
desc emp01;
insert into emp01 values(null, null, 1000, 'tester');
select * from emp01;

-- emp01 삭제
drop table emp01;


-- 필수 입력을 위한 제약 조건 :NOT null 
-- emp01 테이블 생성 : empno, ename에 null값이 들어가지 않도록 제약
create table emp01(
        empno   number(4)       not null,
        ename   varchar2(10)    not null,
        job     varchar(9),
        deptno  number(2)
        );
insert into emp01 values(null, null, 'tester', '10');
-- ORA-01400: NULL을 ("SCOTT"."EMP01"."EMPNO") 안에 삽입할 수 없습니다

-- 데이터의 중복 금지 : UNIQUE
drop table emp02;
create table emp02(
    empno  number(4)    unique,
    ename  varchar2(9),
    deptno number(2)
);
insert into emp02 values(1, 'tester', '10');
insert into emp02 values(1, 'tester2', '10');
-- ORA-00001: 무결성 제약 조건(SCOTT.SYS_C0011060)에 위배됩니다.

-- 기본키 제약 : 기본키 설정 > not null, unique
drop table emp04;
create table emp04(
        empno number(4) primary key,
        ename varchar2(10) not null
    );
    
insert into emp04 values(null, 'test'); -- null 에러
insert into emp04 values(1, 'test');
insert into emp04 values(1, 'test'); -- 무결성제약조건 에러

-- 외래키 제약 : 참조하는 테이블과 컬럼을 정의 (reference's')
drop table emp05;
create table emp05(
    empno number(4) primary key,
    ename varchar2(10) not null,
    deptno number(2) references dept(deptno)
);
-- emp05의 deptno테이블에는 emp테이블의 dept에 있는 값만 들어갈 수있다
-- 즉 10,20,30만 들어갈 수 있다. (40,50 X)

insert into emp05 values(1, 'test', 10);
insert into emp05 values(1, 'test', 40); -- 40X 무결성 에러

-- check : 특정 범위 제한 
create table emp06 (
    empno   number(4)       primary key,
    ename   varchar2(10)    not null,
    sal     number(7,2)     check (sal>=800)
);

insert into emp06 values (1, 'test', 1000);
insert into emp06 values (2, 'test', 100); -- 체크 제약조건 위배

-- default :  insert 시에 자동으로 등록되는 데이터 정의
drop table emp07;
create table emp07(
    empno       number(4)       primary key,
    ename       varchar2(10)    not null,
    sal         number(7,2)     check (sal>=500),
    comm        number(7,2)     default 0, -- number와 타입은 맞춰줘야한다.
    hiredate    date            default sysdate
);

insert into emp07 (empno,ename,sal) values (1,'test',1000);
select * from emp07;

/*
    EMPNO ENAME             SAL       COMM HIREDATE
---------- ---------- ---------- ---------- --------
         1 test             1000          0 20/11/12
*/

두 개의 제약조건 (not null과 unique는 primary key로 사용가능하지만 예시로 만듦)

-- empno가 not null과 unique 제약을 동시에 적용
drop table emp03;
create table emp03(
    empno   number(4)   not null unique,
    ename  varchar2(9) not null
);

insert into emp03 values(null, 'test1');
insert into emp03 values(1, 'test1');
insert into emp03 values(2, 'test1');
select * from emp03;

-- 제약조건에 이름 부여
drop table emp08;
create table emp08(
    empno       number(4)    constraint emp08_empno_pk   primary key,
    ename       varchar2(10) constraint emp08_ename_nn   not null,
    sal         number(7,2)  constraint emp08_sal_CK500  check (sal>=500),
    comm        number(7,2)     default 0, -- number와 타입은 맞춰줘야한다.
    hiredate    date            default sysdate
);

insert into emp08 (empno,ename,sal) values (1,'test',1000);

-- 테이블 레벨에서 제약조건 정의
drop table emp09;
create table emp09(
    empno number(4),  -- not null은 컬럼레벨에서 해야함
    ename varchar2(10) not null,
    job  varchar2(9),
    deptno number(10), -- 컬럼정의끝
    constraint emp09_empno_pk primary key (empno),
    constraint emp09_job_uk unique(job),
    constraint emp09_depno_fk foreign key (deptno) references dept(deptno)
    );

insert into emp09 values (null, null, 'job1', 50);
insert into emp09 values (1, null, 'job1', 40);
insert into emp09 values (1, 'tester', 'job1', 40);
insert into emp09 values (1, 'tester', 'job1', 40);

출처 : http://302.pe.kr/379

Oracle SQL Developer

- Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Database. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle.

- http://www.oracle.com/technetwork/developer-tools/sql-developer/index.html

♣ 들여쓰기 기본값 변경 하기 (2-->4)

- SQL Developer 의 들여쓰기 기본값은 2.

- 도구>환경설정> 데이터베이스> SQL 포맷터>Oracle 형식 지정>편집>들여쓰기 메뉴에서 값을 변경 할 수 있다.

♣ 들여쓰기에 공백(Space) 대신 탭(Tab) 사용하기

- Preferences -> Database -> SQL Formatter -> Alignment and Indentation -> Use tabulator(도표 작성기 사용) 체크.

♣ Format Code

- 쿼리를 지정 된 포맷에 따라 정렬하기

- 단축키: Ctrl + F7

♣ SQL에디터에서 줄번호 보이게 설정

 - 도구 > 환경설정 > 코드 편집기 > 행 여백 > 행 번호 표시에 체크.

♣ 완성 인사이트 대문자로 설정 하기

- 도구 > 환경설정 > 코드 편집기 > 완성 인사이트 > 입력할 때 대소문자 변경 체크 후 'Upper Case'로 설정.

♣ 유용한 단축키 목록

--------- ALER TABLE
-- 테이블 삭제 : 저장공간을 삭제, 저작되어있는 데이터도 모두 삭제
drop table test_tbl;

-- 모든 로우를 제거하는 truncate : 바로삭제!
create table emp06
as
select * from emp;
select * from emp06;

truncate table emp06;


---------- 테이블 이름변경
-- rename old_name to new_name;
rename emp06 to new_emp;




----------  ALTER TABLE : 테이블 구조의 변경
-- alter table table_name 
-- add (컬럼추가) | modify(컬럼변경) | drop(컬럼삭제) ();

-- 기존 테이블에 속성을 추가 : 각 행의 컬럼 데이터는 null값
-- emp01에  job 컬럼을 추가. (varchar2(10)
desc emp01;

alter table emp01 
add(JOB varchar2(10));
alter table emp01 
add(deptno number(2));


-- 기존 테이블의 컬럼 변경 modify 
-- 새롭게 정의된 컬럼으로 교체하는 것

alter table emp01
modify (deptno number(10));

-- 기존 테이블의 컬럼을 삭제 : 데이터도 사라진다
alter table emp01
drop (deptno);

---------- 테이블 생성 : 
-- create table table_name
-- (
--     column_name domain [constraint] 제약/선택적,
--     column_name domain [constraint],
--     column_name domain [constraint],
--     ...
--   )

create table test_tbl (
        no              number(4),
        user_name       varchar2(10),
        user_id         varchar2(16),
        user_password   varchar2(12),
        reg_date        Timestamp DEFAULT sysdate
    );

desc test_tbl;

-- 사원 테이블과 유사한 구조의 사원번호, 사원이름, 급여 3개의 칼럼으로 구성된
-- emp01 테이블을 생성해 보자.
create table emp01(
        empno number(4),
        ename varchar2(10),
        sal   number(7,2)
    );

---------- create table ~as ~ : 테이블 복사! but 제약조건은 복사되지 않는다.

-- CREATE TABLE 명령어 다음에 컬럼을 일일이 정의하는 대신 
-- AS 절을 추가하여 EMP 테이블과 동일한 내용과 구조를 갖는
-- EMP02 테이블을 생성해 봅시다.
CREATE TABLE EMP02 AS SELECT * FROM EMP;
desc emp02;
desc emp;


--서브 쿼리문의 SELECT 절에 * 대신
--원하는 컬럼명을 명시하면 기존 테이블에서 일부의 컬럼만 복사할 수 있습니다.
create table emp03
as
select empno, ename, sal from emp
;

desc emp03;

--서브 쿼리문의 SELECT 문을 구성할 때
-- WHERE 절을 추가하여 원하는 조건을 제시하면 기존 테이블에서 일부의 행만 복사합니다.

create table emp04
as
select * from emp where deptno=10
;
select * from emp04;


-- 테이블의 구조만 복사하는 것은 별도의 명령이 있는 것이 아닙니다. 
--이 역시 서브 쿼리를 이용해야 하는데 WHERE 조건 절에 항상 거짓이 되는 조건을 지정하게 되면 
--테이블에서 얻어질 수 있는 로우가 없게 되므로 빈 테이블이 생성되게 됩니다

create table emp05
as
select * from emp where 1=2
;
select * from emp05;

-- 부속질의

-- 스칼라 부속질의 / select 부속질의,
-- 단일행, 단일열, 단일값의 결과가 나와야한다.

select name from customer where custid=1;

select custid, (select name from customer where customer.custid=orders.custid),
        saleprice, orderdate
from orders
;


--마당서점의 고객별 group by
-- 판매액을 보이시오 orders
-- (결과는 고객이름과 고객별 판매액을 출력).

select c.custid, c.name, sum(o.saleprice)
from customer c, orders o
where c.custid=o.custid(+)
group by c.custid, c.name
order by sum(o.saleprice)
;

select o.custid, (
    select name from customer c where o.custid=c.custid),
sum(o.saleprice)
from orders o
group by o.custid
;

-- 인라인 뷰 / from 부속질의

--질의 고객번호가 2 이하인 고객의 판매액을 보이시오 
--(결과는 고객이름과 고객별 판매액 출력)
select custid, name
from customer
where custid<=2
; -- 가상테이블

select cs.name, sum(o.saleprice)
from (select custid, name
from customer
where custid<=2) cs , orders o
where cs.custid=o.custid
group by cs.name
;

-- 중첩질의 – WHERE 부속질의
-- 평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.
select orderid, saleprice
from orders
where saleprice < (select avg(saleprice) from orders)
;

-- 각 고객의 평균 주문금액보다 
-- 큰 금액의 주문 내역에 대해서
-- 주문번호, 고객번호, 금액을 보이시오.
select avg(saleprice) 
from orders
group by custid
;


select o.orderid, o.custid, o.saleprice, c.name
from orders o, customer c
where saleprice >
(select avg(saleprice) 
from orders
o2 where o.custid=o2.custid)
AND (o.custid=c.custid)
;


--대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.

select sum(saleprice)
from orders
where custid in (select custid from customer where address LIKE '%대한민국%')
;

--3번 고객이 주문한 도서의 최고 금액보다 
--더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

select orderid, saleprice
from orders
-- where saleprice > (select max(saleprice) from orders where custid=3);
where saleprice > all(select saleprice from orders where custid=3)
;

-- EXISTS 연산자로 대한민국에 거주하는 고객에
-- 판매한 도서의 총 판매액을 구하시오.  

select * from customer where address LIKE '%대한민국%';

select sum(saleprice)
from orders o
where EXISTS (select * from customer c where address LIKE '%대한민국%' AND o.custid=c.custid)
;

-- 중첩질의 : 단순비교 

--평균 급여를 구하는 쿼리문을 서브 쿼리로 사용하여
--평균 급여보다 더 많은 급여를 받는 사원을 검색

select *
from emp        -- ↓ 평균급여
where sal > (select avg(sal)from emp)    
;

-- SCOTT 사원의 같은 부서에 근무하는 사원의 리스트를 출력
select deptno from emp where ename='SCOTT'; --20

select ename, deptno from emp 
where deptno=(select deptno from emp where ename='SCOTT');

-- sal>3000이상 받는 사원이 소속된 부서
select distinct deptno from emp where sal >=3000;

select *
from emp
--where deptno=10 or deptno=20
where deptno in (select distinct deptno from emp where sal >=3000)
;

--30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다
--더 많은 급여를 받는 사람의 이름, 급여를 출력하는 쿼리문을 작성해 봅시다.
select sal from emp where deptno=30;

select ename, sal from emp 
where sal> all (select sal from emp where deptno=30);
-- 모든게 다 만족해야할 때

--다음은 부서번호가 30번인 사원들의 급여 중
--가장 작은 값(950)보다 많은 급여를 받는 사원의 이름,
--급여를 출력하는 예제를 작성해 봅시다.
select ename, sal from emp 
where sal> any (select sal from emp where deptno=30);

-- ROWNUM #입력순 (로우넘 날짜순X)
select rownum, empno, ename, hiredate
from emp
order by hiredate
;

-- 기준에 맞춰 몇개 뽑아내기
select rownum, empno, ename, hiredate
from emp
where rownum<4
order by hiredate
;

select rownum, empno, ename, hiredate from emp order by hiredate
;

-- ROWNUM 날짜순 O
select rownum, empno, ename, hiredate
from (select rownum, empno, ename, hiredate from emp order by hiredate desc)
where rownum<4 -- 입사일이 제일 최근인 세명 (desc)
;

-- 가장 급여를 많이 받는 사원 3명을 출력하자
select rownum, ename, sal from emp order by sal desc;
select rownum, ename, sal 
from(select rownum, ename, sal from emp order by sal desc)
where rownum<4
;

-- CROSS JOIN 크로스 조인 : 조건 없이 테이블과 테이블을 붙여준다
-- 단순하게 붙여주는 것이라 아무 의미가 없다.
select *
from emp, dept;

-- EQUI JOIN
-- 동일 칼럼을 기준으로 조인합니다.
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

-- SCOTT의 사원번호와 이름, 부서이름, 지역을 출력하자
SELECT empno, ename, dname, loc, dept.deptno -- deptno만 쓰면 오류남 or emp.deptno
-- emp.empno, emp.ename, dept.dname, dept.loc, dept(emp).deptno로 쓰는 것이 사실 맞음
FROM emp, dept
where ename='SCOTT' AND emp.deptno=dept.deptno
;

-- 별칭
-- 테이블의 이름이 긴경우.
SELECT e.empno, e.ename, d.dname, d.loc, d.deptno
FROM emp e, dept d
where ename='SCOTT' AND e.deptno=d.deptno
;

-- NON-EQUI JOIN : 동등비교가 아닌 비교연산이 가능
-- 급여 등급을 5개로 나누어 놓은 salgrade에서 정보를 얻어 와서 
-- 각 사원의 급여 등급을 지정해보도록 합시다. 
-- 이를 위해서 사원(emp) 테이블과 급여 등급(salgrade) 테이블을 조인하도록 합시다.
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
;

-- SELF JOIN : 자신의 테이블을 조인
-- SMITH의 매니저 이름이 무엇인지 알아내려면 어떻게 구해야 할까요?

select ename, mgr
from emp
where ename='SMITH'
;

-- +
select ename, mgr
from emp
where empno=7902
;


-- =

select e.ename, e.mgr, m.ename as MGRNAME
from emp e, emp m
where e.mgr=m.empno AND e.ename='SMITH'
;

-- outer join : 정보가 부족해도 출력하도록 하는 join

select e.ename, e.mgr, m.ename as MGRNAME
from emp e, emp m
where e.mgr=m.empno(+)
;

-- ANSI JOIN : CROSS JOIN
select *
from emp cross join dept
;

-- INNER JOIN / ON : 조인의 조건

--SELECT ename, dname
--FROM emp, dept
--where ename='SCOTT' AND emp.deptno=dept.deptno
--;

SELECT ENAME, DNAME 
FROM EMP INNER JOIN DEPT 
ON EMP.DEPTNO=DEPT.DEPTNO 
WHERE ENAME='SCOTT';

-- USING
select *
from emp join dept
-- on emp.deptno=dept.deptno
using (deptno) 
-- 공통 컬럼은 하나만 가지게 해준다. 공통 컬럼을 구별할 필요가 없다
;

-- NATURAL JOIN : 동일 컬럼 조인의 조건을 생략
-- 같은 이름의 컬럼은 하나씩 존재해야 한다.
select *
from emp NATURAL JOIN dept
;

-- ansi outer join
-- table1 [LEFT | RIGHT | FULL ] outer join table2

select *
from emp e left outer join emp m
on (e.mgr=m.empno)
;

:

-- 회원 구매
-- customer, orders

select * from customer;
select * from orders order by custid;

select *
from customer c left outer join orders o
on (c.custid=o.custid)
;

-- 회원별 구매횟수
select c.name, count(o.orderid), sum(o.saleprice), round(avg(o.saleprice))
from customer c, orders o
where c.custid=o.custid(+)
group by c.name
;

# chapter03_02
# 파이썬 문자형

# 문자열 생성
str1= "I am Python"
str2= 'python'
str3= """How are you?"""
str4= '''Thank you'''

print(type(str1), type(str2), type(str3), type(str4))
print(len(str1)) #문자열의 길이

# 빈문자열
str1_t1="" # or str_t1=''
str2_t2 = str()

print(type(str1_t1), len(str1_t1))
print(type(str2_t2), len(str2_t2)) # 둘다 STR, 길이는 0


# 이스케이프 문자 사용 # 탈출 문자
# I'm Boy >> '
print("I'm Boy")  #print('I'm Boy')
print('I\'m Boy')
print('\' , \\ , \t tap, \n enter')
#등등등, 자바와 같은 느낌, 이스케이프 문자 검색해보기
escape_str1="do you have a \"retro games?\""
print(escape_str1)
escape_str2='what\'s on TV?'
print(escape_str2)
# 탭, 줄바꿈
t_s1= "click \t start!"
t_s2="new Lint \n check!"
print(t_s1)
print(t_s2)
print()
# Raw String -  있는 그대로 표시해준다
raw_s1=r'd:\python\test'
print(raw_s1)
print()

# 멀티라인 입력
# 역슬래시를 사용하면 유용하다
multi_str= \
"""
String
multi Line
test
"""
multi_str2="""
string
multi Line
test2
"""

print(multi_str)
print(multi_str2)
print()


# 문자열 연산 (String에서 in 연산자를 쓸 수 있다는 점을 기억하자)
str_o1= "python"
str_o2= "Apple"
str_o3= "How are you doing"
str_o4= "Seoul Daejeon busan"

print(str_o1 * 3 ) #세번반복되서 출력
print(str_o1 + str_o2)
print('y' in str_o1) # str_o1안에 y가 있어? (True/False) > 시퀀스 형에 사용가능
print('P' not in str_o2) #대문자 소문자 구별함
print()

# 문자열 형변환
print(str(66), type(str(66))) # 문자66을 의미.
print(str(10.1), type(str(10.1)))
print(str(True), type(str(True)))
print()


# 문자열 함수 (upper, iaalnum, startswith, count, endswith, isalpha ... )
print("Capitalize : ", str_o1.capitalize()) #첫글자를 대문자로 바꿔준다.
print("endswith? : ", str_o2.endswith("e")) #마지막 문자가 무엇인지 체크 boolean (ex 마침표)
print("replace : ", str_o1.replace("py","PPYY")) #바꿔줌
print("sorted : ", sorted(str_o1)) # 리스트 형태로 반환
print("split : ", str_o4.split(" ")) # 기준을 정해서 그것을 기준으로 리스트로 반환
print()

# 반복(시퀀스)
im_str= "good boy!"

print(dir(im_str)) #__iter__(반복) #im_str에서 사용하는 모든 것 나열

#출력 (슬라이스 가능)
for i in im_str :  print(i)
print()


# 슬라이싱
str_s1 = "Nice Python"
print(len(str_s1))
# 슬라이싱 연습
print(str_s1[0:3]) # 0 1 2
print(str_s1[:3]) # 처음부터 세개. 위에거랑 같음
print(str_s1[5:11])
print(str_s1[5:]) # 5부터 끝까지, 위에거랑 같음
print(str_s1[:len(str_s1)]) #끝부분의 길이를 모를때는 len이용 #[:11]
print(str_s1[:len(str_s1)-1]) #[:10]
print(str_s1[1:9:2]) # 세번째 인수는 단위, 몇개단위로 출력
print(str_s1[-5:]) #뒤에서는 -1부터 시작한다
print(str_s1[1:-2])
print(str_s1[::2]) #처음부터 끝까지 2칸간격으로
print(str_s1[::-1]) #역으로 출력됨
print()

# 아스키코드(또는 아스키코드)
a ='z'

print(ord(a)) # 아스키코드로,
print(chr(122)) # 문자로,

-- 1. 덧셈연산자를 이용하여 모든 사원에 대해서 $300의 급여 인상을 계산한 후 사원의 이름, 급여, 인상된 급여를 출력하시오.

SELECT ename, sal, sal+300
from emp
;

-- 2. 사원의 이름, 급여, 연간 총 수입을 총 수입이 많은 것부터 작은 순으로 출력하시오,
-- 연간 총수입은 월급에 12를 곱한 후 $100의 상여금을 더해서 계산하시오.

SELECT ename, sal, (sal*12+100) as sal2
FROM emp
order by sal2 asc
;

-- 3. 급여가 2000을 넘는 사원의 이름과 급여를 표현, 급여가 많은 것부터 작은 순으로 출력하시오.

SELECT ename, sal
from emp
where sal>=2000
order by sal desc
;

-- 4. 사원번호가 7788인 사원의 이름과 부서번호를 출력하시오.

SELECT ename, deptno
from emp
where empno=7788
;

-- 5. 급여가 2000에서 3000 사이에 포함되지 않는 사원의 이름과 급여를 출력하시오.

select ename, sal
from emp
where sal not BETWEEN 2000 AND 3000
;

-- 6. 1981년 2월 20일 부터 1981년 5월 1일 사이에 입사한 사원의 이름, 담당업무, 입사일을 출력하시오.
SELECT ename, job, hiredate
from emp
where hiredate between '81/02/20' AND '81/05/01'
;

-- 7. 부서번호가 20 및 30에 속한 사원의 이름과 부서번호를 출력, 이름을 기준(내림차순)으로 영문자순으로 출력하시오.
select ename, deptno
from emp
where deptno=10 OR deptno=20
order by ename desc
;

--8 . 사원의 급여가 2000에서 3000사이에 포함되고 부서번호가 20 또는 30인 사원의 이름,
-- 급여와 부서번호를 출력, 이름순(오름차순)으로 출력하시오.

select ename, sal, deptno
from emp
where sal BETWEEN 2000 AND 3000 AND (deptno=20 OR deptno=30)
order by ename;

-- 9. 1981년도에 입사한 사원의 이름과 입사일을 출력하시오. (like 연산자와 와일드카드 사용)
select ename, hiredate
from emp
where hiredate LIKE '81%';

​-- 10. 관리자가 없는 사원의 이름과 담당 업무를 출력하시오.
SELECT ename, job
from emp
where mgr is null
;

-- 11. 커미션을 받을 수 있는 자격이 되는 사원의 이름, 급여, 커미션을 출력하되
-- 급여 및 커미션을 기준으로 내림차순 정렬하여 표시하시오.

SELECT ename, sal, comm
from emp
where comm is not null AND NOT comm=0
order by sal, comm desc
;


-- 12. 이름의 세번째 문자가 R인 사원의 이름을 표시하시오.

select ename
from emp
where ename LIKE '__R%'
;

-- 13. 이름에 A와 E를 모두 포함하고 있는 사원의 이름을 표시하시오.
select ename
from emp
where ename LIKE '%A%E%'
;

-- 14. 담당업무가 CLERK, 또는 SALESMAN이면서 급여가 $1600, $950 
-- 또는 $1300이 아닌 사원의 이름, 담당업무, 급여를 출력하시오.
select ename, job, sal
from emp
where sal NOT in(1600,950,1300) ANd (job='CLERK' OR job='SALESMAN')
;

-- 15. 커미션이 $500 이상인 사원의 이름과 급여 및 커미션을 출력하시오.
select ename, sal, comm
from emp
where comm>=500
;

-- 16. SUBSTR 함수를 사용하여 사원들의 입사한 년도와 입사한 달만 출력하시오.
select substr(hiredate,1,5) from emp;

-- 17. SUBSTR 함수를 사용하여 4월에 입사한 사원을 출력하시오.
select ename from emp where substr(hiredate,4,2)=04;

-- 18. MOD 함수를 사용하여 사원번호가 짝수인 사람만 출력하시오.
select empno, ename from emp where MOD(empno,2)=0;

-- 19. 입사일을 년도는 2자리(YY), 월은 숫자(MM)로 표시하고 요일은 약어 (DY)로 지정하여 출력하시오.
select ename, to_char(hiredate, 'YY-MM, DY') from emp;

-- 20. 올해 몇 칠이 지났는지 출력하시오. 현재날짜에서 올해 1월 1일을 뺀 결과를 출력하고
-- TO_DATE 함수를 사용하여 데이터 형을 일치 시키시오.
select abs(trunc(to_date('20-01-01','YY-MM-DD')-sysdate)) as "올해 지난날짜", 
to_date(sysdate,'YY-MM-DD')-to_date('20-01-01','YY-MM-DD') as "오늘-1월1일" from dual;

-- 21. 사원들의 상관 사번을 출력하되 상관이 없는 사원에 대해서는 NULL 값 대신 0으로 출력하시오.
select ename, nvl(mgr,0) as "상관사번"
from emp
;

-- 22. DECODE 함수로 직급에 따라 급여를 인상하도록 하시오.
--직급이 ‘ANALIST”인 사원은 200, ‘SALESMAN’인 사원은 180,
--‘MANAGER’인 사원은 150, ‘CLERK”인 사원은 100을 인상하시오.​
select ename, sal,
    decode(job,
    'ANALYST', sal+200,
    'SALESMAN', sal+180,
    'MANAGER', sal+150,
    'CLERK', sal+100)
    as "인상된 급여"
from emp
;
        

-- 23. 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오.
-- 평균에 대해서는 정수로 반올림하시오.
select max(sal), min(sal), sum(sal), round(avg(sal))
from emp
;

-- 24. 각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균 액을 출력하시오.
-- 평균에 대해서는 정수로 반올림 하시오.
select job, max(sal), min(sal), sum(sal), round(avg(sal))
from emp
group by job
;

-- 25. COUNT(*) 함수를 이용하여 담당업무가 동일한 사원 수를 출력하시오.​
select job, count(*)
from emp
group by job
;

-- 26. 관리자 수를 출력하시오.
select job, count(*)
from emp
group by job
having job='MANAGER'
;

-- 27. 급여 최고액, 급여 최저액의 차액을 출력하시오.​
select max(sal)-min(sal) as "최고급여-최저급여"
from emp
;

-- 28. 직급별 사원의 최저 급여를 출력하시오.
-- 관리자를 알 수 없는 사원과 최저 급여가 2000 미만인 그룹은 제외시키고
-- 결과를 급여에 대한 내림차순으로 정렬하여 출력하시오.
select job, min(sal)
from emp
where mgr is not null
group by job
having min(sal)>2000
order by min(sal) desc
;

-- 29. 각 부서에 대해 부서번호, 사원 수, 부서 내의 모든 사원의 평균 급여를 출력하시오.
-- 평균 급여는 소수점 둘째 자리로 반올림 하시오.
select deptno, count(*), round(avg(sal),2)
from emp
group by deptno
;

-- 30. 각 부서에 대해 부서번호 이름, 지역 명, 사원 수, 부서내의 모든 사원의 평균 급여를 출력하시오.
-- 평균 급여는 정수로 반올림 하시오. DECODE 사용.​
select deptno, count(*)as "사원 수", round(avg(sal)) as "평균급여",
decode(deptno,
        '10','ACCOUNTING',
        '20','RESEARCH',
        '30','SALES',
        '40','OPERATIONS'
        ) as "부서이름", 
decode(deptno,
        '10','NEW YORK',
        '20','DALLAS',
        '30','CHICAGO',
        '40','BOSTON'
        ) as "지역 명" 
from emp
group by deptno
;

-- 31. 업무를 표시한 다음 해당 업무에 대해 부서 번호별 급여 및 부서 10, 20, 30의 급여 총액을 각각 출력하시오.
-- 별칭은 각 job, dno, 부서 10, 부서 20, 부서 30, 총액으로 지정하시오.
select job, deptno as "dno",
decode(deptno,'10',sum(sal)) as "부서10",
decode(deptno,'20',sum(sal)) as "부서20",
decode(deptno,'30',sum(sal)) as "부서30",
sum(sal) as "총액"
from emp
group by job, deptno
;

요청한 작업을 수행하는 중 오류 발생:

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
06552. 00000 -  "PL/SQL: %s"
*Cause:    
*Action:
업체 코드 6552


@ 조치내역

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on 湲?3??14 23:49:54 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> update sys.props$ set value$ = 'AL32UTF8' where name = 'NLS_CHARACTERSET'
 2  ;

1 row updated.

SQL> update props$ set value$='AL32UTF8' where name='NLS_NCHAR_CHARACTERSET';

1 row updated.

SQL> select * from sys.props$ where name='NLS_NCHAR_CHARACTERSET';

NAME
--------------------------------------------------------------------------------
VALUE$
--------------------------------------------------------------------------------
COMMENT$
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL32UTF8
NCHAR Character set


SQL> commit;

Commit complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1385488 bytes
Variable Size             327158768 bytes
Database Buffers          310378496 bytes
Redo Buffers                5545984 bytes
Database mounted.
Database opened.
SQL>

Oracle SQL Developer에서 재시도 -> 테이블 생성 및 성공

-- 그룹함수 : 하나의 행의 컬럼을 대상이 아닌 행의 그룹의 컬럼들을 묶어 그룹화하고 연산하는 함수
-- SUM, AVG, COUNT, MAX, MIN

select 
    sum(sal) as "월 급여 총액",
    to_char(sum(sal), 'L99,999') as "월 급여 총액2",
    round(avg(sal)) as "월 급여 평균",
    count(*) as "사원 수",
    count(comm) as "커미션을 받는 사원수", -- null은 세지 않는다. 0은 셈
    max(sal) as "가장 큰 급여"
from emp
;

select
    sum(comm),
    avg(comm),
    count(comm),
    max(comm),
    min(comm)    
from emp
where comm is not null and comm>0
;

-- 전체행을 구한다
select count(*) as from emp; -- 전체 사원의 수
select count(*) as from emp where job='MANAGER'; -- 매니저의 수
-- 매니저의 수, 평균급여, 최고급여
select count(*), avg(sal), max(sal) from emp where job='MANAGER';

-- 직무의 개수
select count(distinct job) -- 중복제거
from emp
;

------------- 특정 컬럼을 기준으로 그룹핑: Group by 컬럼

select deptno
from emp
group by deptno --order by deptno와 다르다.
;


-- 부서별 평균 월급
select deptno, avg(sal)
from emp
group by deptno
;


-- 각 부서별 인원을 부서별 인원이 많은 수대로 정렬
select deptno, count(*)
from emp
group by deptno
order by count(*) desc
;

-- 각 부서별 인원 및 커미션을 받는 사람.
select deptno, count(*)as "부서인원", count(comm) as"커미션받는사람 수"
from emp
-- where comm is not null and comm>0
group by deptno
;

------------------  HAVING : 그룹함수에 조건/ 그룹의 결과를 제한
-- 부서별로 그룹지은 후(GROUP BY), 그룹 지어진 부서별 평균 급여가 2000 이상인(HAVING) 
-- 부서번호와 부서별 평균 급여를 출력하는 경우
select deptno, round(avg(sal))
from emp
GROUP by deptno
HAVING avg(sal)>=2000  
-- having not avg(sal)>=2000
;

-- 부서별 급여의 최대값과 최소값을 구하되
-- 최대 급여가 2900이상인 부서만 출력합니다.
select deptno, max(sal), min(sal)
from emp
group by deptno
having max(sal)>=2900
order by deptno
;

 
-- 직무별 지표 : 사원의 수, 급여의 총합, 평균 급여, 최대최소
select job, count(*) || '명' as "사원 수",
            to_char(sum(sal)*1100, 'L99,999,999') as "급여 총합",
            to_char((avg(sal))*1100, 'L99,999,999') as "급여 평균", 
            to_char(max(sal)*1100, 'L99,999,999')as "최대 급여", 
            to_char(min(sal)*1100, 'L99,999,999') as "최소 급여"
from emp
where job != 'PRESIDENT'  
GROUP by job
order by job
;