mysql常用语句练习-基于ecshop2.7.3数据库(1)

复制代码 代码如下:

SELECT * FROM ecs_goods WHERE goods_id = 1;
SELECT goods_id, goods_name FROM ecs_goods WHERE goods_id = 1;
SELECT goods_id,cat_id,goods_name FROM ecs_goods WHERE cat_id !=
3;
SELECT goods_id,cat_id,goods_name FROM ecs_goods WHERE cat_id
<> 3;
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE
shop_price > 3000;
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE
shop_price <= 100;

sql语句:

function cat_options($spec_cat_id, $arr)
{
static $cat_options = array();
if (isset($cat_options[$spec_cat_id]))
{
return $cat_options[$spec_cat_id];
}
/*
开头化关键参数:
$level:当前子节点深度
$last_cat_id:当前父节点ID
$options:带有缩进品级的数组
$cat_id_array:沿同一路线的父节点依次进驻
$level_array:该节点的子节点深度,也是逐个进驻
*/
if (!isset($cat_options[0]))
{
$level = $last_cat_id = 0;
$options = $cat_id_array = $level_array = array();
while (!empty($arr))//如若还应该有待构造的节点则继续遍历
{
foreach ($arr AS $key => $value)
{
$cat_id = $value[‘cat_id’];
//超级分类结点
if ($level == 0 && $last_cat_id == 0)
{
if ($value[‘parent_id’] > 0)
{
break;
}
$options[$cat_id] = $value;
$options[$cat_id][‘level’] = $level;
$options[$cat_id][澳门金沙vip,’id’] = $cat_id;
$options[$cat_id][‘name’] = $value[‘cat_name’];
//遍历过了就不再遍历
unset($arr[$key]);
if ($value[‘has_children’] == 0)
{
continue;
}
$last_cat_id = $cat_id;//下层结点的老爹结点
$cat_id_array = array($cat_id);
$level_array[$last_cat_id] = ++$level;
continue;
}
//当前结点的老爹结点ID等于它的上一流结点ID
if ($value[‘parent_id’] == $last_cat_id)
{
$options[$cat_id] = $value;
$options[$cat_id][‘level’] = $level;
$options[$cat_id][‘id’] = $cat_id;
$options[$cat_id][‘name’] = $value[‘cat_name’];
unset($arr[$key]);//遍历过了就不再遍历
//如若当前结点有子女则当前结点要进驻,但不再遍历;反之不进驻也不再遍历
if ($value[‘has_children’] > 0)
{
if (end($cat_id_array) != $last_cat_id)
{
$cat_id_array[] = $last_cat_id;
}
$last_cat_id = $cat_id;//当现结点做为下超级结点的新的生父结点
$cat_id_array[] = $cat_id;//进驻

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE
cat_id = 4 OR cat_id = 3;
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE
cat_id IN( 3, 4 );

 cat_id  shop_goods_type   cat_id  limit 

$level_array[$last_cat_id] = ++$level;//当前结点的下拔尖结点深度
}

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE
shop_price BETWEEN 100 AND 858;

cat_id shop_goods_type cat_id
limit…

}
elseif ($value[‘parent_id’] > $last_cat_id)
{//假若当前结点老爸深度抢先方今阿爹结点的纵深则实行下一轮循环
break;
}
}//endforeach
$count = count($cat_id_array);
if ($count > 1)
{
//抽出最后进驻的老爸节点作为当下父亲节点
$last_cat_id = array_pop($cat_id_array);
}
elseif ($count == 1)
{
if ($last_cat_id != end($cat_id_array))
{
//进驻的老爸结点只有八个时还要未有作为当下阿爸节点时把它抽取
$last_cat_id = end($cat_id_array);
}
else
{ //不然最终收取的老爸结点一定是顶级分类结点
$level = 0;
$last_cat_id = 0;
$cat_id_array = array();
continue;
}
}

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE
cat_id != 3 AND cat_id != 4
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE
cat_id NOT IN( 3, 4 );
SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE
cat_id NOT BETWEEN 3 AND 4

if ($last_cat_id && isset($level_array[$last_cat_id]))
{
//抽出当前结点的深浅
$level = $level_array[$last_cat_id];
}
else
{
$level = 0;
}
}//end
while,此时已到位非递归前序遍历构造树的干活,当中$options已保存了从根结点初叶的具备结点带有分层性质的数组
$cat_options[0] = $options;
}
else
{
$options = $cat_options[0];
}
//若是从0开头即取全方位树则直接再次来到不再管理.
if (!$spec_cat_id)
{
return $options;
}
//否则开端从内定结点截取,以下相比较不难笔者要么某个说说啊,要说就说多少个参数含义吧
/*
$spec_cat_id_level:截取结点的深浅
$spec_cat_id_array:最终回到的以该结点为根结点的一棵商品归类树
最后回到的数组是这么排序的:按老爹结点大小,按间接阿爹结点,按同一阿爹结点那样的先根遍历,具个例证:
一流结点有1,5 二级结点有2,6,7
三级结点有8,9,假设1的直接孩子是2,6而2的一贯孩子是8,9;别的
5的一直孩子是7那么最终的数组是如此排列的1->2->8->9->6->5->7
*/
else
{
if (empty($options[$spec_cat_id]))
{
return array();
}
$spec_cat_id_level = $options[$spec_cat_id][‘level’];

SELECT goods_id,cat_id,goods_name,shop_price FROM ecs_goods WHERE (
shop_price > 100 AND shop_price < 300 )
OR ( shop_price > 1000 AND shop_price < 3000 );

foreach ($options AS $key => $value)
{
if ($key != $spec_cat_id)
{
unset($options[$key]);
}
else
{
break;
}
}
$spec_cat_id_array = array();
foreach ($options AS $key => $value)
{
if (($spec_cat_id_level == $value[‘level’] && $value[‘cat_id’]
!= $spec_cat_id) ||
($spec_cat_id_level > $value[‘level’]))
{
break;
}
else
{
$spec_cat_id_array[$key] = $value;
}
}
$cat_options[$spec_cat_id] = $spec_cat_id_array;
return $spec_cat_id_array;
}
}

