sql基础复习

--1、while循环
declare @sum int
declare @i int
set @i=1
set @sum=0
while(@i<101)
begin
 set @sum =@sum+@i
 set @i=@i+1
   if(@i>90)
   print @i
end
print @sum

--2、goto语句
declare @num int
set @num=100
flag:
print @num
select @num=@num+1
while(@num<106)goto flag
print '------------------'
print @num

--3、表变量

declare @mytable table(nam nvarchar(50),num nvarchar(50))

insert into @mytable select ClassName,ClassNum  from dbo.tb_Class

select * from @mytable

--4、局部临时表，全局临时表

--局部临时表
create table #temp_tb(
 id int,
 pwd nvarchar(50)
)

--全局临时表
create table ##temp_tb(
 id int,
 pwd nvarchar(50)
)

--5、批量导入数据并创建临时表
select ClassName,ClassNum into #temp from dbo.tb_Class
select * from #temp

--6、区间查询 between and （not between and）

select * from tb_class where classnum between 1200 and 1500

select * from tb_class where classnum not between 1200 and 1500

--7、in变量,is null is not null,去重复distinct

--8、随机查询、子查询

select top 1 * from tb_class order by newid()

--9、行号

select '行号'=identity(int,1,1),classname,classnum into #temp from tb_class
select * from #temp

--模糊查询 like not like "% _ []"通配符

select * from tb_class where className like '高%[^三]'

select * from tb_class where des like '[a-z]%'

select * from tb_class where ClassNum like '[^1]%'

select * from tb_class where des like '%100/%%' escape '/'

select * from tb_class where des+ClassName like '%[二在]%'


--case 语句、类型转换 cast显示转换
select *,班级=
case 
when ClassNum>1500 then '大班'
when ClassNum=1500 then '中班'
when ClassNum<1500 then cast(ClassNum as nvarchar(20))
end
from tb_class


--类型转换 1,cast 2,Convert
select convert(nvarchar(20),getdate(),111)as 时间格式

--去除空格 rtrim ltrim

select ltrim('       中国    人         ')as title

--截取字符串substring

select substring('中华人民共和国',3,2) as title

--字符插入sutff

select stuff('中华共和国',3,0,'人民') as title

--计算字符长度 len
select len('中华人民共和国')

--字符串大小写lower upper
--字符串替换replace
select replace('中*华*人*民*共*和*国','*','-')

--获取字符所在位置

select substring('010-99998888',0,charindex('-','010-99998888'))

--日期函数
select year(getdate())
select month(getdate())
select day(getdate())
select datepart(year,getdate())
select datepart(hh,getdate())

select datename(dw,getdate()) as 今天星期几

select datename(ww,getdate()) as 本周是一年的第几周

select datename(yy,getdate()) as 年份

select datename(dy,getdate()) as 今天是一年中的第几天

select datediff(d,'2012-11-27','2012-11-30') as 相差天数
select datediff(hh,'2012-11-27','2012-11-30') as 相差小时


select getdate(),dateadd(d,3,getdate()) as 增加三天
select getdate(),dateadd(hh,3,dateadd(d,3,getdate())) as 增加三天在增加三小时

--排序order by  笔画排序：Chinese_prc_stroke_cs_as_ks_ws,音序排序：chinese_prc_cs_as

select * from tb_class order by des collate Chinese_prc_stroke_cs_as_ks_ws

select * from tb_class order by des collate Chinese_prc_cs_as

--动态排序

declare @myorder int
set @myorder=2
select * from tb_class order by case @myorder
when 1 then classnum
when 2 then id
end
desc

--聚合函数,where（作用单行） 和 having（作用多行）区别

select count(distinct classname) from tb_class

--游标  sql语句面向一个集合操作，游标面向逐条记录的操作
declare cur_s cursor 
for select * from tb_class
for read only --只读游标
for update of classname--更新游标

declare @cur_ss cursor--游标变量

open cur_s

fetch next from cur_s

while @@FETCH_STATUS=0
begin
fetch next from cur_s
end

close cur_s
--释放游标
deallocate cur_s

