数据库表达式查询 - 原生mysql查询 - 事务操作 - 翻页

作业内容：1、练习数据库表达式：普通查询、表达式查询、快捷查询、区间查询、组合查询 2、练习原生mysql查询 3、练习事务操作 4、练习翻页功能

1. 练习数据库表达式

$res[] = Db::table('boke')->field(['id', 'title'])->find(1);
$res[] = Db::table('boke')->field(['id', 'title'])->where('id', 2)->find();
$res[] = Db::table('boke')->field(['id', 'title'])->select(['id' => 3])->toArray();
$res[] = Db::table('boke')->field(['id', 'title'])->where([['id', '=', 4]])->select()->toArray();
$res[] = Db::table('boke')->field(['id', 'title'])->where('id', 1)->value('title');
$res[] = Db::table('boke')->field(['id', 'title'])->where([['id', '<', 2]])->column('title');
$res[] = Db::table('boke')->field(['id', 'title'])->where([['id', '<', 3]])->column('title', 'id');
$res[] = Db::getLastSql();
$res[] = Db::table('boke')->field(['id', 'title'])->find(0);
$res[] = Db::table('boke')->field(['id', 'title'])->fetchSql()->find(0);
$res[] = Db::table('boke')->field(['id', 'title'])->where('id', 0)->select()->toArray();
$res[] = Db::table('boke')->field(['id', 'title'])->where('id', 0)->fetchSql()->select();
halt($res);

查询测试1

// null  not null
$res[] = Db::table('boke')->where('id', 1);
// > >= < <= <>
$res[] = Db::table('boke')->where('id', '=', 2);
$res[] = Db::table('boke')->where([['id', '=', 3]]);
// like  not like
$res[] = Db::table('boke')->where([['title', 'like', '%php%']]);
// in  not in  between  not between
$res[] = Db::table('boke')->where([['id', 'in', '4,5']]);
$res[] = Db::table('boke')->where([['id', 'between', [6,7]]]);
$res = array_map(function($item){
    $item = $item->field(['id', 'title', 'img'])->limit(1)->select()->toArray();
    return $item;
}, $res);
halt($res);

查询测试2

// Null NotNull
$res[] = Db::table('boke')->whereNull('img');
// Like NotLike
$res[] = Db::table('boke')->whereLike('img', '%/upload/%');
// In  NotIn  Between  NotBetween
$res[] = Db::table('boke')->whereIn('id', '1,2');
$res[] = Db::table('boke')->whereBetween('id', [3,4]);
$res[] = Db::table('boke')->where([['id', 'in', [5,6]]])->whereOr('id', 7);
$res = array_map(function($item){
    $item = $item->field(['id', 'title', 'img'])->limit(1)->select()->toArray();
    return $item;
}, $res);
halt($res);

查询测试3

$res[] = Db::table('boke')->whereNull('img')->count();
// max min avg sum
$res[] = Db::table('boke')->whereLike('img', '%/upload/%')->max('num');
$res[] = Db::table('boke')->where([['id', 'in', [5,6]]])->whereOr('id', 7)->min('id');
halt($res);

查询测试4

2. 练习原生mysql查询

// execute 增改
$res[] = Db::execute("insert `boke` (`title`, `content`) values ('title', 'content'), ('title2', 'content2')");
$res[] = Db::execute("update `boke` set `content`='content2-modify' where `title`='title2'");
// query 查删
$res[] = Db::query("select `id`, `title`, `content` from `boke` where id > :id order by `id` desc limit 0,2", ['id' => 0]);
$res[] = Db::query("delete from `boke` where `title` like 'title%'");
halt($res);

原生mysql查询

3. 练习事务操作

$res[] = Db::transaction(function(){
    $id = Db::table('cat')->insertGetId(['name' => 'category']);
    Db::table('boke')->insert(['title' => 'article', 'cat' => $id]);
});
$res[] = Db::table('boke')->field(['id', 'title', 'cat'])->order('id', 'desc')->limit(1)->select()->toArray();
$res[] = Db::table('cat')->where('name', 'category')->delete();
$res[] = Db::table('boke')->where('title', 'article')->delete();
halt($res);

事务操作

4. 练习翻页功能

控制器 app\index\controller\index.php

namespace app\index\controller;
use think\facade\Db;
use app\BaseController;
use think\facade\View;
use think\facade\Request;
class Index extends BaseController
{
    // 练习
    public function demo()
    {
        $list = Db::table('boke')->where('status', 1)->paginate([
        'list_rows' => 10,
        // 'var_page' => 'page',
        // 'page' => Request::get('page'),
        // 'query' => Request::param()
        ]);
        // $page = $list->render(); // 分页显示
        $total = $list->total();
        return view::fetch('demo', compact('list', 'total'));
    }
}

视图 app\index\view\index\demo.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
<h3>数据分页</h3>
<ul>
    {volist name='list' id='vo'}
    <li>{$vo.id} - {$vo.title}</li>
    {/volist}
</ul>
Total: {$total} pages: {$list|strip_tags='<a>'|raw}
</body>
</html>

数据分页

博客分页： https://www.php.cn/blog/detail/27641.html

数据库表达式查询 - 原生mysql查询 - 事务操作 - 翻页

数据库表达式查询 - 原生mysql查询 - 事务操作 - 翻页

1. 练习数据库表达式

2. 练习原生mysql查询

3. 练习事务操作

4. 练习翻页功能

更多相关文章

随机推荐