首页 > 代码库 > php连接MySQL数据库及增删改查

php连接MySQL数据库及增删改查

  1、连接MySQL数据库$conn = new mysqli($host,$user,$password);

$conn -> query(‘set names utf8‘);      //设置字符编码,避免存入中文数据乱码
$conn -> select_db(‘db‘);         
 //选择自己建立的数据库(db)

1.1 判断连接状态
if (!$conn) {
  die("
数据库连接异常");
}

//数据库的创建
$db = "create database db";
if($conn -> query($db)){
echo "成功创建数据库";
}else{
echo "创建失败";
}

//数据表的创建
$table = "create table table_name(
id int(10) unsigned auto_increment unique primary key,
user varchar(100) NOT NULL
);";

//1.1.1 添加数据方法1:预处理语句及绑定参数方法(适合多条查询语句的执行)
$table = $conn -> prepare("insert into user_table(id,user) values(NULL,?)");//准备sql语句
$name = ‘小明‘;      
$table -> bind_param("s",$name); //(类型:s,类型值);参数s为string,i为interger,d为double,类型值仅可为一变量
if ($table -> execute() && $conn -> affected_rows){ //判断是否添加成功
   echo "添加成功"; 
}else{
   echo "添加失败";
}
//1.1.1 添加数据方法二:直接调用query()方法(适合单条语句;多条用";"隔开,用multi_query()方法查询);
$insert = "insert into table(name) values(‘你好‘)";
if ($conn -> query($insert) && $conn -> affected_rows){
  echo "成功添加";
}else{
  echo "添加失败";  
}

//1.1.2 删除数据方法1直接调用query()方法;
$delete = "delete from user_table where user in(‘张三‘,‘1‘) or id in(1)";//删除user=‘张三‘的列名 或 id=1的列名
if($conn -> query($delete) && $row = $conn -> affected_rows){
echo "删除成功,一共:$row"."行";
}else{
  ehco "删除失败";
}
//1.1.2 删除数据方法2:预处理语句及绑定参数
$delete = $conn -> prepare("delete from user_table where id in(?) or user in(?);");
$id = 1;
$name = ‘张三‘;
$delete -> bind_param(‘ii‘,$id,$name);
if($delete -> execute() && $row = $conn -> affected_rows){
echo "删除成功,一共:$row"."行";
}else{
  echo "
删除失败";
}

//1.1.3 更改数据方法1:直接query()方法
$update = "update user_table set user = ‘刘老师‘ where id = ‘115‘;";
if($conn -> query($update) && $row = $conn -> affected_rows){
  echo "更改:$row"."行";
}else{
  echo "更改失败";
}

//1.1.3 更改数据方法2:预处理语句及绑定参数
$update = $conn -> prepare("update user_table set user = ? where id = ?;");
$user = "黄老师";
$id = 1;
$update -> bind_param("si",$user,$id);
if($update -> execute() && $row = $conn -> affected_rows){
echo "更改:$row"."行";
}else{
  echo "更改失败";
}
//1.1.4 查询数据方法1:直接query()方法
$select = "select id,user from user_table ORDER BY user,id DESC "; $result = $conn -> query($select);
if($result -> num_rows>0){              // ORDER BY排序 DESC降序排序
while ($row = $result -> fetch_array()){    //循环查询并返回指定条件的每条语句
echo "$row[id]:$row[user]<br>";
}
}else{
  echo "没有数据";
}
1.2 面向过程方法
$conn = mysqli_connect($this ->host,$this -> user,$this -> password);
mysqli_query($conn, ‘set names utf8‘);      //设置字符编码,避免存入中文数据乱码
mysqli_select_db($conn, ‘db‘);          //选择自己建立的数据库(db)

1.2.1增删改查(同理上面);
即:
mysqli_query() == $conn -> query();

1.3 PDO方法(略)


 

php连接MySQL数据库及增删改查