SELECT goods_id,cat_id,goods_name,shop_price,click_count FROM
ecs_goods WHERE (cat_id = 3 ) AND ( shop_price >= 1000 AND
shop_price <= 3000 )
AND ( click_count > 5 )

您恐怕感兴趣的小说:

  • php实现Infiniti级分类(递归方法)
  • php
    Infiniti级分类,一级轻便的极端级分类,辅助出口树状图
  • php完结Infiniti级分类
  • php实现Infiniti级分类查询(递归、非递归)
  • ThinkPHPInfiniti级分类原理达成留言与还原成效实例
  • php+mysql达成Infiniti级分类 |
    树型显示分类关系
  • ThinkPHP自动填写达成Infiniti级分类的章程
  • php实现Infiniti级分类完成代码(递归方法)
  • PHP完结递归Infiniti级分类
  • PHPTree——php急忙变动Infiniti级分类

 

SELECT * FROM ecs_goods WHERE cat_id IN ( 2, 3, 4, 5 )

SELECT goods_id,cat_id,goods_name FROM ecs_goods WHERE goods_name
LIKE ‘诺基亚%’

 

 #把表中字段num取值范围为20~29之间的值造成20【注:ecshop2.7.3尚未这一个ecs_mian1这几个表,能够团结创办三个,他就二个字段num,
int类型,然后填充一些测量检验数据】

UPDATE ecs_mian1 SET num = ( FLOOR( num / 10 ) * 10 ) WHERE num >=
20 AND num <= 29

 #把表中字段num取值范围为30~39里头的值形成30

UPDATE ecs_mian1 SET num = ( FLOOR( num / 10 ) * 10 ) WHERE num
BETWEEN 30 AND 39

 

#以”HUAWEI”开端的货品

SELECT goods_id, goods_name FROM ecs_goods WHERE goods_name LIKE
‘诺基亚%’;

#截取”三星”前边的商品名称,并用”金立”起首 连接起来
SELECT goods_id, CONCAT( ‘小米’, SUBSTRING( goods_name, 4 ) ) FROM
ecs_goods WHERE goods_name LIKE ‘诺基亚%’;

#截取”小米”前边的商品名称,并用”Nokia”起头 连接起来后更新
UPDATE ecs_goods SET goods_name = CONCAT( ‘小米’, SUBSTRING(
goods_name, 4 ) ) WHERE goods_name LIKE ‘诺基亚%’

#以地点的讲话相反
UPDATE ecs_goods SET goods_name = CONCAT( ‘诺基亚’, SUBSTRING(
goods_name, 4 ) ) WHERE goods_name LIKE ‘小米%’