3 week MYSQL

날짜 함수

datediff	일 차이
timestampdiff	지정한 연,분기,월,시분초 차이

• 두 날짜의 차이

select datediff(날짜1, 날짜2)

select timestampdiff( [second / minute / hour / day / week / month / quarter / year], 날짜1 ,날짜2)

 

where과 having의 차이 

where은 기본적인 조건절로 우선적으로 모든 필드에 적용된다. 하지만 having 절은 group by된 이후 필드부터 적용된다.

 

ex) 급여가 5000이상 받는 사원으로 합계를 내서 업무를 그룹화하여 급여의 합계가 20000을 초과하는 업무명, 사원수 , 합계, 평균을 구하라

select job_id, count(employee_id) 사원수 , count(salary) 합계,avg(salary) 평균
from employees
where sum(salary)>=5000
group by job_id
having salary>20000;

-> 급여가 5000이상 받는 사원 = where

-> 그룹화한 필드부터 급여의 합계가 20000을 초과 = having

 

 

핵심 join

두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법

현재 테이블에서 취득할 수 없는 컬럼의 데이터를 상대 테이블에서 취득하기 위함

• inner join

inner join

inner join은 A와 B 테이블의 교집합을 반환

select * from A inner join B on A.key = B.key

select * from A , B where A.key=B.key​

• self join

self join

self join은 자기 자신을 가리키는 테이블을 두번 이상 참조하여 조인하는 것이다.

select * from A a1 join A a2

select * from A as a1 , A as a2

 

sub query

서브 쿼리란 SQL 안에 포함되어 있는 SQL문을 의미한다.

 

- 서브 쿼리를 사용하는 이유

1. 서브 쿼리는 쿼리를 구조화시키므로 각 쿼리에 대한 구분을 명확히 해준다.
2. 제약적인 group by에 대해 또 다른 방법을 제공해준다.
3. 가독성이 좋다.

- 서브 쿼리 주의 사항

1. 서브 쿼리는 메인 쿼리의 컬럼을 모두 사용할 수 있지만 메인 쿼리는 서브 쿼리의 컬럼을 사용할 수 없다.
2. 괄호를 감싸서 사용한다.
3. order by를 사용할 수 없다.

- 서브 쿼리의 위치에 따른 명칭

select (select ...) --스칼라 서브 쿼리
from (select ...) -- 인라인 뷰
where (select ...) -- 일반 서브 쿼리

1. 스칼라 서브 쿼리 :  select 절에 사용하는 서브 쿼리로,  컬럼처럼 사용한다.
2. 인라인 뷰 : from 절에 사용하는 서브 쿼리로, 테이블 처럼 사용한다.  * 무조건 as 별칭을 지정해줘야 한다.
3. 일반 서브 쿼리 : where 절에 사용하는 서브 쿼리로, 변수 처럼 사용한다.

 

sequence

int auto_increment primary key;

자동으로 값이 증가

 

 

무결성

column을 지정하는 성질

1. primary key : 기본키 , null 값과 중복값을 허용하지 않는다. 
2. unique key : 고유키, null 값을 허용하지만 중복값을 허용하지 않는다.
3. foreign key : 외래키, null 값을 허용하며 join 목적으로 사용하는 경우가 많으며, 외부 테이블에서는 기본키인경우가 대부분
4. check : 범위나 조건을 지정하여 설정한 값만 허용한다.
5. null / not null : null을 허용할지 / null을 허용하지 않을 지 지정

• 무결성 제약조건까지 모두 삭제

drop table if exists 테이블이름

• 범위 설정

create table 테이블이름(
	col1 varchar(10( primary key -- 기본키 지정
	col2 varchar(10) not null -- null을 허용하지 않는다.
    	constraint col3 varchar(20) check(col2 >=10) -- 10이상인 값만 허용
  );

• 기본키 삭제

alter table 테이블이름 drop primary key

• 기본키와 외래키 연결

create table 테이블A(
	A_colname int primary key;
 );
 
 create table 테이블B(
 	A_colname int,
 	foreign key(A_colname) reference 테이블A(A_colname)
 );

 

외부 접속 허용

create user "userName"@'%' identified by "Password"; -- id와 password 생성

grant all privileges on *.* to 'userName'@'%' with grant option; -- 외부에 모든 접속을 허용

flush privileges