MySQL , SQL 기본 문법들

< SQL 잡기술들 >

순서 : SELECT ~ FROM ~ WHERE ~ GROUP BY ~ HAVING ~ ORDER BY ~ LIMIT ~ ;

< AND >
select name from member where height>160 AND member_num<4;

 

=> 각기다른 column 조건 AND 로 연결

 

여러개 써서 조건 여러개 걸기 가능 

Select name From member

WHERE

height > 160 

AND department is not null

AND commission is not null 

AND ....; 

< BETWEEN X AND Y >
select name from member where height BETWEEN 163 AND 166;

=> X 이상 Y 이하 

< OR > ( 칼럼 = ' ' ) OR ( 칼럼 = ' ' )
select name from member where address = '경기' OR address = '전남' OR address = '서울';

 (*주의 : address = '경기 ' or '서울'   이렇게는 (X)  / 매번 써줘야함 )  

< IN > OR 한방에
select name from member where address IN ('경기', '전남', '서울');

=>  IN 으로 OR 문 합치기 (이거나 이거나)

< is > null
null 인 값을 select 로 찾아올때는 = 연산자로는 못찾는다.
select name from member where phone_number is null;
=> is 로 찾아야 한다.
- is null / is not null

< LIKE > %
select name from member where name LIKE '%대';

=> 소녀시대 // LIKE 활용 / % = 모든글자

select name from member where name LIKE '%소녀%';

=>소녀시대/우주소녀 // 이름에 소녀 들어가면 다찾음 %A%

 

select name from member where name LIKE '_ _ 시대';

=>    _ _  글자 수 (2글자)

2022-12-06

where hire_date Like '_ _ _ _ _12%';               => 날짜에 쓰면 12월 만 찾아내기 

 

< 따옴표 >
- 문자열의 경우, Java 에서는 " " 쌍따옴표 / ORACLE DB 에서는 '  ' 따옴표만 인정한다.
- MySQL 은 둘다 인정한다.
- 괜히 헤깔릴 수 있으니, SQL 에서는 되도록 ' ' 따옴표를 사용해야겠다.


< ORDER BY > 정렬 ASC (디폴트) / DESC / 동률정렬

select * from member ORDER BY height;

=>   기본 오름차순 정렬 ( ASC 생략 ) 
select * from member ORDER BY height DESC; 
=> 내림차순 
select * from member ORDER BY height DESC,  weight ASC;

=>  height로 먼저 정렬 => 동률일때 weight 오름차순으로 정렬

- null 값은 제일 작은 값으로 본다.
( asc 면 맨처음 / desc 면 마지막 )

- order by 에 limit 0,3   => index 사용해서
게시판 Paging 기능 쉽게 구현 가능 ( limit => mysql 기능, oracle엔 없다)


< LIMIT >   ( MySQL 전용) 
select * from member ORDER BY height LIMIT 3; 

=>  LIMIT 정렬순에서 3개까지만 보겠다.

select * from member ORDER BY height LIMIT 0 , 3; 

=> index[ 0 ] 번째 부터 3개 보겠다.

 

< DISTINCT > 중복 제외
select address from member;    = > 서울 경기 경기 서울 서울 경남 서울 전남 경기 경남 서울 경기 . . .
select DISTINCT address from member;   => 서울 경기 경남   // DISTINCT => 중복 제거하고 종류만 뽑아서 보여줌


< GROUP BY >  각 부서별 급여 총합 조회 

 

select  SUM(price)  from buy GROUP BY Product;

=> Product 그룹별로, SUM(price) 값을 조회해라. 

 

SUM(price) 

10000

20000

30000  

( 근데 이러면 어떤 그룹의 SUM 가격인지 알 수 없다. )

 

 

select Product, SUM(price) from buy GROUP BY Product;   

 => 추가로, 해당  Product 칼럼도 같이 보여줘라. 

상품         가격 

A             10000

B             20000

C             30000

 

=>  자동으로 해당하는 Product 를 가져와서 붙여준다! 

 

( GROUB BY 한 칼럽이 아닌 '일반 칼럼' 인 경우) 

