[오라클] SQL 실습을 위한 오라클 11g 설치

오라클에서 회원가입만 하면 누구든지 오라클 SQL를 다운로드 받아 설치해서 연습해볼 수 있게 제공하고 있으니 부담없이 다운로드 받아 설치해보자.

설치를 위해서는 총 3개의 파일이 필요하다.

1. JDK 설치

2. 오라클 DB 설치

3. 오라클 SQL Developer

[JDK 설치]

http://java.oracle.com 에 접속한다.

자신의 운영체제에 맞는 걸 선택해서 다운로드 받은 다음에 설치하면 된다.

설치시 특별한 설명이 필요한 것이 없으니 설치과정은 생략한다.

[Oracle DBMS 설치]

데이터베이스(DBMS) 중에서 가장 안정성이 높아서 기업에서 많이 사용하는 오라클 데이터 베이스 설치를 하려면 http://www.oracle.com/kr/index.html 에 접속하여 회원가입을 한다.

로그인할 때 비밀번호는 대문자를 1글자 넣어야 된다. 회원 아이디 생성할 때 비밀번호에 대문자, 숫자가 들어간다는 걸 기억하고 있어야....

보통 대문자 설정을 잘 안하는데 오라클, 아이폰 사이트 로그인할 때는 비밀번호에 대문자가 들어간다.

다운로드를 누르고 oracle 11g를 선택하면 http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html 로 접속된다.

약간 아래로 내리면 오라클 데이터베이스 11g release 2 가 보인다.

본인 PC에 맞는 운영체제에 따라 파일 2개(File1, File2) 를 내려받는다.

다운로드 받은 2개의 파일을 압축을 풀면 아래의 형태로 보일 것이다.

setup 을 눌러서 설치한다.

전자메일 입력란에 보안관련 알림을 받기 위한 사용자 email을 입력한다.
‘My Oracle Support를 통해 보안 갱신 수신’ 체크박스는 해제하고 [다음] 버튼을 선택한다

프록시 서버 설정창이 나오면 ‘구성 상의 중요 보안문제에 대한 알림을 수신하지 않습니다’ 체크박스를 선택하고 [계속] 버튼을 선택한다.

설치 옵션은 ‘데이터베이스 생성 및 구성’을 선택하고 ‘다음’ 버튼을 선택한다

‘데스크톱 클래스’를 선택하고 [다음] 버튼을 선택 한다.

전역 데이타베이스 이름은 orcl 로 설정한다. 다른 이름으로 변경하고 싶다면 변경해도 된다.

그리고 설치할 드라이브를 C로 변경하고 싶다면 Oracle Base 에서 D를 C로 변경만 해주면 자동으로 변경된다.

중간에 오류가 나는 경우가 있다. 이 경우에는 모두 무시를 선택하고 설치했더니 이상없이 설치가 잘 되었다.

설치 확인을 위해서 PC와 노트북에서 모두 설치를 해봤다. 노트북에서 에러가 나서 모두 무시하고 설치를 했다.

설치하는 과정에 시간이 좀 걸린다.

설치가 다 되고 나면 비밀번호 관리를 눌러서 비밀번호 설정을 해준다.

연습할 대상 DB 테이블 접속을 위해서 사용자 비밀번호 지정을 해줘야 한다.

scott 계정에 잠금설정을 해제하고 새로운 비밀번호를 scott 로 입력해도 좋고 tiger 로 입력해도 좋다.

본인이 원하는 비밀번호를 설정하면 된다.

설치가 다 되면 닫기를 눌러준다.

설정이 다 끝났으면 접속은 다음 글에서 접속하면 된다.

[ 오라클 Developer 다운로드]

오라클 Developer 를 다운로드 받으면 SQL 명령문 연습을 편하게 할 수 있다.

다운로드만 하고 실행파일만 실행하면 별도의 설치없이 이용이 가능하다.

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

본인 PC 운영체제에 맞는 버전을 받으면 된다.

혹시라도 접속에 문제가 된다면 이전 버전을 받는다.

다운로드 받아서 원하는 폴더에 압축을 풀고나서 실행파일만 실행해주면 된다.

접속을 하기 위해서는 아래 그림과 같은 순서로 관리자 접속을 위한 설정을 해준다.

비밀번호는 DBMS 설치하면서 설정한 비밀번호 (ex, oracle) 를 입력한다.

그리고 정상적으로 접속되는지 확인한 다음에 저장한다.

이제 scott 계정 접속을 설정한다.

user01 계정 생성하는 방법이다.

여기서 직접 등록해도 되고 다음 게시물에서처럼 등록해도 된다.

이제 접속 설정을 위한 준비는 다 끝났다.

이제 열심히 SQL 명령문을 연습해보고 이해하자.

문법 : 

SELECT   select_list

FROM   table 또는 view

WHERE  조건   연산자  ( SELECT  select_list

      FROM  table

     WHERE  조건  ) ;

sub query 는 메인 쿼리 안에 포함되어  있는 또다른 별도 Select 문이다.

여러번의 Select문을 수행해야 얻을 수 있는 결과를 하나의 중첩된 Select 문으로 쉽게 얻을 수 있다.

sub query 가 먼저 실행되고, 그 결과가 메인 쿼리에 전달되어 실행된다.

두개의 테이블을 sub query 로 조인할 때에는 반드시 일치되는 공통 칼럼을 사용해야 한다.

- sub query 는 Select문의 select 절, FROM 절, Where절, Having 절에 올 수 있다.

  . select 절에 오는 sub query 는 스칼라 서브쿼리,

  . FROM 절에 오는 서브쿼리는 inline view 서브쿼리

- sub query 는 SELECT 문만 아니라 다른 문(INSERT, DELETE, UPDATE) 에도 쓸 수 있다

- sub query 에는 반드시 괄호를 사용한다.

- operator(연산자) 는 단일행 연산자 ( > , = , >= , < , <= , != )와

                              복수행 연산자 ( IN , ANY ,ALL,EXISTS )를 사용 할 수 있다.

