[SQL] SQL 기본 문법, SQL 고급 문법

Chapter 03 SQL 기본 문법

3. 데이터 입력: INSERT

4. 데이터 수정: UPDATE

5. 데이터 삭제: DELETE

 

3. 데이터 입력: INSERT

create table hongong1 (
toy_id int,
toy_name char(4),
age int
)default charset=utf8;
insert into hongong1 values(1, '우디', 25);
insert into hongong1 (toy_id, toy_name) values (2, '버즈');
insert into hongong1 (toy_name, age, toy_id) values ('제시', 20,3);
select * from hongong1;

INSERT INTO 테이블명 [칼럼명] VALUES 넣을값

칼럼명 생략시 모든 칼럼에 값을 넣게 됨→칼럼 개수와 순서를 맞춰서 값 입력

칼럼 지정시 일부 칼럼에만 넣거나(빠진 칼럼에는 NULL 입력) 칼럼 순서를 바꿔서 입력 가능

 

3.1 AUTO_INCREMENT

1부터 1씩 증가하는 값 자동 입력

 PRIMARY KEY로 지정, 값 입력시 NULL 입력

중간에 값이 삭제되는 경우 채워지지 않음(1~10까지 있을 때 5가 삭제된 경우, 5는 비워지고 다음 값을 입력하면 11이 됨)

주로 구매 테이블 같은 경우에 사용한다.

 

CREATE table hongong2(
	toy_id int auto_increment primary key,
    toy_name char(4),
    age int
)default charset=utf8;
insert into hongong2 values(null, '보핍', 25);
insert into hongong2 values(null, '슬링키', 22);
insert into hongong2 values(null, '렉스', 21);

 

 

3.1.1 last_insert_id()

AUTO_INCREMENT로 마지막으로 입력된 값 반환

 

3.1.2 AUTO_INCREMENT의 시작값 변경

alter table hongong2 auto_increment=100;
insert into hongong2 values(null, '재남', 35);

ALTER TABLE로 실행한다. 실행한 이후의 ROW부터 적용된다.

 

 

 

3.2 INSERT INTO~SELECT

다른 테이블의 행을 SELECT로 가져와서 테이블에 입력

INSERT INTO 테이블명 (칼럼명1, 칼럼명2, ...)
    SELECT문;

가져오는 칼럼의 개수, 순서, 데이터타입과 입력받을 칼럼의 개수, 순서, 데이터타입이 일치해야 함

create table city_popul (city_name char(35), population int);
insert into city_popul
	select Name, Population from world.city;
select * from city_popul;

 

 

4. 데이터 수정: UPDATE

UPDATE 테이블명
    SET 컬럼=값1, 컬럼=값2, ...
    WHERE 조건;

-- city_name 칼럼의 값이 'Seoul'인 행의 city_name을 '서울'로 변경
update city_popul
	set city_name = '서울'
    where city_name = 'Seoul';

-- city_name 칼럼의 값이 'New York'인 행의 city_name을 '뉴욕'으로, population을 0으로 변경
update city_popul
	set city_name = '뉴욕', population = 0
    where city_name = 'New York';

 

값을 연산할 수 있다.

-- population 칼럼의 값을 10000으로 나눠 저장
update city_popul
	set population = population/10000;

 

 

5. 데이터 삭제: DELETE

-- where절로 행을 지정하지 않으면 전체 행 삭제
delete from city_popul
	where city_name like 'New%';
    
-- limit로 삭제할 개수 지정 가능
delete from city_popul
	where city_name like 'New%'
    limit 5;

 

 

+테이블 삭제

create table big_table1 (select * from world.city, sakila.country);
create table big_table2 (select * from world.city, sakila.country);
create table big_table3 (select * from world.city, sakila.country);

delete from big_table1;
drop table big_table2;
truncate table big_table3;

DELETE: 오래 걸림, 테이블 남김

DROP: 빠름, 테이블을 남기지 않음

TRUNCATE: 빠름, 테이블 남김

 

 

 

 

 

Chapter04 SQL 고급 문법

4.1 MySQL의 데이터 형식

4.2 두 테이블을 묶는 조인

 

4.1 MySQL의 데이터 형식

1. 데이터 형식

1.1 정수형

데이터 형식	바이트 수	숫자 범위
TINYINT	1	-128~127
SMALLINT	2	-32,768 ~ 32,767
INT	4	약 -21억 ~ +21억
BIGINT	8	약 -900경 ~ +900경

 

out of range: 데이터 형식의 범위를 벗어난 값을 입력할 경우 발생하는 오류

UNSIGNED: 값의 범위가 0부터 시작. TINYINT의 경우 0~255

예시: height TINYINT UNSIGNED,

 

1.2 문자형

CHAR: 글자 개수 고정

VARCHAR: 10으로 지정하고 3만큼만 입력하는 경우 3만큼의 메모리만 사용

 

대량 데이터 형식

데이터 형식		바이트 수
TEXT 형식	TEXT	1~65535
	LONGTEXT	1~4294967295
BLOB 형식	BLOB	1~65535
	LONGBLOB	1~ 4294967295

사진, 동영상: BLOB/LONGBLOB

 

1.3 실수형

데이터 형식	바이트 수	설명
FLOAT	4	소수점 아래 7자리까지
DOUBLE	8	소수점 아래 15자리까지

 

1.4 날짜형

