CREATE SCHEMA `haksa` ;



use haksa;




학사 테이블 만들기

#학과테이블

create table department(

dept_code int(2) Not null, #학과번호

dept_name char(30) Not null, #학과명

dept_ename varchar(50), #학과영문이름

Create_date date default null, #학과생성날짜

primary key (dept_code)

)engine = innoDB;


#우편번호테이블

create table post(

post_no varchar(7) Not null, #우편번호

post_dong char(30) Not null, #동이름

post_address char(60) Not null, #주소

ddd char(4), #DDD 전화지역번호

primary key (post_no)

)engine = innoDB;


#학적(학생신상)테이블

create table student(

stu_no char(10) Not null, #학번

stu_name char(10) Not null, #학생이름

stu_ename varchar(30), #영문이름

dept_code int(2) Not null, #학과코드

grade int(1) Not null, # 학년

class int (1) Not null, #반

juya char(2), #주야구분

id_num varchar(14) Not null, #주민등록번호

post_no varchar(7), #우편번호

address varchar(100), #주소

tel varchar(14), #전화번호

phone_no varchar(14), #휴대전화번호

birth_year char(4), #출생년도

primary key (stu_no),

constraint s_dp_fk foreign key(dept_code)  #외래키 학과 테이블의 학과코드

references department(dept_code),

constraint s_ps_fk foreign key(post_no)  #외래키 우편번호 테이블의 우편번호코드

references post(post_no)

) engine = innoDB;


#교과목테이블

create table subject(

sub_code char(5) Not null, #과목번호

sub_name varchar(50) Not null, #과목명

sub_ename varchar(50), #영문과목명

create_year char(4), #개설년도

primary key (sub_code)

)engine = innoDB;


#교수테이블

create table professor(

prof_code char(4) Not null, #교수번호

prof_name char(10) Not null, #교수명

prof_ename varchar(30), #교수영문이름

Create_date date default null, #교수임용날짜

primary key (prof_code)

)engine = innoDB;


#수강신청

create table attend(

stu_no char(10) Not null, #학번

att_year char(4) Not null, #수강년도

att_term int(1) Not null, # 수강학기

att_isu int(1) Not null, #이수구분

sub_code char(5) Not null, #과목코드

prof_code char(4) Not null, #교수번호

att_point int(1) Not null, #이수학점

att_grade int(3) default '0', #취득점수

att_div char(1) default 'N' Not null, #수강신청구분

att_jae char(1) default '1', #재수강 구분 1(본학기 수강), 2(재수강), 3(계절학기 수강)

att_date date Not null, #수강처리일자

primary key (stu_no, att_year, att_term, sub_code, prof_code, att_jae),

constraint su_att_fk foreign key(sub_code) #외래키 교과목 테이블의 과목코드

references subject(sub_code),

constraint pr_att_fk foreign key(prof_code) #외래키 교수 테이블의 교수코드

references professor(prof_code)

) engine = innoDB;


#등록금테이블

create table fee(

stu_no varchar(10) Not null, #학번

fee_year varchar(4) Not null, #등록년도

fee_term int(1) Not null, #등록학기

fee_enter int(7), #입학금

fee_price int(7) Not null, #등록금(수업료)

fee_total int(7) Default '0' Not null, #등록금총액=입학금+수업료

jang_code char(2) Null, #장학코드

jang_total int(7), #장학금액

fee_pay int(7) Default '0' Not null, #납부총액=등록금총액-장학금액

fee_div char(1) Default 'N' Not null, #등록구분

fee_date date Not null, #등록날짜

primary key (stu_no, fee_year, fee_term)

) engine = innoDB;


#성적테이블

create table score(

stu_no char(10) Not null, #학번

sco_year char(4) Not null, #성적취득년도

sco_term int(1) Not null, #학기

req_point int(2), #신청학점

take_point int(2), #취득학점

exam_avg float(2,1), #평점평균

exam_total int(4), #백분율 총점

sco_div char(1), #성적구분

sco_date date, #성적처리일자

primary key (stu_no, sco_year, sco_term)

) engine = innoDB;


#동아리테이블

create table circle(

cir_num int(4) Not null auto_increment, #동아리가입번호

cir_name char(30) Not null, #동아리명

stu_no char(10) Not Null, #학번

stu_name char(10) Not Null, #이름

president char(1) default '2' Not null, #동아리회장(0), 부회장(1), 회원(2)

primary key (cir_num),

constraint s_ci_fk foreign key(stu_no) #외래키 학적 테이블의 학번

references student(stu_no)

)engine = innoDB;






값 삽입~!



#DEPARTMENT 입력

INSERT INTO DEPARTMENT VALUES

(10,'간호학과','Dept. of Nersing','1954-02-01');


INSERT INTO DEPARTMENT VALUES

(20,'경영학과','Dept. of Management','1980-02-10');


INSERT INTO DEPARTMENT VALUES

(30,'수학학과','Dept. of Mathematics','1984-02-20');


INSERT INTO DEPARTMENT VALUES

(40,'컴퓨터정보학과','Dept. of Computer Information','1995-02-01');


INSERT INTO DEPARTMENT VALUES

(50,'정보통신학과','Dept. of Information Communication','1997-02-10');


INSERT INTO DEPARTMENT VALUES

(60,'회계학과','Dept. of Accounting','1998-03-01');


#POST 입력

INSERT INTO POST VALUES

       ('556-820', '화양면', '전라남도 여수시 화양면', '061');


INSERT INTO POST VALUES

       ('135-900', '압구정동', '서울특별시 강남구 압구정동', '02');


INSERT INTO POST VALUES

       ('451-800', '팽성읍', '경기도 평택시 팽성읍', '031');


INSERT INTO POST VALUES

       ('545-800', '광양읍', '전람남도 광양시 광양읍', '061');


INSERT INTO POST VALUES

       ('135-794', '압구정동', '서울특별시 강남구 압구정동 한양아파트', '02');


INSERT INTO POST VALUES

       ('550-130', '서교동', '전라남도 여수시 서교동', '061');


INSERT INTO POST VALUES

       ('150-051', '신길1동', '서울특별시 영등포구 신길1동', '02');


INSERT INTO POST VALUES

       ('500-170', '운암동', '광주광역시 북구 운암동', '062');


INSERT INTO POST VALUES

       ('506-040', '소촌동', '광주광역시 광산구 소촌동', '062');


INSERT INTO POST VALUES

       ('545-080', '광영동', '전라남도 광양시 광영동', '061');


INSERT INTO POST VALUES

       ('550-160', '봉강동', '전라남도 여수시 봉강동', '061');


INSERT INTO POST VALUES

       ('135-905', '압구정동', '서울특별시 강남구 압구정동 구현대아파트', '02');


INSERT INTO POST VALUES

       ('550-260', '여서동', '전라남도 여수시 여서동', '061');


INSERT INTO POST VALUES

       ('135-786', '압구정동', '서울특별시 강남구 압구정동 신현대아파트', '02');


INSERT INTO POST VALUES

       ('546-121', '농성동', '광주광역시 서구 농성트', '062');


INSERT INTO POST VALUES

       ('590-020', '향교동', '전라북도 남원시 향교동', '063');


INSERT INTO POST VALUES

       ('548-820', '영남면', '전라남도 고흥군 영남면', '061');


#STUDENT 학생 테이블 입력

INSERT INTO STUDENT VALUES

       ('20001001', '김유신', 'Kim Yoo-Shin', 40, 4, 3, '야', '811007-1632013','556-820','안포리 28번지','061)685-7818','011-617-1290','1981');


INSERT INTO STUDENT VALUES

       ('20001015', '박도준', 'Park Do-Jun', 40, 4, 1, '주', '780116-1580715','135-900','386번지','02)744-6126','011-611-9884','1978');


INSERT INTO STUDENT VALUES

       ('20001021', '이상길', 'Lee Sang-Gil', 40, 4, 1, '주', '750819-1227014','451-800','안정리 주공APT 107동 504호','031)691-5423',NULL,'1975');


INSERT INTO STUDENT VALUES

       ('20041002', '김유미', 'Kim Yoo-Mi', 40, 3, 2, '주', '830207-2629715','545-800','덕례리 산 16-1번지','061)763-1439','010-617-1290','1983');


INSERT INTO STUDENT VALUES

       ('20041007', '정인정', 'Jeung Yin-Jeung', 40, 2, 2, '주', '830315-2351225','135-794','6동 1203호','02)723-1078','016-605-7837','1983');


INSERT INTO STUDENT VALUES

       ('20041033', '연개소문', 'Yean Gae-So-Moon', 40, 3, 3, '야', '810615-1633111','550-130','280-50번지','061)642-9304','018-641-9304','1981');


INSERT INTO STUDENT VALUES

       ('20061011', '박정인', 'Park Jung-In', 40, 2, 1, '주', '830403-1635213','150-051','985번지 롯데APT 102-306','02)652-2439','017-3142-1294','1983');


INSERT INTO STUDENT VALUES

       ('20061014', '고혜진', 'Ko Hea-Jin', 10, 2, 1,'주', '870307-2638759','500-170','삼익APT 101동 102호','061)781-5135',NULL,'1987');



INSERT INTO STUDENT VALUES

       ('20061048', '김영호', 'Kim Young-Ho', 10, 4, 3 ,'야', '860811-1548758','506-040','라인APT 207동 309호','062)678-1010','017-614-7575','1986');


INSERT INTO STUDENT VALUES

       ('20071001', '장수인', 'Jang Soo-In', 40, 1, 1 ,'주', '890209-1616822','545-080','금광APT 108동 1101호','061)791-1236',NULL,'1989');


INSERT INTO STUDENT VALUES

       ('20071010', '홍길동', 'Hong Gil-Dong', 40, 1, 3,'야', '880402-1850838','550-160','해태APT 104동 605호','061)642-4034','010-6425-9245','1988');


INSERT INTO STUDENT VALUES

       ('20071022', '이순신', 'Lee Sun-Shin', 10, 1, 3,'야', '890222-1218818','135-794','2동 1004호','02)745-7667','010-7141-1860','1989');


INSERT INTO STUDENT VALUES

       ('20071300', '유하나', 'Yoo Ha-Na', 50, 1, 1 ,'주', '880921-2573717','550-260','주공APT 204동 512호','061)651-5992','019-651-0707','1988');


INSERT INTO STUDENT VALUES

       ('20071307', '김문영', 'Kim Moon-Young', 50, 1, 3,'야', '880418-2121623','135-905','31동 102호','02)745-5485','019-4624-0460','1988');


INSERT INTO STUDENT VALUES

       ('20071405', '최차영', 'Choi Cha-Young', 50, 1, 2 ,'주', '881003-2581516','135-786','101동 540호','02)745-6893',NULL,'1988');


#SUBJECT 입력

INSERT INTO SUBJECT VALUES

       ('4001', '데이터베이스 응용', 'Database Application', '2002');


INSERT INTO SUBJECT VALUES

       ('4002', '웹사이트 구축', 'Web Site Construction', '2003');


INSERT INTO SUBJECT VALUES

       ('4003', '소프트웨어공학', 'Software Engineering', '2003');


INSERT INTO SUBJECT VALUES

       ('4004', '웹프로그래밍', 'Web Programming', '2002');


INSERT INTO SUBJECT VALUES

       ('4005', '컴퓨터구조', 'Computer Structure', '2001');


INSERT INTO SUBJECT VALUES

       ('4006', '정보처리실무', 'Information Process Practical business', '2001');


INSERT INTO SUBJECT VALUES

       ('4007', 'UML', 'Unified Modeling Language', '2005');


INSERT INTO SUBJECT VALUES

       ('4008', '운영체제', 'Operating System', '2002');


INSERT INTO SUBJECT VALUES

       ('4009', '전자상거래 실무', 'Electronic Commerce', '2003');


INSERT INTO SUBJECT VALUES

       ('4010', '윈도우즈 프로그래밍', 'Windows Programming', '1998');


INSERT INTO SUBJECT VALUES

       ('4011', '자바프로그래밍', 'Java Programming', '1999');


INSERT INTO SUBJECT VALUES

       ('4012', '네트워크 프로그래밍', 'Network Programming', '2005');


INSERT INTO SUBJECT VALUES

       ('4013', '컴포넌트 프로그래밍', 'Component Programming', '2004');


#PROFESSOR 입력

INSERT INTO PROFESSOR VALUES

       ('4001','정진용','Jung jin-yong','1995-09-01');

     

INSERT INTO PROFESSOR VALUES

       ('4002','나인섭','Na in-sub','2006-02-02');


INSERT INTO PROFESSOR VALUES

       ('4003','정창부','Jung chang-boo','2003-03-01');


INSERT INTO PROFESSOR VALUES

       ('4004','박상철','Park sang-chul','2000-01-15');



INSERT INTO PROFESSOR VALUES

       ('4005','정병열','Jung byeong-yeol','1998-03-01');


INSERT INTO PROFESSOR VALUES

       ('4006','고진광','Go jin-gwang','1988-03-01');


INSERT INTO PROFESSOR VALUES

       ('4007','김영식','Kim young-sik','1986-03-01');


