Index
SQL 실습 (CRUD, SELECT)
SQL 접속
.\mysql -u root -p
DB 목록 확인 및 사정
show databases; #DB 목록 조회
create database test_db; #db 생성
use test_db; #db 사용설정
테이블 생성
create table board(seq int, name char(50), content char(100));
테이블 필드 정보 조회
desc board
테이블에 레코드 추가
insert into board(seq, name, content) values (1, 'yejun', 'yejun content');
테이블 조회
select * from board;
조건문을 통한 테이블 조회
select * from board where content='yejun content';
데이터 수정
update board set name='yejun_change' where seq=1;
레코드 삭제
delete from board where seq=4;
산술 연산자
select * from board;
select 1*2;
select 1*2*3;
select * from board where s자
비교 연산자
논리 연산자
💡
우선순위 NOT > AND > OR
비트 논리 연산자
💡
&(AND), |(OR), ^(XOR)
select 1&2
select 1|2
select 1^2
연결 연산자 - concat
select 'ab' 'cd';
select * from board where name='ad' 'min';
select concat('ad', 'min');
연결 연산자 - in연산자
select * from board where name in ('yejun_change', 'admin');
select * from board where name not in ('yejun_change', 'admin');
연산자 - LIKE 연산자
select * from board where name like '%yejun%';
select * from board where name like 'y_jun%';
함수 - substring, substr, mid (ORABLE: SUBSTR, SUBSTRB)
select substring('yejun',1,1);
select substring('yejun',1,3);
select substring((select name from board where seq=1), 1, 1);
select substring((select name from board where seq=1), 2, 1);
select substring((select name from board where seq=1), 3, 1);
함수 - ascii
select ascii('a');
select ascii(substr('abcd', 1, 1));
select ascii(substr('abcd', 2, 1));
이중 select 구문 (서브 쿼리)
select name from board where seq=1 #yejun_change
select ascii(substr((select name from board where seq=1), 1, 1))=121; #true (121=='y')
select ascii(substr((select name from board where seq=1), 1, 1))=122; #false (122=='z')
함수 - concat
select concat(char(97), char(98));
함수 - count/length
select count(*) from board;
select length('abcd');
select name, length(name) from board;
함수 - case when, if
select case when 1=1 then 'ab' else 'cd' end; #true
select case when 1=2 then 'ab' else 'cd' end; #false
select * from board where seq=(case when 1=2 then 1 else 1 end);
select * from board where seq=(case when 1=2 then 1 else 2 end);
select * from board where seq=(if(1=1, 1, 2));
select * from board where seq=(if(1=2, 1, 2));
select * from board where seq=(if(1&1, 1, 2));
select * from board where seq=(if(1&2, 1, 2));
이중 select 구문 - 2 (서브 쿼리)
select * from board where seq=(select 1);
select * from board where seq=(select seq from board where name='admin');
정렬 구문
select * from board order by seq asc; #오름차순
select * from board order by seq desc; #내림차순
필드순서를 기준으로로 정렬
select * from board order by 1;
게시판 검색 기능에 union sql injection
필드 개수를 맞춰줘야 함.
select * from board where name='admin' union select '1',version(), '3';
정렬 방식 정하고 데이터 추출
나오는 데이터 순서를 보고, 쿼리문이 들어가는 구나 인지 할 수 있음
select * from board where name like '%' order by if(1=1, seq, name);
select * from board where name like '%' order by if(1=2, seq, name);
입력된 길이만큼 레코드 출력
select * from board limit 1;
select * from board limit 3;
select * from board limit 0,2;
select * from board limit 1,2;
select * from board where content like '%e%' and 1=1;
select * from board where content like '%e%' and 1=2;
버전 확인
select version() limit 0,1;
응용 - 버전 확인 방법
select * from board where content like '%e%' and ascii(substr((select version() limit 0,1),1,1))=53;
select * from board where content like '%e%' and ascii(substr((select version() limit 0,1),1,1))=49; # 49='10'
Uploaded by N2T