SqlServer - 知识点

最新推荐文章于 2021-05-09 21:05:39 发布

itlwc

最新推荐文章于 2021-05-09 21:05:39 发布

阅读量1.2k

点赞数

分类专栏： Database-Sqlserver 文章标签： database sql server

本文链接：https://blog.csdn.net/itlwc/article/details/8097158

版权

Database-Sqlserver 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SQL简介

SQL全称Structured Query Language 结构查询语言
SQL是用于访问和处理数据库的标准的计算机语言
SQL对大小写不敏感
DML数据操作语言
	Data Manipulation Language
	DML语句执行完成时,不会被自动提交,可以回滚
DDL数据定义语言
	Data Definition Language
	DDL语句执行完成时,会被自动提交,不能回滚
DCL数据控制语言
	Date Control Language
	DCL用来设置用户或者角色权限

SqlServer数据类型

整型
	bigint
		从 -2^63 到 2^63-1 的整型数据
	int
		从 -2^31 到 2^31-1 的整型数据
	smallint
		从 -2^15 到 2^15-1 的整数数据
	tinyint
		从 0 到 255 的整数数据
	bit
		1 或 0 的整数数据
浮点型
	decimal
		从 -10^38+1 到 10^38–1 的固定精度和小数位的数字数据
	numeric
		功能上等同于decimal
货币
	money
		货币数据值介于 -2^63 到 2^63-1,精确到货币单位的千分之十
	smallmoney
		货币数据值介于 -214,748.3648 到 +214,748.3647,精确到货币单位的千分之十
日期
	datetime
		从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据
	smalldatetime
		从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据,精确到分钟
字符串
	char
		固定长度的非 Unicode 字符数据,最大长度为 8,000 个字符
	varchar
		可变长度的非 Unicode 数据,最长为 8,000 个字符
	text
		可变长度的非 Unicode 数据,最大长度为 2^31-1 个字符
Unicode字符串
	nchar
		固定长度的 Unicode 数据,最大长度为 4,000 个字符
	nvarchar
		可变长度 Unicode 数据,其最大长度为 4,000 字符
	ntext
		可变长度 Unicode 数据，其最大长度为 2^30-1 个字符
二进制字符串
	binary
		固定长度的二进制数据,其最大长度为 8,000 个字节
	varbinary
		可变长度的二进制数据,其最大长度为 8,000 个字节
	image
		可变长度的二进制数据,其最大长度为 2^31-1 个字节
其他的类型
	cursor
		游标的引用
	table
		一种特殊的数据类型,存储供以后处理的结果集
	timestamp
		数据库范围的唯一数字,每次更新行时也进行更新
	uniqueidentifier
		全局唯一标识符GUID

运行命令

打开查询分析器
	isqlw,前提是环境变量path有SQL的Bin路径
打开和关闭MSSQLServer服务
	启动默认实例
		net start mssqlserver
	启动命名实例
		net start mssql$NewData
	关闭默认实例
		net stop mssqlserver
	关闭命名实例
		new stop mssql$NewData

备份和还原数据库

备份库
	backup database qnmis to disk='e:\qnmis20110101'
还原库
	restore database qnmis from disk='e:\qnmis20110101'

标识符

@
	局部变量
@@
	全局变量
#
	局部临时对象
##
	全局临时对象

语法

distinct

取不重复的数据
	select distinct js_bh from d_xt_yh

newid()

随即取10条数据
	select top 10 * from d_xt_yh order by newid()

Top

规定要返回的记录的数目
返回前两条记录
	select top 2 * from d_xt_yh
	select top 2 yh_bh,yh_mm from d_xt_yh
返回前百分比记录
	select top 50 percent * from d_xt_yh
	select top 50 percent yh_bh,yh_mm from d_xt_yh

like

用于在 WHERE子句中搜索列中的指定模式
%
	替代一个或多个字符
	以"标段"结尾,"标段"前面有一个或者多个字符
		select * from d_xt_yh where yh_mc like '%标段'
	以"标段"开始,"标段"后面有一个或者多个字符
		select * from d_xt_yh where yh_mc like 'B%'
