Application of MySQL view to create a view

The application of MySQL view to create a view

View is also called a virtual table, including a set of records returned by executing a query . Views can simplify the method of obtaining data and implement abstract queries through aliases.

In order to simplify the query, in the actual project development process, you can usually create a view for a certain table in the database. Only the fields that developers care about are created in the view. Enter the username and password of the logged-in user in the input box respectively. After clicking the "Enter" button, if the user enters an incorrect username or password, an error prompt will be given. Otherwise, if the entered username and password are correct, a successful login will be prompted. .

Recommended related mysql video tutorials: "mysql tutorial"

Technical points

Creating views in MySQL can be achieved through the create view statement. The specific creation format is as follows:

$create [ or deplace] [algorithm={merge | temptable | undefined}] view view_name[( column_list)] as select_statement [with [cascaded | local] check option]

algorithm={merge | temptable | undefined} attribute is used to optimize the execution of MySQL views. This attribute has 3 available set up. The following will introduce how to use these 3 settings.

merge: This parameter causes any clauses passed in when MySQL executes the view to be merged into the view's query definition.

temptable: If the data in the table below the view changes, these changes will be reflected immediately on the next pass through the table.

undefined: When the query result and the view result have a one-to-one correspondence, MySQL sets the algorithm to temptable.

view_name: The name of the new view.

select_statement: SQL query statement is used to limit the contents of the virtual table.

mysql view creation process

Since this example uses a view to create a virtual table, it should be created first under the "Command Prompt" view, you can create a view at the command prompt. The code to create the view is as follows:

create view chkadmin as select username, password from user

Create the view in the "Command Prompt" as follows:

After creating the view chkadmin, the view only contains There are two fields, username and password, which will bring great convenience to the password verification work.

Then we create the user input login interface, connect to the database, and determine whether the user clicked the "Enter" button. If so, verify the user identity through the view chkadmin. The process code is as follows:

<form method="post" action="3.php" name="form1">
    用户名：<input type="text" name="username"  ><br>
    密  码：<input type="password" name="pwd"><br>
    <input type="submit" name="Submit" value="进入">
    <input type="submit"  value="重置">
</form>
<?php
if($_POST[&#39;Submit&#39;]){
    $name = $_POST[&#39;username&#39;];
    $password = $_POST[&#39;pwd&#39;];
header("Content-Type:text/html; charset=utf-8");
$link = mysqli_connect("localhost", "root", "root");
$conn = mysqli_select_db($link, "php_cn");
$sql = ("select * from chkadmin where username=&#39;".$name."&#39; and password = &#39;".$password."&#39;");
$result = mysqli_query($link, $sql);
$res = mysqli_fetch_array($result, MYSQLI_ASSOC);
    if ($res==false){
        echo "<script>alert(&#39;用户名或者密码输入错误&#39;);history.back();</script>";
        exit();
    }else{
        echo "<br><p align=&#39;center&#39;>登录成功！</p>";
    }
}

When we enter wrong information, as shown below:

When we enter correct information, as shown below:

Then we will introduce the MySQL creation view here. Have you guys mastered it? If you haven’t mastered it yet, you can read another article carefully. , in the next article we will continue to introduce the application of MySQL views. For details, please read "MySQL View Applications - Modifying Views"!

【Recommended related tutorials】

1.【MYSQL online free video tutorial】

2. Recommended related video courses : "Han Shunping's latest MySQL basic video tutorial in 2016"

The above is the detailed content of Application of MySQL view to create a view. For more information, please follow other related articles on the PHP Chinese website!