MySQL Chapter3[13-2]

Chapter3

데이터 변경을 위한 SQL문 (insert, auto_increment, insert into ~ select, update, delete)

데이터베이스와 테이블을 만든 후에는 데이터를 변경하는, 즉 입력/수정/삭제하는 기능이 필요합니다. 

새로운 가입한 회원을 테이블에 입력할 때는 insert문을, 회원의 주소나 연락처가 변경되느어 정보를 수정할 때는 update문을 사용하고, 회원이 탈퇴를 해서 삭제를 해야하는 경우에는 delete문을 사용합니다.

데이터 입력:insert
:테이블에 행 데이터를입력하는 기본적인 SQL문은 insert입니다.

기본문 
insert into 테이블 열1,2... values 값1,2...

use market_db;
-- table 생성
create table hongong1 ( toy_id int, toy_name char(4), age int);
-- 입력 insert
insert into hongong1 values(1,'우디',25);
-- age 빼고 입력을 할 때
insert into hongong1 (toy_id, toy_name) values(2,'버즈');

select * from hongong1;​

자동으로 증가하는 auto_increment
:열을 정의할 때 1부터 증가하는 값을 입력해줍니다.

-- 자동으로 증가하는 AUTO_INCREMENT & PRIMARY KEY
create table hongong2 
(
	-- auto_increment는 무조건 primary key를 해줘야 한다.
	toy_id int auto_increment primary key,
    toy_name char(4),
    age int
);

-- toy_id에 null값을 주고 나머지 입력
insert into hongong2 values (null, '보핍', 25);
insert into hongong2 values (null, '슬링키', 22);
insert into hongong2 values (null, '렉스', 21);

-- auto_increment의 마지막 값을 리턴해준다.
select last_insert_id();

-- auto_increment 값을 100부터 시작한다.
alter table hongong2 auto_increment=100;
insert into hongong2 values(null,'재남',35);
select * from hongong2;

다른 테이블의데이터를 한번에 입력하는 insert into ~ select
: 많은 양의 데이터를 지금까지 했던 방식으로 직접 타이핑해서 입력하려면 오랜 시간이 걸릴 것인데, 다른 테이블에 이미 데이터가 입력되어 있다면 insert into ~ select 구문을 사용해서 해당 테이블의 데이터를 가져와서 한번에 입력할 수가 있습니다.

가져오기 위한 데이터를 불러온다.

-- 데이터 새로 불러오기 'world'
select count(*) from world.city;
-- world 테이블의 상위 5개만 조회
select * from world.city limit 5;

담기 위해 테이블을 새로 생성 후 담는다.

-- table 새로 생성
create table city_popul1
(
	city_name char(35),
    population int
);

-- world.city에서 name과 population만 가져와서 담는다.
insert into city_popul1 select Name, population from world.city;
select * from city_popul;

update문
:기존에 입력되어 있는 값을 수정하는 명령입니다.

-- update문
use market_db;
-- table city_popul1에서 city_name이 Seoul인 것을 서울로 변경
update city_popul1 set city_name = '서울' where city_name = 'Seoul';
select * from city_popul1 where city_name='서울';

--  table city_popul에서 city_name이 New York인 것을 city_name은 뉴욕으로, population은 0으로 변경.
update city_popul1 set city_name = '뉴욕', population= 0 where city_name = 'New York';
select * from city_popul1 where city_name='뉴욕';

-- city_popul1 table에서 popoulation을 10000으로 나눈 값을 popoulation에 다시 적용.
update city_popul1 set population = population / 10000;
select * from city_popul1 limit 5;

delete문
:테이블의 행 데이터를 삭제해야 하는 경우도 발생하는데, 이때 사용하는 것이 delete문입니다.

-- delete문
-- city_popul1에서 city_name이 New%인 것만 삭제.
delete from city_popul1 where city_name like 'New%';
select * from city_popul1 where city_name like 'New%';

-- 대용량 테이블의 삭제
-- 바로 만들면서 기존 테이블꺼를 담는다.
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);
select count(*) from big_table1;

-- 삭제하는 3가지, delete와 truncate는 테이블안의 행을 삭제하는 거지만
-- drop은 테이블 자체를 삭제한다.
-- delete
delete from big_table1;
-- drop
drop table big_table2;
-- truncate
truncate table big_table3;

Chapter4

MySQL의 데이터 형식

테이블을 만들 때는 데이터 형식을 설정해야 합니다. 데이터 형식에는 크게 숫자형, 문자형, 날짜형이 있습니다. 여기서 세부적으로는 여러개가 나뉘기도 합니다.

변수의 사용

SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있습니다. 변수의 선언과 값의 대입은 다음 형식을 따릅니다.

set @변수이름 = 변수의 값; ---> 변수의 선언 및 값 대입
select @변수이름 ; ---> 변수의 값 출력

