Sqlsever学习篇章（一）

 前言

       结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。 

sql语言4大类

数据查询语言DQL	数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块
数据操纵语言DML	DML主要有三种形式：insert、update、delete
数据定义语言DDL	DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等；DDL操作是隐性提交的！不能rollback
数据控制语言DCL	DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等

---

---

1.表的创建和删除

--创建表
create table [T_Table] 
(
   [ID] numeric(18, 0) identity(1,1) constraint [PK_T_Table_ID] primary key not null, 
   [Name] nvarchar(128) constraint [UQ_T_Table_Name] unique constraint [CK_T_Table_Name] check(len([Name]) < 5) not null, 
   [Card] varchar(18),
   [Age] tinyint, 
   [Location] nvarchar(max), 
   [Timestamp] datetime constraint [DF_T_Table_Timestamp] default getdate()
);

--查询表信息
select * from [sysobjects] where [name] = 'T_Table' and [xtype] = 'U';

--查询表中列信息
select * from [syscolumns] where [id] = object_id('T_Table');

--删除表
drop table [T_Table];

对于列的约束最好自己添加名称，否则系统会自动命名，不方便管理，可以在系统表 [sysobjects]中查询相关信息。 

2.列的增删改查

  --查找表中所有列信息
  select * from [syscolumns] where [id] = object_id('T_Table');

  --增加列
  if not exists (select 1 from [syscolumns] where [id] = object_id('T_Table') and [name] = 'Location')
    begin
        alter table [T_Table] add [Location] varchar(255);
    end;

  --修改列
  if exists (select 1 from [syscolumns] where [id] = object_id('T_Table') and [name] = 'Location')
    begin
        alter table [T_Table] alter column [Location] varchar(max) not null;
    end;

  --删除列
  if exists (select 1 from [syscolumns] where [id] = object_id('T_Table') and [name] = 'Location')
    begin
        alter table [T_Table] drop column [Location];
    end;

  --列改名  注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。
  if exists (select 1 from [syscolumns] where [id] = object_id('T_Table') and [name] = 'Location')
    begin
        exec [sp_rename] 'T_Table.Location', 'Place', 'column';
    end;

3.约束

约束被 DB2 Universal Database（DB2 UDB） 用来对数据业务规则和数据完整性进行实施、维护。  

数据库表的5种约束

名称	sql关键字	sysobjects.xtype	sysobjects.type	命名规范
主键	primary key	PK	K	PK_
外键	foreign key	F	F	FK_
检查	check	C	C	DF_
默认值	default	D	D	CK_
唯一键	unique	UQ	K	UQ_

  --当知道主键约束的名称时删除主键
  if exists (select 1 from [sysobjects] where [parent_obj] = object_id('T_Table') and [name] = 'PK_T_Table_ID')
  begin
      alter table [T_Table] drop constraint [PK_T_Table_ID];
  end
  
  --当不知道主键约束名称时删除主键
  declare @PKName varchar(max);
  set @PKName = (select [name] from [sysobjects] where [parent_obj] = object_id ('T_Table') and [xtype] = 'PK');
  if(len(@PKName) <> 0)
  begin
      declare @AlterSQL nvarchar(max);
      set @AlterSQL = N'alter table [T_Table] drop constraint ' + @PKName;
      exec SP_EXECUTESQL @AlterSQL;
  end

  --添加联合主键
  alter table [T_Table] add constraint [PK_T_Table_ID_Name] primary key([ID],[Name]);

--添加check约束
alter table [T_Table] add constraint [CK_T_Table_Name] check(len([Name]) < 5);

--添加外键约束(假设表T_Table2中主键为Name)
--有外键约束后，增删改都必须保证统一（可以通过设置取消统一）
alter table [T_Table] add constraint [FK_T_Table_Name] foreign key([Name]) references [T_Table2]([Name]);

--添加默认值约束
alter table [T_Table] add constraint [DF_T_Table_Location] default(N'China') for [Location];

--添加唯一约束
alter table [T_Table] add constraint [UQ_T_Table_Name] unique([Name]);

4.索引 

主键、外键和唯一索引的区别

	主键	外键	唯一索引
