SQL이란? (3/3)

jungnoeun·2021년 7월 18일

Naver boostcourse mysql sql

bc

목록 보기

4/7

DML

cast 형변환

mysql에서는 실제로 데이터 타입을 바꿔서 사용하는 기능이 따로 없었는데 버전4.2부터 추가됨.
대표적으로 CAST,CONVERT함수 등이 있음.
cast함수는 타입을 변경(지정)하는데 유용하다.
cast 함수의 사용법:
- CAST(expression AS type)
  -> expr식에서 나오는 값을 as뒤에 나오는 타입으로 바꿔달라.
- CONVERT(expression,type)
  -> cast에서 as를 사용했다면 convert에서는 ,를 사용해 표현한다. 마찬가지로 식의 값을 ,뒤의 타입으로 바꿔달라는 함수이다.
- CONVERT(expr USING transcoding_name)
  -> convert함수중에는 using으로 구분해서 사용하면 Charset을 바꿀 수 있는 부분도 존재한다.
MySQL 타입: BINARY,CHAR,DATE,DATETIME,SIGNED{INTEGER},TIME,UNSIGNED{INTEGER}

예제

mysql> select cast(now() as date);
mysql> select cast(1-2 as unsigned);
mysql> select cast(1-2 as signed);

-> 차례때로 현재날짜를 보여달라. -1을 unsigned형으로 보여달라. -1을 signed형으로 보여달라이다.

그룹함수

단일함수: 이전까지 썼던 함수들로 칼럼하나에 하나의 결과가 나오는 것이다.
그룹함수: 여러개의 칼럼의 값을 가지고 결과값을 하나만 만들어내는 것이다.

단일함수의 경우,

mysql> select concat(name,'aaaa') from employee;

-> employee 테이블의 모든 직원들의 이름뒤에 aaaa를 붙여서 출력해라.

그룹함수의 경우,

mysql> select count(*) from employee;

-> count는 전체 몇개인지 카운트해준다. 그리고 결과가 하나(총개수)만 나온다. 전체가 몇개 레코드인지 알고싶을 때 위처럼 *을 써준다.(결과: 14)

mysql> select avg(salary) from employee;

-> employee 테이블에서 모든 직원들의 급여의 평균을 보여준다. 결과는 하나이다.(그룹함수)

boss라는 칼럼에 NULL값을 가진 데이터가 있는 경우, select count해보면 14개가 아니라 13개가 나온다 널값은 제외하고 카운팅하기 때문이다.

mysql> select avg(salary),sum(salary) from employee where deptno = 30;

-> employee 테이블에서 부서번호가 30인 직원의 급여의 평균과 총합계를 출력해라.

mysql> select deptno,avg(salary), sum(salary) from employee
group by deptno;

-> employee 테이블에서 부서별 직원의 부서번호, 급여평균과 총합계를 출력해라. group bt 절을 사용하였다.

어떤 조건에 대해서 그룹으로, 그룹별로 뭔가 하고 싶다면 반드시 group by라는 키워드를 사용해서 어떤 기준으로 grouping을 한건지 알려줄 수도 있다.
원하는 쿼리들이 생겼을때, 원하는 데이터베이스에서 내가 원하는 값을 추출해내야할 때, 어떤 쿼리들이 있는지 생각하면서 학습하기.

데이터 입력 (INSERT문)

insert into 테이블명(필드1,필드2,필드3,필드4, ...)
	values (필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, ...)
    
insert into 테이블명
	values (필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, ...)

