[Day 15 | MySQL] SubQuery, DML, Table and 과제

union all ==> 중복된 값도 같이 보여준다

union ==> 중복된 값은 제거하고 보여준다.

 

 

 

 

subquery : 

질문을 얼마나 짧게 질문을 쪼개느냐의 싸움. 

장점1. 안에서 쪼개서 질문을 만들다보면, 데이터가 안전하게 흘러나오는 것을 점검할 수 있음. 

장점2. 에러율이 떨어진다. 

장점3. 절대 늦어지지 않는다. 

 

 

SubQuery01.SQL

-- SubQuery01.sql -- 

smith 와 같은 job을 가진 직원들의 이름, job은? 

1) smith 의 job은?


SELECT JOB 
FROM emp 
WHERE (ENAME = 'SMITH');


2) CLERK job을 가진 직원들의 이름, job은 ?

SELECT ENAME, JOB
FROM EMP 
WHERE (JOB = 'CLERK');

-- 3) 결합 
SELECT ENAME, JOB
FROM EMP 
WHERE (JOB =(SELECT JOB 
			 FROM emp 
		 	 WHERE (ENAME = 'SMITH')));
		 	
		 	
-------------------------------------

-- smith와 같은 급여 등급을 가진 직원들의 이름,급여, 급여 등급은?
-- 1) 스미스 급여등급
SELECT s.grade 
from emp e, salgrade s 
where (e.sal BETWEEN s.losal and s.hisal)
and e.ename = 'smith';

-- 2) 그 급여등급 직원들 이름, 급여, 급여등급? 
SELECT e.ename , e.sal , s.grade 
from emp e, salgrade s 
where (s.grade = (SELECT s.grade 
				 from emp e, salgrade s 
				 where (e.sal BETWEEN s.losal and s.hisal)
				 and e.ename = 'smith'));




smith와 같은 부서인 직원들의 이름, 부서명은?

-- 1) 스미스 부서 
SELECT d.dname 
from emp e, dept d 
where e.deptno = d.deptno
and e.ename = 'smith'; 


-- 2)그 부서 직원들 이름, 부서명 

SELECT e.ename , d.dname 
from emp e , dept d 
where d.dname  = (SELECT d.dname 
				 from emp e, dept d 
				 where e.deptno = d.deptno
				 and e.ename = 'smith'); 
				
-- 2)그 부서 직원들 이름, 부서명(굳이 안드러나도 ) 
				
SELECT e.ename , d.dname 
from emp e , dept d 
where e.deptno = d.deptno
and (e.deptno  = (SELECT d.deptno 
				  from emp e 
				  where (e.ename = 'smith')));


				 
				 
				 
				 

				 
 -- smith와 같은 부서인 직원들의 이름, job은?

SELECT e.ename , e.job 
from emp e , dept d 
where (d.dname  = (SELECT d.dname 
				   from emp e, dept d 
				   where e.deptno = d.deptno
		 		   and e.ename = 'smith')); 
				
해설: 

SELECT ename, job
from emp 
where (deptno = (select deptno
				 from emp
				 where (ename = 'smith') ) );
				
				

전 직원의 평균 급여보다 급여를 많이 받는 직원들의 이름, 급여, 급여등급?

-- 1) 전 직원 평균 급여? 
SELECT AVG(e.sal) 
from emp e ;


2) 그 것보다 많이 받는 직원 이름, 급여, 급여 등급? 

SELECT e.ename , e.sal , s.grade 
from emp e , salgrade s 
where (e.sal BETWEEN s.losal and s.hisal)
having (e.sal > (SELECT AVG(e.sal) 
				 from emp e));	
				 
------------------------------------------


allen의 직속상관과 같은 상관을 모시는 직원들의
이름, 사번, 부서명, 직속상관이름은?
(단, allen 제외)


1)allen의 직속 상관 사번 ? -- 무엇을 선택할건지 고민하면서 보아야  

SELECT mgr 
from emp 
WHERE ename = 'allen';

2) 그 상관(사번)을 모시는 직원들 이름,  사번, 부서명, 직속상관? 

SELECT e1.ename , e1.empno , d.dname , e2.ename 
from emp e1, emp e2, dept d
where (e1.deptno = d.deptno)
and (e1.mgr = e2.empno )
and (e1.mgr = (SELECT mgr 
			   from emp 
			   WHERE ename = 'allen'));
			  
3) Allen 제외 
SELECT e1.ename , e1.empno , d.dname , e2.ename 
from emp e1, emp e2, dept d
where (e1.deptno = d.deptno)
and (e1.mgr = e2.empno )
and (e1.mgr = (SELECT mgr 
			   from emp 
			   WHERE ename = 'allen'))
and (e1.ename != 'allen');
			  
			  



sales 부서 직원들의 급여보다 많은 급여를 받는 직원들의
이름, 급여는?

1) sales 부서 직원들의 급여? 

