[SQL] MySQL 문법 공부 (데이터 조회, 추가, 삭제, 조건 / 명령어 종류)

✍️ Study Note

[SQL 명령어 종류]

DML : Data Manipulation Laguage

데이터 조작(선택, 삽입, 수정,삭제)

SELECT, INSERT, UPDATE, DELETE

DDL : Data Definition Language

데이터베이스 개체(테이블, 개체, 뷰, 인덱스) 생성, 삭제, 변경

CREATIE, DROP, ALTER

DCL : Data Control Language

접근권한 제어

GRANT, REVOKE, DENY

[SQL 명령어 순서]

작성 순서

SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY

실행 순서

FROM - WHERE - SELECT - GROUP BY - HAVING - ORDER BY

[SQL 명령어]

데이터베이스, 테이블, 열 조회 (Show, Describe, Select)

show databases; -- 전체 데이터베이스 조회
use employees; -- 특정 데이터베이스 선택
SHOW TABLE STATUS; -- 전체 테이블 조회 (2번 데이터베이스)
describe employees; -- 특정 테이블 조회
select first_name, last_name from employees; -- 특정 열 선택

데이터베이스, 테이블 생성 (Create) + 데이터 삽입 (Insert)

CREATE DATABASE SQLDB; -- 데이터베이스 생성
USE SQLDB;

-- 테이블 생성
CREATE TABLE usertbl 
(userID char(8) not null primary key,
 name varchar(10) not null,
 birthyear int not null,
 addr char(2) not null,
 mobile1 char(3), 
 mobile2 char(8),
 height smallint,
 mdate date);
 
 create table buytbl
 (num int auto_increment not null primary key,
  userID char(8) not null,
  prodName char(6) not null,
  groupName char(4),
  price int not null,
  amount smallint not null,
  foreign key (userID) references usertbl(userID)
  );
  

-- 값 입력
INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011','111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011','222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019','333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011','444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL,NULL, 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016','666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL,NULL, 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011','888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018','999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010','000000', 176, '2013-5-5');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL , 30, 2);
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자' , 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자' , 200, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자' , 1000, 5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류' , 50, 3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자' , 80, 10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책', '서적' , 15, 5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책', '서적' , 15, 2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류' , 50, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL , 30, 2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책', '서적' , 15, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL , 30, 2);

- AUTO_INCREMENT

자동으로 1부터 증가하는 값.
INSERT 할 경우, 이 열의 값은 추가 불가
PRIMARY KEY, UNIQUE로 지정 필요
데이터 타입 = 숫자
AUTO_INCREMENT = '숫자' -> 해당 숫자부터 입력되기 시작.
SET @@AUTO_INCREMENT_INCREMENT = '숫자' -> 해당 숫자만큼 증가

Ref. (5)번 예시

USE SQLDB;

-- 테이블 생성
CREATE TABLE TESTTBL2
(ID INT AUTO_INCREMENT PRIMARY KEY, USERNAME CHAR(3), AGE INT); 

-- 값 추가
INSERT INTO TESTTBL2 VALUES (NULL, '지민', 25);
INSERT INTO TESTTBL2 VALUES (NULL, '유나', 22);
INSERT INTO TESTTBL2 VALUES (NULL, '유경', 21);

-- 마지막 ID 번호 확인
SELECT LAST_INSERT_ID(); 

-- ID 100 세팅
ALTER TABLE TESTTBL2 AUTO_INCREMENT = 100; 

 -- ID 100으로 데이터 INPUT
INSERT INTO TESTTBL2 VALUES (NULL, '찬미', 23);

-- 값 조회
SELECT * FROM TESTTBL2

Ref. (6)번 예시

-- 테이블 생성 & ID 1000 시작 세팅
CREATE TABLE TESTTBL3
(ID INT AUTO_INCREMENT PRIMARY KEY, USERNAME CHAR(3), AGE INT);
ALTER TABLE TESTTBL3 AUTO_INCREMENT = 1000;

-- ID 배수 3 세팅 & 값 추가
SET @@auto_increment_increment=3;
INSERT INTO TESTTBL3 VALUES (NULL, '나연', 20), (NULL, '정연', 18), (NULL, '모모', 19);

-- 값 조회
SELECT * FROM TESTTBL3;

