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
      Rename 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.

       Rename View

      Renaming Views in MySQL

      The MySQL RENAME TABLE statement in MySQL is generally used to rename the name of a table. But this statement can also be used to rename views because views are typically virtual tables created by a query.
      Before renaming a view, we need to ensure that no active transactions are being performed on the view using its old name. It is, however, recommended to delete the existing view and re-create it with a new name instead of renaming it.

      Syntax

      Following is the basic syntax of the RENAME TABLE query to rename a view in MySQL 
      RENAME TABLE original_view_name 
      TO new_view_name;

      Example

      First of all, let us create a table with the name CUSTOMERS using the following query 
      CREATE TABLE CUSTOMERS(
         ID int NOT NULL,
         NAME varchar(20) NOT NULL,
         AGE int NOT NULL,
         ADDRESS varchar(25),
         SALARY decimal(18, 2),
         PRIMARY KEY (ID)
      );
      Here, we are inserting some records into the above-created table using the query below 
      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 based on the above created table using the following query 
      CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;
      The view will be created as follows
      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
      7
      Muffy
      24
      Indore
      10000.00
      Renaming the view
      Now, we know that we are having an existing view in our database named "CUSTOMERS_VIEW". So, we are going to rename this view to VIEW_CUSTOMERS using the below query 
      RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS;

      Verification

      Using the following SELECT statement, we can verify whether the view is renamed or not 
      SELECT * FROM VIEW_CUSTOMERS;
      The "VIEW_CUSTOMERS" view displayed is as follows
      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
      7
      Muffy
      24
      Indore
      10000.00

      Rules to be followed while Renaming Views

      There are some rules and practices to ensure that the renaming process goes smoothly and one should follow them while renaming a view in MySQL. They are listed below:
      • Avoid renaming system views: In MySQL, the system views are views that contain all the information about the database management system. It is recommended not to rename these views because it can cause issues with the functioning of the database.
      • Update all references to the view: After renaming a view in MySQL, any stored procedures, triggers, or other database objects that reference the view will need to be updated to use the new name of the view. If we failed to update these references results in errors or issues with the functioning of the database system.
      • Test thoroughly: It is important to test the renaming process thoroughly in the development or testing environment to make sure that all references to the view have been updated correctly.
      • Use a consistent naming convention: While working with views in MySQL, it's recommended to use a consistent naming convention. If you need to rename a view, follow the same naming convention you've used for other views in the database.
      • Backup the database: Before renaming a view, it is recommended to have a backup of the database to make sure that you have a restore point.

      Renaming a View Using a Client Program

      Until now, we used an SQL statement to rename a view directly in the MySQL database. But, we can also perform the same rename operation on a view using another client program.

      Syntax

      PHPNodeJSJavaPython
      To Rename a view into MySQL Database through a PHP program, we need to execute the RENAME statement using the mysqli function named query() as follows 
      $sql = "RENAME TABLE first_view To tutorial_view";
      $mysqli->query($sql);

      Example

      Following are the programs
      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.');
      // A view can be renamed by using the RENAME TABLE old_view_name TO new_view_name
      $sql = "RENAME TABLE first_view To tutorial_view";
      if ($mysqli->query($sql)) {
          printf("View renamed successfully!.");
      }
      if ($mysqli->errno) {
          printf("View could not be renamed!.", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      View renamed successfully!.