数据库

T-SQL编程

SELECT ESSN_汪, SUM(HOURS_汪) AS 小时总数,
'勤奋水平'= CASE 
	    WHEN (SUM(HOURS_汪)>=400)
		    THEN '非常勤奋'
	    WHEN(SUM(HOURS_汪)>=200)
		    THEN '勤奋'
	    WHEN(SUM(HOURS_汪)<200)
		    THEN '一般'
	    WHEN SUM(HOURS_汪) IS NULL
		    THEN '无记录'
	    END
FROM WORKS_ON_wangsipeng
GROUP BY ESSN_汪
-------------------------------------------------------------------------------------
DECLARE @randomNum INT
DECLARE @guessNum INT
DECLARE @low INT
DECLARE @high INT
SELECT @randomNum=CAST(CEILING(RAND()*100) AS INT)
PRINT '我生成的随机数是' + str(@randomNum);
SELECT @guessNum=50,@low=0,@high=100
WHILE(@randomNum!=@guessNum)
BEGIN
	   PRINT @guessNum
	   if(@randomNum<@guessNum)
		   BEGIN
		   PRINT '大了！'
		   SET @high=@guessNum
		   END
	   else
		   BEGIN
		   PRINT '小了！'
		   SET @low=@guessNum
		   END
	   SET @guessNum=(@high+@low)/2
END
PRINT '猜对了！'

索引

---创建唯一非聚集索引
create unique nonclustered index indexName on tableName(indexColumn) with
(fillfactor=80
)
---重命名索引
execute sp_rename
'tableName.indexName',
'preName','curName'
---不同列序
create index indexName on tableName(index1 asc ,index2 desc)
---删除索引
drop tableName.indexName

存储过程

---创建存储过程
create proc procName
as xxxx
exec procName
---创建带有参数的存储过程
create proc procName(@param int=0)  或者(@str varchar(20)='p')
as
exec procName 1000
---创建带有输出的存储过程
create proc procName(@param1 int,@param2 int output)
as
set @param2=(select avg(salary) from tableName)
declare @outputParam int
exec procName 1,@outputParam output
---创建带有返回值的存储过程
create proc procName(@res varchar(20))
return @res
declare @x varchar(20)
execute @x=procName '666'

自定义函数

returns @员工参与项目表 table (ssn char(9),
		fname varchar(20),
		lname varchar(20),
		pname varchar(50),
		hours real)
insert @员工参与项目表 select ESSN_汪,FNAME_汪,LNAME_汪,PNAME_汪,HOURS_汪
---创建带返回值的函数
create function funcName (@param varchar(20))
returns table
as
begin
---直接返回
    return
    (
        select * from tableName
    )
end
go
select * from  dbo.funcName('xxx')---一定要加dbo

游标

declare queryCursor cursor
for select fname,lname from employee
declare @fname varchar(20),@lname varchar(20)
open queryCursor
fetch next from queryCursor into @fname,@lname ---下一行
fetch absolute 3 ---绝对位置为第三行
while @@fetch_status==0
begin
    ---获取当前游标
    delete Employee_wangsipeng where current of deleteCursor
    fetch next from queryCursor into @fname,@lname
end
close queryCursor
deallocate queryCursor
go

函数中带有游标

create function funcName(@param char)
returns @tableName table
(
    ssn char,
    fname char
)
as
begin
declare cursorName cursor
for select ssn,fname from tableName
open cursorName
fetch next from cursorName into @ssn,@fname
while(@@fetch_status==0)
begin
    xxx
    fetch next from cursorName into @ssn,@fname
end
end

触发器

insert触发器

CREATE trigger  effect
on tableName
for insert
as
begin
declare @ssn int
select @ssn=ssn_汪 from inserted
end

update触发器

create trigger updateTrigger
on tableName
for update
as
begin
declare @ssn int
select @ssn=ssn_汪 from inserted
end

delete触发器

create trigger deleteTrigger
on tableName
instead of delete
as
begin
    select ssn from deleted
end

instead of 触发器更新视图(由多张基表构成的试图不能更新,因为回影响到多个基表)

create view viewName
as
begin
    xxx
end
insert viewName values(xxxxx)
create trigger viewTrigger
on viewName
instead of insert
as
begin
    xxx
end

事务控制

添加主键或者约束

---主键
alter table tableName
add constraint pkName primary key(columnName)
---约束
add constraint ckName check(sex='F' or sex='M')

• 自动事务模式

只有出错的语句不会被执行,而其他无错的语句回正常执行

SET IMPLICIT_TRANSACTIONS ON; 
-- ON是打开隐式事务模式或关闭自动事务模式，OFF 是关闭隐式事务模式或打开自动提交事务模式

• 显示事务模式

如果不加入异常处理和回滚程序,出错的语句就不会执行,而其他正确的语句回正常执行
通过begin transaction开启事务

• 隐式事务

不用begin、transaction来开启事务,每个SQL语句一执行就会开启事务
直到commit或者rollback结束事务,如果不加入异常处理和回滚程序,出错的语句就不会执行
而其他无错的语句会正常执行

• 回滚事务

set xact_abort 是设置事务回滚的,默认为off,XACT_ABORT开关仅仅是中断当前事务的执行并回滚，但不会影响已经提交的事务

为on的时候在自动事务模式下，不仅出错的语句不会执行，并且出错语句之后的语句也不会执行，但出错语句之前已经执行的语句不会回滚
因为在自动事务模式下,每个sql语句都是单独的事务


打开XACT_ABORT开关后，在显式事务模式下，不仅出错的语句不会执行
并且出错语句之后的语句也不会执行，而且出错语句之前已经执行的语句会回滚。关闭出错语句之后会执行

在显式事务模式下加入了异常处理程序后,当程序执行到出错语句后
会跳转到CATCH段回滚整个事务已经完成的操作（出错语句之后的语句不再执行）

数据库和表存储结构

• 创建数据库

create database dbName on primary(name='logicName';filename='physicalAddressAndName mdf';size='';maxSize='';filegrowth=''),
file group(同上 ndf 可以多个) log on(同上 ldf)

• 存储结构
  30000800 2d000000 05000604 00170017
  0017 001b 00435373 777075

30代表得是插入字段有变长得(10则是定长得),0800指向第八个数据 0500表示有五个字段
2d00是定长字段得数据
0500后面得06是null位图,转换为而二进制为0110,从1开始数,表示二三字段为null
04表示有4个变长字段  第一个0017表示第一个变长字段的结束位置,转为十进制是23
表示第结束于第二十三个数据,数据为4353.
第二个0017表示第二个变长字段为NULL,第三个同理 第四个变长字段结束位置为001b
数据是73777075

 在关系代数中，一种基于同名共通属性列相等、且结果中重复的列被去掉的连接运算是：
 自然连接

删除表 drop table tableName

模式和内模式只能有一个,外模式可以有多个

SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，其中，CREATE、DROP、ALTER语句是实现哪种功能？（数据定义ddl ）

参照完整性要求有关联的两个表之间数据的一致性。参照完整性可以通过建立（主键和外键约束 ）来实现。