- 단일행 sub query : sub query 의 결과가 1개의 행만 나오며, 반드시 단일행 연산자를 사용해야 한다. 

- 복수행 sub query : sub query 가 여러 개의 행을 리턴하며, 반드시 복수행 연산자를 사용해야 한다.
- sub query 에는 order by 를 사용할 수 없다.
- sub query 에는 NULL 값이 있으면 결과가 출력되지 않는다.

예제

단일행 sub query

-- 1. EMP 테이블에서 'SCOTT' 보다 급여를 많이 받는 사람의 이름과 급여를 출력

SQL> select ename, sal
from emp
where sal > (select sal from emp where ename = 'SCOTT');

-- 2. 사원테이블에서 BLAKE 보다 급여가 많은 사원들의 사번, 이름, 급여 출력
SQL> select empno, ename, sal
from emp
where sal > (select sal from emp where ename ='BLAKE');

-- 3. 사원테이블에서 MILLER 보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색
SQL> select empno, ename, hiredate
from emp
where hiredate > (select hiredate from emp where ename = 'MILLER');
select hiredate from emp where ename = 'MILLER';

-- 4. emp 테이블에서 사원번호가 7521의 업무와 같고 급여가 7934 보다 많은 사원의 사원번호, 이름, 담당업무, 입사일자, 급여를 출력
SQL> select empno, ename, job, hiredate, sal
from emp
where job = (select job from emp where empno=7521)
and sal > (select sal from emp where empno = 7934);

-- 5. 서브쿼리에서 그룹함수 사용
select empno, ename, job, sal, deptno
from emp
where sal < (select avg(sal) from emp);

-- 6. 사원테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름, 급여를 검색
SQL> select empno, ename, sal
from emp
where sal > (select avg(sal) from emp);

-- 7. emp 테이블에서 20번 부서의 최소급여보다 많은 모든 부서의 최소급여 출력

--  having 절에서의 서브쿼리

SQL> select deptno, min(sal)
from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);


복수행 sub query
- 복수행 서브쿼리 : IN, ALL, ANY
- 복수행 서브쿼리란 sub query 의 결과가 2건 이상 출력되는 것을 말한다.

- 메인 쿼리의 비교 조건이 sub query 의 결과 중에서 하나라도 일치하면 검색 가능

-- 1. emp 테이블에서 업무별로 최소 급여를 받는 사원의 사원번호, 이름, 업무, 입사일자, 급여, 부서번호 출력
SQL> select empno, ename, job, hiredate, sal, deptno
from emp
where sal IN (select min(sal) from emp group by job)

order by 3;

-- 2. 사원테이블에서 업무가 MANAGER인 사원의 최소급여보다 적은 급여를 받는 사원들의 이름 검색
SQL> select empno, ename, job, sal, deptno
from emp
where sal < ALL (select sal from emp where job = 'MANAGER');

-- ALL 을 사용한 것과 min(sal)을 사용한 것이 결과는 동일하다.
SQL> select empno, ename, job, sal, deptno
from emp
where sal < (select min(sal) from emp where job = 'MANAGER');

-- 3. ANY : 최소값보다 큰, ALL : 최소값보다 작은
SQL> select empno, ename, job, sal, deptno
from emp
where sal > ANY (select sal from emp where job = 'MANAGER');

SQL> select empno, ename, job, sal, deptno
from emp
where sal > (select min(sal) from emp where job = 'MANAGER');

-- 4. EXISTS 연산자 : sub query 에서 검색된 결과가 하나라도 존재하면 메인 쿼리 조건이 참이 되는 연산

-- 사원중에서 comm 을 받은 사원이 한 명이라도 있으면 모든 사원 출력
SQL> select *
from emp 
where exists (select empno from emp where comm IS NOT NULL);

다중컬럼 서브쿼리 : pairwise (컬럼을 쌍으로 묶어서 동시에 비교하는 방식)

-- sub query 에서 여러 개의 컬럼 값을 검색하여 메인 쿼리의 조건절과 비교하는 sub query

--5-1. 사원테이블에서 부서별로 가장 많은 급여를 받는 사번, 이름, 이메일, 급여 출력

-- 부서별 : group by 를 사용해야 한다는 의미, 최대 급여 : max(sal)
SQL> select deptno, empno, ename, sal
from emp
where (deptno, sal) IN (select deptno, max(sal) from emp group by deptno);

다중컬럼 서브쿼리 : unpairwise (칼럼별로 나누어 비교하고 나중에 AND 연산 처리하는 방식)

--5-2. 사원테이블에서 부서별로 가장 많은 급여를 받는 사번, 이름, 이메일, 급여 출력
SQL> select deptno, empno, ename, sal
from emp
where deptno in (select deptno from emp group by deptno)
and sal in (select max(sal) from emp group by deptno);

-- 6. 사원테이블에서 부서별 최대 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 검색

-- 오류가 생길 수 있는 쿼리문
SQL> select empno, ename, deptno, sal
from emp
where sal in (select max(sal) from emp group by deptno);

-- 정확한 쿼리문
select empno, ename, deptno, sal
from emp
where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);

SQL 그룹함수, having절

단일 행 함수와는 달리 그룹 함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 가져오는 함수이다.

그룹 당 하나의 결과가 주어지도록 행의 집합에 대해 연산할 경우 GROUP BY절을 이용하여 그룹화 할 수 있고 HAVING를 이용하여 그룹에 대한 조건을 제한할 수 있다

기본구문은 아래와 같다.
SELECT column_name, group_function(column_name)
FROM table_name
WHERE column_name =  'value'  -- where 절에는 그룹함수가 올 수 없음
GROUP BY column_name;  -- group by 절 뒤에는 컬럼 별칭을 사용할 수 없음

- GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.

- 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.

