<CMD mysql 접근 방법>
> mysql -u root -p
Enter password : 패스워드 입력
<CREATE, DROP 구문>
1) CREATE 구문 - 데이터베이스 생성
CREATE DATABASE [DB_NAME];
ex) CREATE DATABASE test_db;
2) CREATE 구문 - 테이블 생성
CREATE TABLE [NAME] ([COLUMN_NAME] [DATA_TYPE]);
ex) CREATE TABLE test_tb (name char(20), age int);
3) DROP 구문 - 데이터베이스/테이블 삭제
DROP [DATABASE/TABLE] [NAME];
ex) DROP TABLE test_tb;
<실습 DB 구문>
> show databases;
: database 목록을 출력
> create database example;
: example 데이터베이스 생성
>select database();
: 현재 database 출력
> use example;
: example DB를 사용하기 위해서 선택
> create table member(seq int, name char(20), email char(50));
: member 테이블 생성
> show tables;
: 현재 데이터베이스 테이블 출력
> describe member; (or desc member;)
: member 테이블에 대한 구조 보기
<INSERT, SELECT, UPDATE, DELETE 구문>
1) INSERT구문 - 데이터 삽입
INSERT INTO [TABLE_NAME] ([COLUMN1]...) VALUES ([DATA1]...);
ex) INSERT INTO test_tb (name, age) VALUES ('blingson', 55);
2) SELECT구문 - 데이터 조회
SELECT [COLUMN1] FROM [TABLE] WHERE [CONDITION];
SELECT name, age FROM test_tb;
3) UPDATE구문 - 데이터 수정
UPDATE [TABLE] SET [COLUMN1]=[DATA1] WHERE [CONDITION];
UPDATE test_tb SET age=35 WHERE name='blingson';
4) DELETE구문 - 데이터 삭제
DELETE FROM [TABLE] WHERE [CONDITION];
DELETE FROM test_tb WHERE name='blingson';
<실습 DB 구문>
> show databases;
: database 목록을 출력
> use example;
: example DB를 사용하기 위해서 선택
> show tables;
: 현재 데이터베이스 테이블 출력
> describe member; (or desc member;)
: member 테이블에 대한 구조 보기
> insert into member(seq, name, email) values(1, 'administrator', 'administrator@test.co.kr');
: member 테이블 내 하나의 데이터(레코드) 삽입
> insert into member(seq, name, email) values(2, 'test1', 'test1@test.co.kr');
> insert into member(seq, name, email) values(3, 'guest', 'guest@test.co.kr');
> insert into member(seq, name, email) values(5, 'bling', 'bling@test.co.kr');
> select * from member;
: member 테이블 모든 컬럼 출력
> select name, email from member;
: name과 email 값만 보기
> select name, email from member where seq=1;
: where 절을 이용하여 seq 값이 1인것을 출력
> select name, email from member where seq=5;
> insert into member values(6, 'test', 'test@test.co.kr');
: 데이터 삽입
> update member set name='test1' where seq=6;
: 데이터 수정
> delete from member where seq=6;
: 데이터 삭제
<연산자>
1. SQL 연산자
- 산술 연산자
- 비교 연산자(관계 연산자)
- 논리 연산자
- 비트 논리 연산자
- 연결 연산자
- IN 연산자
- LIKE 연산자
2. 연산자와 피연산자의 관계
| 5 | + | 3 |
| 피연산자(1) | 연산자 | 피연산자(2) |
| 연산에 참여하는 변수나 값 | 연산을 수행하는 기호 혹은 키워드 | 연산에 참여하는 변수나 값 |
3. 산술 연산자
| 연산자 | 의미 | 우선순위 | 표현식 | 예시 |
| + | 더하기 | 2 | 피연산자1+피연산자2 | 1+2 |
| - | 빼기 | 2 | 피연산자1-피연산자2 | 2-1 |
| * | 곱하기 | 1 | 피연산자1*피연산자2 | 5*3 |
| / | 나누기 | 1 | 피연산자1/피연산자2 | 8/2 |
<실습>
> select 1+4;
: 1+4 연산을 하여 결과 값을 출력
> select 1+4*2;
: 1+4*2 연산을 하여 결과 값을 출력
> select * from member where seq=4-1;
: seq=3 의 레코드가 출력
4. 비교 연산자
| 연산자 | 의미 | 표현식 |
| > | 크다 | 피연산자1>피연산자2 |
| >= | 크거나 같다 | 피연산자1>=피연산자2 |
| < | 작다 | 피연산자1<피연산자2 |
| <= | 작거나 같다 | 피연산자1<=피연산자2 |
| = | 같다 | 피연산자1=피연산자2 |
| <>, != | 같지 않다 | 피연산자1<>피연산자2 피연산자1!=피연산자2 |
※ 기준은 연산자의 왼쪽 값을 기준으로 한다.
<실습>
> select * from member where seq=1;
: seq값이 1과 같다
> select * from member where seq>2;
: seq값이 2보다 큰것(초과)
> select * from member where seq>=2;
: seq값이 2 이상
> select * from member where seq<3;
: seq값이 3보다 적은것을 출력
> select * from member where seq<>3;
> select * from member where seq!=3;
: seq값이 3번 레코드는 제외
5. 논리 연산자
| 연산자 | 의미 | 우선순위 | 표현식 |
| NOT | 부정 | 1 | NOT 피연산자 |
| AND | 그리고(논리곱) | 2 | 피연산자1 AND 피연산자2 |
| OR | 또는(논리합) | 3 | 피연산자1 OR 피연산자2 |
<실습>
> select * from member where seq=1 and seq=5;
: 둘다 참이 될수 없기 때문에 거짓
> select * from member where seq=1 or seq=5;
: 하나라도 참이 되기 때문에 참(seq=1이 참이면 seq=5가 거짓이고 seq=5가 참이면 seq=1이 거짓)
> select * from member where seq=5 and name='legugu';
: 두개다 참이 되기 때문에 참
> update member set name='administrator' where seq=1;
: seq=1의 이름 오타를 'administrator'로 수정
6. 비트 논리 연산자
| DBMS | AND | OR | XOR |
| ORACLE | bitand(피연산자1, 피연산자2) | - | - |
| MSSQL | 피연산자1 & 피연산자2 | 피연산자1 | 피연산자2 | 피연산자1 ^ 피연산자2 |
| MYSQL | 피연산자1 & 피연산자2 | 피연산자1 | 피연산자2 | 피연산자1 ^ 피연산자2 |
<실습>
> select 1&1;
: 1&1 은 1을 출력
> select 2&1;
: 2&1 은 0을 출력
: 2는 2bit로 출력하면 10, 1은 2bit로 출력하면 01(10, 01 둘다 계산하면 참이기 때문에 00을 반환해서 0이다.
> select 3&1;
: 3은 2bit로 출력하면 11, 1은 2bit로 출력하면 01(11, 01을 and 비트하면 01이기 나오기 때문에 결과가 1이다)
> select 5&3;
: 3은 2bit로 출력하면 101, 3을 2bit로 출력하면 011(101, 011을 and 비트하면 1의 결과값을 출력)
> select 5|2;
: 5는 2bit로 출력시 111, 2는 2bit로 출력 시 010(111, 010 or 비트 연산 시 4,2,1 합치면 7의 결과값 출력)
7. 연결 연산자
| DBMS | 연산자 | 표현식 | 예시 |
| ORACLE | || | 피연산자1||피연산자2 | 'te'||'st' |
| MSSQL | + | 피연산자1+피연산자2 | 'te'||'st' |
| MYSQL | 공백 | 피연산자1 공백 피연산자2 | 'te'공백'st' |
※ 공통적으로 CONCAT(문자열1, 문자열2...) 함수 사용이 가능하다.
<실습>
> select 'te' 'st'
: 'te' 'st' 문자열이 하나로 합쳐져서 반환되는 것을 확인할 수 있다.(test)
> select * from member where name='gue' 'st';
: name이 guest로 되어 있는 레코드 출력
> select concat('bl', 'ing', 'son');
: 3개의 문자들으 하나로 합쳐서 출력(blingson)
8. IN 연산자
1) IN 연산자 사용
[컬럼/값] IN (값1, 값2...)
SELECT name, email FROM member WHERE id in('admin','guest')
2) NOT IN 연산자 사용
[컬럼/값] NOT IN (값1, 값2...)
SELECT name, email FROM member WHERE id not in('admin','guest')
<실습>
> select * from member where name in ('administrator', 'guest');
> select * from member where name='administrator' or name='guest';
: member 테이블에서 name이 administrator와 guest만 출력
> select * from member where name not in ('administrator', 'guest');
: member 테이블에서 name이 administrator와 guest만 제외하고 출력
9. LIKE 연산자
| 와일드카드 | 의미 | 예시 |
| % | 모든 문자 | id like 'ad%' |
| _ | 하나의 문자 | id like 'admi_' |
<실습>
> select * from member where name like '%admin%';
: name에 admin이라는 문자가 들어가는 것을 찾아달라
> select * from member where name like 'gu_st';
: name에 gu와 st 사이에 문자를 모르니 찾아달라
> select * from member where email like '%@test.co.kr';
: 특정 이메일만 출력(@test.co.kr 도메인을 사용하는 사용자 모두 출력)
<함수>
1. 문자열 함수
| DBMS | 함수 | 사용법 |
| ORACLE | SUBSTR SUBSTRB |
함수(문자열, 시작위치, 길이) |
| MSSQL | SUBSTRING | |
| MYSQL | SUBSTRING SUBSTR MID |
<실습>
> select substring('test',1,1);
: 첫번째 문자(t) 출력
> select substring('test',2,1);
: 두번째 문자(e) 출력
> select substring('test',3,1);
: 세번째 문자(s) 출력
> select substring('test',4,1);
: 네번째 문자(t) 출력
> select substring('test',3,2);
> select substr('test',3,2);
> select mid('test',3,2);
: 세번째 문자에서 부터 2글자 출력(st)
2. 문자, 아스키 코드 변환 함수
| DBMS | CHAR → ASCII | ASCII → CHAR |
| ORACLE | ASCII | CHR |
| MSSQL | ASCII | CHAR |
| MYSQL | ASCII, ORD | CHAR |
<실습>
> select ascii('a');
: a에 대한 10진수 ASCII 코드 번호 출력(97)
> select bin(ascii('a'));
: a에 대한 2진수 ASCII 코드 번호 출력(1100001)
> select ascii(substring('test',1,1));
: test의 첫번째 글자의 ascii 번호(116)
> select ascii(substring('test',2,1));
> select ascii(substring('test',3,1));
> select ascii(substring('test',4,1));
> select char(97);
: ASCII 코드를 문자로 출력(a)
> select concat(char(97), char(97));
: ASCII 코드 문자 두개를 concat 함수를 이용하여 합치다(aa)
> select concat(char(97),char(100),char(109),char(105),char(110));
: admin 출력 '(싱글쿼터)를 사용하지 못하는 곳에 문자열 출력 방법
3. COUNT 함수
SELECT COUNT(COLUMN) FROM [TABLE]
<실습>
> select count(*) from member;
: member 테이블에 있는 레코드 갯수 출력
4. 길이 함수
| DBMS | 함수 |
| ORACLE | LENGTH |
| MSSQL | LEN |
| MYSQL | LENGTH |
<실습>
> select length('test');
: 출력하는 문자열의 길이 출력(4)
> select name, length(email) from member;
: member 테이블에 대한 name과 email의 길이를 출력
<조건문>
| DBMS | 구문 |
| ORACLE | DECODE 함수, CASE WHEN 구문 |
| MSSQL | CASE WHEN 구문 |
| MYSQL | IF 함수, CASE WHEN 구문 |
※ [CONDITION] : 조건문
<실습>
> select case when 1=1 then 1 else 2 end;
: 조건에 대한 결과가 출력(참이니깐 1 출력)
> select case when 1=1 then 'test1' else 'test2' end
> select case when 1=2 then 'test1' else 'test2' end
: 문자열도 출력 가능
> select * from member where seq=(case when 1=1 then 1 else 2 end);
> select * from member where seq=(case when 1=2 then 1 else 2 end);
: member 테이블에서 seq 값이 참이면 seq=1이 출력 거짓이면 seq=2 출력
<서브쿼리>
1. 서브쿼리(SubQuery) 개념
SELECT COLUMN1, COLUMN2 FROM TABLE WHERE[COLUMN]=(SELECT COLUMN FROM TABLE WHERE COLUMN=[VALUE])
메인쿼리 : SELECT COLUMN1, COLUMN2 FROM TABLE
서브쿼리 : (SELECT COLUMN FROM TABLE WHERE COLUMN=[VALUE])
※ 서브쿼리는 또 하나의 SELECT 구문
2. SELECT, FROM, WHERE 절에 따른 서브쿼리 명칭
SELECT (SubQuery) FROM (SubQuery) a WHERE [COLUMN]=(SubQuery)
스칼라 서브쿼리 인라인 뷰 일반 서브쿼리
스칼라 서브쿼리 : 하나의 레코드에 하나의 컬럼만 반환할 수 있음, 즉 하나의 데이터만 반환 가능
인라인 뷰 : 하나 이상의 테이블들의 기반으로 만들어진 테이블로써 물리적인 데이터를 저장하고 있지 않아서 가상테이블이라고 한다.(a라는 가상의 테이블)
일반 서브쿼리 : 연산자에 따라서 반환되는 레코드 수와 컬럼 수가 다름.(하나의 레코드)
3. 서브쿼리 종류
1) 단일 행 서브쿼리 : 하나의 레코드만 반환
SELECT name, email FROM member WHERE id=(SELECT id FROM bbs where idx=192)
(SELECT id FROM bbs where idx=192) : 단일 행 반환
2) 다중 행 서브쿼리 : 다수의 레코드 반환
SELECT name, email FROM member WHERE id in(SELECT id FROM bbs)
(SELECT id FROM bbs) : 다중 행 반환
<실습>
> select name, (select version()) from member;
: 스칼라 서브쿼리
> select name, (select 1,2) from member;
> select name, (select 1 union select 2) from member;
: 스칼라 서브쿼리에 두개의 레코드를 반환하여 에러 발생
> select name, (select email from member where seq=a.seq) from member a;
: 새로은 테이블 a를 이용하여 이름과 이메일 컬럼을 출력
> select * from (select * from member)a;
: 인라인 뷰(서브쿼리 옆에 별칭을 꼭 적어줘야 한다.)
: member 테이블의 정보를 반환받아서 a라는 별칭을 준다.
> select * from (select * from member)a where a.seq=1;
: a라는 가상의 테이블(뷰)에서 seq=1 출력
> select * from member where seq=(select 1);
: 일반 서브쿼리(seq=1에 대한 결과를 반환)
> select * from member where seq=(select seq from member where name='guest');
: from에서 member 값을 받아서 반환이 되고 where 절에서 특정 seq 값이 셋팅되어 있고 서브쿼리에서 member 값을 반환 받아서 name가 guest인것을 반환하여 guest에 대한 레코드 출력한다.(단일행 서브쿼리)
> select * from member where seq in(select seq from member);
: 다중 행 서브쿼리
<ORDER BY 절을 이용한 정렬>
1. ORDER BY절 사용법
SELECT COLUMN1, COLUMN2 FROM TABLE ORDER BY COLUMN [ASC/DESC]
ORDER BY절 : ORDER BY COLUMN [ASC/DESC]
ASC : 오름차순 : ASCENDING
DESC : 내림차순 : DESCENDING
※ ASC/DESC를 입력하지 않을시 기본적으로 ASC로 인식한다.
<실습>
> select * from member order by seq asc;
: seq 컬럼을 오름차순으로 정렬하여 출력
> select * from member order by seq desc;
: seq 컬럼을 내림차순으로 정렬하여 출력
> select * from member order by seq;
: seq 컬럼을 오름차순으로 정렬하여 출력
> insert into member values(2, 'admin', 'admin@test.co.kr');
: 데이터 추가 삽입
> select * from member;
: member 테이블 확인시 지금은 정렬이 안된 상태로 출력
> select * from member order by seq, name;
: seq값을 정렬하고 그리고 name도 정렬하여 다수의 컬럼을 정렬하는 방식
> select * from member order by 1;
: 1은 첫번째 컬럼 기준으로 정렬한다. (2: 두번째 컬럼, 3. 세번째 컬럼)
> select seq, name from member order by 3;
: select 절에서 3번째 컬럼이 없어서 에러 발생
<레코드 출력 개수 제한>
| DBMS | 키워드 |
| ORACLE | ROWNUM |
| MSSQL | TOP |
| MYSQL | LIMIT |
1. MYSQL의 LIMIT절
1) 인자를 2개 사용하는 방법
SELECT COLUMN1, COLUMN2 FROM TABLE LIMIT [OFFSET], [ROW_COUNT]
[OFFSET] : 위치값(0부터 시작)
[ROW_COUNT] : 레코드 갯수
2) 하나의 인자를 사용하는 방법
SELECT COLUMN1, COLUMN2 FROM TABLE LIMIT [ROW_COUNT]
[ROW_COUNT] : 레코드 갯수
<실습>
> select * from member limit 3;
: 3개의 레코드만 순차적으로 출력
> select * from member limit 1,4;
: 두번째 레코드 부터 다섯번째 레코드까지 순차적으로 출력
※ 정리 URL :
성공적인 SQL 인젝션 공격을 위한, SQL 기본 문법 - 인프런
웹 해킹의 꽃이라 불리는 SQL 인젝션 공격을 익히기 위한 기초 단계! SQL 기본 문법에 대한 강의입니다! 초급 프로그래밍 언어 데이터베이스 보안 SQL 모의해킹 MySQL 데이터베이스 온라인 강의 SQL
www.inflearn.com