浅谈前后端交互之mysql基础

数据库增删查改

操作数据库的基本步骤大多相同，只不过是sql语句结构的区别。不同的sql语句能够完成不同的功能，因此学习如何操作数据库实际上就是在学习如何编写sql语句。

(1)使用sql语句基本【模板】
(2)sql查询语句
(3)sql插入语句
(4)sql修改语句
(5)sql删除语句

增删改

//1 插入语句1
//$sql = "insert into 表名(字段1,字段2,...) values ('值1','值2',...)";
$sql = "insert into userinfo(username,password) values('范冰冰','1234')";
//2 插入语句2
//$sql = "insert into 表名 values(值1,值2,...)";
$sql = "insert into userinfo values(null,'吴亦凡','666')";
//3 修改语句
//$sql = "update  表名 set 字段1='新值1',… where 条件（id=$id）";
$sql = "update userinfo set username = 'wy' where id = 5";
//4 删除语句
//$sql = 'delete from 表名 where 条件';
$sql = 'delete from userinfo where id = 4';
//总结：插入语句 修改语句 删除语句 都是返回boolean值 成功true 失败false

查

//1  查询所有 $sql = "select [信息] from [表名] where [查询条件]";
$sql = "select * from userinfo";
//object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(3) ["lengths"]=> NULL ["num_rows"]=> int(5) ["type"]=> int(0) }
        
//2  查询表里的用户名
$sql = "select username from userinfo";
//object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(1) ["lengths"]=> NULL ["num_rows"]=> int(5) ["type"]=> int(0) }

//3  查询id = 1
$sql = "select * from userinfo where id = 1";
//object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(3) ["lengths"]=> NULL ["num_rows"]=> int(1) ["type"]=> int(0) }

//4  查询id = 4（不存在）
$sql = "select * from userinfo where id = 4";
//object(mysqli_result)#2 (5) { ["current_field"]=> int(0) ["field_count"]=> int(3) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) }

//5  查询账号admin 密码123456 的数据（登录）
$sql = "select * from userinfo where username = 'admin' and password = '123456'";
//Array ( [0] => Array ( [id] => 1 [username] => admin [password] => 123456 ) )

//6  查询账号是wyf 或者 wy
$sql = "select * from userinfo where username = 'wyf' or username = 'wy'";
//Array ( [0] => Array ( [id] => 2 [username] => wyf [password] => 123456 ) [1] => Array ( [id] => 5 [username] => wy [password] => 1234 ) )

//7 like % 模糊查询
$sql = "select * from userinfo where username like '%w%'";
//Array ( [0] => Array ( [id] => 2 [username] => wyf [password] => 123456 ) [1] => Array ( [id] => 5 [username] => wy [password] => 1234 ) )

//8 查询账号和密码
$sql = "select username,password from userinfo";
//Array ( [0] => Array ( [username] => admin [password] => 123456 ) [1] => Array ( [username] => wyf [password] => 123456 ) [2] => Array ( [username] => 范冰冰 [password] => 1234 ) [3] => Array ( [username] => wy [password] => 1234 ) [4] => Array ( [username] => 吴亦凡 [password] => 666 ) )

连接数据库

<?php

    //1.创建连接数据库
    //语法：mysqli_connect('域名','账号','密码','数据库','端口号')
    $con = mysqli_connect('localhost','root','','database');
    //var_dump($con);//连接成功返回object，失败bool(false)

    if ($con) {
        //2.设置编码格式
        mysqli_query($con,'set names utf8');

        //3.sql语句  插入语句
        //语法：$sql = "insert into 表名(字段1,...) values(字段1值,...)";
        $sql = "insert into userinfo(username,password) values('wyf','123456')";

        //4.发送sql语句  执行sql
        $result = mysqli_query($con,$sql);

        var_dump($result);//bool(true)

    }else{
        echo '数据库连接失败';
    }

    //5.关闭数据库
    mysqli_close($con);

?>