CODEWITHSUNDEEP
mysqlsql
Zach Kaplan
Zach Kaplan

Reputation: 103

Display the contents of a VIEW in MySQL

This is actually a two part question.

First: I was wondering if there is a way to display the information in a view I just created. I couldn't find anything online that was similar to the DISPLAY Tables query that could be used for views.

The query to create my view is:

CREATE VIEW View1 AS
SELECT *
FROM CustOrder
WHERE shipToName = 'Jim Bob'

Secondly, once I find out how to display that specific view from above, how do I go about finding the highest "paidPrice" (a column in the CustOrder table)?

Thank you all in advance!

Upvotes: 3

Views: 21310

Answers (2)

Guangxu Li
Guangxu Li

Reputation: 51

In answer to Question 1:

SHOW CREATE VIEW view_name;

reference: http://dev.mysql.com/doc/refman/5.7/en/show-create-view.html

Upvotes: 5

Michael Berkowski
Michael Berkowski

Reputation: 270775

A view is little more than a stored SELECT statement, but from the perspective of the client, they are mostly equivalent to real tables. To interact with a view you have created, you may simply issue SELECT statements against it.

-- Regular SELECT statements, no different
-- from querying against a real table.
-- Get all rows from the view
SELECT * FROM View1

-- Get the MAX() value from a column
SELECT MAX(paidPrice) AS maxprice FROM View1

You may also create views which represent multiple joined tables. This is a common use case, wherein many tables are frequently joined for querying. You may use a view to handle the joins, and expose only certain columns to certain database users rather than grant full access to your schema.

CREATE VIEW joinview AS (
  SELECT 
    t1.id,
    t1.col1,
    t1.col2,
    -- The view will only expose the alias
    t1.col3 AS aliased_name,
    -- Use an alias to avoid a column name collision
    t2.col1 AS t2c1, 
    -- The view will expose the column name without the table name
    t2.col99
  FROM 
    t1 INNER JOIN t2 ON t1.id = t2.t1_id
);

Now the view will only expose columns as a SELECT query would. You will no longer need to reference the individual tables, since it produces a flat output.

-- Retrieve 2 columns from the join view
SELECT col99, aliased_name FROM joinview

Finally, because views act just like normal tables, you can join them to other tables or views too. Take care when assembling views with joins though, to be sure that the underlying tables are appropriately indexed. Otherwise, the views may perform poorly (just as they would for normal SELECT queries executed without appropriate indexing).

Upvotes: 2

Related Questions