mysql 공부정리 (2)

-- 4장 데이터의 추가 삭제 갱신
-- 행 추가하기 - insert
/* insert 명령
insert into 테이블명 values(값 1, 값 2, ...) 
*/
-- insert로 행 추가하기
select * from sample41;
-- 테이블의 열 구성 확인하기
desc sample41;
-- smaple41 테이블에 행 추가하기
insert into sample41 values(1,'abc','2014-01-25');
select * from sample41;
-- 값을 지정할 열 지정하기
-- insert의 열 지정
-- insert into 테이블명 (열1, 열2 ...) values(값1,값2...)
-- 열을 지정해 행 추가하기
insert into sample41(a,no) values('XYZ',2);
select * from sample41;
-- not null 제약
-- not null 제약이 걸린 열은 null 값을 허용x
-- not null 제약회피하기
insert into sample41(no,a,b) values(3,null,null);
select * from sample41;
-- default 명시적으로 값을 지정하지 않았을 경우 사용하는 초깃값
desc sample411;
-- 값을 생략하지 않고 행 추가하기 
insert into sample411(no,d)values(1,1);
select * from sample411;
-- 명시적으로 디폴트 지정
-- default로 값을 지정해 행 추가하기
insert into sample411(no,d) values(2,default);
select * from sample411;
-- 암묵적으로 디폴트 저장
insert into sample411(no)values(3);
select * from sample411;

-- 삭제하기 delete
/* delete 명령 
delete from에 테이블명 where 조건식 
*/
-- delete로 행 삭제하기
-- 저장된 데이터 확인하기
select * from sample41;
-- sample41 테이블에서 no 열이 3인행 삭제하기
delete from sample41 where no = 3;
set SQL_SAFE_updates = 0; -- 에러 1175 해결법
/* delete 명령 구
where 구에서 대상이 되는 행을 검색한느 것은 select 명령에서도 delete 명령에서도 똑같다.
*/
-- 데이터 갱신하기 update
-- update 명령
-- update 테이블명 set 열1 = 값1, 열2= 값2, ... where 조건식
-- update로 데이터 갱신하기
-- update 명령
-- update 테이블명 set 열명 = 값 where 조건식
select * from sample41;
-- sample41의 셀 값을 갱신하기
update sample41 set b = '2014-09-07' where no = 2;
select * from sample41;
-- update 명령에서는 where 조건에 일치하는 모든 행이 갱신된다.
-- update로 갱신할 경우 주의사항
-- update 명령으로 증가 연산하기
update sample41 set no = no+1;
select * from sample41;
-- 복수열 갱신
/* update 명령
update 테이블명 set 열명1= 값1, 열명2=값2, ... where 조건식
*/
-- null로 갱신하기
-- null초기화
update sample41 set a = null;
select * from sample41;

-- 물리삭제와 논리삭제
/* 물리삭제는 sql의 delete 명령을 사용해 직접 데이터를 삭제하자는 사고방식
   논리삭제는 테이블에 '삭제플래그'와 같은 열을 미리 준비해 둡니다. 즉, 테이블에서 실제로 행을 삭제하는 대신, update 명령을 이용해
   '삭제플래그'의 값을 유효하게 갱신해두자는 발상에 의한 삭제방법 */

-- 집계와 서브쿼리
-- 행 개수 구하기 
/* 집계 함수
count(집합)
sum(집합)
avg(집합)
min(집합)
max(집합) */
-- sample51의 행 개수 구하기
select * from sample51;
-- count로 행 개수 구하기
select count(*) from sample51; -- conut 집계함수로 행 개수를 구할 수 있다.
-- where 구 지정하기
-- sample51의 행 개수를 where 구를 지정하여 구하기
select * from sample51 where name = 'A';
select count(*) from sample51 where name = 'A';

-- 집계함수와 null 값
-- 행 개수를 구할 때 null 값 다루기
select * from sample51;
select count(no), count(name) from sample51; -- 집계함수는 집합 안에 null값이 있을 경우 무시한다.

-- distinct로 중복 제거
select * from sample51;
select all name from sample51;
select distinct name from sample51; -- distinct는 예약어 중복된 데이터를 제외한 결과를 클라이언트로 반환