--存储过程是一组为了完成特定功能的SQL语句集合,创建Create、修改Alter、删除Drop
create procedure GetClassInfo
(
@id int,
@ClassName nvarchar(50),
@Sum int output--存储过程返回值一种情况
)
as
begin 
select @Sum=sum(ClassNum)from tb_Class where id>@id and ClassName=@ClassName
declare @AllSum int
select @AllSum=sum(ClassNum)from tb_Class
return @AllSum--存储过程返回值另一种情况
end
go
declare @sum int
declare @AllSum int
EXEC @AllSum=GetClassInfo 1,'高三',@sum output
select @sum as 总数,@AllSum as 全部总数

--重命名存储过程
exec sp_rename 'GetClassInfo','ReNameGetInfo'
--监视存储过程
exec sp_monitor
--返回参数含义
--   *last_run: 上次运行时间
--
--      *current_run：本次运行的时间
--
--      *seconds: 自动执行存储过程后所经过的时间
--
--      *cpu_busy:计算机CPU处理该存储过程所使用的时间
--
--      *io_busy:在输入和输出操作上花费的时间
--
--       *idle：SQL Server已经空闲的时间
--
--       *packets_received：SQL Server读取的输入数据包数
--
--       *packets_sent：SQL Server写入的输出数据包数
--
--        *packets_error:SQL Server在写入和读取数据包时遇到的错误数
--
--        *total_read: SQL Server读取的次数
--
--         *total_write: SQLServer写入的次数
--
--         *total_errors: SQL Server在写入和读取时遇到的错误数
--
--          *connections:登录或尝试登录SQL Server的次数

 


--自动执行存储过程
--sp_procoption [@procName=] 'procedure', [@optionName=] 'option', [@optionValue=] 'value'
--         [@procName=] 'procedure'： 即自动执行的存储过程
--
--         [@optionName=] 'option'：其值是startup，即自动执行存储过程
--
--         [@optionValue=] 'value'：表示自动执行是开(true)或是关(false)
exec sp_procoption @procName='masterproc', @optionName='startup', @optionValue='true' 

--自定义函数
--标量函数
create function myfunAdd(@a int,@b int)
returns int
as
begin
declare @c int
set @c=@a+@b
return @c
end
go
declare @a1 int,
        @b1 int,
        @c1 int
set @a1=8
set @b1=7
set @c1=School.dbo.myfunAdd(@a1,@b1)
print @c1

--表值函数
create function GetTable()
returns table
as
return (select * from tb_class)

select * from School.dbo.GetTable()

--触发器，特殊的存储过程,嵌套触发器、递归触发器

create trigger mytrigger
on tb_Class
for update,delete,insert--三种触发器
as
update tb_student set age=age+1 where id =1

update tb_Class set des='???' where id =1
delete from tb_Class where id=6


--事务
begin try
begin transaction
insert into tb_class values('特色班',100,'描述')
insert into tb_student([name],sex,age,classid) values('小刘',1,22,5)
commit transaction
end try
begin catch
rollback
end catch

--保存事务
begin transaction
insert into tb_class values('特色二班',100,'描述')
insert into tb_student([name],sex,age,classid) values('小二',1,22,5)
save transaction save1
insert into tb_class values('特色三班',100,'描述')
insert into tb_student([name],sex,age,classid) values('小叁',1,22,5)
rollback transaction save1
commit transaction

--事务并发控制
begin tran
select * from dbo.tb_Class with(holdlock)
waitfor delay '00:00:10'
commit tran


update tb_Class set ClassNum=200 where id=12
select * from tb_Class

--视图

--创建约束，主键约束、外键约束、唯一约束、check约束、default约束

--创建表设置字段
create table ss
(
 id int identity(1,1) not null constraint pk_id primary key

)

--修改表设置字段
alter table dbo.tb_Class
add constraint pk_id primary key(id)
-- drop constraint pk_id --删除主键
--add constraint un_ss unique(id)--唯一约束
add constraint ch_sex check (like '[0-1]')
add constraint de_sex default 1 for sex

alter table dbo.tb_student
add constraint fk_classid
foreign key(classid)
references tb_Class (id)