3. MySQL 문법2

1. MySQL 문자열 함수

1-1) concat

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

select concat('안녕하세요!', 'MySQL') as concat;
select * from member;
select concat(address1, ' ', address2, ' ', address3) as address
	from member where userid='apple';

결과

 

1-2) left, right

왼쪽 또는 오른쪽에서 길이만큼 문자열을 가져옴

select left('ABCDEFGHIJKLMN', 5);     # ABCDE
select userid, left(userpw, 2) as password from member;

 

결과

 

1-3) substring

문자열의 일부를 가져옴

- substring(문자열, 시작위치): 시작위치부터 끝까지
- substring(문자열, 시작위치, 길이): 시작위치부터 길이만큼
select substring('ABCDFGHIJKLMN', 5) as sub;       # FGHUJKLMN 
select substring('ABCDFGHIJKLMN', 5, 3) as sub;    # 5번째 F부터 3개 : FGH
select userid, substring(userpw, 1, 3) as password from member;

결과

 

1-4) char_length

문자열의 길이를 반환

select char_length('ABCDEFGHIJKLMN') as cnt;        # 14
select email, char_length(email) as len from member;

결과

 

1-5) lpad, rpad

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

# lpad(문자열, 총길이, 채울문자열)
select lpad('ABCDEF', 10, '0');     # 10자를 채우는데 빈공간을 0으로 채움
select userid, rpad(userid, 20, '*') as pad from member;

결과

 

1-6) ltrim, rtrim, trim

왼쪽, 오른쪽, 모든 공백을 제거

select ltrim('   ABCDEFG    ') as ltrim;     
select trim('   ABCDEFG     ') as trim;

 

 

1-7) replace

문자열에서 특정 문자열을 변경

# replace(문자열, 대상, 바꿀 문자열)
select replace('ABCDEFG', 'CD', '') as repl;   # ABEFG

2. 유니온(union)

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

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

 

2-1) 두 개의 테이블 생성

# 1) product 테이블 생성, 데이터 입력
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', '그래픽카드', '엄청 빨라요', 4000000, now());
insert into product values('00002', '맥북', '너무 예뻐요', 14000000, now());
insert into product values('00003', '밀키트', '맛있어요', 10000, now());
insert into product values('00004', '서버', '잘돌아요', 10000000, now());
insert into product values('00005', '스포츠카', '빨라요', 100000000, now());

# 2) product_new 테이블 생성, 데이터 입력
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());

 

2-2) union 적용

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

결과

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

insert into product_new values('00003', '밀키트', '맛있어요', 10000, now());
select code, name, price, regdate from product
union
select code, name, price, regdate from product_new;

결과

2-4) union은 중복데이터를 제거

select code, name, price from product
union
select code, name, price from product_new;   # 밀키트가 사라짐

결과

 

2-5) union all은 중복데이터를 모두 출력

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

결과

3. 서브쿼리

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

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

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

결과

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

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

결과

 

3-3) auto_increment : 필드에 identity한 숫자를 자동으로 증가

# orders 테이블 생성
create table orders(
    no  int auto_increment primary key,   # 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);  # 같은 번호를 지정하면 에러

# 제일 마지막에 넣은 숫자 기준으로 증가함
insert into orders (userid, product_code, cnt) values ('apple', '00003', 2);
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);
select * from orders;

결과

3-4) 상품을 최소 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);

결과

3-5) 상품을 최소 2번이상 구입한 아이디의 앞 2글자와 이름, 상품 구입횟수를 출력
(방법1) 조인

select left(m.userid, 2), 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;

결과

(방법2) 서브쿼리

select left(m.userid, 2), 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;

3-6) 테이블 내용 복사하기

# orders_new 테이블 생성
create table orders_new(
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_new(select * from orders);   
create table orders_new(
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_new(select * from orders);   
select * from orders_new;

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

4. 뷰(view)

create view 뷰이름 as 쿼리문
- 가상의 테이블을 생성
- 실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 직접 저장하고 있지 않음
    
# 뷰를 사용하는 이유
- 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;

결과

4-1) member 테이블의 userid, name, hp와 profile 테이블의 mbti를 가지는 가상 테이블(뷰) 만들기

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

결과

4-2) 뷰 수정하기

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

 

4-3) 뷰 삭제하기

drop view vw_member_profile;
select * from vw_member_profile;

4-4) ER - Diagram

Database메뉴 -> Reserve Engineer

5. MySQL 사용자

5-1) 사용자 추가하기

- MySQL 8.0 Command Line Client에서 root 계정으로 로그인
- 접속 가능한 사용자 추가하기
create user '사용자명'@'localhost' identified by '비밀번호';

- 사용자 목록 조회
use mysql;
select user, host from user;

- 할당 권한 상세 옵션
create, drop, alter : 테이블에 대한 생성, 삭제, 변경 권한
select, insert, update, delete : 테이블의 데이터를 조회, 삽입, 변경, 삭제에 대한 권한
all : 모든 권한
usage : 권한을 부여하지 않고 계정만 생성

grant 권한 on 데이터베이스명.테이블명 to '사용자'@'localhost';  #localhost는 ip를 등록
grant all on *.* to 'apple'@'localhost';
grant all on *.* to 'apple'@'%';  # 모든 ip에서 접근 가능
flush privileges;                 # 변경사항이 즉시 적용됨

5-2) 사용자 삭제하기

drop user 계정명;

 

5-3) 사용자 권한 조회/삭제하기

- 조회
show grants for '계정명'@'localhost';

- 제거
revoke 권한 privileges on *.* from '계정명'@'localhost';

( 적용 )

- 새로운 데이터베이스 생성
create database apple;
create table apple.member(select * from kdt.member);

- apple.member에 select 권한만 가능한 apple 계정을 생성 (콘솔창 입력)
create user 'apple'@'localhost' identified by '1111';    # 사용자 생성
grant select on apple.member to 'apple'@'localhost';
flush privileges;

- apple 데이터베이스에 모든 권한을 가진 사용자 'superman'을 생성하고 member 테이블에 사용자를 추가!
show grants for 'superman'@'localhost';
revoke all privileges on apple.* from 'superman'@'localhost';
drop user 'superman'@'localhost';

use mysql;
select user, host from user;