数据库查询语句(DBA).doc

select c.path from tsp_goodscategory c where = 联想笔记本;select as 分类名称, t.goodsquantity as 商品总数量, t.sellgoodsquantity as 可卖商品数量 from tsp_goodscategory t where instr(4028807c33b057770133b0a2967d0017,4028807c33b057770133b0a342a30019,4028807c33b057770133b0a7d851001f, t.id, 1, 1) 0;INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置,默认为1J 出现的位置,默认为1SQL 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.idwhereg.store_id=(selectidfromtet_storetwheret.storename=709394)andp.isdelete=0andp.ismarketable=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 (139.com,,126.com,163.com,21) 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 m.username = 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 = 1select * 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