Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Drop Views

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Drop View

      A MySQL View is a virtual table which is generated from a predefined SQL query. It contains (all or selective) records from one or more database tables.
      Views are not stored in a database physically, but they can still be dropped whenever not necessary. Even though they are used to see and modify the data in a database table, the data in that table remains unchanged when views are dropped.

      The MySQL DROP VIEW Statement

      The DROP VIEW statement in MySQL is used to delete an existing view, along with its definition and other information. Once the view is dropped, all the permissions for it will also be removed. We can also use this statement to drop indexed views.
      Suppose a table is dropped using the DROP TABLE command and it has a view associated to it, this view must also be dropped explicitly using the DROP VIEW command.
      NOTE −
      • While trying to perform queries, the database engine checks all the objects referenced in that statement are valid and exist. So, if a view does not exist in the database, the DROP VIEW statement will throw an error.
      • To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.

      Syntax

      Following is the syntax of the DROP VIEW Statement 
      DROP VIEW view_name;
      Where, view_name is the name of the view to be deleted.

      Example

      Suppose we have created a table named CUSTOMERS using the following CREATE TABLE query 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR(15) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS VARCHAR(25),
         SALARY DECIMAL(10, 2),
         PRIMARY KEY(ID)
      );
      Let us insert records in the above created table using the following INSERT query 
      INSERT INTO CUSTOMERS VALUES 
      (1, 'Ramesh', '32', 'Ahmedabad', 2000),
      (2, 'Khilan', '25', 'Delhi', 1500),
      (3, 'Kaushik', '23', 'Kota', 2500),
      (4, 'Chaitali', '26', 'Mumbai', 6500),
      (5, 'Hardik','27', 'Bhopal', 8500),
      (6, 'Komal', '22', 'MP', 9000),
      (7, 'Muffy', '24', 'Indore', 5500);
      Creating a View
      Now, let us create a view on this table using the CREATE VIEW statement as shown below 
      CREATE VIEW testView AS SELECT * FROM CUSTOMERS;
      You can verify the list of all the views using the following query 
      SHOW FULL TABLES WHERE table_type = 'VIEW';
      The view will be created as follows
      Tables_in_sample
      Table_type
      testview
      VIEW
      Dropping a View
      Following query drops the view created above 
      DROP VIEW testView;

      Verification

      To verify if we have deleted the view or not, display the list of views using the query below
      SHOW FULL TABLES WHERE table_type = 'VIEW';
      As the view is dropped, an empty set is returned.
      Empty set (0.12 sec)

      The IF EXISTS clause

      If you try to drop a view that doesn't exist, an error will be generated. Let us see an example where we are dropping a view named NEW using the following query 
      DROP VIEW NEW;
      The following error is displayed (where 'tutorialspoint' is the database name) 
      ERROR 1051 (42S02): Unknown table 'tutorialspoint.new'
      However, if you use the IF EXISTS clause along with the DROP VIEW statement as shown below, the query will be ignored even if a VIEW with the given name does not exist.
      DROP VIEW IF EXISTS NEW;

      Deleting Rows from a View

      Instead of removing an entire view, we can also drop selected rows of a view using the DELETE statement with a WHERE clause.

      Syntax

      Following is the syntax of the DELETE statement 
      DELETE FROM view_name WHERE condition;

      Example

      In this example, let us first create a testView on the CUSTOMERS table using the following query 
      CREATE VIEW testView AS SELECT * FROM CUSTOMERS;
      Now, using the following query, you can delete a record from the testView created on the CUSTOMERS table. The changes made to the data in view will finally be reflected in the base table CUSTOMERS.
      DELETE FROM testView WHERE Location = 'Indore';
      The associated table CUSTOMERS will have the following records
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00

      Dropping View Using Client Program

      In addition to drop a view from the MySQL database using the MySQL query, we can also perform the another operation on a table using a client program.

      Syntax

      Following are the syntaxes of the Drop View from MySQL in various programming languages
      PHPNodeJSJavaPython
      The MySQL PHP connector mysqli provides a function named query() to execute the DROP VIEW query in the MySQL database.
      $sql="DROP VIEW view_name";
      $mysqli->query($sql);

      Example

      Following are the implementations of this operation in various programming languages
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      // drop a view;
      $sql = "DROP VIEW first_view";
      if ($mysqli->query($sql)) {
          printf("View dropped successfully!.");
      }
      if ($mysqli->errno) {
          printf("View could not be dropped!.", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      View dropped successfully!.