数据库查询语句(DBA)

select c.path from tsp_goodscategory c where = '联想笔记本';

select as 分类名称,

t.goodsquantity as 商品总数量,

t.sellgoodsquantity as 可卖商品数量

from tsp_goodscategory t

where

instr('4028807c33b057770133b0a2967d0017,4028807c33b057770133b0a342a30019,4028807c33 b057770133b0a7d851001f', t.id, 1, 1) > 0;

INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 出现的位置,默认为1

SQL> select instr("abcde",'b');

结果是2，即在字符串“abcde”里面，字符串“b”出现在第2个位置。如果没有找到，则返回0；不可能返回负数

1、substr(string string, int a, int b)

参数1:string 要处理的字符串

参数2：a 截取字符串的开始位置（起始位置是0）

参数3：b 截取的字符串的长度(而不是字符串的结束位置)

例如：

substr("ABCDEFG", 0); //返回：ABCDEFG，截取所有字符

substr("ABCDEFG", 2); //返回：CDEFG，截取从C开始之后所有字符

substr("ABCDEFG", 0, 3); //返回：ABC，截取从A开始3个字符

substr("ABCDEFG", 0, 100); //返回：ABCDEFG，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。

substr("ABCDEFG", 0, -3); //返回：EFG，注意参数-3，为负值时表示从尾部开始算起，字符串排列位置不变。

2、substr(string string, int a)

参数1:string 要处理的字符串

参数2：a 可以理解为从索引a（注意：起始索引是0）处开始截取字符串，也可以理解为从第（a+1）个字符开始截取字符串。

例如：

substr("ABCDEFG", 0); //返回：ABCDEFG, 截取所有字符

substr("ABCDEFG", 2); //返回：CDEFG，截取从C开始之后所有字符

select *

from tsp_goods g

where g.store_id =

(select id from tet_store t where t.storename = '709394')

and isdelete = '0'

and ismarketable = '1';

select * from tsp_product p join tsp_goods g on p.goods_id = g.id

where g.store_id=(select id from tet_store t where t.storename='709394') and p.isdelete='0' and p.i smarketable='1';

select*from tsso_member m where m.modifydate =to_date('2011-3-8 14:05:33','yyyy-mm-dd hh24:mi:ss');(日期格式查询)

select*from test09csc.tsso_member

where lower(substr(email,instr(email,'@','-1')+1,1000))

NOT in('','','','','')

or email is null

order by email;

substr(email, instr(email, '@', '-1')+1, 1000)

就是取@符号后的字符串。

如：123456@ 返回

-1 是instr从后往前找,找到第一个@

+1表示在@符号开始取

1000表示最大取1000个字符

select * from tet_store t where t.storename='1314';店铺

select * from tsp_goods g where g.store_id=(select id from tet_store t where t.storename='1314'); 商品

select * from tsp_goods g where g.store_id=(select id from tet_store s where s.storename='709394') and ='水杯';

select * from tetm_address;

select * from tetm_etmrefgoods;

select * from tetm_etminfo

查询金额

select *

from tet_account a

join tsso_member m on a.owner = m.id

where ername = '709394';

select *

from tsp_goods g

where g.store_id =

(select id from tet_store t where t.storename = '709394')

and isdelete = '0'

and ismarketable = '1';

select *

from tsp_product prod

left join tsp_goods gs on prod.GOODS_ID = gs.id

where gs.Store_Id = '4028807c30daa6680130df6db1ae00d8'

and gs.isdelete = '0'

and gs.ismarketable = '1'

select *

from (select t.delivery_branch_store_id

from tms_order t, tms_branch_store tbs

where t.delivery_branch_store_id = tbs.id

group by t.delivery_branch_store_id) temp

left join tms_order td on td.delivery_branch_store_id =

temp.delivery_branch_store_id

left join tms_member tm on tm.id = td.member_id;

查询tsp_product 表

select * from tsp_product prod left join tsp_goods gs on prod.GOODS_ID=gs.id where gs.Store_Id='4028807c30daa6680130df6db1ae00d8'

and prod.isdelete=0 and prod.ISMARKETABLE=1

单一查询数据库：

select prod.*

from tsp_product prod

left join tsp_goods gs on prod.GOODS_ID = gs.id

where gs.Store_Id = '4028807c30daa6680130df6db1ae00d8'

and prod.isdelete = 0

and prod.ISMARKETABLE = 1

select t.goodssn, count(*)

from tsp_goods t

where t.store_id = '4028807c30daa6680130df6db1ae00d8'

group by t.goodssn

select * from tsp_goods g where g.store_id=(select id from tet_store s where s.storename='709394')