MySQL, 3일차 (1)

MySQL 문자열 함수

concat : 복수의 문자열을 연결해 주는 함수

select concat('안녕하세요, 'MySQL') as concat;

select concat(address1, ' ', address2, ' ', address3) as address
from member where userid='apple';

 

 left, right : 왼쪽 또는 오른족에서 길이만큼 문자열을 가져옴 

select left('ABCDEFGHIJKLMN', 5);

select userid, left(userpw, 2) as password from member;

 

substring : 문자열의 일부를 가져옴
- substring(문자열, 시작위치): 시작위치부터 끝까지 

- substring(문자열, 시작위치, 길이) : 시작위치부터 길이만큼

select substring('ABCDEFGHIJKMLN', 5) as sub;

select substring('ABCDEFGHIJKMLN', 5, 3) as sub;

select userid, substring(userpw, 1, 3) as password from member;

 

char_length: 문자열의 길이를 반환

select char_length('ABCDEFGHIJKMLN') as cnt;

select email, char_length(email) as len from member;

 

lpad, rpad : 왼쪽 또는 오른쪽의 해당 길이 만큼 늘리고, 빈 공간을 채울 문자열을 반환 

-  lpad(문자열, 총길이, 채울문자열) 

select lpad('ABCDEFG', 10, '0') as pad;

select userid, rpad(userid, 20, '*') as pad from member;

 

ltrim, rtrim, trim: 왼쪽, 오른쪽, 모든 공백을 제거

select ltrim('     ABCDEF     ') as ltrim;

select trim('     ABCDEF     ') as trim;

 

replace : 문자열에서 특정 문자열을 변경
- replace(문자열, 대상, 바꿀 문자열)

select replace('ABCDEFG', 'CD', '') as repl;

 

 유니온(union)
 :합집합을 나타내는 연산자로, 중복된 값을 제거함
 서로 같은 종류의 테이블(컬럼이 같아야 함)에서만 적용이 가능함

 select 컬럼명1, 컬럼명2, .. from 테이블1 union select 컬럼명1, 컬럼명2 .. from  테이블

 

create table product(
	code varchar(5) not null,
    name varchar(20) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);

insert into product values('00001', '그래픽카드', '엄청 빨라요', 400000, now());
insert into product values('00002', '맥북', '너무 예뻐요', 1400000, now());
insert into product values('00003', '밀키트', '맛있어요', 10000, now());
insert into product values('00004', '서버', '잘돌아요', 10000000, now());
insert into product values('00005', '스포츠카', '빨라요', 100000000, now());

select * from product;

create table product_new(
	code varchar(5) not null,
    name varchar(20) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);

insert into product_new values('10001', '게임', '재밌어요', 91000, now());
insert into product_new values('10002', '집', '넓어요', 500000000, now());
insert into product_new values('10003', '고양이', '야옹', 500000, now());
insert into product_new values('10004', '강아지', '멍멍', 400000, now());

select * from product_new;

 

두 테이블의 모든 로우가 합쳐짐

select code, name, price, regdate from product
union
select code, name, price, regdate from product_new;

insert into product values('00003', '밀키트', '맛있어요', 10000, now());
select * from product_new;

 

 날짜/시간이 다르기 때문에 중복 데이터가 모두 출력

union은 중복데이터를 제거

select code, name, price from product
union
select code, name, price from product_new;

 

union all은 중복데이터를 모두 출력

select code, name, price from product
union all
select code, name, price from product_new;

 

서브쿼리(Sub Query)
    - 다른 쿼리 내부에 포함되어 있는 select 문을 의미
    - 서브쿼리를 포함하고 있는 쿼리르 외부쿼리라고 부르고, 서브쿼리는 내부쿼리라고도 부름
    - 서브쿼리는 괄호()를 사용해서 표현
    - select, where, from, having 절 등에서 사용할 수 있음

 

 상품코드가 '00001'의 가격보다 크거나 같은 가격을 가지고 있는 상품의 모든 정보를 출력

select * from product where price >= (select price from product where code='00001')

product 테이블에서 모든 상품의 code, name, price, 가장 비싼 가격을 모두 출력

select code, name, price, (select max(price) from product) as '가장비싼가격' from product;

 

auto_increment: 필드에 identity한 숫자를 자동으로 부여 

create table orders(
	no int auto_increment primary key,
    userid varchar(20) not null,
    product_code varchar(5) not null,
    cnt int default 0,
    regdate datetime default now(),
    foreign key(userid) references member(userid)
);

insert into orders values (1, 'apple', '00005', 1, null);
select * from orders;
insert into orders values (10, 'banana', '00004', 1, null);
select * from orders;
-- insert into orders values (10, 'apple', '00003', 2, null); # 중복된 no
insert into orders (userid, product_code, cnt) values ('apple', '00003', 2);
select * from orders;

insert into orders (userid, product_code, cnt) values ('banana', '00002', 1);
insert into orders (userid, product_code, cnt) values ('orange', '00004', 1);
insert into orders (userid, product_code, cnt) values ('avocado', '00003', 1);
insert into orders (userid, product_code, cnt) values ('cherry', '00001', 1);

 

상품을 최소 2번이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별을 출력

select * from member;
select * from orders;
select userid, name, gender from member where userid 
in (select userid from orders group by userid having count(no) >= 2);

상품을 최소 2번이상 구입한 아이디의 앞 2글자와 이름, 상품 구입횟수를 출력

 

- 조인

select left(m.userid, 2) as userid, m.name, count(o.no) as cnt from member as m inner join 
orders as o on m.userid = o.userid group by m.userid having cnt >= 2;

- 서브쿼리

select left(m.userid, 2) as userid, m.name, sub.ocnt from member as m inner join
(select userid, count(no) as ocnt from orders group by userid having count(no) >= 2) as sub
on m.userid = sub.userid;

select * from orders_new;
insert into orders_new(select * from orders);

create table orders_new_new(select * from orders);
select * from orders_new_new;

 

뷰(view)
    - 가상의 테이블을 생성
    - 실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 직접 저장하고 있지 않음 
    
    뷰를 사용하는 이유
    - SQL 코드를 간결하게 만들어 줌
    - 삽입, 삭제, 수정 작업에 대한 제한 사항을 가짐 
    - 내부 데이터를 전체 공개하고 싶지 않을 때 

select * from member;
select userid, name, hp, gender from member;
create view vw_member as select userid, name, hp, gender from member;
select * from vw_member;
select * from profile;

 

 member 테이블의 userid, name, hp와 profile 테이블의 mbti를 가지는 가상 테이블(뷰)를 만들어보자 

-  vw_member_profile  

create view vw_member_profile as select m.userid, m.name, m.hp, p.mbti from member as m left join 
profile as p on m.userid = p.userid;

select * from vw_member_profile;

 

뷰 수정하기 

alter view 뷰이름 as 쿼리문 ...

 

alter view vw_member_profile as select m.userid, m.name, m.hp, p.mbti from member as m inner join 
profile as p on m.userid = p.userid;

 

뷰 삭제하기

drop view vw_member_profile;
select * from vw_member_profile;

 

ER - Diagram

Database - Reverse Engineer