반응형
SQL 주요 문법 정리
데이터베이스 기본 개념
- 데이터베이스(DB): 데이터/자료들의 집합/모임.
- 관계형 DB: 자료들 간의 종속되는 관계를 표현할 수 있는 DB.
- 테이블: 행과 열로 이루어진 데이터 형식 (표).
SQL (Structured Query Language)
주의할 점
- SQL은 대소문자를 구분하지 않습니다.
- SQL 명령어 문장 끝에는 세미콜론(
;)으로 마무리합니다.
[0] 메타데이터 관리어
1. show
show databases;: DB 서버 내 전체 DB 목록을 조회합니다.show variables like 'datadir';: DB 서버 내 로컬 경로를 조회합니다.show tables;: 활성화 중인 DB 내 모든 테이블을 조회합니다.
2. use
use DB명;: DB 서버 내에서 지정한 DB를 활성화(사용)합니다.
[1] DB 정의어 (DDL - Data Definition Language)
1. create
- DB 생성:
create database DB명; - 테이블 생성:
create table 테이블명 ( 속성명1 타입 제약조건, 속성명2 타입 제약조건 );
2. drop
drop database DB명;: 지정한 DB를 삭제합니다.drop database if exists DB명;: 만약 지정한 DB가 존재하면 삭제합니다.drop table 테이블명;: 지정한 테이블을 삭제합니다.drop table if exists 테이블명;: 만약 지정한 테이블이 존재하면 삭제합니다.
[2] DB 조작어 (DML - Data Manipulation Language)
참고: MySQL Workbench에서는 기본적으로
safe mode가 활성화되어 있어UPDATE및DELETE가 제한될 수 있습니다.
safe mode해제:set SQL_SAFE_UPDATES = 0;safe mode설정:set SQL_SAFE_UPDATES = 1;
1. INSERT (레코드 추가)
- 특정 속성에 값 추가:
insert into 테이블명 (속성명1, 속성명2) values (값1, 값2);- 모든 속성에 값 추가 (속성명 생략 시, 테이블 정의 순서대로 값 입력):
insert into 테이블명 values (값1, 값2);- 여러 레코드 한 번에 추가:
insert into 테이블명 values (값1, 값2), (값1, 값2), (값1, 값2);
2. SELECT (레코드 조회)
- 전체 속성 조회:
select * from 테이블명; - 특정 속성 조회:
select 속성명1, 속성명2 from 테이블명; - 조건에 따른 조회:
select * from 테이블명 where [조건절];
3. UPDATE (레코드 수정)
- 전체 레코드의 속성값 수정:
update 테이블명 set 속성명 = 새로운값; - 조건에 따른 레코드의 속성값 수정:
update 테이블명 set 속성명 = 새로운값 where [조건절]; - 다수 속성값 동시 수정:
update 테이블명 set 속성명1 = 새로운값1, 속성명2 = 새로운값2 where [조건절];
4. DELETE (레코드 삭제)
- 전체 레코드 삭제:
delete from 테이블명; - 조건에 따른 레코드 삭제:
delete from 테이블명 where [조건절];
데이터 타입
| 종류 | 타입 | 설명 |
|---|---|---|
| 정수 | tinyint |
1바이트 (-128 ~ 127) |
smallint |
2바이트 (±3만) | |
mediumint |
3바이트 (±800만) | |
int |
4바이트 (±21억) | |
bigint |
8바이트 (±21억 이상) | |
unsigned |
부호 없음 (양수 범위 2배 증가) | |
| 실수 | float |
4바이트 소수점 |
double |
8바이트 소수점 | |
decimal |
문자 타입의 소수점 (오차 없음) | |
| 날짜/시간 | date |
날짜 (YYYY-MM-DD) |
time |
시간 (HH:MI:SS) | |
datetime |
날짜와 시간 | |
| 문자 | char(길이) |
고정 길이 문자열 (최대 255) |
varchar(길이) |
가변 길이 문자열 (최대 255) | |
text |
최대 6만 글자 | |
longtext |
최대 42억 글자 (4GB) | |
| 논리 | bool |
true 또는 false (내부적으로 tinyint) |
제약 조건
not null:null값을 허용하지 않습니다.unique: 중복값을 허용하지 않습니다.default 기본값: 값 생략 시 지정된 기본값으로 초기화합니다. (예:default 0,default now())auto_increment: 레코드 추가 시 자동으로 번호를 1씩 증가시킵니다. (primary key와 함께 사용)primary key: 기본 키(PK). 레코드를 식별하는 고유한 값입니다. (not null과unique속성을 포함)- 선언:
constraint primary key(필드명)
- 선언:
foreign key: 외래 키(FK). 다른 테이블의primary key를 참조합니다.- 선언:
constraint foreign key(FK필드명) references 참조테이블(PK필드명)
- 선언:
주요 키워드 및 연산자
키워드
- 별칭 (
as):select 속성명 as 별칭 from 테이블명;(as는 생략 가능) - 중복 제거 (
distinct):select distinct 속성명 from 테이블명;
연산자
- 산술:
+,-,*,/,div(몫),mod(나머지) - 비교:
>,<,>=,<=,=,!= - 논리:
and,or,not - 범위 (
between):where 속성명 between 시작값 and 끝값 - 포함 (
in):where 속성명 in (값1, 값2) - NULL:
where 속성명 is null,where 속성명 is not null - 문자 패턴 (
like):%: 모든 문자 대응 (글자 수 제한 없음) -like '김%'_: 한 글자 대응 -like '김_'
데이터 정렬 및 제한
정렬 (order by)
- 오름차순:
order by 속성명 asc(기본값) - 내림차순:
order by 속성명 desc - 다중 정렬:
order by 속성명1 asc, 속성명2 desc
조회 결과 제한 (limit)
limit 레코드수: 지정된 수만큼의 레코드만 조회합니다.limit 시작인덱스, 개수: 페이징 처리에 주로 사용됩니다.- 1페이지:
limit 0, 10 - 2페이지:
limit 10, 10
- 1페이지:
SQL 키워드 작성 순서
select -- (3)
from -- (1)
where -- (2)
order by -- (4)
limit -- (5)[ 데이터베이스 ] : 데이터/자료들의 집합/모임 (이하 *DB*)
[ 관계형 DB ] : 자료들 간의 종속되는 관계 표현 가능한 DB
[ 테이블 ] : 행과 열로 이루어진 데이터의 형식 = 표
[ SQL ]
- 주의할점
1) SQL 대소문자를 구분하지 않는다.
2) SQL 명령어 문장 끝에 (;)으로 마무리한다.
[0] 메타데이터 관리어
1. show
1) show databases; : DB서버내 전체 DB 목록 조회
2) show variables like 'datadir' : DB서버내 LOCAL PATH 조회
3) show tables; : *활성화*중인 DB내 모든 테이블 조회
2. use
1) use DB명; : DB서버내 지정한 DB 활성화(사용)
[1] DB 정의 : DDL
1. create
1) create database DB명; : 지정한 DB 생성
2) create table 테이블명( : 지정한 테이블 생성
속성명1 타입 제약조건 ,
속성명2 타입 제약조건
);
2. drop
1) drop database DB명; : 지정한 DB 삭제
2) drop database if exists DB명; : 만약 지정한 DB 존재하면 삭제
3) drop table 테이블명; : 지정한 테이블 삭제
4) drop table if exists 테이블명; : 만약에 지정한 테이블 존재하면 삭제
[2] DB 조작어 : DML * 키워드/문법에 관한 대소문자 구분 없음. *이 파트는 암기해라*
1. INSERT
1) 특정 속성에 값 대입하여 레코드 추가
insert into 테이블명( 속성명1 , 속성명2 ) values ( 값1, 값2 );
2) 모든 속성에 값 대입하여 레코드 추가 , 속성명 생략(단 속성명 순서)
insert into 테이블명 values( 값1, 값2 );
3) 하나의 insert로 여러개의 레코드를 추가
insert into 테이블명 values( 값1, 값2 ) , ( 값1, 값2 ) , ( 값1, 값2 );
- 주의할점 : 각 속성의 제약조건에 따라 적절하게 값 대입
2. SELECT
1) 전체 속성에 레코드 조회 , *(와일드카드)
select * from 테이블명;
2) 특정 속성에 레코드 조회
select 속성명1 , 속성명2 from 테이블명
3) 특정 속성 조건에 따른 레코드 조회
select * from 테이블명 where [조건절]
3. UPDATE
MySQL Workbench에서는 수정/삭제를 safe mode이므로 불가능
safe mode 해제 : set SQL_SAFE_UPDATES = 0 VS 1은 세이프모드 구동
set SQL_SAFE_UPDATES = 0;
1) 전체 레코드의 속성값 수정
update 테이블명 set 속성명 = 새로운값;
2) 특정 속성 조건에 따른 레코드의 속성값 수정
update 테이블명 set 속성명 = 새로운값 where [조건절];
3) 다수 속성값 수정
update 테이블명 set 속성명 = 새로운값 , 속성명 = 새로운값 where [조건절];
4. DELETE
1) 전체 레코드 삭제
delete from 테이블명;
2) 특정 속성 조건에 따른 레코드 삭제
delete from 테이블명 where [조건절];
[ 데이터타입 ] : 테이블내 속성들이 갖는/저장하는 자료들의 분류/타입/종류
1. 정수
1) tinyint 1바이트 -128 ~ 127
2) smallint 2바이트 +-약 30000
3) mediumint 3바이트 +-약 800만
4) int 4바이트 +-약 21억
5) bigint 8바이트 +-21억 이상
* signed : 부호가 있다(+ 양수 - 음수)
* unsigned : 부호가 없다(허용범위 *2)
int unsigned : 42억까지(대신 음수 없다.)
2. 실수
1) float 4바이트 소수점
2) double 8바이트 소수점
3) decimal 문자타입 소수점을 문자로 처리하는 타입 , 오차없음
3. 날짜/시간
1) date 날짜표현 2025-07-15
2) time 시간표현 10:02:30
3) datatime 날짜/시간표현 2025-07-15 10:02:30
4. 문자
1) char(길이) 문자열표현 고정길이 최대255글자 , char(5) -> "유재석" -> [유] [재] [석] [ ] [ ]
2) varchar(길이) 문자열표현 가변길이 최대255글자 , varchar(5) -> "유재석" -> [유] [재] [석] 남은길이삭제
3) test 문자열표현 최대6만글자
4) longtext 문자열표현 최대42억글자, 4GB, 대용량
5. 논리
1) bool 논리표현 true 혹은 false , tinyint와 같은 1바이트
[ 제약조건 ] : 테이블내 속성의 제약조건
1. not null : 해당 속성에 null 값을 저장하지 않는다.
2. unique : 해당 속성에 중복값을 허용하지 않는다. (중복방지)
3. default 기본값 : 해당 속성에 값 생략시 기본값으로 초기화한다.
default 0 : 해당 속성에 값이 생략되면 0 초기화
default now() : 해당 속성에 값이 생략되면 현재 날짜/시간 초기화한다.
4. auto_increment : (MySQL) 해당 속성에 순서대로 자동번호 초기화한다. ( + primary key랑 같이 써야함 )
5. primary key : 기본/식별키(PK) , 식별 가능한 고유 값 필드키
-> 학번, 사번, 주민등록번호, 주문번호, 제품번호, 게시물번호, 예약번호...
-> 절대적으로 중복이 없는 고유값 !!!!!
특징) not null , unique 포함
선언) constraint primary key( 필드명 )
6. foreign key : 참조/외래키(FK) , 다른 테이블의 기본키(PK)를 참조하는 키
-> 수강신청학번, 출근직원사번, 작성자회원번호 등
특징) FK필드는 NULL과 중복이 가능하다. PK값 참조.
선언) constraint foreign key( FK필드명 ) references 참조할테이블명( PK필드명 );
주의할점 : FK속성타입과 PK속성타입 일치한다.
[ 생성된 테이블 다이어그램 확인 ] : 여럿 테이블 간의 관계 확인
# workbench 상단메뉴 -> [Database] -> [Reverse Engineer] -> stored connection : local
# -> 비번 입력 -> DB 선택 -> finish
[ 키워드 ]
1. 별칭 : as
select 속성명 as 별칭 from 테이블명 as 별칭
select 속성명 별칭 from 테이블명 별칭
2. 중복제거 : distinct
select distinct 속성명 from 테이블명
[ 연산자 ]
1. 산술연산자 : +더하기 -빼기 *곱하기 /나누기 div몫 mod나머지
select (산술) from 테이블명 where (산술)
2. 비교연산자 : >초과 <미만 >=이상 <=이하 =같다 !=같지않다
select 속성명 from 테이블명 where (비교)
3. 논리연산자 : and이면서 or이거나 not부정
select 속성명 from 테이블명 where (논리)
4. 기타연산자
(1) select 속성명 from 테이블명 where 속성명 between 시작값 end 끝값
(2) select 속성명 from 테이블명 where 속성명 in( 값1 , 값2 );
5. null연산자
(1) select 속성명 from 테이블명 where 속성명 is null
(2) select 속성명 from 테이블명 where 속성명 is not null
6. 문자패턴
(1) 속성명 like '%' : 모든문자대응 , 문자수제한없음
select 속성명 from 테이블명 where 이름 like '김%'; 김으로 시작하는 이름 찾기
(2) 속성명 like '_' : 모든문자대응 , _개수만큼
select 속성명 from 테이블명 where 이름 like '김_'' 김풍 찾기. 이름은 한글자여야만해!
[ 정렬 ]
1. order by 속성명 asc : 오름차순 , asc/desc 생략시 기본값
2. order by 속성명 desc : 내림차순
* 주의할점 : 2차 정렬부터는 앞 정렬의 동일한 레코드끼리 정렬
select 속성명 from 테이블명 order by 학급 asc , 점수 desc; 학급 먼저 오름차순 정렬 후, 동일한 학급끼리 점수 높은쪽(내림차순)부터 정렬
[ 조회결과 제한 ]
1. limit 레코드수
2. limit 시작레코드번호(0) , 개수
* 주로 페이징처리에 사용됨.
select 속성명 from 테이블명 limit 0 , 10; [1페이지]
select 속성명 from 테이블명 limit 10 , 10; [2페이지]
select 속성명 from 테이블명 limit 20 , 10; [3페이지]
[ 키워드 작성 순서 ]
[select] 속성명 [from] 테이블명 [where] 조건절 [order by] 속성명 asc/desc limit 개수;
[3] [1] [2] [4]
# SQL 연산자
# 예제코드 복붙 후 전체 실행(ctrl + shift + enter)
-- ctrl + shift + enter
drop database if exists mydb0717;
create database mydb0717;
use mydb0717;
# 1. 회원테이블
create table member( # 아이돌 그룹
mid char(8) not null , # 식별키 최대 8자리
mname varchar(10) not null , # 그룹명 최대 10자리
mnumber int not null , # 인원수 정수 +-21억정도
maddr char(2) not null , # 지역 최대 2자리
mphone1 char(3) , # 지역번호 최대 2자리
mphone2 char(8) , # 전화번호 최대 8자리
mheight smallint , # 평균키 정수 +-3만정도
mdebut date , # 데뷔일 yyyy-mm-dd
constraint primary key ( mid ) # 제약조건
);
# 2. 구매테이블
create table buy(
bnum int auto_increment , # 구매번호 정수 자동번호 부여
mid char(8), # 구매자 FK
bpname char(6) not null , # 제품명 최대 6자리
bgname char(4) , # 분류명 최대 4자리
bprice int not null , # 가격 정수
bamount smallint not null , # 구매수량 정수
constraint primary key(bnum) , # 제약조건
constraint foreign key ( mid ) references member(mid) # 제약조건
);
# 샘플데이터
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');
INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '아이폰', '디지털', 200, 5);
INSERT INTO buy VALUES(NULL, 'BLK', '청바지', '패션', 50, 3);
INSERT INTO buy VALUES(NULL, 'MMU', '에어팟', '디지털', 80, 10);
INSERT INTO buy VALUES(NULL, 'GRL', '혼공SQL', '서적', 15, 5);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 2);
INSERT INTO buy VALUES(NULL, 'APN', '청바지', '패션', 50, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 1);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 4);
# 샘플 확인
select * from member; -- ctrl + enter
select * from buy;
# [1] as 별칭 , 조회 결과의 속성명 변경(단 원본의 속성명과 상관없다)
# 복잡한 조회에서 속성명 단순화
select mid from member;
select mid as 회원아이디 from member;
select mid as 회원아이디 , mname 회원명 from member; -- as는 공백으로 대체 가능.
select mid 회원아이디 from member as m; -- member 테이블명을 'm'으로 별칭하기.
select mid 회원아이디 from member m; -- as는 생략 가능하다. (띄어쓰기로 구분)
# [2] distinct , 속성값이 중복인 레코드 제거
select maddr as 주소 from member; -- 조회 결과 속성 값들의 중복이 존재
select distinct maddr as 주소 from member; -- 중복 제거
# [3] 산술연산자 , +더하기 , -빼기 , *곱하기 , /나누기 , div 몫 , mod 나머지
select mnumber + 3 as 더하기, mnumber -3 as 빼기, mnumber * 3 as 곱하기 ,
mnumber / 3 as 나누기 , mnumber div 3 as 몫, mnumber mod 3 as 나머지
from member;
# [4]
# 비교연산자 : >초과 , <미만 , >=이상 , <=이하 , *=등호* , !=부등호
# 논리연산자 : not 부정 , and 이면서 , or 이거나
# 기타연산자 : between 시작값 and 끝값 , 속성명 in ( 값1, 값2, 값3 );
select * from member where mname = '블랙핑크'; -- member테이블 mname속성값이 '블랙핑크'와 같으면
select * from member where mnumber = 4; -- member테이블 mnumber속성값이 4이면
select * from member where mname != '블랙핑크'; -- member테이블 mname속성값이 '블랙핑크' 아니면
select * from member where not mname = '블랙핑크'; -- member테이블의 mname속성값이 '블랙핑크'와 같지 않으면
select * from member where mheight <= 162; -- member테이블의 mheight속성값이 162 이하인 레코드 조호
select * from member where mheight >= 165 and mheight <= 170; -- mheight 속성값이 165이상 170이하일때
select * from member where mheight between 165 and 170; -- mheight 속성값이 165이상 170이하일때
select * from member where maddr = '경기' or maddr = '전남' or maddr = '경남'; -- 조건1 or 조건2 or 조건3
select * from member where maddr in('경기' , '전남' , '경남' ); -- ( 조건1 , 조건2, 조건3 )
# null연산자 : 속성명 is null , 속성명 is not null
select * from member where mphone1 = ' '; -- 속성값이 비어있으면? ' ' 빈칸 대신 null 사용
select * from member where mphone1 = null; -- 속성값이 null이면? X불가
select * from member where mphone1 is null; -- 속성값이 null이면? O가능
select * from member where mphone1 is not null; -- 속성값이 null 아니면
# 문자패턴 : 속성명 like '문자패턴' , %:문자대응(개수상관) , _:문자대응(_수만큼)
select * from member where mname like '에이%'; -- 속성값이 '에이'로 시작하는
select * from member where mname like '에이_'; -- 속성값이 '에이'로 시작하는 세글자(에이핑크 안잡힘)
select * from member where mname like '에이__'; -- 속성값이 '에이'로 시작하는 네글자(에이핑크 잡힘)
select * from member where mname like '%핑크'; -- 속성값이 '핑크'로 끝나는
select * from member where mname like '%이%'; -- 속성값에 '이'가 포함된
select * from member where mname like '_이_'; -- 속성값 두번째 글자가 '이'인 세글자
select * from member where mname like '__이_'; -- 속성값 세번째 글자가 '이'인 세글자(오마이걸, 트와이스 잡힘)
# [5] order by 속성명 asc/desc , 정렬 , asc(오름차순/기본값) desc(내림차순)
# 오름차순 : 1 2 3 4 , a b c , ㄱ ㄴ ㄷ , 25-07-15 25-07-16
# 내림차순 : 4 3 2 1 , c b a , ㄷ ㄴ ㄱ , 25-07-16 25-07-15
select * from member order by mdebut asc;
select * from member order by mdebut; -- asc/desc 생략시 자동으로 asc(기본)
select * from member order by mdebut desc;
# 주의할점 : 2개 이상의 정렬은 ,(쉼표) 구분하되, 1차정렬에서 중복값이 있을경우 중복값끼리 2차 정렬
# (1차) 먼저 주소(maddr) 내림차순으로 정렬후 (2차) 주소가 동일한 데이터끼리 mdebut(날짜) 오름차순 정렬
select * from member order by maddr desc , mdebut asc;
# [6] limit 레코드수 , 조회결과 제한 * 페이징 처리에서 사용
# limit 시작레코드번호(0~) , 개수
select * from member limit 2; -- 조회 결과에서 상위 2개만 제한
select * from member limit 0 , 2; -- 조회 결과에서 0번 레코드부터 2개 제한
select * from member limit 2 , 3; -- 2번 레코드부터 3개 제한
select * from member order by mheight desc limit 3; -- mheight속성 내림차순 후 상위 3개 제한 (키 큰 사람 3명)drop database if exists mydb0718;
create database mydb0718;
use mydb0718;
# 1. 회원테이블
create table member( # 아이돌 그룹
mid char(8) not null , # 식별키 최대 8자리
mname varchar(10) not null , # 그룹명 최대 10자리
mnumber int not null , # 인원수 정수 +-21억정도
maddr char(2) not null , # 지역 최대 2자리
mphone1 char(3) , # 지역번호 최대 2자리
mphone2 char(8) , # 전화번호 최대 8자리
mheight smallint , # 평균키 정수 +-3만정도
mdebut date , # 데뷔일 yyyy-mm-dd
constraint primary key ( mid ) # 제약조건
);
# 2. 구매테이블
create table buy(
bnum int auto_increment , # 구매번호 정수 자동번호 부여
mid char(8), # 구매자 FK
bpname char(6) not null , # 제품명 최대 6자리
bgname char(4) , # 분류명 최대 4자리
bprice int not null , # 가격 정수
bamount smallint not null , # 구매수량 정수
constraint primary key(bnum) , # 제약조건
constraint foreign key ( mid ) references member(mid) # 제약조건
);
# 샘플데이터
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');
INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '아이폰', '디지털', 200, 5);
INSERT INTO buy VALUES(NULL, 'BLK', '청바지', '패션', 50, 3);
INSERT INTO buy VALUES(NULL, 'MMU', '에어팟', '디지털', 80, 10);
INSERT INTO buy VALUES(NULL, 'GRL', '혼공SQL', '서적', 15, 5);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 2);
INSERT INTO buy VALUES(NULL, 'APN', '청바지', '패션', 50, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 1);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 4);
# [1] 그룹 : ~별 , ~끼리 , 두개이상의 중복[ 레코드/행/가로 ]되는 속성값들을 묶음
# 주의할점 :
select * from buy; -- buy 테이블의 모든 레코드 조회
select bpname from buy group by bpname; -- buy 테이블의 'bpname' 속성 그룹
select bpname , bprice from buy group by bpname; -- 오류
-- 오류 : bpname 속성값이 동일하더라도 bprice가 다를 수 있기 때문에!
-- 즉] 다수 속성명 조회시 하나의 그룹으로 불가능하다.
# [2] 집계/통계 함수 , +그룹
select bamount from buy;
select sum( bamount ) from buy; # sum( 속성명 ) : 합계
select avg( bamount ) from buy; # avg( 속성명 ) : 평균
select min( bamount ) from buy; # min( 속성명 ) : 최솟값
select max( bamount ) from buy; # max( 속성명 ) : 최댓값
select count( bamount ) from buy; # count( 속성명 ) : (레코드)개수 , null 제외
select count( * ) from buy; # count( * ) : (레코드)개수, null 포함
# (1) 회원아이디 별 구매수량 총합계 조회 , (그룹:mid) , (집계:bamount)
select mid , bamount from buy group by mid; -- 실행불가능
select mid , sum( bamount ) from buy; -- 실행불가능
select mid , sum( bamount ) from buy group by mid; -- 실행가능
# (2) 회원아이디 별 총구매금액(수량*가격) 조회
select mid , sum( bamount * bprice ) from buy group by mid;
# (3) 회원아이디 별 구매가격 평균 조회
select mid , avg( bprice ) from buy group by mid;
# (4) 총 구매횟수 조회
select count(*) from buy;
# (5) 회원아이디 별 구매횟수 조회
select mid , count( * ) from buy group by mid;
# [3] having : 그룹별(후) 조건 : 별칭O vs where 그룹(전) 조건 : 별칭X
# 1. 구매수량이 3 초과하는 회원아이디별 레코드 조회
select * from buy where bamount > 3;
select bamount as 수량 from buy where 수량 > 3; -- where(조건절)에서 별칭 쓰지마세요
# 2. 회원아이디별 총구매금액(집계)이 1000 초과인 레코드 조회
select mid , sum( bamount * bprice ) from buy group by mid having sum( bamount * bprice ) >= 1000;
select mid , sum( bamount * bprice ) as 총구매금액 from buy
group by mid having 총구매금액 >= 1000; -- 별칭 넣어서 계산식을 함수화(간략화)
select mid , sum( bamount * bprice ) as 총구매금액 from buy where 총구매금액 >= 1000
group by mid; -- 역시 where절에서 as 사용했기에 실행 불가
select count( * ) from buy; -- 이거 됨
select count( * ) from buy where count( * ) > 3; -- 이거 안 됨. where에 집계 넣지 마라
# [*] select 키워드 간의 작성 규칙
# select 속성명 from 테이블명 where 일반조건 group by 그룹속성명 having 그룹조건 order by 정렬속성명 asc/desc limit 개수;
# [*] 처리 순서
# select [3] from [1] where [2] group by [4] having [5] order by [6] limit [7]
SQL day08.sql 명령어 및 개념 정리
이 문서는 day08.sql 파일에 포함된 SQL 명령어와 데이터베이스 개념을 설명합니다.
1. 데이터베이스 관리
데이터베이스를 생성, 삭제, 사용하는 기본 명령어입니다.
DROP DATABASE IF EXISTS mydb0724;mydb0724라는 데이터베이스가 존재할 경우에만 삭제합니다.IF EXISTS는 데이터베이스가 없을 때 발생할 수 있는 오류를 방지합니다.
CREATE DATABASE mydb0724;mydb0724라는 이름의 새 데이터베이스를 생성합니다.
USE mydb0724;- 현재 세션에서
mydb0724데이터베이스를 기본 작업 데이터베이스로 지정합니다. 이 명령어를 실행한 후의 모든 쿼리는 이 데이터베이스에 대해 실행됩니다.
- 현재 세션에서
2. 테이블 생성 및 제약 조건
데이터를 저장할 테이블을 만들고, 데이터의 무결성을 보장하기 위한 제약 조건을 설정합니다.
기본 키 (Primary Key - PK)
- 개념: 테이블의 각 레코드(행)를 고유하게 식별할 수 있는 값입니다.
NULL값을 가질 수 없으며, 중복된 값을 허용하지 않습니다. (예: 회원번호, 제품번호, 학번) - 명령어:
table1이라는 테이블을 생성합니다.num_pk라는 정수(INT) 타입의 컬럼을 생성하고, 이 컬럼을 기본 키(PK)로 지정합니다.
CREATE TABLE table1 ( num_pk INT, CONSTRAINT PRIMARY KEY (num_pk) );
외래 키 (Foreign Key - FK)
- 개념: 한 테이블의 필드(컬럼)가 다른 테이블의 기본 키(PK)를 참조하는 키입니다. 테이블 간의 관계를 설정하며, 데이터의 참조 무결성을 보장합니다. 중복 값과
NULL값을 가질 수 있습니다. (예: 게시물의 작성자 회원번호, 주문 내역의 고객번호) - 명령어:
table2테이블을 생성합니다.num_fk컬럼을 외래 키(FK)로 지정하여table1테이블의num_pk컬럼을 참조하도록 합니다.
CREATE TABLE table2 ( no_pk INT, CONSTRAINT PRIMARY KEY (no_pk), num_fk INT, CONSTRAINT FOREIGN KEY (num_fk) REFERENCES table1(num_pk) ON DELETE CASCADE ON UPDATE CASCADE );
참조 무결성 옵션
외래 키 제약 조건에서 참조하는 PK 값이 변경되거나 삭제될 때의 동작을 정의합니다.
ON DELETE CASCADE: 참조하고 있는table1의 PK 레코드가 삭제되면, 해당 레코드를 참조하는table2의 FK 레코드가 함께 삭제됩니다.ON UPDATE CASCADE: 참조하고 있는table1의 PK 값이 변경되면, 해당 값을 참조하는table2의 FK 값이 함께 변경됩니다.ON DELETE SET NULL: PK 레코드가 삭제되면, FK 값은NULL로 변경됩니다.ON UPDATE SET NULL: PK 값이 변경되면, FK 값은NULL로 변경됩니다.ON DELETE RESTRICT(기본값):table2에서 참조하고 있는 PK 레코드는 삭제할 수 없습니다.ON UPDATE RESTRICT(기본값):table2에서 참조하고 있는 PK 레코드는 수정할 수 없습니다.
3. 데이터 조작 및 조회 (JOIN)
JOIN은 관계형 데이터베이스에서 가장 중요한 개념 중 하나로, 두 개 이상의 테이블을 PK와 FK 관계를 기반으로 연결하여 하나의 결과 집합으로 조회하는 기능입니다.
샘플 데이터 추가
INSERT INTO table1 VALUES (1), (2), (3), (4), (5);
INSERT INTO table2 VALUES (1, 1), (2, 2), (3, 1), (4, 1), (5, 2);(1) 교차 조인 (Cross Join)
- 두 테이블의 모든 가능한 레코드 조합을 반환합니다. (table1의 5개 * table2의 5개 = 25개 레코드)
- 보통 조건 없이 사용하지 않으며,
WHERE절과 함께 사용하여INNER JOIN처럼 사용될 수 있습니다. SELECT * FROM table1, table2;
(2) 내부 조인 (Inner Join) - 교집합
- 두 테이블 간에 공통된 값(PK와 FK가 일치하는)을 가진 레코드만 조회합니다.
WHERE절 사용:SELECT * FROM table1, table2 WHERE table1.num_pk = table2.num_fk;INNER JOIN ON절 사용 (권장):JOIN조건과WHERE의 일반 조건을 명확히 구분할 수 있어 가독성이 높습니다.SELECT * FROM table1 INNER JOIN table2 ON table1.num_pk = table2.num_fk;INNER는 생략 가능합니다. (JOIN ON)AS를 사용하여 테이블에 별칭(alias)을 부여할 수 있습니다.SELECT t1.num_pk, t2.num_fk FROM table1 AS t1 JOIN table2 AS t2 ON t1.num_pk = t2.num_fk;
NATURAL JOIN:- 두 테이블에서 이름이 같은 모든 컬럼을 기준으로 조인합니다. 의도치 않은 컬럼이 조인될 수 있어 사용을 권장하지 않습니다.
SELECT * FROM table1 NATURAL JOIN table2;
(3) 외부 조인 (Outer Join)
- 교집합(Inner Join) 결과와 함께, 한쪽 테이블에는 있지만 다른 쪽 테이블에는 없는 데이터도 포함하여 조회합니다.
LEFT OUTER JOIN:- 왼쪽 테이블(
table1)의 모든 레코드를 포함하고, 오른쪽 테이블(table2)에서는 조인 조건에 맞는 레코드만 포함합니다. 일치하는 레코드가 없으면NULL로 표시됩니다. SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.num_pk = t2.num_fk;
- 왼쪽 테이블(
RIGHT OUTER JOIN:- 오른쪽 테이블(
table2)의 모든 레코드를 포함하고, 왼쪽 테이블(table1)에서는 조인 조건에 맞는 레코드만 포함합니다. SELECT * FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON t1.num_pk = t2.num_fk;OUTER는 생략 가능합니다. (LEFT JOIN,RIGHT JOIN)
- 오른쪽 테이블(
(4) 전체 조인 (Full Outer Join) - 합집합
- 두 테이블의 모든 레코드를 조회합니다. (양쪽 테이블의
OUTER JOIN결과를 합친 것) - MySQL은
FULL OUTER JOIN을 직접 지원하지 않으므로,LEFT JOIN과RIGHT JOIN의 결과를UNION으로 합쳐서 구현합니다.
SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.num_pk = t2.num_fk
UNION
SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.num_pk = t2.num_fk;(5) 차집합
- 한쪽 테이블에만 존재하는 레코드를 조회합니다.
OUTER JOIN결과에서NULL인 부분을 필터링하여 구현합니다. - 왼쪽 테이블(
table1) 기준 차집합:table1에는 있지만table2에는 없는 데이터 SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.num_pk = t2.num_fk WHERE t2.num_fk IS NULL;- 오른쪽 테이블(
table2) 기준 차집합:table2에는 있지만table1에는 없는 데이터 SELECT t2.* FROM table1 t1 RIGHT JOIN table2 t2 ON t1.num_pk = t2.num_fk WHERE t1.num_pk IS NULL;
# 1. 데이터베이스 생성
drop database if exists mydb0724; -- mydb0724 DB가 존재하면 삭제한다
create database mydb0724; -- mydb0724 DB 생성
use mydb0724; -- 워크벤치 재실행 후에도 use 써야함
# 2-1. 테이블 생성
create table table1( -- 'table1' 테이블 생성
num_pk int , -- 'num_pk' 속성/컬럼/필드를 int 타입 선언
constraint primary key (num_pk) -- 선언된 'num_pk' 속성을 pk(식별키) 설정
); # primary key : 식별키 , 중복없음 , null 불가능 , 고유한 값 , 게시물번호/회원번호/학번/제품번호 등등
# 2-2. 테이블 생성
create table table2(
no_pk int ,
constraint primary key (no_pk),
num_fk int , -- 'num_fk' 속성을 int 타입 선언
-- 선언된 'num_fk' 속성을 fk(참조키) 설정 -- 'table1' 테이블의 'num_pk'를 참조
constraint foreign key (num_fk) references table1(num_pk)
on delete cascade -- table1의 'num_pk'값이 삭제되면 'num_fk'값도 같이 삭제
on update cascade -- table1의 'num_pk'값이 변경되면 'num_fk'값도 같이 변경
/* 참조 옵션 : constraint foreign key ( fk필드 ) references 테이블명 ( pk필드 ) 옵션
on [update / delete] [cascade / set null / restrict]
1. on delete cascade : 참조중인 pk 레코드가 삭제되면 fk 레코드도 같이 삭제한다.
2. on update cascade : 참조중인 pk 레코드의 pk값이 변경되면 fk 값도 같이 변경된다.
3. on delete set null : 참조중인 pk 레코드가 삭제되면 fk 값은 null로 변경한다.
4. on update set null : 참조중인 pk 레코드의 pk값이 변경되면 fk 값은 null로 변경된다.
5. on delete restrict : (기본값/생략가능) pk가 참조중인 pk레코드를 삭제 불가능 설정한다.
6. on update restrict : (기본값/생략가능) pk가 참조중인 pk레코드를 수정 불가능 설정한다.
* 단 restrict에서 pk값을 참조하는 fk값이 존재하지 않으면 가능
*/
); # foreign key : 참조키 , 중복가능 , null 가능 , 참조값 , 댓글이위치한게시물번호/게시물작성자/수강신청학생/주문한제품번호
# 실생활 : 우리집 집주소 pk , 쿠팡/G마켓/카카오쇼핑에서 우리집주소 fk 등록
# 집주소는 *하나*만 존재하고 각 쇼핑몰은 집주소를 참조한다.
# 왜 ? PK와 FK 참조(관계)형 테이블 = 관계형 데이터베이스 관리 시스템 RDBMS
# 3. 서로 다른 테이블 합치기 = join , # pk 레코드와 fk 레코드를 하나의 테이블로 조회
# 샘플 데이터
insert into table1 value(1) , (2) , (3) , (4) , (5);
insert into table2 value(1 , 1) , (2 , 2) , (3 , 1) , (4 , 1) , (5 , 2);
# 레코드 조회
select * from table1;
select * from table2;
# (1) 두 개 이상의 테이블 조회하기 : select * from 테이블명A , 테이블명B;
# 주의할점 : 두 테이블간의 데카르트 곱으로 표현된다. 테이블명A(5개) 테이블명B(5개) , 5*5 -> 25개의 레코드로 표시됨
select * from table1, table2;
# (2) 교집합 : 두 테이블간의 공통된 값(pk-fk) 기준으로 레코드 조회
# 2-1) where 절 , 일반적으로 join과 조건절 구분이 어렵기 때문에 실무 사용불가능.
select * from table1, table2 where table1.num_pk = table2.num_fk;
# 2-2) inner join on 절 , 테이블A inner join 테이블B on 테이블A.pk속성명 = 테이블B.FK속성명
select * from table1 inner join table2 on table1.num_pk = table2.num_fk;
# where 일반조건 , having 그룹조건 , on 집합조건
# 2-3) inner 생략 가능 , 테이블에 as 별칭 가능 , join 결과에서 원하는 속성명만 조회
select * from table1 join table2 on table1.num_pk = table2.num_fk;
select * from table1 as t1 inner join table2 t2 on t1.num_pk = t2.num_fk;
select t1.num_pk as 식별키 , t2.num_fk 참조키 from table1 t1 inner join table2 t2 on t1.num_pk = t2.num_fk;
# 아래 키워드 전제조건 : pk속성명과 fk속성명이 일치할때만 가능하다.
# 2-4) natural join : 자연 조인, 조인 의도와 다르게 동일한 속성명이 존재할 수 있으므로 비권장*
select * from table1 natural join table2;
# 2-5) join using : 현재 두 테이블간의 동일한 pk-fk 속성명이 아니므로 불가능
select * from table1 join table2 using(num_pk);
# (3) outer join
# 3-1) left outer join on : 왼쪽 테이블(table1)에 있는 모든 레코드와 오른쪽 테이블(table2)에 교집합되는 레코드 조회
# 왼쪽 테이블의 레코드 전체 조회 + 오른쪽 테이블과 교집합 레코드 값 표현되고 없으면 null
select * from table1 t1 left outer join table2 t2 on t1.num_pk = t2.num_fk;
# 3-2) right outer join on : 오른쪽 테이블(table2)에 있는 모든 레코드와 왼쪽 테이블(table2)에 교집합되는 레코드 조회
select * from table1 t1 right outer join table2 t2 on t1.num_pk = t2.num_fk;
# 3-3) outer 생략가능
select * from table1 t1 left join table2 t2 on t1.num_pk = t2.num_fk;
# (4) 합집합 , MySQL : union , oracle : full outer join
# 4-1) union : 2개 이상의 select를 하나로 합치기 , left outer + (union) + right outer
select * from table1 t1 left join table2 t2 on t1.num_pk = t2.num_fk union
select * from table1 t1 right join table2 t2 on t1.num_pk = t2.num_fk;
# (5) 차집합 : left outer join에서 교집합(공통pk-fk) 제외한
# 5-1) 왼쪽 테이블(table1)의 차집합
select t1.* from table1 t1 left join table2 t2 on t1.num_pk = t2.num_fk where t2.num_fk is null;
# 5-2) 오른쪽 테이블(table2)의 차집합
select t2.* from table1 t1 right join table2 t2 on t1.num_pk = t2.num_fk where t1.num_pk is null;
/*
교집합 : 두 테이블간의 pk와 fk값이 동일한 레코드를 하나의 테이블로 조회
1. where
2. inner join on
3. join on
4. natural join
5. join using
차집합 : 두 테이블간의 하나의 테이블A 레코드를 제외한 하나의 테이블B의 레코드 조회
A-B : select 테이블A.* from 테이블A left outer join 테이블B on 테이블A.pk = 테이블B.fk where 테이블B.FK is null
합집합 : 두 테이블간의 모든 레코드를 하나의 테이블로 조회
Oracle DB : full outer join
MySQL DB : left outer join *union* right outer join
*/반응형