- 그룹함수를 사용하여 특정 그룹으로 구분할 때는 GROUP BY 절을 사용하며, 특정 그룹없이 중복된 데이터를 제거할 경우에는 DISTINCT 절을 사용한다.

- Count(*) 함수는 서로 다른 모든 행에 대하여 연산하지 않고 각 그룹화된 행에서 계산된다.

그룹함수의 종류

Count : NULL 이 아닌 행의 개수

SUM : NULL 값을 제외한 입력 데이터들의 합계

AVG : NULL 값을 제외한 입력 데이터들의 평균

MAX : 입력 데이터들의 최대 값

MIN : 입력 데이터들의 최소 값

STDDEV : NULL 값을 제외한 입력 데이터들의 표준편차

VARIANCE : NULL 값을 제외한입력 데이터들의 분산 값

예제

-- 각 부서별로 인원수, 급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력

SQL> SELECT deptno, count(*), round(avg(sal),0), min(sal), max(sal), sum(sal)
FROM emp
GROUP BY deptno
ORDER BY sum(sal) desc;

-- 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회
SELECT job, COUNT(empno) "인원수", round(AVG(sal)) "평균급여액", MAX(sal) "최고급여액",
       MIN(sal) "최저급여액", SUM(sal) "급여합계"
  FROM emp
  GROUP BY job;

SELECT deptno, ename, AVG(sal)
FROM emp
GROUP BY deptno;
위 구문은 정상적인 결과를 조회할까?
※ 그룹함수 사용시 GROUP BY 절로 묶이지 않은 단순 컬럼은 SELECT 리스트에 사용 불가능하고 사용시 에러가 발생한다. 정상적으로 처리되려면 SELECT 문에서 ename 을 제거하던가, 아니면 GROUP BY 문에 ename을 추가하던지 해야 한다.

SELECT deptno, ename, AVG(sal)
FROM emp
GROUP BY deptno, ename;


HAVING 절
SELECT COUNT(column) as cnt FROM table GROUP BY column HAVING COUNT(column) > 5;
  - WHERE 절에서는 집계함수를 사용 할 수 없다.
  - HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
  - HAVING절은 GROUP BY절과 함께 사용된다.

-- 사원수가 다섯 명이 넘는 부서와 사원수를 조회
SELECT d.dname, COUNT(e.empno) "사원수"
 FROM emp e, dept d
 WHERE e.deptno = d.deptno
 GROUP BY d.dname
 HAVING COUNT(e.empno) > 5;

ANSI 조인으로 표기하면....

SELECT d.dname, COUNT(e.empno) "사원수"
 FROM emp e INNER JOIN dept d
 ON e.deptno = d.deptno
 GROUP BY d.dname
 HAVING COUNT(e.empno) > 5;

-- 전체 월급이 4000을 초과하는 job에 대해서 job과 월급여 합계를 조회.
-- 단 판매원(SALESMAN)은 제외하고 월 급여 합계로 내림차순 정렬
SELECT job, SUM(sal) "급여 합계"
 FROM emp
 WHERE job != 'SALESMAN'          -- 판매원은 제외
 GROUP BY job                    -- 업무별로 Group By
 HAVING SUM(sal) > 4000     -- 전체 월급이 4000을 초과하는
 ORDER BY SUM(sal) DESC;   -- 월급여 합계로 내림차순 정렬

분석 :
먼저 직업별로 조회해본다. select job from emp group by job;
그러면 직업 전체 현황이 조회된다. 판매원을 뜻하는 job 이 뭔지 정확하게 파악할 수 있다.
비교조건이 월급이다.
SUM(sal) > 4000 을 초과하는 job 을 조회하라는 것인데
where 조건에는 그룹함수 SUM(sal) 을 사용하여 비교할 수가 없으므로 Having SUM(sal) 로 비교해야 한다.

**별 평균/합계가 2000 이상 과 같은 조건이 나오면 Group by Having 절을 사용한다고 기억하면 된다.

SQL 숫자함수

Round(숫자, 원하는 자릿수) : 주어진 숫자를 반올림하여 출력함

SELECT ROUND(column_name,decimals) FROM table_name;

-- 급여가 1500부터 3000사이의 사람은 급여의 15%를 월회비로 지불
-- 출력양식은 이름, 급여, 회비(10원 단위는 반올림 처리) 정보를 출력
SQL> select ename, sal, round((sal*0.15),-2) 회비 from emp where sal between 1500 and 3000;
           회비  = sal * 0.15
           정수 2번째 자리에서 반올림 : 100 단위로 계산

Trunc(숫자, 원하는 자릿수) : 주어진 숫자를 버림한 후 출력함

오라클에서는 trunc 로 하면 되는데 MySQL 에서는 truncate 로 해야 한다.

-- 30번(영업부) 부서원의 연봉을 계산하여 이름, 부서번호, 급여, 연봉 정보를 연봉이 큰 순서데로 출력하라.

-- 연말 특별 보너스 150%, 연봉은 천단위 미만 절삭처리

SQL> select ename, deptno, sal ,trunc(((sal * 12) + (sal * 1.5)),-3) 연봉 from emp where deptno =30 order by 연봉 desc;

-- 급여(sal)가 2000 이상인 모든 사람은 급여의 5%를 경조비로 내기로 하였다 .
-- 이름, 급여, 수당(소수점이하 절삭 처리) 정보를 출력하라.
SQL> select ename as 이름, sal as 급여 , trunc((sal*0.05),0) 경조비 from emp where sal >= 2000;

MOD(숫자, 나누기할 수) : 주어진 숫자를 나누기 한 후 나머지 값 출력함

CEIL(숫자) : 주어진 숫자와 가장 근접한 큰 정수를 출력함

SQL> select ceil(10.7) from dual;

FLOOR(숫자) : 주어진 숫자와 가장 근접한 작은 정수를 출력함

SQL> select floor(10.7) from dual;

POWER : 제곱하여 출력함

SQL> select sign(1000), sign(0), sign(-22) from dual;