첫번째 insert문의 경우 나열된 필드의 순서대로 알맞게 맵핑을 시켜서 필드에 실제로 입력할 값들을 써주면 된다.
이때 필드의 개수와 필드의 값의 개수는 같아야 한다. 그리고 필드와 값이 제대로 맵핑이 되어야 한다. 데이터타입 맞추기.
필드명은 생략도 가능한데, 생략했을 때는 반드시 모든 필드의 값들을 채워줘야 한다. 이때 필드 순서는 테이블을 생성했을때 만들어진 순서이다. desc 테이블명의 결과로 나오는 순서대로 필드값을 부여해주면 된다.
원하는 필드만 골라서 입력하고 싶으면 필드를 나열하고 값을 맵핑해주면 된다.
모든 것을 쓸때는 그냥 사용하면 된다.

만약 테이블 생성시 디폴트 값 같은 것이 세팅되어 있었다면 필드가 나열되지 않아도 해당 디폴트 값이 알아서 입력이 될것이다.

mysql> insert into ROLE(role_id, description) values(200,'CEO');
mysql> insert into ROLE values(200,'CEO');

-> ROLE 테이블에 role_id는 200, description에는 'CEO'로 한건의 데이터를 저장해라. 만약 desc ROLE을 실행해보고 필드가 위의 2개가 다면 2번째 쿼리와 같이 나타낼 수 있다.

mysql> insert into ROLE(description) values('aaa');

-> 위의 ROLE 테이블에 대해서 위의 명령어를 실행하면 에러가 발생하는 것을 볼 수 있다. 위의 테이블에서 role_id를 보면 KEY에서 PRIMARY KEY로 지정되어 있는 것을 볼 수 있다. PRIMARY KEY는 널 값을 입력할 수 없는데, 디폴트가 널이다. 지정하지 않으면 널 값이 들어가서 오류를 발생시킨다.

mysql> insert into ROLE(role_id) values(201);

-> 반대로 role_id만 넣어서 입력하면, 201번이 제대로 입력되는 것을 볼 수 있다.

즉, 칼럼명을 나열해서 원하는 칼럼만 입력이 가능하지만, 대신 NOT,NULL 조건이나 PRIMARY KEY 와 같이 널이 들어갈 수 없는 칼럼들은 반드시 나열해서 값을 지정해야 한다. 그렇지 않으면 널 값이 들어가서 조건자체가 받아들여지지 않을 것이다. 그렇게 되면 오류를 발생시킨다.

데이터 입력 (UPDATE문)

칼럼의 내용을 수정할 수 있는 업데이트 구문이다.

UPDATE 테이블명
	SET 필드1 = 필드1의 값, 필드2 = 필드2의 값,필드3 = 필드3의 값,...
WHERE 조건식

set이라는 예약어를 사용해서 어떤 칼럼명(필드)에 값을 뭐로 바꿀건지 주면 된다. 여러개의 필드 값을 바꿀떄는 ,로 구분하면 된다.
그리고 where절을 사용할 수 있다. where절은 필수는 아니지만, 생략하게 되면 전체 데이터가 다 바뀐다.

update ROLE
set description = 'CTO'
where role_id = 200;

-> ROLE 테이블에 role_id가 200인 경우 description을 'CTO'로 수정해라.
단, where절에 적절한 조건을 부여하지 않으면 모든데이터가 수정되므로 조심해야 한다.

데이터 삭제 (DELETE문)

delete
 from 테이블명
where 조건식

-> 조건식을 통해 특정 row만 삭제할 수 있다.

마찬가지로 조건식을 주지 않으면 다 지워질 수 있다.

delete from ROLE where role_id = 200;

-> ROLE 테이블에서 role_id는 200인 정보를 삭제하여라.

DDL

Data Definition Language.
db의 스키마를 생성,변경,제거하기 위해 사용됨.
CREATE, DROP, ALTER등이 해당된다.

MySQL 데이터 타입

테이블을 정의하기 위해서는 테이블의 어떤 필드들을 정의하고, 해당 필드들은 어떤 타입이고, 자릿수는 얼만큼 차지할 것인지 등의 정보를 줘야 함.
-> 이때 필요한 것이 데이터타입이다.
내가 사용할 이 테이블의 이 칼럼에는 어떤 타입의 데이터들을 사용할지 지정할 수 있다.

