SQL

 --select * from '데이터베이스명'.'테이블명' ;
 select * from employees.departments ;

select *  from employees.employees; -- 전체 데이터를 조회 
select emp_no , birth_date, gender from employees.employees ; 
-- 특정컬럼을 대상으로 조회 

select count() from dual ; 
-- dual 은 더미테이블 
select (1+1) / 2 as val from dual ; 
-- select절에서 다양한 연산처리를 할떄 미리 사용

select emp_no, birth_date from employees.employees order by birth_date asc ; 
-- 데이터를 조회된 결과를 특정 컬럼을 기준으로 오름차순(ASC) 혹은 내림차순(desc)으로 보여줄지 결정.

select emp_no, birth_date from employees.employees order by emp_no desc ; 
select * from employees.employees order by first_name desc , gender ; 
-- order by는 deafault가 오름차순

select distinct first_name from employees.employees ; 
-- 중복된 값을 제거하고 유일한 값들만 보여줄 때 씀 
select distinct first_name , gender from employees.employees ;

select * from employees.employees where first_name = 'Georgi' ; 
-- 조건에 맞는 한정된 집합만을 조회할 때 쓰는 where
select * from employees.employees where hire_date > '1986-04-30' ; 
-- 1986년 4월 30일 이후의 입사한 사람들을 조회
select*from employees.employees where first_name = 'Georgi' and hire_date > '1986-04-30'; 
-- 입사일이 1986년 4월 30일 이후에 입사한 이름이 Gerogi인 사람 조회 
select  from employees.employees where hire_date between '1986-03-11' and '1986-04-30'; 
-- 1986년 3월11일과 4월30일 사이의 입사한 사람들을 조회

select * from employees.employees where first_name in ('Percy' , 'Weiye'); 
-- 성이 Percy 이거나 Weiye인 사람을 조회

select * from employees.employees where first_name like 'W%'; 
-- 이믈의 첫글자가 W로 시작되는 사람들을 모두 조회

CREATE TABLE TEST_DB.customer ( 
seq INT NOT NULL AUTO_INCREMENT, 
ct_id VARCHAR(20) NOT NULL, 
ct_pw VARCHAR(100) NOT NULL, 
address VARCHAR(100) NOT NULL, 
ct_tell VARCHAR(20) NOT NULL, 
REG_DATE DATE NOT NULL, 
EXPIRED_DATE DATE NOT NULL, 
PRIMARY KEY(seq) ) ENGINE= MYISAM CHARSET=utf8 ;
-- show character set ; 캐릭터 셋 종류 확인 

INSERT INTO test_db.customer (ct_id, ct_pw, address, ct_tell, REG_DATE, EXPIRED_DATE) 
values ('yunpha123','1234','인천시 부평구 갈산동 무지개아파트 101호','010-1234-5678','20180920','2020-02-18') ;

select * from test_db.customer ;

select * from employees.departments; 
insert into employees.departments (dept_no, dept_name) values('d010', 'android developteam') ; 
insert into employees.departments (dept_no, dept_name) values('d011', 'IOS developteam') ; 
insert into employees.departments (dept_no, dept_name) values('d012', 'Infra team') ; 
insert into employees.departments (dept_no, dept_name) values('d013', 'operation team') ;
-- 근데 내가 부서이름을 잘못 넣었어. 
update employees.departments set dept_name = 'andorid team' where dept_no = 'd010'; 
select * from employees.departments where dept_no= 'd010';

select from employees.departments ; 

update employees.departments set dept_name = 'IOS team' 
where dept_name = 'IOS developteam'; 

select  from employees.departments where dept_no = 'd011';
-- 위험 update에 조건절이 없을경우 db에서는 매우 위험한 작업으로 인식함 왜냐하면 데이터 전체를 바꾸게 되므로 
--update employees.departments set dept_no = 'd100' ;

select  from employees.employees ; 
select  from employees.dept_emp ;
select * from employees.employees a , employees.dept_emp b where a.emp_no = b.emp_no ; 
create table employees.emp_dept as (select a.emp_no , b.dept_no from employees.employees a , employees.dept_emp b where a.emp_no = b.emp_no) ;
select * from employees.emp_dept ;
delete from employees.emp_dept where emp_no = 10001 and dept_no='d005'; 
delete from employees.emp_dept where emp_no = 10002 ;
delete from employees.emp_dept ; -- 데이터만 삭제됨

 CREATE VIEW 뷰이름 AS ( SELECT 필드이름1, 필드이름2, ... FROM 테이블이름 WHERE 조건)

CREATE VIEW employees.emp_dept22 as (
select a.emp_no , b.dept_no from employees.employees a , employees.dept_emp b 
where a.emp_no = b.emp_no) ; 
select * from employees.emp_dept22 ;

select emp_no , salary from employees.salaries ; 
select a.*, b.salary from employees.employees a left join 
(select emp_no , salary from employees.salaries) b on a.emp_no = b.emp_no ; 

--테이블 생성 
create table employees.emp_salary as (select a.* , b.salary from employees.employees a 
left join 
(select emp_no , salary from employees.salaries) b on a.emp_no = b.emp_no ) ; 
select * from employees.emp_salary ;

select emp_no , first_name, last_name, salary 
from (select * from employees.emp_salary where hire_date ='1986-06-26' ) a 
order by salary limit 3 ;

select emp_no , first_name,last_name, salary , 
(select round(avg(salary),-1) from employees.emp_salary ) as '평균급여' 
from employees.emp_salary where emp_no =10010;

select emp_no, first_name , last_name ,hire_date from employees.employees 
where hire_date < (select min(hire_date) from employees.employees where last_name = 'Peac') 
order by hire_date ;

CREATE TABLE table_name (
   id    INTEGER  PRIMARY KEY,
   col2  CHARACTER VARYING(20),
   col3  INTEGER FOREIGN KEY REFERENCES other_table(column_name),
   ... )