SQL基本操作（二）：T-SQL语言

--用户自定义数据类型
--添加自定义数据类型
use xscj
--调用系统存储过程
exec sp_addtype 'student_num','char(6)','not null' 
--将当前的T-SQL批处理语句发送给SQL Server
go
--删除自定义数据类型
use xscj
exec sp_droptype 'student_num' 
go                                               
--创建局部变量 @var1,@var2并赋值,然后输出变量
--定义变量（变量名及类型）
declare @var1 varchar(20),@var2 varchar(25)
--给变量赋值(一个set语句只能给一个变量赋值 而  select 可以初始化多个变量)
set @var1='中国'
set @var2=@var1+'是一个伟大的国家'
select @var1,@var2
go
--创建一个名为sex的局部变量,并在select语句中使用该局部变量查找表xs中的所有女同学的学号，姓名
use xscj
declare @sex bit
set @sex=0
select 学号,姓名 from xs where 性别=@sex
go
--使用查询给变量赋值
use xscj
declare @student char(8)
set @student=(select 姓名 from xs where 学号='001102')
select @student
go
--使用游标变量
use xscj
--定义游标变量
declare @cursorVar cursor 
--给游标变量赋值
set @cursorVar=cursor scroll dynamic
for select 学号,姓名 from xs where 姓名 like '王%'
--打开游标
open @cursorVar
--对游标的操作
fetch next from @cursorVar
while @@fetch_status=0
begin
fetch next from @cursorVar
end
--关闭游标
close @cursorVar
--删除游标
deallocate @cursorVar
--算术运算符
use xscj
set nocount on
declare @startdate datetime
set @startdate=getdate()
select  学号,姓名,@startdate-出生日期 as 年龄 from xs
--位运算符
use xscj
create table bittop
(
a int not null,
b int  not null
)
insert bittop values(1,7)
go
select a&b,a|b,a^b from bittop
go
drop table bittop
--比较运算
use xscj
declare @student student_num
set @student='001111'
if(@student<>0)
select *from xs where 学号=@student
--查询成绩高于王林最高成绩的学生姓名 课程名及成绩
use xscj
select 姓名,课程名,成绩 
from xs,xs_kc,kc
where 成绩>all
           ( select b.成绩
from xs a,xs_kc b,kc c 
where a.学号=b.学号 and b.课程号=c.课程号 and a.姓名='王林'
            )and 姓名<>'王林'
go
--查询成绩高于王林最低成绩的学生姓名,课程名，成绩
use xscj
select 姓名,课程名,成绩
from xs,xs_kc,kc
where 成绩>any
            (
select b.成绩
from xs a,xs_kc b,kc c
where a.学号=b.学号 and b.课程号=c.课程号 and a.姓名='王林'
             )and 姓名!='王林'  
go   


--逻辑运算符
--查询总学分在40~50分的学生学号和姓名
use xscj
select 学号,姓名,总学分
from xs
where 总学分 between 40 and 50
go
--查询课程名以‘计 ‘算’‘软’或开头的情况
use xscj
select *
from kc 
where 课程名 like '[计软算]%'
--存储过程中，学生学号为入口参数，然后使用模式匹配查找某个学生选的所有的课程
use xscj
create procedure find_course1 @num char(20)
as
select @num=rtrim(@num)+'%'
select a.姓名,t.课程名
from xs a,xs_kc b,kc t
where a.学号=b.学号 and b.课程号=t.课程号 and a.学号 like @num
--查询所有选课学生的姓名
use xscj
select distinct 姓名
from xs
where exists
                  (
select * from xs_kc where xs.学号=xs_kc.学号
                   )
go
--使用IN子句实现上述子查询
select distinct 姓名
from xs
where xs.学号 in
              (
select xs_kc.学号 from xs_kc
              )

--字符串连接运算符
use xscj
select(学号+space(1)+','+space(1)+姓名) as 学号及姓名
from xs
where substring(学号,5,2)='13'
--流程控制语句
--如下程序用于控制总学分>40分的学生人数
declare @num int
select @num=(select count(姓名) from xs where 总学分>40)
if @num>0
select @num as '总学分>90的人数'
--如果“计算机”的平均成绩高于90分，则显示"平均成绩高于75分"
use xscj
declare @text1 char(20)
set @text1='平均成绩高于90.'
if(select avg(成绩)
from xs,xs_kc,kc
where xs.学号=xs_kc.学号 and xs_kc.课程号=kc.课程号 and kc.课程名='计算机基础'
   )<90
