SQL编程基础代码

看懂一下代码、试试一下代码，我们就可以几乎在MS SQL server或者sybase上进行SQL编程了：

*******************数据类型******************************************

/*字符类型*/
create table zifu
(    
    char_string char(5),
    varchar_string varchar(5),
    text_string text)
insert into zifu values( 'test','test','it is atest data type')
/*insert into zifu values( '#￥%%￥est','test','it is atest data type')只能存Unicode标准字符256个*/
select * from zifu

/* 整形数据*/
create table zhengxing
(
    bigint_number bigint,
    int_numer int,
    smallint_number smallint,
    tinyint_number tinyint)
insert into zhengxing values( 4565555546565,545784515,32767,255)/*2^15-1=32767,2^8-1=255*/
select * from zhengxing 

/* 小数数据*/
create table xiaoshu
(
    decimal_number decimal(8,6),
    numeric_number numeric(5,3))
insert into xiaoshu values(99.99999777,40)
insert into xiaoshu values(99.9999999,90.9999)
/*insert into xiaoshu values(99.9999999,90)    99.9999999为错误数据，但99.99999977和99.9999979为正确*/
/*但numeric(5,3)可以输入90.9999，多一个数且为9*/
select * from xiaoshu

/* 日期时间类型*/
create table date 
( 
    c1 datetime,
    c2small smalldatetime)

insert into date values('2001-5-16 11:5:56.56','4/15/1985 11:31:15pm')/*存的是2001年和1985年*/
insert into date values('01-5-16 11:5:56.56','4/15/85 11:31:15pm')/*存的是2016年和1985年*/
insert into date values('April 15,1999','April 15 1999')/*可以不加，号*/
 select * from date

select day('05/06/2005')as 'day','month'=month('05/06/2005'),
        year('05/06/2005')as 'year'/*结果为：6 5 2005 */
select day('05-06-2005')as 'day'/*结果为：6*/
/*'05/06/2005'默认的是：6日5月2005年，月为第一个，与英文相同*/

/* 二进制类型*/
create table binary_table
(
    c1_binary binary(10),
    c2_varbinary varbinary(20),
    c3_image image)
insert binary_table values(0x321,0xffff,0x14ffff)/*binary存的是0x03210000000000000000，其他两个按原样存*/
select * from binary_table

/* 货币数据类型*/
create table huobi
(
    money_number money,
    smallmoney_number smallmoney)
insert into huobi values($123.45,cast('$111,111.45'as money))
/*insert into huobi values($123.45,$111,111.45)直接存时money不能夹，号*/
insert into huobi values($123.45,$111111.45)
insert into huobi values(123.45,111111.45)
insert into huobi values(￥123.45,￥111111.45)/*上面四行的结果存储跟显示都一样*/
select * from huobi

/* Unicode类型*/
declare @myunicodevar nchar(25)
set @myunicodevar='this is a Unicode string'
print @myunicodevar

declare @myunicodevar nchar(25)
set @myunicodevar='@#$%^&is is a Unicode string'
print @myunicodevar

/* 其他类型*/
/* sql_variant类型*/
create table 特殊数据
(
    aql_variant_data sql_variant)
insert into 特殊数据 values('this is a sql_variant data type test')
insert into 特殊数据 values(545454)
insert into 特殊数据 values('April 15,1999')
insert into 特殊数据 values('Apr  il')
select * from 特殊数据

/* table数据类型*/
declare @table_examble table
(    
    col_num int primary key,
    col_text char(50))
insert into @table_examble values(1,'this is a')
insert into @table_examble values(2,'table data type examble')
select * from @table_examble

/* 逻辑数据类型*/
create table bit_table
(
    c1 bit,
    c2 bit,
    c3 bit)
insert into bit_table values(22,null,0)/*能输入1，0，null，输入除0外的数都视为1*/
select * from bit_table

/* 用户自定义数据类型*/
exec sp_addtype birthday,datetime,'null'
create table 特殊数据2
(
    char_data char(20),
    birthday_day birthday)