변수는 워크벤치를 재시작할 때까지는 유지되지만, 종료하면 없어지므로 임시로 사용한다 생각을 하면 됩니다. 

ex)

-- 변수의 사용
use market_db;
set @myvar1 = 5;
set @myvar2 = 4.25;

select @myvar1;
select @myvar1+@myvar2;

set @txt = '가수 이름==> ' ;
set @height = 166;

SELECT @txt, mem_name FROM member WHERE height > @height;​

데이터 형변환
:문자형을 저웃형으로 바꾸거나 반대로 정수형을 문자형으로 바꾸는 것을 데이터의 형 변환이라고 부릅니다. 형 변환에는 직접 함수를 사용해서 변환하는 명시적인 변환과 별도의 지시 없이 자연스럽게 변환되는 암시적인 변환(자동 형변환)이 있습니다.

SELECT price '평균 가격' FROM buy;
-- 항상 select가 먼저 실행되고, 그 다음에 avg가 실행이 된다. 순서 생각.
SELECT AVG(price) '평균 가격' FROM buy;​

함수를 이용한 명시적인 변환
: 데이터 형식을 변환하는 함수는 cast(), convert()입니다. 둘은 형식만 다를 뿐 동일한 기능을 합니다.

-- 정수
SELECT CAST(AVG(price) AS SIGNED) '평균 가격' FROM buy;
-- 또는
select convert(avg(price) , signed) '평균 가격' from buy;​

날짜형도 변경할 수 있다.

-- 구분자를 날짜형으로 변경가능.
select cast('2022$12$12' as date);
select cast('2022/12/12' as date);
select cast('2022%12%12' as date);
select cast('2022@12@12' as date);

 

-- 가격 수량을 곱하는 실제 구매액을 표시하는 SQL을 작성할 수 있다.
-- concat()은 문자를 이어주는 역할을 합니다.
SELECT 
    num,
    CONCAT(CAST(price AS CHAR),
            'X',
            CAST(amount AS CHAR),
            '=') '가격X수량',
    price * amount '구매액'
FROM
    buy;

두 테이블을 묶는 조인

조인이랑 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말합니다. 두 테이블을 엮어야만 원하는 형태로 나오는 경우도 많습니다. 

내부조인
: 두 테이블을 연결 할 때 가장 많이 사용되는 것이 내부 조인입니다. 그냥 조인이라고 부른다면 내부 조인을 의미하는 것입니다.

1.일대다 관계의 이해
:두 테이블을 조인을 위해서는 테이블이 일대다 관계로 연결되어야 합니다. 일대다 관계란 한쪽 테이블에는 하나의 값(PK)만 존재해야 하지만 연결된 다른 테이블에는 여러개의 값(FK)이 존재할 수 있는 관계를 뜻합니다.

2.내부 조인의 기본
:일반적으로 조인이라고 부르는 것은 내부 조인을 말하는 것으로 조인 중에서 가장 많이 사용이 됩니다. 조인은 3개의 테이블로도 할 수 있지만, 보통은 2개로 조인을 합니다.

-- 내부 조인의 기본
use market_db;
-- buy table에서 mem_id가 GRL인 것에서 inner join table명을 통해 서로 연결한다.
-- select (column) from (table명) inner join (join할 테이블명) on 연결할 테이블명.column명 =테이블명.column명 where 조건식
select * from buy inner join member on buy.mem_id = member.mem_id where buy.mem_id = 'GRL';

-- where절이 없을 경우
-- buy table을 기준으로 한 것
select * from buy inner join member on buy.mem_id = member.mem_id;
-- member table을 기준으로 한 것
select * from member inner join buy on buy.mem_id = member.mem_id;

-- column명이 양쪽 테이블에 존재하는 것을 사용할 때는 명확하게 하기 위헤 테이블 명.column명을 사용해준다
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;
    
-- 별칭(alias) from 테이블, inner join 테이블 각각 뒤에 별칭을 붙여줌으로써 별칭을 사용할 수 있다.
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;​

*중복된 결과 1개만 출력하기(distinct)

-- 중복된 결과 1개만 출력하기
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;

3.외부 조인
:내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나오는데, 이와 달리 외부 조인은 한족에만 데이터가 있어도 결과가 나옵니다.

-- 외부 조인
select M.mem_id, M.mem_name, B.prod_name, M.addr
	from member	m
    left outer join buy B
    on M.mem_id = B.mem_id
    order by M.mem_id;

-- 외부 조인의 활용
-- distinct를 이용해 중복된 것은 하나만 보이게 하고, is null 값만 가져와서 조인해준다.
select distinct M.mem_id, B.prod_name, M.mem_name, M.addr
	from member M
    left outer join buy B
    on M.mem_id = B.mem_id
    where B.prod_name is null
    order by M.mem_id;