기본키의 값을 ABC00001와 같이 앞자리는 문자열 뒷자리는 숫자로 하고, 숫자는 자동 증가로 할 때 사용하는 방법입니다.


table1_seq 테이블 : 임시 테이블로 기본키만 있으며, 이 테이블의 기본키 값이 정수부분에 해당함

table1 테이블 : 실제 테이블로 ABC00001값이 들어갈 테이블

tg_table1_insert : 트리거로 table1에 insert 작업을 하기 전에 동작하는 트리거

LAST_INSERT_ID() : 가장 마지막에 insert 작업이 실행된 테이블의 기본키 값을 반환하는 함수)

LPAD(값, 자리수, 'A') : 값이 지정된 자리수보다 작은 경우 남은 자리를 A로 채워주는 함수


테이블 생성 샘플 코드

1
2
3
4
5
6
7
8
CREATE TABLE table1_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE table1
(
  id VARCHAR(8) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
);

cs


트리거 샘플 코드

1
2
3
4
5
6
7
8
9
DELIMITER $$
CREATE TRIGGER tg_table1_insert
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT('ABC', LPAD(LAST_INSERT_ID(), 5, '0'));
END$$
DELIMITER ;

cs

table1에 insert 작업 샘플 코드

INSERT INTO Table1 (name) VALUES ('Jhon'), ('Mark');


출처 : stackoverflow(바로가기)

저작자표시 비영리

 - 설치파일 다운로드 하기(링크)

 - 오라클 사이트에 처음 접속하는 경우 아래와 같은 창이 뜨는데 모든쿠키수락을 클릭하면 된다. 

 - MySQL Installer for Windows를 클릭

 - 설치파일 실행 후 설치 진행(이후 과정을 상세히 보려면 아래 더보기 버튼 클릭)

 - root 계정 설정 및 사용자 계정 설정

 - 사용자 계정 추가 과정을 상세히 보려면 아래 더보기 버튼 클릭

 - root 계정으로 서버 연결 테스트

 - 이후 과정을 상세히 보려면 아래 더보기 버튼 클릭

설치완료!

저작자표시 비영리

MySQL 프로시저

  • 프로시저는 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

  • 프로시저는 다음과 같은 장점이 있다.

    1. 하나의 요청으로 여러 SQL문을 실행할 수 있다.(네트워크에 대한 부하를 줄일 수 있다.)
    2. 미리 구문 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.
    3. 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조무결성 유지가 가능하게 된다. 간단히 말하면 응용 프로그램 측 로직을 가지지 않고도 데이터베이스의 데이터 앞뒤가 맞게 될 수 있다.
    4. 보수성이 뛰어 나다.

  • 프로시저는 다음과 같은 단점이 있다.

    1. 코드 자산으로서의 재사용성이 나쁘다.
    2. 업무의 사양 변경 시 외부 응용 프로그램과 할께 프로시저의 정의를 변경할 필요가 있다.
 

MySQL 프로시저 목록 확인

mysql> show procedure status;
 

MySQL 프로시저 스크립트 확인방법

mysql> show create procedure 프로시저명;
 

MySQL 프로시저 정의

DROP PROCEDURE IF EXISTS procedure_name; -- 이미 프로시저가 정의 되어 있다면 삭제 
DELIMITER // --//대신 다른 문자로 대체 가능 
CREATE PROCEDURE procedure_name 
([ 
    [ IN | OUT ] parameter_name 
    { parameter_type | ARRAY OF parameter_type }, ... 
]) 
[ DECLARE variable_declaration;...[;] ] 
BEGIN 
    procedure_body_statement;...[;] 
END // 
DELIMITER ;
 
  
  • 예시 : 유저 정보 확인하는 프로시저
DROP PROCEDURE IF EXISTS search_user; 
DELIMITER // 
CREATE PROCEDURE search_user() 
BEGIN 
    SELECT USER,HOST FROM MYSQL.USER; 
END // 
DELIMITER ;
  
  • 예시 : 강의를 수강하는 학생 정수를 입력시 학점을 계산하는 프로시저
DROP TABLE IF EXISTS course; 
-- course 테이블 생성 
CREATE TABLE `course` ( 
    `course_num` int(11) NOT NULL AUTO_INCREMENT COMMENT '수강번호', 
    `course_student_num` int(11) DEFAULT NULL COMMENT '학번', 
    `course_class_num` int(11) DEFAULT NULL COMMENT '강의번호', 
    `course_mid` double DEFAULT NULL COMMENT '중간성적', 
    `course_end` double DEFAULT NULL COMMENT '기말성적',
    `course_attendance` int(11) DEFAULT NULL COMMENT '출석점수', 
    `course_report` double DEFAULT NULL COMMENT '과제점수', 
    `course_total` double DEFAULT NULL COMMENT '총점', 
    `course_grade` varchar(2) NOT NULL DEFAULT 'F', 
    PRIMARY KEY (`course_num`) ) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='수강';
  
