MySQL(서브쿼리, 데이터 입력·수정·삭제, 함수)

서브쿼리(SubQuery)

SQL안의 SQL

• 하나의 SQL명령문의 처리결과를 다른 SQL 명령문에 전달하기 위해 두 개 이상의 SQL문을 하나의 SQL문으로 연결한 상태
• 검색조건을 다른 결과집합에서 찾기 위해서 사용
• 서브쿼리를 포함한 SQL문을 '메인쿼리' 라고 함
• 서브쿼리는 select 문의 시작과 끝에 ( )로 묶어서 메인쿼리와 구분

select 컬럼 ~~ from 테이블이름  where 컬럼 연산자 (select~~~~ )

종류

이름	설명
단일 행 서브쿼리	단 하나의 검색 결과만을 반환하는 형태. 서브쿼리를 검색결과로 사용하기 위해 비교연산자(=, <, >)사용
다중 행 서브쿼리	하나 이상의 검색결과를 반환하는 형태. 서브쿼리를 검색결과로 사용하기 위해 IN 연산자 사용

단일행 서브쿼리

예제 1)

'전은지' 교수와 같은 직급의 교수에 대한 이름, 직급 조회

예제 2)

1학년 학생중에서 키가 전체 학생의 평균키를 초과하는 학생의 이름, 학과번호, 학년, 키를 조회

예제 3)

'이광훈'학생과 같은 학과에 재학중인 학생의 이름, 소속학과 이름을 조회

예제 4)

급여를 300만원 초과로 받는 교수에게 지도받는 학생들의 학번, 학년, 이름 조회

데이터 입력 : INSERT

INSERT 절

테이블에 데이터를 추가하기 위한 구문

insert into 테이블이름 values (값1, 값2, ~~~, 값n);

값을 명시하는 방법

- 숫자형식의 데이터는 홑따옴표를 사용하지 않음.
- 그 밖의 형식(문자열, 날짜형식 등)값은 홑따옴표로 감싼다
- 값을 나열할 떄는 테이블구조에서 정의하고 있는 컬럼의 순서에 맞게 명시해야 함.

• DESC 테이블이름; 의 구문으로 확인가능

예제 1)

학생테이블에 다음의 정보로 새로운 데이터 추가하기

학번	10110	이름	홍길동
아이디	hkd85	학년	1
주민번호	8501011143098	생년월일	2013-10-01 11:42:30
전화번호	055)-777-7777	키	170
몸무게	70	학과번호	101
담당교수번호	9903

8번째 행에 홍길동의 값 추가.

컬럼명을 명시한 저장

insert into 테이블이름 (컬럼1, 컬럼2, ~~~~, 컬럼n)
values (값1, 값2,~~~, 값n);

이 때는 나열되는 컬럼의 이름이 테이블구조에서 정의하고 있는 컬럼의 순서와 일치할 필요 X.
테이블이름뒤에 명시되는 컬럼의 순서나 갯수는 values 뒤에 나열되는 값과 일치하여야 함.

예제 2)

학생테이블에 다음의 정보로 새로운 데이터를 컬럼명 나열하여 추가하기

학번	10111	이름	둘리
아이디	dolly	학년	2
주민번호	8502021234765	생년월일	2011-10-01 11:42:30
전화번호	02)-123-2345	키	170
몸무게	70	학과번호	101
담당교수번호	9903

9번째 행에 둘리의 값 추가.

테이블의 구조를 통한 Null 데이터 저장가능 여부 확인

- DESC 구문을 사용하여 테이블 구조를 조회했을 때, Null 항목에 "YES"라고 표시되는 컬럼은 Null값을 허용.
- 즉, 아래의 테이블 구조에서 deptno, dname은 필수 입력항목이고, loc컬럼은 미필수 입력 항목이 된다.

loc컬럼에 NULL항목에 YES라고 명시되어 있음.

Null 데이터를 입력하기 위한 암묵적인 방법

- INSERT INTO 절에서 해당 칼럼의 이름과 값을 생략.

예제 1)

학과번호 300인 '응용과학'과를 위치가 결정되지 않은 상태로 저장.

Null 데이터를 입력하기 위한 명시적인 방법

- VALUES 절의 컬럼값에 NULL을 사용

예제 2)

학과번호 301인 '영문학과'를 위치가 결정되지 않은 상태로 저장.

날짜 데이터 입력방법(1)

- 칼럼에 날짜를 입력하면 'YYYY-MM-DD HH:MI:SS' 혹은 'YYYY-MM-DD' 형식에 따른 날짜 데이터를 입력해야 함.