insert into 特殊数据2 values('hingis','12/31/1980')
insert into 特殊数据2 values('marina','05/25/1981')
select * from 特殊数据2 
drop table 特殊数据2 /*只有删掉使用 用户自定义数据类型 的表特殊数据2时,才可以删除 用户自定义数据类型--birthday*/
exec sp_droptype birthday


*******************运算符******************************************


create table employee
(
    id int identity(1,1) primary key,
    vacationhour int,
    sickleavehour int)
insert into employee(vacationhour,sickleavehour) values(19,2)
insert into employee(vacationhour,sickleavehour) values(77,19)
select id,vacationhour,sickleavehour+vacationhour
    from employee
    order by vacationhour desc

create table teacherinfo
( tno char(5),
 name char(5))
insert into teacherinfo values('aaa','bbb')
insert into teacherinfo values('ccc','d')
select tno+name as info from teacherinfo/*结果是'aaa bbb'中间有个空格*/

set nocount on
declare @startdate datetime,@adddays int
set @startdate='2/11/1989 6:00 pm'
set @adddays=6
set nocount off
select @startdate+1.25 as 'start date',
    @startdate+@adddays as 'add date'


declare @mycounter int
set @mycounter=29%5
print convert (varchar(255) ,@mycounter)
/*print @mycounter为错误语法*/


*******************流程控制******************************************


declare @yf real,@w int
set @w=120
if @w<=100
set @yf=@w*0.12
else
    set @yf=100*0.12+(@w-100)*0.05
print '邮件的重量是:'+cast(@w as varchar(20) )+'克'
print '邮费是:'+cast(@yf as varchar(20) )+'元'

print '邮件的重量是:'+convert (varchar(255) ,@w)+'克'
print '邮费是:'+convert (varchar(255) ,@yf)+'元'/*cast与convert一样的*/

declare @hyuser varchar(50),@hypwd varchar(50),@msg varchar(50)
select @hyuser='liping',@hypwd='111'
if @hyuser='hystu1'
    begin
        if @hypwd='111'
            set @msg='登陆成功'
        else
            set @msg='密码错误，请重新输入'
    end
else if @hyuser='hystu2'
    begin
        if @hypwd='222'
            set @msg='登陆成功'
        else
            set @msg='密码错误，请重新输入'
    end
else if @hyuser='hystu3'
    begin
        if @hypwd='333'
            set @msg='登陆成功'
        else
            set @msg='密码错误，请重新输入'
    end
else 
    set @msg='用户名不正确，请重新输入'
print @msg


create table hyuser
(
    hyid int identity(1,1) primary key,
    hyname    varchar(50) unique,
    hypwd varchar(50))
insert into hyuser(hyname,hypwd) values('stu1','111111')
insert into hyuser(hyname,hypwd) values('stu2','222222')
insert into hyuser(hyname,hypwd) values('stu3','333333')
select * from hyuser

declare @hyuser varchar(50),@hypwd varchar(50),@msg varchar(50),@num int,@num1 int
select @hyuser='stu1',@hypwd='111111'
select @num=count(*)from hyuser where hyname=@hyuser
if @num>=1
    begin
        select @num1=count(*)from hyuser where hyname=@hyuser and hypwd=@hypwd
        if @num1>=1
            set @msg='登陆成功'
        else
            set @msg='密码错误，请重新输入'
    end
else
    set @msg='用户名不正确，请重新输入'
print @msg


declare @cj float,@str varchar(60)
set @cj=90
set @str=
case
    when @cj>100 or @cj<0 then'成绩输入错误，成绩应在0到100之间'
    when @cj>=60 and @cj<70 then'及格'
    when @cj>=70 and @cj<80 then'中等'
    when @cj>=80 and @cj<90 then'优良'
    when @cj>=90 and @cj<=100 then'优秀'
else '不及格'
end
print '该生学生等级是'+@str

declare @x int,@sum int
select @x=0,@sum=0
while @x<=100
begin
    set @sum=@sum+@x
    set @x=@x+1
end
print '从1加到100之和是：'+cast(@sum as varchar(50))


