데이터 베이스(1)

데이터베이스 - 데이터 저장 
테이블 - 2차원 구조의 표 
SQL  - 테이블 제어 명령어

---

DDL(데이터 정의어), DML(데이터 조작어), DCL(데이터 제어어)
- DDL

• CREATE : 스키마, 도메인, 테이블, 뷰, 인덱스 등을 정의한다.
• ALTER : 테이블에 대한 정의를 변경하는데 사용된다.
• DROP : 테이블, 스키마 등 삭제

(스키마 만드는 방법)
CREATE SCHEMA [스키마 명] AUTHORIZATION 사용자id;
(도메인 만드는 방법)
CREATE DOMAIN [도메인 명] [데이터 타입] (Default 기본 값); 
ex) CREATE DOMAIN 주소 CHAR(10) DEFAULT '대전';
(테이블 만드는 법)
CREATE TABLE 테이블 명(
            속성명 데이터타입 [NOT NULL], ---
            [, PRIMARY KEY 기본키 속성명],
            [, FOREIGN KEY 외래키속성명 REFERENCES 참조테이블(기본키속성명)]
            [, CONSTRAINT 제약조건명] [CHECK (조건식)]
);
뷰 : 사용자에게 접근이 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 기본 테이블로부터 유도된 이름을 가지는 가상 테이블
(뷰 만드는 법)
CREATE VIEW 뷰명[속성명, --] AS SELECT 문;
ex) CREATE VIEW 이천고객(성명, 전화번호) AS SELECTE 성명, 전화번호 FROM 고객 WHERE 주소 = '이천';
 
(인덱스 만드는 법)
CREATE INDEX 인덱스 명 ON 테이블 명 (속성명[ASC/DESC] ) [CLUSTER];

• ASC : 오름차순
• DESC : 내림차순
• CLUSTER : 인덱스가 클러스터드 인덱스로 설정됨 
• 클러스터드 인덱스 : 인덱스 키의 순서에 따라 데이터가 정렬
• 넌 클러스터드 인덱스 : 인덱스의 키 값만 정렬, 데이터 정렬X

ex) CREATE INDEX 고객번호_inx ON 고객(고객번호 DESC) 
(테이블 추가)
ALTER TABLE [테이블 명] ADD COLUMN [속성 명] [데이터 타입];
(속성명 변경)
ALTER TABLE [테이블 명] ALTER COLUMN 속성명;
(속성 삭제)
ALTER TABLE [테이블 명] DROP COLUMN  속성명;
(테이블, 인덱스, 뷰 등 삭제)
DROP (테이블,인덱스, 뷰 등) (테이블,인덱스, 뷰 등 이름) {RESTIRCT | CASCADE};

• RESTRICT : 다른 곳에서 참조하고 있으면 삭제가 취소된다.
• CASCADE : 제거하는 요소를 참조하는 모든 개체를 모두 제거.

 

---

- DML 

• SELECT : 테이블에서 조건에 맞는 튜플을 검색
• INSERT : 테이블에 새로운 튜플 삽입
• DELETE : 테이블에서 조건에 맞는 튜플 삭제
• UPDATE : 테이블에서 조건에 맞는 튜플의 내용을 변경

(테이블이 1개 일 시)
SELECT 속성 명 FROM 테이블 명 ;
(* 테이블이 여러개 일 시)
SELECT [테이블 1. 속성명], [테이블 2. 속성명] FROM [테이블 1의 테이블 명] [테이블 2의 테이블 명] WHERE (테이블1과 2가 서로 대응되는 값이 조건으로 들어감);
(중복 제거)
SELECT [DISTINCT]  속성 명 FROM 테이블 명
UPDATE [테이블 명] SET [속성 명] = [속성에 들어갈 값] (LIMIT : 리밋을 걸어줄 시 제한해서 그 값을 넣음 );
INSERT INTO [테이블 명] VALUES ('-----');
INSERT INTO [테이블 명] SET [속성 명] = [속성에 들어갈 값 지정];
(나머지는 밑의 문제에서 정리)

---

- DCL

