MySQL Tutorial
Introduction Installation of MySQL Getting MySQL server information RDBMS Concepts Data Definition Language Data types Managing Databases Managing Tables Managing Views Inserting records in a table Updating records in a table Deleting records from a table Retrieving Records Data Control Language Variables in MySQL Procedures in MySQL Built-In functions in MySQL User Defined functions in MySQL Triggers in MySQL Cursors in MySQL Transaction control Event scheduler Tuning MySQL Server Performance Managing users Importing and Exporting data Database Testing Workbench PHPMyAdminViews 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
Web development and Automation testing
solutions delivered!!