2014/08/13 - [DB/MSSQL] - MS-SQL SELECT 쿼리로 한꺼번에 다른 테이블에 INSERT 하기 (INSERT ... SELECT ... FROM ...)
이번에는 기존에 데이터가 쌓여있는 테이블의 존재하는 모든 데이터를 일괄로 신규로 생성한 테이블에 옮기고자 할때 INSERT ... SELECT ... FROM 을 이용하여 데이터를 이관할 수 있다.
우선 2가지 테이블이 존재한다고 가정하자
* TEMP_TABLE (기존에 데이터가 쌓여있는 테이블)
create table temp_table( id varchar(10), title varchar(20), flag varchar(1) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
* TEMP_TABLE에 들어갈 가상의 데이터 INSERT
insert into temp_table(id,title,flag) values('1','제목1','Y'); insert into temp_table(id,title,flag) values('2','제목2','N'); insert into temp_table(id,title,flag) values('3','제목3','Y'); insert into temp_table(id,title,flag) values('4','제목4','Y'); insert into temp_table(id,title,flag) values('5','제목5','N'); insert into temp_table(id,title,flag) values('6','제목6','Y'); insert into temp_table(id,title,flag) values('7','제목7','Y'); insert into temp_table(id,title,flag) values('8','제목8','N'); insert into temp_table(id,title,flag) values('9','제목9','Y');
* REAL_TABLE (신규로 특정 컬럼만 이관하기 위한 테이블)
create table real_table( id varchar(10), title varchar(20) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
※ 위와같이 2개의 테이블 생성한 후 temp_table에만 데이터를 넣어놓은 상태이다
temp_table -> real_table에 id,title 컬럼만 일괄로 넣고자 한다.
하지만 조건으로 flag값이 'N'인 데이터만 넣고자 한다.
그렇다면 결론적으로 real_table에 들어가야 하는 데이터들은
id값이 2,5,8 인 데이터들만 들어가야 할 것이다 .
* INSERT ... SELECT ... FROM 실행
INSERT real_table(id,title) SELECT id,title FROM temp_table WHERE flag = 'N'
* 실행 결과
위와 같이 정상적으로 temp_table의 데이터 중 flag값이 'N' 인 3개의 데이터들이 id,title값만 real_table 테이블에 데이터가 제대로 들어가는 것을 확인 할 수 있다.
도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!
MySQL - 널체크(IFNULL 사용하기) (0) | 2014.08.23 |
---|---|
MySQL LIMIT를 사용하여 페이징쿼리를 동작시켜보자 (0) | 2014.08.15 |
MySQL 인코딩 UTF-8 설정하기 [한글깨짐문제 해결] (7) | 2014.08.12 |
MySQL 시퀀스 auto_increment 생성 및 초기화하기 (0) | 2014.08.11 |
MySQL 대소문자 구분 안하기 - lower_case_table_names 변경 (7) | 2014.07.10 |
2014/08/05 - [서버에웹환경구축하기/5.마리아DB(MariaDB) 설치] - [서버구축 - 11] 서버에 MySQL과 동일한 마리아DB(MariaDB) 설치하기
2014/08/05 - [서버에웹환경구축하기/5.마리아DB(MariaDB) 설치] - [서버구축 - 12] 마리아DB(MariaDB) 인코딩 UTF-8 설정하기
mysql 설치 시 UTF-8 환경으로 default 설치 되는 경우도 있지만, 본인처럼 한글이 깨지는 현상 발생시 mysql 환경을 utf-8으로 맞춰주는 포스팅을 진행하고자 한다.
대부분 mysql 기본 설치 시, 인코딩 타입이 latin(?) 타입으로 주로 설치되는것을 확인 할 수 있었다.
다음 명령어를 통하여 본인의 mysql 문자 인코딩을 확인하도록 하자
SHOW VARIABLES LIKE 'c%'
* 문자 인코딩 목록확인
확인결과 system 부분만 utf8이고 그외에는 latin1으로 인코딩 되어있다.
다음 파일을 수정하여 utf8 환경으로 변경 하도록 하자
윈도우일경우 my.ini
리눅스일경우 my.cnf
파일을 찾아서 각각 위치에 맞추어 추가를 해주도록 하자
본인은 local환경이 윈도우에 APM 파일을 설치하여 확인한 결과이다.
100% 되는방법이라고 장담은 못한다. 보편적인 방법을 설명하는 것 뿐이다.
본인의 경우, 윈도우/리눅스 MySQL 설치 후, 인코딩 설정시에는 적용이 되서 포스팅을 한것임.
* my.ini 또는 my.cnf 코드 추가
[client] . . #하단추가 default-character-set = utf8 [mysqld] . . #하단추가 init_connect="SET collation_connection = utf8_general_ci" init_connect="SET NAMES utf8" default-character-set = utf8 character-set-server = utf8 collation-server = utf8_general_ci [mysqldump] . . #하단추가 default-character-set = utf8 [mysql] . . #하단추가 default-character-set = utf8
데몬 재실행 후 다시한번 인코딩 확인 명령어를 찍어서 확인해보도록 하자
* 문자 인코딩 목록 재확인(1)
위와같이 대부분 utf8로 잡혔다 하지만 database 부분에 대해서는 latin1이 여전히 적용되있었다 .
아마 기존에 신규 생성된 database가 latin1으로 생성되서 그런거 같다.
그래서 신규 생성했던 2가지 database 를 제거 후 다시한번 확인 해보았다.
* 문자 인코딩 목록 재확인(2)
확인결과 정상적으로 UTF-8 로 인코딩 타입이 적용되었다.
도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!
MySQL LIMIT를 사용하여 페이징쿼리를 동작시켜보자 (0) | 2014.08.15 |
---|---|
MySQL SELECT 쿼리로 한꺼번에 다른 테이블에 INSERT 하기 (INSERT ... SELECT ... FROM ...) (0) | 2014.08.12 |
MySQL 시퀀스 auto_increment 생성 및 초기화하기 (0) | 2014.08.11 |
MySQL 대소문자 구분 안하기 - lower_case_table_names 변경 (7) | 2014.07.10 |
MySQL GROUP CONCAT 이용하여 쿼리목록을 컬럼 하나로 보여주기 (세로를 가로로) (0) | 2014.07.09 |
2014/08/11 - [DB/MSSQL] - MS-SQL 시퀀스 identity 생성 및 초기화하기
2014/08/11 - [DB/Oracle] - 오라클(Oracle) sequence 생성, 등록, 최종 시퀀스값 조회하기
1. MySQL 시퀀스 생성하기
CREATE TABLE test_board( seq INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(20) NOT NULL ); INSERT INTO test_board(title) VALUES('hello'); INSERT INTO test_board(title) VALUES('hello2'); INSERT INTO test_board(title) VALUES('hello3');
위와같이 실행후 SELECT문 실행
확인결과 title 컬럼에만 데이터를 insert 하였는데 seq 컬럼은 자동적으로 값이 증가 하여
insert 되는것을 확인 할 수 있었다.
위와같이 자동으로 컬럼의 숫자값을 일정하게 증가하고 싶다면 auto_increment 를 시퀀스로 사용할 컬럼에 지정을 해주면 된다.
그럼 이제 모든 데이터를 지우고 다시 같은 insert 쿼리를 동작 후 똑같이 조회를 해보도록 하자
delete from test_board; INSERT INTO test_board(title) VALUES('hello'); INSERT INTO test_board(title) VALUES('hello2'); INSERT INTO test_board(title) VALUES('hello3');
데이터를 지우고 다시 같은 내용의 insert 구문을 넣어주었다
다시한번 데이터 조회를 해보도록 하자
위와 같이 seq가 지워진 시퀀스 값에 이어서 자동 증가하는 현상이 발생하였다.
물론 중복이 되지 않으므로 상관은 없으나 모든 데이터를 삭제 후 시퀀스값을 초기화 하길 원할때가
있으므로 시퀀스를 초기화 해주기로 해보자
2. MySQL 시퀀스 초기화
시퀀스 초기화 하고자 할 경우는 하단의 명령어를 주면 간단하게 시퀀스값이 초기화가 된다.
alter table test_board auto_increment=1;
위와같이 alter table 후 test_board(시퀀스컬럼을 초기화 하고자 하는 테이블명) 을 지정 후 auto_increment=1(자동증가값 update를 해준다)
그럼 초기화가 정상적으로 되는지 임의의 시나리오를 작성하도록 하자
1) test_board의 데이터 모두 delete
2) 시퀀스 초기화 작업(alter table xxx auto_increment=x)
3) 다시한번 insert 쿼리
4) select 구문을 통화 데이터 확인
위와같이 시나리오를 실행한 결과 다음과 같이 시퀀스 컬럼이 초기화 된 후,
자동증가하여 컬럼에 추가되는것을 확인 할 수 있었다.
도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!
MySQL LIMIT를 사용하여 페이징쿼리를 동작시켜보자 (0) | 2014.08.15 |
---|---|
MySQL SELECT 쿼리로 한꺼번에 다른 테이블에 INSERT 하기 (INSERT ... SELECT ... FROM ...) (0) | 2014.08.12 |
MySQL 인코딩 UTF-8 설정하기 [한글깨짐문제 해결] (7) | 2014.08.12 |
MySQL 대소문자 구분 안하기 - lower_case_table_names 변경 (7) | 2014.07.10 |
MySQL GROUP CONCAT 이용하여 쿼리목록을 컬럼 하나로 보여주기 (세로를 가로로) (0) | 2014.07.09 |
MySQL로 간혹가다 작업 도중 분명히 테이블이 존재하는데
Table '테이블명' doesn't exist
라는 문구가 나올때가 존재한다.
윈도우에서는 대부분 대소문자 구분없이 설치가 되지만,
간혹가다가 리눅스용 MySQL은 대소문자 구분하는 형식으로 설치가 되있는 경우가 있다 .
show variables like 'lower_case_table_names';
다음과 같은 명령을 실행하고 확인 하였을 경우
lower_case_table_names의 값이 0인지 1인지 2인지를 확인해야 한다.
본인의 테스트 서버는 0이어서 오류가 났었던 것이다.
보통 2는본적이 없어서 모르겠고 0 또는 1인것을 보았었다.
0일 경우 : 대소문자 구분함
1일 경우 : 대소문자 구분안함
본인은 대소문자 구분을 하지 않기위하여 lower_case_table_names의 값을 1로 설정을 해주고자 한다.
MySQL의 my.cnf 파일을 건드려 주어야 하는데
보통은 /etc/my.cnf에 존재한다.
vi로 my.cnf 파일을 보도록 하자
접속하다보면 [mysqld] << 이영역이 있을텐데
영역의 맨밑에
lower_case_table_names = 1
라고 설정을 해주도록 하자
비교를 하기 위해 한줄 내려서 작성하였다.
붙여서 상관은 없다
위치는 [mysqld] < 밑에 나열된 명령어들 그리고 다음 영역의 [] 가 나오기 전에만 작성해 주면
되겠다.
저장하고 나온다음에 mysqld 서비스를 재시작 해주었다.
쿼리에서 다시 명령어를 실행하여 대소문자 구분값이 변경되었는지 확인해보자
본인은 1로 잘 변경이 되었다.
한번 다시 아까 오류난 쿼리를 재실행 해보도록 하겠다.
정상적으로 소문자 테이블인대 대문자로 명령어를 실행해도 잘나온다
유의하도록 하자...
도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!
MySQL LIMIT를 사용하여 페이징쿼리를 동작시켜보자 (0) | 2014.08.15 |
---|---|
MySQL SELECT 쿼리로 한꺼번에 다른 테이블에 INSERT 하기 (INSERT ... SELECT ... FROM ...) (0) | 2014.08.12 |
MySQL 인코딩 UTF-8 설정하기 [한글깨짐문제 해결] (7) | 2014.08.12 |
MySQL 시퀀스 auto_increment 생성 및 초기화하기 (0) | 2014.08.11 |
MySQL GROUP CONCAT 이용하여 쿼리목록을 컬럼 하나로 보여주기 (세로를 가로로) (0) | 2014.07.09 |
MySQL 세로로 뽑힌 데이터를 가로로 뽑을 수 있는 함수가 제공된다.
MySQL 5.0.19? 버전이상에 지원이 되는듯 함.
SELECT * FROM (SELECT 'TEST1' test UNION SELECT 'TEST2' test UNION SELECT 'TEST3' test UNION SELECT 'TEST4' test) TESTINFO
위와같이 가상의 목록 데이터가 존재한다고 하자
그럼 다음처럼 데이터가 출력될것이다.
이 세로형 데이터를 하나의 컬럼에 세미콜론(,)으로 구분을 주어 가로형 데이터로 출력을 해보도록 하겠다.
사용 function은
GROUP_CONCAT(컬럼명 SEPARATOR ',')
상단에 가로로 묶어줄 컬럼명은 test이므로
SELECT GROUP_CONCAT(test SEPARATOR ',') testGrp FROM (SELECT 'TEST1' test UNION SELECT 'TEST2' test UNION SELECT 'TEST3' test UNION SELECT 'TEST4' test) TESTINFO
이런식으로 GROUP_CONCAT() 함수를 사용해주게 된다면
다음과 같이 컬럼하나에 리스트 목록데이터들이 구분자로하여금 모두 들어가있는것을 확인 할 수 있을것이다.
GROUP_CONCAT 괜추한듯함.
도움이 되셨다면 공감클릭! 궁금하신점은 댓글!!
MySQL LIMIT를 사용하여 페이징쿼리를 동작시켜보자 (0) | 2014.08.15 |
---|---|
MySQL SELECT 쿼리로 한꺼번에 다른 테이블에 INSERT 하기 (INSERT ... SELECT ... FROM ...) (0) | 2014.08.12 |
MySQL 인코딩 UTF-8 설정하기 [한글깨짐문제 해결] (7) | 2014.08.12 |
MySQL 시퀀스 auto_increment 생성 및 초기화하기 (0) | 2014.08.11 |
MySQL 대소문자 구분 안하기 - lower_case_table_names 변경 (7) | 2014.07.10 |