SELECT e.sal
from dept d , emp e 
where(d.deptno = e.deptno)
and (d.dname = 'sales');


2) 많이 급여를 받는 직원들의 이름, 급여? 

SELECT ename, sal
from emp 
where (sal > x);

3) 결합; 

SELECT ename, sal
from emp e 
where (sal > all (SELECT e.sal	
  	   from dept d, emp e
  	   where ( d.deptno = e.deptno)
  	   and (d.dname = 'sales')));
	


sales 업무를 하는 직원들의 급여보다 많은 급여를 받는 직원들의 이름, 급여는? 

1) sales 업무 직원들 급여

SELECT e.sal  
from emp e 
where job = 'salesman';



SELECT ename, sal
from emp e 
where (sal > all (SELECT e.sal  
				  from emp e 
				  where job = 'salesman'));

1. 데이터 수정 자체는 위험하다 

2. 위치 수정할 때는, 일단 출력하고, 수정문으로 바꿔준다 (update / set) 

 

 

DML01.SQL

-- DML01.sql --



-- ---------------- INSERT ------------------------ 

SELECT * FROM dept2;

INSERT into dept2 (deptno, dname, loc) values(50, 'IT', 'SEOUL');

INSERT into dept2 ( dname, loc, deptno) values('OP', 'BUSAN', 60); -- 컬럼명을 적시하면 순서 바껴도 괜찮음 

INSERT into dept2 values (70, 'AD', 'ILSAN');
INSERT into dept2 values (80, 'MKT', 'Jeju'),
						 (90, 'DEV', 'Jeju');


SELECT * FROM dept2 d ;


desc dept; -- dept에 적혀있는 것을 정리해보자 

-- deptno int primary key
-- dname	varchar(20)
-- loc		varchar (20)
-- -----------------------




create table dept3 (deptno INT auto_increment primary key, 
					dname	varchar(20),
					loc		varchar(20));
				
				
insert into dept3 values(null, 'IT', 'SEOUL');
insert into dept3 values(null, 'OP', 'JEJU');
insert into dept3 values(null, 'AD', 'Busan');




alter table dpet3 auto_increment = 100;
insert into dept3 values(null, 'IT', 'SEOUL');
insert into dept3 values(null, 'OP', 'JEJU');
insert into dept3 values(null, 'AD', 'Busan');



set @@auto_increment_increment = 3;  -- 3씩 증가 


insert into dept3 values(null, 'IT', 'SEOUL');
insert into dept3 values(null, 'OP', 'JEJU');
insert into dept3 values(null, 'AD', 'Busan');


SELECT * from dept3;



-------------------------

-- Data 가 들어있는 테이블 복사 
create table dept4
select  * from dept;


SELECT * from dept4;
------------------------------------------

create table dept5 (deptno int primary key, 
					dname 	varchar(20),
					loc		varchar(20)); 
				
select * from dept5; 

-- 비어있는 table에 데이터만 복사

insert into dept5
select * from dept;

select * from dept5;


-----------------------
-- data 수정 : update

select * from dept2; 

--> 90번 부서의 위치를 LA로 수정. 

1) 일단 화면에 출력을 하고, 
select loc 
from dept2 
WHERE (deptno = 90);

2) 수정문으로 변경
update dept2 
set loc = 'LA' 
WHERE (deptno = 90);


SELECT * FROM DEPT2;



-- > 70, 90번 부서의 위치를 Jeju로 수정해주세요. 
SELECT loc 
from dept2
where (deptno = 70 or deptno = 90);

UPDATE dept2
set loc = 'Jeju'
where (deptno in(70, 90));


-- > 80번 부서 이름은 Admin, 위치는 Busan으로 수정해주세요.
SELECT d.dname , loc 
from dept2 d 
where d.deptno = 80;

UPDATE dept2 d 
set d.dname = 'Admin',  d.loc = 'Busan' 
where d.deptno = 80;

SELECT * from dept2 d ;


-- 데이터 삭제 : DELETE 

80번 부서를 삭제

select * 
from dept2 
where (deptno = 80);

DELETE from dept2 
where (deptno = 80);

SELECT * from dept2;


70번 부서, Jeju 위치인 부서를 삭제 : 

SELECT *
from dept2 
where deptno = 70 
and loc = 'Jeju';


select * 
from dept2 
where(deptno, loc) in ((70, 'jeju')); 

DELETE from dept2 
where deptno = 70 
and loc = 'Jeju';


delete 
from dept2 
where(deptno, loc) in ((70, 'jeju')); 


SELECT * from dept2;


-- emp에서 
-- clerk 업무를 하면서 research 부서인 직원과 
-- manager 업무를 하면서 accounting 부서인 직원들의 사번, 이름, job, 부서 출력. 

SELECT e.empno , e.ename , e.job , d.dname 
from emp e , dept d 
where(e.job, d.dname) in (('clerk', 'research'))
or (e.job, d.dname) in (('manager', 'accounting'));

