TSQL实用小代码.doc

【SQL Server 数据库导入导出部分】 1、在查询分析器下查询Excel文档 SELECT * FROM OpenDataSource(Microsoft.Jet.OLEDB.4.0,Data Source=c:测试.xls; User ID=Admin;Password=;Extended properties=Excel 8.0).Sheet1$ 2、从数据库中导出数据并存到文件中 EXEC master.xp_cmdshell bcp CAS2004.HGZ_LIAOJIAN out c:temp1.xls -c -q -S. -Usa -P 3、从文件中导入数据到数据库对应表中 EXEC master.xp_cmdshell bcp CAS2004.HGZ_LIAOJIAN in c:temp1.xls -c -q -S. -Usa -P 【SQL SERVER 数据库实用SQL语句】 1.按姓氏笔画排序: Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 2.分页SQL语句 select * from(select (row_number() OVER (ORDER BY tab.ID Desc) as rownum,tab.* from 表名 As tab) As t where rownum between 起始位置 And 结束位置 3.获取当前数据库中的所有用户表 select * from sysobjects where xtype=U and category=0 4.获取某一个表的所有字段 select name from syscolumns where id=object_id(表名) 5.查看与某一个表相关的视图、存储过程、函数 select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like %表名% 6.查看当前数据库中所有存储过程 select name as 存储过程名称 from sysobjects where xtype=P 7.查询用户创建的所有数据库 select * from master.sysdatabases D where sid not in(select sid from master.syslogins where name=sa) 或者 select dbid, name AS DB_NAME from master.sysdatabases where sid 0x01 8.查询某一个表的字段和数据类型 select column_name,data_type from information_schema.columns where table_name = 表名 9.使用事务 在使用一些对数据库表的临时的SQL语句操作时，可以采用SQL SERVER事务处理，防止对数据操作后发现误操作问题 开始事务 Begin tran Insert Into TableName Values() SQL语句操作不正常，则回滚事务。 回滚事务 Rollback tran SQL语句操作正常，则提交事务，数据提交至数据库。 提交事务 Commit tran 10. 按全文匹配方式查询 字段名 LIKE N%a-zA-Z0-9Chinaa-zA-Z0-9% OR 字段名 LIKE N%a-zA-Z0-9China OR 字段名 LIKE NChinaa-zA-Z0-9% OR 字段名 LIKE NChina 11计算执行SQL语句查询时间 declare d datetime set d=getdate() select * from SYS_ColumnProperties select 语句执行花费时间(毫秒)=datediff(ms,d,getdate() 12、说明：几个高级查询运算词 A： UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 C： INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。【关闭SQL Server 数据库所有使用连接】 使用存储过程终止： 在查询分析器下创建终止数据库所有接连的存储过程，通过调用该存储过程可以关闭所有使用该数据库的连接操作。 -创建终止使用数据库下所有进程的存储过程，参数为数据库名称 use master go create proc KillSpByDbName(dbname varchar(20) as begin declare sql nvarchar(500),temp varchar(1000) declare spid int set sql=declare getspid cursor for select spid from sysprocesses where dbid=db_id(+dbname+) exec (sql) open getspid fetch next from getspid into spid while fetch_status -1 begin set temp=kill +rtrim(spid) exec(temp) fetch next from getspid into spid end close getspid deallocate getspid end -举例使用，关闭数据库下的所有连接操作 Use master Exec KillSpByDbName 数据库名称1. 分页查询数据 -查询到行的结果 select * from( select ID,DocClassName,DocClassDesc, ROW_NUMBER() OVER(order by ID) as row from Sys_DocClass ) a where row between 20 and 30 select * from Sys_DocClass 2、增加异常处理 try . catch SET XACT_ABORT ON - 打开try功能 BEGIN TRY begin tran insert into Sys_DocClass values(.) -数据表操作语句 commit tran -提交事务 print commited END TRY BEGIN CATCH rollback tran -回滚事务 print rolled back END CATCH 3、通用表达式CTE，可以简化嵌套SQL -例：结合通用表达式进行分页 WITH DocClasses AS( select ID,DocClassName,DocClassDesc, ROW_NUMBER() OVER(order by ID) as row from Sys_DocClass ) select ID,DocClassName,DocClassDesc from DocClasses where row between 20 and 301. select top m * from tablename where id not in (select top n id from tablename) 2. select top m * into 临时表(或表变量) from tablename order by columnname - 将top m笔插入 set rowcount n select * from 表变量 order by columnname desc 3. select top n * from (select top m * from tablename order by columnname) a order by columnname desc 4.如果tablename里没有其他identity列，那么： select identity(int) id0,* into #temp from tablename 取n到m条的语句为： select * from #temp where id0 =n and id0 = m 如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行： exec sp_dboption 你的DB名字,select into/bulkcopy,true 5.如果表里有identity属性，那么简单： select * from tablename where identitycol between n and m 6.如果是sql server 2005 可以这样写： select top 20 * from T order col except select top 2 * from T order col /给用户授予权限 grant connect,dba,resource to testserver_user; /创建用户并指定表空间 create user ORACLE8I identified by ORACLE8I default tablespace ORACLE8I temporary tablespace test_temp ; /创建数据表空间 create tablespace oracle8i logging datafile E:oracleproduct10.2.0oradatatestservertest_data01.dbf size 32m autoextend on next 32m maxsize 2048m extent management local; /创建临时表空间 create temporary tablespace test_temp tempfile E:oracleproduct10.2.0oradatatestservertest_temp01.dbf size 32m autoextend on next 32m maxsize 2048m extent management local; 表锁 : lock table emp in exclusive mode; 对emp表加的表锁 一般不使用表锁; alter table aa move tablespace bb-设置 min server memory 配置项EXEC sp_configure Nmin server memory (MB), 0-设置 max server memory 配置项EXEC sp_configure Nmax server memory (MB), 256-使更新生效RECONFIGURE WITH OVERRIDE/*-功能说明 下面的代码是在SQL Server 2000上创建名为 MyDB 的数据库 该数据库包括1个主要数据文件、3个用户定义的文件组和1个日志文件 ALTER DATABASE语句将用户定义文件组指定为默认文件组。 之后，通过指默认的文件组来创建表，并且将图像数据和索引放到指定的文件组中。 最后，将文件组中的指定数据文件删除-*/-切换到 master 数据库USE masterGO-A. 创建数据库 MyDBCREATE DATABASE MyDBON PRIMARY -主文件组和主要数据文件 ( NAME=MyDB_Primary, FILENAME= c:MyDB_Prm.mdf),FILEGROUP MyDB_FG1 -用户定义文件组1 ( NAME = MyDB_FG1_Dat1, FILENAME = c:MyDB_FG1_1.ndf), -次要数据文件1 ( NAME = MyDB_FG1_Dat2, FILENAME = d:MyDB_FG1_2.ndf), -次要数据文件2FILEGROUP MyDB_FG2 -用户定义文件组2 ( NAME = MyDB_FG1_Dat, FILENAME = e:MyDB_FG2.ndf) -次要数据文件LOG ON -日志文件 ( NAME=MyDB_log, FILENAME =d:MyDB.ldf)GO-B. 修改默认数据文件组ALTER DATABASE MyDB MODIFY FILEGROUP MyDB_FG1 DEFAULTGO-切换到新建的数据库 MyDBUSE MyDB-C. 在默认文件组MyDB_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB_FG2CREATE TABLE My