DML (data manipulation language)

데이터 조작 언어

트랜잭션이 발생하는 SQL도 이 DML에 속함

커밋, 롤백 → 트랜잭션

DDL(data definition language)

create drop alter

롤백, 커밋 사용불가

실행즉시 적용됨

DCL (data contril language)

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

Insert

INSERT INTO 테이블이름 VALUES(값)

USE SQLDB;

CREATE TABLE TESTTBL1(id INT, userNAME CHAR(3), age INT);
INSERT INTO TESTTBL1 VALUES (1, '뽀로로', 16);
INSERT INTO TESTtesttbl1TBL1 (id, userNAME) VALUES (2, '크롱');
INSERT INTO TESTTBL1 VALUES (3, '루피', 14);

AUTO_INCREMENT

자동으로 1부터 증가하는 값을 입력하는 키워드

반드시 열을 기본키 또는 유니크로 설정해야함

USE SQLDB;

CREATE TABLE TESTTBL2(id INT AUTO_INCREMENT PRIMARY KEY, userNAME CHAR(3), age INT);
INSERT INTO TESTTBL2 VALUES (NULL, '에디', 15);
INSERT INTO TESTTBL2 VALUES (NULL, '포비', 12);
INSERT INTO TESTTBL2 VALUES (NULL, '퉁퉁이', 11);

ALTER TABLE TESTTBL2 AUTO_INCREMENT = 100;
INSERT INTO TESTTBL2 VALUES (NULL, '패티', 11);

>>>
1	에디	15
2	포비	12
3	퉁퉁이	11
100	패티	11

→ AUTO_INCREMENT PRIMARY KEY

SET @@auto_increment_increment=3

@@ 붙이면 서버 변수

USE SQLDB;

CREATE TABLE TESTTBL3(id INT AUTO_INCREMENT PRIMARY KEY, userNAME CHAR(3), age INT);
ALTER TABLE TESTTBL3 AUTO_INCREMENT = 100;
SET @@auto_increment_increment=3;
INSERT INTO TESTTBL3 VALUES (NULL, '에디', 15);
INSERT INTO TESTTBL3 VALUES (NULL, '포비', 12);
INSERT INTO TESTTBL3 VALUES (NULL, '퉁퉁이', 11);

>>>
100	에디	15
103	포비	12
106	퉁퉁이	11

대량 데이터 삽입 형식

INSERT INTO 테이블이름 SELECT문;

VER1.

CREATE TABLE TESTTBL4(id INT, Fname VARCHAR(50), Lnmae VARCHAR(50));
INSERT INTO TESTTBL4 SELECT emp_no,first_name, last_name FROM employees.employees;

VER2.

CREATE TABLE TESTTBL5( SELECT emp_no,first_name, last_name FROM employees.employees);
SELECT * FROM TESTTBL5 LIMIT 5;

UPDATE

테이블에 입력된 값을 수정하는 명령어

UPDATE 테이블 이름
SET 열1=값1, 열2=값2
WHERE 조건;

DELETE

DELETE FROM TESTTBL4 WHERE Fname='Aamer';
DELETE FROM TESTTBL4 WHERE Fname= 'Aamer' LIMIT 5;

CREATE TABLE BIGTBL1(SELECT * FROM employees.employees);
CREATE TABLE BIGTBL2(SELECT * FROM employees.employees);
CREATE TABLE BIGTBL3(SELECT * FROM employees.employees);

DELETE FROM BIGTBL1;                //데이터 삭제     //롤백하기 위한 자료를 만드느라 오래걸림
DROP TABLE BIGTBL2;                 //테이블 삭제
TRUNCATE TABLE BIGTBL3;             //자료만 삭제    //롤백을 못한다

[ 자세한 설명 ]

1) DELETE

하여 테이블에 있는 하는 방식데이터를 하나하나 선택하여 제거
WHERE절을 사용
WHERE절을 사용하지않고 테이블의 모든 데이터를 삭제하더라도, 내부적으로는 한줄 한줄 일일히 제거하는 과정을 거칩니다.
처리속도가 늦고, 퍼포먼스에 좋지않은 영향을 줄 수 있습니다.
원하는 데이터만 골라서 삭제할 때에는 DELETE 사용 / 전체 데이터 삭제할 때에는 TRUNCATE 사용합니다.
데이터를 삭제하더라도 데이터가 담겨있던 Storage는 Release 되지 않습니다.
DELETE된 데이터는 COMMIT 명령어를 사용하기 전이라면, ROLLBACK 명령어를 통해 되돌릴 수 있습니다.

2) TRUNCATE