DROP PROCEDURE IF EXISTS calculate_grade; 
DELIMITER // 
CREATE PROCEDURE calculate_grade( 
    IN in_mid DOUBLE, 
    IN in_end DOUBLE, 
    IN in_att INT, 
    IN in_rep INT, 
    IN in_class_num INT, 
    IN in_student_num INT ) 
BEGIN 
    DECLARE total DOUBLE DEFAULT 0; 
    DECLARE grade VARCHAR(2); 
    SET total = in_mid + in_end + in_att + in_rep; 
    IF total >= 95 AND total <= 100 THEN 
        SET grade = 'A+'; 
    END IF; 
    IF total >=90 AND total < 95 THEN 
        SET grade = 'A'; 
    END IF; 
    IF total >=85 AND total < 90 THEN 
        SET grade = 'B+'; 
    END IF; 
    IF total >=80 AND total < 85 THEN 
        SET grade = 'B'; 
    END IF; 
    IF total >=75 AND total < 80 THEN 
        SET grade = 'C+'; 
    END IF; 
    IF total >=70 AND total < 75 THEN 
        SET grade = 'C'; 
    END IF; 
    IF total >=65 AND total < 70 THEN 
        SET grade = 'D+'; 
    END IF; 
    IF total >=60 AND total < 65 THEN 
        SET grade = 'D'; 
    END IF; 
    IF total >=0 AND total < 60 THEN 
        SET grade = 'F'; 
    END IF; 
 
    UPDATE university.course 
        SET 
            course_mid = in_mid, 
            course_end = in_end, 
            course_report = in_rep, 
            course_attendance = in_att, 
            course_total = total, 
            course_grade = grade 
        WHERE course_student_num = in_student_num AND course_class_num = in_class_num AND course_num >= 1; 
END // 
DELIMITER ;
 
  
 

프로시저 호출

CALL 프로시저명(매개변수들);
  • 예시 : 학번이 2019160160이고 수강 과목 번호가 1번인 데이터가 있다고 가정
CALL calculate_grade(40.0, 30.0, 9,9,1,2019160160); SELECT * FROM course;

프로시저 실행 전
프로시저 실행 후

용어 정리

DELIMITER

  • 프로시저나 트리거에서 사용된다.
  • 프로시저를 생성할 때 프로시저 안에 있는 쿼리들이 ;으로 인해 실행되면 안되기 때문에 이를 막기 위해 DELIMITER를 이용하여 DELIMITER를 이용하여 지정된 문자가 나타나기 전까지는 ;을 만나도 실행되지 않게 막아준다.

IN

  • 프로시저를 호출하기 위해 필요한 정보들로 함수의 매개변수(인자)에 해당한다.

DECLARE

  • 프로시저 내부에서 사용하는 변수를 선언할 때 사용한다.

SET

  • 변수의 값을 설정할 때 사용한다.

IF 조건식 THEN 실행문 ELSE 실행문 END IF;

  • if문에 해당하며 유의사항으로 else if문을 처리할 땐 다음과 같이 처리해야 한다.

IF total >= 95 AND total <= 100 THEN 
    SET grade = 'A+'; 
ELSE 
    IF total >= 90 THEN 
        SET grade = 'A'; 
    END IF; 
END IF;
  

MySQL 기본 명령어 정리

데이터베이스 접속

  • cmd 창에서

  • mysql -u 사용자명 -p

데이터베이스 리스트 확인

  • mysql> SHOW DATABASES;

데이터베이스 사용

  • mysql> USE 데이터베이스명;

테이블 리스트 확인

  • mysql> SHOW TABLES;

사용자 확인

  • mysql> USE mysql;
mysql> SELECT user, host FROM user;

사용자 권한 확인

  • mysql> SHOW GRANTS FOR '아이디'@'%';

사용자 추가

  • mysql> CREATE USER '아이디'@localhost IDENTIFIED BY '비밀번호';

사용자 삭제

  • mysql> DELETE FROM USER WHERE USER='username';

MySQL 버전 확인

  • mysql> SHOW VARIABLES LIKE "%version%";

쿼리 결과 세로로 보기

  • cmd창에서 mysql을 접속할 때 -E 옵션을 이용하여 접속

  • mysql -E -u root -p

현재 상태 확인하기

  • mysql> STATUS;

테이블 정보 확인하기

  • mysql> EXPLAIN 테이블명;

+ Recent posts

티스토리툴바