《建立数据库和表》PPT课件.ppt

Ch2建立数据库,1,本章内容,1.建立数据库,2,SQLserver2008客户端工具,SQLServerManagementStudio是为SQLServer数据库管理员和开发人员提供的新工具。它提供了用于数据库管理的图形工具和功能丰富的开发环境。完成数据库管理与维护的大部分工作。如配置系统环境和管理SQLServer服务器，显示、建立、管理所有的SQLServer对象，备份和恢复数据库，复制数据库，管理用户帐户等。输入和执行Transact-SQL语句，并且迅速查看这些语句的结果，以分析和处理数据库中的数据。,3,SQLserver2005系统数据库简介,1).master(主要的)是系统最重要的数据库，它记录了SQLServer系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQLServer的初始化信息和其它系统及用户数据库的相关信息。建议不要在master数据库中创建任何用户对象。如表、视图、存储过程或触发器等。2).model（模型的）是所有用户数据库和tempdb（临时）数据库的模板数据库，它含有master数据库所有系统表的子集，这些系统表是每个用户定义数据库需要的。,4,3).msdb是代理服务数据库，用于为警报、任务调度和记录操作员的操作提供存储空间。4).tempdb（临时）用于为所有的临时表、临时存储过程提供存储空间，还用于任何其它的临时存储要求。例如存储SQLServer生成的工作表。tempdb数据库是全局资源，所有连接到系统的用户的临时表和存储过程都存储在该数据库中。在SQLServer每次启动时都重新创建，因此该数据库在系统启动时总是干净的，临时表和存储过程在连接断开时自动除去。,5,自带的示例数据库,5).AdventureWorks数据库（示例）AdventureWorks数据库:以一家大型跨国生产公司为模型，该公司生产金属和复合材料的自行车，产品远销北美、欧洲和亚洲市场。该数据库及其中的表经常在帮助文档和教程所介绍的示例中使用。默认情况下不安装AdventureWorks数据库，可以从Microsoft下载中心下载并安装。,6,1.建立数据库,SQLServer中每个数据库由一组操作系统文件组成。数据库中的所有数据、对象和数据库操作日志都存储在这些文件中。数据库文件可以分为三类：主数据库文件(.mdf)次数据库文件(.ndf)事务日志文件(.ldf),7,1.1数据库文件,主数据库文件(PrimaryDatabasefile)每个数据库有且仅有一个主数据库文件，主数据库文件用来存储数据库的启动信息和部分或全部数据。一个数据库可以有1到多个数据库文件，其中只有一个文件为主数据库文件。主数据库文件的文件扩展名为mdf，如：try1.mdf。次数据库文件(SecondaryDatabaseFile)用于存储主数据库文件中未存储的剩余数据和数据库对象。一个数据库可以没有次数据库文件，也可以有多个次数据库文件。次数据库文件的文件扩展名为ndf如：try1_2.ndf。事务日志文件(TranscationLogFile)用于存储数据库的更新情况等事务日志信息。当数据库损坏时，可以利用事务日志文件恢复数据库。一个数据库可以有1到多个事务日志文件。事务日志文件的扩展名为ldf如：try1_log.ldf。,8,1.2创建数据库,创建数据库的过程实际上是确定数据库的名称、设计数据库所占用的存储空间和文件的存放位置。创建数据库的途径:SQLServerManagementStudio,两种方法,使用管理工具,使用Transact-SQL语句,9,使用管理工具创建数据库,创建mydb1数据库，文件存放在C盘db下,10,数据库名称数据库的所有者,默认为登录用户,数据库文件存储位置,添加此数据库文件,11,使用SQL命令CREATEDATABASEmydb2ONPRIMARY-在主文件组创建,PRIMARY可以省略(NAME=mydb2,FILENAME=c:dbmydb2.mdf,SIZE=3072KB,FILEGROWTH=1024KB)LOGON-创建日志文件(NAME=mydb2_log,FILENAME=c:dbmydb2_log.ldf,SIZE=1024KB,FILEGROWTH=10%),创建mydb2数据库，文件存放在C盘db下,12,练习：,1、创建一个mydb1数据库，位置“D:mydb1”,初始容量8M，最大容量16M，文件增长量为5%。CREATEDATABASEmydb1ON(NAME=mydb1,FILENAME=d:mydb1.mdf,SIZE=8MB,MAXSIZE=16MB,FILEGROWTH=5%)2、增加一个“D:mydb_log1”日志文件，要求初始容量为2MB,最大容量50MB，文件增长量10%。CREATEDATABASEmydb_log1ADDLOGFILE(NAME=mydb_log1,FILENAME=D:mydb_log1,SIZE=2MB,MAXSIZE=50MB,FILEGROWTH=10%),13,1.3数据库包含的数据对象,在一个SQLServer数据库中，除了包含基本表以外，还包含了与这些基本表相关的多种对象，如：视图、索引、存储过程和触发器等等。目的是为执行与数据有关的活动提供支持。,14,1.表在数据库中，所有的数据存放在表中，表由行(记录)和列(字段)组成，一个数据库可以包含多个表。2.视图视图是由查询数据表产生的结果，是一种虚构的表。视图把表中的部分数据映射出来供用户使用，这样可以防止所有的用户直接对表进行操作而导致系统的性能和安全性的下降。3.存储过程一个存储过程实际上是由一组SQL语句组成的完成特定功能的程序。存储过程在服务器端被编译后可以反复执行。4索引索引是对表中的一个或多个列的值进行排序的结构。可以利用索引提高对数据库表中的特定信息的访问速度。,15,5.约束约束是一种定义自动强制数据库完整性的方式。约束定义了关于列中允许值的规则。例如，强制定义某成绩列的值只能在0到100之间。6.默认值如果在插入行时没有指定该行中某列的值，那么使用默认值可以指定该列自动使用的值。例如，定义某“性别”列的默认值为“男”，则插入某学生信息时，如果没有指定其性别，则自动采用定义的默认值“男”。7.触发器触发器由一组SQL语句组成，当对表或视图进行某种操作（添加、删除或修改）时，这组命令会自动执行。,16,8.用户用于定义允许访问当前数据库的用户及其权限。9.角色角色定义了一组具有相同权限的用户。,17,整数型bit1或0的整数数据。Tinyint（极小整数）从0到255的整数数据。Smallint（小整数）从-215(-32,768)到215-1(32,767)的整数数据。int从-231(-2,147,483,648)到231-1(2,147,483,647)的整型数据（所有数字）。bigint从-263(-9223372036854775808)到263-1(9223372036854775807)的整型数据（所有数字）。,2、数据类型,18,浮点数型：decimal（小数）从-1038+1到10381的固定精度和小数位的数字数据。例如：decimal(5,2)总长度5位（不含小数点），2位小数。numeric（数值）功能上等同于decimal。货币型：Money（货币）货币数据值介于-263(-922,337,203,685,477.5808)与263-1(+922,337,203,685,477.5807)之间，精确到货币单位的千分之十，小数点后4位。smallmoney货币数据值介于-214,748.3648与+214,748.3647之间，精确到货币单位的千分之十。,19,近似数字real从-3.40E+38到3.40E+38的浮点精度数字float从-1.79E+308到1.79E+308的浮点精度数字时间日期型smalldatetime从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟datetime从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒（或3.33毫秒）。,20,字符串char固定长度的ASCII字符数据，最大长度为8,000个字符。例如：char（5），占据5个字节。varchar可变长度的ASCII数据，最长为8,000个字符。例如：varchar（5），占据1-5个字节。text可变长度的ASCII数据，最大长度为231-1(2,147,483,647)个字符。nchar固定长度的二字节ASCII码数据，最大长度为4,000个字符。nvarchar可变长度Unicode数据，其最大长度为4,000字符。ntext可变长度Unicode数据，其最大长度为230-1(1,073,741,823)个字符。,21,二进制binary固定长度的二进制数据，其最大长度为8,000个字节。varbinary可变长度的二进制数据，其最大长度为8,000个字节。image可变长度的二进制数据，其最大长度为231-1(2,147,483,647)个字节。主要用于图片、视频、声音等。,22,其它数据类型uniqueidentifier全局唯一标识符(GUID)使用newid()自动生成。cursor游标的引用。sql_variant一种存储SQLServer支持的各种数据类型（text、ntext、timestamp和sql_variant除外）值的数据类型。table一种特殊的数据类型，存储供以后处理的结果集。timestamp数据库范围的唯一数字，每次更新行时也进行更新。,23,3.建立数据表使用管理工具SSMS创建表,列的常用属性列名列的数据类型是否允许为空（NULL或NOTNULL）主键（PRIMARYKEY）默认值（DEFAULT）,24,设置自动增加的标识字段,25,使用CREATETABLE语句在数据库“mydb1”中创建“学生”表。CREATETABLE学生(学号char(10)PRIMARYKEY,姓名varchar(50)NOTNULL,性别bitDEFAULT(0),出生日期smalldatetime,班级char(8)NOTNULL,入学成绩floatDEFAULT(0),所属院系tinyintNOTNULL),3.建立数据表使用SQL语句创建表,26,删除表,在查询窗口中执行该语句，建立表结构。使用droptable可删除表,练习：创建”学生表”,练习：删除”学生表”,27,4、操作数据插入、删除、修改,插入数据INSERTINTO表名(列名1,列名2,.,列名n)VALUES(值1,值2,.,值n)例如：INSERTINTO院系VALUES(5,水声,0,0,王亮INSERTINTO院系(院系编号,院系名称)VALUES(6,计算机学院),所有字段,部分字段,28,向教师表插入数据,向教师表中插入6条记录（4人属于5系，2人是6系的）：insertinto教师values(t05001,王慧,女,35,讲师,5)insertinto教师values(t05002,张绍华,男,45,副教授,5)insertinto教师values(t05003,李树生,男,55,教授,5)insertinto教师values(t05004,刘华,男,25,助教,5)insertinto教师values(t06001,赵树龙,男,31,讲师,6)insertinto教师values(t06002,李建,男,42,副教授,6)若尝试插入下面3条记录：insertinto教师values(t07001,王建新,男,40,副教授,7)insertinto教师values(t06002,李佩琦,女,22,助教,6)insertinto教师values(t06003,焦晓山,女,17,无,6)结果怎样？原因？,被拒绝，违反了完整性约束,29,注意：,在插入数据时，还需要考虑到表的约束和规则等因素，如果插入的数据违反表约束或规则，则无法正常插入数据。例如：不允许设置标识列的值不允许向唯一性约束列中插入相同的数据不能违反检查约束不能与绑定到列的规则冲突不能违反外部键约束“mydb1”数据库中的各个数据表建立和插入数据时，顺序应该是先被参照的表（主键表），然后是参照表（外键表）。,30,向学生表插入记录insertinto学生values(2009051101,李珊,1,1988-3-25,20090511,598,5),练习6：（1）类似地，依次地向学生表、课程表、选课表插入记录。每个表的记录数要求在5条以上。（2）注意各字段数据要与定义的数据类型一致。,31,修改数据语法：UPDATE表名SET列名1=值1,列名2=值2,.,列名n=值nWHERE更新条件例如：update教师set年龄=38where教师号=t05001update教师set年龄=年龄+1where所在院系=6请尝试：update教师set所在院系=8where教师号=t05001update教师set性别=难where教师号=t05001update教师set年龄=68where教师号=t05001结果如何？原因？,4操作数据插入、删除、修改,修改一条记录,一次修改多条记录,32,删除记录DELETEFROM表名WHERE删除条件例如：deletefrom教师where职称=助教deletefrom教师where年龄50尝试删除被参照数据表（主键表）的数据记录，如删除院系表的一条记录，观察结果，分析原因。deletefrom院系where院系编号=6当6系还有教师、学生时，根据系统的设置，会有不同的处理方式：正常删除，但可能造成数据不一致（没有设置外键约束时）；拒绝删除（有外键约束时）；级联删除（需要设置。删除6系的同时，删除所有6系教师和学生）。删除表中全部记录deletefrom表名,或者使用SQL命令进行设置,再或者修改外键的insert和update规范,4操作数据插入、删除、修改,33,createtable教师(教师号char(6)primarykey,姓名char(10)notnull,性别char(2)check(性别in(男,女),年龄tinyintcheck(年龄between18and65),职称char(6)check(职称in(教授,副教授,讲师,助教,工人,其他),所在院系tinyintforeignkeyreferences院系(院系编号)ONDELETECASCADE/*当删除院系表中的元组造成了与教师表不一致时拒绝删除*/ONUPDATENOACTION/*当更新院系表中的院系编号时，级联更新教师表中相应的元组*/),4操作数据插入、删除、修改,34,CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEXindex_nameONtable|view(columnASC|DESC,.n)WITH,.nONfilegroup:=PAD_INDEX|FILLFACTOR=fillfactor|IGNORE_DUP_KEY|DROP_EXISTING|STATISTICS_NORECOMPUTE|SORT_IN_TEMPDB,CREATEINDEX：创建索引,35,参数说明：UNIQUE:为表或视图创建唯一索引,不允许存在索引值相同的两行。CLUSTERED:为表或视图创建一个聚集索引。NONCLUSTERED:创建一个非聚集索引。index_na