하는 방식 ( <-> DELETE)
전체 데이터를 한번에 삭제
최초 생성되었을 당시의 Storage만 남기고, 데이터가 남겨있던 Storage는 Release 됩니다.
TRUNCATE TABLE을 하면 CREATE TABLE을 한 직후의 상태와 같습니다.
자동 COMMIT이 되는 명령어이기 때문에, 이미 지운 데이터는 되돌릴 수 없습니다.

3) DROP

버리는 방식입니다. (처음부터 없었던 테이블처럼)
테이블 자체를 완전히 날려
테이블 자체가 모두 지워지며, 해당 테이블에 생성되어있던 모든 인덱스도 사라집니다.
자동 COMMIT이 되는 명령어이기 때문에, 이미 지운 데이터는 되돌릴 수 없습니다.
오라클에서(오라클10g부터)는 테이블이 삭제되는 것이 아니라 윈도우의 휴지통 개념처럼 잠시 삭제 -> 테이블 이름이 BIN$..로 변경됩니다.

IGNORE

INSERT IGNORE INTO ~ ;

기본키가 중복되어도 에러 메세지만 띄우고 뒷줄로 넘어감

ON DUPLICATE KEY UPDATE구문

INSERT INTO memberTBL VALUES(’KHD’,’강후덜’,’미국’) 
    ON DUPLICATE KEY UPDATE userName = ‘강후덜’,addr=’미국’)

순위 함수

비집계함수중 RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER()

SELECT ROW_NUMBER() OVER(ORDER BY height DESC) 
     "키큰순위", userName, addr, height FROM userTBL;

SELECT ROW_NUMBER() OVER(ORDER BY height DESC, 두번째 조건) 
     "키큰순위", userName, addr, height FROM userTBL;

SELECT ROW_NUMBER() OVER(PARTITION BY addr ORDER BY height DESC) 
     "키큰순위", userName, addr, height FROM userTBL;

DENSE_RANK

값이 같을 경우 동일한 등수로 처리함

RANK

공동 3등 다음에 5등이 나오게 함

NTILE

NTILE(나눌 그룹 갯수)

RANK 랑 같이 쓸 수는 없음

LEAD()

바로 다음 사람 하고 키 차이를 나타낼 수 있다

select NAME, ADDR, height AS '키', height-(lead(height,1,0) 
OVER (ORDER BY height DESC )) AS "다음사람과 키 차이" FROM mydb.userTBL;

>>>
박은빈	서울	182	0
김용규	서울	182	0
이승규	서울	182	0
이슝기	서울	182	0
이승동	서울	182	0
이가은	서울	182	0
김지연	서울	182	0
박완호	서울	182	0
송은지	서울	182	0
양은진	서울	182	182

제일 마지막 값은 비교할 대상이 없어서 자기 자신이 나옴

FIRST_VALUE()

select NAME, ADDR, height AS '키', height-(FIRST_VALUE(height) 
OVER (PARTITION BY ADDR ORDER BY height DESC )) AS "다음사람과 키 차이" FROM mydb.userTBL;

→ PARTITION BY

CUME_DIST() 누적 백분율

SELECT ADDR, NAME, HEIGHT AS '키', (CUME_DIST() 
OVER (PARTITION BY ADDR ORDER BY height DESC )) * 100 
AS "누적인원 백분율%" FROM mydb.userTBL;

PIVOT

한열에 포함된 여러 값을 여러 열로 변환하여 출력하고 필요하면 집계까지 출력 가능

CREATE TABLE PIVOTTEST ( uName CHAR(3), season CHAR(2), amount INT);
INSERT INTO PIVOTTEST VALUES('유재석','겨울',10);
INSERT INTO PIVOTTEST VALUES('강호동','여름',10);
INSERT INTO PIVOTTEST VALUES('유재석','가을',10);
INSERT INTO PIVOTTEST VALUES('유재석','봄',10);
INSERT INTO PIVOTTEST VALUES('유재석','봄',10);
INSERT INTO PIVOTTEST VALUES('강호동','겨울',10);
INSERT INTO PIVOTTEST VALUES('유재석','여름',10);
INSERT INTO PIVOTTEST VALUES('유재석','겨울',10);
INSERT INTO PIVOTTEST VALUES('강호동','여름',10);

SELECT uName,
	SUM(CASE WHEN season='봄' THEN amount END) AS '봄',
	SUM(CASE WHEN season='여름' THEN amount END) AS '여름',
	SUM(CASE WHEN season='가을' THEN amount END) AS '가을',
	SUM(CASE WHEN season='겨울' THEN amount END) AS '겨울'
FROM PIVOTTEST
GROUP BY uName;

데이터 형식의 종류

숫자 데이터

출처 : https://velog.io/@98kimjh/Database-MySQL-Data-Types

