MYSQL 특이점..

 

@제약조건 스키마로확인

SELECT *  FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE  constraint_schema ='11.22COURSE';

 

@제약조건 테이블명으로 확인

select * from information_schema.table_constraints WHERE TABLE_NAME='STUDENT';
select * from information_schema.table_constraints WHERE TABLE_NAME='GRADE_REPORT';

 

 

@ UPDATE문에 테이블을 조인시켜도 되는구나

 

UPDATE STUDENT S JOIN GRADE_REPORT G ON S.STUDENT_NUMBER = G.STUDENT_NUMBER
               JOIN SECTION  E ON E.SECTION_IDENTIFIER= G.SECTION_IDENTIFIER
                JOIN COURSE C ON C.COURSE_NUMBER = E.COURSE_NUMBER
SET GRADE='F'
WHERE S.NAME='Brown' AND  COURSE_NAME ='Database';

 

@SELECT로 INSERT시킬때 VALUES를 쓰지않는다.

 

INSERT INTO MAJOR_GRADE_REPORT (STUDENT_NUMBER, SECTION_IDENTIFIER, GRADE) (
      (SELECT G.STUDENT_NUMBER, E.SECTION_IDENTIFIER, G.GRADE
      FROM STUDENT S JOIN GRADE_REPORT G ON S.STUDENT_NUMBER = G.STUDENT_NUMBER
     JOIN SECTION  E ON E.SECTION_IDENTIFIER= G.SECTION_IDENTIFIER
     JOIN COURSE C ON C.COURSE_NUMBER = E.COURSE_NUMBER
      WHERE S.MAJOR=C.DEPARTMENT)
);