테이블 생성

mysql> create table 테이블명
-> 필드명1 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREASED],
-> 필드명2 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREASED],
-> 필드명3 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREASED]
  ..............
-> PRIMARY KEY(필드명)
);

테이블은 칼럼(필드)가 필요하니 각 필드의 이름을 정의해준다.
제약조건
- 필드는 NULL값을 허용할거다 또는 아예 NOT NULL로만 실행할거다.
- 디폴트 값을 부여할 것이다.
- 자동으로 값이 늘어나는 옵션을 부여할 것이다.
- PRIMARY KEY로 특정한 필드를 지정할 것이다. 단, PRIMARY KEY는 필드생성시에도 지정가능하다.

참고: 위의 create 절에서 table이 아닌 다른 객체로 실행을 하면 똑같은 구문으로 실행이 된다.

예시

mysql> create table book(
  -> ibsn varchar(10) primary key,
  -> title varchar(20) not null,
  -> price integer not null);

ibsn,title,price 라는 칼럼이 있는 book 테이블 생성
잘 만들어졌는지 show tables를 통해 확인가능.

테이블 수정 (컬럼 추가/삭제)

alter 예약어 사용.
추가시에는 add 예약어 사용하고 필드이름, 타입, 제약조건 부여해야 함.
삭제시에는 drop사용.

mysql> alter table 테이블명
  -> add 필드명 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREASED];

mysql> alter table 테이블명
  -> drop 필드명;

예시

mysql> alter table book
  -> add author varchar(20);

mysql> alter table book
  -> drop price;

book이라는 테이블에 author이라는 칼럼(필드) 추가. author 칼럼은 20개이하의 자리를 갖는 문자형태를 갖음.
price 필드(칼럼)를 삭제함.

테이블 수정 (칼럼 수정)

change 예약어 사용

mysql> alter table 테이블명
  -> change 필드명 새필드명 타입 [NULL | NOT NULL][DEFAULT][AUTO_INCREASED];

원래의 필드를 새로운 이름, 타입, 제약조건으로 바꿔준다.
desc 테이블명으로 필드 확인 가능.

테이블 이름 변경

mysql> alter table 테이블명 rename 변경이름;

원래의 테이블명을 rename뒤의 이름으로 바꿈

테이블 삭제

drop 예약어 사용

mysql> drop table 테이블명;

단, 제약조건 때문에 삭제가 되지 않는 경우가 존재한다.
우리 샘플 테이블로 예를 들면, department 테이블과 employee 테이블이 있다. department 테이블에 부서넘버라는 필드가 있고 employee테이블에도 부서넘버 필드가 존재한다.
해당 필드들은 서로 관계를 가지고 있다. 이와 같은 관계를 foreign key 관계라고 한다.
이때, employee 테이블에 deptno를 삽입할때, department 테이블이 갖고 있는 숫자만 가능하다. 그렇지 않으면 null값으로 입력을 해야 한다.

예시
```
mysql> insert into employee (empno,name,deptno)
  -> values(1111,'kang',100);
```
employee 테이블의 deptno에 department 테이블이 갖고 있지 않은 값인 100을 넣어본다. -> 에러발생
에러 발생 이유: foreign key 제약조건으로 department 테이블과 연관관계를 맺고 있기 때문이다. 그래서 없는 값 넣으면 안된다.
```
mysql> insert into employee (empno,name,deptno)
  -> values(1111,'kang',30);
```
department 테이블에 존재하는 값인 30을 deptno에 넣었을 때 제대로 입력이 된다.
즉, 테이블을 삭제할 시, 테이블을 생성한 반대순서로 삭제해야 한다.
위의 예시에서는 employee 테이블 삭제후에 department 테이블을 삭제해야 한다.

jungnoeun

개발자

이전 포스트

SQL이란? (2/3)

다음 포스트