-- 예제 : 사원테이블에서 입사일이 12월인 사원의 사번, 사원명, 입사일 검색

-- 사원번호(empno), 사원명(ename), 직급(job), 관리자번호(mgr), 입사일(hiredate), 급여(sal),

-- 커미션(comm), 부서번호(deptno)
SQL> select empno, ename, hiredate from emp where to_char(hiredate,'MM')='12';

MYSQL 함수에 대한 기본적인 설명은 http://www.w3schools.com 에 가면 잘 나와 있다.

substr(string,start,length) : 문자열/칼럼에서 특정 길이를 추출할 때 사용하는 함수

substr 함수는 SQL, PHP, VBA 등에서 모두 같은 의미로 사용된다.

SQL> select substr(hiredate,1,6) from emp;

-- 첫번째부터 6개의 글자를 가져오라는 의미

PHP 문에서 주석처리는 //를 사용하는데, SQL 에서는 주석처리는 -- 를 붙인다.

VBA 에서는 주석처리는 ' 를 붙인다.

SQL> SELECT left(addr1,6) FROM member_data WHERE addr0 LIKE '서울%';

-- 지역이 서울인 주소(add1)에서 왼쪽에서부터 6글자를 잘라서 보여줘라

SQL> SELECT right(addr1, 6) FROM member_data WHERE addr0 LIKE '서울%';

-- 지역이 서울인 주소(add1)에서 오른쪽에서부터 6글자를 잘라서 보여줘라

<?php
// MYSQL 데이터베이스를 연다
$result = mysql_query("SELECT pub_name, SUBSTR(pub_name,4,5) FROM publisher WHERE country='USA'"); 
while($row=mysql_fetch_array($result))
{ 
    echo "<tr>";
    echo "<td align='center' width='200'>" . $row['pub_name'] . "</td>"; 
    echo "<td align='center' width='200'>" . $row['SUBSTR(pub_name,4,5)'] . "</td>"; 
    echo "</tr>"; 
} 
echo "</table>";
?> 

INSTR (string, 찾는글자, 시작위치) : 문자열/칼럼에서 특정 글자의 위치를 찾아주는 함수

LPAD(문자열, 자릿수, '채울문자') : 원하는 문자열을 선행에 Padding

-- LPAD(문자열, 자릿수, '채울문자')
SQL> select empno, ename, LPAD(sal,10,'*') "급여" from emp;

RPAD(문자열, 자릿수, '채울문자') : 원하는 문자열을 후행에 Padding

SQL> SELECT RPAD('dennis kim', 15, '*') NAME FROM dual ;

LTRIM(문자열, '제거할문자')

NVL(칼럼, 치환할 값) : NULL 값을 다른 값으로 바꾸어 주는 함수

CONCAT(char1, char2) : Concatenation의 약자로 두 문자를 결합하는 역할을 하며 || 연산자와 같다.

INITCAP(char)  : 주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 준다

-- MySQL 에서는 함수 지원을 못하는지 에러가 발생

MySQL 테이블 이름변경, 삭제, 칼럼삭제, 칼럼추가

MySQL 에서 테이블을 삭제하거나 이름 변경, 그리고 테이블의 칼럼을 삭제하거나 추가, 이름 변경하는 것에 대하 알아봤다.

가장 편한 방법은 phpMyAdmin 을 이용하면 이런 작업이 수월한데 MySQL 자체에서도 어떻게 하는 것인지 알아두면 좋을 거 같아서 적어둔다.

테이블 이름 변경
mysql > RENAME TABLE `comnetlink`.`rb_member_data1` TO `comnetlink`.`rb_member_data` ;

테이블 삭제
mysql > DROP TABLE `rb_member_data`;

테이블 칼럼 삭제
mysql > ALTER TABLE `rb_member_data` DROP `is_modify`;

mysql > ALTER TABLE `rb_member_data` DROP `is_checking`, DROP `is_modify`;

테이블 칼럼 추가
mysql > ALTER TABLE `rb_member_data` ADD `is_modify` TINYINT( 4 ) NOT NULL DEFAULT '0' AFTER `is_checking`;

테이블 칼럼 이름 변경
mysql > ALTER TABLE `rb_member_data` CHANGE `is_modify` `modify` TINYINT( 4 ) NOT NULL DEFAULT '0';

phpMyAdmin 을 이용한 테이블 등록

phpMyAdmin 을 이용하여 DB에 테이블을 등록하는 방법이다.

오늘 실수로 테이블을 날려 먹었다.

테이블을 백업 받아두고 날려 먹어서 다행이었지 정말 시껍했다.

PHP 4.0 버전에서는 varchar(9) 길이가 짧아도 테이블에 긴 데이터가 들어가는 모양이다.

PHP 5.6 버전에서는 이런 걸 칼같이 잡아내어 칼럼길이를 충분히 늘려주고 데이터를 업로드했다.

MySQL 통계 Group by

문제 : 판매자별 상품 판매건수를 구하라.

SQL 문 : Group by 문을 활용하고, 판매자 ID 별로 건수를 SUM하면 된다. 만약 테이블 레코드가 한줄씩 들어간 경우라면 SUM 대신에 Count(goods_code) 를 하면 된다.

SELECT sales_name, SUM (goods_code)

FROM orders

GROUP BY sales_name;

MySQL 인덱스

MySQL은 첫 번째 열부터 전체 테이블에 걸쳐서 연관된 열을 검색하기 때문에 테이블이 크면 클수록 검색속도가 엄청나게 느려진다.

SQL은 테이블에 있는 행(record)를 접근하는 방법은 순차적 접근(sequential access) 방법이고, 다른 하나는 직접 접근(direct access) 이다.

테이블이 많은 행을 가지고 있을 때 오직 하나의 행만 찾는다면 많은 시간을 소비하고 비효율적이다.

MySQL 전문(full-text) 검색은 작은 데이터 집합에서는 빠르지만 데이터의 크기가 커지면 성능이 나빠진다.