INSERT INTO PROFESSOR VALUES

       ('4008','최우진','Choi woo-jin','1997-03-01');


INSERT INTO PROFESSOR VALUES

       ('4009','문창수','Moon chang-soo','1995-03-01');


INSERT INTO PROFESSOR VALUES

       ('5010','정종필','Jung jong-phil','1997-03-01');


INSERT INTO PROFESSOR VALUES

       ('5011','최종주','Choi jong-joo','1992-03-05');


#ATTEND 입력

INSERT INTO ATTEND VALUES

('20061011','2006',1,3,4001,'4002',3, 99,'Y','1','2006-03-05');


INSERT INTO ATTEND VALUES

('20061011','2006',1,4,4002,'4003',3, 95,'Y','1','2006-03-05');


INSERT INTO ATTEND VALUES

('20061011','2006',1,4,4003,'4004',3, 97,'Y','1','2006-03-05');


INSERT INTO ATTEND VALUES

('20061011','2006',1,4,4004,'4001',3, 98,'Y','1','2006-03-05');


INSERT INTO ATTEND VALUES

('20061011','2006',1,4,4005,'4007',3, 96,'Y','1','2006-03-05');


INSERT INTO ATTEND VALUES

('20061011','2006',1,4,4006,'4008',3, 95,'Y','1','2006-03-05');



INSERT INTO ATTEND VALUES

('20061011','2006',2,3,4007,'4009',3, 93,'Y','1','2006-09-03');


INSERT INTO ATTEND VALUES

('20061011','2006',2,4,4008,'4005',3, 92,'Y','1','2006-09-03');


INSERT INTO ATTEND VALUES

('20061011','2006',2,4,4009,'4006',3, 94,'Y','1','2006-09-03');


INSERT INTO ATTEND VALUES

('20061011','2006',2,4,4010,'4001',3, 90,'Y','1','2006-09-03');


INSERT INTO ATTEND VALUES

('20061011','2006',2,4,4011,'4002',3, 91,'Y','1','2006-09-03');


INSERT INTO ATTEND VALUES

('20061011','2006',2,4,4012,'4003',3, 92,'Y','1','2006-09-03');


INSERT INTO ATTEND VALUES

('20071300','2007',1,3,4001,'4002',3, 99,'Y','1','2007-03-05');


INSERT INTO ATTEND VALUES

('20071300','2007',1,4,4002,'4003',3, 95,'Y','1','2007-03-05');


INSERT INTO ATTEND VALUES

('20071300','2007',1,4,4003,'4004',3, 97,'Y','1','2007-03-05');


INSERT INTO ATTEND VALUES

('20071300','2007',1,4,4004,'4001',3, 98,'Y','1','2007-03-05');


INSERT INTO ATTEND VALUES

('20071300','2007',1,4,4005,'4007',3, 93,'Y','1','2007-03-05');


INSERT INTO ATTEND VALUES

('20071300','2007',1,4,4006,'4008',3, 95,'Y','1','2007-03-05');


#FEE 입력

INSERT INTO FEE VALUES

('20061011','2006',1, 500000,3000000,3500000,01,500000,3000000,'Y','2006-02-18');



INSERT INTO FEE VALUES

('20061011','2006',2, NULL,3000000,3000000,10,2500000,500000,'Y','2006-08-20');


INSERT INTO FEE VALUES

('20061011','2007',1, NULL,3000000,3000000,11,2000000,1000000,'Y','2007-02-18');


INSERT INTO FEE VALUES

('20061011','2007',2, NULL,3000000,3000000,21,800000,2200000,'Y','2007-08-10');


INSERT INTO FEE VALUES

('20001015','2000',1, 500000,2500000,3000000,02,1000000,2000000,'Y','2000-02-01');


INSERT INTO FEE VALUES

('20001015','2000',2, NULL,2500000,2500000,10,2500000,0,'Y','2000-08-10');


INSERT INTO FEE VALUES

('20001015','2001',1, NULL,2800000,2800000,10,2500000,300000,'Y','2001-02-15');


INSERT INTO FEE VALUES

('20001015','2001',2, NULL,2800000,2800000,10,2500000,300000,'Y','2001-08-16');


INSERT INTO FEE VALUES

('20001015','2006',1, NULL,3000000,3000000,10,2500000,500000,'Y','2006-02-14');


INSERT INTO FEE VALUES

('20001015','2006',2, NULL,3000000,3000000,10,2500000,500000,'Y','2006-08-18');


INSERT INTO FEE VALUES

('20001015','2007',1, NULL,3000000,3000000,11,2000000,1000000,'Y','2007-02-10');


INSERT INTO FEE VALUES

('20001015','2007',2, NULL,3000000,3000000,10,2500000,500000,'Y','2007-08-19');


INSERT INTO FEE VALUES

('20071300','2007',1, 500000,3000000,3500000,01,500000,3000000,'Y','2007-02-18');


INSERT INTO FEE VALUES

('20071300','2007',2, NULL,3000000,3000000,11,2000000,1000000,'Y','2007-08-10');


INSERT INTO FEE VALUES

('20071001','2007',1, 500000,3000000,3500000,01,500000,3000000,'Y','2007-02-18');


INSERT INTO FEE VALUES

('20071001','2007',2, NULL,3000000,3000000,NULL,NULL,3000000,'Y','2007-08-10');


INSERT INTO FEE VALUES

('20071010','2007',1, 500000,3000000,3500000,01,500000,3000000,'Y','2007-02-18');


INSERT INTO FEE VALUES

('20071010','2007',2, NULL,3000000,3000000,NULL,NULL,3000000,'Y','2007-08-10');


INSERT INTO FEE VALUES

('20071022','2007',1, 500000,3000000,3500000,01,500000,3000000,'Y','2007-02-18');


INSERT INTO FEE VALUES

('20071022','2007',2, NULL,3000000,3000000,NULL,NULL,3000000,'Y','2007-08-10');


INSERT INTO FEE VALUES

('20071307','2007',1, 500000,3000000,3500000,01,500000,3000000,'Y','2007-02-18');


INSERT INTO FEE VALUES

('20071307','2007',2, NULL,3000000,3000000,NULL,NULL,3000000,'Y','2007-08-10');


INSERT INTO FEE VALUES

('20071405','2007',1, 500000,3000000,3500000,01,500000,3000000,'Y','2007-02-18');


INSERT INTO FEE VALUES

('20071405','2007',2, NULL,3000000,3000000,10,2500000,500000,'Y','2007-08-10');

#SCORE 입력

INSERT INTO SCORE VALUES

('20061011','2006',1,18,18,4.5,580,'Y','2006-08-10');


INSERT INTO SCORE VALUES

('20061011','2006',2,18,18,4.0,552,'Y','2007-01-11');


INSERT INTO SCORE VALUES

('20071300','2007',1,18,18,4.4,577,'Y','2007-08-09');


INSERT INTO SCORE VALUES

('20071307','2007',1,18,18,4.4,575,'Y','2007-08-09');



INSERT INTO SCORE VALUES

('20071405','2007',1,18,18,4.2,572,'Y','2007-08-09');


INSERT INTO SCORE VALUES

('20061011','2007',2,18,18,0,0,'N','2007-11-10');


INSERT INTO SCORE VALUES

('20061300','2007',2,18,18,0,0,'N','2007-11-10');


INSERT INTO SCORE VALUES

('20061307','2007',2,18,18,0,0,'N','2007-11-10');


INSERT INTO SCORE VALUES

('20061405','2007',2,18,18,0,0,'N','2007-11-10');


#CIRCLE 입력

INSERT INTO CIRCLE VALUES

(1,'컴맹탈출','20061011','박정인','0');


INSERT INTO CIRCLE VALUES

(2,'컴맹탈출','20071300','유하나','1');


INSERT INTO CIRCLE VALUES

(3,'컴맹탈출','20071307','김문영','2');


INSERT INTO CIRCLE VALUES

(4,'Java길라잡이','20071001','장수인','2');


INSERT INTO CIRCLE VALUES

(5,'Java길라잡이','20041007','정인정','1');


INSERT INTO CIRCLE VALUES

(6,'Java길라잡이','20001015','박도준','0');


INSERT INTO CIRCLE VALUES

(7,'PHP길라잡이','20001021','이상길','0');



출처:http://www.gbbook.com/file/01.php?admin_mode=read&no=6&make=title&search=sql

MySQL에서 사용자를 추가/제거 하는 방법과 권한을 부여하는 방법입니다. 

접속하기

$ mysql -u root -p


사용자 확인하기

mysql database를 선택하고, host, user, password를 확인합니다. 

mysql >use mysql; 

mysql > select host, user, password from user; 

 여기서 host는 localhost, '%'가 있습니다.  사용자 아이디 뒤에 @localhost, '%'에 따라서 외부 접근이 허용되는 권한을 줄 수 있습니다. localhost는 내부접근, '%'는 외부 접근입니다. 

사용자 추가 (권한 추가)

사용자 아이디를 만드는 방법, 비밀번호 추가, 외부접근 허용

userid에 추가하고자 하는 아이디를 입력하면 됩니다. 


identified by 'psasword'; password에 비밀번호를 함께 입력하면 패스워드를 설정할 수 있습니다. 

 mysql > create user userid 

mysql > create user userid@localhost identified by 'password';

mysql > create user 'userid'@'%' identified by 'password;

예) create user 'hiru'@'%' identified by 'hirururu'; 

(hiru라는 외부접근이 가능한 유저를 생성하고, 비밀번호는 hirururu로 설정하겠다.)



다른 방법은 

mysql > insert into user (host, user, password) values ('localhost', 'hiru', 'password('hirururu')); 

사용자 제거

mysql > drop user 'hiru';

mysql > delete from user where user ='hiru';



사용자에게 데이터베이스 사용권한을 부여하는 방법

MySQL 서버에 접속해서 모든 사용자가 Delete, Insert, Update의 권한이 있다면, 생각만해도 끔찍하지요. 

권한을 추가하고 삭제하기 위해서, GRANT와 REVOKE의 명령을 사용한다. 

SELECT, DELETE, UPDATE, INSERT의 모든 권한을 주기 위해서는 

 mysql > grant all privileges on dbname.table to userid@host identified by 'password';

mysql > grant select, insert, update on dbname.table to userid@host identified by 'password';

mysql > grant select, insert, update on dbname.table to userid@'192.168.%' identified by 'password';

(host가 192.168.X.X로 시작되는 모든 IP의 원격 접속을 허용한다는 의미입니다)



(dbname.table 대신 dbname.* 은 해당 database의 모든 table의 접근을 허용한다. *.*은 모든 접근을 가능하게 한다.) 


변경된 권한을 적용하기

mysql > flush privileges; 



권한을 삭제하는 방법

mysql > revoke all on dbname.table from username@host



권한을 확인하는 방법

mysql > show grants for userid@host

mysql > show grants for 'hiru'@'%';



출처: http://ourcstory.tistory.com/45 [쌍쌍바나나의 블로그]







-- join

-- inner join 은 두테이블 양쪽에 다 데이터가 있는 것만 출력 한다. 


select *

from buyTbl

   inner join userTbl                  -- userTbl 과 join 해라

      on buyTbl.userID = userTbl.userID   -- 설정해주기

WHERE buyTbl.userID = 'JYP';



-- 오류 예)

-- 전체를 다 출력할때는 해당 칼럼이 어느 테이블의 칼럼인지 명시해 줘야 한다.

select userID, name, prodName, addr, mobile1 + mobile2 as '연락처'

from buyTbl

   inner join userTbl                  -- userTbl 과 join 해라

      on buyTbl.userID = userTbl.userID;   -- 설정해주기



-- 해결 예)

-- 전체를 다 출력할때는 해당 칼럼이 어느 테이블의 칼럼인지 명시해 줘야 한다.

select buytbl.userID, name, prodName, addr, mobile1 + mobile2 as '연락처'

from buyTbl

   inner join userTbl                  -- userTbl 과 join 해라

      on buyTbl.userID = userTbl.userID;   -- 설정해주기



-- 해결 예)

-- 전체를 다 출력할때는 해당 칼럼이 어느 테이블의 칼럼인지 명시해 줘야 한다.

-- 회원이 10명인데 5명 밖에 안나온다. buy tbl 에 존재하는 회원만 나온다.

-- 한번도 구매하지 않은 이승기, 김경호, 임재범, 윤종신, 조관우는 나오지 않았다. 

-- inner join은 양쪽테이블에 모두 내용이 있는 것만 조인이 되는 방식이다. 

-- 다 보고 싶을 때는 outter 조인 사용한다.

select B.userID, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 as '연락처'

from buyTbl B

   inner join userTbl U                  -- userTbl 과 join 해라

      on B.userID = U.userID   -- 설정해주기

ORDER BY  U.userID;


-- 다대다 풀기



create table stdTbl( -- 학생 테이블

stdName varchar(10) not null primary key,

addr char(4) not null

);


create table clubTbl( -- 동아리테이블

clubName varchar(10) not null primary key,

roomNo char(4) not null

);



