SQL - 유저 생성 관리, 함수, 서브쿼리, 뷰, 트랜젝션

1. 함수

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

concat(문자열1,문자열2,....)

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

select left('ABCDEFGHIKJLMN',5); # 왼쪽에서 다섯글자 가져옴
select right('ABCDEFGHIKJLMN',5);# 오른쪽에서 다섯글자 가져옴
select userid, left(userid,2) as leftfunc from member;

1.3. substring

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

select substring('ABCDEFGHIKJLMN',5); # 5부터 끝까지
select substring('ABCDEFGHIKJLMN',5,3); # 5부터 7까지

1.4. char_length : 문자열의 길이를 반환

select char_length('ABCDEFGHIKJLMN') as cnt;

1.5. pad

lpad, rpad는 왼쪽 또는 오른쪽에 문자열을 넣으며 원하는 길이만큼 늘리는 함수입니다.

• lpad(문자열,총길이,채울문자열) # 왼쪽에 문자열채워서 총길이 맞춘다
• rpad(문자열,총길이,채울문자열) # 오른쪽에 문자열채워서 총길이 맞춘다

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

1.6. trim : 공백제거

• ltrim, rtrim, trim : 왼쪽, 오른쪽, 양쪽 공백을 제거

select ltrim('  ABC   ') as ltrim;
select rtrim('  ABC   ') as ltrim;
select trim('  ABC   ') as ltrim;

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

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

1.8. union : 합집합

• 중복된 값을 제거할때 사용
• 서로같은 종류의 테이블(컬럼이 같아야함)에서만 적용이 가능

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

1.9. union all : 합집합

• 중복값 제거하지 않음

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

2. 서브쿼리

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

2.1. where절에 쓴 서브쿼리

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

2.2. select절에 쓴 서브쿼리

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

2.3. 서브쿼리를 사용한 복제

2.3.1. 데이터 복제

테이블1에 테이블2의 데이터 복제

create table 테이블1 (
	필드명1 데이터타입 제약조건, 
	필드명2 데이터타입 제약조건,
	필드명3 데이터타입 제약조건,
	...
    필드명n 데이터타입  제약조건
)
insert into 테이블1(select * from 테이블2);

2.3.2. 테이블 복제

테이블2을 복제한 테이블1 생성

create table 테이블1(select * from 테이블2을);

3. auto_increment

• 필드의 identity한 숫자를 자동으로 부여
• 무조건 PK가 됨(MySQL에서만)

create table 테이블명(
	변수명 데이터타입 auto_increment 제약조건
);

4. CROD

CROD는 현업에서 많이 사용하는 용어입니다. select, insert, update, delete를
create, read, update, delete로 표현한 것 입니다.

5. MySQL의 사용자 관리

root는 가장 높은 사용자 권한을 가지고 있습니다. root에서 사용자를 생성하고 권한을 부여하고 권한을 회수하고 사용자를 삭제하는 등의 사용자 관리에 대해 알아보겠습니다.

5.1. 사용자 추가하기

• MySQL 8.0 Command Line Client에서 root로 로그인
• 접속가능한 사용자 추가하기

create user '사용자명'@'localhost' identified by '비밀번호'

이때, #localhost썼음으로 현재 접속한 곳의 사용자를 추가하겠다는 의미합니다. 만약 특정한 IP에서만 접속할 수 있는 사용자를 등록하고 싶다면 IP 주소를 입력하면 됩니다.

5.2. 사용자 목록 조회

use mysql; #mysql 데이터베이스 안에 사용자데이터가 저장되어서 여기서 사용자 찾아야함
select user, host from user;

5.3. 권한 할당 상세 옵션

5.3.1. 권한 종류

• create, drop, alter : 테이블 생성,삭제,변경
• select, insert, update, delete : 테이블의 데이터 조회, 삽입, 변경, 삭제
• all : 모든 권한
• usage : 권한을 부여하지 않고 계정만 생성 (default)

5.3.2. 권한 부여 / 회수

• grant : 권한부여 명령어
• revoke : 권한 회수 명령어

grant select in 데이터베이스명.테이블명 to '사용자'@'localhost';
grant all on 데이터베이스명.테이블명 to '사용자'@'localhost';
flush privileges; # 캐쉬날리고 다시적용하는 기능, 새로고침과 같음. 권한적용 바로 안됐을수도 사용
grant select on 데이터베이스명.테이블명 to '사용자'@'%'; -- 모든 아이피에서 접근가능
grant select on 데이터베이스명.테이블명 to '사용자'@'특정아이피주소'; -- 특정 아이피만 접근가능

6. 뷰

가상의 테이블을 생성하여 사용

• 실제 테이블처럼 행과 열을 가지고 있지만 데이터를 직접 저장하고 있지는 않음
• 뷰를 쓰는 이유 :sql코드 간결하게 만들 수 있다(join한 결과를 뷰에 저장하면 뷰만 불러오면 하나의 테이블처럼 결과값 나올수있음)
• 삽입 삭제 수정 작업에 제한사항을 가짐
• 내부 데이터를 전체 공개하고 싶지 않을때

create view 뷰이름 as 쿼리

7. 트랜젝션(transaction)

트랜젝션은 분할이 불가능한 업무처리의 단위로 한꺼번에 수행되어야할 연산의 모음입니다.

• commit : 모든 작업들을 정상 처리하겠다고 확정하는 명령어, 해당 처리과정을 DB에 영구적으로 저장
• rollback : 작업 중 문제가 발생되어 트랜젝션의 처리과정에서 발생한 변경사항을 모두 취소하는 명령어

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

7.1. 자동 commit 확인

show variables like '%commit%'

기본값으로 자동 commit이 켜져있습니다.

7.2. 자동 commit 켜기/끄기

# 자동 commit 끄기
set autocommit = 0(off)
# 자동 commit 켜기
set autocommit = 1(on)

7.3. 트렌젝션의 예외

DDL문에 대해 트랜젝션에 예외를 적용합니다. DDL은 Data Definition Languague로 create, drop, alter, rename, truncate가 해당합니다.
DDL은 rollback이 불가능합니다. 때문에 굉장히 신중히 사용하여야 합니다.