-- 집계함수에서 distinct
-- 중복을 제거한 뒤 개수 구하기
select count(ALL name), count(distinct name) from sample51; -- null을 제외한 값의 개수

-- count 이외의 집계함수
/* sum,avg,min,max
sum([all|distinct]집합)
avg([all|distinct]집합)
min([all|distinct]집합)
max([all|distinct]집합)
*/
-- sum으로 합계 구하기
select * from sample51;
select SUM(quantity) from sample51; -- 수치형만 가능 null값은 무시
-- avg로 평균내기
-- avg로 평균값 구하기
select * from sample51;
select avg(quantity), sum(quantity)/count(quantity) from sample51; -- avg 집계함수로 집합의 평균값을 구할 수 있다.
-- avg로 평균값 계산하기(null을 0으로 반환)
select avg(case when quantity is null then 0 else quantity end) as avgnull0 from sample51;
-- min,max로 최솟값 최대값 구하기
select * from sample51;
select min(quantity), max(quantity), min(name), max(name) from sample51;

-- 그룹화 group by
/* group by
select * from 테이블명 group by 열1,열2....
*/
-- group by로 그룹화
select * from sample51;
-- name열로 그룹화하기
-- group by 구에서 name 열을 지정해 그룹화 하기
select name from sample51 group by name;
-- name열을 그룹화해 계산하기
-- group by 구와 집계함수를 조합
select name, count(name), sum(quantity) from sample51 group by name;

-- having 구로 조건 지정
-- 집계함수는 where 구의 조건식에서는 사용할 수 없다 다음은 오류가 난다
select name, count(name) from sample51 where count(name)= 1 group by name;
-- where 구가 실행순서가 group by로 그룹화하는 순서보다 앞서기 때문이다.
-- having을 사용해 검색
 select name, count(name) from sample51 group by name;
-- having구로 걸러내기
select name, count(name) from sample51 group by name having count(name) = 1;
-- 집계함수를 사용시 having 구로 검색조건을 지정한다.
-- 내부처리 순서 where 구 -> group by 구 -> having 구 -> select 구 -> order by 구

-- 복수열의 그룹화
-- group by에서 지정한 열이외의 열은 집계함수를 사용하지 않은 채 select 구에 지정할 수 없다.

-- 결과값 정렬
-- 집계한 결과 정렬하기
-- name 열로 그룹화해 합계를 구하고 내림차순으로 정렬
select name,count(name),sum(quantity) from sample51 group by name order by sum(quantity) desc;

-- 서브쿼리
/* 서브쿼리 (select 명령) */
-- delete의 where 구에서 서브쿼리 사용하기
select * from sample54;
-- sample54에서 a의 최솟값 검색하기
select min(a) from sample54;
-- 최솟값을 가지는 행 삭제하기
-- 괄호로 서브쿼리를 지정해 삭제
delete from sample54 where a = (select min(a) from sample54);
-- 단 위의 문장은 mysql에서는 실행이 불가능하다 delete 명령를 select 명령으로 바꾸면 실행은 가능하다

/* sql에는 순차형 언어에서처럼 변수가 존재하지 않는다 반약 변수를 사용할 수 있다고 하면 다음과 같이 정리해 표현할 수 있다.
변수 = (select min(a) from sample54);
delete from sample54 where a 변수
*/

/* 클라이언트 변수
앞서 언급한 변수에 관한 것으로, mysql 클라이언트에 한해 다음과 같이 구현할 수 있습니다. 이때 @a가 변수가 되고 set이 변수에 대입하는 명령이 됩니다.
mysql>set @a =(select min(a) from sample54);
mysql>delete from sample54 where a= @a;
*/

