一、基本的建库、建表操作（增删改查略去不讲）
--创建数据库School
Create database School
--使用数据库
Use School
--创建学生表（SID为自增主键，Ssex有Check约束，且所有字段，均不为空）
create table Student(SID int identity not null primary key,Sname nvarchar(30) not null,Sage int not null, Ssex nvarchar(5) check(Ssex='男' or Ssex='女') not null);
alter table Student alter column Sage nvarchar(30) not null;（修改表）
--创建课程表（CID为自增主键，且均不为空）
create table Course(CID int identity not null primary key,Cname nvarchar(30) not null,TID int not null);
--创建成绩表（SID为自增主键，且均不为空）
create table Score(SID int identity not null primary key,CID int not null,Score int not null);
--修改成绩表，为其添加一个外键CID约束
alter table Score
add constraint FK_SC foreign key(CID) references Course(CID);
--修改课程表，删除其原来的外键约束
alter table Course
drop constraint FK_SC
--修改课程表，删除其原来的外键约束
alter table Course
drop constraint FK_SC--修改课程表，删除其原来的外键约束
alter table Course
drop constraint FK_SC--修改课程表，删除其原来的外键约束
alter table Course
drop constraint FK_SC--修改课程表，删除其原来的外键约束
alter table Course
drop constraint FK_SC--修改课程表，删除其原来的外键约束
alter table Course
drop constraint FK_SC--修改课程表，删除其原来的外键约束
alter table Course
drop constraint FK_SC
--修改成绩表，为其添加一个外键CID约束
alter table Score
add constraint FK_SC foreign key(CID) references Course(CID);
--修改成绩表，为其添加一个外键CID约束
alter table Score
add constraint FK_SC foreign key(CID) references Course(CID);
--修改成绩表，为其添加一个外键CID约束
alter table Score
add constraint FK_SC foreign key(CID) references Course(CID);
--修改成绩表，为其添加一个外键CID约束
alter table Score
add constraint FK_SC foreign key(CID) references Course(CID);
--级联删除（B表应用A表主键做外键，A表删除一条信息时也要把B的一条信息给删除）
--创建索引
create fulltext index on Change (id)
---创建表TestA
create table TestA
(
id varchar(20) primary key,
password varchar(20) not null
)
--创建表Test并添加外键
create table TestB
(
id int identity(1,1) primary key,
name varchar(50) not null,
uerid varchar(20),
foreign key (uerid) references TestA(id) on delete cascade--声明级联删除
)
--创建新表，同时与其他表关联， 当删除父记录时 同时删除该记录也就是当删除orders表中的一条记录
create table orderdetails
( orderid int not null , id int not null , description varchar(123) not null,
--设置主键
constraint pkOrderdetails primary key (orderid,id),
--设置外键，级联操作
constraint fkOrderContainsDetails foreign key (orderid) references orders(orderid)
on update no action on delete cascade)---当更新操作时不级联，当删除操作时级联
--声明一个临时表，在删除数据之前，将将要删除的数据插入临时表里，主要用到output， deleted.字段
declare @Test as Table(tname nvarchar(30),tage nvarchar(30),tsex nvarchar(30))
delete Student
output deleted.Sname,deleted.Sage,deleted.Ssex into @Test
where Sname='赵雷'
select * from @Test
select * from Student;
--声明一个临时表，在插入数据之前，将将要插入的数据插入临时表，主要用到output ，inserted.字段
declare @Test1 as Table(tname nvarchar(30),tage nvarchar(30),tsex nvarchar(30))
insert into Student output inserted.Sname,inserted.Sage,inserted.Ssex into @Test1 values('Olive','1990-01-16','女');
select * from @Test1
二、常用的系统存储过程
--如何修改数据库名称
sp_renamedb 'db_oldName','db_newName'
--数据库名称
select db_Name()
--应用程序程序名称
select app_name()
--获取登录者名字
select suser_sname()
--获取字段定义的长度
select col_length('Student','Sname')
--获取架构名
select object_schema_name(object_id('tb'))
--获取字段名称
select col_name(object_id('Student'),2)
--查看数据库
sp_helpdb
--获取表的数据结构及相关信息
sp_help Student
--查看表信息
sp_tables Student
--查看字段信息
sp_columns Student
--查看索引
sp_helpIndex Student
--查看存储过程
sp_helptext 'ShowAllChuKuInfo'
--查看约束
sp_helpConstraint Student
--查看触发器定义
sp_helptext 'InsertTwo'
--sp_helptrigger 用于查看触发器的属性
sp_helptrigger 'InsertTwo'
三、常用的操作和常用函数
ABS：ABS函数返回数值表达的绝对值
语法：ABS（numeric_expression）可以是整型、短整型、微短整型、小数、浮点数、货币、实型或小货币型数。
ACOS：ACOS函数是一个数学函数，它返回以弧度表示的角度，即：ARCCOS。
语法：ACOS（float_expression）变量是浮点数数据类型，表示角度的余弦。
ASIN：反正弦函数。
SIN：它返回表达式中的正弦值。
语法：SIN（ float_expression）float_expression是浮点型的资料
COS：它返回表达式中的余弦值。
语法：COS（ float_expression）float_expression是浮点型的资料。
TAN：它返加表达式的正切值。
COT：它返回表达式中的余切值。
ATAN：反正切函数。
SIGN：如果表达式为正，SIGN函数返回1，如果表达为负，SIGN返回-1，如果表达式为0，则函数返回0。
CEILING：返回不小于给定的数值表达式的最小整数。
语法：CEILING（numeric_expression）小数、浮点、整数、实数、货币等类型。
例如：SELECT CEILING（1.45）返回值为2
SELECT CEILING （-1.45）返回值为-1
FLOOR：返回不大于给定的数值表达式的最大整数。
语法：FLOOR（numeric_expression）小数、浮点、整数、实数、货币等类型。
例如：SELECT FLOOR（1.45）返回值为1
SELECT FLOOR （-1.45）返回值为-2
ASCII：ASCII函数返回整数，表示字符表达式最左边的字符的ASCII代码值。
语法：ASCII（character_expression）
例：ASCII（‘AB’）返回65。
CHAR：字符串函数，用于将ASCII码整数转换为字符。
语法：CHAR（integer_expression）变量是0~255间的正整数。
例如：CHAR（65）返回‘A’
CHARINDEX：字符串函数，返回想要的查找字符串，在目标字符中的起始位置。
语法：CHARINDEX（‘PATTERN’，EXPRESSION[，START_LOCATION]）；
例如：CHARINDE（‘A’，‘BCDEA’，2）返回值为5，其2为从第几个字符进行搜索不写默认为从第一个位置。
AVG：AVG是个集合函数，它计算数值列的平均值，忽略空值，如果使用了DISTINCT则对重复的值只取一次进行平均。
语法；AVG（[ALL|DISTINCT] expression）
ALL是缺省值。
例如：SELECT DISTINCT（AGE）FROM TABLE1 如果任意行有NULL值，则此行被忽略。
COUNT：是一个集合函数，它返回的整数代表表达式中值的个数通为一个列，DISTINCT和COUNT一起使用时，返回单一值的个数。
语法：COUNT（{[ALL|DISTINCT] EXPRESSION |*}）
例如：SELECT COUNT（COLUMN1）FROM TABLE1，
返回COLUMN1中没有空值的行数，如果是使用*将返回表中所有行的数目。
使用DISTINCT则返回唯一值的个数。
SUM：SUM是一个集合函数，返回表格中选定的数值型列中的值的总和。
语法：SUM（[ALL|DISTINCT]expression）
MAX：MAX返回表达式中的最大值，
语法：MAX（expression）
MIN：MIN返回表达式中的最小值，
语法：MIN（expression）
GETDATE：GETDATE是一个函数，它返回系统日期和时间。
语法：GETDATE（）
DATEADD：DATEADD返回根据日期时间添加的日期，
语法：DATEADD（datepart,number,date）
变量：DATEPART是用于计算的日期单位。有一定的格式。
NUMBER是指添加至日期变量中的日期单位数。
DATE：格式化为日期的字符串，或日期时间型的列名、变量或参数。
例如：SELECT DATEADD（DAY，7，GETDATE（））
DATEDIFF：返回两个指定日期的日期单位差别。
语法：DATEDIFF（datepart,date,date）
例如：DATEDIFF（day,datevar,getdate()）
返回值为DATEVAR和当前日期相差的天数。
DATEPART：返回的整数表示日期中的日期单位。
语法：DATEPART（datepart,date）
例如：DATEPART（month,getdate()）
返回值为当前日期的月份数。
YEAR：显示所提供日期的年，
语法：YEAR（DATE）
MONTH：显示所提供日期的月，
语法：MONTH（DATE）
DAY：显示所提供日期的天，
语法：DAY（DATE）
CAST：CAST语句用来进行数据类型的转换。
语法：CAST（expression AS datatype）
例如：CAST（MYDATE AS VARCHAR）
把MYDATE 转换成可变字符类型。
CONVERT：用来进行资料类的转换。
语法：CONVERT（data_type,expression）
例如：SELECT ‘my birthday is today’+convert(varchar(12),getdate( ))
把日期转换成字符串类型，用加号与前边相连。
EXP：返回以E为底的N次幂。
语法：EXP（float_expression）
例如：EXP（0）返回值为1。
LOG：LOG函数返回浮点表达式的自然对数。
语法：LOG（float_expression）
LOG10：LOG函数返回浮点表达式的以10为底的对数值。
语法：LOG10（float_expression）
ROUND：ROUND返回给出的数学表达式的，并四舍五入至指定的精度。
语法：ROUND（numeric_expression,length[,funcion]）
变量：numeric_expression为数值型表达式
length是四舍五入的精度，小数点后边的数，当为负值时，数字表达式在小数点左边进行四舍五入。
Function值为0时表达式四舍五入，如果不为0，将数字表达式舍位。
SQUARE：求平方
语法：SQUARE（float_expression）
SQRT：求平方根
语法：SQRT（float_expression）
STR：SQRT可以将数值型转换为字符型资料，允许控制小数的格式。
语法：STR（float_expression[,length[,decimal]]）
变量：length：想要的字符串的总长度。缺省为10。
decimal：是小数点右边的位数。四舍五入。如果不写长度则为取整，小数后四
舍五入。
ISNULL：ISNULL可有非空资料代替NULL。
语法：ISNULL（expression,value）
例如：SELECT ISNULL(NULL,'ABC')
LEFT：LEFT函数返回字符表达式从左边开始的给定的字节数。如果该整数是一个负数， 就返回NULL。
语法：LEFT（character_expression,integer_expression）
变量：character_expression可以是变量、常量、或表中的列。
Integer_expression为要返回的字符数。
RIGHT：RIGHT函数返回字符表达式从右边开始的给定的字节数。如果该整数是一个负
数，就返回NULL。
语法：LEFT（character_expression,integer_expression）
变量：character_expression可以是变量、常量、或表中的列。
Integer_expression为要返回的字符数。
LEN：LEN函数提供了字符串表达式中的字符的长度。
语法：LEN（string-expression）
例如：LEN（‘ABCE’）值为4
LOWER：LOWER函数将大写字符转换为小写字符。
语法：LOWER（character_expression）
变量是字符表达式。
UPPER：UPPER函数将小写字符转换为大写字符。
语法：UPPER（character_expression）
变量是字符表达式。
REVERSE:REVERSE返回字符串表达式的倒序。
语法：REVERSE（character_expression）
例如：REVERSE（‘PAM’）结果为‘MAP’
LTRIM：LTRIM删除字符表的前导空格。
语法：LTRIM（character_expression）
变量是字符表达式。
RTRIM：RTRIM删除字符表的后继空格。
语法：RTRIM（character_expression）
变量是字符表达式。
NULLIF：如果一对表达式相等，NULLIF函数就返回NULL，如果它们不相等，NULLIF函数就返回第一个表达式的值。
语法：NULLIF（expression1,expression2）
变量：expression可以数值型，也可以是字符串。
例如：SELECT NULLIF（134，135）返回值为134。
PI：PI函数返回pi的值3。1415926
REPLACE：REPLACE用串表达式3取代串表达式1中的表达式2。
语法：REPLACE（‘string1’，‘string2’，‘string3’）
例如：SELECT REPLACE（‘mydog’，‘dog’，‘car’）
本例中，用CAR取代了MYDOG中的DOG，结果为MYCAT。
STUFF：函数可以将表达式的一部分用所提供的另一个字符串替代，替代部分用起始位置和长度来定义。
语法：STUFF（character_expression,start,length,character_expression）
LENGTH：是决定要替换的字符数。
例如：STUFF（‘MY DOG FIDO’，8，4，‘SALLY’）
SUBSTRING：返回字符型的一部分。
语法：SUBSTRING（expression,start,length）
例如：SUBSTRING（‘MICROSOFT’，6，4）
返回SOFT。
REPLICATE：REPLICATE将字符串表达式复制用户定义的次数。
语法：REPLICATE（character_expression,integer-expression）
例如：REPLCATE（‘2’，5）
-try catch使用
--打开try catch功能
set xact_abort on
begin try
begin tran
insert into Student(SID) values(-1)
commit tran
print 'commited'
end try
begin catch
rollback
print 'rolled back'
end catch
--获取当前月有多少天
select DATEPART
(day,
DATEADD(DAY,-1,
CAST(cast(YEAR(getdate()) as nvarchar)+'-'+cast(month(getdate()) as nvarchar)+'-01' as datetime)
)
)
--取到小数第二位四舍五入
Select Convert(Numeric(20,2), IsNull(50.01634,0))
--50.02
--一个月第一天
select DATEADD(mm,DateDiff(mm,0,getdate()),0)
--DateAdd当添加的值和初试值均为0时，返回的时间为1900-01-01 00:00:000
select DATEADD(mm,0,0);
--如何查询本日、本月、本年的记录SQL
--本年：
select * from Stduent where year(date)=year(getdate())
--本月：
select * from Stduent where year(date)=year(getDate()) And month(date)=month(getdate())
--本日：
select * from Stduent where year(date)=year(getDate()) And month(date)=month(getdate()) and Day(date)=Day(getDate())
--昨天的记录：
datediff(day,[Datetime],getdate())=1 把Datetime换为你的相应字段，getdate()-Datetime即为时间差。
--本月记录：
SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0
--本周记录：
SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0
--本日记录：
SELECT * FROM 表 WHERE datediff(day,[dateadd],getdate())=0
--本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
select DATEADD(wk,datediff(wk,0,getdate()),0);
--一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
select DATEADD(yy,DateDiff(yy,0,getdate()),0)
--季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
select DATEDIFF(qq,0,GETDATE())
select DATEDIFF(WK,0,GETDATE())
select DATEDIFF(MM,0,GETDATE())
select DATEDIFF(yy,0,getdate())
--当天的半夜
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
select DATEADD(dd,datediff(dd,0,getdate()),0)
select DATEADD(dd,datediff(dd,0,getdate()),0)
--去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
select DATEADD(ms,-3,dateadd(yy,datediff(yy,0,getdate()),0))
--本年的第一天
select DATEADD(YY,DATEDIFF(yy,0,getdate()),0)
--本年的最后一天
select dateadd(MS,-3,DATEADD(yy,datediff(yy,0,getdate()),0))
--本月的第一天
select DATEADD(mm,datediff(mm,0,GETDATE()),0);
--本月的最后一天
select DATEADD(ms,-3,DateAdd(mm,datediff(mm,0,getdate()),0))
--本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
--本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate()) ), 0)
--本周的星期一
select DATEADD(wk,datediff(wk,0,getdate()),0);
--显示星期几
select datename(weekday,getdate());
--显示本月的天数
select DATEDIFF(dd,getdate(),DATEADD(mm,1,getdate()));
--显示某年某月有多少天
create Function DaysInMonth1(@datetime datetime)
returns int
as
begin
return Day(datepart(dd,dateadd(ms,-3,dateadd(mm,datediff(mm,0,@datetime)+1,0))))
end
select dbo.DaysInMonth1('2012-2-13');
--显示本周的星期一
select DATEADD(WK,DATEDIFF(wk,0,getdate()),0)
--获取当前数据库中所有用户的表
select * from sysobjects where xtype='U'
--随机取出条数据
select top 3 * from Student order by newid()
select NEWID()
--查找表中多余的重复记录
select * from Student where SID in( select SID from Student group by SID having COUNT(Sname)>1)
create table Tb(TID int identity(1,1),TName nvarchar(10) unique)
delete from Tb
insert into Tb select 'Olive'
--重置自增ID
dbcc checkident(Tb,reseed,4)
insert into Tb select 'Olive1'
delete Tb where TName='Olive'
insert into Tb select 'Olive'
insert into Tb Select 'MoYao'
select * from Tb
--关键字waitfor 主要有waitfor delay(推迟执行)，waitfor time (特定时间执行)
waitfor delay '00:00:08'
print('Hello ,Im waitfrom delay')
waitfor time '20:49:50'
print('Hello,Im waitfor time')
--检测数据库是否存在
if( exists(select * from sys.databases where name='School'))
select * from Student
go
--检测数据表是否存在
if( exists(select * from sys.objects where name='Student'))
select * from Student order by SID desc
go
--检测数据列是否存在
if(exists(select * from sys.all_columns where object_id=object_id('Student') and name='Sname'))
select * from Student
go
--同时无条件的查询两个不相关的表，所得新表字段为两表字段之和，信息条数为两个信息条数的乘积 （即为：两表的笛卡尔积）
select T.*,Student.* from T ,Student
--交叉连接即为上边所讲的那种连接
select T.*,Student.* from T cross join Student
--随机数均可以产生0-1之间的随机数
select RAND();
select RAND(CHECKSUM(NEWID()))
--随机数0-9
select ABS(RAND())
select ABS(checksum(newid()))%10
--随机数a-b之间
select a+ABS(checksum(newid()))%(b-a+1)
select 1+ABS(checksum(newid()))%100
--SubString字符串截取
select SUBSTRING('Olive116',1,5)
--left从左边截取字符串
select LEFT('Olive',3)
--right从右边截取字符串
select RIGHT('Olive',4)
--字符串替换
select REPLACE('OOLive','o','1')
--反转排序
select REVERSE('Olive')
--删除指定的长度并在指定的起点处插入另一组字符
select STUFF('OliveOOO',6,3,'Hello')
--以指定的次数重复字符串的值
select replicate('Olive',3)
--返回表达式中第一次出现的位置
select PATINDEX('%Olive%','Hello!Olive')
--返回字符串中指定表达式的开始位置
select CHARINDEX('O','Hello,Olive')
--返回输入表达式的第一个字符的整数值
select UNICODE('o')
--返回由数字数据转换来的字符数据
select STR(123.1314,4),LEN(STR(123.3212,321))
select STR(123.1334,4,4)
--发音匹配度
select Sname,SOUNDEX(Sname) from Student
--difference()简化两个字符串发音相似度,返回0-4之间的值来反应两个字符的相似度，值越大就越相似
select Sname,SOUNDEX(Sname),DIFFERENCE(Sname,'Olive') from Student
--查询重复记录
select * from Student where SID in (select SID from Student group by SID having COUNT(SID)>0)
--删除重复的记录（并保留SID最小的记录）
delete from Student where
SID in (select SID from Student group by SID having COUNT(SID)>0)
and SID not in(select MIN(SID) from Student group by SID having count(SID)>0)
--双引号
select '''''' as result
--isnumeric（）判断是否是数字是为1否为0
select ISNUMERIC('a')
--isdate()判断是否为日期是为1否为0
select ISDATE('2012-3-2')
四、存储过程、函数、触发器、事务、游标
（1）存储过程
--创建存储过程（无参）
create procedure ShowTestA
as
select * from TestA
--修改存储过程（有参，且为输出参数)
alter procedure ShowTestA
@LastName varchar(10) = null output
as
if @LastName is null
begin
print 'null'
print '123'
end
else
begin
print @LastName
print '456'
end
exec ShowTestA
exec ShowTestA 'Olive'
（2）自定义函数（关键点：函数和存储过程 区别主要在于函数必须声明返回值类型）
--用户自定义函数（返回时间）
create function DayOnly(@date datetime)
returns varchar(12)
as
begin
return convert(varchar(12),@date,101)
end
select dbo.DayOnly(getdate())
--用户自定义函数(返回表)
drop function TestATable
create function TestATable(@Name nvarchar(10))
returns table
as
return select * from TestA where [email protected]
select * from TestA
select * from TestATable('12')
--用户自定义函数输入参数、返回表
create function ResultTable(@Name nvarchar(10))
returns @Result table (Rid varchar(10),Rpwd varchar(10))
as
begin
insert into @Result select * from TestA where [email protected]
return
end
select * from ResultTable('12')
（3）触发器
--创建触发器
create trigger UpdateStudent
on Student
for insert
as
update Student set Sname='O(∩_∩)O哈哈~' where SID=( select Inserted.SID from Inserted)
insert into Student select 'MM','1988-11-11','男'
select * from Student order by SID Desc
.禁用触发器
alter table Sales.SalesOrderDetail
disabletrigger all --这里的all可以是具体的触发器名
.删除触发器
drop trigger orderDetailNotDiscontinued
.修改触发器
alter trigger tr_title
on mytable
for insert,update
as
if update(title)
begin
print('title1')
end
--触发器的相关函数Update()
create trigger UpdateTest
on Student
for insert,update
as
if update(SName)----如果update（）中的字段有更新的话就为true否则为false
select Inserted.Sname from Inserted
insert into Student select 'OLIVEVEVE','1989-12-22','女'
--触发器(插入新的数据，并且在插入后更新刚插入的输入的姓名)
create trigger InsertTwo
on Student
after insert
as
update Student set Sname='OOOO' where SID=( select inserted.SID from Inserted)
insert into Student select 'MoYao','1989-11-15','男'
select * from Student
其中inserted表是一个临时表
存储的是将要插入的信息
这个触发器的目的是检查将要插入的信息是否符合规定
(在product表里没有特殊的记录)
这个例子是check约束所不能解决的了的
use AdventureWorks
go
create trigger orderDetailNotDiscontinued
on Sales.SalesOrderDetail
afterinsert,update
as
if exists
(
select 'true' from Inserted i
join Production.Product p
on i.ProductID= p.ProductID
where p.DiscontinuedDateis not null
)
begin
print('error appear.')
rollback tran
end
（4）事务
sql事务是使用SQL server自身的事务：在存储过程中直接使用Begin Tran，Rollback Tran，Commit Tran实现事务：
优点：执行效率最佳
限制：事务上下文仅在数据库中调用，难以实现复杂的业务逻辑。
begin try
begin tran
insert into Student(SID) values(-1)
commit tran
print 'commited'
end try
begin catch
rollback
print 'rolled back'
end catch
TransactionScope事务
TransactionScope事务类，它可以使代码块成为事务性代码。并自动提升为分布式事务
优点：实现简单，同时能够自动提升为分布式事务
那么如何解决数据库并发问题呢？
解决此问题的办法就是为数据库加锁，以防止多个组件读取数据，通过锁住事务所用的数据，能保证开锁之前，只有本事务才能访问数据库。这样就避免了交叉存取的问题
（5）游标
--游标：数据集合里的上下移动的指针
drop cursor StudentCursor
--声明变量
declare @SID int
declare @Sname nvarchar(30)
declare @Sage nvarchar(30)
declare @Ssex nvarchar(5)
--声明游标
declare StudentCursor cursor for
select * from Student
--打开游标
open StudentCursor
--将游标移向下一行，将获取的数据放入之前声明的变量中
fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex
--判断是否获取数据成功，@@fetch_status为系统全局变量，0-表示成功，1-表示取不到数据，2-超出了最后一条数据
while @@fetch_status=0
begin
print(Cast(@SID as varchar)+':'+@Sage+':'+@Ssex)
--提取下一条记录
fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex
end
close StudentCursor
deallocate StudentCursor
--存储过程中使用游标（游标的作用域）
create procedure TestStudentCursor
as
--声明变量填充游标检索到的数据
declare @SID int
declare @Sname nvarchar(30)
declare @Sage nvarchar(30)
declare @Ssex nvarchar(5)
--声明存储过程内部的全局游标
declare StudentCursor cursor global for
select * from Student
--打开游标
open StudentCursor
--读取下一条数据，并将数据填充到之前声明的变量中
fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex
--判断是否取到数据，且取到的数据是否符合要求
while(@SID<5) and (@@fetch_status=0)
begin
--打印数据
print(cast(@SID as varchar)+'::'+@Sname+'::'+@Sage)
--读取下一条数据
fetch next from StudentCursor into @SID,@Sname,@Sage,@Ssex
end
--关闭游标
close StudentCursor
--释放游标
deallocate StudentCursor
--执行存储过程
exec TestStudentCursor
五、实用功能
（1）分页
分页的几种写法（性能效果最好的是top/max 和Row_number()两种方法）
--写法1，not in/top
select top 50 * from pagetest
where id not in (select top 9900 id from pagetest order by id)
order by id
--写法2，not exists
select top 50 * from pagetest
where not exists
(select 1 from (select top 9900 id from pagetest order by id)a where a.id=pagetest.id)
order by id
--写法3，max/top
select top 50 * from pagetest
where id>(select max(id) from (select top 9900 id from pagetest order by id)a)
order by id
--写法4，row_number()
select top 50 * from
(select row_number()over(order by id)rownumber,* from pagetest)a
where rownumber>9900
select * from
(select row_number()over(order by id)rownumber,* from pagetest)a
where rownumber>9900 and rownumber<9951
select * from
(select row_number()over(order by id)rownumber,* from pagetest)a
where rownumber between 9901 and 9950
--分页函数（先循环插入100000000条数据然后再分页）
create table #FYTable (id int identity(1,1),title nvarchar(20),con nvarchar(20))
declare @title nvarchar(300),
@con nvarchar(300)
declare @start int,@end int
set @start=0
set @end=1000000
set @title='Olive'
set @con='I love'
while @start<@end
begin
insert into #FYTable select @title,@con
set @[email protected]+1
end
select * from #FYTable
create procedure dbo.FenYe
@count int,@yeshu int
as
select top(@yeshu) * from #FYTable where id not in (select top(@[email protected]) id from #FYTable)
(2)排序
--排序函数
select SName,Sage,SSex from(
select SName,Sage,Ssex ,Rank() over(order by sage) as rank from Student
) as a where a.rank>1 and a.rank<9;
（3）表行列转换
--数据表行列转换（case when then）
create table Factory( FID int,FDepartment nvarchar(20),FMeterial nvarchar(20), FNumber int)
insert into Factory values(1,'工厂1','材料1',1);
insert into Factory values(1,'工厂2','材料1',1);
insert into Factory values(1,'工厂2','材料2',2);
insert into Factory values(1,'工厂2','材料3',1);
insert into Factory values(1,'工厂3','材料3',1);
insert into Factory values(1,'工厂2','材料1',2);
insert into Factory values(1,'工厂3','材料2',1);
select * from Factory
select FDepartment,
SUM(case FMeterial when '材料1' then FNumber else 0 end) as 材料1,
SUM(case FMeterial when '材料2' then FNumber else 0 end) as 材料2,
SUM(case FMeterial when '材料3' then FNumber else 0 end) as 材料3
from Factory group by FDepartment
--行列转换函数Pivot
create table Sell
(
[Year] int,[Quarter] nvarchar(2),Quantity int
)
insert into Sell values(2011,'Q1',12)
insert into Sell values(2011,'Q2',13)
insert into Sell values(2011,'Q2',14)
insert into Sell values(2011,'Q3',15)
insert into Sell values(2011,'Q2',12)
insert into Sell values(2011,'Q3',13)
insert into Sell values(2011,'Q4',15)
insert into Sell values(2011,'Q4',17)
insert into Sell values(2011,'Q4',11)
insert into Sell values(2012,'Q3',13)
insert into Sell values(2012,'Q4',15)
insert into Sell values(2012,'Q2',17)
insert into Sell values(2012,'Q4',11)
insert into Sell values(2012,'Q3',13)
insert into Sell values(2012,'Q1',15)
insert into Sell values(2012,'Q1',17)
insert into Sell values(2012,'Q4',11)
select * from Sell
select * from Sell Pivot(sum(Quantity) for [Quarter] in (Q1,Q2,Q3,Q4) )as p
select [Year], SUM(case when [Quarter]='Q1' then Quantity end) as Q1,
SUM(case when [Quarter]='Q2' then Quantity end) as Q2,
SUM(case when [Quarter]='Q3' then Quantity end) as Q3,
SUM(case when [Quarter]='Q4' then Quantity end) as Q4
from Sell group by [Year]
--case when then 用例
drop table T
go
create table T (TID int identity(1,1) not null primary key,Quantity int not null,ProductID int not null,[Type] int not null)
go
insert into T select 2,12,1
insert into T select 6,12,2
insert into T select 3,10,2
insert into T select 8,10,1
insert into T select 2,12,1
select * from T
go
--统计每种商品有多少件
select ProductID,SUM(case when [Type]=1 then Quantity else -Quantity end) from T group by ProductID
--使用case when 为表的新列填充不同的数据
alter table T add Descriptions nvarchar(20) null
go
update T set Descriptions= case when [Type]=1 then '现存'
when [Type]=2 then '售出'
else '未知'
end
go
--怎么把这样一个表
--year month amount
--1991 1 1.1
--1991 2 1.2
--1991 3 1.3
--1991 4 1.4
--1992 1 2.1
--1992 2 2.2
--1992 3 2.3
--1992 4 2.4
--查成这样一个结果
--year m1 m2 m3 m4
--1991 1.1 1.2 1.3 1.4
--1992 2.1 2.2 2.3 2.4
create table #DateTest([year] int,[month] int,amount float)
drop table #DateTest
insert into #DateTest
select 1991,1,1.1
union select 1991,2,1.2
union select 1991,3,1.3
union select 1991,4,1.4
union select 1992,1,1.1
union select 1992,2,1.2
union select 1992,3,1.3
union select 1992,4,1.4
select * from #DateTest
--根据月份的不同分为不同的字段，然后再按照年份分组
select [year],sum(case [MONTH] when 1 then amount else 0 end) as m1,sum(case [MONTH] when 2 then amount else 0 end) as m2,sum(case [MONTH] when 3 then amount else 0 end) as m3,sum(case [MONTH] when 4 then amount else 0 end) as m4 from #DateTest group by [year]
--有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value。这道题的SQL语句怎么写？
create table #A(keys int,value varchar(20))
create table #B(keys int,value varchar(20))
insert into #A values(1,'aa'),(2,'ab'),(3,'ac')
insert into #B values(1,'aa'),(2,'a'),(3,'a')
--先将更新#B中value的值更新为#A中value的值，同时限定#B中的keys=#A.Keys 同时#B.value<>#A.value
update #B set value=(select a.value from #A a where a.keys=#B.keys) where #B.keys in(select #B.keys from #B,#A where #A.keys=#B.keys and #A.value<>#B.value)
select * from #B
select * from #A
delete from #A
delete from #B
--先删除#A，#B一样的部分，然后再根据两表中Keys 的相等进行更新
update #B set #B.value=s.value from (select * from #A except select * from #B) as s where #B.keys=s.Keys
--原表:
--courseid coursename score
--1 java 70
--2 oracle 90
--3 xml 40
--4 jsp 30
--5 servlet 80
--为了便于阅读,查询此表后的结果显式如下(及格分数为60):
--courseid coursename score mark
--1 java 70 pass
--2 oracle 90 pass
--3 xml 40 fail
--4 jsp 30 fail
--5 servlet 80 pass
create table #B1(id int identity(1,1),coursename varchar(30),score int )
insert into #B1 values('java',70),('oracle',90),('xml',40),('jsp',30),('servlet',80)
select * from #B1
--case when 的使用
select id,coursename,score, case when score > 60 then 'pass'
else 'fail'
end as mark from #B1
--原表:
--id proid proname
--1 1 M
--1 2 F
--2 1 N
--2 2 G
--3 1 B
--3 2 A
--查询后的表:
--id pro1 pro2
--1 M F
--2 N G
--3 B A
create table C(id int,proid int,proname varchar(2))
insert into C values(1,1,'M'),(1,2,'F'),(2,1,'N'),(2,2,'G'),(3,1,'B'),(3,2,'A')
select * from C
select id,(select proname from C c where proid=1 and cc.id=c.id ) as pro1,(select proname from C c2 where proid=2 and c2.id=cc.id ) as pro2 from C cc group by id
--如下
--表a
--列 a1 a2
--记录 1 a
-- 1 b
-- 2 x
-- 2 y
-- 2 z
--用select能选成以下结果吗？
--1 ab
--2 xyz
create table #D (a1 varchar(2),a2 varchar(2))
insert into #D values ('1','a'),('1','b'),('2','x'),('2','y'),('2','z')
create table #D1 (id varchar(2),value varchar(2))
alter table #D1 alter column value varchar(20)
insert into #D1 select distinct(a1),'' from #D
declare @id varchar(2),@value varchar(20)
declare LianJie cursor for select * from #D
open LianJie
fetch next from LianJie into @id,@value
while @@FETCH_STATUS=0
begin
update #D1 set value=value+@value where[email protected]
fetch next from LianJie into @id,@value
end
close LianJie
deallocate LianJie
select * from #D
select * from #D1
--3. 表内容：
--2005-05-09 胜
--2005-05-09 胜
--2005-05-09 负
--2005-05-09 负
--2005-05-10 胜
--2005-05-10 负
--2005-05-10 负
--如果要生成下列结果, 该如何写sql语句?
-- 胜负
--2005-05-09 2 2
--2005-05-10 1 2
create table #E (dt varchar(20),shengfu nvarchar(20))
insert into #E values('2012-9-15','胜'),('2012-9-15','胜'),('2012-9-15','负'),('2012-9-15','负'),('2012-9-16','胜'),('2012-9-16','胜'),('2012-9-16','负')
select * from #E
select dt, SUM(case when shengfu='胜' then 1 else 0 end) as 胜,SUM(case when shengfu='负' then 1 else 0 end) as 负 from #E group by dt
--表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列
create table #E1 (A int ,B int,C int)
insert into #E1 values(1,3,5),(6,4,2),(8,7,9)
select * from #E1
select case when A>B then A else B end as AB,case when B>C then B else C end as BC from #E1
table1
月份 部门业绩
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门 部门名称
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 （result）
部门部门名称 一月份 二月份 三月份
01 国内业务一部 10 null null
02 国内业务二部 10 8 null
03 国内业务三部 null 5 8
04 国际业务部 null null 9
create table #F([month] nvarchar(20), bumen nvarchar(20), yeji int)
insert into #F values('一月份','01',10),('一月份','02',10),('一月份','03',5),('二月份','02',8),('二月份','04',9),('三月份','03',8)
create table #F1(bumen nvarchar(20),bumenmingcheng nvarchar(20))
insert into #F1 values('01','国内业务一部'),('02','国内业务二部'),('03','国内业务三部'),('04','国际业务部')
select * from #F1
select f.bumen ,f1.bumenmingcheng,sum(case when f.[month]='一月份' then f.yeji else null end) as 一月份,
sum(case when f.[month]='二月份' then f.yeji else null end) as 二月份,
sum(case when f.[month]='三月份' then f.yeji else null end) as 三月份 from #F f,#F1 f1 where f.bumen=f1.bumen group by f.bumen,f1.bumenmingcheng
drop table #Str
create table #Str(id int identity(1,1),str11 varchar(20), [type] int )
insert into #Str values('how',1),('are',1),('you',1),('fine',2),('thank',2),('you',2)
select * from #Str
create table #Str1(ss nvarchar(100),id int )
insert into #Str1 select distinct '',([type]) from #Str
select * from #Str1
declare @id1 int,@str1 nvarchar(100)
declare StrCursor cursor for select str11,[type] from #Str
open StrCursor
fetch next from StrCursor into @str1,@id1
while @@FETCH_STATUS=0
begin
update #Str1 set ss=ss+@str1 where[email protected]
fetch next from StrCursor into @str1,@id1
end
close StrCursor
deallocate StrCursor
select * from #Str1
--Post
create Table Post(id int identity(1,1) primary key,title nvarchar(200),content text,CreateDate nvarchar(30))
--Comments
create Table Comments(Cid int identity(1,1) primary key,id int,content text,CreateDate nvarchar(30))
insert into Post select '我从远方赶来','我为她而狂野，我为你来看我不顾一切',cast(GETDATE() as nvarchar)
insert into Comments values
(1,'不虚此行呀',CAST(getdate() as nvarchar)),
(1,'这一个不能停留太久的世界',cast(getdate() as nvarchar)),
(1,'无可取代',cast(getdate() as nvarchar))
select * from Comments
create table #Ts (id int,com nvarchar(max))
insert into #Ts select distinct(id),'' from Comments
declare @ii int,@com nvarchar(max)
declare TsCursor cursor for select id ,content from Comments
open TsCursor
fetch next from TsCursor into @ii,@com
while @@FETCH_STATUS=0
begin
update #Ts set com=com+@com where[email protected]
fetch next from TsCursor into @ii,@com
end
close TsCursor
deallocate TsCursor