_
	仅替代一个字符
	以"1标段"结尾,"1标段"前面有一个字符
		select * from d_xt_yh where yh_mc like '_1标段'
	以"A1标"开始,"A1标"后面有一个字符
		select * from d_xt_yh where yh_mc like 'A1标_'
[   ]
	指定范围
	[a-f]表示[abcdef]中的任何单个字符
	以"1标段"结尾,"1标段"前面有一个字符,字符是abcdef任意一个
		select * from d_xt_yh where yh_mc like '[a-f]1标段'
	'abc[def]'
		表示abcd,abce,abcf
[^  ]
	不属于指定范围的任何单个字符
	以"1标段"结尾,"1标段"前面有一个字符,字符不是bcdef任意一个
		select * from d_xt_yh where yh_mc like '[^bcdef]1标段'

运算符

and
or
in
is null
between and
not
not in
is not null
not between and

waitfor

等待10秒钟再查询
	waitfor delay '00:00:10'
	select * from d_dept
等待到16时54分30秒再查询
	waitfor time '16:54:30'
	select * from d_dept
需要注意
	delay 最多24小时
time 必须是 datetime 类型,且不允许指定 datetime 值的日期部分

排序

升序排列  
	select * from dbo.d_xt_yh order by yh_bh asc  
降序排列  
	select * from dbo.d_xt_yh order by yh_bh desc  
别名排序  
	select yh_bh as '用户编号' from dbo.d_xt_yh order by '用户编号' desc  
列位置排序  
	select * from dbo.d_xt_yh order by 1 desc  
多列排序  
	select * from dbo.d_xt_yh order by yh_bh desc,jg_bh asc

连接

left join
	左表为基础,左表全部显示出来,右表只显示符合条件的记录,
	右表记录不足均为null
right join
	右表为基础,右表全部显示出来,左表只显示符合条件的记录,
	左表记录不足均为null
inner join
	不以左右表为基础,只显示左右表匹配的
full join
	不以左右表为基础,只显示左右表匹配的
union
	合并两个或多个SELECT语句的结果集

对表的操作

创建表并带有自增列
	create table d_emp(
		emp_id int identity(1,1) primary key,
		emp_name varchar(50),
		emp_age int
	)
删除表  
	delete from d_emp 
	truncate table d_emp  
	drop table d_emp  
增加列 
	alter table d_emp add dept_id int
删除列  
	alter table d_emp drop column dept_id  
修改列的定义  
	alter table d_emp alter column emp_name varchar(100)  
修改表列名  
	exec sp_rename 'd_emp.emp_name','name','column'
修改表名  
	exec sp_rename 'd_emp','d_emp_tmp','object'

创建临时表的几种方法

方法一
	select * into #temp from d_emp
方法二
	insert into #temp
	select * from d_emp
方法三
	insert into #temp
	select 1,'李文超',2

判断

declare @cph varchar(20)
if @cph in ('',null,'null')
	begin
		set @cph='陕a88888'
	end
else
 	begin
 		set @cph='陕a12345'
 	end
select @cph

case when

select emp_name,
case
when emp_age<18 then '未成年'
when emp_age>=18 then '成年人'
end
as mark
from d_emp

循环

declare @count int
declare @index int
set @index=1
select @count=count(*) from d_xt_yh
select identity(int,1,1) as number,* into #temp from d_xt_yh
while @index<=@count
	begin
		select * from #temp where number = @index
		set @index=@index+1
	end
drop table #temp

约束

对于基本表而言约束分为
列约束:列约束是对某一列的约束,包含在列定义中,不必指定列的名称
表约束:不包含在列定义中,通常用于多个列一起进行约束,必须指定列的名称

主键约束

约束唯一标识数据库表中的每条记录
增加1
	create table d_emp(
		emp_id int primary key not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int
	)
增加2
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int,
		primary key (emp_id)
	)
增加3
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int,
		constraint pk_d_emp primary key (emp_id)
	)
增加4
	alter table d_emp add primary key (emp_id)
