您当前的位置: 首页 » MySQL/MSSQL编程 » MySQL查询语句练习器
MySQL查询语句练习器
论坛链接
<?php
2 // 用户名： luciferstar
3 // 数据库名： luciferstar_uk_db
4 // 数据表名： MsgBrd
5 // 数据字段（列名）： (NO,NAME,SEX,AGE)
6 // 使用前，如果需要，请修改这些数据。但使用了网页中的创建和安装功能后，可不修改。
7 //
8
9 /***************************************
10 *
11 ** 查询前后数据输出，用于比较
12 *
13 **************************************/
14 function show($link,$result)
15 {
16 echo "<pre>";
17 for ($i = 0; $i < mysql_num_rows($result); $i++)
18 printf ("Table: %s\n", mysql_tablename($result, $i));
19 echo "<p>";
20 $fields = mysql_list_fields("luciferstar_uk_db", "MsgBrd", $link);//修改一下你的数据库和查询表
21 $columns = mysql_num_fields($fields);
22 for ($i = 0; $i < $columns; $i++)
23 {
24 echo mysql_field_name($fields, $i) . "\t";
25 }
26 echo "\n";
27 $query = "SELECT * FROM MsgBrd"; //修改一下你的查询表
28 $result = mysql_query($query) or die("Query failed : " . mysql_error());
29 while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
30 {
31 foreach ($line as $col_value)
32 {
33 print "".$col_value."\t";
34 }
35 print "\n";
36 }
37 echo "</pre>";
38 }
39 /***********************************
40 *
41 ** 执行你的查询操作
42 *
43 ************************************/
44 function doaction($action,$no,$name,$sex,$age)
45 {
46 switch($action)//修改一下你的查询表
47 {
48 case "install":$query = "create database luciferstar_uk_db"; break;
49 case "create": $query = "create table MsgBrd (NO SMALLINT,NAME CHAR(8),SEX CHAR(2),AGE SMALLINT)"; break;
50 case "insert": $query = "insert into MsgBrd values($no,\"$name\",\"$sex\",$age)"; break;
51 case "update": $query = "update MsgBrd set NAME=\"$name\",SEX=\"$sex\",AGE=$age where NO=$no"; break;
52 case "select"; $query = "select NO,NAME,SEX,AGE from MsgBrd order by NO ASC"; break;
53 case "delete"; $query = "delete from MsgBrd where NO=$no"; break;
54 default: $query=$action; break;
55 }
56 echo "operation: $query<p>";
57 $result = mysql_query($query) or die("Query failed : " . mysql_error());
58 if(substr($query,0,6)=="select"||substr($query,0,6)=="SELECT")
59 {
60 echo "<pre>SELECT RESULT:\n";
61 while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
62 {
63 foreach ($line as $col_value)
64 {
65 print "".$col_value."\t";
66 }
67 print "\n";
68 }
69 echo "</pre>";
70 }
71 echo "<p>";
72 }
73
74
75 if($_POST['user']!=NULL)
76 $link=mysql_connect("localhost",$_POST['user'], "");
77 else
78 $link=mysql_connect("localhost", "luciferstar", "");//修改一下你的数据库查询的用户名
79 $result = mysql_list_tables("luciferstar_uk_db"); //修改一下你的数据库和查询表
80 echo "before";
81 show($link ,$result);
82 if($_POST['query']==NULL)
83 {
84 doaction($_POST['action'],$_POST['no'],$_POST['name'],$_POST['sex'],$_POST['age']);
85 }
86 else
87 {
88 doaction(stripslashes($_POST['query']),0,0,0,0);
89 }
90 echo "after";
91 show($link ,$result);
92
93 mysql_free_result($result);
94 mysql_close($link);
95 ?>
2 // 用户名： luciferstar
3 // 数据库名： luciferstar_uk_db
4 // 数据表名： MsgBrd
5 // 数据字段（列名）： (NO,NAME,SEX,AGE)
6 // 使用前，如果需要，请修改这些数据。但使用了网页中的创建和安装功能后，可不修改。
7 //
8
9 /***************************************
10 *
11 ** 查询前后数据输出，用于比较
12 *
13 **************************************/
14 function show($link,$result)
15 {
16 echo "<pre>";
17 for ($i = 0; $i < mysql_num_rows($result); $i++)
18 printf ("Table: %s\n", mysql_tablename($result, $i));
19 echo "<p>";
20 $fields = mysql_list_fields("luciferstar_uk_db", "MsgBrd", $link);//修改一下你的数据库和查询表
21 $columns = mysql_num_fields($fields);
22 for ($i = 0; $i < $columns; $i++)
23 {
24 echo mysql_field_name($fields, $i) . "\t";
25 }
26 echo "\n";
27 $query = "SELECT * FROM MsgBrd"; //修改一下你的查询表
28 $result = mysql_query($query) or die("Query failed : " . mysql_error());
29 while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
30 {
31 foreach ($line as $col_value)
32 {
33 print "".$col_value."\t";
34 }
35 print "\n";
36 }
37 echo "</pre>";
38 }
39 /***********************************
40 *
41 ** 执行你的查询操作
42 *
43 ************************************/
44 function doaction($action,$no,$name,$sex,$age)
45 {
46 switch($action)//修改一下你的查询表
47 {
48 case "install":$query = "create database luciferstar_uk_db"; break;
49 case "create": $query = "create table MsgBrd (NO SMALLINT,NAME CHAR(8),SEX CHAR(2),AGE SMALLINT)"; break;
50 case "insert": $query = "insert into MsgBrd values($no,\"$name\",\"$sex\",$age)"; break;
51 case "update": $query = "update MsgBrd set NAME=\"$name\",SEX=\"$sex\",AGE=$age where NO=$no"; break;
52 case "select"; $query = "select NO,NAME,SEX,AGE from MsgBrd order by NO ASC"; break;
53 case "delete"; $query = "delete from MsgBrd where NO=$no"; break;
54 default: $query=$action; break;
55 }
56 echo "operation: $query<p>";
57 $result = mysql_query($query) or die("Query failed : " . mysql_error());
58 if(substr($query,0,6)=="select"||substr($query,0,6)=="SELECT")
59 {
60 echo "<pre>SELECT RESULT:\n";
61 while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
62 {
63 foreach ($line as $col_value)
64 {
65 print "".$col_value."\t";
66 }
67 print "\n";
68 }
69 echo "</pre>";
70 }
71 echo "<p>";
72 }
73
74
75 if($_POST['user']!=NULL)
76 $link=mysql_connect("localhost",$_POST['user'], "");
77 else
78 $link=mysql_connect("localhost", "luciferstar", "");//修改一下你的数据库查询的用户名
79 $result = mysql_list_tables("luciferstar_uk_db"); //修改一下你的数据库和查询表
80 echo "before";
81 show($link ,$result);
82 if($_POST['query']==NULL)
83 {
84 doaction($_POST['action'],$_POST['no'],$_POST['name'],$_POST['sex'],$_POST['age']);
85 }
86 else
87 {
88 doaction(stripslashes($_POST['query']),0,0,0,0);
89 }
90 echo "after";
91 show($link ,$result);
92
93 mysql_free_result($result);
94 mysql_close($link);
95 ?>