조건절 (Where, Between, In, Like)

-- WHERE
SELECT * FROM USERTBL
WHERE NAME = '김경호';


-- WHERE + 연산자
SELECT USERID, NAME FROM USERTBL
WHERE BIRTHYEAR >= 1970 AND HEIGHT >= 182;


-- BETWEEN + AND
SELECT NAME, HEIGHT FROM USERTBL
WHERE HEIGHT BETWEEN 180 AND 183;


-- OR
SELECT NAME, HEIGHT FROM USERTBL
WHERE ADDR ='경남' OR ADDR = '전남' OR ADDR = '경북';


-- IN
SELECT NAME, HEIGHT FROM USERTBL
WHERE ADDR IN ('경남', '전남', '경북');


-- LIKE + '%'
SELECT NAME, HEIGHT FROM USERTBL
WHERE NAME LIKE '김%'; --  글자수 제한 없을 경우 '%'


-- LIKE + '_'
SELECT NAME, HEIGHT FROM USERTBL
WHERE NAME LIKE '_종신'; -- 한글자 지정일 경우 '_'

서브쿼리, Any, All

SELECT NAME, HEIGHT FROM USERTBL
WHERE HEIGHT > (SELECT HEIGHT FROM USERTBL WHERE NAME = '김경호');

-- [>= ANY] 170 OR 173 둘 중 하나와 크거나 동일한 사람 검색
SELECT NAME, HEIGHT FROM USERTBL
WHERE HEIGHT >= ANY(SELECT HEIGHT FROM USERTBL WHERE ADDR = '경남');

-- [>= ALL] 170 AND 173 둘 모두와 크거나 동일한 사람 검색 (더 조건이 까다로운 173 기준으로 필터됨)
SELECT NAME, HEIGHT FROM USERTBL
WHERE HEIGHT >= ALL(SELECT HEIGHT FROM USERTBL WHERE ADDR = '경남');

-- [= ANY] 170 OR 173 둘 중 하나와 동일한 사람 검색
SELECT NAME, HEIGHT FROM USERTBL
WHERE HEIGHT = ANY(SELECT HEIGHT FROM USERTBL WHERE ADDR = '경남');

-- [IN] 170 OR 173 둘 중 하나와 동일한 사람 검색
SELECT NAME, HEIGHT FROM USERTBL
WHERE HEIGHT IN (SELECT HEIGHT FROM USERTBL WHERE ADDR = '경남');

- ANY, SOME, IN : 서브쿼리의 여러개 값 중 한 가지만 만족하면 됨

- ALL : 서브쿼리의 여러개 값 모두 만족해야 함

정렬, 중복제거, 상위 n (Order by, Distinct, Limit)

--정렬
SELECT NAME, MDATE FROM USERTBL ORDER BY MDATE;
SELECT NAME, MDATE FROM USERTBL ORDER BY MDATE DESC;

-- 중복제거
SELECT DISTINCT ADDR FROM USERTBL ORDER BY ADDR;

-- 상위 N 출력
USE EMPLOYEES;
SELECT EMP_NO, HIRE_DATE FROM EMPLOYEES
ORDER BY HIRE_DATE ASC
LIMIT 0, 5;   -- 0번째줄부터 시작해서 5줄 출력

테이블 복사 (Create)

CREATE TABLE BUYTBL2 (SELECT*FROM BUYTBL); -- 전체복사 
CREATE TABLE BUYTBL3 (SELECT USERID, PRODNAME FROM BUYTBL); --일부 열 복사

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

- PK, FK 등 제약조건은 복사 안됨

데이터 그룹화 (Group by) + 집계함수(Sum, Avg, etc)

-- GROUP BY & SUM
SELECT USERID AS '사용자 아이디', SUM(AMOUNT) AS '총 구매 개수' 
FROM BUYTBL 
GROUP BY USERID;

SELECT USERID AS '사용자 아이디', SUM(PRICE*AMOUNT) AS '총 구매 액수' 
FROM BUYTBL 
GROUP BY USERID;

- SUM / AVG / MIN / MAX / COUNT / COUNT(DISTINCT) / STDEV : 표준편차 / VAR_SAMP : 분산

특정 조건 조회 (Where, Count, Group by, Having)