增加5
	alter table d_emp
	add constraint pk_d_emp primary key (emp_id)

删除1
	alter table d_emp  
	drop constraint pk_d_emp

如果不知道某个表的主键名称可以通过下面语句进行查找
	select name from sysobjects
	where parent_obj=object_id('d_emp') and xtype='pk'

复合主键

增加1
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50)
		primary key (emp_id,emp_name)
	)
增加2
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50)
		constraint pk_d_emp primary key (emp_id,emp_name)
	)
增加3
	alter table d_emp
	add constraint pk_d_emp primary key (emp_id,emp_name)

外键约束

foreign key约束
	仅引用位于同一服务器上的同一数据库表,跨数据库的引用必须通过触发器来实现
	可引用同一表的其他列,此行称为自引用
	还可以定义为引用另一张表的unique约束
列级foreign key约束
	references子句只能列出一个引用列,引用列必须和定义约束列数据类型相同
表级foreign key约束
	references 子句可以列出多个引用列,引用列的个数,数据类型必须和定义约束列相同
在临时表中不可以使用foreign key约束
增加1
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int foreign key references d_dept(dept_id)
	)
增加2
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int,
		foreign key (dept_id) references d_dept(dept_id)
	)
增加3
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int,
		constraint fk_d_emp foreign key (dept_id) references d_dept(dept_id)
	)
增加4
	alter table d_emp add foreign key (dept_id)
	references d_dept(dept_id)
增加5
	alter table d_emp
	add constraint fk_d_emp foreign key (dept_id)
	references d_dept(dept_id)

删除1
	alter table d_emp drop constraint fk_d_emp

如果不知道某个表的外键名称可以通过下面语句进行查找
	select name from sysobjects
	where parent_obj=object_id('d_emp') and xtype='f'

级联引用完整性约束

删除或者更新某一行,而该行被其他表外键所引用,则产生错误并回滚
	alter table d_emp add foreign key (dept_id) references d_dept(dept_id)
	on delete no action on update no action
删除或者更新某一行,而该行被其他表外键所引用,则也将子表行删除
	alter table d_emp add foreign key (dept_id) references d_dept(dept_id)
	on delete cascade on update cascade 
(2005特有)删除或者更新某一行,而该行被其他表外键所引用,则子表行设置为空
	alter table d_emp add foreign key (dept_id) references d_dept(dept_id)
	on delete set null on update set null
(2005特有)删除或者更新某一行,而该行被其他表外键所引用,则子表行设置为默认值
	alter table d_emp add foreign key (dept_id) references d_dept(dept_id)
	on delete set default on update set default

检查约束

约束用于限制列中的值的范围
增加1
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int,
		check (emp_age>18 and emp_age<60)
	)
增加2
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int,
		constraint chk_d_emp_emp_age check (emp_age>18 and emp_age<60)
	)
增加3
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int
		constraint chk_d_emp_emp_age check (emp_age>18 and emp_age<60)
	)
增加4
	alter table d_emp add check (emp_age>18 and emp_age<60)
增加5
	alter table d_emp add constraint chk_d_emp_emp_age
	check (emp_age>18 and emp_age<60)

删除1  
	alter table d_emp    
	drop constraint chk_d_emp_emp_age  

如果不知道某个表的检查约束名称可以通过下面语句进行查找  
	select name from sysobjects
	where parent_obj=object_id('d_emp') and xtype='c'

禁用检查约束
	alter table d_emp
	nocheck constraint all

默认约束

约束用于向列中插入默认值
增加1
	create table d_emp(
		emp_id int not null primary key,
		emp_name varchar(50) default '李文超',
		emp_age int,
		dept_id int
	)
增加2
	alter table d_emp add default('李文超') for emp_name
增加3
	alter table d_emp add constraint df_d_emp_emp_name 
	default('李文超') for emp_name

删除1    
	alter table d_emp      
	drop constraint df_d_emp_emp_name    

如果不知道某个表的默认约束名称可以通过下面语句进行查找    
	select name from sysobjects
	where parent_obj=object_id('d_emp') and xtype='d'

