T-SQL教程-5

不知道您对于在SQL语句中拼接字符串是否常用

View Code
 --行转列
 create table #CarLog(LogDate datetime,PathLine nvarchar(10),Amount int)
 --drop table #CarLog
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','1号线',1)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-25','1号线',91)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-26','1号线',66)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','3号线',44)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','3号线',33)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','5号线',12)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','6号线',22)
 
 select * from #CarLog
 
 declare @s nvarchar(4000)
 set @s=''
 select @s=@s+',' +PathLine from #CarLog
 print @s

 

当然也可以使用SQL来生成随机密码(以前博客园有文章说过……)

View Code

http://www.cnblogs.com/insus
-----------------------随机产生密码的存储过程
create PROCEDURE [dbo].[usp_RandomPassword]
(
   @Length INT = 8
)
AS
BEGIN 
    DECLARE @RandomPassword  NVARCHAR(MAX) = N'',@L INT = 1  
    --下面的变量，你可以自定制需要的特殊字符
    DECLARE @SpecialCharacter NVARCHAR(255) = '@#$%&*?'
    WHILE @L <= @Length
    BEGIN        
       --下面这句，Insus.NET把2改为3。
        DECLARE @R INT = ROUND(RAND() * 3, 0)
        SET @RandomPassword = @RandomPassword + CASE @R
        WHEN 0 THEN CHAR(ROUND(RAND() * 9 + 48,0))
        WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
        WHEN 2 THEN CHAR(ROUND(RAND() * 25 + 97,0))
        WHEN 3 THEN SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * 6 + 1,0)),1) END  --添加此句                                          
        SET @L = @L + 1       
    END
    SELECT @RandomPassword
END

