案例

案例:Laravel
在小说列表中附带上前10条评论?,在收获小说列表时还要把各个篇章的前10条商量一齐查询出来。

那是卓越分区查询案例,要求依靠 comments 表中的 post_id 字段举办分区,同有的时候间依靠法则实行排序,把相符条件的前 N 条是数据收取来。

在别的数据库(OracleSQL ServerVertica)
包含了 row_number partition by 那样的函数,能够相比较便于的落到实处。

比如在 SQL Server 中:

SELECT * FROM (
SELECT *, row_number() OVER (partition by post_id ORDER BY created_at desc) rank FROM comments where post_id in (1,2,3,4,5) 
) b where rand < 11;

在 mysql 中要复杂一些,大家先来看看下面案例中实现供给的两种消除办法。

–1 GETDATE() 重临当前系统日期
SELECT GETDATE()

一. 概述 

 存储进度和函数是事先经过编写翻译并积存在数据库中的一段sql语句集结,可以简化使用开辟人员的大队人马做事,减弱数量在数据库与应用服务器之间的传导,升高数据管理功能是有实益的。存款和储蓄进程和函数的界别在于函数必得有再次回到值,存款和储蓄进程的参数能够应用in,out
,inout类型,而函数参数只好是in类型。 创设须求create routine 权限,
修改删除供给alter routine权限,施行须要execute权限。

  1.1 创制存款和储蓄进度,调用,删除

-- 创建
DELIMITER $$
CREATE PROCEDURE proc_city(IN city_id INT ,OUT num INT)
READS SQL DATA
BEGIN
    --  要设置初始值,不然为null 与sqlserver一样
    SET num=0;
    SELECT * FROM city WHERE city.city_id=city_id;
    SET num=num+1;
END $$
DELIMITER ;

 --  调用
CALL proc_city (2,@num); SELECT @num;

澳门微尼斯人手机版 1澳门微尼斯人手机版 2

-- 删除
DROP  PROCEDURE proc_city

澳门微尼斯人手机版,  特征值介绍:
  CREATE PROCEDURE proc_name()
    { contains sql | no sql | READS SQL DATA | modifies sql data}
  那么些特征值只是提须要服务器,并从未基于那些特征值来约束进度实际上运用数据的状态。
contains sql 表示子程序不分包读或写多少的言辞。no sql表示子程序不带有sql
语句。 READS SQL DATA:表示子程序包括数据的话语,但不富含写多少。modifies
sql data 表示子程序包罗写多少。默许是contains sql。

  1.2 创设函数,调用,删除

-- 创建
SET GLOBAL log_bin_trust_function_creators=TRUE;
DELIMITER $$
CREATE FUNCTION fn_city (city_count INT)
RETURNS INT 
BEGIN
DECLARE  city_count1 INT;
    --  要设置初始值,
    SET city_count1=0;
    SET  city_count1=city_count+1;
    RETURN city_count1;
END $$
DELIMITER ;

-- 调用
SELECT  fn_city (2);

澳门微尼斯人手机版 3

-- 删除
DROP FUNCTION fn_city

  1.3 查看音讯

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='proc_city' OR  ROUTINE_NAME='fn_city'

澳门微尼斯人手机版 4

  1.4 变量的选用

DELIMITER $$
CREATE PROCEDURE proc_demo_declare()
READS SQL DATA
BEGIN
    -- 定义 
    DECLARE i INT ;
    -- 赋值
    SET i=10;
    SELECT i;
    -- 第二种赋值
    SELECT COUNT(1) INTO i  FROM city;
    SELECT i;
END $$
DELIMITER ;

call proc_demo_declare;

      澳门微尼斯人手机版 5    澳门微尼斯人手机版 6

1.5 定义Handler碰着难题的拍卖步骤

--  语法
DECLARE
{EXIT | CONTINUE}
HANDLER FOR
{error-number | SQLSTATE error-STRING | CONDITION}
SQL statement

  (1) 管理项目{EXIT | CONTINUE} continue:
当蒙受实施错误时,跳过继续上边包车型客车话语。exit: 当遇到施行错误时,甘休语句
  (2) 触发条件 (SQLSTATE,MYSQL
EWranglerROWrangler,CONDITION)MYSQL错误代码。ANSI-standard SQLSTATE
code。命名法则。可使用系统内置的SQLEXCEPTION,SQLWA福特ExplorerNING和NOT FOUND。
  (3)错误触发的操作。

--   以continue为例
DELIMITER $$
CREATE PROCEDURE proc_demo_handler_continue()
BEGIN
    -- 定义条件 continue : 当遇到执行错误时,跳过继续下面的语句
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;    

    -- 这条可以插入
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(2,'中国','cn4');
    SET @x=1;

    -- 这条不能插入,外键约束fk_city_country
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(8,'中国','cn5');
    SET @x=2;

    -- 这条可以插入
    INSERT INTO city(country_id,cityname,Citycode) VALUES(2,'中国','cn6');
    SET @x=3;
END $$
DELIMITER ;

