출처| https://www.youtube.com/watch?v=UVY0mfa4VP0&list=PLqTUMsvO70nk8WfCyU-IPmc85390CaSqM&index=1
https://cafe.naver.com/thisisMySQL
https://velog.io/@ong_hh/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EA%B5%AC%EC%B6%95
테이블과 동일하게 사용하는 개체다.
뷰는 한 번 생성해 놓으면 테이블이라고 생각하고 사용해도 될 정도로 사용자들의 입장에서는 테이블과 거의 동일한 개체로 여겨진다.
뷰를 생성하는 구문은 다음과 같다.
use tabledb;
create view v_usertbl as
select userid, name, addr from usertbl;
select * from v_usertbl -- 뷰를 데이블이라고 생각해도 무방
보안에 도움이 된다.
복잡한 쿼리를 단순화 시켜 줄 수 있다.
SELECT U.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM usertbl U
INNER JOIN buytbl B
ON U.userid = B.userid ;
---------------------------------------------------
CREATE VIEW v_userbuytbl
AS
SELECT U.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM usertbl U
INNER JOIN buytbl B
ON U.userid = B.userid ;
---------------------------------------
SELECT * FROM v_userbuytbl WHERE name = '김범수';
USE sqlDB;
CREATE VIEW v_userbuytbl
AS
SELECT U.userid AS 'USER ID', U.name AS 'USER NAME', B.prodName AS 'PRODUCT NAME',
U.addr, CONCAT(U.mobile1, U.mobile2) AS 'MOBILE PHONE'
FROM usertbl U
INNER JOIN buytbl B
ON U.userid = B.userid;
SELECT `USER ID`, `USER NAME` FROM v_userbuytbl; -- 주의! 백틱을 사용한다.
ALTER VIEW v_userbuytbl
AS
SELECT U.userid AS '사용자 아이디', U.name AS '이름', B.prodName AS '제품 이름',
U.addr, CONCAT(U.mobile1, U.mobile2) AS '전화 번호'
FROM usertbl U
INNER JOIN buytbl B
ON U.userid = B.userid ;
SELECT `이름`,`전화 번호` FROM v_userbuytbl;
DROP VIEW v_userbuytbl;
USE sqlDB;
CREATE OR REPLACE VIEW v_usertbl
AS
SELECT userid, name, addr FROM usertbl;
DESCRIBE v_usertbl;
SHOW CREATE VIEW v_usertbl;
UPDATE v_usertbl SET addr = '부산' WHERE userid='JKW' ;
INSERT INTO v_usertbl(userid, name, addr) VALUES('KBM','김병만','충북') ;
CREATE VIEW v_sum
AS
SELECT userid AS 'userid', SUM(price*amount) AS 'total'
FROM buytbl GROUP BY userid;
SELECT * FROM v_sum;
SELECT * FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'sqldb' AND TABLE_NAME = 'v_sum';
CREATE VIEW v_height177
AS
SELECT * FROM usertbl WHERE height >= 177 ;
SELECT * FROM v_height177 ;
DELETE FROM v_height177 WHERE height < 177 ;
INSERT INTO v_height177 VALUES('KBM', '김병만', 1977 , '경기', '010', '5555555', 158, '2023-01-01') ;
ALTER VIEW v_height177
AS
SELECT * FROM usertbl WHERE height >= 177
WITH CHECK OPTION ;
INSERT INTO v_height177 VALUES('WDT', '서장훈', 2006 , '서울', '010', '3333333', 155, '2023-3-3') ;
CREATE VIEW v_userbuytbl
AS
SELECT U.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS mobile
FROM usertbl U
INNER JOIN buytbl B
ON U.userid = B.userid ;
INSERT INTO v_userbuytbl VALUES('PKL','박경리','운동화','경기','00000000000','2023-2-2');
DROP TABLE IF EXISTS buytbl, usertbl;
SELECT * FROM v_userbuytbl;
CHECK TABLE v_userbuytbl;
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'innodb_data_file_path';
CREATE TABLESPACE ts_a ADD DATAFILE 'ts_a.ibd';
CREATE TABLESPACE ts_b ADD DATAFILE 'ts_b.ibd';
CREATE TABLESPACE ts_c ADD DATAFILE 'ts_c.ibd';
USE sqldb;
CREATE TABLE table_a (id INT) TABLESPACE ts_a;
CREATE TABLE table_b (id INT);
ALTER TABLE table_b TABLESPACE ts_b;
CREATE TABLE table_c (SELECT * FROM employees.salaries);
ALTER TABLE table_c TABLESPACE ts_c;
DROP TABLE table_c ;
CREATE TABLE table_c (SELECT * FROM employees.salaries);
ALTER TABLE table_c TABLESPACE ts_c;