Managing views in MySQL

Creating view

You can create view as shown in below syntax.

CREATE VIEW ADSPERCUSTOMER AS
SELECT
CustomerId, Count(*) TotalAds
FROM
ADVERTS
GROUP by CustomerId
ORDER BY TotalAds DESC;

Dropping View

You can use below syntax to drop view

DROP VIEW ADSPERCUSTOMER;

Getting information on views

To find out which views are available in database, you can use below syntax.
SHOW FULL TABLES IN Database_Name WHERE TABLE_TYPE LIKE ‘VIEW’;

For example, to find out all views in DEALS database, you can use below syntax.
SHOW FULL TABLES IN DEALS WHERE TABLE_TYPE LIKE ‘VIEW’;

Altering views

ALTER VIEW ADSPERCUSTOMER AS
SELECT
CustomerId, Count(*) TotalAds
FROM
ADVERTS
GROUP by CustomerId
ORDER BY TotalAds ASC;

Renaming views
To rename view, you can use below syntax. Don’t get confused by looking at TABLE keyword in below statement.
RENAME TABLE ADSPERCUSTOMER to ADSPERCUST

You may also like...