데이터베이스 2. SQL

테이블 생성

create table 'shop_db'.'member'(
	'member_id' char(8) not null,
    'member_name' char(20) not null,
    'member_addr' varchar(45) null,
primary key('member_id'));

create table city_popul(select name, population from world.city);
// create하면서 동시에 insert하는 방법

create table city_popul(select name as city_name, population as city_popul from world.city);
//이름 바꾸는 것도 가능

 

shop_db에 member이라는 테이블을 만든다. member_id은 8글자까지 member_name는 20글자까지

member_addr은 가변길이문자역 45자까지 기본키는 member_id

char()은 속도는 빠르나 공간낭비 가능성, varchar()은 가변길이로 공간 저장성이 좋으나 속도가 느림

 

테이블 값 넣기 insert

insert into 'shop_db'.'member'('goah','고흐','네덜란드');
insert into 'shop_db'.'member'('goah','고흐',NULL);

shop_db의 member테이블에 값을 행으로 넣겠다.

null이 허용된 값에는 null넣기 가능

 

insert into hongong(toy_id, toy_name) values (3, '버즈');
// 특정 값에만 넣기 가능

insert inyo hongong (toy_name, toy_id) values('제시'ㅡ 3);
//값 순서를 바꾸는 것도 가능

insert into member values(값1, 값2), (값3, 값4), (값5, 값6);
//여러 행의 값을 한번에 넣는 방법

insert into city_popul select name, population, from world.city;
// select 결과를 바로 insert하는 방법

 

 

테이블에서 특정 값 가져오기 select

select * from memebr;
select * from where member_id = 'goha';

select count(*) from buy where price<= 30 and amount > 1;
// 조건을 만족하는 개수를 출력 and로 두 조건 모두 만족하는 것만 출력하도록 함.

select sum(amount) frm buy where group_name is null
//합을 출력함 is null로 null값만 출력하는 것 가능

select max(num) form buy;
// 가장 큰 값을 출력함.

select 열이름
form 테이블이름
where 조건식
group by 열 이름
having 그룹 조건식
order by 열이름 정렬조건
limit 숫자;
//select 작성 순서

 

member테이블의 모든 값을 가져와라, where로 조건주기 가능

조건이 없다면 기본키 순으로 나온다

 

스키마

데이터베이스를 스키마라고도 한다. 

 

데이터 형식

char varcharm int등 저장될 데이터 형식

 

예약어

select, insert등과 같이 기존에 약속된 SQL

 

인덱스

데이터를 빠르게 찾을 수 있는 목차개념

데이터가 적을 때는 체감할 수 없으나, 데이터가 많아지면 효과를 발휘

없을때는 모든 데이터를 조회하는 full scan

 

인덱스 생성

create index idx_member_name on member(member_name);

drop index idx_memebr_name on memebr;

특정 테이블에 인덱스를 생성, 삭제 하는  방법

 

뷰

create view member_view as select member_id, member_name from member;
select * from member_view;

테이블의 일부만 보여주고 싶을 때 사용

물리적으로 새로운 테이블이 생기는 것이 아니라 권한제한으로 일부만 보여주는 것

 

스토어드 프로시저 

SQL안에서도 일반 프로그래밍 처럼 코딩할 수 있음.

 

데이터베이스 생성하기

drop database if exists marker_db;
create database marker_db;
use market_db;

만약 market_db가 있다면 삭제하고 다시 만들고 해당 db를 사용한다.

 

주석 사용하는 방법

--insert into 'shop_db'.'member'('goah','고흐','네덜란드');

--를 붙인다.

 

연습문제

select sum(amount * price) from member where member_id = 'BLK';
// BLK가 구매하면서 사용한 금액의 합을 구함

select mem_name,debut_date from memeber where addr='서울' order by debute_date asc;
//주소지가 서울인 가수들의 이름과 데뷔일자가 데뷔일 순서대로 나오도록 SQL문 작성

 

between and문

select mem_name, height from member where height >= 163 and height <=165;
//and문을 사용해 두 조건을 모두 만족하는 결과를 출력

select mem_name, height from member where height betweent 163 and 165;
//between and를 사용해  사이 값을 출력

 

in을 통한 조건 주기

select mem_name, addr from member where addr = '경기' or addr='전남' or addr='경남';
select mem_name, addr from member shere addr in('경기','전남','경남');
동일한 결과

