SqlServer （一）

 /*
--创建数据库
create database SQLStudy
--使用数据库
use sqlstudy
--创建表
create table tb_user
(
 UserID int not null primary key,
 UserName varchar(14),
 PassWord varchar(14)
)
--插入100条记录
declare @var_number int
declare @var_sunnumber int
set @var_number=0
set @var_sunnumber=0
while @var_number<100
begin
set @var_number=@var_number+1
set @var_sunnumber=@var_sunnumber+@var_number
insert into tb_user values(@var_number,'PMQ','PMQ')
end
select @var_sunnumber
GO
select * from tb_user
--创建临时表
declare @var_table table
(
 ProductNumber int,
 AuthorName varchar(14)
)
--把tb_user表的数据插入临时表
insert into @var_table select UserID,UserName from tb_user
--查询临时表
select * from @var_table
--查询常量
select 'L',UserID from tb_user
GO
--字符操作
declare @var_position int,@var_str char(15)
set @var_position=1
set @var_str='SQL SERVER'
while @var_position<=datalength(@var_str)
begin
select ascii(substring(@var_str,@var_position,1)),
char(ascii(substring(@var_str,@var_position,1)))
select @var_position=@var_position+1
end
GO
--操作数字5代表长度，1代表小数位数
select str(123.45,7,3)
--操作字符1代表开始位置，5代表长度
select subString('abcdefghijk',1,5)
--将数字转化为nchar类型
select nchar(234)
--char(),ascii把字符转化为ascii码，或者把ascii码转化为字符
--为M字符在特定列userName的开始位置
select patindex('%M%',userName) from tb_user where userID=50
GO
--as的使用
select UserID as ddd,userName from tb_user
select UserID as 'ddd',userName from tb_user
--游标的使用
create table historyTable
(
User_ID   int,
User_Name varchar(14)
)
GO
declare @var_userID int
declare @var_userName varchar(14)
declare var_userCursor cursor for
select userID,userName from tb_user
open var_userCursor
fetch next from var_userCursor into @var_userID,@var_userName
while(@@fetch_status=0)
begin
insert into historyTable values(@var_userID,@var_userName)
fetch next from var_userCursor into @var_userID,@var_userName
end
close var_userCursor
deallocate var_userCursor
select * from historytable
--给表添加列
alter table tb_user
add userLevel int
GO
--修改列
alter table tb_user
alter column  userName varchar(20)
多表连接分组聚合函数
select  avg(a.UserID+b.User_ID),a.userName,b.User_ID from (select UserID, UserName from tb_user where UserID in (5,6,7,8)) a left join historytable b on a.userID=b.user_ID group by a.userName,a.UserID,b.User_ID
--复习游标用法
declare @var_name varchar(14)
declare var_cursor cursor for
select UserName from tb_user where userid=59
open var_cursor
fetch next from var_cursor into @var_name
while(@@fetch_status=0)
begin
 select @var_name
fetch next from var_cursor into @var_name
end
close var_cursor
deallocate var_cursor
--if,else的使用
declare @var_name varchar(20)
declare @var_number int
declare var_cursor cursor for
select userID from tb_user
open var_cursor
fetch next from var_cursor into @var_number
while(@@fetch_status=0)
begin
if(@var_number<=10)
  set @var_name='小于10'
else
  if(@var_number<=20)
   set @var_name='小于20'
  else
    set @var_name='依次类推'
print @var_name
fetch next from var_cursor into @var_number
end
close var_cursor
deallocate var_cursor
--case,when的使用
select UserID,UserName,用户 = case
when(userID<=20) then '前二十名'
when (userID<=50) then '前五十名'
else '后面名次'
end from tb_user
select userID,username,用户 = case userID
when 20 then '前二十名'
when 30 then '前三十名'
else '没有名次'
end from tb_user
--使用存储过程
create procedure pro_tb_user
   @username varchar(14)
as
select * from tb_user where username=@username
GO
--调用存储过程
execute pro_tb_user 'pmq'
--传入动态SQL语句
create procedure pro_tb_usersqd
@username varchar(14)
as
--声明局部变量
declare @sql varchar(50)
set @sql='select * from tb_user where username='''+@username+''''
exec (@sql)
GO
--调用存储过程
execute pro_tb_usersqd 'pmq'
--带输出参数的存储过程
create procedure pro_tb_user_output
@username varchar(14),
@userid   int output
as
  select @userid = count(userID)  from tb_user where username= @username
GO
--调用带输出参数的存储过程
declare @var_userid int
execute pro_tb_user_output 'PMQ',@var_userid output
select @var_userid--声明带输出游标的存储过程
create procedure pro_tb_cursor_test
@var_cursor cursor varying output
as
set @var_cursor=cursor forward_only static for
select  * from tb_user
open  @var_cursor
GO
declare @varjubcursor cursor
exec  pro_tb_cursor_test @varjubcursor output
while (@@fetch_status=0)
begin  
fetch next from @varjubcursor
end
close @varjubcursor
deallocate @varjubcursor
GO
--用户自定义函数
create function  ResultDemo
(
@varNumber1 int,
@varNumber2 int,
@varNumber3 int
)
returns int
as
begin
 declare @Res int
 set @Res=(@varNumber1+@varNumber2)*@varNumber3
 return @Res
end
GO
--调用用户自定义函数
declare @var_select int
set @var_select= dbo.ResultDemo(3,4,5)
select @var_select
--再练习一下
create function ResultString
(
  @varstrString varchar(50)
)
 returns varchar(50)
as
begin
return '输出的是:'+@varstrString
end
GO
--调用
declare @varstrString varchar(50)
set @varstrString=dbo.ResultString('PMQ是英雄')
select @varstrString
--function返回table
create function ReturnTable
(
@username varchar(14)
)
returns @userTable table
(
userID int,
userName varchar(14)
)
as
begin
  insert into @userTable select UserID,UserName from tb_user where UserName=@username
  return
end
GO
select * from dbo.ReturnTable('PMQ')*/
use Sqlstudy