唯一约束

唯一标识数据库表中的每条记录
主键约束和唯一约束的区别在于
	一个表中可以定义多个唯一约束但只能定义一个主键约束
	唯一约束允许一个null值,主键约束不允许
增加1
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50) unique,
		emp_age int,
		dept_id int
	)
增加2
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int,
		unique (emp_name)
	)
增加3
	create table d_emp(
		emp_id int not null,
		emp_name varchar(50),
		emp_age int,
		dept_id int,
		constraint uq_d_emp_emp_name unique (emp_name)
	)
增加2
	alter table d_emp add unique (emp_name)
增加3
	alter table d_emp add constraint uq_d_emp_emp_name unique (emp_name)

删除1    
	alter table d_emp      
	drop constraint uq_d_emp_emp_name    

如果不知道某个表的默认约束名称可以通过下面语句进行查找    
	select name from sysobjects
	where parent_obj=object_id('d_emp') and xtype='uq'

NOT NULL约束

指定该约束列不接受NULL值

系统对象

系统表

sysobjects 在数据库每创建一个对象在表中占一行
name 		sysname 	对象名 
Id 			int 		对象标识号
xtype 		char(2) 	对象类型
	C = CHECK 约束
	D = 默认值或 DEFAULT 约束
	F = FOREIGN KEY 约束
	L = 日志
	FN = 标量函数
	IF = 内嵌表函数
	P = 存储过程
	PK = PRIMARY KEY 约束(类型是 K)
	RF = 复制筛选存储过程
	S = 系统表
	TF = 表函数
	TR = 触发器
	U = 用户表
	UQ = UNIQUE 约束(类型是 K)
	V = 视图
	X = 扩展存储过程
uid 		smallint 	所有者对象的用户ID
parent_obj 	int 		父对象的对象标识号(例如，对于触发器或约束,该标识号为表ID)
crdate 		datetime 	对象的创建日期

系统存储过程

更新对象的名称
	execute sp_rename 'd_dept','d_dept_new'
查看可用的数据库
	execute sp_helpdb
查看对象定义内容
	execute sp_helptext 'v_dept'
强制在下次执行存储过程对其重新编译
	execute sp_recompile 'p_dept'
刷新视图
	execute  sp_refreshview 'v_emp'
查看视图的特征
	execute sp_help 'v_emp'
查看视图所依赖的对象
	execute sp_depends 'v_emp'
查看表中的索引
	execute sp_helpindex 'd_dept'
启动 itlwc数据库 直接递归触发器
	execute sp_dboption 'itlwc','recursive_triggers',true

系统函数

object_id('')
	返回对象的ID
	得到d_emp表中主键约束
		select name from sysobjects
		where parent_obj=object_id('d_emp') and xtype='fk'

对象

视图

视图不是真实存在的基础表而是一个虚拟表,
	视图所对应的数据并不实际以视图结构存储在数据库中,而是存储在视图所引用的表中
视图的分类
	标准视图:
	索引视图:
	分区视图:
视图的作用
	集中数据,定制数据,合并分隔数据,安全机制
视图最多1024列

创建视图  
	create view v_emp  
	as   
	    select * from d_emp  
删除视图  
	drop view v_emp  

对视图的操作
	insert into v_dept values(1,'开发部')
	insert into v_dept values(2,'财务部')
	update v_dept set dept_name='人事部' where dept_id=2
	delete from v_dept where dept_id=2

存储过程

存储过程是存放在服务器端数据库中的子程序
存储过程在第一次执行时,进行语法检测和编译,执行后他的执行计划就驻留在高速缓存中,用于后续调用
存储过程的分类
	系统存储过程:由系统提供,存放在MASTER数据库中,前缀为SP
	用户存储过程:由用户创建
系统存储过程主要包含
	目录存储过程
	复制类存储过程
	安全管理类存储过程
	分布式查询存储过程
自定义的存储过程,应避免使用SP_为前缀.
因为接收到SP_开头的存储过程,SQL SERVER 首先会在 MASTER 数据库中寻找存储过程,这会影响执行效率
存储过程最多可以有2100个参数,最大为128MB

