SQL_10일차

23.03.22 수 10일차

수업내용

• SQL 구문
• AUTO SET(MY-SQL)

SQL 구문

• testTBL 데이터형 설정 및 테이블 작성

create table testTBL(
    id nvarchar2(4),
    title   nvarchar2(10)
);

• testTBL 테이블 값 INSERT

insert into testTBL(id, title)
values ('1234', 'aaa');
insert into testTBL(id, title)
values ('a123', 'aaa');
insert into testTBL(id, title)
values ('222', 'bbb');
insert into testTBL(id, title)
values ('', 'bbb');

• testTBL SELECT(WHERE문으로 레코드값 검색)

select * from testTBL
where id='1234'; 

- 테이블 속성의 (세로줄)레코드 값 중 
문자가 포함된경우 숫자에도 따옴표(' ') 처리 필요

• NULLIF
  1) 두개의 컬럼을 비교
  2) 같으면 NULL , 다른값이면 첫번째 컬럼의 값을 출력

select id, title, nullif(id, title)
from testTBL;

• testTBL id컬럼 null값 포함한 레코드 검색

select * from testTBL
where id is null;

• bankBook0322 테이블 생성

CREATE TABLE bankBook0322
(
  name NVARCHAR2(10),
  money INT ,
  CONSTRAINT CK_money CHECK (money >= 0)
);

• bankBook0322 값 INSERT 후 COMMIT

insert  into bankBook0322 values('영심이', 1000);
insert  into bankBook0322 values('당탕이', 0);
commit;

• 값 INSERT 됐는지 SELECT문으로 전체 레코드 값 확인

select * from bankbook0322;

• INSERT 프로시저 생성

create or replace procedure  ps_bankBook0322_insert
(
  b_name       VARCHAR2,
  b_money       VARCHAR2 
)
is
begin
  insert  into bankBook0322 (name, money)
  values(b_name, b_money);
end;

• 프로시저 레코드 값 삽입

execute ps_bankBook0322_insert('하니', 200);

• 값 삽입 확인

select * from bankBook0322;

• UPDATE 프로시저 생성
  (값 수정)

create or replace procedure  ps_bankBook0322_update
(
  b_name       VARCHAR2,
  b_money       VARCHAR2 
)
is
begin -- 트랜잭션의 시작
  update bankBook0322 set money = b_money 
  where name = b_name;
end; -- 트랜잭션의 끝

• 프로시저 값 수정

execute ps_bankBook0322_update ('하늘이', 700);
-- '하늘이'인 레코드의 money 값을 700으로 수정 

• 값 수정 확인

select * from bankBook0322;

• UPDATE 프로시저 생성2
  (이름 검색,값 증가)

create or replace procedure  ps_bankBook0322_update2
(
  b_name       VARCHAR2
)
is
begin -- 트랜잭션의 시작
   update bankBook0322 set money = money +100
   -- money 값 +100 된 값으로 수정
   where name like  b_name;
   --b_name이 포함된 name 컬럼의 
     레코드값을 수정대상으로 함
end; -- 트랜잭션의 끝

• 프로시저 값 수정

execute ps_bankBook0322_update2 ('%하%');
--'하' 가 포함된 레코드의 money값을 +100

• 값 수정 확인

select * from bankBook0322;

• UPDATE 프로시저 생성3
  (이름 검색2)

create or replace procedure  ps_bankBook0322_update3
(
  b_name       VARCHAR2
)
is
begin -- 트랜잭션의 시작
   update bankBook0322 set money = money +100 
   where name like  '%'| | b_name | |'%';  
   - b_name 변수값을 포함한 값 위치를 기준
   - ||(or연산기호)를 통해 입력값에서 %제외하기
end; -- 트랜잭션의 끝

• 프로시저 값 수정

execute ps_bankBook0322_update3 ('심');
--'심' 이 포함된 레코드의 money값을 +100

• 값 수정 확인

select * from bankBook0322;

• 고객수 출력하는 프로시저

set SERVEROUTPUT on; - 출력설정
create or replace procedure sp_bankbook0322_selectone
as
    var1 number;
begin
    select count(*) into var1 from 		 
    bankbook0322;
    DBMS_OUTPUT.PUT_LINE('고객수 : ' || var1);
    - DBMS_OUTPUT : 하나의 레코드만 출력가능
end;

• 고객수 프로시저 값 확인

execute sp_bankbook0322_selectone;

• 특정 이름이 포함된 고객 수 구하기프로시저

create or replace procedure sp_bankbook0322_selectone2
(
    b_name nvarchar2
)
as -- is
    var1 number;
begin
    select count(*) into var1 from
    bankbook0322
    where name like '%'|| b_name ||'%';
    DBMS_OUTPUT.PUT_LINE('고객수 : ' || var1);
    -- DBMS_OUTPUT : 하나의 레코드만 출력가능    
end;

• 특정 이름이 포함된 고객수 프로시저 값 확인

execute sp_bankbook0322_selectone2('하');

• function
  사용자가 직접 함수를 정의해서 사용할 수 있다.

create or replace function userFunc1 -- 함수명
(
    var1 int, -- 매개변수, 형
    var2 int,
    var3 int
)
return number -- number 형으로 반환
as
begin
    return var1 + var2 + var3;
end;

• function 값 확인

select userFunc1(10, 20, 30) as "결과값" from dual;

• Trigger
  1) 테이블에 DML의 이벤트가 발생할때 자동으로 실행
  2) INSERT 목적이 일반적

• 입력할 테이블 만들기

CREATE  TABLE bankBook0322_BACK
(
  name NVARCHAR2(10),
  money INT ,
  newdate DATE
);

• 트리거 만들기

create TRIgger tri_bankbook0322_update
after update or delete 
- or로 두개의 DML사용가능 
- DML구문 이후에 작동하라는 작동조건
on bankbook0322 -- 트리거 부착 테이블 지정
for each row -- 각 행마다 적용
begin
    insert into bankbook0322_back(name, money, newDate)
    values(:old.name, :old.money, sysdate());
end;

• UPDATE TRIGGER 동작

update bankBook0322 set money = money+1
where name like '%이%';

• DELETE TRIGGER 동작

delete from bankBook0322_BACK where name='영심이';

• TRIGGER 동작 확인

select * from bankBook0322_BACK;

• auto set(Mysql)
  ID : ppk(DB) / PW : 1234

• 테이블 만들기
  1) 유니코드형 없다
  2) NUMBER 형 없다
  3) ERROR가 없이 실행되는 경우가 많다
  (오류없이 값이 다르게 변경되어 입력되는 경우)

• AUTO_IINCREMENT(자동증가) 추가 테이블 생성

CREATE TABLE persons(
    IDX int(3) NOT NULL 
    AUTO_IINCREMENT PRIMARY KEY,
    ID int,
    FirstName varchar(255)
);

• 날짜 추가 테이블 생성

CREATE TABLE persons1(
    ID int(3),
    FirstName varchar(255),
    mdate   date
);

• 값추가

insert into persons1(id, FIRstName, mdate)
values (101, 'Chang', '2023-03-22');
insert into persons1(id, FIRstName, mdate)
values (101, 'Chang', sysdate());

• 칼럼 추가

Alter table persons1 
ADD idx int(5);

• 느낀점

MARIA DB를 이용하여 CMD창을 이용해 TABLE DROP, CREATE, UPDATE, SELECT 동작을 해보았다.
ORACLE이 아닌 다른곳에서 SQL 구문을 사용해서 진행해보니 새롭고 신기했다.
다양한 툴을 접해보고 사용해보고싶단 생각이 들었다.