SELECT 에 쓰이는 간단문법

- SHOW DATABASES; 서버 데이터베이스 리스트 조회

- SHOW TABLE STATUS; 테이블 정보 조회

- SHOW TABLES; 테이블이름만 조회

- MYSQL 5.7은 CHAR와 VARCHAR가 모두 UTF-8코드를 사용

UTF-8 코드는 영문/숫자/기호를 입력하면 내부적으로 1바이트 할당, 한글/중국어/일본어 등을 입력하면 내부적으로 3바이트 할당

-데이터베이스(=스키마)개체이름은 식별자(dentifier)라고 한다.

개체란, 데이터베이스, 테이블, 인덱스, 열, 뷰, 트리거, 스토이드 프로시저

 

- 관계연산자 : AND, OR, NOT등

- 조건연산자 : =, <, >, <=, >=, <>, != emd

- SELECT 열 FROM 테이블이름 WHERE 컬럼명 BETWEEN 조건1 AND 조건2

- SELECT 열 FROM 테이블이름 WHERE 필드이름 IN ('데이터1', '데이터2')

- SELECT 열 FROM 테이블이름 WHERE 조건 LIKE '~%'

% : 무엇이든 환영

_ :한칸매치

 

---

ANY / ALL / SOME 그리고 서브쿼리

SELECT Name, height FROM userTBL WHERE height > 177;
 
SELECT height FROM userTBL WHERE height > (SELECT height FROM userTBL Where name = '홍길동');

177을 직접 하드코딩하지 않고 동적으로 처리하고자 할때 괄호안에 서브쿼리를 만들어준다.

홍길동의 키에 따라 값이 유동적으로 변경해서 좋지만 단점도 있다.

단점 1. 홍길동이름이 복수로 존재할때, 2. 홍길동이름이 존재하지않을때

복수로 존재한다면 에러코드 1242 를 출력될것이다.

 

만약 데이터가 아래와같이 있을때

홍길동	180cm	90년생
홍길동	145cm	07년생

 

1. ANY

SELECT height FROM userTBL WHERE height >= ANY (SELECT height FROM userTBL Where name = '홍길동');

=> 홍길도 키 145이상 또는 키 180이상이므로 결국 값이 가장 작은 키 145이상으로 검색한것과 같다.

SELECT height FROM userTBL WHERE height = ANY (SELECT height FROM userTBL Where name = '홍길동');

=> 홍길도 키 145와 같거나 키 180과 같은사람

=> IN 이랑 같음

 

2. ALL

SELECT height FROM userTBL WHERE height => ALL (SELECT height FROM userTBL Where name = '홍길동');

=> 키 145와 키 180 모두 만족해야하므로 키 180과 같음

 

3. SOME은 ANY와 동일

---

ORDER BY : 정렬

SELECT 컬럼명 FROM 테이블명 WHERE 조건 ORDER BY 정렬조건 컬럼명 (ASC, DESC)
-- ASC는 Default

---

DISTINCT : 중복된 것은 하나만 남기기

SELECT DISTINCT 조건컬렴명 FROM 테이블명

---

LIMIT : 제한

SELECT 컬렴명 FROM 테이블명 LIMIT 숫자

---

CREATE TABLE ... SELECT : 테이블을 복사

CREATE TABLE 새로운테이블 (SELECT 복사할열 FROM 기존에티블)

---

GROUP BY

SELECT 컬럼명
[FROM table_references]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}]

일자	구매자	구매수량
2020-01-01	홍길동	30
2020-01-01	이순신	5
2020-01-01	홍길동	12
2020-01-02	홍길동	10
2020-01-03	이순신	13

 

SELECT NAME, SUM(AMOUNT) AS '총구매갯수' FROM BUYTABLE GROUP BY NAME;

A> 먼저 그룹화 시킨다.

NAME	총구매갯수
이순신	5
이순신	13
홍길동	30
홍길동	12
홍길동	10

B> 이것을 SUM 집계함수를 이용해서 아래와같이 결과를 나타낸다.

NAME	총구매갯수
이순신	18
홍길동	52

 

집계함수

함수명	설명
AVG()	평균을 구한다
MIN()	최솟값을 구한다
MAX()	최댓값을 구한다
COUNT()	행의 갯수를 센다
COUNT(DISTINCT)	행의 갯수를 센다 (중복은 1개만 인정)
STDEV()	표준편차를 구한다
VAR_SAMP()	분산을 구한다

 

