TIL 2023.01.09 ~ 2023.01.11 MySQL

DB

MySQL

설치 도움말 파일로 생성하기

mysql --help > help.txt

exe 파일로 설치가 아닌 zip 파일로 설치 시 압축 푼 폴더로 가서 data 폴더 생성 후 bin 폴더안에서 cmd 창 새로 실행한 뒤
아래 커맨드로 mysql을 실행 해둬야 mysql 로그인 가능 꺼질 경우 mysql 작동 안함

mysqld

로그인

mysql -u root -p

방화벽문제로 접속 불가능할 경우

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
>> success
sudo firewall-cmd --reload

테스트 - 버젼 확인, 현재 날짜

SELECT VERSION(), CURRENT_DATE;

사용자 확인

use mysql;
mysql > select host, user, password from user;

사용자 추가

create user 'userid'@'%' identified by '비밀번호'; 

사용자의 권한 추가

grant all privileges
> on webdb.*
> to user01@localhost
> identified by '1234';

데이터베이스 생성, 사용

create database webdb;
use webdb;

테이블 확인, 구조확인

show tables;
desc pets;

테이블 생성, 삭제

CREATE TABLE pet ();
DROP TABLE pet;

데이터 조회, 입력, 수정, 삭제

SELECT column1, column2,... FROM table_name WHERE condition;
INSERT INTO table_name [(column1, column2, column3, ...)] VALUES (value1, value2, value3, ...);
UPDATE table_name SET column='value', ... WHERE condition;
DELETE FROM talbe_name WHERE condition

파일 데이터 로드 해오기
(레코드의 값들은 탭(tab)으로 구분되어있으며 column순서대로 값이 적혀있어야한다!)

LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

ORDER BY 절

ORDER BY birth;
ORDER BY birth DESC; (역순 정렬)

WHERE 절

AND, OR
IS NULL
IS NOT NULL
LIKE "b%"; (b로 시작)
NOT LIKE "%fy" (fy로 끝남)
LIKE "%w%"; (w가 포함됨)
IN ('value', 'value');

문자형 함수

UPPER(), UCASE()
LOWER(), LCASE()
SUBSTRING(column, 시작index, 끝index)
LPAD(column, 출력할 자릿수, 빈자리에 출력할 문자), RPAD()
TRIM(), LTRIM(), RTRIM() - 공백제거

숫자형 함수

ABS(x) - x의 절대값
MOD(m, n) - m을 n으로 나눈 나머지
FLOOR(x) - x보다 크지 않은 가장 큰 정수
CEILING(x) - x 보다 작지 않은 가장 큰 정수
ROUND(x) - x 에 가장 가까운 정수
ROUND(x, d) - x값중에 소숫점 d자리에 가장 근접한 수
POW(x, y) - x의 y승
SIGN(x) - x가 음수인지 0인지 양수인지 판별
GREATEST(x, y, z...) - 가장 큰 수
LEAST(x, y, z...) - 가장 작은 수

날짜형 함수

CURDATE() YYYY-MM-DD
CURTIME() HH:mm:SS
NOW(), SYSDATE()
DATE_FORMAT(date,format) 
PERIOD_DIFF(p1, p2) - p1과 p1의 차이 개월 수 반환(날짜 포맷은 YYYYMM or YYMM 이여야함)
ADDDATE('1998-01-02', INTERVAL 42 DAY);  (42일후의 날짜 반환) INTERVAL 이후 1 MONTH, 1 YEAR 같은 형태로 작성
SUBDATE('1998-01-02', INTERVAL 2 MONTH) - (2달전의 날짜 반환)

형변환

CAST(20200101 AS DATE); - 숫자를 날짜 형태로 변환 (2020-01-01)
CAST(20200101030330 AS CHAR); - 숫자를 문자열로 변환

그룹 함수

COUNT(expr) non-NULL인 row의 숫자를 반환
COUNT(DISTINCT expr,[expr...]) non-NULL인 중복되지 않은 row의 숫자를 반환
COUNT(*) row의 숫자를 반환
AVG(expr) expr의 평균값을 반환
MIN(expr) expr의 최소값을 반환
MAX(expr) expr의 최대값을 반환
SUM(expr) expr의 합계를 반환
VARIANCE(expr) 분산
STDDEV(expr) expr의 표준 편차를 반환

GROUP BY - HAVING 절

- GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
- 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
- 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다.
- 아래는 집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
- HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
- HAVING절은 GROUP BY절과 함께 사용이 된다.

SELECT job
     , COUNT(*) cnt
     , SUM(sal) sal
  FROM emp
 WHERE deptno IN ('10', '20', '30')
 GROUP BY job