-- 调用 
CALL proc_demo_handler_continue; SELECT @x,@x2;

  使用了continue:
当遇到试行错误时,跳过继续下边包车型客车言语,三条语句,只插入成功二条成功

澳门微尼斯人手机版 7

  

--  以exit为例
DELIMITER $$
CREATE PROCEDURE proc_demo_handler_exit()
BEGIN
    -- 定义条件  : 当遇到执行错误时,停止语句  
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @x2 = 1;    

    -- 这条可以插入
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(2,'中国','cn4');
    SET @x=1;

    -- 这条不能插入,外键约束fk_city_country
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(8,'中国','cn5');
    SET @x=2;
    --  这条不可以插入
    INSERT INTO city(country_id,cityname,Citycode) VALUES(2,'中国','cn6');
    SET @x=3;
END $$
DELIMITER ;

-- 调用
CALL proc_demo_handler_exit;SELECT @x,@x2;

  使用了exit: 当碰着实践错误时,甘休语句, 三条语句,只插入成功首先条

澳门微尼斯人手机版 8

 

消除办法

澳门微尼斯人手机版 9

方法1:

在 blade 中要展现商议数据的地点 post->comments()->limit(10)

题目:假使取了 20 条 Post 数据,就能够有 20 条取 comments 的 sql
语句,会导致实施的 sql 语句过多。

不是卓殊可取,首要难点会导致 SQL
语句过多,对数据库服务器产生压力,但是这里能够动用缓存来改革,然而不在本文章切磋范围里。

 

方法2:

一贯通过 with 把 Post 的全体 comments 数据都收取来,在 blade 中 post->comments->take(10)

主题素材:Laravel
会预先把小说全部的评论和介绍数据查询出来,要是小说的钻探数据相当多,可能会招致内部存储器泄漏。

–2 DATEADD(日期部分,常数,日期)
再次来到将日期的钦命日期部分加常数后的结果回到

方法3:

$posts = Post::paginate(15);

$postIds = $posts->pluck('id')->all();

//找出符合条件的 comments ,同时定义 @post, @rank 变量,这里没有用 all,get 等函数,此时并不会执行 SQL 语句。
$sub = Comment::whereIn('post_id',$postIds)->select(DB::raw('*,@post := NULL ,@rank := 0'))->orderBy('post_id');

//把上面构造的 sql 查询作为子表进行查询,根据 post_id 进行分区的同时 @rank 变量不断+1
$sub2 = DB::table( DB::raw("({$sub->toSql()}) as b") )
            ->mergeBindings($sub->getQuery())
            ->select(DB::raw('b.*,IF (
            @post = b.post_id ,@rank :=@rank + 1 ,@rank := 1
        ) AS rank,
        @post := b.post_id'));

//取出符合条件的前10条comment
$commentIds = DB::table( DB::raw("({$sub2->toSql()}) as c") )
            ->mergeBindings($sub2)
        ->where('rank','<',11)->select('c.id')->pluck('id')->toArray();

$comments = Comment::whereIn('id',$commentIds)->get();

$posts = $posts->each(function ($item, $key) use ($comments) {
    $item->comments = $comments->where('post_id',$item->id);
});

会发生三条sql

select * from `posts` limit 15 offset 0;

select `c`.`id` from (select b.*,IF (
@post = b.post_id ,@rank :=@rank + 1 ,@rank := 1
) AS rank,
@post := b.post_id from (select *,@post := NULL ,@rank := 0 from `comments` where `post_id` in ('2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16') order by `post_id` asc) as b) as c where `rank` < '11';

select * from `comments` where `id` in ('180', '589', '590', '3736');

日期部分能够是:

知识点

  1. toSql() 方法的法力是为着拿走不分包 binding 参数的 SQL,
    约等于说带问号的 SQL
  2. getQuery() 方法的功用是为着拿走 binding参数并取代 toSql() 获得SQL的问号,从而获取完全的SQL
  3. raw() 的成效是一向把 SQL 套进 Laravel 的查询构造器中。
  4. mysql 查询语句中定义变量 @post := NULL ,@rank := 0 以及 IF 函数的选拔
  5. 何以创设子查询。

何以不直接用原生 SQL 语句来兑现?

此处之所以坚贞不屈运用 Laravel Query Builder 来完结,能够使得幸免 SQL 注入,并且和 ORM 的 Model 对象关联起来。

澳门微尼斯人手机版 10

 

–常数为正
SELECT DATEADD(YY,1,GETDATE())

–等同于
SELECT DATEADD(YEAR,1,GETDATE())

澳门微尼斯人手机版 11

 

–常数为负数
SELECT DATEADD(YY,-2,GETDATE())

澳门微尼斯人手机版 12

 

–常数为小数(直接舍去小数部分)
SELECT DATEADD(YY,2.4,GETDATE())
SELECT DATEADD(YY,2.5,GETDATE())
SELECT DATEADD(YY,2.6,GETDATE())

发表评论

电子邮件地址不会被公开。 必填项已用*标注