[DB_MySQL] 이것이 MySQL이다 ch06 SQL 기본 문법3 (DML, DDL, DCL, insert, auto_increment)
bobo122023. 2. 6. 18:55
728x90
반응형
1. SQL의 분류
▷ DML (Data Manipulation Language, 데이터 조작 언어)
▶ 데이터를 조작(선택, 삽입, 수정, 삭제)하는데 사용되는 언어
▶ DML 구문이 사용되는 대상은 테이블의 행
▶ DML 사용하기 위해서는 테이블이 정의되어 있어야 합니다.
▶ SQL문 중 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당
▶ 트랜잭션(Transaction)이 발생하는 SQL도 DML에 속함
→ 테이블의 데이터를 변경(입력/수정/삭제)할 때 실제 테이블에 완전히 적용하지 않고, 임시로 적용시키는 것
→ 취소 가능(ROLL BACK)
▷ DDL(Data Definition Language, 데이터 정의 언어)
▶ 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할
▶ CREATE, DROP, ALTER 자주 사용
▶ DDL은 트랜잭션 발생시키지 않음
▶ 되돌림(ROLLBACK)이나 완전적용(COMMIT) 사용 불가
▶ 실행 즉시 MySQL에 적용
▷ DCL(Data Control Language, 데이터 제어 언어)
▶ 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문
▶ GRANT/REVOKE/DENY 구문
2. 데이터 변경을 위한 SQL문
◎ 데이터 삽입 : INSERT
▷ INSERT문의 기본
▶ 테이블 이름 다음에 나오는 열 생략 가능
▶ 생략할 경우 VALUES 다음에 나오는 값들의 순서 및 개수가 테이블이 정의된 열 순서 및 개수와 동일해야 합니다.
use sqldb;
create table testTbl1 (id int, userName char(3), age int);
insert into testTbl1 values(1, '홍길동', 25);
select * from testTBL1;
-- 원하는 컬럼에만 데이터를 삽입
insert into testTbl1(id, userName) values(2,'설현');
select * from testTBL1;
-- 입력되는 데이터의 순서를 지정
insert into testTbl1(userName, age, id) values ('하니', 26, 3);
select * from testTBL1;
▷ 자동으로 증가하는 AUTO_INCREMENT
▶ INSERT에서는 해당 열이 없다고 생각하고 입력
→ INSERT문에서 NULL 값 지정하면 자동으로 값 입력
▶ 1부터 증가하는 값 자동 입력
▶ 적용할 열이 PRIMARY KEY 또는 UNIQUE일때만 사용가능
▶ 데이터 형은 숫자 형식만 사용 가능
1. AUTO_INCREMENT 사용 예제
-- 자동 값 입력 AUTO_INCREMENT (숫자형만 가능) : 기본 증가값 1, primary key 또는 unique에 적용
-- primary key : 중복값은 입력 불가능, null 값 입력 x
-- unique : 중복 값은 입력 불가능, null 값 입력 가능
use sqldb;
create table testtbl2(
id int auto_increment primary key,
userName char(3),
age int
);
select * from testtbl2;
insert into testtbl2 values(null, '지민', 25);
insert into testtbl2 values(null, '유나', 22);
insert into testtbl2 values(null, '유경', 21);
-- 현재 어느 값까지 입력 되었는지 확인
select last_insert_id();
-- auto_increment 시작값 변경 auto_increment = 100
alter table testtbl2 auto_increment = 100;
insert into testtbl2 values(null, '찬미', 23);
select * from testetbl2;
2. AUTO_INCREMENT 사용 예제 2
-- auto_increment 증가값 변경 set @@auto_increment_increment = 3;
create table testtbl3(
id int auto_increment primary key,
userName char(3),
age int
);
-- 시작 값을 1000으로 변경
alter table testtbl3 auto_increment = 1000;
-- 증가 값을 3으로 변경
set @@auto_increment_increment = 3;
insert into testtbl3 values(null, '나연', 20);
insert into testtbl3 values(null, '정연', 18);
insert into testtbl3 values(null, '모모', 19);
select * from testtbl3;
-- 한꺼번에 데이터를 입력
insert into testtbl3 values(null, '나연', 20), (null, '정연', 18), (null, '모모', 19);
select * from testtbl3;
한 행을 삭제하고 싶다면 아래와 같은 코드를 사용하면 됩니다.
delete from testtbl3 where id = 1000;
1000번 행이 삭제된 결과가 나타납니다.
delete from testtbl3 where id >= 1009;
▷대량의 샘플 데이터 생성
▶ INSERT INTO ...SELECT 구문 사용
▶ 다른 테이블의 데이터를 가져와 대량으로 입력하는 효과
▶ SELECT문의 열 개수 = INSERT할 테이블의 열 개수
▶ 테이블 정의까지 생략하려면 CREATE TABLE ... SELECT 구문을 사용
-- 대량의 데이터 입력 (샘플 데이터 생성) insert into ...... select문'
-- insert into (서브쿼리)
use sqldb;
create table testtbl4(id int, Fname varchar(50), Lname varchar(50));
desc testtbl4;
insert into testtbl4
select emp_no, first_name, last_name from employees.employees;
select * from testtbl4;
create table testtbl5
select emp_no, first_name, last_name from employees.employees;
select * from testttesttbl5bl5;
employees 데이터베이스의 employees 테이블의 값들을 지정한 값들만 모두 복사해옵니다.
◎ 데이터의 수정 : UPDATE
▷ 기존에 입력되어 있는 값 변경하는 구문
▷ WHERE절 생략 가능하나 WHERE절 생략하면 테이블의 전체 행의 내용 변경됨
▶ 실무에서 실수가 종종 일어나므로 주의 필요
▶ 원상태로 복구하기 복잡하며, 다시 되돌릴 수 없는 경우도 있음
1. update를 이용한 예제1
set SQL_SAFE_UPDATES = 0;
-- 입력된 데이터 수정 : update
-- Fname = 'Kyoichi'의 Lname 값을 모두 '없음'으로 변경
update testtbl4
set Lname = '없음'
where Fname = 'Kyoichi';
select * from testtbl4 where Fname = 'Kyoichi';
2. update를 이용한 예제2
-- buytbl에 물품의 가격을 50% 인상
update buytbl
set price = price * 1.5;
select * from buytbl;
◎데이터의 삭제 : DELETE FROM
▷ 행 단위로 데이터 삭제하는 구문
▷ WHERE절 생략되면 전체 데이터를 삭제함
-- 입력된 데이터 삭제 : delete from
-- 228개의 Fname 'Aamer' 삭제
delete from testtbl4
where Fname = 'Aamer';
-- 상위 5개를 잘라냄
delete from testtbl4
where Fname = 'Aamer' limit 5;
select * from testtbl4 where Fname = 'Aamer';
전체 테이블의 내용 삭제
delete from testtbl4;
select * from testtbl4;
▷ 테이블을 삭제하는 경우의 속도 비교
▶ DML문인 DELETE는 트랜잭션 로그 기록 작업 때문에 삭제 느림
▶ DDL문인 DROP과 TRUNCATE문은 트랜잭션 없어 빠름
→ 테이블 자체가 필요 없을 경우에는 DROP으로 삭제
→ 테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는 것이 효율적
-- 대량의 데이터 삭제
create table bigtbl1 (select * from employees.employees);
create table bigtbl2 (select * from employees.employees);
create table bigtbl3 (select * from employees.employees);
delete from bigtbl1; -- 테이블 구조는 남기고 데이터만 삭제 2.562 -- 삭제에 대한 로그를 기록
drop table bigtbl2; -- 테이블 삭제 0.016
truncate table bigtbl3; -- 테이블 구조는 남기고 데이터만 삭제 0.047 -- 로그를 기록하지 x 삭제
◎ 조건부 데이터 입력, 변경
▷ 기본 키가 중복된 데이터를 입력한 경우
▶ 오류로 입력 불가
1. 새로운 테이블 만들어서 값 복사해오기
-- 조건부 데이터 입력, 변경
create table membertbl (select userID, name, addr from usertbl limit 3);
select * from membertbl;
desc membertbl;
usertbl의 위에서부터 3개를 불러옵니다.
2. 기본키 지정
-- 제약 조건(constraint)을 따로 기입 : 테이블 수정 (alter table)
alter table membertbl
add conStraint pk_memberTBL primary key(userID); -- userID를 PK로 지정
select * from membertbl;
pk_memberTBL이라고 기본키의 변수 명을 지정해줍니다.
3. 추가 데이터 입력 (BBK가 중복이 되어서 오류로 데이터 입력 안됨)
-- 추가 데이터 입력 : PK : BBK 가 중복이 되어서 오류로 데이터 입력 X
insert into membertbl values('BBK', '비비코', '미국');
insert into membertbl values('SJH', '서장훈', '서울');
insert into membertbl values('HJY', '현주엽', '경기');
4. 오류가 발생해도 나머지 부분 모두 실행(ignore 사용)
-- 오류가 발생해도 나머지 부분 다 실행
insert ignore into membertbl values('BBK', '비비코', '미국');
insert ignore into membertbl values('SJH', '서장훈', '서울');
insert ignore into membertbl values('HJY', '현주엽', '경기');
5. 기본키가 중복되는 경우에 데이터가 수정되도록 입력합니다. ▷ 중복되는 값이 없어도 값이 추가됩니다.
-- 기본키가 중복되는 경우에 데이터가 수정되도록 입력
insert into membertbl values ('BBK', '비비코', '미국')
on duplicate key update name = '비비코', addr='미국';
insert into membertbl values ('DJM', '동짜몽', '일본')
on duplicate key update name = '동짜몽', addr='일본';
◎ transaction, rollback 사용 예제
-- 트랜잭션 사용법 : 실행 commit, 취소 rollback
start transaction; -- 트랜잭션 사용
delete from membertbl;
select * from membertbl;
rollback; -- 트랜잭션 사용 이전 되돌리기
select * from membertbl;
트랜잭션을 사용하고 rollback을 사용해야 복구가 됩니다.
▷ 대용량 데이터 처리의 경우 에러 발생하지 않은 구문 실행
▶ INSERT IGNORE문
→ 에러 발생해도 다음 구문으로 넘어가게 처리
→ 에러 메시지보면 적용되지 않은 구문이 어느 것인지 구분 가능
▶ ON DUPLICATE KEY UPDATE 구문
→ 기본 키가 중복되면 데이터를 수정되도록 하는 구문도 활용 가능
3. WITH절과 CTE
◎ WITH절과 CTE 개요
▷ WITH절은 CTE(Common Table Expression)를 표현하기 위한 구문
▷ MySQL 8.0 이후부터 사용 가능하게 됨
▷ CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등을 대신할 수 있으며 간결한 식으로 보여집니다.
▷ CTE는 ANSI-SQL99 표준(기존 SQL은 ANSI-SQL92 기준)
▷ CTE는 비재귀적 CTE와 재귀적 CTE가 있지만 주로 사용되는 것은 비재귀적 CTE
◎ 비재귀적 CTE
▷ 단순한 형태, 복잡한 쿼리문장을 단순화하는데 적합
▷ CTE는 뷰와 용도가 비슷하지만 개선된 점이 많음
▷ 뷰는 계속 존재해서 다른 구문에서도 사용 가능하지만, CTE와 파생 테이블은 구문이 끝나면 소멸됨
▷ 중복 CTE 허용됨
-- 비재귀적 CTE : 복잡한 쿼리를 단순화
/*
WITH CTE_테이블명(열 이름)
AS
(
쿼리문
)
select 열 이름 FROM CTE_테이블명
*/
-- 사용자별 총 구매액 구하기
select userid as '사용자', sum(price*amount) as '총구매액'
from buytbl group by userid;
-- 총 구매액이 가장 많은 순으로 정렬
select userid as '사용자', sum(price*amount) as '총구매액'
from buytbl group by userid
order by sum(price*amount) desc;
-- 단순하게 보이게 만들어주는 것 CTE
select * from abc order by 총구매액 desc;
with abc(userid, total)
as
(
select userid as '사용자', sum(price*amount) as '총구매액'
from buytbl group by userid
)
select * from abc order by total desc;
-- 회원테이블(userTBL)에서 각 지역별로 가장 큰 키를 1명씩 뽑은 후, 그 사람들 키의 평균을 내보자
-- 각 지역별로 가장 큰 키 출력
select addr, max(height) from usertbl group by addr;
-- with
with cte_userTBL(addr, maxheight)
as
(
select addr, max(height) from usertbl group by addr
)
select avg(maxheight) from cte_usertbl;
DML, DDL, DCL, insert, auto_increment 등을 이용한 구문들을 사용해보았습니다!