create table stdclubTbl(

num int auto_increment not null primary key,

stdName varchar(10) not null,

clubName varchar(10) not null,

foreign key(stdName) references stdTbl(stdName),

foreign key(clubName) references clubTbl(clubName)

);




insert into stdTbl values('김범수','경남'),('성시경','서울'),('조용필','경기'),('은지원','경북'),('바비킴','서울');


insert into ClubTbl values ('수영','101호'),('바둑','102호'),('축구','103호'),('봉사','104호');


INSERT INTO stdClubTbl values (null, '김범수', '바둑'),(null, '김범수', '축구'),

(null, '조용필', '축구'),(null, '은지원', '축구'),(null, '은지원', '봉사'),(null, '바비킴', '봉사');




-- 학생을 기준으로 학생 이름/지역/가입한 동아리/동아리 이름을 출력하기


select S.stdName, S.addr, C.clubname, C.roomNo

from stdTbl S

inner join stdclubTbl SC

   ON S.stdName = SC.stdName

inner join clubTbl C

   ON SC.clubName = C.clubName

order by S.stdName;



-- 동아리를 기준으로 가입한 학생 출력하기


select C.clubname, C.roomNo, S.stdName, S.addr

from stdTbl S

inner join stdclubTbl SC

   ON S.stdName = SC.stdName

inner join clubTbl C

   ON SC.clubName = C.clubName

order by C.clubname;



-- outter join 한쪽만 있어도 보여준다.

-- 전체 회원의 구매 기록을 출력

-- 왼쪽 테이블(userTbl) 모두 출력

select B.userID, U.name, B.prodName, U.addr, concat(mobile1 , U.mobile2) as '연락처'

from usertbl U

   left outer join buytbl B                  

      ON U.userID = B.userID

ORDER BY  U.userID;



-- right outter join 

select B.userID, U.name, B.prodName, U.addr, concat(mobile1 , U.mobile2) as '연락처'

from buytbl B

   right outer join usertbl U                  

      ON U.userID = B.userID

ORDER BY  U.userID;



-- 가입만 하고 활동안한 회원 보기


select U.userID, U.name, B.prodName, U.addr, concat(mobile1 , U.mobile2) as '연락처'

from usertbl U

   left outer join buytbl B                  

      ON U.userID = B.userID

        where B.prodName is null

ORDER BY  U.userID;



-- 동아리에 가입하지 않은 학생(학생이 기준이됨)

select S.stdName, S.addr, C.clubname, C.roomNo

from stdTbl S

left OUTER join stdclubTbl SC

   ON S.stdName = SC.stdName

left OUTER join clubTbl C

   ON SC.clubName = C.clubName

order by S.stdName;



-- 가입한 학생이 하나도 없는 동아리 (동아리가 기준이됨)


select  C.clubname, C.roomNo,S.stdName, S.addr

from stdTbl S

left OUTER join stdclubTbl SC

   ON S.stdName = SC.stdName

RIGHT OUTER join clubTbl C

   ON SC.clubName = C.clubName

order by  C.clubname;



-- 동아리에 가입하지 않은 학생도 출력, 학생이 없는 동아리도 출력

select S.stdName, S.addr, C.clubname, C.roomNo

from stdTbl S

left OUTER join stdclubTbl SC

   ON S.stdName = SC.stdName

left OUTER join clubTbl C

   ON SC.clubName = C.clubName


union


select  C.clubname, C.roomNo,S.stdName, S.addr

from stdTbl S

left OUTER join stdclubTbl SC

   ON S.stdName = SC.stdName

RIGHT OUTER join clubTbl C

   ON SC.clubName = C.clubName;

    

    

 

 

 


-- 셀프조인. 테이블하나에 자기가 조인


CREATE TABLE empTbl (emp char(3), manager char(3), empTel varchar(8));


insert into empTbl values('나사장',null,'0000');


insert into empTbl values('김재무','나사장','2222');


insert into empTbl values('김부장','김재무','2222-1');



insert into empTbl values('이부장','김재무','2222-2');



insert into empTbl values('우대리','이부장','2222-2-1');



insert into empTbl values('지사원','이부장','2222-2-2');



insert into empTbl values('이영업','나사장','1111');



insert into empTbl values('한과장','이영업','1111-1');



insert into empTbl values('최정보','나사장','3333');



insert into empTbl values('윤차장','최정보','3333-1');



insert into empTbl values('이주임','윤차장','3333-1-1');




-- 직송상관의 연락처


select A.emp AS '부하직원', B.emp AS '직속상관', B.empTel AS '직속상관연락처'

FROM empTbl A

   INNER JOIN empTbl B

      ON A.manager = B.emp

WHERE A.emp = '우대리';



-- union 중복된 열 하나만 출력 / union all 중복 상관 없이 다 출력/ 

-- select 문장 1, 과 2의 결과 열의 개수가 같아야 한다. 호환되는 데이터 형식도 같아야 한다.


select stdName, addr from stdTbl

union all

select clubName, roomNo from clubTbl;



-- not in 첫번째 쿼리의 결과 중에서 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문

-- 전화가 없는 사람 제외


select name ,concat(mobile1, mobile2) as '전화번호' from userTbl

where name not in

(select name from userTbl where mobile1 is null);


-- 반대로 전화가 없는 사람만 조회



select name ,concat(mobile1, mobile2) as '전화번호' from userTbl

where name in

(select name from userTbl where mobile1 is null);








-- sql 프로그래밍



drop procedure if exists ifProc; -- 기존에 만든적 있다면 삭제

delimiter $$

create procedure ifProc()

begin


   declare var1 int; -- 변수선언

    set var1 = 100; -- 변수에 값 대입

    

    if var1 = 100 then -- 만약 @var1이 100이라면

      select '100입니다';

   else

      select '100이 아닙니다';

   end if;

    

end $$

delimiter ;


call ifProc();




-- 해당직원 출력


drop procedure if exists ifProc2; -- 기존에 만든적 있다면 삭제

delimiter $$

create procedure ifProc2()

begin

   declare hireDATE DATE;  -- 입사일

    declare curDATE DATE; -- 오늘

    declare days int;     -- 근무한 일수


    select hire_date into hireDATE -- hire_date 열의 결과를 hireDATE에 대입

    from employees.employess

    where emp_no = 10001;

    

    set curDATE = current_date(); -- 현재 날짜

    SET days = datediff(curDATE,hireDATE); -- 날자의 차이 , 일 단위

    

    IF (days/365) >= 5 THEN -- 5년이 지났다면

      SELECT concat('입사한지',days, '일이나 지났습니다. 축하합니다.');

   else

      select '입사한지' + days + '일밖에 안되었네요. 열심히 일하세요';

    end if;

end $$

delimiter ;


call ifProc2();


-- case 


drop procedure if exists case2; -- 기존에 만든적 있다면 삭제

delimiter $$

create procedure case2()

begin


   declare point int;

    declare credit char(1);

    set point = 77;

case

    when point >= 90 then

      set credit = 'a';

   when point >= 80 then

      set credit = 'b';

   when point >= 70 then

      set credit = 'c';

   when point >= 60 then

      set credit = 'd';    

    else

      set credit = 'f';

   end case;

    select concat('취득점수==>', point), concat('학점==>', credit);

end $$

delimiter ;


call case2();

-- 데이터 형식




-- 문자 데이터 형식



-- 날짜와 시간 데이터 형식



-- MYSQL 고급 / 내장함수 및 JSON 예제


-- 날짜 함수

select cast('2020-10-19 12:23:21.123' AS DATE) as 'DATE' ;

select cast('2020-10-19 12:23:21.123' AS TIME) as 'TIME' ;

select cast('2020-10-19 12:23:21.123' AS DATETIME) as 'DATETIME' ;



-- 변수

SET @myVar1 =5;

SET @myVar2 =3;

SET @myVar3 =4.25;

SET @myVar4 ='가수이름=>';


select @myVar1;

select @myVar2 + @myVar3;

select @myVar4, Name from userTbl where height > 180;


-- limit에는 변수를 못쓰는데 한가지 방법이 있다.

-- prepare 구문


set @myVar1 = 1;

prepare myQuery

from 'select @myVar4, Name from userTbl order by height limit ?';

execute myQuery using @myVar1;



-- 데이터 변환 함수 cast() , avg() --명시적인 형변환 방법


select avg(amount) as '평균 구매 개수' from buyTbl;


select cast(avg(amount) as signed integer) as '평균 구매 개수' from buyTbl;


select convert(avg(amount),signed integer) as '평균 구매 개수' from buyTbl;


-- 날짜 변환 


select cast('2020/12/11' as date); 

select cast('2020$12$11' as date);

select cast('2020&12&11' as date);


-- 응용


select num, concat(cast(price as char(10)),'x',cast(amount as char(4)),'=') as '단가x수량',

price*amount as '구매액'

from buytbl;



-- 암시형 형변환


select '100' + '200' ; -- 문자와 문자를 더함 (정수로 변환되서 처리)

select concat('100','200'); -- 문자와 문자를 연결

select concat(100,'200'); -- 정수와 문자를 연결(정수가 문자로 연결되서 처리)

select 1 > '2mega'; -- 정수인 2로 변환되어 비교 틀린경우 0 반환

select 3 > '2MEGA'; -- 정수인 2로 변환되어 비교 맞는경우 1 반환

SELECT 0 = 'mega2'; -- 문자는 0으로 변환됨



-- mysql 내장함수


select if (100>200,'참이다','거짓이다'); -- 앞에 비교값이 참이면 두번째 값 출력, 거짓이면 세번째 값출력


select ifnull(null,'널이군요'), ifnull(100,'널이군요'); -- null 이면 두번째 값 출력


select nullif(100,100), ifnull(200,100); -- 두 값이 같으면 null 출력, 다르면 앞에 값 출력


select case 10

when 1 then '일'

        

when 5 then '오'

        

when 10 then '십'

        else '모름'

        end;

        

select ascii('A'), CHAR(65);        

SELECT bit_length('abc'),char_length('abc'),length('abc');

SELECT bit_length('가나다'),char_length('가나다'),length('가나다');

SELECT concat_ws('/','2020','01','01'); -- / 로 합치기


select elt(1,'하나','둘','셋'),

field('둘','하나','둘','셋'),

find_in_set('둘','하나,둘,셋'),

instr('하나둘셋','둘'),

locate('둘','하나둘셋');


-- 문자열 함수

select format(123456.123456,4); -- 소숫점 4자리까지 출력 반올림되서 출력됨

select bin(31),hex(31),oct(31); -- 2진수 16진수 8진수

select insert('abcdefg',3,4,'@@'),insert('abcdefg',1,2,'@@'); -- 지정 위치에 문자 대체

select left('abcdefg',3),right('abcdefg',3); -- 왼쪽 3번째 까지 해당되는 문자 출력

select lower('abcdefg'),upper('abcdefg'); -- 소문자로 변환, 대문자로 변환

select lpad('안녕',5,'###'),rpad('안녕',5,'###'); -- 문자길이를 총 5개로 잡고 ### 추가

select ltrim('  안녕'),rtrim('  안녕'); -- 왼쪽오른쪽 공백 제거

select trim('   공백제거  '), trim(both 'ㅋ' from 'ㅋㅋㅋ안녕ㅋㅋㅋㅋ');

select repeat('안녕',3); -- 반복

select replace('안녕 친구','안녕','hello'); -- 문자열 치환

select reverse ('hello'); -- 거꾸로 출력

select concat('안녕',space(10),'친구');

select substring('안녕친구들',3,2); -- 3번째 문자 부터 2번째 까치 출력

select substring_index('hi.my.name.is','.',2), -- 두번째 점 이후로 버린다.

substring_index('hi.my.name.is','.',-2);



-- 수학함수


select abs(-400); -- 절대값 구하기


select ceiling(4.7),floor(4.7),round(4.7);-- 올림 내림 반올림

select conv('AA',16,2),CONV(100,10,8);-- 진수끼리 변환 하는 함수 AAR가 16진수 인데 2진수로 바꿔

SELECT MOD(157,10),157%10, 157 MOD 10; -- 나머지 값 구하기

SELECT RAND(), floor(1+(RAND() * (6-1)));-- 임의의 값 구하기

SELECT truncate(12345.12345,2),TRUNCATE(12345.12345,-2);



-- 날짜및 시간 함수


SELECT adddate('2020-01-01',INTERVAL 31 DAY),ADDDATE('2020-01-01',INTERVAL 1 MONTH);


SELECT subdate('2020-01-01',INTERVAL 31 DAY),subdate('2020-01-01',INTERVAL 1 MONTH);


SELECT addtime('2020-01-01 23:59:59', '1:1:1'),addtime('15:00:00','2:10:10');

SELECT subtime('2020-01-01 23:59:59', '1:1:1'),SUBTIME('15:00:00','2:10:10');


select year(curdate()),MONTH(curdate()),DAYOFMONTH(curdate());

SELECT hour(curtime()), MINUTE(current_time()), SECOND(current_time()), MICROSECOND(current_time());

SELECT DATE(NOW()),TIME(NOW());

SELECT datediff('2020-01-01',NOW()), timediff('23:23:59','12:11:10');