레코드가 100만개 있는 경우나 Index 된 텍스트의 크기가 기가바이트 단위인 경우 Query 수행 시간은 1초 ~ 10분 사이를 왔다갔다 하게 되므로 고성능 웹 응용 프로그램에서는 사용할 수가 없다.

MySQL 은 적절한 스키마와 최적화된 Query가 있고, WHERE절에 너무 많은 칼럼이 들어 있지 않은 한 잘 동작한다. 하지만 칼럼수가 늘어나고, 가능한 모든 경우에 대한 검색 기능을 지원하려고 한다면 필요한 Index 수가 기하급수적으로 늘어난다.

Index 를 추가하더라도 해당 칼럼이 절반정도가 선택되는 경우라면 Index 가 별로 도움이 안되어 Full Scan 을 사용한다.

MySQL 은 Index Scan 을 사용할지 Full Table Scan을 사용할지를 자동적으로 결정한다.

MySQL 은 Index 를 사용해서 매치되는 행을 찾고, 디스크에 무작위 순서로 접근하면서 레코드들을 하나하나 읽어낸 뒤 Sort 버퍼에 집어넣는다. 그런 다음 파일 정렬을 통해 값을 정렬하고, 정렬된 값들 중 대부분을 버리게 된다. 이 과정에서 LIMIT 절에 상관없이 전체 결과를 임시로 저장해서 처리한다. 만약 결과 set 이 Sort 버퍼에 다 들어갈 수 없다면 결과 set 은 디스크에 저장되어야 하므로 디스크 I/O까지도 많이 발생시키게 된다.

테이블간 데이터 복사(구조가 다른 경우 포함)

테이블 구조가 다른 경우에도 테이블을 복사해야 하는 경우가 있다.

테이블을 설계하다보면 테이블 구조가 변경해야 하는 상황에서는 A 테이블과 B 테이블 구조가 약간 상이한 상황이 있다.

[니즈]

A_TABLE 테이블의 특정한 조건에 해당되는 데이터를

B_TABLE 테이블로 옮기고 싶다.

[방법]

INSERT INTO B_TABLE (필드1, 필드2, 필드3, 필드4)

(SELECT 필드1, 필드2, 필드3, 필드4 FROM A_TABLE WHERE 조건);

순서만 정확하게 맞추어주면 복사가 된다. 

INSERT INTO 테이블에 있는 필드수는 SELECT 문의 필드수와 동일해야 한다.

물론 INSERT INTO 테이블에 있는 필드 타입도 SELECT 문의 필드 타입과 동일해야 한다.

필수 필드가 있는 경우에는 반드시 포함해야 한다. 포함하지 않으면 에러가 발생한다.

작업은 EditPlus 같은 에디터를 이용하여 미리 만들어두고 나서

MySQL 접속을 하여 복사하여 붙여넣기를 하면 작업이 한방에 끝난다.

참고로 자동으로 증가되는 UID 번호는 필드에 포함시키지 않아도 된다. 굳이 포함시킨다면 증가되는 번호가 서로 다르게 증가될 수 있으므로 NULL 로 필드를 지정하면 된다. 'NULL' 이 절대 아니라는 점...

MySQL의 root암호 분실시 대처법

리눅스 root 패스워드 관리는 매우 중요하다.

만약 MySQL root 패스워드를 분실한 경우가 생긴다면 아래 방법으로 MySQL root 패스워드 복구가 가능하다.

아래 내용은 직접 실행을 해보고 나서 작성을 한 것이다.

테스트 환경 : CentOS 6.5

이미지는 실제 메시지를 눈으로 확인하는 차원으로 보기 위해서 캡처를 한 것이다.

# ps -ef | grep mysql
# killall mysqld

# cd /usr/local/mysql/bin/
# ./mysqld_safe --skip-grant &
로 데몬을 실행하게 되면 대기모드 상태가 된다.

여기서 엔터키를 치면 대기모드에서 빠져나오므로 다른 콘솔창에서 패스워드 변경 작업이 끝나기 전에는 엔터키를 치지 말아야 한다.

다른 콘솔창을 다른 창을 띄워서 아래 과정을 실행하여 mysql 패스워드를 변경한다.

# mysql -uroot -p
mysql> use mysql;
mysql> UPDATE user SET password=password('ROOT_비밀번호') WHERE user='root';
mysql> flush privileges;
mysql> quit

실행된 mysq 데몬을 죽인 다음에 정상적으로 mysql 데몬을 실행한다.
# /etc/init.d/mysqld start

이제 정상 가동된 것을 확인할 수 있을 것이다.

SELECT DISTINCT 구문

SELECT DISTINC 구문은 오라클, MySQL 등 모든 SQL 문에서 중복값을 제외한 값을 보고 싶을 때 사용한다.

또다른 방법으로는 select job from emp group by job; 처럼 그룹함수를 이용해도 중복값을 제거한 결과를 얻을 수 있다.

MySQL 사용자 계정 패스워드 변경

리눅스에서 MySQL 사용자의 패스워드를 변경하고자 할 경우

# mysql -u root -p    // root 계정으로 접속한다.

# mysql -u userid -p    // userid 계정으로 접속할 경우

mysql > use mysql;    // mysql DB 로 접속 변경
mysql > update user set password=password('패스워드') where user='userid';

사용자 계정 패스워드를 변경했으면

mysql > flush privileges;

해 주어야 권한 변경사항이 적용된다.

[정리] MySQL Data 백업 방법 및 복구 방법

MySQL 데이터를 백업하는 방법에는 물리적인 백업과 논리적인 백업이 있다.

ㅇ 물리적 백업

    - MySQL 데이터가 기록되는 폴더를 통째로 압축해서 백업한다. (속도가 빠르며 작업이 단순)

    - 복구할 때는 MySQL 데몬을 정지시키고 해당 폴더에 압축을 푼다.

