'SQL'에 해당되는 글 93건

접속로그 통계 구현을 위해서 로그를 쌓고 있는 테이블에서 데이터를 가공해야 한다.

접속로그 테이블에는 접속실패, 접속성공 등 모든 접속 시도 데이터를 저장해야 한다.

하지만 접속통계는 로그인 성공에 대한 것만 일자별로 사용자 기준으로 접속하는 테이블을 구현하면 될 것이다.

데이터를 추출하여 넣은 SQL 쿼리문은 다음과 같다.

없는 자료는 Insert 하고 중복된 자료는 특정 칼럼만 업데이트한다.

달라질 칼럼은 hit 칼럼밖에 없을 것이다.

중복체크하는 중요한 Key는 date_userID 인덱스 설정이다.

이 테이블에서 다시 일별 사용자수 접속 통계, 접속수 통계를 구분하는 테이블을 만들어보자.

UNIQUE KEY 설정은 필수 사항!!!

SQL 엔진에 따른 참조무결성 여부 확인 및 테이블 간에 참조 무결성 확인

MySQL 샘플 데이터베이스 설치하는 방법이다.

https://www.mysqltutorial.org/mysql-sample-database.aspx

위 사이트에 접속하면 받을 수가 있는데 문제는 Import 하면 에러가 발생하더라.

전체적인 구조는 위와 같다.

Legacy Create 테이블 생성 방식(?)이라 그런지 몰라도 테이블 순서가 매우 중요하다.

sql 파일을 Editor로 열어보면, Foreign Key 가 설정되어 있고 employees 테이블을 참조하고 있다.

customer 테이블보다 먼저 employees 테이블이 먼저 Import 되어야 한다는 의미로 간주해야 한다.

이런 점을 고려하여 테이블 순서를 변경한 파일을 첨부한다.

위 파일을 Import 하면 정상적으로 테이블이 생성되고 데이터가 추가될 것이다.

DBWeaver TOOL 을 이용해서 접속해보자.

접속환경 : VirtualBox 기반 CentOS 7.9 MariaDB 10.5

DB IP address : 192.168.1.20 (사설 IP)

Access IP address : 192.168.1.25

테이블 설계 예시이다.

중복 체크를 하기 위해서 UNIQUE KEY 설정을 3개의 칼럼을 JOIN 으로 해서 하나의 KEY로 설정했다.

Engine 은 MyISAM 으로 한 이유는 Legacy PHP 로 코딩하는 경우, InnoDB 엔진으로 설정하면 원치 않는 에러 발생시 해결하기 난감한 경험을 겪어서이다.

MariaDB 테이블명이 대소문자 구분이 되는 경우와 되지 않는 경우가 있다.

확인 방법

show variables like 'lower_case_table_names';

lower_case_table_names = 0

- UNIX 기반 시스템의 기본값

- 테이블이름, 별명 및 데이터베이스 이름이 대소문자를 구분하여 비교

lower_case_table_names = 1

- 테이블이름, 별명 및 데이터베이스 이름이 소문자로 저장되며 대소문자를 구분하지 않는다.

변경방법

vi /etc/my.cnf.d/server.cnf

lower_case_table_names=1

1. 개요

MySQL에는 아래 3가지 방법을 이용하여 중복 레코드를 관리할 수 있다.

1. INSERT IGNORE ...
2. REPLACE INTO ...
3. INSERT INTO ... ON DUPLICATE UPDATE

각 방법의 특징을 요약하면 다음과 같다.

방법 특징

2. 사전 조건 및 중복 처리 방법

중복 레코드 관리를 위해선 테이블에 UNIQUE INDEX가 필요하다.

자동으로 증가하는 Primary Key 로 중복관리를 하는 것은 쉽지 않다.

중복 레코드 관리를 위한 KEY가 2개의 칼럼 조합일 수도 있다.

UPDATE하고자 할 때는 항상 column=VALUES(column)와 같이 적어줘야 한다는 점이다.

두가지 케이스로 테스트를 해보면서, id 값이 어떻게 변경되는지 확인해 보시라.