declare @x int,@sum int
select @x=0,@sum=0
while @x<=10
begin
    set @sum=@sum+@x
    set @x=@x+1
    if @sum>30
        break
end
waitfor delay '00:00:03'
print '等3秒后输出'
print '最后结果是：'+cast(@sum as varchar(50))

declare @x int,@sum int
select @x=0,@sum=0
while @x<=100
begin
    set @x=@x+1
    if @x%2=1
        continue
    set @sum=@sum+@x
end
print '从1加到100所有偶数之和是：'+cast(@sum as varchar(50))

declare @x int,@sum int
set @x=0
set @sum=0
bz:
set @x=@x+1
set @sum=@sum+@x
while @x<100 goto bz
print '利用goto语句实现从1加到100之和是：'+cast(@sum as varchar(50))




*******************常用函数******************************************



/*算数函数*/
/*三角函数*/
declare @x real
set @x=0.68
print sin(@x)
print cos(@x)
print tan(@x)
print cot(@x)
print asin(@x)
print acos(@x)
print atan(@x)

print abs(-2)/*绝对值*/
print abs(5)/**/
print sign(10)/*返回正负号*/
print sign(-45)
print sign(0)
print log(10)/*自然对数值e*/
print log10(100)/*以10为底的对数值*/
print sqrt(9)/*平方根*/
print exp(3)/*3^e指数值，结果为20.0855,e的值是2.71828……*/
print POWER(2.000, -3)/*结果为0.125*/
print POWER(2, -3)/*结果为0，因为结果与指定数值表达式的数据类型相同。*/
/*因此，如果结果有三位小数，则计算其指定幂的数字必须也有三位小数。*/


/*字符串函数*/
/*字符串转换函数*/
select ascii(123) as '123',ascii('123') as '"123"',ascii('abc') as '"abc"'/*结果为49，49，97，返回字符表达式最左端的ASCII代码值*/
select char(123),char(321),char(-123)/*结果为{，NULL，NULL。将数字转化为ASCII码，必须是0~255之间的值*/
select lower('Abc'),lower('ABC'),upper('Abc'),upper('abc')/*lower把字符都变小写，upper是变大写*/
select str(123.45,6,1),str(123.45,2,2)/* 结果为123.5和**，将数字转化为字符*/
/*字符长度及去空格函数*/
select '    hello,how are you?     '/*原样输出*/
select ltrim('    hello,how are you?     ')/*前面空格被去掉*/
select rtrim('    hello,how are you?     ')/*后面空格被去掉*/
select len('    hello,how are you?     ')/*22*/
select len(ltrim('    hello,how are you?     '))/*18*/
select len(rtrim('    hello,how are you?     '))/*22*/
/*取字符串函数*/
select left('hello,how are you?',5)
select right('hello,how are you?',12)
select substring('hello,how are you?',7,11)
/*字符串比较函数*/
select charindex('123','dihg123udie',2)/*2指从第二个位置查找（默认从0开始），结果为5，即1所在位置*/
select patindex('123','data123tsdg'),patindex('%123%','data123tsdg')/*第一个短字符串必须前后有%%，结果为0，5*/
/*字符串操作函数*/
select replicate('hello',2)/*指定次数重复字符串，结果为hellohello*/
select reverse('hello')/*将字符串的字符排列顺序反转，结果为olleh*/
select replace('you are a student','student','teacher')/*用第3字符串代替第1字符串中的子字符串第2字符串*/
select stuff('hello',2,3,'andd')/*删除位子为2开始的3个字符，用最后面的字符串代替，结果为handdo，删除了ell*/
select quotename('hello','{}')/*被特定字符括起来，结果为{hello}*/
select quotename('hello')/*默认为方括号[]，结果为[hello]*/
select quotename('hello','<>')/*结果为<hello>*/


/*数据类型转换函数*/
/*可在char与int之间转，也可text与char，image与binary，int与money*/
declare @yf int
set @yf=120
print '邮费是:'+cast(@yf as varchar(20) )
print '邮费是:'+convert (varchar(255) ,@yf)/*此的data_type不能使用户自定义的，长度默认为10*/
set @yf=convert (int ,'-78')
print convert (varchar(255) ,@yf)


