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 사용자 추가 제거 권한 부여 (0) | 2017.08.23 |
|---|---|
| <실습> 3.mysql join&outer join & 프로그래밍 (2) | 2017.05.12 |
| <실습> 2.mysql 내장함수(날짜,문자,숫자,JSON 등) 정리 (2) | 2017.05.11 |
| <실습> 1.mysql 기본 (4) | 2017.05.10 |
| MYSQL 트리거&TRIGGER 예제&문제 17 (4) | 2016.12.20 |
댓글