전산 교육 정리

CREATE TABLE 테이블명 (
	컬러명1 컬럼명세,
    컬럼명2 컬럼명세,
    컬럼명3 컬럼명세...
    );
    
CREATE TABLE 고객(
	고객번호 int,
    고객명 char(20),
    전화 char(20), 
    주소 char(50)
);

- IF NOT EXISTS : 동일한 테이블이 없을때만 테이블을 생성하는 옵션 (생략 가능하나, 이때 테이블 존재시 에러)

실습

create database order_manger;
use order_manger;


create table 영업사원(
영업사원번호 int,
영업사원명 char(10),
생년 char(8),
전화 char(15),
주소 char(100)
);

INSERT  INTO 영업사원(영업사원번호,영업사원명, 생년, 전화, 주소) VALUES
  (1,"박수동", 1988, "010-325-1234", "서울 도봉동 455"),
  (2,"도인범", 1978, "010-222-5432", "서울 성북동 290"),
  (3, "천길호", 1984, "010-6767-8787", "서울 방배동 54"),
  (5, "김희철", 1990, "010-622-0001", "서울 홍제동 244");
  
create table 주문(
주문번호 int,
일시 datetime,
금액 char(20),
고객번호 int,
영업사원번호 int
);

INSERT  INTO  주문(주문번호, 일시, 금액, 고객번호, 영업사원번호) VALUES
  (1, "2021-03-02 10:11", 400000, 1, 1),
  (2, "2021-03-02 10:11", 1650000, 2, 2),
  (3, "2021-03-02 11:10", 1050000, 3, 3),
  (4, "2021-03-02 11:17", 1800000, 4, 4),
  (5, "2021-03-02 11:51", 2570000, 5, 2);

create table 고객(
고객번호 int,
고객명 char(20),
전화 char(20),
주소 char(50)
);

INSERT  INTO  고객(고객번호,고객명, 전화, 주소) VALUES
 (1, "김원일", "02-335-2544", "서울 중곡동 311"),
 (2,"박동수", "02-277-7654", "서울 서초동 656"),
 (3, "최길원", "031-988-0987", "경기 안양 76"),
 (4, "박인수", "02-778-9888", "서울 은평동 87"),
 (5, "황철언", "02-490-9900", "서울 평창동 81");
 
 create table 주문상품(
주문번호 int,
상품코드 int,
수량 char(20)
);

select * from 주문상품;

INSERT  INTO  주문상품(주문번호,상품코드,수량) VALUES
 (1, 1, 50),
 (2, 2, 10),
 (3, 3, 15),
 (4, 4, 20),
 (5, 5, 21);
 
create table 상품(
    상품명 char(20), 
    단가 int
    );

INSERT  INTO  상품(상품명, 단가) VALUES
  ("티엘론단열", 80000),
  ("경질우레탄", 55000),
  ("난연점착제", 70000),
  ("T동파이프", 90000),
  ("광폭점착제", 120000),
  ("열반사단열", 100000);

실습 완성본

create database order_manger;
use order_manger;


create table 영업사원(
영업사원번호 int,
영업사원명 char(10),
생년 char(8),
전화 char(15),
주소 char(100)
);


alter table 영업사원 add primary key pk_영업사원번호(영업사원번호);


-- SET SQL_SAFE_UPDATES = 0; UPDATE 옵션 설정

update 영업사원
set 주소 =  "서울 도봉동 455"
where 영업사원번호 = 1;

 INSERT  INTO 영업사원(영업사원번호,영업사원명, 생년, 전화, 주소) VALUES
  (1,"박수동", 1988, "010-325-1234", "서울 도봉동 455"),
  (2,"도인범", 1978, "010-222-5432", "서울 성북동 290"),
  (3, "천길호", 1984, "010-6767-8787", "서울 방배동 54"),
  (5, "김희철", 1990, "010-622-0001", "서울 홍제동 244");


create table 주문(
주문번호 int,
일시 datetime,
금액 char(20),
고객번호 int,
영업사원번호 int
);


alter table 주문 add primary key pk_주문번호(주문번호);
ALTER TABLE 주문 ADD FOREIGN KEY (영업사원번호) REFERENCES 영업사원(영업사원번호);
ALTER TABLE 주문 ADD FOREIGN KEY (고객번호) REFERENCES 고객(고객번호);


  INSERT  INTO  주문(주문번호, 일시, 금액, 고객번호, 영업사원번호) VALUES
  (1, "2021-03-02 10:11", 400000, 1, 1),
  (2, "2021-03-02 10:11", 1650000, 2, 2),
  (3, "2021-03-02 11:10", 1050000, 3, 3),
  (4, "2021-03-02 11:17", 1800000, 4, 4),
  (5, "2021-03-02 11:51", 2570000, 5, 2);