/*日期与时间函数*/
select day('05-06-2005')as 'day','month'=month('05/06/2005'),
        year('05/06/2005')as 'year'/*结果为6日5月2005年*/
select datediff(month,'02-02-2009',getdate())/*结果为1,03-02-2009时运行的，返回相差月数*/
select datepart(day,'05-06-2005')/*结果为6*/
select getdate()/*年月日2009-03-02 21:23:07.403*/

/*文本和图像函数*/
/*字符类型*/
create table zifu
(    
    char_string char(5),
    varchar_string varchar(5),
    text_string text)
insert into zifu values( 'char','varch','it is atest data type')
select char_string,textptr(text_string) from zifu/*textptr返回text_string列的文本指针*/
select char_string,'valid (if 1 ) Text data'=textvalid('zifu.text_string',textptr(text_string)) from zifu
/*textvalid返回text_string列的文本指针是否有效*/
/**/
/**/


*******************自定义函数******************************************

if exists ( select table_name from information_schema.tables
                where table_name='orders')
    drop table orders
GO
SET NOCOUNT on
CREATE TABLE orders
(
 order_id int IDENTITY(1000, 1) NOT NULL,
 cust_id  int NOT NULL,
 order_date smalldatetime NOT NULL DEFAULT GETDATE(),
 order_amt money NOT NULL,
 order_person char(30) NOT NULL DEFAULT CURRENT_USER
)



create function chengji(@xname_in char(10),@cname_in char(10))
returns tinyint
as
begin
    declare @score_out tinyint
    select @score_out=Score from StudentCourse a,StudentInfo b,CourseInfo c
        where a.sno=b.sno and c.cno=a.cno and
            b.Sname=@xname_in and c.Cname=@cname_in
return ( @score_out)
end

select dbo.chengji('蔡美怡','计算机基础')


create function chang_date ( @date datetime,@separator nvarchar(2)='::' )
returns nvarchar(20)
begin
    declare @retunstring nvarchar(20)
    set @retunstring='今天是'
    +convert(nvarchar(5),datepart(year,@date))+'年'+@separator
    +convert(nvarchar(5),datepart(month,@date))+'月'+@separator
    +convert(nvarchar(5),datepart(day,@date))+'日'+@separator
    return @retunstring
end

/*error*/
select dbo.chang_date(select getdate(),'::')


CREATE FUNCTION chengji1(@xname_in char(10))
RETURNS TABLE
AS
  RETURN (select Cname, Score from StudentCourse, StudentInfo, CourseInfo
WHERE StudentInfo.Sno=StudentCourse.Sno AND
CourseInfo.Cno=StudentCourse.Cno AND Sname=@xname_in)
GO

/*OK*/
USE TeachingManageSYS
GO
CREATE FUNCTION teachercourse
(@number char(25))
RETURNS TABLE
AS
RETURN
 (    SELECT Tname
        FROM TeacherInfo WHERE Tno=@number
    UNION SELECT Cno
        FROM Teach
    WHERE Tno=@number
)
insert into  rankpayment values('001',2000)
insert into teacherInfo values('01','zhangsan','男',28,'001','','','')
insert into  teach values('01','sd',6565)
select * from teachercourse('01')



USE TeachingManageSYS
GO
CREATE FUNCTION all_score(@cname_in char(10))
RETURNS @all_score_tab table(Sno char(6) primary key,Sname char(10) not null,Ssex char(2),Score real)
AS
BEGIN
INSERT @all_score_tab
SELECT a.Sno,a.Sname,a.Ssex,b.Score
FROM StudentINFO a,StudentCourse b,CourseINFO c
WHERE a.Sno=b.Sno AND b.Cno=c.Cno AND c.Cname=@cname_in
RETURN
END
GO
select * from all_score('计算机基础')


