访问MySQL数据库的脚本示例

2020年12月29日16:02:50 发表评论 36 次浏览

以下脚本显示了一些可用于访问MySQL数据库功能的应用程序的示例。

建立表格

<?php
$server= "dbXX.notlogy.com"; /* Address of the notlogy Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

if (!mysqli_query($link, "CREATE TABLE " . $table . "(name varchar(25), email varchar(25), id int(11))")) 
  die(printf("<H3>Table cannot be created: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error()));

mysqli_close($link);
?>

更新表

你可以通过更新表来更改现有表的条目。

随着更新查询, 该字段中的所有条目电子邮件, 其中包含notlogy.com, 设置为mail@example.com.

<?php
$server= "dbXX.notlogy.com"; /* Address of the notlogy Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access to SQL server and creation of the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

if (!mysqli_query($link, "UPDATE " . $tabelle 
      . " SET email = 'mail@example.com' WHERE INSTR(LCASE(email), 'notlogy.com')")) 
  die(printf("<H3>Record cannot be updated: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

$number = mysqli_affected_rows($link); 
printf("There were " . $number . " records updated.<BR />");

mysqli_close($link);
?>

删除表格

以下脚本从数据库中删除表。

警告

在任何情况下都不应删除数据库, 只能删除单个表, 因为无法重新创建数据库。

<?php
$server= "dbXX.notlogy.com"; /* Address of the notlogy Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access the SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

if (!mysqli_query($link, "DROP TABLE " . $table)) 
  die(printf("<H3>Table cannot be deleted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error()));

mysqli_close($link);
?>

在表格中输入数据

<?php
$server= "dbXX.notlogy.com"; /* Address of the notlogy Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

$number = 0; 
/* Einfügen der Daten */ 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('John Smith', 'john@smith.com', 1)")) 
 die(printf("<H3>Data record 1 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('Jane Doe', 'jane@doe.com', 2)")) 
 die(printf("<H3>Data record 2 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('Thomas Schmitt', 'thomas@schmitt.com', 3)")) 
 die(printf("<H3>Data record 3 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('Example Information', 'info@example.com', 4)")) 
 die(printf("<H3>Data record 4 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 
if (!mysqli_query($link, "INSERT INTO " . $table . " VALUES('notlogy', 'support@notlogy.com', 5)")) 
  die(printf("<H3>Data record 5 cannot be inserted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$number += mysqli_affected_rows($link); 

/* Total number of records inserted */ 
printf("There were " . $number . " records inserted.<BR />");

mysqli_close($link);
?>

读取数据/查看表

<?php
$server= "dbXX.notlogy.com"; /* Address of the notlogy Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access the SQL server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

$result=mysqli_query($link, "SELECT * FROM " . $table . " ORDER BY name"); 
$row_cnt = mysqli_num_rows($result); 
printf("There were " . $row_cnt . " records found.<BR />"); 

/* Output of the table in an HTML table */ 
echo "<table><tr>"; 

while ($field = mysqli_fetch_field($result)) { 
  echo "<th>$field->name</th>"; 
} 
$field_cnt = mysqli_field_count($link); 
echo "</tr>"; 
while($row = mysqli_fetch_row($result)) { 
  echo "<tr>"; 
  for($i = 0; $i < $field_cnt; $i++) { 
    echo "<td>$row[$i]</td>"; 
  } 
  echo "</tr>\n"; 
} 
echo "</table>"; 

/* Close results */ 
mysqli_free_result($result);
mysqli_close($link);
?>

读取表的特定条目

在此脚本中, 仅从表中读取某些条目。这极大地提高了脚本的速度, 从而减少了网站的加载时间。

在这里, 前3个条目是从数据库中检索的, 其中包含联合的在里面电子邮件字段, 或仅包含爱奥诺斯.

通过使用电子邮件字段的选择不区分大小写LCASE().

该查询非常有效, 因为仅传输真正需要的内容。只有田野名称和电子邮件传输, 最后一个字段id被忽略。

<?php
$server= "dbXX.notlogy.com"; /* Address of the notlogy Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 
$result = mysqli_query($link, "SELECT name, email FROM " . $table 
 . " WHERE (name = 'notlogy' OR INSTR(LCASE(email), 'united')) 
 ORDER BY NAME DESC LIMIT 3"); 

$row_cnt = mysqli_num_rows($result); 
printf("Es wurden " . $row_cnt . " records found.<BR />");
/* Output of the table in HTML format */ 
echo "<table border=\"1\" align=center width=50%"; 
echo "<tr>"; 
echo "<div color=\"#ffff00\">"; 
$field_cnt = mysqli_field_count($link); 
while ($field = mysqli_fetch_field($result)) { 
  echo "<th>$field->name</A></th>"; 
} 
echo "</font></tr>"; 
while($row = mysqli_fetch_row($result)) { 
  echo "<tr>"; 
  for($i = 0; $i < $field_cnt; $i++) { 
    echo "<td align=center>$row[$i]</td>"; 
  } 
  echo "</tr>\n"; 
} 
echo "</table>";
/* close result set */ 
mysqli_free_result($result);
mysqli_close($link);
?>

从表中删除单个条目

<?php
$server= "dbXX.notlogy.com"; /* Address of the notlogy Database servers */
$user= "xxxxxx"; /* Database Username */
$password= "yyyyyyy"; /* Password */
$database= "dbxxxxxx"; /* Name of the Database */
$table= "test"; /* Name of the table, can be freely chosen */

/* Access the SQL Server and create the table */
if ((!$link = mysqli_connect($server, $user, $password, $database))) 
  die(printf("<H3>Database connection not possible: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

if (!mysqli_query($link, "DELETE FROM " . $table. " WHERE id = 3")) 
       die(printf("<H3>Record cannot be deleted: [%d] %s</H3>", mysqli_connect_errno(), mysqli_connect_error())); 

$number = mysqli_affected_rows($link); 
printf("There were " . $number . " records deleted<BR />");

mysqli_close($link);
?>

内容

  • 建立表格
  • 更新表
  • 删除表格
  • 在表格中输入数据
  • 读取数据/查看表
  • 读取表的特定条目
  • 从表中删除单个条目
  • 到达顶点
一盏木

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: