--查表
select a.name, b.name from syscolumns a, sysobjects b
where lower(a.name) like '%stockid%'
and a.id = b.id
and b.xtype = 'u' and b.name like 'tb%'
2、将明细数据组合成一个字段
select * into #tbComRelation from (
select 1 goodsid,'a' name,1 comgoodsid union all
select 2 goodsid,'b' name,1 comgoodsid union all
select 3 goodsid,'c' name,1 comgoodsid
)t
select 1 comgoodsid, 'comName' comname into #tbComGoods
select *, stuff(replace(replace((select name n from #tbComRelation t where comgoodsid=com.comgoodsid FOR XML AUTO),'<t n="','+'),'"/>',''),1,1,'')
from #tbComGoods com
FOR XML AUTO是关键。stuff和replace都只是替换字符
SELECT a=(SELECT name+'+' FROM #tbComRelation WHERE comgoodsid=1 FOR XML PATH(''))
3、mysql数据导出到txt,再从txt导入到数据库
当你一前一后地使用 SELECT ... INTO OUTFILE 和 LOAD DATA INFILE 将数据从一个数据库写到一个文件中,然后再从文件中将它读入数据库中时,两个命令的字段和行处理选项必须匹配。否则,LOAD DATA INFILE 将不能正确地解释文件内容。假设你使用 SELECT ... INTO OUTFILE 以逗号分隔字段的方式将数据写入到一个文件中:
mysql> SELECT * INTO OUTFILE 'data.txt' -> FIELDS TERMINATED BY ',' -> FROM ...;
为了将由逗号分隔的文件读回时,正确的语句应该是:
mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY ',';
4、mysql拷贝表数据
create table bb2 select * from jos_sobi2_fields where
sqlserver拷贝表数据
select * into #tbtemp from tbtemp where
mysql和sqlserver通用查询数据,并添加到其他表
insert test2 (itemid,title) (SELECT itemid,title FROM `jos_sobi2_item` limit 0,10)
5、mysql存储过程
(1)创建:不带参数:
(a)create procedure p()
select 'hello world!'
(b)create procedure p1()
select count(*) from table
(2)查看存储过程:
方法一:select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
方法二:show procedure status;
方法三:show create procedure sp_name
(3)删除存储过程
drop procedure sp_name
sql += @" and exists (select * from tbBoardToShop as bts inner join tbMixBoard as mb on bts.mixboardid=mb.mixboardid where boardtype='4' and sp.shopid=bts.shopid)";
6、sqlserver权限设置
exec sp_grantdbaccess '用户名' //添加用户访问数据库
EXEC sp_revokedbaccess '用户名' //删除用户访问数据库
GRANT SELECT TO 用户名 //添加用户查询所有表
GRANT SELECT ON 表名 TO 用户名
7、sqlserver使用任务执行语句
begin tran 开始任务
delete from test
commit tran 完成语句(完成和回滚语句必须二选一)
rollback 回滚(完成和回滚语句必须二选一)