SELECT dayofweek(curdate()),monthname(curdate()),dayofyear(curdate());

SELECT LAST_DAY('2020-02-01');

SELECT makedate(2020,32);

SELECT maketime(12,11,10);

SELECT period_add(202001,11),period_diff(202001,201812); 

SELECT quarter('2020-07-07');

SELECT time_to_sec('12:11:10');

SELECT current_user(),database();

SELECT * FROM usertbl;

SELECT found_rows();


SELECT ROW_COUNT(); -- UPDATE, DELETE 했을때 몇개 했는지 반환


SELECT version();


SELECT sleep(5);

SELECT '5초후에 보입니다';







-- JSON 데이터

SELECT JSON_OBJECT('name',name,'height',height) AS 'JSON 값'

FROM usertbl

WHERE height >= 180;



set @json='{ "userTBL":

[

{"name":"임재범","height":182},

{"name":"이승기","height":182},

{"name":"성시경","height":186}

    ]

}';



select json_valid(@json); -- JSON 데이터가 맞으면 1 반환

select json_search(@json,'one','성시경'); -- 성시경이 있는 위치 를 숫자로 반환

select json_extract(@json,'$.userTBL[2].name'); -- 위치를 주면 값을 반환

select json_insert(@json,'$.userTBL[0].mDate','2009-09-09'); -- 데이터 추가하기

select json_replace(@json,'$.userTBL[0].name','홍길동'); -- 데이터 치환

select json_remove(@json,'$.userTBL[0]') -- 삭제 



drop database if exists sqlDB; -- 만약 sqlDB가 있으면

CREATE database sqlDB;




use sqlDB;


create table userTbl -- 회원 테이블

( userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 ID (PK)

  name varchar(10) NOT NULL, -- 이름

  birthYear INT NOT NULL, -- 출생년도

  addr char(2) not null, -- 지역

  mobile1 char(3), -- 폰번호 앞자리 3 자리

  mobile2 char(8),

  height SMALLINT, -- 키

  mDate DATE -- 가입일

);



