链接和关闭
<?php
//强制显示utf-8
header("Content-type: text/html; charset=utf-8");
$servername = "localhost";
$username = "root";
$password = "";
$con = mysql_connect($servername,$username,$password);
// 检测连接
if (!$con) {
die("Connection failed: " . mysql_error());
}
echo "数据库链接成功";
mysql_close($con);
echo "数据库关闭";
?>
创建数据库&创建表
<?php
//强制显示utf-8
header("Content-type: text/html; charset=utf-8");
$servername = "localhost";
$username = "root";
$password = "";
$con = mysql_connect($servername,$username,$password);
// 检测连接
if (!$con) {
die("Connection failed: " . mysql_error());
}
//创建数据库
if (mysql_query("CREATE DATABASE my_db",$con)) {
# code...
echo "Database create";
}else {
echo "Error createing database:".mysql_error();
}
//create table in my_db database
mysql_select_db("my_db",$con);
//创建了主键和需要的字段
$sql = "CREATE TABLE Person
(
personID int NOT NULL primary key AUTO_INCREMENT,
FirstName varchar(15),
LastName varchar(15),
Age int
)";
mysql_query($sql,$con);
mysql_close($con);
echo "<br>执行完成-数据库关闭";
?>
MySQL数据类型
4D134F37-DC97-4744-ABE2-CD6267B3DA6E.png
插入数据
<?php
//强制显示utf-8
header("Content-type: text/html; charset=utf-8");
$servername = "localhost";
$username = "root";
$password = "";
$con = mysql_connect($servername,$username,$password);
// 检测连接
if (!$con) {
die("Connection failed: " . mysql_error());
}
//创建数据库
if (mysql_query("CREATE DATABASE my_db",$con)) {
# code...
echo "Database create";
}else {
echo "Error createing database:".mysql_error();
}
//create table in my_db database
mysql_select_db("my_db",$con);
//创建了主键和需要的字段
mysql_query("INSERT INTO Person (FirstName,LastName,Age)
Values ('Glenn1','Quagmire','33')");
mysql_query("INSERT INTO Person (FirstName,LastName,Age)
Values ('aaaaa','bbbb','10')");
mysql_close($con);
echo "<br>执行完成-数据库关闭";
?>
使用表单插入数据
post.php
<html>
<body>
<form action="dbtest.php" method="post">
Firstname: <input type="text" name="firstname" />
Lastname: <input type="text" name="lastname" />
Age: <input type="text" name="age" />
<input type="submit" />
</form>
</body>
</html>
dbtest.php
<?php
//强制显示utf-8
header("Content-type: text/html; charset=utf-8");
$servername = "localhost";
$username = "root";
$password = "";
$con = mysql_connect($servername,$username,$password);
// 检测连接
if (!$con) {
die("Connection failed: " . mysql_error());
}
//创建数据库
if (mysql_query("CREATE DATABASE my_db",$con)) {
# code...
echo "Database create";
}else {
echo "Error createing database:".mysql_error()."<br>";
}
//create table in my_db database
mysql_select_db("my_db",$con);
$sql = "INSERT INTO Person (FirstName,LastName,Age)
VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";
if (!mysql_query($sql,$con)) {
# code...
die('Error:'.mysql_error());
}
echo "添加了一条数据";
mysql_close($con);
echo "<br>执行完成-数据库关闭";
?>
查询
<?php
//强制显示utf-8
header("Content-type: text/html; charset=utf-8");
$servername = "localhost";
$username = "root";
$password = "";
$con = mysql_connect($servername,$username,$password);
// 检测连接
if (!$con) {
die("Connection failed: " . mysql_error());
}
//创建数据库
if (mysql_query("CREATE DATABASE my_db",$con)) {
# code...
echo "Database create";
}else {
echo "Error createing database:".mysql_error()."<br>";
}
// table in my_db database
mysql_select_db("my_db",$con);
$result = mysql_query("SELECT * FROM Person");
while ($row = mysql_fetch_array($result)) {
# code...
echo "姓名:".$row['FirstName']." ".$row['LastName'];
echo "<br>";
}
mysql_close($con);
echo "<br>执行完成-数据库关闭";
?>
条件查询
运算符
D766BF81-B7E8-419E-BE77-2EE444BFF849.png
$result = mysql_query("SELECT * FROM Person WHERE FirstName = 'aaaaa' ");
排序
默认升序
$result = mysql_query("SELECT * FROM Persons ORDER BY age");
使用 DESC 关键词来设定降序排序（9 在 1 之前，"p" 在 "a" 之前）：
SELECT column_name(s)
FROM table_name
ORDER BY column_name DESC
可以根据多个列进行排序。当按照多个列进行排序时，只有第一列相同时才使用第二列：
SELECT column_name(s)
FROM table_name
ORDER BY column_name1, column_name2
更新数据
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value
$sql = mysql_query("UPDATE Person SET Age = '100' WHERE FirstName = 'aaaaa'");
删除数据
DELETE FROM table_name
WHERE column_name = some_value
$sql = mysql_query("DELETE FROM Person WHERE FirstName = '11'");
