中文SQLServer数据库管理与开发源代码.docx
- 文档编号:30034669
- 上传时间:2023-08-04
- 格式:DOCX
- 页数:29
- 大小:21.67KB
中文SQLServer数据库管理与开发源代码.docx
《中文SQLServer数据库管理与开发源代码.docx》由会员分享,可在线阅读,更多相关《中文SQLServer数据库管理与开发源代码.docx(29页珍藏版)》请在冰豆网上搜索。
中文SQLServer数据库管理与开发源代码
源代码
第2章
Createdatabasetest
Onprimary
(NAME=test_dat,
FILENAME='C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\test.mdf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=15%),
FILEGROUPtest_data
(NAME=test_data_dat,
FILENAME='C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\test_data.ndf',
SIZE=100MB,
MAXSIZE=500MB,
FILEGROWTH=10MB)
LOGON
(NAME=Sales_log,
FILENAME='C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\testlog.ldf',
SIZE=5MB,
MAXSIZE=25MB,
FILEGROWTH=2MB)
GO
例1ALTERDATABASEtest
MODIFYFILE
(NAME=test_dat,
SIZE=25MB)
GO
例2ALTERDATABASEtest
ADDFILE
(NAME=test_data_dat2,
FILENAME='C:
\ProgramFiles\MicrosoftSQL
Server\MSSQL.1\MSSQL\Data\test_data2.ndf',
SIZE=50MB,
MAXSIZE=100MB,
FILEGROWTH=5MB)
GO
例3DROPDATABASEtest
GO
第3章
例1USEbasetest
Execsp_addtypesname,'varchar(10)'
Execsp_addtypebirthday,smalldatetime,null
例2Execsp_droptypesname
CREATETABLEbasetest.dbo.course
(cnochar
(2)UNIQUE,
cnamechar(20)NOTNULLUNIQUE,
cpnichar
(2),
ccreditchar(5))
USEbasetest
DELETEFROMcourseWHEREcno=2
第4章
例1USEbasetest
SELECT*FROMstudent
例2USEbasetest
SELECTsname,ssexFROMstudent
例3USEbasetest
SELECTsname,2006-sageFROMstudent
例4USEbasetest
SELECTsnameAS'学生姓名',2006-sageAS'出生年月'FROMstudent
例5USEbasetest
SELECTTOP5*FROMstudent
例6USEbasetest
SELECTDISTINCTsnofromsc
例7USEbasetest
SELECTstudent.sno,sname,sdept,cno,grade
FROMstudentJOINsc
ONstudent.sno=sc.sno
例8USEbasetest
SELECTstudent.sno,sname,sdept,cno,grade
FROMstudentLEFTJOINSC
ONstudent.sno=sc.sno
例9USEbasetest
SELECTsname,ssex,sage
FROMstudent
WHEREsage>20;
USEbasetest
SELECTsname,ssex,sage
FROMstudent
WHERENOTsage<=20;
例10USEbasetest
SELECTsname,ssex,sage
FROMstudent
WHEREsageBETWEEN21AND23
例11USEbasetest
SELECTsname,sno,sage,sdept
FROMstudent
WHEREsnameLIKE'黄%';
例12USEbasetest
SELECTsname,ssex,sage
FROMstudent
WHEREsdeptIN('MA');
例13USEbasetest
SELECTcname,ccredit
FROMcourse
WHEREcpniISNULL
例14USEbasetest
SELECTsno,sname
FROMstudent
WHEREssex='男'ANDsdept='CS'
例15USEbasetest
SELECTDISTINCTstudent.sno,sname
FROMstudent,sc
WHEREssex='女'ANDstudent.sno=sc.sno
例16USEbasetest
SELECT*FROMstudent
ORDERBYsageDESC;
例17USEbasetest
SELECTsdept,sno,sname,ssex
FROMstudent
GROUPBYsdept,sno,sname,ssex
例18USEbasetest
SELECTsdept,sno,sname,ssex
FROMstudent
GROUPBYsdept,sno,sname,ssex
HAVINGssex='女';
例19USEbasetest
SELECTsdept,SSEX,AVG(sage)AS平均年龄
FROMstudent
GROUPBYsdept,ssex;
例20USEbasetest
SELECTsdept,SSEX,AVG(sage)AS平均年龄
FROMstudent
GROUPBYsdept,ssexWITHCUBE;
例21USEbasetest
SELECTsdept,ssex,AVG(sage)AS平均年龄
FROMstudent
GROUPBYsdept,ssexWITHROLLUP;
例22USEbasetest
SELECTsno,SUM(grade)AS总成绩
FROMsc
GROUPBYsno;
例23USEbasetest
SELECTsno,AVG(grade)AS平均成绩
FROMsc
GROUPBYsno;
例24USEbasetest
SELECTsno,count(cno)AS选修课总数
FROMsc
GROUPBYsno;
例25USEbasetest
SELECTsdept,sname,MAX(sage)AS最大年龄
FROMstudent
GROUPBYsdept,sname;
例26USEbasetest
SELECT*FROMsc
WHEREsno=05007
COMPUTEAVG(grade),MAX(grade);
例27USEbasetest
SELECT*FROMsc
WHEREcno=5
ORDERBYgrade
COMPUTEAVG(grade),MAX(grade)BYgrade;
例28USEbasetest
SELECTsno,sname
FROMstudent
WHEREsno
IN(SELECTsno
FROMsc
WHEREcno=6);
例29USEbasetest
SELECTsno,sname,ssex
FROMstudent
WHEREsno
IN(SELECTsno
FROMsc
WHEREcno
IN(SELECTcno
FROMcourse
WHEREcname='数据库'));
例30USEbasetest
SELECTsno,sname,ssex
FROMstudent
WHEREEXISTS
(select*FROMsc
WHEREsno=student.snoANDcno=6);
例31USEbasetest
SELECTsno,sname,sdept,sage
FROMstudent
WHEREsage<=ALL
(SELECTsage
FROMstudent
WHEREsdept='IS')
ANDsdept!
='IS';
例32USEbasetest
SELECT*FROMstudent
WHEREsdept='IS'
UNION
SELECT*FROMstudent
WHEREsage>20;
例33USEbasetest
SELECT*FROMstudent
WHEREsdept='IS'
UNIONALL
SELECT*FROMstudent
WHEREsage>20;
第5章
例1USEbasetest
GO
CREATEVIEWMA系的学生信息AS
SELECT*
FROMstudent
WHEREsdept='MA';
例2USEbasetest
GO
CREATEVIEWIS系女生选课信息(学号,姓名,性别,选修课号,成绩)
AS
SELECTstudent.sno,sname,ssex,cno,grade
FROMstudent,sc
WHEREstudent.sno=sc.snoANDssex='女';
例3USEbasetest
GO
CREATEVIEW男生信息视图
AS
SELECT*FROMstudent
WHEREsdept='男'
WITHCHECKOPTION
例4USEbasetest
GO
CREATEVIEWCS系学生信息
AS
SELECT*FROMstudent
WHEREsdept='CS';
USEbasetest
GO
ALTERVIEWCS系学生信息(学号,姓名,性别,年龄)
WITHENCRYPTION
AS
SELECTsno,sname,ssex,sage
FROMstudent
WHEREsdept='CS';
例5USEbasetest
DROPVIEW学生选课信息
例6USEbasetest
SELECT*FROMCS系学生信息
WHERE性别='女';
例7USEbasetest
UPDATECS系学生信息
SET年龄=21
WHERE学号=05008;
第6章
例1USEbasetest
GO
CREATETABLEstudent_test
(
snoint,
snamechar(5)UNIQUE
)
GO
EXECsp_helpindexstudent_test;
例2USEbasetest
GO
CREATETABLEstudent_test1
(
snointprimarykey,
snamechar(5)
)
GO
EXECsp_helpindexstudent_test1;
例3USEbasetest
GO
CREATETABLEstudent_test3
(
snoint,
snamechar(5),
ssexchar
(2),
sdeptchar(5)
)
CREATEUNIQUEINDEXindex_student
ONstudent_test3(sno,sname)
WITHPAD_INDEX,FILLFACTOR=60
CREATEINDEXindex_sdept
ONstudent_test3(sdept)
EXECsp_helpindexstudent_test3
第7章
例1USEbasetest
GO
SELECTCOL_LENGTH('student','sname')AS'姓名长度'
FROMstudent
GO
例2Select'当前日期'=GETDATE(),
'月'=MONTH(GETDATE()),
'日'=DAY(GETDATE()),
'年'=YEAR(GETDATE());
例3PrintSTUFF('abXYFfghijklmn',3,3,'cde');
例4USEbasetest
GO
SELECTsno,ROUND(AVG(grade),4)AS'平均成绩'
FROMsc
GROUPBYsno
例5USEbasetest
GO
CREATEFUNCTIONinfo_student(@snonchar(8))
RETURNSFLOAT
AS
BEGIN
DECLARE@GradeFLOAT
SET@Grade=(SELECTAVG(grade)
FROMsc
WHEREsno=@sno
GROUPBYsno)
RETURN@Grade
END
例6USEbasetest
GO
CREATEFUNCTIONinfo_sdept(@sdeptnchar(10))
RETURNSTABLE
AS
RETURN(SELECTsno,sname,ssex
FROMstudent
WHEREsdept=@sdept);
USEbasetest
GO
SELECT*FROMdbo.info_sdept('MA');
例7USEbasetest
GO
CREATEFUNCTIONhigher_grade(@highergradeFLOAT)
RETURNS@higher_gradeTABLE(snochar(10),snamechar(20),gradeFLOAT)
AS
BEGIN
INSERT@higher_grade
SELECTstudent.sno,sname,grade
FROMstudent,sc
WHEREstudent.sno=sc.sno
ANDgrade>@highergrade
RETURN
END
GO
例8USEbasetest
GO
DECLARE@GRADEFLOAT,@MESSAGEvarchar(200)
SET@GRADE=98
IFEXISTS(SELECT*
FROMsc
WHEREgrade>@GRADE)
BEGIN
SELECTDISTINCTstudent.sno,sname,grade
FROMstudent,sc
WHEREstudent.sno=sc.sno
ANDgrade>@GRADE
END
ELSE
SET@MESSAGE='不存在选修课成绩高于98的学生'
PRINT@MESSAGE
例9USEbasetest
GO
SELECTcno,cname,'课程说明'=
CASEcpni
WHEN'4'THEN'该课程有先行课数据结构'
ELSE'该课程没有先行课数据结构'
END
FROMcourse
ORDERBYcno,cname
例10DECLARE@rint,@timesint
SET@r=1
SET@times=1
label1:
SET@r=@r*@times
SET@times=@times+1
IF@times<=5
GOTOlabel1
PRINT'5的阶乘为:
'+str(@r)
例11DECLARE@iint,@sumint
SET@i=0
SET@sum=0
WHILE@i>=0
BEGIN
SET@i=@i+1
IF@i>100
BEGIN
SELECT'1到100的和'=@sum
BREAK
END
ELSE
SET@sum=@sum+@i
END
例12USEbasetest
BEGIN
WAITFORDELAY'00:
00:
10'
SELECT*FROMcourse
END
例13USEbasetest
GO
DECLAREstudent_cursorCURSOR
FORSELECT*
FROMstudent
WHEREssex='女'
ORDERBYsno
FORREADONLY
例14USEbasetest
GO
OPENstudent_cursor
FETCHNEXTFROMstudent_cursor
WHILE@@FETCH_STATUS=0
BEGIN
FETCHNEXTFROMstudent_cursor
END
例15IFEXISTS(SELECTnameFROMsysobjects
WHEREname='stu_pro'andtype='p')
DROPPROCEDUREstu_pro
GO
CREATEPROCEDUREstu_proAS
SELECT*
FROMstudent
WHEREssex='女'
GO
EXECUTEstu_pro
GO
例16USEbasetest
GO
IFEXISTS(SELECTnameFROMsysobjects
WHEREname='sum_pro'ANDtype='p')
DROPPROCEDUREsum_pro
GO
CREATEPROCsum_pro
@aint,
@bint,
@resultintOUTPUT
AS
SET@result=@a+@b
成功执行后就创建了sum_pro存储过程。
然后在SQLServermanagementstudioquery窗口中输入下面命令:
USEbasetest
GO
DECLARE@aint,@bint,@resultint
SET@a=789
SET@b=123
EXECsum_pro@a,@b,@resultoutput
SELECT'a'=@a,'b'=@b,'a+b和为'=@result
例17USEbasetest
GO
EXECsp_helptextstu_pro
例18USEbasetest
GO
EXECsp_dependsstu_pro
例19USEbasetest
GO
EXECsp_helpstu_pro
例20USEbasetest
GO
EXECsp_renamestu_pro,'查询女生信息过程'
第8章
例1USEbasetest
IFEXISTS(SELECTnameFROMsysobjects
WHEREname='stu_tr'ANDtype='TR')
DROPTRIGGERTR1
GO
CREATETRIGGERstu_tr
ONstudent
WITHENCRYPTION
FORINSERT,UPDATE
AS
PRINT'新加入一个学生'
GO
例2USEbasetest
GO
EXECsp_helptrigger'student'
例3USEbasetest
GO
EXECsp_helptext'stu_tr'
例4USEbasetest
GO
EXECsp_help'stu_tr'
例5USEbasetest
IFEXISTS(SELECTnameFROMsysobjects
WHEREname='sc_tr'ANDtype='TR')
DROPTRIGGERsc_tr
GO
CREATETRIGGERsc_tr
ONsc
FORINSERT
AS
IFEXISTS(
SELECTsnoFROMinserted
WHEREsnoNOTIN
(
SELECTsnoFROMstudent
WHEREsdept='IS'
)
)
BEGIN
PRINT'该学生不是IS系的学生'
ROLLBACKtran
END
GO
INSERTsc(sno)
VALUES(05001)
例6USEbasetest
GO
IFEXISTS(SELECTnameFROMsysobjects
WHEREname='course_tr'ANDtype='TR')
DROPTRIGGERcourse_tr
GO
CREATETRIGGERcourse_tr
ONcourse
FORDELETE
AS
IFEXISTS(
SELECTcnoFROMdeleted
WHEREcnoIN
(
SELECTcnoFROMsc
)
)
BEGIN
PRINT'课程有学生选修不能被删除'
ROLLBACKtran
END
GO
DELETEFROMcourse
例7USEbasetest
GO
IFEXISTS(SELECTnameFROMsysobjects
WHEREname='sc1_tr'ANDtype='TR')
DROPTRIGGERsc1_tr
GO
CREATETRIGGERsc1_tr
ONsc
FORUPDATE
AS
IFEXISTS(
SELECTcnoFROMinserted
WHEREcnoNOTIN
(
SELECTcnoFROMcourse
)
)
BEGIN
PRINT'没有该课程的记录'
ROLLBACK
END
GO
UPDATEsc
SETcno=12
WHEREsno='05002'
第9章
例1USEbasetest
GO
CREATETABLEtable2
(numberin
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 中文 SQLServer 数据库 管理 开发 源代码