Q1 MySQL 연동 예제(2) : 수정

 

CREATE DATABASE studentGradeDB;
USE studentGradeDB;
CREATE TABLE studentTbl(
	studentNum 			CHAR(8) NOT NULL PRIMARY KEY,	/* 학번 */
    studentName 		NCHAR(5) NOT NULL,      	    /* 이름 */
    studentAdress		VARCHAR(100) NULL,				/* 주소 가변형 */	
    studentPhone 		CHAR(11) NULL,					/* 연락처 */
    studentDepartment	VARCHAR(10) NOT NULL,			/* 학과 */
    studentYear  		SMALLINT NOT NULL				/* 입학년도 */
);

CREATE TABLE studentGradeTbl(
	num	 				INT AUTO_INCREMENT NOT NULL PRIMARY KEY,  /* AUTO_INCREMENT => SQL Server에서는 IDENTITY*/
	studentNum 			CHAR(8) NOT NULL ,  			 /* 학번 */
    subjectName			NCHAR(20) NOT NULL,				 /* 과목명 */
    term				CHAR(2),					 	 /* 학기 P, S, F ,M */	
    subjectScore		CHAR(2),						 /* 점수 */
    subjectSukcha		SMALLINT, 						 /* 과목석차 */	
    FOREIGN KEY (studentNum) REFERENCES studentTbl(studentNum) /* studentTbl의 studentNum과 연결  */
);

USE studentGradeDB;
DROP table studentTbl;
DROP table studentGradeTbl;

SHOW tables;

INSERT INTO studentTbl(studentNum,studentName,studentAdress,studentPhone,studentDepartment,studentYear)
VALUES( '20200001','이순신', '서울','01011112222','컴공' ,2020);
INSERT INTO studentTbl(studentNum,studentName,studentAdress,studentPhone,studentDepartment,studentYear) 
VALUES( "20200002",'장보고', '경기','01033334444','컴공' ,2020);
INSERT INTO studentGradeTbl(num,studentNum,subjectName,term,subjectScore,subjectSukcha) 
VALUES( NULL,'20200001', 'F','구조','A+' ,1);
INSERT INTO studentGradeTbl(num,studentNum,subjectName,term,subjectScore,subjectSukcha) 
VALUES( NULL,'20200001', 'W','개론','A+' ,1);
INSERT INTO studentGradeTbl(num,studentNum,subjectName,term,subjectScore,subjectSukcha)
VALUES( NULL,'20200001', 'S','실험','A+' ,1);
INSERT INTO studentGradeTbl(num,studentNum,subjectName,term,subjectScore,subjectSukcha)
VALUES( NULL,'20200002', 'F','구조','B0' ,2);
INSERT INTO studentGradeTbl(num,studentNum,subjectName,term,subjectScore,subjectSukcha)
VALUES( NULL,'20200002', 'W','개론','C0' ,2);
INSERT INTO studentGradeTbl(num,studentNum,subjectName,term,subjectScore,subjectSukcha)
VALUES( NULL,'20200002', 'S','실험','A0' ,2);

SELECT * 
FROM studentTbl
JOIN studentGradeTbl  /*두 테이블을 합침*/
ON studentTbl.studentNum =studentGradeTbl.studentNum  /* 합치는 조건 입력*/
WHERE studentTbl.studentNum = '20200001';  /* 특정 값만 보기 */