데이터 입력 / 수정 / 삭제 / 데이터 고급 조회 1
<학습 내용>
-데이터 입력하기
-데이터 수정/삭제
-INNER JOIN / OUTER JOIN
데이터 입력하기
(1) 데이터 저장하기 INSERT INTO
-나열되는 컬럼 순서는 테이블 구조와 일치하지 않아도 괜찮음
-VALUE 와 컬럼은 1:1 대응 필요
데이터의 무결정
> 잘못된 데이터가 들어가는 것을 방지하고, 올바른 데이터만 유지되도록 보장하는 것
> 무결성을 보장하기 위해 테이블 생성 시 적절한 제약 조건을 설정 (테이블 생성 시 지정하는 옵션)
> 데이터 조회보다는 입력/ 수정 / 삭제 할 때 지키는 옵션들
(1) 엔티티 무결성
-각 행은 고유해야하고, 각 행을 식별하기 위해서 NULL 이 아닌 고유 값(일련 번호) 가 필요하다 | 기본키 (PRIMARY KEY)
(EX 학급 동명이인을 중복 없는 번호로 구분)
(2) 도메인 무결성
-컬럼에 들어갈 값의 유형과 범위를 제한하는 것
(EX 나이는 0보다 큰 정수만 허용 / 특정 회사 범위 내에서만 통용되는 지식)
(3) 참조 무결성
-어떤 값은 반드시 다른 테이블에 저장되어 있는 값 중 하나여야 함
-테이블 2개가 논리적인 관계를 가지고 있어야 함
-기본키는 엔티티 무결성을 보장하며 NULL 불가
-유일한 컬럼
-기본키 값의 일련번호가 자동으로 증가하는 조건을 걸어줄 수 있음 (AUTO_INCREMENT)
-INSERT 구문에서 일련 번호 부분을 아예 생략
<연습문제>
1. 학과테이블(departments)에소프트웨어공학과를새로등록하세요.학과번호는자동으로생성되도록하며, 학과명은‘소프트웨어공학과’,위치는‘2호관’,전화번호는‘051-123-7890’,이메일은‘sw@myschool.ac.kr’, 설립연도는2012,홈페이지는‘http://sw.myschool.ac.kr’로입력하세요.
INSERT INTO DEPARTMENTS (dname,loc,phone,email,established,homepage)
VALUES ('소프트웨어공학과' , '2호관' , '051-123-7890','sw@myschool.ac.kr',2012,'http://sw.myschool.ac.kr');
문자열 값('소프트웨어공학과', '2호관' 등)은 따옴표로 감싸고, 숫자 값(2012)은 따옴표 없이 입력
2, 방금등록한소프트웨어공학과에소속된학생1명을students테이블에추가하세요.학생의이름은‘박세린’, 학년은2학년이며,생년월일은2005년10월17일입니다.키는169cm,몸무게는56kg,성별은‘여’,학적 상태는‘재학’입니다.주민번호(idnum)과연락처는임의로지정하세요.
INSERT INTO STUDENTS (name,grade,birthdate,height,weight,gender,status,idnum,phone,user_id,department_id) VALUES ('박세린',2,'2005-10-17 00:00:00',169,56,'여','재학','21201-2000123','010-2339-8952','serin',504);
id 컬럼은 AUTO_INCREMENT 이므로 자동 생성되어 입력하지 않습니다.
3. 소프트웨어공학과에소속된교수1명을professors테이블에등록하세요.
이교수의이름은‘최정훈’이며, 아이디는‘jhchoi’,직급은‘부교수’입니다.급여는5,200,000원이며,입사일은2018년3월2일,재직상태는 ‘재직’입니다.소속학과는앞에서생성한학과와동일하게설정하세요.이메일,연락처,사진의URL은Null로 저장하세요.
INSERT INTO PROFESSORS (NAME,USER_ID,POSITION,SAL,HIREDATE,COMM,EMAIL,PHONE,PHOTO_URL,STATUS,DEPARTMENT_ID) VALUES ('최정훈','jhchoi','부교수',520,'2018-03-02 00:00:00',NULL,NULL,NULL,NULL,'재직',504);
4.소프트웨어공학과에서개설하는과목으로‘소프트웨어프로젝트실습’을추가하세요.학점은3이며,개설학과 번호는소프트웨어공학과로설정하세요.담당교수는아직정해지지않았습니다.
INSERT INTO SUBJECTS (NAME,CREDIT,DEPARTMENT_ID,PROFESSOR_ID) VALUES ('소프트웨어 프로젝트 실습',3,504,NULL);
5.학생번호10101번이`웹프로그래밍`과목을2025년3월2일에수강신청하였습니다.이학생의점수는아직 부여되지않았습니다.이정보를enrollments테이블에등록하세요
INSERT INTO ENROLLMENTS (STUDENT_ID, SUBJECT_ID ,ENROLL_DATE, SCORE) VALUES (10101,1007,'2025-03-02',NULL);
도메인 무결성
-정해진 형식의 데이터만 저장 가능
-ENUM 열거형 데이터로 고정값 지정
-MD5 로 암호화하여 INSERT 가능하고, WHERE 절에서도 MD5 로 감싸서 조회해야함
<연습문제>
1. students테이블에학생을추가하되,성별은‘남’,상태는‘재학’으로저장하세요.입학일은오늘날짜로 저장하며,키/몸무게는임의로설정하세요.그밖의다른필수항목은임의의데이터로지정하세요.
INSERT INTO STUDENTS (NAME,USER_ID,GRADE,IDNUM,BIRTHDATE,PHONE,HEIGHT,WEIGHT,DEPARTMENT_ID,GENDER,STATUS,ADMISSION_DATE) VALUES ('정보희','BOHEE',3,MD5('BOHEE'),'1999-05-06 06:30:30','010-2339-8952','160','55','504','남','재학',CURDATE())
2.101번학과의박태수부교수의이메일주소는'PARK@MYSCHOOL.AC.KR'이다.이메일을모두소문자로 변환해서등록하세요.그밖의정보는임의로저장하세요.
INSERT INTO PROFESSORS (NAME,USER_ID,POSITION,SAL,HIREDATE,EMAIL,STATUS,DEPARTMENT_ID) VALUES ('박태수','TAESU','부교수','200',CURDATE(),LOWER('PARK@MYSCHOOL.AC.KR' ),'재직',101)
3.학생이름이'김하늘',소속학과명이'소프트웨어공학과'인학생을등록하세요. 이메일은이름 +소속학과앞글자3개@myschool.ac.kr형식으로생성하며,전부소문자로저장하면됩니다. 예:kim소프@myschool.ac.kr→kim소프@myschool.ac.kr→소문자변환 그밖의정보는임의로설정하여저장하세요.
INSERT INTO STUDENTS (NAME,USER_ID,GRADE,IDNUM,BIRTHDATE,PHONE,HEIGHT,WEIGHT,DEPARTMENT_ID,EMAIL) VALUES ('김하늘','HANEUL',3,MD5('HANEUL'),'1999-05-06','010-2339-8952',160,55,101,LOWER(CONCAT('HANEUL','소프트','@myschool.ac.kr')))
4.enrollments테이블은student_id와subject_id가참조키입니다.존재하지않는학생번호와과목번호로 수강신청을시도해보세요.
INSERT INTO ENROLLMENTS (STUDENT_ID,SUBJECT_ID,ENROLL_DATE) VALUES (10000,10000,NOW())
ERROR 1452 (23000): Cannot add or update a child row:
5.students테이블에성별을'남성'으로잘못입력하면어떤일이발생하는지확인해보세요
INSERT INTO STUDENTS (NAME,USER_ID,GRADE,IDNUM,BIRTHDATE,PHONE,HEIGHT,WEIGHT,DEPARTMENT_ID,EMAIL,GENDER) VALUES ('김구름','GURUM',3,MD5('GURUM'),'1999-05-06 00:00:00','010-2339-8952',160,55,101,LOWER(CONCAT('GURUM ','소프트','@myschool.ac.kr')),'남성')
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
데이터 수정/삭제
참조키가 걸려있는 경우 부모키를 함부로 삭제할 수 없음
<연습문제>
1.학번이10101번인학생의전화번호를'010-1234-5678'로수정하세요.
UPDATE STUDENTS
SET PHONE = '010-1234-5678'
WHERE ID=10101
2.모든‘휴학’상태의학생을‘재학’으로변경하세요.
UPDATE STUDENTS
SET STATUS = '재학'
WHERE STATUS ='휴학'
3.1학년학생중키가160cm이하인학생을삭제하시오.삭제가안될경우왜삭제가안되는지 확인하세요.
DELETE FROM STUDENTS
WHERE HEIGHT <= 160 AND GRADE =1
Cannot delete or update a parent row: a foreign key constraint fails
4.102번학과의이름을'소프트웨어학과'로,위치를'공학관'으로수정하세요.
UPDATE DEPARTMENTS
SET DNAME = '소프트웨어학과' , LOC = '공학관'
WHERE ID = 102
INNER JOIN
-두 테이블을 연결해서 공통된 값을 기준으로 데이터를 조회하는 방법
-양쪽 테이블 모두에 존재하는 값 출력
- EQUI JOIN ,NATURAL JOIN 과 같음
-ON 뒤에 두 테이블을 연결할 조건 명시
1. 학생 이름과 지도 교수 이름을 함께 출력하시오.
SELECT S.NAME , P.NAME FROM STUDENTS S INNER JOIN PROFESSORS P ON S.PROFESSOR_ID = P. ID;
2. 수강신청한 학생의 이름과 수강한 과목명을 출력하시오
SELECT S.NAME , SB.NAME FROM ENROLLMENTS E
INNER JOIN STUDENTS S ON E.STUDENT_ID = S.ID
INNER JOIN SUBJECTS SB ON E.SUBJECT_ID = SB.ID
3. 학생이 수강한 과목과 그 과목의 학과명을 출력하시오.
SELECT S.NAME,D.DNAME,SB.NAME FROM STUDENTS S
INNER JOIN DEPARTMENTS D ON S.DEPARTMENT_ID= D. ID
INNER JOIN SUBJECTS SB ON S.PROFESSOR_ID= SB. PROFESSOR_ID
SELECT
s.name AS
학생이름
,
sub.name AS
과목명
,
d.dname AS
개설학과
FROM enrollments e
INNER JOIN students s ON e.student_id = s.id
INNER JOIN subjects sub ON e.subject_id = sub.id
INNER JOIN departments d ON sub.department_id = d.id;
4. 2024년 이후 입사한 교수의 이름과 소속 학과명을 출력하시오.
SELECT P.NAME, D.DNAME FROM PROFESSORS P INNER JOIN DEPARTMENTS D
ON P.DEPARTMENT_ID = D.ID
WHERE YEAR(P.HIREDATE) > 2024;
5. 2과목 이상 수강신청한 학생에 대해서 학생별로 학생의 이름과 수강신청한 과목 수, 평균 점수를 평균점수가 높은 순으로 조회 하시오.
SELECT S.NAME, COUNT(*) COUNT, AVG(E.SCORE) AVG_SCORE FROM STUDENTS S
INNER JOIN ENROLLMENTS E ON S.ID = E.STUDENT_ID
GROUP BY S.ID
HAVING COUNT(e.subject_id)>=2
ORDER BY AVG_SCORE DESC
*S.NAME 으로 GROUP BY 했는데, 동명 이인이 있어 다른 결과가 나왔음
OUTER JOIN
-기준 테이블의 모든 행을 결과에 포함시키는 방식
-한쪽에만 존재해도 결과에 포함
1. professors 와 subjects 를 조인하여, 과목을 담당하지 않는 교수의 이름만 출력하세요
SELECT * FROM PROFESSORS P LEFT JOIN SUBJECTS S ON P.ID = S.PROFESSOR_ID WHERE S.NAME IS NULL;
2. departments 와 subjects 를 조인하여, 학과명과 과목 수를 출력하세요. 모든 학과가 포함되어야 하며,
과목이 없는 학과도 출력되어야 합니다.
SELECT D.DNAME, COUNT(S.NAME)
FROM DEPARTMENTS D LEFT OUTER JOIN SUBJECTS S ON D.ID = S.DEPARTMENT_ID
GROUP BY D.DNAME;
3. 담당 과목이 없는 교수도 포함하여, 교수 이름과 담당 과목명을 출력하시오.
모든 교수를 출력하되, 과목을 맡지 않은 교수의 과 목명은 NULL로 표시되도록 하세요
SELECT P.NAME,S.NAME FROM PROFESSORS P LEFT OUTER JOIN SUBJECTS S ON P.ID = S.PROFESSOR_ID
4. 모든 학생의 이름과 수강신청한 과목명을 출력하시오.
단, 수강신청을 하지 않은 학생도 모두 포함되어야 하며, 수강신청이 없 는경우 과목명은 NULL로 표시됩니다
SELECT * FROM STUDENTS S LEFT JOIN
5. students 와 enrollments 를 조인하여, 수강 신청을 한 번도 하지 않은 학생의 이름을 출력하세요
SELECT * FROM STUDENTS S LEFT OUTER JOIN ENROLLMENTS E ON S.ID = E.STUDENT_ID
WHERE E.STUDENT ID IS NULL
'빅데이터 국비 교육' 카테고리의 다른 글
| [아이티윌 빅데이터 52기] Day 3 SQL 함수 2 그룹 조회 (0) | 2025.10.02 |
|---|---|
| [아이티윌 빅데이터 52기] Day 2 데이터 검색하기 SQL 함수 (0) | 2025.10.01 |
| [아이티윌 빅데이터 52기] Day 1 데이터 베이스의 시작하기 (0) | 2025.10.01 |