第四六章 建数据库Word文档下载推荐.docx
- 文档编号:15841238
- 上传时间:2022-11-16
- 格式:DOCX
- 页数:12
- 大小:88.18KB
第四六章 建数据库Word文档下载推荐.docx
《第四六章 建数据库Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《第四六章 建数据库Word文档下载推荐.docx(12页珍藏版)》请在冰豆网上搜索。
【例4-7】将两个数据文件和一个事务日志文件添加到CJMS数据库中。
ALTERDATABASECJMS
ADDFILE
(NAME=CJMS1,
\mssql\CJMS1.ndf'
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB),
(NAME=CJMS2,
\mssql\CJMS2.ndf'
SIZE=3MB,
MAXSIZE=10MB,
GO
ADDLOGFILE
(NAME=CJMSlog1,
\mssql\CJMSlog1.ldf'
FILEGROWTH=5MB);
GO
【例4-16】清空数据库CJMS中数据文件CJMS2并从数据库中删除该文件。
useCJMS;
DBCCSHRINKFILE(CJMS2,EMPTYFILE);
REMOVEFILECJMS2;
【例4-17】完整备份数据库CJMS到指定位置。
【例4-23】
usemaster;
backupdatabasecj
todisk='
E:
\cj.bak'
;
restoredatabasecjtest
FROMDISK='
WITHMOVE'
cj'
TO'
F:
\cjtest.mdf'
MOVE'
cj_log'
\cjtest_log.mdf'
CREATEDATABASECJMS_snapshotON
(NAME=CJMS,
\mssql\CJMS_snapshot.ss'
)
ASSNAPSHOTOFCJMS
UPDATEtblScoreSETGrade=grade+1WHERECourseID='
C8030120'
useCJMS
selectCourseID,gradefromtblScore
useCJMS_snapshot
selectCourseID,gradefromtblScore
第六章数据查询
【例6-6】
go
select*fromtblStudents
whereSnameLIKE'
李%'
【例6-12】
selectStudentID,Sname,SexfromtblStudents
ORDERBYSname;
selectStudentID,Sname,DepartmentfromtblStudents
ORDERBYDepartmentASC,BirthdayDESC;
selectStudentID,Sname,BirthdayfromtblStudents
WHEREBirthdayBETWEEN'
1990-1-1'
AND'
1999-12-31'
selectDepartment,COUNT(StudentID)ASTotal_Number
fromtblStudents
GROUPBYDepartment;
SELECTCASEWHEN(GROUPING(Item)=1)THEN'
ALL'
ELSEISNULL(Item,'
UNKNOWN'
ENDASItem,
CASEWHEN(GROUPING(Color)=1)THEN'
ELSEISNULL(Color,'
ENDASColor,
SUM(Quantity)ASQtySum
FROMInventory
GROUPBYItem,ColorWITHROLLUP
selectStudentID,CourseID,avg(grade)ASavg_grade
fromtblScore
GROUPBYStudentID,ROLLUP(Course)
ORDERBYStudentID,CourseID;
【例6-25】
GROUPBYStudentID,CourseIDWITHROLLUP
【例6-26】
GROUPBYStudentID,CourseIDWITHCUBE
【例6-29】
selectStudentID,Sname,Sex,Department
fromtblStudents
WHEREStudentIDIN
(selectStudentIDFROMtblScoreWHERECourseID='
C8030248'
);
【例6-31】
select*fromtblCourses
WHERECredit>
ANY(SELECTCreditfromtblCourses);
All(SELECTCreditfromtblCourses);
【例6-34】
selectc.CourseID,Cname,StudentID,grade
fromtblCoursesascJOINtblScoreassc
ONc.CourseID=sc.CourseID
ORDERBYsc.CourseID;
【例6-36】
selectst.StudentID,Sname,CourseID,grade
fromtblStudentsasstLEFTJOINtblScoresc
ONst.StudentID=sc.studentID;
习题1
useTSGL
SELECTBookID,Bname,author,Press,Price
fromtbBooks
习题2
updatetbBooks
setPrice=Price*0.85
SELECTBookID,Bname,author,Press,PriceAS'
打折价'
习题3
SELECTdistinctBookIDfromtbLending;
习题4
wherePricebetween20and40
习题5
wherePricenotbetween20and40
习题6
wherePressin('
机械工业出版社'
'
人民邮电出版社'
'
电子工业出版社'
wherePress='
orPress='
orPress='
习题7
fromtbBooks
wherePressnotlike'
andPressnotlike'
习题8
selectReaderID,Rname
fromtbReaders
whereRnameLIKE'
_丽'
习题9
andlen(Rname)=3;
SELECTdistinctgradeas分数低于平均分者
fromtblScore
wheregrade<
(selectAVG(grade)fromtblScore);
习题10
[程李]%'
习题11
whereRnamenotLIKE'
习题12
select*fromtbLending
whereReturndatalike'
null'
习题13
whereReturndatanotlike'
习题14
wherePricenotbetween20and40andPresslike'
习题15
SELECTcount(BookID)asTotal_Number
习题16
fromtbLending
习题17
SELECT'
average'
=AVG(Price),'
max'
=MAX(Price),'
min'
=MIN(Price)
习题18
SEL
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第四六章 建数据库 第四 数据库
![提示](https://static.bdocx.com/images/bang_tan.gif)