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

       CREATE VIEW

      MySQL views are a type of virtual tables. They are stored in the database with an associated name. They allow users to do the following −
      • Structure data in a way that users or classes of users find natural or intuitive.
      • Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
      • Summarize data from various tables which can be used to generate reports.
      A view can be created from one or more tables, containing either all or selective rows from them. Unless indexed, a view does not exist in a database.

      MySQL Create View Statement

      Creating a view is simply creating a virtual table using a query. A view is an SQL statement that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query.

      Syntax

      Following is the syntax of the CREATE VIEW Statement 
      CREATE VIEW view_name AS select_statements FROM table_name;

      Example

      Assume we have created a table using the SELECT statement as shown below 
      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 7 records in the above created table 
      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);
      Following query creates a view based on the above create table 
      CREATE VIEW first_view AS SELECT * FROM CUSTOMERS;

      Verification

      You can verify the contents of a view using the select query as shown below 
      SELECT * FROM first_view;
      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

      With REPLACE and IF NOT EXISTS Clauses

      Usually, if you try to create a view with the name same as an existing view an error will be generated as shown as 
      CREATE VIEW first_view AS SELECT * FROM CUSTOMERS;
      As the view already exists, following error is raised 
      ERROR 1050 (42S01): Table 'first_view' already exists
      So, you can use the REPLACE clause along with CREATE VIEW to replace the existing view.
      CREATE OR REPLACE VIEW first_view AS SELECT * FROM CUSTOMERS;

      With WHERE Clause

      We can also create a view using the where clause as shown below 
      CREATE VIEW test_view AS SELECT * FROM CUSTOMERS WHERE SALARY>3000;
      Following are the contents of the above created view
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00

      The With Check Option

      The WITH CHECK OPTION is an option used with CREATE VIEW statement. The purpose of this WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) in the query. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

      Syntax

      Following is the syntax 
      CREATE VIEW view_name 
      AS SELECT column_name(s) 
      FROM table_name 
      WITH CHECK OPTION;

      Example

      In the following example, we are creating a view using CREATE VIEW statement along with the WITH CHECK OPTION 
      CREATE VIEW NEW_VIEW 
      AS SELECT * FROM CUSTOMERS 
      WHERE NAME IS NOT NULL 
      WITH CHECK OPTION;
      The view is 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

      Creating a MySQL View Using Client Program

      In addition to creating a view in MySQL Database using the SQL queries, we can also do so using a client program.

      Syntax

      Following are the syntaxes of the Create View into MySQL in various programming languages
      PHPNodeJSJavaPython
      The MySQL PHP connector mysqli provides a function named query() to execute a CREATE VIEW query in the MySQL database.
      $sql="CREATE VIEW views_name AS
         SELECT col_1, col_2, col_3 FROM table_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.');
      
      // CREATING A VIEW;
      $sql = "CREATE VIEW first_view AS
              SELECT tutorial_id, tutorial_title, tutorial_author
              FROM clone_table";
      if ($mysqli->query($sql)) {
         printf("View created successfully!.");
      }
      if ($mysqli->errno) {
         printf("View could not be created!.", $mysqli->error);
      }
      
      $mysqli->close();

      Output

      The output obtained is as follows 
      View created successfully!.