数据库学习(增删改查）

一、数据库基本语法
1）插入数据：

INSERT INTO table_name(column1,column2,...) VALUES(value1,value2,...);

INSERT INTO user(uid,username,gender,brief) VALUES(0001,“张三”,1,“我不是法外狂徒的那个张三”);
2)删除数据

DELETE FROM user WHERE uid=3;

3)更新数据

UPDATE user SET username="王晓美" WHERE uid=2;

4)查找

SELECT * FROM user WHERE gender=3;
SELECT * FROM user WHERE gender=3 or gender=1;

二、PHP操作数据库
1）面向过程MySQLi

<?php
$servername="localhost";$username="root";$password="";//创建连接
$conn=mysqli_connect($servername,$username,$password);//检测连接
if(!$conn){
  die("Connection failed:".mysqli_connect_error());
}
echo "连接成功";
mysqli_close($conn);//关闭链接

?>

2)面向对象MySQLi

$servername="localhost";$username="root";$password="";//创建连接
$conn=new mysqli($servername,$username,$password);//检测连接
if($conn->connect_error){
  die("连接失败:".$conn->connect_error);
}
echo "连接成功";
$conn->close();//关闭链接

3)增删改查配合（面向对象）

$servername="localhost";//域名
$username="root";//账号名
$password="";//密码
$dbname="mydb";//数据库名

//创建链接
$con=new mysqli($servername,$username,$password,$dbname);
//检测链接
if($con->connect_error){
  die("连接失败:".$con->connect_error);
}
//插入
$sql="INSERT INTO user(username) VALUES('Mike')";
 if($conn->query($sql)===TRUE){
   echo "新纪录插入成功";
 }else{
   echo "Error:".$sql."<br>".$conn->error;
 }
 $con->close();//关闭链接

删

//创建链接
$con=new mysqli($servername,$username,$password,$dbname);
//检测链接
if($con->connect_error){
  die("连接失败:".$con->connect_error);
}
//删除
$sql="DELETE FROM user WHERE username='John'";
mysqli_query($con,$sql);
echo "删除成功";
$con->close();//关闭链接

搜索

<?php
$servername="localhost";//域名
$username="root";//账号名
$password="";//密码
$dbname="mydb";//数据库名

//创建链接
$con=new mysqli($servername,$username,$password,$dbname);
//检测链接
if($con->connect_error){
  die("连接失败:".$con->connect_error);
}
$sql="select * from user";
$result=$con->query($sql);
if($result){
  $attr=$result->fetch_all();
  var_dump($attr);
}
mysqli_query($con,$sql);
echo "搜索成功";
$con->close();//关闭链接
?>

4)增删改查（面向对象预处理）
预处理参数类型：
i - integer 整形
d-double 双浮点
s-string 字符串类型
b-BLOB blod类型
（1）增

<?php
  header('content-type:text/html;charset=utf-8');
  $host="localhost";
  $user="root";
  $password="";
  $db="mydb";

  $mysqli=new mysqli($host,$user,$password,$db);//实例化mysqli对象,连接mysql数据库
  if($mysqli->connect_error){
    die($mysqli->connect_error);
  }
  $mysqli->set_charset('utf8');//设置字符集

  $sql="insert into user(username,brief) values(?,?)";
  $mysqli_stmt=$mysqli->prepare($sql);//准备预处理语句

  $username="小明同学";
  $brief="一个很好的同学";

  //s代表string类型  下面这个要传变量
  $mysqli_stmt->bind_param('ss',$username,$brief);

  //执行预处理语句
  if($mysqli_stmt->execute()){
    echo $mysqli_stmt->insert_id;//程序成功，返回插入数据表的行id
    echo PHP_EOL;
  }else{
    echo $mysqli_stmt->error;//执行失败，错误信息
  }
?>

（2）筛选

<?php
  header('content-type:text/html;charset=utf-8');
  $host="localhost";
  $user="root";
  $password="";
  $db="mydb";

  $mysqli=new mysqli($host,$user,$password,$db);//实例化mysqli对象,连接mysql数据库
  if($mysqli->connect_error){
    die($mysqli->connect_error);
  }
  $mysqli->set_charset('utf8');//设置字符集

  $sql="SELECT username,brief FROM user WHERE uid=?";
  $mysqli_stmt=$mysqli->prepare($sql);//准备预处理语句
  //i是值int类型
  $id=2;
  //bind_param只能绑定参数
  $mysqli_stmt->bind_param('i',$id);

  //执行预处理语句
  if($mysqli_stmt->execute()){
    //bind_result() 绑定结果集中的值到变量
    $mysqli_stmt->bind_result($uid,$brief);
    //遍历结果集
    while($mysqli_stmt->fetch()){
      echo '姓名：'.$uid;
      echo "<br/>";
      echo '简介：'.$brief;
      echo "<br/>";
    }
  }
  //释放结果集
  $mysqli_stmt->free_result();
  $mysqli_stmt->close();
  $mysqli->close();
?>

5）创建表的执行语句

CREATE TABLE `mydb`.`demo` (`uid` INT NOT NULL AUTO_INCREMENT COMMENT '用户id' , `username` VARCHAR(255) NOT NULL COMMENT '用户名' , `age` INT NOT NULL COMMENT '年龄' , `gender` INT NOT NULL COMMENT '性别' , PRIMARY KEY (`uid`)) ENGINE = InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_bin;