--- dbo.[usp_RandomPassword]
alter PROCEDURE [dbo].[usp_RandomPassword]
(
   @Length INT = 8
)
AS
BEGIN     
    DECLARE @RandomPassword NVARCHAR(MAX) = N'',@L INT = 1
    WHILE @L <= @Length  --循环密码长度
    BEGIN
        --随机产生每一位字符，ASCII码48至122
        DECLARE @RndChar CHAR(1) = CHAR(ROUND(RAND() * (122-48+1) + 48,0))
        --随机产生的字符不包括下面字符
        IF ASCII(@RndChar) NOT IN(58,59,60,61,62,63,64,91,92,93,94,95,96) -- : , ; , < , = , > , ? ,@ , [ , \ , ] , ^ , _ , `
        BEGIN
            SET @RandomPassword = @RandomPassword + @RndChar
            SET @L = @L + 1
        END       
    END
    SELECT @RandomPassword
END
-----------------------随机产生密码的存储过程
alter PROCEDURE [dbo].[usp_RandomPassword]
(
   @Length INT = 8
)
AS
BEGIN 
    DECLARE @RandomPassword NVARCHAR(MAX) = N'',@L INT = 1
    --随机密码将由下面字符串产生，数字0-9,大写字母A-Z,小写字母a-z
    DECLARE @BaseString VARCHAR(255) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' 
    WHILE @L <= @Length --循环密码长度
    BEGIN
        --61是变量@BaseString的长度减一
        SET @RandomPassword = @RandomPassword + SUBSTRING(@BaseString, CONVERT(INT,ROUND(RAND() * 61 + 1,0)),1)
        SET @L = @L + 1
    END
    SELECT @RandomPassword
END

-----------------------随机产生密码的存储过程
CREATE PROCEDURE [dbo].[usp_RandomPassword]
(
   @Length INT = 8
)
AS
BEGIN 
    DECLARE @RandomPassword NVARCHAR(MAX) = N''
    DECLARE @R TINYINT,@L INT = 1     
    WHILE  @L <= @Length --循环密码长度
    BEGIN
        SET @R = ROUND(RAND() * 2, 0)    --随机产生0,1,2整数
        IF @R = 0 --当变量为0时，将随机产生一位数字
            SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 9 + 48,0))
        ELSE IF @R = 1 --当变量为1时，将随机产生一位大写字母
            SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 65,0))
        ELSE IF @R = 2 --当变量为2时，将随机产生一位小写字母
            SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 97,0))           
        SET @L = @L + 1
    END
    SELECT @RandomPassword
END
----------重构
CREATE PROCEDURE [dbo].[usp_RandomPassword]
(
   @Length INT = 8
)
AS
BEGIN 
    DECLARE @RandomPassword  NVARCHAR(MAX) = N'',@L INT = 1  
    WHILE @L <= @Length --循环密码长度
    BEGIN        
        DECLARE @R INT = ROUND(RAND() * 2, 0)
        SET @RandomPassword = @RandomPassword + CASE @R
        WHEN 0 THEN CHAR(ROUND(RAND() * 9 + 48,0))
        WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))
        WHEN 2 THEN CHAR(ROUND(RAND() * 25 + 97,0)) END                           
        SET @L = @L + 1       
    END
    SELECT @RandomPassword
END
-----------------------
--MS SQL有一个函数CHAR()是将int(0-255) ASCII代码转换为字符
--下面MS SQL语句，可以随机生成小写、大写字母，特殊字符和数字。
--大写字母：
select CHAR(ROUND(RAND() * 25 + 65,0))
 
--小写字母：
select CHAR(ROUND(RAND() * 25 + 97,0))
 
--特殊字符：
select CHAR(ROUND(RAND() * 13 + 33,0))
 
--数字：
select CHAR(ROUND(RAND() * 9 + 48,0))

 

 

----------------------------------
--随机密码首字符不能为数字与特殊字符但必须包含且只有一个特殊字符
--修正了特殊字符集长度问题，使用特殊字符集长度减一(LEN(@SpecialCharacter) - 1) 这样当你增减特殊字符集时，再不需多次地方一同修改
ALTER PROCEDURE [dbo].[usp_RandomPassword]
(
   @Length INT = 8
)
AS
BEGIN 
    DECLARE @RandomPassword NVARCHAR(MAX) = N''
    DECLARE @SpecialCharacter NVARCHAR(255) = N'@#$%&*?'  --特殊字符集
    DECLARE @HasSpcCht BIT = 0  --判断产生的随机数是否有包含随机数
    DECLARE @L INT = 1 
   
    DECLARE @R TINYINT,@R1 TINYINT,@R2 TINYINT    
    WHILE  @L <= @Length --循环密码长度，每一位字符将随机产生
    BEGIN
        IF @L = 1 --随机数第一位只为字母，大写或小写
        BEGIN
            SET @R = ROUND(RAND() * 1, 0)           
            SET @RandomPassword = @RandomPassword + CASE @R
            WHEN 0 THEN CHAR(ROUND(RAND() * 25 + 97,0))
            WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))       
            END               
        END   
        ELSE  
        BEGIN           
            IF @L = @Length AND @HasSpcCht = 0  --当最后一位时，如果没有产生过特殊字符，那为随机数产生一个。
            BEGIN
                SET @RandomPassword = @RandomPassword + SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * (LEN(@SpecialCharacter) - 1) + 1,0)),1)
                SET @HasSpcCht = 1
            END
            ELSE
            BEGIN
                SET @R1 = ROUND(RAND() * 3, 0)
                IF @R1 = 0
                    SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 97,0))
                   
                IF @R1 = 1
                    SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 25 + 65,0))
                   
                IF @R1 = 2
                    SET @RandomPassword = @RandomPassword + CHAR(ROUND(RAND() * 9 + 48,0))   
                               
                IF @R1 = 3  --随机产生特殊字符
                BEGIN   
                    IF @HasSpcCht = 0  --如果没有产生过，那为随机数产生一个。
                    BEGIN
                        SET @RandomPassword = @RandomPassword + SUBSTRING(@SpecialCharacter, CONVERT(TINYINT,ROUND(RAND() * (LEN(@SpecialCharacter) - 1) + 1,0)),1)
                        SET @HasSpcCht = 1
                    END               
                    ELSE  --如果已经产生过特殊字符，只循环产生字母，或数字
                    BEGIN 
                        SET @R2 = ROUND(RAND() * 2, 0)
                        SET @RandomPassword = @RandomPassword + CASE @R2
                        WHEN 0 THEN CHAR(ROUND(RAND() * 25 + 97,0))
                        WHEN 1 THEN CHAR(ROUND(RAND() * 25 + 65,0))   
                        WHEN 2 THEN CHAR(ROUND(RAND() * 9 + 48,0))   
                        END   
                    END               
                END
            END
        END       
       
        SET @L = @L + 1  --随机产生一位，随机数位数加一位。
    END   
    SELECT @RandomPassword
END

 

 

不知道您是否还记得数据库连接字符串如何来写？

View Code
 1.连接sqlserver的连接字符串是：
 “server=.;database=数据库名;uid=用户名;pwd=密码”
 1.连接VS内置的数据库的连接字符串是：
 DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App.mdf;Integrated Security=True;User Instance=True"
 下面我说明下这连接字符串的意思：
  "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Student.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
 DataSource相当于server
 DataSource=.\SQLEXPRESS:是本机下的数据库实例SQLEXPRESS
 AttachDbFilename=|DataDirectory|\App.mdf附加项目App_Data下的App.mdf
 Integrated Security=True;User Instance=True"一个是将整体安全性设置为true,一个是将用户实例设置为true

 

曾经在博客园火热一时的T-SQL文章，经过本人整理的

 

View Code
 --查询所有数据库
 use master
 select * from sysdatabases where dbid>4;--系统自带的数据库分别是master->1,model->3,msdb->4,tempdb->2
 
 
 --查询数据库中所有数据库（存储过程）
 exec sp_helpdb;
 
 --查询指定数据库中的表
 use master
 
 select * from sysobjects where xtype='u' ;
 if object_id('#test1') is not null
 drop table #test1
 go
 
 create table #test1
 (
   id int not null primary key,
   name nvarchar default('haha')
 )
 
 drop table #test1
 
 set nocount off
 
 select name from sysobjects where xtype='u'--读数据库中表名
 
 select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名')--读取某表的列名
 
 --exec调用保存在变量中的批处理代码
 declare @sql as varchar(100);
 set @sql='print''this is a message......;'';';
 exec(@sql)
 
 ----------------------------------------
 --DDL--数据定义语言
 --DML--数据操作语言
 
 --数据库定期备份
 if day(current_timestamp)=9
  begin
    print '今天是一个月的第一天，数据库备份'
    print '开始完全备份'
    backup database dbtest to disk='E:\backup\backup_dbtest_full.bak' with init;
    print '完全备份成功'
  end
 else
  begin
    print '今天是一个月的最后一天，数据库备份'
    print '开始差异备份'
    backup database dbtest to disk='E:\backup\backup_dbtest_diff.bak' with differential;
    print '差异备份成功'
  end
 
 ------------------------------------------------------------
 use tempdb;
 
 if object_id('dbo.Orders','u') is not null drop table dbo.Orders;
 create table dbo.Orders
 (
 orderid int not null
 constraint pk_order  primary key,
 orderdate datetime not null
 constraint def_orderdate default(current_timestamp)
 )
 
 
 ----------------子查询--------------------------------------------
 --子查询分为1-独立子查询2-相关子查询
 --返回结果可以是一个单独的值（标量）或者多个值或者整个表变量
 declare @maxid as int = (select max(orderid) from Sales.Orders);
 select orderid,orderdate,empid,custid
 from Sales.Orders
 where orderid=@maxid;
 
 --sql2005
 select orderid,orderdate,empid,custid
 from Sales.Orders
 where orderid=(select max(orderid) from Sales.Orders);
 --对于有效的标量子查询，它的返回值不能超过一个，如果标量子查询返回了多个值，在运行时可能会失败。
 --比如
 select orderid
 from Sales.Orders
 where empid=(select E.empid from HR.Employees as E where E.lastname like N'B%')
 select E.lastname from HR.Employees as E
 --因为恰巧该表中只有一个人的名字是以B开头的所以,sql会认为右边子查询是标量值
 --假如右边表达式没有返回任何值，那么两者比较得出的结果是NULL,而与NULL比较的结果都为UNKNOW，所以不会返回任何值
 --当然上面的查询还可以用联结查询
 select O.orderid
 from HR.Employees as E
 join Sales.Orders as O
 on E.empid=O.empid
 where E.lastname like N'D%'
 
 --独立多值子查询IN------
 --<标量表达式> IN <多值子查询>
 
 
 --用某表的偶数行数据填充tempdb
 use tempdb
 select *
 into dbo.tempdb
 from TSQLFundamentals2008.Sales.Orders
 where orderid%2=0;
 
 select * from tempdb;
 
 --返回tempdb中介于min(orderid)与max(orderid)并且不在表中的orderid
 select n
 from tempdb
 where n between (select min(O.orderid) from tempdb as O) and (select max(E.orderid) from tempdb as E)
 and n not in(select orderid from tempdb);
 
 -------------------------游标使用-------------------------------------------
 --游标通常步骤
 --1.在某个查询的基础上声明游标
 --2.打开游标
 --3.从第一个游标记录中把列值提取到指定的变量
 --4.当还没有超出游标最后一行时，（@@fetch_status函数返回值为0），循环遍历游标记录，在每一次遍历中，从当前游标记录把列
 --值提取到指定的变量，再为当前执行相应的处理
 --5.关闭游标
 --6.释放游标
 use TSQLFundamentals2008
 declare @result table
 (
  custid int,
  ordermonth datetime,
  qty int,
  runqty int,
  primary key(custid,ordermonth)
 );
 
 declare
   @custid as int,
   @prvcustid as int,
   @ordermonth as datetime,
   @qty as int,
   @runqty as int;
 declare c cursor fast_forward for
  select custid,ordermonth,qty
  from Sales.CustOrders
  order by custid,ordermonth;
 
 open c
 fetch next from c into @custid,@ordermonth,@qty;
 select @prvcustid=@custid,@runqty=0;
 while @@fetch_status=0
  begin
    if @custid<>@prvcustid
      set @runqty=@runqty+@qty;
      insert into @result values(@custid,@ordermonth,@qty,@runqty);
      fetch next from c into @custid,@ordermonth,@qty;
  end
 close c;
 deallocate c;
 select custid,convert(varchar(7),ordermonth,121) as ordermonth,qty,runqty
 from @result
 order by custid,ordermonth;
 
 
 -------------------------------------------
 --1）接受数据导入的表已经存在。 
      insert into t1 select * from  OPENROWSET('MICROSOFT.JET.OLEDB.4.0'  ,
     'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$);
 --2）导入数据并生成表。
      select * into t1 from  OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
      'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$);
 --
 --3) 导入Excel中指定的列到数据库表中指定的列。
      INSERT INTO t1(a1,a2,a3) SELECT a1,a2,a3 FROM OPENROWSET 'MICROSOFT.JET.OLEDB.4.0' ,'Excel5.0; HDR=YES; DATABASE=c:\\test.xls',sheet1$);
 --
 --需要注意的地方。
 --1）外围应用配置器的设置。
 --    从“功能外围应用配置器”中选择“启动 OPENROWSET 和 OPENDATASOURCE 支持”选项。
 --2）关闭Excel表。
 --     如果在导入时要导入的Excel表格处于打开状态，会提示：
 --   “无法初始化链接服务器 "(null)" 的 OLE DB 访问接口 "microsoft.jet.oledb.4.0" 的数据源对象。”
 --3）导入数据时，Excel的首行会作为表头，若导入到已存在的数据库表，则忽略首行。
 
 
 --------------------------------------直接从数据库将表导出到EXCEL-------------------------------------------------------------
 EXEC master..xp_cmdshell 'bcp master.dbo.t2 out d:\Temp.xls -c -q -S"127.0.0.1" -U"sa" -P"123456"'
 
 --参数：S 是SQL服务器名；U是用户；P是密码
 
 
 
 
 --查询优化------------------------------------------
 set nocount on;
 use master;
 if db_id('performance') is null
  create database performance;
 go
  use performance;
 go
 
 --创建填充的数字辅助表
 set nocount on;
 if object_id('dbo.nums','u') is not null
   drop table nums;
 create table dbo.nums(n int not null primary key);
 
 declare @max as int,@rc as int;
 set @max=1000;
 set @rc=1;
 
 insert  into dbo.nums(n) values(1);
 while @rc*2<=@max
  begin
 
   insert into dbo.nums(n) select n+@rc from dbo.nums;
   set @rc=@rc*2;
  end
 
 insert into dbo.nums(n) select n+@rc from dbo.nums where n+@rc<=@max;
 go
 
 --如果数据表存在，则先删除
 
 
 ------------------------------------------------------------
 use insideTSQL2008;
 
 set nocount off;
 
 select orderid,custid
 from sales.orders
 where orderid=(select max(orderid) from sales.orders );--取出orderid最大的订单信息（标量子查询）
 
 -------相关子查询,返回每个客户最大的订单信息
 select orderid,custid
 from sales.orders as T1
 where orderid=(select max(orderid) from sales.orders as T2 where T1.custid=T2.custid)
 order by custid;
 
 ---在期待多个值的地方可以使用多值子查询,返回下过订单的客户
 select custid,companyname
 from sales.customers
 where custid not in (select custid from sales.orders);
 
 --在期待表的地方还可以使用 表值子查询 或 表表达式
 --查询每个订单年份返回最大的订单ID
 select * from sales.orders;
 select * from sales.customers;
 select order_year,max(orderid) as max_orderid
 from (select orderid,year(orderdate) as order_year from sales.orders) as T1
 group by order_year;
 
 --子查询可以按两种方式进行分类，按期望值的数量可以分为标量子查询，多值子查询
 --按子查询对外部的依赖，分为独立子查询，相关子查询，标量子查询和多值子查询既可以是独立子查询，也可以是相关子查询
 --查询由每个美国雇员至少为其处理过的一个订单的所有客户
 --假设知道美国雇员的empid是1，2，3，4，8
 --(1)
 select custid
 from sales.orders
 where empid in(1,2,3,4,8)
 group by custid
 having count( distinct empid)=5;
 --(2)
 select custid
 from sales.orders
 where empid in (select empid from hr.employees where country='usa')
 group by custid
 having count(distinct empid)=5
 
 --返回每个月最后实际订单日期发生的订单
 select orderid,custid,empid,orderdate
 
 from sales.orders
 where orderdate in(
 select max(orderdate)
 from sales.orders
 group by year(orderdate),month(orderdate));
 
 --相关子查询，是引用了在外部查询中出现的列的子查询，从逻辑上讲，子查询会为外部查询的每一行进行一次计算。
 --决胜属性（tiebreaker）,是一个属性或属性列表，可以惟一的对元素进行排名
 
 --先创建2张表
 use master;
 
 if db_id('DbTest') is not null drop database DbTest;
 
 create database DbTest;
 go
 
 use DbTest;
 go
 
 --创建Customers表
 create table Customers
 (
   custid       INT          NOT NULL IDENTITY,
   companyname  NVARCHAR(40) NOT NULL,
   country      NVARCHAR(15) NOT NULL,
   constraint pk_customer primary key(custid)
 );
 --创建Orders表
 CREATE TABLE Orders
 (
   orderid        INT          NOT NULL IDENTITY,
   custid         INT          NULL,
 
   CONSTRAINT PK_Orders PRIMARY KEY(orderid),
   CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid)
   REFERENCES Customers(custid),
 
 );
   set identity_insert Customers on;
 
   INSERT INTO Customers(custid, companyname,country)
   VALUES(1, N'大众', N'中国');
   INSERT INTO Customers(custid, companyname,country)
   VALUES(2, N'宝马', N'美国');
   INSERT INTO Customers(custid, companyname,country)
   VALUES(3, N'奔驰', N'中国');
   INSERT INTO Customers(custid, companyname,country)
   VALUES(4, N'奇瑞', N'德国');
   INSERT INTO Customers(custid, companyname,country)
   VALUES(5, N'福特', N'美国');
  
   set identity_insert Customers off;
   set identity_insert Orders on;
 --custid代表员工号
   INSERT INTO Orders(orderid, custid)
   VALUES(1,1);
   INSERT INTO Orders(orderid, custid)
   VALUES(2,2);
   INSERT INTO Orders(orderid, custid)
   VALUES(3,3);
   INSERT INTO Orders(orderid, custid)
   VALUES(4,4);
   INSERT INTO Orders(orderid, custid)
   VALUES(5,5);
 --查看表的数据
 select custid,companyname,country from Customers;
 select orderid,custid from Orders;
 --插入数据成功
 
 --咱们回到正题,比较Exists与in,not exists与 not in
 
 --查询来自中国,而且下过订单的所有客户
 select custid,companyname
 from Customers as C
 where country=N'中国'
 and exists (select * from Orders as O where O.custid=C.custid);
 --返回
 --custid    companyname
 --1            大众
 --3            奔驰
 
 --外部查询返回来自中国的客户信息,对于这个客户,exists谓词在Orders表查找是否至少存在一个与外部客户行信息相同的custid订单行
 
 --用IN查询刚刚的需求
 select custid,companyname
 from Customers as C
 where country=N'中国'
 and custid in(select custid from Orders);
 --结果跟上面的返回一样的值
 
 --下面的知识点我们需要认识到:
 --当列表中有NULL时，in实际会产生一个UNKNOWN的结果，例如 a in(d,b,null)的结果是UNKNOWN,而a not in (d,b,null)返回的是not unknowd仍然是unknowd
 --而not in与not exists则结果会很不同，例如a in(a,b,null)返回的是TRUE,而a not in(a,b,null)返回的肯定是not true即为false
 --有了上面的认识，好继续开工了....
 --我们现在向Orders表插入一行数据（6,null）
 set identity_insert Orders on;
 
 insert into Orders(orderid,custid) values(6,null);
 
 set identity_insert Orders off;
 
 set identity_insert Customers on;
 
 insert into Customers(custid,companyname,country) values(7,N'雷克萨斯',N'美国');
 
 set identity_insert Customers off;
 
 select * from Orders;
 select * from Customers;
 
 --合并字符串
 select ','+companyname from Customers where country=N'中国'
 for xml path('')
 
 
 --假设现在要返回来自美国且没有订单的客户
 select custid,companyname
 from Customers as C
 where country=N'美国'
 and not exists (select * from Orders as O where O.custid=C.custid );
 --返回
 --custid    companyname
 --7            雷克萨斯
 
 --我们再用IN方法
 select custid,companyname
 from Customers as C
 where country=N'美国'
 and custid not in(select custid from Orders);
 --返回的结果为空!!!
 --为什么呢??
 --因为还记得我们刚插入的一行数据中custid为null么，这就是问题所在
 --not in (select custid from Orders)返回的实际是unknown，所以返回结果集为空，除非你显示的规定custid不能为空
 
 --下面是正确的解决方法
 select custid,companyname
 from Customers as C
 where country=N'美国'
 and custid not in (select custid from Orders where custid is not null);
 --返回
 --custid    companyname
 --7            雷克萨斯
 
 --所以在含有NULL值的列的时候，就要小心了，not exists与not in在逻辑上是不等价的
 use tempdb
 go
 if object_id('dbo.sales') is not null
 drop table dbo.sales;
 go
 --无法绑定由多个部分组成的标识符 "dbo.sales"?
 create table sales
 (
 empid  varchar(10) not null  primary key,
 mgrid varchar(10) not null,
 qty int not null
 )
 
 delete from sales;
 insert into sales(empid,mgrid,qty)
 select
 'B','X','300'
 union
 select
 'C','Z','300'
 union
 select
 'D','X','200'
 union
 select
 'E','Z','300'
 union
 select
 'F','Y','150'
 union
 select
 'G','Y','300'
 union
 select
 'H','Z','240'
 union
 select
 'I','Y','300'
 union
 select
 'J','Z','350'
 union
 select
 'K','Z','300';
 
 
 select * from sales;
 --row_number()函数编号
 select empid,qty,mgrid,
 row_number() over ( partition by mgrid order by qty) as rowrank
 from sales
 order by qty;
 
 --用基本方法计算行号(列的值不为NULL)
 select empid,(select count(*) from sales s where s.empid<ss.empid)+1 as rowrank from sales ss order by empid;
 
 --更新某列的值为null
 update sales set empid=null where empid=N'F';
 
 --select 字段 into 表 from 表
 use tempdb;
 if object_id('tempdb..#myshippers') is not null
 drop table #myshippers;
 select shipperid,companyname,phone
 into #myshippers
 from InsideTSQL2008.sales.shippers;
 
 --如果需要为某个表创建应急的空副本，使用select into 很容易就可以得到它，只需提交以下语句
 select * into target_table from source_table where 1=2;
 --创建名为MyOrders的表
 select *
 into MyOrders
 from InsideTSQL2008.sales.orders
 where 1=2;
 
 --OUTPUT子句
 --支持output子句有INSERT,DELETE,UPDATE,MERGE，可以引用特殊的inserted，deleted表
 --SCOPE_IDENTITY()函数可以返回当前范围内会话最后生成的标识值
 --@@IDENTITY 中包含语句生成的最后一个标识值,如果语句未影响任何包含标识列的表，则 @@IDENTITY 返回 NULL
 --
 use tempdb;
 if object_id('CustomersDim') is not null drop table CustomersDim;
 go
 create table CustomersDim
 (
   keycol int not null identity primary key,
   custid int not null,
   companyname nvarchar(40) not null
 )
 --@@rowcount返回受上一语句影响的行数。 如果行数大于 20 亿，请使用 ROWCOUNT_BIG
 --声明一个表变量
 declare @NewCusts table
 (
   custid int not null primary key,
   keycol int not null unique
 )
 insert into CustomersDim(Custid,companyname)
 output inserted.custid,inserted.keycol
 into @NewCusts--表变量
 --output inserted.custid,inserted.keycol
 select custid,companyname
 from InsideTSQL2008.Sales.Customers
 where country=N'UK';
 
 select * from @NewCusts;
 select @@identity ;
 --谨慎使用，现在我们想下，假设上面表 A 和表 B 都有IDENTITY自增域，那么我们在表 A 插入一条数据后，
 --使用了 SELECT @@IDENTITY 输出时，输出的到底是 A 还是 B 的自增域的值呢？
 --答案很明显，是谁最后插入就输出谁，那么就是 B 了。
 --于是，我本意是想得到 A 的自增域值，结果得到了 B 的自增域值，一只 BUG 随之诞生，搞不好还会影响到整个系统数据的混乱。
 --因此，对于这种情况，建议大家慎用 @@IDENTITY，而尽量采用 SCOPE_IDENTITY() 函数替换之。SCOPE_IDENTITY() 也是得到最后一条自增域的值，
 --但是它是仅限在一个操作范围之内，而@@IDENTITY 是取全局操作的最后一步操作所产生的自增域的值的。
 select scope_identity();
 
 
 --从表中删除大量数据，并避免日志爆炸式增长和锁升级
 --假如要删除2006年之前所有订单
 --那我们采取分批方法，每次删除5000行
 while 1=1
  begin
    delete top(5000) from LargeOrders where orderdate<'20060101';
    if(@@rowcount<5000) break;--如果没有5000行，则循环一次就跳出循环
  end;
 
 --返回一个随机的数
 --select N'随机'+right('000000000'+cast(1+ abs(checksum(newid())) as varchar(10)),10) as 随机值;
 
 --5分钟内向某表随机插入值
 use tempdb;
 if object_id('randtable') is not null drop table randtable;
 go
 create table randtable
 (
   ID int not null identity primary key,
   msg varchar(max) not null
 )
 
 declare @msg as varchar(max);
 declare @now as datetime;
 set @now=current_timestamp;
  while 1=1 and datediff(second,@now,current_timestamp)<300
    begin
      set @msg=N'随机'+right('000000000'+cast(1+ abs(checksum(newid())) as varchar(10)),10);
      insert into randtable(msg) values (@msg);
    end
 --select @@rowcount--返回影响行数
 
 select * from randtable;
 select (max(ID)-min(ID)) as N'总行数' from randtable;
 select max(ID)as N'最大ID' from randtable;--
 select min(ID) as N'最小ID'from randtable;--
 
 --比较几种删除数据的执行效率
 --我们一分钟内随机插入数据
 delete  from randtable;--用这种直接删除数据方式，删除415850条数据       用时52秒：
                                              
                                                --删除5686094条数据      用时29秒;
                                                --删除7679924条数据      用时19秒;
                                                --删除11248379条数据      用时2秒;
                                                --删除10803495条数据      用时2秒;
 
 --下面我们采取另一种方式删除，即分批删除
 while 1=1
  begin
    delete top(5000) from randtable --where ID<19061681;
    --if(@@rowcount<5000) break;--如果没有5000行，则循环一次就跳出循环
  end;
 
 --比较现在时间与之后之间的差值
 datediff(second,@now,current_timestamp);
 
 --更新表结构
 alter table tablename
   add constraint pk_name primary key(columnname);
 
 
 --将空值转换为实际值
 --coalesce(comm,0)
 --与COALESCE(expression1,...n) 的功能与以下 CASE 表达式相同：
 --
 --CASE
 --
 --   WHEN (expression1 IS NOT NULL) THEN expression1
 --
 --   WHEN (expression2 IS NOT NULL) THEN expression2
 --
 --   ...
 --
 --   ELSE expressionN
 --
 --END 类似
 
 --利用case指定order by的列动态排序
 select ename,sal,job,comm
 case when job='saleman' then comm else sal end as ordered
 from emp
 order by 5;
 
 --UNION ALL(包括重复行)
 --将多个来源的行组合起来，放到一个结果集，所有select列表的项目数
 --和对应项目的数据类型必须要匹配
 
 --UNION(不包括重复行)
 
 --创建数字辅助表(Nums)
 if object_id('Nums') is not null drop table Nums;
 go
 create table Nums(n int not null primary key);
 declare
   @max as int,
   @rc as int;
  set @max=10000;
  set @rc=1;
 insert into Nums values(1);
 while @rc*2<=@max
   begin
      insert into Nums select n+@rc from Nums;
      set @rc=@rc*2;
   end
 insert into Nums
   select n+@rc from Nums where n+@rc<=@max;
 
 -- 创建一个“天”表，把Customers表与Employees表生成雇员-客户-每天组合
 select custid,empid,dateadd(day,n-1,'20090101') as orderdate ,row_number() over (order by (select 0))as orderid
 from sales.customers
 cross join HR.employees
 cross join Nums
 where n<31;
 
 --对于每个订单，计算订单价格占总价格的百分比，以及它与所有订单平均价格的差额
 drop table Myordervalues
 select * into MyorderValues
 from sales.Ordervalues;
 
 alter table myordervalues
 add constraint pk_myordervalues primary key (orderid);
 create index idx_val on myordervalues(val);
 
 select orderid,custid,val ,cast(val/(select sum(val) from myordervalues)*100 as numeric(5,2)) as pct,
 cast(val-(select avg(val) from  myordervalues)*100 as numeric(12,2)) as diff
 from myordervalues;
 
 --内联结（inner）
 --关于ON与where，如果一个查询既包含ON，又要包含where字句，逻辑上他们会依次应用，除了一个
 --外，在Inner Join的on字句中指定逻辑表达式，还是在where字句中指定逻辑表达式，没有任何区别，因为
 --两个步骤没有中间步骤会添加外部行。
 --这个例外，就是当指定了group by all选项时，group by all会把where字句过滤掉的组再添加到结果集
 --但不会添加ON字句过滤掉的组。
 
 --如何规范的去放置逻辑条件呢？
 --参考：把表之间匹配属性的筛选器应该位于ON字句中，而只对一个表的属性进行筛选的筛选器应该位于在where字句中
 --例如:
 select c.custid,companyname,orderid from sales.customers as c,sales.orders as o on c.custid=o.custid and country=N'USA';
 
 --外联接（OUTER）
 --外联接用于返回两个表中按一定条件匹配的行，以及“保留”表中不能被匹配的行
 --可以用left,right,full,关键字保留表，left把坐标标记为保留表，right把右表标记为保留表，full把两个表都标记为保留表
 --外联接有三个阶段：笛卡尔积-》ON筛选器-》添加外部行，对于保留表中未找到匹配的行，将作为外部行添加到结果集，并用
 --null作为相应非保留表属性的取值
 
 --以下查询返回客户及其订单ID
 select c.custid,o.orderid into #tb
 from sales.Customers as c
 left outer join sales.orders as o
 on c.custid=o.custid;
 --返回订单是NULL的客户行
 select* from #tb where orderid is null;
 
 --关键字outer是可选的，因为使用left,right,full就隐含着这是一个外联接，通常使用
 --内联接不加关键字inner,而使用外联接通常加上关键字outer
 
 
 --其他联接
 --自联接（是在同一个表的两个实例之间进行的联接）
 --以下是一个简单的自联接例子，对Employees表的两个实例进行联接，一个代表雇员（E），另一个代表经理(M)
 --当联接同一个表的两个实例时,必须至少为其中一个表应用别名,为每一个实例,提供惟一的一个名称
 select E.firstname,E.lastname as emp,
 M.firstname,M.lastname as mgr
 from HR.Employees as E left outer join HR.Employees as M
 on E.mgrid=M.empid;
 
 --不等联接
 --等值联接是联接条件基于等号(=)运算符的联接，不等联接的联接条件中包含的是除等号以外的其他运算符
 
 --存储过程
 --o(∩_∩)o ，终于到存储过程这一环节了，不容易啊....
 --存储过程是把一个或多个T-SQL语句组合到一个逻辑单元，在sqls中保存为一个对象，当首次执行时，sqlserver创建执行计划
 --并把它存在计划内缓存中，然后进行重用，重用计划使得存储过程提供了可靠的性能
 --优点：
 --（1）提升应用程序的可支持性
 --（2）减少网络流量
 --（3）促进代码可复用性
 --（4）淡化数据的获取方式
 --（5）查询时间比较稳定
 --（6）有效防止SQL Injection
 --（7）能作为控制层，控制一定的权限
 
 --基本语法
 --没有参数的存储过程
 --create procedure [schema_name.] procedure_name--是架构和存储过程的名字
 --as {<sql_statement>[...n]}--主体
 --创建一个查询master数据库的存储过程
 use master
 go
 create procedure select_master
 as
 select * from spt_values;
 go
 --执行存储过程
 exec  select_master;--不需要加exec procedure proc_name
 --go关键字用于标记存储过程结束
 --在创建存储过程的时候，会检查sql语法的正确性，不会去检查引用表是否存在，这意味着你可能引用了不存在
 --的表，并且直到运行的时候才报错，这叫做延迟名称解析
 
 --创建带参数的存储过程，一个存储过程可以传入2100个参数
 --语法如下
 --create {proc|procedure} [schema_name] procedure_name[;number]
 --[{@parameter[type_shacema_name.]data_type}[varying][=default][out|output][readonly]][,...n]
 --[with <procedure_option>[,...n]]
 --[for replaction]
 --as {<sql_statement>[;][...n]|<method_specifier>}
 --参数以@为前缀后面是参数的数据类型和可选的默认值
 --
 create procedure shoppingitem
 (@shoppingcartid nvarchar(50),
 @quantity int =1,
 @productid int)
 as
 if exists(select * from Sales.ShoppingCartItem
 where shoopingcartid=@shoppingcartid
 and productid=@productid)
 
 begin
   update sale.shoppingcartitem
   set quantity=@quantity
   where shoppingcartid=@shoppingcartid and productid=@productid
   print'updated....'
 end
 
 else
   begin
     insert sale.shoppingcartitem(shoppingcartid,productid,quantity)values (@shoppingcartid,@productid,@quantity)
     print 'inserted.....'
   end
 go
 --执行，传入三个参数
 exec shoppingitem '1000',2,316
 
 --output参数
 --该参数是把信息返回给存储过程调用者，无论是另外的存储过程或即席调用（嵌入程序中sql语句）
 --例子
 --创建一个返回某个组的部门列表
 create procedure seldepartment
 @groupname nvarchar(50),
 @deptcount int putput
 as
  select [name]
  from hr.department
  where groupname=@groupname
  order by [name]
 select @deptcount=@@rowcount
 go
 --执行
 declare @deptcount int
 exec deldepartment 'hr',@deptcount output
 --更新存储过程
 alter procedure ....
 --删除存储过程
 drop procedure ....
 
 --创建自动执行的存储过程
 --每当重启sql时，自动向一张表中插入重启的时间
 use master
 go
 create table sqlstartlog
 (
 startid int identity(1,1) primary key not null,
 startdatetime datetime not null
 )
 go
 --创建一个存储过程向表中插入值
 create procedure insertstartlog
 as
  insert sqlstartlog(startdatetime) values(getdate())
 go
 --设置自动执行的存储过程
 exec sp_procoption @ProcName='insertstartlog',@OptionName='startup',@OptionValue='true';
 --重启后，查看insertstartlog
 select * from sqlstartlog;
 --如果需要禁用
 exec sp_procoption @ProcName='insertstartlog',@OptionName='startup',@OptionValue='false';
 --在这个更总sql server启动的例子中，存储过程必须创建在master数据库中
 
 --加密
 --只需要在创建存储过程的名字后面加入with encryption
 --查看存过程
 alter procedure sampleencryption
 with encryption
 as
 set nocount on--使不提示受T-SQL语句影响的行数，@@rowcount不受其影响
  select * from sqlstartlog
 go
 --
 exec sp_helptext sampleencryption;
 
 --使用execute as来指定过程的安全上下文
 --with execute as 关键字允许你指定存储过程执行所在的安全上下文，覆盖存储过程调用者的默认安全
 
 --概念：权链
 --当创建一个对象（例如存储过程，或者视图），并将其用来对另外一个数据库对象进行Insert,Delete,Select,Update对象的时候
 --就出现了“所有权链”，如果存储过程对象的架构和它引用对象的架构一样，sqlserver只会检查存储过程调用者是否有对存储过程execute的
 --权限
 
 --重新编译与缓存
 --存储过程的性能优势主要是在重用计划
 
 --当存储过程的计划自动或先是重建的时候就会发生重新编译，当存储过程中引用的基础表或其他对象
 --发生变化后，存储过程就会在其执行期间自动重新编译。计划使用的索引发生变动或者存储过程引用的表键发生了大量的更新也可能引起重新编译
 --sql server存储过程使用的是语句级别的重新编译，能够减小负载。
 
 --查询缓存数量
 select count(*) 'CachedPlansBefore'
 from sys.dm_exec_cached_plans;
 --清空缓存
 dbcc freeproccache;
 
 --注意事项
 --不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
 
 --存储过程常用命令
 --显示所有存储过程的基本信息
 show procedure status;
 
 
 
 -- 显示使用的时间
 declare @BeginDate datetime
 set @BeginDate=getdate();
 begin
    
 end
 
 SELECT BeginDate = @dt, EndDate = GETDATE(),
  Second = DATEDIFF(Second, @dt, GETDATE());
 
 --生成多少万条不重复的n位数字
 use tempdb
 go
 
 --创建测试表
 create table testtable(id char(8) );
 
 --创建用于自动过滤重复值的唯一索引
 create unique index ui_tb on testtable(id)
 with IGNORE_DUP_KEY
 go
 --测试数据插入的时间
 declare @dt datetime
 set @dt=getdate()
 set nocount on;
 declare @row int
 set @row=100000
 while @row>0
 begin
   raiserror('need %d rows',10,1,@row) with nowait
  set rowcount @row
  insert testtable select id=right(100000000+convert(bigint,abs(checksum(newid()))),8)
 from syscolumns c1,syscolumns c2--syscolumns为每个表和视图中的每列返回一行，并为数据库中的存储过程的每个参数返回一行。
 set @row=@row-@@rowcount;
 end
 select BeginDate=@dt,EndDate=getdate(),Second=datediff(Second,@dt,getdate())
 go
 drop table testtable;
 
 ---------------------------------------------------
 use tempdb
 go
 
 --我写的这种只有5秒左右时间,而上面却有40几秒
 --创建测试表
 create table testtable(id int identity(1,1) not null,num char(8)  );
 
 --创建用于自动过滤重复值的唯一索引
 create unique index ui_tb on testtable(num)
 with IGNORE_DUP_KEY
 go
 --测试数据插入的时间
 declare @dt datetime
 set @dt=getdate()
 set nocount on;
 declare @row int
 set @row=100000
 declare @xx char(8)
 while @row>0
 begin
  set @xx=right(100000000+convert(bigint,abs(checksum(newid()))),8);
  --print @xx;
  insert testtable(num) values(@xx)
 set @row=@row-@@rowcount;
 end
 select BeginDate=@dt,EndDate=getdate(),Second=datediff(Second,@dt,getdate())
 select count(*) from testtable;
 go
 drop table testtable;
 
 select * from dbo.testtable
 
 --这是第二次温习not in与exists ，not exists了
 use tempdb;
 go
 if object_id(N'test') is not null
 drop table test
 create table test
 (
   ID int not null identity(1,1) primary key,
    depno char(10)
 )
 insert into test(depno) values(20);
 insert into test(depno) values (10);
 insert into test(depno) values(40);
 insert into test(depno) values (30);
 insert into test(depno) values (null);
 
 create table test2
 (
   ID int not null identity(1,1) primary key,
   depno char(10)
 )
 insert into test2(depno) values(10);
 insert into test2(depno) values (100);
 insert into test2(depno) values (20);
 
 select * from test;
 select * from test2;
 
 select t.depno from test t where t.depno not in  (select t2.depno from test2 t2 )
 
 --下面查询查不到任何值，因为子查询中depno含有null值，并且是用not in判断
 select t2.depno from test2 t2 where t2.depno not in  (select t.depno from test t )
 --采用exists
 select t2.depno from test2 t2 where  not exists (select null from test t where t.depno=t2.depno )
 
 ESCAPE
 --如果要以文本的形式搜索，而不是被sql解释为通配符，可以使用escape关键字
 where description like '%/%%' escape '/'--搜索与“%”匹配的
 create   table   a   (name   varchar(10)) 
   go 
   insert   into   a   select   '11%22' 
   union   all   select   '11%33' 
   union   all   select   '12%33' 
   go 
   select   *   from   a     WHERE   name   LIKE   '%/%33'   ESCAPE   '/'   --指定用'/'符号来说明跟在其后面的通配符字符为普能字符。(第二个%是字符不是通配符来的) 
   go 
   drop   table   a
 --结果为： 
 -- name               
   ----------   
 -- 11%33 
 -- 12%33  
 --再来一个
 SELECT * FROM finances WHERE description LIKE 'gs_' ESCAPE 's'
 --意思就是： 比如，我们要搜索一个字符串 "g_" ,如果直接 like "g_"，那么 "_"的作用就是通配符，而不是字符，结果，我们会查到比如 "ga","gb","gc",而不是我们需要的 "g_". 用 LIKE 'gs_' ESCAPE 'S' 's'表示特殊用法标志
 
 --声明变量
 --在sql2005不允许声明变量后紧接着就赋值
 --而在sql2008可以省略set关键字直接赋值
 use InsideTSQL2008
 go
 select top 10percent * from MyorderValues;--返回10%的行
 
 --建议
 --避免使用不兼容的数据类型。例如float和int、char和varchar、binary和
 --varbinary是不兼容的
 --避免使用不兼容的数据类型。例如float和int、char和varchar、binary和
 --varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进
 --行的优化操作。例如:
 SELECT name FROM employee WHERE salary >60000
 --在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000
 --是个整型数。我们应当在编程时将整型转化成为货币型,而不要等到运行时转化。
 
 --尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃
 --使用索引而进行全表扫描。如：
 SELECT * FROM T1 WHERE F1/2=100
 --应改为:
 SELECT * FROM T1 WHERE F1=100*2
 
 SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’
 --应改为:
 SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’
 
 SELECT member_number, first_name, last_name  FROM members
 WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21
 --应改为:
 SELECT member_number, first_name, last_name  FROM members
 WHERE dateofbirth < DATEADD(yy,-21,GETDATE())
 --即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询
 --时要尽可能将操作移至等号右边。
 
 --尽量使用数字型字段，一部分开发人员和数据库管理人员喜欢把包含数值信
 --息的字段
 --设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在
 --处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一
 --次就够了。
 
 --创建用','分隔的列表
 select * from nums
 declare @splitstring nvarchar(50)
 set @splitstring='';
 select @splitstring=@splitstring+convert(char(2),n)+','--因为n是数值型，需要转换，不然就是求和了
 from Nums
 where n<15;
 select @splitstring;
 
 --select into到临时表
 select n into #tempdb
 from nums;
 select * from #tempdb;
 
 --在频繁执行的查询中，发生隐式转换，将会非常影响性能例如nchar转为char
 
 --复习索引
 --索引是在表上创建的数据库对象，它可以提供到数据更快的访问，并且可以使查询执行更快
 --sql中，存储单位最小的是页，页是不可再分的，换句话说，要么整个读取,要么不读
 --如果表上没有索引，那么就是存放在堆Heap中，即使你想找的数据就是第一项，那么sql引擎也需要进行全表扫描
 --对于数据库检索来说，对于磁盘的IO是最消耗时间的
 
 --测试sql IO次数
 use tempdb
 go
 create table testio
 (
 id int,
 c1 int,
 c2 int,
 c3 int,
 c4 char(2)
 )
 insert into testio values(1,1,1,'a');
 insert into testio values(2,2,2,'a');
 insert into testio values(3,3,3,'a');
 insert into testio values(4,4,4,'a');
 insert into testio values(5,5,5,'a');
 insert into testio values(6,6,6,'a');
 insert into testio values(7,7,7,'a');
 insert into testio values(8,8,8,'a');
 insert into testio values(9,9,9,'a');
 insert into testio values(10,10,10,'a');
 insert into testio values(11,11,11,'a');
 insert into testio values(12,12,12,'a');
 insert into testio values(13,13,13,'a');
 insert into testio values(14,14,14,'a');
 insert into testio values(15,15,15,'a');
 insert into testio values(16,16,16,'a');
 insert into testio values(170,170,170,'a');
 
 --开启IO数量
 set statistics io on;
 select * from testio where c1=170;
 --建立索引
 create index idx_testio on testio(c1);
 select * from testio where c1=170;
 
 
 use HR
 go
 select top 10(ID) from Talent_Big;
 select  * from Talent_Big where ID=480000;
 --建立索引
 drop index idx_talent_big on Talent_Big;
 
 --理解聚集和聚集索引
 --
 --   在SQL Server中，最主要的两类索引时聚集索引和非聚集索引。可以看到，这两个分类都是围绕聚集这个关键字进行的，那么首先理解什么是聚集。
 --
 --   聚集在索引中的定义：
 --
 --   为了提高某个属性（或属性组）的查询速度，把这个或这些属性（成为聚集码）上具有相同值的元组集合中存放在连续的物理块成为聚集。
 --
 --非聚集索引
 --
 --    因为每个表只能有一个聚集索引，如果我们对一个表的查询不仅仅限于聚集索引上的字段。我们又对聚集索引列之外还有索引的要求，那么就需要非聚集索引了。
 --
 --    非聚集索引，本质上来说也是聚集索引的一种，非聚集索引并不改变其所在表的物理结构，而是额外生成一个聚集索引的B树结构，但叶节点是对于其所在表的引用，
 --这个引用分为两种，如果其所在表上没有聚集索引，则引用行号；如果其所在表上已经有了聚集索引，则引用聚集索引的页，从而实现更大限度的使用。
 
 --随着数据量的增长，产生了索引碎片，很多存储的数据进行了不适当的跨页,会造成碎片
 --这时我们可以通过重建索引来提高速度:
 
 ALTER INDEX idx_text_tb2_EmployeeID ON test_tb2 REBUILD
 --
 --还有一种情况是，当随着表数据量的增大，有时候需要更新表上的统计信息，让查询分析器根据这些信息选择路径，使用:
 --
 --UPDATE STATISTICS 表名
 --
 --那么什么时候知道需要更新这些统计信息呢，就是当执行计划中估计行数和实际表的行数有出入时:
 
 --  当然索引的使用也是要付出代价的：
 --
 --  1、通过聚集索引的原理我们知道，当表建立索引后，就可以B树来存储数据，所以当对其进行更新插入删除，就需要页在物理上移动以调整B树，因此当更新插入删除数据时，会带来性能的下降。
 --而对于非聚集索引，当更新表后，非聚集索引需要进行更新，相当于多更新了N(N=非聚集索引数量）个表。因此也下降了性能。
 --
 --  2、通过上面对非聚集索引原理的介绍，可以看到，非聚集索引需要额外的磁盘空间。
 --
 --  3、前文提过，不恰当的非聚集索引反而会减低性能。
 --
 -- 所以使用索引需要根据实际情况进行权衡.通常我都会将非聚集索引全部放到另外一个独立硬盘上，这样可以分散IO，从而使查询并行.
 
 
 --视图
 --sql中分为3种视图
 --普通视图，在数据库中不保存实际数据，只是视图定义
 --索引视图，保存了真实的索引数据
 --分布式分区视图，可以用UNION ALL来把多个跨两个以上sql实例的较小的表组合成一个虚拟表
 
 --select语句允许一个视图定义1024个列，然而，不可以在视图定义中使用某些select元素，包括into，option,compute,compute by 或者对
 --表变量或临时表的引用。除非使用了top关键字，否则也不可以使用order by
 --创建视图
 create view view_somedefin
 as
 select * from testio
 go
 --查看某个视图的定义
 select definition from sys.sql_modules
 where object_id=object_id('view_somedefin');
 --删除视图
 drop view view_somedefin;
 --视图加密
 create view view_somedefinencrypt
 with encryption
 as
 select * from testio;
 go
 --此时通过sys.sql_modules系统目录视图来查看该视图的definition是null    
 
 --使用视图修改数据
 --可以像普通表一样对视图进行插入，更新，删除，但是这些操作只能引用一个表中的列，而且，这些操作中引用列不能进行
 --衍生，例如它们不能基于聚合函数被计算或受group by ,distinct 或having子句
 --例子
 create view view_production
 as
 select ID,proname,proprice,num,num*prpprice totalcost
 from production
 go
 --以上创建了一个视图，现在向其中插入一条数据
 insert view_production (proname,proprice,num,totalcost) values ('可乐',2,10,20);
 --发生错误
 --对视图或函数’view_production‘的更新或插入失败、因其包含派生域或常量域
 --其实问题是出生在’totalcost‘中，它引用了两列，只要去掉插入该列的值即可
 
 --创建索引视图
 use InsideTSQL2008
 go
 create view view_Employees
 with schemabinding
 as
 select E.empid ,E.lastname,E.firstname,E.title,E.address,E.city,O.orderdate,O.shipname
 from HR.Employees E
 join
 Sales.Orders O
 on O.empid=E.empid ;
 go
 --drop view view_Employees;
 set statistics io on;
 select  * from view_Employees
 --查询完毕后，我们发现empid这列是无序的，并且Orders表的逻辑读取次数为21次，Employees的逻辑读取次数2次
 --因为empid为主键与外键，所以已经有了聚集索引，而一个表中只能有一个聚集索引，所以为视图创建非聚集索引
 create  nonclustered index uci_index_Employees_Orders
 on Sales.Orders(orderdate)
 go
 --删除索引
 drop index Sales.Orders.uci_index_Employees_Orders;

 

一些我 不常用到的SQL

View Code
 --------添加链接服务器
 EXEC sp_addlinkedserver 'MyDBServer','','MSDASQL',NULL,NULL,
 'DRIVER={SQL Server};SERVER=192.168.1.254,1234;UID=sa;PWD=1234568@abc;' 
 
 
 select * from MyDBServer.Hisense_MobileSales.dbo.sales
 
 Exec sp_droplinkedsrvlogin DBVIP,Null       --删除映射（录与链接服务器上远程登录之间的映射）
 exec sp_dropserver 'DBVIP'                     --删除链接
 
 EXEC  sp_addlinkedserver
       @server='DBVIP',--被访问的服务器别名
       @srvproduct='',
       @provider='SQLOLEDB',
       @datasrc="DRIVER={SQL Server};SERVER=192.168.1.254,1234;UID=sa;PWD=1234568@abc;"   --要访问的服务器
 
 
 EXEC sp_addlinkedsrvlogin
      'DBVIP', --被访问的服务器别名
      'false',
      NULL,
      'sa', --帐号
      'thankyoubobby' --密码
 Select   *   from DBVIP.Hisense_MobileSales.dbo.city  
 
 
 
 ----修改密码成功
 EXEC sp_password null,"123456","sa"
 go
 Alter LOGIN sa ENABLE

 

 

View Code
 ---2005附加数据库
 ---ATTACH DATABASE TEMPLATE
 exec sp_attach_db 'ghpTest','C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ghpTest.mdf',
                             'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ghpTest_log.ldf'
 GO
 ---分离数据库
 use ghpTest
 go
 exec sp_detach_db  @dbname ='ghpTest'
 go
 ---列出存储过程
 exec sp_stored_procedures
 GO
 --系统视图
 select * from sys.objects
 
 ---列出存储过程
 select * from sys.objects WHERE TYPE='P'
 select [name] from sysobjects where xtype='P' order by [name]
 GO
 ---列出所有表
 select * from sys.objects WHERE TYPE='U' order by [name]
 select [name] from sysobjects where xtype='U' order by [name]
 GO
 --列出视图
 select * from sys.objects WHERE TYPE='V' order by [name]
 select [name] from sysobjects where xtype='V' order by [name]
 GO
 --
 select * from sysobjects
 GO
 --查詢数据库中的表所占用空间
 exec sp_spaceused '表名' --取得表占用空间 
 exec sp_spaceused 'Province'
 exec sp_spaceused --数据库所有空间
 
 
 ---1 方式
 create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))
 GO
 declare @name varchar(100)
 declare cur cursor  for
     select name from sysobjects where xtype='u' order by name
 open cur
 fetch next from cur into @name
 while @@fetch_status=0
 begin
     insert into #data
     exec sp_spaceused   @name
     print @name
 
     fetch next from cur into @name
 end
 close cur
 deallocate cur
 go
 select * from #Data
 GO
 ---2 方式
 create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int)
 GO
 insert into #dataNew
 select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data,
 convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data
 GO
 select * from #dataNew order by data desc
 GO
 ---数据库对象限定符:
 --[[[server.][database].][schema].]database_object
 ---schema dbo(默認模式)
 
 /*
 
 使用完毕后，记得一定要要关闭它，因为这是一个安全隐患,切记执行下面的SQL语句
 
 --创建链接服务器
 exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
 exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '
 
 --查询示例
 select * from ITSV.数据库名.dbo.表名
 
 --导入示例
 select * into 表 from ITSV.数据库名.dbo.表名
 
 --以后不再使用时删除链接服务器
 exec sp_dropserver  'ITSV ', 'droplogins '
 
 --连接远程/局域网数据(openrowset/openquery/opendatasource)
 --1、openrowset
 
 --查询示例
 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
 
 --生成本地表
 select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
 
 --把本地表导入远程表
 insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
 select *from 本地表
 
 --更新本地表
 update b
 set b.列A=a.列A
 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
 on a.column1=b.column1
 
 --openquery用法需要创建一个连接
 
 --首先创建一个连接创建链接服务器
 exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
 --查询
 select *
 FROM openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ')
 --把本地表导入远程表
 insert openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ')
 select * from 本地表
 --更新本地表
 update b
 set b.列B=a.列B
 FROM openquery(ITSV,  'SELECT * FROM 数据库.dbo.表名 ') as a 
 inner join 本地表 b on a.列A=b.列A
 
 --3、opendatasource/openrowset
 SELECT   *
 FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
 --把本地表导入远程表
 insert opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
 select * from 本地表
 
 */
 
 
 --2005 启用Ad Hoc Distributed Queries的方法，执行下面的查询语句就可以了：
 exec sp_configure 'show advanced options',1
 reconfigure
 exec sp_configure 'Ad Hoc Distributed Queries',1
 reconfigure
 --使用完毕后，记得一定要要关闭它，因为这是一个安全隐患,切记执行下面的SQL语句
 exec sp_configure 'Ad Hoc Distributed Queries',0
 reconfigure
 exec sp_configure 'show advanced options',0
 reconfigure 
 
 --2005
 SELECT top 10 * FROM OPENDATASOURCE('SQLOLEDB','Data Source=.;User ID=sa;Password=geovindu;').geovinduDB.dbo.meetingApply
 GO
 
 --openrowset使用OLEDB的一些例子
 select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;','select * from GEOVINDU.dbo.school') as t
 select * from openrowset('SQLOLEDB','Server=(local);PWD=***;UID=sa;',GEOVINDU.dbo.school) as t
 select * from openrowset('SQLOLEDB','Server=(local);Trusted_Connection=yes;',GEOVINDU.dbo.school) as t
 select * from openrowset('SQLOLEDB','(local)';'sa';'***','select * from GEOVINDU.dbo.school') as t
 select * from openrowset('SQLOLEDB','(local)';'sa';'***',GEOVINDU.dbo.school) as t
 select * from openrowset('SQLOLEDB','(local)';'sa';'***','select school.id as id1,people.id as id2 from GEOVINDU.dbo.school inner join GEOVINDU.dbo.people on school.id=people.id') as t
 
 --openrowset使用SQLNCLI的一些例子(SQLNCLI在SqlServer2005以上才能使用)
 select * from openrowset('SQLNCLI','(local)';'sa';'***','select * from GEOVINDU.dbo.school') as t
 select * from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select * from GEOVINDU.dbo.school') as t
 select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;','select * from GEOVINDU.dbo.school') as t
 select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;',GEOVINDU.dbo.school) as t
 select * from openrowset('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=GEOVINDU','select * from dbo.school') as t
 
 --openrowset其他使用
 insert openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from GEOVINDU.dbo.school where id=1') values('geovindu')/*要不要where都一样，插入一行*/
 update openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from GEOVINDU.dbo.school where id=1') set name='geovindu'
 delete from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;','select name from GEOVINDU.dbo.school where id=1')
 
 
 
 
 
 --opendatasource使用SQLNCLI的一些例子
 select * from opendatasource('SQLNCLI','Server=(local);UID=sa;PWD=***;').GEOVINDU.dbo.school as t
 select * from opendatasource('SQLNCLI','Server=(local);UID=sa;PWD=***;DataBase=TB').GEOVINDU.dbo.school as t
 
 --opendatasource使用OLEDB的例子
 select * from opendatasource('SQLOLEDB','Server=(local);Trusted_Connection=yes;').GEOVINDU.dbo.school as t
 
 --opendatasource其他使用
 insert opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').GEOVINDU.dbo.school(name) values('geovindu')/*要不要where都一样，插入一行*/
 update opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').GEOVINDU.dbo.school set name='geovindu'
 delete from opendatasource('SQLNCLI','Server=(local);Trusted_Connection=yes;').GEOVINDU.dbo.school where id=1
 
 
 
 
 
 --openquery使用OLEDB的一些例子
 exec sp_addlinkedserver   'ITSV', '', 'SQLOLEDB','(local)'
 exec sp_addlinkedsrvlogin 'ITSV', 'false',null, 'sa', '***'
 select * FROM openquery(ITSV,  'SELECT *  FROM GEOVINDU.dbo.school ')
 
 --openquery使用SQLNCLI的一些例子
 exec sp_addlinkedserver   'ITSVA', '', 'SQLNCLI','(local)'
 exec sp_addlinkedsrvlogin 'ITSVA', 'false',null, 'sa', '***'
 select * FROM openquery(ITSVA,  'SELECT *  FROM GEOVINDU.dbo.school ')
 
 --openquery其他使用
 insert openquery(ITSVA,'select name from GEOVINDU.dbo.school where id=1') values('geovindu')/*要不要where都一样，插入一行*/
 update openquery(ITSVA,'select name from GEOVINDU.dbo.school where id=1') set name='geovindu'
 delete openquery(ITSVA,'select name from GEOVINDU.dbo.school where id=1')
 
 
 backup database intranet to disk='C:\ba.bak' with init
 
 
 RESTORE DATABASE nwind_new FROM DISK = 'c:\backups\northwind\nwind.bak'
 WITH
 MOVE 'northwind' TO 'd:\Program Files\Microsoft SQL
 Server\Data\nwind_new.mdf'
 MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQL
 Server\Data\nwind_new_log.ldf'
 SELECT *
 FROM OPENDATASOURCE(
     'Microsoft.Jet.OLEDB.4.0',
     'Data Source=C:\test.xls;Extended Properties=EXCEL 5.0'
 )...[Sheet1$];
 
 SELECT * FROM
 OpenDataSource(
     'Microsoft.Jet.OLEDB.4.0',
     'Data Source="c:\temp\payroll.mdb";
     User ID=Admin;Password=;')...employees
 
 
 SELECT  *
 FROM OPENROWSET
         ( BULK 'C:\data.txt',SINGLE_CLOB)
 AS a