-- 서브쿼리 : 최장신, 최단신 구하기
SELECT NAME, HEIGHT FROM USERTBL
WHERE HEIGHT = (SELECT MAX(HEIGHT) FROM USERTBL)
OR HEIGHT = (SELECT MIN(HEIGHT) FROM USERTBL);

-- COUNT : 유저 총 인원
SELECT COUNT(*) FROM USERTBL; 

-- COUNT : 핸드폰 있는 유저 인원
SELECT COUNT(MOBILE1) FROM USERTBL;

-- HAVING : 총 구매액 1000 이상
SELECT USERID AS '구매자', SUM(PRICE*AMOUNT) '총구매액'
FROM BUYTBL 
GROUP BY USERID
HAVING SUM(PRICE*AMOUNT) > 1000;

- COUNT : NULL 값 제외하고 카운트함.

- HAVING : ALIACE 사용 불가

중간 합계 & 총 합계 구하기 (Rollup)

SELECT NUM, GROUPNAME, SUM(PRICE*AMOUNT) AS '비용'
FROM BUYTBL
GROUP BY GROUPNAME, NUM
WITH ROLLUP;

- WITH ROLL UP A, B : A별 중간 합계 + B 총 합계

데이터 추가 & 변경 (Insert, Insert ignore, on duplicate key update)

CREATE TABLE TESTTBL1 (ID INT, USERNAME CHAR(3), AGE INT);

-- INSERT 예시
INSERT INTO TESTTBL1 VALUES (1,'홍길동', 25);
INSERT INTO TESTTBL1(ID, USERNAME) VALUES (2,'설현'); -- AGE는 NULL
INSERT INTO TESTTBL1(USERNAME, AGE, ID) VALUES ('하니', 26, 3); 

-- INSERT IGNORE 예시
INSERT IGNORE INTO MEMBERTBL VALUES('BBK', '비비코', '미국');
INSERT IGNORE INTO MEMBERTBL VALUES('SJH', '서장훈', '서울');
INSERT IGNORE INTO MEMBERTBL VALUES('HJY', '현주엽', '경기');

-- ON DUPLICATE KEY UPDATE 예시
INSERT INTO MEMBERTBL VALUES ('BBK', '비비코', '미국')
 ON DUPLICATE KEY UPDATE NAME = '비비코', ADDR ='미국';
INSERT INTO MEMBERTBL VALUES ('DJM', '동짜몽', '일본')
 ON DUPLICATE KEY UPDATE NAME = '동짜몽', ADDR ='일본';

- INSERT INTO 테이블 (열1, 열2) VALUES (값1, 값2)

- 테이블의 열은 생략 가능. 이 경우 테이블이 정의된 열 순서에 맞춰 값을 넣어야 함.

- INSERT IGNORE : 에러는 무시하고 진행됨.

- ON DUPLICATE KEY UPDATE

INSERT VALUE의 값 중복일 때 (PK 중복), 기존 값이 ON DUPLICATE KEY UPDATE 뒷편 값으로 업데이트 (UPDATE)
INSERT VALUE의 값 미중복일 때 (PK 중복X), VALUES의 값이 새로 입력됨. (INSERT)

대량 데이터 업로드

-- 테이블 생성 시, 대량 업로드
CREATE TABLE TESTTBL5 (SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEES.EMPLOYEES);
SELECT * FROM TESTTBL5;

-- 테이블 생성 후, 대량 업로드
CREATE TABLE TESTTBL4 (ID INT, FNAME VARCHAR(50), LNAME VARCHAR(50));
INSERT INTO TESTTBL4
SELECT EMP_NO, FIRST_NAME, LAST_NAME FROM EMPLOYEES.EMPLOYEES;
SELECT * FROM TESTTBL4;

- 테이블 생성 시 : CREATE TABLE 새로운 테이블 (SELECT 가져올 열 FROM 기존 테이블)

- 테이블 생성 후 : INSERT INTO 새로운 테이블 SELECT 가져올 열 FROM 기존 테이블

데이터 수정 & 삭제

UPDATE TESTTBL4 SET LNAME = '없음' WHERE FNAME = 'Kyoichi';
DELETE FROM TESTTBL4 WHERE FNAME = 'AAMER';

--테이블 삭제
DELETE FROM BIGTBL1;
DROP TABLE BIGTBL2;
TRUNCATE TABLE BIGTBL3;