예제 3)

교수테이블에서 다음의 데이터를 입력

교수번호	9920	이름	고길동
아이디	gilldong	직급	교수
급여	450	입사일	2014년 01월 01일
학과	102번 학과

마지막 행에 고길동교수의 값 추가

날짜 데이터 입력방법 (2)

- 자동으로 현재 날짜로 입력하기 위해서는 now( ) 함수를 사용.

교수번호	9921	이름	또치
아이디	ddochi	직급	교수
급여	500	입사일	오늘
보직수당	미정	학과	201번 학과

마지막 행에 또치교수의 입사일을 오늘로 기록

중복 데이터 방지 (1)

- 테이블에 중복되는 데이터가 저장되어 있다면 하나를 뺀 나머지데이터들은 불필요한 값으로 시스템의 자원을 낭비함.

위 사진은 에러로 나타나지 않는것으로 사진으로만 나타냄.

- 그래서 각 데이터는 중복되지 않기 위한 최소한의 제약을 요구함.

• 예) 한 학급안에서 이름이 같은 학생은 학번으로 구분된다.

- 이 를 위해 '테이블의 각 행을 고유하게 식별하는 값'을 가진 열(또는 열조합)을 기본키(Primary Key) 라고 한다.

중복 데이터 방지 (2)

- 데이터베이스 테이블의 컬럼에 기본기옵션을 지정하여 중복 데이터의 입력을 방지 할 수 있음.

• 기본키로 설정된 컬럼은 각 데이터행의 구분을 위해 고유한 값만을 저장해야 하기 때문에
  다른행과 중복된 값을 저장할 수 없도록 제약.
• 기본키는 일련번호의 개념으로 사용되는데 데이터를 저장할 때마다 일련번호를 계산하기 어렵기 때문에
  AUTO_INCREMENT 옵션을 사용하여 데이터 저장시 시스템에서 자동으로 일련변호를 생성하도록 할 수 있음.

deptno 컬럼에는 기본키속성이 있고 AUTO_INCREMENT 옵션이 설정되어있음.

중복 데이터 방지 (3)

- 기본키 속성이 설정된 컬럼에 저장되는 값이 기존에 저장된 데이터와 중복될 경우 에러발생.
- 이 와 같이 테이블을 생성하면서 특정컬럼을 기본키로 설정하면
중복 데이터 저장에 대한 제약이 설정되는데 이를 기본키 제약조건 이라 한다.
- 데이터가 중복되지 않는다는 특성을 데이터 무결성 이라고 한다.
- 그러므로 기본키 제약조건을 다른 말로 데이터무결성 제약조건 이라 한다.

기본키에 대한 자동증가 일련번호 속성 사용

- 기본키에는 AUTO_INCREMENT 속성을 추가할 수 있다.
- 이 속성이 설정되면, insert 절에 해당컬럼을 명시안해도 자동으로 기존의 값들중 가장 큰값 + 1이 되어 저장됨.

데이터 수정을 위한 UPDATE 절

update 테이블이름 set 컬럼1=값1, 컬럼2=값2, ~~~~, 컬럼n=값n [where 검색조건];

데이터 삭제를 위한 DELETE 절

delete from 테이블이름 [where 검색조건];

※ 주의 - 수정, 삭제시 'where'절을 쓰지 않으면 컬럼의 값이 다 수정 및 삭제됨

예제 1)

교수번호가 9903번인 교수의 직급을 '조교수'로 급여는 200만원으로 수정.

예제 2)

학생번호가 20103인 학생의 데이터 삭제

학생번호가 20103인 학생이 삭제되어 조회 불가.

* Empty set : '비어 있는 값' 이라는 의미.

함수의 사용

함수의 사용방법

- 데이터 입력 -> 저장할 값에 함수를 사용.

insert into 테이블이름 values (함수이름(값), 함수이름(값), ~~~~, 함수이름(값));

- 데이터 수정 -> 수정될 값에 함수를 적용.

update 테이블이름 set
컬럼이름=함수이름(값), 컬럼이름=함수이름(값), ~~~, 컬럼이름=함수이름(값)
[where 함수가 적용된 검색조건];

- 데이터 삭제 -> 삭제된 대상을 지정하는 where 절에 함수를 사용.

delete from 테이블이름 [where 함수가 적용된 검색조건];

예제 1)

학번이 20101번인 학생의 생년월일은 현재시각으로 변환.