不带参数的存储过程
	create proc p_dept
	as
	select * from d_dept
带输入参数的存储过程
	create proc p_dept
		@dept_id int
	as
		select * from d_dept where dept_id=@dept_id
带输入输出参数的存储过程
	create proc p_dept
		@dept_id int,
		@dept_name varchar(50) output
	as
		select @dept_name=dept_name from d_dept where dept_id=@dept_id
存储过程中执行存储过程
	create proc p_result
		@dept_id int
	as
		declare @emp_name varchar(50)
		exec p_dept 
			@dept_id,
			@emp_name output
		select @emp_name

函数

函数是由一个或者多个T-SQL语句组成的子程序,用于封装代码便于重新使用
自定义函数只能返回单一值或者表
函数不能对基表进行DML语句
根据函数返回类型不同,将用户自定义的函数分为三种类型
	标量函数:函数返回一个确定类型的标量值
	内联表函数:以表的形式返回一个返回值
	多语句表值函数:标量函数和内联表函数的结合体

增加标量函数
	create function fn_getDeptName(@dept_id int)
		returns varchar(50)
	as
	begin
	 	declare @dept_name varchar(50)
		select @dept_name=dept_name from d_dept where dept_id=@dept_id
		return @dept_name
	end
执行标量函数
	select dbo.fn_getDeptName(1)
删除标量函数
	drop function fn_getDeptName

增加内联表函数
	create function if_d_dept(@dept_id int)
		returns @temp table(id int,name varchar(50))
	as
	begin
		insert into @temp
		select dept_id,dept_name from d_dept where dept_id=@dept_id
	 return
	end
执行内联表函数
	select * from if_d_dept(1)
删除内联表函数
	drop function if_d_dept

增加多语句表值函数
	create function fnif_d_dept(@dept_name varchar(50))
		returns @temp table(id int,name varchar(50))
	as
	begin
		--函数中使用临时表
		declare @result table(id int,name varchar(50))
		insert into @result
		select dept_id,dept_name from d_dept where dept_name=@dept_name
	
		insert into @temp
		select * from @result
	 return
	end
执行多语句表值函数
	select * from fnif_d_dept('开发部')
删除多语句表值函数
	drop function fnif_d_dept

触发器

触发器是一种与数据表紧密关联的特殊存储过程
当数据表进行 insert delete update 事件发生时,
	所设置的触发器就会自动执行,保持数据库的完整性
触发器在数据库以独立的对象存储(存储过程通过其他程序启动),
	触发器不能直接调用,只能通过事件来启动
触发器不能传递或者接收参数
触发器的类型
	DML触发器:在数据库数据发生操作语言(DML)事件时将调用 DML 触发器
		after 触发器:在执行了 insert delete update 语句之后,after 触发器才会被激发
			用于对变动数据进行检查,如果错误,将拒绝后者回滚
			after 触发器只能在表上指定
			每个触发操作可以包含多个触发器
			插入和删除表中的 text,ntext,image 列引用,不能使用
		instead of 触发器:数据变动之前被激活,转而去执行触发器定义的操作,并不再执行原来SQL操作
			instead of 触发器可以指定在视图和表上
			每个触发操作只能包含一个触发器	
			
	DDL触发器(2005):当服务器或者数据库发生数据定义语言(DDL)事件,将调用触发器
		DLL触发器主要有,数据库作用域和服务器作用域
		DLL触发器用来管理任务,例如审核和控制数据库操作
触发器的作用
	DML触发器可以实现级联更改,保证数据的完整性
触发器两个特殊虚表
	inserted和deleted表,系统在内存中创建两张表,不存储在数据库中,两张表是只读的
	这两表被触发的表结构相同,当触发完成之后两张表被删除
	inserted 表存放增加的记录和存放更新后的记录
	deleted 表存放被删除的记录和存放更新之前的记录
测试特定列的 update 操作
	update 对某一个列的 update
	columns_update 对多个列的 update
	返回一个布尔值
