집 > 기사 > 데이터 베이스 > MySQL 뷰 관련 개념 및 적용 사례 분석

MySQL 뷰 관련 개념 및 적용 사례 분석

WBOY앞으로: 2023-05-31 11:25:321201검색

1. 데이터베이스 객체에 대한 간단한 이야기

2.

MySQL 뷰 관련 개념 및 적용 사례 분석

뷰는 모든 테이블 대신 테이블의 일부를 사용하는 데 도움이 될 수 있으며, 다른 한편으로는 사용자마다 다른 쿼리 뷰를 구성할 수도 있습니다. 예를 들어, 회사의 영업사원의 경우 데이터의 일부만 보여주고 싶지만 구매 가격과 같은 일부 특수 데이터는 제공되지 않습니다. 또 다른 예로, 직원 급여는 민감한 필드이므로 일정 수준 이상의 직원에게만 공개되며, 다른 사람의 쿼리 뷰에는 이 필드가 제공되지 않습니다.
뷰는 일종의 가상 테이블로, 그 자체로는 데이터가 없고 메모리 공간도 거의 차지하지 않는 SQL에서 중요한 개념입니다.
뷰는 기존 테이블을 기반으로 구축됩니다. 뷰가 구축되는 이러한 테이블을 기본 테이블이라고 합니다.
뷰 생성 및 삭제는 뷰 자체에만 영향을 미치며 해당 기본 테이블에는 영향을 미치지 않습니다. 그러나 뷰의 데이터가 추가, 삭제, 수정되면 데이터 테이블의 데이터도 이에 따라 변경되며 그 반대의 경우도 마찬가지입니다.
View는 사용자에게 기본 테이블 데이터를 제공하는 또 다른 형태의 표현입니다. 일반적으로 소규모 프로젝트의 데이터베이스에서는 뷰를 사용할 필요가 없지만, 대규모 프로젝트와 데이터 테이블이 상대적으로 복잡할 경우 뷰의 가치는 자주 쿼리되는 결과 집합을 가상 테이블에 넣는 데 도움이 됩니다. 사용 효율성을 향상시킵니다. 이해하고 사용하기가 매우 쉽습니다.

MySQL 뷰 관련 개념 및 적용 사례 분석

3. 뷰 작업과 관련된 SQL

Create view

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | TEMPTABLE}] VIEW 뷰 이름 [(필드 목록)]
AS
쿼리 문 [WITH [CASCADED|LOCAL] CHECK OPTION]

데이터베이스의 테이블 객체 및 뷰 객체 보기

SHOW TABLES;

뷰 구조 보기

DESC / DESCRIBE 뷰 이름;

뷰의 속성 정보 보기

SHOW TABLE STATUS LIKE '뷰 이름'G

뷰의 상세 정의 정보 보기

SHOW CREATE VIEW 뷰 이름;

뷰를 업데이트할 수 있도록 하려면, 각 뷰 행은 기본 테이블의 행에 대응해야 하며 관계는 일대일이어야 합니다. 또한 뷰 정의에서 다음 조건이 발생하면 뷰는 업데이트 작업을 지원하지 않습니다.
뷰 정의 시 "ALGORITHM = TEMPTABLE"을 지정한 경우 뷰는 INSERT 및 DELETE 작업을 지원하지 않습니다. 기본 테이블에 정의된 모든 값 포함 비어 있지 않고 기본값이 지정되지 않은 열의 경우 뷰는 INSERT 작업을 지원하지 않습니다.
뷰를 정의하는 SELECT 문에서 JOIN 조인트 쿼리를 사용하는 경우 뷰는 INSERT 및 DELETE 작업을 지원하지 않습니다.
뷰를 정의하는 SELECT 문 뒤의 필드 목록에 수학적 표현식이나 하위 쿼리가 사용되는 경우 뷰는 INSERT를 지원하지 않으며 UPDATE를 사용하는 필드 값도 지원하지 않습니다.
뷰를 정의하는 SELECT 문 다음에 DISTINCT 및 집계 함수를 사용하면 뷰는 INSERT, UPDATE, DELETE를 지원하지 않습니다. 뷰를 정의하는 문에 하위 쿼리가 포함되어 있고 하위 쿼리가 FROM 이후에 테이블을 참조하는 경우 뷰는 INSERT, UPDATE, DELETE를 지원하지 않습니다.
뷰 정의는 업데이트할 수 없는 뷰를 기반으로 합니다.
뷰 데이터는 업데이트가 가능하지만, 일반적으로 뷰는 가상 테이블로서 쿼리를 용이하게 하기 위해 주로 사용되며, 뷰 데이터를 업데이트하는 것은 권장하지 않습니다.
실제 데이터 테이블의 데이터를 연산하면 뷰 데이터의 변경이 완료됩니다.