create table 고객(
고객번호 int primary key,
고객명 char(20),
전화 char(20),
주소 char(50)
);



INSERT  INTO  고객(고객번호,고객명, 전화, 주소) VALUES
 (1, "김원일", "02-335-2544", "서울 중곡동 311"),
 (2,"박동수", "02-277-7654", "서울 서초동 656"),
 (3, "최길원", "031-988-0987", "경기 안양 76"),
 (4, "박인수", "02-778-9888", "서울 은평동 87"),
 (5, "황철언", "02-490-9900", "서울 평창동 81");


create table 주문상품(
주문번호 int,
상품코드 int,
수량 char(20)
);

alter table 주문상품 add primary key pk_주문상품번호(주문번호,상품코드);

select * from 주문상품;

alter table 주문상품
add constraint idx1 foreign key(주문번호) References 주문(주문번호)
on delete cascade, 
add constraint idx2 foreign key(상품코드) References 상품(상품코드)
on delete cascade;


INSERT  INTO  주문상품(주문번호,상품코드,수량) VALUES
 (1, 1, 50),
 (2, 2, 10),
 (3, 3, 15),
 (4, 4, 20),
 (5, 5, 21);

create table 상품(
상품코드 int,
상품명 char(20),
단가 char(20)
);

alter table 상품 add primary key pk_상품코드(상품코드);

INSERT  INTO  상품(상품코드, 상품명, 단가) VALUES
  (1, "티엘론단열", 80000),
  (2, "경질우레탄", 55000),
  (3, "난연점착제", 70000),
  (4, "T동파이프", 90000),
  (5, "광폭점착제", 120000),
  (6, "열반사단열", 100000);

- CASCADE: 부모테이블 삭제나 수정시 하위테이블 자동 삭제, 수정

- SET NULL: 자동 NULL로 세팅

- RISTRICT: 거부(에러유발)...MySQL의 기본값

- INT(int) : 4Byte 정수 (저장범위: -2147483648~2147483647)

- DOUBLE(double) : 실수 데이터(8바이트)

- DATETIME : 날짜와 시간을 yyyy-mm-dd hh:mm:ss 의 고정된 문자열 형식으로 취급

(표현범위: 1000-01-01 00:00:00~9999-12-31 23:59:59, 8byte형)

Alter Table

ADD... 기존테이블에 컬럼 추가

(문제)

ALTER TABLE 제품
	ADD 제품요약 CHAR(50),
    ADD 생산지 CHAR(30) AFTER 단가;
    
create table 제품(
	제품번호 int NOT NULL,
    제품명 char(20);
    단가 int NOT NULL,
    생산국가 char(20) DEFAULT "kor");

MODIFY... 기존컬럼 구조, 성격 수정

(문제) 아래 테이블의 "제품번호" 컬럼에 PK를 부여하고,

"공급자" 컬럼을 신규추가하고

"제품명" 컬럼의 특성을 NOT NULL로 수정하세요.

ALTER TABLE 제품
	MODIFY 제품번호 int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    MODIFY 제품명 CHAR(50) NOT NULL,
    ADD 공급자 CHAR(30) NOT NULL;

ALTER TABLE 제품

MODIFY 제품번호 int NOT NULL PRIMARY KEY AUTO_INCREMENT,

MODIFY 제품명 CHAR(30) NOT NULL,

ADD 공급자 CHAR(30) NOT NULL;

AUTO_INCREMENT : 데이터가 삽입될때마다 1씩 증가해주는 역할

DROP Table (테이블 삭제) - rollback 불가(회복 불가)

TRUNCATE Table (테이블 데이터 삭제) - rollback 불가

[내부적으로 DROP and CREATE로 동작]

트렌젝션 :

- 논리적으로 함께 처리되어야 하는 연산단위

- 논리적으로 분할 할 수 없는 최소단위

- All or Nothing

- START TRANSACTION; :트랜젝션 개시명령

- COMMIT; : 변경사항 저장

- ROLLBACK; : 변경사항 취소

SAVE POINT 지정가능

TRANSACTION은 언제 시작되는가

-실행 가능한 SQL문장이 제일 처음 실행될 때

-명시적인 START TRANSACTION문을 만났을 때

TRANSACTION은 언제 종료되는가?

- COMMIT이나 ROLLBACK 명령에 의해

- 기계 장애 또는 시스템 충돌(crash)이 발생한 경우  ROLLBACK

- deadlock이 발생한 경우  ROLLBACK

- 사용자가 MySQL 종료시  ROLLBACK

