Detailed explanation of MySQL view management view example [add, delete, modify and query operations]

SHOW CREATE VIEW [database_name].[view_ name];

CREATE VIEW organization AS
  SELECT 
    CONCAT(E.lastname, E.firstname) AS Employee,
    CONCAT(M.lastname, M.firstname) AS Manager
  FROM
    employees AS E
      INNER JOIN
    employees AS M ON M.employeeNumber = E.ReportsTo
  ORDER BY Manager;

mysql> SELECT * FROM organization;
+------------------+------------------+
| Employee | Manager |
+------------------+------------------+
| BondurLoui | BondurGerard |
| CastilloPamela | BondurGerard |
| JonesBarry | BondurGerard |
| HernandezGerard | BondurGerard |
.......many many data is omitted here.......
| KatoYoshimi | NishiMami |
| KingTom | PattersonWilliam |
| MarshPeter | PattersonWilliam |
| FixterAndy | PattersonWilliam |
+------------------+------------------+
24 rows in set

SHOW CREATE VIEW organization;

ALTER
 [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
 VIEW [database_name]. [view_name]
  AS
 [SELECT statement]

ALTER VIEW organization
 AS 
 SELECT CONCAT(E.lastname,E.firstname) AS Employee,
     E.email AS employeeEmail,
     CONCAT(M.lastname,M.firstname) AS Manager
 FROM employees AS E
 INNER JOIN employees AS M
  ON M.employeeNumber = E.ReportsTo
 ORDER BY Manager;

CREATE OR REPLACE VIEW v_contacts AS
  SELECT 
    firstName, lastName, extension, email
  FROM
    employees;
-- Query view data SELECT * FROM v_contacts;

+-----------+-----------+-----------+--------------------------------+
| firstName | lastName | extension | email |
+-----------+-----------+-----------+--------------------------------+
| Diane | Murphy | x5800 | [email protected] |
| Mary | Hill | x4611 | [email protected] |
| Jeff | Firrelli | x9273 | [email protected] |
| William | Patterson | x4871 | [email protected] |
| Gerard | Bondur | x5408 | [email protected] |
| Anthony | Bow | x5428 | [email protected] |
| Leslie | Jennings | x3291 | [email protected] |
.............. Many many data is omitted here..................................
| Martin | Gerard | x2312 | [email protected] |
| Lily | Bush | x9111 | [email protected] |
| John | Minsu | x9112 | [email protected] |
+-----------+-----------+-----------+--------------------------------+
25 rows in set

CREATE OR REPLACE VIEW v_contacts AS
  SELECT 
    firstName, lastName, extension, email, jobtitle
  FROM
    employees;
-- Query view data SELECT * FROM v_contacts;

+-----------+-----------+-----------+--------------------------------+----------------------+
| firstName | lastName | extension | email | jobtitle |
+-----------+-----------+-----------+--------------------------------+----------------------+
| Diane | Murphy | x5800 | [email protected] | President |
| Mary | Hill | x4611 | [email protected] | VP Sales |
| Jeff | Firrelli | x9273 | [email protected] | VP Marketing |
................... A large amount of data is omitted here...................................................
| Yoshimi | Kato | x102 | [email protected] | Sales Rep |
| Martin | Gerard | x2312 | [email protected] | Sales Rep |
| Lily | Bush | x9111 | [email protected] | IT Manager |
| John | Minsu | x9112 | [email protected] | SVP Marketing |
+-----------+-----------+-----------+--------------------------------+----------------------+
25 rows in set

DROP VIEW [IF EXISTS] [database_name].[view_name]

DROP VIEW IF EXISTS organization;