select e.ename, e.empno, e.job, d.dname
from emp e, dept d
where (e.deptno = d.deptno)
 and  (e.job,d.dname) in (('clerk','Research') , ('manager','Accounting'));

 

 

숫자 데이터 형식: 

날짜와 시간 데이터 형식: 

기타데이터: 영화, 대본 등 데이터가 컬럼 하나에 저장 가능하다. 

 

변수는 내일 함수처리 할 때 몰아서 할 것.

 

 

p313

8.1 테이블

테이블의 생성 및 사용은 지금까지 계속 해왔다. 쿼리로 직접 만들어보는 것이 중요. 

p313 샘플 예를 가지고 이야기 해보자. 

만약 쇼핑몰을 만든다. 

 

그러면

1. 회원 테이블 

2. 구매 테이블

이건 쉽게 만드는 것이고, 

정식적으로 만들려면 모델링이라는 과정을 거쳐야 함. 

 

테이블 만들때,

이름명 무엇으로 할지, 컬럼명 무엇으로 할지, 데이터 타입 무엇으로 할지, Null값 처리 할지말지 

 

 

언어보다 더 빠르게 필요한건 DB 설계서(정의서) 

그 다음에 들어갈 것은 제약조건. 

 

제약조건: 

DB화한다는 것은: DBMS (DB Management system)

- 주 Data (행 단위 Record)

- DB Object(Table 관리) 

- 통신(TCP/IP) --> 누구나 동시에

- 보안(인증/인가) 

- 관리(백업/복원, 이중화) 

 

Table 생성할 때, 데이터의 무결성을 지켜야 한다.: <-- 데이터에 이상한 값이 들어오지 않도록 방어해주어야

1. DataType - 데이터 타입으로 무결성을 지켜야 (나이에 a가 들어가면 안됨, 상식적으로 이해할 수 있는 데이터)

2. 제약조건(Constraint) <-- 나이에 음수, 1000살 등이 있으면 안됨 

3. 참조 <-- 가입하지 않은 누군가가 물건을 산 흔적? 안됨. 

 

제약조건(Constraint)  :  5 + @개 

• PK (Primary Key) : 기본키 제약조건 ==> Unique + Null( X ) --> 테이블 당 하나 
• UK(Unique) :유니크 제약조건           ==> Unique + Null( O ) --> 테이블 당 n
• FK: 외래키                                         ==> 자기/다른 table unique 한 컬럼 참조
• CK(Check) :                                     ==> 범위(age between 1 and 120) , 열거 ( gender in ('M', 'F')
• NN : Not Null                                   ==> Null 허용 여부 

• DF(Default) : 기본값을 정의             ==> 값이 들어오지 않았을 때 기본값을 입력

제약조건도 이름을 줄 수 있다. (아래 Mob1 처럼) 

 

 

 

 

강사님 갠슬로 전송하기 ! 

 

과제 1. 

제출 시 네이밍:  [과제]_2021.11.29_홍길동_(select퀴즈) 

-- Q. Martin의 job 소속 직원들의 평균 급여보다 급여를 낮게 받는 직원들의 이름,job, 부서명, 급여, 급여등급, 상관이름, 상관부서명은?
​
-- 1) martin은 무슨JOB?  --> salesman
​
SELECT e1.job
from emp e1
where (e1.ename ='Martin');
​
​
​
-- 2)해당 job의 평균 급여는? --> 1400
​
SELECT AVG(e1.sal) 
from emp e1 
where e1.job = (SELECT e1.job
				from emp e1
				where (e1.ename ='Martin'));
​
​
-- 3) 그 급여보다 낮은 급여를 받고 있는 직원들의 이름,job, 부서명, 급여, 급여등급, 상관이름, 상관부서명은? (e1. 부하 , e2. 매니저)
​
SELECT e1.ename , d1.dname , e1.sal, s1.grade , e2.ename , d2.dname 
from emp e1, dept d1, salgrade s1, emp e2, dept d2
where (e1.mgr = e2.empno)
and (e1.sal BETWEEN s1.losal  and s1.hisal )
and ( e1.deptno = d1.deptno )
and ( e2.deptno = d2.deptno )
and (e1.sal  < (SELECT AVG(e1.sal) 
				from emp e1 
				where e1.job = (SELECT e1.job
								from emp e1
								where (e1.ename ='Martin'))));

과제 2
제출 시 네이밍:  [과제]_2021.11.29_홍길동_(Table 설계) 

 

테이블 설계. 웹 게시판까지 연결

테이블 설계도를 엑셀로 만들기 (위 예시 느낌처럼) 

회원 Table

게시판 Table

 

뭘 굉장히 많이 했는데 복습을 좀 해야겠다 

(｡•́︿•̀｡) 

 

스큐엘의 신이 되자 👼🏻