嵌套触发器
	当某一个触发器执行,能够触发另外一个触发器执行,这种情况称为嵌套触发器
	如果一个触发器修改一张表,而这张表已经有了其他触发器,这时就使用了嵌套触发器
	由于触发器在事务中执行,如果在一组嵌套触发器的任意层发生错误,则整个事务将被取消,回滚
	触发器最多嵌套32级
递归触发器
	直接递归:
		默认情况SQL server是禁止 after 触发器的直接递归
		如果需要开启,sp_dboption 'itlwc','recursive_triggers',true
	间接递归
触发器的激发顺序
	execute sp_settriggerorder 'tigger_curd1','first','insert'
	execute sp_settriggerorder 'tigger_curd2','last','insert'

创建 after 触发器
	create table d_dept(
		dept_id int,
		dept_name varchar(20)
		constraint pk_d_dept primary key (dept_id)
	)
	create table d_deptBackup(
		operate varchar(20),
		operateTime smalldatetime,
		dept_id int,
		dept_name varchar(20)
	)
	--判断是否存在
	if(object_id('tigger_curd','tr') is not null)
		drop trigger tigger_curd
	go
	create trigger tigger_curd
	on d_dept
		after insert,delete,update
	as
		insert into d_deptBackup
		select '插入数据',getDate(),dept_id,dept_name from inserted
		insert into d_deptBackup
		select '删除数据',getDate(),dept_id,dept_name from deleted
创建 after delete 触发器
	if(object_id('tigger_curd','tr') is not null)
		drop trigger tigger_curd
	go
	create trigger tigger_curd
	on d_dept
		after delete
	as
		declare @rowcount int
		select @rowcount=@@rowcount	--当前删除操作影响的记录数		
		if @rowcount>1
			begin
				rollback transaction	--回滚取消操作
				raiserror('一次只能删除一条记录',16,1)	--给出错误信息
			end
		declare @dept_name varchar(20)
		set @dept_name = '开发部'
		if(@dept_name in (select dept_name from deleted))
		begin
			rollback transaction	--回滚取消操作
			raiserror('不能删除 开发部 ',16,1)	--给出错误信息
		end
使用 update()
	if(object_id('tigger_curd','tr') is not null)
		drop trigger tigger_curd
	go
	create trigger tigger_curd
	on d_dept
		after update
	as
		if(update(dept_id) or update(dept_name))
			begin
				rollback transaction	--回滚取消操作
				raiserror('不允许更新该列信息',16,1)	--给出错误信息
			end
创建 instead of 触发器
	create table emp2005(
		emp_id varchar(10),
		emp_name varchar(10)
	)
	create table emp2006(
		emp_id varchar(10),
		emp_name varchar(10)
	)
	create table emp2007(
		emp_id varchar(10),
		emp_name varchar(10)
	)
	create view v_emp
	as
		select * from emp2005
		union all
		select * from emp2006
		union all	
		select * from emp2007
			
	if(object_id('tigger_curd','tr') is not null)
		drop trigger tigger_curd
	go
	create trigger tigger_curd
	on v_emp
		instead of insert
	as
		declare @emp_id char(4)
		select @emp_id = substring(emp_id,1,4) from inserted
		if @emp_id ='2005'
			begin
				insert into emp2005
				select * from inserted
			end
		else if @emp_id ='2006'
			begin
				insert into emp2006
				select * from inserted
			end
		else if @emp_id ='2007'
			begin
				insert into emp2007
				select * from inserted
			end
		else
			begin
				rollback transaction	--回滚取消操作
				raiserror('插入记录emp_id不正确',16,1)	--给出错误信息
			end
