随笔分类 -  Sqlserver

摘要：1.updatemem_localmemberbaseinfosetbalance=(selectbfrom(selectcount(uniqueid)asa,sum(balance)asbgroupbyuniqueidhavingcount(uniqueid)>1)x)whereuniqueid=(selectdfrom(selectcount(uniqueid)asc,uniqueid asdgroupbyuniqueidhavingcount(uniqueid)>1)x)andid>100000;2.delete from mem_localmemberbaseinfo 阅读全文

摘要：1.CREATE DATABASEuse mastergocreate database ssq on(name=ssq_dat,filename='e:\ssq.mdf',size=100mb,maxsize=500mb,filegrowth=10mb)log on(name=ssq_log,filename='e:\ssq.ldf',size=50mb,maxsize=300mb,filegrowth=5mb);go2.CREATE TABLEcreate table ssq_data2003(date_ssq int NOT NULL,number_ssq 阅读全文

摘要：1.Union两个结果集a、b中所包括的所有行，消除重复行。Union All不消除重复行。2.Except两个结果集a、b，只存在于a中，但不存在于b中的行。Except All不消除重复行。3.Intersect两个结果集a、b，既存在于a又存在于b的行。All时不消除匹配行。4.left outer join结果包含左表中的所有行和匹配行。5.right outer join结果包含右表中的所有行和匹配行。6.full outer join不仅包括匹配行，还包括左右表中的所有行。 阅读全文

摘要：1.建立存储过程create procedure s_memberasdeclare @su intselect @su = sumu from ( select count(uniqueid) as count,sum(balance) as sumu from mem_localmemberbaseinfo group by uniqueid having count(uniqueid)>1) xreturn @su2.调用存储过程declare @sum_m intexecute @sum_m=s_memberselect @sum_m 阅读全文

摘要：1.检索数据单列：select a from table where...多列：select a,b from table where...所有列:select * from table where...不同值:select distinct a from table where...限制结果:select top 5 a from table where...按名称排序检索:select a,b from table where... order by a,b按位置排序检索:select a,b from table where... order by 2,1降序检索：select a,b 阅读全文

摘要：1.确保local.dbo.leveltype和local1.dbo.leveltype两个表的levelid和levelname不一样。2.确保local.dbo.mem_localmemberbaseinfo和local1.dbo.mem_localmemberbaseinfo中的id不重复。update mem_localmemberbaseinfo set id=id+100000;3.local.dbo.mem_localdisountaccount和local1.dbo.mem_localdiscountaccount同步骤2。update mem_localdiscountacc 阅读全文

摘要：1.内联结(等值联结)select vend_name,prod_name,prod_pricefrom vendors,productswhere vendors.vend_id = products.vend_id等效于select vend_name,prod_name,prod_pricefrom vendors inner join productson vendors.vend_id = products.vend_id2.左联结select customers.cust_id,orders.order_numfrom customers left outer join order 阅读全文

摘要：1.查询sqlserver中所有数据库select [name] from [sysdatabases] order by [name]2.查询某个数据库中的所有表select [id], [name] from [sysobjects] where [type] = 'u' order by [name]3.查询某表中的所有字段select [name] from [syscolumns] where [id] = 21575 order by [colid]4.查询某数据库中的所有触发器select * from sysobjects where xtype='TR 阅读全文

摘要：::完整备份declare @filename nvarchar(100) set @filename='e:\\databack\\data'+convert(char(8),getdate(),112)+'.bak' print @filename BACKUP DATABASE [local] TO DISK = @filename WITH NOINIT , NOUNLOAD , NAME = N'aWkSQLBAK', NOSKIP, STATS = 10, NOFORMAT::完整备份基础上进行差异备份declare @filenam 阅读全文

摘要：1.查询某一列或多列的重复记录，只能查出重复的值，不能列出整条记录的信息selectid,name from tbgroupby id,namehaving(count(*))>12.查询某一列重复的记录，并列出全部select*from tbwhereid in (select stuid from stuinfogroupby stuidhaving(count(*))>1)3.查询某一列重复的记录，列出多余的记录，比如重复三条，列出多余的两条前提：需要有一个不重复的列，例如下例中的recno,重复的是stuidselect*from stuinfo s1where recno 阅读全文

摘要：1.查询可用数据库，排除系统库select * from sysdatabases where dbid>42.查询可用表，排除系统表select * from sysobjects where xtype='u'3.删除/建立数据库drop/create database students;4.删除/新建表drop table tablename;create table tb (id int,name varchar(20),sex varchar(20))5.插入记录insert into tb (id,name,sex) values ('1',& 阅读全文

摘要：insert into tongxunlu (name,address,email) select sname,saddress,semail from students;select students.sname,students.saddress,students.semail into tongxunlu from students;select students.sname,students.saddress,students.semail,identity(int,1,1) as studentid into tongxunlu from students;update studen 阅读全文