MySQL - 04

복습

• DDL(Data Definition Language)
  • 데이터 정의어
  • 테이블에 관련된 쿼리문(create, drop, alter)
• DML(Data Mainpulation Language)
  • 데이터 조작어
  • CRUD 작업을 하는 언어
    • 데이터 추가, 삭제, 수정, 검색

    #추가
    insert into 테이블명 [(컬럼명1, 컬럼명2,..)] values(값1, 값2, ...);
    #삭제
    delete from 테이블명 where 조건식;
    #수정
    update 테이블명 set 컬럼명 = 새로운값, [컬럼명 = 새로운값] where 조건식; 
    #검색
    select 컬럼명1, 컬럼명2 ... from 테이블명 where 조건식;

Auto_increment, date, datetime 타입

AUto)increament

Table에 값이 추가될 때마다 증가하는 값을 미리 설계할 수 있다.
PK가 자료형이 int라면 설정 가능

create table 테이블명(
		컬럼명 int primary key auto_increment,
	);

date, datetime 타입

• date
  • 'YYYY-MM-DD' 형태로 삽입
• datetime
  • 'YYYY-MM-DD HH:MI:SS' 형태로 삽입

예제

create table product(
	prodnum int primary key auto_increment,
    prodname varchar(300),
    prodprice int not null,
    regdate datetime
);
# 데이터 삽입
# prodnum을 명시하지 않아도 되지만 다른 데이터테이블명을 명시적으로 작성해야한다.
insert into product (prodname, prodprice) values('구찌',100000);
insert into product (prodname, prodprice) values('프라다',300000);

# 아래와 같이 저장됨 
1	구찌		100000	
2	프라다	3000000	

# date도 값을넣어보자 
insert into product (prodname, prodprice, regdate)
values('구찌 공책', 300000, '2022-04-12 19:15:00');

# 위와 같이 시간을 직접 넣을 수도 있고 아래처럼 현재시간을 넣을 수 있다.
insert into product (prodname, prodprice, regdate) 
values('구찌 키보드', 340000, now()); 

아래와 같이 저장되었다.

like 조건식, 와일드카드

like 조건식

뒤에 오는 와일드 카드 문자열과 비교하여 같다면 참, 다르다면 거짓
컬럼 like('와일드카드 문자열');

와일드카드

_ : (한글자)
% : 모든것

'%A'		:	글자수에 상관없이 끝이 'A'면 참	
'_이_'	:	세글자 중 가운데가 이면 참
'_이%'	:	두번째 글자가 '이' 이면 참
'%이_'	:	뒤에서 두번째 글자가 '이' 면 참
'__이%'	:	세번째 글자가 '이'이면 참
'_이_%'	:	두번째 글자가 '이' 이며 세글자 이상이면 참

예제

#'구찌'라는 이름이 들어가는 모든 상품 검색
select * from product where prodname like('%구찌%');

#이름에 키가 들어가는 모든 상품 검색
select * from product where prodname like('%키%');

함수

단일행 함수

행 하나당 결과를 하나 만들어내는 함수
문자함수, 숫자함수, 형변환함수, null처리 함수

#문자함수
select concat('he','llow'), length('flower) from dual;
#숫자함수
select ceil(3.14),floor(3.14), round(3.141592,2) from duql;
#널처리 함수
select ifnull(regdate, '등록날짜없음') regdate from productt where prodname ='구찌지우개'

문자함수와 숫자함수는 잘 쓰지 않는다. 차라리 데이터를 받아서 자바나 프로그래밍 언어환경에서 처리하는게 더 쉽기 때문이다.
단, 널처리 함수는 많이 쓰이니 잘 알아두도록 하자

그룹 함수

여러 행의 데이터들을 받아서 하나의 결과로 도출해주는 함수
sum, min, max, avg, count

기타 조건

group by

~별 ~~
대륙별 총 인구수
그룹함수를 적용시킬 파트를 나누는 문법
group by 컬럼1, 컬럼2,. ...
컬럼1로 그룹 짓고 그 내부에서 컬럼2로 그룹 짓고 ,..

# 대륙 별 총 인구 수 
select continent, sum(population) "총 인구" from country
group by continent;

having

group by를 통해 그룹을 짓고 구해진 결과가 있을 때
각 그룹에 조건을 부여할 때 사용하는 문법
where절과 having 절의 차이

• where 절은 각 데이터들에게 적용되는 조건을 설정, 조건식에 그룹함수 사용 불가
• having 절은 각 그룹에게 적용되는 조건을 설정, 조건식에 그룹 함수 사용 가능

# 대륙 별 총 인구수가 4억 이상인 대륙과 총인구수
 select continent 대륙, sum(population) "총 인구수" 
from country group by continent having sum(population) > 400000000;

order by

검색 결과를 정렬하는 문법
order by 컬럼1[정렬기준], 컬럼2[정렬기준],.....
-> 컬럼1 기준으로 정렬 후 내부에서 컬럼 2 기준으로 정렬
정렬기준 옵션
asc : 오름차순
desc : 내림차순

# 대륙별, 지역별 인구수를 정렬시키기
select continent 대륙, region 지역, sum(population) "총 인구수" 
from country group by continent, region order by continent asc;

limit

검색된 결과의 개수와 위치를 제한하는 문법
oracle에는 없음,
게시판을 불러올때 유용하게 쓰임

	select문 limit 정수(n);
			검색된 결과 맨 위에서 n개만 추출

	select문 limit 정수(n), 정수(m);
			검색된 결과의 n번째부터 m개만 추출

# 위에서부터 10개의 데이터 추출
select * from country order by name limit 10;
# 위에서 5번째부터 5개 데이터 추출
select * from country order by name limit 5,5;

연습

#대륙별로 평균 gnp 검색
select continent 대륙, round(avg(gnp),2) gnp from country group by continent;

#gnp 평균이 100000 이상인 지역들의 지역명, gnp 최대값, gnp 최소값, gnp 평균 검색
select Name, max(gnp), min(gnp), round(avg(gnp),2) 
from country group by region having avg(gnp) > 100000;

#대륙별 평균 인구를 출력하되 15000000명을 넘는 대륙만 검색
select continent, round(avg(population)) from country 
group by continent having avg(population) > 15000000;

#인구수가 2000만명을 넘는 나라들의 평균 넓이가 2000000 제곱km를 넘는 대륙들만 검색
select continent, surfaceArea, population from country where population > 20000000 
group by continent having SurfaceArea > 2000000;

#대륙별, 그리고 지역별로 나라들의 평균 수명 검색
select continent, region, lifeExpectancy 
from country group by continent, region order by continent, region ;

#아시아, 유럽에 속한 나라들 중에 정치제도별로 정치제도, 나라수, 평균수명, 평균gnp 검색
select continent 대륙, GovernmentForm 정치제도, count(GovernmentForm) '나라 수',
ifnull(round((lifeExpectancy),2), 0) '평균수명', round(avg(gnp),2) '평균gnp'
from country 
where continent in ('Asia', 'Europe') 
group by GovernmentForm order by continent;