• COMMIT : 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려줌 
• ROLLBACK : 데이터베이스 조작 작업이 비장적으로 종료되었을 때 원래의 상태로 복구 
• GRANT : 사용자에게 권한을 부여 
• REVOKE : 사용자의 사용권한을 취소

(검색 권한 부여)
GRANT SELECT ON 테이블 명 TO 사용자;
ex)
GRANT SELECT ON EMP TO 청춘고양이;
(모든 권한 부여)
GRANT ALL ON EMP TO 청춘고양이;
(검색 권한 회수)
REVOKE SELECT ON 테이블 명 FROM 사용자;
ex) 
REVOKE SELECT ON EMP FROM 청춘고양이;
(저장하는 곳 지정 - 롤백 시에 사용)
ex)
SAVEPOINT  S1;
DLETE FROM EMP 사원번호 = 20;
-
-
-
ROLLBACK S1; - 이렇게 사용시 사원번호 20인 것을 지워라 하기 전으로 돌아감. 

---

 SQL 문제
밑에 해당 SQL을 작성.
DROP DATABASE IF EXISTS scott;
CREATE DATABASE scott;
USE scott;
CREATE TABLE DEPT (
DEPTNO DECIMAL(2),
DNAME VARCHAR(14),
LOC VARCHAR(13),
CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
);
CREATE TABLE EMP (
EMPNO DECIMAL(4),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR DECIMAL(4),
HIREDATE DATE,
SAL DECIMAL(7,2),
COMM DECIMAL(7,2),
DEPTNO DECIMAL(2),
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);
CREATE TABLE SALGRADE (
GRADE TINYINT,
LOSAL SMALLINT,
HISAL SMALLINT
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,STR_TO_DATE('17-12-1980','%d-%m-%Y'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,STR_TO_DATE('20-2-1981','%d-%m-%Y'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,STR_TO_DATE('22-2-1981','%d-%m-%Y'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,STR_TO_DATE('2-4-1981','%d-%m-%Y'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,STR_TO_DATE('28-9-1981','%d-%m-%Y'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,STR_TO_DATE('1-5-1981','%d-%m-%Y'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,STR_TO_DATE('9-6-1981','%d-%m-%Y'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,STR_TO_DATE('13-7-1987','%d-%m-%Y')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,STR_TO_DATE('17-11-1981','%d-%m-%Y'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,STR_TO_DATE('8-9-1981','%d-%m-%Y'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,STR_TO_DATE('13-7-1987', '%d-%m-%Y'),1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,STR_TO_DATE('3-12-1981','%d-%m-%Y'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,STR_TO_DATE('3-12-1981','%d-%m-%Y'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,STR_TO_DATE('23-1-1982','%d-%m-%Y'),1300,NULL,10);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
#1. 사원 테이블의 모든 레코드를 조회하시오. 
SELECT * FROM EMP;
#2. 사원명과 입사일을 조회하시오.
SELECT ENAME, HIREDATE FROM EMP;
#3. 사원번호와 이름을 조회하시오.
SELECT EMPNO, ENAME FROM EMP;
#4. 사원테이블에 있는 직책의 목록을 조회하시오. (hint : distinct, group by)
SELECT DISTINCT JOB FROM EMP ;
#5. 총 사원수를 구하시오. (hint : count)
SELECT COUNT(*) AS `사원` FROM EMP;
#6. 부서번호가 10인 사원을 조회하시오.
SELECT ENAME FROM EMP WHERE DEPTNO = 10; 
#7. 월급여가 2500이상 되는 사원을 조회하시오.
SELECT ENAME FROM EMP WHERE SAL >= 2500;
#8. 이름이 'KING'인 사원을 조회하시오.
SELECT ENAME AS `사원` FROM EMP WHERE ENAME = 'KING';
#9. 사원들 중 이름이 S로 시작하는 사원의 사원번호와 이름을 조회하시오. (hint : like)
SELECT EMPNO AS `사원번호`, ENAME AS `이름` FROM EMP WHERE ENAME LIKE 'S%';
#10. 사원 이름에 T가 포함된 사원의 사원번호와 이름을 조회하시오. (hint : like)
SELECT EMPNO AS `사원번호`, ENAME AS `이름` FROM EMP WHERE ENAME LIKE '%T%'; 
#11. 커미션이 300, 500, 1400 인 사원의 사번,이름,커미션을 조회하시오. (hint : OR, in )
SELECT EMPNO AS `사원번호`, ENAME AS `이름`, COMM AS `커미션` FROM EMP WHERE COMM = '300' OR COMM = '500' OR COMM = '1400';
#12. 월급여가 1200 에서 3500 사이의 사원의 사번,이름,월급여를 조회하시오. (hint : AND, between)
SELECT EMPNO AS `사원번호`, ENAME AS `이름`, SAL AS `월급여` FROM EMP WHERE SAL BETWEEN '1200' AND '3500';
#13. 직급이 매니저이고 부서번호가 30번인 사원의 이름,사번,직급,부서번호를 조회하시오.
SELECT EMPNO AS `사원번호`, ENAME AS `이름`, JOB AS `직급` , DEPTNO AS `부서번호` FROM EMP WHERE JOB = 'MANAGER' AND DEPTNO = '30';
#14. 부서번호가 30인 아닌 사원의 사번,이름,부서번호를 조회하시오. (not)
SELECT EMPNO AS `사원번호`, ENAME AS `이름`, JOB AS `직급` , DEPTNO AS `부서번호` FROM EMP WHERE NOT DEPTNO = '30';
#15. 커미션이 300, 500, 1400 이 모두 아닌 사원의 사번,이름,커미션을 조회하시오. (hint : not in)
SELECT EMPNO AS `사원번호`, ENAME AS `이름`, COMM AS `커미션` FROM EMP WHERE COMM NOT IN ('300', '500', '1400');
#16. 이름에 S가 포함되지 않는 사원의 사번,이름을 조회하시오. (hint : not like)
SELECT EMPNO AS `사원번호`, ENAME AS `이름` FROM EMP WHERE ENAME NOT LIKE '%S%';
#17. 급여가 1200보다 미만이거나 3700 초과하는 사원의 사번,이름,월급여를 조회하시오. (hint : not, between)
SELECT EMPNO AS `사원번호`, ENAME AS `이름`, SAL AS `월급여` FROM EMP WHERE SAL NOT BETWEEN '1200' AND '3700';
#18. 직속상사가 NULL 인 사원의 이름과 직급을 조회하시오. (hint : is null, is not null)
SELECT ENAME AS `이름`, JOB AS `직급` , DEPTNO AS `부서번호` FROM EMP WHERE MGR IS NULL;
#19. 부서별 평균월급여를 구하는 쿼리 (hint : group by, avg())
SELECT AVG(SAL) AS `월급여`, JOB FROM EMP GROUP BY DEPTNO;
#20. 부서별 전체 사원수와 커미션을 받는 사원들의 수를 구하는 쿼리 (hint : group by, count())
SELECT DEPTNO, COUNT(*), COUNT(IF(COMM=0,NULL,COMM)) FROM EMP GROUP BY DEPTNO;
SELECT DEPTNO, COUNT(*), COUNT(COMM) FROM EMP GROUP BY DEPTNO;
#21. 부서별 최대 급여와 최소 급여를 구하는 쿼리 (hint : group by, min(), max())
SELECT DEPTNO, MAX(SAL) AS `최대 급여`, MIN(SAL) AS `최소 급여` FROM EMP  GROUP BY DEPTNO;
#22. 부서별로 급여 평균 (단, 부서별 급여 평균이 2000 이상만) (hint : group by, having)
SELECT DEPTNO, AVG(SAL) AS `급여 평균` FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >=2000;
#23. 월급여가 1000 이상인 사원만을 대상으로 부서별로 월급여 평균을 구하라. 단, 평균값이 2000 이상인 레코드만 구하라. (hint : group by, having)
SELECT DEPTNO, AVG(SAL) AS `급여 평균` FROM EMP WHERE SAL >= 1000 GROUP BY DEPTNO HAVING AVG(SAL) >=2000;
#24. 월급여를 많이 받는 순으로 조회하시오.
SELECT *FROM EMP ORDER BY SAL DESC;

---

드래그 할 시 에 정답이 나옵니다.