ㅇ 논리적 백업

    - 리눅스에서 기본 제공하는 mysqldump를 이용하는 방법

    - phpmyadmin 을 이용하는 방법

    - 기타 툴을 이용하는 방법

    등이 있다.

    논리적 백업을 하면 문제 발생시 원인 파악및 해결하기가 수월한데 복구 시간이 상당 소요될 수 있다.

1. 리눅스 상에서 mysqldump를 이용한 백업

3. phpmyadmin 상에서 백업

    phpmyadmin 을 이용하면 명령어를 몰라도 쉽게 백업을 할 수 있다.

     자료를 백업하는데에는 시간 별로 걸리지 않아서 자주 백업해 두어야 문제가 생길 때 복구가 가능하다.

4. phpmyadmin 으로 복구

   용량이 작은 것은 phpmyadmin 에서 복구하면 편리한데 용량이 좀 큰 것은 작업하다보면 Lock 이 걸려버려서

   작업하기가 쉽지 않다.

MySQL DB 생성/삭제 및 사용자 추가/삭제, 권한 부여

서버 관리를 하다보면 mysql 사용자 계정을 추가해 줄때가 있다.

MySQL 접속 및 데이터 베이스 추가

# mysql -u root -p

사용자 계정을 추가하기 전에 먼저 현재 생성된 사용자 계정을 확인한다.

mysql > use mysql;    // mysql database 선택
mysql > select host, user, password from user;    // user 테이블 살펴보기

사용자 추가 (권한추가)
mysql > create user 사용자ID;   // 사용자 추가

mysql > create user userid@localhost identified by '비밀번호';

// 사용자(user)를 추가하면서 패스워드까지 설정

기존에 사용하던 계정에 외부 접근 권한을 부여하려면, Host를 '%' 로 하여 똑같은 계정을 추가한다

mysql > create user 'userid'@'%' identified by '비밀번호';  // '%' 의 의미는 외부에서의 접근을 허용

다른 방법으로는

mysql > USE mysql;     // mysql database 선택
mysql > INSERT INTO user (Host, User, Password) VALUES ('localhost', '계정아이디', password('비밀번호'));
mysql > INSERT INTO user (Host, User, Password) VALUES ('%', '계정아이디', password('비밀번호'));
mysql > FLUSH privileges;

mysql > drop user '사용자ID'@localhost;    // 사용자 삭제

mysql > select * from user;    // 등록된 모든 사용자 ID 조회
mysql > delete from user where user = '사용자ID';      // 사용자 삭제

계정이 생성되었다면, 그 계정이 접근할 수 있는 데이터베이스를 생성하고 권한을 부여해야 한다

데이터베이스 생성

mysql > show databases;     // DB 목록 확인

msyql > create database DB명;    // 데이터베이스 생성

mysql >create database sampledb default character set utf8mb4 COLLATE = utf8mb4_unicode_ci;

msyql > create schema DB명 default character set utf8; -- 둘중에 하나를 입력하면 DB 생성됨

mysql > create database DB명 default character set utf8;

mysql > create database DB명 default character set utf8 COLLATE utf8_general_ci;

mysql > CREATE DATABASE IF NOT EXISTS DB명 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

// default character set을 지정하지 않으면 한글이 깨져서 나오므로 주의해야 한다.

// default character set 변경

msyql > ALTER DATABASE DB명 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

msyql > drop database DB명;       // 데이터베이스 삭제

사용자에게 데이터베이스 사용권한 부여

MySQL은 사용자 이름, 비밀번호, 접속 호스트로 여러분을 인증한다. 

MySQL은 로그인을 시도하는 위치가 어디인가 하는 것도 인증의 일부로 간주한다.

MySQL 에서 사용자 계정을 추가하고 권한을 추가하거나 제거하는 데 GRANT 와 REVOKE 명령을 사용하기를 권장한다.

사용자에게 허가된 것을 확인하려면 SHOW GRANTS 를 사용한다.

IDENTIFIED BY '비밀번호'; 는 권한부여를 하면서 비밀번호까지 변경하고자 하는 경우

이므로 비밀번호는 변경하지 않으려면 이 부분을 빼면 된다.

mysql > GRANT ALL PRIVILEGES ON DB명.테이블 TO 계정아이디@host IDENTIFIED BY '비밀번호';

// 계정이 이미 존재 하는데 'identified by '비밀번호' 부분을 추가하면 비밀번호가 변경된다

mysql> GRANT ALL privileges ON DB명.* TO 계정아이디@locahost IDENTIFIED BY '비밀번호';
mysql> GRANT ALL privileges ON DB명.* TO 계정아이디@'%' IDENTIFIED BY '비밀번호';

mysql > grant all privileges on DB명.* to userid@'%' identified by '비밀번호' ;  //모든 원격지에서 접속 권한 추가

host에 '200.100.%' 로 하면 IP주소가 200.100.X.X 로 시작되는 모든 IP에서 원격 접속을 허용한다는 의미

host에 '200.100.100.50' 으로 하면 IP주소가 200.100.100.50 인 곳에서만 원격 접속을 허용한다는 의미

mysql > grant all privileges on test.* to userid@localhost identified by '비밀번호';// user 에게 test 데이터베이스 모든 테이블에 대한 권한 부여

mysql> grant select, insert, update on test.* to user@localhost identified by '비밀번호';

// user 에게 test 데이터베이스 모든 테이블에 select, insert, update 권한 부여

mysql> grant select, insert, update on test.* to user@localhost;   -- 패스워드는 변경없이 권한만 부여하는 경우

// user 에게 test 데이터베이스 모든 테이블에 select, insert, update 권한 부여

mysql> grant all privileges on *.* to user@localhost identified by '비밀번호' with grant option;

// user 에게 모든 데이터베이스 모든 테이블에 권한 부여// 전역 권한은 모두 광범위한 보안문제가 수반되므로 권한을 허용하는 경우 신중해야 함

mysql > flush privileges;     // 변경된 내용을 반영(권한 적용)

사용자에게 부여된 권한 확인