Primary Key 값의 변동없이 Insert INTO, Update를 처리하려면 PHP 등과 같은 별도의 백엔드 언어를 통해서 처리하는 코드를 구현해야 한다.

MySQL 샘플 DB가 필요해서 설치하는 과정을 적어둔다.

https://github.com/datacharmer/test_db 에 접속해서 파일을 다운로드한다.

test_db-master.zip 라는 파일이 다운로드 된다.

이제 PC에 설치된 MariaDB 에 접속해야 한다.

CMD 창을 관리자권한으로 열어야만 접속이 되더라.

Windows키 + R 을 누르면

cmd 를 입력하고 Ctrl + Shift + Enter키를 누르면 관리자권한으로 접속된 CMD창이 뜬다.

이제 MariaDB가 설치된 폴더로 이동한다.

그리고 다운로드 받은 파일을 해당 폴더로 옮겼다.

mysql -u root -p

패스워드 입력

을 하면 DB에 접속된다.

use mysql;

source employees.sql;

를 하면 DB와 테이블이 생성되고 샘플 데이터가 업로드된다.

테이블이 생성된 걸 확인할 수 있다.

이제 DBeaver 접속툴로 접속해 본 이미지를 확인해보자.

데이터 확인

쿼리문 연습은 phpMyAdmin 에서 해도 된다.

DB 접속은 보통 phpMyAdmin 으로 접속하기 때문에 사용할 일이 없었다.

꼭 DB 접속툴을 이용하여 접속해야 하는 환경에서 HeidiSQL 접속을 시도했는데 접속이 어렵다.

그래서 DB툴을 검색하니 DBeaver 가 있어서 설치하고 접속을 해봤다.

HeidiSQL은 테스트 환경이 IP TIME 공유기 환경에서 해봐야 편의성 여부를 알 수 있을 듯 싶다.

보통은 DB 접속을 원격에서 하면 DB에서 원격 접속을 허용해야 하는데 DBeaver는 localhost 접속으로 가능하게 만들었더라. SSH 접속을 통해서 로컬상에서 DB에 접속하는 방법으로 접속을 하는 거 같다.

https://dbeaver.io/ 에서 파일 다운로드 받아서 설치하면 아래와 같은 단계를 거쳐서 DB접속이 가능하다.

DB가 접속된 걸 확인할 수 있다.

설치한 pythondb의 ER 다이어그램을 실행시켜 봤다.

DB 테이블 구조는 https://link2me.tistory.com/2072 에 올려진 테이블 구조를 샘플로 등록했다.

아직 데이터는 하나도 생성하지 않아서 데이터는 없다.

윈도우 뿐만 아니라 MAC에서도 이용할 수 있어 편리한 DB 툴인 거 같다.

ER Diagram 기능은 https://dbeaver.com/docs/wiki/ER-Diagrams/ 를 참조하면 될 듯 싶다.

회원 테이블 예시이다.

비밀번호는 RSA 암호화 비밀번호를 사용하고, SALT 20자를 적용하여 보안을 고려한다.

access 칼럼은 관리자가 userID를 접속을 불허할 것인지, 비밀번호를 5회 이상 틀릴 경우 접속이 차단되도록 설정했다가 일정 시간이 경과하면 자동으로 접속 허용 상태로 변경처리할 것인지 정하는 용도이다.

가입일자와 최근 접속일자를 구분하여 최근에 접속한 날짜를 기록한다.

로그인 실패횟수를 카운트하여 접속 실패횟수 초과 설정값을 넘어가면 접속을 차단하는 로직을 구현하면 된다.

Hash 와 SALT 개념은 https://st-lab.tistory.com/100 에 잘 설명되어 있더라.

코드가 Java 코드 예시이긴 해도 개념 이해하는데 이보다 훌륭한 자료 찾기는 쉽지 않을 듯 싶다.

(예제) 테이블에서 장학금을 받는 학생의 학번과 장학금 내역을 출력하라.

SELECT studentNO, scholarship

