--exec sp_databases
--exec sp_helpdb master
--exec sp_renamedb 原名,新名
--go
--系统命令的改变
/*exec sp_configure 'show advanced options',1
go
reconfigure
exec sp_configure 'xp_cmdshell',1
go
reconfigure
exec xp_cmdshell 'mkdir d:\15041',no_output
go
*/
create database stuDB1504
go
use stuDB1504
create table stuInfo
(
stuName varchar(10) not null,
stuNo char(6) not null primary key,
stuAge int not null
)
create table stuMarks
(
ExamNo char(8) not null primary key,
stuNo char(8) not null,
writtenExam int,
LabExam int
)
go
use stuDB1504
insert into stuInfo values('花千骨','s25301',100)
insert into stuInfo values('东方','s25302',100)
insert into stuInfo values('秦始皇','s25303',100)
insert into stuInfo values('唐明皇','s25304',100)
insert into stuInfo values('素素','s25318',100)
insert into stuMarks values('S817213','s25318',80,58)
insert into stuMarks values('S817216','s25303',50,90)
insert into stuMarks values('S817217','s25302',77,58)
insert into stuMarks values('S817218','s25301',45,82)
go
常用的系统存储过程
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT
comshell扩展存储过程
--Purpose: xp_cmdshell扩展存储过程的使用
USE master
GO
/*---创建数据库bankDB,保存在D:\bank---*/
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=3mb,
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
/*---检测是否存在:存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
DROP PROCEDURE proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
AS
select * from stuMarks
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
/*---检测是否存在:存储过程存放在系统表sysobjects中---*/
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
declare @op1 int,@op2 int
set @op1=60
set @op2=55
EXEC proc_stu @op1,@op2
带默认参数的存储过程
USE stuDB
GO
/*---检测是否存在:存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
DROP PROCEDURE proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
@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
GO
/*---调用存储过程----*/
EXEC proc_stu --都采用默认值:笔试和机试及格线都为60分
EXEC proc_stu 64 --机试采用默认值:笔试及格线64分,机试及格线60分。
EXEC proc_stu 60,55 --都不采用默认值:笔试及格线60分,机试及格线55分。
--错误的调用方式:EXEC proc_stu ,55 --希望笔试采用默认值,机试及格线55分
--正确的调用方式:EXEC proc_stu @labPass=55 --笔试采用默认值,机试及格线55分
带输出参数的存储过程
USE stuDB
GO
/*---检测是否存在:存储过程存放在系统表sysobjects中---*/
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 ' 参加本次考试没有通过的学员:'
print ' 学号 姓名 笔试成绩 机试成绩 '
--SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo
--INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
-- WHERE writtenExam<@writtenPass OR labExam<@labPass
/*定义变量*/
DECLARE @stuNo char(6),@stuName varchar(20),@writtenExam int, @labExam int
/*定义只读游标*/
DECLARE user_csr CURSOR READ_ONLY FOR
SELECT stuInfo.stuNo,stuName,writtenExam,labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass OR labExam<@labPass
/*打开游标*/
OPEN user_csr
/*执行第一次数据读取操作*/
FETCH NEXT FROM user_csr INTO @stuNo,@stuName,@writtenExam,@labExam
/*循环游标读取操作*/
WHILE @@FETCH_STATUS=0
BEGIN
PRINT ' ' + @stuNo + ' ' + @stuName + ' ' + convert(varchar(3),@writtenExam)+' '+convert(varchar(3),@labExam)
FETCH NEXT FROM user_csr INTO @stuNo,@stuName,@writtenExam,@labExam
END
/*关闭游标*/
CLOSE user_csr
/*释放游标*/
DEALLOCATE user_csr
/*--统计并返回没有通过考试的学员人数--*/
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
带返回值的存储过程
USE stuDB
GO
/*---检测是否存在:存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
DROP PROCEDURE proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
@writtenPass int=60, --默认参数放后
@labPass int=60 --默认参数放后
AS
declare @notpassSum int
/*--统计并返回没有通过考试的学员人数--*/
SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
WHERE writtenExam<@writtenPass OR labExam<@labPass
IF @notpassSum>=5
begin
print '未通过人数:'+convert(varchar(5),@notpassSum)+ '人,超过60%'
return 0;
end
ELSE
begin
print '未通过人数:'+convert(varchar(5),@notpassSum)+ '人,已控制在60%以下'
return 1;
end
GO
/*---检测是否存在:存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu2' )
DROP PROCEDURE proc_stu2
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu2
AS
declare @result int
exec @result = proc_stu 64,60
IF @result=0
print '及格分数线还应下调'
else
print '及格分数线适中'
GO
exec dbo.proc_stu2
临时表
//建表
CREATE TABLE tb1(
ID1 char(2) NOT NULL,
ID2 char(4) NOT NULL,
col int,
PRIMARY KEY(ID1,ID2)
)
GO
INSERT tb1 SELECT 'aa','0001',1
UNION SELECT 'aa','0003',2
UNION SELECT 'aa','0004',3
UNION SELECT 'bb','0005',4
UNION SELECT 'bb','0006',5
UNION SELECT 'cc','0007',6
UNION SELECT 'cc','0009',7
GO
select * from tb1
--重排编号处理
SELECT ID=IDENTITY(int,0,1),* INTO #tempTb FROM tb1 ORDER BY ID1,ID2
UPDATE t1 SET ID2=RIGHT(10001+tmp1.ID-tmp2.ID,4)
FROM tb1 t1,#tempTb tmp1,(SELECT ID1,ID=MIN(ID) FROM #tempTb GROUP BY ID1) tmp2
WHERE t1.ID1=tmp1.ID1 AND t1.ID2=tmp1.ID2 AND tmp1.ID1=tmp2.ID1
DROP TABLE #tempTb
SELECT * FROM tb1
自定义函数1
CREATE FUNCTION dbo.fun_hello
( @name VARCHAR(8) )
RETURNS VARCHAR(20)
AS
BEGIN
RETURN @name+',您好!'
END
GO
select dbo.fun_hello('欧阳夏丹') as '问好'
自定义函数2
CREATE FUNCTION dbo.getSectionUser
( @uid int )
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @uname varchar(15)
select @uname=Uname from bbsUsers where UID=@uid
RETURN @uname
END
GO
select Sname, dbo.getSectionUser(SmasterID) as '版主' from bbsSection