第四六章 建数据库.docx

第四六章 建数据库.docx

《第四六章 建数据库.docx》由会员分享，可在线阅读，更多相关《第四六章 建数据库.docx（12页珍藏版）》请在冰豆网上搜索。

第四六章建数据库

【例4-3】将两个数据文件和一个事务

CREATEDATABASECJMS

ONPRIMARY

（NAME=CJMS_date,

FILENAME='D:

\mssql\CJMS.mdf',

SIZE=10MB,

MAXSIZE=UNLIMITED,

FILEGROWTH=10%）

LOGON

（NAME=CJMS_log,

FILENAME='D:

\mssql\CJMS.ldf',

SIZE=1MB,

MAXSIZE=5MB,

FILEGROWTH=1MB）;

【例4-7】将两个数据文件和一个事务日志文件添加到CJMS数据库中。

ALTERDATABASECJMS

ADDFILE

（NAME=CJMS1,

FILENAME='D:

\mssql\CJMS1.ndf',

SIZE=5MB,

MAXSIZE=100MB,

FILEGROWTH=5MB）,

（NAME=CJMS2,

FILENAME='D:

\mssql\CJMS2.ndf',

SIZE=3MB,

MAXSIZE=10MB,

FILEGROWTH=1MB）;

GO

ALTERDATABASECJMS

ADDLOGFILE

（NAME=CJMSlog1,

FILENAME='D:

\mssql\CJMSlog1.ldf',

SIZE=5MB,

MAXSIZE=100MB,

FILEGROWTH=5MB）;

GO

【例4-16】清空数据库CJMS中数据文件CJMS2并从数据库中删除该文件。

useCJMS;

DBCCSHRINKFILE（CJMS2,EMPTYFILE）;

GO

ALTERDATABASECJMS

REMOVEFILECJMS2;

GO

【例4-17】完整备份数据库CJMS到指定位置。

【例4-23】

usemaster;

backupdatabasecj

todisk='E:

\cj.bak';

restoredatabasecjtest

FROMDISK='E:

\cj.bak'

WITHMOVE'cj'TO'F:

\cjtest.mdf',MOVE'cj_log'TO'F:

\cjtest_log.mdf'

GO

CREATEDATABASECJMS_snapshotON

（NAME=CJMS,

FILENAME='D:

\mssql\CJMS_snapshot.ss'）

ASSNAPSHOTOFCJMS

UPDATEtblScoreSETGrade=grade+1WHERECourseID='C8030120';

useCJMS

selectCourseID,gradefromtblScore

GO

useCJMS_snapshot

selectCourseID,gradefromtblScore

第六章数据查询

【例6-6】

useCJMS

go

select*fromtblStudents

whereSnameLIKE'李%';

【例6-12】

useCJMS

go

selectStudentID,Sname,SexfromtblStudents

ORDERBYSname;

useCJMS

go

selectStudentID,Sname,DepartmentfromtblStudents

ORDERBYDepartmentASC,BirthdayDESC;

useCJMS

go

selectStudentID,Sname,BirthdayfromtblStudents

WHEREBirthdayBETWEEN'1990-1-1'AND'1999-12-31';

useCJMS

go

selectDepartment,COUNT（StudentID）ASTotal_Number

fromtblStudents

GROUPBYDepartment;

SELECTCASEWHEN（GROUPING（Item）=1）THEN'ALL'

ELSEISNULL（Item,'UNKNOWN'）

ENDASItem,

CASEWHEN（GROUPING（Color）=1）THEN'ALL'

ELSEISNULL（Color,'UNKNOWN'）

ENDASColor,

SUM（Quantity）ASQtySum

FROMInventory

GROUPBYItem,ColorWITHROLLUP

useCJMS

go

selectStudentID,CourseID,avg（grade）ASavg_grade

fromtblScore

GROUPBYStudentID,ROLLUP（Course）

ORDERBYStudentID,CourseID;

【例6-25】

useCJMS

go

selectStudentID,CourseID,avg（grade）ASavg_grade

fromtblScore

GROUPBYStudentID,CourseIDWITHROLLUP

ORDERBYStudentID,CourseID;

【例6-26】

useCJMS

go

selectStudentID,CourseID,avg（grade）ASavg_grade

fromtblScore

GROUPBYStudentID,CourseIDWITHCUBE

ORDERBYStudentID,CourseID;

【例6-29】

useCJMS

go

selectStudentID,Sname,Sex,Department

fromtblStudents

WHEREStudentIDIN

（selectStudentIDFROMtblScoreWHERECourseID='C8030248'）;

【例6-31】

useCJMS

go

select*fromtblCourses

WHERECredit>ANY（SELECTCreditfromtblCourses）;

select*fromtblCourses

WHERECredit>All（SELECTCreditfromtblCourses）;

【例6-34】

useCJMS

go

selectc.CourseID,Cname,StudentID,grade

fromtblCoursesascJOINtblScoreassc

ONc.CourseID=sc.CourseID

ORDERBYsc.CourseID;

【例6-36】

useCJMS

go

selectst.StudentID,Sname,CourseID,grade

fromtblStudentsasstLEFTJOINtblScoresc

ONst.StudentID=sc.studentID;

习题1

useTSGL

GO

SELECTBookID,Bname,author,Press,Price

fromtbBooks

习题2

useTSGL

GO

updatetbBooks

setPrice=Price*0.85

SELECTBookID,Bname,author,Press,PriceAS'打折价'

fromtbBooks

习题3

useTSGL

GO

SELECTdistinctBookIDfromtbLending;

习题4

useTSGL

GO

SELECTBookID,Bname,author,Press,Price

fromtbBooks

wherePricebetween20and40

习题5

useTSGL

GO

SELECTBookID,Bname,author,Press,Price

fromtbBooks

wherePricenotbetween20and40

习题6

useTSGL

GO

SELECTBookID,Bname,author,Press,Price

fromtbBooks

wherePressin（'机械工业出版社','人民邮电出版社','电子工业出版社'）

wherePress='机械工业出版社'orPress='电子工业出版社'orPress='人民邮电出版社'

习题7

useTSGL

GO

SELECTBookID,Bname,author,Press,Price

fromtbBooks

wherePressnotlike'机械工业出版社'andPressnotlike'电子工业出版社'

习题8

useTSGL

go

selectReaderID,Rname

fromtbReaders

whereRnameLIKE'_丽'

习题9

useTSGL

go

selectReaderID,Rname

fromtbReaders

whereRnameLIKE'李%'andlen（Rname）=3;

useCJMS

GO

SELECTdistinctgradeas分数低于平均分者

fromtblScore

wheregrade<（selectAVG（grade）fromtblScore）;

习题10

useTSGL

go

selectReaderID,Rname

fromtbReaders

whereRnameLIKE'[程李]%'

习题11

useTSGL

go

selectReaderID,Rname

fromtbReaders

whereRnamenotLIKE'[程李]%'

习题12

useTSGL

go

select*fromtbLending

whereReturndatalike'null'

习题13

useTSGL

go

select*fromtbLending

whereReturndatanotlike'null'

习题14

useTSGL

GO

SELECTBookID,Bname,author,Press,Price

fromtbBooks

wherePricenotbetween20and40andPresslike'机械工业出版社'

习题15

useTSGL

GO

SELECTcount（BookID）asTotal_Number

fromtbBooks

习题16

useTSGL

GO

SELECTcount（BookID）asTotal_Number

fromtbLending

习题17

useTSGL

GO

SELECT'average'=AVG（Price）,'max'=MAX（Price）,'min'=MIN（Price）

fromtbBooks

习题18

useTSGL

GO

SEL