FROM tablename

WHERE scholarship > 0;

예제) 장학금을 백만원 이상 지급 받는 학생 중에서 2회 이상 지급받은 학생의 학번과 지급 받는 횟수를 학번 내림 차순으로 출력하라. ( 실행 순서 : FROM 절 → WHERE 절 → GROUP BY 절 → HAVING 절 → SELECT 절 → ORDER BY 절)

SELECT studentNO, count(*)

FROM tablename

WHERE scholarship > 1000000

GROUP BY studentNO

having count(*) > 1

ORDER BY studentNO DESC;

※ GROUP BY 절을 사용할 때에는 count, avg, min, max, sum 과 같은 그룹 함수를 같이 사용해야 한다.

※ WHERE 절은 FROM 절에서 생성된 중간 테이블에서 동작하고,

   HAVING 절은 GROUP BY 절에서 생성된 중간 테이블에서 동작한다.

   SELECT 절은 최종 결과 테이블에 표현될 열을 지정하기 위해서 사용된다.

   HAVING 절의 조건에 있는 수식은 통계 함수를 가질 수 있지만

   WHERE 절에 있는 조건 수식에는 통계함수를 가질 수 없다.

※ 만약 결과 테이블에 서로 다른 테이블로부터 데이터를 가져오려 한다면

   FROM 절에 여러 개의 테이블을 지정해야 한다.

※ FROM 절에 동일한 테이블 이름이 한번 이상 사용될 때는 반드시 가명을 사용해야 한다.

예제) 1990년 이후에 출생한 여학생의 학번, 이름, 주민등록번호를 출력하라

※ substring(id_num,1,2) 라고 하면 앞 2자리를 반환한다. 1부터 시작된다.

SELECT studentNO, userNM, id_num

FROM tablename

WHERE substring(id_num, 8,1) = 2 and birth_year > '1990';

예제) 성별이 남자가 아닌 학생의 학번, 이름을 출력하라.

SELECT studentNO, userNM

FROM tablename

WHERE NOT substring(id_num, 8, 1) = 1;

예제) 나이가 가장 많은 학생을 제외한 나머지 학생의 학번, 이름, 주민등록번호를 출력하다.

SELECT studentNO, userNM, id_num

FROM tablename

WHERE birth_year > any (select birth_year from tablename);

예제) 주민등록번호의 앞 2자리의 값이 서로 다른 숫자를 가지는 것은 몇명인지 출력하라.

SELECT count(distinct substring(id_num,1,2)

FROM tablename;

예제) 학년별, 주야 인원을 출력하라. 순서는 학년별 오름차순, 주야 오름 차순이다.

※ 2개 이상의 열에 대한 그룹화

   count(*) 함수는 서로 다른 모든 행에 대해 연산하지 않고, 각 그룹화된 행에 의존하여 실행된다.

SELECT grade, daynight, count(*)

FROM tablename

GROUP BY grade, daynight

ORDER BY grade, daynight;

예제) 재학중에 납부한 등록금의 전체 납부 금액이 30,000,000 원 이상인 각 학생에 대하여 출력하라.

SELECT studentNO, sum(fee)

FROM tablename

GROUP BY studentNO

having sum(fee) >= 30,000,000;

테이블에서 테이블로 행을 복사

- INSERT INTO 절에 있는 열의 수는 SELECT 절에 있는 수식의 수와 동일해야 한다.

- INSERT INTO 절에 있는 자료형은 SELECT 절에 있는 수식의 자료형과 일치해야 한다.

ALTER TABLE `테이블명` ENGINE = MYISAM;

ALTER TABLE `테이블명` ENGINE = INNODB;

DB가 이상한 것인지는 정확하게 모르겠으나 Lagacy PHP 에서 INNODB 엔진으로 설정한 테이블에 데이터가 저장되지 않는 거 같더라.

localhost에서는 잘 저장되는데, 원격 서버에서 DB 접속할 때 auto commit 이 안되어서 그런지 안되더라.