-- 스칼라 값
-- 서브쿼리의 패턴
-- 하나의 값을 반환하는 패턴
select min(a) from sample54;
-- 복수의 행이 반환되지만 열은 하나인 패턴
select no from sample54;
-- 하나의 행이 반환되지만 열이 복수인 패턴
select min(a),max(no) from sample54;
-- 복수의 행 복수의 열이 반환되는 패턴
select nom, a from sample54;
-- select 명령이 하나의 값만 반환하는 것을 '스칼라 값을 반환한다'고 한다!
delete from sample54 where a = (select min(a) from sample54); -- =연산자를 사용하여 비교할 경우에는 스칼라 값끼리 비교할 필요가 있다.
-- select 구에서 서브쿼리 사용하기
select
	(select count(*) from sample51) as sq1,
    (select count(*) from sample54) as sq2;  -- from 구 생략 

-- set 구에서 서브쿼리 사용하기
update sample54 set a = (select max(a) from sample64);
-- from 구에서 서브쿼리 사용하기
select * from (select * from sample54) sq;
/* select 명령 안에 select 명령이 들어있는 구조를 네스티드 구조,또는 중첩구조 내포구조라 부릅니다.
뒤에 sq는 별명입니다 from 구에서는 테이블이나 서브쿼리에 별명을 붙일 수 있습니다.
*/
-- from 구에서 서브쿼리 사용하기(as지정)
select * from (select * from sample54) as sq;

-- insert 명령과 서브쿼리
-- values 구에서 서브쿼리 사용하기
insert into sample541 values (
	(select count(*) from sample51),
    (select count(*) from sample54)
);
select * from sample541;

-- insert select 
-- select 결과를 insert 하기
insert into sample541 select 1,2;
select * from sample541;
-- 테이블의 행 복사하기 
insert into sample542 select *from sample543;

-- 상관 서브쿼리
/* exists
exists (select명령)
*/
 -- exists 서브쿼리를 사용해 검색할 때 '데이터가 존재하는지 아닌지' 판별하기 위해 조건을 지정할 수도 있습니다.
 select * from sample551;
 select * from sample552;
 -- exists를 사용해 '있음'으로 갱신하기
 update sample551 set a = '있음' where
	exists (select * from sample552 where no2 = no);
select * from sample551;
-- not exists를 사용해 '없음'으로 갱신하기
update sample551 set a = '없음' where
  not exists (select * from sample552 where no2 = no);
select * from sample551;

-- 상관 서브쿼리
/* 상관 서브쿼리에서는 부모 명령과 연관되어 처리되기 때문에 서브쿼리 부분만을 따로 떼어내어 실행시킬 수 없습니다.
update sample551 set a= '있음' where
 exists (select*from sample 552 where no2=no);
 
 select * from sample552 where no2=no;
 -> 에러 : no2가 불명확하다.
 */
 -- 테이블명 붙이기
 -- 열에 테이블명 붙이기
 update sample551 set a = '있음' where
	exists (select * from sample552 where sample552.no2 = sample552.no);
-- in
/* 스칼라 값끼리 비교할 때는 = 연산자를 사용합니다. 다만 집합을 비교할 때는 사용할 수 없습니다.
in을 사용하면 집합 안의 값이 존재하는지를 조사할 수 있습니다. */

-- in을 사용해 조건식 기술
select * from sample551 where no in (3,5);
-- in의 오른쪽을 서브쿼리로 지정하기
select * from sample551 where no in
	(select no2 from sample552);

-- 뷰 작성과 삭제
/* 뷰의 작성 및 삭제
create view 뷰명 select 명령
drop view 뷰명
*/
-- from 구에 기술된 서브쿼리에 이름을 붙이고 데이터 베이스 객체화하여 쓰기 쉽게한 것을 뷰라고 한다.
-- 뷰는 select 명령을 기록하는 데이터베이스 객체이다.
-- 뷰를 작성하는 것으로 복잡한 select 명령을 간략하게 표현할 수 있다.
-- 가상테이블 뷰는 테이블처럼 취급할 수 있지만 '실체가 존재하지 않는다' 라는의미로 가상테이블이라고도 합니다.
-- 뷰 작성과 삭제 (작성 create view 삭제 drop view)
-- 뷰 작성하기
create view sample_view_67 as select * from sample54;
select * from sample_view_67;
-- create view에서 열 지정하기
create view sample_view_672(n,v,v2)as
	select no, a, a*2 from sample54;
select * from sample_view_672 where n = 1;
-- 뷰 삭제하기
drop view sample_view_67;