配置：
p配置phpstudy和vscode联合方便从php代码层面学习mysql
配制请看这篇文章
这篇文章写的非常详细
mysql配置：
可能我们的电脑输入mysql -u 用户名 -p可能会出现mysql非系统指令
我们去到phpstudy目录下找到mysql的bin
复制bin的文件地址
然后去配置系统环境变量
将bin的路径新建到path里面，就可以解决上面的问题了
然后就可以登录mysql了
登陆命令是:
mysql -u 用户名 -p
password:这里会要你输入密码
账号密码与这里的相对应
myphpadmin：
需要用的时候点击管理会在浏览器打开，然后输入用户和密码就可以了
php与数据库的学习:
1.数据库的连接：
数据库的连接需要用到sqli_connect()函数连接
mysqli_connect(servername,username,password);
<?php
$con=mysqli_connect("localhost","root","123456");
if(!$con)
{
die("Could not connect:".mysqli_connect_error());
}
echo("Connect successsfully");
mysqli_close($con);
?>
连接成功
2.创建数据库和表：
创建数据库的库：
需要用到mysqli_query()来进行sql操作
首先查看下数据库：（注意是databases）
创建数据库用的指令：
CREATE DATABASE database_name
$sql="create database mydb";///mysql不区分大小写<?php
$servername = "localhost";
$username = "root";
$password = "123456";
// 创建连接
$con = mysqli_connect($servername, $username, $password);
// 检测连接
if (!$con) {
die("连接失败: " . mysqli_connect_error());
}
echo("Connect successsfully");
$sql="create database mydb";///mysql不区分大小写
if(mysqli_query($con,$sql))
{
echo("数据库创建成功");
}
else
{
echo("数据库创建失败".mysqli_error($con));
}
mysqli_close($con);
?>
我已经创建过了
创建数据库的表：
语法：
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....... )
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";<?php
$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "mydb";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
// 使用 sql 创建数据表
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
if (mysqli_query($conn, $sql)) {
echo "数据表 MyGuests 创建成功";
} else {
echo "创建数据表错误: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
创建成功
php的mysql插入数据：
一、向数据库表插入数据
指令：
<?php
$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "mydb";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
// 使用 sql 创建数据表
$sql = "insert into myguests(firstname,lastname,email)
values('John','Doe','john@qq.com')";
if(mysqli_query($conn,$sql))
{
echo("插入成功");
}
else
{
echo("插入失败".mysqli_error($conn));
}
mysqli_close($conn);
?>
二、把来自表单的数据插入数据库
我们写一个insert.html文件
<html>
<body>
<form action="insert2.php" method="post">
Firstname: <input type="text" name="firstname" />
Lastname: <input type="text" name="lastname" />
Email: <input type="text" name="email" />
<input type="submit" />
</form>
</body>
</html>然后准备一个insert2.php文件
<?php
$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "myDB";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[email]')";
if (mysqli_query($conn, $sql)) {
echo "新记录插入成功";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);
?>
然后vscode安装一个open in browser的插件
运行insert.html
然后在提交
PHP MySQL Select：
1.查询数据
语法：
SELECT column_name(s) FROM table_name
tips:* 字符选取表中所有数据
- 使用mysqli_select_db()函数选择要使用的数据库。这里选择的数据库是"mydb"，请确保该数据库存在于数据库服务器中。
- 使用mysqli_query()函数执行查询，并将结果保存在$result变量中。
- 使用while循环和mysqli_fetch_array()函数迭代遍历从数据库中检索到的每一行数据。
<?php
$servername = "localhost";
$username = "root";
$password = "123456";
// 创建连接
$conn = mysqli_connect($servername, $username, $password);
// 检测连接
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
mysqli_select_db($conn,"mydb");
$sql=("SELECT * FROM myguests;");
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result))
{
echo $row['firstname'] . " " . $row['lastname']. " " . $row['email'];
echo "<br />";
}
mysqli_close($conn);
?>
查询结果
2.将数据显示在html表格中
<?php
$servername = "localhost";
$username = "root";
$password = "123456";
// 创建连接
$conn = mysqli_connect($servername, $username, $password);
// 检测连接
if (!$conn) {
die("连接失败: " . mysqli_connect_error());
}
mysqli_select_db($conn,"mydb");
$sql=("SELECT * FROM myguests;");
echo "<table border='1'>
<tr>
<th>firstname</th>
<th>lastname</th>
<th>email</th>
</tr>";
$result = mysqli_query($conn,$sql);
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($conn);
?>
PHP MySQL Order By与 Where:
1.where查找
选取第一个名字是john的数据
<?php
$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "myDB";
// 创建连接
$con = mysqli_connect($servername, $username, $password,$dbname);
// 检测连接
if (mysqli_connect_errno())
{
echo "连接失败: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM myguests
WHERE firstname='John'");
while($row = mysqli_fetch_array($result))
{
echo $row['firstname'] . " " . $row['lastname'];
echo "<br>";
}
?>2.order by排序：
原本数据库是john在前，aaa在后，通过email排序之后，在前
<?php
$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "myDB";
// 创建连接
$con = mysqli_connect($servername, $username, $password,$dbname);
// 检测连接
if (mysqli_connect_errno())
{
echo "连接失败: " . mysqli_connect_error();
}
$result = mysqli_query($con,"SELECT * FROM myguests ORDER BY email");
while($row = mysqli_fetch_array($result))
{
echo $row['firstname'];
echo " " . $row['lastname'];
echo " " . $row['email'];
echo "<br>";
}
mysqli_close($con);
?>PHP MySQL Update与 Delete：
1.update：
将john的email替换为aaa的email
<?php
$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "myDB";
// 创建连接
$con = mysqli_connect($servername, $username, $password,$dbname);
// 检测连接
if (mysqli_connect_errno())
{
echo "连接失败: " . mysqli_connect_error();
}
mysqli_query($con,"UPDATE myguests SET email='aaa@qq.com'
WHERE firstname='John' AND lastname='Doe'");
mysqli_close($con);
?>2.delete删除表中的数据
<?php
$servername = "localhost";
$username = "root";
$password = "123456";
$dbname = "myDB";
// 创建连接
$con = mysqli_connect($servername, $username, $password,$dbname);
// 检测连接
if (mysqli_connect_errno())
{
echo "连接失败: " . mysqli_connect_error();
}
mysqli_query($con,"DELETE FROM myguests WHERE lastname='Doe'");
mysqli_close($con);
?>
表中lastname=Doe的数据都被删除了
基本操作就这些啦，白白！！！
3858
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
