다른 테이블 SELECT 하여 Table Create 하기

1
2
3
4
CREATE TABLE testTbl5
(    SELECT emp_no, first_name, last_name
      FROM employees.employees
);
cs

 

이렇게 하면 SELECT 한 컬럼 명칭 그대로 테이블이 생성된다.

 

 

원하는 컬럼명칭으로 만들고 싶을 땐 이렇게 하면 된다.

1
2
3
4
5
create TABLE testljy
(
    SELECT id AS jyid, Fname AS jyfname, Lname AS jylname 
    FROM testtbl4
);
cs

AS 를 붙인 후 SELECT 하면 

원하는대로 테이블이 생긴다! 

 

 

다중 행 INSERT 시 기본키가 중복되면 건너뛰게 하기

 

3개의 행을 INSERT 할 때, 1번째행 INSERT 시 무결성에 의해 2,3번째행도 INSERT 가 되지 않을 때,

오류 행은 건너뛰고 2,3번째행을 입력하는 방법이 있다.

 

1
2
3
INSERT IGNORE INTO memberTBL VALUES ('BBK''바비코''미국');
INSERT IGNORE INTO memberTBL VALUES ('SJG''서장훈''서울');
INSERT IGNORE INTO memberTBL VALUES ('HJY''현주엽''경기');
cs

INSERT 뒤 IGNORE 를 붙여주면, 오류가 나던 말던 다음 행을 INSERT 한다.

 

 

 

기본 키 중복 시 수정되도록 하기

위에서는 기본키가 같은 데이터가 있을 때 무시하고 입력하는 것을 했는데,

기본키가 같은 데이터가 있을 때 데이터를 수정하고 싶으면 어떻게 할까?

INSERT INTO 문은 동일하고 ON DUPLICATE KEY UPDATE 컬럼='수정값', 컬럼2='수정값' 이렇게 하면 된다.

 

1
2
3
4
INSERT INTO memberTBL VALUES('BBK''바비킴''미국')
    ON DUPLICATE KEY UPDATE name='빠삐코', addr='미국';

 

INSERT INTO memberTBL VALUES('DJM''동짜몽''일본')
    ON DUPLICATE KEY UPDATE name='동짜몽', addr='일본';
cs

 

>>BBK 라는 키가 이미 입력되어 있을 때, name 을 빠삐코로, addr 을 미국으로 업데이트한다.

>>DJM 라는 키가 이미 입력되어 있을 때, name 을 동짜몽으로, addr 을 일본으로 업데이트한다.

'공부 > My SQL' 카테고리의 다른 글

oracle 하루 이전 날짜 구하기  (0) 2022.05.25
oracle json value 추출  (0) 2022.05.12
WITH AS (CTE)  (0) 2021.03.08
뷰, 스토어드 프로시저, 트리거  (0) 2020.12.12
테이블 생성, 테이블 조회, 인덱스 생성  (0) 2020.12.07

>> AUTO_INCREMENT

테이블의 속성이 AUTO_INCREMENT 로 지정되어 있으면,

INSERT 에서는 해당 열이 없다고 생각하고 입력해도 된다.

 

AUTO_INCREMENT 는 자동으로 1부터 증가하는 값을 입력해주며, 

AUTO_INCREMENT로 지정할 때는 꼭 PRIMARY KEY 또는 UNIQUE 로 지정해주어야 한다.

 

아래는 AUTO_INCREMENT 를 사용하는 예시이다.

 

1
2
3
4
5
CREATE TABLE testtbl2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userName CHAR(3),
    age INT
);
 
cs

AUTO_INCREMENT 를 이용한 id 컬럼을 생성해주고,

 

1
2
3
INSERT INTO testtbl2 VALUES (NULL'길동'25);
INSERT INTO testtbl2 VALUES (NULL'길자'22);
INSERT INTO testtbl2 VALUES (NULL'길순'21);
cs

INSERT 입력시 NULL 로 넣어주면,

id 가 1부터 1씩 증가하여 자동 생성된다.

 

AUTO_INCREMENT 가 어디까지 증가되었는지 확인하려면.

1
SELECT LAST_INSERT_ID();
cs

쿼리를 실행하면 마지막에 입력된 값을 보여준다.

 

 

만약 AUTO_INCREMENT 를 100부터 입력되게 변경해주고 싶다면

1
ALTER TABLE testtbl2 AUTO_INCREMENT=100;
cs

해당 SQL 문을 사용해주면 된다.

 

위의 SQL 문은 AUTO_INCREMENT 가 1씩 증가한다.

만약 1씩 증가하지 않고 다른 증가값을 지정하고 싶다면

서버 변수인 @@auto_increment_increment 변수를 변경시켜야 한다.

 

위 testtbl2 에 증가값을 5로 증가시켜보자.

 

