문자열 함수

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

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

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

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

select left('ABCDEFGHIJKLMNOP', 5);

select userid, left(userid, 2) as leftfunc from member;

3. substring
   문자열의 일부를 가져옴

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

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

4. char_length
   문자열의 길이를 반환

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

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

lpad(문자열, 총길이, 채울문자열)
select point, lpad(point, 5, '0') as pad from member;

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

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

7. replace
   문자열에서 특정 문자열을 변경

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

---

유니온(union)

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

🖥️예제

product 테이블

product_new 테이블

현재 00004 플립5가 중복된다.

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

플립5가 중복되지만 regdate 값이 다르기 때문에 중복제거가 되지 않는다.(플립5가 2개)

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

select문에 regdate를 빼면 모든 컬럼의 값이 같으므로 중복값이 제거된다.

만약 중복값을 제거하고싶지 않을 경우 union all을 사용하면 된다.

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

---

서브쿼리(Sub Query)

• 다른 쿼리 내부에 포함되어 있는 select 문을 의미
• 서브쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부르고, 서브쿼리는 내부쿼리라고 부름
• 서브쿼리는 괄호()를 사용해서 표현
• select where, from, having 절 등에서 사용할 수 있음
• 단일값일때 연산자로 비교 가능
• 다중값이면 in을 사용

🖥️ 서브쿼리 예제

where절

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

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

select절

코드번호, 상품명, 가격, 제일 비싼 가격을 출력하고싶다

select code, name, price, (select max(price) from product) as max_price from product;

auto increment

필드의 identity한 숫자를 자동으로 부여

예제 테이블

서브쿼리 심화 예제

1. 상품을 최소 2번이상 구입한 횟수가 있는 회원의 아이디와 이름 성별을 출력해보세요

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

처음에는 문제에서 어떤 부분을 서브쿼리로 만들어야하는지 생각한 후 서브쿼리를 먼저 만들고 그 다음에 전체 쿼리를 작성하면 더 편하게 쿼리를 작성할 수 있다.

2. 상품을 최소 2번이상 구입한 아이디의 앞 2글자와 상품 구입 횟수를 출력(조인으로 작성)

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

---

CRUD

MySQL는 관계형 데이터베이스 관리 시스템(RDBMS)으로, 데이터를 생성(Create), 읽기(Read), 수정(Update), 삭제(Delete)하는 기본적인 데이터 조작 작업을 수행할 수 있다. 이러한 데이터 조작 작업은 CRUD 작업이라고도 불린다.

---

MySQL 사용자

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 select on 데이터베이스명.테이블명 to '사용자'@'localhost';
  grant all on *,* to '사용자'@'localhost';
  flush privileges;          			
  grant select on 데이터베이스명.테이블명 to '사용자'@'%'; -- 모든 IP에서 접근이 가능

  🖥️ 예제

  1. apple 데이터베이스 생성

     create database apple;

  2. apple 데이터베이스에 kdt.member 테이블을 복사

     create table apple.member(select * from kdt.member);

  3. apple.member select권한만 가능한 apple계정을 만들어보자

     grant select on apple.member to 'apple'@'localhost';

2. 사용자 삭제하기

drop user '계정명'@'localhost';

3. 사용장 권한 제거하기

revoke 권한명 privileges on 데이터베이스명.테이블명 from '계정명'@'localhost';

4. 사용자 권한 조회하기

show grants for '계정명'@'localhost';

🖥️ 예제
1. apple 데이터베이스에모든 권한을 가진 사용자 'orange'를 생성

create user 'orange'@'localhost' identified by '2222';
grant all on apple.* to 'orange'@'localhost';

2. orange의 권한을 확인

show grants for 'orange'@'localhost';

3. orange의 모든 권한을 제거

revoke all privileges on apple.* from 'orange'@'localhost';

from 부분에서 오류가 나는데 해당 부분은 무시해도 된다.

4. orange 사용자를 삭제

drop user 'orange'@'localhost';

5. user 검색

use mysql;
select user, host from user;

---

뷰(view)

• 가상의 테이블을 생성
• 실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 직접 저장하고 있지는 않음

뷰를 만드는 이유

• SQL 코드를 간결하게 만들기 위함
• 삽입, 삭제, 수정 작업에 제한 사항을 가짐
• 내부 데이터를 전체 공개하고 싶지 않을 때

create view 뷰이름 as 쿼리.....

🖥️ 예제
member의 userid, name, hp와 profile의 mbtl를 출력하는 뷰(vw_member_profile)를 만들어보자

member 테이블

profile 테이블

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

select * from vw_member_profile;

트랜젝션(Transaction)

• 분할이 불가능한 업무처리의 단위
• 한꺼번에 수행되어야 할 연산 모음

commit

모든 작업들을 정상 처리하겠따고 확정하는 명령어. 해당 처리 과정을 DB에 영구적으로 저장

rollback

작업 중 문제가 발생되어 트랜젝션의 처리 과정에서 발생한 변경사항을 모두 취소하는 명령어

srart transaction
	블록안의 명령어들은 하나의 명령어 처럼 처리됨
    ...
    성공하던지, 실패하던지 둘 중 하나의 결과가 발생
    문제가 발생하면 rollback;
    정상적인 처리가 완료되면 commit;

자동 커밋 확인

• autocommit: ON -> 자동으로 commit 해줌
• set autocommint =0 (off)m set autocommit=1 (on)

show variables like '%commit%';

🖥️ 예제

start transaction;
	insert into word values('car', '자동차', 1);
	select * from word;
    rollback;
    select * from word;
    
    insert into word values('car', '자동차', 1);
    commit;
    select * from word;

insert문으로 롤백이 가능하다.

트렌젝션의 예외

DDL문(create, drop, alter, rename, truncate)에 e대해 예외를 적용: rollback 대상이 아니다.

truncate

• 개별적으로 행을 삭제할 수 없으며, 테이블 내부의 모든 데이터를 삭제
• DDL문이기 때문에 rollback이 불가능
• 트랜젝션 로그에 한번만 기록하므로 delete 구문보다 성능 면에서 빠름

truncate table 테이블명;

🖥️ truncate 및 트렌젝션 예제

select * from product_new;
start transaction;
	delete from product_new;
    rollback;
    select * from product_new;
    
    
start transaction;
	truncate table product_new;
    select * from product_new;
    rollback;
    select * from product_new;

# autocommit 활성화
set autocommit = 1;

delete문은 롤백이 가능하지만 truncate는 롤백이 불가능하다.