in에 있는 값만 가져옴

 

like %문을 활용해 특정 데이터 조회

select * from member where member_name like '우%';
select * from member where mem_name like '_ _핑크';

우로 시작하는 모든 값, 앞에 두 글자가 있고 핑크로 끝나는 모든 값.

 

서브쿼리

여러개의 SQL문이 필요한 경우 하나의 SQL로 합쳐서 사용하는 방법

select. mem_name, height from member where height > (select height from member where mem_name = '에이핑크');

 

관계연산자/ 논리연산자

<>= <= >= 등 크다 작다 같다 등을 지정
and or 참 거짓을 판단

 

auto_increment 자동으로 숫자를 증가시켜줌. 테이블 생성시 지정, 기본값 등으로 활용

* 모든 열을 지정하는 가호

 

별명 짓기

select mem_id ad ID mem_name as 이름 from member where mem_id='itz';
select mem_id ad ID mem_name as '이름 순서' from member where mem_id='itz';

이름 대신 활용할 별명을 지음. as다음에 넣음

별명에 공백이 있을 경우 ''나 ""사이에 넣는다. 

공백 사용은 비추천

 

 

order by

select * from member where height >= 164 order by height desc;

select * from member where height >= 164 order by height desc, debut_date asc;

정렬조건을 지정함. 

asc 오름차순 desc 내림차순 

여러조건도 입력가능

 

limit

출력개수 제한함.

select mem_id, debut_date from member limit 0, 2;
select mem_id, debut_date from member limit 2;
select mem_id, debut_date from member limit 2, 4;

0으로 시작하면 처음부터 2개

시작 조건 생략가능

2, 4는 2번째부터 4개를 출력하라

 

disctinct

중복결과를 제거

select distinct addr from member;

addr 중 중복값을 제거하고 가져와라

 

group by, having

그룹을 지정하고 그 그룹에 대한 조건을 지정함

select mem_id, sum(amount) from buy group by mem_id;
// mem_id별로 그룹으로 묶고 양의 합계를 출력한다.

select addr 주소, count(*) 회원수 from member groug by addr having count(*) >= 2;
// 그룹에 조건을 지정함

 

avg(), count(), sum(), max(), min(), count(distinct)

select avg(amount) form buy;
select count(*) from member;
select sum(amount) from member;
select min(amount) from member;
select max(amount) from member;
select count(distinct *) from member;

평균값, 행의 개수, 값의 합, 최소값, 최대값, 중복을 제거한 개수를 출력해줌

 

자동 값 증가 auto_increment 

기본키에만 사용 가능

데이터 삽입시에는 null로 넣으면 된다.

create table hongong (
toy_id int auto_increment primary key,
toy_name char(4),
age int);
//테이블 생성시 선언

insert into hongong values(null, '이름', 24);
//자동생성은 null

select last_insert_id
//마지막으로 입력된 auto_increment값을 가져옴

alter table hongong auto_increment=100;
//자동생성 시작값을 변경함
//이후에는 101 102 이런식으로 생성

 

데이터 수정 update

update city_popul
	set city_name = '뉴욕', population = 0
    where city_name = 'New York';

기존에 입력된 데이터를 수정하는 문장

 

행 삭제 delete

delete from dity_popul where city name like 'new%';
--new로 시작하는 값을 지워라

delete from dity_popul where city name like 'new%' limit 5;
-- limit도 사용할 수 있다. 상위 5개를 지우라는 뜻

where절이 없으면 모든 데이터를 삭제한다.

 

 

테이블 자체 삭제 drop

drop table big_table2;
-- 테이블 자체를 삭제하라

 

테이블 내 모든 자료를 삭제 truncate

truncate table big_table2;
-- 테이블 내 모든 데이터를 삭제한다

delete보다 빠르게 삭제하며 where로 조건을 줄 수는 없다.(모든 데이터 삭제)

 

테이블 들을 출력하는 방법

show tables;

 

SQL주석처리

-- 주석
/*주석*/ 
# 주석

SQL의 주석을 처리하는 3가지

 

SQL 합계출력 roll up

select addr, count(*)
from member
group by addr with rollup;

rollup는 모두 합친 계가 출려된다

위 처럼 group by를 사용하면 그룹별 계도 출력된다

 

DDL create drop alter 테이블 조작

DML insert updete delete select 내용 변경

DCL grand deny revock 등 권한