1
SET @@auto_increment_increment = 5;
cs

해당 쿼리 실행 후 아래 INSERT 문을 실행시키면

 

1
2
INSERT INTO testtbl2 values (NULL'민수'5);
INSERT INTO testtbl2 values (NULL'민지'7);
cs

다음과 같은 결과가 반환된다.

> 뷰 생성

 

1
2
3
CREATE VIEW 뷰이름
AS
SELECT * FROM 뷰에 넣을테이블 ;
cs

 

어느 테이블에서 SELECT 해서 뷰이름이라는 VIEW 를 생성한다. (테이블 데이터 그대로 복사된다)

 

 

> 스토어드 프로시저

스토어드 프로시저란 여러 SQL 문을 하나로 묶어서 편리하게 사용하는 기능이다.

SQL 뿐만 아니라 다른 프로그래밍 언어와 같은 기능을 담당할 수도 있다.

1
2
3
4
5
CREATE PROCEDURE 프로시져이름()
BEGIN
    SELECT * FROM 테이블;
    SELECT * FROM 테이블2;
END
cs

BEGIN 과 END 사이에 있는 쿼리문으로 프로시저를 생성한다.

 

프로시저 호출은 

1
CALL 프로시져이름();
cs

위와 같이 CALL 을 써서 호출한다.

 

> 트리거

트리거란 테이블에 부착되어 테이블에 INSERT나 UPDATE 또는 DELETED 작업이 발생되면 실행되는 코드이다.

간단한 트리거 예시

 

1
2
3
4
5
6
7
8
9
10
DELIMITER //
CREATE TRIGGER trg_deletedMemberTBL -- 트리거 이름
    AFTER DELETE -- 삭제 후에 작동하게 지정
    ON memberTBL -- 테이블명
    FOR EACH ROW -- 각 행마다 적용시킴
 BEGIN
    INSERT INTO deletedMemberTBL
         VALUES    (OLD.memberID, OLD.memberName, OLD.memberAddress, CURDATE() );
 END //
 DELIMITER ;

 

trg_deletedMemberTBL 이라는 트리거를 생성한다.

이 트리거는 memberTBL 에서 데이터가 삭제될때마다 실행된다.

memberTBL 에서 데이터가 삭제되면 이 데이터를 deletedMemberTBL 테이블에 Insert 한다.

 

 

> 시작하기

MySQL 워크벤치 실행 후 왼쪽 스키마를 더블클릭 후 실행할 쿼리를 입력해야 한다.

 

 

 

> 테이블 생성

스키마 > Tables > Create Table 후 생성

혹은

1
2
3
4
5
6
7
8
9
CREATE TABLE `테이블이름`  (
 
`컬럼명1` CHAR(8NOT NULL,
 
`컬럼명2` INT ,
 
`컬럼명3` VARCHAR(10NOT NULL,
 
PRIMARY KEY (`컬럼명1`));
cs

쿼리 작성 후 실행해주면 된다.

 

쿼리 실행은 Ctrl + Enter !!! (문단 별로)
화면에 작성한 모든 쿼리 실행은 Ctrl + Shift + Enter  !!!

 

여기서 컬럼명 앞뒤에 ` 문자가 있는데, 이는 엔터 왼쪽에 있는 ' 가 아니라 숫자 1 왼쪽에 있는 ` 이다.

명칭은 백틱(backtick) , 오라클과는 다르다 (오라클은 ' 로 사용했다.)

 

> 조회

 

1
select * from 테이블명 where `컬럼명` = '원하는값';
cs

간단한 SELECT 쿼리, 컬럼명은 ` 로 감싸주어어야 하며 값은 ' 로 감싸주면 된다.

 

 

> 인덱스 생성

인덱스는 "찾아보기" 이며 인덱스가 없는 데이터 조회는 목차 없는 책과 같다 했다. (책을 전부 뒤져보아야 한다)인덱스를 적절히 활용하면 시스템 성능이 몇 배, 몇 십 배 이상까지 차이가 난다고 한다.

 

 

1
CREATE INDEX idx_TABLE_primarykey ON TABLE(primarykey);
cs

TABLE 테이블에 primarykey 컬럼을 활용한 idx_TABLE_primarykey 라는 인덱스를 생성할 것이다.

인덱스 이름은 어느 테이블의 어느 열에 설정된 인덱스인지를 알 수 있도록 지정해주는것이 좋다.

'공부 > My SQL' 카테고리의 다른 글

oracle 하루 이전 날짜 구하기  (0) 2022.05.25
oracle json value 추출  (0) 2022.05.12
WITH AS (CTE)  (0) 2021.03.08
Select Create Table, Insert Ignore INTO.., ON DUPLICATE KEY  (0) 2021.03.02
뷰, 스토어드 프로시저, 트리거  (0) 2020.12.12

+ Recent posts