이번주도 화이팅!! 🎈✨✨
테이블 명은 가급적이면 대소문자를 구분해서 쓰자!
SQL에서는 구분하지 않지만, 나중에 리눅스에서 돌릴 것도 생각한다면 가급적 대소문자를 구분하는 것이 좋음
UNSIGNED [언사인드]
테이블 설정중 하나로 음수값을 제한한다는 의미로 사용된다.
CREATE TABLE `Code` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
)DECIMAL [디시멀]
고정 소수점 타입(fixed-point types)
MySQL에서 고정 소수점 타입인 DECIMAL은 실수의 값을 정확하게 표현하기 위해 사용됩니다.
DECIMAL에서 사용하는 고정 소수점 방식은 실수를 표현할 때 소수부의 자릿수를 고정하여 표현합니다.
즉, 소수부의 자릿수를 미리 정해 놓고, 고정된 자릿수로만 소수 부분을 표현하는 방식입니다.
MySQL에서 DECIMAL 타입은 NUMERIC을 구현하여 만들어졌습니다.
따라서 대부분의 경우 DECIMAL 대신 NUMERIC을 사용해도 똑같이 동작합니다.
문법
DECIMAL(M,D)
M은 소수 부분을 포함한 실수의 총 자릿수를 나타내며, 최댓값은 65입니다.
D는 소수 부분의 자릿수를 나타내며, D가 0이면 소수 부분을 가지지 않습니다.
테이블 수정은 무조건 MODIFY! 수정은 ADD인줄 알았는데 그게 아니고
수정 범위가 테이블인지 컬럼인지에 따라 다르다고 한다.
ADD : 테이블과 관 INDEX KEY를 추가할때, CHECK를 추가할때 사용한다.
MODIFY : NOT NULL을 추가할 때, 데이터 타입, 컬럼명을 수정할때 사용한다.
저장 프로시저 (스토어드 프로시져)
스토어드 프로시져 안에 변수선언, 할당을 하면서 쿼리문을 돌려서 하나의 메서드와 같은 기능을 만들어 둔다.
목적은 속도가 빠르게 되고, 보안에 좋다.
자 이제부터 저장프로시저 안에 사용될수 있는 기능들에 대한 설명이다.
1. 프로시저 생성
delimiter 구분자를 ~로 하겠다 설정하는 함수.
delimiter 이후 구분자는 ~가 된다.
기본 타입은 아래와 같다.
delimiter //
CREATE PROCEDURE helloworld()
BEGIN
END
//
delimiter ;코드를 작성하면 아래와 같다.
delimiter //
CREATE PROCEDURE helloworld()
BEGIN
DECLARE str VARCHAR(30); -- 지역변수 선언
SET str = 'Hello, world'; --변수의 할당
SELECT str;
END
//
delimiter ;이렇게 한다음에 쿼리를 실행할꺼면 구분자를 ;로 하겠다고 delimiter ; 를 써줘야 한다.
그리고 실행하면 db목록중에 스토어드 프로시져가 생성된다.
2. 연산자 사용
mysql>SET @var1 = 2+6;
mysql>SET @var2 := @var1-2;
mysql>SELECT @var1, @var2;
@var1 @var2
----------------------
8 6
SET @v1 = X'41';
x로 시작하면 16진수라는 의미
SET @v1 = b'1000001';
b로 시작하면 바이너리 라는 의미
3. 전역함수 & 지역함수
DECLARE @변수명
DECLARE [디클리어] - 변수를 선언하는 함수
변수명 앞에 @가 있으면 전역함수 @가 없으면 지역함수이다.
초기화를 무조건 해야하는게 전역함수이다.
1. 변수명은 영숫자 문자, ., _ $ 및 로 구성될 수 있다.
2. @mark or @Mark 같다.
3. 변수이름에는 특수문자 즉 !, #, ^, -, 등...이 포함될 수 있다 .
-단, 인용부호로 묶어야 한다.
-@’var@1′ or @”var^2″ or @`var3`(백틱)
4. DEFAULT 값은 SET을 하지 않는 경우 무조건 해줘야한다.
if문
DELIMITER //
CREATE PROCEDURE sungjukmgmt()
BEGIN
DECLARE hakbun CHAR(6);
DECLARE irum VARCHAR(20);
DECLARE kor, eng, mat, tot INT DEFAULT 0;
DECLARE average DECIMAL(5,2) DEFAULT 0.00;
DECLARE grade CHAR(1) DEFAULT 'F';
DECLARE str VARCHAR(100);
SET hakbun = '20-001';
SET irum = '백두산' ;
SET kor = 78, eng = 89, mat = 99;
SET tot = kor + eng + mat;
SET average = tot / 3;
IF average >= 90 THEN SET grade = 'A';
ELSEIF average >= 80 THEN SET grade = 'B';
ELSEIF average >= 60 THEN SET grade = 'C';
ELSE SET grade = 'F';
END IF;
SET str = CONCAT('학번 ==> ', hakbun);
SET str = CONCAT(str, '이름 ==> ', irum);
SET str = CONCAT(str, '국어 ==> ', kor);
SET str = CONCAT(str, '수학 ==> ', mat);
SET str = CONCAT(str, '총점 ==> ', tot);
SET str = CONCAT(str, '평균 ==> ', average);
SET str = CONCAT(str, '평점 ==> ', grade);
SELECT str;
END
//
DELIMITER ;
case문
DELIMITER //
CREATE PROCEDURE sungjukmgmt()
BEGIN
DECLARE hakbun CHAR(6);
DECLARE irum VARCHAR(20);
DECLARE kor, eng, mat, tot INT DEFAULT 0;
DECLARE average DECIMAL(5,2) DEFAULT 0.00;
DECLARE grade CHAR(1) DEFAULT 'F';
DECLARE str VARCHAR(100);
SET hakbun = '20-001';
SET irum = '백두산' ;
SET kor = 78, eng = 89, mat = 99;
SET tot = kor + eng + mat;
SET average = tot / 3;
CASE
WHEN average >= 90 THEN SET grade = 'A';
WHEN average >= 80 THEN SET grade = 'B';
WHEN average >= 60 THEN SET grade = 'C';
ELSE SET grade = 'F';
END CASE;
SET str = CONCAT('학번 ==> ', hakbun);
SET str = CONCAT(str, '이름 ==> ', irum);
SET str = CONCAT(str, '국어 ==> ', kor);
SET str = CONCAT(str, '수학 ==> ', mat);
SET str = CONCAT(str, '총점 ==> ', tot);
SET str = CONCAT(str, '평균 ==> ', average);
SET str = CONCAT(str, '평점 ==> ', grade);
SELECT str;
END
//
DELIMITER ;. 프로시져를 호출
CALL helloworld();
이러한 결과가 나온다.
프로시저 지우기
DROP PROCEDURE helloworld;SHOW VARIABLES;
모든 시스템변수 확인 가능
-> 그런데 엄청 많기 때문에 "SELECT @@sort_buffer_size"로 검색하면 필터 검색이 가능하다.
내가 만든 프로시저 확인하기
DESC INFORMATION_SCHEMA.ROUTINES;
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE specific_name = '프로시저 이름';
변수의 값을 프로시저에 넣어서 작동시키는 법 - IN
IN 매개변수
예시1
DELIMITER //
-- in위치에 데이를 넣으건지 뺄껀지 정해서 넣어라/생략하면 in임
CREATE PROCEDURE teat_proc_in(IN v_anme VARCHAR(100))
BEGIN
SELECT CONCAT('my name is ', v_anme);
END
//
DELIMITER;
CALL teat_proc_in('한라산');
-- my name is 한라산
예시2
-- 입력 매개변수러 데이터를 변경할 경우 해당 컬럼의 데이터 타입과 같아야한다.
DELIMITER //
CREATE PROCEDURE emp_sal_update(
IN v_empno SMALLINT(4), IN v_sal FLOAT(7,2)
)
BEGIN
UPDATE emp
SET sal = v_sal
WHERE EMPNO = v_empno;
COMMIT;
-- 성공시 update success가 출력되게 한다.
SELECT 'update success';
END
//
DELIMITER ;
CALL emp_sal_update(7369, 1000);프로시저에서 변수의 값을 가지고 나오는 법 - OUT
DELIMITER //
CREATE PROCEDURE test_proc_out(OUT v_string VARCHAR(100))
BEGIN
DECLARE str VARCHAR(100);
SET str = CONCAT('내이름은','백두산');
SELECT str INTO v_string;
END
//
DELIMITER ;
CALL test_proc_out(@p_string);
SELECT @p_string;
- 자바에서는 두개이상의 데이터를 리턴할때, 배열이나 컬렉션을 쓰는데 sql에서는 여러개의 데이터를 프로시저에서 반환할 수 있다.,
IN/OUT둘다 이용하는 예제
-- 사원번로 00의 월급과 이름은?
DELIMITER //
CREATE PROCEDURE sp_emp_select
(
IN v_EMPNO SMALLINT(4),
OUT v_ENAME VARCHAR(10),
OUT v_SAL FLOAT(7,2)
)
BEGIN
SELECT SAL, ENAME INTO v_SAL, v_ENAME
FROM emp
WHERE EMPNO = v_EMPNO;
END
//
DELIMITER ;
CALL sp_emp_select(7934, @t_ename, @t_sal);
SELECT @t_sal AS "임금", @t_ename AS "이름";
표시한거는 순서를 맞출 것
INOUT 예제
한개의 변수로 입력받아서 출력하는 경우다.
자주 사용하지는 않지만, 변수를 최소화 할수 있다.
DELIMITER //
CREATE PROCEDURE test_proc_inout(INOUT v_str VARCHAR(100))
BEGIN
DECLARE txt VARCHAR(100);
SET txt = 'my name is ';
SELECT CONCAT(txt, v_str) INTO v_str;
END
//
DELIMITER ;
SET @t_str = '한강';
CALL test_proc_inout(@t_str);
SELECT @t_str;
VIEW
- 개발자들에게 편리함
1. 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 액세스를 제한한다. --> . 보안에 도움이 된다 (테이블 이름, 컬럼 이름을 노출시키지 않을 수 있다)
2. 복잡한 질의어를 통해 얻을 수 있는 결과를 간단한 질의어를 써서 구할 수 있게 한다. --> 성능향상
3. 데이터 독립성을 허용한다.
4. 동일한 데이터의 다른 VIEW를 나타낸다.
5. 조인을 한 것처럼 여러 테이블에 대한 데이터를 VIEW를 통해 볼 수 있다.
6. 한개의 VIEW로 여러 테이블에 대한 데이터 를 검색할 수 있다.
단순뷰 : 한개의 테이블 대상
복합뷰 : 여러개의 테이블 대상
양식
CREATE [OR REPLACE] VIEW view_name(alias,...)
AS
Subquery
[WITH CHECK OPTION]
-- OR REPLACE : 기존에 존재하는 뷰가 있다면 삭제하고 새로 만든다
-- WITH CHECK OPTION : 서브쿼리 내의 조건을 만족하는 행만 변경 가능
예시
-- 10번 부서의 사람들의 사원번호, 이름, 직업을 VIEW로 만들었다.
CREATE VIEW empview10
AS
SELECT empno, ename, job
FROM emp
WHERE deptno = 10;
DESC empview10 --VIEW 구조보기
SELECT * FROM empview10; --View 를 이용한 데이터 조회WITH CHECK OPTION
view를 만들때 걸었던 조건에 락을 거는것이다.
해당 조건을 변경하려고 할때 오류가 발생한다.
-- emp_20은 emp가 20인 사람들을 보기위해 만든 viwe이다.
CREATE OR REPLACE VIEW emp_20
AS
SELECT empno, ename, sal, dname, loc, DEPTNO
FROM emp NATURAL JOIN (dept)
WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT emp_20_ck;
-- 그런데 20번을 30번으로 바꾸려 하는걸 막는게 WITH CHECK OPTION 옵션이다.
UPDATE emp_20
SET deptno = 30;
-- 오류발생view 제거하기
DROP VIEW view_name;최상위 n개의 값 출력
-- limit 방식
SELECT *
from emp
ORDER BY sal DESC
LIMIT 3;
-- 전역함수 방식
SET @ROWNUM :=0;
SELECT @ROWNUM := @ROWNUM + 1 AS rank, empno, ename, hiredate
FROM emp
ORDER BY hiredate
LIMIT 3;INDEX
-- 장/단점
1)장점
- 검색 속도가 빨라진다.
- 시스템에 부하를 줄여서 시스템 전체 성능을 향상시킨다.
2)단점
-인덱스를 위한 추가 공간이 필요하다.
-인덱스를 생성하는 데 시간이 걸린다.
-데이터의 변경작업이 자주 일어나는 경우에는 오히려 성능이 더 떨어진다
-- 생성하지 않아야 할 조건
1) 테이블에 행이 적은 경우
2) 컬럼이 WHERE 조건에 자주 사용되지 않을 때
3) WHERE 조건에 의한 결과가 전체 행에 대해 10~15%의 결과보다 높게 리턴될 때
4) 테이블이 자주 입력, 수정 , 삭제 될 때는 오히려 검색 속도가 더 떨어진다.