关闭

关闭

关闭

封号提示

内容

首页 第3章SQL Server 数据库基本对象操作和管理.ppt

第3章SQL Server 数据库基本对象操作和管理.ppt

第3章SQL Server 数据库基本对象操作和管理.ppt

上传者: 艾尔小茜茜 2018-05-06 评分 0 0 0 0 0 0 暂无简介 简介 举报

简介:本文档为《第3章SQL Server 数据库基本对象操作和管理ppt》,可适用于工程科技领域,主题内容包含第章SQLServer数据库基本对象操作和管理数据库在SQLServer中创数据库方法有两种使用SQLServerManager和TSQL(Crea符等。

第章SQLServer数据库基本对象操作和管理数据库在SQLServer中创数据库方法有两种使用SQLServerManager和TSQL(CreateDatebase)创建数据库。当新数据库创建之后系统自动把model数据库中的系统表复制到新数据库中。下面描述在SQLServer中用两种不同方法创建数据库的内容。定义数据库一般来说为了创建数据库首先应定义数据库定义数据库的内容包括:()数据库名称如命名数据名称为MaterialData。()数据库大小如主文件(mdf)大小为MB日志文件大小为MB。()数据库将驻留在哪一个文件中如数据库将驻留在lsquoC:ProgramFilesMicrosoftSQLServerMSSQLdatamaterialDataMDFrsquo中。即创建MaterialData数据库,主文件(mdf)大小为MB日志文件大小为MB。数据库将驻留在#C:ProgramFilesMicrosoftSQLServerMSSQLdatamaterialDataMDF#中。ServerManager创建数据库下面描述用ServerManager创建数据库步骤。第一步登录SQLServer数据库引擎后图所示SQLServer数据库管理图面。SQLServer数据库管理界面在选择数据库菜单中右击鼠标出现图所示图面选择新建数据库功能第二步指定数据库名称MaterialData在进入新建数据库图所示图面中在数据库名称输入框输入数据库名称MaterialData指定数据库名称图数据库名称输入图面指定数据库所有者第三步指定数据库所有者可选择默认所有者。选择数据库所有者右边按钮进入数据库所有者指定方法步骤图所示图指定数据库所者图指定数据库所者指定主文件与日志文件初始大小第四步指定主文件与日志文件初始大小图所示。主文件MB日志文件MB。图指定主文件与日志文件初始大小指定主文件与日志文件自助增长方式与最大文件大小第五步指定主文件与日志文件自助增长方式与最大文件大小在图中选择自动增长栏中右边按扭出现图所示窗口。在所示窗口中可指定文件自动增长方式与最大文件大小。图指定主文件与日志文件自助增长方式最大文件大小指定主文件与日志文件目录路径第六步指定主文件与日志文件目录路径在图的路径栏中选择与主文件、日志文件对应的右边按钮分别进入主文件目录路径与日志文件目录路径指定窗口图与图所示。.TSQL(CreateDatebase)创建数据库下面描述用TSQL(CreateDatebase)创建数据库。()选择新建查询命令进入TSQL命令编辑窗口图所示。()在图数据库下拉列表框中选择master数据库图所示。()在图的TSQL命令编辑窗口输入如下的TSQL语句。CREATEDATABASEMaterialDataONPRIMARY(NAME=MaterialData,FILENAME=#C:ProgramFilesMicrosoftSQLServerMSSQLMSSQLDatamaterialDataMDF#,SIZE=MB,MAXSIZE=MB,FILEGROWTH=)LOGON(NAME=MaterialLog,FILENAME=#C:ProgramFilesMicrosoftSQLServerMSSQLMSSQLDatamaterialLogLDF#,SIZE=MB,MAXSIZE=MB,FILEGROWTH=MB)其中PRIMARY指定主文件(扩展名为dmf)FILENAME指定文件名与路径SIZE指定数据或日志文件大小MAXSIZE指定文件能增长到的最大长度FILEGROWTH指定文件增长量()在图中选择执行功能菜单执行结果图所示数据库创建成功()当新数据库创建之后系统自动把model数据库中的系统表复制到新数据库中新建成功的MaterialData出现在数据库列表中图所示。表表管理设计与创建假如有如下五个业务表部门编号部门名称生产管理部销售管理部表Department表Product产品编码产品名称电动机电影机部门编号产品编号产量管理人员编号管理人员姓名管理人员性别管理人员工资表planning表Manager表Orderbill订单编号管理人员编号订单日期订单金额现对以上五个表的表名、字段名、字段类型、字段长度、主键及外键设计如下表所示:表Department字段名字段类型、长度及键说明deptNochar()NOT主键deptNamevarchar()NOT字段名字段类型、长度及键说明productNochar()NOT主键productNamevarchar()NOT表Product表planning字段名字段类型、长度及键说明deptNochar()NOT外键productNochar()NOT外键planoutputnumeric()字段名字段类型、长度及键说明ManagerNochar()NOT主键ManagerNamevarchar()NOTSexchar()Wagenumeric()表Manager表Orderbill字段名字段类型、长度及键说明OrderbillNochar()NOT主键managerNochar()NOT,外键OrderDateDateOrderMoneyNumeric(,)现试图在中建立的数据库MaterialData中创建以上个表。在SQLServer中,可用下述方法在己建立的数据库中创建及修改表。第一步进入MaterialData数据库中新建表功能图所示。.使用SQLServerManager创建表填写表中列名、数据类型及是否为空设置键及表存盘。()Departmenta填写Department表中列名、数据类型图所示。第二步b设置表Department中主键图所示。c存盘及表命名图图所示。图输入表命名Table图输入表命名Department()planninga填写表planning中列名、数据类型图所示。表b存盘及表命名图所示。图输入表命名Table图输入表命名Planningc按图所示设置deptNo外键图设置deptNo外键使用ldquo添加ldquo按钮图设置deptNo外键确定ldquo标识ldquo名称图置deptNo外键选择主键表与外键表图deptNo外键选择主键表与外键表字段d设置productNo外键图所示。图设置productNo外键使用ldquo添加ldquo按钮图设置productNo外键确定标ldquo名称rdquoFKplanningProduct图设置productNo外键确定主表、外键表等相关表中字段图设置productNo外键完成返回ManagementStudio窗口.使用SQLServerManager设置表的属性在属性框中可修改表的属性如可对前面设计的各表的列名类型等进行修改如图所示。.使用SQLServerManager修改表的结构使用SQLServerManager增加、删除列()增加列图所示。()删除列.使用TSQL语言创建表创建表的SQL语句是CREATETABLE。CREATETABLE命令的语法如下:CREATETABLEtablename(nameOfattrtypeOfattrlnsmeofattrtypeOfattr...)()创建表department。SQLQuerysql程序设计如下:CREATETABLEdepartment(deptNochar()NOTPRIMARYKEY,deptNamevarchar()NOT)()创建表product。SQLQuerysql程序设计如下:CREATETABLEproduct(productNochar()NOTPRIMARYKEY,productNamevarchar()NOT)()创建表planningSQLQuerysql程序设计如下:CREATETABLEplanning(deptNochar()NOTconstraintFKplandeptNoforeignkey(deptNo)referencesdepartment(deptNo),productNochar()NOTconstraintFKplanproductNoforeignkey(productNo)referencesproduct(productNo),planoutputnumeric())()创建表ManagerSQLQuerysql程序设计如下:CREATETABLEManager(ManagerNochar()NOTPRIMARYKEY,ManagerNamevarchar()NOT,Sexchar(),Wagenumeric())创建表OrderbillSQLQuerysql程序设计如下:CREATETABLEOrderbill(OrderbillNochar()NOTPRIMARYKEY,managerNochar()NOTconstraintFKOrderbillmanagerNoforeignkey(managerNo)referencesmanager(managerNo),OrderDateDatetime,OrderMoneynumeric(,))向表中插入数据表创建完成后可以用命令INSERTINTO向表里填充记录。语法是:INSERTINTOtablename(nameOfattrnameOfattr...)VALUES(valattrvalattr...)INSERT语句实例把表中数据输入数据库MaterialData中Department表中。部门编号部门名称生产管理部销售管理部表DepartmentINSERTINTOdepartment(deptNo,deptName)VALUES(##,#生产管理部#)INSERTINTOdepartment(deptNO,deptName)VALUES(##,#销售管理部#).使用SELECT子句插入数据语法形式:INSERTtablenameSELECTcolumnlistFROMtablelistWHEREserachconditions注意:参考表可以相同或不同要插入数据的表必须已存在要插入数据的表必须和SELECT子句结果集兼容建立department临时表department,并把department中数据输入department。程序设计如下:CREATETABLEdepartment(deptNochar()NOTPRIMARYKEY,deptNamevarchar()NOT)INSERTdepartmentSELECTdeptNO,deptNameFROMdepartment实例使用UPDATE修改表中数据Update(更新数据)命令要修改记录中的一个或者多个属性的值使用UPDATE命令。语法是UPDATEtablenameSETnameOfattr=value...nameofattrk=valuekWHEREcondition实例把department表中ldquo销售部rdquo改为ldquo市场部rdquo。程序设计如下:UPDATEdepartmentSETdeptName=#市场部#WHEREdeptName=#销售管理部#删除一条记录要从一个表中删除一条记录使用DELETEFROM命令。语法是:DELETEFROMtablenameWHEREcondition实例删除department表部门名为市场部的部门。DELETEFROMdepartmentWHEREdeptName=#市场部#使用TransactSQL修改表结构修改表结构语句的基本语法格式为:ALTERTABLEtablename{ALTERCOLUMNcolumnnamenewdatatype(precision,scale)|NOT}|ADD{columedefinition},hellipn|DROP{CONSTRAINTconstraintname|COLUMNcolumn},hellipn实例在表Product中增加一个新列Price(money类型允许为空)。程序设计如下:USEMaterialDataALTERTABLEProductADDPricemoneyGO实例在表Product中删除列Price。程序设计如下:USEMaterialDataALTERTABLEProductDROPCOLUMNPriceGOIndex索引索引:一种物理结构提供一种以一列或多列的值为基础的迅速查找表中的行的机制索引类型聚簇索引:行的物理顺序与索引顺序一致非聚簇索引:逻辑顺序唯一性索引:创建索引的列上不包括重复数据SQLServer对索引的限制每表一个聚簇索引每表多个非聚簇索()可视化创建索引选择要创建索引的列选择ldquo索引键(I)helliprdquo图所示。图选择要创建索引的列在弹出的ldquo索引键rdquo对话框中点击ldquo添加rdquo按钮图所示。在ldquo标识rdquo栏下的ldquo(名称)rdquo里修改新增索引的名称为:IXProductName。图所示。图修改新增索引的名称在ldquo常规rdquo栏下的ldquo列rdquo里选择新增索引要绑定的列及排序方式(升序或降序)图选择新增索引要绑定的列及排序方式图选择新增索引要绑定的列及排序方式点击ldquo确定rdquo完成排序使用TransactSQL创建索引CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEXindexnameON{table|view}(columnASC|DESC,hellipn)WITHindexoption,hellipnONfilegroupindexoption::={PADINDEX|FILLFACTOR=fillfactor|IGNOREDUPKEY|DROPEXISTING|STATISTICSNORECOMPUTE|SORTINTEMPDB}实例为表Product的productNane字段建立升序索引:USEMaterialDataIFEXISTS(SELECTNAMEFROMsysindexesWHEREname=#IXProductName#)DROPINDEXProductIXProductNameGOUSEMaterialDataCREATEINDEXIXProductNameONProduct(productName)GO实例为表Product的productNane、productNo两字段建立索引:USEMaterialDataIFEXISTS(SELECTNAMEFROMsysindexesWHEREname=#IXPNamePNo#)DROPINDEXProductIXPNamePNoGOUSEMaterialDataCREATEINDEXIXPNamePNoONProduct(productName,productNo)GOSQLServer数据查询与表现在SQLServer中方法有两种查询数据库数据方法使用SQLServerManager和TSQL创建查询。SQL里面最常用的命令是select(查询)语句用于检索数据。检索表中列技术.检索全部列检索pub数据库中jobs表中全部信息。SELECT*FROMjobs.重新对列排序结果集中列的顺序由SELECT关键字后面列名的顺序确定。SELECTjobdesc,jobidFROMjobs检索表中列技术.使用文字串技术可在SELECT关键字后面增加文字说明。SELECTjobdesc,#itsidis#,jobidFROMjobs.改变列标题技术可在SELECT关键字后面对每列表头增加描述字说明。SELECT#Descrbition#=jobdesc,#IdentifyNumber#=jobidFROMjobs将jobs表中的jobdesc、jobid两列表头增加描述字说明。这样列表显示的表头将是Descrbition(对应jobdesc列)、IdentifyNumber(对应jobid列)。操纵数据技术使用算术运算符:,,*,。可以用在各种数字列上。对pub数据库titles表中的price列进行如下数据运算:price、pricendash、price*及price。对应这些运算的SELECT语句如下:SELECTtitle,price,priceFROMtitlesSELECTtitle,price,pricendashFROMtitlesSELECTtitle,price,(price*)FROMtitlesSELECTtitle,price,(price)FROMtitles选择行技术语法格式:SELECTslectlistFROMtablelistWHEREsearchconditions.比较技术searchconditions比较运算符有:等于=大于小于大于或者等于=小于或者等于=不等于不大于!不小于!实例查询pubs数据库employee表中pubid不等于列名为pubid,empid,fname的行。Selectpubid,empid,fnameFromemployeeWherepubid##查询pubs数据库authors表中state等于CA列名为aulname,city,state的行。Selectaulname,city,stateFromauthorsWherestate=#CA#.范围技术BETWEEN指定搜索的范围。语法:testexpressionNOTBETWEENbeginexpressionANDendexpression从pubs数据库titles表中检索年销售额在至之间的书籍。Selecttitleid,ytdsalesFromtitlesWhereytdsalesBetweenand.逻辑运算技术{AND|OR}NOT指定搜索的逻辑运算结果。语法:SELECTselectlistFROMtablelistWHERENOTexpression{AND|OR}NOTexpression实例从pubs数据库authors表中检索aulname=#white#或aulname=#green#或state=#CA#的作者。Select*FromauthorsWhereaulname=#white#ORaulname=#green#ORstate=#CA#.汇总数据技术)合计函数AVG数据表达式的平均值COUNT某个表达式中数据值的数量COUNT(*)所选择行的数量MAX表达式中最大值MIN表达式中最小值SUN数据表式的总和实例从pubs数据库titles表中检索price平均值。Selectavg(price)Fromtitles从pubs数据库titles表中检索price种类数语句如下:Selectcount(price)Fromtitles从pubs数据库titles表中检索记录数语句如下:Selectcount(*)Fromtitles()GROUPBY子句对查询结果分组后合计(聚集)函数将分别作用于每个组按指定的一列或多列值分组值相等的为一组()GROUPBY子句求各个课程号及相应的选课人数。SELECTCnoCOUNT(Sno)FROMSCGROUPBYCno视图及其应用视图综述视图的操作视图定义信息查询案例中的视图应用一、视图综述视图的基本概念视图是一个虚拟表从一个或多个表中导出(视图也可以从视图中导出)其内容由查询语句定义生成。从表面上看视图和真实表一样具有结构和数据包含一系列带有名称的列和行数据。实质上表是视图的基础数据库中只存储了视图定义而不存放视图所对应的数据视图所对应的数据仍存放在视图所引用的基表中。一、视图综述视图的作用)视图可以集中数据满足不同用户对数据的不同要求。)视图可以简化复杂查询的结构从而方便用户对数据的操作。)视图能够对数据提供安全保护。)便于组织数据导出。二、视图的操作创建视图修改视图使用视图删除视图创建视图时应该注意以下:创建者必须拥有创建视图的权限对视图中引用的基表或视图有许可权。只能在当前数据库中创建视图如果使用分布式查询视图所引用的基表和视图可以存在于其它数据库或它服务器中。在一个视图中最多引用列视图中记录的行数限制由基表中记录数目决定。视图的名称必须遵循标识符的定义规则对于每个用户来说也必须唯一而且视图名称不允许与该用户拥有的表重名。视图中列的名称一般继承其基表中列的名称如果视图中某一列是算术表达式、函数、常量或者来自多个表的列名相同必须要为视图中的列定义名称。可以将视图创建在其它视图上SQLServer中允许层的视图嵌套。不能将规则、默认绑定在视图上。不能在临时表上创建视图也不能创建临时视图。创建视图时应该注意以下:创建视图使用企业管理器创建视图使用CREATEVIEW语句创建视图例:创建一个包括学生学号、姓名及所选课程的课程名以及成绩的视图(视图名:成绩单)。使用企业管理器创建视图创建过程中的图解:属性图视图设计器使用企业管理器创建视图创建步骤如下:)在企业管理器打开ldquo视图设计器rdquo。)单击工具栏上的ldquo添加表rdquo按钮将表添加到视图设计器中。)根据新建视图的需要从表中选择视图引用的列。)在网格窗格中的ldquo准则rdquo栏中设置过滤记录的条件。)设置视图的其它属性。)在视图设计器窗口中检查SQL语法。)最后单击ldquo保存rdquo按钮为视图指定名称。关闭ldquo视图设计器rdquo。图ldquo添加表rdquo对话框使用企业管理器创建视图图选择表列使用CREATEVIEW语句创建视图语法格式如下:CREATEVIEWdatabasenameownerviewname(column,n)ASselectstatementWITHCHECKOPTION使用CREATEVIEW语句创建视图例:以学生表(S表)为基础建立一个视图其名称为ldquo学生名单rdquo其包含列为学生表中的学号和姓名。程序清单如下:CREATEVIEW学生名单ASSELECTSNO,SNAMEFROMS从单个表派生的视图:使用CREATEVIEW语句创建视图例:以选课表(SC表)为基础建立一个视图其名称为ldquo数据库成绩rdquo其包含列为选课表中的所有列筛选记录条件为ldquo课程编号=lsquo#rdquo。程序清单如下:WITHCHECKOPTIONCREATEVIEW数据库成绩ASSELECT*FROMSCWHERECNO=##使用CREATEVIEW语句创建视图例:给出有北京仓库订购单的北京供应商的名称。WITHCHECKOPTION从多个表派生的视图:CREATEVIEWBJ订购单ASSELECT供应商名FROM供应商WHERE地址=#北京#AND供应商号IN(SELECT供应商号FROM订购单WHERE职工号IN(SELECT职工号FROM职工WHERE仓库号IN(SELECT仓库号FROM仓库WHERE城市=#北京#)))例:列出每个职工经手的具有最高总金额的订购单信息。订购单使用CREATEVIEW语句创建视图使用CREATEVIEW语句创建视图CREATEVIEWVMAXASSELECT职工号,MAX(总金额)AS最大总金额FROM订购单GROUPBY职工号SELECT订购单职工号,订购单号,供应商号,订购日期,总金额FROM订购单,VMAXWHERE订购单职工号=VMAX职工号AND订购单总金额=VMAX最大总金额视图中的虚列:或:CREATEVIEWVSALASSELECT职工号,工资as月工资,工资*as年工资FROM职工使用CREATEVIEW语句创建视图CREATEVIEWVSAL(职工号,月工资,年工资)ASSELECT职工号,工资,工资*FROM职工例:定义一个视图包含职工号、月工资、年工资。修改视图使用企业管理器使用ALTERVIEW语句其语法格式如下:ALTERVIEWviewname(column,n)ASselectstatementALTERVIEW学生名单ASSELECTSNAMEFROMS修改视图例:将视图ldquo学生名单rdquo修改为只包含列为学生表中的姓名。程序清单如下:WITHCHECKOPTION使用视图通过视图查询数据信息例:查询ldquo数据库成绩rdquo视图中全部的记录。SELECT*FROM数据库成绩使用视图通过视图修改数据在使用视图修改数据时要注意下列一些事项:不能在一个语句中对多个基表使用数据修改语句。如果要修改由两个或两个以上基表得到的视图必须进行多次修改每次修改只能影响一个基表。对于基表中需更新而又不允许空值的所有列它们的值在INSERT语句或DEFAULT定义中指定。这将确保基表中所有需要值的列都可以获取值。不能修改那些通过计算得到结果的列。在视图定义中使用了WITHCHECKOPTION子句则所有在视图上执行的数据修改语句都必须符合定义视图的SELECT语句中所设定的条件。在基表的列中修改的数据必须符合对这些列的约束条件如是否为空、约束、DEFAULT定义等。使用视图使用视图例:向ldquo数据库成绩rdquo视图中插入一条新的选课记录。学号为lsquorsquo,课程号为lsquorsquo,成绩为。INSERTINTO数据库成绩VALUES(##,##,)GO使用视图例:将ldquo数据库成绩rdquo视图中学生lsquorsquo的成绩提高。UPDATE数据库成绩SETSCORE=SCORE*WHERESNO=##INSERTINTO学生名单VALUES(##,#孟欢#)使用视图例:向学生名单视图中插入一条新记录学号:姓名:孟欢。使用视图CREATEVIEWSSVIEW(SNO,SAVG)ASSELECTSNO,AVG(SCORE)FROMSCGROUPBYSNOUPDATESSVIEWSETSAVG=WHERESNO=##例:功能?正确?使用视图例:删除ldquo数据库成绩rdquo视图中学号为ldquordquo的同学记录。DELETEFROM数据库成绩WHERESNO=##GO删除视图使用企业管理器删除视图使用DROPVIEW语句语法格式如下:DROPVIEW{view},n例:删除视图ldquo数据库成绩rdquo。DROPVIEW数据库成绩案例中的视图应用)建立计算机系(系部代码=lsquorsquo)学生视图该视图包含学生的学号、姓名和入学时间信息。)建立学生表中全体学生年龄的视图该视图包含学生的学号、姓名和年龄信息。涉及的表:学生(学号姓名性别出生日期入学时间系部代码))建立选修ldquo高等数学rdquo的学生的视图该视图包含学生的学号和姓名信息。)建立没有获得学分(成绩)的学生的视图该视图包含学生的学号、姓名、课程名和成绩信息。涉及的表:学生(学号姓名性别)课程(课程号课程名)选修(学号课程号成绩)案例中的视图应用案例中的视图应用建立计算机系(系部代码=lsquorsquo)学生视图该视图包含学生的学号、姓名和入学时间信息。CREATEVIEWV计算机系学生ASSELECT学号,姓名,入学时间FROM学生WHERE系部代码=##涉及的表:学生(学号姓名性别出生日期入学时间系部代码)案例中的视图应用建立学生表中全体学生年龄的视图该视图包含学生的学号、姓名和年龄信息。CREATEVIEWV学生年龄ASSELECT学号,姓名,YEAR(GETDATE())YEAR(出生日期)AS年龄FROM学生涉及的表:学生(学号姓名性别出生日期入学时间系部代码)案例中的视图应用建立选修ldquo高等数学rdquo的学生的视图该视图包含学生的学号和姓名信息。CREATEVIEWV选修高数ASSELECT学生学号,学生姓名FROM学生,课程,选修WHERE课程课程号=选修课程号AND选修学号=学生学号AND课程课程名=#高等数学#涉及的表:学生(学号姓名性别)课程(课程号课程名)选修(学号课程号成绩)案例中的视图应用建立没有获得学分(成绩)的学生的视图该视图包含学生的学号、姓名、课程名和成绩信息。CREATEVIEWV未获学分ASSELECTA学号,A姓名,C课程名,B成绩FROM学生AJOIN选修BONA学号=B学号JOIN课程CONB课程号=C课程号WHEREB成绩涉及的表:学生(学号姓名性别)课程(课程号课程名)选修(学号课程号成绩)

用户评论(0)

0/200

精彩专题

上传我的资料

每篇奖励 +2积分

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

资料评价:

/135
¥5.0 购买

意见
反馈

立即扫码关注

爱问共享资料微信公众号

返回
顶部