- DDL명령(create, alter, drop 등) 실행에 의해  implicit commit

ACID : (원자성,일관성, 고립성, 지속성)

- Atomicity 원자성

- Consistency 일관성

- Isolation 고립성

- Durability 지속성

View

데이터베이스 객체 : 데이터베이스 내에 존재하는 논리적 관리구조

view :

- 하나 이상의 테이블이나 뷰를 연결해 생성한 가상 테이블

- 하나의 뷰로 여러 테이블에 대한 검색 결과를 볼 수 있음.

- 다양한 유저의 관심별 데이터 검색이 가능

CREATE VIEW view_name
AS
SELECT-statement;

CREATE VIEW sample_view
AS
SELECT * FROM products where price >= 40;

View(뷰) 문제

- 주문별로 어떤 제품인지와 단가, 수량, 금액, 어떤 고객의 주문인지 함께 보여주는 뷰를

order_status 라는 이름으로 작성해보기

CREATE VIEW order_status
AS
SELECT orderdetailid, orderid, productid, productname, 
	price, quantity, price*quantity as 금액, customerID, customerName
FROM orderdetails
	INNER JOIN products USING(productid)
    INNER JOIN orders USING(orderID)
    INNER JOIN customers USING(customerID)
ORDER BY orderdetailid;

stored procedure(저장 프로시저)

- 자주 사용되는 SQL문 등을 프로시져 루틴으로 저장 후 호출해 사용

- 호출하면, 최초 한번만 컴파일 후 캐시에 배치하고 실행

- 재 호출시 캐시에서 즉시 실행(컴파일 불필요)

- 프로그램의 코드 절감 및 재 사용성, 논리화, 모듈화 등

 두 수간의 자연수 합 누적 프로그램

DELIMITER $$
Create procedure accumulate( 
	in start_val int, 
	in finish_val int, 
	out accumulate_val int 
)
BEGIN 
	set accumulate_val = start_val; 
	while start_val < finish_val do 
		set start_val = start_val +1; 
		set accumulate_val = accumulate_val + start_val; 
	end while;
END $$ 
DELIMITER ;

예제) 원하는 테이블 이름 입력시 레코드 수 반환

DELIMITER $$
create procedure count_record(in tb_name char(20), out cnt int)
Begin
set @sql_statement= concat('select count(*) into @ct from ', tb_name);
PREPARE command FROM @sql_statement;
EXECUTE command;
DEALLOCATE PREPARE command;
set cnt= @ct;
End $$
DELIMITER ;

Procedure VS. Function 비교

이슈	프로시저	함수
호출방법	Call로 호출, 결과값을 반환 X	SQL문에서 식으로 호출, 결과값을 반드시 반환
반환갯수	반환값(out, inout)이 있거나 없거나	반환값(return)이 반드시 하나
전달	입력(in)이 있거나 없거나	입력이 있거나 없거나
코드수용	안에 삽입, 삭제, 수정 등의 작업 가능 안에 동적쿼리, 트렌젝션 작업 가능 예외 처리 블록 가능	안에 선택질의만 가능 안에 동적, 트렌 불가 예외처리 코드 불가

트리거(Trigger)

: 테이블 이벤트에 반응해 자동으로 실행되는 장치

CREATE TRIGGER MyTrigger
	BEFORE UPDATE ON MyTable
	FOR EACH ROW 
BEGIN
	SET temp_price = OLD.price; 
	INSERT INTO ...
END

'전산 관련 시험 > DB(SQLP, SQLD) 문제풀이' 카테고리의 다른 글

(DB, 데이터베이스) DBMS 구성 (0)	2024.03.03
전산 교육 테스트 대비_SQL (0)	2023.05.14
전산 교육 정리_SQL(2) (0)	2023.05.11
전산 교육 정리_SQL(1) (0)	2023.05.10
DB_뷰와 시스템 카탈로그 (0)	2022.12.04

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

Learning_EunBi

전산 교육 정리_SQL(3)

View

stored procedure(저장 프로시저)

Procedure VS. Function 비교

트리거(Trigger)

'전산 관련 시험 > DB(SQLP, SQLD) 문제풀이' 카테고리의 다른 글

댓글

티스토리툴바

개인정보

단축키

내 블로그

블로그 게시글

모든 영역

전산 교육 정리_SQL(3)

View

stored procedure(저장 프로시저)

Procedure VS. Function 비교

트리거(Trigger)

'전산 관련 시험 > DB(SQLP, SQLD) 문제풀이' 카테고리의 다른 글

관련글

댓글

티스토리툴바

개인정보

단축키

내 블로그

블로그 게시글

모든 영역