USE TeachingManageSYS
GO
ALTER FUNCTION chengji1(@xname_in char(10))
RETURNS table
AS
  RETURN (select Cname, Score,
   lever =
       CASE
          WHEN score is null THEN '未考'
           WHEN score<60 THEN '不及格'
           WHEN score>=60 AND score<70 THEN '及格'
           WHEN score>=70 AND score<80 THEN '中'
           WHEN score>=80 AND score<90 THEN '良好'
           WHEN score>=90 THEN '优秀'
END
FROM StudentCourse, StudentInfo, CourseInfo 
WHERE StudentInfo.Sno=StudentCourse.Sno AND
CourseInfo.Cno=StudentCourse.Cno AND Sname=@xname_in)
GO

USE TeachingManageSYS
GO
DROP function all_score
GO

/*alter table studentinfo alter column sname char(10) not null*/


*******************游标******************************************

declare TM_cursor1 cursor for    
    select * from studentcourse where cno='0101'

declare TM_cousor2 cursor for
    select * from studentinfo where ssex='女'
    for read only

declare TM_cursor3 cursor for
    select * from rankpayment where payment>40
for update

open tm_cursor1

declare test_cursor cursor
    for select * from studentinfo
open test_cursor
fetch next from test_cursor
close test_cursor

/*运行不得*/
open test_cursor
fetch prior from test_cursor
fetch first from test_cursor
fetch last from test_cursor



declare xuesheng cursor
for
    select sno,sname,ssex from studentinfo 
        order by sno

declare @xuehao varchar(20)
declare @xingming varchar(20)
declare @xingbie varchar(20)
print '-------------学生信息------------------'
print ''
print '  学号          姓名             性别'
print '---------------------------------------'

open xuesheng
fetch next from xuesheng
into @xuehao,@xingming,@xingbie
while @@fetch_status = 0
    begin
        begin
            select @xuehao=convert( char(20),@xuehao )
            select @xingming=convert( char(20),@xingming )
            select @xingbie=convert( char(20),@xingbie )
            print @xuehao+@xingming+@xingbie
        end
        fetch next from xuesheng
        into @xuehao,@xingming,@xingbie
    end
close xuesheng
deallocate xuesheng
错误提示：
Cursor Fetch: Implicit conversion from data type xml to varchar is not allowed.



DECLARE Score_cursor CURSOR FOR 
SELECT Score  FROM StudentCourse s, CourseInfo c
WHERE s.Cno=c.Cno AND c.Cname='操作系统' 

DECLARE @Score_100 INT,@Score_90 INT,@Score_80 INT 
DECLARE @Score_70 INT, @Score_60 INT, @Score_others INT 
DECLARE @Score_grade INT 
SET @Score_100=0 
SET @Score_90=0 
SET @Score_80=0 
SET @Score_70=0 
SET @Score_60=0 
SET @Score_others=0 
SET @Score_grade=0 
OPEN Score_cursor 
IF(@@FETCH_STATUS = 0) GOTO LOOP1
LOOP: 
FETCH NEXT FROM Score_cursor INTO @Score_grade 
IF (@Score_grade = 100) SET @Score_100=@Score_100+1 
ELSE IF (@Score_grade >= 90)  SET @Score_90=@Score_90+1 
ELSE IF(@Score_grade >= 80)   SET @Score_80=@Score_80+1 
ELSE IF (@Score_grade >= 70)  SET @Score_70=@Score_70+1 
ELSE IF (@Score_grade >= 60)  SET @Score_60=@Score_60+1 
ELSE SET @Score_others=@Score_others+1 
IF(@@FETCH_STATUS = 0) GOTO LOOP 
LOOP1:
PRINT '-------------------成绩分段统计--------------------------------------'
PRINT ''
PRINT '       100         90         80         70         60       其他 '
PRINT '---------------------------------------------------------------------'

PRINT STR(@Score_100)+','+STR(@Score_90)+ ','+STR(@Score_80)+','+ 
STR(@Score_70)+','+STR(@Score_60)+','+STR(@Score_others) 
CLOSE Score_cursor 
DEALLOCATE Score_cursor
GO


*******************存储过程******************************************

create procedure student
as
    select * from studentinfo where sno='010101'

exec student

exec sp_stored_procedures