定义	唯一标识一条记录，不能有重复的，不允许为空	表的外键是另一表的主键, 外键可以有重复的, 可以是空值	该字段没有重复值，但可以有一个空值
作用	用来保证数据完整性	用来和其他表建立联系用的	是提高查询排序的速度
个数	主键只能有一个	一个表可以有多个外键	一个表可以有多个唯一索引

聚集索引和非聚集索引的区别？

聚集索引一定是唯一索引。但唯一索引不一定是聚集索引。  

聚集索引，在索引页里直接存放数据，而非聚集索引在索引页里存放的是索引，这些索引指向专门的数据页的数据。（没有特意指定的索引都是非聚集索引）

--查询所有该表下的索引项
execute [sp_helpindex] @objname = 'T_Table';

--创建唯一索引
create unique index [IX_Table_Name] on [T_Table]([Name]);

--创建联合唯一索引
create unique index [IX_Table_Name] on [T_Table]([Name],[Card]);

--删除索引（不能用来删除主键和唯一键）
drop index [T_Table].[IX_Table_Name];

--创建聚集索引（一个表只能有一个聚集索引，主键属于聚集索引）
create clustered index [IX_Clu_Table_Name] on [T_Table]([Name]);

--创建非聚集的筛选索引
create index [IX_Table_Name] on [T_Table]([Name]) where [Age] < 35;

唯一约束和唯一索引的区别： 

唯一约束和唯一索引，都可以实现列数据的唯一，列值可以有null。唯一约束是通过唯一索引来实现数据的唯一。约束是业务检查，而索引是一种数据结构,  两个东西的效果是一样。但在建立唯一约束时,会自动创建一个唯一索引。并且,失效该约束时,索引自动删除.而创建唯一索引则不会自动创建唯一约束 

5.事务

CREATE TABLE [ValueTable] ([value] INT);  

--声明事务
DECLARE @TransactionName VARCHAR(20) = 'Transaction1';  

--开启事务
BEGIN TRAN @TransactionName  
  INSERT INTO [ValueTable] VALUES(1), (2); 
--回滚事务 
ROLLBACK TRAN @TransactionName; 
 
INSERT INTO [ValueTable] VALUES(3),(4);   
SELECT [value] FROM [ValueTable];  
DROP TABLE [ValueTable]; 

事务包含4种重要的属性，统称为ACID（原子性、一致性、隔离性和持久性），一个事务必须通过ACID

• 原子性（ Atomic）

事务是一个整体的工作单元，事务对数据库所作的操作要么全部执行，要么全部取消。如果某条语句执行失败，则所有语句全部回滚。

• 一致性（ ConDemoltent）

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。如果事务成功，则所有数据将变为一个新的状态；如果事务失败，则所有数据将处于开始之前的状态。

• 隔离性（ Isolated）

由某一事务所作的修改必须与其他事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据

• 持久性（ Durability）

当事务提交后，对数据库所作的修改就会永久保存下来

6.锁模式

锁模式	描述
共享（S）	用于不更改或不更新数据的操作（只读操作），如 select 语句
更新（U）	用于可更新的资源中，防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁
排他（X）	用于数据修改操作，如 insert、update 或 delete。确保不会同时出现同一资源进行多重更新
意向	用于建立锁的层次结构。意向锁的类型为意向共享（IS）、意向排他（IX）以及与意向排他共享（SIX）
架构	在执行依赖于表架构的操作时使用。架构锁的类型为架构修改（Sch-M）和架构稳定性（Sch-S）
大容量更新（BU）	向表中大容量复制数据并指定了 TABLOCK 提示时使用

7.触发器 

触发器不会递归触发。 

7.1 DML after/for 触发器 

 >>insert 触发器，在有数据添加后触发：在 inserted 中获取到新行的数据

alter trigger [dbo].[Trigger_T_Table_Insert] on [dbo].[T_Table] after insert as 
begin
	set nocount on;  --表示在该事务中不返回影响的行数
	declare @Name varchar(max);
	declare @ID numeric(18,0);
    select @ID = [ID], @Name = [Name] from inserted;
end

---

