3. MySQL 데이터베이스 관리와 사용자 권한 설정

1. MySQL 문자열 함수

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

select concat('Hello!', 'Mysql') as concat;
select * from member;
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('ABCDEFGHIJKLMN', 5) as sub;
select substring('ABCDEFGHIJKLMN', 5, 3) as sub;
select userid, substring(userpow, 1, 3) as password from member;

 

char_length: 문자열의 길이를 반환

select char_length('ABCDEFGHIJKLMN', 5) as cnt;
select email, char_length(email) as len from meber;

 

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

#lpad(문자열, 총길이, 채울문자열)
select lpad('ABCDEFGHIJKLMN', 10, '0') as pad;
select userid, rpad(userid, 20, '*') as pad from member;

 

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

select ltrim('          ABCD'     ) as ltrtim;
select trim('          ABCD'     ) as trtim;

 

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

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

 

2. 유니온

합집합을 나타내는 연산자로, 중복값을 제거한
서로 같은 종류의 테이블(컬럼이 같아야 함)에서만 적용이 가능
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', 'graphic card', 'super fast', 400000, now());
insert into product values('00002', 'mac book', 'pretty', 1200000, now());
insert into product values('00003', 'meal kit', 'delicious', 10000, now());
insert into product values('00004', 'server', 'nice', 100000, now());
insert into product values('00005', 'super car', 'good', 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', 'game', 'funny', 910000, now());
insert into product_new values('10002', 'house', 'big', 500000000, now());
insert into product_new values('10003', 'cat', 'yayong', 500000, now());
insert into product_new values('10004', 'dog', 'mung', 400000, now());

select * from product_new;

 

두 테이블의 모든 row가 합쳐짐

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

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

insert into product_new values('00001', 'meal kit', 'delicious', 10000, now());

 

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;

 

3. 서브쿼리(Sub Query)

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

# 상품코드가 '00001'의 가격보다 크거나 같은 가격을 가지고 있는 상품의 모든 정보를 출력
select * from product where  price >= (select price from product where code='00001');

# product 테이블에서 모든 상품의 code, name, pirce, 가장비싼가격(100000000) 을 모두 출력          
select code, name, price, ( select max(price) from product ) as max_price 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);
insert into orders values (10, 'banana', '00004', 1, null);
-- 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), 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;

 

테이블 복제

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)
);

select * from orders_new;
insert into orders_new(select * from orders);
 
create table orders_new_new(select * from orders);
select *  from orders_new_new;

 

4. 뷰(view)

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

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;

# 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 right join profile as p on m.userid = p.userid;
select * from vw_member_profile;

# 뷰 삭제하기
drop view vw_member_profile;
select * from vw_member_profile; # 삭데 되었으므로 에러 발생

 

5. ER - Diagram

"ER - Diagram"은 엔터티-관계 다이어그램(Entity-Relationship Diagram)을 가리킵니다. 
   이는 데이터베이스 설계에서 엔터티(개체)와 그들 간의 관계를 시각적으로 표현한 것입니다.
   
   ER - Diagram
   Database - > Reverse Engineer

 

6. MySQL 사용자

   1. 사용자 추가하기
   MySQl 8.0 Command line Client에서 root 계정으로 로그인
   - 접속 가능한 사용자 추가하기
    create user '사용자명'@'localhost' identified by '비밀번호';
       create user 'apple'@'localhost' identified by '1111';
- 사용자 목록 조회
    use mysql;
       select user, host from user;
- 할당 권환 상세 옵션
    create, rop, alter: 테이블에 대한 생성, 삭제, 변경 권한
       select, insert, update, delete: 테이블의 데이터를 조회, 삽입, 변경, 삭제에 대한 권한
       all: 모든 권한
       usage: 권한을 부여하지 않고 계정만 생성
       
       grant 권한 on 데이터베이스명.테이블명 to '사용자'@'localhost';
       grant all on *.* to 'apple'@'localhost';
       
       grant all on *.* to 'apple'@'%'; # 모든 ip에서 접근이 가능
       
       flush privileges;
       변경사항이 즉시 적용됨

2. 사용자 삭제하기
    drop user 계정명;

3. 사용자 권한 조회하기
    show grants for '계정명'@'localhost';

4. 사용자 권한 제거하기
    revoke 권한명 privilleges 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 테이블에 사용자를 추가!
    create user 'superman'@'localhost' identified by '1112';
    grant all on apple.*  to 'superman'@'localhost';

#  superman 계정 권한을 취소 및 삭제
show grants for 'superman'@'localhost';
revoke all privileges on apple.* from 'superman'@'localhost';
drop user 'superman'@'localhost';

# 확인하기
use mysql;
select user, host from user;