이것 때문에 원인 찾느라 엄청 삽질을 했다.

PDO 방식의 코드는 기본적으로 INNODB 를 고려한 데이터 Insert, Update가 처리되도록 코드가 구현되어 있는데

Lagacy PHP 에서는 commit 이 자동으로 안되는지 모르겠다.

직접 DB에 접속하여 쿼리를 실행하면 잘 저장되는 걸 확인했기 때문에 원인이 뭘까 이 고민 저고민 하다가

혹시 엔진 때문인가 싶어서 MyISAM 으로 엔진 변경하고 테스트했더니 잘 저장되네.

SUBTIME(start_value, time) 은 start_value 에서 time만큼을 차감한 결과를 돌려준다.

SELECT SUBTIME('2015-08-01 01:30:10','02:50:20') AS subtime
// subtime -> 2015-07-31 22:39:50

SELECT SUBTIME('2015-08-01 01:30:10','2015-08-01 02:50:20') AS subtime
// subtime -> NULL

SELECT SUBTIME('2015-08-01 01:30:10.126587','00:50:20') AS subtime
// subtime -> 2015-08-01 00:39:50.126587

SELECT SUBTIME('2015-08-01 01:30:10.126587','00:50:20.020202') AS subtime
// subtime -> 2015-08-01 00:39:50.106385

SELECT SUBTIME('01:30:10.126587','02:50:20.020202') AS subtime
// subtime -> -01:20:09.893615

SELECT SUBTIME('01:30:10.126587','-02:50:20.020202') AS subtime
// subtime -> 04:20:30.146789

SELECT SUBTIME("10:24:21", "5");

결과는 10:24:16 ("5"는 5초를 의미)

CURDATE() 현재 연-월-일, CURTIME() 현재 시:분:초,

NOW()와 SYSDATE() 현재 연-월-일 시:분:초 출력

현재시간으로 부터 30분까지의 결과를 반환하고자 한다면....

where regdate > subtime(current_timestamp(), '0:30:0') and regdate < current_timestamp()

다른 방법으로

where regdate >= date_add(now(), interval -30 minutes)

where regdate >= date_add(now(), interval -1 day)  // 1일전

MySQL/MariaDB 백업, 복구에 대한 명령어를 적어둔다.

## 모든 DB 백업
mysqldump -uroot -p --all-databases > alldbbackup.sql

## 모든 DB 복구
mysql -u root -p < alldbbackup.sql

## 특정 DB 백업

mysqldump -uroot -p --databases kchart > kchart.sql

# kchart DB에서 특정 테이블 여러개 백업
mysqldump -uroot -p kchart allowHosts > allowHosts.sql
mysqldump -uroot -p kchart allowHosts > allowHosts.sql
mysqldump -uroot -p kchart allowHosts SYS_MEMBER > sys_allow_backup.sql

## DB 접속

mysql -u root -p

// DB 생성
create database kchart default character set utf8;

## root 비밀번호 설정
use mysql;
update user set password=password('설정할root비번') where user='root';
flush privileges;

## DB 사용권한 등록

## username, password, dbname을 각각 상황에 맞게 수정한다.

use mysql;
create user username@localhost identified by 'password';
grant all privileges on dbname.* to username@localhost;
flush privileges;

### 사용자 현황 검색

use mysql;
select host, user, password from user;

## DB 백업본 업로드
mysql -u root -p
비밀번호 입력
use kchart;
source partmember.sql;

테이블 칼럼을 phpMyAdmin 을 이용하여 추가하다보니 명령어를 잘 모르겠다.

그래서 명령어를 추가해 둔다.

칼럼 추가

ALTER TABLE 테이블명
ADD 추가할 컬럼명  데이터 유형;

ADD isDelete TINYINT( 2 ) NOT NULL ,
ADD OStype TINYINT( 2 ) NOT NULL ,
ADD phoneBrand VARCHAR( 20 ) NULL ,
ADD phoneModel VARCHAR( 20 ) NULL ,
ADD level TINYINT( 2 ) NOT NULL DEFAULT '1',
ADD chosung VARCHAR( 10 ) NULL ,
ADD ismobileNO TINYINT( 2 ) NOT NULL DEFAULT '1'

