-- 형식 delete 테이블명 -2023-10-20
-- delete from 테이블명 where 조건절
-- 모든테이블 목록조회
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'hrdb2019'
-- emp_emp 테이블의 데이터 조회
SELECT * FROM emp_emp
-- emp_id 홍길똥1인 사원을 삭제
DELETE FROM emp_emp WHERE emp_name ='홍길똥1';
SELECT * FROM emp_emp ee
-- emp_name이 테스트인 모든 사원을 삭제
DELETE FROM emp_emp WHERE emp_name = '테스트'
-- 모든사원삭제
DELETE FROM emp_emp;
DROP TABLE emp_emp ;
SELECT * FROM information_schema.tables WHERE TABLE_NAME LIKE 'emp%';
CREATE TABLE emp_sys
as
SELECT * FROM employee e WHERE dept_id ='SYS';
SELECT * FROM emp_sys;
DELETE FROM emp_sys WHERE emp_id = 's0001'
ROLLBACK;
COMMIT; -- 현재의 작업까지 완료--> db에 저장
SELECT * FROM emp_sys;
-- 최사모 사원의 입사일을 현재날짜로 수정
UPDATE emp_sys SET hire_date = curdate() WHERE emp_name = '최사모';
ROLLBACK; -- 나는 현재 auto 커밋 설정이 되어있기 때문에 롤백으로 수정작업이 안됨
UPDATE emp_sys SET hire_date = curdate() WHERE emp_name = '한국인';
-- 테이블의 데이터 절삭 : TRUNCATE TABLE 테이블명;
SELECT * FROM EMP_SYS;
COMMIT;
DELETE FROM EMP_SYS; -- 전체행삭제
ROLLBACK; -- DELETE 전으로 롤백
TRUNCATE TABLE EMP_SYS; -- ROLLBACK이 불가능한 행 영구삭제
-- ** DML에 속하는 INSERT(c),SELECT(R) UPDATE(U) DELETE(D) 쿼리는 SET AUTOCOMIIT설정에 영향을 받으므로
-- 현재 트랜잭션 설정을 확인
/*
* 자동 행번호 생성 : auto_increment
* 형식 : 테이블 생성시 기본키 컬럼(중복x,nullx)에 데이터 타입 전후에 입력
* -- 기본키를 별도로 입력받지 않는 테이블에서 기본키의 역할을 하도록 정의
*
* */
-- 테이블 생성 : emp
SELECT * FROM information_schema.tables
USE 'hrdb2019';
CREATE TABLE BOARD(
BID INT AUTO_INCREMENT PRIMARY KEY,
BTITLE VARCHAR(50) NOT NULL,
BCONTENT VARCHAR(200),
BDATE DATE
);
INSERT INTO board (BTITLE,BCONTENT,BDATE)
VALUES('TEST','CONTENT TEST',CURDATE() );
DROP TABLE board
SELECT * FROM board
SELECT last_insert_id() FROM BOARD -- AUTO_INCREMENT로 반영된 마지막으로 된 정보를 알려줌
DELETE FROM board WHERE BID = 4
-- BID를 문자열+숫자 : 'B001'
DROP TABLE board2;
CREATE TABLE BOARD2(
BID CHAR(4) PRIMARY KEY,
BTITLE VARCHAR(50) NOT NULL,
BCONTENT VARCHAR(200),
BDATE DATE
)
CREATE TABLE BNUMBER(
ID INT AUTO_INCREMENT PRIMARY KEY
);
INSERT INTO BNUMBER VALUES()
SELECT * FROM BNUMBER
INSERT INTO board2(BTITLE,BCONTENT,BDATE)
VALUES(CONCAT('TITLE','CONTENT',CURDATE()
)
SELECT *FROM board2 ;
SELECT CONCAT('B',last_insert_id()+1) FROM DUAL;
SELECT uuid()
-- 프로시저 : 데이터베이스 함수 - BOARD2 테이블에 INSERT 명령이 실행되면 TG_TABLE1_INSERT 프로시저 호출
-- BOARD2 INSERT 호출 > TG_TABLE1_INSERT 프로시저 호출 > BNUMBER 테이블의 ID 값 반환
CREATE TRIGGER TG_TABLE1_INSERT
BEFORE INSERT ON BOARD2
FOR EACH ROW BEGIN
INSERT INTO BNUMBER VALUES (NULL);
SET NEW.BID = concat('ABC',LPAD(last_insert_id(),5,'0') );
END$$
DELIMITER;
DROP TABLE BOARD2;
-- 테이블생성
-- bid를 문자열 숫자 'ABC00001'
CREATE TABLE BOARD2(
BID CHAR(10) PRIMARY KEY,
BTITLE VARCHAR(50) NOT NULL,
BCONTENT VARCHAR(200),
BDATE DATE
)
CREATE TABLE bnumber(
id int AUTO_INCREMENT PRIMARY key
)
-- BOARD3 테이블 생성하고, BOARD3 테이블의 BID 값은 'B_0001' 형식으로 생성
-- BOARD3_SEQU 테이블을 생성하여 BID 마지막에 들어가는 숫자를 자동으로 반환하도록 함
-- BOARD3과 BOARD3_SEQU 테이블을 연결하는 TG_BOARD3_INSERT 트리거 프로시저를 생성
CREATE TABLE BOARD3(
BID VARCHAR(10) PRIMARY KEY,
BTITLE VARCHAR(50) NOT NULL,
BCONTENT VARCHAR(200) ,
BDATE DATE
)
INSERT INTO board3(BTITLE,BCONTENT,BDATE)
values(
'title','content',curdate()
)
SELECT * FROM board3_sequ
INSERT INTO board3_sequ values()
CREATE TABLE BOARD3_SEQU(
id int AUTO_INCREMENT PRIMARY key
)
SELECT * FROM information_SCHEMA.tables WHERE table_name LIKE 'boa%'
DELIMITER $$
CREATE TRIGGER tg_board3_insert
BEFORE INSERT ON board3
FOR EACH ROW
BEGIN
INSERT INTO BOARD3_SEQU VALUES (NULL);
SET NEW.BID = CONCAT('a_', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;
SHOW TRIGGERs;
DROP TRIGGER IF EXISTS tg_BOARD3_insert;
INSERT INTO board3(BTITLE,BCONTENT,BDATE)
values(
'title','content',curdate()
)
SELECT * FROM BOARD3;
-- 행번호 생성 : row_number() over (정렬 컬럼)
-- 오라클 : rownum과 order by 를 함께 사용 불가
-- employee 텡이블의 연봉 기준으로 정렬한 후 행번호를 함께 출력
SELECT ROW_NUMBER() OVER(ORDER BY salary desc) AS rno FROM employee ORDER BY salary DESC;
-- 정보시스템 부서의 사원들의 아이디, 사원명,입사일, 연봉 그리고 소속본부를 출력
-- 단 최종데이터에 행번호 출력
SELECT ROW_NUMBER () OVER(ORDER BY e.emp_id) AS rno,e.emp_id ,e.emp_name ,e.hire_date ,e.salary ,u.unit_name
FROM employee e INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN unit u ON u.unit_id = d.unit_id
WHERE d.dept_name ='정보시스템';
SELECT ROW_NUMBER ()OVER(ORDER BY e.emp_id) AS RNO,e.emp_id ,e.emp_name ,e.hire_date ,e.salary ,u.unit_name
FROM employee e ,department d ,unit u
WHERE e.dept_id =d.dept_id
AND d.unit_id = u.unit_id
AND d.dept_name = '정보시스템';
-- 휴가사용 이유가 '두통'인 사원들 중에 영업부서인 사원의 사원명 폰번호 부서명 휴가사용이유 조회
-- 최종 데이터 출력 시 행번호 추가
SELECT ROW_NUMBER ()OVER(ORDER BY e.emp_name) AS rno,e.emp_name,e.phone ,d.dept_name,v.reason
FROM employee e
INNER JOIN department d ON e.dept_id = d.dept_id
INNER JOIN vacation v ON v.emp_id = e.emp_id
WHERE d.dept_name = '영업'
AND v.reason = '두통';
-- 휴가를 간 적이 있는 정보시스템 부서의 사원들을 출력
-- 최종데이터 출력 시 행번호 추가
-- 실행한 sql 쿼리를 > sys_view 이름으로 생성
CREATE VIEW sys_view
as
SELECT concat('w',ROW_NUMBER ()OVER(ORDER BY e.emp_id)) AS RNO,e.emp_id FROM employee e
WHERE e.dept_id = (SELECT d.dept_id FROM department d WHERE d.dept_name='정보시스템')
AND e.emp_id in (SELECT v.emp_id FROM vacation v);
-- 뷰(view) : 논리적인 테이블로 SQL을 실행하여 생성되는 가상의 테이블
-- 형식 : create view [view 이름]
-- AS
-- 서브쿼리 (select ~~)
-- 모든 뷰의 목록을 조회
SELECT * FROM information_schema.views WHERE TABLE_SCHEMA = 'hrdb2019' ;
-- employee 테이블의 가상테이블 emp_view 생성
CREATE VIEW emp_view
AS
SELECT * FROM employee;
SELECT * FROM emp_view;
SELECT * FROM sys_view
CREATE VIEW emp_view2
as
SELECT e.emp_id ,e.emp_name ,e.hire_date ,d.dept_name ,u.unit_name
FROM employee e INNER JOIN department d INNER JOIN unit u
ON e.dept_id = d.dept_id
AND u.unit_id = d.unit_id;
CREATE VIEW emp_view
as
SELECT e.emp_id ,e.emp_name ,e.hire_date ,d.dept_name ,u.unit_name
FROM employee e INNER JOIN department d INNER JOIN unit u
ON e.dept_id = d.dept_id
AND u.unit_id = d.unit_id
WHERE LEFT (hire_date,4) BETWEEN '2014' AND '2015'
SELECT * FROM emp_view;
--
SELECT * FROM emp_view2 WHERE hire_date BETWEEN '2014-01-01' AND '2015-12-31';
SELECT * FROM emp_view2 WHERE hire_date BETWEEN '2017-01-01' AND '2018-12-31';
USE myshop2019
SELECT DATABASE ()
SELECT * FROM customer c -- agikim
SELECT * FROM order_header oh WHERE oh.customer_id = 'mdpark';
-- 박마당이라는 고객이 어떤상품을 주문했는지 조회
-- 상품의 카테고리 추가
-- 주문을 관리하는 사원정보 포함
CREATE VIEW MGM_PRODUCT
as
SELECT c.customer_id ,c.customer_name ,oh.order_id ,p.product_name ,sc.sub_category_name ,c2.category_name,e.employee_id ,e.employee_name
FROM customer c ,order_header oh ,order_detail od ,product p,sub_category sc ,category c2,employee e
WHERE c.customer_id = oh.customer_id
AND oh.order_id = od.order_id
AND od.product_id = p.product_id
AND p.sub_category_id =sc.sub_category_id
AND sc.category_id = c2.category_id
AND e.employee_id = oh.employee_id;
SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name ='mgm_product'
-- 최천사 고객이 주문한 상품 내역
SELECT * FROM mgm_product WHERE customer_name = '최천사';
-- 고객별 상품 주문한 건수
SELECT customer_name
FROM mgm_product
GROUP BY customer_id,customer_name