exec sp_help student/*显示对象信息*/
exec sp_helptext student/*显示对象定义*/
exec sp_depends student/*显示对象依赖关系的信息*/

alter procedure student
with encryption
as 
    select * from studentinfo where sno='010102'
exec student

drop procedure student

exec sp_columns

drop procedure 学生成绩查询
create procedure 学生成绩查询
@Sno varchar(6)
with encryption
as 
    select StudentInfo.Sno As 学号,Sname as 姓名,Ssex as 性别,Cname as 课程名,Score as 成绩
        from CourseInfo,StudentCourse,StudentInfo
        where StudentInfo.Sno=StudentCourse.Sno and
            StudentCourse.Cno=CourseInfo.Cno and StudentInfo.Sno=@Sno

insert into CourseInfo values('s33','计算机基础',3.5,48)
insert into StudentCourse values('010101','s33',90)
insert into StudentCourse values('010102','s33',95)
exec 学生成绩查询 @sno='010101'


*******************触发器******************************************

create trigger reminder
on StudentCourse
after insert,update
as 
    raiserror('对不起！你不能对该表进行任何修改。',16,10)

update StudentCourse
set Score=88
where Sno='010101'


create trigger safety
on database
for drop_table,alter_table
as
    print' 你不能删除该数据库中的表！'
    rollback;

drop table StudentCourse

alter trigger reminder
on StudentCourse
instead of insert,update,delete
as 
    raiserror('对不起！你不能对该表进行任何修改。',16,10)

if object_id('reminder','tr') is not null
drop trigger reminder
if object_id('safety1','tr') is not null
drop trigger safety1

disable  trigger reminder on StudentCourse 
disable  trigger safety on database

enable  trigger reminder on StudentCourse
enable  trigger safety on database


alter database TeachingManageSYS set recursive_triggers on

exec sp_configure 'nested triggers',0 /*禁用嵌套触发器*/
exec sp_configure 'nested triggers',1 /*（默认）允许使用嵌套触发器*/

create trigger safety
on database
for drop_view,alter_view,create_view,
    drop_table,alter_table,create_table
as
    print'对不起！你不能对数据库中的表或视图做任何更改，如果你是管理员请先禁用触发器“safety”！'
    rollback; 

create trigger reminder1
on CourseInfo
after insert,update,delete
as 
    raiserror('对不起！你不能对该表进行任何修改。',16,10)
go
create trigger reminder2
on RankPayment
after insert,update,delete
as 
    raiserror('对不起！你不能对该表进行任何修改。',16,10)
go
create trigger reminder3
on StuAchievement
after insert,update,delete
as 
    raiserror('对不起！你不能对该表进行任何修改。',16,10)
go
create trigger reminder4
on StudentCourse
after insert,update,delete
as 
    raiserror('对不起！你不能对该表进行任何修改。',16,10)
go
create trigger reminder5
on StudentInfo
after insert,update,delete
as 
    raiserror('对不起！你不能对该表进行任何修改。',16,10)
go
create trigger reminder6
on Teach
after insert,update,delete
as 
    raiserror('对不起！你不能对该表进行任何修改。',16,10)
go
create trigger reminder7
on TeacherInfo
after insert,update,delete
as 
    raiserror('对不起！你不能对该表进行任何修改。',16,10)


create trigger 域完整性
on RankPayment 
for update,insert
as
    if( (select payment from RankPayment where Trank='教授')!=60
        or (select payment from RankPayment where Trank='副教授')!=45
        or (select payment from RankPayment where Trank='讲师')!=30)
    begin
        raiserror('对不起！你的输入有误。',10,1)
        rollback transaction
    end


*******************备份与还原******************************************

/*使用系统存储过程创建命名备份设备*/
exec sp_addumpdevice 'disk','TeachingManageSYS','e:\dump\dump1.bak'

/*临时备份设备*/
backup database TeachingManageSYS to disk=
    'e:\backup\TeachingManageSYS.bak'

/*语言命令备份*/
backup database TeachingManageSYS to TeachingManageSYS

/*error*/
restore database TeachingManageSYS from TeachingManageSYS
    with recovery