CREATE OR REPLACE

VIEW 절 사용

뷰 수정뷰를 삭제하면 뷰 정의만 삭제되고 기본 테이블의 데이터는 삭제되지 않습니다. 뷰 삭제 구문은 다음과 같습니다. (참고: 뷰 a와 b를 기반으로 새 뷰 c가 생성됩니다. 뷰 a 또는 뷰 b가 삭제되면 뷰 c의 쿼리가 실패합니다. 해당 뷰 c를 삭제하거나 수동으로 수정, 그렇지 않으면 사용에 영향을 미칩니다. )

DROP VIEW IF EXISTS 뷰 이름;

4. 실제 SQL 보기

먼저 이 테이블에 대한 SQL 스크립트 코드는 다음과 같습니다. 아래에 주어진다.

/*Table structure for table `employees` */
 
DROP TABLE IF EXISTS `employees`;
 
CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL DEFAULT &#39;0&#39;,
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` varchar(10) NOT NULL,
  `salary` double(8,2) DEFAULT NULL,
  `commission_pct` double(2,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `emp_email_uk` (`email`),
  UNIQUE KEY `emp_emp_id_pk` (`employee_id`),
  KEY `emp_dept_fk` (`department_id`),
  KEY `emp_job_fk` (`job_id`),
  KEY `emp_manager_fk` (`manager_id`),
  CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `emp_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`),
  CONSTRAINT `emp_manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `employees` */
 
insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`hire_date`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`) values (100,&#39;Steven&#39;,&#39;King&#39;,&#39;SKING&#39;,&#39;515.123.4567&#39;,&#39;1987-06-17&#39;,&#39;AD_PRES&#39;,24000.00,NULL,NULL,90),(101,&#39;Neena&#39;,&#39;Kochhar&#39;,&#39;NKOCHHAR&#39;,&#39;515.123.4568&#39;,&#39;1989-09-21&#39;,&#39;AD_VP&#39;,17000.00,NULL,100,90),(102,&#39;Lex&#39;,&#39;De Haan&#39;,&#39;LDEHAAN&#39;,&#39;515.123.4569&#39;,&#39;1993-01-13&#39;,&#39;AD_VP&#39;,17000.00,NULL,100,90),(103,&#39;Alexander&#39;,&#39;Hunold&#39;,&#39;AHUNOLD&#39;,&#39;590.423.4567&#39;,&#39;1990-01-03&#39;,&#39;IT_PROG&#39;,9000.00,NULL,102,60),(104,&#39;Bruce&#39;,&#39;Ernst&#39;,&#39;BERNST&#39;,&#39;590.423.4568&#39;,&#39;1991-05-21&#39;,&#39;IT_PROG&#39;,6000.00,NULL,103,60),(105,&#39;David&#39;,&#39;Austin&#39;,&#39;DAUSTIN&#39;,&#39;590.423.4569&#39;,&#39;1997-06-25&#39;,&#39;IT_PROG&#39;,4800.00,NULL,103,60),(106,&#39;Valli&#39;,&#39;Pataballa&#39;,&#39;VPATABAL&#39;,&#39;590.423.4560&#39;,&#39;1998-02-05&#39;,&#39;IT_PROG&#39;,4800.00,NULL,103,60),(107,&#39;Diana&#39;,&#39;Lorentz&#39;,&#39;DLORENTZ&#39;,&#39;590.423.5567&#39;,&#39;1999-02-07&#39;,&#39;IT_PROG&#39;,4200.00,NULL,103,60),(108,&#39;Nancy&#39;,&#39;Greenberg&#39;,&#39;NGREENBE&#39;,&#39;515.124.4569&#39;,&#39;1994-08-17&#39;,&#39;FI_MGR&#39;,12000.00,NULL,101,100),(109,&#39;Daniel&#39;,&#39;Faviet&#39;,&#39;DFAVIET&#39;,&#39;515.124.4169&#39;,&#39;1994-08-16&#39;,&#39;FI_ACCOUNT&#39;,9000.00,NULL,108,100),(110,&#39;John&#39;,&#39;Chen&#39;,&#39;JCHEN&#39;,&#39;515.124.4269&#39;,&#39;1997-09-28&#39;,&#39;FI_ACCOUNT&#39;,8200.00,NULL,108,100),(111,&#39;Ismael&#39;,&#39;Sciarra&#39;,&#39;ISCIARRA&#39;,&#39;515.124.4369&#39;,&#39;1997-09-30&#39;,&#39;FI_ACCOUNT&#39;,7700.00,NULL,108,100),(112,&#39;Jose Manuel&#39;,&#39;Urman&#39;,&#39;JMURMAN&#39;,&#39;515.124.4469&#39;,&#39;1998-03-07&#39;,&#39;FI_ACCOUNT&#39;,7800.00,NULL,108,100),(113,&#39;Luis&#39;,&#39;Popp&#39;,&#39;LPOPP&#39;,&#39;515.124.4567&#39;,&#39;1999-12-07&#39;,&#39;FI_ACCOUNT&#39;,6900.00,NULL,108,100),(114,&#39;Den&#39;,&#39;Raphaely&#39;,&#39;DRAPHEAL&#39;,&#39;515.127.4561&#39;,&#39;1994-12-07&#39;,&#39;PU_MAN&#39;,11000.00,NULL,100,30),(115,&#39;Alexander&#39;,&#39;Khoo&#39;,&#39;AKHOO&#39;,&#39;515.127.4562&#39;,&#39;1995-05-18&#39;,&#39;PU_CLERK&#39;,3100.00,NULL,114,30),(116,&#39;Shelli&#39;,&#39;Baida&#39;,&#39;SBAIDA&#39;,&#39;515.127.4563&#39;,&#39;1997-12-24&#39;,&#39;PU_CLERK&#39;,2900.00,NULL,114,30),(117,&#39;Sigal&#39;,&#39;Tobias&#39;,&#39;STOBIAS&#39;,&#39;515.127.4564&#39;,&#39;1997-07-24&#39;,&#39;PU_CLERK&#39;,2800.00,NULL,114,30),(118,&#39;Guy&#39;,&#39;Himuro&#39;,&#39;GHIMURO&#39;,&#39;515.127.4565&#39;,&#39;1998-11-15&#39;,&#39;PU_CLERK&#39;,2600.00,NULL,114,30),(119,&#39;Karen&#39;,&#39;Colmenares&#39;,&#39;KCOLMENA&#39;,&#39;515.127.4566&#39;,&#39;1999-08-10&#39;,&#39;PU_CLERK&#39;,2500.00,NULL,114,30),(120,&#39;Matthew&#39;,&#39;Weiss&#39;,&#39;MWEISS&#39;,&#39;650.123.1234&#39;,&#39;1996-07-18&#39;,&#39;ST_MAN&#39;,8000.00,NULL,100,50),(121,&#39;Adam&#39;,&#39;Fripp&#39;,&#39;AFRIPP&#39;,&#39;650.123.2234&#39;,&#39;1997-04-10&#39;,&#39;ST_MAN&#39;,8200.00,NULL,100,50),(122,&#39;Payam&#39;,&#39;Kaufling&#39;,&#39;PKAUFLIN&#39;,&#39;650.123.3234&#39;,&#39;1995-05-01&#39;,&#39;ST_MAN&#39;,7900.00,NULL,100,50),(123,&#39;Shanta&#39;,&#39;Vollman&#39;,&#39;SVOLLMAN&#39;,&#39;650.123.4234&#39;,&#39;1997-10-10&#39;,&#39;ST_MAN&#39;,6500.00,NULL,100,50),(124,&#39;Kevin&#39;,&#39;Mourgos&#39;,&#39;KMOURGOS&#39;,&#39;650.123.5234&#39;,&#39;1999-11-16&#39;,&#39;ST_MAN&#39;,5800.00,NULL,100,50),(125,&#39;Julia&#39;,&#39;Nayer&#39;,&#39;JNAYER&#39;,&#39;650.124.1214&#39;,&#39;1997-07-16&#39;,&#39;ST_CLERK&#39;,3200.00,NULL,120,50),(126,&#39;Irene&#39;,&#39;Mikkilineni&#39;,&#39;IMIKKILI&#39;,&#39;650.124.1224&#39;,&#39;1998-09-28&#39;,&#39;ST_CLERK&#39;,2700.00,NULL,120,50),(127,&#39;James&#39;,&#39;Landry&#39;,&#39;JLANDRY&#39;,&#39;650.124.1334&#39;,&#39;1999-01-14&#39;,&#39;ST_CLERK&#39;,2400.00,NULL,120,50),(128,&#39;Steven&#39;,&#39;Markle&#39;,&#39;SMARKLE&#39;,&#39;650.124.1434&#39;,&#39;2000-03-08&#39;,&#39;ST_CLERK&#39;,2200.00,NULL,120,50),(129,&#39;Laura&#39;,&#39;Bissot&#39;,&#39;LBISSOT&#39;,&#39;650.124.5234&#39;,&#39;1997-08-20&#39;,&#39;ST_CLERK&#39;,3300.00,NULL,121,50),(130,&#39;Mozhe&#39;,&#39;Atkinson&#39;,&#39;MATKINSO&#39;,&#39;650.124.6234&#39;,&#39;1997-10-30&#39;,&#39;ST_CLERK&#39;,2800.00,NULL,121,50),(131,&#39;James&#39;,&#39;Marlow&#39;,&#39;JAMRLOW&#39;,&#39;650.124.7234&#39;,&#39;1997-02-16&#39;,&#39;ST_CLERK&#39;,2500.00,NULL,121,50),(132,&#39;TJ&#39;,&#39;Olson&#39;,&#39;TJOLSON&#39;,&#39;650.124.8234&#39;,&#39;1999-04-10&#39;,&#39;ST_CLERK&#39;,2100.00,NULL,121,50),(133,&#39;Jason&#39;,&#39;Mallin&#39;,&#39;JMALLIN&#39;,&#39;650.127.1934&#39;,&#39;1996-06-14&#39;,&#39;ST_CLERK&#39;,3300.00,NULL,122,50),(134,&#39;Michael&#39;,&#39;Rogers&#39;,&#39;MROGERS&#39;,&#39;650.127.1834&#39;,&#39;1998-08-26&#39;,&#39;ST_CLERK&#39;,2900.00,NULL,122,50),(135,&#39;Ki&#39;,&#39;Gee&#39;,&#39;KGEE&#39;,&#39;650.127.1734&#39;,&#39;1999-12-12&#39;,&#39;ST_CLERK&#39;,2400.00,NULL,122,50),(136,&#39;Hazel&#39;,&#39;Philtanker&#39;,&#39;HPHILTAN&#39;,&#39;650.127.1634&#39;,&#39;2000-02-06&#39;,&#39;ST_CLERK&#39;,2200.00,NULL,122,50),(137,&#39;Renske&#39;,&#39;Ladwig&#39;,&#39;RLADWIG&#39;,&#39;650.121.1234&#39;,&#39;1995-07-14&#39;,&#39;ST_CLERK&#39;,3600.00,NULL,123,50),(138,&#39;Stephen&#39;,&#39;Stiles&#39;,&#39;SSTILES&#39;,&#39;650.121.2034&#39;,&#39;1997-10-26&#39;,&#39;ST_CLERK&#39;,3200.00,NULL,123,50),(139,&#39;John&#39;,&#39;Seo&#39;,&#39;JSEO&#39;,&#39;650.121.2019&#39;,&#39;1998-02-12&#39;,&#39;ST_CLERK&#39;,2700.00,NULL,123,50),(140,&#39;Joshua&#39;,&#39;Patel&#39;,&#39;JPATEL&#39;,&#39;650.121.1834&#39;,&#39;1998-04-06&#39;,&#39;ST_CLERK&#39;,2500.00,NULL,123,50),(141,&#39;Trenna&#39;,&#39;Rajs&#39;,&#39;TRAJS&#39;,&#39;650.121.8009&#39;,&#39;1995-10-17&#39;,&#39;ST_CLERK&#39;,3500.00,NULL,124,50),(142,&#39;Curtis&#39;,&#39;Davies&#39;,&#39;CDAVIES&#39;,&#39;650.121.2994&#39;,&#39;1997-01-29&#39;,&#39;ST_CLERK&#39;,3100.00,NULL,124,50),(143,&#39;Randall&#39;,&#39;Matos&#39;,&#39;RMATOS&#39;,&#39;650.121.2874&#39;,&#39;1998-03-15&#39;,&#39;ST_CLERK&#39;,2600.00,NULL,124,50),(144,&#39;Peter&#39;,&#39;Vargas&#39;,&#39;PVARGAS&#39;,&#39;650.121.2004&#39;,&#39;1998-07-09&#39;,&#39;ST_CLERK&#39;,2500.00,NULL,124,50),(145,&#39;John&#39;,&#39;Russell&#39;,&#39;JRUSSEL&#39;,&#39;011.44.1344.429268&#39;,&#39;1996-10-01&#39;,&#39;SA_MAN&#39;,14000.00,0.40,100,80),(146,&#39;Karen&#39;,&#39;Partners&#39;,&#39;KPARTNER&#39;,&#39;011.44.1344.467268&#39;,&#39;1997-01-05&#39;,&#39;SA_MAN&#39;,13500.00,0.30,100,80),(147,&#39;Alberto&#39;,&#39;Errazuriz&#39;,&#39;AERRAZUR&#39;,&#39;011.44.1344.429278&#39;,&#39;1997-03-10&#39;,&#39;SA_MAN&#39;,12000.00,0.30,100,80),(148,&#39;Gerald&#39;,&#39;Cambrault&#39;,&#39;GCAMBRAU&#39;,&#39;011.44.1344.619268&#39;,&#39;1999-10-15&#39;,&#39;SA_MAN&#39;,11000.00,0.30,100,80),(149,&#39;Eleni&#39;,&#39;Zlotkey&#39;,&#39;EZLOTKEY&#39;,&#39;011.44.1344.429018&#39;,&#39;2000-01-29&#39;,&#39;SA_MAN&#39;,10500.00,0.20,100,80),(150,&#39;Peter&#39;,&#39;Tucker&#39;,&#39;PTUCKER&#39;,&#39;011.44.1344.129268&#39;,&#39;1997-01-30&#39;,&#39;SA_REP&#39;,10000.00,0.30,145,80),(151,&#39;David&#39;,&#39;Bernstein&#39;,&#39;DBERNSTE&#39;,&#39;011.44.1344.345268&#39;,&#39;1997-03-24&#39;,&#39;SA_REP&#39;,9500.00,0.25,145,80),(152,&#39;Peter&#39;,&#39;Hall&#39;,&#39;PHALL&#39;,&#39;011.44.1344.478968&#39;,&#39;1997-08-20&#39;,&#39;SA_REP&#39;,9000.00,0.25,145,80),(153,&#39;Christopher&#39;,&#39;Olsen&#39;,&#39;COLSEN&#39;,&#39;011.44.1344.498718&#39;,&#39;1998-03-30&#39;,&#39;SA_REP&#39;,8000.00,0.20,145,80),(154,&#39;Nanette&#39;,&#39;Cambrault&#39;,&#39;NCAMBRAU&#39;,&#39;011.44.1344.987668&#39;,&#39;1998-12-09&#39;,&#39;SA_REP&#39;,7500.00,0.20,145,80),(155,&#39;Oliver&#39;,&#39;Tuvault&#39;,&#39;OTUVAULT&#39;,&#39;011.44.1344.486508&#39;,&#39;1999-11-23&#39;,&#39;SA_REP&#39;,7000.00,0.15,145,80),(156,&#39;Janette&#39;,&#39;King&#39;,&#39;JKING&#39;,&#39;011.44.1345.429268&#39;,&#39;1996-01-30&#39;,&#39;SA_REP&#39;,10000.00,0.35,146,80),(157,&#39;Patrick&#39;,&#39;Sully&#39;,&#39;PSULLY&#39;,&#39;011.44.1345.929268&#39;,&#39;1996-03-04&#39;,&#39;SA_REP&#39;,9500.00,0.35,146,80),(158,&#39;Allan&#39;,&#39;McEwen&#39;,&#39;AMCEWEN&#39;,&#39;011.44.1345.829268&#39;,&#39;1996-08-01&#39;,&#39;SA_REP&#39;,9000.00,0.35,146,80),(159,&#39;Lindsey&#39;,&#39;Smith&#39;,&#39;LSMITH&#39;,&#39;011.44.1345.729268&#39;,&#39;1997-03-10&#39;,&#39;SA_REP&#39;,8000.00,0.30,146,80),(160,&#39;Louise&#39;,&#39;Doran&#39;,&#39;LDORAN&#39;,&#39;011.44.1345.629268&#39;,&#39;1997-12-15&#39;,&#39;SA_REP&#39;,7500.00,0.30,146,80),(161,&#39;Sarath&#39;,&#39;Sewall&#39;,&#39;SSEWALL&#39;,&#39;011.44.1345.529268&#39;,&#39;1998-11-03&#39;,&#39;SA_REP&#39;,7000.00,0.25,146,80),(162,&#39;Clara&#39;,&#39;Vishney&#39;,&#39;CVISHNEY&#39;,&#39;011.44.1346.129268&#39;,&#39;1997-11-11&#39;,&#39;SA_REP&#39;,10500.00,0.25,147,80),(163,&#39;Danielle&#39;,&#39;Greene&#39;,&#39;DGREENE&#39;,&#39;011.44.1346.229268&#39;,&#39;1999-03-19&#39;,&#39;SA_REP&#39;,9500.00,0.15,147,80),(164,&#39;Mattea&#39;,&#39;Marvins&#39;,&#39;MMARVINS&#39;,&#39;011.44.1346.329268&#39;,&#39;2000-01-24&#39;,&#39;SA_REP&#39;,7200.00,0.10,147,80),(165,&#39;David&#39;,&#39;Lee&#39;,&#39;DLEE&#39;,&#39;011.44.1346.529268&#39;,&#39;2000-02-23&#39;,&#39;SA_REP&#39;,6800.00,0.10,147,80),(166,&#39;Sundar&#39;,&#39;Ande&#39;,&#39;SANDE&#39;,&#39;011.44.1346.629268&#39;,&#39;2000-03-24&#39;,&#39;SA_REP&#39;,6400.00,0.10,147,80),(167,&#39;Amit&#39;,&#39;Banda&#39;,&#39;ABANDA&#39;,&#39;011.44.1346.729268&#39;,&#39;2000-04-21&#39;,&#39;SA_REP&#39;,6200.00,0.10,147,80),(168,&#39;Lisa&#39;,&#39;Ozer&#39;,&#39;LOZER&#39;,&#39;011.44.1343.929268&#39;,&#39;1997-03-11&#39;,&#39;SA_REP&#39;,11500.00,0.25,148,80),(169,&#39;Harrison&#39;,&#39;Bloom&#39;,&#39;HBLOOM&#39;,&#39;011.44.1343.829268&#39;,&#39;1998-03-23&#39;,&#39;SA_REP&#39;,10000.00,0.20,148,80),(170,&#39;Tayler&#39;,&#39;Fox&#39;,&#39;TFOX&#39;,&#39;011.44.1343.729268&#39;,&#39;1998-01-24&#39;,&#39;SA_REP&#39;,9600.00,0.20,148,80),(171,&#39;William&#39;,&#39;Smith&#39;,&#39;WSMITH&#39;,&#39;011.44.1343.629268&#39;,&#39;1999-02-23&#39;,&#39;SA_REP&#39;,7400.00,0.15,148,80),(172,&#39;Elizabeth&#39;,&#39;Bates&#39;,&#39;EBATES&#39;,&#39;011.44.1343.529268&#39;,&#39;1999-03-24&#39;,&#39;SA_REP&#39;,7300.00,0.15,148,80),(173,&#39;Sundita&#39;,&#39;Kumar&#39;,&#39;SKUMAR&#39;,&#39;011.44.1343.329268&#39;,&#39;2000-04-21&#39;,&#39;SA_REP&#39;,6100.00,0.10,148,80),(174,&#39;Ellen&#39;,&#39;Abel&#39;,&#39;EABEL&#39;,&#39;011.44.1644.429267&#39;,&#39;1996-05-11&#39;,&#39;SA_REP&#39;,11000.00,0.30,149,80),(175,&#39;Alyssa&#39;,&#39;Hutton&#39;,&#39;AHUTTON&#39;,&#39;011.44.1644.429266&#39;,&#39;1997-03-19&#39;,&#39;SA_REP&#39;,8800.00,0.25,149,80),(176,&#39;Jonathon&#39;,&#39;Taylor&#39;,&#39;JTAYLOR&#39;,&#39;011.44.1644.429265&#39;,&#39;1998-03-24&#39;,&#39;SA_REP&#39;,8600.00,0.20,149,80),(177,&#39;Jack&#39;,&#39;Livingston&#39;,&#39;JLIVINGS&#39;,&#39;011.44.1644.429264&#39;,&#39;1998-04-23&#39;,&#39;SA_REP&#39;,8400.00,0.20,149,80),(178,&#39;Kimberely&#39;,&#39;Grant&#39;,&#39;KGRANT&#39;,&#39;011.44.1644.429263&#39;,&#39;1999-05-24&#39;,&#39;SA_REP&#39;,7000.00,0.15,149,NULL),(179,&#39;Charles&#39;,&#39;Johnson&#39;,&#39;CJOHNSON&#39;,&#39;011.44.1644.429262&#39;,&#39;2000-01-04&#39;,&#39;SA_REP&#39;,6200.00,0.10,149,80),(180,&#39;Winston&#39;,&#39;Taylor&#39;,&#39;WTAYLOR&#39;,&#39;650.507.9876&#39;,&#39;1998-01-24&#39;,&#39;SH_CLERK&#39;,3200.00,NULL,120,50),(181,&#39;Jean&#39;,&#39;Fleaur&#39;,&#39;JFLEAUR&#39;,&#39;650.507.9877&#39;,&#39;1998-02-23&#39;,&#39;SH_CLERK&#39;,3100.00,NULL,120,50),(182,&#39;Martha&#39;,&#39;Sullivan&#39;,&#39;MSULLIVA&#39;,&#39;650.507.9878&#39;,&#39;1999-06-21&#39;,&#39;SH_CLERK&#39;,2500.00,NULL,120,50),(183,&#39;Girard&#39;,&#39;Geoni&#39;,&#39;GGEONI&#39;,&#39;650.507.9879&#39;,&#39;2000-02-03&#39;,&#39;SH_CLERK&#39;,2800.00,NULL,120,50),(184,&#39;Nandita&#39;,&#39;Sarchand&#39;,&#39;NSARCHAN&#39;,&#39;650.509.1876&#39;,&#39;1996-01-27&#39;,&#39;SH_CLERK&#39;,4200.00,NULL,121,50),(185,&#39;Alexis&#39;,&#39;Bull&#39;,&#39;ABULL&#39;,&#39;650.509.2876&#39;,&#39;1997-02-20&#39;,&#39;SH_CLERK&#39;,4100.00,NULL,121,50),(186,&#39;Julia&#39;,&#39;Dellinger&#39;,&#39;JDELLING&#39;,&#39;650.509.3876&#39;,&#39;1998-06-24&#39;,&#39;SH_CLERK&#39;,3400.00,NULL,121,50),(187,&#39;Anthony&#39;,&#39;Cabrio&#39;,&#39;ACABRIO&#39;,&#39;650.509.4876&#39;,&#39;1999-02-07&#39;,&#39;SH_CLERK&#39;,3000.00,NULL,121,50),(188,&#39;Kelly&#39;,&#39;Chung&#39;,&#39;KCHUNG&#39;,&#39;650.505.1876&#39;,&#39;1997-06-14&#39;,&#39;SH_CLERK&#39;,3800.00,NULL,122,50),(189,&#39;Jennifer&#39;,&#39;Dilly&#39;,&#39;JDILLY&#39;,&#39;650.505.2876&#39;,&#39;1997-08-13&#39;,&#39;SH_CLERK&#39;,3600.00,NULL,122,50),(190,&#39;Timothy&#39;,&#39;Gates&#39;,&#39;TGATES&#39;,&#39;650.505.3876&#39;,&#39;1998-07-11&#39;,&#39;SH_CLERK&#39;,2900.00,NULL,122,50),(191,&#39;Randall&#39;,&#39;Perkins&#39;,&#39;RPERKINS&#39;,&#39;650.505.4876&#39;,&#39;1999-12-19&#39;,&#39;SH_CLERK&#39;,2500.00,NULL,122,50),(192,&#39;Sarah&#39;,&#39;Bell&#39;,&#39;SBELL&#39;,&#39;650.501.1876&#39;,&#39;1996-02-04&#39;,&#39;SH_CLERK&#39;,4000.00,NULL,123,50),(193,&#39;Britney&#39;,&#39;Everett&#39;,&#39;BEVERETT&#39;,&#39;650.501.2876&#39;,&#39;1997-03-03&#39;,&#39;SH_CLERK&#39;,3900.00,NULL,123,50),(194,&#39;Samuel&#39;,&#39;McCain&#39;,&#39;SMCCAIN&#39;,&#39;650.501.3876&#39;,&#39;1998-07-01&#39;,&#39;SH_CLERK&#39;,3200.00,NULL,123,50),(195,&#39;Vance&#39;,&#39;Jones&#39;,&#39;VJONES&#39;,&#39;650.501.4876&#39;,&#39;1999-03-17&#39;,&#39;SH_CLERK&#39;,2800.00,NULL,123,50),(196,&#39;Alana&#39;,&#39;Walsh&#39;,&#39;AWALSH&#39;,&#39;650.507.9811&#39;,&#39;1998-04-24&#39;,&#39;SH_CLERK&#39;,3100.00,NULL,124,50),(197,&#39;Kevin&#39;,&#39;Feeney&#39;,&#39;KFEENEY&#39;,&#39;650.507.9822&#39;,&#39;1998-05-23&#39;,&#39;SH_CLERK&#39;,3000.00,NULL,124,50),(198,&#39;Donald&#39;,&#39;OConnell&#39;,&#39;DOCONNEL&#39;,&#39;650.507.9833&#39;,&#39;1999-06-21&#39;,&#39;SH_CLERK&#39;,2600.00,NULL,124,50),(199,&#39;Douglas&#39;,&#39;Grant&#39;,&#39;DGRANT&#39;,&#39;650.507.9844&#39;,&#39;2000-01-13&#39;,&#39;SH_CLERK&#39;,2600.00,NULL,124,50),(200,&#39;Jennifer&#39;,&#39;Whalen&#39;,&#39;JWHALEN&#39;,&#39;515.123.4444&#39;,&#39;1987-09-17&#39;,&#39;AD_ASST&#39;,4400.00,NULL,101,10),(201,&#39;Michael&#39;,&#39;Hartstein&#39;,&#39;MHARTSTE&#39;,&#39;515.123.5555&#39;,&#39;1996-02-17&#39;,&#39;MK_MAN&#39;,13000.00,NULL,100,20),(202,&#39;Pat&#39;,&#39;Fay&#39;,&#39;PFAY&#39;,&#39;603.123.6666&#39;,&#39;1997-08-17&#39;,&#39;MK_REP&#39;,6000.00,NULL,201,20),(203,&#39;Susan&#39;,&#39;Mavris&#39;,&#39;SMAVRIS&#39;,&#39;515.123.7777&#39;,&#39;1994-06-07&#39;,&#39;HR_REP&#39;,6500.00,NULL,101,40),(204,&#39;Hermann&#39;,&#39;Baer&#39;,&#39;HBAER&#39;,&#39;515.123.8888&#39;,&#39;1994-06-07&#39;,&#39;PR_REP&#39;,10000.00,NULL,101,70),(205,&#39;Shelley&#39;,&#39;Higgins&#39;,&#39;SHIGGINS&#39;,&#39;515.123.8080&#39;,&#39;1994-06-07&#39;,&#39;AC_MGR&#39;,12000.00,NULL,101,110),(206,&#39;William&#39;,&#39;Gietz&#39;,&#39;WGIETZ&#39;,&#39;515.123.8181&#39;,&#39;1994-06-07&#39;,&#39;AC_ACCOUNT&#39;,8300.00,NULL,205,110);

먼저 위의 표를 바탕으로 새로운 뷰를 생성합니다.

CREATE TABLE emps
AS 
SELECT *
FROM employees;

전화번호가 ‘011’

CREATE OR REPLACE VIEW emp_v1
AS 
SELECT last_name, salary, email
FROM emps
WHERE phone_number LIKE &#39;011%&#39;;

로 시작하는 직원의 이름, 급여, 이메일을 쿼리하도록 요청하는 emp_v1 뷰를 생성합니다.

要求将视图 emp_v1 修改为查询电话号码以 ‘011’ 开头的并且邮箱中包含 e 字符的员工姓名和邮箱、电话号码

CREATE OR REPLACE VIEW emp_v1
AS 
SELECT last_name, salary, email, phone_number
FROM emps
WHERE phone_number LIKE &#39;011%&#39; AND email LIKE &#39;%e%&#39;;

MySQL 뷰 관련 개념 및 적용 사례 분석

向 emp_v1 插入一条记录，是否可以？

DESC emps; 
DESC emp_v1; 
 
INSERT INTO emp_v1(last_name,salary,email,phone_number) 
VALUES(&#39;Tom&#39;,2300,&#39;tom@126.com&#39;,&#39;1322321312&#39;); 
 
#实测不可以

修改 emp_v1 中员工的工资，每人涨薪 1000

UPDATE emp_v1
SET salary = salary + 1000;
 
SELECT * FROM emp_v1;
SELECT * FROM emps;

删除 emp_v1 中姓名为 Olsen 的员工

DELETE FROM emp_v1
WHERE last_name = &#39;Olsen&#39;;
 
SELECT *
FROM emp_v1
WHERE last_name = &#39;Olsen&#39;;

MySQL 뷰 관련 개념 및 적용 사례 분석

创建视图 emp_v2 ，要求查询部门的最高工资高于 12000 的部门 id 和其最高工资

CREATE OR REPLACE VIEW emp_v2
AS
SELECT department_id, MAX(salary) max_sal
FROM emps
GROUP BY department_id
HAVING MAX(salary) > 12000;
 
SELECT * FROM emp_v2;

MySQL 뷰 관련 개념 및 적용 사례 분석

向 emp_v2 中插入一条记录，是否可以？

INSERT INTO emp_v2
VALUES(400, 18000);

MySQL 뷰 관련 개념 및 적용 사례 분석

删除刚才的 emp_v2 和 emp_v1

DROP VIEW IF EXISTS emp_v1, emp_v2;
 
SHOW TABLES;

5.视图的优缺点

MySQL 뷰 관련 개념 및 적용 사례 분석

1. 操作简单

将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。

2. 减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。因此，在使用时，我们需要定义视图的查询语句以获得结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

3. 数据安全

通过视图实现某些数据的结果集，MySQL可以对用户访问这些数据的限制。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。同时， MySQL 可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

4. 适应灵活多变的需求当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量。这种方式在实际工作中使用得比较多。

5. 能够分解复杂的查询逻辑数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。维护视图的成本会增加，因为用于创建视图的 SQL 查询可能会重命名字段或包含复杂的逻辑。

实际项目中，如果视图过多，会导致数据库维护成本的问题。

위 내용은 MySQL 뷰 관련 개념 및 적용 사례 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명：

이 기사는 yisu.com에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제

이전 기사：Python이 MySQL을 작동하여 시간과 업데이트 시간을 생성하는 방법다음 기사：Python이 MySQL을 작동하여 시간과 업데이트 시간을 생성하는 방법