laika222
11/9/2016 - 6:13 PM

Use CREATE VIEW to create a view, followed by AS and then the SELECT statement, which can have multiple joins. You can then view the VIEW by

Use CREATE VIEW to create a view, followed by AS and then the SELECT statement, which can have multiple joins. You can then view the VIEW by using SELECT * from viewname. Line 13 shows how you can later view the code that was used to create the view. Line 15 shows you how to show views, similar to how you can SHOW TABLES.

CREATE VIEW and SHOW CREATE VIEW MS.mysql
content_copyfile_download
CREATE VIEW orderdetail AS
SELECT orders1.OrderID, customers1.CustomerID, customers1.LastName, customers1.FirstName, products1.Product, products1.Price, companies1.CompanyName
FROM orders1
JOIN customers1
  ON orders1.CustomerID = customers1.CustomerID
JOIN products1
  ON orders1.ProductID = products1.ProductID
JOIN companies1
  ON products1.CompanyID = companies1.CompanyID
ORDER BY orders1.OrderID;

-- HOW TO VIEW HOW THE VIEW WAS CREATED
SHOW CREATE VIEW orderdetail;

SHOW FULL TABLES;

SHOW FULL TABLES IN selfproject WHERE TABLE_TYPE LIKE 'VIEW';