문자 데이터

날짜와 시간 데이터

@ : 지역변수

USE SQLDB;
SET @myVar1 = 5;
SET @myVar2 = 3;
SET @myVar3 = 4.25;
SET @myVar4 = 'MC 이름 ==>';

 select @myVar1;
 select @myVar2 + @myVar3;
 
 select @myVar4 , userName From userTBL WHERE height > 180;

CAST

avg(amount) as signed integer

as date로도 가능

CONVERT

암시적인 형 변환

cast함수나 convert함수를 사용하지 않고 데이터 형식을 변환하는 것

select '100' + '200';
 select CONCAT('100' , '200');
 select CONCAT(100 , '200');
 SELECT 1 > '3mega';   #3으로 간주
 SELECT 4 > '3MEGA';   #3으로 간주  
 SELECT 0 = 'mega3';   #0으로 간주

내장함수

IF, IFNULL, NULLIF

SELECT IF(100>200, '참이다','거짓이다');
 SELECT NULLIF (NULL, '널이군요'), IFNULL(100,'널이군요');
 SELECT NULLIF (100,100), IFNULL(200,100);

CASE… WHEN … ELSE… END

SELECT CASE 10
	 WHEN 1 THEN '일'
	 WHEN 5 THEN '오'
	 WHEN 10 THEN '십'
 	ELSE '모름'
END;

ASCII, CHAR

GIT_LENGTH, CHAR_LENGTH, LENGTH

CONCAT, CONCAT_WS

ELT : 첫번째 인수인 ‘위치’에 적힌 숫자를 보고 그 숫자 번째에있는 문자열을 반환

FIELD

FIND_IN_SET

INSTR

LOCATE

LOWER, UPPER

INSERT

LEFT, RIGHT

BIN, HEX, OCT

LPAD, RPAD

LTRIM, RTRIM

TRIM

REPEAT

REPLACE

REVERSE

SPACE(길이) : 길이만큼의 공백을 반환

SUBSTRING

SUBSTRING_INDEX

수학 함수

ABS

CELING, FLOOR, ROUND

CONV (진수 변경)

DEGREE, RADIANS, PI

MOD

POW, SQRT

날짜/시간 함수

ADDDATE, SUBDATE

ADDTIME, SUBTIME

YEAR

DATE, TIME

DATEDIFF, TIMEDIFF

DAYOFWEEK, MONTHNAME, DAYOFYEAR

PERIOD_ADD, PERIOD_DIFF

QUARTER

TIME_TO_SEC

CURDATE, CURTIME, NOW, SYSDAYE

시스템 / 정보 함수

USER, DATABASE

SLEEP

VERSION

…

'데이터베이스' 카테고리의 다른 글

데이터 베이스 MySQL 3 (0)	2024.02.03
MySQL로 배우는 데이터 베이스 개론과 실습 1장 연습문제 (0)	2024.02.02
데이터 베이스 MySQL 1 (0)	2024.02.02

Eunjineee's 기술블로그

데이터 베이스 MySQL 2

DML (data manipulation language)

DDL(data definition language)

DCL (data contril language)

Insert

AUTO_INCREMENT

SET @@auto_increment_increment=3

대량 데이터 삽입 형식

UPDATE

DELETE

IGNORE

ON DUPLICATE KEY UPDATE구문

순위 함수

DENSE_RANK

RANK

NTILE

LEAD()

FIRST_VALUE()

CUME_DIST() 누적 백분율

PIVOT

데이터 형식의 종류

숫자 데이터

문자 데이터

날짜와 시간 데이터

CAST

CONVERT

암시적인 형 변환

내장함수

IF, IFNULL, NULLIF

CASE… WHEN … ELSE… END

수학 함수

날짜/시간 함수

시스템 / 정보 함수

'데이터베이스' 카테고리의 다른 글

티스토리툴바

데이터 베이스 MySQL 2

DML (data manipulation language)

DDL(data definition language)

DCL (data contril language)

Insert

AUTO_INCREMENT

SET @@auto_increment_increment=3

대량 데이터 삽입 형식

UPDATE

DELETE

IGNORE

ON DUPLICATE KEY UPDATE구문

순위 함수

DENSE_RANK

RANK

NTILE

LEAD()

FIRST_VALUE()

CUME_DIST() 누적 백분율

PIVOT

데이터 형식의 종류

숫자 데이터

문자 데이터

날짜와 시간 데이터

CAST

CONVERT

암시적인 형 변환

내장함수

IF, IFNULL, NULLIF

CASE… WHEN … ELSE… END

수학 함수

날짜/시간 함수

시스템 / 정보 함수

'데이터베이스' 카테고리의 다른 글

관련글

티스토리툴바