데이터 형식	바이트 수	설명
DATE	3	날짜만 저장. YYYY-MM-DD
TIME	3	시간만 저장. HH:MM:SS
DATETIME	8	날짜 및 시간 저장. YYYY-MM-DD HH:MM:SS

 

 

2. 변수의 사용

SET @변수명 = 변수값; -- 변수 선언 및 초기화
SELECT @변수명; -- 변수 값 출력

set @txt = '가수 이름 ==> ';
set @height = 166;
select @txt, mem_name from member where height>@height;

*LIMIT에는 변수 사용 불가

 

3. 데이터 형 변환

3.1 명시적 형변환

CAST(), CONVERT()

select cast(avg(price) as signed) '평균 가격' from buy;
select convert(avg(price), signed) '평균 가격' from buy;

signed: 부호 있는 정수

unsigned: 부호 없는 정수

 

cast와 avg 모두 괄호를 사용한다. 헷갈릴 수 있기 때문에, 익숙해지기 전까지는 연습삼아 과정별로 나눠서 입력하는 것도 좋다.

select price '평균 가격' from buy;
SELECT AVG(price) as '평균 가격' from buy;
select cast(avg(price) as signed) '평균 가격' from buy;
select convert(avg(price), signed) '평균 가격' from buy;

 

날짜형 변환

select cast('2022$12$12' as date);
select convert('2022/12/12', date);
select convert('2022%12%12', date);
select convert('2022@12@12', date);

 

문자형 변환+concat 사용

-- select num, 가격x수량, 구매액 from buy; 가이드
select num, 
    price, 'x', amount, '=' '가격x수량', 
    price*amount '구매액' from buy;
    
--형변환
select num,
convert(price, char), 'x', convert (amount, char), '=' '가격x수량', 
price*amount '구매액' from buy;

--concat
select num, 
    concat(convert(price, char), 'x', convert (amount, char), '=') '가격x수량', 
    price*amount '구매액' from buy;

 

3.2 암시적 형변환

select '100' + '200'; -- 정수로 형변환, 300
select concat (100, '200'); -- 문자열로 형변환, 100200
select 3<'5frogs'; -- 정수 5로 형변환, 1(true)
select 3>'frog'; -- 문자열은 정수 0으로 형변환, 1(true)

 

 

 

 

 

4.2 두 테이블을 묶는 조인

1. 내부 조인

2. 외부 조인

3. 기타 조인

 

 

1. 내부 조인

두 테이블에 모두 데이터가 있어야 결과 나옴

 

select * from buy
	where buy.mem_id = 'GRL';

이 상태에서 JOIN문을 추가한다고 생각한다.

select * from buy
    inner join member
    on buy.mem_id = member.mem_id
	where buy.mem_id = 'GRL';

 

where절로 조건을 제한하지 않은 경우

select * from buy
	inner join member
    on buy.mem_id = member.mem_id;

기준이 되는 테이블이 member인 경우

-- 오류: 두 테이블 모두에 mem_id 컬럼이 있음
select mem_id, mem_name, prod_name, addr, concat(phone1, phone2) '연락처'
 	from buy
 	inner join member
     on buy.mem_id = member.mem_id;
     
-- 중복되는 컬럼에 테이블명 명시
select buy.mem_id, mem_name, prod_name, addr, concat(phone1, phone2) '연락처'
	from buy
	inner join member
    on buy.mem_id = member.mem_id;
    
-- 모든 컬럼에 테이블명을 명시해서 더 명확하게 하고 join했음을 알기 쉽게 함
select buy.mem_id, member.mem_name, buy.prod_name, member.addr, concat(member.phone1, member.phone2) '연락처'
	from buy
    inner join member
    on buy.mem_id = member.mem_id;
    
-- 테이블명 별칭을 사용해서 간략하게 표현
select b.mem_id, m.mem_name, b.prod_name, m.addr, concat(m.phone1, m.phone2) '연락처'
	from buy b
	inner join member m
    on b.mem_id = m.mem_id;

 

DISTINCT문 사용

select distinct m.mem_id, m.mem_name, m.addr
	from buy b
		inner join member m
        on b.mem_id = m.mem_id
	order by m.mem_id;

 

 

 

2. 외부 조인

한쪽에만 데이터가 있어도 결과 나옴

SELECT 칼럼명
FROM 첫번째테이블(LEFT)
    LEFT|RIGHT|FULL OUTER JOIN
    ON 조인조건
[WHERE절]

보통 LEFT OUTER JOIN을 사용한다.

-- 구매 이력이 없는 회원도 출력
select m.mem_id, m.mem_name, b.prod_name, m.addr
	from member m
		left outer join buy b
        on b.mem_id = m.mem_id
	order by m.mem_id;

 

RIGHT OUTER JOIN: 기준이 오른쪽 테이블

FULL OUTER JOIN: LEFT+RIGHT 합

 

 

3. 기타 조인

3.1. 상호 조인cross join(=카티션 곱cartesian product)

select *
    from buy
    cross join member;

 

3.2. 자체 조인self join

select a.emp "직원", b.emp "직속상관", b.phone "직속상관연락처"
    from emp_table a
        inner join emp_table b
        on a.manager = b.emp
    where a.emp = '경리부장';

 

 

 

 

 

4.3 SQL 프로그래밍

1. IF문

2. CASE문

3. WHILE문

4. 동적 SQL

1. IF문

2. CASE문

3. WHILE문

4. 동적 SQL