Zach Kaplan Zach Kaplan - 1 year ago 91
SQL Question

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:

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!

Answer Source

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

-- 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 (
    -- 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
    t1 INNER JOIN t2 ON = 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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download