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 ;

[PHP] MYSQL 테이블 복사 방법

phpMyAdmin 을 이용하면 굳이 아래와 같은 걸 알 필요도 없다.

간단하게 테이블 복사, 데이터 복사가 해결된다.

MYSQL 테이블 복사하는 방법에 대해 조사하고 내나름대로 이해하기 쉽게 다시 정리했다.

// A_TABLE : 원본테이블, B_TABLE : 대상 테이블

1. 복사할 테이블이 없는 경우

테이블을 생성후, 데이터를 복사

//동일한 구조만 생성하고자 할 때

CREATE TABLE B_TABLE LIKE A_TABLE;

// 신규 테이블 생성시 기존의 테이블 스키마 정보 + 데이터 가져옴

** 아래 방법은 테이블 생성 및 데이터 복사는 잘 되는데 primary key 설정이 안됨

CREATE TABLE B_TABLE as select * from A_TABLE;

또는

CREATE TABLE B_TABLE select * from A_TABLE;

// 완전히 다른 테이블을 생성하고자 할 때

CREATE TABLE [테이블명] ( [필드명] [필드타입](크기), ... );

예제

2. 복사할 테이블이 존재하는 경우

// A_TABLE 라는 테이블의 특정한 조건에 해당되는 데이터를 B_TABLE 라는 테이블로 전부 옮길 때

INSERT INTO B_TABLE SELECT * FROM A_TABLE;

// A_TABLE 라는 테이블의 특정한 조건에 해당되는 데이터를 B_TABLE 라는 테이블로 옮길 때
INSERT INTO B_TABLE (필드1, 필드2, 필드3, 필드4) SELECT 필드1, 필드2, 필드3, 필드4 FROM A_TABLE where 조건;

[PHP] MYSQL SELECT 문 다루기

<?php
include "connect.php"; //mysql DB접속

$tblName = sample; // 테이블명
$query="SELECT COUNT(*) FROM $tblName;"; //SELECT COUNT(*) : 가져온 레코드의 개수를 세어주는 함수
// $query="SELECT SUM(uid) FROM $tblName;"; // 지정된 컬럼(uid)에 입력된 값들의 합계를 구함
// $query="SELECT MAX(uid)+1 FROM $tblName;"; // 지정된 컬럼(uid)에 입력된 값중에서 최대값을 구함
// $query="SELECT MIN(uid) FROM $tblName;"; // 지정된 컬럼(uid)에 입력된 값중에서 최소값을 구함
// $query="SELECT AVG(uid) FROM $tblName;"; // 지정된 컬럼(uid)에 입력된 값들의 평균을 구함
$result=mysql_query($query, $DB_CONNECT); //쿼리문입력
$count = mysql_result($result, 0, 0); //꺼내온 값의 저장 : mysql_result (쿼리결과, 열, 행);
// 저장될 변수 $count, $sum, $max, $min, $average 등으로 지정
echo $count;
?>

<?php
include "connect.php"; //mysql DB접속

$tblName = sample; // 테이블명
$query="SELECT COUNT(*) FROM $tblName;"; //SELECT COUNT(*) : 가져온 레코드의 개수를 세어주는 함수
$result=mysql_query($query, $DB_CONNECT); //쿼리문입력
$row = mysql_fetch_array ($result); //연관배열만들기, $배열이름["함수(매개변수)"]
echo $row["COUNT(*)"];
?>

<?php
include "connect.php"; //mysql DB접속
$tblName = sample; // 테이블명
$query = "SELECT ROUND(AVG(uid),0)) FROM $tblName;" // ROUND(컬럼명,자릿수) 함수는 반올림하는 함수
// 자릿수를 1로 지정하면 소수점 둘째자리에서 반올림하여 소수점 첫째자리까지만 표시하라는 의미
$result = mysql_query($query, $DB_CONNECT); //쿼리문입력
$average = mysql_result($result, 0, 0); //꺼내온 값의 저장 : mysql_result (쿼리결과, 열, 행);
echo $average;
?>