创建 递归触发器
	insert into d_dept values(1001,'开发部')
	insert into d_dept values(1002,'开发部')
	insert into d_dept values(1003,'开发部')
	insert into d_dept values(1004,'开发部')
	insert into d_dept values(2001,'开发部')
	insert into d_dept values(2002,'开发部')
	insert into d_dept values(3001,'开发部')

	if(object_id('tigger_curd','tr') is not null)
		drop trigger tigger_curd
	go
	--删除本身并且删除前一个和后一个
	create trigger tigger_curd
	on d_dept
		after delete
	as
		declare @rowcount int
		set @rowcount = @@rowcount
		if @rowcount=1
			begin
				declare @up_dept_id int
				declare @down_dept_id int
				select @up_dept_id=dept_id,@down_dept_id=dept_id from deleted
				set @up_dept_id=@up_dept_id-1
				set @down_dept_id=@down_dept_id+1
				delete from d_dept where dept_id=@up_dept_id
				delete from d_dept where dept_id=@down_dept_id
			end
	--测试:最后只剩下 2001,2002,3001
	delete from d_dept where dept_id=1002

索引

概念  
	索引是对数据库表中一个或多个列的值进行排序的结构
	建立索引目的是对表中记录的查询或者排序
	当表中有别设置为 unique 的字段时,sql server 会自动建立一个非聚集的唯一性索引  
	当表中有 primary key 的字段时,sql server 会自动建立一个聚集索引
索引的作用  
	索引相当与书的目录,无需对数据库进行整体扫描,加快数据检索速度  
	通过唯一索引,可以保证数据的唯一性  
	可以加速表与表之间的连接  
	在使用 order by 和 group by 子句进行检索数据,显著减少查询中排序和分组的时间  
	使用索引可以在检索数据的过程中使用优化隐藏器,提过系统的性能  
使用索引
	查询表中所有数据,使用索引是没有意义的  
	索引列应该在 where 子句中频繁使用的列
	当用户要检索字段的数据包含很多数值或者很多控制 NULL 时,为该列创建索引会大大提高速度
	经常排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序速度
	索引占用数据库空间,在设计数据库应该把索引空间考虑进去
	尽量把表和他的索引存在不同的磁盘上,这样会提高查询速度
	不给数据较少和数据较大但不经常查询的表建立索引  
	多表连接查询的时候,最好能够为关联字段建立索引  
	当表数据以查询为主导,更新相对少,则要多采用索引,增加检索速度  
	当表数据更新为主导,查询相对少,则不要建立太多的索引,避免影响更新的速度
	如果对表进行大量更新时,可以先销毁索引,等数据更新完成之后再创建索引  
	要在表的更新速度与查询速度之间寻求一个平衡点
索引分类
	聚集索引:
		是按照数据存放的物理位置为顺序的
		也就是说改变了表中数据存放的物理位置
		对于多行检索的检索很快
		在插入新行或者更新聚集索引一部分列时,DBMS将自动重新排序
	非聚集索引:
		独立于数据行的结构
		非聚集索引包含键值和行定位器
		对于单行的检索很快
		一个表中可以创建多个非聚集索引,SQL Server 查询优化器会自动决定使用哪个索引
  
创建简单索引  
	create index index_d_dept  
	on d_dept(dept_name)  
创建唯一索引  
	create unique index index_d_dept  
	on d_dept(dept_name)  
创建多列索引  
	create unique index index_d_dept  
	on d_dept(dept_id,dept_name)  
删除索引  
	drop index d_dept.index_d_dept 
禁用索引(2005)
	alter index index_d_dept
	on d_dept disable
启用索引(2005)
	alter index index_d_dept
	on d_dept rebuild
重命名索引
	execute sp_rename 'd_dept.index_d_dept','d_dept.index_d_dept_new','index'
查看表中的索引
	execute sp_helpindex 'd_dept'

自定义数据类型

增加自定义数据类型
	execute sp_addtype lwctype,'varchar(60)','not null'
删除自定义数据类型
	execute sp_droptype lwctype

itlwc

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SqlServer - 知识点

1.运行中打开查询分析器isqlw ,当然前提是环境变量path有SQL的Bin路径比如: C:\Program Files\Microsoft SQL Server\80\Tools\BINN2.运行中打开和关闭MSSQLServer服务启动默认实例: net start mssqlserver启动命名实例: net start mssql$NewData关闭默认实例: net
复制链接

扫一扫