>>update 触发器，在有数据更新后触发：在 inserted 中获取到有更新后的那一行的数据，在deleted 中获取到更新前的那一行的数据

alter trigger [dbo].[Trigger_T_Table_Update] on [dbo].[T_Table] after update as 
begin
	set nocount on;
	declare @Name varchar(max);
	declare @ID numeric(18,0);
    select @ID = [ID], @Name = [Name] from inserted;
end

---

>>delete 触发器，在有数据删除后触发：在 deleted 中获取到有已删除的那一行数据

alter trigger [dbo].[Trigger_T_Table_Delete] on [dbo].[T_Table] after delete as 
begin
	set nocount on;
	declare @Name varchar(max);
	declare @ID numeric(18,0);
    select @ID = [ID], @Name = [Name] from deleted;
end

 可以同时定义insert和update触发器：

alter trigger [dbo].[Trigger_T_Table_Insert] on [dbo].[T_Table] after insert,update as 
begin
	set nocount on;
	declare @Name varchar(max);
	declare @ID numeric(18,0);
    select @ID = [ID], @Name = [Name] from inserted;
	if(len(@Name) = 0)
	   rollback;
end

可以用 rollback 回滚事务（但是若是有自增的值不会恢复） 

可以用 commit 提交事务

updated 和 deleted 中有可能不只一行数据

7.2 DML instead of 触发器  

取代所定义的操作，以触发器中的操作为准，每个表只能有一个对应的 instead of 触发器。下例中，因为没有进行插入操作，所有对于自增列没有值。假如一张表既有 after insert 触发器，又有instead of insert 触发器的情况下，只会触发 instead of 触发器，而不会触发 after insert 触发器。

alter trigger [dbo].[Trigger_T_Table_InsteadOf] ON [dbo].[T_Table] instead of insert as 
begin
	set nocount on;
	declare @Name varchar(max);
	declare @ID numeric(18,0);
    select @ID = [ID], @Name = [Name] from inserted;
	if(len(@Name) < 3)
	   insert [T_Table] ([Name]) values (@Name + CAST(@ID as varchar(max)));
end

7.3 DDL 触发器

DDL触发器禁止修改和删除表

create trigger [Trigger_Drop_Alter] on database for drop_table,alter_table as
begin
  raiserror('不能删除和修改表',16,1);
  rollback;
end

7.4 触发器的管理 

--重命名触发器
 exec sp_rename 'Trigger_T_Table_Update','Trigger_T_Table_Update3';

 --禁用触发器
 disable trigger [Trigger_T_Table_Update3] on [T_Table];

 --启用触发器
 enable trigger [Trigger_T_Table_Update3] on [T_Table];

 --删除触发器
 drop trigger [Trigger_T_Table_Update3];

8.游标

create trigger [dbo].[Trigger_T_Table_Update] ON [dbo].[T_Table] after update as 
begin
	set nocount on;
	declare @Name varchar(max);
	declare @ID numeric(18,0);
	declare c1 cursor for select [ID],[Name] from inserted;  --创建游标
	open c1;   --打开游标
	fetch next from c1 into @ID,@Name;
	while @@Fetch_Status = 0  --表示游标读取下一条数据是否成功
	begin
	  if(len(@Name) < 3)
	  begin
		 update [T_Table] set [Name] = (@Name + '+' + CAST(@ID as varchar(max))) where [ID] = @ID;
	  end
	  else
	  begin
	    update [T_Table] set [Name] = (@Name + '-' + CAST(@ID as varchar(max))) where [ID] = @ID;
	  end
	  fetch next from c1 into @ID,@Name;
	end
	close c1;  --关闭游标
    deallocate c1;  --释放游标
end

9.视图

--创建视图
create view [V_T_Table] as select [ID],[Name] from [T_Table];

--查询视图
select * from [sysobjects] where [name] = 'V_T_Table' and [xtype] = 'V';

--修改视图
alter view [V_T_Table] as select [ID] from [T_Table];

--删除视图
drop view [V_T_Table];

10.存储过程

--创建存储过程
create procedure [SelectTable] @ID numeric(18,0) as
select * from [T_Table] where [ID] = @ID;