- 데이터 수정 : UPDATE 테이블 SET 열 = 바꿀 값

- 데이터 삭제 : DELETE FROM 테이블 WHERE 삭제할 조건

- 데이터 삭제 명령어

DELETE : 테이블 구조 O / 트랜잭션 로그 O / DML
DROP : 테이블 구조 X / 트랜잭션 로그 X / DDL
-RUNCATE : 테이블 구조 O / 트랜잭션 로그 X / DDL

CTE WITH 절

-- 구매자, 총 구매액 내림차순
WITH ABC(USERID, TOTAL)
 AS
 (SELECT USERID, SUM(PRICE*AMOUNT) 
  FROM BUYTBL GROUP BY USERID)
SELECT * FROM ABC ORDER BY TOTAL DESC;
  
  
-- 각 지역 최고키 평균값
WITH CTE_USERTBL(ADDR, MAXHEIGHT)
 AS
 (SELECT ADDR, MAX(HEIGHT) 
  FROM USERTBL GROUP BY ADDR)
SELECT AVG(MAXHEIGHT*1.0) AS '각 지역별 최고키의 평균' FROM CTE_USERTBL;

- WITH CTE_테이블 (열 이름) AS (SELECT 문) SELECT * FROM CTE_테이블

- 'CTE_테이블'은 임시 테이블이며, 해당 구문이 끝나면 없어짐.

저작자표시 비영리 변경금지

'코딩공부' 카테고리의 다른 글

[SQL] MySQL Workbench 설정 (명령어 대문자, 주석, 쿼리 폰트 설정) (0)	2021.10.11
[SQL] 데이터 타입과 변환, 변수 선언 (0)	2021.10.11
[SQL] MySQL Workbench 사용자 생성 방법 (0)	2021.10.10
[Python] VS code (Visual studio) Python Run 단축키 세팅 (0)	2021.10.02
[Python] 윈도우 가상환경 및 패키지 설치방법 (0)	2021.10.01

Lim's daily note

[SQL] MySQL 문법 공부 (데이터 조회, 추가, 삭제, 조건 / 명령어 종류)

[SQL 명령어 종류]

DML : Data Manipulation Laguage

DDL : Data Definition Language

DCL : Data Control Language

[SQL 명령어 순서]

작성 순서

실행 순서

[SQL 명령어]

데이터베이스, 테이블, 열 조회 (Show, Describe, Select)

데이터베이스, 테이블 생성 (Create) + 데이터 삽입 (Insert)

조건절 (Where, Between, In, Like)

서브쿼리, Any, All

정렬, 중복제거, 상위 n (Order by, Distinct, Limit)

테이블 복사 (Create)

데이터 그룹화 (Group by) + 집계함수(Sum, Avg, etc)

특정 조건 조회 (Where, Count, Group by, Having)

중간 합계 & 총 합계 구하기 (Rollup)

데이터 추가 & 변경 (Insert, Insert ignore, on duplicate key update)

대량 데이터 업로드

데이터 수정 & 삭제

CTE WITH 절

'코딩공부' 카테고리의 다른 글

티스토리툴바

[SQL] MySQL 문법 공부 (데이터 조회, 추가, 삭제, 조건 / 명령어 종류)

[SQL 명령어 종류]

DML : Data Manipulation Laguage

DDL : Data Definition Language

DCL : Data Control Language

[SQL 명령어 순서]

작성 순서

실행 순서

[SQL 명령어]

데이터베이스, 테이블, 열 조회 (Show, Describe, Select)

데이터베이스, 테이블 생성 (Create) + 데이터 삽입 (Insert)

조건절 (Where, Between, In, Like)

서브쿼리, Any, All

정렬, 중복제거, 상위 n (Order by, Distinct, Limit)

테이블 복사 (Create)

데이터 그룹화 (Group by) + 집계함수(Sum, Avg, etc)

특정 조건 조회 (Where, Count, Group by, Having)

중간 합계 & 총 합계 구하기 (Rollup)

데이터 추가 & 변경 (Insert, Insert ignore, on duplicate key update)

대량 데이터 업로드

데이터 수정 & 삭제

CTE WITH 절

'코딩공부' 카테고리의 다른 글

'코딩공부' Related Articles

티스토리툴바