MySQL 创建数据表
创建MySQL数据表需要以下信息:
- 表名
- 表字段名
- 定义每个表字段
CREATE TABLE table_name (column_name column_type);
使用PHP脚本创建数据表
bool mysql_query( sql, connection );
可以使用PHP的
mysql_query()
函数来创建已存在数据库的数据表
。
该函数有两个参数,在执行成功时返回TRUE
,否则返回FALSE
。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "CREATE TABLE tutorials_tbl( ".
"tutorial_id INT NOT NULL AUTO_INCREMENT, ".
"tutorial_title VARCHAR(100) NOT NULL, ".
"tutorial_author VARCHAR(40) NOT NULL, ".
"submission_date DATE, ".
"PRIMARY KEY ( tutorial_id )); ";
mysql_select_db( 'TUTORIALS' );
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('数据表创建失败: ' . mysql_error());
}
echo "数据表创建成功\n";
mysql_close($conn);
?>
MySQL 删除数据表
MySQL中删除数据表
是非常容易操作的
, 但是你再进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失
。
DROP TABLE table_name ;
使用PHP脚本删除数据表
bool mysql_query( sql, connection );
PHP使用 mysql_query 函数来删除 MySQL 数据表。
该函数有两个参数,在执行成功时返回
TRUE
,否则返回FALSE
。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysql_error());
}
echo '连接成功<br />';
$sql = "DROP TABLE w3cschool_tbl";
mysql_select_db( 'W3CSCHOOL' );
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('数据表删除失败: ' . mysql_error());
}
echo "数据表删除成功\n";
mysql_close($conn);
?>
MySQL 插入数据
MySQL 表中使用
INSERT INTO SQL语句
来插入数据。
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
如果
数据是字符型
,必须使用单引号
或者双引号
,如:"value"
。
使用PHP脚本插入数据
你可以使用PHP 的 mysql_query() 函数来执行 SQL INSERT INTO命令来插入数据。
该函数有两个参数,在执行成功时返回 TRUE,否则返回 FALSE。
程序接收用户输入的三个字段数据,并插入数据表中:
向 MySQL 数据库添加数据
<meta charset="utf-8">
<title>向 MySQL 数据库添加数据</title>
</head>
<body>
<?php
if(isset($_POST['add']))
{
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
if(! get_magic_quotes_gpc() )
{
$w3cschool_title = addslashes ($_POST['w3cschool_title']);
$w3cschool_author = addslashes ($_POST['w3cschool_author']);
}
else
{
$w3cschool_title = $_POST['w3cschool_title'];
$w3cschool_author = $_POST['w3cschool_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO w3cschool_tbl ".
"(w3cschool_title,w3cschool_author, submission_date) ".
"VALUES ".
"('$w3cschool_title','$w3cschool_author','$submission_date')";
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully\n";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="600" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="250">Tutorial Title</td>
<td>
<input name="w3cschool_title" type="text" id="w3cschool_title">
</td>
</tr>
<tr>
<td width="250">Tutorial Author</td>
<td>
<input name="w3cschool_author" type="text" id="w3cschool_author">
</td>
</tr>
<tr>
<td width="250">Submission Date [ yyyy-mm-dd ]</td>
<td>
<input name="submission_date" type="text" id="submission_date">
</td>
</tr>
<tr>
<td width="250"> </td>
<td> </td>
</tr>
<tr>
<td width="250"> </td>
<td>
<input name="add" type="submit" id="add" value="Add Tutorial">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
在我们接收用户提交的数据时,为了数据的安全性我们需要使用
get_magic_quotes_gpc() 函数来判断特殊字符的转义是否已经开启
。如果这个选项为off(未开启),返回0
,那么我们就必须调用addslashes 这个函数来为字符串增加转义
。
你也可以添加其他检查数据的方法,比如邮箱格式验证,电话号码验证,是否为整数验证等。
MySQL 查询数据
MySQL 数据库使用
SQL SELECT语句
来查询数据。
使用PHP脚本来获取数据
使用PHP函数的
mysql_query()及SQL SELECT命令
来获取数据。该函数用于执行SQL命令,然后通过 PHP 函数
mysql_fetch_array() 来使用或输出所有查询的数据
。mysql_fetch_array() 函数从
结果集
中取得一行作为关联数组
,或数字数组
,或二者兼有 返回根据从结果集取得的行生成的数组,如果没有更多行则返回 false
。
以下实例为
从数据表 w3cschool_tbl 中读取所有记录。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
w3cschool_author, submission_date
FROM w3cschool_tbl';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "Tutorial ID :{$row['w3cschool_id']} <br> ".
"Title: {$row['w3cschool_title']} <br> ".
"Author: {$row['w3cschool_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
以上实例中,读取的每行记录赋值给变量$row,然后再打印出每个值。
注意:记住如果你
需要在字符串中使用变量
,请将变量置于花括号{}
。在上面的例子中,PHP
mysql_fetch_array()函数
第二个参数为MYSQL_ASSOC
,设置该参数查询结果返回关联数组
,你可以使用字段名称来作为数组的索引。PHP提供了另外一个函数
mysql_fetch_assoc()
, 该函数从结果集中取得一行作为关联数组
。 返回根据从结果集取得的行生成的关联数组,如果没有更多行,则返回 false。
该实例使用了mysql_fetch_assoc()
函数来输出数据表w3cschool_tbl的所有记录:
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
w3cschool_author, submission_date
FROM w3cschool_tbl';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_assoc($retval))
{
echo "Tutorial ID :{$row['w3cschool_id']} <br> ".
"Title: {$row['w3cschool_title']} <br> ".
"Author: {$row['w3cschool_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
你也可以使用常量 MYSQL_NUM
作为PHP mysql_fetch_array()函数的第二个参数
,返回数字数组
。
实例使用MYSQL_NUM参数显示数据表w3cschool_tbl的所有记录:
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
w3cschool_author, submission_date
FROM w3cschool_tbl';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
echo "Tutorial ID :{$row[0]} <br> ".
"Title: {$row[1]} <br> ".
"Author: {$row[2]} <br> ".
"Submission Date : {$row[3]} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
以上三个实例输出结果都一样。
内存释放
在我们执行完SELECT语句后,
释放游标内存
是一个很好的习惯。 可以通过PHP函数mysql_free_result()来实现内存的释放
。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
w3cschool_author, submission_date
FROM w3cschool_tbl';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
echo "Tutorial ID :{$row[0]} <br> ".
"Title: {$row[1]} <br> ".
"Author: {$row[2]} <br> ".
"Submission Date : {$row[3]} <br> ".
"--------------------------------<br>";
}
mysql_free_result($retval);
echo "Fetched data successfully\n";
mysql_close($conn);
?>
MySQL where 子句
我们知道从MySQL表中使用SQL SELECT 语句来读取数据。
如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中
以下是SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法:
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
- 查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
- 你可以在WHERE子句中指定任何条件。
- 你可以使用AND或者OR指定一个或多个条件。
- WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。
- WHERE 子句类似于程序语言中的if条件,根据 MySQL 表中的字段值来读取指定的数据。
操作符列表,可用于 WHERE 子句中
- 如果我们想再MySQL数据表中读取指定的数据,WHERE 子句是非常有用的。
- 使用主键来作为 WHERE 子句的条件查询是非常快速的。
- 如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据。
使用PHP脚本读取数据
你可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE 子句的命令来获取数据。
该函数用于执行SQL命令,然后通过 PHP 函数 mysql_fetch_array() 来输出所有查询的数据。
以下实例将从 w3cschool_tbl 表中返回使用 w3cschool_author 字段值为 Sanjay 的记录
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
w3cschool_author, submission_date
FROM w3cschool_tbl
WHERE w3cschool_author="Sanjay"';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "Tutorial ID :{$row['w3cschool_id']} <br> ".
"Title: {$row['w3cschool_title']} <br> ".
"Author: {$row['w3cschool_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
MySQL UPDATE 查询
如果我们需要修改或更新MySQL中的数据,我们可以使用 SQL UPDATE 命令来操作。.
语法
以下是 UPDATE 命令修改 MySQL 数据表数据的通用SQL语法:
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
- 你可以同时更新一个或多个字段。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以在一个单独表中同时更新数据。
- 当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。
使用PHP脚本更新数据
PHP中使用函数mysql_query()来执行SQL语句,你可以在SQL UPDATE语句中使用或者不使用WHERE子句。
该函数与在mysql>命令提示符中执行SQL语句的效果是一样的。
以下实例将更新 w3cschool_id 为3的 w3cschool_title 字段的数据。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'UPDATE w3cschool_tbl
SET w3cschool_title="Learning JAVA"
WHERE w3cschool_id=3';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\n";
mysql_close($conn);
?>
MySQL DELETE 语句
你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。
语法
以下是SQL DELETE 语句从MySQL数据表中删除数据的通用语法:
DELETE FROM table_name [WHERE Clause]
- 如果没有指定 WHERE 子句,MySQL表中的所有记录将被删除。
- 你可以在 WHERE 子句中指定任何条件
- 您可以在单个表中一次性删除记录。
- 当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。
使用 PHP 脚本删除数据
PHP使用 mysql_query() 函数来执行SQL语句, 你可以在SQL DELETE命令中使用或不使用 WHERE 子句。
以下PHP实例将删除w3cschool_tbl表中w3cschool_id为3的记录:
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'DELETE FROM w3cschool_tbl WHERE w3cschool_id=3';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($conn);
?>
MySQL LIKE 子句
我们知道在MySQL中使用 SQL SELECT 命令来读取数据, 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
WHERE 子句中可以使用等号 (=) 来设定获取数据的条件,如 “w3cschool_author = ‘Sanjay’”。
但是有时候我们需要获取 w3cschool_author 字段含有 “jay” 字符的所有记录,这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号(%)字符来表示任意字符,类似于UNIX或正则表达式中的星号 (*)。
如果没有使用百分号(%), LIKE 子句与等号(=)的效果是一样的。
语法
以下是SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法:
SELECT field1, field2,...fieldN
FROM table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
- 你可以在WHERE子句中指定任何条件。
- 你可以在WHERE子句中使用LIKE子句。
- 你可以使用LIKE子句代替等号(=)。
- LIKE 通常与 % 一同使用,类似于一个元字符的搜索。
- 你可以使用AND或者OR指定一个或多个条件。
- 你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。
- -
在PHP脚本中使用 LIKE 子句
你可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE…LIKE 子句的命令来获取数据。
- 该函数用于执行SQL命令,然后通过 PHP 函数 mysql_fetch_array() 来输出所有查询的数据。
- 但是如果是DELETE或者UPDATE中使用 WHERE…LIKE 子句的SQL语句,则无需使用mysql_fetch_array() 函数。
以下是我们使用PHP脚本在w3cschool_tbl表中读取w3cschool_author字段中以”jay”为结尾的的所有记录
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
w3cschool_author, submission_date
FROM w3cschool_tbl
WHERE w3cschool_author LIKE "%jay%"';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "Tutorial ID :{$row['w3cschool_id']} <br> ".
"Title: {$row['w3cschool_title']} <br> ".
"Author: {$row['w3cschool_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
MySQL 排序
我们知道从MySQL表中使用SQL SELECT 语句来读取数据。
如果我们需要对读取的数据进行排序,我们就可以使用MySQL的 ORDER BY 子句来设定你想按哪个字段哪中方式来进行排序,再返回搜索结果。
语法
以下是SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
- 你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
- 你可以设定多个字段来排序。
- 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升排列。
- 你可以添加 WHERE…LIKE 子句来设置条件。
在PHP脚本中使用 ORDER BY 子句
你可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 ORDER BY 子句的命令来获取数据。 该函数用于执行SQL命令,然后通过 PHP 函数 mysql_fetch_array() 来输出所有查询的数据。
尝试以下实例,查询后的数据按 w3cschool_author 字段的降序排列后返回。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
w3cschool_author, submission_date
FROM w3cschool_tbl
ORDER BY w3cschool_author DESC';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "Tutorial ID :{$row['w3cschool_id']} <br> ".
"Title: {$row['w3cschool_title']} <br> ".
"Author: {$row['w3cschool_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>
MySQL 分组
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
GROUP BY 语法
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
本章节实例使用到了以下表结构及数据,使用前我们可以先将以下数据导入数据库中
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
接下来我们使用
GROUP BY 语句
将数据表按名字进行分组,并统计每个人有多少条
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name WITH ROLLUP
WITH ROLLUP
可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:
SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
其中记录 NULL 表示所有人的登录次数。
我们可以使用 coalesce
来设置一个可以取代 NUll 的名称
,coalesce 语法:
select coalesce(a,b,c);
参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。
以下实例中如果名字为空我们使用总数代替:
SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;