加载中…
个人资料
岸芷汀蓝
岸芷汀蓝
  • 博客等级:
  • 博客积分:0
  • 博客访问:195,267
  • 关注人气:68
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

内容为:sql语句,事物,索引,视图,存储过程等

(2009-07-12 08:51:59)
标签:

it

分类: 数据库

case分支语句
SELECT * FROM stuMarks  --原始成绩
print 'ABCDE五级显示成绩如下:'
SELECT stuNo,成绩= CASE 
                     WHEN writtenExam<60 THEN 'E'
                     WHEN writtenExam between 60 AND 69 THEN 'D'
                     WHEN writtenExam between 70 AND 79 THEN 'C'
                     WHEN writtenExam between 80 AND 89 THEN 'B'
                     ElSE 'A'
                  END
             FROM stuMarks
——————————————————————————————————————————————————————--------------------------------------------------------------------------------
dbaccess

use master
go
--如果存在已有的登陆名
if exists (select * from syslogins where name='luogaoping\lgp')
  exec sp_revokelogin 'luogaoping\lgp'
--授权登陆名,连接数据库的能力做认证(windows 身份验证)
exec sp_grantlogin 'luogaoping\lgp'
-------------------------------------------------------------------
use shopping
go
--如果已经有数据库用户则收回此用户,并重新建立
if exists (select * from sysusers where name='myuser' )
  exec sp_revokedbaccess 'myuser'
--创建数据库安全帐号(用户)
exec sp_grantdbaccess 'luogaoping\lgp','myuser'
-------------------------------------------------------------------
--创建默认连接的数据库
exec sp_defaultdb 'luogaoping\lgp','pubs'
-------------------------------------------------------------------
--赋予权限
grant select on studentinfo to myuser
--收回权限
revoke select on myuser
-------------------------------------------------------------------

--SQL 身份验证

exec sp_addlogin 'luogaoping','123'
go
exec sp_grantdbaccess 'luogaoping','dbluogaoping'
go
exec sp_defaultdb 'luogaoping','pubs'
go
grant create table to dbluogaoping
grant select on studentinfo to dbluogaoping
———————————————————————————————————————————————————————-----------------------------------------------------------------------------
if语句
DECLARE @myavg float
SELECT @myavg=AVG(writtenExam)  from stuMarks
print '本班平均分'+convert(varchar(5),@myavg)
IF (@myavg>70)
  BEGIN
    print '本班笔试成绩优秀,前三名的成绩为'
    SELECT TOP 3 * FROM stuMarks ORDER BY writtenExam DESC
  END
ELSE
  BEGIN
    print '本班笔试成绩较差,后三名的成绩为'
    SELECT TOP 3 * FROM stuMarks ORDER BY writtenExam DESC
  END
——————————————————————————————————————————————————————--------------------------------------------------------------------------------
while语句
INSERT INTO stuMarks(examNo,stuNo,writtenExam,LabExam)  --插入测试数据
   VALUES('S271819','s25318',56,48)
SELECT * FROM stuMarks

DECLARE @n int
WHILE(1=1) --条件永远成立
  BEGIN
    SELECT @n=COUNT(*) FROM stuMarks WHERE writtenExam<60 --统计不及格人数
    IF (@n>0)
       UPDATE stuMarks SET writtenExam=writtenExam+2  --每人加2分
    ELSE
       BREAK  --退出循环
  END
print '加分后的成绩如下:'
SELECT * FROM stuMarks

————————————————————————————————————————————————————————----------------------------------------------------------------------------

-- Purpose: 常用系统存储过程使用

EXEC sp_databases  --列出当前系统中的数据库
EXEC  sp_renamedb 'Northwind','Northwind1'--改变数据库名称(单用户访问)
USE stuDB
GO
EXEC sp_tables  --当前数据库中查询的对象的列表
EXEC sp_columns stuInfo  --返回某个表列的信息
EXEC sp_help stuInfo  --查看表stuInfo的信息
EXEC sp_helpconstraint stuInfo --查看表stuInfo的约束
EXEC sp_helpindex stuMarks  --查看表stuMarks的索引
EXEC sp_helptext 'view_stuInfo_stuMarks' --查看视图的语句文本
EXEC sp_stored_procedures  --返回当前数据库中的存储过程列表

————————————————————————————————————————————————————————----------------------------------------------------------------------------
Purpose: xp_cmdshell扩展存储过程的使用
USE master
GO

EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT  --创建文件夹D:\bank

--创建建库bankDB
IF exists(SELECT * FROM sysdatabases WHERE name='bankDB')
  DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
 ON
 (
  NAME='bankDB_data',
  FILENAME='d:\bank\bankDB_data.mdf',
  SIZE=1mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= 'bankDB_log',
  FILENAME='d:\bank\bankDB_log.ldf',
  SIZE=1mb,
  FILEGROWTH=15%
 )
GO
EXEC xp_cmdshell 'dir D:\bank\' --查看文件

————————————————————————————————————————————————————————----------------------------------------------------------------------------
如何创建事务
USE stuDB
GO
--恢复原来的数据
--UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='李四'
SET NOCOUNT ON --不显示受影响的行数信息
print '查看转帐事务前的余额'
SELECT * FROM bank 
GO


BEGIN TRANSACTION

DECLARE @errorSum INT
SET @errorSum=0  --初始化为0,即无错误


