SQL-电子工业-耿文兰.ppt

SQLServer2000数据库管理与开发,电子工业出版社,课程要求,考试成绩评定考勤及纪律20%课堂测验及作业30%期末考试50%本课程学习技巧加强记忆充分思考加强上机实践理论联系实际,2,3,目录,第01章SQLServer2000概述第02章数据库管理第03章表及其应用第04章数据的查询与更新第05章使用视图和索引第06章Transact-SQL程序设计第07章使用规则、默认值与触发器第08章SQLServer中的安全管理第09章备份和还原第10章不同数据格式的转换,第01章SQLServer2000概述,1.1SQLServer2000简介1.2SQLServer2000的安装1.3启动和停止SQLServer服务1.4注册服务器和创建服务器组1.5SQLServer2000管理工具简介,总结,5,回忆以前学习的知识,生活中遇到的大量数据，需要解决哪些问题？数据保存、查询、排序、筛选、删除在Execl中学习的相关内容？数据清单、自动筛选和高级筛选、分类汇总、数据透视图和透视表Execl数据库,6,计算机化数据库的好处,数据结构化数据能够共享数据冗余度小，易扩充数据与程序的独立性较高对数据实行集中统一控制可以建立和遵循某些标准便于维护数据完整性能够实现数据的安全性,7,数据库的发展史,萌芽阶段文件系统使用磁盘文件来存储数据初级阶段第一代数据库出现了网状模型、层次模型的数据库中级阶段第二代数据库关系型数据库和结构化查询语言高级阶段新一代数据库“关系-对象”型数据库,8,SQLServer2000,MSSQLServer2000是一个大型的关系型数据库系统是一个多层的客户/服务器数据库系统采用请求-应答工作方式,9,SQLServer2000的版本,企业版最大支持32个CPU、64G内存的多处理器系统，适合大规模商业应用标准版最大支持4个CPU、2G内存的多处理器系统，适合中小规模商业应用个人版支持两个CPU的处理器系统，适合个人或小型工作组的实验应用开发版适合于开发人员应用评估版适合于开发人员体验SQLServer2000的新功能,安装SQLServer2000企业版,11,各版本关系,无缝迁移，100%的兼容性,12,SQLServer2000的新特性,与Internet集成可伸缩性和易用性企业级数据库功能易于安装、部署和使用数据仓库,13,安装SQLServer20004-1,以MicrosoftSQLServer2000Personal版本为例1.插入光盘SQLServer2000自动安装或双击光盘上的AUTORUN.EXE文件2.点击【安装SQLServer2000组件（C）】3.点击【安装数据库服务器（S）】,14,安装SQLServer20004-2,4.选中【本地计算机】选项。5.选择【创建新的SQLSERVER实例】项,15,安装SQLServer20004-3,6.在姓名后输入：“王斌”，在公司名称后输入：“*计算机工程有限公司”7.接受许可协议8.选中【服务器和客户端工具S】,16,安装SQLServer20004-4,9.选中【典型】安装方式10.选择【使用本地系统帐户】11.选择【混合模式（Windows身份验证和SQL身份验证）】，输入用户名“sa”和密码“sa”12.至此安装SQLSERVER2000全部结束,17,停止和启动SQLServer服务,在对SQLServer数据库进行任何操作之前，必须启动本地或远程SQLServer服务器使用服务管理器使用命令服务管理运行：NetStartmssqlserver暂停：Netpausemssqlserver继续：Netcontinuemssqlserver停止：Netstopmssqlserver,18,注册一个SQLServer4-1,在SQLServer企业管理器的菜单中选择操作，从菜单中选择【新建SQLServer注册】，进入到【欢迎使用注册SQLServer向导】对话框,19,注册一个SQLServer4-2,选择复选框中你要使用的一个或多个服务器。选择使用的身份认证方式SQLServer身份认证,20,注册一个SQLServer4-3,在SQLServer身份认证对话框中输入登录名和密码选择服务器组或创建服务器组,21,注册一个SQLServer4-4,整个注册过程完成,22,管理工具简介,服务管理器服务启动、暂停和停止管理企业管理器可以完成几乎所有管理工作查询分析器允许输入、执行SQL语句并返回结果客户端网络实用工具客户端数据库连接工具事件探查器数据库事件管理工具数据导入/导出工具用于数据的传递与转换,结构化查询语言SQLStructuredQueryLanguageSQL是RDBMS的标准语言，遵循ANSI(美国国家标准协会)所制定的标准。SQL分类数据查询语言（DQL）：Select*FROMABC数据操作语言（DML）：UpdateABCSETA=“CH”数据控制语言（DCL）：RevokeCreateTableFROMU1数据定义语言（DDL）：CreateTableABC通用命令语言（CCL）：DeclareCSCursorFor,SQL的分类,24,第01章总结,掌握概念关系数据库SQLServer2000的版本两种身份验证模式管理工具的使用服务管理器企业管理器查询分析器,25,早期数据保存方式计算机文件,可以以不同的文件格式进行数据保存今天人们依然采用文件方式保存一些数据,26,数据库模型,模型：事物抽象特征或者过程的描述数据模型描述用来存储数据的容器在该容器中存储和检索数据的过程数据库模型分类层次模型网状模型关系模型,27,75,3,86,2,98,1,成绩,学号,刘三阳,3,李东,2,张明,1,姓名,学号,关系模型,记录和记录之间通过属性之间的关系来进行连接保证数据独立性，并形成数据集之间的关系,28,基于关系模型的数据库,目前较流行的关系型数据库桌面数据库MSAccess大型网络数据库MSSQLServer、Oracle和Sybase、DB2等,29,请求-应答工作方式,数据库服务器,响应和提供数据,应用程序,数据库,操作和查询,30,SQLServer实例,安装SQLServer2000数据服务器就是安装SQLServer2000数据库引擎实例一台计算机上可安装多个SQLServer数据库引擎实例在单台计算机上可以运行的实例数目取决于可用资源，SQLServer2000最多支持16个实例SQLServer实例类型默认实例一台计算机上只能有一个默认实例默认实例可以是SQLServer的任何版本命名实例,31,SQLServer服务SQLServer代理服务MSDTC服务,每个SQLServer实例都有一个SQLServer服务，默认实例名为MSSQLServer,服务管理器,32,服务管理器的启动方法,托盘图标开始菜单程序组执行文件Sqlmangr.exe,33,企业管理器,在服务器节点上指示灯是绿色的，表示服务器连接成功,34,登录查询分析器,35,使用查询分析器,第02章数据库管理,2.1数据库系统概述2.2SQLServer数据库概念2.3系统数据库2.4创建用户数据库2.5查看数据库信息2.6设置数据库选项2.7修改数据库2.8删除数据库2.9数据库的分离与附加,总结,37,数据库概述,数据库就是按照一定的组织结构存储在计算机存储介质上的相关数据的集合数据库具有以下的特点结构化数据库文件及文件内部数据都有一定的结构独立性数据的逻辑组织与物理存储方式无关数据共享，减少冗余数据集中存储，统一管理安全性与完整性保证数据的正确性和可靠性,38,什么是DBMS？,数据库管理系统是用来帮助用户建立、使用和管理数据库的软件系统，简称DBMS数据库管理系统的功能建立：建立数据库结构，并存储用户数据操作：插入、修改、删除、检索、统计汇总、打印等其他：数据安全性、数据完整性、并发控制（在网络环境下多个用户同时对同一数据库中的数据进行存取的操作）等管理,39,数据库系统,数据库系统狭义地讲是由数据库管理系统和用户构成广义地讲是由计算机硬件、操作系统、数据库管理系统以及在它支持下建立起来的数据库、应用程序、用户和维护人员组成的一个整体,40,数据库系统的组成,计算机基本系统数据库管理系统数据库物理数据库有关应用所需要的工作数据的集合，是数据库的主体描述数据库关于各级数据结构的描述数据，通常由一个数据词典系统管理,41,SQLServer数据库,SQLServer2000是一种采用SQL语言的关系数据库管理系统，是以文件的形式存储在磁盘上的(以文件组形式进行组织管理)，数据库文件(使用便于管理的规则命名)根据作用不同分为三种,42,采用多个数据库文件来存储数据的优点,数据库文件可以不断扩充而不受操作系统文件大小的限制可以将数据库文件存储在不同的硬盘中，这样可以同时对几个硬盘做数据存取提高了数据处理的效率，对于服务器型的计算机尤为有用,43,数据库对象,数据库中的数据按不同形式组织在一起，构成了不同的数据库对象数据库对象在磁盘上没有对应的文件SQLServer2000中有以下数据库对象：表、视图、存储过程、触发器、用户自定义数据类型、用户自定义函数、索引、规则、默认、约束等,44,数据库文件组,将多个数据文件存放在不同的磁盘上并把多个数据文件组成一个或多个文件组数据库根据组内数据文件的大小，按比例地写入组内所有数据文件，使多个磁盘同时并行工作、提高读写速度在一个数据库中可以创建多个文件组，而一个数据文件只能属于一个文件组事务日志文件不能属于任何文件组一个数据文件或文件组只被一个数据库使用,45,数据库文件组示例,46,系统数据库2-1,系统数据库,示例数据库,47,系统数据库2-2,临时数据库，临时数据可被自动删除，不需要特殊权限，系统中仅有一个，空间不足可自动扩展,系统数据库,用户数据库,示例数据库,主控数据库，用于管理其他数据库和保存SQLServer系统信息,模板数据库，当创建一个用户数据库时其内容自动复制到新数据库中,来存储计划信息以及与备份和恢复相关的信息，SQLServer代理使用其安排报警、作业并记录操作员,48,系统表,SQLServer2000中的每个数据库都包含系统表系统表在创建数据库时自动产生系统表用来记录SQLServer组件所需的数据SQLServer的操作能否成功，取决于系统表中信息的完整性任何用户都不应直接修改系统表,49,master数据库中的系统表,50,常用数据库系统表,51,创建用户数据库,一个数据库是包含表、视图、存储过程及触发器等数据库对象的容器，在数据库中建立的各种数据库对象都保存在数据库文件中创建数据库之前应考虑好数据库的拥有者、数据库的初始容量、最大容量、增长量及数据库文件存放的路径等因素要创建数据库，用户必须具有相应的权限可以使用企业管理器、CREATEDATABASE语句和向导三种方法,52,使用企业管理器创建数据库,数据文件的逻辑名,数据文件的物理名,文件组,日志文件不属于任何文件组,53,使用语句创建数据库,CREATEDATABASE数据库名ONPRIMARY(NAME=数据文件的逻辑名称,FILENAME=数据文件的物理名称,SIZE=数据文件的初始大小,MAXSIZE=数据文件的最大容量,FILEGROWTH=数据文件的增长量),nLOGON(NAME=事务日志文件的逻辑名称,FILENAME=事务日志文件的物理名称,SIZE=事务日志文件的初始大小,MAXSIZE=事务日志文件的最大容量,FILEGROWTH=事务日志文件的增长量),n,例2.1,示例,54,使用向导创建数据库,完成后会询问“是否马上为新创建的数据库创建维护计划”。维护计划就是设置何时自动定期对数据库进行维护。,55,查看数据库信息,数据库信息主要包括三方面基本信息维护信息空间使用情况使用企业管理器查看数据库信息演示使用企业管理器查看数据库信息使用sp_helpdb查看数据库信息语法：executesp_helpdb数据库名若省略数据库名，与“select*fromsysdatabases”语句的功能完全相同,56,使用语句查看数据库信息,使用sp_databases查看所有可用数据库信息语法：EXECUTEsp_databases使用sp_helpfile查看当前数据库中某个文件信息语法：EXECUTEsp_helpfile文件名省略文件名则显示当前数据库中所有文件的信息使用sp_helpfilegroup查看当前数据库中文件组信息语法：EXECUTEsp_helpfilegroup文件组名省略文件组名则显示当前数据库中所有文件组信息，用法同sp_helpfile,57,设置数据库选项,仅允许特殊用户访问数据库,数据库只能读取，不能修改,当最后一个用户退出，SQLServer将自动关闭该数据库并释放其所占的系统资源,数据库文件中未使用空间超过文件大小的25%，将自动缩小空间，缩小后数据库文件的大小不能小于初始大小。设置只读时，此选项无效,对数据库访问权限进行设置和修改,58,使用存储过程配置数据库特性,executesp_dboption数据库名,选项名,选项值,设置数据库为只读,EXECsp_dboptionpubs,readonly,True,EXECsp_dboptionpubs,autoshrink,true,EXECsp_dboptionstudents,singleuser,true,设置数据库为自动压缩,设置数据库为单用户(例2.3),EXECsp_dboptionstudents,查看数据库students所有设置为true的选项(例2.4),使用企业管理器修改数据库,操作方法和创建数据库大体相同注意问题在修改文件的“分配的空间”项时，所改动的值必须大于现有的空间值若要通过删除未用空间来缩小数据库文件的容量，先选择要缩小的数据库，然后在“收缩数据库”对话框中改变其容量值,59,60,使用语句修改数据库,ALTERDATABASE数据库名ADDFILETOFILEGROUP文件组|ADDLOGFILE|REMOVEFILE逻辑文件名|ADDFILEGROUP文件组名|REMOVEFILEGROUP文件组名|MODIFYFILE|MODIFYFILEGROUP文件组名，文件组属性,例2.5,示例,61,删除数据库,删除一个数据库会删除该数据库中的所有对象，从而释放出该数据库所占用的磁盘空间注意当数据库处于正在使用、正在被恢复和正在参与复制三种状态之一时，不能删除该数据库若要删除的数据库正在被其他用户所使用，则可以先断开服务器与该用户的连接，然后删除该数据库系统数据库中的master、model和tempdb不能被删除，msdb虽然可以被删除，但删除msdb后很多服务(比如SQLServer代理服务)将无法使用，因为这些服务在运行时会用到msdb,62,使用企业管理器删除数据库,63,使用语句删除数据库,64,关于数据库的命令小结,创建数据库,createdatabase,alterdatabase,dropdatabase,修改数据库,删除数据库,sp_helpdb,查看数据库定义信息,sp_dboption,查看/修改数据库设置,65,数据库与系统的分离,分离数据库实际上是从SQLServer系统中删除数据库组成该数据库的数据文件和事务日志文件依然完好无损的保存在磁盘上注意只有固定服务器角色成员sysadmin才可执行分离操作系统数据库master、model和tempdb数据库无法从系统分离出去,若要执行分离操作，必须先单击“清除”按钮清除连接,66,将数据库附加到系统,使用这些数据文件和事务日志文件可以将数据库再附加到任何SQLServer系统中，而且数据库在新系统中的使用状态与它分离时的状态完全相同附加数据库只需要指定要附加到SQLServer系统的数据库主数据文件的路径与文件名,67,语句实现数据库的分离与附加,分离sp_detach_db数据库名称,TRUE|FALSE附加sp_attach_db数据库名称,数据库文件名，16dbname必须是唯一的filename数据库文件的物理名称，包括路径最多可以指定16个文件名文件名列表至少必须包括主文件可使用此方法对现有数据库制作多个副本，也可以实现在不同SQLServer服务器之间移动传递数据的目的,68,第02章总结,掌握概念数据库数据库管理系统数据库系统数据库文件数据库对象文件组系统数据库应用技能用户数据库的创建、修改、删除数据库信息的查看、数据库参数配置数据库与系统的分离与附加,课后作业,69,例2.1,用语句创建一个名为students的数据库，包含一个主数据文件和一个事务日志文件。这两个文件都放在d:data目录中主数据文件逻辑文件名为studentdata，物理文件名为student_data.mdf，初始容量大小为10MB，最大容量为50MB，每次增长量为25%事务日志文件的逻辑文件名为studentlog，物理文件名为student_log.ldf，初始容量大小为10MB，最大容量为不受限制，每次增长量为2MB,CREATEDATABASEstudentsONPRIMARY(NAME=studentdata,FILENAME=d:datastudent_data.mdf,SIZE=10,MAXSIZE=50,FILEGROWTH=25%)LOGON(NAME=studentlog,FILENAME=d:datastudent_log.ldf,SIZE=10MB,MAXSIZE=unlimited,FILEGROWTH=2MB),70,示例,用T-SQL语句在C:DATA与D:DATA文件夹中创建一个教师信息数据库teacher，该数据库包含一个主数据文件逻辑名teacherdata1，物理名C:DATAtdata1.mdf,初始容量1MB，最大容量10MB，每次增长量为15%一个辅助数据文件逻辑名teacherdata2物理名D:DATAtdata2.ndf,初始容量2MB，最大容量15MB，每次增长量为2MB两个数据文件不单独创建文件组，即使用默认的PRIMARY组一个事务日志文件逻辑名teacherlog物理名D:DATAteacherlog.ldf，初始容量512KB，最大容量不受限制，每次增长量为512KB,CREATEDATABASEteacherONPRIMARY(NAME=teacherdata1,FILENAME=C:DATAtdata1.mdf,SIZE=1,MAXSIZE=10,FILEGROWTH=15%),(NAME=teacherdata2,FILENAME=D:DATAtdata2.mdf,SIZE=2,MAXSIZE=15,FILEGROWTH=2)LOGON(NAME=teacherlog,FILENAME=D:DATAteacherlog.ldf,SIZE=512kb,FILEGROWTH=512kb),SQL语句在书写时不区分大小写，一条语句可以写在多行上，但不能将多条语句写在一行上。,71,文件格式,(NAME=数据文件的逻辑名称,FILENAME=数据文件的物理名称,SIZE=数据文件的初始大小,MAXSIZE=数据文件的最大容量|unlimited,FILEGROWTH=数据文件的增长量),在修改文件的“分配的空间”项时，所改动的值必须大于现有的空间值文件的物理文件名不允许进行修改，所以请勿指定物理名称,72,例2.5,用语句向students数据库添加一个名为studentsfilegroup的文件组，并将student_dataadd.ndf添加到此文件组中,-添加文件组ALTERDATABASEstudentsADDFILEGROUPstudentsfilegroupgo-添加数据文件，并将其加入新文件组ALTERDATABASEstudentsADDFILE(NAME=studentdataadd,FILENAME=d:datastudent_dataadd.ndf)TOFILEGROUPstudentsfilegroupgo-查看数据库信息executesp_helpdbstudentsgo,73,示例,修改students数据库原有主数据文件studentsdata初始大小改为2MB按2MB自动增长最大容量20MB,ALTERDATABASEstudentsMODIFYFILE(NAME=studentsdata,SIZE=2,MAXSIZE=20,FILEGROWTH=2),74,CREATEDATABASEtestONPRIMARY(NAME=testdat1,FILENAME=d:datatestdat1.mdf,SIZE=1,MAXSIZE=10,FILEGROWTH=1),(NAME=testdat2,FILENAME=d:datatestdat2.ndf,SIZE=1,MAXSIZE=10,FILEGROWTH=1)LOGON(NAME=testlog1,FILENAME=d:datatestlog1.ldf,SIZE=512KB,MAXSIZE=5,FILEGROWTH=512KB),2.8,第03章表及其应用,3.1表概述3.2创建表3.3查看表信息3.4修改表3.5删除表,总结,76,表的概念,表是相关联的行列集合表是按照行列结构存储数据的描述一个个体属性的总和称为一条记录描述个体的一个属性称为一个字段，也称数据项结构和数据记录是表的两大组成部分,77,表的示例,行/记录,列/属性/字段/数据项,78,创建表的限制,每个数据库里最多有20亿个表每个表上最多可创建一个聚集索引，249个非聚集索引每个表最多可以设置1,024个字段每条记录最多占8,060B，但不包括text字段和image字段,79,创建表前的考虑,数据库中要存放哪些数据，这些数据如何划分到表中确定每个表需要哪些列，即决定表所包含的属性有哪些,80,数据类型,数据类型是用来表现数据的特性的，表中的每列数据必须是同一种类型的数据，列的数据类型确定了该列数据的取值范围SQLServer的数据类型包括两类系统定义的数据类型用户定义的数据类型(第07章介绍),81,数据的完整性,数据完整性,正确性,+,一致性,=,表中所有行唯一，即所有记录都是可以区分的,表中指定列的数据具有正确的数据类型、格式和有效的数据范围,有关联的两个或两个以上表之间数据的一致性。可以通过建立主键和外键约束来实现。,82,约束,对输入数据取值范围和格式的限制称为约束约束是用来保证数据完整性的六种约束主键约束(PRIMARYKEY)唯一约束(UNIQUE)外键约束(FOREIGNKEY)检查约束(CHECK)默认值约束(DEFAULT)空值约束(NULL),83,数据库完整性的实现,84,创建表,每个表必须有一个主键字段同一表中不允许重名字段每个表必须描述的是一个实体的信息字段必须符合命名规则表必须是符合数据规范化要求的,85,使用企业管理器创建表,86,customers表,87,goods表,88,orders表,89,设置约束,主键约束唯一约束外键约束检查约束,90,使用语句创建表,CREATETABLE表名(列名列属性列约束,n|字段名AS计算列),CREATETABLECategories(CategoryIdintIDENTITY(1,1)primarykey,CategoryNamenvarchar(15)NOTNULL,DescriptionntextNULL,PictureimageNULL),例3.1,例3.2,例3.3,表级约束,练习,91,查看表信息,查看表定义信息即查看系统表sysobjects和syscolumns中记录查看方法通过企业管理器的“表属性”窗口executesp_help表名查看表约束通过企业管理器的“表属性”窗口查看表中的数据查看表之间的依赖关系使用企业管理器：所有任务显示相关性,92,修改表,ALTERTABLE表名ADD列名数据类型(长度)NULL|NOTNULL|ALTERCOLUMN列名数据类型(长度)NULL|NOTNULL|DROPCOLUMN字段名,n|ADDCONSTRAINT约束定义,n|DROPCONSTRAINT约束名,n|NOCHECKCONSTRAINT约束名|CHECKCONSTRAINT约束名,93,使用ADD子句添加列,ALTERTABLE表名ADD列名数据类型(长度)NULL|NOTNULL若向已存在记录的表中添加列，新添加的列可以设置允许为空；若不允许为空，则需指定默认值,例3.4,94,使用ADDCONSTRAINT子句添加约束,ALTERTABLE表名ADDCONSTRAINT约束定义,例3.5,级联引用完整性,95,使用DROPCONSTRAINT子句删除约束,ALTERTABLE表名DROPCONSTRAINT约束名,例3.6,96,使用DROPCOLUMN子句删除列,ALTERTABLE表名DROPCOLUMN字段名,在删除列时，必须先删除基于该列的索引和约束后，才能删除该列,例3.7,97,使用ALTERCOLUMN子句修改列属性,ALTERTABLE表名ALTERCOLUMN列名数据类型(长度)NULL|NOTNULL在默认状态下，列可以被设置为空值，将一个原来允许为空值的列改为不允许为空值时，必须满足列中没有存放空值的记录以及在列上没有创建索引,例3.8,98,使用NOCHECKCONSTRAINT子句使约束无效,ALTERTABLE表名NOCHECKCONSTRAINT约束名|CHECKCONSTRAINT约束名该子句只对外键约束和检查约束起作用,例3.9,99,删除表,DROPTABLE表名,ndroptable语句不能删除系统表如果一个表被其他表通过外键约束引用，那么必须先删除设置了外键约束的表，或删除其外键约束,100,第03章总结,掌握概念表数据类型数据完整性约束应用企业管理器操作创建表、修改表结构、删除表查看表数据查看表约束命令CreatetableAltertableDroptable,课后作业,上机作业,101,系统定义的数据类型,统一字符型,精确小数,近似数值,char(2)nchar(2),decimal(5,2),0/1以外的任何值都会作为1来处理,字符型需用引号引起来,需用引号引起来,存储图像等数据,带有4位小数的decimal类型的数据,102,主键约束,每个表仅能定义一个主键主键值是表中记录的标识主键列可以有一个或多个列组合而成主键值不可为空(null)主键值不能重复image和text类型的列不能做主键,103,唯一约束,是一列或几列的组合用于保证非主键的不可重复字段可以为表定义多个唯一约束只能为表定义一个主键约束该列允许为空值唯一约束可以定义在允许空值的列上主键约束只能定义在不允许为空值的列上唯一约束不改变字段的物理存储位置记录按主键值的指定顺序存储,104,外键约束,设置外键约束字段的表称为子表，它所引用的表称为父表外键可以是单一字段，也可以是多个字段的组合外键所引用父表中的字段必须是创建了主键约束或唯一约束的列子表中外键字段添加的新数据，必须在父表存在子表中引用父表数据的记录未删除，父表中被引用数据不能被删除,105,外键约束示例,子表,记录,如果数据项不在父表中,添加,父表,如果导致子表产生孤立记录,删除,106,检查约束,用指定的条件检查限制输入数据的取值范围是否正确用于保证数据的参照完整性和域完整性如：成绩必须0,107,默认值约束,默认值约束是给某个字段绑定一个默认的初始值输入记录时若没有给出该字段的数据，则自动填入默认值以保证数据的域完整性对设置了默认值约束的字段若输入数据时则以输入的数据为准可为常量、内置函数或表达式,108,空值约束,空值NULL是不知道或不能确定的特殊数据不等同于数值0和字符的空格空值约束就是设置某个字段是否允许为空用以保证数据的实体完整性和域完整性,109,数据规范化,从关系型数据库的表中除去冗余数据的过程规范化是得到高效的关系型数据库表的逻辑结构最好和最容易的方法规范化数据时，应执行以下操作将数据库的结构精简为最简单的形式从表中删除冗余列标识所有依赖于其他数据的数据,110,范式,构造数据库必须遵循一定的规则，在关系数据库中，这种规则就是范式公认的规范化等级有五种1NF2NF3NF等只要符合第三范式的数据就可以称为完全规范化的数据,111,1NF,所有属性必须只有一个值修正方法示例：运动队（标识、队名、队员）,112,2NF,任何非主属性都必须依赖于完整的主属性特别适用于复合主属性的情况修正方法示例：产品供应情况（供应商编号、产品编号、购买价格、供应商名称）,113,3NF,非主属性不能依赖于另一非主属性修正方法示例：城市（城市标识、城市名称、所在省份、省长姓名）,114,列属性,数据类型(长度)NULL|NOTNULLIDENTITY(初始值,步长值),115,列约束,CONSTRAINT约束名PRIMARYKEY(列名)CONSTRAINT约束名UNIQUE(列名)CONSTRAINT约束名FOREIGNKEY(外键列)REFERENCES引用表名(引用列)ONDELETECASCADE|NOACTIONONUPDATECASCADE|NOACTIONCONSTRAINT约束名CHECK(检查表达式)CONSTRAINT约束名DEFAULT默认值,116,列约束说明,约束名为以后修改管理时使用，省略为系统默认的约束名字段约束也可以在创建表结构以后另外单独设置定义字段时同时定义所绑定的约束时，可以省略列名,117,计算列示例,createtableorders1(订单号intidentity(1,1)primarykey,货品名称varchar(20)notnullreferencesgoods(货品名称),客户编号intforeignkeyreferencescustomers(客户编号),数量intnotnullcheck(数量0),价格money,总金额as数量*价格,订货日期datetimedefaultgetdate(),118,例3.1,用语句创建customers表，记录客户的基本信息表中包含如下内容：客户编号、姓名、地址、电话和出生日期设置客户编号字段为主键约束，电话字段为唯一约束，姓名字段不能为空,CREATETABLEcustomers(客户编号intPRIMARYKEY,姓名varchar(20)NOTNULL,地址varchar(50),电话varchar(20)UNIQUE,出生日期datetime),119,例3.2,用语句创建goods表，记录货品的基本信息表中包含如下内容：货品名称、库存量、供应商、状态和价格设置货品名称字段为主键约束设置状态字段为bit类型,CREATETABLEgoods(货品名称varchar(20)PRIMARYKEY,库存量int,供应商varchar(50),状态bitDEFAULT0,价格money),120,例3.3,用语句创建orders表，记录客户订购货品的订单信息表中包含内容：订单号、货品名称、客户编号、数量、总金额和订货日期设置订单号字段为自动编号，初始值为1，递增量为1，同时设置为主键客户编号字段为外键约束数量字段检查约束为数量大于0，同时不能为空订货日期默认值，为系统日期,CREATETABLEorders(订单号intIDENTITY(1,1)PRIMARYKEY,货品名称varchar(20)NOTNULLREFERENCESgoods(货品名称),客户编号intFOREIGNKEYREFERENCEScustomers(客户编号),数量intNOTNULLCHECK(数量0),总金额money,订货日期datetimeDEFAULTgetdate(),121,约束的两种定义方式,列级约束列约束内嵌在列的声明中定义，作为列定义的一部分只作用于此列本身放在列名和数据类型之后的位置，即逗号之前表级约束表级约束作为表定义的一部分，可以作用于多个列，且必须将多列约束定义为单独的表元素作为独立的由逗号分隔的语句数据库服务器以同样的方式处理列级和表级约束，涉及到多列的约束必须作为表级约束处理,122,列级约束和表级约束,CREATETABLEorders(订单号intIDENTITY(1,1),货品名称varchar(20)NOTNULLREFERENCESgoods(货品名称),客户编号intFOREIGNKEYREFERENCEScustomers(客户编号),数量intNOTNULLCHECK(数量0),总金额money,订货日期datetimeDEFAULTgetdate(),CONSTRAINTpk_ordersPRIMARYKEY(订单号),123,图书信息管理数据库,124,创建借阅信息表,Createtable借阅信息表(图书编号char(10)notnullforeignkeyreferences图书信息表(图书编号),读者编号char(8)notnullreferences读者信息表(读者编号,借阅时间datetimeconstraintdf_datedefaultgetdate(),归还时间datetimenull,是否超期bitdefault0,超期罚款moneynull,constraintpk_jieyueprimarykey(图书编号,读者编号),列级约束,表级约束,125,设置主键约束,126,设置唯一约束,127,设置外键约束,级联引用完整性,128,设置检查约束,129,例3.4,向customers表中添加“电子邮件”列，其数据类型为varchar，长度为50，不允许空,USEmarketALTERTABLEcustomersADD电子邮件varchar(50)NOTNULLDEFAULT,130,例3.5,将customers表中“电子邮件”列的默认值设置为cqdx,USEmarketALTERTABLEcustomersADDCONSTRAINTdef_eDEFAULTcqdxFOR电子邮件,131,DEFAULT约束,132,CHECK约束,133,PRIMARYKEY约束,134,UNIQUE约束,135,FOREIGNKEY约束,136,级联引用完整性,可以在定义FOREIGNKEY约束时的REFERENCES子句中使用ONDELETE和ONUPDATE子句，这些子句用于指定CASCADE或NOACTION选项CASCADE选项允许对已定义UNIQUE或PRIMARYKEY约束的列值进行任何更改，列值会自动把这些更改传送给外键值NOACTION（默认选项）选项则限制任何试图删除或更新由其他表的外键引用的关键字的操作,137,级联引用完整性示例,重新创建orders表当customers表的客户编号改变时，自动修改orders表中记录的客户编号，但不允许删除客户当goods表中的货品删除时，自动删除orders表中对应货品的订货记录，但不允许修改货品名称,CREATETABLEorders(订单号intIDENTITY(1,1)PRIMARYKEY,货品名称varchar(20)NOTNULLREFERENCESgoods(货品名称)ONDELETECASCADE,客户编号intFOREIGNKEYREFERENCEScustomers(客户编号)ONUPDATECASCADE,数量intNOTNULLCHECK(数量0),总金额money,订货日期datetimeDEFAULTgetdate(),138,例3.6,删除customers表中“电子邮件”列上创建的默认值约束def_e,USEmarketALTERTABLEcustomersDROPCONSTRAINTdef_e,139,例3.7,删除customers表中的“电子邮件”字段,USEmarketALTERTABLEcustomersDROPCOLUMN电子邮件,140,例3.8,将customers表的“电子邮件”列改为最大长度为50的char型数据，且允许为空值,USEmarketALTERTABLEcustomersALTERCOLUMN电子邮件char(50)NULL,141,例3.9,使customers表的“电子邮件”列创建的约束无效或重新生效,USEmarketALTERTABLEcustomersADDCONSTRAINTchk_eCHECK(电子邮件LIKE%)ALTERTABLEcustomersNOCHECKCONSTRAINTchk_eALTERTABLEcustomersCHECKCONSTRAINTchk_e,142,3.93.10-0,创建“学生档案”数据库,CREATEDATABASE学生档案ONPRIMARY(NAME=studentdata,FILENAME=d:datastudentdata.mdf,SIZE=1,MAXSIZE=5,FILEGROWTH=10%)LOGON(NAME=studentlog,FILENAME=d:datastudentlog.ldf,SIZE=1,MAXSIZE=5,FILEGROWTH=10%),143,3.9,CREATETABLE人事表(学号INTIDENTITY(2001,1),姓名CHAR(8),性别CHAR(2),电话号码VARCHAR(20),家庭住址VARCHAR(50)CREATETABLE成绩表(学号INT,选修课程VARCHAR(20),成绩DECIMAL(4,1),144,3.10,ALTERTABLE成绩表ADDCONSTRAINTfk_xuehaoFOREIGNKEY(学号)REFERENCES人事表(学号),CONSTRAINTck_checkCHECK(成绩=0AND成绩=0and成绩=!=!，=，!=，!，!=100,207,例4.8,查询goods表中库存量不在100500之间的货品记录,USEmarketSELECT*FROMgoodsWHERE库存量NOTBETWEEN100AND500,208,例4.9,查询customers表中姓名是王兰、李红的客户信息,USEmarketSELECT*FROMcustomersWHERE姓名IN(王兰,李红),209,例4.10,查询customers表中姓李的客户信息,USEmarketSELECT*FROMcustomersWHERE姓名LIKE李%,210,例4.11,查询goods表中不知道供应商名称的货品信息，即供应商列为空值,USEmarketSELECT*FROMgoodsWHERE供应商ISNULL,211,例4.12,用两种方法查询goods表中库存量在1001000之间的货品记录,USEmarketSELECT*FROMgoodsWHERE库存量BETWEEN100AND1000SELECT*FROMgoodsWHERE库存量=100AND库存量=30,218,例4.17,统计orders表各种货品订货总数量，并给出明细表,USEmarketSELECT*FROMordersORDERBY货品名称COMPUTESUM(数量)BY货品名称,219,例4.18,将customers表和orders表进行交叉连接，观察结果,USEmarketSELECT*FROMcustomersCROSSJOINordersSELECT*FROMcustomers,orders,220,交叉连接示例,USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyers,sales,221,例4.19,用内连接方法连接customers和orders两个表，观察连接后的结果,SELECTc.客户编号,c.姓名,o.数量,o.总金额FROMcustomersAScINNERJOINordersASoONc.客户编号=o.客户编号SELECTc.客户编号,c.姓名,o.数量,o.总金额FROMcustomersASc,ordersASoWHEREc.客户编号=o.客户编号,222,内连接示例,USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_id,223,例4.20,使用左外连接查询goods表和orders表，获取存在订货的货品名称、订货日期和订货数量,USEmarketSELECTgoods.货品名称,订货日期,数量FROMgoodsLEFTJOINordersONgoods.货品名称=orders.货品名称,224,左外连接示例,USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersLEFTOUTERJOINsalesONbuyers.buyer_id=sales.buyer_id,225,例4.21,使用右外连接查询goods表和orders表，获取存在订货的货品名称、订货日期和订货数量,USEmarketSELECTgoods.货品名称,订货日期,数量FROMordersRIGHTJOINgoodsONgoods.货品名称=orders.货品名称,226,例4.22,使用全外连接查询goods表和orders表，获取存在订货的货品名称、订货日期和订货数量,USEmarketSELECTgoods.货品名称,订货日期,数量FROMgoodsFULLOUTERJOINordersONgoods.货品名称=orders.货品名称,227,例4.24,查询orders表中订购了两种或两种以上货品的客户编号和他所订购的货品名称,USEmarketSELECTa.客户编号,a.货品名称FROMordersASaJOINordersbONa.客户编号=b.客户编号ANDa.订单号b.订单号,228,自连接示例,USEjoindbSELECTa.buyer_idASbuyer1,a.prod_id,b.buyer_idASbuyer2FROMsalesASaJOINsalesASbONa.prod_id=b.prod_idWHEREa.buyer_idb.buyer_id,229,例4.25,从表customers中检索出地址为上海的客户输出到表shangh，地址为北京的客户输出到beij，然后将两表合并为一个结果集,SELECT*INTOshanghFROMcustomersWHERE地址LIKE上海%SELECT*INTObeijFROMcustomersWHERE地址LIKE北京%SELECT*FROMshanghUNIONSELECT*FROMbeij,230,嵌套子查询和相关子查询,嵌套子查询总是最先执行最内层查询相关子查询的执行过程依赖于值的外部查询。相关子查询的WHERE子句引用外部查询的FROM子句中的表。这就是说子查询是以循环的方式进行处理的对外部查询选择的每行都执行一次,231,例4.26,从goods表中检索数据，列出高于平均价格的货品信息,SELECT*FROMgoodsWHERE价格(SELECTAVG(价格)FROMgoods),232,例4.27,从orders表中检索数据，以查询有订单的客户信息,SELECT*FROMcustomersWHERE客户编号IN(SELECT客户编号FROMorders),233,例4.28,从orders表中检索数据，以查询有订单的客户信息,SELECT*FROMcustomersWHEREEXISTS(SELECT*FROMordersWHEREcustomers.客户编号=orders.客户编号),234,例4.29,用IN子句完成上例的操作,SELECT*FROMcustomersWHERE客户编号IN(SELECT客户编号FROMorders),235,例4.30,从customers表中检索数据，以查询一次订货数量大于等于20的客户编号和姓名,SELECTa.客户编号,a.姓名FROMcustomersaWHERE20=ALL(SELECT价格FROMgoods),237,例4.32,向orders表添加一条新记录，给所有列提供列值,INSERTordersVALUES(book,1,10,250,1/23/2003)INSERTordersVALUES(book,1,10,250,DEFAULT)由于订货日期设定了默认值，所有可以使用默认值,字符型和日期型数据需要用单引号括起来,238,例4.33,创建一个与customers表结构一样的表cq，使该表中仅有重庆客户的信息,SELECT*INTOcqFROMcustomersWHERE2=4INSERTcqSELECT*FROMcustomersWHERE地址LIKE重庆%,239,例4.34,将goods表中的库存量大于100的货品的定价降低10%,UPDATEgoodsSET价格=价格*0.9WHERE库存量=100,240,例4.35,把orders表中的“总金额”用该货品在goods表中的“价格”与在orders表中的订货“数量”的乘积代替，并显示修改后的记录,UPDATEordersSET总金额=价格*数量FROMgoodsWHEREorders.货品名称=goods.货品名称SELECTa.货品名称,a.价格,b.数量,b.总金额FROMgoodsASa,ordersASbWHEREa.货品名称=b.货品名称,241,例4.36,删除goods表中的所有记录,DELETEgoods,242,例4.37,删除goods表中的未知定价的记录,DELETEgoodsWHERE价格ISNULL,243,例4.38,删除orders表中地址为重庆的客户订货记录,DELETEordersFROMcustomersWHEREorders.客户编号=customers.客户编号AND地址LIKE重庆%,244,例4.39,删除orders表中的所有订单,TRUNCATETABLEorders,245,删除表和删除表数据的区别,DROPTABLE、DELETE和TRUNCATETABLE语句的区别DROPTABLE语句不仅删除表数据，还将删除表结构DELETE和TRUNCATETABLE语句只删除表数据不含WHERE子句的DELETE语句的作用与TRUNCATETABLE语句一样，将删除表中所有数据，但TRUNCATETABLE语句的执行速度更快DELETE语句可通过WHERE子句实现部分表数据删除，而TRUNCATETABLE语句不能包含WHERE子句，即不能删除部分数据,246,学生信息管理数据库,247,查询条件2-1,检索“基本信息表”中