select salary, sum(salary) from member GROUP BY prodcut; 

( salary 는 그룹화한 칼럼이 아니라 일반 칼럼이다) 

salary는 레코드 107개 데이터, sum 은 GROUP BY 한 값이 총 3개 인데, 이렇게 동시에 뽑으려 하면 
 salary 레코드도 3개 밖에 안뽑힌다. ( 게다가 순서도 의미없는 순서 ) 
=> 집계함수 조회할 때는, 일반 컬럼은 같이 조회해도 의미가 없다.  ( 같이조회 X )

 

=> GROUP BY 한 칼럼과 같이 조회하면  
     개수도 DISTINCT 로 보기 때문에 일치하고, 

     SUM/AVG 등 계산한 값을 해당하는 순서 그룹에 맞춰서 자동으로 조회해준다. 

 

     SELECT  그룹화칼럼 , SUM(  ) FROM member GROUP BY 그룹화칼럼; 

 

그룹화한 바로 그 칼럼을 같이 조회해서 쓰는 것이 맞는 사용법!  

 

 

< 집계 함수 > 

 

- GROUP BY => 집계함수와 같이 사용

- SUM  /  AVG  /  MAX  /  MIN  /  COUNT   

 

select SUM(price) from buy;

( 총 구매 금액 )

=> 집계함수 자체만 사용하기 

 

select id, SUM(price*amount)  from buy group by id;

 (그룹 id 별로 총구매금액  )

=>  SUM 에  ( prcice * amount ) 더할 값들을 수식으로 줘서 필요한 값을 구하도록 응용해서 사용

select id, AVG(amount), AVG(price) from buy group by id;

( 그룹 별로 방문(구매)할때마다, 평균 몇개, 평균 얼마 ) 

=> 집계함수 여러개 사용 가능 

 

-  COUNT  :  레코드 개수 
  => (기본) null 인거 count 안함!

 

SELECT  COUNT(commission_pct) from employees WHERE commission_pct IS  NULL;

=> NULL 인거 찾으라는 건 COUNT 못함. 

=> 대신 IS NOT NULL 찾으라는건 COUNT 함. 

 

-  DISTINCT COUNT :  중복제거하고 개수 

 

select DISTINCT COUNT( department ) from emplyee; 

< GROUP BY => HAVING 절과 쓴다. ( WHERE 절과 함께 쓸 수 없다 ) >
순서 : group by ~ HAVING ~ [조건]

select id, sum(price*amount) from buy GROUP BY id HAVING sum(price*amount) > 300;

id 그룹별로 묶어서 총가격을 더해라. 그중에(HAVING) 총가격 더한게 300 이상일 때만 select 해라

 

 

- 조건이 집계함수(SUM / AVG) 값을 사용 O => having 절
- 조건이 집계함수(SUM / AVG) 값을 사용 X => where 절 
=> 두개 잘 구분해서 사용! 

( AND 로 여러 조건 줄 수 있다는 것 기억! )

< DECIMAL > 10진수/소수
- pay DECIMAL ( 10, 2 ) => ( 10자리수 / 소수점 2자리수 ) 길이 정하기


< 알리아스Alias - 별칭주기 >
=> 진짜 column 이름은 그대로 // Table 에 보이는 모습만 바꿈

select height 신장 , debut "데뷔 날짜" from member;
// 한칸 뛰고 별칭
// 별칭에 띄워쓰기하려면 " " 쌍따옴표

- ORDER BY 할 때, 알리아스로 준 별칭으로도 SQL 문 가능하다.
select pay 급여 from emp ORDER BY 급여; //그냥 쓰기귀찮을때.


<  입력 INSERT  >

 

insert into boardentity (id, title, contents)
values(7, 'title making', 'contents making');

insert into boardentity values (7, 'title making', 'content making'); - 칼럼명 명시 생략 가능

 

- 칼럼명 명시 기능 

insert into emp (employee_id, last_name, hire_date) 
values(95, 'june', now());
=> 넣을 타입을 먼저 명시해놓으면 그 값들만 들어간다는 거고, 
나머지는 자동으로 null 처리 해준다.   