mysql > SHOW GRANTS FOR test@localhost;  -- userid 와 host명까지 붙여서 검색해야 함

mysql > SHOW GRANTS FOR test@'%';

mysql > SHOW GRANTS FOR test@'200.100.100.50';

사용자에게 데이터베이스 사용권한 제거

revoke all on DB명.테이블명 from 사용자ID;     // 모든 권한을 삭제

이제 다시 show grants 로 정보를 확인해보면

user 정보는 남아 있는데, 권한부여 정보는 삭제되고 없는 것이 보인다.

사용자 계정마저 삭제하고 권한 설정 정보를 확인하려고 하면 Error 가 발생하는 걸 확인할 수 있다.

사용자 계정 삭제

mysql > drop user userid@'%';

mysql > drop user userid@localhost;

위에 있는 것과 비교해서 host 가 % 로 된 것이 삭제되었다.

비밀번호 보안

MySQL 비밀번호가 일반 텍스트로 저장되지 않는다고 해서 비밀번호를 간단하게 만들어서는 안된다. MySQL 서버에 접속 가능한 사람은 누구든지 무차별 대입공격(brute-force) 방식으로 비밀번호를 알아내려고 시도해 볼 수 있으며, MySQL 은 유닉스 비밀번호에서처럼 이런 공격을 감지하고 막아낼 방도가 없다.

도움이 되셨다면 댓글을 달거나 공감 눌러주세요.

MySQL root 패스워드 설정/변경

리눅스에서 MySQL 을 설치하고 나면 root 패스워드가 없는 상태이다.

root 패스워드를 지정하면 mysql 서버를 안전하게 보호할 수 있다.

root 패스워드를 변경하고 싶을 때에도 아래와 같은 순서로 변경하면 된다.

아래 명령어를 Editplus 에 복사하여 두고 한줄씩 복사한 다음 마우스 우클릭을 하면 붙여넣기가 된다.

mysql > flush privileges;   // 변경사항 적용
mysql > select host, user, password from user;

root 패스워드 분실시 대체방법

http://link2me.tistory.com/496

엑셀데이터를 MYSQL DB Import (Update)

DB 데이터가 잘못된 것이 있어서 엑셀로 내보내기를 한 다음 엑셀에서 작업을 한 후 수정된 부분만 다시 DB에 업데이트를 하고 싶다.

어떤 방법을 택하면 편할까 하는 생각을 했지만 내가 아는 수준이 미약한지라 아주 허접한 방법으로 시도를 하기로 했다.

먼저 엑셀에서 필요한 부분만 남기고 나머지는 모두 삭제한다.

이 데이터를 아래 VBA 코드를 이용하여 내보내기를 한다.

Sub TextExport()
'// Export Each Sheet As Text File
   Dim rng As Range
   Dim iRow As Long, iCol As Integer
   Dim sTxt As String, sPath As String
   Dim ff As Integer

   ff = FreeFile
    sPath = ThisWorkbook.Path & "\"
    Open sPath & ActiveSheet.Name & ".csv" For Output As #ff
    Set rng = ActiveSheet.UsedRange

    deLimiter = ";"     '// 구분자

    For iRow = 2 To rng.Rows.Count  '// 2행부터 마지막 행까지
        For iCol = 1 To rng.Columns.Count  '// 1열부터 오른쪽 최대 열까지
            sTxt = sTxt & ActiveSheet.Cells(iRow, iCol).Value & deLimiter
        Next iCol
        Print #ff, Left(sTxt, Len(sTxt) - 1)

       sTxt = vbNullString
    Next iRow
    Close #ff   '// 작업을 마치고 파일을 닫는다   
    MsgBox "내보내기 완료"
End Sub

그러면 파일에 2번째 행부터 마지막 행까지 내보내기가 된다.

파일 내보내기가 완료되었으므로 이제 서버의 MYSQL 로 데이터를 업데이트 하기 위한 과정이 필요하다.

<?
########## KIMSQRB와 DB연동 ##########
defined('__KIMS__');
session_start();
error_reporting(E_ALL ^ E_NOTICE);

include_once '/_var/db.info.php';
include_once '/_core/function/db.mysql.func.php';   
$DB_CONNECT = isConnectDb($DB);
########### DB 연동 정보 가져오기 끝 ###########
$tblName = data;    // 테이블명

$file=fopen("data.csv","r"); // 엑셀로 내보낸 파일명을 적어준다
if(!$file) {
    echo("Failed to open the file");
    exit;
}

$total_line = 0;
while($line = fgetcsv($file,1000, ";")) { // 구분자는 ; 로 지정, 한줄씩 읽어드린 값을 1000 라인까지 계속하라.
    // 조건에 맞는 것만 필드 업데이트 하기
    $result = mysql_query("Update $tblName SET subject = '$line[1]' Where uid = '$line[0]'",$DB_CONNECT);
$total_line++;
}

echo("총 $total_line Update");
fclose ($file);
?>

바로 위 PHP 소스코드와 data.csv 파일을 서버에 업로드한다음에 실행하면 업데이트가 된다.

PHP 소스코드는 DB를 연동하고 fopen 으로 파일을 열어서 while 문으로 업데이트한다.

파일에서 가져온 데이터 값은 line[0], line[1] 로 읽어들인다.

이 방법으로 했더니 1000 개 정도 되는 데이터를 순식간에 업데이트했다.

게시물 개수, 업데이트, 출력

MySQL 에서 직접 실행할 명령어에 mysql_query 함수를 사용하고 DB연결만 시키면 PHP에서 MySQL DB의 데이터를 조회, 업데이트, 삭제, 추가 등을 할 수 있다.

// 조건에 맞는 게시물 개수 구하기

$count = mysql_query("SELECT count(*) FROM $tblName Where role = 'man'",$DB_CONNECT);
$cnt = mysql_result($count,0,0);
echo $cnt;
echo "<br>";