UPDATE bank SET currentMoney=currentMoney-800 WHERE customerName='张三'
SET @errorSum=@errorSum+@@error  --累计是否有错误
UPDATE bank SET currentMoney=currentMoney+800 WHERE customerName='李四'
SET @errorSum=@errorSum+@@error  --累计是否有错误

print '查看转帐事务过程中的余额'
SELECT * FROM bank


IF @errorSum<>0  --如果有错误
  BEGIN
    print '交易失败,回滚事务'
    ROLLBACK TRANSACTION
  END 
ELSE
  BEGIN
    print '交易成功,提交事务,写入硬盘,永久的保存'
    COMMIT TRANSACTION  
  END
GO

print '查看转帐事务后的余额'
SELECT * FROM bank 
GO
————————————————————————————————————————————————————————----------------------------------------------------------------------------
创建索引
USE stuDB
GO

IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'IX_stuMarks_writtenExam')
   DROP INDEX stuMarks.IX_stuMarks_writtenExam  --删除索引

CREATE NONCLUSTERED INDEX IX_stuMarks_writtenExam
   ON stuMarks(writtenExam)
    WITH FILLFACTOR= 30
GO


SELECT * FROM stuMarks
  (INDEX=IX_stuMarks_writtenExam)
    WHERE writtenExam BETWEEN 60 AND 90
————————————————————————————————————————————————————————----------------------------------------------------------------------------
创建视图
USE stuDB
GO

IF EXISTS (SELECT * FROM  sysobjects
          WHERE name = 'view_stuInfo_stuMarks')
   DROP VIEW view_stuInfo_stuMarks
GO


CREATE VIEW view_stuInfo_stuMarks
  AS
    SELECT 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,
      机试成绩=labExam,平均分=(writtenExam+labExam)/2
        FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
GO


SELECT * FROM view_stuInfo_stuMarks
————————————————————————————————————————————————————————----------------------------------------------------------------------------
不带参数的存储过程
USE stuDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO

CREATE PROCEDURE proc_stu
  AS
    DECLARE @writtenAvg float,@labAvg float --笔试和机试平均分变量
    SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) FROM stuMarks
    print '笔试平均分:'+convert(varchar(5),@writtenAvg) 
    print '机试平均分:'+convert(varchar(5),@labAvg)
    IF (@writtenAvg>70 AND @labAvg>70)
       print '本班考试成绩:优秀'
    ELSE
       print '本班考试成绩:较差'
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员:'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<60 OR labExam<60
GO


EXEC proc_stu  --调用存储过程的语法:EXEC 过程名 [参数]
———————————————————————————————————————————————————————— --------------------------------------------------------------------------  
带参数的存储过程
USE stuDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO

CREATE PROCEDURE proc_stu
  @writtenPass int,
  @labPass int
  AS
    print '笔试及格线:'+convert(varchar(5),@writtenPass)
    print '机试及格线:'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员:'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
GO


--假定本次考试机试偏难,机试的及格线定为55分,笔试及格线定为60分。
EXEC proc_stu 60,55  --或这样调用:EXEC proc_stu @labPass=55,@writtenPass=60

————————————————————————————————————————————————————————---------------------------------------------------------------------------
带输出参数的存储过程
USE stuDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO

CREATE PROCEDURE proc_stu
  @notpassSum int OUTPUT, --OUTPUT关键字,否则视为输入参数
  @writtenPass int=60,  --默认参数放后
  @labPass int=60       --默认参数放后
  AS
    print '笔试及格线:'+convert(varchar(5),@writtenPass)
       + '   机试及格线:'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员:'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
   
    SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
      WHERE writtenExam<@writtenPass OR labExam<@labPass
   
GO


DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64   --机试及格线采用默认值:笔试及格线64分,机试及格线60分。
print '--------------------------------------------------'
IF @sum>=3
  print '未通过人数:'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
ELSE
  print '未通过人数:'+convert(varchar(5),@sum)+ '人,已控制在60%以下,及格分数线适中'
GO
—————————————————————————————————————————————————————————-------------------------------------------------------------------------
raiserror

USE stuDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO

CREATE PROCEDURE proc_stu
  @notpassSum int OUTPUT, --OUTPUT关键字,否则视为输入参数
  @writtenPass int=60,  --默认参数放后
  @labPass int=60       --默认参数放后
  AS
   
    IF  (NOT @writtenPass BETWEEN 0 AND 100) OR(NOT @labPass BETWEEN 0 AND 100)
       BEGIN
         RAISERROR ('及格线错误,请指定0-100之间的分数,统计中断退出',16,1)
         RETURN  --立即返回,退出存储过程
       END
    print '笔试及格线:'+convert(varchar(5),@writtenPass)
       + '   机试及格线:'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员:'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
   
    SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
      WHERE writtenExam<@writtenPass OR labExam<@labPass
   
GO


DECLARE @sum int,@t int
EXEC proc_stu @sum OUTPUT ,888   --笔试及格线误输入604分
SET @t=@@ERROR --如果出现了错误,执行了RAISERROR语句,系统全局@@ERROR将不等于0,表示有错
print  '错误号:'+convert(varchar(5),@t)
IF @t<>0 
   RETURN  --退出批处理,后续语句不再执行
print '--------------------------------------------------'
IF @sum>=3
  print '未通过人数:'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
ELSE
  print '未通过人数:'+convert(varchar(5),@sum)+ '人,已控制在60%以下,及格分数线适中'
GO

 

 

 

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有