- 명시 안하면 개수 어긋나기 때문에 안들어감 
-  not null 컬럼 일 경우 'doesn't have a default value' 오류 뜬다. )

 

- decimal 로 정한 타입으로 맞춰서 넣을 필요없다. 
=> decimal 이 자동으로 맞춰줌

- 날짜 insert 할 때, 2022 /22 둘다 2022로 가져와주고, 
  시간 안쓰면 00:00:00 으로 자동으로 가져와준다. 
-  now( ) 로 지금시각까지 넣을 수 있고 ( 시간까지 줌 )
   current_date( ) 이건 날짜까지            (시간은 0000)

 

 

< insert  ignore into    (강제)  > 
insert  ignore into emp values(6, ~ , '2020' ); 
=> 날짜 format이 지켜지지않아 원래 안들어가지만 
일단 대충 넣어만 놔줘 ( 나중에 내가 알아서 처리할게 ) 

( 실무에서 대량의 데이터 한번에 insert 할 때, 짜잘짜잘한거 때문에 전체가 insert 가 안될 수 있으니까 
그럴때 일단 강제로 넣어놓고 나중에 처리(update) 하는 방법. 
( - MySQL 만의 기능 )  ) 

 

 

< on duplicate key >  <= PK 있을 때 사용가능 (혹은 unique) 
insert into cpemp valuse ( ~ ) 
on duplicate key update hire_date = now( ); 

=> 인서트 할껀데, 만약 중복 키가 있다면, 
       -  update 해줘 데이트를 now 로 . 

 

< insert into + select 서브쿼리 > 
- 테이블은 이미 존재하고, 데이터만 복사해서 집어넣을 때 사용 

insert into  emp  select * from emplyees where ~; 

( 여기서 본체 쿼리는 insert 고, 이걸 실행하기 위한 쿼리인 select 를 서브쿼리 라고 한다. ) 

 

 

 

<  수정 UPDATE  >        => PK 이용 (safe mode)

 

update member SET addr = ' 바꿀이름 ' WHERE ( key칼럼이름 = 'gd');

 

<  Update + limit   >
update  emp  set  salary = salary + salary*0.2
where first_name = '부장'  order by ~
LIMIT 2;  

=> 부장중에 2명까지만 update 한다. 

 

< **주의** >
UPDATE 할때 WHERE 절 안주면 모든 DATA 가 다 바뀌어버린다!  
( DELETE 는 당연하고! ) 

=> UPDATE 나 DELETE 할때는 백업 필수! 
  1. COPY 해서 복사본으로 작업하기 
  2. 매일 / 매주 / 매월 단위로 백업 해놓기 
    

 

 

 

<  DELETE 삭제  >           PK 이용 (safe mode)
DELETE  from member where id = 'hero';

 

 

<MySQL 날짜 date 형식>
220701 ( YYMMDD)
20220701 (YYYYMMDD) 둘다 된다.