HAVING COUNT(*) > 2 AND SUM(sal) > 5000

제약조건

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
DEFAULT

CHECK

테이블 생성 시 제약 조건 부여

CREATE TABLE Test
(
    ID INT NOT NULL,
    Name VARCHAR(30),
    ReserveDate DATE,
    RoomNum INT
);


CREATE TABLE 테이블이름
(
    필드이름 필드타입,
    ...,
    [CONSTRAINT 제약조건이름]
    FOREIGN KEY (필드이름)
    REFERENCES 테이블이름 (필드이름)
)

추가 제약 조건 조절 ALTER

부여

ALTER TABLE 테이블이름
ADD 필드이름 필드타입 NOT NULL

ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름] PRIMARY KEY (필드이름)

ALTER TABLE 테이블이름
ADD [CONSTRAINT 제약조건이름]
FOREIGN KEY (필드이름)
REFERENCES 테이블이름 (필드이름)

수정

ALTER TABLE 테이블이름
MODIFY COLUMN 필드이름 필드타입 PRIMARY KEY

ALTER TABLE 테이블이름
MODIFY COLUMN [CONSTRAINT 제약조건이름] UNIQUE (필드이름)

삭제

ALTER TABLE 테이블이름
DROP FOREIGN KEY 제약조건이름

REFERENCE 옵션

ON DELETE, ON UPDATE
CASCADE - 같이 수정, 삭제 됨
SET NULL - 수정, 삭제 될 경우 NULL 적용
NO ACTION - 아무일도 하지 않음
SET DEFAULT - 수정, 삭제 시 기본값으로 적용
RESTRICT - 참조하는 테이블에 데이터가 남아 있으면, 참조되는 테이블의 데이터를 삭제하거나 수정할 수 없음.

FOREIGN KEY (ParentID)
REFERENCES Test1(ID) ON UPDATE CASCADE ON DELETE RESTRICT

join

oracle 표준 방식

equal join self join 오라클 표준방식은 결국 집합의 모든 형태를 나타내기 어렵고
어떤 join방식인지 한눈에 알아보기 어렵기 때문에 ansi 방식이 새로 생겨나게 되었고
이후 RDBMS들은 이 방식을 따라가게 되었음

ansi 방식

cross join

select * from emp cross join dept;

inner join(=equal join) where 대신에 on 사용하기 때문에 where로 조건을 더 달 수 있음

select * from emp inner join dept on emp.deptno=dept.deptno;
select * from emp inner join dept using(deptno); // 중복된 컬럼명이 있을 때
select * from emp natural join dept; // 오직 하나의 컬럼명만 중복되었을 때
select * from emp inner join dept on emp.deptno=dept.deptno where emp.deptno=10;

outer join

select * from emp left outer join dept on emp.deptno=dept.deptno;
select * from emp right outer join dept on emp.deptno=dept.deptno;

// 공통되지 않는 부분만
select * from emp left outer join dept using(deptno) where emp.empno is null;
select * from emp right outer join dept using(deptno) where emp.empno is null;

self join

select a.empno, a.ename, b.ename from emp a inner join emp b on a.mgr=b.empno;

변수 사용

set @su1:1234;
select @su1 from dual;
select @su2:=4321 from dual;

// rownum 만들어주기
set @rownum:=0;
select @rownum:=@rownum+1, empno, ename from emp;
select @rn:=@rn+1, empno, ename from emp, (select @rn:=0 from dual) a;

서브쿼리

// table 서브쿼리
select * from (select empno, ename from emp) a;

// column 서브쿼리
// return 값은 반드시 하나여야함 Error : Subquery returns more than 1 row
// 그렇기 때문에 보통 하나의 결과만을 리턴하는 그룹함수를 사용한다.
select empno, ename, sal,(select avg(sal) from emp) from emp;

// 조건절 서브쿼리
// 상황에 따라 다르다.
select empno, ename, sal from emp where sal<(select avg(sal) from emp);
select empno, ename, sal from emp where sal in (select sal from emp);

//기존값 복사 결과의 필드(컬럼,레코드) 복사
insert into dept (select * from dept where deptno=10);
// 테이블 복사
create table t50 as (select empno, ename from emp);
// 스키마만 복사
create table t51 as (select empno, ename from emp where 1!=1);
// 테이블 중복제거
create table dept2 as (select distinct deptno, dname, loc from dept);
drop table dept;
alter table dept2 rename dept;

// insert에 활용 단 이 쿼리는 레이스컨디션 문제가 발생할 수 도 있다. 걍 auto_increment쓰는게 낫다. 혹은 sequence 만들어 사용
insert into dept values ((select max(deptno)+1 from dept a), 'test', 'test');