select @text1='平均成绩低于90.'
else
select @text1 
go 
--IF...ELSE语句的嵌套使用
use xscj
if(select avg(成绩)
from xs,xs_kc,kc
where xs.学号=xs_kc.学号 and xs_kc.课程号=kc.课程号 and kc.课程名='计算机基础'
   )<85
select '平均成绩低于85'
else
if(select avg(成绩)
from xs,xs_kc,kc
where xs.学号=xs_kc.学号 and xs_kc.课程号=kc.课程号 and kc.课程名='计算机基础'
      )>85
select '平均成绩高于85'
go
--显示字符串"China"中每个字符的ASCII值和字符
declare @position int,@string char(8)
set @position=1
set @string='China'
while @position<=datalength(@string)
begin
select ASCII(substring(@string,@position,1)),char(ASCII(substring(@string,@position,1)))
set @position=@position+1
end
go
--检查学生的平均成绩,若>75 将返回状态代码1，否则 将返回状态代码2
use xscj
create procedure checkavg @param varchar(10)
as
if(select avg(成绩)
from xs_kc
where xs_kc.学号=@param
group by 学号
   )>75
return 1
else 
return 2
go  
--waitfor语句
waitfor time '00:44'
select *from xs
--显示ABS函数对三个不同数字的效果
--返回给定数字表达式的绝对值
select ABS(-5.0),ABS(0.0),ABS(0)
--ACOS 反余弦函数 求一个余弦值对应的角度
declare @angle real
set @angle=0
select 'The ACOS='+CONVERT(varchar,ACOS(@angle))
--RAND([seed]) 
--返回0~1之间的一个随机值.参数seed为整型表达式,返回值类型为float
declare @count int
set @count=5
select RAND(@count)*10 as Rand_Num
go
--ASCII函数 CHAR函数
--显示字符串"China"中每个字符的ASCII值和字符
declare @position int,@string char(8)
set @position=1
set @string='China'
while @position<=datalength(@string)
begin
select ASCII(substring(@string,@position,1)),char(ASCII(substring(@string,@position,1)))
set @position=@position+1
end
go

--LEFT(character_expression,integer_expression) 返回值为varchar型
--返回课程名最左边的8个字符
use xscj
select LEFT(课程名,4)
from kc
order by 课程号
go
--LTRIM（character_expression）删除character_expression字符串中的前导空格,返回值类型为varchar
declare @str varchar(40)
set @str='    中国,是一个古老而伟大的国家'
select @str
select LTRIM(@str)
go
--replace('string_expression1','string_expression2','string_expression3')
--用string_expression3替换string_expression1中包含string_expression2中的部分
select replace('我爱你祖国','祖国','母亲')
go
--SUBSTRING(expression,start,length) 函数
--分解学生的姓名为 姓 名
use xscj
select substring(姓名,1,1) as 姓,substring(姓名,2,LEN(姓名)-1) as 名
from xs
order by 姓名
--STR(float_expression[,length[,decimal]])函数 返回值类型为 char
select STR(123.45, 6, 1)
go
--使用CASE函数对学生按性别分类
use xscj
select 学号,sex=
CASE 性别
when 1 then '男生'
when 0 then '女生'
end
from xs
go
--根据学生的年龄范围显示相应信息
use xscj
select 学号,姓名,年龄=
CASE
when year(getdate())-year(出生日期)<=27 then '年龄较小'
when year(getdate())-year(出生日期)<30 and year(getdate())-year(出生日期)>27 then '年龄适中'
else '年龄偏大'
end
from xs
go
--使用CAST 检索总学分为 40~49分的学生姓名,并将总学分转换为char(20)
use xscj
select 姓名,总学分
from xs
where CAST(总学分 as char(20)) like '4_' and 总学分>=40
go
--使用CONVERT 检索总学分为 40~49分的学生姓名,并将总学分转换为char(20)
use xscj
select 姓名,总学分
from xs
where CONVERT(char(20),总学分) like '4_' and 总学分>=40
go
--游标函数--用于返回有关游标的信息
--CURSOR_ROWS函数 返回最后打开的游标中当前存在的满足条件的行数 返回类型为 integer
--CURSOR_STATUS函数 返回游标的状态是打开还是关闭 返回值类型:smallint
use xscj
select @@CURSOR_ROWS 
--select CURSOR_STATUS('local','student_cursor') as 游标状态1
declare student_cursor cursor
for select 姓名 from xs
--select CURSOR_STATUS('local','student_cursor') as 游标状态2
open student_cursor
fetch next from student_cursor
select CURSOR_STATUS('local','student_cursor') as 游标状态3
select @@CURSOR_ROWS 
close student_cursor
--select CURSOR_STATUS('local','student_cursor') as 游标状态4
deallocate student_cursor
--select CURSOR_STATUS('local','student_cursor') as 游标状态5
--@@FETCH_STATUS 返回FETCH语句执行后的游标状态 返回值类型:integer
--@@FETCH_STATUS 控制while循环中的游标的活动
use xscj
declare @name varchar(20),@st_id varchar(20)
declare student_cursor cursor 
for select 姓名,学号 from xs
open student_cursor
fetch next from student_cursor into @name,@st_id
select @name ,@st_id
while @@FETCH_STATUS=0
begin            
fetch next from student_cursor           
end     
close student_cursor
deallocate student_cursor
--DB_ID(['database_name']) 返回其数据库标识ID号 返回类型:smallint
--DB_NAME(database_id) 根据参数database_id 返回数据库名 如果参数不指定返回当前的数据库名 返回类型 nvarchar(128)
select DB_ID('wkdl')
select DB_NAME(8)
--用户自定义函数
--(1)标量函数 
--计算全体学生某门功课的平均成绩
use xscj
create function averageMark(@kcno char(20)) returns int
as
begin 
declare @aver int
select @aver=
                    (
select avg(成绩)
from xs_kc
where 课程号=@kcno
group by 课程号
                    )
