苹果V10 相关 MYSQL 命令

随机 10条数据
SELECT * FROM `mac_vod` WHERE vod_id >= (SELECT floor(RAND() * (SELECT MAX(vod_id) FROM `mac_vod`)))  ORDER BY vod_id LIMIT 10

显示指定范围数据
其中0代表从第一条数据开始;
1代表往后去几条数据;
从0开始取5条数据
select * from `mac_vod` limit 0,5
从5开始取3条数据
select * from `mac_vod` limit 5,3

显示最后一条数据
select * from `mac_vod` order by `vod_id` desc limit 1


 随机显示指定类型5条数据
select * from `mac_vod` WHERE vod_id >= (SELECT floor( RAND() * ((SELECT MAX(vod_id) FROM `mac_vod`)-(SELECT MIN(vod_id) FROM `mac_vod`)) + (SELECT MIN(vod_id) FROM `mac_vod`))) AND `type_id`= 3 ORDER BY vod_id LIMIT 5";

SELECT * FROM `mac_vod` WHERE vod_id >= (SELECT floor(RAND() * (SELECT MAX(vod_id) FROM `mac_vod`)))  AND `type_id`= 3 ORDER BY vod_id LIMIT 10

显示指定库总数量
select count(*) FROM `mac_vod` WHERE `type_id` = 33

`mac_vod` 
`vod_time_add`

//正常显示数据  最新时间显示指定类型(cid)从第0条开始显示3条数据
SELECT * FROM `mac_vod` WHERE `type_id` = 1 ORDER BY `vod_time_add` DESC limit 0,3
 
//随机显示数据  随机显示指定类型5条数据
SELECT * FROM `mac_vod` WHERE vod_id >= (SELECT floor( RAND() * ((SELECT MAX(vod_id) FROM `mac_vod`)-(SELECT MIN(vod_id) FROM `mac_vod`)) + (SELECT MIN(vod_id) FROM `mac_vod`))) AND `type_id`= 33 ORDER BY vod_id LIMIT 5
 
//显示指定ID详细数据 .php?sql=vod_vod&type=id&vod_id=100 'SELECT * FROM ff4_vod WHERE `vod_id` =100
SELECT * FROM `mac_vod` WHERE vod_id = 100
 
//搜索影视名称 包含 老顽童 相关数据,并显示5条  ASC是指定列按升序排列,DESC则是指定列按降序排列
SELECT * FROM `mac_vod` WHERE `vod_name`  like '%老顽童%' ORDER BY `vod_time_add` DESC LIMIT 5
SELECT * FROM `mac_vod` WHERE `vod_name`  like '%老顽童%' ORDER BY `vod_time_add` DESC LIMIT 0,3
//搜索包含古天乐人相关数据,并显示5条
SELECT * FROM `mac_vod` WHERE `vod_actor`  like '%古天乐%' ORDER BY `vod_time_add` DESC LIMIT 5
// 从第1条开始 显示3条数据
SELECT * FROM `mac_vod` WHERE `vod_actor`  like '%古天乐%' ORDER BY `vod_time_add` DESC LIMIT 0,3
// 从第4条开始 显示5条数据
SELECT * FROM `mac_vod` WHERE `vod_actor`  like '%古天乐%' ORDER BY `vod_time_add` DESC LIMIT 3,5

//显示总数量
select count(*) FROM `mac_vod`
 
//显示指定库总数量
select count(*) FROM `mac_vod` WHERE `type_id` = 33
 
//显示指定时间后更新多少条数据
select count(*) FROM `mac_vod` WHERE vod_time_add > 1558022400
 
//显示指定时间后 指定类型更新多少条数据
select count(*) FROM `mac_vod` WHERE vod_time_add > 1558022400 and `type_id` = 10
select count(*) FROM `mac_vod` WHERE  `type_id` = 33 and vod_time_add > 1595779200
 
//显示当天更新多少条数据
select count(*) FROM `mac_vod` WHERE vod_time_add > 获取当天24点的时间戳
 
//显示当天 指定类型更新多少条数据 .php?sql=vod_vod&type=ciddttime&cid=10
select count(*) FROM `mac_vod` WHERE vod_time_add > $today and `type_id` = 10

20200727 时间戳 1595779200
输出调试文本 (时间_到时间戳 (到时间 (取日期 (取现行时间 ())), 真, ))

类型上级归  查看所以电影
mac_type
type_id 上级 type_id

电影 type_id = 1
上级 type_pid = 0 一级
动作 type_id = 6
上级 type_pid = 1 二级

查询一级分类为电影的数量
SELECT  count(*)  FROM `mac_vod` WHERE `type_id_1` = 1
查询一级分类为电影的数量 当天更新数据 20200727 时间戳 为 1595779200
SELECT  count(*)  FROM `mac_vod` WHERE `type_id_1` = 1 and vod_time_add > 1595779200

 

相关推荐

网友评论(0)