칼럼 변경

ALTER TABLE members

CHANGE isDelete isDelete TINYINT( 2 ) NOT NULL DEFAULT '0'

칼럼 삭제

ALTER TABLE 테이블명
DROP 삭제할 컬럼명;

ALTER TABLE members
  DROP isUpdate,
  DROP isDelete;

특정 칼럼 다음에 필드 추가

ALTER TABLE members ADD isUpdate TINYINT( 2 ) NOT NULL DEFAULT '0' AFTER isGanbu ,
ADD isDelete TINYINT( 2 ) NOT NULL DEFAULT '0' AFTER isUpdate

칼럼 순서 변경

ALTER TABLE members modify column corpID VARCHAR( 12 ) AFTER idx;

phpMyAdmin 에서 테이블명 변경을 했더니 아래와 같은 방식으로 처리가 된다.

CREATE TABLE `helpdesk`.`positionMN` (

MySQL 사용자 권한 부여 방법인데 찾기가 귀찮아서 여기에 적어둔다.

<?php

$DB['host'] = 'localhost';

$DB['name'] = 'kkkope2018';

$DB['user'] = 'kkkope';

$DB['pass'] = '!kkkope@#';

$DB['head'] = 'rb';

$DB['port'] = '3306';

$DB['type'] = 'MyISAM';

//use mysql;

//grant all privileges on kkkope2018.* to kkkope@localhost identified by '!kkkope@#';

//flush privileges;

?>

두개의 테이블에서 불일치하는 데이터를 찾는 SQL 명령으로 idx 는 index를 타는 칼럼이다.

select * from favoriteBook where staffidx not in (select idx from member);

select * from FavoriteBook where staffidx not in (select idx from SYS_MEMBER);

불일치 데이터를 찾아서 지우는 SQL 문

delete from FavoriteBook where staffidx not in (select idx from member);

delete from FavoriteBook where staffidx not in (select idx from SYS_MEMBER);

select * from FavoriteBook where myidx not in (select idx from SYS_MEMBER);
delete from FavoriteBook where myidx not in (select idx from SYS_MEMBER);

누락된 데이터 찾기

SELECT b.fieldB, a.fieldA FROM tableA a RIGHT OUTER JOIN tableB b ON a.fieldA = b.fieldB WHERE a.fieldA IS NULL;

불일치 데이터 찾기

두 테이블의 uid는 서로 같고 특정 필드 업데이트에 따른 불일치가 되는 데이터 찾기

즉 백업 이후에 변경된 데이터 찾기 같은 셈이다.

select a.uid,a.direct,b.direct,a.is_checking,b.is_checking from data_0218 a, data_0220 b where a.uid=b.uid and a.is_direct=5 and a.is_checking=0 and b.is_checking NOT IN(0);

Toad for MySQL 파일을 다운로드하여 설치를 해봤다.

다운로드 :  http://software.dell.com/products/toad-for-mysql/

윈도우에 설치한 AutoSet9 에서 구동하는 MySQL DB 접속을 해보니 접속이 잘된다.

테이블 구조 설계를 위한 Database Diagram 을 할 수 있어서 엄청 편하다.

코딩을 하다보면 테이블 구조 파악이 쉽지 않아서 애를 먹는 경우가 있는데 이걸 이용해서 구조 설계를 해두면 엄청 편리할 거 같다.

SQL 에 대한 개념을 이해하려고 책도 참 여러권 샀다. 다 정독한건 아니고 필요한 곳만 찾아 본것이 많다.

하지만 난 아직 초보다. 프로그래머가 아니라 취미로 배우니 수준이 올라가지 않는다.

SQL 교육을 수강하고 책을 보면서 나름 터득한 걸 적어본다.

나중에 강의하게 되면 설명 쉽게 해줄 생각으로 좀 정리한 자료다.

SQL Aliases
- 칼럼명을 별칭(Aliases)으로 할 수도 있고, 테이블명을 별칭으로 할 수도 있다.

https://www.w3schools.com/sql/sql_join.asp 사이트를 참조하면 개념을 쉽게 이해할 수 있다.

영문이라 처음에는 이해가 잘 안될 수도 있다. 하지만 쿼리문을 계속 사용하다보면 이해가 될 것이다.

무엇이든 배우는데 가장 중요한 것은 개념에 대한 이해다. 개념을 이해하면 응용은 자연스럽게 된다.

select o.OrderID, c.CustomerName 등으로 select 절에 나열(Query 속도상 필요한 칼럼만 뽑는게 좋음)

Orders 테이블의 것만 조회하고 싶다면, select o.* from .... 이라고 하면 된다.

아래 그림의 교집합 안에서 조건에 맞는 걸 또 추출하고 싶다면, Where 조건절을 추가한다.

SELECT o.* FROM Orders o INNER JOIN Customers c ON o.CustomerID=c.CustomerID where o.CustomerID='userid'; // userid 는 PHP 에서 변수로 받은 거라고 보면 됨.

SELF JOIN 은 두개의 물리적인 테이블을 만들어서 원하는 결과를 뽑아내야 하지만, 그럴 필요없이 메모리 공간에 하나의 논리적인 테이블을 만들어서, 물리적인 테이블과 조인하여 결과를 추출하는 것이라고 이해하면 된다.

Update FavoriteBook f INNER JOIN COMMON c ON f.grpidx=c.grpidx SET f.grpNM=c.grpNM where f.grpidx=2;

- FavoriteBook f INNER JOIN COMMON c ON f.grpidx=c.grpidx : 한개의 테이블이라고 생각한다.

- Update 테이블명 SET  WHERE 조건

- 오라클 쿼리방식으로 한다면

  Update FavoriteBook f, COMMON c SET f.grpNM=c.grpNM where f.grpidx=c.grpidx and f.grpidx=2;

순수하게 A만 뽑아주는 쿼리

SELECT a.id, a.name, a.age, b.mobile, b.internet 
FROM a LEFT JOIN b on a.id = b.id 
WHERE b.id is null
order by a.id ASC

MySQL 은 FULL OUTER JOIN 은 없다.

LEFT JOIN 과 RIGHT JOIN 을 UNION 으로 묶어서 처리하면 된다.

간단하게 정리한 것이지만 도움되면 댓글이나 공감 눌러주세요.

MySQL 에서 정규식을 접목하여 검색하면 OR 검색조건 작성이 심플해진다.

<?php
$sql="select * from test where eng regexp '가|나|다|라'";
// 정규식을 접목한 or 검색
$s1="가";
$s2="나";
$s3="다";
$s4="라";
$sql = "select * from test where eng regexp '".$s1."|".$s2."|".$s3."|".$s4."' ";
echo $sql;
?>

select * from test where eng regexp 'do?'; // ?는 마지막 글자가 0 또는 1개 일치 : d 또는 do가 일치
select * from test where name regexp '^..$'; // 시작부터 2글자로 끝나는 이름을 찾아라.

select * from test where eng regexp '^I'; // 첫글자가 I로 시작되는 문장을 찾아라

select * from test where eng regexp '^I\''; // 첫글자가 I'로 시작되는 문장을 찾아라

SELECT * FROM test WHERE col1 REGEXP '^[0-9]+$'; // 숫자로만 된 것을 찾아라

SELECT * FROM test WHERE col1 REGEXP '^[[:digit:]]+$'; // 숫자로만 된 것을 찾아라

SELECT * FROM test WHERE col1 NOT REGEXP '^[0-9]+$'; // 숫자로만 된 것을 제외하고 찾아라

SELECT * FROM test WHERE length(col1) > 0 and col1 NOT REGEXP '^[0-9]+$'; // 숫자 제외하고 찾아라

SELECT * FROM test WHERE col1 REGEXP '[0-9]+'; // 숫자와 문자를 포함하는 것을 찾아라