return @aver
end
go
--在select语句中调用 用户自定义函数
--如下程序对上例的函数的调用
use xscj
declare @course varchar(20)
declare @aver1 int
select @course='101'
--调用用户函数，并将返回值赋值给局部变量
select @aver1=dbo.averageMark(@course)
--显示局部变量的值
select @aver1 as '101课程的平均成绩'
go
--利用EXEC执行调用 用户自定义函数
use xscj
declare @aver1 int
EXEC @aver1=dbo.averageMark @kcno= '101'
select @aver1 as '101课程平均成绩'
go
--在xscj中建立一个course表，并将一个字段定义为计算列
use xscj
create table course
(
cno int,
canme nchar(20),
credit int,
--将此列定义为计算列
aver as
     (
       dbo.averageMark(cno)
     )
)  
insert into course(cno,canme,credit) values('101','计算机基础',5)
insert into course(cno,canme,credit) values('101','计算机基础',5)
insert into course(cno,canme,credit) values('102','程序设计与语言',4)
insert into course(cno,canme,credit) values('206','离散数学',4)
insert into course(cno,canme,credit) values('208','数据结构',4)
insert into course(cno,canme,credit) values('209','操作系统',4)
insert into course(cno,canme,credit) values('210','计算机原理',5)
insert into course(cno,canme,credit) values('212','数据库原理',4)
insert into course(cno,canme,credit) values('301','计算机网络',3)
insert into course(cno,canme,credit) values('302','软件工程',3)
insert into course(cno,canme,credit) values('304','算法',3)
insert into course(cno,canme,credit) values('305','软件设计',3)
select *from course 
--(2)内嵌表值函数 用于实现参数化视图(由于视图不支持在where子句中指定搜索条件参数)
create function fn_View1(@para nvarchar(30)) returns table
as return 
(
select 学号,姓名
from xscj.dbo.xs
where 专业名=@para
)          
go
--内嵌表值函数的调用
select *from  fn_View1('计算机')
go
--多语句表值函数
create function score_table(@studentID char(6)) returns @score table
                                     (
                                       xs_ID char(6),
                                       xs_Name char(8),
                                       kc_Name char(16),
                                       cj tinyint,
                                       xf tinyint
                                      )
as
begin
insert into @score 
select s.学号,s.姓名,p.课程名,o.成绩,p.学分
from  xs as s   
inner join xs_kc as o on(s.学号=o.学号)
inner join kc as p on(o.课程号=p.课程号)
where s.学号=@studentID
return
end

--多语句表值的调用
--查询学号为 '001101'学生的各科成绩和学分
select *from score_table('001101')

 

转载于:https://www.cnblogs.com/Joetao/articles/2234605.html