--执行存储过程
exec [SelectTable] 4;

--修改存储过程
alter procedure [SelectTable] @ID numeric(18,0) = 3,@Num int output as
begin
  select [Name] from [T_Table] where [ID] = @ID;
  set @Num = @ID + 2;
end

--执行存储过程
declare @Num int;
exec [SelectTable] 4,@Num output;
select @Num;

--删除存储过程
drop procedure [SelectTable];

11.函数 

 11.1 将16进制字符串转换为整型

create function [dbo].[HexToInt] (@Hex varchar(max))
returns int
as
  begin
      set @Hex = upper(@Hex);
      declare @Result int = 0;
      declare @HexLen int = len(@Hex);
      declare @Index int = 1;
      declare @One char;
      while(@Index <= @HexLen)
        begin
            set @One = substring(@Hex, @Index, 1);
            declare @As int = ascii(@One);
            declare @Num int = 0;
            if(@As >= 48 and @As <= 57)
              begin
                  set @Num = cast(@One as int);
              end
            else if(@As >= 65 and @As <= 70)
              begin
                  set @Num = @As - 55;
              end;
            set @Result = @Result + power(16, @HexLen - @Index) * @Num;
            set @Index = @Index + 1;
        end;
      return @Result;
  end;

11.2 将整型转换为16进制字符串

create function [dbo].[IntToHex] (@IntNum int)  
returns varchar(max)
as  
begin  
  declare @Mods int,@Res varchar(max);  
  set @Res = '';  
  while @IntNum <> 0  
  begin  
   set @Mods = @IntNum % 16;  
   if @Mods > 9  
      set @Res = char(ascii('A') + @Mods - 10) + @Res;  
   else  
      set @Res = cast(@Mods as varchar(1)) + @Res;  
   set @IntNum = @IntNum / 16;  
  end;  
  return @Res;  
end;

11.3 调用函数

--对于自定义的函数一定要加上 dbo，不加表示内置函数
select dbo.IntToHex(2550);

11.4 内置函数 

str	返回由数字数据转换来的字符数据。 字符数据右对齐，具有指定长度(默认为10)和十进制精度。	select str(25.698,4,1);	结果：‘25.7’
left/right	返回字符串中从左(右)边开始指定个数的字符。	select left('abcdefg',4);	结果：abcd
cast	由一种数据类型转换为另一种数据类型	declare @Num int; set @Num = cast(substring('658abc',1,3) as int); select @Num;	结果：658
convert	由一种数据类型转换为另一种数据类型	declare @Num int; set @Num = convert(int ,substring('658abc',1,3)); select @Num;	结果：658

11.5 函数和存储过程的区别

•  调用方式不一样，储存过程是用 exec 来执行，而函数是直接调用
• 函数始终只能返回一个值(一个标量值或一个表格)。而存储过程可以返回一个标量值、一个表值或无需返回值。
• 设计中函数相当于静态方法（执行一些单一和表不相关的算法），而存储过程相当于对象方法（执行和表有关的算法）

12.附录 

12.1 标识值（自增列） 

 一个表只能有一个标识列。

--报告当前标识值
dbcc checkident ('T_Table', noreseed);

--重置标识值
dbcc checkident ('T_Table', reseed, 10);

12.2 标识列插入(identity_insert )

默认情况下标识列的值是不能手动插入的（标识列不能应用更新）。 

--开启标识列更新
set identity_insert [T_Table] on;

--关闭标识列更新
set identity_insert [T_Table] off;

12.3 choose

select choose (3,1,2,3,4);  --结果：3

12.4 case

  declare @Num int = 1;
  select case @Num 
  when 1 then  '11'
  when 2 then  '22'
  else  '33'
  end;

  declare @Num int = 4;
  select case  
  when @Num = 1 then  '11'
  when @Num * 2 = 8 then  '22'
  else  '33'
  end;

12.5 row_number 

select *,row_number() over (order by [ID]) as [Num] from [T_Table];

12.6 with

with #T1 as 
(
  select [ID],[Name],row_number() over (order by [ID]) as [Num] from [T_Table]
) select * from #T1;