---

HAVING

WHERE절에는 집계함수가 들어갈수없다. 들어가면 에러코드 1111 가 나타난다.

특정 조건을 만족하는것을 만들기위해서는 아래와같이 HAVING 절에 조건을 넣으면된다.

예제에서는 집계함수를 없지만 사용할수있다.

SELECT NAME, SUM(AMOUNT) AS '총구매갯수' 
FROM BUYTABLE 
GROUP BY NAME
HAVING AMOUNT > 10;

NAME	총구매갯수
이순신	13
홍길동	42

---

ROLLUP

총합 또는 중간합계가 필요하다면 GROUP BY 절과 함께 WITH ROLLUP문을 사용하면 된다.

SELECT NAME, SUM(AMOUNT) AS '총구매갯수' 
FROM BUYTABLE 
GROUP BY NAME
WITH ROLLUP;

NAME	SUM(AMOUNT)
이순신	5
이순신	13
	18
홍길동	30
홍길동	12
홍길동	10
	52
	82

---

SQL의 분류

1. DML(Data Manipulation Language : 데이터 조작 언어)

데이터를 조작(선택, 삽입, 수정, 삭제)하는 데 사용되는 언어다.

DML 구문이 사용되는 대상은 테이블의 행이다.

 

SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당된다.

또 트랜잭션(Transaction)이 발생하는 SQL도 이 DML이다.

 

2. DDL(Data Definition Language : 데이터 정의 언어)

데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 객체를 생성/삭제/변경하는 역할을 한다.

자주 사용하는 DDL은 CREATE, DROP, ALTER 등이다.

DDL은 트랜잭션을 발생시키지 않는다. 그러므로 ROLLBACK 이나 COMMIT을 시킬수 없다.

실행 즉시 MySQL에 적용된다.

 

3. DCL(Data Control Language : 데이터 제어 언어)

사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문이다.

GRANT/REVOKE/DENY 등이 이에 해당된다.

 

INSERT [INTO] 테이블[(열1, 열2, ...)] VALUES (값1, 값2 ...)
 
-- 자동으로 증가하는 AUTO_INCREMENT
CREATE TABLE testTb1
(id int AUTO_INCREMENT PRIMARY KEY,
NAME char(3)
AMOUNT int);
 
INSERT INTO testTb1 VALUES (NULL, '이순신' 10);
INSERT INTO testTb1 VALUES (NULL, '이순신' 15);
INSERT INTO testTb1 VALUES (NULL, '홍길동' 20);
 
-- ALTER TABLE testTb1 AUTO_INCREMENT = 100
-- 중간에 증가값 변경
 
-- SET @@auto_increment_increment=3;
-- 증가값 3으로 변경 (기본값 1)
 
-- 대량의 샘플 데이터 생성
CREATE TABLE testTb2 (id int, NAME varchar(50), amount int);
INSERT INTO testTb2
(SELECT id, name, amount FROM testTb1);
 
-- 또는 CREATE TABLE testTb2 
INSERT INTO testTb2
(SELECT id, name, amount FROM testTb1);
 
UPDATE 테이블이름
SET 열1=값1, 열2=값2
WHERE 조건;
-- 주의 : WHERE절을 생략이 가능하지만 생략한다면 테이브르이 전체의 행이 변경
-- SET price = price * 1.5 하면 현재의 단가에서 1.5배 전체적으로 일괄적용
 
DELETE FROM 테이블이름 WHERE 조건;
 
/*
DELETE FROM 테이블명1;
DROP TABLE 테이블명2;
TRUNCATE TABLE 테이블명3;
*/

DML문인 DELETE는 트랜잭션 로그를 기록하는 작업 때문에 삭제하는데 오래걸린다.

수백만 건 또는 수천 만건의 데이터를 삭제할 경우에 한참동안 삭제를 할 수도 있다.

DDL문인 DROP문은 테이블 자체를 삭제한다.

그리고 DDL은 트랜잭션을 발생시키지 않는다고 했다.

역시 DDL문인 TRUNCATE문의 효과는 DELETE와 동일하지만 트랜잭션 로그를 기록하지 않아서 속도가 무척 빠르다.

그러므로 대용량의 테이블 전체 내용을 삭제할 때 테이블 자체가 필요없을 경우에는 DROP으로 삭제하고, 테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는것이 효율적이다.

 

속도 : DROP>TRUNCATE>>>DELETE