create table (
- hireday DATE, => 날짜
INSERT INTO EMPLOYEES VALUES( ~, NOW( ), ~);
날짜 줄때 NOW( ) 로 주면 현재 날짜로 입력된다.

 

- 날짜는 '    ' 따옴표 필수!

- 날짜 <= 연산자 사용 가능 ( 초 단위까지 )

select * from member where hire_date  >=  ' 2006-01-01 00:00:00 '; 

=> 2006년 이후로 가져온다. 

 

- 집계함수에서 MAX 는 가장 높은 (최근) 날짜  / MIN 은 낮은 (오래된) 날짜 SELECT MIN(hire_date) from member; 

 

 

<MySQL Int type 형식>
1500 <= 이거로 쓰자
'1500' 둘다 되긴한다.

- CHAR type 일때는 ' 따옴표 ' 처리 필수


< DB 생성 >
DROP DATABASE IF EXISTS member_db; (초기화하면서 다시 만들고 싶을때)
CREATE DATABASE member_db;

 

- DESCRIBE 테이블명  =>  칼럼,타입,눌,키,특성 등 정보 보여줌 

<DB 사용 선언 USE>
USE member_db; ( = 더블클릭 bold 글씨 )

 

<DB 에서 table 직접 생성할때>
CREATE TABLE table명 (
id Long primary key,
username varchar(255));
Entity 에서 중간에 대문자로 한번 강조줬으면, DB 에선 그걸 _ 로 받아들인다. _ 로 끊어서 생성해줘야함.

< Table 이름변경 >
ALTER TABLE 테이블이름 RENAME 새이름;


< Column 추가 > TYPE 속성
ALTER TABLE 테이블이름 ADD 칼럼이름 VARCHAR(45) NOT NULL;

<Column 제거 >
ALTER TABLE 테이블이름 DROP 칼럼이름 ;

<Column 변경 >
ALTER TEBLE 테이블이름 MODIFY 칼럼이름 CHAR(10); MODIFY - TYPE 만 변경

ALTER TABLE 테이블이름 CHANGE 칼럼이름 새이름 CHAR(10); CHANGE - 이름, TYPE 다 변경 가능





< MySQL 에서 table 생성 create문 >
CREATE TABLE product (
productid INT NOT NULL,
productname CHAR(20) NOT NULL,
productnum INT NOT NULL,
PRIMARY KEY ( productid ) ) ;


< CREATE COPY >
CREATE TABLE emp_compy (
SELECT emp_name, pay, hire from emp );

- select 로 가져와서 테이블 생성하기
- 실무에서 이거 select * 로 복사해둬서 백업용으로 사용한다.



< PK 선언 >
(1) productid INT NOT NULL PRIMARY KEY, 이렇게 칼럼뒤에 특성으로 붙여서 선언해주거나
(2) 위처럼 따로 선언 primary key( )


< PK SEQUENCE 선언 > AUTO_INCREMENT
create table product ( productid INT AUTO_INCREMENT NOT NULL PRIMARY KEY, ~ )
// PK 생성할때 특성 Null줄 때 그냥 띄어쓰기만으로 추가해준다.

< FOREIGN KEY 선언 >
CREATE TABLE BUY(
이걸로할게 참조 테이블명 (칼럼)
FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);



< MySQL 에서 table 제거 drop문 >
DROP TABLE selling; //바로 table 명

< MySQL 에서 INDEX 생성문 >
인덱스이름 테이블명 (칼럼명)-괄호이용
CREATE INDEX idx_member_name ON member ( name );

=> column 에 index 붙이겠다.

붙여놓으면 자동으로 index로 찾음 (non-unique key lookup 방식)
=> 빠름 빠름

< MySQL 에서 VIEW 생성 > => 인터페이스 역할 ( 고객 읽기전용 데이터 )

CREATE VIEW member_view AS select*from member; // view 생성

select * from member_view; // view 이용 view 로 뽑기


< MySQL - (Stored) PROCEDURE 생성 > => 메소드화

DELIMITER // ( 한칸 띄고 // )
CREATE PROCEDURE mypro( )
BEGIN
select * from member where ~ ;
select * from product where ~ ;
END //
( 여기에 DELIMITER ; 닫아주는 것도 있는데 일단 안닫아야 만들어짐 - 1064 ERROR )

CALL mypro( );

=> begin~end 부분 실행됨





< IFNULL > null 일때 값을 넣어놓기

select  IFNULL(pay, 1000) from emp;
페이를 조회하는데, null 이면 ( 페이를, 1000으로 맞춰줘 )
=> NULL 이면 0으로 라도 바꿔놓으면 PAY 를 0 으로 출력할 수 있다.


< CONCAT > 조회시 두개 이상의 컬럼 연결

select CONCAT( emp_name, ' 님의 연봉은 ', pay*12, ' 입니다.')연봉일지 from emp;

연봉일지

이사원 님의 연봉은 540011.88 입니다.

=> concat 쓰면 자바에서 + 처럼 데이터들을 String으로 더해서 출력할 수 있다.

----
< any / all / some / 서브쿼리 보충 >
----