// 조건에 맞는 것만 필드 업데이트 하기
$result = mysql_query("Update $tblName SET sex = 2 Where role = 'man'",$DB_CONNECT);

// 조건에 맞는 게시물 전부 출력해서 확인하기
$result = mysql_query("SELECT * FROM $tblName Where role = 'man'",$DB_CONNECT);
while($row = mysql_fetch_array($result)) {
    echo $row['role']. " / " . $row['sex'];
    echo "<br>";
}

GROUP BY 절

SELECT 문으로 검색한 결과를 특정 컬럼을 기준으로 그룹화하기 위해 GROUP BY 절을 사용한다.

GROUP BY 절을 사용할 때는 그룹 함수를 같이 사용해야 한다.

개수를 구하는 COUNT(), 평균을 구하는 AVG(), 최소값을 구하는 MIN(), 최대값을 구하는 MAX(), 총 합을 구하는 SUM() 등이 있다.

[PHP] MYSQL 테이블 생성하고 데이터까지 복사 예제

아래 방법은 테이블A 에서 테이블B로 테이블 구조를 생성하고 데이터까지 복사하는 방법이다.

그러나 primary key 등은 생성하지 못한다.

<?php
defined('__KIMS__');
session_start();

// 테이블이 생성되기 전에 데이터 베이스 연결하기
// 데이터베이스는 mysql_select_db () 함수를 사용하여 선택
include_once 'db.info.php';
require '../_core/function/db.mysql.func.php';
require '../_core/function/lib/getContent.lib.php';
$DB_CONNECT = isConnectDb($DB);
// 데이터베이스 연결하기 완료

$A_TABLE = Sample;    // 원본 테이블
$B_TABLE = Sample_copy;    // 복사할 테이블
$result = mysql_query("SELECT COUNT(*) from $B_TABLE", $DB_CONNECT);
if ( !$result )  {
$result = "CREATE TABLE $B_TABLE as select * from $A_TABLE";
mysql_query($result, $DB_CONNECT);
echo "테이블 Copy 완료!!";
}
else { echo "테이블이 이미 있어요"; }

//데이터베이스와의 연결 종료
mysql_close($DB_CONNECT);
?>

[PHP] MYSQL 신규 테이블 생성 예제

아래 예제는 킴스큐RB 로 운용하는 테이블 정보를 phpMyAdmin 을 이용하여 테이블 내보내기를 하여 만들어진 SQL 문을 가져다가 만든 것이다. 직접 테스트해보고 테이블 구조는 약간 변경했다.

$A_TABLE, $B_TABLE 이라고 굳이 적은 이유는 테이블 생성, 데이터 복사 등을 편하게 하기 위한 목적이다.

아직 킴스큐RB 구조 모듈에 맞게 작업할 줄을 몰라서 현재 방법으로 정리해둔다.

첨부한 파일은 아래 코드임.

tb_copy.zip

<?php
defined('__KIMS__');
session_start();

// 테이블이 생성되기 전에 데이터 베이스 연결하기
// 데이터베이스는 mysql_select_db () 함수를 사용하여 선택
include_once 'db.info.php';
require '../_core/function/db.mysql.func.php';
require '../_core/function/lib/getContent.lib.php';
$DB_CONNECT = isConnectDb($DB);

// 데이터베이스 연결하기 완료

$A_TABLE = Sample;    // 원본 테이블
$B_TABLE = Sample_copy;    // 복사 또는 신규 생성할 테이블
$result = mysql_query("SELECT COUNT(*) from $B_TABLE", $DB_CONNECT);
if ( !$result )
{
$result =
"CREATE TABLE `$B_TABLE` (
  `uid` int(10) unsigned NOT NULL auto_increment,
  `user_id` varchar(20) NOT NULL default '',
  `user_name` varchar(16) NOT NULL default '',
  `Cate1` varchar(12) default NULL,
  `Cate2` varchar(12) default NULL,
  `subject` varchar(200) NOT NULL default '',
  `explain` varchar(200) default NULL,
  `direct` varchar(200) default NULL,
  `comment` text,
  `ABC` char(2) default NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`uid`)
) TYPE=MyISAM AUTO_INCREMENT=0 ";

mysql_query($result, $DB_CONNECT);
echo "테이블 생성완료!!";
}
else { echo "테이블이 이미 있어요"; }

//데이터베이스와의 연결 종료
mysql_close($DB_CONNECT);
?>

테스트 해보니 PHP 와 MYSQL 버전에 따라서 DB테이블 구조 다루는 방식이 약간 다른 거 같다.

버전이 서로 다른 걸 가지고 테이블을 생성하니까 생성이 안된다.

위의 것은 PHP 버전: 4.4.8 이고, MYSQL 4.0.27

테이블 구조는 원래 테이블에서 일부 테이블을 삭제한 것임

-- PHP Version: 5.4.27, MYSQL 5.1
-- Table structure for table `bbs_data`
CREATE TABLE `bbs_data` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `gid` double(11,2) NOT NULL DEFAULT '0.00',
  `name` varchar(30) NOT NULL DEFAULT '',
  `nic` varchar(50) NOT NULL DEFAULT '',
  `id` varchar(16) NOT NULL DEFAULT '',
  `pw` varchar(50) NOT NULL DEFAULT '',
  `category` varchar(100) NOT NULL DEFAULT '',
  `subject` varchar(200) NOT NULL DEFAULT '',
  `content` mediumtext NOT NULL,
  `regis` varchar(14) NOT NULL DEFAULT '',
  `modify` varchar(14) NOT NULL DEFAULT '',
  `d_comment` varchar(14) NOT NULL DEFAULT '',
  `upload` text NOT NULL,
  `ip` varchar(25) NOT NULL DEFAULT '',
  `adddata` text NOT NULL,
  PRIMARY KEY (`uid`),
  KEY `bbs` (`bbs`),
  KEY `category` (`category`),
  KEY `subject` (`subject`),
  KEY `d_regis` (`d_regis`),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;