SQL 강의 - day2 : MYSQL

▶ 다시 정리 -처음 접속시
mysql -u유저명 -p비밀번호   => exit
show databases; => DB 목록보기
use 선택DB명; => DB선택 
show tables;  => 테이블 목록보기
desc 테이블명 ;=> 테이블의 구조보기

select * from 테이블명 ;=> 해당 테이블의 데이터보기

rename table 테이블명(전) to 테이블명(후);  => 테이블명 변경

Distinct : 중복제거

우리 학교의 과를 출력(중복없이 출력)
select distinct address from student;

 

산술연산자 (+ - * /) => 나머지 연산자가 없음.
값이 null 이면 연산이 안됨.
null + 1 = null
비교연산자 (>, <, >=, <=, =, <>) 
   -  +=, -=, ++ 안됨.
논리연산자 (and, or, not)
우선순위 ()

▶ 집약과 정렬
집약 : 열의 데이터 합계, 평균 같은 집계값을 구하기 위한 함수
- count : 검색된 행의 개수
- sum : 합계
- avg : 평균
- max : 최대값
- min : 최소값
- group by : 그룹별로 묶음
- having : 그룹에 조건
- order by : 정렬 (기준을 여러개 줄 수 있음.)
  각 필드마다 asc, desc 결정해야 함.
ex) order by age asc, score desc;
- limit : 시작번지, 개수 => 시작번지부터 개수만큼 추출

 

▶ 조건을 나타내는 함수
if문, case~when~then문
- if문
if(조건식, 참, 거짓)
if(조건식, 참, if(조건식, 참, 거짓))

- case문
case
when 조건 then 값
when 조건 then 값
when 조건 then 값
when 조건 then 값
else 값
end;

 

▶SQL 내장함수
산술연산 함수
- 산술연산자 (+ - * /)
- mod(값, 값2) : 값을 값2로 나눈 나머지
- round(값, 자리수) : 반올림
- ceil(값) : 올림 / floor(값) : 버림 => 자리수 지정X
- sqrt : 제곱근 / pow : 제곱

문자열 함수
-concat(str, str2) : 문자열 연결
-length(str) : 문자열 길이 (영어1, 한글2)
-char_length(str) : 무조건 1글자로 인식
-substr(str, start, count) : 시작 위치부터 개수만큼 추출
-left(str, count) : 왼쪽에서 개수만큼 추출
-right(str, count) : 오른쪽에서 개수만큼 추출
-mid(str, start, count) : 시작위치에서 개수만큼 추출
-replace(str,old,new): old 문자를 찾아 new 문자로 변경
-insert(str,위치,길이,삽입할 문자) 
  : 문자에서 위치의 길이만큼 지우고 삽입
-ucase(str):대문자로변환 / lcase(str):소문자로변환
-space(숫자) : 숫자만큼 공백 추가
-trim(문자) : 공백제거 /ltrim /rtrim
-trim(both '-' from '------abcd--')
- both(양쪽) / leading(앞) / trailing(뒤)
-lpad(str, 총길이, 채울문자)/rpad 
: 앞/뒤쪽에 총길이만큼 공백을 삽입, 공백부분에 채울문자를 채우기
-repeat(str, 횟수) : 문자열을 횟수만큼 반복
-reverse(str) : 문자를 거꾸로 출력

날짜함수
-adddate / subdate 
: 날짜를 기준으로 ~후, ~전 날짜
adddate(날짜, interval 5 day)
interval 뒤쪽 값 : year, month, day, hour, minute, second 가능
-curdate(), curtime(), now(), sysdate() : 오늘날짜/시간
-year(날짜), month(), day(), hour(), minute(), second()
:년월일시분초 추출
-date(날짜) / time(날짜/시간) : 추출
-datediff(날짜1, 날짜2) : 날짜2에서 날짜1까지 남은 날짜
-dayofweek(날짜) : 요일(일=1, 월=2....)
-monthname(날짜) : 월의 영어이름을 리턴
-dayofyear(날짜) : 1년중 몇일이 경과했는지 리턴
-last_day(날짜) : 해당 월의 마지막 날짜
-time_to_sec(시간) : 시간을 초단위로 변환

논리함수
-if문
if(조건식, 참, 거짓)
if(조건식, 참, if(조건식, 참, 거짓))
-case~when~then 문
case
when 조건 then 값
when 조건 then 값
when 조건 then 값
else 값
end;

정보함수
- user(), current_user(), session_user() 
: 현재 유저
- database(), schema() : 현재 사용중인 DB 명 리턴

---

 

-칼럼 생성시 계산된 값을 자동계산해 생성하고 싶을 경우
(generated columns)

1.실저장(자주조회)  

2. 가상저장 -보여주기만함(조회가 어쩌다 한번)

-stored : 값이 저장되는 방식
(데이터가 입력되거나, 수정될 때, 해당칼럼도 같이 갱신)
-virtual : 데이터를 저장하지 않고 정의만
(해당 칼럼을 읽으려고 시도할 때 계산 을 통해 보여주는 것만)

<db가 많이 사용하는 케이스> : 트리거와 비슷한 개념(d,u,i)
차이점은 칼럼에서만 사용할 수 있음 
alter table buy add total int generated always as  
(price*amount) stored after amount;

-제품명 별 판매수량/ 판매합계 조회
select product_name, sum(amount), sum(price) from buy
group by product_name;

-그룹없이 총 매출, 판매금액
select sum(amount), sum(price) from buy;

 

▶ 트랜잭션(transaction)
- 하나의 작업을 하기위한 명령어 묶음 단위
- 연관있는 작업들의 모임
- 하나라도 명령어가 완료되지 못하다면(error) 취소
- 모든 명령어가 완료되어야 진행
- 완료와 취소를 나중에 결정하는 개념
- 데이터의 안전성 확보를 위한 방법


start transaction; //자바에서 알아서 함
명령어1; //dml에 해당하는 언어 
명령어2;
...
rollback; => 위에 작업한 명령어 모두 취소
commit; => 작업 완료 

DDL/ DCL 포함 안됨

 

 

▶ DB에서 변수의 값을 저장할 경우
-사용자 변수(전역변수), 지역변수, 
-사용자 변수 Session 영역에 저장 
-한 클라이언트에서 정의한 사용자 변수는  다른 클라이언트가 보거나 사용할 수 없음
-SET @cnt :=값; (:=,=)
//칼럼명과 변수명은 구분되어야 하기 때문에 닷을 씀
select @cnt;