create TABLE buyTbl -- 회원 구매 테이블 (

( num int auto_increment not null primary key, -- 순번 pk

userID CHAR(8) NOT NULL, -- ID(FK) 

prodName CHAR(6) NOT NULL, -- 물품명

groupName char(4), -- 분류

price int not null, -- 단가

amount smallint NOT NULL, -- 수량

foreign key (userID) references userTbl(userID)

);





INSERT INTO userTbl values('LSG','이승기',1987,'서울','011','1111111',182,'2008-8-8');



INSERT INTO userTbl values('KBS','김범수',1979,'경남','011','2222222',173,'2012-4-4');


INSERT INTO userTbl values('KKH','김경호',1971,'전남','019','3333333',177,'2007-7-7');



INSERT INTO userTbl values('JYP','조용필',1950,'경기','011','4444444',166,'2009-4-4');



INSERT INTO userTbl values('SSK','성시경',1979,'서울',NULL,NULL,186,'2013-12-12');



INSERT INTO userTbl values('LJB','임재범',1963,'서울','016','6666666',182,'2009-9-9');



INSERT INTO userTbl values('YJS','윤종신',1969,'경남',NULL,NULL,170,'2005-5-5');



INSERT INTO userTbl values('EJW','은지원',1972,'경북','011','8888888',174,'2014-3-3');



INSERT INTO userTbl values('JKW','조관우',1965,'경기','018','9999999',172,'2013-5-5');



INSERT INTO userTbl values('BBK','바비킴',1973,'서울','010','0000000',176,'2013-5-5');



-- 구매테이블에 데이터 삽입



INSERT INTO buyTbl values(NULL, 'KBS','운동화',NULL, 30, 2);




INSERT INTO buyTbl values(NULL, 'KBS','노트북','전자', 1000, 1);



INSERT INTO buyTbl values(NULL, 'JYP','모니터','전자', 200, 1);




INSERT INTO buyTbl values(NULL, 'BBK','모니터','전자', 200, 5);




INSERT INTO buyTbl values(NULL, 'KBS','청바지','의류', 50, 3);




INSERT INTO buyTbl values(NULL, 'BBK','메모리','전자', 80, 10);



INSERT INTO buyTbl values(NULL, 'SSK','책','서적', 15, 5);



INSERT INTO buyTbl values(NULL, 'EJW','책','서적', 15, 2);



INSERT INTO buyTbl values(NULL, 'EJW','청바지','의류', 50, 1);



INSERT INTO buyTbl values(NULL, 'BBK','운동화',NULL, 30, 2);



INSERT INTO buyTbl values(NULL, 'EJW','책','서적', 15, 1);



INSERT INTO buyTbl values(NULL, 'BBK','운동화',NULL, 30, 2);



--  문자형 (CHAR , VARCHAR) 에 데이터를 입력하려면 홀따옴표 (' ')로 묶어 줘야 한다.


SELECT * FROM userTbl;

select * from buyTbl;



-- 회원 테이블의 결과 순서가 입력한 순서와 다른 이유는 userID를 Primary key 로 지정했기 때문에 자동으로 클러스터형 인텍스가

-- 생성돼서 입력 시에 userID열로 정렬이 되기 때문이다.





-- 기본적인 WHERE 절

-- SELECT 필드이름 FROM 테이블 이름 WHERE 조건식;

SELECT * 

FROM userTbl;


-- where 절 사용

SELECT * 

FROM userTbl 

where name = '김경호';



-- 관계 연산자 사용

-- 1970년 이후에 출생하고 신장이 182 이상인 사람의 아이디와 이름을 조회

select userID, name 

from userTbl 

where birthYear >= 1970 and height >=182;



-- 1970년 이후에 출생했거나 신장이 182 이상인 사람의 아이디와 이름을 조회 해보자

select userID, name

from userTbl 

where birthYear >= 1970 or height >=182;

-- 했거나는 or , 하고, 면, 그리고 는 and



-- between ..and, in() 그리고 like

-- 키가 180 - 183인 사람을 조회

select name, height 

from userTbl

where height >= 180 and height <= 183;


-- 방법2 (숫자나 연속적인 데이터에는 between and 사용)

select name, height 

from userTbl

where height between 180 and 183;



-- in()

-- 지역이 겨안ㅁ, 전남, 경북인 사람의 정보 확인


select name, addr

from userTbl

where addr = '경남'

or addr = '전남'

or addr = '경북';


-- 방법 2

select name, addr

from userTbl

where addr in('경남','전남','경북');



-- 문자열 내용검색 like

-- 성이 김씨인 사람

select name, height from userTbl where name like '김%';


-- 아무거나 앞에 한글자 오고 뒤에 이름만 매치

select name, height from userTbl where name like '_종신';

-- % 나 _ 가 검색할 문자열의 제일 앞에 들어가는 것은 mysql 성능에 나쁜 영향을 줄 수 있다. 예로 name열을 %용 이나

-- _ 용필등으로 검색하면 name 열에 인덱스가 있어도 인덱스를 사용하지 않고 전체 데이터를 검색하게 된다.




-- any/all/some 그리고 서브쿼리(쿼리 안에 또 다른 쿼리가 있는 것 )


-- 김경호 보다 키가 큰 사람 출력

-- 방법1

select name, height 

from userTBL

where height > 177;


-- 김경호의 키를 모른다고 가정했을 때


select name, height 

from userTBL

where height > (select height from userTBL where name = '김경호'); 

-- 서브 쿼리에서 출력되는 열은 반드시 하나 이어야 한다.



-- 지역이 경남인 사람의 키보다 크거나 같은 사람을 추출해 보자

select name, height 

from userTBL

where height >=(select height from userTBL where addr ='경남');

-- Error Code: 1242. Subquery returns more than 1 row




-- any 구문 (서브쿼리의 여러개 결과 중 한 가지만 만족해도 되며, all은 서브쿼리의 여러개의 결과를 모두 만족시켜야 한다.)

-- some은 any와 동일한 의미로 사용된다.

select name, height 

from userTBL

where height >= any (select height from userTBL where addr ='경남');



-- all

select name, height 

from userTBL

where height >= all (select height from userTBL where addr ='경남');



-- =any(서브쿼리)는 in(서브쿼리)와 동일한 의미

select name, height 

from userTBL

where height =any (select height from userTBL where addr ='경남');



-- in(서브쿼리)

select name, height 

from userTBL

where height in (select height from userTBL where addr ='경남');




-- 원하는 순서대로 정렬하여 출력 : order by 

-- 기본 오름차순 정렬

select name, mDate

from userTbl

order by mDate;


-- 내림차순

select name, mDate

from userTbl

order by mDate desc;


-- order by는 성능을 떨어뜨릴 소지가 있으므로 되도록 사용하지는 말것



-- 중복된것은 하나만 남기는 distinct


select addr from userTbl;


-- distic


select distinct addr from userTbl;


-- 출력 개수를 제한 하는 limit

-- 나이가 제일 많은 회원 5명


select name, birthYear

from userTbl

order by birthYear asc;


-- 전체 다볼필요 없는데, 전체를 보여준다...낭비


-- limit 사용

select name, birthYear

from userTbl

order by birthYear asc

limit 5;


-- limit 시작개수, 마지막수

select name, birthYear

from userTbl

order by birthYear asc

limit 0, 5;



-- 테이블을 복사하는 CREATE TABLE ...SELECT 

-- 형식 : create TABLE 새로운 테이블 (SELECT 복사할열 FROM 기존 테이블)

-- 단 pk나 fk 등의 제약 조건은 복사되지 않는다.

CREATE TABLE buyTbl2(select * from buyTbl);

select * from buyTbl2;



-- group by 및 having 그리고 집계 함수

-- group by 절은 그룹으로 묶어주는 역할을 한다.

-- 구매 테이블에서 사용자가 구매한 물품의 개수 구하기


select userID, amount 

from buyTbl

order by userID;

-- 결과는 사용자별로 여러번의 물건 구매가 이루어져 각각의 행이 별도로 출력된다.


-- 집계함수 사용. 집계함수는 주로 GROUP BY 저로가 함께 쓰이며 데이터를 그룹화 해주는 기능을 한다.


SELECT userID, SUM(amount)

from buyTbl 

group by userID;



SELECT userID AS '사용자ID', SUM(amount) AS '총 구매 개수'

from buyTbl 

group by userID;



-- 구매액의 총합출력 . 가격 * 수량


SELECT userID AS '사용자ID', SUM(price*amount) AS '총 구매액'

from buyTbl 

group by userID;



-- 전체 구매자가 구매한 물품의 개수

select avg(amount) as '평균 구매 개수' 

from buyTbl;



-- 각 사용자별로 한 번 구매시 물건을 평균 몇개 구매했는지 보자

select userID,avg(amount) as '평균 구매 개수' 

from buyTbl

group by userID;



-- 전체 회원의 수 카운트


SELECT COUNT(*) FROM userTbl;


-- 휴대폰 이 있는 회원만 조회

SELECT COUNT(mobile1) FROM userTbl;



-- 가장 큰 키와 가장 작은키의 회원 이름과 키를 출력


select name, max(height), min(height) from userTbl;


-- 가장 큰키와 가장 작은 키는 나왔지만 이름은 하나 뿐이라서 어떤 것에 해당하는지 알 수가 없다.


select name, max(height), min(height) 

from userTbl

group by name;


-- 그냥 모두 다 나왔다.


-- 앞에서 배운 서브 쿼리와 조합해보자


select name, height

from userTbl

where height = (select max(height) from userTbl)

or height = (select min(height) from userTbl);




-- having 절



-- 앞에서 했던 sum()을 사용해서 사용자별 총구매액을 구해보자


SELECT userID AS '사용자ID', SUM(price*amount) AS '총 구매액'

from buyTbl 

group by userID;


-- 이 중에서 총 구매액이 1,000 이상인 사용자에게만 사은품을 증정하고 싶다면?


SELECT userID AS '사용자ID', SUM(price*amount) AS '총 구매액'

from buyTbl

where SUM(price*amount) > 1000 

group by userID;


-- 오류가 뜬다  having을 사용! having은 집계함수에 대한 조건!



SELECT userID AS '사용자ID', SUM(price*amount) AS '총 구매액'

from buyTbl 

group by userID

having sum(price*amount);




-- rollup

-- 총합 또는 중간합계가 필요하다면 grop by 절과 함께 with rollup 문을 사용하면 된다.

-- 만약 분류 groupname 별로 합계 및 그 총합을 구하고 싶다면 다음의 구문을 사용하자


select num, groupName, sum(price* amount) as '비용'

from buyTbl

group by groupName, num

with rollup;



-- 소합계 및 총합계만 필요하다면 num을 빼면된다


select groupName, sum(price* amount) as '비용'

from buyTbl

group by groupName

with rollup;



-- sql의 분류

/*

DML (데이터 조작) 조회, 삽입, 수정, 삭제.

- 트랜젝션이란 임시로 적용시키는 것. 실수 시 임시로 적용한 것 취소


DDL

-데이터베이스,테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할 

-CREATE, DROP, ALTER (ROLLBACK 이나 COMMIT 을 못시킴)


DCL

- 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문

- GRANT/REVOKE/DENY 등


*/

-- 자동으로 증가하는 AUTO_INCREMENT

-- AUTO_INCREMENT로 지정할때 PK 또는 UNIQUE로 지정해줘야 하고 숫자만 가능


-- 대량의 샘플 데이터 생성

/*

CREATE table TEST1(id int, name varchar(50),lname varchar(50));

insert into TEST1

SELECT EMP_NO, FIRST_NAME, LAST_NAME

FROM EMPLOYESS.EMPLOYESS;


*/


-- 데이터의 수정 update

/*

UPDATE 테이블이름 

SET 열1=값1, 열2=값2

WHERE 조건;

*/

-- WHERE절은 생략이 가능하지만 생략하면 테이블의 전체의 행이 변경된다.

-- 구매 테이블에서 현재의 단가가 모두 1.5배 인상되었다면..

-- UPDATE buyTbl set price = price * 1.5;



-- 테이블 삭제 종류 3가지


-- delete from tbl; -- 가장 오래 거린다.

-- drop table tbl; -- 테이블 자체를 삭제한다.

-- truncate table tbl; -- 테이블 구조를 남겨 놓고 데이터만 삭제


-- 조건부 데이터 입력, 변경

-- 첫행에 오류가 발생해도 뒷행은 실행 되게 하는 법


-- ignore

-- insert ignore into membertbl value('bbk','비비코','미국')

-- insert ignore into membertbl value('djm','동짜몽','서울')


트리거 


트리거는 테이블에 삽입 , 수정, 삭제등의 작업을 할때 자동으로 작동되는 개체로 

프로시저와 비슷한 모양을 갖는다.

하지만 트리거에는 스토어드 프로시저와 달리 IN OUT 매개변수를 사용할 수도 없다. 



-- 테이블 생성 


SELECT * FROM testTbl;


create TABLE IF NOT EXISTS testTbl5(id INT, txt VARCHAR(10));


INSERT INTO testTbl5 VALUES(1,'EXID');



INSERT INTO testTbl5 VALUES(2,'AFTERS');



INSERT INTO testTbl5 VALUES(3,'IOI');



DROP TRIGGER IF EXISTS testTrg;


DELIMITER //

CREATE TRIGGER testTrg -- 트리거 이름 

AFTER DELETE -- 삭제 후에 작동되도록 지정

    ON testTbl5 -- 트리거를 부착할 테이블

    for each row -- 각행마다 적용시킴  

    

begin 

set @msg = 'delete complet'; -- 트리거 실행시 작동되는 코드 


end //

delimiter ;





-- 데이터를 삽입 , 수정, 삭제 해보자 


set @msg = '';


insert into testTbl5 VALUES(4,'nine');

select @msg;


update testTbl5 set txt = 'apink' where id = 3;

select @msg;


delete from testTbl5 where id = 4;

select @msg;





--  paste table make


create TABLE IF NOT EXISTS checkcheck(id INT, txt VARCHAR(10));


-- update


DROP TRIGGER IF EXISTS backupTbl;

DELIMITER $$

CREATE TRIGGER backupTbl

AFTER UPDATE

    ON testTbl5

for each row

begin

insert into checkcheck VALUES(OLD.id,OLD.txt);

    

END //

DELIMITER ;



-- delete



DROP TRIGGER IF EXISTS backup_del_Tbl;

DELIMITER $$

CREATE TRIGGER backup_del_Tbl

AFTER delete

    ON testTbl5

for each row

begin

insert into checkcheck VALUES(OLD.id,OLD.txt);

END //

delimiter ;



-- CHECK TABLE


SELECT * FROM testTbl5;



update testTbl5 set txt ='big' where id = 1;


delete from testTbl5 where id=2;

commit;


select * from checkcheck;



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
트리거 
 
 
 
트리거는 테이블에 삽입 , 수정, 삭제등의 작업을 할때 자동으로 작동되는 개체로 
 
프로시저와 비슷한 모양을 갖는다.
 
하지만 트리거에는 스토어드 프로시저와 달리 IN OUT 매개변수를 사용할 수도 없다. 
 
 
 
 
 
-- 테이블 생성 
 
 
 
SELECT * FROM testTbl;
 
 
 
create TABLE IF NOT EXISTS testTbl5(id INT, txt VARCHAR(10));
 
 
 
INSERT INTO testTbl5 VALUES(1,'EXID');
 
 
 
 
 
INSERT INTO testTbl5 VALUES(2,'AFTERS');
 
 
 
 
 
INSERT INTO testTbl5 VALUES(3,'IOI');
 
 
 
 
 
DROP TRIGGER IF EXISTS testTrg;
 
 
 
DELIMITER //
 
CREATE TRIGGER testTrg -- 트리거 이름 
 
    AFTER DELETE        -- 삭제 후에 작동되도록 지정
 
    ON testTbl5            -- 트리거를 부착할 테이블
 
    for each row        -- 각행마다 적용시킴  
 
    
 
begin 
 
    set @msg = 'delete complet'-- 트리거 실행시 작동되는 코드 
 
 
 
end //
 
delimiter ;
 
 
 
 
 
 
 
 
 
-- 데이터를 삽입 , 수정, 삭제 해보자 
 
 
 
set @msg = '';
 
 
 
insert into testTbl5 VALUES(4,'nine');
 
select @msg;
 
 
 
update testTbl5 set txt = 'apink' where id = 3;
 
select @msg;
 
 
 
delete from testTbl5 where id = 4;
 
select @msg;
 
 
 
 
 
 
 
 
 
--  paste table make
 
 
 
create TABLE IF NOT EXISTS checkcheck(id INT, txt VARCHAR(10));
 
 
 
-- update
 
 
 
DROP TRIGGER IF EXISTS backupTbl;
 
DELIMITER $$
 
CREATE TRIGGER backupTbl
 
    AFTER UPDATE
 
    ON testTbl5
 
    for each row
 
begin
 
    insert into checkcheck VALUES(OLD.id,OLD.txt);
 
    
 
END //
 
DELIMITER ;
 
 
 
 
 
-- delete
 
 
 
 
 
DROP TRIGGER IF EXISTS backup_del_Tbl;
 
DELIMITER $$
 
CREATE TRIGGER backup_del_Tbl
 
    AFTER delete
 
    ON testTbl5
 
    for each row
 
begin
 
    insert into checkcheck VALUES(OLD.id,OLD.txt);
 
END //
 
delimiter ;
 
 
 
 
 
-- CHECK TABLE
 
 
 
SELECT * FROM testTbl5;
 
 
 
 
 
update testTbl5 set txt ='big' where id = 1;
 
 
 
delete from testTbl5 where id=2;
 
commit;
 
 
 
select * from checkcheck;
cs


커서


커서는 테이블의 여러행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식


순서 : 파일을 연다 -> 처음 데이터를 읽는다. -> 파일의 끝까지 반복한다. -> 파일을 닫는다. 


명령어


커서선언 DECLARE CURSOR


반복조건 선언 DECLARE CONTINUE HANDLER

(더이상 읽을 행이 없을 경우에, 실행할 내용 설정)



커서 열기 OPEN


커서에서 데이터 가져오기 FETCH

(LOOP ~ END LOOP 문으로 반복 구간 지정 )


데이터 처리 


커서닫기 




-- 예제 테이블 생성 및 데이타 생성 


CREATE TABLE Member (

   userid VARCHAR(20),

   `point` INT

) ENGINE = InnoDB ROW_FORMAT = DEFAULT;




insert into Member (UserID ) VALUES ( 'User01') ; 

insert into Member (UserID ) VALUES ( 'User02') ; 

insert into Member (UserID ) VALUES ( 'User03') ; 

insert into Member (UserID ) VALUES ( 'User04') ; 

commit;



select * from Member;





drop PROCEDURE if EXISTS curdemo


delimiter $$

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done boolean DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  

  -- 커서로 만들 데이타 값들

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  -- 커서가 마지막에 도착할 때의 상태값

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


  -- 커서를 연다. 

  OPEN cur1;

 

  -- Loop 가 돌아간다. 

  read_loop: LOOP

 

  -- 커서로 만드어진 데이타를 돌린다. 

  FETCH cur1 INTO vUserID ;

 

    SET vRowCount = vRowCount +1 ; 

    

    -- 커서가 마지막 로우면 Loop를 빠져나간다. 

    IF done THEN

      LEAVE read_loop;

    END IF;

 

  END LOOP;


  SELECT vRowCount  ; 

  -- 커서를 닫는다. 

  CLOSE cur1;

 

END;

delimiter;



Call curdemo() ; 







 -- 예제 테이블 생성


CREATE TABLE PointHistory (

   UserID VARCHAR(20),

   PointDate DATE,

   PointValue INT

) ENGINE = InnoDB ROW_FORMAT = DEFAULT;


-- 예제 데이타 생성


insert into PointHistory (  UserID  ,PointDate  ,PointValue) 

VALUES (   'User02'   ,'2014-01-01'  , 10  )



-- 이미 있는 프로시져 삭제한다. ..


DROP PROCEDURE IF EXISTS curdemo ;

delimiter $$

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  DECLARE vPointValue int ; 

    

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


  OPEN cur1; 


  read_loop: LOOP

 

  FETCH cur1 INTO vUserID ;

  

    -- Not Found Handler 값 변화 살펴보자.

  SELECT done ;  

 

    -- 포인트 테이블의 값을 읽어 온다. 

    SELECT PointValue into vPointValue FROM pointhistory

    WHERE UserID = vUserID ; 

    

    --  회원테이블의 포인트 값에 업데이트 한다. 

    UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 

    

    -- 커서가 몇번을 도는지 알아 본다. 

    SET vRowCount = vRowCount + 1  ; 

    

    IF done THEN

      LEAVE read_loop;

    END IF;

 

  END LOOP;


  SELECT vRowCount ; 

  CLOSE cur1;

 

END;

delimiter;



Call curdemo() ;



-- 실행 결과 없다.  → 우리가 원하는 바가 아니다. vRowCount 가 1이다. 1번 돌았다.




-- 3. 해결 방법 

-- 

-- - 커서의 DECLARE CONTINUE HANDLER FOR NOT FOUND 는 커서의 집합이 없을 때이기도 하지만 커서안에서 다른 쿼리문의 집합이 없을 때도 True 을 반환한다. 

-- - 그래서 Mysql 은 커서가 이상해 이런 얘기가 나오는 거다. 

-- - MSSQL은 커서의 집합만을 비교하여 마지막 커서행인지 판단해 주는데 mysql 의 경우 커서뿐만 아니라 커서안의 select 의 집합도 NOT FOUND로 판단하고 있다. 

-- - 그래서 커서안의 select  의 집합의  NOT FOUND와 Curosor 의 NOT FOUND을 구분하여 줄 필요가 있다. 



DROP PROCEDURE IF EXISTS curdemo ;

delimiter $$

CREATE PROCEDURE curdemo()

BEGIN

  DECLARE done INT DEFAULT FALSE;

  DECLARE vRowCount INT DEFAULT 0 ;

  DECLARE vUserID varchar(20);

  DECLARE vPointValue int ;   

  

  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;

 

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE ;


  OPEN cur1; 


  REPEAT

 

  FETCH cur1 INTO vUserID ;


  -- Not Found Handler 값 변화 살펴보자.

  SELECT done ;  


    -- 커서가 마지막이 아니라면    

    IF NOT done THEN      

    

      SELECT PointValue into vPointValue FROM pointhistory

      WHERE UserID = vUserID ; 

    

      UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 

      -- SELECT concat(vUserID, '', vPointValue)  ; 

    

      SET vPointValue = 0 ; 


      -- 위의 select 가 조회 데이타가 없어서 not found 되어

      -- fetch 문을 빠져나가는 걸 방지한다.  

      SET done = False ;        


    END IF;  

  

  UNTIL DONE END REPEAT;

  CLOSE cur1;

END;

delimiter;


-- 위의 예제는 커서가 마지막행을 만나기 전에 SELECT 문에서 조회값이 없는 경우 Not Found 도 발생하는 걸 인위적으로  SET done = False 으로 해결 하고 있다. 


Call curdemo() 



출처http://bizadmin.tistory.com/entry/MySQL-Fetch-Cursor-%EB%AC%B8-%EC%82%AC%EC%9A%A9%EB%B0%A9%EB%B2%95



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
커서
 
 
 
커서는 테이블의 여러행을 쿼리한 후에, 쿼리의 결과인 행 집합을 한 행씩 처리하기 위한 방식
 
 
 
순서 : 파일을 연다 -> 처음 데이터를 읽는다. -> 파일의 끝까지 반복한다. -> 파일을 닫는다. 
 
 
 
명령어
 
 
 
커서선언 DECLARE CURSOR
 
 
 
반복조건 선언 DECLARE CONTINUE HANDLER
 
(더이상 읽을 행이 없을 경우에, 실행할 내용 설정)
 
 
 
 
 
커서 열기 OPEN
 
 
 
커서에서 데이터 가져오기 FETCH
 
(LOOP ~ END LOOP 문으로 반복 구간 지정 )
 
 
 
데이터 처리 
 
 
 
커서닫기 
 
 
 
 
 
 
 
-- 예제 테이블 생성 및 데이타 생성 
 
 
 
CREATE TABLE Member (
 
   userid VARCHAR(20),
 
   `point` INT
 
ENGINE = InnoDB ROW_FORMAT = DEFAULT;
 
 
 
 
 
 
 
insert into Member (UserID ) VALUES ( 'User01') ; 
 
insert into Member (UserID ) VALUES ( 'User02') ; 
 
insert into Member (UserID ) VALUES ( 'User03') ; 
 
insert into Member (UserID ) VALUES ( 'User04') ; 
 
commit;
 
 
 
 
 
select * from Member;
 
 
 
 
 
 
 
 
 
drop PROCEDURE if EXISTS curdemo
 
 
 
delimiter $$
 
CREATE PROCEDURE curdemo()
 
BEGIN
 
  DECLARE done boolean DEFAULT FALSE;
 
  DECLARE vRowCount INT DEFAULT 0 ;
 
  DECLARE vUserID varchar(20);
 
  
 
  -- 커서로 만들 데이타 값들
 
  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;
 
 
 
  -- 커서가 마지막에 도착할 때의 상태값
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 
 
  -- 커서를 연다. 
 
  OPEN cur1;
 
 
 
  -- Loop 가 돌아간다. 
 
  read_loop: LOOP
 
 
 
  -- 커서로 만드어진 데이타를 돌린다. 
 
  FETCH cur1 INTO vUserID ;
 
 
 
    SET vRowCount = vRowCount +1 ; 
 
    
 
    -- 커서가 마지막 로우면 Loop를 빠져나간다. 
 
    IF done THEN
 
      LEAVE read_loop;
 
    END IF;
 
 
 
  END LOOP;
 
 
 
  SELECT vRowCount  ; 
 
  -- 커서를 닫는다. 
 
  CLOSE cur1;
 
 
 
END;
 
delimiter;
 
 
 
 
 
Call curdemo() ; 
 
 
 
 
 
 
 
 
 
 
 
 
 
 -- 예제 테이블 생성
 
 
 
CREATE TABLE PointHistory (
 
   UserID VARCHAR(20),
 
   PointDate DATE,
 
   PointValue INT
 
ENGINE = InnoDB ROW_FORMAT = DEFAULT;
 
 
 
-- 예제 데이타 생성
 
 
 
insert into PointHistory (  UserID  ,PointDate  ,PointValue) 
 
VALUES (   'User02'   ,'2014-01-01'  , 10  )
 
 
 
 
 
-- 이미 있는 프로시져 삭제한다. ..
 
 
 
DROP PROCEDURE IF EXISTS curdemo ;
 
delimiter $$
 
CREATE PROCEDURE curdemo()
 
BEGIN
 
  DECLARE done INT DEFAULT FALSE;
 
  DECLARE vRowCount INT DEFAULT 0 ;
 
  DECLARE vUserID varchar(20);
 
  DECLARE vPointValue int ; 
 
    
 
  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;
 
 
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 
 
  OPEN cur1; 
 
 
 
  read_loop: LOOP
 
 
 
  FETCH cur1 INTO vUserID ;
 
  
 
    -- Not Found Handler 값 변화 살펴보자.
 
  SELECT done ;  
 
 
 
    -- 포인트 테이블의 값을 읽어 온다. 
 
    SELECT PointValue into vPointValue FROM pointhistory
 
    WHERE UserID = vUserID ; 
 
    
 
    --  회원테이블의 포인트 값에 업데이트 한다. 
 
    UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 
 
    
 
    -- 커서가 몇번을 도는지 알아 본다. 
 
    SET vRowCount = vRowCount + 1  ; 
 
    
 
    IF done THEN
 
      LEAVE read_loop;
 
    END IF;
 
 
 
  END LOOP;
 
 
 
  SELECT vRowCount ; 
 
  CLOSE cur1;
 
 
 
END;
 
delimiter;
 
 
 
 
 
Call curdemo() ;
 
 
 
 
 
-- 실행 결과 없다.  → 우리가 원하는 바가 아니다. vRowCount 가 1이다. 1번 돌았다.
 
 
 
 
 
 
 
-- 3. 해결 방법 
 
-- 
 
-- - 커서의 DECLARE CONTINUE HANDLER FOR NOT FOUND 는 커서의 집합이 없을 때이기도 하지만 커서안에서 다른 쿼리문의 집합이 없을 때도 True 을 반환한다. 
 
-- - 그래서 Mysql 은 커서가 이상해 이런 얘기가 나오는 거다. 
 
-- - MSSQL은 커서의 집합만을 비교하여 마지막 커서행인지 판단해 주는데 mysql 의 경우 커서뿐만 아니라 커서안의 select 의 집합도 NOT FOUND로 판단하고 있다. 
 
-- - 그래서 커서안의 select  의 집합의  NOT FOUND와 Curosor 의 NOT FOUND을 구분하여 줄 필요가 있다. 
 
 
 
 
 
DROP PROCEDURE IF EXISTS curdemo ;
 
delimiter $$
 
CREATE PROCEDURE curdemo()
 
BEGIN
 
  DECLARE done INT DEFAULT FALSE;
 
  DECLARE vRowCount INT DEFAULT 0 ;
 
  DECLARE vUserID varchar(20);
 
  DECLARE vPointValue int ;   
 
  
 
  DECLARE cur1 CURSOR FOR SELECT Userid FROM Member;
 
 
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE ;
 
 
 
  OPEN cur1; 
 
 
 
  REPEAT
 
 
 
  FETCH cur1 INTO vUserID ;
 
 
 
  -- Not Found Handler 값 변화 살펴보자.
 
  SELECT done ;  
 
 
 
    -- 커서가 마지막이 아니라면    
 
    IF NOT done THEN      
 
    
 
      SELECT PointValue into vPointValue FROM pointhistory
 
      WHERE UserID = vUserID ; 
 
    
 
      UPDATE Member Set point = vPointValue WHERE UserID = vUserID ; 
 
      -- SELECT concat(vUserID, '', vPointValue)  ; 
 
    
 
      SET vPointValue = 0 ; 
 
 
 
      -- 위의 select 가 조회 데이타가 없어서 not found 되어
 
      -- fetch 문을 빠져나가는 걸 방지한다.  
 
      SET done = False ;        
 
 
 
    END IF;  
 
  
 
  UNTIL DONE END REPEAT;
 
  CLOSE cur1;
 
END;
 
delimiter;
 
 
 
-- 위의 예제는 커서가 마지막행을 만나기 전에 SELECT 문에서 조회값이 없는 경우 Not Found 도 발생하는 걸 인위적으로  SET done = False 으로 해결 하고 있다. 
 
 
 
Call curdemo() 
 
 
 
 
 
출처: http://bizadmin.tistory.com/entry/MySQL-Fetch-Cursor-%EB%AC%B8-%EC%82%AC%EC%9A%A9%EB%B0%A9%EB%B2%95
 
 
 
 
 
 
 
 
 
 
cs




MySQL 뷰 생성

CREATE VIEW 뷰이름

AS SELECT문


-- student 테이블로부터 모든 학생의 학번과 학년, 반을 가지고 있는 뷰 테이블을 생성하라


create view student_1

as select * from student;


select * from student_1;




-- 등록한 학생의 학번과 등록년도에 대한 뷰테이블을 생성하라

-- 


create view student_2

as select s.stu_no, a.att_year

from student s, attend a

where s.stu_no = a.stu_no;


select * from student_2;



create view v_fee

as select stu_no, fee_year

from fee

where fee_year is not null;


select * from v_fee;



-- 재학생의 학번과 이름, 성별, 입학년도, 생년, 월, 일, 나이에 대한 뷰 테이블을 생성하라



create view student_6

as select stu_no, stu_name, id_num,substring(id_num,8,1) "sex", substring(id_num,1,2),substring(id_num,3,2),substring(id_num,5,2), 

year(now()) - birth_year +1 "age"

from student;


select * from student_6;



-- 재학생 중 21세 이상인 여학생의 학번, 이름, 성별, 출생년도, 나이를 출력하라

-- 


select stu_no, stu_name 

from student_6

where age > 20 and sex = 2;



-- 재학생중 21세에 해당하는 학생의 성년식 행사를 위한 명단을 출하라. 단, 출력 형식은 학과, 

-- 학년, 학번, 이름, 생년, 월, 일, 나이를 출력하라 

-- 


-- 뷰 테이블에서 재학생 중 20세 이상이고, 2000~2004년에 입학한 학생을 구하라 

-- 



-- 뷰 테이블을 삭제하라. 

-- 

drop viw student_8;


-- 전라남도 여수지역 (우편번호 550)에 살고 있는 학생의 학번, 이름, 현주소의 우편번호 3자리를

-- 가지는 뷰 테이블을 생성하라 

--



create view v_address(hak,irum, hpost) as

    select stu_no, stu_name, substring(post_no,1,3)

    from student

    where substring(post_no,1,3) = '550'

    select * from v_address;

    

 

-- 등록금 총액별로 학생 인원 수 현황을 생성하는 뷰테이블을 생성하라 

-- 


create view v_feetotal1(fee_total,row_total) as

select fee_pay,count(*)

from fee

GROUP BY fee_pay


select * from v_feetotal1;


-- 등록금 총액별로 학생 인원 수 현황을 생성한 뷰 테이블 의 내용을 출력하라 

-- 


select fee_total, row_total

from v_feetotal1;


-- 학적테이블에서 1985년 이전에 출생한 모든 학생에 대한 뷰 테이블을 생성하라

-- 


create view v_old as

select *

from student

where birth_year < 1985;



-- 

-- 위의 테이블에서 학번이 20001001인 학생의 출생년도를 1986으로 변경하라 

-- 


update v_old

set birth_year = '1986'

where stu_no = '20001001';



select stu_no, stu_name, birth_year from v_old;



select stu_no, stu_name, birth_year from student;



update student

set birth_year = '1981'

where stu_no = '20001001';



-- 

-- 학적테이블에서 1985년 이전에 출생한 모든 학생에 대한 뷰테이블을 생성하라 

-- 

create view v_old12 as

select *

from student

where birth_year < 1985

with CHECK OPTION;


-- 위 테이블에서 학번이 20001001인 학생의 출생 년도를 1986으로 변경하라 


update v_old12

    set birth_year = '1986'

    where stu_no ='20001001'; 



-- 등록 테이블로 부터 학번과 학생별 등록금 납입 총액의 합계로 구성하는 뷰 테이블을 생성하라

-- 


CREATE view totals (stu_no, fee_total)as

select stu_no, sum(fee_pay)

from fee

GROUP BY stu_no;


-- 뷰 테이블로 부터 학생별 등록금 납입총액의 최대값을 구하라  

-- 


select max(fee_total)

from totals


-- 뷰 테이블과 학적테이블을 이용하여  학번, 이름, 납입 총액을 출력하라.

-- 


select s.stu_no, s.stu_name, t.fee_total

from student s, totals t

where s.stu_no = t.stu_no


-- 수강 신청한 학생 중에서 뷰 테이블에 존재하는 학생의 학번, 납입총액을 출력하라

-- 


select stu_no, fee_total

from totals

where stu_no in

(select stu_no from attend);


-- 뷰 테이블에 존재하는 학생의 학번, 납입총액을 출력하라. 단, 출력 순서는 납입 총액 내림차순으로 

-- 정렬한다. 

-- 

select stu_no, fee_total

from totals

order by fee_total desc;


-- 뷰 테이블에 존재하는 학생의 학번과 동아리 테이블에 존재하는 학생의 학번을 학번 오름차순으로 정렬하여 출력하라.

-- 


select stu_no from totals

union

select stu_no from circle

order by stu_no;


-- 수강신청 테이블에서 학생별, 수강년도별, 학기별로 그룹을 만들고 이 그룹의 수강신청 학점이 

-- 5학점 이상인 학생의 학번, 연도, 학기, 수강학점계를 뷰 테이블 을 생성하라 

-- 



select stu_no, att_year, att_term

from attend

group by stu_no, att_year, att_term

having sum(att_point) > 5;



-- 적어도 한번 이상 등록한 학생들을 학적테이블과 동일한 가상 테이블 aa 를 생성하라 

-- 

create view aa as

select * from student

where stu_no 

IN

(select stu_no

from fee);




-- 적어도 한번은 등록하고 남자인 학생의 학번과 이름을 출력하라 

-- 


select stu_no, stu_name

from aa

where substring(id_num,8,1)=1;



-- 응용분야

-- 

-- 

-- 학급 3반에서 등록한 학생의 학번과 반을 출력하라

-- 


select stu_no, class

from student

where stu_no

in

(select stu_no from fee)

and class = 3;


-- 학급별로 등록한 학생의 학급 통계를 출력하라 

-- 


select class, count(*)

from  student

where stu_no

in

(select stu_no from fee)

GROUP BY class;



-- 등록한 학생의 학번과 반으로 구성되는 테이블 aaaa를 생성하라

-- 


create view aaaa as

select * from student

where stu_no 

IN

(select stu_no

from fee);



select * from aaaa where class=3;


select count(*) from aaaa GROUP BY class;


-- 재학생 중 수강신쳥 연도, 학기와 등록년도, 학기가 동일한 학생의 학번과 이름, 수강년도, 수강학기를 

-- 출력하라 

-- 


-- 재학생 중 2006년에 수강 신청을 했으며 2006년에 등록한 학생의 평균 등록금보다 더 많은 등록금을 납부해야하고, 성별이 남자인 학생의 학번과 이름을 출력하라


-- 1

CREATE view greater1 as

select DISTINCT stu_no

from fee

where fee_total > (select avg(fee_total) from fee where fee_year=2006);



-- 2

create view first as

select DISTINCT stu_no

from attend

where stu_no in 

(select stu_no from attend where att_year = '2006')



-- 3 

select stu_no, stu_name

from student

where SUBSTRING(ID_NUM,8,1) = '1'

AND STU_NO IN

(SELECT STU_NO FROM GREATER)

AND STU_NO IN 

(SELECT STU_NO FROM FIRST);



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
use haksa;
 
 
 
MySQL 뷰 생성
CREATE VIEW 뷰이름
AS SELECT문
 
-- student 테이블로부터 모든 학생의 학번과 학년, 반을 가지고 있는 뷰 테이블을 생성하라
 
create view student_1
as select * from student;
 
select * from student_1;
 
 
 
-- 등록한 학생의 학번과 등록년도에 대한 뷰테이블을 생성하라
-- 
 
create view student_2
as select s.stu_no, a.att_year
from student s, attend a
where s.stu_no = a.stu_no;
 
select * from student_2;
 
 
create view v_fee
as select stu_no, fee_year
from fee
where fee_year is not null;
 
select * from v_fee;
 
 
-- 재학생의 학번과 이름, 성별, 입학년도, 생년, 월, 일, 나이에 대한 뷰 테이블을 생성하라
 
 
create view student_6
as select stu_no, stu_name, id_num,substring(id_num,8,1"sex", substring(id_num,1,2),substring(id_num,3,2),substring(id_num,5,2), 
year(now()) - birth_year +1 "age"
from student;
 
select * from student_6;
 
 
-- 재학생 중 21세 이상인 여학생의 학번, 이름, 성별, 출생년도, 나이를 출력하라
-- 
 
select stu_no, stu_name 
from student_6
where age > 20 and sex = 2;
 
 
-- 재학생중 21세에 해당하는 학생의 성년식 행사를 위한 명단을 출하라. 단, 출력 형식은 학과, 
-- 학년, 학번, 이름, 생년, 월, 일, 나이를 출력하라 
-- 
 
-- 뷰 테이블에서 재학생 중 20세 이상이고, 2000~2004년에 입학한 학생을 구하라 
-- 
 
 
-- 뷰 테이블을 삭제하라. 
-- 
drop viw student_8;
 
 
-- 전라남도 여수지역 (우편번호 550)에 살고 있는 학생의 학번, 이름, 현주소의 우편번호 3자리를
-- 가지는 뷰 테이블을 생성하라 
--
 
 
    create view v_address(hak,irum, hpost) as
    select stu_no, stu_name, substring(post_no,1,3)
    from student
    where substring(post_no,1,3= '550'
    
    select * from v_address;
    
 
-- 등록금 총액별로 학생 인원 수 현황을 생성하는 뷰테이블을 생성하라 
-- 
 
create view v_feetotal1(fee_total,row_total) as
select fee_pay,count(*)
from fee
GROUP BY fee_pay
 
select * from v_feetotal1;
 
-- 등록금 총액별로 학생 인원 수 현황을 생성한 뷰 테이블 의 내용을 출력하라 
-- 
 
select fee_total, row_total
from v_feetotal1;
 
-- 학적테이블에서 1985년 이전에 출생한 모든 학생에 대한 뷰 테이블을 생성하라
-- 
 
create view v_old as
select *
from student
where birth_year < 1985;
 
 
-- 
-- 위의 테이블에서 학번이 20001001인 학생의 출생년도를 1986으로 변경하라 
-- 
 
update v_old
set birth_year = '1986'
where stu_no = '20001001';
 
 
select stu_no, stu_name, birth_year from v_old;
 
 
select stu_no, stu_name, birth_year from student;
 
 
update student
set birth_year = '1981'
where stu_no = '20001001';
 
 
 
 
 
-- 
-- 학적테이블에서 1985년 이전에 출생한 모든 학생에 대한 뷰테이블을 생성하라 
-- 
create view v_old12 as
select *
from student
where birth_year < 1985
with CHECK OPTION;
 
 
-- 위 테이블에서 학번이 20001001인 학생의 출생 년도를 1986으로 변경하라 
 
 
    update v_old12
    set birth_year = '1986'
    where stu_no ='20001001'
 
 
 
 
-- 
-- 
-- --
-- 등록 테이블로 부터 학번과 학생별 등록금 납입 총액의 합계로 구성하는 뷰 테이블을 생성하라
-- 
 
CREATE view totals (stu_no, fee_total)as
select stu_no, sum(fee_pay)
from fee
GROUP BY stu_no;
 
-- 뷰 테이블로 부터 학생별 등록금 납입총액의 최대값을 구하라  
-- 
 
select max(fee_total)
from totals
 
-- 뷰 테이블과 학적테이블을 이용하여  학번, 이름, 납입 총액을 출력하라.
-- 
 
select s.stu_no, s.stu_name, t.fee_total
from student s, totals t
where s.stu_no = t.stu_no
 
 
-- 수강 신청한 학생 중에서 뷰 테이블에 존재하는 학생의 학번, 납입총액을 출력하라
-- 
 
select stu_no, fee_total
from totals
where stu_no in
(select stu_no from attend);
 
 
-- 뷰 테이블에 존재하는 학생의 학번, 납입총액을 출력하라. 단, 출력 순서는 납입 총액 내림차순으로 
-- 정렬한다. 
-- 
select stu_no, fee_total
from totals
order by fee_total desc;
 
-- 뷰 테이블에 존재하는 학생의 학번과 동아리 테이블에 존재하는 학생의 학번을 학번 오름차순으로 정렬하여 출력하라.
-- 
 
select stu_no from totals
union
select stu_no from circle
order by stu_no;
 
-- 수강신청 테이블에서 학생별, 수강년도별, 학기별로 그룹을 만들고 이 그룹의 수강신청 학점이 
-- 5학점 이상인 학생의 학번, 연도, 학기, 수강학점계를 뷰 테이블 을 생성하라 
-- 
 
 
select stu_no, att_year, att_term
from attend
group by stu_no, att_year, att_term
having sum(att_point) > 5;
 
 
-- 적어도 한번 이상 등록한 학생들을 학적테이블과 동일한 가상 테이블 aa 를 생성하라 
-- 
create view aa as
select * from student
where stu_no 
IN
(select stu_no
from fee);
 
 
 
-- 적어도 한번은 등록하고 남자인 학생의 학번과 이름을 출력하라 
-- 
 
select stu_no, stu_name
from aa
where substring(id_num,8,1)=1;
 
 
-- 응용분야
-- 
-- 
-- 학급 3반에서 등록한 학생의 학번과 반을 출력하라
-- 
 
select stu_no, class
from student
where stu_no
in
(select stu_no from fee)
and class = 3;
 
-- 학급별로 등록한 학생의 학급 통계를 출력하라 
-- 
 
select class, count(*)
from  student
where stu_no
in
(select stu_no from fee)
GROUP BY class;
 
 
-- 등록한 학생의 학번과 반으로 구성되는 테이블 aaaa를 생성하라
-- 
 
create view aaaa as
select * from student
where stu_no 
IN
(select stu_no
from fee);
 
 
select * from aaaa where class=3;
 
 
select count(*from aaaa GROUP BY class;
 
 
-- 재학생 중 수강신쳥 연도, 학기와 등록년도, 학기가 동일한 학생의 학번과 이름, 수강년도, 수강학기를 
-- 출력하라 
-- 
 
 
-- 재학생 중 2006년에 수강 신청을 했으며 2006년에 등록한 학생의 평균 등록금보다 더 많은 등록금을 납부해야하고, 성별이 남자인 학생의 학번과 이름을 출력하라
 
-- 1
 
CREATE view greater1 as
select DISTINCT stu_no
from fee
where fee_total > (select avg(fee_total) from fee where fee_year=2006);
 
 
 
-- 2
create view first as
select DISTINCT stu_no
from attend
where stu_no in 
(select stu_no from attend where att_year = '2006')
 
 
-- 3 
select stu_no, stu_name
from student
where SUBSTRING(ID_NUM,8,1= '1'
AND STU_NO IN
(SELECT STU_NO FROM GREATER)
AND STU_NO IN 
(SELECT STU_NO FROM FIRST);
 
 
cs



스토어드 함수 

사용자가 직접 만들어서 사용하는 함수를 스토어드 함수라고 한다.


개요


DELIMITER $$

CREATE FUNCTION NAME (PARAMETER)

RETURNS 반환값; 


BEGIN


이 부분에 프로그래밍 코딩 ;

RETURN 반환값;


END $$

DELIMITER;


SELECT NAME();



- 스토어드 함수는 프로시저와 달리 IN OUT 을 사용할수 없다. 

- 스토어드 함수의 파라미터는 모두 입력 파라미터로 사용된다. 


- 스토어드 함수는 RETURNS문으로 반환할 값의 데이터 형식을 지정하고, 본문 안에서는 RETURN 문으로 하나의 값을 반환 해야 한다. 스토어드 프로시저는 별도의 반환하는 구문이 없고 OUT 파라미터를 이용해서 값을 반환 할 수 있다. 


- 스토어드 프로시저는 CALL 로 호출하지만 스토어드 함수는 SELECT 문장 안에서 호출된다. 


- 스토어드 프로시저 안에는 SELECT 문을 사용할 수 있지만 , 스토어드 함수 안에서는 

집합 결과를 반환하는 SELECT 를 사용할 수 없다. 


SELECT - INTO 는 집합 결과를 반환 하는 것이 아니므로 예외적으로  스토어드 함수에서 

사용할 수 있다. 


- 스토어드 프로시저는 여러 SQL문이나 숫자 계산 등의 다양한 용도로 사용되지만 스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환 하는데 주로 사용된다. 



drop function if EXISTS userFunc;


DELIMITER $$

CREATE FUNCTION userFunc(VALUE1 INT, VALUE2 INT)

RETURNS INT


BEGIN

RETURN VALUE1+VALUE2;

    

END $$


DELIMITER ;


SELECT userFunc(100,200);




-- SELECT AGE 


drop function if EXISTS AGE;


DELIMITER $$

CREATE FUNCTION AGE(VALUE1 INT)

RETURNS INT


BEGIN

DECLARE age INT;

    SET age = YEAR(curdate())- VALUE1;

    RETURN age;

    

END $$

DELIMITER ;



SELECT AGE(1988);



-- HUAL YONG


SELECT AGE(1988) INTO @AGE1988;


SELECT AGE(1990) INTO @AGE1990;


SELECT concat('1988-1990===>',(@AGE1988-@AGE1990));


-- 

프로시져는 쿼리문의 집합이다.

 

-- 형태

--

-- DELIMITER $$

--

-- CREATE PROCEDURE NAME (IN OR OUT PARAMETER)

-- BEGIN

--

-- SQL QUERY

--

-- END $$

--

-- DELIMITER ;

--

-- CALL NAME();

 

-- EX)

 

DROP PROCEDURE IF EXISTS STUDENT;

DELIMITER //

CREATE PROCEDURE STUDENT()

BEGIN

SELECT * FROM STUDENT;

END //

DELIMITER;

 

CALL STUDENT();

 

-- -----------------------------

-- 프로시져의 수정

 

ALTER PROCEDURE

 

-- 프로시져의 삭제

 

DROP PROCEDURE

 


-- 배개변수의 이용

 


IN 입력_매개 변수_이름 데이터_형식

 

CALL 프로시져 이름(전달값);


OUT 매개 변수_이름 데이터_형식

 

출력 매개 변수에 값을 대입하기 위해서는 주로 SELECT - INTO

 


EX)

 

CALL 프로시져 이름(@ParameterName);

 

select @ParameterName;

 

 

 

-- SIL SEUB 1

 

DROP PROCEDURE IF EXISTS useProc2;

DELIMITER $$

 

CREATE PROCEDURE useProc2(

           IN useBirth INT,

    IN useHeight INT

 

)

 

BEGIN

           SELECT * FROM useTbl

                     where birthYear > useBirth and height > useHeight;

       

           END $$

    DELIMITER;

   

    CALL useProc2(1970,178);

   

   

  

-- SIL SEUB 2

 

DROP PROCEDURE IF EXISTS myStudent;

DELIMITER $$

CREATE PROCEDURE myStudent(

           IN birth INT,

    IN idNum INT

)

BEGIN

 

SELECT STU_NO, STU_NAME FROM STUDENT WHERE birth_year < birth and substring(id_num,8,1) = idNum;

END $$

DELIMITER;

 

CALL myStudent(1988,1);

 


-- out procedure

 

DROP PROCEDURE IF EXISTS userProc3;

DELIMITER $$

CREATE PROCEDURE userProc3(

           IN txtValue CHAR(10),

    OUT outValue INT

)

BEGIN

 

INSERT INTO testTBL VALUES(NULL, txtValue);

select max(id) into outValue from testTBL;

 

END $$

DELIMITER ;

 

 

CREATE TABLE IF NOT EXISTS testTBL(

 

                     id INT AUTO_INCREMENT PRIMARY KEY,

        txt CHAR(10)

);

 

call userProc3('test1',@checkValue);

 

SELECT concat('check==>',@checkValue);

 

 

-- if else

 

 

DROP PROCEDURE IF EXISTS ifElseProc;

DELIMITER $$

CREATE PROCEDURE ifElseProc(

          

    IN userName VARCHAR(10)

)

 

BEGIN

           DECLARE bYear INT;

   

    SELECT birth_year into bYear FROM student

    where stu_name = userName;

   

   

    if (bYear >=1980) then

   

                     select 'still young';

       

        ELSE

       

        select 'you are old';

       

        end if;

           end $$

    delimiter;

   

    call ifElseProc('김유신');

   

-- case

 

DROP PROCEDURE IF EXISTS caseProc;

 

DELIMITER $$

 

CREATE PROCEDURE caseProc(

 

           IN userName VARCHAR(20)

)

 

BEGIN

 

           DECLARE bYear INT;

    DECLARE tti CHAR(20);

   

    SELECT birth_year into bYear from student

    where stu_name = userName;

   

    case

   

    when (bYear %12 = 0) then set tti = 'monkey';

   

    when (bYear %12 = 1) then set tti = 'ciken';

   

    when (bYear %12 = 2) then set tti = 'dog';

   

    when (bYear %12 = 3) then set tti = 'pig';

   

    when (bYear %12 = 4) then set tti = 'mouse';

   

    when (bYear %12 = 5) then set tti = 'cou';

   

    when (bYear %12 = 6) then set tti = 'tiger';

   

    when (bYear %12 = 7) then set tti = 'rabit';

   

    when (bYear %12 = 8) then set tti = 'dragon';

   

    when (bYear %12 = 9) then set tti = 'snake';

   

    when (bYear %12 = 10) then set tti = 'horse';

   

    else set tti = 'shep';

   

    end case;

   

    select concat(userName,'tii is=',tti);

   

    end $$

    delimiter;

   

    call caseProc('김유신');

   

-- gugudan

 

DROP TABLE IF EXISTS guguTBL;

 

CREATE TABLE guguTBL( txt VARCHAR(100)); -- 구구단 저장용 테이블

 

DROP PROCEDURE IF EXISTS whileProc;

 

DELIMITER $$

 

CREATE PROCEDURE whileProc()

 

BEGIN

 

                     DECLARE str VARCHAR(100); -- 각 단을 문자열로 저장

        DECLARE I INT;    -- 구구단 앞자리

        DECLARE K INT; -- 뒷자리

                     SET I = 2;

       

        WHILE(I <10) DO -- 바깥 반복문 2~9단까지

       

        SET STR = '  ' ; -- 각 단의 결과를 저장할 무낮 초기화

        SET K = 1; -- 구구단 뒷자리는 항상 1~ 9까지

       

                                WHILE (K<10) DO

                                          SET STR = concat(STR,' ',I,'X', K ,'=',I*K); -- 문자열 만들기

                SET K = K+1; -- 뒷자리 증가

                                END WHILE;

            SET I = I+1; -- 앞자리 증가

            INSERT INTO guguTBL VALUES(str); -- 각 단의 결과를 테이블에 입력

                                END WHILE;

                     end $$

        delimiter;

call whileProc();

select * from guguTBL;       

 

-- declare ~ handler

 

DROP PROCEDURE IF EXISTS errorProc;

 

DELIMITER $$

CREATE PROCEDURE errorProc()

BEGIN

           DECLARE I INT; -- 1씩 증가하는 값

    DECLARE HAP INT; -- 합계 오버플로 발생시킬 예정

    DECLARE SAVEHAP INT; -- 합계 오버플로 직전의 값을 저장

   

    DECLARE EXIT HANDLER FOR 1264 -- 오버플로가 발생하면 이부분 수행

    BEGIN

                     SELECT concat('IN OVERFLOWER BEFORE HAP=>', SAVEHAP);

        SELECT concat('1+2+3+4+',I,'OVERFLOWER');

           END;

   

    SET I = 1; -- 1 부터 증가

 

    SET HAP = 0; -- 합계를 누적

   

    WHILE (TRUE) DO  -- 무한 루프

   

    SET SAVEHAP = HAP; -- 오버플로 직전의 합계를 저장

    SET HAP = HAP+ I; -- 오버플로가 나면 11, 12 수행

   

           END WHILE;

END $$

DELIMITER;

 

CALL errorProc();




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
프로시져는 쿼리문의 집합이다.
 
 
 
-- 형태
 
--
 
 DELIMITER $$
 
 
 CREATE PROCEDURE NAME (IN OR OUT PARAMETER)
 
 BEGIN
 
 
 
SQL QUERY
 
 
 
END $$
 
 
DELIMITER ;
 
 
 
 CALL NAME();
 
 
 
-- EX)
 
 
 
DROP PROCEDURE IF EXISTS STUDENT;
 
DELIMITER //
 
CREATE PROCEDURE STUDENT()
 
BEGIN
 
SELECT * FROM STUDENT;
 
END //
 
DELIMITER;
 
 
 
CALL STUDENT();
 
 
 
-- -----------------------------
 
-- 프로시져의 수정
 
 
 
ALTER PROCEDURE
 
 
 
-- 프로시져의 삭제
 
 
 
DROP PROCEDURE
 
 
 
 
 
-- 배개변수의 이용
 
 
 
 
 
IN 입력_매개 변수_이름 데이터_형식
 
 
 
CALL 프로시져 이름(전달값);
 
 
 
OUT 매개 변수_이름 데이터_형식
 
 
 
출력 매개 변수에 값을 대입하기 위해서는 주로 SELECT - INTO
 
 
 
 
 
EX)
 
 
 
CALL 프로시져 이름(@ParameterName);
 
 
 
select @ParameterName;
 
 
 
 
 
 
 
-- SIL SEUB 1
 
 
 
DROP PROCEDURE IF EXISTS useProc2;
 
DELIMITER $$
 
 
 
CREATE PROCEDURE useProc2(
 
           IN useBirth INT,
 
    IN useHeight INT
 
 
 
)
 
 
 
BEGIN
 
           SELECT * FROM useTbl
 
                     where birthYear > useBirth and height > useHeight;
 
       
 
           END $$
 
    DELIMITER;
 
   
 
    CALL useProc2(1970,178);
 
   
 
   
 
  
 
-- SIL SEUB 2
 
 
 
DROP PROCEDURE IF EXISTS myStudent;
 
DELIMITER $$
 
CREATE PROCEDURE myStudent(
 
           IN birth INT,
 
    IN idNum INT
 
)
 
BEGIN
 
 
 
SELECT STU_NO, STU_NAME FROM STUDENT WHERE birth_year < birth and substring(id_num,8,1= idNum;
 
END $$
 
DELIMITER;
 
 
 
CALL myStudent(1988,1);
 
 
 
 
 
-- out procedure
 
 
 
DROP PROCEDURE IF EXISTS userProc3;
 
DELIMITER $$
 
CREATE PROCEDURE userProc3(
 
           IN txtValue CHAR(10),
 
    OUT outValue INT
 
)
 
BEGIN
 
 
 
INSERT INTO testTBL VALUES(NULL, txtValue);
 
select max(id) into outValue from testTBL;
 
 
 
END $$
 
DELIMITER ;
 
 
 
 
 
CREATE TABLE IF NOT EXISTS testTBL(
 
 
 
                     id INT AUTO_INCREMENT PRIMARY KEY,
 
        txt CHAR(10)
 
);
 
 
 
call userProc3('test1',@checkValue);
 
 
 
SELECT concat('check==>',@checkValue);
 
 
 
 
 
-- if else
 
 
 
 
 
DROP PROCEDURE IF EXISTS ifElseProc;
 
DELIMITER $$
 
CREATE PROCEDURE ifElseProc(
 
          
 
    IN userName VARCHAR(10)
 
)
 
 
 
BEGIN
 
           DECLARE bYear INT;
 
   
 
    SELECT birth_year into bYear FROM student
 
    where stu_name = userName;
 
   
 
   
 
    if (bYear >=1980) then
 
   
 
                     select 'still young';
 
       
 
        ELSE
 
       
 
        select 'you are old';
 
       
 
        end if;
 
           end $$
 
    delimiter;
 
   
 
    call ifElseProc('김유신');
 
   
 
-- case
 
 
 
DROP PROCEDURE IF EXISTS caseProc;
 
 
 
DELIMITER $$
 
 
 
CREATE PROCEDURE caseProc(
 
 
 
           IN userName VARCHAR(20)
 
)
 
 
 
BEGIN
 
 
 
           DECLARE bYear INT;
 
    DECLARE tti CHAR(20);
 
   
 
    SELECT birth_year into bYear from student
 
    where stu_name = userName;
 
   
 
    case
 
   
 
    when (bYear %12 = 0) then set tti = 'monkey';
 
   
 
    when (bYear %12 = 1) then set tti = 'ciken';
 
   
 
    when (bYear %12 = 2) then set tti = 'dog';
 
   
 
    when (bYear %12 = 3) then set tti = 'pig';
 
   
 
    when (bYear %12 = 4) then set tti = 'mouse';
 
   
 
    when (bYear %12 = 5) then set tti = 'cou';
 
   
 
    when (bYear %12 = 6) then set tti = 'tiger';
 
   
 
    when (bYear %12 = 7) then set tti = 'rabit';
 
   
 
    when (bYear %12 = 8) then set tti = 'dragon';
 
   
 
    when (bYear %12 = 9) then set tti = 'snake';
 
   
 
    when (bYear %12 = 10) then set tti = 'horse';
 
   
 
    else set tti = 'shep';
 
   
 
    end case;
 
   
 
    select concat(userName,'tii is=',tti);
 
   
 
    end $$
 
    delimiter;
 
   
 
    call caseProc('김유신');
 
   
 
-- gugudan
 
 
 
DROP TABLE IF EXISTS guguTBL;
 
 
 
CREATE TABLE guguTBL( txt VARCHAR(100)); -- 구구단 저장용 테이블
 
 
 
DROP PROCEDURE IF EXISTS whileProc;
 
 
 
DELIMITER $$
 
 
 
CREATE PROCEDURE whileProc()
 
 
 
BEGIN
 
 
 
                     DECLARE str VARCHAR(100); -- 각 단을 문자열로 저장
 
        DECLARE I INT;    -- 구구단 앞자리
 
        DECLARE K INT-- 뒷자리
 
                     SET I = 2;
 
       
 
        WHILE(I <10) DO -- 바깥 반복문 2~9단까지
 
       
 
        SET STR = '  ' ; -- 각 단의 결과를 저장할 무낮 초기화
 
        SET K = 1-- 구구단 뒷자리는 항상 1~ 9까지
 
       
 
                                WHILE (K<10) DO
 
                                          SET STR = concat(STR,' ',I,'X', K ,'=',I*K); -- 문자열 만들기
 
                SET K = K+1-- 뒷자리 증가
 
                                END WHILE;
 
            SET I = I+1-- 앞자리 증가
 
            INSERT INTO guguTBL VALUES(str); -- 각 단의 결과를 테이블에 입력
 
                                END WHILE;
 
                     end $$
 
        delimiter;
 
call whileProc();
 
select * from guguTBL;       
 
 
 
-- declare ~ handler
 
 
 
DROP PROCEDURE IF EXISTS errorProc;
 
 
 
DELIMITER $$
 
CREATE PROCEDURE errorProc()
 
BEGIN
 
           DECLARE I INT-- 1씩 증가하는 값
 
    DECLARE HAP INT-- 합계 오버플로 발생시킬 예정
 
    DECLARE SAVEHAP INT-- 합계 오버플로 직전의 값을 저장
 
   
 
    DECLARE EXIT HANDLER FOR 1264 -- 오버플로가 발생하면 이부분 수행
 
    BEGIN
 
                     SELECT concat('IN OVERFLOWER BEFORE HAP=>', SAVEHAP);
 
        SELECT concat('1+2+3+4+',I,'OVERFLOWER');
 
           END;
 
   
 
    SET I = 1-- 1 부터 증가
 
 
 
    SET HAP = 0-- 합계를 누적
 
   
 
    WHILE (TRUE) DO  -- 무한 루프
 
   
 
    SET SAVEHAP = HAP; -- 오버플로 직전의 합계를 저장
 
    SET HAP = HAP+ I; -- 오버플로가 나면 11, 12 수행
 
   
 
           END WHILE;
 
END $$
 
DELIMITER;
 
 
 
 
CALL errorProc();
cs


+ Recent posts

티스토리 툴바